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)
     (sqlite:execute-non-query ,db "PRAGMA foreign_keys = ON")
     ,@body))

(defun db-init ()
  (with-db (db)
    ;; 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)")

    ;; RSS
    (sqlite:execute-non-query db "create table if not exists rss_feeds (id INTEGER PRIMARY KEY, url, title, next_fetch, period)")
    (sqlite:execute-non-query db "create unique index if not exists rss_feeds_url_idx on rss_feeds (url)")
    (sqlite:execute-non-query db "create table if not exists rss_items (id INTEGER PRIMARY KEY, feed_id REFERENCES rss_feeds, guid, link, title, published)")
    (sqlite:execute-non-query db "create index if not exists rss_items_idx on rss_items (feed_id, guid)")

    (sqlite:execute-non-query db "create table if not exists rss_chat_feeds (chat_id, feed_id REFERENCES rss_feeds)")
    (sqlite:execute-non-query db "create index if not exists rss_chat_feeds_chat_idx on rss_chat_feeds (chat_id)")
    (sqlite:execute-non-query db "create index if not exists rss_chat_feeds_feed_idx on rss_chat_feeds (feed_id)")))

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

(defun db-get-last-finance ()
  (with-db (db)
    (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)))
    (with-db (db)
      (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")))


;; RSS
(defun %make-feed (row)
  (when row
    (make-feed :id (nth 0 row)
               :url (nth 1 row)
               :title (nth 2 row)
               :next-fetch (when (nth 3 row) (local-time:unix-to-timestamp (nth 3 row)))
               :period (nth 4 row))))

(defun db-rss-get-feed-by-url (url)
  (with-db (db)
    (%make-feed (car (sqlite:execute-to-list db "select id, url, title, next_fetch, period from rss_feeds where url = ?" url)))))

(defun db-rss-add-feed (feed)
  (with-db (db)
    (sqlite:execute-non-query db "insert into rss_feeds (url, title, next_fetch, period) values (?, ?, ?, ?)"
                              (feed-url feed)
                              (feed-title feed)
                              (feed-next-fetch-unix feed)
                              (feed-period feed))
    (setf (feed-id feed) (sqlite:last-insert-rowid db))
    feed))

(defun db-rss-update-feed (feed)
  (with-db (db)
    (sqlite:execute-non-query db "update rss_feeds set title = ?, next_fetch = ?, period = ? where id = ?"
                              (feed-title feed)
                              (feed-next-fetch-unix feed)
                              (feed-period feed)
                              (feed-id feed))))

(defun db-rss-get-active-feeds ()
  (with-db (db)
    (mapcar #'%make-feed
            (sqlite:execute-to-list db "select id, url, title, next_fetch, period from rss_feeds where exists (select 1 from rss_chat_feeds where feed_id=id)"))))

(defun db-rss-get-chat-feeds (chat-id)
  (with-db (db)
    (mapcar #'%make-feed
            (sqlite:execute-to-list db "select id, url, title, next_fetch, period from rss_feeds where id in (select feed_id from rss_chat_feeds where chat_id  = ?)" chat-id))))

(defun db-rss-get-feed-chats (feed)
  (flatten (with-db (db)
             (sqlite:execute-to-list db "select chat_id from rss_chat_feeds where feed_id = ?"
                                     (feed-id feed)))))

(defun db-rss-set-chat-feeds (chat-id feeds)
  (with-db (db)
    (sqlite:with-transaction db
      (sqlite:execute-non-query db "delete from rss_chat_feeds where chat_id = ?" chat-id)
      (dolist (feed feeds)
        (sqlite:execute-non-query db "insert into rss_chat_feeds (chat_id, feed_id) values (?, ?)" chat-id (feed-id feed))))))

(defun db-rss-item-exists (item)
  (with-db (db)
    (sqlite:execute-single db "select id from rss_items where feed_id = ? and guid = ? limit 1"
                           (feed-id (feed-item-feed item))
                           (feed-item-guid item))))

(defun db-rss-add-item (item)
  (with-db (db)
    (sqlite:execute-non-query db "insert into rss_items (feed_id, guid, link, title, published) values (?, ?, ?, ?, ?)"
                              (feed-id (feed-item-feed item))
                              (feed-item-guid item)
                              (feed-item-link item)
                              (feed-item-title item)
                              (feed-item-published-unix item))))

(defun %make-feed-item (feed row)
  (when row
    (make-feed-item :feed feed
                    :guid (nth 0 row)
                    :link (nth 1 row)
                    :title (nth 2 row)
                    :published (when (nth 3 row) (local-time:unix-to-timestamp (nth 3 row))))))

(defun db-rss-last-feed-items (feed &optional (limit 10))
  (with-db (db)
    (mapcar #'(lambda (row) (%make-feed-item feed row))
            (sqlite:execute-to-list db "select guid, link, title, published from rss_items where feed_id = ? order by published desc, id desc limit ?" (feed-id feed) limit))))