1 (defpackage :sqlite-tests
2 (:use
:cl
:sqlite
:5am
:iter
:bordeaux-threads
)
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)
32 (signals sqlite-constraint-error
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 (prepare-statement db
"select id, user_name, age from users where age < $x")))
62 (is (equalp (statement-column-names statement
)
63 '("id" "user_name" "age")))
64 (is (equalp (statement-bind-parameter-names statement
)
66 (bind-parameter statement
1 25)
68 (loop while
(step-statement statement
)
69 collect
(list (statement-column-value statement
0)
70 (statement-column-value statement
1)
71 (statement-column-value statement
2))
72 finally
(reset-statement statement
))))
73 (is (equalp (fetch-all) '((1 "joe" 18) (2 "dvk" 22))))
74 (is (equalp (fetch-all) '((1 "joe" 18) (2 "dvk" 22))))
75 (clear-statement-bindings statement
)
76 (is (equalp (fetch-all) '()))))
77 (finalize-statement statement
)))))
80 (defparameter *db-file
* "/tmp/test.sqlite")
83 (defun ensure-table ()
84 (with-open-database (db *db-file
*)
85 (execute-non-query db
"CREATE TABLE IF NOT EXISTS FOO (v NUMERIC)")))
88 (test test-concurrent-inserts
89 (when (probe-file *db-file
*)
90 (delete-file *db-file
*))
93 (do-zillions 10 10000)
94 (when (probe-file *db-file
*)
95 (delete-file *db-file
*))))
98 (defun do-insert (n timeout
)
99 "Insert a nonsense value into foo"
101 (with-open-database (db *db-file
* :busy-timeout timeout
)
103 (execute-non-query db
"INSERT INTO FOO (v) VALUES (?)" n
)))
107 (defun do-zillions (max-n timeout
)
108 (iterate (for n from
1 to max-n
)
110 (bt:make-thread
(let ((n n
))
112 (do-insert n timeout
))))
115 (iter (for thread in threads
)
117 (for thread-result
= (bt:join-thread thread
))
118 (unless thread-result
120 (finally (is-true all-ok
"One of inserter threads encountered a SQLITE_BUSY error"))))))