|cage 7f1f9c703f Added link to new repository.||2 months ago|
|src||2 years ago|
|t||4 years ago|
|.gitignore||4 years ago|
|COPYING||4 years ago|
|README.org||2 months ago|
|README.txt||4 years ago|
|orizuru-orm-test.asd||4 years ago|
|orizuru-orm.asd||4 years ago|
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.
(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
|timestamp||string||timestamp [with time zone]||yes||yes (see notes)|
timestampcolumns migration is allowed only from and to ~text~;
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:
namewill change type from
agewill be able to assume a NULL value;
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
with value ~foo can not be converted to
integer and will signal an
error, while a conversion from
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.
(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).
(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))
(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)
*WARNING*: this part is very experimental, the API could change at any moment probably broken and, moreover, the code is ugly. :)
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)
a that reference the
id slot/column of
b, the library will figure out the actual name of said
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"))
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)
listof parameters (see examples below);
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)))))))
to get the results from query use the method
specialized on class
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
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
error is signalled; in this case the user should specify the join
column using the macro:
with-join-column ((column) &body body)
(with-join-column (:to-foo) (let ((q (make-query-set :y->foo.=1)))))
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 ...)
To find the foreign key of a table user must set or bind the special
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)))
Whilst i appreciate that someone would test this code to improve it i can not recommend to use it in production (see NO WARRANTY).
;;;; 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))
;;;; 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")))))
(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))
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
(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.
Bugs or other problems can be reported on the issue tracker.
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.
Much of the work for this library come from "Eudoxia0", the original author of this code, to whom goes my acknowledgment.
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.
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.