Next: Introduction, Previous: (dir), Up: (dir) [Contents][Index]
This is the cl-batis Reference Manual, version 0.1, generated automatically by Declt version 2.4 patchlevel 1 "Will Decker" on Mon Apr 08 13:08:57 2019 GMT+0.
| • Introduction | What cl-batis is all about | |
| • Systems | The systems documentation | |
| • Modules | The modules documentation | |
| • Files | The files documentation | |
| • Packages | The packages documentation | |
| • Definitions | The symbols documentation | |
| • Indexes | Concepts, functions, variables and data types |
;; 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"))
Cl-Batis does not support DDL.
If you want to use DDL, use do-sql.
(do-sql session "truncate table product")
There are two type of methods.
updateselectwhen use (cl-syntax:use-syntax :annot), @update and @select can be used.
@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 ("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))
@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:
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:
select * from product where
and valid_flag = '1'
This would also fail.
So, cl-batis provides SQL-WHERE function.
@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.
@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.
(update-one *session* register-product :id 1 :name "NES" :price 14800)
(select-one *session* search-product :id 1)
-> (:|name| "NES" :|price| 14800))
(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)))
(commit *session*)
(rollback *session*)
(close-sql-session *session*)
;;;
;;; 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))
This library is available on Quicklisp.
(ql:quickload :cl-batis)
Copyright (c) 2017 tamura shingo (tamura.shingo@gmail.com)
Licensed under the MIT License.
Next: Modules, Previous: Introduction, Up: Top [Contents][Index]
The main system appears first, followed by any subsystem dependency.
| • The cl-batis system | ||
| • The batis system |
Next: The batis system, Previous: Systems, Up: Systems [Contents][Index]
tamura shingo
MIT
0.1
batis (system)
cl-batis.asd (file)
Previous: The cl-batis system, Up: Systems [Contents][Index]
tamura shingo
MIT
SQL Mapping Framework for Common Lisp
# Cl-Batis - SQL Mapping Framework for Common Lisp
[](https://travis-ci.org/tamurashingo/cl-batis)
## 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
“‘common-lisp
(commit *session*)
(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.
“‘commonlisp
(ql:quickload :cl-batis)
“‘
## Author
* tamura shingo (tamura.shingo@gmail.com)
## Copyright
Copyright (c) 2017 tamura shingo (tamura.shingo@gmail.com)
## License
Licensed under the MIT License.
0.1
batis.asd (file)
src (module)
Modules are listed depth-first from the system components tree.
| • The batis/src module |
batis (system)
src/
Files are sorted by type and then listed depth-first from the systems components trees.
| • Lisp files |
Next: The batis<dot>asd file, Previous: Lisp files, Up: Lisp files [Contents][Index]
cl-batis.asd
cl-batis (system)
Next: The batis/src/batis<dot>lisp file, Previous: The cl-batis<dot>asd file, Up: Lisp files [Contents][Index]
Next: The batis/src/macro<dot>lisp file, Previous: The batis<dot>asd file, Up: Lisp files [Contents][Index]
src (module)
src/batis.lisp
Next: The batis/src/sqlparser<dot>lisp file, Previous: The batis/src/batis<dot>lisp file, Up: Lisp files [Contents][Index]
sql.lisp (file)
src (module)
src/macro.lisp
Next: The batis/src/dbi<dot>lisp file, Previous: The batis/src/macro<dot>lisp file, Up: Lisp files [Contents][Index]
src (module)
src/sqlparser.lisp
parse (function)
Next: The batis/src/sql<dot>lisp file, Previous: The batis/src/sqlparser<dot>lisp file, Up: Lisp files [Contents][Index]
Next: The batis/src/datasource<dot>lisp file, Previous: The batis/src/dbi<dot>lisp file, Up: Lisp files [Contents][Index]
src (module)
src/sql.lisp
Previous: The batis/src/sql<dot>lisp file, Up: Lisp files [Contents][Index]
src (module)
src/datasource.lisp
Next: Definitions, Previous: Files, Up: Top [Contents][Index]
Packages are listed by definition order.
Next: The batis-asd package, Previous: Packages, Up: Packages [Contents][Index]
cl-batis.asd
Next: The batis package, Previous: The cl-batis-asd package, Up: Packages [Contents][Index]
batis.asd
Next: The batis<dot>macro package, Previous: The batis-asd package, Up: Packages [Contents][Index]
batis.lisp (file)
cl-batis
Next: The batis<dot>sqlparser package, Previous: The batis package, Up: Packages [Contents][Index]
macro.lisp (file)
Next: The batis<dot>dbi package, Previous: The batis<dot>macro package, Up: Packages [Contents][Index]
sqlparser.lisp (file)
common-lisp
parse (function)
Next: The batis<dot>sql package, Previous: The batis<dot>sqlparser package, Up: Packages [Contents][Index]
dbi.lisp (file)
common-lisp
Next: The batis<dot>datasource package, Previous: The batis<dot>dbi package, Up: Packages [Contents][Index]
sql.lisp (file)
Previous: The batis<dot>sql package, Up: Packages [Contents][Index]
datasource.lisp (file)
Definitions are sorted by export status, category, package, and then by lexicographic order.
| • Exported definitions | ||
| • Internal definitions |
Next: Internal definitions, Previous: Definitions, Up: Definitions [Contents][Index]
| • Exported macros | ||
| • Exported functions | ||
| • Exported generic functions |
Next: Exported functions, Previous: Exported definitions, Up: Exported definitions [Contents][Index]
define sql name and its args
macro.lisp (file)
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))
macro.lisp (file)
apply sql-body when test-form is true
Example:
(sql-cond (not (null product_name))
" and product_name like :product_name ")
macro.lisp (file)
define UPDATE SQL
Example:
@select ("update product set product_price = :product_price where product_id = :product_id")
(defsql update_price (product_price product_id))
macro.lisp (file)
Next: Exported generic functions, Previous: Exported macros, Up: Exported definitions [Contents][Index]
generate prepared-type SQL and parameters
sqlparser.lisp (file)
insert ‘SET‘.
Furthermore, if that content ends with ‘,‘, strip it off.
macro.lisp (file)
insert ‘WHERE‘ if there is any condition.
Furthermore, if that content begins with ‘AND‘ or ‘OR‘, strip it off.
macro.lisp (file)
Previous: Exported functions, Up: Exported definitions [Contents][Index]
datasource.lisp (file)
datasource.lisp (file)
datasource.lisp (file)
datasource.lisp (file)
datasource.lisp (file)
datasource.lisp (file)
datasource.lisp (file)
datasource.lisp (file)
datasource.lisp (file)
Previous: Exported definitions, Up: Definitions [Contents][Index]
| • Internal functions | ||
| • Internal generic functions | ||
| • Internal classes |
Next: Internal generic functions, Previous: Internal definitions, Up: Internal definitions [Contents][Index]
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
sqlparser.lisp (file)
sqlparser.lisp (file)
sqlparser.lisp (file)
sqlparser.lisp (file)
macro.lisp (file)
macro.lisp (file)
Next: Internal classes, Previous: Internal functions, Up: Internal definitions [Contents][Index]
automatically generated reader method
datasource.lisp (file)
automatically generated writer method
datasource.lisp (file)
automatically generated reader method
datasource.lisp (file)
automatically generated writer method
datasource.lisp (file)
Previous: Internal generic functions, Up: Internal definitions [Contents][Index]
datasource.lisp (file)
<sql-session> (class)
dbi-cp.proxy:<dbi-connection-proxy>
:proxy
proxy (generic function)
(setf proxy) (generic function)
datasource.lisp (file)
<sql-session> (class)
datasource.lisp (file)
standard-object (class)
dbi.driver:<dbi-connection>
:connection
connection (generic function)
(setf connection) (generic function)
Previous: Definitions, Up: Top [Contents][Index]
| • Concept index | ||
| • Function index | ||
| • Variable index | ||
| • Data type index |
Next: Function index, Previous: Indexes, Up: Indexes [Contents][Index]
| Jump to: | B C F L M |
|---|
| Jump to: | B C F L M |
|---|
Next: Variable index, Previous: Concept index, Up: Indexes [Contents][Index]
| Jump to: | (
C D F G L M P R S T U |
|---|
| Jump to: | (
C D F G L M P R S T U |
|---|
Next: Data type index, Previous: Function index, Up: Indexes [Contents][Index]
| Jump to: | C P S |
|---|
| Index Entry | Section | ||
|---|---|---|---|
| | |||
| C | |||
connection: | Internal classes | ||
| | |||
| P | |||
proxy: | Internal classes | ||
| | |||
| S | |||
Slot, connection: | Internal classes | ||
Slot, proxy: | Internal classes | ||
| | |||
| Jump to: | C P S |
|---|
Previous: Variable index, Up: Indexes [Contents][Index]
| Jump to: | <
B C P S |
|---|
| Jump to: | <
B C P S |
|---|