Fix DB foreign key integrity - cascade updates and deletes

[jzohrab]

Currently, the DB doesn't really enforce referential integrity, which could result in weird behaviour. Better get that under control.

e.g., the "words" table has PK "WoID", which is referenced in various tables. Currently, when hacking at the db with straight SQL, it appears that deletes from the "words" table aren't cascaded to child tables, which is bad -- eg. a wordparents record may refer to something that has been deleted, or, worse, was deleted and then replaced with something new.

Deletes in parent tables should cascade to child tables. I believe that the Doctrine model is correctly removing things from dependent tables when parents are removed (unit tests are covering that), but it is better to be safe than sorry.

Todo:

• fix the FKs, cascade update and delete
• Add tests for these things just to be complete.
• tests to ensure FKs are enforced

---

List of FKs to fix -- there may be others, but this is a good start.

CREATE TABLE IF NOT EXISTS "books" (
    FOREIGN KEY("BkLgID") REFERENCES "languages" ("LgID") ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE IF NOT EXISTS "bookstats" (
    FOREIGN KEY("BkID") REFERENCES "books" ("BkID") ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE IF NOT EXISTS "booktags" (
    FOREIGN KEY("BtT2ID") REFERENCES "tags2" ("T2ID") ON UPDATE NO ACTION ON DELETE NO ACTION,
    FOREIGN KEY("BtBkID") REFERENCES "books" ("BkID") ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE IF NOT EXISTS "sentences" (
    FOREIGN KEY("SeTxID") REFERENCES "texts" ("TxID") ON UPDATE NO ACTION ON DELETE NO ACTION,
    FOREIGN KEY("SeLgID") REFERENCES "languages" ("LgID") ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE IF NOT EXISTS "texts" (
    FOREIGN KEY("TxBkID") REFERENCES "books" ("BkID") ON UPDATE NO ACTION ON DELETE NO ACTION,
    FOREIGN KEY("TxLgID") REFERENCES "languages" ("LgID") ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE IF NOT EXISTS "texttags" (
    FOREIGN KEY("TtTxID") REFERENCES "texts" ("TxID") ON UPDATE NO ACTION ON DELETE NO ACTION,
    FOREIGN KEY("TtT2ID") REFERENCES "tags2" ("T2ID") ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE IF NOT EXISTS "texttokens" (
    FOREIGN KEY("TokTxID") REFERENCES "texts" ("TxID") ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE IF NOT EXISTS "wordimages" (
    FOREIGN KEY("WiWoID") REFERENCES "words" ("WoID") ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE IF NOT EXISTS "wordparents" (
    FOREIGN KEY("WpParentWoID") REFERENCES "words" ("WoID") ON UPDATE NO ACTION ON DELETE NO ACTION,
    FOREIGN KEY("WpWoID") REFERENCES "words" ("WoID") ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE IF NOT EXISTS "wordtags" (
    FOREIGN KEY("WtWoID") REFERENCES "words" ("WoID") ON UPDATE NO ACTION ON DELETE NO ACTION,
    FOREIGN KEY("WtTgID") REFERENCES "tags" ("TgID") ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE wordflashmessages (
  FOREIGN KEY("WfWoID") REFERENCES "words" ("WoID") ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE IF NOT EXISTS "words" (
    FOREIGN KEY("WoLgID") REFERENCES "languages" ("LgID") ON UPDATE NO ACTION ON DELETE NO ACTION
);

With Sqlite have to follow a painful process: https://www.sqlite.org/lang_altertable.html#otheralter

[jzohrab]

In progress in branch issue_38_fk_cascade_delete.

[jzohrab]

Fixed in develop, tests added. Ran the migrations on my prod data, looks good.