1 (defpackage :sqlite-tests
2 (:use
:cl
:sqlite
:5am
:iter
)
3 (:export
:run-all-tests
))
5 (in-package :sqlite-tests
)
7 (def-suite sqlite-suite
)
9 (defun run-all-tests ()
12 (in-suite sqlite-suite
)
15 (with-open-database (db ":memory:")))
17 (test test-disconnect-with-statements
19 (with-open-database (db ":memory:")
20 (prepare-statement db
"create table users (id integer primary key, user_name text not null, age integer null)"))))
22 (defmacro with-inserted-data
((db) &body body
)
23 `(with-open-database (,db
":memory:")
24 (execute-non-query ,db
"create table users (id integer primary key, user_name text not null, age integer null)")
25 (execute-non-query ,db
"insert into users (user_name, age) values (?, ?)" "joe" 18)
26 (execute-non-query ,db
"insert into users (user_name, age) values (?, ?)" "dvk" 22)
27 (execute-non-query ,db
"insert into users (user_name, age) values (?, ?)" "qwe" 30)
30 (test create-table-insert-and-error
31 (with-inserted-data (db)
33 (execute-non-query db
"insert into users (user_name, age) values (?, ?)" nil nil
))))
35 (test test-select-single
36 (with-inserted-data (db)
37 (is (= (execute-single db
"select id from users where user_name = ?" "dvk")
41 (with-inserted-data (db)
42 (is (equalp (multiple-value-list (execute-one-row-m-v db
"select id, user_name, age from users where user_name = ?" "joe"))
45 (test test-select-list
46 (with-inserted-data (db)
47 (is (equalp (execute-to-list db
"select id, user_name, age from users")
48 '((1 "joe" 18) (2 "dvk" 22) (3 "qwe" 30))))))
51 (with-inserted-data (db)
52 (is (equalp (iter (for (id user-name age
) in-sqlite-query
"select id, user_name, age from users where age < ?" on-database db with-parameters
(25))
53 (collect (list id user-name age
)))
54 '((1 "joe" 18) (2 "dvk" 22))))))
56 (test test-loop-with-prepared-statement
57 (with-inserted-data (db)
59 with statement
= (prepare-statement db
"select id, user_name, age from users where age < ?")
60 initially
(bind-parameter statement
1 25)
61 while
(step-statement statement
)
62 collect
(list (statement-column-value statement
0) (statement-column-value statement
1) (statement-column-value statement
2))
63 finally
(finalize-statement statement
))
64 '((1 "joe" 18) (2 "dvk" 22))))))