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 10)
     ,@body))

(defun db-init ()
  (sqlite:with-open-database (db (db-path))
    ;; Finance
    (sqlite:execute-non-query db "create table if not exists finance (ts, usd, eur, gbp, brent)")
    (sqlite:execute-non-query db "create index if not exists fin_ts_ids on finance (ts)")

    ;; Foursquare
    (sqlite:execute-non-query db "create table if not exists fsq_chat_users (chat_id, user_id)")
    (sqlite:execute-non-query db "create index if not exists fsq_chat_users_chat_idx on fsq_chat_users (chat_id)")
    (sqlite:execute-non-query db "create index if not exists fsq_chat_users_user_idx on fsq_chat_users (user_id)")

    (sqlite:execute-non-query db "create table if not exists fsq_seen (id, created_at)")
    (sqlite:execute-non-query db "create index if not exists fsq_seen_idx on fsq_seen (id)")))

;; Finance
(defun db-add-finance (ts usd eur gbp brent)
  (sqlite:with-open-database (db (db-path) :busy-timeout 10)
    (sqlite:execute-non-query db "insert into finance (ts, usd, eur, gbp, brent) values (?, ?, ?, ?, ?)"
                              ts usd eur gbp brent)))

(defun db-get-last-finance ()
  (sqlite:with-open-database (db (db-path) :busy-timeout 10)
    (sqlite:execute-one-row-m-v db "select ts, usd, eur, gbp, brent from finance order by ts desc limit 1")))

(defun %finance-alist (statement)
  (let ((names (sqlite:statement-column-names statement))
        (map '(("usd" . "USD/RUB") ("eur" . "EUR/RUB") ("gbp" . "GBP/RUB") ("brent" . "Brent"))))
    (cons (sqlite:statement-column-value statement 0)
          (loop
             for i from 1 below (length names)
             for col in (rest names)
             collect (cons (aget col map) (sqlite:statement-column-value statement i))))))

(defun db-get-series (after-ts &optional usd eur gbp brent (avg 60))
  (let ((sql (format nil
                     "select ts/~a*~a~:[~;,avg(usd) as usd~]~:[~;,avg(eur) as eur~]~:[~;,avg(gbp) as gbp~]~:[~;,avg(brent) as brent~] from finance where ts >= ? group by ts/~a order by ts"
                     avg avg usd eur gbp brent avg)))
    (sqlite:with-open-database (db (db-path) :busy-timeout 10)
      (loop
         with statement = (sqlite:prepare-statement db sql)
         initially (sqlite:bind-parameter statement 1 (local-time:timestamp-to-unix after-ts))
         while (sqlite:step-statement statement)
         collect (%finance-alist statement)
         finally (sqlite:finalize-statement statement)))))

;; Foursquare
(defun db-fsq-get-chat-users (chat-id)
  (flatten (with-db (db)
             (sqlite:execute-to-list db "select user_id from fsq_chat_users where chat_id = ?" chat-id))))

(defun db-fsq-get-user-chats (user-id)
  (flatten (with-db (db)
             (sqlite:execute-to-list db "select chat_id from fsq_chat_users where user_id = ?" user-id))))

(defun db-fsq-set-chat-users (chat-id users)
  (with-db (db)
    (sqlite:with-transaction db
      (sqlite:execute-non-query db "delete from fsq_chat_users where chat_id = ?" chat-id)
      (dolist (user-id users)
        (sqlite:execute-non-query db "insert into fsq_chat_users (chat_id, user_id) values (?, ?)" chat-id user-id)))))

(defun db-fsq-add-seen (id created-at)
  (with-db (db)
    (sqlite:execute-non-query db "insert into fsq_seen (id, created_at) values (?, ?)" id created-at)))

(defun db-fsq-has-seen (id)
  (with-db (db)
    (sqlite:execute-single db "select id from fsq_seen where id = ? limit 1" id)))

(defun db-fsq-last-created ()
  (with-db (db)
    (sqlite:execute-single db "select created_at from fsq_seen order by created_at desc limit 1")))