The datafly Reference Manual

Table of Contents

The datafly Reference Manual

This is the datafly Reference Manual, version 0.1, generated automatically by Declt version 3.0 "Montgomery Scott" on Sun May 15 04:35:02 2022 GMT+0.

1 Introduction

datafly

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-14T19:20:13")

(retrieve-all
  (select :*
    (from :user)))
;=> ((:ID 1 :NAME "nitro_idiot" :EMAIL "nitro_idiot@example.com" :REGISTERED-AT "2014-04-14T19:20:13")
;    (:ID 2 :NAME "m2ym" :EMAIL "m2ym@example.com" :REGISTERED-AT "2014-04-15T01:03:42"))

(execute
 (insert-into :tweet
   (set= :id 1
         :user_id 1
         :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-14T19: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-14T19:20:13")
;    #S(USER :ID 2 :NAME "m2ym" :EMAIL "m2ym@example.com" :REGISTERED-AT "2014-04-15T01: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-15T04: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-16T11:02:31.000000+09:00)
;    #S(TWEET :ID 1 :USER-ID 1 :BODY "Hi." :CREATED-AT @2014-04-15T18: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-14T19: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.

2 Systems

The main system appears first, followed by any subsystem dependency.

2.1 datafly

Author

Eitaro Fukamachi

License

BSD 3-Clause

Description

Lightweight database library.

Long Description

# datafly

[](https://travis-ci.org/fukamachi/datafly)

Datafly is a lightweight database library for Common Lisp.

## Quickstart

Datafly provides 3 functions which wrap [CL-DBI](https://github.com/fukamachi/cl-dbi) — ‘retrieve-one‘, ‘retrieve-all‘ and ‘execute‘.

They take a [SxQL](https://github.com/fukamachi/sxql) statement.

“‘common-lisp
(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-14T19:20:13")

(retrieve-all
(select :*
(from :user)))
;=> ((:ID 1 :NAME "nitro_idiot" :EMAIL "nitro_idiot@example.com" :REGISTERED-AT "2014-04-14T19:20:13")
; (:ID 2 :NAME "m2ym" :EMAIL "m2ym@example.com" :REGISTERED-AT "2014-04-15T01:03:42"))

(execute
(insert-into :tweet
(set= :id 1
:user_id 1
: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.

“‘common-lisp
(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-14T19: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-14T19:20:13")
; #S(USER :ID 2 :NAME "m2ym" :EMAIL "m2ym@example.com" :REGISTERED-AT "2014-04-15T01: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.

“‘common-lisp
(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.

“‘common-lisp
(defvar *user*
(retrieve-one
(select :*
(from :user)
(where (:= :name "nitro_idiot")))
:as ’user))

;; Returns a local-time:timestamp.
(user-registered-at *user*)
;=> @2014-04-15T04:20:13.000000+09:00
“‘

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

“‘common-lisp
(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-16T11:02:31.000000+09:00)
; #S(TWEET :ID 1 :USER-ID 1 :BODY "Hi." :CREATED-AT @2014-04-15T18: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.

“‘common-lisp
(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-14T19: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.

“‘common-lisp
(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](https://github.com/fukamachi/sxql)
* [CL-DBI](https://github.com/fukamachi/cl-dbi)
* [cl-annot](https://github.com/arielnetworks/cl-annot), [CL-SYNTAX](https://github.com/m2ym/cl-syntax)
* [function-cache](https://github.com/AccelerationNet/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.

Version

0.1

Dependencies

• iterate
• optima
• trivial-types
• closer-mop
• cl-syntax-annot
• sxql
• dbi
• alexandria
• babel
• local-time
• function-cache
• jonathan
• kebab
• log4cl

Source

datafly.asd (file)

Component

src (module)

3 Modules

Modules are listed depth-first from the system components tree.

3.1 datafly/src

Parent

datafly (system)

Location

src/

Components

• datafly.lisp (file)
• model.lisp (file)
• db.lisp (file)
• cache.lisp (file)
• logger.lisp (file)
• inflate.lisp (file)
• json.lisp (file)
• util.lisp (file)

4 Files

Files are sorted by type and then listed depth-first from the systems components trees.

4.1 Lisp

4.1.1 datafly.asd

Location

datafly.asd

Systems

datafly (system)

4.1.2 datafly/src/datafly.lisp

Dependencies

• model.lisp (file)
• db.lisp (file)
• cache.lisp (file)
• logger.lisp (file)
• inflate.lisp (file)
• json.lisp (file)

Parent

src (module)

Location

src/datafly.lisp

Packages

datafly

4.1.3 datafly/src/model.lisp

Dependencies

• db.lisp (file)
• cache.lisp (file)
• util.lisp (file)

Parent

src (module)

Location

src/model.lisp

Packages

datafly.model

Exported Definitions

• copy-model (function)
• defmodel (macro)
• model (macro)
• object-to-plist (function)

4.1.4 datafly/src/db.lisp

Dependency

logger.lisp (file)

Parent

src (module)

Location

src/db.lisp

Packages

datafly.db

Exported Definitions

• *connection* (special variable)
• *default-row-type* (special variable)
• connect-cached (function)
• connect-toplevel (function)
• database-type (function)
• disconnect-toplevel (function)
• execute (function)
• retrieve-all (function)
• retrieve-all-values (function)
• retrieve-one (function)
• retrieve-one-value (function)

Internal Definitions

• *connections* (special variable)
• connection-quote-character (function)
• convert-column-name (function)
• convert-column-value (function)
• convert-row (function)
• execute-with-connection (function)
• get-prev-stack (function)

4.1.5 datafly/src/cache.lisp

Parent

src (module)

Location

src/cache.lisp

Packages

datafly.cache

Exported Definitions

• clear-model-caches (function)
• clear-object-caches (function)
• defcached-accessor (macro)

Internal Definitions

• *model-accessors* (special variable)
• accessor-cache (function)

4.1.6 datafly/src/logger.lisp

Parent

src (module)

Location

src/logger.lisp

Packages

datafly.logger

Exported Definitions

• *sql-logger* (special variable)
• *trace-sql* (special variable)

Internal Definitions

*sql-logger-pattern* (special variable)

4.1.7 datafly/src/inflate.lisp

Dependency

db.lisp (file)

Parent

src (module)

Location

src/inflate.lisp

Packages

datafly.inflate

Exported Definitions

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

4.1.8 datafly/src/json.lisp

Parent

src (module)

Location

src/json.lisp

Packages

datafly.json

Exported Definitions

• convert-object (generic function)
• convert-object (method)
• convert-object (method)
• encode-json (generic function)
• encode-json (method)

Internal Definitions

• association-list-p (function)
• convert-for-json (function)
• object-to-plist (function)

4.1.9 datafly/src/util.lisp

Parent

src (module)

Location

src/util.lisp

Packages

datafly.util

Exported Definitions

• partition (function)
• partition-if (function)

5 Packages

Packages are listed by definition order.

5.1 datafly

Source

datafly.lisp (file)

Use List

common-lisp

5.2 datafly.model

Source

model.lisp (file)

Use List

• sxql
• iterate
• optima
• common-lisp

Exported Definitions

• copy-model (function)
• defmodel (macro)
• model (macro)
• object-to-plist (function)

5.3 datafly.db

Source

db.lisp (file)

Use List

• sxql
• iterate
• common-lisp

Exported Definitions

• *connection* (special variable)
• *default-row-type* (special variable)
• connect-cached (function)
• connect-toplevel (function)
• database-type (function)
• disconnect-toplevel (function)
• execute (function)
• retrieve-all (function)
• retrieve-all-values (function)
• retrieve-one (function)
• retrieve-one-value (function)

Internal Definitions

• *connections* (special variable)
• connection-quote-character (function)
• convert-column-name (function)
• convert-column-value (function)
• convert-row (function)
• execute-with-connection (function)
• get-prev-stack (function)

5.4 datafly.cache

Source

cache.lisp (file)

Use List

• function-cache
• iterate
• common-lisp

Exported Definitions

• clear-model-caches (function)
• clear-object-caches (function)
• defcached-accessor (macro)

Internal Definitions

• *model-accessors* (special variable)
• accessor-cache (function)

5.5 datafly.logger

Source

logger.lisp (file)

Use List

common-lisp

Exported Definitions

• *sql-logger* (special variable)
• *trace-sql* (special variable)

Internal Definitions

*sql-logger-pattern* (special variable)

5.6 datafly.inflate

Source

inflate.lisp (file)

Use List

common-lisp

Exported Definitions

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

5.7 datafly.json

Source

json.lisp (file)

Use List

• iterate
• common-lisp

Exported Definitions

• convert-object (generic function)
• convert-object (method)
• convert-object (method)
• encode-json (generic function)
• encode-json (method)

Internal Definitions

• association-list-p (function)
• convert-for-json (function)
• object-to-plist (function)

5.8 datafly.util

Source

util.lisp (file)

Use List

common-lisp

Exported Definitions

• partition (function)
• partition-if (function)

6 Definitions

Definitions are sorted by export status, category, package, and then by lexicographic order.

6.1 Exported definitions

6.1.1 Special variables

Special Variable: *connection*

Package

datafly.db

Source

db.lisp (file)

Special Variable: *default-row-type*

Package

datafly.db

Source

db.lisp (file)

Special Variable: *sql-logger*

Package

datafly.logger

Source

logger.lisp (file)

Special Variable: *trace-sql*

Package

datafly.logger

Source

logger.lisp (file)

6.1.2 Macros

Macro: defcached-accessor NAME ARGS CACHE-ARGS &body BODY

Package

datafly.cache

Source

cache.lisp (file)

Macro: defmodel NAME-AND-OPTIONS &body SLOT-DESCRIPTIONS

Package

datafly.model

Source

model.lisp (file)

Macro: model DEFSTRUCT-FORM

Package

datafly.model

Source

model.lisp (file)

6.1.3 Functions

Function: clear-model-caches MODEL &optional ACCESSOR

Package

datafly.cache

Source

cache.lisp (file)

Function: clear-object-caches OBJECT &optional ACCESSOR

Package

datafly.cache

Source

cache.lisp (file)

Function: connect-cached &rest CONNECT-ARGS

Package

datafly.db

Source

db.lisp (file)

Function: connect-toplevel &rest CONNECT-ARGS

Package

datafly.db

Source

db.lisp (file)

Function: copy-model OBJECT

Package

datafly.model

Source

model.lisp (file)

Function: database-type ()

Package

datafly.db

Source

db.lisp (file)

Function: datetime-to-timestamp VAL

Package

datafly.inflate

Source

inflate.lisp (file)

Function: disconnect-toplevel ()

Package

datafly.db

Source

db.lisp (file)

Function: execute STATEMENT

Package

datafly.db

Source

db.lisp (file)

Function: object-to-plist OBJECT

Package

datafly.model

Source

model.lisp (file)

Function: octet-vector-to-string VAL

Package

datafly.inflate

Source

inflate.lisp (file)

Function: partition ITEM SEQUENCE &key FROM-END START END KEY TEST

Package

datafly.util

Source

util.lisp (file)

Function: partition-if PRED SEQUENCE &key FROM-END START END KEY

Package

datafly.util

Source

util.lisp (file)

Function: retrieve-all STATEMENT &key AS PRETTIFY

Package

datafly.db

Source

db.lisp (file)

Function: retrieve-all-values STATEMENT &optional KEY PRETTIFY

Package

datafly.db

Source

db.lisp (file)

Function: retrieve-one STATEMENT &key BY = AS PRETTIFY

Package

datafly.db

Source

db.lisp (file)

Function: retrieve-one-value STATEMENT &optional KEY PRETTIFY

Package

datafly.db

Source

db.lisp (file)

Function: string-to-keyword VAL

Package

datafly.inflate

Source

inflate.lisp (file)

Function: tinyint-to-boolean VAL

Package

datafly.inflate

Source

inflate.lisp (file)

Function: unixtime-to-timestamp VAL

Package

datafly.inflate

Source

inflate.lisp (file)

6.1.4 Generic functions

Generic Function: convert-object OBJECT

Package

datafly.json

Source

json.lisp (file)

Methods

Method: convert-object (OBJECT structure-object)

Method: convert-object (OBJECT standard-object)

Generic Function: encode-json OBJECT &optional STREAM

Package

datafly.json

Source

json.lisp (file)

Methods

Method: encode-json OBJECT &optional STREAM

6.2 Internal definitions

6.2.1 Special variables

Special Variable: *connections*

Package

datafly.db

Source

db.lisp (file)

Special Variable: *model-accessors*

Package

datafly.cache

Source

cache.lisp (file)

Special Variable: *sql-logger-pattern*

Package

datafly.logger

Source

logger.lisp (file)

6.2.2 Functions

Function: accessor-cache ACCESSOR

Package

datafly.cache

Source

cache.lisp (file)

Function: association-list-p OBJECT

Package

datafly.json

Source

json.lisp (file)

Function: connection-quote-character CONN

Package

datafly.db

Source

db.lisp (file)

Function: convert-column-name NAME

Package

datafly.db

Source

db.lisp (file)

Function: convert-column-value VALUE

Package

datafly.db

Source

db.lisp (file)

Function: convert-for-json OBJECT

Package

datafly.json

Source

json.lisp (file)

Function: convert-row ROW &key AS PRETTIFY

Package

datafly.db

Source

db.lisp (file)

Function: execute-with-connection CONN STATEMENT

Package

datafly.db

Source

db.lisp (file)

Function: get-prev-stack ()

Package

datafly.db

Source

db.lisp (file)

Function: object-to-plist OBJECT

Package

datafly.json

Source

json.lisp (file)

Appendix A Indexes

A.1 Concepts

A.2 Functions

A.3 Variables

A.4 Data types