313 lines
12 KiB
EmacsLisp
313 lines
12 KiB
EmacsLisp
;;; noise-db.el --- SQLite database for Noise signal client -*- lexical-binding: t; -*-
|
|
|
|
;;; Commentary:
|
|
|
|
;; Manages the local SQLite database for contacts, conversations, and messages.
|
|
|
|
;;; Code:
|
|
|
|
(require 'cl-lib)
|
|
|
|
(defcustom noise-db-file (expand-file-name "noise/noise.db" user-emacs-directory)
|
|
"Path to the Noise SQLite database file."
|
|
:type 'file
|
|
:group 'noise)
|
|
|
|
(defvar noise-db--connection nil
|
|
"The active SQLite database connection.")
|
|
|
|
(defun noise-db-init ()
|
|
"Initialize the Noise database.
|
|
Creates the database file and tables if they do not exist. Idempotent."
|
|
(noise-db--open)
|
|
(noise-db--create-schema))
|
|
|
|
(defun noise-db--open ()
|
|
"Open (or return existing) database connection."
|
|
(unless (and noise-db--connection
|
|
(sqlitep noise-db--connection))
|
|
(let ((dir (file-name-directory noise-db-file)))
|
|
(unless (file-exists-p dir)
|
|
(make-directory dir t)))
|
|
(setq noise-db--connection (sqlite-open noise-db-file))))
|
|
|
|
(defun noise-db--close ()
|
|
"Close the database connection if open."
|
|
(when (and noise-db--connection (sqlitep noise-db--connection))
|
|
(sqlite-close noise-db--connection)
|
|
(setq noise-db--connection nil)))
|
|
|
|
(defun noise-db--create-schema ()
|
|
"Create all required tables if they do not exist."
|
|
(noise-db--exec
|
|
"CREATE TABLE IF NOT EXISTS contacts (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL DEFAULT '',
|
|
color TEXT DEFAULT '',
|
|
profile_name TEXT DEFAULT '',
|
|
last_fetch INTEGER DEFAULT 0
|
|
)")
|
|
(noise-db--exec
|
|
"CREATE TABLE IF NOT EXISTS conversations (
|
|
id TEXT PRIMARY KEY,
|
|
type TEXT NOT NULL CHECK(type IN ('direct', 'group')),
|
|
name TEXT NOT NULL DEFAULT '',
|
|
last_message_preview TEXT DEFAULT '',
|
|
last_message_time INTEGER DEFAULT 0,
|
|
last_message_sender TEXT DEFAULT '',
|
|
unread_count INTEGER DEFAULT 0,
|
|
is_archived INTEGER DEFAULT 0,
|
|
expiration_timer INTEGER DEFAULT 0
|
|
)")
|
|
(noise-db--exec
|
|
"CREATE TABLE IF NOT EXISTS messages (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
conversation_id TEXT NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
|
|
timestamp INTEGER NOT NULL,
|
|
source TEXT NOT NULL DEFAULT '',
|
|
body TEXT DEFAULT '',
|
|
type TEXT NOT NULL DEFAULT 'text',
|
|
receipt_state TEXT DEFAULT '',
|
|
has_attachment INTEGER DEFAULT 0,
|
|
attachment_path TEXT DEFAULT ''
|
|
)")
|
|
(noise-db--exec
|
|
"CREATE TABLE IF NOT EXISTS conversation_members (
|
|
conversation_id TEXT NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
|
|
contact_id TEXT NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (conversation_id, contact_id)
|
|
)")
|
|
(noise-db--exec
|
|
"CREATE INDEX IF NOT EXISTS idx_messages_conversation_time
|
|
ON messages(conversation_id, timestamp)")
|
|
(noise-db--exec
|
|
"CREATE INDEX IF NOT EXISTS idx_conversations_time
|
|
ON conversations(last_message_time)"))
|
|
|
|
(defun noise-db--exec (sql &rest args)
|
|
"Execute SQL with ARGS and return nil."
|
|
(sqlite-execute noise-db--connection sql args))
|
|
|
|
(defvar noise-db--column-cache (make-hash-table :test 'equal)
|
|
"Cache of table name to column list mappings.")
|
|
|
|
(defun noise-db--query (sql &rest args)
|
|
"Execute SQL query with ARGS and return all rows as a list of hash tables.
|
|
Each row is a hash table with column names as keys.
|
|
For `count(*)` style queries, falls back to raw list rows."
|
|
(let ((rows (sqlite-select noise-db--connection sql args)))
|
|
(when rows
|
|
(let ((columns (noise-db--column-names sql)))
|
|
(if columns
|
|
(mapcar (lambda (row)
|
|
(let ((ht (make-hash-table :test 'equal :size (length columns))))
|
|
(seq-do-indexed
|
|
(lambda (val idx)
|
|
(puthash (nth idx columns) val ht))
|
|
row)
|
|
ht))
|
|
rows)
|
|
rows)))))
|
|
|
|
(defun noise-db--column-names (sql)
|
|
"Extract column names from a SELECT SQL statement.
|
|
For `SELECT *` queries, uses PRAGMA table_info to get column names."
|
|
(let ((case-fold-search nil))
|
|
(cond
|
|
;; SELECT c.* FROM conversations c ...
|
|
((string-match "SELECT[[:space:]]+DISTINCT[[:space:]]+c\\.\\*" sql)
|
|
(noise-db--table-columns "conversations"))
|
|
;; SELECT * FROM tablename
|
|
((string-match "SELECT[[:space:]]+\\*[[:space:]]+FROM[[:space:]]+\\([^[:space:]]+\\)" sql)
|
|
(noise-db--table-columns (match-string 1 sql)))
|
|
;; SELECT explicit columns, FROM
|
|
((string-match "SELECT[[:space:]]+\\(.*?\\)[[:space:]]+FROM" sql)
|
|
(noise-db--parse-column-list (match-string 1 sql))))))
|
|
|
|
(defun noise-db--parse-column-list (cols-str)
|
|
"Parse a comma-separated column list into a list of column names."
|
|
(mapcar (lambda (c)
|
|
(let ((trimmed (string-trim c)))
|
|
(cond
|
|
;; "col AS alias"
|
|
((string-match "[Aa][Ss][[:space:]]+\\([^ ,]+\\)$" trimmed)
|
|
(match-string 1 trimmed))
|
|
;; "table.col"
|
|
((string-match "\\.\\([^ ]+\\)$" trimmed)
|
|
(match-string 1 trimmed))
|
|
(t trimmed))))
|
|
(split-string cols-str ",")))
|
|
|
|
(defun noise-db--table-columns (table-name)
|
|
"Get column names for TABLE-NAME using PRAGMA table_info."
|
|
(or (gethash table-name noise-db--column-cache)
|
|
(let ((info (sqlite-select noise-db--connection
|
|
(format "PRAGMA table_info(%s)" table-name))))
|
|
(when info
|
|
(let ((cols (mapcar (lambda (row) (nth 1 row)) info)))
|
|
(puthash table-name cols noise-db--column-cache)
|
|
cols)))))
|
|
|
|
(cl-defun noise-db-upsert-contact (id &key name color profile-name)
|
|
"Insert or update a contact with ID."
|
|
(noise-db--exec
|
|
"INSERT INTO contacts (id, name, color, profile_name, last_fetch)
|
|
VALUES (?, ?, ?, ?, ?)
|
|
ON CONFLICT(id) DO UPDATE SET
|
|
name = COALESCE(NULLIF(?, ''), name),
|
|
color = COALESCE(NULLIF(?, ''), color),
|
|
profile_name = COALESCE(NULLIF(?, ''), profile_name),
|
|
last_fetch = ?"
|
|
id (or name "") (or color "") (or profile-name "") (floor (float-time))
|
|
(or name "") (or color "") (or profile-name "") (floor (float-time))))
|
|
|
|
(defun noise-db-get-contact (id)
|
|
"Get a single contact by ID. Returns nil if not found."
|
|
(car (noise-db--query "SELECT * FROM contacts WHERE id = ?" id)))
|
|
|
|
(defun noise-db-get-all-contacts ()
|
|
"Get all contacts ordered by name."
|
|
(noise-db--query "SELECT * FROM contacts ORDER BY name COLLATE NOCASE"))
|
|
|
|
(cl-defun noise-db-upsert-conversation (id &key type name members last-message-preview
|
|
last-message-time last-message-sender
|
|
unread-count is-archived expiration-timer)
|
|
"Insert or update a conversation with ID.
|
|
MEMBERS is a list of contact IDs for the conversation."
|
|
(let ((existing (noise-db-get-conversation id)))
|
|
(if existing
|
|
(noise-db--exec
|
|
"UPDATE conversations SET
|
|
name = COALESCE(NULLIF(?, ''), name),
|
|
last_message_preview = COALESCE(NULLIF(?, ''), last_message_preview),
|
|
last_message_time = COALESCE(?, last_message_time),
|
|
last_message_sender = COALESCE(NULLIF(?, ''), last_message_sender),
|
|
unread_count = COALESCE(?, unread_count),
|
|
is_archived = COALESCE(?, is_archived),
|
|
expiration_timer = COALESCE(?, expiration_timer)
|
|
WHERE id = ?"
|
|
(or name "") (or last-message-preview "")
|
|
(or last-message-time (gethash "last_message_time" existing))
|
|
(or last-message-sender "")
|
|
unread-count is-archived expiration-timer
|
|
id)
|
|
(noise-db--exec
|
|
"INSERT INTO conversations (id, type, name, last_message_preview, last_message_time,
|
|
last_message_sender, unread_count, is_archived, expiration_timer)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
|
|
id (or type "direct") (or name "") (or last-message-preview "")
|
|
(or last-message-time 0) (or last-message-sender "")
|
|
(or unread-count 0) (or is-archived 0) (or expiration-timer 0))))
|
|
(when members
|
|
(noise-db--exec "DELETE FROM conversation_members WHERE conversation_id = ?" id)
|
|
(dolist (member-id members)
|
|
(noise-db--exec
|
|
"INSERT OR IGNORE INTO conversation_members (conversation_id, contact_id) VALUES (?, ?)"
|
|
id member-id))))
|
|
|
|
(defun noise-db-get-conversation (id)
|
|
"Get a single conversation by ID. Returns nil if not found."
|
|
(car (noise-db--query "SELECT * FROM conversations WHERE id = ?" id)))
|
|
|
|
(defun noise-db-get-all-conversations ()
|
|
"Get all conversations ordered by last message time descending."
|
|
(noise-db--query
|
|
"SELECT * FROM conversations ORDER BY last_message_time DESC"))
|
|
|
|
(cl-defun noise-db-insert-message (conversation-id &key timestamp source body type
|
|
receipt-state has-attachment attachment-path)
|
|
"Insert a message into the messages table."
|
|
(noise-db--exec
|
|
"INSERT INTO messages (conversation_id, timestamp, source, body, type, receipt_state,
|
|
has_attachment, attachment_path)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
|
|
conversation-id timestamp (or source "")
|
|
(or body "") (or type "text") (or receipt-state "")
|
|
(if has-attachment 1 0) (or attachment-path ""))
|
|
(let ((preview (or body ""))
|
|
(sender (or source "")))
|
|
(noise-db--exec
|
|
"UPDATE conversations
|
|
SET last_message_preview = ?,
|
|
last_message_time = ?,
|
|
last_message_sender = ?
|
|
WHERE id = ?"
|
|
preview timestamp sender conversation-id)))
|
|
|
|
(defun noise-db-get-messages (conversation-id &rest kwargs)
|
|
"Get messages for CONVERSATION-ID, ordered by timestamp.
|
|
Keyword arguments: :limit N, :offset N."
|
|
(let* ((limit (plist-get kwargs :limit))
|
|
(offset (plist-get kwargs :offset))
|
|
(sql-str "SELECT * FROM messages WHERE conversation_id = ? ORDER BY timestamp")
|
|
(sql-params (list conversation-id)))
|
|
(when limit
|
|
(setq sql-str (concat sql-str " LIMIT ?"))
|
|
(setq sql-params (nconc sql-params (list limit))))
|
|
(when offset
|
|
(setq sql-str (concat sql-str " OFFSET ?"))
|
|
(setq sql-params (nconc sql-params (list offset))))
|
|
(apply #'noise-db--query sql-str sql-params)))
|
|
|
|
(defun noise-db-mark-conversation-read (id)
|
|
"Set the unread count of conversation ID to zero."
|
|
(noise-db--exec "UPDATE conversations SET unread_count = 0 WHERE id = ?" id))
|
|
|
|
(defun noise-db-increment-unread (id)
|
|
"Increment the unread count of conversation ID by one."
|
|
(noise-db--exec
|
|
"UPDATE conversations SET unread_count = unread_count + 1 WHERE id = ?" id))
|
|
|
|
(defun noise-db-set-last-message-sender (id sender)
|
|
"Set the last-message SENDER shown in previews for conversation ID."
|
|
(noise-db--exec
|
|
"UPDATE conversations SET last_message_sender = ? WHERE id = ?" sender id))
|
|
|
|
(defun noise-db-update-receipt-state (timestamp state)
|
|
"Upgrade the receipt state of own messages with TIMESTAMP to STATE.
|
|
Only moves forward (sent < delivered < read), never downgrades."
|
|
(noise-db--exec
|
|
"UPDATE messages SET receipt_state = ?
|
|
WHERE source = 'me' AND timestamp = ?
|
|
AND (CASE receipt_state
|
|
WHEN 'read' THEN 3 WHEN 'delivered' THEN 2 WHEN 'sent' THEN 1
|
|
ELSE 0 END)
|
|
< (CASE ?
|
|
WHEN 'read' THEN 3 WHEN 'delivered' THEN 2 WHEN 'sent' THEN 1
|
|
ELSE 0 END)"
|
|
state timestamp state))
|
|
|
|
(defun noise-db-get-conversation-members (conversation-id)
|
|
"Get member contact IDs for CONVERSATION-ID."
|
|
(noise-db--query
|
|
"SELECT contact_id FROM conversation_members WHERE conversation_id = ?"
|
|
conversation-id))
|
|
|
|
(defun noise-db-conversations-for-contact (contact-id)
|
|
"Get all conversations that include CONTACT-ID."
|
|
(noise-db--query
|
|
"SELECT c.* FROM conversations c
|
|
JOIN conversation_members cm ON c.id = cm.conversation_id
|
|
WHERE cm.contact_id = ?
|
|
ORDER BY c.last_message_time DESC"
|
|
contact-id))
|
|
|
|
(defun noise-db-search-contacts (query)
|
|
"Search contacts by name or phone number matching QUERY."
|
|
(noise-db--query
|
|
"SELECT * FROM contacts WHERE name LIKE ? OR id LIKE ? ORDER BY name COLLATE NOCASE"
|
|
(concat "%" query "%") (concat "%" query "%")))
|
|
|
|
(defun noise-db-search-conversations (query)
|
|
"Search conversations by name or member names matching QUERY."
|
|
(noise-db--query
|
|
"SELECT DISTINCT c.* FROM conversations c
|
|
LEFT JOIN conversation_members cm ON c.id = cm.conversation_id
|
|
LEFT JOIN contacts co ON cm.contact_id = co.id
|
|
WHERE c.name LIKE ? OR co.name LIKE ?
|
|
ORDER BY c.last_message_time DESC"
|
|
(concat "%" query "%") (concat "%" query "%")))
|
|
|
|
(provide 'noise-db)
|
|
;;; noise-db.el ends here
|