Orizuru-orm

1Orizuru-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 Crane.

2Usage

2.1Defining Tables

(deftable user ()
  (name :type text :uniquep t)
  (age :type integer :nullp nil :initform 18)
  (friend :type integer :foreign user)))

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.

2.1.1Column 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

2.1.1.1Notes

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

2.2Migrating

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

(deftable user ()
  (name :type integer :uniquep t :nullp nil)
  (age :type integer :nullp t :initform 18)
  (description :type text))

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.

2.2.1About 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.

2.3Connecting

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

(connect)

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

2.4Creating, Saving, and Deleting Objects

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

2.5Filtering

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

2.5.1More complex filtering

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

2.5.1.1Creating a query set

Assuming a valid database connection and the following tables definition:

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

evaluating this form:

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

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

  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)

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:

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

2.5.1.2Filtering query set

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

  filter-set (&optional qset params logical-op)

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

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

2.5.1.3Get results from query-set

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

  all-from-set ((object query-set) &key (as-plist nil))

  (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

2.5.1.4Changing join column

given this two tables

  (deftable foo ()
    (dummy
     :type text))

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

a query set like

(let ((q (make-query-set :y->foo.=1))

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:

with-join-column ((column) &body body)

example given:

  (with-join-column (:to-foo)
    (let ((q (make-query-set :y->foo.=1)))))

2.5.1.5Change 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:

  (with-join-column (:to-foo)
    (let ((q (make-query-set :y->foo.=1)))
      (all-from-set q)))

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:

map-to-model (query-set class-symbol)

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

2.5.1.6Finding 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:

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

2.5.1.7Notes

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

2.6Beyond ORM

Use sxql

2.7Transactions

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

2.8Fixtures

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

2.9Inflate/Deflate

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

2.10Using testing suite

2.10.1Set up Postgres

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

 $ 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

2.10.2Run the test suite

  (ql:quickload :orizuru-orm-test)
  (run-all-tests :use-debugger t)

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

 $ su -
 (root)# su - postgres
 (postgres)$ dropdb orizuru_test_db

then go to Set up Postgres and start again.

3BUGS

Bugs or other problems can be reported on the issue tracker.

4Contributing

Help is appreciated, just point your browser to the repository to get the development version, fork and then send patches. Thank you in advance! :)

4.1Testing

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.

5Acknowledgment

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

6License

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.

7NO 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.