en_Tutorial2

Mapping related tables: one-to-many (tut2.cpp)

Let's take a look at one-to-many kind of relationship, consider the example
Client – Orders. One client may have zero or more orders in some accounting
system, each order belongs to some client. Clients are stored in table
client_tbl, while their orders are stored in table order_tbl.

At the SQL level this relationship can be expressed as having a foreign key
constraint on column client_id in the child table order_tbl referencing
column id in the parent table client_tbl.

At the ORM level such a relationship is represented by the objects'
properties. An instance of a class that maps to the child table usually has an
object-reference property, referencing the parent object. From the other
side of the relationship, an instance of a class that maps to the parent table
may have a collection-of-objects property (which is sometimes called
"backref"), to be able to iterate all over its children.

First, let's define data schema with two classes Client and Order, mapped
to two tables client_tbl and order_tbl. Also let's define the relationship
between the classes.

<schema>
    <table name="client_tbl" sequence="client_seq" class="Client" xml-name="client">
        <column name="id" type="longint">
            <primary-key />
        </column>
        <column name="dt" type="datetime" null="false" default="sysdate" />
        <column name="name" type="string" size="100" null="false" />
        <column name="email" type="string" size="100" null="false" />
        <column name="phone" type="string" size="50" null="true" />
        <column name="budget" type="decimal" />
    </table>
    <table name="order_tbl" sequence="order_seq" class="Order" xml-name="order">
        <column name="id" type="longint">
            <primary-key />
        </column>
        <column name="client_id" type="longint" null="false">
            <foreign-key table="client_tbl" key="id"/>
        </column>
        <column name="dt" type="datetime" null="false" default="sysdate" />
        <column name="memo" type="string" size="100" />
        <column name="total_sum" type="decimal" null="false" />
        <column name="paid_sum" type="decimal" default="0" />
        <column name="paid_dt" type="datetime" />
    </table>
    <relation type="one-to-many">
        <one class="Client" property="orders" />
        <many class="Order" property="owner" />
    </relation>
</schema>

In the table definition of order_tbl the column client_id is defined to be
a foreign key, it acts like a link between the tables. When there is only one
such link between two tables, then there is no need to specify additional
parameters in the element <relation>.

SQLite code for the definition above looks like this (see
Tutorial1, how to use yborm_gen utility):

CREATE TABLE client_tbl (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(50),
    budget NUMERIC
);

CREATE TABLE order_tbl (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    client_id INTEGER NOT NULL,
    dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    memo VARCHAR(100),
    total_sum NUMERIC NOT NULL,
    paid_sum NUMERIC DEFAULT 0,
    paid_dt TIMESTAMP
    , FOREIGN KEY (client_id) REFERENCES client_tbl(id)
);

The following program uses the domain classes, generated from the XML
description, see Tutorial1. The program creates the child
object first and assigns some values to its fields, then it creates the parent
object, and then it links the child to the parent. After all it stores the
objects to the session and flushes them to the database committing transaction
as well (session.commit();). Note, that these objects will be stored in
proper sequence (first - parent, second - child), also note that the foreign
key value is assigned automatically, as well as primary key values.

#include <iostream>
#include "domain/Client.h"
#include "domain/Order.h"
int main()
{
    std::auto_ptr<Yb::SqlConnection> conn(new Yb::SqlConnection(
            "sqlite+sqlite://./tut2.sqlite"));
    Yb::Engine engine(Yb::Engine::READ_WRITE, conn);
    Yb::Session session(Yb::init_schema(), &engine);

    Domain::Order::Holder order;
    std::string amount;
    std::cout << "Enter order amount: \n";
    std::cin >> amount;
    order->total_sum = Yb::Decimal(amount);

    Domain::Client::Holder client;
    std::string name, email;
    std::cout << "Enter name, email: \n";
    std::cin >> name >> email;
    client->name = name;
    client->email = email;
    client->dt = Yb::now();

    std::cout << "Client's orders count: " << client->orders.size() << "\n";
    order->owner = client;
    std::cout << "Client's orders count: " << client->orders.size() << "\n";

    order->save(session);
    client->save(session);
    session.commit();
    std::cout << order->xmlize(1)->serialize() << std::endl;
    return 0;
}

Here you can see that linking a child (Order class) to a parent (Client
class) looks like an assignment (order->owner = client;). Also, this example
operates on the domain classes in a different way than it was seen in
Tutorial1. A kind of smart pointers
<DomainClass>::Holder is used here to implement the nested or recurrent
object references. That is, all the object-properties are implemented using
<DomainClass>::Holder classes, and therefore they must be dereferenced using
an arrow (->), and not a dot (.).

Let's compile the example:

$ c++ -I. -I$YBORM_ROOT/include/yb -o tut2 tut2.cpp domain/Client.cpp domain/Order.cpp -L$YBORM_ROOT/lib -lybutil -lyborm

And then run it:

$ ./tut2
Enter order amount: 
45.67
Enter name, email: 
Petya pet@ya.ru
Client's orders count: 0
Client's orders count: 1
<order><id>2</id><owner><id>2</id><dt>2014-06-01T17:25:32</dt><name>Petya</name><email>pet@ya.ru</email><budget is_null="1"/></owner><dt>2014-06-01T17:25:17</dt><memo is_null="1"/><total-sum>45.67</total-sum><paid-sum>0</paid-sum><paid-dt is_null="1"/></order>

If we would trace the SQL operators, performing the same steps as in
Tutorial1, we could see the following:

14-06-01 17:25:32.293 31927/31927 DEBG orm: flush started
14-06-01 17:25:32.293 31927/31927 DEBG sql: begin transaction
14-06-01 17:25:32.293 31927/31927 DEBG sql: prepare: INSERT INTO client_tbl (dt, name, email, budget) VALUES (?, ?, ?, ?)
14-06-01 17:25:32.294 31927/31927 DEBG sql: bind: (DateTime, String, String, Decimal)
14-06-01 17:25:32.294 31927/31927 DEBG sql: exec prepared: p1="'2014-06-01 17:25:32'" p2="'Petya'" p3="'pet@ya.ru'" p4="NULL"
14-06-01 17:25:32.295 31927/31927 DEBG sql: prepare: SELECT SEQ LID FROM SQLITE_SEQUENCE WHERE NAME = 'client_tbl'
14-06-01 17:25:32.295 31927/31927 DEBG sql: exec prepared:
14-06-01 17:25:32.295 31927/31927 DEBG sql: fetch: LID='2' 
14-06-01 17:25:32.295 31927/31927 DEBG sql: fetch: no more rows
14-06-01 17:25:32.295 31927/31927 DEBG sql: prepare: INSERT INTO order_tbl (client_id, dt, memo, total_sum, paid_sum, paid_dt) VALUES (?, ?, ?, ?, ?, ?)
14-06-01 17:25:32.295 31927/31927 DEBG sql: bind: (LongInt, DateTime, String, Decimal, Decimal, DateTime)
14-06-01 17:25:32.295 31927/31927 DEBG sql: exec prepared: p1="2" p2="'2014-06-01 17:25:17'" p3="NULL" p4="45.67" p5="0" p6="NULL"
14-06-01 17:25:32.295 31927/31927 DEBG sql: prepare: SELECT SEQ LID FROM SQLITE_SEQUENCE WHERE NAME = 'order_tbl'
14-06-01 17:25:32.296 31927/31927 DEBG sql: exec prepared:
14-06-01 17:25:32.296 31927/31927 DEBG sql: fetch: LID='2' 
14-06-01 17:25:32.296 31927/31927 DEBG sql: fetch: no more rows
14-06-01 17:25:32.296 31927/31927 DEBG orm: flush finished OK
14-06-01 17:25:32.296 31927/31927 DEBG sql: commit
14-06-01 17:25:32.395 31927/31927 DEBG sql: prepare: SELECT order_tbl.id, order_tbl.client_id, order_tbl.dt, order_tbl.memo, order_tbl.total_sum, order_tbl.paid_sum, order_tbl.paid_dt FROM order_tbl WHERE order_tbl.id = ?
14-06-01 17:25:32.395 31927/31927 DEBG sql: exec prepared: p1="2"
14-06-01 17:25:32.395 31927/31927 DEBG sql: fetch: ID='2' CLIENT_ID='2' DT='2014-06-01T17:25:17' MEMO=NULL TOTAL_SUM='45.67' RECEIPT_SUM='0' RECEIPT_DT=NULL 
14-06-01 17:25:32.395 31927/31927 DEBG sql: fetch: no more rows
14-06-01 17:25:32.395 31927/31927 DEBG sql: prepare: SELECT client_tbl.id, client_tbl.dt, client_tbl.name, client_tbl.email, client_tbl.budget FROM client_tbl WHERE client_tbl.id = ?
14-06-01 17:25:32.396 31927/31927 DEBG sql: exec prepared: p1="2"
14-06-01 17:25:32.396 31927/31927 DEBG sql: fetch: ID='2' DT='2014-06-01T17:25:32' NAME='Petya' EMAIL='pet@ya.ru' BUDGET=NULL 
14-06-01 17:25:32.396 31927/31927 DEBG sql: fetch: no more rows

Note the correct sequence of insertion (first – parent, second – child). This
is achieved by doing the topological sort on the graph of objects. The value
of foreign key is assigned automatically, as well as the values of primary
keys.

On linking these objects using an assignment we can see that the orders
collection of the corresponding instance of Client class gets altered as
well. This is because both client->orders and order->owner properties
share the same internal object RelationObject. The same effect could be
achieved if we would insert into the collection-property. Let's change the
assignment at the child object side

    order->owner = client;

to the insertion into the collection-property at the parent object side

    client->orders.insert(*order);

These two lines do exactly the same thing.