github.com/fukamachi/datafly

Eitaro Fukamachi

BSD 3-Clause

datafly

Build Status

Datafly is a lightweight database library for Common Lisp.

Quickstart

Datafly provides 3 functions which wrap CL-DBI — retrieve-one, retrieve-all and execute.

They take a SxQL statement.

(use-package:sxql)
(connect-toplevel:sqlite3:database-name #P"myapp.db")
(retrieve-one(select:*(from:user)(where(:=:name"nitro_idiot"))));=> (:ID 1 :NAME "nitro_idiot" :EMAIL "nitro_idiot@example.com" :REGISTERED-AT "2014年04月14日T19:20:13")
(retrieve-all(select:*(from:user)));=> ((:ID 1 :NAME "nitro_idiot" :EMAIL "nitro_idiot@example.com" :REGISTERED-AT "2014年04月14日T19:20:13"); (:ID 2 :NAME "m2ym" :EMAIL "m2ym@example.com" :REGISTERED-AT "2014年04月15日T01:03:42"))
(execute(insert-into:tweet(set=:id1:user_id1:body"Hi.":created_at(princ-to-string *now*))))

If you specify :as option with a class name to retrieval functions, they create instances of the class for each rows.

(defstruct user
 id
 name
 email
 registered-at)
(retrieve-one(select:*(from:user)(where(:=:name"nitro_idiot"))):as'user);=> #S(USER :ID 1 :NAME "nitro_idiot" :EMAIL "nitro_idiot@example.com" :REGISTERED-AT "2014年04月14日T19:20:13")
(retrieve-all(select:*(from:user)):as'user);=> (#S(USER :ID 1 :NAME "nitro_idiot" :EMAIL "nitro_idiot@example.com" :REGISTERED-AT "2014年04月14日T19:20:13"); #S(USER :ID 2 :NAME "m2ym" :EMAIL "m2ym@example.com" :REGISTERED-AT "2014年04月15日T01:03:42"))

The structure doesn't require having slots same as an existing table's in a database. It is acceptable even if the names are different. This might be convenient when you'd like to treat a JOINed table result as a structure object.

Model Definitions

Datafly provides a macro defmodel which defines a flavored structure class.

(defmodel(user(:inflate registered-at #'datetime-to-timestamp)) id
 name
 email
 registered-at)
;; Same as the above.(syntax:use-syntax:annot)
@model
(defstruct(user(:inflate registered-at #'datetime-to-timestamp)) id
 name
 email
 registered-at)

(:inflate <columns> <inflation-function>) options mean inflation-function will be applied to each <columns> when creating an instance.

(defvar*user*(retrieve-one(select:*(from:user)(where(:=:name"nitro_idiot"))):as'user))
;; Returns a local-time:timestamp.(user-registered-at *user*);=> @2014年04月15日T04:20:13.000000+09:00

defmodel also allows :has-a and :has-many options.

(use-package:sxql)
(defmodel(user(:inflate registered-at #'datetime-to-timestamp)(:has-a config (where(:=:user_id id)))(:has-many(tweets tweet)(select:*(from:tweet)(where(:=:user_id id))(order-by(:desc:created_at))))) id
 name
 email
 registered-at)
(defvar*user*(retrieve-one(select:*(from:user)(where(:=:name"nitro_idiot"))):as'user))
(user-config *user*);=> #S(CONFIG :ID 4 :USER-ID 1 :TIMEZONE "JST" :COUNTRY "jp" :LANGUAGE "ja")
(user-tweets *user*);=> (#S(TWEET :ID 2 :USER-ID 1 :BODY "Is it working?" :CREATED-AT @2014年04月16日T11:02:31.000000+09:00); #S(TWEET :ID 1 :USER-ID 1 :BODY "Hi." :CREATED-AT @2014年04月15日T18:58:20.000000+09:00))

Provided inflation functions

• tinyint-to-boolean
• datetime-to-timestamp
• unixtime-to-timestamp
• string-to-keyword
• octet-vector-to-string

Tips: Getting Association List or Hash Table for each rows

retrieve-one and retrieve-all return row(s) as a property list or a list of property lists by default.

If you'd like they were other types, for example "Association List" or "Hash Table", you can do it by passing :as parameter.

(retrieve-one(select:*(from:user)(where(:=:name"nitro_idiot"))):as'trivial-types:association-list);=> ((:ID . 1) (:NAME . "nitro_idiot") (:EMAIL . "nitro_idiot@example.com") (:REGISTERED-AT . "2014年04月14日T19:20:13"))
(retrieve-one(select:*(from:user)(where(:=:name"nitro_idiot"))):as'hash-table);=> #<HASH-TABLE :TEST EQL :COUNT 4 {1007AE3CD3}>

If no :as parameter is specified, *default-row-type* will be used.

(let((*default-row-type*'hash-table))(retrieve-all(select:*(from:user))));=> (#<HASH-TABLE :TEST EQL :COUNT 4 {100815FA03}> #<HASH-TABLE :TEST EQL :COUNT 4 {100815FE43}>)

See Also

• SxQL
• CL-DBI
• cl-annot, CL-SYNTAX
• function-cache

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.