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.
The main system appears first, followed by any subsystem dependency.
batisSQL Mapping Framework for Common Lisp
tamura shingo
MIT
# Cl-Batis - SQL Mapping Framework for Common Lisp
## 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.
0.1
cl-dbi (system).
cl-dbi-connection-pool (system).
cl-ppcre (system).
cl-syntax (system).
cl-syntax-annot (system).
src (module).
Modules are listed depth-first from the system components tree.
batis/srcbatis (system).
batis.lisp (file).
macro.lisp (file).
sqlparser.lisp (file).
dbi.lisp (file).
sql.lisp (file).
datasource.lisp (file).
Files are sorted by type and then listed depth-first from the systems components trees.
cl-batis/cl-batis.asdbatis/batis.asdbatis/src/batis.lispbatis/src/macro.lispbatis/src/sqlparser.lispbatis/src/dbi.lispbatis/src/sql.lispbatis/src/datasource.lispbatis/src/batis.lispmacro.lisp (file).
sqlparser.lisp (file).
sql.lisp (file).
datasource.lisp (file).
src (module).
batis/src/macro.lispsql.lisp (file).
src (module).
trim-first-and-or (function).
trim-last-comma (function).
batis/src/sqlparser.lispsrc (module).
parse (function).
lex-colon (function).
lex-doublequote (function).
lex-normal (function).
lex-quote (function).
batis/src/dbi.lispbatis/src/sql.lispsqlparser.lisp (file).
datasource.lisp (file).
dbi.lisp (file).
src (module).
select-list (method).
select-one (method).
update-one (method).
create-params (function).
gen-sql-params (function).
sql-execute (method).
sql-execute (method).
batis/src/datasource.lispsrc (module).
close-sql-session (method).
close-sql-session (method).
commit (method).
commit (method).
create-sql-session (method).
create-sql-session (method).
create-sql-session (method).
rollback (method).
rollback (method).
with-transaction (macro).
<sql-session-dbi-cp> (class).
<sql-session-dbi> (class).
<sql-session> (class).
connection (reader method).
(setf connection) (writer method).
proxy (reader method).
(setf proxy) (writer method).
Packages are listed by definition order.
batis.sqlcl-annot.
common-lisp.
select-list (generic function).
select-one (generic function).
update-one (generic function).
create-params (function).
gen-sql-params (function).
sql-execute (generic function).
batis.macrocl-annot.
common-lisp.
trim-first-and-or (function).
trim-last-comma (function).
batis.sqlparsercommon-lisp.
parse (function).
lex-colon (function).
lex-doublequote (function).
lex-normal (function).
lex-quote (function).
batis.datasourcecl-annot.
cl-annot.class.
common-lisp.
close-sql-session (generic function).
commit (generic function).
create-sql-session (generic function).
rollback (generic function).
with-transaction (macro).
<sql-session-dbi-cp> (class).
<sql-session-dbi> (class).
<sql-session> (class).
connection (generic reader).
(setf connection) (generic writer).
proxy (generic reader).
(setf proxy) (generic writer).
Definitions are sorted by export status, category, package, and then by lexicographic order.
define sql name and its args
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))
apply sql-body when test-form is true
Example:
(sql-cond (not (null product_name))
" and product_name like :product_name ")
define UPDATE SQL
Example:
@select ("update product set product_price = :product_price where product_id = :product_id")
(defsql update_price (product_price product_id))
generate prepared-type SQL and parameters
insert ‘SET‘.
Furthermore, if that content ends with ‘,‘, strip it off.
insert ‘WHERE‘ if there is any condition.
Furthermore, if that content begins with ‘AND‘ or ‘OR‘, strip it off.
<sql-session-dbi-cp>)) ¶<sql-session-dbi>)) ¶<sql-session-dbi-cp>)) ¶<sql-session-dbi>)) ¶<sql-session>) sql &optional params) ¶<sql-session-dbi-cp>)) ¶<sql-session-dbi>)) ¶<sql-session>) sql-name &rest params &key &allow-other-keys) ¶<sql-session>) sql-name &rest params &key &allow-other-keys) ¶<sql-session>) sql-name &rest params &key &allow-other-keys) ¶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)
generate parameterized SQL and its parameters
<sql-session>)) ¶automatically generated reader method
<sql-session>)) ¶automatically generated writer method
<sql-session-dbi-cp>)) ¶automatically generated reader method
<sql-session-dbi-cp>)) ¶automatically generated writer method
<sql-session-dbi-cp>) sql params) ¶<sql-session-dbi>) sql params) ¶dbi-cp.proxy:<dbi-connection-proxy>
(error "missing initarg")
:proxy
dbi.driver:<dbi-connection>
(error "missing initarg")
:connection
| Jump to: | (
C D F G L M P R S T U W |
|---|
| Jump to: | (
C D F G L M P R S T U W |
|---|
| 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 | ||
| | |||
| Jump to: | C P S |
|---|
| Jump to: | <
B C D F M P S |
|---|
| Jump to: | <
B C D F M P S |
|---|