sqlite_test.go 9.8 KB
Newer Older
gwenn's avatar
gwenn committed
1
package sqlite_test
Patrick Crosby's avatar
Patrick Crosby committed
2 3

import (
gwenn's avatar
gwenn committed
4
	. "github.com/gwenn/gosqlite"
5
	"reflect"
gwenn's avatar
gwenn committed
6
	"strings"
Patrick Crosby's avatar
Patrick Crosby committed
7 8 9
	"testing"
)

10
func checkNoError(t *testing.T, err error, format string) {
Patrick Crosby's avatar
Patrick Crosby committed
11
	if err != nil {
12
		t.Fatalf(format, err)
Patrick Crosby's avatar
Patrick Crosby committed
13
	}
14 15 16 17 18
}

func open(t *testing.T) *Conn {
	db, err := Open("", OPEN_READWRITE, OPEN_CREATE, OPEN_FULLMUTEX, OPEN_URI)
	checkNoError(t, err, "couldn't open database file: %s")
Patrick Crosby's avatar
Patrick Crosby committed
19
	if db == nil {
gwenn's avatar
gwenn committed
20
		t.Fatal("opened database is nil")
Patrick Crosby's avatar
Patrick Crosby committed
21
	}
gwenn's avatar
Misc.  
gwenn committed
22
	//db.Profile(profile, "PROFILE")
gwenn's avatar
gwenn committed
23
	return db
Patrick Crosby's avatar
Patrick Crosby committed
24 25
}

gwenn's avatar
gwenn committed
26 27 28 29
func createTable(db *Conn, t *testing.T) {
	err := db.Exec("DROP TABLE IF EXISTS test;" +
		"CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT," +
		" float_num REAL, int_num INTEGER, a_string TEXT); -- bim")
30
	checkNoError(t, err, "error creating table: %s")
Patrick Crosby's avatar
Patrick Crosby committed
31 32
}

gwenn's avatar
gwenn committed
33 34 35 36 37 38 39
func TestVersion(t *testing.T) {
	v := Version()
	if !strings.HasPrefix(v, "3") {
		t.Fatalf("unexpected library version: %s", v)
	}
}

gwenn's avatar
gwenn committed
40 41
func TestOpen(t *testing.T) {
	db := open(t)
42
	checkNoError(t, db.Close(), "Error closing database: %s")
gwenn's avatar
gwenn committed
43 44
}

gwenn's avatar
gwenn committed
45 46 47
func TestEnableFKey(t *testing.T) {
	db := open(t)
	defer db.Close()
48
	b := Must(db.IsFKeyEnabled())
gwenn's avatar
gwenn committed
49
	if !b {
50
		b = Must(db.EnableFKey(true))
gwenn's avatar
gwenn committed
51 52 53 54 55 56
		if !b {
			t.Error("cannot enabled FK")
		}
	}
}

57 58 59
func TestEnableExtendedResultCodes(t *testing.T) {
	db := open(t)
	defer db.Close()
60
	checkNoError(t, db.EnableExtendedResultCodes(true), "cannot enabled extended result codes: %s")
61 62
}

63 64 65
func TestIntegrityCheck(t *testing.T) {
	db := open(t)
	defer db.Close()
66
	checkNoError(t, db.IntegrityCheck(1, true), "Error checking integrity of database: %s")
67 68
}

gwenn's avatar
gwenn committed
69 70 71 72
func TestCreateTable(t *testing.T) {
	db := open(t)
	defer db.Close()
	createTable(db, t)
Patrick Crosby's avatar
Patrick Crosby committed
73 74
}

gwenn's avatar
gwenn committed
75 76 77
func TestTransaction(t *testing.T) {
	db := open(t)
	defer db.Close()
78
	checkNoError(t, db.Begin(), "Error while beginning transaction: %s")
gwenn's avatar
gwenn committed
79 80 81
	if err := db.Begin(); err == nil {
		t.Fatalf("Error expected (transaction cannot be nested)")
	}
82
	checkNoError(t, db.Commit(), "Error while commiting transaction: %s")
gwenn's avatar
gwenn committed
83 84
}

85 86 87
func TestExists(t *testing.T) {
	db := open(t)
	defer db.Close()
88
	b := Must(db.Exists("SELECT 1 where 1 = 0"))
89 90 91
	if b {
		t.Error("No row expected")
	}
92
	b = Must(db.Exists("SELECT 1 where 1 = 1"))
93 94 95 96 97
	if !b {
		t.Error("One row expected")
	}
}

Patrick Crosby's avatar
Patrick Crosby committed
98
func TestInsert(t *testing.T) {
gwenn's avatar
gwenn committed
99 100 101
	db := open(t)
	defer db.Close()
	createTable(db, t)
gwenn's avatar
gwenn committed
102
	db.Begin()
Patrick Crosby's avatar
Patrick Crosby committed
103 104
	for i := 0; i < 1000; i++ {
		ierr := db.Exec("INSERT INTO test (float_num, int_num, a_string) VALUES (?, ?, ?)", float64(i)*float64(3.14), i, "hello")
105
		checkNoError(t, ierr, "insert error: %s")
gwenn's avatar
gwenn committed
106 107
		c := db.Changes()
		if c != 1 {
108
			t.Errorf("insert error: %d but got 1", c)
Patrick Crosby's avatar
Patrick Crosby committed
109 110
		}
	}
111
	checkNoError(t, db.Commit(), "Error: %s")
Patrick Crosby's avatar
Patrick Crosby committed
112

gwenn's avatar
gwenn committed
113 114
	lastId := db.LastInsertRowid()
	if lastId != 1000 {
115
		t.Errorf("last insert row id error: %d but got 1000", lastId)
gwenn's avatar
gwenn committed
116 117
	}

Patrick Crosby's avatar
Patrick Crosby committed
118
	cs, _ := db.Prepare("SELECT COUNT(*) FROM test")
gwenn's avatar
gwenn committed
119
	defer cs.Finalize()
gwenn's avatar
gwenn committed
120 121 122

	paramCount := cs.BindParameterCount()
	if paramCount != 0 {
123
		t.Errorf("bind parameter count error: %d but got 0", paramCount)
gwenn's avatar
gwenn committed
124 125 126
	}
	columnCount := cs.ColumnCount()
	if columnCount != 1 {
127
		t.Errorf("column count error: %d but got 1", columnCount)
gwenn's avatar
gwenn committed
128 129
	}

gwenn's avatar
gwenn committed
130
	if !Must(cs.Next()) {
gwenn's avatar
gwenn committed
131
		t.Fatal("no result for count")
Patrick Crosby's avatar
Patrick Crosby committed
132 133
	}
	var i int
134
	checkNoError(t, cs.Scan(&i), "error scanning count: %s")
Patrick Crosby's avatar
Patrick Crosby committed
135 136 137 138 139 140
	if i != 1000 {
		t.Errorf("count should be 1000, but it is %d", i)
	}
}

func TestInsertWithStatement(t *testing.T) {
gwenn's avatar
gwenn committed
141 142 143
	db := open(t)
	defer db.Close()
	createTable(db, t)
gwenn's avatar
gwenn committed
144
	s, serr := db.Prepare("INSERT INTO test (float_num, int_num, a_string) VALUES (:f, :i, :s)")
145
	checkNoError(t, serr, "prepare error: %s")
Patrick Crosby's avatar
Patrick Crosby committed
146
	if s == nil {
gwenn's avatar
gwenn committed
147
		t.Fatal("statement is nil")
Patrick Crosby's avatar
Patrick Crosby committed
148
	}
gwenn's avatar
gwenn committed
149
	defer s.Finalize()
Patrick Crosby's avatar
Patrick Crosby committed
150

151
	if s.ReadOnly() {
gwenn's avatar
gwenn committed
152
		t.Errorf("update statement should not be readonly")
153 154
	}

gwenn's avatar
gwenn committed
155 156
	paramCount := s.BindParameterCount()
	if paramCount != 3 {
157
		t.Errorf("bind parameter count error: %d but got 3", paramCount)
gwenn's avatar
gwenn committed
158
	}
gwenn's avatar
gwenn committed
159
	firstParamName, berr := s.BindParameterName(1)
gwenn's avatar
gwenn committed
160
	if firstParamName != ":f" {
161
		t.Errorf("bind parameter name error: %s but got ':f' (%s)", firstParamName, berr)
gwenn's avatar
gwenn committed
162
	}
gwenn's avatar
gwenn committed
163
	lastParamIndex, berr := s.BindParameterIndex(":s")
gwenn's avatar
gwenn committed
164
	if lastParamIndex != 3 {
165
		t.Errorf("bind parameter name error: %d but got 3 (%s)", lastParamIndex, berr)
gwenn's avatar
gwenn committed
166
	}
gwenn's avatar
gwenn committed
167

gwenn's avatar
gwenn committed
168
	db.Begin()
Patrick Crosby's avatar
Patrick Crosby committed
169
	for i := 0; i < 1000; i++ {
gwenn's avatar
gwenn committed
170
		c, ierr := s.ExecUpdate(float64(i)*float64(3.14), i, "hello")
171
		checkNoError(t, ierr, "insert error: %s")
gwenn's avatar
gwenn committed
172
		if c != 1 {
173
			t.Errorf("insert error: %d but got 1", c)
Patrick Crosby's avatar
Patrick Crosby committed
174 175
		}
	}
gwenn's avatar
gwenn committed
176

177
	checkNoError(t, db.Commit(), "Error: %s")
Patrick Crosby's avatar
Patrick Crosby committed
178 179

	cs, _ := db.Prepare("SELECT COUNT(*) FROM test")
gwenn's avatar
gwenn committed
180
	defer cs.Finalize()
181
	if !cs.ReadOnly() {
gwenn's avatar
gwenn committed
182
		t.Errorf("select statement should be readonly")
183
	}
gwenn's avatar
gwenn committed
184
	if !Must(cs.Next()) {
gwenn's avatar
gwenn committed
185
		t.Fatal("no result for count")
Patrick Crosby's avatar
Patrick Crosby committed
186 187
	}
	var i int
188
	checkNoError(t, cs.Scan(&i), "error scanning count: %s")
Patrick Crosby's avatar
Patrick Crosby committed
189 190 191 192
	if i != 1000 {
		t.Errorf("count should be 1000, but it is %d", i)
	}

193
	rs, _ := db.Prepare("SELECT float_num, int_num, a_string FROM test where a_string like ? ORDER BY int_num LIMIT 2", "hel%")
gwenn's avatar
gwenn committed
194
	defer rs.Finalize()
gwenn's avatar
gwenn committed
195 196
	columnCount := rs.ColumnCount()
	if columnCount != 3 {
197
		t.Errorf("column count error: %d but got 3", columnCount)
gwenn's avatar
gwenn committed
198 199 200
	}
	secondColumnName := rs.ColumnName(1)
	if secondColumnName != "int_num" {
201
		t.Errorf("column name error: %s but got 'int_num'", secondColumnName)
gwenn's avatar
gwenn committed
202 203
	}

gwenn's avatar
gwenn committed
204
	if Must(rs.Next()) {
205 206 207
		var fnum float64
		var inum int64
		var sstr string
gwenn's avatar
gwenn committed
208 209
		rs.Scan(&fnum, &inum, &sstr)
		if fnum != 0 {
210
			t.Errorf("Expected 0 but got %f\n", fnum)
gwenn's avatar
gwenn committed
211 212
		}
		if inum != 0 {
213
			t.Errorf("Expected 0 but got %d\n", inum)
gwenn's avatar
gwenn committed
214 215
		}
		if sstr != "hello" {
216
			t.Errorf("Expected 'hello' but got %s\n", sstr)
gwenn's avatar
gwenn committed
217 218
		}
	}
gwenn's avatar
gwenn committed
219
	if Must(rs.Next()) {
gwenn's avatar
gwenn committed
220 221 222 223 224
		var fnum float64
		var inum int64
		var sstr string
		rs.NamedScan("a_string", &sstr, "float_num", &fnum, "int_num", &inum)
		if fnum != 3.14 {
225
			t.Errorf("Expected 3.14 but got %f\n", fnum)
gwenn's avatar
gwenn committed
226 227
		}
		if inum != 1 {
228
			t.Errorf("Expected 1 but got %d\n", inum)
gwenn's avatar
gwenn committed
229 230
		}
		if sstr != "hello" {
231
			t.Errorf("Expected 'hello' but got %s\n", sstr)
gwenn's avatar
gwenn committed
232 233
		}
	}
gwenn's avatar
gwenn committed
234 235 236 237 238 239 240 241 242
	if 999 != rs.Status(STMTSTATUS_FULLSCAN_STEP, false) {
		t.Errorf("Expected full scan")
	}
	if 1 != rs.Status(STMTSTATUS_SORT, false) {
		t.Errorf("Expected one sort")
	}
	if 0 != rs.Status(STMTSTATUS_AUTOINDEX, false) {
		t.Errorf("Expected no auto index")
	}
Patrick Crosby's avatar
Patrick Crosby committed
243 244
}

gwenn's avatar
gwenn committed
245 246 247 248 249
func TestBlob(t *testing.T) {
	db := open(t)
	defer db.Close()

	err := db.Exec("CREATE TABLE test (content BLOB);")
250
	checkNoError(t, err, "error creating table: %s")
gwenn's avatar
gwenn committed
251
	s, err := db.Prepare("INSERT INTO test VALUES (?)")
252
	checkNoError(t, err, "prepare error: %s")
gwenn's avatar
gwenn committed
253 254 255 256 257
	if s == nil {
		t.Fatal("statement is nil")
	}
	defer s.Finalize()
	err = s.Exec(ZeroBlobLength(10))
258
	checkNoError(t, err, "insert error: %s")
gwenn's avatar
gwenn committed
259 260 261
	rowid := db.LastInsertRowid()

	bw, err := db.NewBlobReadWriter("main", "test", "content", rowid)
262
	checkNoError(t, err, "blob open error: %s")
gwenn's avatar
gwenn committed
263 264 265
	defer bw.Close()
	content := []byte("Clob")
	n, err := bw.Write(content)
266
	checkNoError(t, err, "blob write error: %s")
gwenn's avatar
gwenn committed
267 268

	br, err := db.NewBlobReader("main", "test", "content", rowid)
269
	checkNoError(t, err, "blob open error: %s")
gwenn's avatar
gwenn committed
270 271
	defer br.Close()
	size, err := br.Size()
272
	checkNoError(t, err, "blob size error: %s")
gwenn's avatar
gwenn committed
273 274
	content = make([]byte, size)
	n, err = br.Read(content)
275
	checkNoError(t, err, "blob read error: %s")
gwenn's avatar
gwenn committed
276
	if n != 10 {
277
		t.Fatalf("Expected 10 bytes but got %d", n)
gwenn's avatar
gwenn committed
278 279 280 281 282
	}
	//fmt.Printf("%#v\n", content)
	br.Close()
}

gwenn's avatar
gwenn committed
283
func TestScanColumn(t *testing.T) {
284 285 286 287
	db := open(t)
	defer db.Close()

	s, err := db.Prepare("select 1, null, 0")
288
	checkNoError(t, err, "prepare error: %s")
289
	defer s.Finalize()
gwenn's avatar
gwenn committed
290
	if !Must(s.Next()) {
291
		t.Fatal("no result")
292 293
	}
	var i1, i2, i3 int
gwenn's avatar
gwenn committed
294
	null := Must(s.ScanByIndex(0, &i1 /*, true*/ ))
295
	if null {
296 297
		t.Errorf("Expected not null value")
	} else if i1 != 1 {
298
		t.Errorf("Expected 1 but got %d\n", i1)
299
	}
gwenn's avatar
gwenn committed
300
	null = Must(s.ScanByIndex(1, &i2 /*, true*/ ))
301
	if !null {
302 303
		t.Errorf("Expected null value")
	} else if i2 != 0 {
304
		t.Errorf("Expected 0 but got %d\n", i2)
305
	}
gwenn's avatar
gwenn committed
306
	null = Must(s.ScanByIndex(2, &i3 /*, true*/ ))
307
	if null {
308 309
		t.Errorf("Expected not null value")
	} else if i3 != 0 {
310
		t.Errorf("Expected 0 but got %d\n", i3)
311 312 313
	}
}

gwenn's avatar
gwenn committed
314
func TestNamedScanColumn(t *testing.T) {
315 316 317 318
	db := open(t)
	defer db.Close()

	s, err := db.Prepare("select 1 as i1, null as i2, 0 as i3")
319
	checkNoError(t, err, "prepare error: %s")
320
	defer s.Finalize()
gwenn's avatar
gwenn committed
321
	if !Must(s.Next()) {
322
		t.Fatal("no result")
323 324
	}
	var i1, i2, i3 int
gwenn's avatar
gwenn committed
325
	null := Must(s.ScanByName("i1", &i1 /*, true*/ ))
326
	if null {
327 328
		t.Errorf("Expected not null value")
	} else if i1 != 1 {
329
		t.Errorf("Expected 1 but got %d\n", i1)
330
	}
gwenn's avatar
gwenn committed
331
	null = Must(s.ScanByName("i2", &i2 /*, true*/ ))
332
	if !null {
333 334
		t.Errorf("Expected null value")
	} else if i2 != 0 {
335
		t.Errorf("Expected 0 but got %d\n", i2)
336
	}
gwenn's avatar
gwenn committed
337
	null = Must(s.ScanByName("i3", &i3 /*, true*/ ))
338
	if null {
339 340
		t.Errorf("Expected not null value")
	} else if i3 != 0 {
341 342 343 344 345 346 347 348 349
		t.Errorf("Expected 0 but got %d\n", i3)
	}
}

func TestScanCheck(t *testing.T) {
	db := open(t)
	defer db.Close()

	s, err := db.Prepare("select 'hello'")
350
	checkNoError(t, err, "prepare error: %s")
351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368
	defer s.Finalize()
	if !Must(s.Next()) {
		t.Fatal("no result")
	}
	var i int
	_, err = s.ScanByIndex(0, &i)
	if serr, ok := err.(*StmtError); ok {
		if serr.Filename() != "" {
			t.Errorf("Expected '' but got '%s'", serr.Filename())
		}
		if serr.Code() != ErrSpecific {
			t.Errorf("Expected %s but got %s", ErrSpecific, serr.Code())
		}
		if serr.SQL() != s.SQL() {
			t.Errorf("Expected %s but got %s", s.SQL(), serr.SQL())
		}
	} else {
		t.Errorf("Expected StmtError but got %s", reflect.TypeOf(err))
369 370 371
	}
}

gwenn's avatar
gwenn committed
372 373 374 375 376 377 378
/*
func TestLoadExtension(t *testing.T) {
	db := open(t)

	db.EnableLoadExtension(true)

	err := db.LoadExtension("/tmp/myext.so")
379
	checkNoError(t, err, "load extension error: %s")
gwenn's avatar
gwenn committed
380 381
}
*/
382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407

func TestScanNull(t *testing.T) {
	db := open(t)
	defer db.Close()

	s, err := db.Prepare("select null")
	checkNoError(t, err, "prepare error: %s")
	defer s.Finalize()
	if !Must(s.Next()) {
		t.Fatal("no result")
	}
	var pi *int
	null := Must(s.ScanByIndex(0, &pi))
	if !null {
		t.Errorf("Expected null value")
	} else if pi != nil {
		t.Errorf("Expected nil but got %p\n", pi)
	}
	var ps *string
	null = Must(s.ScanByIndex(0, &ps))
	if !null {
		t.Errorf("Expected null value")
	} else if ps != nil {
		t.Errorf("Expected nil but got %p\n", ps)
	}
}