[odb-users] One-to-many bidirectional relationship without intermediary table

[Boris Kolpackov]

Mocnik Marko <marko.mocnik at lisec.com> writes:

> I'm trying to map two tables of the form:
> 
> CREATE TABLE testi
> (
>     asdf integer NOT NULL,
>     description varchar(500),
>     CONSTRAINT testi_pkey PRIMARY KEY (asdf)
> )
> 
> CREATE TABLE testi2
> (
>     asdf integer NOT NULL,
>     qwer integer NOT NULL,
>     moredata varchar(100),
>     CONSTRAINT pk_testi2 PRIMARY KEY (asdf, qwer),
>     CONSTRAINT fk_t2_t FOREIGN KEY (asdf) REFERENCES testi (asdf)
> )
> 
> I know that's not pretty, but we have some legacy tables that have
> this kind of relationship. Is there any way to map this and if so, how?

This is actually a pretty standard one-to-many mapping except for the
foreign key (testi2.asdf) also being part of the primary key, which is
something we don't yet support in ODB out of the box.

I was able to get the identical mapping using these classes:

#pragma db object
struct testi
{
  #pragma db id
  int asdf;
};

#pragma db object
struct testi2
{
  #pragma db value
  struct id_type
  {
    #pragma db points_to(testi)
    int asdf;
    int qwer;
  };

  #pragma db id column("")
  id_type id;
};

The generated database schema (PostgreSQL version):

CREATE TABLE "testi" (
  "asdf" INTEGER NOT NULL PRIMARY KEY);

CREATE TABLE "testi2" (
  "asdf" INTEGER NOT NULL,
  "qwer" INTEGER NOT NULL,
  PRIMARY KEY ("asdf",
               "qwer"),
  CONSTRAINT "asdf_fk"
    FOREIGN KEY ("asdf")
    REFERENCES "testi" ("asdf")
    INITIALLY DEFERRED);

With virtual data members and a bit of effort you should be able
to change testi2::id_type::asdf to a pointer, something along these
lines (a sketch):

#pragma db object
struct testi2
{
  #pragma db transient
  testi* asdf;

  #pragma db transient
  int qwer;

  #pragma db value
  struct id_type
  {
    #pragma db points_to(testi)
    int asdf;
    int qwer;
  };

  #pragma db member(id) virtual(id_type) id column("") \
    get (...) \
    set (...)
};

More information on virtual data members:

https://codesynthesis.com/products/odb/doc/manual.xhtml#14.4.13


> The only way I could manage to get a mapping to compile produced a
> stack overflow when running a query...

It's hard to say what's wrong without seeing the code.


> Also, is it possible to use two separate headers for testi and testi2?
> I could not get this to compile because in one of the files I would
> need to use a forward declaration instead of a direct include and
> then the compiler would not find the appropriate template specializations.

Maybe this will help:

https://codesynthesis.com/products/odb/doc/manual.xhtml#6.3