Mito is yet another object relational mapper, and it aims to be a successor of Integral.
- Supports MySQL, PostgreSQL and SQLite3
- Adds
id
(serial/uuid primary key),created_at
andupdated_at
by default like Ruby's ActiveRecord - Migrations
- DB schema versioning
This software is still ALPHA quality. The APIs likely change.
This software should work fine with MySQL, PostgreSQL and SQLite3 on SBCL/Clozure CL.
(mito:connect-toplevel :mysql :database-name "myapp" :username "fukamachi" :password "c0mon-1isp")
;=> #<DBD.MYSQL:<DBD-MYSQL-CONNECTION> {100691BFF3}>
(mito:deftable user ()
((name :col-type (:varchar 64))
(email :col-type (or (:varchar 128) :null))))
;=> #<MITO.DAO.TABLE:DAO-TABLE-CLASS COMMON-LISP-USER::USER>
(mito:table-definition 'user)
;=> (#<SXQL-STATEMENT: CREATE TABLE user (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64) NOT NULL, email VARCHAR(128))>)
(mito:deftable tweet ()
((status :col-type :text)
(user :col-type user)))
;=> #<MITO.DAO.TABLE:DAO-TABLE-CLASS COMMON-LISP-USER::TWEET>
(mito:table-definition 'tweet)
;=> (#<SXQL-STATEMENT: CREATE TABLE tweet (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, status TEXT NOT NULL, user_id BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP, updated_at TIMESTAMP)>)
Mito provides the functions connect-toplevel
and disconnect-toplevel
to establish and sever a connection to RDBMS.
connect-toplevel
takes the same arguments as dbi:connect
: typically the driver-type, the database name to connect, user name and password.
(mito:connect-toplevel :mysql :database-name "myapp" :username "fukamachi" :password "c0mon-1isp")
connect-toplevel
sets *connection*
to a new connection and returns it.
To use a connection lexically, just bind it:
(let ((mito:*connection* (dbi:connect :sqlite3 :database-name #P"/tmp/myapp.db")))
(unwind-protect (progn ...)
;; Ensure that the connection is closed.
(dbi:disconnect mito:*connection*)))
In most cases dbi:connect-cached
is a better option, since it reuses a connection for multiple threads.
(let ((mito:*connection* (dbi:connect-cached :sqlite3 :database-name #P"/tmp/myapp.db")))
(unwind-protect (progn ...)
;; Ensure that the connection is closed.
))
Use connection-database-name
to get the name of the current connection, or of one named via parameter.
As Mito's dao table class is defined as a CLOS metaclass, a table class can be defined like this:
(defclass user ()
((name :col-type (:varchar 64)
:accessor user-name)
(email :col-type (or (:varchar 128) :null)
:accessor user-email))
(:metaclass mito:dao-table-class))
deftable
's syntax is the same as that of cl:defclass
. However, the definition is a little bit redundant.
mito:deftable
is a thin macro, to allow definion of a table class with less typing.
For example, the above example can be rewritten, using deftable
as follows.
(mito:deftable user ()
((name :col-type (:varchar 64))
(email :col-type (or (:varchar 128) :null))))
It adds :metaclass mito:dao-table-class
, and adds default accessors that start with <class-name>-
by default, like defstruct
does.
The prefix for accessors can be changed with the :conc-name
class option:
(mito:deftable user ()
((name :col-type (:varchar 64))
(email :col-type (or (:varchar 128) :null)))
(:conc-name my-))
(my-name (make-instance 'user :name "fukamachi"))
;=> "fukamachi"
If :conc-name
is NIL, default accessors will NOT be defined.
In Mito, a class corresponding to a database table is defined by specifying (:metaclass mito:dao-table-class)
.
(defclass user ()
((name :col-type (:varchar 64)
:accessor user-name)
(email :col-type (or (:varchar 128) :null)
:accessor user-email))
(:metaclass mito:dao-table-class))
The above defines a Common Lisp normal class, except that it allows additional options.
(defclass {class-name} ()
({column-definition}*)
(:metaclass mito:dao-table-class)
[[class-option]])
column-definition ::= (slot-name [[column-option]])
column-option ::= {:col-type col-type} |
{:primary-key boolean} |
{:inflate inflation-function} |
{:deflate deflation-function} |
{:references {class-name | (class-name slot-name)}} |
{:ghost boolean}
col-type ::= { keyword |
(keyword . args) |
(or keyword :null) |
(or :null keyword) }
class-option ::= {:primary-key symbol*} |
{:unique-keys {symbol | (symbol*)}*} |
{:keys {symbol | (symbol*)}*} |
{:table-name table-name} |
{:auto-pk auto-pk-mixin-class-name} |
{:record-timestamps boolean} |
{:conc-name conc-name}
auto-pk-mixin-class-name ::= {:serial | :uuid}
conc-name ::= {null | string-designator}
Note: the class automatically adds some slots -- a primary key named id
if there is no primary key, created_at
and updated_at
for recording timestamps. To disable these behaviors, specify :auto-pk nil
or :record-timestamps nil
to defclass forms.
(mito.class:table-column-slots (find-class 'user))
;=> (#<MITO.DAO.COLUMN:DAO-TABLE-COLUMN-CLASS MITO.DAO.MIXIN::ID>
; #<MITO.DAO.COLUMN:DAO-TABLE-COLUMN-CLASS COMMON-LISP-USER::NAME>
; #<MITO.DAO.COLUMN:DAO-TABLE-COLUMN-CLASS COMMON-LISP-USER::EMAIL>
; #<MITO.DAO.COLUMN:DAO-TABLE-COLUMN-CLASS MITO.DAO.MIXIN::CREATED-AT>
; #<MITO.DAO.COLUMN:DAO-TABLE-COLUMN-CLASS MITO.DAO.MIXIN::UPDATED-AT>)
This class inherits mito:dao-class
implicitly.
(find-class 'user)
;=> #<MITO.DAO.TABLE:DAO-TABLE-CLASS COMMON-LISP-USER::USER>
(c2mop:class-direct-superclasses *)
;=> (#<STANDARD-CLASS MITO.DAO.TABLE:DAO-CLASS>)
This may be useful to define methods that can be applied for many or all table classes.
(mito:table-definition 'user)
;=> (#<SXQL-STATEMENT: CREATE TABLE user (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64) NOT NULL, email VARCHAR(128), created_at TIMESTAMP, updated_at TIMESTAMP)>)
(sxql:yield *)
;=> "CREATE TABLE user (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64) NOT NULL, email VARCHAR(128), created_at TIMESTAMP, updated_at TIMESTAMP)"
; NIL
(mapc #'mito:execute-sql (mito:table-definition 'user))
(mito:ensure-table-exists 'user)
(defvar me
(make-instance 'user :name "Eitaro Fukamachi" :email "[email protected]"))
;=> USER
(mito:insert-dao me)
;-> ;; INSERT INTO `user` (`name`, `email`, `created_at`, `updated_at`) VALUES (?, ?, ?, ?) ("Eitaro Fukamachi", "[email protected]", "2016-02-04T19:55:16.365543Z", "2016-02-04T19:55:16.365543Z") [0 rows] | MITO.DAO:INSERT-DAO
;=> #<USER {10053C4453}>
;; Same as above
(mito:create-dao 'user :name "Eitaro Fukamachi" :email "[email protected]")
;; Getting the primary key value
(mito:object-id me)
;=> 1
;; Retrieving from the DB
(mito:find-dao 'user :id 1)
;-> ;; SELECT * FROM `user` WHERE (`id` = ?) LIMIT 1 (1) [1 row] | MITO.DB:RETRIEVE-BY-SQL
;=> #<USER {10077C6073}>
(mito:retrieve-dao 'user)
;=> (#<USER {10077C6073}>)
;; Updating
(setf (slot-value me 'name) "nitro_idiot")
;=> "nitro_idiot"
(mito:save-dao me)
;-> ;; UPDATE `user` SET `id` = ?, `name` = ?, `email` = ?, `created_at` = ?, `updated_at` = ? WHERE (`id` = ?) (2, "nitro_idiot", "[email protected]", "2016-02-04T19:56:11.408927Z", "2016-02-04T19:56:19.006020Z", 2) [0 rows] | MITO.DAO:UPDATE-DAO
;; Deleting
(mito:delete-dao me)
;-> ;; DELETE FROM `user` WHERE (`id` = ?) (1) [0 rows] | MITO.DAO:DELETE-DAO
(mito:delete-by-values 'user :id 1)
;-> ;; DELETE FROM `user` WHERE (`id` = ?) (1) [0 rows] | MITO.DAO:DELETE-DAO
;; Counting
(mito:count-dao 'user)
;-> 1
Use select-dao
to build custom queries with sxql (examples below).
To define a relationship, use :references
on the slot:
(mito:deftable user ()
((id :col-type (:varchar 36)
:primary-key t)
(name :col-type (:varchar 64))
(email :col-type (or (:varchar 128) :null))))
(mito:deftable tweet ()
((status :col-type :text)
;; This slot refers to USER class
(user-id :references (user id))))
;; The :col-type of USER-ID column is retrieved from the foreign class.
(mito:table-definition (find-class 'tweet))
;=> (#<SXQL-STATEMENT: CREATE TABLE tweet (
; id BIGSERIAL NOT NULL PRIMARY KEY,
; status TEXT NOT NULL,
; user_id VARCHAR(36) NOT NULL,
; created_at TIMESTAMPTZ,
; updated_at TIMESTAMPTZ
; )>)
You can also specify another foreign class at :col-type
to define a relationship:
(mito:deftable tweet ()
((status :col-type :text)
;; This slot refers to USER class
(user :col-type user)))
(mito:table-definition (find-class 'tweet))
;=> (#<SXQL-STATEMENT: CREATE TABLE tweet (
; id BIGSERIAL NOT NULL PRIMARY KEY,
; status TEXT NOT NULL,
; user_id VARCHAR(36) NOT NULL,
; created_at TIMESTAMP,
; updated_at TIMESTAMP
; )>)
;; You can specify :USER arg, instead of :USER-ID.
(defvar *user* (mito:create-dao 'user :name "Eitaro Fukamachi"))
(mito:create-dao 'tweet :user *user*)
(mito:find-dao 'tweet :user *user*)
The latter example allows you to create/retrieve TWEET
by a USER
object, not a USER-ID
.
Mito doesn't add foreign key constraints for referring tables, since I'm not sure it's still handful while using with ORMs.
Inflation/Deflation is a function to convert values between Mito and RDBMS.
(mito:deftable user-report ()
((title :col-type (:varchar 100))
(body :col-type :text
:initform "")
(reported-at :col-type :timestamp
:initform (local-time:now)
:inflate #'local-time:universal-to-timestamp
:deflate #'local-time:timestamp-to-universal))
(:conc-name report-))
One of the pains in the neck to use ORMs is the "N+1 query" problem.
;; BAD EXAMPLE
(use-package '(:mito :sxql))
(defvar *tweets-contain-japan*
(select-dao 'tweet
(where (:like :status "%Japan%"))))
;; Getting names of tweeted users.
(mapcar (lambda (tweet)
(user-name (tweet-user tweet)))
*tweets-contain-japan*)
This example sends a query to retrieve a user, like "SELECT * FROM user WHERE id = ?" for each iteration.
To prevent this performance issue, add includes
to the above query, which sends only a single WHERE IN query instead of N queries:
;; GOOD EXAMPLE with eager loading
(use-package '(:mito :sxql))
(defvar *tweets-contain-japan*
(select-dao 'tweet
(includes 'user)
(where (:like :status "%Japan%"))))
;-> ;; SELECT * FROM `tweet` WHERE (`status` LIKE ?) ("%Japan%") [3 row] | MITO.DB:RETRIEVE-BY-SQL
;-> ;; SELECT * FROM `user` WHERE (`id` IN (?, ?, ?)) (1, 3, 12) [3 row] | MITO.DB:RETRIEVE-BY-SQL
;=> (#<TWEET {1003513EC3}> #<TWEET {1007BABEF3}> #<TWEET {1007BB9D63}>)
;; No additional SQLs will be executed.
(tweet-user (first *))
;=> #<USER {100361E813}>
(ensure-table-exists 'user)
;-> ;; CREATE TABLE IF NOT EXISTS "user" (
; "id" BIGSERIAL NOT NULL PRIMARY KEY,
; "name" VARCHAR(64) NOT NULL,
; "email" VARCHAR(128),
; "created_at" TIMESTAMP,
; "updated_at" TIMESTAMP
; ) () [0 rows] | MITO.DAO:ENSURE-TABLE-EXISTS
;; No changes
(mito:migration-expressions 'user)
;=> NIL
(mito:deftable user ()
((name :col-type (:varchar 64))
(email :col-type (:varchar 128)))
(:unique-keys email))
(mito:migration-expressions 'user)
;=> (#<SXQL-STATEMENT: ALTER TABLE user ALTER COLUMN email TYPE character varying(128), ALTER COLUMN email SET NOT NULL>
; #<SXQL-STATEMENT: CREATE UNIQUE INDEX unique_user_email ON user (email)>)
(mito:migrate-table 'user)
;-> ;; ALTER TABLE "user" ALTER COLUMN "email" TYPE character varying(128), ALTER COLUMN "email" SET NOT NULL () [0 rows] | MITO.MIGRATION.TABLE:MIGRATE-TABLE
; ;; CREATE UNIQUE INDEX "unique_user_email" ON "user" ("email") () [0 rows] | MITO.MIGRATION.TABLE:MIGRATE-TABLE
;-> (#<SXQL-STATEMENT: ALTER TABLE user ALTER COLUMN email TYPE character varying(128), ALTER COLUMN email SET NOT NULL>
; #<SXQL-STATEMENT: CREATE UNIQUE INDEX unique_user_email ON user (email)>)
SQLite3 migration creates temporary tables with pre-migration data. To delete them after migration is complete set
mito:*migration-keep-temp-tables*
to nil
. It has no effect on other drivers.
If mito:*auto-migration-mode*
is set to t
, and you are connected to a database, Mito will run migrations after
each change to model definitions.
$ ros install mito
$ mito
Usage: mito command [option...]
Commands:
generate-migrations
migrate
migration-status
Options:
-t, --type DRIVER-TYPE DBI driver type (one of "mysql", "postgres" or "sqlite3")
-d, --database DATABASE-NAME Database name to use
-u, --username USERNAME Username for RDBMS
-p, --password PASSWORD Password for RDBMS
-s, --system SYSTEM ASDF system to load (several -s's allowed)
-D, --directory DIRECTORY Directory path to keep migration SQL files (default: "/Users/nitro_idiot/Programs/lib/mito/db/")
--dry-run List SQL expressions to migrate
-f, --force Create a new empty migration file even when it's unnecessary.
mito --database postgres --username fukamachi --pasword c0mmon-l1sp
A subclass of DAO-CLASS is allowed to be inherited. This may be useful when you need classes that have similar columns:
(mito:deftable user ()
((name :col-type (:varchar 64))
(email :col-type (:varchar 128)))
(:unique-keys email))
(mito:deftable temporary-user (user)
((registered-at :col-type :timestamp)))
(mito:table-definition 'temporary-user)
;=> (#<SXQL-STATEMENT: CREATE TABLE temporary_user (
; id BIGSERIAL NOT NULL PRIMARY KEY,
; name VARCHAR(64) NOT NULL,
; email VARCHAR(128) NOT NULL,
; registered_at TIMESTAMP NOT NULL,
; created_at TIMESTAMP,
; updated_at TIMESTAMP,
; UNIQUE (email)
; )>)
If you need a 'template' for tables, not related to any specific database table, you can use DAO-TABLE-MIXIN
:
(defclass has-email ()
((email :col-type (:varchar 128)
:accessor object-email))
(:metaclass mito:dao-table-mixin)
(:unique-keys email))
;=> #<MITO.DAO.MIXIN:DAO-TABLE-MIXIN COMMON-LISP-USER::HAS-EMAIL>
(mito:deftable user (has-email)
((name :col-type (:varchar 64))))
;=> #<MITO.DAO.TABLE:DAO-TABLE-CLASS COMMON-LISP-USER::USER>
(mito:table-definition 'user)
;=> (#<SXQL-STATEMENT: CREATE TABLE user (
; id BIGSERIAL NOT NULL PRIMARY KEY,
; name VARCHAR(64) NOT NULL,
; email VARCHAR(128) NOT NULL,
; created_at TIMESTAMP,
; updated_at TIMESTAMP,
; UNIQUE (email)
; )>)
Since insert-dao
, update-dao
and delete-dao
are defined as generic functions, you can define :before
, :after
or :around
methods on those.
(defmethod mito:insert-dao :before ((object user))
(format t "~&Adding ~S...~%" (user-name object)))
(mito:create-dao 'user :name "Eitaro Fukamachi" :email "[email protected]")
;-> Adding "Eitaro Fukamachi"...
; ;; INSERT INTO "user" ("name", "email", "created_at", "updated_at") VALUES (?, ?, ?, ?) ("Eitaro Fukamachi", "[email protected]", "2016-02-16 21:13:47", "2016-02-16 21:13:47") [0 rows] | MITO.DAO:INSERT-DAO
;=> #<USER {100835FB33}>
(ql:quickload :mito)
Or, with Roswell:
ros install mito
- Eitaro Fukamachi ([email protected])
Copyright (c) 2015 Eitaro Fukamachi ([email protected])
Licensed under the LLGPL License.