From bd3470cd7c7012863c53a93f013799110859cb1e Mon Sep 17 00:00:00 2001 From: Nathaniel Wesley Filardo Date: Sat, 12 Mar 2022 21:10:15 +0000 Subject: [PATCH] cdb; foreign key handling - init: add foreign key behavior constraints - everywhere: request sqlite honor them --- cdb | 14 ++++++++++---- 1 file changed, 10 insertions(+), 4 deletions(-) diff --git a/cdb b/cdb index 5dad445..1bad82c 100755 --- a/cdb +++ b/cdb @@ -378,8 +378,10 @@ mksubcmd(function(c) hash TEXT NOT NULL UNIQUE ON CONFLICT FAIL)]]) ddo([[CREATE TABLE IF NOT EXISTS hash_hash_superseders ( supersederid INTEGER PRIMARY KEY ASC, - oldid INTEGER REFERENCES hashes(hashid), - newid INTEGER REFERENCES hashes(hashid), + oldid INTEGER REFERENCES hashes(hashid) + ON UPDATE CASCADE ON DELETE RESTRICT, + newid INTEGER REFERENCES hashes(hashid) + ON UPDATE CASCADE ON DELETE RESTRICT, timestamp INTEGER DEFAULT CURRENT_TIMESTAMP, note TEXT, UNIQUE(oldid, newid) ON CONFLICT FAIL)]]) @@ -388,8 +390,10 @@ mksubcmd(function(c) -- TODO: can we make path_hash a WITHOUT ROWID table? ddo([[CREATE TABLE IF NOT EXISTS path_hash ( pairid INTEGER PRIMARY KEY ASC, - pathid INTEGER REFERENCES paths(pathid), - hashid INTEGER REFERENCES hashes(hashid), + pathid INTEGER REFERENCES paths(pathid) + ON UPDATE CASCADE ON DELETE RESTRICT, + hashid INTEGER REFERENCES hashes(hashid) + ON UPDATE CASCADE ON DELETE RESTRICT, timestamp INTEGER DEFAULT CURRENT_TIMESTAMP, UNIQUE(pathid, hashid) ON CONFLICT FAIL)]]) ddo([[CREATE INDEX IF NOT EXISTS path_hash_idx_pathid @@ -1142,6 +1146,8 @@ if not dbh then error ("Database error: " .. err) end +sql_do(dbh, "PRAGMA foreign_keys = ON;") + args:_command_fn(dbh) ----------------------------------------------------------------- }}} -- 2.50.1