[odb-users] polymorphic base orphans

[Boris Kolpackov]

Сергей Никонов <shprotello at mail.ru> writes:

> #pragma db object abstract polymorphic
> struct base
> {
>   virtual ~base() {}
> #pragma db id auto
>   int id;
> };
> #pragma db object
> struct derived1
>   : public base
> {
> #pragma db null
>   int value;
> };
> #pragma db object
> struct derived2
>   : public base
> {
> #pragma db null
>   int value;
> #pragma db null on_delete(cascade)
>   std::shared_ptr<derived1> ref;
> };

The generated database schema for this object model (using PosgreSQL as
an example):

CREATE TABLE "base" (
  "id" SERIAL NOT NULL PRIMARY KEY,
  "typeid" TEXT NOT NULL);

CREATE TABLE "derived1" (
  "id" INTEGER NOT NULL PRIMARY KEY,
  "value" INTEGER NULL,
  CONSTRAINT "id_fk"
    FOREIGN KEY ("id")
    REFERENCES "base" ("id")
    ON DELETE CASCADE);

CREATE TABLE "derived2" (
  "id" INTEGER NOT NULL PRIMARY KEY,
  "value" INTEGER NULL,
  "ref" INTEGER NULL,
  CONSTRAINT "id_fk"
    FOREIGN KEY ("id")
    REFERENCES "base" ("id")
    ON DELETE CASCADE,
  CONSTRAINT "ref_fk"
    FOREIGN KEY ("ref")
    REFERENCES "derived1" ("id")
    ON DELETE CASCADE
    INITIALLY DEFERRED);


> After persisting objects like this:
>  
>     auto d1 = std::make_shared<derived1>();
>     db.persist(d1);
>     auto d2 = std::make_shared<derived2>();
>     d2->ref = d1;
>     db.persist(d2);
>  
> lets delete «d1» instance:
>  
>     db.erase<base>(d1->id);
>  
> After committing the database will contain an orphan record:
>    id    typeid
>    ----------------
>    2    derived2

Yes, this is the base entry of d2. When you erased d1, d2 is also erased
due to the derived2::ref reference that has ON DELETE CASCADE. But ON
DELETE CASCADE between base and derived2 points the the "wrong" (for
this case) direction. I don't think there can be a perfect solution to
this issue without breaking everything else. As the manual states, ODB
is not aware of ON DELETE actions that happen at the database level and
thus there are limitations.

I can see two ways to work around this issue:

1. Change std::shared_ptr<derived1> to std::shared_ptr<base> so that the
   generated ON DELETE CASCADE points in the right direction.

   With a bit of virtual member magic you can probably even keep derived1
   in the interface and only present base to ODB.

2. Change on_delete(cascade) to on_delete(set_null) and then erase
   derived2 entries with NULL ref via an ODB call (so that it can do
   the right thing with regards to base/derived).