common.scm (5310B)
1 ; Copyright (c) 2023-2026, Natacha Porté 2 ; 3 ; Permission to use, copy, modify, and distribute this software for any 4 ; purpose with or without fee is hereby granted, provided that the above 5 ; copyright notice and this permission notice appear in all copies. 6 ; 7 ; THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES 8 ; WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF 9 ; MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR 10 ; ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES 11 ; WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN 12 ; ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF 13 ; OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. 14 15 ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; 16 ;; Database Creation/Migration 17 18 (define (db-version) 19 (query fetch-value (sql db "PRAGMA user_version;"))) 20 21 (when (null? (schema db)) 22 (write-line "Initializing database with schema v2") 23 (for-each 24 (lambda (s) (exec (sql/transient db s))) 25 (list "CREATE TABLE config (key TEXT PRIMARY KEY, val);" 26 "CREATE TABLE tag (id INTEGER PRIMARY KEY, 27 name TEXT NOT NULL, 28 auto INTEGER DEFAULT 0);" 29 "CREATE TABLE entry (id INTEGER PRIMARY KEY, 30 url TEXT NOT NULL, type TEXT, description TEXT, notes TEXT, 31 protected INTEGER DEFAULT 0, ptime INTEGER, 32 ctime INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP, 33 mtime INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP);" 34 "CREATE TABLE tagrel (url_id REFERENCES entry(id) 35 ON UPDATE CASCADE ON DELETE CASCADE, 36 tag_id REFERENCES tag(id) 37 ON UPDATE CASCADE ON DELETE CASCADE);" 38 "CREATE TABLE feed (id INTEGER PRIMARY KEY, filename TEXT NOT NULL, 39 url TEXT NOT NULL, selector TEXT NOT NULL, 40 title TEXT NOT NULL, 41 active INTEGER NOT NULL DEFAULT 1, 42 mtime INTEGER);" 43 "CREATE TABLE selector (id INTEGER PRIMARY KEY, text TEXT);" 44 "CREATE INDEX i_mtime ON entry(mtime);" 45 "CREATE INDEX i_pmtime ON entry(protected,mtime);" 46 "CREATE UNIQUE INDEX i_url ON entry(url);" 47 "CREATE UNIQUE INDEX i_tag ON tag(name);" 48 "CREATE UNIQUE INDEX i_rel0 ON tagrel(url_id,tag_id);" 49 "CREATE INDEX i_rel1 ON tagrel(url_id);" 50 "CREATE INDEX i_rel2 ON tagrel(tag_id);" 51 "CREATE TABLE gruik 52 (id INTEGER PRIMARY KEY, 53 position INTEGER NOT NULL, 54 notes TEXT NOT NULL, 55 description TEXT, 56 ptime INTEGER NOT NULL, 57 section TEXT NOT NULL, 58 title TEXT NOT NULL, 59 url TEXT NOT NULL, 60 mark INTEGER NOT NULL DEFAULT 0, 61 ctime INTEGER NOT NULL, 62 mtime INTEGER NOT NULL, 63 stime INTEGER);" 64 "CREATE UNIQUE INDEX i_gruik ON gruik(position);" 65 "CREATE INDEX i_gruik_time ON gruik(ptime);" 66 "CREATE TABLE gruik_tags 67 (gruik_id REFERENCES gruik(id) ON UPDATE CASCADE ON DELETE CASCADE, 68 tag_id REFERENCES tag(id) ON UPDATE CASCADE ON DELETE CASCADE);" 69 "CREATE UNIQUE INDEX i_gruik_rel ON gruik_tags(gruik_id,tag_id);" 70 "CREATE INDEX i_gruik_tags ON gruik_tags(tag_id,gruik_id);" 71 "PRAGMA user_version = 3;"))) 72 73 (when (= 0 (db-version)) 74 (write-line "Updating database schema from v0 to v1") 75 (assert (= 1 (query fetch-value 76 (sql db "SELECT val FROM config WHERE key = ?;") 77 "schema-version"))) 78 (for-each 79 (lambda (s) (exec (sql/transient db s))) 80 (list "CREATE TABLE IF NOT EXISTS 81 selector (id INTEGER PRIMARY KEY, text TEXT);" 82 "DELETE FROM config WHERE key='schema-version';" 83 "PRAGMA user_version = 1;"))) 84 85 (when (= 1 (db-version)) 86 (write-line "Updating database schema from v1 to v2") 87 (for-each 88 (lambda (s) (exec (sql/transient db s))) 89 (list "ALTER TABLE feed ADD COLUMN mtime INTEGER;" 90 "PRAGMA user_version = 2;"))) 91 92 (when (= 2 (db-version)) 93 (for-each 94 (lambda (s) (exec (sql/transient db s))) 95 (list "CREATE TABLE gruik 96 (id INTEGER PRIMARY KEY, 97 position INTEGER NOT NULL, 98 notes TEXT NOT NULL, 99 description TEXT, 100 ptime INTEGER NOT NULL, 101 section TEXT NOT NULL, 102 title TEXT NOT NULL, 103 url TEXT NOT NULL, 104 mark INTEGER NOT NULL DEFAULT 0, 105 ctime INTEGER NOT NULL, 106 mtime INTEGER NOT NULL, 107 stime INTEGER);" 108 "CREATE UNIQUE INDEX i_gruik ON gruik(position);" 109 "CREATE INDEX i_gruik_time ON gruik(ptime);" 110 "CREATE TABLE gruik_tags 111 (gruik_id REFERENCES gruik(id) ON UPDATE CASCADE ON DELETE CASCADE, 112 tag_id REFERENCES tag(id) ON UPDATE CASCADE ON DELETE CASCADE);" 113 "CREATE UNIQUE INDEX i_gruik_rel ON gruik_tags(gruik_id,tag_id);" 114 "CREATE INDEX i_gruik_tags ON gruik_tags(tag_id,gruik_id);" 115 "PRAGMA user_version = 3;")))