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

quejacq at gmail.com quejacq at gmail.com
Fri Dec 16 08:47:17 EST 2011


Hi Boris,

Thank you very much for your answer.

1. Storing IP addresses as string is fine as long as you don't need to do
binary operations on these addresses, such as network mask matching. This
is why I was searching for an integer. Moreover, IPv4 addresses can be
stored in the IPv6 format, so keeping one (or more) columns dedicated to
just one "kind" of address is IMHO not a god solution.

The best solution for storing as a uinque column is postgresql's inet (or
cidr) type. Alas, MySQL doesn't provide an equivalent field. As a result, I
was hoping to do the manual mapping myself, with the help of some sort of
value_traits class. But, inferring from the declaration of value_traits in
the ODB source code, I suppose this is not currently possible. I don't
think the "virtual members" you mentionned will solve this particular
problem either. They would allow ODB to read/write to the
asio::boost:ip_address object, but I am unable to understand how it will
help me map either an IPv4 or an IPv6 address to the same database multiple
fields.

2. OK, I understand the technical difficulty. What would you suggest
instead? Create a table that assigns a unique ID for each composite primary
key, and use that as a foreign key in the other table ?

3. Yes, thank you. I did not realise ODB "magically" managed to find the
foreign key.

Anyway, thank you again for your fast answer. I don't think ODB will do it
for me on this project, but I will keep an eye on it as I believe it is a
really good solution for ORM in C++. Still, congratulations on what you
have achieved so far :).

All the best,

Quentin


On Fri, Dec 9, 2011 at 1:33 PM, Boris Kolpackov <boris at codesynthesis.com>wrote:

> 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
>


More information about the odb-users mailing list