[odb-users] Migration issues with on_delete(set_null) constraint

Anton Paymyshev anton.paymyshev at gmail.com
Thu Jun 22 02:30:27 EDT 2017


My configuration is:
odb 2.5.0.b3
windows 10

In short: ODB compiler does not generate proper update query when a new
field added to db object with on_delete(set_null) constraint.

[See TEST.h at the bottom]

1) odb-compile TEST.h (odb.exe --database sqlite --generate-schema TEST.h)
2) uncomment 3 lines in TEST.h (effectively adds ptr field to struct and
updates schema version)
3) odb-compile TEST.h again
4) look into TEST-odb.cxx:
There is odb::create_schema(...) method which creates table S from scratch
and it adds ON DELETE SET NULL constraint on ptr field:
          db.execute ("CREATE TABLE \"S\" (\n"
                      "  \"id\" INTEGER NOT NULL PRIMARY KEY
                      "  \"ptr\" INTEGER NULL,\n"
                      "  CONSTRAINT \"ptr_fk\"\n"
                      "    FOREIGN KEY (\"ptr\")\n"
                      "    REFERENCES \"S\" (\"id\")\n"
                      "    ON DELETE SET NULL\n"
                      "    DEFERRABLE INITIALLY DEFERRED)");

There is also odb::migrate_schema_2(...) method which adds ptr column but
***NO*** ON DELETE SET NULL constraint:
          db.execute ("ALTER TABLE \"S\"\n"
                      "  ADD COLUMN \"ptr\" INTEGER NULL CONSTRAINT
\"ptr_fk\" REFERENCES \"S\" (\"id\")");

Also migrate query lacks DEFERRABLE INITIALLY DEFERRED constraint which is
not the case for us but it looks like the symptom of same issue.

#pragma db model version(1, 1)
//#pragma db model version(1, 2)

#pragma db object
struct S
#pragma db id auto
int id;

//#pragma db added(2) on_delete(set_null)
// S* ptr;

More information about the odb-users mailing list