chatikbot/db.lisp

(in-package #:chatikbot)

(defvar *db-name* "db.sqlite" "SQLite database name")

(defun db-path ()
  (merge-pathnames *db-name*
                   (asdf:component-pathname
                    (asdf:find-system '#:chatikbot))))

(defmacro with-db ((db) &body body)
  `(sqlite:with-open-database (,db (db-path) :busy-timeout 30)
     (sqlite:execute-non-query ,db "PRAGMA foreign_keys = ON")
     ,@body))

(defun %db-execute (db sql &rest parameters)
  (apply #'sqlite:execute-non-query db sql parameters))
(defun %db-select (db sql &rest parameters)
  (apply #'sqlite:execute-to-list db sql parameters))
(defun %db-single (db sql &rest parameters)
  (apply #'sqlite:execute-single db sql parameters))

(defun db-execute (sql &rest parameters)
  (with-db (db)
    (apply #'%db-execute db sql parameters)
    (sqlite:last-insert-rowid db)))

(defun db-select (sql &rest parameters)
  (with-db (db)
    (apply #'%db-select db sql parameters)))

(defun db-single (sql &rest parameters)
  (with-db (db)
    (apply #'%db-single db sql parameters)))

(defmacro db-transaction ((db) &body body)
  `(with-db (,db)
     (sqlite:with-transaction ,db ,@body)))

(defmacro def-db-init ((db) &body body)
  `(add-hook :db-init #'(lambda (,db)
                          (handler-case (progn ,@body)
                            (error (e) (log:error e)))
                          (values))))

(defun db-init ()
  (with-db (db)
    (%db-execute db "create table if not exists settings (var, val)")
    (%db-execute db "create unique index if not exists settings_var_unique on settings (var)")))