CL-DBI-Connection-Pool - connection pool for CL-DBI

This library provides connection pool for CL-DBI.

Usage

Create connection pool

make connection pool.

(dbi-cp:make-db-connection-pool driver-name &key database-name username password (initial-size 10) (max-size 10)) ;; => dbi-cp:<dbi-connection-pool>

• driver-name
  • :sqlite, :mysql, :postgresql (same as CL-DBI)
• database-name
  • database name (same as CL-DBI)
• username
  • username (same as CL-DBI)
• password
  • password (same as CL-DBI)
• initial-size
  • initial number of connections that are created when the pool is started
• max-size
  • maximum number of connections

Additionally, it accepts parameters used in cl-dbi.

(defparameter *CONNECTION-POOL*
  (dbi-cp:make-dbi-connection-pool :mysql :database-name "dbi-cp" :username "root" :password "password"))

Get connection

get database connection from connection pool.

(dbi-cp:get-connection connection-pool) ;; => dbi-cp.proxy:<dbi-connection-proxy>

• connection-pool (dbi-cp:<dbi-connection-pool>)
  • connection pool

(setf conn (dbi-cp:get-connection *CONNECTION-POOL*))

prepare, execute, fetch ...

Prepare

prepare SQL statement.

(dbi-cp:prepare connection sql) ;; => dbi.driver:<dbi-query>

• connection (dbi-cp.proxy:<dbi-connection-proxy>)
  • database connection
• sql
  • SQL statement

this function is based on CL-DBI

Execute

execute SQL.

(dbi-cp:execute query &optional params) ;; => dbi.driver:<dbi-query>

• query (dbi.driver:<dbi-query>)
  • precompiled query (returned by prepare)
• params
  • SQL parameters

this function is based on CL-DBI

Fetch

fetch first row from query which is returned by execute.

(dbi-cp:fetch query) ;; => result

• query (dbi.driver:<dbi-query>)
  • returned by execute

this function is based on CL-DBI

Fetch all

fetch all ret row from query.

(dbi-cp:fetch-all query) ;; => result

• query (dbi.driver:<dbi-query>)
  • returned by execute

this function is based on CL-DBI

row count

return the number of counts modified by last execute INSERT/UPDATE/DELETE query.

(dbi-cp:row-count connection) ;; => number

• connection (dbi-cp.proxy:<dbi-connection-proxy>)
  • database connection

this function is based on CL-DBI

do sql

do preparation and execution at once for INSERT, UPDATE, DELETE or DDL.

(dbi-cp:do-sql connection sql &optional params)

• connection (dbi-cp.proxy:<dbi-connection-proxy>)
  • database connection
• sql
  • SQL statement
• params
  • SQL parameters

this function is based on CL-DBI

Transaction

create transaction block

start a transaction and commit at the end of this block. if the evaluation body is interrupted, the transaction is rolled back automatically.

(dbi-cp:with-transaction connection &body body)

• connection (dbi-cp.proxy:<dbi-connection-proxy>)
  • database connection
• body
  • body

this function is based on CL-DBI

commit

Within with-transaction, you can use commit. Outside of with-transaction, commit does nothing.

(dbi-cp:commit connection)

• connection (dbi-cp.proxy:<dbi-connection-proxy>)
  • database connection

this function is based on CL-DBI

rollback

Like commit, rollback is also executed within with-transaction.

(dbi-cp:rollback connection)

• connection (dbi-cp.proxy:<dbi-connection-proxy>)
  • database connection

this function is based on CL-DBI

savepoint

set a named transaction savepoint with a name of identifier.

(dbi-cp:savepoint connection identifier)

• connection (dbi-cp.proxy:<dbi-connection-proxy>)
  • database connection
• identifier
  • name of transaction savepoint

this function is based on CL-DBI

rollback savepoint

rollback a transaction to the named savepoint.

(dbi-cp:rollback-savepoint connection &optional identifier)

• connection (dbi-cp.proxy:<dbi-connection-proxy>)
  • database connection
• identifier
  • name of transaction savepoint

this function is based on CL-DBI

release savepoint

remove the named savepoint. no commit or rollback occurs.

(dbi-cp:release-savepoint connection &optional identifier)

• connection (dbi-cp.proxy:<dbi-connection-proxy>)
  • database connection
• identifier
  • name of transaction savepoint

this function is based on CL-DBI

Example

;;;
;;; create connection pool
;;;
CL-USER> (defparameter *pool*
           (dbi-cp:make-dbi-connection-pool :mysql
                                            :database-name "test"
                                            :username "root"
                                            :password "password"))
*POOL*

;;;
;;; get database connection
;;;
CL-USER> (defparameter connection (dbi-cp:get-connection *pool*))
CONNECTION
CL-USER> connection
#<DBI-CP.PROXY:<DBI-CONNECTION-PROXY> {1002E23973}>

;;;
;;; execute DDL
;;;
CL-USER> (dbi-cp:do-sql connection "create table person (id integer primary key, name varchar(24) not null)")
; No value

;;;
;;; select
;;;
CL-USER> (let* ((query (dbi-cp:prepare connection "select count(*) from person"))
                (result (dbi-cp:execute query)))
           (format T "~A" (dbi-cp:fetch result)))
(count(*) 0)
NIL

;;;
;;; insert
;;;
CL-USER> (dbi-cp:with-transaction connection
           (let* ((query (dbi-cp:prepare connection "insert into person (id, name) values (?, ?)")))
             (dbi-cp:execute query (list 1 "user1"))
             (dbi-cp:execute query (list 2 "user2"))
             (dbi-cp:execute query (list 3 "user3"))))
#<DBD.MYSQL:<DBD-MYSQL-QUERY> {1004B671F3}>

;;;
;;; select
;;;
CL-USER> (let* ((query (dbi-cp:prepare connection "select * from person"))
                (result (dbi-cp:execute query)))
           (dbi-cp:fetch-all result))
((:|id| 1 :|name| "user1") (:|id| 2 :|name| "user2") (:|id| 3 :|name| "user3"))

;;;
;;; rollback
;;;
CL-USER> (dbi-cp:with-transaction connection
           (dbi-cp:execute (dbi-cp:prepare connection "delete from person"))
           (rollback connection))
0
CL-USER> (dbi-cp:rollback connection)
; No value
CL-USER> (let* ((query (dbi-cp:prepare connection "select count(*) from person"))
                (result (dbi-cp:execute query)))
           (format T "~A" (dbi-cp:fetch result)))
(count(*) 3)
NIL

;;;
;;; release connection
;;;
CL-USER> (dbi-cp:disconnect connection)
NIL

;;;
;;; shutdown connection pool
;;;
CL-USER> (dbi-cp:shutdown *pool*)
NIL

Installation

This library is available on Quicklisp.

(ql:quickload :cl-dbi-connection-pool)

how to develop

require

• make
• docker

test

prepare

To prepare, create a docker image for testing.

make setup

run test

make test

swank server

Start the swank server.

make test.swank

connect with SLIME.

M-x slime-connect 127.0.0.1 4005

create connection pool

(ql:quickload :cl-dbi-connection-pool)

(defvar *pool-sqlite3* (dbi-cp:make-dbi-connection-pool :sqlite3
                                                        :database-name "/app/volumes/sqlite3-test.db"))

(defvar *pool-mysql* (dbi-cp:make-dbi-connection-pool :mysql
                                                      :database-name "test"
                                                      :username "root"
                                                      :password "password"
                                                      :host "mysql-test"
                                                      :port 3306))

(defvar *pool-postgres* (dbi-cp:make-dbi-connection-pool :postgres
                                                         :database-name "test"
                                                         :username "dbicp"
                                                         :password "password"
                                                         :host "postgresql-test"
                                                         :port 5432))

stop test containers

make test.down

Author

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

Copyright

Copyright (c) 2017 tamura shingo (tamura.shingo@gmail.com)

License

Licensed under the LLGPL License.