orizuru-orm

#+OPTIONS: html-postamble:nil html-preamble:nil
#+AUTHOR:
#+TITLE: Orizuru-orm

* Orizuru-Orm

Orizuru-Orm  is an  ORM for  Common Lisp  and postgreSQL,  providing a
simple bridge  between CLOS and  relational databases, and out  of the
box         migrations,         it         is         based         on
[[https://github.com/eudoxia0/crane/][Crane]].

* Usage
** Defining Tables

#+BEGIN_SRC common-lisp
(deftable user ()
  (name :type text :uniquep t)
  (age :type integer :nullp nil :initform 18)
  (friend :type integer :foreign user)))
#+END_SRC

The foreign argument accepts a symbol that represents another table or
a  sexp  of  the  form  ~(table  &key  on-delete  on-update))~,  where
acceptable  values   are  ~:no-action  :restrict   :cascade  :set-null
:set-default~.

*** Column types
|-------------+--------------+----------------------------+-----------------+--------------------+
| orizuru-orm | CL           | SQL                        | inflate/deflate | migration allowed? |
|-------------+--------------+----------------------------+-----------------+--------------------+
| int         | integer      | integer                    | yes             | yes                |
|-------------+--------------+----------------------------+-----------------+--------------------+
| bigint      | integer      | bigint                     | yes             | yes                |
|-------------+--------------+----------------------------+-----------------+--------------------+
| smallint    | integer      | smallint                   | yes             | yes                |
|-------------+--------------+----------------------------+-----------------+--------------------+
| numeric     | ratio        | numeric                    | yes             | yes                |
|-------------+--------------+----------------------------+-----------------+--------------------+
| double      | double-float | double precision           | yes             | yes                |
|-------------+--------------+----------------------------+-----------------+--------------------+
| text        | string       | text                       | yes             | yes                |
|-------------+--------------+----------------------------+-----------------+--------------------+
| varchar     | string       | text                       | yes             | yes                |
|-------------+--------------+----------------------------+-----------------+--------------------+
| timestamp   | string       | timestamp [with time zone] | yes             | yes (see notes)    |
|-------------+--------------+----------------------------+-----------------+--------------------+
| datetime    | string       | TODO                       | TODO            | TODO               |
|-------------+--------------+----------------------------+-----------------+--------------------+

**** Notes
 - for ~timestamp~ columns migration is allowed only from and to ~text~;
 - the timezone for timestamp column is governed by the value of the special variable
   ~orizuru-orm.sql:*timestamp-column-uses-timezone-p*~.

** Migrating

The first rule  is: never use migration in  production before testing!
And, in  any case,  always be sure  to be able  to perform  a rollback
(backup, snapshot, whatever tool you have to recover a database).

#+BEGIN_SRC common-lisp
(deftable user ()
  (name :type integer :uniquep t :nullp nil)
  (age :type integer :nullp t :initform 18)
  (description :type text))
#+END_SRC

Just  make the  changes, and  Orizuru-Orm will  compute the  diffs and
perform all the ~ALTER TABLE~ directives for you.

In the example above the following action will be performed:

- the column ~name~ will change type from ~integer~ to ~text~;
- the column ~age~ will be able to assume a NULL value;
- the column ~friend ~ is *deleted*;
- a column ~description~ is added.

*** About altering column type

This  operation is  allowed but  a  bit tricky.   A limited  automatic
conversion using  common sense  (my common  sense!) is  available (see
[[Column types]]) but,  of course, the conversion i  have chosen could
not be useful or even harmless  for your database, so use this feature
with caution.  For  example a conversion from a column  of type ~text~
with value ~foo~ can not be  converted to ~integer~ and will signal an
error, while a conversion from ~double~ to ~int~ will drop the decimal
part and so on.

And, finally,  be careful that  removing a  slot will remove  a column
(and its values,  and possibly the column of a  referenced table) from
the database.

** Connecting

#+BEGIN_SRC common-lisp
(setup
 :migrations-directory
 (asdf:system-relative-pathname :myapp #p"migrations/")
 :databases
 '(:main
   (:type :postgres
    :name "myapp_db"
    :user "user"
    :pass "user")))

(connect)
#+END_SRC

For configuration management and switching databases in development/production
environments, you might want to use [Envy](https://github.com/fukamachi/envy).

** Creating, Saving, and Deleting Objects

#+BEGIN_SRC common-lisp
(let ((instance (create 'ship :name "Dalliance"
                              :tonnage 77)))
  ;; FIXME: It's back luck to rename a ship
  (setf (name instance) "Serenity")
  ;; Expand the cargo hold
  (incf (tonnage instance) 25)
  ;; Save these changes!
  (save instance)
  ;; Time to retire
  (del instance))
#+END_SRC

** Filtering

#+BEGIN_SRC common-lisp
(filter 'user) ;; Returns everything

(filter 'user :name "John")

(filter 'user (:> :age 21))

;; Returns a single object
(single 'user :name "John")

;; Throws an error if this returns more
;; than one object
(single! 'user (:< age 35))

;; t if a match exists, nil otherwise
(exists 'user :name "John")

;; If this record doesn't exist create it
(get-or-create 'user :name "John" :age 19)
#+END_SRC

*** More complex filtering

*WARNING*: this part is very experimental, the API could change at any
moment probably broken and, moreover, the code is ugly. :)

**** Creating a query set

Assuming a valid database connection and the following tables definition:

#+BEGIN_SRC common-lisp
    (deftable m ()
      (a
       :type integer))
    (deftable x ()
      (dummy
       :type text))
    (deftable y ()
      (to-x
       :type integer
       :foreign (x :restrict :cascade)))
    (deftable z ()
      (to-m
       :type integer
       :foreign (m :restrict :cascade))
      (to-y
       :type integer
       :foreign (y :restrict :cascade)))
#+END_SRC

evaluating this form:

#+BEGIN_SRC common-lisp

  (let ((orizuru-orm.util:*foreign-slots-class-package* :user-package))
    (make-query-set :z->y->x.= 2))

#+END_SRC

create an object that hold a  query like that (some escaping character
removed for readability):

#+BEGIN_SRC sql
  SELECT z.* FROM z
  INNER JOIN y ON (z.to-y = y.id)
  INNER JOIN x ON (y.to-x = x.id)
  WHERE (x.id = 2)
#+END_SRC

as you can  see the sequence ~a->b~ means a  slot/column (foreign key)
from  class/table   ~a~  that   reference  the  ~id~   slot/column  of
class/table ~b~, the  library will figure out the actual  name of said
slot.

It is  possible to filter around  a different slot and  with different
test like that:

#+BEGIN_SRC common-lisp

  (let ((orizuru-orm.util:*foreign-slots-class-package* :user-package))
    (make-query-set :z->y->x_dummy.ilike "%foo"))

#+END_SRC

**** Filtering query set

query can be  "chained" with ~filter-set~, this macro  will not modify
the original object it is applied to:

#+BEGIN_SRC text
  filter-set (&optional qset params logical-op)
#+END_SRC

- qset :: is the query-set object you want to modify (actually a modified copy is returned);
- params :: is a ~list~ of parameters (see examples below);
- logical-op :: a logical operation as keyword '(:or :and :not); default is :and.

Here is some more examples (the following will show the code generated
as sxql):

#+BEGIN_SRC common-lisp
  (let ((q (make-query-set :z.= 2)))
    (->sxql q)))

  ;; =>

  (SELECT (:Z.*)
    (FROM :Z)
    (WHERE (:AND (:= :Z.ID 2))))

  (let ((q (make-query-set :x.= 2)))
    (setf q (filter-set q (:y_id.> 1) :or))
    (setf q (filter-set q (:y_id.<= 5) :and))
    (->sxql q))

  ;; =>

  (SELECT (X.*)
    (FROM X)
    (WHERE (AND (<= Y.ID 5)
                (OR (> Y.ID 1)
                    (AND (= X.ID 2))))))


  (let ((q (make-query-set :z->y->x.= 2)))
    (setf q (filter-set q (:z->m_a.> 9)))
    (->sxql q))

  ;; =>

  (SELECT (Z.*)
    (FROM Z)
    (INNER-JOIN M ON (= Z.TO-M M.ID))
    (INNER-JOIN Y ON (= Z.TO-Y Y.ID))
    (INNER-JOIN X ON (= Y.TO-X X.ID))
    (WHERE (AND (> M.A 9)
                (AND (= X.ID 2)))))

  ;; of course this does not makes sense, it is just to show the API ^^;

  (let ((q (make-query-set :z->y->x.= 2)))
    (setf q (filter-set q (:y_id.> 1)  :not))
    (setf q (filter-set q (:y_id.<= 2) :and))
    (setf q (filter-set q (:z_id.> 0)))
    (setf q (filter-set q (:z->m_a.> 9)))
    (->sxql q))

  ;; =>

  (SELECT (:Z.*)
    (FROM :Z)
    (INNER-JOIN :M :ON (:= :Z.TO-M :M.ID))
    (INNER-JOIN :Y :ON (:= :Z.TO-Y :Y.ID))
    (INNER-JOIN :X :ON (:= :Y.TO-X :X.ID))
    (WHERE (:AND (:> :M.A 9)
                 (:AND (:> :Z.ID 0)
                       (:<= :Y.ID 2)
                       (:AND (:NOT (:> :Y.ID 1))
                             (:AND (:= :X.ID 2)))))))

#+END_SRC

**** Get results from query-set

to  get  the  results  from   query  use  the  method  ~all-from-set~,
specialized on class ~query-set~

#+BEGIN_SRC common-lisp
  all-from-set ((object query-set) &key (as-plist nil))
#+END_SRC

#+BEGIN_SRC common-lisp
  (all-from-set a-query-set :as-plist nil)

  ;; =>

  ;; '(instance-of-class ...) or nil

  (all-from-set a-query-set :as-plist t)

  ;; =>

  ;; '((:slot-1 value :slot-2 value) ... ) or nil
#+END_SRC

**** Changing join column

given this two tables

#+BEGIN_SRC common-lisp
  (deftable foo ()
    (dummy
     :type text))

  (deftable bar ()
    (to-foo
     :type integer
     :foreign (foo :restrict :cascade))
    (to-foo-2
     :type integer
     :foreign (foo :restrict :cascade)))
#+END_SRC

a query set like

#+BEGIN_SRC common-lisp
(let ((q (make-query-set :y->foo.=1))

#+END_SRC

because the join can happen either on column ~to-foo~ or ~to-foo-2~ an
error is  signalled; in  this case  the user  should specify  the join
column using the macro:

#+BEGIN_SRC common-lisp
with-join-column ((column) &body body)
#+END_SRC

example given:

#+BEGIN_SRC common-lisp
  (with-join-column (:to-foo)
    (let ((q (make-query-set :y->foo.=1)))))
#+END_SRC

**** Change mapped table

by  default getting  data  form  a query-set  will  return  a list  of
instances (or  list of plist  see: [[Get results from  query-set]]) of
the first recognized table, for example the values of this form:

#+BEGIN_SRC common-lisp
  (with-join-column (:to-foo)
    (let ((q (make-query-set :y->foo.=1)))
      (all-from-set q)))
#+END_SRC

is a  list of instances  of class ~y~ (or  nil if the  search criteria
does not match  any row of the  table); to get a list  of instances of
~foo~ use the function:

#+BEGIN_SRC text
map-to-model (query-set class-symbol)
#+END_SRC

#+BEGIN_SRC common-lisp
  (with-join-column (:to-foo)
    (let ((q (make-query-set :y->foo.=1)))
      (setf q (map-to-model q :foo))
      (all-from-set q)))

  ;; => '(instance-of-foo-1, instance-of-foo-2 ...)
#+END_SRC

**** Finding slots that are foreign keys

To find the foreign  key of a table user must set  or bind the special
variable   ~orizuru-orm.util:*foreign-slots-class-package*~   to   the
actual   package   where   your    tables   are   defined;   a   macro
~query-set:with-table-package~ makes this task less annoyng:

#+BEGIN_SRC common-lisp
  (with-table-package ((the-package) &body body)
    (with-join-column (:to-foo)
      (let ((q (make-query-set :y->foo.=1)))
        (setf q (map-to-model q :foo))
        (all-from-set q)))
#+END_SRC

**** Notes

Whilst i appreciate that someone would  test this code to improve it i
can not recommend to use it in production (see [[NO WARRANTY]]).

** Beyond ORM

Use [[https://github.com/fukamachi/sxql][sxql]]

** Transactions

#+BEGIN_SRC lisp
;;;; Automatic
(with-transaction ()
  (let ((restaurants (filter 'restaurant ...)))
    (loop for restaurant in restaurants do
          ...
          (save restaurant))))

;;;; Manual
(progn
  (begin-transaction)
  (let ((restaurants (filter 'restaurant ...)))
    (loop for restaurant in restaurants do
          ...
          (save restaurant)))
  (commit))
#+END_SRC

** Fixtures

#+BEGIN_SRC lisp
;;;; initial-data.lisp
(app:user
  (:name "john"
   :groups (:admin :staff))
  (:name "joe"
   :groups (:admin)))
(app:company
  (:name "Initech"
   :city "Denver"))

;;;; myapp.asd
(asdf:defsystem myapp
  :defsystem-depends-on (:clos-fixtures)
  :components ((:module "src"
                :components
                ((:fixture "initial-data")))))
#+END_SRC

** Inflate/Deflate

#+BEGIN_SRC lisp
(definflate (stamp 'timestamp)
  ;; Inflate a timestamp value
  ;; into a timestamp object
  (local-time:universal-to-timestamp stamp))

(defdeflate (stamp local-time:timestamp)
  ;; Deflate a timestamp object
  ;; into a string
  (local-time:format-timestring nil stamp))
#+END_SRC

** Using testing suite

*** Set up Postgres

Assuming there is a working  PostgreSQL server running and configured
use the following:

#+BEGIN_SRC shell
 $ su -
 (root)# su - postgres
 (postgres)$ createdb orizuru_test_db
 (postgres)$ psql -c "CREATE USER orizuru_test_user WITH PASSWORD 'orizuru_test_user'"
 (postgres)$ psql -c "GRANT ALL PRIVILEGES ON DATABASE orizuru_test_db TO orizuru_test_user"
 (postgres)$ ^D
 (root)#     ^D

#+END_SRC

*** Run the test suite

#+BEGIN_SRC lisp
  (ql:quickload :orizuru-orm-test)
  (run-all-tests :use-debugger t)
#+END_SRC

if you  plane to rerun the  test you must delete  the testing database
first (be careful not to delete a valuable database!)

#+BEGIN_SRC shell
 $ su -
 (root)# su - postgres
 (postgres)$ dropdb orizuru_test_db
#+END_SRC

then go to [[Set up Postgres]] and start again.

* BUGS

Bugs or other problems can be reported on the
[[https://codeberg.org/cage/orizuru-orm/][issue tracker]].

* Contributing

Help   is    appreciated,   just    point   your   browser    to   the
[[https://codeberg.org/cage/orizuru-orm/][repository]]   to   get   the
development          version,          fork          and          then
[[https://codeberg.org/cage/orizuru-orm/pulls][send   patches]].  Thank
you in advance!  :)

** Testing

If you add new feature please add  a test to ensure that it works and,
in the future, if a regression occurs, is not missed.

* Acknowledgment

Much of the work for this  library come from "Eudoxia0", the original
author of this code, to whom goes my acknowledgment.

* License

Originally released as  Crane Copyright © 2013  Fernando Borretti under
the MIT license (following).

Permission is hereby granted, free  of charge, to any person obtaining
a  copy  of this  software  and  associated documentation  files  (the
"Software"), to  deal in  the Software without  restriction, including
without limitation  the rights to  use, copy, modify,  merge, publish,
distribute, sublicense,  and/or sell  copies of  the Software,  and to
permit persons to whom the Software  is furnished to do so, subject to
the following conditions:

The  above  copyright  notice  and this  permission  notice  shall  be
included in all copies or substantial portions of the Software.

THE  SOFTWARE IS  PROVIDED  "AS  IS", WITHOUT  WARRANTY  OF ANY  KIND,
EXPRESS OR  IMPLIED, INCLUDING  BUT NOT LIMITED  TO THE  WARRANTIES OF
MERCHANTABILITY,    FITNESS    FOR    A   PARTICULAR    PURPOSE    AND
NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
OF CONTRACT, TORT OR OTHERWISE, ARISING  FROM, OUT OF OR IN CONNECTION
WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

For this version:

This program  is Copyright ©  2019 Universita' degli Studi  di Palermo
and  released  under GNU  General  Public  license  version 3  of  the
License, or (at your option) any later version.(see COPYING file).

This program  is distributed in the  hope that it will  be useful, but
WITHOUT   ANY  WARRANTY;   without  even   the  implied   warranty  of
MERCHANTABILITY  or FITNESS  FOR  A PARTICULAR  PURPOSE.  See the  GNU
General Public License for more details.

* NO WARRANTY

This library  is distributed in the  hope that it will  be useful, but
WITHOUT   ANY  WARRANTY;   without  even   the  implied   warranty  of
MERCHANTABILITY  or FITNESS  FOR  A PARTICULAR  PURPOSE.  See the  GNU
Lesser General Public License for more details.


alexandria

anaphora

cl-fad

closer-mop

clos-fixtures

cl-ppcre-unicode

clunit2

iterate

local-time

sxql

uiop

orizuru-orm

Upstream URL

Author

Maintainer

License

orizuru-orm

Version:

License:

Authors:

Maintainers:

Dependencies:

Dependencies (12)

Dependents (0)