I just updated to persistent-sqlite and (fortunately) have rather extensive foreign key validation for my database, beyond just "enable the pragma". I noticed that as of 2.11 the migrations persistent generates can cause primary keys to be renumbered, rendering all the foreign keys referring to them incorrect. This is, of course, a bit of an...eh...problem.
I haven't pinpointed the offending code, but I suspect #1066 is too blame for this? As an example, let's look at the migration for one of my tables:
CREATE TEMP TABLE "Implementation_backup"
( "id" INTEGER PRIMARY KEY
, "algorithmId" INTEGER NOT NULL
, "name" VARCHAR NOT NULL
, "prettyName" VARCHAR NULL
, "flags" VARCHAR NULL
, "type" VARCHAR NOT NULL
, CONSTRAINT "UniqImpl" UNIQUE ("algorithmId","name")
);
INSERT INTO "Implementation_backup"("algorithmId","name","prettyName","flags","type")
SELECT "algorithmId","name","prettyName","flags","type" FROM "Implementation";
DROP TABLE "Implementation"; []
CREATE TABLE "Implementation"
( "id" INTEGER PRIMARY KEY
, "algorithmId" INTEGER NOT NULL REFERENCES "Algorithm" ON DELETE RESTRICT ON UPDATE RESTRICT
, "name" VARCHAR NOT NULL
, "prettyName" VARCHAR NULL
, "flags" VARCHAR NULL
, "type" VARCHAR NOT NULL
, CONSTRAINT "UniqImpl" UNIQUE ("algorithmId","name")
);
INSERT INTO "Implementation"
SELECT "id","algorithmId","name","prettyName","flags","type"
FROM "Implementation_backup";
DROP TABLE "Implementation_backup";
Note that the backup table has an id column, but this column is not inserted by query that fills Implementation_backup. As a result SQLite uses autoincrement filling it with ids from 1 to N. So now the id's are distinct from the ids in the original table.
Then the table is recreated and filled from Implementation_backup, including the newly generated bogus primary keys. Any foreign references to the primary key of this table are now corrupted and point to completely wrong data!
I just updated to
persistent-sqliteand (fortunately) have rather extensive foreign key validation for my database, beyond just "enable the pragma". I noticed that as of 2.11 the migrations persistent generates can cause primary keys to be renumbered, rendering all the foreign keys referring to them incorrect. This is, of course, a bit of an...eh...problem.I haven't pinpointed the offending code, but I suspect #1066 is too blame for this? As an example, let's look at the migration for one of my tables:
Note that the backup table has an
idcolumn, but this column is not inserted by query that fillsImplementation_backup. As a result SQLite uses autoincrement filling it with ids from 1 to N. So now the id's are distinct from the ids in the original table.Then the table is recreated and filled from
Implementation_backup, including the newly generated bogus primary keys. Any foreign references to the primary key of this table are now corrupted and point to completely wrong data!