GNATColl ORM problem (foreign key twins)

GNATColl ORM problem (foreign key twins)

[Jacob Sparre Andersen]

I am spending some time studying and experimenting with the GNATColl
database interfacing facilities.  GNATColl can generate a type-safe
database interface from a formal description of the tables, but in some
cases it generates un-compilable Ada packages.

I want to model a collection of categories and subcategories in a
database like this:

| TABLE    | categories          | category        |  | Item categorisations |
| name     | Text                | PK              |  |                      |

| TABLE    | category_subsets    | category_subset |  | Item categorisations |
| superset | FK categories(name) | NOT NULL        |  |                      |
| subset   | FK categories(name) | NOT NULL        |  |                      |

But this fails because GNATColl names the foreign key fields with the
referenced field name instead of with the referencing field name.

See <http://repositories.jacob-sparre.dk/gnatcoll-orm-foreign-key-twins>
for some of the variations I have tried.

Does any of you know enough about GNATColl (specifically
"gnatcoll_db2ada") to have an idea about how to fix the problem?  (The
problem has been reported to AdaCore, but they don't have any customers
funding a fix.)

Greetings,

Jacob
-- 
Photo of the day:
                  http://billeder.sparre-andersen.dk/dagens/

Re: GNATColl ORM problem (foreign key twins)

[Simon Wright]

Jacob Sparre Andersen <jacob@jacob-sparre.dk> writes:

> I want to model a collection of categories and subcategories in a
> database like this:
>
> | TABLE    | categories          | category        |  | Item categorisations |
> | name     | Text                | PK              |  |                      |
>
> | TABLE    | category_subsets    | category_subset |  | Item categorisations |
> | superset | FK categories(name) | NOT NULL        |  |                      |
> | subset   | FK categories(name) | NOT NULL        |  |                      |
>
> But this fails because GNATColl names the foreign key fields with the
> referenced field name instead of with the referencing field name.

I think that the problem is that the thing in brackets is not meant to
be the PK in the table that is being referenced, it's the name of the
reverse relationship. Look for 'revert_name' under [1] (I'm pretty sure
this should be 'reverse_name', BTW).

So yours would be

| TABLE    | category_subsets     | category_subset |  | Item categorisations |
| superset | FK categories(parents) | NOT NULL        |  |                    |
| subset   | FK categories(children) | NOT NULL        |  |                    |

and you get a function Parents that takes a Category and returns (a list
of?) Categories (a Category_Manager?).

[1] http://docs.adacore.com/gnatcoll-docs/sql.html#database-schema

Re: GNATColl ORM problem (foreign key twins)

[Jacob Sparre Andersen]

Simon Wright wrote:

> I think that the problem is that the thing in brackets is not meant to
> be the PK in the table that is being referenced, it's the name of the
> reverse relationship. Look for 'revert_name' under [1] (I'm pretty
> sure this should be 'reverse_name', BTW).

Thanks.  It looks like you are correct.

> | TABLE    | category_subsets     | category_subset |  | Item categorisations |
> | superset | FK categories(parents) | NOT NULL        |  |                    |
> | subset   | FK categories(children) | NOT NULL        |  |                    |

It can be even simpler:

| TABLE    | category_subsets | category_subset |  | Item categorisations |
| superset | FK categories    | NOT NULL        |  |                      |
| subset   | FK categories    | NOT NULL        |  |                      |

> [1] http://docs.adacore.com/gnatcoll-docs/sql.html#database-schema

The formal grammar for foreign keys might be improved by making it
explicit that the reverse_name isn't required.

Once more; thanks for clarifying the documentation for me.

Greetings,

Jacob
-- 
"... while the C compiler will happily generate code for
 almost anything produced by leaning on the keyboard."