sqlite3_test.odin (13620B)
1 package sqlite3 2 3 import "core:os" 4 import "core:path/filepath" 5 import "core:slice" 6 import "core:testing" 7 8 @(test) 9 version_is_vendored :: proc(t: ^testing.T) { 10 context.allocator = context.temp_allocator 11 testing.expect_value(t, version(), "3.53.4") 12 13 // The version alone would match a system library that happens to agree, 14 // so check the options the justfile's recipe sets. 15 db, err := open(MEMORY) 16 testing.expect_value(t, err, nil) 17 defer close(&db) 18 rows, qerr := query(db, `PRAGMA compile_options`) 19 testing.expect_value(t, qerr, nil) 20 defer finish(&rows) 21 opts: map[string]bool 22 for next(&rows) { 23 opts[text(rows, 0)] = true 24 } 25 testing.expect(t, opts["DQS=0"], "the archive must be the one just sqlite built") 26 testing.expect( 27 t, 28 opts["THREADSAFE=1"], 29 "threadsafe is why the recipe departs from the recommended set", 30 ) 31 testing.expect(t, opts["ENABLE_FTS5"], "FTS5 is compiled in for a full-text index") 32 testing.expect( 33 t, 34 opts["OMIT_LOAD_EXTENSION"], 35 "load_extension is omitted so the link needs no libdl", 36 ) 37 testing.expect( 38 t, 39 !opts["OMIT_AUTOINIT"], 40 "autoinit stays on, so no call can precede initialize", 41 ) 42 } 43 44 // TRANSIENT is what every bind call passes, and it is the reason a caller may 45 // free or overwrite its buffer the moment bind returns. 46 @(test) 47 bind_copies_the_caller_s_bytes :: proc(t: ^testing.T) { 48 context.allocator = context.temp_allocator 49 db, err := open(MEMORY) 50 testing.expect_value(t, err, nil) 51 defer close(&db) 52 testing.expect_value(t, exec(db, `CREATE TABLE t(v TEXT)`), nil) 53 54 buf := make([]byte, 5, context.temp_allocator) 55 copy(buf, "alpha") 56 stmt, perr := prepare(db, `INSERT INTO t VALUES (?)`) 57 testing.expect_value(t, perr, nil) 58 testing.expect_value(t, bind(&stmt, string(buf)), nil) 59 // Overwrite the buffer after binding but before stepping. 60 copy(buf, "OMEGA") 61 testing.expect(t, !next(&stmt), "an insert returns no row") 62 testing.expect_value(t, finish(&stmt), nil) 63 64 rows, qerr := query(db, `SELECT v FROM t`) 65 testing.expect_value(t, qerr, nil) 66 defer finish(&rows) 67 testing.expect(t, next(&rows)) 68 testing.expect_value(t, text(rows, 0), "alpha") 69 } 70 71 @(test) 72 memory_round_trip :: proc(t: ^testing.T) { 73 context.allocator = context.temp_allocator 74 db, err := open(MEMORY) 75 testing.expect_value(t, err, nil) 76 defer close(&db) 77 78 testing.expect_value(t, exec(db, `CREATE TABLE note(id INTEGER PRIMARY KEY, body TEXT)`), nil) 79 testing.expect_value(t, exec_args(db, `INSERT INTO note(body) VALUES (?)`, "hello"), nil) 80 testing.expect_value(t, changes(db), 1) 81 testing.expect_value(t, last_id(db), 1) 82 83 rows, qerr := query(db, `SELECT id, body FROM note`) 84 testing.expect_value(t, qerr, nil) 85 testing.expect(t, next(&rows), "one row expected") 86 testing.expect_value(t, integer(rows, 0), 1) 87 testing.expect_value(t, text(rows, 1), "hello") 88 testing.expect(t, !next(&rows), "only one row expected") 89 testing.expect_value(t, finish(&rows), nil) 90 } 91 92 // A bound value survives unchanged through the characters that break SQL 93 // built by hand: an apostrophe, a quote, a semicolon, a backslash, a tab and 94 // a newline. 95 @(test) 96 quotes_round_trip :: proc(t: ^testing.T) { 97 context.allocator = context.temp_allocator 98 db, err := open(MEMORY) 99 testing.expect_value(t, err, nil) 100 defer close(&db) 101 testing.expect_value(t, exec(db, `CREATE TABLE t(v TEXT)`), nil) 102 103 awkward := []string { 104 "name-says-what-it-isn't", 105 `he said "quoted"`, 106 "; DROP TABLE t; --", 107 "back\\slash and \ttab", 108 "line\nbreak", 109 "", 110 } 111 for v in awkward { 112 testing.expect_value(t, exec_args(db, `INSERT INTO t(v) VALUES (?)`, v), nil) 113 } 114 rows, qerr := query(db, `SELECT v FROM t ORDER BY rowid`) 115 testing.expect_value(t, qerr, nil) 116 defer finish(&rows) 117 i := 0 118 for next(&rows) { 119 testing.expect_value(t, text(rows, 0), awkward[i]) 120 // An empty string must stay text, not become NULL. 121 testing.expect(t, !is_null(rows, 0), "empty string must not be NULL") 122 i += 1 123 } 124 testing.expect_value(t, i, len(awkward)) 125 126 // The table survived the statement that tried to drop it. 127 count, cerr := query(db, `SELECT count(*) FROM t`) 128 testing.expect_value(t, cerr, nil) 129 defer finish(&count) 130 testing.expect(t, next(&count)) 131 testing.expect_value(t, integer(count, 0), i64(len(awkward))) 132 } 133 134 @(test) 135 value_types_round_trip :: proc(t: ^testing.T) { 136 context.allocator = context.temp_allocator 137 db, err := open(MEMORY) 138 testing.expect_value(t, err, nil) 139 defer close(&db) 140 testing.expect_value(t, exec(db, `CREATE TABLE v(i INT, r REAL, b BLOB, f INT, n TEXT)`), nil) 141 142 raw := []byte{0, 1, 2, 0xff, 0} 143 testing.expect_value( 144 t, 145 exec_args(db, `INSERT INTO v VALUES (?, ?, ?, ?, ?)`, i64(-7), 2.5, raw, true, nil), 146 nil, 147 ) 148 rows, qerr := query(db, `SELECT i, r, b, f, n FROM v`) 149 testing.expect_value(t, qerr, nil) 150 defer finish(&rows) 151 testing.expect(t, next(&rows)) 152 testing.expect_value(t, integer(rows, 0), -7) 153 testing.expect_value(t, real(rows, 1), 2.5) 154 testing.expect(t, slice.equal(blob(rows, 2), raw), "blob must survive its NUL bytes") 155 testing.expect_value(t, boolean(rows, 3), true) 156 testing.expect(t, is_null(rows, 4), "nil must bind as NULL") 157 testing.expect_value(t, column_count(rows), 5) 158 testing.expect_value(t, name(rows, 1), "r") 159 } 160 161 @(test) 162 errors_carry_the_message :: proc(t: ^testing.T) { 163 context.allocator = context.temp_allocator 164 db, err := open(MEMORY) 165 testing.expect_value(t, err, nil) 166 defer close(&db) 167 168 _, serr := query(db, `SELECT nope FROM missing_table`) 169 fault, ok := serr.(Fault) 170 testing.expect(t, ok, "a bad query must fault") 171 testing.expect_value(t, fault.code, Code.Error) 172 // The point of the error type: the text names the table, not just a code. 173 testing.expect_value(t, fault.text, "no such table: missing_table") 174 175 testing.expect_value(t, exec(db, `CREATE TABLE u(v TEXT UNIQUE)`), nil) 176 testing.expect_value(t, exec_args(db, `INSERT INTO u VALUES (?)`, "x"), nil) 177 cerr := exec_args(db, `INSERT INTO u VALUES (?)`, "x") 178 cfault, cok := cerr.(Fault) 179 testing.expect(t, cok, "a duplicate must fault") 180 testing.expect_value(t, cfault.code, Code.Constraint) 181 testing.expect_value(t, cfault.text, "UNIQUE constraint failed: u.v") 182 } 183 184 @(test) 185 bind_count_is_checked :: proc(t: ^testing.T) { 186 context.allocator = context.temp_allocator 187 db, err := open(MEMORY) 188 testing.expect_value(t, err, nil) 189 defer close(&db) 190 testing.expect_value(t, exec(db, `CREATE TABLE t(a, b)`), nil) 191 192 short := exec_args(db, `INSERT INTO t VALUES (?, ?)`, i64(1)) 193 fault, ok := short.(Fault) 194 testing.expect(t, ok, "a short argument list must fault") 195 testing.expect_value(t, fault.code, Code.Range) 196 testing.expect_value(t, fault.text, "statement takes 2 parameters, got 1") 197 198 // What that guard is worth: bind the same statement through the raw API, 199 // leaving the second parameter unset, and SQLite stores NULL without 200 // complaint. bind refuses the call instead of writing that row. 201 stmt, perr := prepare(db, `INSERT INTO t VALUES (?, ?)`) 202 testing.expect_value(t, perr, nil) 203 testing.expect_value(t, Code(sqlite3_bind_int64(stmt.handle, 1, 1)), Code.Ok) 204 testing.expect(t, !next(&stmt), "an insert returns no row") 205 testing.expect_value(t, finish(&stmt), nil) 206 207 rows, qerr := query(db, `SELECT a, b FROM t`) 208 testing.expect_value(t, qerr, nil) 209 defer finish(&rows) 210 testing.expect(t, next(&rows), "the unguarded insert wrote a row") 211 testing.expect_value(t, type_of(rows, 0), Type.Integer) 212 testing.expect_value(t, type_of(rows, 1), Type.Null) 213 } 214 215 @(test) 216 exec_runs_every_statement :: proc(t: ^testing.T) { 217 context.allocator = context.temp_allocator 218 db, err := open(MEMORY) 219 testing.expect_value(t, err, nil) 220 defer close(&db) 221 222 schema := ` 223 CREATE TABLE a(x INT); 224 CREATE TABLE b(y INT); 225 INSERT INTO a VALUES (1); 226 INSERT INTO b VALUES (2); 227 ` 228 testing.expect_value(t, exec(db, schema), nil) 229 rows, qerr := query(db, `SELECT a.x + b.y FROM a, b`) 230 testing.expect_value(t, qerr, nil) 231 defer finish(&rows) 232 testing.expect(t, next(&rows)) 233 testing.expect_value(t, integer(rows, 0), 3) 234 } 235 236 @(test) 237 prepared_statement_is_reused :: proc(t: ^testing.T) { 238 context.allocator = context.temp_allocator 239 db, err := open(MEMORY) 240 testing.expect_value(t, err, nil) 241 defer close(&db) 242 testing.expect_value(t, exec(db, `CREATE TABLE n(v INT)`), nil) 243 244 stmt, perr := prepare(db, `INSERT INTO n VALUES (?)`) 245 testing.expect_value(t, perr, nil) 246 for i in 0 ..< 100 { 247 testing.expect_value(t, bind(&stmt, i64(i)), nil) 248 testing.expect(t, !next(&stmt), "an insert returns no row") 249 testing.expect_value(t, reset(&stmt), nil) 250 } 251 testing.expect_value(t, finish(&stmt), nil) 252 253 sum, qerr := query(db, `SELECT count(*), sum(v) FROM n`) 254 testing.expect_value(t, qerr, nil) 255 defer finish(&sum) 256 testing.expect(t, next(&sum)) 257 testing.expect_value(t, integer(sum, 0), 100) 258 testing.expect_value(t, integer(sum, 1), 4950) 259 } 260 261 @(test) 262 file_survives_reopen :: proc(t: ^testing.T) { 263 context.allocator = context.temp_allocator 264 temp := os.temp_directory(context.temp_allocator) or_else "" 265 dir, derr := os.make_directory_temp(temp, "jm-sqlite3-*", context.temp_allocator) 266 testing.expect(t, derr == nil) 267 defer os.remove_all(dir) 268 path, _ := filepath.join({dir, "notes.db"}, context.temp_allocator) 269 270 { 271 db, err := open(path, Opts{wal = true}) 272 testing.expect_value(t, err, nil) 273 defer close(&db) 274 testing.expect_value(t, exec(db, `CREATE TABLE note(body TEXT)`), nil) 275 testing.expect_value(t, transact(db, write_three), nil) 276 } 277 testing.expect(t, os.is_file(path), "the database file must exist") 278 279 db, err := open(path, Opts{no_create = true}) 280 testing.expect_value(t, err, nil) 281 defer close(&db) 282 rows, qerr := query(db, `SELECT body FROM note ORDER BY rowid`) 283 testing.expect_value(t, qerr, nil) 284 defer finish(&rows) 285 got: [dynamic]string 286 for next(&rows) { 287 append(&got, text(rows, 0)) 288 } 289 testing.expect_value(t, len(got), 3) 290 testing.expect_value(t, got[0], "one") 291 testing.expect_value(t, got[2], "three's") 292 } 293 294 @(private = "file") 295 write_three :: proc(db: Db, user: rawptr) -> Error { 296 for body in ([]string{"one", "two", "three's"}) { 297 exec_args(db, `INSERT INTO note(body) VALUES (?)`, body) or_return 298 } 299 return nil 300 } 301 302 @(test) 303 transaction_rolls_back :: proc(t: ^testing.T) { 304 context.allocator = context.temp_allocator 305 db, err := open(MEMORY) 306 testing.expect_value(t, err, nil) 307 defer close(&db) 308 testing.expect_value(t, exec(db, `CREATE TABLE t(v TEXT UNIQUE)`), nil) 309 310 ferr := transact(db, write_then_fail) 311 fault, ok := ferr.(Fault) 312 testing.expect(t, ok, "the body's failure must be returned") 313 testing.expect_value(t, fault.code, Code.Constraint) 314 315 // Nothing the body wrote before it failed may survive. 316 rows, qerr := query(db, `SELECT count(*) FROM t`) 317 testing.expect_value(t, qerr, nil) 318 defer finish(&rows) 319 testing.expect(t, next(&rows)) 320 testing.expect_value(t, integer(rows, 0), 0) 321 } 322 323 @(private = "file") 324 write_then_fail :: proc(db: Db, user: rawptr) -> Error { 325 exec_args(db, `INSERT INTO t VALUES (?)`, "dup") or_return 326 exec_args(db, `INSERT INTO t VALUES (?)`, "dup") or_return 327 return nil 328 } 329 330 @(test) 331 read_only_refuses_writes :: proc(t: ^testing.T) { 332 context.allocator = context.temp_allocator 333 temp := os.temp_directory(context.temp_allocator) or_else "" 334 dir, derr := os.make_directory_temp(temp, "jm-sqlite3-ro-*", context.temp_allocator) 335 testing.expect(t, derr == nil) 336 defer os.remove_all(dir) 337 path, _ := filepath.join({dir, "ro.db"}, context.temp_allocator) 338 339 { 340 db, err := open(path) 341 testing.expect_value(t, err, nil) 342 defer close(&db) 343 testing.expect_value(t, exec(db, `CREATE TABLE t(v INT)`), nil) 344 } 345 346 db, err := open(path, Opts{read_only = true}) 347 testing.expect_value(t, err, nil) 348 defer close(&db) 349 werr := exec_args(db, `INSERT INTO t VALUES (?)`, i64(1)) 350 fault, ok := werr.(Fault) 351 testing.expect(t, ok, "a write to a read-only database must fault") 352 testing.expect_value(t, fault.code, Code.Read_Only) 353 } 354 355 @(test) 356 missing_file_faults :: proc(t: ^testing.T) { 357 context.allocator = context.temp_allocator 358 temp := os.temp_directory(context.temp_allocator) or_else "" 359 path, _ := filepath.join({temp, "jm-sqlite3-does-not-exist.db"}, context.temp_allocator) 360 _, err := open(path, Opts{no_create = true}) 361 fault, ok := err.(Fault) 362 testing.expect(t, ok, "opening a missing database without create must fault") 363 testing.expect_value(t, fault.code, Code.Cant_Open) 364 testing.expect_value(t, fault.text, "unable to open database file") 365 testing.expect(t, !os.exists(path), "no_create must not create the file") 366 } 367 368 // Opts.wal on an in-memory database leaves the mode alone: SQLite will not 369 // put :memory: into WAL. 370 @(test) 371 memory_ignores_wal :: proc(t: ^testing.T) { 372 context.allocator = context.temp_allocator 373 db, err := open(MEMORY, Opts{wal = true}) 374 testing.expect_value(t, err, nil) 375 defer close(&db) 376 377 rows, qerr := query(db, `PRAGMA journal_mode`) 378 testing.expect_value(t, qerr, nil) 379 defer finish(&rows) 380 testing.expect(t, next(&rows)) 381 testing.expect_value(t, text(rows, 0), "memory") 382 } 383 384 // And a file-backed database does take WAL, which is the other half of the 385 // option's doc. 386 @(test) 387 file_takes_wal :: proc(t: ^testing.T) { 388 context.allocator = context.temp_allocator 389 temp := os.temp_directory(context.temp_allocator) or_else "" 390 dir, derr := os.make_directory_temp(temp, "jm-sqlite3-wal-*", context.temp_allocator) 391 testing.expect(t, derr == nil) 392 defer os.remove_all(dir) 393 path, _ := filepath.join({dir, "wal.db"}, context.temp_allocator) 394 395 db, err := open(path, Opts{wal = true}) 396 testing.expect_value(t, err, nil) 397 defer close(&db) 398 rows, qerr := query(db, `PRAGMA journal_mode`) 399 testing.expect_value(t, qerr, nil) 400 defer finish(&rows) 401 testing.expect(t, next(&rows)) 402 testing.expect_value(t, text(rows, 0), "wal") 403 }