orizuru-orm
2024-10-12
An ORM for Common Lisp and PostgreSQL.
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 totext
; - the timezone for timestamp column is governed by the value of the special variable
orizuru-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 frominteger
totext
; - 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.