The cl-batis Reference Manual

This is the cl-batis Reference Manual, version 0.1, generated automatically by Declt version 4.0 beta 2 "William Riker" on Tue Jul 15 03:38:01 2025 GMT+0.

Table of Contents


1 Introduction


2 Systems

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


2.1 cl-batis

Author

tamura shingo

License

MIT

Version

0.1

Dependency

batis (system).

Source

cl-batis.asd.


2.2 batis

SQL Mapping Framework for Common Lisp

Author

tamura shingo

License

MIT

Long Description

# Cl-Batis - SQL Mapping Framework for Common Lisp

![ci workflow](https://github.com/tamurashingo/cl-batis/actions/workflows/ci.yml/badge.svg)

## Usage

### create session

“‘common-lisp
;; with CL-DBI connection
(defparameter *conn-dbi* (dbi:connect :mysql
:database-name "batis"
:username "nobody"
:password "nobody"))
(defparameter *session* (create-sql-session *conn-dbi*))

;; with CL-DBI-Connection-Pool
(defparameter *conn-pool* (dbi-cp:make-dbi-connection-pool :mysql
:database-name "batis"
:username "nobody"
:password "nobody"))
(defparameter *session* (create-sql-session *conn-pool*))

;; direct
(defparameter *session* (create-sql-session :mysql
:database-name "batis"
:username "nobody"
:password "nobody"))

“‘

### how to do DDL

Cl-Batis does not support DDL.
If you want to use DDL, use ‘do-sql‘.

“‘common-lisp
(do-sql session "truncate table product")
“‘

### Define SQL

There are two type of methods.

- ‘update‘
- ‘select‘

when use ‘(cl-syntax:use-syntax :annot)‘, ‘@update‘ and ‘@select‘ can be used.

#### update

“‘common-lisp
@update ("insert into product (id, name, price) values (:id, :name, :price)")
(defsql register-product (id name price))

@update ("update
product "
(sql-set
(sql-cond (not (null name))
" name = :name, ")
(sql-cond (not (null price))
" price = :price "))
(sql-where
" id = :id "))
(defsql update-product (id name price))
“‘

#### select

“‘common-lisp
@select ("select name, price from product where id = :id")
(defsql search-product (id))

@select ("select id, name, price from product"
(sql-where
(sql-cond (not (null name))
" and name = :name ")
(sql-cond (not (null price_low))
" and price >= :price_low ")
(sql-cond (not (null price_high))
" and price <= :price_high "))
" order by id ")
(defsql filter-product (name price_low price_high))
“‘

#### where, set

“‘common-lisp
@select
("select * from product where "
(sql-cond (not (null price))
" price = :price")
(sql-cond (not (null valid_flag))
" and valid_flag = :valid_flag"))
(defsql search-by-price (price valid_flag))
“‘

In dynamic condition, if ‘sql-cond‘ returns nothing, you would end up with SQL that looked like this:

“‘SQL
select * from product where
“‘

This would fail.
And, if only the second condition was met, you would end up with SQL that looked like this:

“‘SQL
select * from product where
and valid_flag = ’1’
“‘

This would also fail.

So, ‘cl-batis‘ provides ‘SQL-WHERE‘ function.

“‘common-lisp
@select
("select * from product"
(sql-where
(sql-cond (not (null price))
" price = :price")
(sql-cond (not (null valid_flag))
" and valid_flag = :valid_flag ")))
(defsql search-by-product (price valid_flag))
“‘

The ‘SQL-WHERE‘ knows to only insert ‘WHERE‘ if there is any condition.
Furthermore, if that content begins with ‘AND‘ or ‘OR‘, strip it off.

“‘common-lisp
@update
("update product"
(sql-set
(sql-cond (not (null price))
" price = :price, ")
(sql-cond (not (null name))
" name = :name "))
(sql-where
" id = :id "))
(defsql update-product-info (id price name))
“‘

There is a similar solution for dynamic update statements called ‘SQL-SET‘.
The ‘SQL-SET‘ knows to strip last comma off.

### Execute

#### update

“‘common-lisp
(update-one *session* register-product :id 1 :name "NES" :price 14800)
“‘

#### select

“‘common-lisp
(select-one *session* search-product :id 1)
-> (:|name| "NES" :|price| 14800))
“‘

“‘common-lisp
(select-list *session* filter-product :price_low 20000)
->((:|id| 2 :|name| "SNES" :|price| 25000)
(:|id| 3 :|name| "MEGA DRIVE" :|price| 21000)
(:|id| 4 :|name| "PC Engine" :|price| 24800)))
“‘

### transaction

When exiting the ‘transaction-macro‘ block, it will automatically commit.

“‘common-lisp
(with-transaction *session*
; blah blah blah
)
“‘

To explicitly commit, use ‘commit‘.

“‘common-lisp
(with-transaction *session*
(update-one *session* register-product :id 1 :name "NES" :price 14800)
(commit *session*))
“‘

You can roll back using ‘rollback‘.

“‘common-lisp
(with-transaction *session*
;blah
;blah
;blah
(rollback *session*))
“‘

### release session

“‘common-lisp
(close-sql-session *session*)
“‘

## Databases

* SQLite3
* PostgreSQL
* MySQL

## Example

“‘common-lisp
;;;
;;; create session
;;;
CL-USER> (defparameter session
(create-sql-session :mysql
:database-name "scdata"
:username "root"
:password "password"))
SESSION

;;;
;;; create table
;;;
CL-USER> (do-sql session "create table product (id integer primary key, name varchar(20) not null, price integer not null)") ; No value

;;;
;;; define sql
;;;
CL-USER> (select (" select * from product where id = :id ")
(defsql select-product (id)))
SELECT-PRODUCT
CL-USER> (select (" select name, price from product "
(sql-where
(sql-cond (not (null name))
" and name = :name ")
(sql-cond (not (null price_low))
" and price >= :price_low ")
(sql-cond (not (null price_high))
" and price <= :price_high "))
" order by id ")
(defsql select-product-by-name-or-price (name price_low price_high)))
; in:
; SELECT (" select name, price from product "
; (SQL-WHERE (SQL-COND (NOT (NULL NAME)) " and name = :name ")
; (SQL-COND (NOT (NULL PRICE_LOW))
; " and price >= :price_low ")
; (SQL-COND (NOT (NULL PRICE_HIGH))
; " and price <= :price_high "))
; " order by id ")
; (NULL NAME)
; –> IF
; ==>
; NAME
;
; caught STYLE-WARNING:
; reading an ignored variable: NAME

; (NULL PRICE_LOW)
; –> IF
; ==>
; PRICE_LOW
;
; caught STYLE-WARNING:
; reading an ignored variable: PRICE_LOW

; (NULL PRICE_HIGH)
; –> IF
; ==>
; PRICE_HIGH
;
; caught STYLE-WARNING:
; reading an ignored variable: PRICE_HIGH
;
; compilation unit finished
; caught 3 STYLE-WARNING conditions
SELECT-PRODUCT-BY-NAME-OR-PRICE
CL-USER> (update ("insert into product (id, name, price) values (:id, :name, :price)")
(defsql register-product (id name price)))
REGISTER-PRODUCT

;;;
;;; insert
;;;
CL-USER> (update-one session register-product :id 1 :name "NES" :price 14800)
(1)
CL-USER> (update-one session register-product :id 2 :name "SNES" :price 25000)
(1)
CL-USER> (update-one session register-product :id 3 :name "MEGA DRIVE" :price 21000)
(1)
CL-USER> (update-one session register-product :id 4 :name "PC Engine" :price 24800)
(1)

;;;
;;; select one record
;;;
CL-USER> (select-one session select-product :id 1)
(:|id| 1 :|name| "NES" :|price| 14800)

;;;
;;; select some records
;;;
CL-USER> (select-list session select-product-by-name-or-price)
((:|name| "NES" :|price| 14800) (:|name| "SNES" :|price| 25000)
(:|name| "MEGA DRIVE" :|price| 21000) (:|name| "PC Engine" :|price| 24800))
CL-USER> (select-list session select-product-by-name-or-price :price_low 20000)
((:|name| "SNES" :|price| 25000) (:|name| "MEGA DRIVE" :|price| 21000)
(:|name| "PC Engine" :|price| 24800))
CL-USER> (select-list session select-product-by-name-or-price :price_low 20000 :price_high 22000)
((:|name| "MEGA DRIVE" :|price| 21000))
CL-USER> (select-list session select-product-by-name-or-price :name "PC Engine")
((:|name| "PC Engine" :|price| 24800))
“‘

## Installation

~~This library is available on Quicklisp.~~

use qlot.

## Author

* tamura shingo (tamura.shingo@gmail.com)

## Copyright

Copyright (c) 2017, 2024, 2025 tamura shingo (tamura.shingo@gmail.com)

## License

Licensed under the MIT License.

Version

0.1

Dependencies
  • cl-dbi (system).
  • cl-dbi-connection-pool (system).
  • cl-ppcre (system).
  • cl-syntax (system).
  • cl-syntax-annot (system).
Source

batis.asd.

Child Component

src (module).


3 Modules

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


3.1 batis/src

Source

batis.asd.

Parent Component

batis (system).

Child Components

4 Files

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


4.1 Lisp


4.1.1 cl-batis/cl-batis.asd

Source

cl-batis.asd.

Parent Component

cl-batis (system).

ASDF Systems

cl-batis.

Packages

cl-batis-asd.


4.1.2 batis/batis.asd

Source

batis.asd.

Parent Component

batis (system).

ASDF Systems

batis.

Packages

batis-asd.


4.1.3 batis/src/batis.lisp

Dependencies
Source

batis.asd.

Parent Component

src (module).

Packages

batis.


4.1.4 batis/src/macro.lisp

Dependency

sql.lisp (file).

Source

batis.asd.

Parent Component

src (module).

Packages

batis.macro.

Public Interface
Internals

4.1.5 batis/src/sqlparser.lisp

Source

batis.asd.

Parent Component

src (module).

Packages

batis.sqlparser.

Public Interface

parse (function).

Internals

4.1.6 batis/src/dbi.lisp

Source

batis.asd.

Parent Component

src (module).

Packages

batis.dbi.

Public Interface

do-sql (method).


4.1.7 batis/src/sql.lisp

Dependencies
Source

batis.asd.

Parent Component

src (module).

Packages

batis.sql.

Public Interface
Internals

4.1.8 batis/src/datasource.lisp

Source

batis.asd.

Parent Component

src (module).

Packages

batis.datasource.

Public Interface
Internals

5 Packages

Packages are listed by definition order.


5.1 batis.sql

Source

sql.lisp.

Use List
  • cl-annot.
  • common-lisp.
Public Interface
Internals

5.2 batis-asd

Source

batis.asd.

Use List
  • asdf/interface.
  • common-lisp.

5.3 batis.macro

Source

macro.lisp.

Use List
  • cl-annot.
  • common-lisp.
Public Interface
Internals

5.4 batis.sqlparser

Source

sqlparser.lisp.

Use List

common-lisp.

Public Interface

parse (function).

Internals

5.5 cl-batis-asd

Source

cl-batis.asd.

Use List
  • asdf/interface.
  • common-lisp.

5.6 batis

Source

batis.lisp.

Nickname

cl-batis

Use List
  • cl-annot.
  • common-lisp.

5.7 batis.dbi

Source

dbi.lisp.

Use List

common-lisp.

Public Interface

do-sql (generic function).


5.8 batis.datasource

Source

datasource.lisp.

Use List
  • cl-annot.
  • cl-annot.class.
  • common-lisp.
Public Interface
Internals

6 Definitions

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


6.1 Public Interface


6.1.1 Macros

Macro: defsql (sql-name args &key sql-body sql-type &allow-other-keys)

define sql name and its args

Package

batis.macro.

Source

macro.lisp.

Macro: select (sql-form def-form)

define SELECT SQL

Example:
@select ("select * from product where valid_flag = ’1’ " (sql-cond (not (null product_name))
" and product_name like :product_name ")) (defsql fetch-product (product_name))

Package

batis.macro.

Source

macro.lisp.

Macro: sql-cond (test-form sql-body)

apply sql-body when test-form is true

Example:
(sql-cond (not (null product_name))
" and product_name like :product_name ")

Package

batis.macro.

Source

macro.lisp.

Macro: update (sql-form def-form)

define UPDATE SQL

Example:
@select ("update product set product_price = :product_price where product_id = :product_id") (defsql update_price (product_price product_id))

Package

batis.macro.

Source

macro.lisp.

Macro: with-transaction (session &body body)
Package

batis.datasource.

Source

datasource.lisp.


6.1.2 Ordinary functions

Function: parse (sql)

generate prepared-type SQL and parameters

Package

batis.sqlparser.

Source

sqlparser.lisp.

Function: sql-set (&rest columns)

insert ‘SET‘.
Furthermore, if that content ends with ‘,‘, strip it off.

Package

batis.macro.

Source

macro.lisp.

Function: sql-where (&rest conditions)

insert ‘WHERE‘ if there is any condition.
Furthermore, if that content begins with ‘AND‘ or ‘OR‘, strip it off.

Package

batis.macro.

Source

macro.lisp.


6.1.3 Generic functions

Generic Function: close-sql-session (session)
Package

batis.datasource.

Methods
Method: close-sql-session ((session <sql-session-dbi-cp>))
Source

datasource.lisp.

Method: close-sql-session ((session <sql-session-dbi>))
Source

datasource.lisp.

Generic Function: commit (session)
Package

batis.datasource.

Methods
Method: commit ((session <sql-session-dbi-cp>))
Source

datasource.lisp.

Method: commit ((session <sql-session-dbi>))
Source

datasource.lisp.

Generic Function: create-sql-session (conn &key database-name &allow-other-keys)
Package

batis.datasource.

Methods
Method: create-sql-session (driver-name &rest params &key database-name &allow-other-keys)
Source

datasource.lisp.

Method: create-sql-session ((connection-pool <dbi-connection-pool>) &key &allow-other-keys)
Source

datasource.lisp.

Method: create-sql-session ((conn dbi-connection) &key &allow-other-keys)
Source

datasource.lisp.

Generic Function: do-sql (session sql &optional params)
Package

batis.dbi.

Methods
Method: do-sql ((session <sql-session>) sql &optional params)
Source

dbi.lisp.

Generic Function: rollback (session)
Package

batis.datasource.

Methods
Method: rollback ((session <sql-session-dbi-cp>))
Source

datasource.lisp.

Method: rollback ((session <sql-session-dbi>))
Source

datasource.lisp.

Generic Function: select-list (session sql-name &rest params &key &allow-other-keys)
Package

batis.sql.

Methods
Method: select-list ((session <sql-session>) sql-name &rest params &key &allow-other-keys)
Source

sql.lisp.

Generic Function: select-one (session sql-name &rest params &key &allow-other-keys)
Package

batis.sql.

Methods
Method: select-one ((session <sql-session>) sql-name &rest params &key &allow-other-keys)
Source

sql.lisp.

Generic Function: update-one (session sql-name &rest params &key &allow-other-keys)
Package

batis.sql.

Methods
Method: update-one ((session <sql-session>) sql-name &rest params &key &allow-other-keys)
Source

sql.lisp.


6.2 Internals


6.2.1 Ordinary functions

Function: create-params (named-params named-value)

create params to use execute method.
named-params: array of parameter names
named-value: property list of argment values

(create-params ’(NAME PRICE PRICE) ’(:NAME "name" :PRICE 100)) -> ("name" 100 100)

Package

batis.sql.

Source

sql.lisp.

Function: gen-sql-params (sql-name params)

generate parameterized SQL and its parameters

Package

batis.sql.

Source

sql.lisp.

Function: lex-colon (sql pos len params start)
Package

batis.sqlparser.

Source

sqlparser.lisp.

Function: lex-doublequote (sql pos len params)
Package

batis.sqlparser.

Source

sqlparser.lisp.

Function: lex-normal (sql pos len params)
Package

batis.sqlparser.

Source

sqlparser.lisp.

Function: lex-quote (sql pos len params)
Package

batis.sqlparser.

Source

sqlparser.lisp.

Function: trim-first-and-or (condition)
Package

batis.macro.

Source

macro.lisp.

Function: trim-last-comma (column)
Package

batis.macro.

Source

macro.lisp.


6.2.2 Generic functions

Generic Reader: connection (object)
Package

batis.datasource.

Methods
Reader Method: connection ((<sql-session> <sql-session>))

automatically generated reader method

Source

datasource.lisp.

Target Slot

connection.

Generic Writer: (setf connection) (object)
Package

batis.datasource.

Methods
Writer Method: (setf connection) ((<sql-session> <sql-session>))

automatically generated writer method

Source

datasource.lisp.

Target Slot

connection.

Generic Reader: proxy (object)
Package

batis.datasource.

Methods
Reader Method: proxy ((<sql-session-dbi-cp> <sql-session-dbi-cp>))

automatically generated reader method

Source

datasource.lisp.

Target Slot

proxy.

Generic Writer: (setf proxy) (object)
Package

batis.datasource.

Methods
Writer Method: (setf proxy) ((<sql-session-dbi-cp> <sql-session-dbi-cp>))

automatically generated writer method

Source

datasource.lisp.

Target Slot

proxy.

Generic Function: sql-execute (session sql params)
Package

batis.sql.

Methods
Method: sql-execute ((session <sql-session-dbi-cp>) sql params)
Source

sql.lisp.

Method: sql-execute ((session <sql-session-dbi>) sql params)
Source

sql.lisp.


6.2.3 Classes

Class: <sql-session-dbi-cp>
Package

batis.datasource.

Source

datasource.lisp.

Direct superclasses

<sql-session>.

Direct methods
Direct slots
Slot: proxy
Type

dbi-cp.proxy:<dbi-connection-proxy>

Initform

(error "missing initarg")

Initargs

:proxy

Readers

proxy.

Writers

(setf proxy).

Class: <sql-session-dbi>
Package

batis.datasource.

Source

datasource.lisp.

Direct superclasses

<sql-session>.

Direct methods
Class: <sql-session>
Package

batis.datasource.

Source

datasource.lisp.

Direct subclasses
Direct methods
Direct slots
Slot: connection
Type

dbi.driver:<dbi-connection>

Initform

(error "missing initarg")

Initargs

:connection

Readers

connection.

Writers

(setf connection).


Appendix A Indexes


A.1 Concepts


A.2 Functions

Jump to:   (  
C   D   F   G   L   M   P   R   S   T   U   W  
Index Entry  Section

(
(setf connection): Private generic functions
(setf connection): Private generic functions
(setf proxy): Private generic functions
(setf proxy): Private generic functions

C
close-sql-session: Public generic functions
close-sql-session: Public generic functions
close-sql-session: Public generic functions
commit: Public generic functions
commit: Public generic functions
commit: Public generic functions
connection: Private generic functions
connection: Private generic functions
create-params: Private ordinary functions
create-sql-session: Public generic functions
create-sql-session: Public generic functions
create-sql-session: Public generic functions
create-sql-session: Public generic functions

D
defsql: Public macros
do-sql: Public generic functions
do-sql: Public generic functions

F
Function, create-params: Private ordinary functions
Function, gen-sql-params: Private ordinary functions
Function, lex-colon: Private ordinary functions
Function, lex-doublequote: Private ordinary functions
Function, lex-normal: Private ordinary functions
Function, lex-quote: Private ordinary functions
Function, parse: Public ordinary functions
Function, sql-set: Public ordinary functions
Function, sql-where: Public ordinary functions
Function, trim-first-and-or: Private ordinary functions
Function, trim-last-comma: Private ordinary functions

G
gen-sql-params: Private ordinary functions
Generic Function, (setf connection): Private generic functions
Generic Function, (setf proxy): Private generic functions
Generic Function, close-sql-session: Public generic functions
Generic Function, commit: Public generic functions
Generic Function, connection: Private generic functions
Generic Function, create-sql-session: Public generic functions
Generic Function, do-sql: Public generic functions
Generic Function, proxy: Private generic functions
Generic Function, rollback: Public generic functions
Generic Function, select-list: Public generic functions
Generic Function, select-one: Public generic functions
Generic Function, sql-execute: Private generic functions
Generic Function, update-one: Public generic functions

L
lex-colon: Private ordinary functions
lex-doublequote: Private ordinary functions
lex-normal: Private ordinary functions
lex-quote: Private ordinary functions

M
Macro, defsql: Public macros
Macro, select: Public macros
Macro, sql-cond: Public macros
Macro, update: Public macros
Macro, with-transaction: Public macros
Method, (setf connection): Private generic functions
Method, (setf proxy): Private generic functions
Method, close-sql-session: Public generic functions
Method, close-sql-session: Public generic functions
Method, commit: Public generic functions
Method, commit: Public generic functions
Method, connection: Private generic functions
Method, create-sql-session: Public generic functions
Method, create-sql-session: Public generic functions
Method, create-sql-session: Public generic functions
Method, do-sql: Public generic functions
Method, proxy: Private generic functions
Method, rollback: Public generic functions
Method, rollback: Public generic functions
Method, select-list: Public generic functions
Method, select-one: Public generic functions
Method, sql-execute: Private generic functions
Method, sql-execute: Private generic functions
Method, update-one: Public generic functions

P
parse: Public ordinary functions
proxy: Private generic functions
proxy: Private generic functions

R
rollback: Public generic functions
rollback: Public generic functions
rollback: Public generic functions

S
select: Public macros
select-list: Public generic functions
select-list: Public generic functions
select-one: Public generic functions
select-one: Public generic functions
sql-cond: Public macros
sql-execute: Private generic functions
sql-execute: Private generic functions
sql-execute: Private generic functions
sql-set: Public ordinary functions
sql-where: Public ordinary functions

T
trim-first-and-or: Private ordinary functions
trim-last-comma: Private ordinary functions

U
update: Public macros
update-one: Public generic functions
update-one: Public generic functions

W
with-transaction: Public macros


A.3 Variables

Jump to:   C   P   S  
Index Entry  Section

C
connection: Private classes

P
proxy: Private classes

S
Slot, connection: Private classes
Slot, proxy: Private classes


A.4 Data types

Jump to:   <  
B   C   D   F   M   P   S  
Index Entry  Section

<
<sql-session-dbi-cp>: Private classes
<sql-session-dbi>: Private classes
<sql-session>: Private classes

B
batis: The batis system
batis: The batis package
batis-asd: The batis-asd package
batis.asd: The batis/batis․asd file
batis.datasource: The batis․datasource package
batis.dbi: The batis․dbi package
batis.lisp: The batis/src/batis․lisp file
batis.macro: The batis․macro package
batis.sql: The batis․sql package
batis.sqlparser: The batis․sqlparser package

C
cl-batis: The cl-batis system
cl-batis-asd: The cl-batis-asd package
cl-batis.asd: The cl-batis/cl-batis․asd file
Class, <sql-session-dbi-cp>: Private classes
Class, <sql-session-dbi>: Private classes
Class, <sql-session>: Private classes

D
datasource.lisp: The batis/src/datasource․lisp file
dbi.lisp: The batis/src/dbi․lisp file

F
File, batis.asd: The batis/batis․asd file
File, batis.lisp: The batis/src/batis․lisp file
File, cl-batis.asd: The cl-batis/cl-batis․asd file
File, datasource.lisp: The batis/src/datasource․lisp file
File, dbi.lisp: The batis/src/dbi․lisp file
File, macro.lisp: The batis/src/macro․lisp file
File, sql.lisp: The batis/src/sql․lisp file
File, sqlparser.lisp: The batis/src/sqlparser․lisp file

M
macro.lisp: The batis/src/macro․lisp file
Module, src: The batis/src module

P
Package, batis: The batis package
Package, batis-asd: The batis-asd package
Package, batis.datasource: The batis․datasource package
Package, batis.dbi: The batis․dbi package
Package, batis.macro: The batis․macro package
Package, batis.sql: The batis․sql package
Package, batis.sqlparser: The batis․sqlparser package
Package, cl-batis-asd: The cl-batis-asd package

S
sql.lisp: The batis/src/sql․lisp file
sqlparser.lisp: The batis/src/sqlparser․lisp file
src: The batis/src module
System, batis: The batis system
System, cl-batis: The cl-batis system