commit 97fc8eaf8ef4d01045b0a177c78e27b2705edc95
parent 10cebf385b4af8e422175d0c5f2a54bed2397048
Author: Natasha Kerensikova <natgh@instinctive.eu>
Date: Sun, 21 Jan 2024 10:13:50 +0000
SQL schema is updated for future features
Diffstat:
5 files changed, 121 insertions(+), 26 deletions(-)
diff --git a/src/pref-matrix.scm b/src/pref-matrix.scm
@@ -117,24 +117,92 @@
(when (= 0 (db-version))
(for-each
- (lambda (s) (exec (sql db s)))
- (list "PRAGMA user_version = 1;"
+ (lambda (s) (exec (sql/transient db s)))
+ (list "PRAGMA user_version = 2;"
"PRAGMA journal_mode = wal;"
"PRAGMA synchronous = normal;"
- "CREATE TABLE config (key TEXT PRIMARY KEY, val);"
- "CREATE TABLE subject (id INTEGER PRIMARY KEY, name TEXT NOT NULL);"
- "CREATE TABLE object (id INTEGER PRIMARY KEY, name TEXT NOT NULL);"
+ "CREATE TABLE config (key TEXT PRIMARY KEY, val ANY);"
+ "CREATE TABLE topic (id INTEGER PRIMARY KEY,
+ name TEXT NOT NULL,
+ closed INTEGER NOT NULL DEFAULT 0);"
+ "CREATE TABLE subject (id INTEGER PRIMARY KEY,
+ topic_id NOT NULL REFERENCES topic(id)
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ name TEXT NOT NULL,
+ hidden INTEGER NOT NULL DEFAULT 0);"
+ "CREATE TABLE object (id INTEGER PRIMARY KEY,
+ topic_id NOT NULL REFERENCES topic(id)
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ name TEXT NOT NULL,
+ hidden INTEGER NOT NULL DEFAULT 0);"
"CREATE TABLE pref (id INTEGER PRIMARY KEY,
- sub_id REFERENCES subject(id)
+ sub_id NOT NULL REFERENCES subject(id)
ON UPDATE CASCADE ON DELETE CASCADE,
- obj_id REFERENCES object(id)
+ obj_id NOT NULL REFERENCES object(id)
ON UPDATE CASCADE ON DELETE CASCADE,
val INTEGER NOT NULL DEFAULT 0);"
- "CREATE UNIQUE INDEX sub_name ON subject(name);"
- "CREATE UNIQUE INDEX obj_name ON object(name);"
+ "CREATE UNIQUE INDEX topic_name ON topic(name);"
+ "CREATE UNIQUE INDEX sub_name ON subject(topic_id,name);"
+ "CREATE UNIQUE INDEX obj_name ON object(topic_id,name);"
+ "CREATE INDEX v_sub_name ON subject(topic_id,name) WHERE hidden=0;"
+ "CREATE INDEX v_obj_name ON object(topic_id,name) WHERE hidden=0;"
"CREATE UNIQUE INDEX sub_obj ON pref(sub_id,obj_id);")))
-(assert (= 1 (db-version)))
+(when (= 1 (db-version))
+ (with-transaction db (lambda ()
+ (for-each
+ (lambda (s) (exec (sql/transient db s)))
+ (list "ALTER TABLE config RENAME TO old_config;"
+ "ALTER TABLE subject RENAME TO old_subject;"
+ "ALTER TABLE object RENAME TO old_object;"
+ "ALTER TABLE pref RENAME TO old_pref;"
+ "CREATE TABLE config (key TEXT PRIMARY KEY, val ANY);"
+ "INSERT INTO config(key,val) SELECT key,val FROM old_config;"
+ "INSERT OR IGNORE INTO config(key,val)
+ VALUES ('default_topic','default');"
+ "DROP TABLE old_config;"
+ "CREATE TABLE topic (id INTEGER PRIMARY KEY,
+ name TEXT NOT NULL,
+ closed INTEGER NOT NULL DEFAULT 0);"
+ "INSERT INTO topic(id,name)
+ SELECT 1,val FROM config WHERE key='default_topic';"
+ "CREATE TABLE subject (id INTEGER PRIMARY KEY,
+ topic_id NOT NULL REFERENCES topic(id)
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ name TEXT NOT NULL,
+ hidden INTEGER NOT NULL DEFAULT 0);"
+ "INSERT INTO subject(id,topic_id,name)
+ SELECT id,1,name FROM old_subject;"
+ "CREATE TABLE object (id INTEGER PRIMARY KEY,
+ topic_id NOT NULL REFERENCES topic(id)
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ name TEXT NOT NULL,
+ hidden INTEGER NOT NULL DEFAULT 0);"
+ "INSERT INTO object(id,topic_id,name)
+ SELECT id,1,name FROM old_object;"
+ "CREATE TABLE pref (id INTEGER PRIMARY KEY,
+ sub_id NOT NULL REFERENCES subject(id)
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ obj_id NOT NULL REFERENCES object(id)
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ val INTEGER NOT NULL DEFAULT 0);"
+ "INSERT INTO pref(id,sub_id,obj_id,val)
+ SELECT id,sub_id,obj_id,val FROM old_pref;"
+ "DROP TABLE old_pref;"
+ "DROP TABLE old_subject;"
+ "DROP TABLE old_object;"
+ "CREATE UNIQUE INDEX topic_name ON topic(name);"
+ "CREATE UNIQUE INDEX sub_name ON subject(topic_id,name);"
+ "CREATE UNIQUE INDEX obj_name ON object(topic_id,name);"
+ "CREATE INDEX v_sub_name ON subject(topic_id,name) WHERE hidden=0;"
+ "CREATE INDEX v_obj_name ON object(topic_id,name) WHERE hidden=0;"
+ "CREATE UNIQUE INDEX sub_obj ON pref(sub_id,obj_id);"
+ "PRAGMA user_version = 2;")))))
+
+(assert (= 2 (db-version)))
+
+(exec (sql/transient db
+ "INSERT OR IGNORE INTO topic(id,name) VALUES (1,'TODO');"))
;;;;;;;;;;;;;;;;;;;
;; Database Query
@@ -252,7 +320,7 @@
name))
#f
(begin
- (exec (sql db "INSERT INTO object(name) VALUES (?);") name)
+ (exec (sql db "INSERT INTO object(topic_id,name) VALUES (1,?);") name)
(let ((result (last-insert-rowid db)))
(unless replaying? (generate-json))
result))))
@@ -264,7 +332,7 @@
name))
#f
(begin
- (exec (sql db "INSERT INTO subject(name) VALUES (?);") name)
+ (exec (sql db "INSERT INTO subject(topic_id,name) VALUES (1,?);") name)
(let ((result (last-insert-rowid db)))
(unless replaying? (generate-json))
result))))
diff --git a/test/run.sh b/test/run.sh
@@ -92,3 +92,13 @@ kill "${SRV_PID}"
trap 'rm -f ${TO_CLEAN}' EXIT
sqlite3 "${TEST_DB}" .dump | diff -u "${TEST_DIR}/test-2-dump.sql" -
+
+##############################################
+## Test 3: database migration from schema v1
+
+cp -f "${TEST_DIR}/test-2-v1.sqlite" "${TEST_DB}"
+rm -f "${TEST_DB}-shm" "${TEST_DB}-wal"
+"$@" "${TEST_DB}" "${TEST_TRACE}" "${TEST_DIR}/test-3.scm"
+sqlite3 "${TEST_DB}" .dump \
+ | sed "/'default_topic'/d;s/'default'/'TODO'/" \
+ | diff -u "${TEST_DIR}/test-2-dump.sql" -
diff --git a/test/test-2-dump.sql b/test/test-2-dump.sql
@@ -1,27 +1,42 @@
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
-CREATE TABLE config (key TEXT PRIMARY KEY, val);
+CREATE TABLE config (key TEXT PRIMARY KEY, val ANY);
INSERT INTO config VALUES('json-prefix','test-');
INSERT INTO config VALUES('server-port',9090);
-CREATE TABLE subject (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
-INSERT INTO subject VALUES(1,'foo');
-INSERT INTO subject VALUES(2,'bar');
-INSERT INTO subject VALUES(3,'meow');
-CREATE TABLE object (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
-INSERT INTO object VALUES(1,'01');
-INSERT INTO object VALUES(2,'03');
-INSERT INTO object VALUES(3,'02');
-INSERT INTO object VALUES(4,'04');
+CREATE TABLE topic (id INTEGER PRIMARY KEY,
+ name TEXT NOT NULL,
+ closed INTEGER NOT NULL DEFAULT 0);
+INSERT INTO topic VALUES(1,'TODO',0);
+CREATE TABLE subject (id INTEGER PRIMARY KEY,
+ topic_id NOT NULL REFERENCES topic(id)
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ name TEXT NOT NULL,
+ hidden INTEGER NOT NULL DEFAULT 0);
+INSERT INTO subject VALUES(1,1,'foo',0);
+INSERT INTO subject VALUES(2,1,'bar',0);
+INSERT INTO subject VALUES(3,1,'meow',0);
+CREATE TABLE object (id INTEGER PRIMARY KEY,
+ topic_id NOT NULL REFERENCES topic(id)
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ name TEXT NOT NULL,
+ hidden INTEGER NOT NULL DEFAULT 0);
+INSERT INTO object VALUES(1,1,'01',0);
+INSERT INTO object VALUES(2,1,'03',0);
+INSERT INTO object VALUES(3,1,'02',0);
+INSERT INTO object VALUES(4,1,'04',0);
CREATE TABLE pref (id INTEGER PRIMARY KEY,
- sub_id REFERENCES subject(id)
+ sub_id NOT NULL REFERENCES subject(id)
ON UPDATE CASCADE ON DELETE CASCADE,
- obj_id REFERENCES object(id)
+ obj_id NOT NULL REFERENCES object(id)
ON UPDATE CASCADE ON DELETE CASCADE,
val INTEGER NOT NULL DEFAULT 0);
INSERT INTO pref VALUES(3,1,4,2);
INSERT INTO pref VALUES(4,1,1,4);
INSERT INTO pref VALUES(5,2,1,0);
-CREATE UNIQUE INDEX sub_name ON subject(name);
-CREATE UNIQUE INDEX obj_name ON object(name);
+CREATE UNIQUE INDEX topic_name ON topic(name);
+CREATE UNIQUE INDEX sub_name ON subject(topic_id,name);
+CREATE UNIQUE INDEX obj_name ON object(topic_id,name);
+CREATE INDEX v_sub_name ON subject(topic_id,name) WHERE hidden=0;
+CREATE INDEX v_obj_name ON object(topic_id,name) WHERE hidden=0;
CREATE UNIQUE INDEX sub_obj ON pref(sub_id,obj_id);
COMMIT;
diff --git a/test/test-2-v1.sqlite b/test/test-2-v1.sqlite
Binary files differ.
diff --git a/test/test-3.scm b/test/test-3.scm
@@ -0,0 +1,2 @@
+(generate-json)
+(exit)
