postmodern-t
[Special Characters][A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [Q] [R] [S] [T] [U] [V] [W] [X] [Y] [Z]
Note: There are two libraries used below: local-time and simple-date. Your choice will impact how times and dates are stored in your database. Make very clear on which library you are using.
Threads
While postmodern is generally thread-safe, it is up to the developer to pay attention and remember that postgresql may be spinning off processes at the same time that you are creating threads in your application.
Something like taking from the postmodern/tests.lisp:
(defclass test-data ()
((id :col-type serial :initarg :id :accessor test-id)
(a :col-type (or (varchar 100) db-null) :initarg :a :accessor test-a)
(b :col-type boolean :col-default nil :initarg :b :accessor test-b)
(c :col-type integer :col-default 0 :initarg :c :accessor test-c))
(:metaclass dao-class)
(:table-name dao-test)
(:keys id))
(execute (dao-table-definition 'test-data))
(defvar *dao-update-lock* (bt:make-lock))
(let ((item (make-instance 'test-data :a "SC" :b t :c 0)))
(with-test-connection (save-dao item))
(let ((id (test-id item)))
(loop for x from 1 to 50 do
(bt:make-thread
(lambda () (with-test-connection
(loop repeat 1000 do (bt:with-lock-held (*dao-update-lock*)
(incf (test-c item) 1))
(save-dao item))
(loop repeat 1000 do (bt:with-lock-held (*dao-update-lock*)
(decf (test-c item) 1))
(save-dao item))))))
(with-test-connection
(describe (get-dao 'test-data id)))))
may return to your control before all the processes are done. As a result, if you check for value of:
(test-c (get-dao 'dao-test 1))
when the lisp code returns, you may be surprised that the answer is not 0. Check a few seconds, later and it may be a different number. If you call
(pomo:list-connections)
or
(query (:select '* :from 'pg-stat-activity))
you may notice that there are still outstanding connection - postgresql is still working its way through the processes you just created and it will get through all of them and you will notice that the value has been finally incremented and decremented down to 0.
Time Functions (now, current-timestamp, current-date, date-trunc, date-part)
Using Local-time library (recommended)
Now
(query (:select (:now)))
(("2012-11-18 08:37:49.874461-08"))
Current-Timestamp
(query (:select (:current-timestamp)))
(("2012-11-18 08:41:15.435901-08"))
Current-Date
(query (:select (:current-date)))
((3562185600)) 1
(query (:select 'current-date (:type "now" :time)))
((3562185600 "08:42:11.345821"))
(query (:select (:to-char 'updated-at "YYYY-MM-DD HH24:MI:SS")
:from 'countries
:where (:= 'name "Nevis")))
(("2011-01-22 15:20:15"))
Date-trunc
(query (:select (:date-trunc "minute" (:now))) )(("2012-11-18 08:46:00-08"))
Date-part
(query (:select (:date-part "year" 'updated-at) :from 'countries :where (:= 'name "Nevis")) :single) 2011.0d0
Simple-date library
(query (:select 'current-time (:type "now" :time)))
(("05:58:04.167966-08" "05:58:04.167966"))
(query (:select (:now)))
((#<SIMPLE-DATE:TIMESTAMP 22-01-2011T15:06:06,756>))
(query (:select (:current-timestamp)))
((#<SIMPLE-DATE:TIMESTAMP 22-01-2011T15:06:13,295>))
(query (:select (:current-date) (:type "now" :time)))
((#<SIMPLE-DATE:DATE 22-01-2011> "07:08:14.350286"))
(query (:select (:to-char 'updated-at "YYYY-MM-DD HH24:MI:SS")
:from 'countries
:where (:= 'name "Nevis")))
(("2011-01-22 15:20:15"))
(query (:select (:to-char 'updated-at "YYYY-MM-DD")
:from 'countries
:where (:= 'name "Nevis")))
(("2011-01-22"))
(query (:select (:to-char (:now) "DY (Day), Mon (month)")))(("SAT (Saturday ), Jan (january )"))
(query (:select (:to-char (:type "yesterday" :timestamp) "FMMonth FMDDth")))(("January 21st"))
(query (:select (:date-trunc "minute" (:now))) )((#<SIMPLE-DATE:TIMESTAMP 22-01-2011T15:49:00>))
A few of the postgresql date time functions don't quite return what I expected using postmodern. The date-part function returns a double-float, regardless of whether you are using s-sql or raw sql. You will get the same return from the extract function as well.
(query (:select (:date-part "year" 'updated-at)
:from 'countries
:where (:= 'name "Nevis"))
:single)
2011.0d0
Truncate
This query sql-op takes one or more table names and will truncate those tables (deleting all the rows. The following keyword parameters are optionally allowed and must be in this order. Note: Committed to github repository on 9/9/2018.
:only will truncate only this table and not descendent tables.
:restart-identity will restart any sequences owned by the table.
:continue-identity will continue sequences owned by the table.
:cascade will cascade the truncation through tables using foreign keys.
(query (:truncate 'bigtable 'fattable))
(query (:truncate 'bigtable 'fattable :only))
(query (:truncate 'bigtable 'fattable :only :continue-identity))
(query (:truncate 'bigtable 'fattable :only :restart-identity))
(query (:truncate 'bigtable 'fattable :only :restart-identity :cascade ))
(query (:truncate 'bigtable 'fattable :only :continue-identity :cascade ))
(query (:truncate 'bigtable 'fattable :continue-identity :cascade ))