1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
|
;;;; basic.scm --- -*- mode: scheme; coding: utf-8; -*-
;;;;
;;;; Copyright (C) 2011 Detlev Zundel <dzu@denx.de>
;;;; Copyright (C) 2018 Ludovic Courtès <ludo@gnu.org>
;;;;
;;;; This library is free software; you can redistribute it and/or
;;;; modify it under the terms of the GNU Lesser General Public
;;;; License as published by the Free Software Foundation; either
;;;; version 3 of the License, or (at your option) any later version.
;;;;
;;;; This library is distributed in the hope that it will be useful,
;;;; but WITHOUT ANY WARRANTY; without even the implied warranty of
;;;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
;;;; Lesser General Public License for more details.
;;;;
;;;; You should have received a copy of the GNU Lesser General Public
;;;; License along with this library; if not, write to the Free Software
;;;; Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
(define-module (tests basic-test)
#:use-module (srfi srfi-64)
#:use-module (ice-9 format)
#:use-module (system foreign)
#:use-module (rnrs bytevectors)
#:use-module (sqlite3))
(define (sqlite-exec* db sql key value)
(let ((stmt (sqlite-prepare db sql)))
(sqlite-bind stmt key value)
(sqlite-map display stmt)
(sqlite-finalize stmt)
#t))
;; Cleanup database so we can check creation
(define db-name "tests/simple.db")
(if (file-exists? db-name)
(begin
(format #t "Removing leftover database ~a~%" db-name)
(delete-file db-name)))
(define db
;; Global database used for tests.
#f)
(test-begin "basic")
(test-assert "sqlite-open"
(begin
(set! db (sqlite-open db-name (logior SQLITE_OPEN_CREATE
SQLITE_OPEN_READWRITE)))
(sqlite-db? db)))
(test-assert "sqlite-busy-timeout"
(sqlite-busy-timeout db 20))
(test-assert "create table"
(sqlite-exec db
"create table project (
reference integer primary key,
name text,
website text
)"))
(test-assert "insert"
(sqlite-exec db
"insert into project values (1, 'Guile', '');
insert into project values (2, 'Guix', 'gnu.org');"))
(test-assert "sqlite-prepare with caching"
(let* ((s "SELECT * FROM project")
(stmt (sqlite-prepare db s #:cache? #t)))
(eq? stmt (sqlite-prepare db s #:cache? #t))))
(test-equal "select"
'(#(1 "Guile" "") #(2 "Guix" "gnu.org"))
(let* ((stmt (sqlite-prepare db "select * from project"))
(result (sqlite-map identity stmt)))
(sqlite-finalize stmt)
(sqlite-finalize stmt) ; no-op
result))
(test-assert "select with named parameters"
(sqlite-exec* db "select * from project where 'bla' = :foo" ":foo" "bla"))
(test-assert "select with named parameters, alternate form"
(sqlite-exec* db "select * from project where 'bla' = :foo" 'foo "bla"))
(test-assert "insert with sqlite-bind"
(begin
(sqlite-exec db "CREATE TABLE foos (dbid INTEGER PRIMARY KEY, name TEXT)")
(let ((stmt (sqlite-prepare db "INSERT INTO foos(name) VALUES(?)")))
(sqlite-bind stmt 1 "myfoo")
(sqlite-step stmt)
(sqlite-finalize stmt)
#t)))
(test-assert "drop"
(sqlite-exec db "DROP TABLE IF EXISTS foos"))
(define bv
(let* ((n 1023)
(v (make-bytevector n)))
(do ((i 0 (1+ i)))
((>= i n))
(bytevector-u8-set! v i (random 256)))
v))
(test-assert "insert blob"
(begin
(sqlite-exec db "CREATE TABLE cow (biggie blob)")
(let ((stmt (sqlite-prepare db "INSERT INTO cow (biggie) VALUES(?)")))
(sqlite-bind stmt 1 bv)
(sqlite-step stmt)
(sqlite-finalize stmt)
#t)))
(test-assert "select blob"
(let* ((stmt (sqlite-prepare db "SELECT biggie from cow"))
(res (vector-ref (car (sqlite-map identity stmt)) 0)))
; (display res)(newline)
; (display bv)(newline)
(bytevector=? res bv)))
(begin
(sqlite-trace db
SQLITE_TRACE_STMT
(lambda (trace p x)
(test-assert "trace"
(string=? (pointer->string
(sqlite-expanded-sql p))
"select * from project where 'bla' = 'bla'"))))
(sqlite-exec* db "select * from project where 'bla' = :foo" 'foo "bla"))
(sqlite-close db)
(delete-file db-name)
(test-end "basic")
|
