iens

commit 26221a3ce715f9aab75294f9f27dd56020949b86
parent 73f292296bb8132dc3dc12aa394111b55112a8b7
Author: Natasha Kerensikova <natgh@instinctive.eu>
Date:   Fri,  3 Apr 2026 19:04:52 +0000

Database schema v3
Diffstat:
M
src/cgi.scm
 | 
33
++-------------------------------
M
src/common.scm
 | 
47
++++++++++++++++++++++++++++++++++++++++++++++-
M
src/iens.scm
 | 
2
+-

3 files changed, 49 insertions(+), 33 deletions(-)
diff --git a/src/cgi.scm b/src/cgi.scm
@@ -246,37 +246,8 @@ END-OF-CSS
 
 (define db (open-database db-name))
 (exec (sql/transient db "PRAGMA foreign_keys = ON;"))
-(define (db-version)
-  (query fetch-value (sql db "PRAGMA user_version;")))
-
-(when (= 2 (db-version))
-  (for-each
-    (lambda (s) (exec (sql/transient db s)))
-    (list "CREATE TABLE gruik
-            (id INTEGER PRIMARY KEY,
-             position INTEGER NOT NULL,
-             notes TEXT NOT NULL,
-             description TEXT,
-             ptime INTEGER NOT NULL,
-             section TEXT NOT NULL,
-             title TEXT NOT NULL,
-             url TEXT NOT NULL,
-             mark INTEGER NOT NULL DEFAULT 0,
-             ctime INTEGER NOT NULL,
-             mtime INTEGER NOT NULL,
-             stime INTEGER);"
-          "CREATE UNIQUE INDEX i_gruik ON gruik(position);"
-          "CREATE INDEX i_gruik_time ON gruik(ptime);"
-          "CREATE TABLE gruik_tags
-            (gruik_id REFERENCES gruik(id) ON UPDATE CASCADE ON DELETE CASCADE,
-             tag_id REFERENCES tag(id) ON UPDATE CASCADE ON DELETE CASCADE);"
-          "CREATE UNIQUE INDEX i_gruik_rel ON gruik_tags(gruik_id,tag_id);"
-          "CREATE INDEX i_gruik_tags ON gruik_tags(tag_id,gruik_id);"
-          "INSERT INTO config(key, val) VALUES ('gruik-source', '/home/nat/irclogs/libera/#gcufeed.log');"
-          "INSERT INTO config(key, val) VALUES ('gruik-seen', 38678556);"
-          "INSERT INTO config(key, val) VALUES ('gruik-host', 'https://users.instinctive.eu');"
-          "INSERT INTO config(key, val) VALUES ('gruik-prefix', '/iens');"
-          "PRAGMA user_version = 3;")))
+
+(include "common.scm")
 
 (unless (= 3 (db-version))
   (die "Unexpectad database version"))
diff --git a/src/common.scm b/src/common.scm
@@ -48,7 +48,27 @@
           "CREATE UNIQUE INDEX i_rel0 ON tagrel(url_id,tag_id);"
           "CREATE INDEX i_rel1 ON tagrel(url_id);"
           "CREATE INDEX i_rel2 ON tagrel(tag_id);"
-          "PRAGMA user_version = 2;")))
+          "CREATE TABLE gruik
+            (id INTEGER PRIMARY KEY,
+             position INTEGER NOT NULL,
+             notes TEXT NOT NULL,
+             description TEXT,
+             ptime INTEGER NOT NULL,
+             section TEXT NOT NULL,
+             title TEXT NOT NULL,
+             url TEXT NOT NULL,
+             mark INTEGER NOT NULL DEFAULT 0,
+             ctime INTEGER NOT NULL,
+             mtime INTEGER NOT NULL,
+             stime INTEGER);"
+          "CREATE UNIQUE INDEX i_gruik ON gruik(position);"
+          "CREATE INDEX i_gruik_time ON gruik(ptime);"
+          "CREATE TABLE gruik_tags
+            (gruik_id REFERENCES gruik(id) ON UPDATE CASCADE ON DELETE CASCADE,
+             tag_id REFERENCES tag(id) ON UPDATE CASCADE ON DELETE CASCADE);"
+          "CREATE UNIQUE INDEX i_gruik_rel ON gruik_tags(gruik_id,tag_id);"
+          "CREATE INDEX i_gruik_tags ON gruik_tags(tag_id,gruik_id);"
+          "PRAGMA user_version = 3;")))
 
 (when (= 0 (db-version))
   (write-line "Updating database schema from v0 to v1")
@@ -68,3 +88,28 @@
     (lambda (s) (exec (sql/transient db s)))
     (list "ALTER TABLE feed ADD COLUMN mtime INTEGER;"
           "PRAGMA user_version = 2;")))
+
+(when (= 2 (db-version))
+  (for-each
+    (lambda (s) (exec (sql/transient db s)))
+    (list "CREATE TABLE gruik
+            (id INTEGER PRIMARY KEY,
+             position INTEGER NOT NULL,
+             notes TEXT NOT NULL,
+             description TEXT,
+             ptime INTEGER NOT NULL,
+             section TEXT NOT NULL,
+             title TEXT NOT NULL,
+             url TEXT NOT NULL,
+             mark INTEGER NOT NULL DEFAULT 0,
+             ctime INTEGER NOT NULL,
+             mtime INTEGER NOT NULL,
+             stime INTEGER);"
+          "CREATE UNIQUE INDEX i_gruik ON gruik(position);"
+          "CREATE INDEX i_gruik_time ON gruik(ptime);"
+          "CREATE TABLE gruik_tags
+            (gruik_id REFERENCES gruik(id) ON UPDATE CASCADE ON DELETE CASCADE,
+             tag_id REFERENCES tag(id) ON UPDATE CASCADE ON DELETE CASCADE);"
+          "CREATE UNIQUE INDEX i_gruik_rel ON gruik_tags(gruik_id,tag_id);"
+          "CREATE INDEX i_gruik_tags ON gruik_tags(tag_id,gruik_id);"
+          "PRAGMA user_version = 3;")))
diff --git a/src/iens.scm b/src/iens.scm
@@ -111,7 +111,7 @@
 
 (include "common.scm")
 
-(assert (= 2 (db-version)))
+(assert (= 3 (db-version)))
 
 ;;;;;;;;;;;;;;;;;;
 ;; Configuration