Add foreign key restraints, expand and fix tests, add deletion and forking.

[misc] / new_todo / init.sql

diff --git a/new_todo/init.sql b/new_todo/init.sql
index 1be14229d48dfa52ae289a9e086d6ca0606fb59f..1a91146ae4285a51a9855fafd56e3a4778413392 100644 (file)
--- a/new_todo/init.sql
+++ b/new_todo/init.sql
@@ -5,23 +5,29 @@ CREATE TABLE days (
 CREATE TABLE templates (
   id INTEGER PRIMARY KEY,
   forked_from INTEGER,
-  forked_at TEXT
+  forked_at TEXT,
+  FOREIGN KEY (forked_from) REFERENCES templates(id)
 );
 CREATE TABLE versioned_default_efforts (
   template INTEGER NOT NULL,
   datetime TEXT NOT NULL,
   default_effort REAL NOT NULL,
-  PRIMARY KEY (template, datetime)
+  PRIMARY KEY (template, datetime),
+  FOREIGN KEY (template) REFERENCES templates(id)
 );
 CREATE TABLE versioned_descriptions (
   template INTEGER NOT NULL,
   datetime TEXT NOT NULL,
   description TEXT NOT NULL,
-  PRIMARY KEY (template, datetime)
+  PRIMARY KEY (template, datetime),
+  FOREIGN KEY (template) REFERENCES templates(id)
+
 );
 CREATE TABLE versioned_titles (
   template INTEGER NOT NULL,
   datetime TEXT NOT NULL,
   title TEXT NOT NULL,
-  PRIMARY KEY (template, datetime)
+  PRIMARY KEY (template, datetime),
+  FOREIGN KEY (template) REFERENCES templates(id)
+
 );