assboard/data.lisp

(in-package :cl-user)

(defpackage #:assboard.data
  (:nicknames :data)
  (:use :cl :postmodern))

(in-package :assboard.data)

;; Install local-time timestamp reader
(local-time:set-local-time-cl-postgres-readers)

(defvar *db-host* "127.0.0.1" "database host name")
(defvar *db-user* "assboard" "database user")
(defvar *db-pass* "assboard" "database pass")
(defvar *db-name* "assboard" "database name")

(defun start-db-connection (&optional (database *db-name*)
                              (database-user *db-user*)
                              (database-password *db-pass*)
                              (host *db-host*))
  "Start the database connection. Reconnects if there is an unconnected
database in *database* which matches the database parameter in the function, it will
be reconnected. Returns boolean on whether the global *database* is now connected."
  (unless *database*
    (setf *database*
          (postmodern:connect database database-user database-password
                              host :pooled-p t))))

(defclass posting ()
  ((id :col-type serial :reader posting-id)
   (author :col-type string :accessor posting-author :initarg :author)
   (status :col-type string :accessor posting-status :initarg :status :col-default "open")
   (title :col-type string :accessor posting-title :initarg :title)
   (body :col-type string :accessor posting-body :initarg :body)
   (maintainer :col-type (or string db-null) :accessor posting-maintainer)
   (created-on :col-type timestamptz :reader posting-created-on :col-default (:now))
   (updated-on :col-type timestamptz :accessor posting-updated-on :col-default (:now)))
  (:metaclass dao-class)
  (:keys id))

(deftable posting
  (!dao-def)
  (!index 'status)
  (!index 'author)
  (!index 'maintainer))

(defclass raw ()
  ((id :col-type serial :reader raw-id)
   (crearsigned :col-type string :initarg :clearsigned)
   (fingerprint :col-type string :reader raw-fingerprint :initarg :fingerprint)
   (timestamp :col-type timestamptz :initarg :timestamp)
   (created-on :col-type timestamptz :col-default (:now))
   (remote-ip :col-type (or string db-null) :initform (ignore-errors (hunchentoot:real-remote-addr)))
   (remote-ua :col-type (or string db-null) :initform (ignore-errors (hunchentoot:user-agent))))
  (:metaclass dao-class)
  (:keys id))

(deftable raw
  (!dao-def))

(defclass posting-edition ()
  ((id :col-type serial :reader posting-edition-id)
   (posting-id :col-type integer :initarg :posting-id)
   (title :col-type string :initarg :title)
   (body :col-type string :initarg :body)
   (raw-id :col-type integer :initarg :raw-id))
  (:metaclass dao-class)
  (:keys id))

(deftable posting-edition
  (!dao-def)
  (!foreign 'posting :posting-id :primary-key)
  (!foreign 'raw :raw-id :primary-key))