IMPORTANT NOTICE

Integral is not recommended for a new project. Consider using Mito instead.

Integral

Integral is an object relational mapper for Common Lisp based on CL-DBI and SxQL.

Warning

This software is still ALPHA quality. The APIs will be likely to change.

Should work well with MySQL/SQLite3 on SBCL/Clozure CL.

Usage

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

Quickstart

Installation

(ql:quickload :integral)

Connecting to database

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.

Defining a database table

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)

Adding records

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>

Retrieving records

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

Updating records

(let ((user (find-dao 'user 1)))
  (setf (slot-value user 'name) "深町英太郎")
  (save-dao user))

Deleting records

(import 'integral:delete-dao)

(let ((user (find-dao 'user 1)))
  (setf (slot-value user 'name) "深町英太郎")
  (delete-dao user))

Migration

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`;
;=> NIL

All 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;
;=> NIL

Mystique: Auto-migration

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

Another Way: define a class from an existing table

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/deflate

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

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

Relations

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

Wanna write a raw SQL?

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

Symbols

Connections

• connect-toplevel (driver-name &rest args &key database-name &allow-other-keys)
• disconnect-toplevel ()

Classes

• <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*

SQL

• 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

Data types

• serial
• tinyint
• smallint
• mediumint
• bigint
• text
• varchar
• enum
• datetime
• date
• timestamp

Errors

• <integral-error>
• <connection-not-established-error>
• <unknown-primary-key-error>
• <type-missing-error>
• <migration-error>

See Also

• CL-DBI - Database independent interface library.
• SxQL - SQL builder library.

Author

• Eitaro Fukamachi (e.arrows@gmail.com)

Copyright

Copyright (c) 2014 Eitaro Fukamachi (e.arrows@gmail.com)

License

Licensed under the BSD 3-Clause License.