1 files changed, 82 insertions, 0 deletions

diff --git a/src/db.lisp b/src/db.lisp
new file mode 100644
index 0000000..9b646d2
--- /dev/null
+++ b/src/db.lisp
@@ -0,0 +1,82 @@
+;; SPDX-License-Identifier: EUPL-1.2
+;; SPDX-FileCopyrightText: 2025 Uko Kokņevičs <perkontevs@gmail.com>
+(defpackage :ukkoclot/db
+  (:use :c2cl :sqlite :ukkoclot/log)
+  (:export :get-inline-bot-type :set-inline-bot-type :with-db))
+(in-package :ukkoclot/db)
+
+(defparameter +target-version+ 1
+  "Intended DB version")
+
+(defmacro with-db ((name path) &body body)
+  `(let ((,name (connect ,path)))
+     (unwind-protect (progn (upgrade ,name) ,@body)
+       (disconnect ,name))))
+
+(defun get-inline-bot-type (db id)
+  (let ((type-int (execute-single db "SELECT type FROM inline_bots WHERE id = ?" id)))
+    (when type-int
+      (integer->inline-bot-type type-int))))
+
+(defun set-inline-bot-type (db id type)
+  (execute-non-query db "INSERT OR REPLACE INTO inline_bots (id, type) VALUES (?, ?)" id (inline-bot-type->integer type)))
+
+(defun inline-bot-type->integer (type)
+  (case type
+    (:blacklisted 0)
+    (:whitelisted 1)
+    (t (error "Unknown inline bot type ~S" type))))
+
+(defun integer->inline-bot-type (num)
+  (case num
+    (0 :blacklisted)
+    (1 :whitelisted)
+    (t (error "Unknown inline bot type value ~S" num))))
+
+(defun upgrade (db)
+  (execute-non-query db "CREATE TABLE IF NOT EXISTS version(id INTEGER PRIMARY KEY, version INTEGER)")
+  (let ((current-ver (execute-single db "SELECT version FROM version WHERE id = 0")))
+    (unless current-ver
+      (setf current-ver 0))
+    (cond
+      ((= current-ver +target-version+) (log-info "Database is up to date"))
+
+      ((> current-ver +target-version+)
+       (log-error "Database has a higher version than supported?")
+       (error "Corrupted Database"))
+
+      (t
+       (log-info "Updating database from version ~A to ~A" current-ver +target-version+)
+       (loop while (< current-ver +target-version+)
+             do (with-transaction db
+                  (log-info "Updating database step from ~A" current-ver)
+                  (incf current-ver)
+                  (upgrade-step db current-ver)
+                  (execute-non-query
+                   db
+                   "INSERT OR REPLACE INTO version(id, version) VALUES (0, ?)"
+                   current-ver)))
+       (log-info "Database updating complete :)")))))
+
+(defun upgrade-step (db new-version)
+  (case new-version
+    (1
+     (execute-non-query db "DROP TABLE IF EXISTS inline_bots_enum")
+     (execute-non-query db "
+CREATE TABLE inline_bots_enum (
+  id INTEGER PRIMARY KEY,
+  value TEXT UNIQUE
+)")
+     (execute-non-query db "
+INSERT INTO inline_bots_enum(id, value)
+VALUES (?, 'blacklisted'), (?, 'whitelisted')"
+                        (inline-bot-type->integer :blacklisted)
+                        (inline-bot-type->integer :whitelisted))
+
+     (execute-non-query db "DROP TABLE IF EXISTS inline_bots")
+     (execute-non-query db "
+CREATE TABLE inline_bots (
+  id INTEGER PRIMARY KEY,
+  type INTEGER REFERENCES inline_bots_enum(id)
+)"))
+    (t (error "Unreachable upgrade step reached ~A" new-version))))