Skip to content
This repository has been archived by the owner. It is now read-only.
[OBSOLETE] Use Mito instead.
Common Lisp
Branch: master
Clone or download

Latest commit

Fetching latest commit…
Cannot retrieve the latest commit at this time.

Files

Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
src
t
.gitignore
.travis.yml
LICENSE
README.markdown
integral-test.asd
integral.asd

README.markdown

IMPORTANT NOTICE

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

Integral

Build Status

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

Copyright

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

License

Licensed under the BSD 3-Clause License.

You can’t perform that action at this time.