iens

commit 31a190bb0b0649892e3f55612533b9561bc1f0a1
parent bbc6440eff0a4597f109063c13ead459800c4225
Author: Natasha Kerensikova <natgh@instinctive.eu>
Date:   Tue, 28 Jul 2026 20:12:12 +0000

Schema update for better source management
Diffstat:
M
src/cgi.scm
 | 
2
+-
M
src/common.scm
 | 
29
+++++++++++++++++++++++++----
M
src/get-gruiks.scm
 | 
4
++--
M
src/iens.scm
 | 
2
+-

4 files changed, 29 insertions(+), 8 deletions(-)
diff --git a/src/cgi.scm b/src/cgi.scm
@@ -292,7 +292,7 @@ END-OF-CSS
 
 (include "common.scm")
 
-(unless (= 7 (db-version))
+(unless (= 8 (db-version))
   (die "Unexpectad database version"))
 
 
diff --git a/src/common.scm b/src/common.scm
@@ -51,7 +51,7 @@
   (query fetch-value (sql db "PRAGMA user_version;")))
 
 (when (null? (schema db))
-  (write-line "Initializing database with schema v2")
+  (write-line "Initializing database with schema v8")
   (for-each
     (lambda (s) (exec (sql/transient db s)))
     (list "CREATE TABLE config (key TEXT PRIMARY KEY, val);"
@@ -95,8 +95,10 @@
              ctime INTEGER NOT NULL,
              mtime INTEGER NOT NULL,
              stime INTEGER,
+             lastseen INTEGER,
              entry_id INTEGER REFERENCES entry(id));"
-          "CREATE UNIQUE INDEX i_gruik ON gruik(position);"
+          "CREATE UNIQUE INDEX i_gruik ON gruik(position) WHERE position > 0;"
+          "CREATE INDEX i_gruik_mtime ON gruik(mtime);"
           "CREATE INDEX i_gruik_time ON gruik(ptime);"
           "CREATE INDEX i_gruik_url ON gruik(url);"
           "CREATE TABLE gruik_tags
@@ -107,9 +109,12 @@
           "CREATE TABLE source_rss
             (id INTEGER PRIMARY KEY,
              name TEXT NOT NULL,
-             url TEXT NOT NULL);"
+             url TEXT NOT NULL,
+             format INTEGER NOT NULL DEFAULT 0,
+             last_modified INTEGER,
+             etag TEXT);"
           "CREATE UNIQUE INDEX i_source_rss ON source_rss(name);"
-          "PRAGMA user_version = 4;")))
+          "PRAGMA user_version = 8;")))
 
 (when (= 0 (db-version))
   (write-line "Updating database schema from v0 to v1")
@@ -229,6 +234,22 @@
            WHERE source_url IS NOT NULL;"
           "PRAGMA user_version = 7;")))))
 
+(when (= 7 (db-version))
+  (with-transaction db
+    (lambda ()
+      (for-each
+        (lambda (s) (exec (sql/transient db s)))
+        (list
+          "CREATE INDEX i_gruik_mtime ON gruik(mtime);"
+          "DROP INDEX i_gruik;"
+          "CREATE UNIQUE INDEX i_gruik ON gruik(position) WHERE position > 0;"
+          "CREATE INDEX i_gruik_section ON gruik(section);"
+          "ALTER TABLE source_rss ADD COLUMN format INTEGER NOT NULL DEFAULT 0;"
+          "ALTER TABLE source_rss ADD COLUMN last_modified INTEGER;"
+          "ALTER TABLE source_rss ADD COLUMN etag TEXT;"
+          "ALTER TABLE gruik ADD COLUMN lastseen INTEGER;"
+          "PRAGMA user_version = 8;")))))
+
 ;;;;;;;;;;;;;;;;;;;;;;;;;
 ;; Database Utilitities
 
diff --git a/src/get-gruiks.scm b/src/get-gruiks.scm
@@ -43,7 +43,7 @@
 
 (include "common.scm")
 
-(assert (= 7 (db-version)))
+(assert (= 8 (db-version)))
 
 ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
 ;; Gruik build from sources
@@ -61,7 +61,7 @@
       (sql db "INSERT INTO gruik(position, notes, ptime,
                                  section, url, title, comment_url,
                                  mark, ctime, mtime)
-               VALUES (-?1, '', datetime(?1,'unixepoch'),
+               VALUES (-1, '', datetime(?1,'unixepoch'),
                        ?2, ?3, ?4, ?5,
                        ?6, ?1, ?1);")
       (query fetch-value
diff --git a/src/iens.scm b/src/iens.scm
@@ -100,7 +100,7 @@
 
 (include "common.scm")
 
-(assert (= 7 (db-version)))
+(assert (= 8 (db-version)))
 
 ;;;;;;;;;;;;;;;;;;
 ;; Configuration