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))))

(defun db-init ()
  (unless (probe-file (db-path))
    (sqlite:with-open-database (db (db-path))
      (sqlite:execute-non-query db "create table finance (ts, usd, eur, gbp, brent)")
      (sqlite:execute-non-query db "create index fin_ts_ids on finance (ts)"))))

(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)))))