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

I agree. That's why I said in my previous email that storing asio::id_address
as a composite (i.e., multi-column) value is probably a bad idea.


> 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 am not sure what made you think so. You can map any C++ type to any
database type by providing the value_traits specialization. While
there is no 16-byte integer type in MySQL (or any other database that
I am aware of), the next best thing will probably be a binary 
representation. You can definitely map asio::id_address to, say, 
MySQL BINARY(16).

The 'mapping' example in the odb-examples package provides a couple of
cases that show how to implement this. For instance, it re-maps the C++
bool type to MySQL VARCHAR(5) and stores the boolean values as "true"
and "false" string literals.


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

Yes, I guess that would be a temporary and not very elegant work around.
The other option would be to map (using the value_traits approach
mentioned above) a composite ID to a single column by "packing" multiple
members into a single-column representation. For instance, if we had a
name struct:

struct name
{
  string first;
  string last;
};

Then we could map it to a database string type and store the two fields
as "first last" or "last,first".


> 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 :).

Thanks, I am glad you like it.

Boris