commit 73f292296bb8132dc3dc12aa394111b55112a8b7
parent 51e3c160f8a188b9a4b494bb683d4870567b3db2
Author: Natasha Kerensikova <natgh@instinctive.eu>
Date: Thu, 2 Apr 2026 17:59:11 +0000
Database schema is extracted into a common source file
Diffstat:
| A | src/common.scm | | | 70 | ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ |
| M | src/iens.scm | | | 57 | +-------------------------------------------------------- |
2 files changed, 71 insertions(+), 56 deletions(-)
diff --git a/src/common.scm b/src/common.scm
@@ -0,0 +1,70 @@
+; Copyright (c) 2023-2026, Natacha Porté
+;
+; Permission to use, copy, modify, and distribute this software for any
+; purpose with or without fee is hereby granted, provided that the above
+; copyright notice and this permission notice appear in all copies.
+;
+; THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
+; WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
+; MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
+; ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
+; WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
+; ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
+; OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
+
+;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
+;; Database Creation/Migration
+
+(define (db-version)
+ (query fetch-value (sql db "PRAGMA user_version;")))
+
+(when (null? (schema db))
+ (write-line "Initializing database with schema v2")
+ (for-each
+ (lambda (s) (exec (sql/transient db s)))
+ (list "CREATE TABLE config (key TEXT PRIMARY KEY, val);"
+ "CREATE TABLE tag (id INTEGER PRIMARY KEY,
+ name TEXT NOT NULL,
+ auto INTEGER DEFAULT 0);"
+ "CREATE TABLE entry (id INTEGER PRIMARY KEY,
+ url TEXT NOT NULL, type TEXT, description TEXT, notes TEXT,
+ protected INTEGER DEFAULT 0, ptime INTEGER,
+ ctime INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP,
+ mtime INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP);"
+ "CREATE TABLE tagrel (url_id REFERENCES entry(id)
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ tag_id REFERENCES tag(id)
+ ON UPDATE CASCADE ON DELETE CASCADE);"
+ "CREATE TABLE feed (id INTEGER PRIMARY KEY, filename TEXT NOT NULL,
+ url TEXT NOT NULL, selector TEXT NOT NULL,
+ title TEXT NOT NULL,
+ active INTEGER NOT NULL DEFAULT 1,
+ mtime INTEGER);"
+ "CREATE TABLE selector (id INTEGER PRIMARY KEY, text TEXT);"
+ "CREATE INDEX i_mtime ON entry(mtime);"
+ "CREATE INDEX i_pmtime ON entry(protected,mtime);"
+ "CREATE UNIQUE INDEX i_url ON entry(url);"
+ "CREATE UNIQUE INDEX i_tag ON tag(name);"
+ "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;")))
+
+(when (= 0 (db-version))
+ (write-line "Updating database schema from v0 to v1")
+ (assert (= 1 (query fetch-value
+ (sql db "SELECT val FROM config WHERE key = ?;")
+ "schema-version")))
+ (for-each
+ (lambda (s) (exec (sql/transient db s)))
+ (list "CREATE TABLE IF NOT EXISTS
+ selector (id INTEGER PRIMARY KEY, text TEXT);"
+ "DELETE FROM config WHERE key='schema-version';"
+ "PRAGMA user_version = 1;")))
+
+(when (= 1 (db-version))
+ (write-line "Updating database schema from v1 to v2")
+ (for-each
+ (lambda (s) (exec (sql/transient db s)))
+ (list "ALTER TABLE feed ADD COLUMN mtime INTEGER;"
+ "PRAGMA user_version = 2;")))
diff --git a/src/iens.scm b/src/iens.scm
@@ -109,62 +109,7 @@
(write-line (conc "Using database " db-name " with SQLite " library-version))
(exec (sql db "PRAGMA foreign_keys = ON;"))
-(define (db-version)
- (query fetch-value (sql db "PRAGMA user_version;")))
-
-;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
-;; Database Creation/Migration
-
-(when (null? (schema db))
- (write-line "Initializing database with schema v2")
- (for-each
- (lambda (s) (exec (sql/transient db s)))
- (list "CREATE TABLE config (key TEXT PRIMARY KEY, val);"
- "CREATE TABLE tag (id INTEGER PRIMARY KEY,
- name TEXT NOT NULL,
- auto INTEGER DEFAULT 0);"
- "CREATE TABLE entry (id INTEGER PRIMARY KEY,
- url TEXT NOT NULL, type TEXT, description TEXT, notes TEXT,
- protected INTEGER DEFAULT 0, ptime INTEGER,
- ctime INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP,
- mtime INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP);"
- "CREATE TABLE tagrel (url_id REFERENCES entry(id)
- ON UPDATE CASCADE ON DELETE CASCADE,
- tag_id REFERENCES tag(id)
- ON UPDATE CASCADE ON DELETE CASCADE);"
- "CREATE TABLE feed (id INTEGER PRIMARY KEY, filename TEXT NOT NULL,
- url TEXT NOT NULL, selector TEXT NOT NULL,
- title TEXT NOT NULL,
- active INTEGER NOT NULL DEFAULT 1,
- mtime INTEGER);"
- "CREATE TABLE selector (id INTEGER PRIMARY KEY, text TEXT);"
- "CREATE INDEX i_mtime ON entry(mtime);"
- "CREATE INDEX i_pmtime ON entry(protected,mtime);"
- "CREATE UNIQUE INDEX i_url ON entry(url);"
- "CREATE UNIQUE INDEX i_tag ON tag(name);"
- "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;")))
-
-(when (= 0 (db-version))
- (write-line "Updating database schema from v0 to v1")
- (assert (= 1 (query fetch-value
- (sql db "SELECT val FROM config WHERE key = ?;")
- "schema-version")))
- (for-each
- (lambda (s) (exec (sql/transient db s)))
- (list "CREATE TABLE IF NOT EXISTS
- selector (id INTEGER PRIMARY KEY, text TEXT);"
- "DELETE FROM config WHERE key='schema-version';"
- "PRAGMA user_version = 1;")))
-
-(when (= 1 (db-version))
- (write-line "Updating database schema from v1 to v2")
- (for-each
- (lambda (s) (exec (sql/transient db s)))
- (list "ALTER TABLE feed ADD COLUMN mtime INTEGER;"
- "PRAGMA user_version = 2;")))
+(include "common.scm")
(assert (= 2 (db-version)))
