8d3e31ef56
We're now using clack, lack, ningle, and websocket-driver. Quite a few more dependencies.
54 lines
1.6 KiB
Common Lisp
54 lines
1.6 KiB
Common Lisp
(in-package #:live-chat-db)
|
|
|
|
(defvar *db* nil
|
|
"Database connection object.")
|
|
|
|
(defvar *db-lock* (bt:make-lock)
|
|
"Lock to ensure thread-safe access to database.")
|
|
|
|
(defmacro with-database (conn &body body)
|
|
"Open the SQLite database."
|
|
`(let ((*db* ,conn))
|
|
(bt:with-lock-held (*db-lock*)
|
|
,@body)))
|
|
|
|
(defun db ()
|
|
(dbi:connect-cached
|
|
:sqlite3 :database-name
|
|
(asdf:system-relative-pathname :live-chat "chat.db")))
|
|
|
|
(defun create-messages-table ()
|
|
"Create the messages table if it doesn't exist."
|
|
(with-database (db)
|
|
(let ((query (dbi:prepare
|
|
*db*
|
|
"CREATE TABLE IF NOT EXISTS messages (id
|
|
INTEGER PRIMARY KEY AUTOINCREMENT,content TEXT NOT NULL)")))
|
|
(dbi:execute query (list)))))
|
|
|
|
(defun insert-message (message)
|
|
"Insert a new message into the database."
|
|
(with-database (db)
|
|
(let ((query (dbi:prepare
|
|
*db*
|
|
"INSERT INTO messages (content) VALUES (?)")))
|
|
(dbi:execute query (list message))
|
|
message)))
|
|
|
|
(defun fetch-messages ()
|
|
"Fetch all messages from the database."
|
|
(with-database (db)
|
|
(let* ((query (dbi:prepare *db*
|
|
"SELECT content FROM messages ORDER BY id"))
|
|
(query (dbi:execute query (list)))
|
|
(results (loop for row = (dbi:fetch query)
|
|
while row
|
|
collect (getf row :|content|))))
|
|
(reverse results))))
|
|
|
|
(defun clear-messages ()
|
|
"Clear all messages from the database."
|
|
(with-database (db)
|
|
(let ((query (dbi:prepare *db* "DELETE FROM messages")))
|
|
(dbi:execute query (list)))))
|