Integral is an object relational mapper for Common Lisp based on CL-DBI and SxQL.
This software is still ALPHA quality. The APIs will be likely to change.
Should work well with MySQL/SQLite3 on SBCL/Clozure CL.
(defclass tweet ()
((id :type integer
:primary-key t
:auto-increment t
:reader tweet-id)
(status :type text
:initarg :status
:accessor tweet-status)
(user :type (varchar 32)
:initarg :user
:accessor tweet-user))
(:metaclass <dao-table-class>))
(connect-toplevel :mysql
:database-name "myapp"
:username "nitro_idiot"
:password "xxxxxxxx")
(let ((tw (make-instance 'tweet
:status "Good morning, world."
:user "nitro_idiot")))
(save-dao tw))
;; Same as the above
(create-dao 'tweet
:status "Good morning, world."
:user "nitro_idiot")
(let ((tw (find-dao 'tweet 3)))
(with-slot (status user) tw
(format t "~A said ~A" user status))
(setf (tweet-status tw) "Good evening, world.")
(save-dao tw))
(let ((tw (find-dao 'tweet 3)))
(delete-dao tw))(ql:quickload :integral)connect-toplevel is a function to establish a connection to a database.
(import 'integral:connect-toplevel)
(connect-toplevel :mysql
:database-name "testdb"
:username "nitro_idiot"
:password "password")Integral is intended to work with MySQL, PostgreSQL and SQLite3. Replace :mysql the above by your favorite RDBMS engine name.
In Integral, database tables are defined as CLOS classes. A table definition looks like this.
(import 'integral:<dao-table-class>)
(defclass user ()
((name :col-type text
:initarg :name))
(:metaclass <dao-table-class>))This user class means a "user" table in a database with a single "TEXT" column, "name".
table-definition is a function to generate a CREATE TABLE SQL for it.
(import '(integral:table-definition integral:execute-sql))
(table-definition 'user)
;=> "CREATE TABLE `user` (`%oid` SERIAL NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` TEXT)"
; NIL
(execute-sql (table-definition 'user))
;; Same as the above except ignoring CREATE TABLE if it already exists.
(ensure-table-exists 'user)Table classes can be called with make-instance like Common Lisp standard-class.
(make-instance 'user :name "Eitaro Fukamachi")
;=> #<USER %oid: <unbound>>The instance won't be recorded in a database. Call save-dao it to add the record to a database.
(import 'integral:save-dao)
(save-dao (make-instance 'user :name "Eitaro Fukamachi"))
;=> #<USER %oid: 1>
(save-dao (make-instance 'user :name "Tomohiro Matsuyama"))
;=> #<USER %oid: 2>(import 'integral:select-dao)
(select-dao 'user)
;=> (#<USER %oid: 1> #<USER %oid: 2>)
(mapcar (lambda (row)
(slot-value row 'name))
(select-dao 'user))
;=> ("Eitaro Fukamachi" "Tomohiro Matsuyama")select-dao takes SxQL clauses. You can specify WHERE, ORDER BY or LIMIT with it.
(import '(sxql:where sxql:limit))
(select-dao 'user
(where (:= :name "Eitaro Fukamachi"))
(limit 1))
;=> (#<USER %oid: 1>)You can also use find-dao for retrieving a single row.
(import 'integral:find-dao)
(find-dao 'user 1)
;=> #<USER %oid: 1>(let ((user (find-dao 'user 1)))
(setf (slot-value user 'name) "深町英太郎")
(save-dao user))(import 'integral:delete-dao)
(let ((user (find-dao 'user 1)))
(setf (slot-value user 'name) "深町英太郎")
(delete-dao user))I introduced Integral generates a table schema from a CLOS class definition. But how can we do when we want to change the table schema after creating it.
Integral has a function to apply the change of the class definition to a table schema. It is generally known as "Migration".
For example, if you want to record a "profile" of users to "user" table, add a slot for it.
(defclass user ()
((name :col-type text
:initarg :name)
(profile :col-type text
:initarg :profile))
(:metaclass <dao-table-class>))Then call migrate-table.
(import 'integral:migrate-table)
(migrate-table 'user)
;-> ALTER TABLE `user` ADD COLUMN `profile` TEXT AFTER `name`;
;=> NILAll changes of indexes and column types are also followed.
(defclass user ()
((id :col-type serial
:primary-key t)
(name :col-type (varchar 64)
:initarg :name)
(profile :col-type text
:initarg :profile))
(:metaclass <dao-table-class>))
;-> ALTER TABLE `user` DROP COLUMN `%oid`;
; ALTER TABLE `user` MODIFY COLUMN `name` VARCHAR(64);
; ALTER TABLE `user` ADD COLUMN `id` SERIAL NOT NULL PRIMARY KEY FIRST;
;=> NILIn development, class redefinitions are done many times. It's boring to execute migrate-table for each single time, isn't it?
Integral has auto-migration feature for executing migrate-table after redefinitions automatically.
Set *auto-migration-mode* T to use the mode.
(setf integral:*auto-migration-mode* t)If you'd like to administrate a database directly by writing raw SQLs, or wanna use Integral for an existing database, you can generate slot definitions from it.
(defclass tweet () ()
(:metaclass <dao-table-class>)
(:generate-slots t)):generate-slots option means slot definitions follow database schema. Note you must establish a database connection before the first make-instance.
inflate and deflate is a feature to convert data between a database and Common Lisp.
(defclass user ()
((name :type string
:initarg :name)
(created-at :type timestamp
:col-type integer
:initarg :created-at))
(:metaclass integral:<dao-table-class>))
;=> #<INTEGRAL.TABLE:<DAO-TABLE-CLASS> USER>
(find-dao 'user 1)
;=> #<USER #x302001D9452D>
(slot-value * 'created-at)
;=> 3599088727
;; Define inflate/deflate methods
(defmethod integral:inflate ((object user) (slot-name (eql 'created-at)) value)
(local-time:universal-to-timestamp value))
(defmethod integral:deflate ((object user) (slot-name (eql 'created-at)) value)
(local-time:timestamp-to-universal value))
(slot-value (find-dao 'user 1) 'created-at)
;=> @2014-01-19T11:52:07.000000+09:00You can also set inflate and deflate functions via :inflate or :deflate keywords in defclass.
(defclass user ()
((name :type string
:initarg :name)
(created-at :type timestamp
:col-type integer
:initarg :created-at
:inflate #'local-time:universal-to-timestamp
:deflate #'local-time:timestamp-to-universal))
(:metaclass integral:<dao-table-class>))Although Integral doesn't have a specific feature for relations like :has-a and :has-many, it can be done with normal methods.
(defmethod user-config ((user user))
(find-dao 'user-config (user-id user)))
(defmethod user-entries ((user user))
(select-dao 'entry (where (:= :user_id (user-id user)))))(import 'integral:retrieve-by-sql)
(retrieve-by-sql "SELECT * FROM user")
;=> ((:%oid 1 :name "深町英太郎"
; :profile "I love Common Lisp and beer")
; (:%oid 2 :name "Tomohiro Matsuyama"
; :profile NIL))retrieve-by-sql takes :as keyword argument to specify a class of the result record.
(retrieve-sql "SELECT * FROM user" :as 'user)
;=> (#<USER %oid: 1> #<USER %oid: 2>)- connect-toplevel (driver-name &rest args &key database-name &allow-other-keys)
- disconnect-toplevel ()
- <dao-class>
- <dao-table-class>
- table-name (class)
- table-definition (class &key (yield t) if-not-exists)
- inflate (object slot-name value)
- deflate (object slot-name value)
- migrate-table (class)
- ensure-table-exists (class)
- recreate-table (class)
- *auto-migration-mode*
- select-dao ((class <dao-table-class>) &rest expressions)
- insert-dao ((obj <dao-class>))
- create-dao ((class <dao-table-class>) &rest initargs)
- update-dao ((obj <dao-class>))
- delete-dao ((obj <dao-class>))
- execute-sql ((sql string) &optional binds)
- retrieve-by-sql ((sql string) &key binds as)
- save-dao ((obj <dao-class>))
- where
- order-by
- group-by
- limit
- serial
- tinyint
- smallint
- mediumint
- bigint
- text
- varchar
- enum
- datetime
- date
- timestamp
- <integral-error>
- <connection-not-established-error>
- <unknown-primary-key-error>
- <type-missing-error>
- <migration-error>
- Eitaro Fukamachi ([email protected])
Copyright (c) 2014 Eitaro Fukamachi ([email protected])
Licensed under the BSD 3-Clause License.