[odb-users] Store a tree in ODB

[Davide Anastasia]

Hi Boris,
We don't need to query often to know the structure of the tree, so a
self-referencing table is absolutely fine for our aim. However, my
suggested solution will enforce a foreign key in the m_father relation,
and I am not sure whether I can put a null value in there for the root
of the tree (DBMS might refuse this value, am I right?).

I've slightly rethink this solution in this way:

Class Node 
{
//...
Private:
std::vector< odb::boost::lazy_shared_ptr< Node > > m_children;
};

In this way I am actually generating a supplementary table that stores
the tree structure, which I can navigate both ways to discover for each
node either its father or its children. Does that sound like a more
reasonable approach?

Best,
Davide

-----Original Message-----
From: Boris Kolpackov [mailto:boris at codesynthesis.com] 
Sent: 26 November 2012 16:01
To: Davide Anastasia
Cc: odb-users at codesynthesis.com
Subject: Re: [odb-users] Store a tree in ODB

Hi Davide,

Davide Anastasia <Davide.Anastasia at qualitycapital.com> writes:

> I want to store a tree inside a database table: every tuple can be the

> father of several other tuples (in a sort of tree-shaped data 
> structure).
> 
> Would something like this work?
>
> class Node
> {
>   //...
> private:
>   odb:: boost::lazy_shared_ptr< Node> m_father; }

Yes, there is no technical reason why this won't work. Self-references
are supported by ODB. How practical this will be is another question,
though. Every "navigation" from child to parent or the other way around
will require a database query. If that's too slow then a database-
specific data type (e.g., ltree in PostgreSQL) or a custom binary
representation of a tree (stored as BLOB) might be the alternatives.


> Can also a child relationship be realized in this way?

Yes, though you would need to use a weak pointer for one of the
references. And probably make one of them inverse (it is a bidirectional
relationship).

Boris