[odb-users] Composite values, multiple values in primary key, and foreign constraints

[Boris Kolpackov]

Hi Quentin,

quejacq at gmail.com <quejacq at gmail.com> writes:

> 1. I'm trying to define a composite value for the class
> boost::asio::ip::address, which can contain either an IPv4 or IPv6 address.
> Because MySQL (for example) cannot handle the 128 bits of an IPv6 address
> in a single integer field, I have to split it up in at least two columns.
> >From what I understand in the manual and examples, I can create composite
> value types using the "db value" pragma. However, it seems to me that I am
> only able to do so if I create the classes myself and use the pragmas
> accordingly.

There are two ways to store a value type like this in the database: as
a simple value (single column) or as a composite value (multi-column).
Which way is better really depends on the application and Section 3.1,
"Concepts and Terminology" in the ODB Manual has more information on
this. Though in this particular case it seems like the simple value
would be preferably (see below).

Let's cover the composite value case first. If you had written the
class yourself, then that would have been easy: you could simply add
a friend declaration to the class and made sure that the types for
all its data members are also mapped.

However, we cannot modify a third-party class like asio::ip::address
to add a friend declaration and its data member are private. So this
is a problem. The way I see how this will be supported in the future
is via the feature we call "virtual members". Essentially, the idea
is to support accessing/modifying private data members using accessors
instead of directly. For more information on virtual member, see this
thread:

http://www.codesynthesis.com/pipermail/odb-users/2011-October/000352.html

Provided we had virtual members and could map asio::ip::address to a
composite value type, I don't think this will be an ideal mapping. If
you look at the data members declared in this type, there are three:
type_ (enum), ipv4_address_, and ipv6_address_. If mapped to a composite
value, this type will occupy (at least) three columns in the database
with only two actually containing any useful data. I think this is a
bit wasteful.

Let's now examine the simple value case, which I think is generally
preferable. The basic idea is to store it in a single column using
some format. To implement it you will need to provide the value_traits
template specialization. The 'mapping' example shows how to do this.

The simplest format that we can use to store asio::ip::address is
probably as a string. The class already has the {to,from}_string()
functions so this should be quite easy to implement. Another, more
compact format, would be to store it as a 17-byte array (1 byte as
a type indicator and 16 bytes for storage).


> 2. Every example carefully avoids the case of multiple values in the
> primary key. While I thought it would be a simple matter of using multiple
> "db id" pragmas, this leads to an error returned by the odb compiler. How
> can I have a primary key made of a tuple? Do I have to define another class
> just reserved for them?

Currently ODB only supports simple values as object ids though we have
support for composite ids high up in our TODO list. However, I don't
think we will support the "multiple ids" case (i.e., where you can mark
several data members as object ids). In many places in its API ODB
returns object ids and returning such a multi-id will be messy.


> 3. The only mention of foreign keys in the manual is inside raw SQL queries
> to the DBMS. How can I define foreign key constraints among tables of the
> database?

ODB maps relationships between C++ objects to foreign key constraints
between database tables. For instance, in the 'relationship' example
we have:

#pragma db object
class employer
{
  ...
};

#pragma db object
class employee
{
  ...

  shared_ptr<employer> employer_;
};

And in the generated database schema in the employee table we have
(using the PostgreSQL schema as an example):

CONSTRAINT "employer_fk"
  FOREIGN KEY ("employer")
  REFERENCES "employer" ("name")
  INITIALLY DEFERRED

Is this what you are looking for?

Boris