Mercurial > games > semicongine

comparison semiconginev2/thirdparty/db_connector/db_sqlite.nim @ 1218:56781cc0fc7c compiletime-tests

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
did: renamge main package
author sam <sam@basx.dev>
date Wed, 17 Jul 2024 21:01:37 +0700
parents semicongine/old/thirdparty/db_connector/db_sqlite.nim@239adab121a3
children
comparison
equal deleted inserted replaced
1217:f819a874058f 1218:56781cc0fc7c
1 #
2 #
3 # Nim's Runtime Library
4 # (c) Copyright 2015 Andreas Rumpf
5 #
6 # See the file "copying.txt", included in this
7 # distribution, for details about the copyright.
8 #
9
10 ## .. note:: In order to use this module, run `nimble install db_connector`.
11 ##
12 ## A higher level `SQLite`:idx: database wrapper. This interface
13 ## is implemented for other databases too.
14 ##
15 ## Basic usage
16 ## ===========
17 ##
18 ## The basic flow of using this module is:
19 ##
20 ## 1. Open database connection
21 ## 2. Execute SQL query
22 ## 3. Close database connection
23 ##
24 ## Parameter substitution
25 ## ----------------------
26 ##
27 ## All `db_*` modules support the same form of parameter substitution.
28 ## That is, using the `?` (question mark) to signify the place where a
29 ## value should be placed. For example:
30 ##
31 ## ```Nim
32 ## sql"INSERT INTO my_table (colA, colB, colC) VALUES (?, ?, ?)"
33 ## ```
34 ##
35 ## Opening a connection to a database
36 ## ----------------------------------
37 ##
38 ## ```Nim
39 ## import db_connector/db_sqlite
40 ##
41 ## # user, password, database name can be empty.
42 ## # These params are not used on db_sqlite module.
43 ## let db = open("mytest.db", "", "", "")
44 ## db.close()
45 ## ```
46 ##
47 ## Creating a table
48 ## ----------------
49 ##
50 ## ```Nim
51 ## db.exec(sql"DROP TABLE IF EXISTS my_table")
52 ## db.exec(sql"""CREATE TABLE my_table (
53 ## id INTEGER,
54 ## name VARCHAR(50) NOT NULL
55 ## )""")
56 ## ```
57 ##
58 ## Inserting data
59 ## --------------
60 ##
61 ## ```Nim
62 ## db.exec(sql"INSERT INTO my_table (id, name) VALUES (0, ?)",
63 ## "Jack")
64 ## ```
65 ##
66 ## Larger example
67 ## --------------
68 ##
69 ## ```Nim
70 ## import db_connector/db_sqlite
71 ## import std/math
72 ##
73 ## let db = open("mytest.db", "", "", "")
74 ##
75 ## db.exec(sql"DROP TABLE IF EXISTS my_table")
76 ## db.exec(sql"""CREATE TABLE my_table (
77 ## id INTEGER PRIMARY KEY,
78 ## name VARCHAR(50) NOT NULL,
79 ## i INT(11),
80 ## f DECIMAL(18, 10)
81 ## )""")
82 ##
83 ## db.exec(sql"BEGIN")
84 ## for i in 1..1000:
85 ## db.exec(sql"INSERT INTO my_table (name, i, f) VALUES (?, ?, ?)",
86 ## "Item#" & $i, i, sqrt(i.float))
87 ## db.exec(sql"COMMIT")
88 ##
89 ## for x in db.fastRows(sql"SELECT * FROM my_table"):
90 ## echo x
91 ##
92 ## let id = db.tryInsertId(sql"""INSERT INTO my_table (name, i, f)
93 ## VALUES (?, ?, ?)""",
94 ## "Item#1001", 1001, sqrt(1001.0))
95 ## echo "Inserted item: ", db.getValue(sql"SELECT name FROM my_table WHERE id=?", id)
96 ##
97 ## db.close()
98 ## ```
99 ##
100 ## Storing binary data example
101 ##----------------------------
102 ##
103 ## ```nim
104 ## import std/random
105 ##
106 ## ## Generate random float datas
107 ## var orig = newSeq[float64](150)
108 ## randomize()
109 ## for x in orig.mitems:
110 ## x = rand(1.0)/10.0
111 ##
112 ## let db = open("mysqlite.db", "", "", "")
113 ## block: ## Create database
114 ## ## Binary datas needs to be of type BLOB in SQLite
115 ## let createTableStr = sql"""CREATE TABLE test(
116 ## id INTEGER NOT NULL PRIMARY KEY,
117 ## data BLOB
118 ## )
119 ## """
120 ## db.exec(createTableStr)
121 ##
122 ## block: ## Insert data
123 ## var id = 1
124 ## ## Data needs to be converted to seq[byte] to be interpreted as binary by bindParams
125 ## var dbuf = newSeq[byte](orig.len*sizeof(float64))
126 ## copyMem(unsafeAddr(dbuf[0]), unsafeAddr(orig[0]), dbuf.len)
127 ##
128 ## ## Use prepared statement to insert binary data into database
129 ## var insertStmt = db.prepare("INSERT INTO test (id, data) VALUES (?, ?)")
130 ## insertStmt.bindParams(id, dbuf)
131 ## let bres = db.tryExec(insertStmt)
132 ## ## Check insert
133 ## doAssert(bres)
134 ## # Destroy statement
135 ## finalize(insertStmt)
136 ##
137 ## block: ## Use getValue to select data
138 ## var dataTest = db.getValue(sql"SELECT data FROM test WHERE id = ?", 1)
139 ## ## Calculate sequence size from buffer size
140 ## let seqSize = int(dataTest.len*sizeof(byte)/sizeof(float64))
141 ## ## Copy binary string data in dataTest into a seq
142 ## var res: seq[float64] = newSeq[float64](seqSize)
143 ## copyMem(unsafeAddr(res[0]), addr(dataTest[0]), dataTest.len)
144 ##
145 ## ## Check datas obtained is identical
146 ## doAssert res == orig
147 ##
148 ## db.close()
149 ## ```
150 ##
151 ##
152 ## Note
153 ## ====
154 ## This module does not implement any ORM features such as mapping the types from the schema.
155 ## Instead, a `seq[string]` is returned for each row.
156 ##
157 ## The reasoning is as follows:
158 ## 1. it's close to what many DBs offer natively (`char**`:c:)
159 ## 2. it hides the number of types that the DB supports
160 ## (int? int64? decimal up to 10 places? geo coords?)
161 ## 3. it's convenient when all you do is to forward the data to somewhere else (echo, log, put the data into a new query)
162 ##
163 ## See also
164 ## ========
165 ##
166 ## * `db_odbc module <db_odbc.html>`_ for ODBC database wrapper
167 ## * `db_mysql module <db_mysql.html>`_ for MySQL database wrapper
168 ## * `db_postgres module <db_postgres.html>`_ for PostgreSQL database wrapper
169
170
171 import ./sqlite3, macros
172
173 import ./db_common
174 export db_common
175
176 import private/dbutils
177
178 import std/private/[since]
179 when defined(nimPreviewSlimSystem):
180 import std/assertions
181
182 type
183 DbConn* = PSqlite3 ## Encapsulates a database connection.
184 Row* = seq[string] ## A row of a dataset. `NULL` database values will be
185 ## converted to an empty string.
186 InstantRow* = PStmt ## A handle that can be used to get a row's column
187 ## text on demand.
188 SqlPrepared* = distinct PStmt ## a identifier for the prepared queries
189
190 proc bindParam*(ps: SqlPrepared, paramIdx: int, val: int32) {.since: (1, 3).}
191 proc bindParam*(ps: SqlPrepared, paramIdx: int, val: int64) {.since: (1, 3).}
192 proc bindParam*(ps: SqlPrepared, paramIdx: int, val: int) {.since: (1, 3).}
193 proc bindParam*(ps: SqlPrepared, paramIdx: int, val: float64) {.since: (1, 3).}
194 proc bindNull*(ps: SqlPrepared, paramIdx: int) {.since: (1, 3).}
195 proc bindParam*(ps: SqlPrepared, paramIdx: int, val: string, copy = true) {.since: (1, 3).}
196 proc bindParam*(ps: SqlPrepared, paramIdx: int,val: openArray[byte], copy = true) {.since: (1, 3).}
197
198 proc dbError*(db: DbConn) {.noreturn.} =
199 ## Raises a `DbError` exception.
200 ##
201 ## **Examples:**
202 ## ```Nim
203 ## let db = open("mytest.db", "", "", "")
204 ## if not db.tryExec(sql"SELECT * FROM not_exist_table"):
205 ## dbError(db)
206 ## db.close()
207 ## ```
208 var e: ref DbError
209 new(e)
210 e.msg = $sqlite3.errmsg(db)
211 raise e
212
213 proc dbQuote*(s: string): string =
214 ## Escapes the `'` (single quote) char to `''`.
215 ## Because single quote is used for defining `VARCHAR` in SQL.
216 runnableExamples:
217 doAssert dbQuote("'") == "''''"
218 doAssert dbQuote("A Foobar's pen.") == "'A Foobar''s pen.'"
219
220 result = "'"
221 for c in items(s):
222 if c == '\'': add(result, "''")
223 else: add(result, c)
224 add(result, '\'')
225
226 proc dbFormat(formatstr: SqlQuery, args: varargs[string]): string =
227 dbFormatImpl(formatstr, dbQuote, args)
228
229 proc prepare*(db: DbConn; q: string): SqlPrepared {.since: (1, 3).} =
230 ## Creates a new `SqlPrepared` statement.
231 if prepare_v2(db, q, q.len.cint,result.PStmt, nil) != SQLITE_OK:
232 discard finalize(result.PStmt)
233 dbError(db)
234
235 proc tryExec*(db: DbConn, query: SqlQuery,
236 args: varargs[string, `$`]): bool {.
237 tags: [ReadDbEffect, WriteDbEffect].} =
238 ## Tries to execute the query and returns `true` if successful, `false` otherwise.
239 ##
240 ## **Examples:**
241 ## ```Nim
242 ## let db = open("mytest.db", "", "", "")
243 ## if not db.tryExec(sql"SELECT * FROM my_table"):
244 ## dbError(db)
245 ## db.close()
246 ## ```
247 assert(not db.isNil, "Database not connected.")
248 var q = dbFormat(query, args)
249 var stmt: sqlite3.PStmt
250 if prepare_v2(db, q.cstring, q.len.cint, stmt, nil) == SQLITE_OK:
251 let x = step(stmt)
252 if x in [SQLITE_DONE, SQLITE_ROW]:
253 result = finalize(stmt) == SQLITE_OK
254 else:
255 discard finalize(stmt)
256 result = false
257
258 proc tryExec*(db: DbConn, stmtName: SqlPrepared): bool {.
259 tags: [ReadDbEffect, WriteDbEffect].} =
260 let x = step(stmtName.PStmt)
261 if x in [SQLITE_DONE, SQLITE_ROW]:
262 result = true
263 else:
264 discard finalize(stmtName.PStmt)
265 result = false
266
267 proc exec*(db: DbConn, query: SqlQuery, args: varargs[string, `$`]) {.
268 tags: [ReadDbEffect, WriteDbEffect].} =
269 ## Executes the query and raises a `DbError` exception if not successful.
270 ##
271 ## **Examples:**
272 ## ```Nim
273 ## let db = open("mytest.db", "", "", "")
274 ## try:
275 ## db.exec(sql"INSERT INTO my_table (id, name) VALUES (?, ?)",
276 ## 1, "item#1")
277 ## except:
278 ## stderr.writeLine(getCurrentExceptionMsg())
279 ## finally:
280 ## db.close()
281 ## ```
282 if not tryExec(db, query, args): dbError(db)
283
284 macro untypedLen(args: varargs[untyped]): int =
285 newLit(args.len)
286
287 macro bindParams*(ps: SqlPrepared, params: varargs[untyped]): untyped {.since: (1, 3).} =
288 let bindParam = bindSym("bindParam", brOpen)
289 let bindNull = bindSym("bindNull")
290 let preparedStatement = genSym()
291 result = newStmtList()
292 # Store `ps` in a temporary variable. This prevents `ps` from being evaluated every call.
293 result.add newNimNode(nnkLetSection).add(newIdentDefs(preparedStatement, newEmptyNode(), ps))
294 for idx, param in params:
295 if param.kind != nnkNilLit:
296 result.add newCall(bindParam, preparedStatement, newIntLitNode idx + 1, param)
297 else:
298 result.add newCall(bindNull, preparedStatement, newIntLitNode idx + 1)
299
300
301 template exec*(db: DbConn, stmtName: SqlPrepared,
302 args: varargs[typed]): untyped =
303 when untypedLen(args) > 0:
304 if reset(stmtName.PStmt) != SQLITE_OK:
305 dbError(db)
306 if clear_bindings(stmtName.PStmt) != SQLITE_OK:
307 dbError(db)
308 stmtName.bindParams(args)
309 if not tryExec(db, stmtName): dbError(db)
310
311 proc newRow(L: int): Row =
312 newSeq(result, L)
313 for i in 0..L-1: result[i] = ""
314
315 proc setupQuery(db: DbConn, query: SqlQuery,
316 args: varargs[string]): PStmt =
317 assert(not db.isNil, "Database not connected.")
318 var q = dbFormat(query, args)
319 if prepare_v2(db, q.cstring, q.len.cint, result, nil) != SQLITE_OK: dbError(db)
320
321 proc setupQuery(db: DbConn, stmtName: SqlPrepared): SqlPrepared {.since: (1, 3).} =
322 assert(not db.isNil, "Database not connected.")
323 result = stmtName
324
325 proc setRow(stmt: PStmt, r: var Row, cols: cint) =
326 for col in 0'i32..cols-1:
327 let cb = column_bytes(stmt, col)
328 setLen(r[col], cb) # set capacity
329 if column_type(stmt, col) == SQLITE_BLOB:
330 copyMem(addr(r[col][0]), column_blob(stmt, col), cb)
331 else:
332 setLen(r[col], 0)
333 let x = column_text(stmt, col)
334 if not isNil(x): add(r[col], x)
335
336 iterator fastRows*(db: DbConn, query: SqlQuery,
337 args: varargs[string, `$`]): Row {.tags: [ReadDbEffect].} =
338 ## Executes the query and iterates over the result dataset.
339 ##
340 ## This is very fast, but potentially dangerous. Use this iterator only
341 ## if you require **ALL** the rows.
342 ##
343 ## **Note:** Breaking the `fastRows()` iterator during a loop will cause the
344 ## next database query to raise a `DbError` exception `unable to close due
345 ## to ...`.
346 ##
347 ## **Examples:**
348 ##
349 ## ```Nim
350 ## let db = open("mytest.db", "", "", "")
351 ##
352 ## # Records of my_table:
353 ## # | id | name |
354 ## # |----|----------|
355 ## # | 1 | item#1 |
356 ## # | 2 | item#2 |
357 ##
358 ## for row in db.fastRows(sql"SELECT id, name FROM my_table"):
359 ## echo row
360 ##
361 ## # Output:
362 ## # @["1", "item#1"]
363 ## # @["2", "item#2"]
364 ##
365 ## db.close()
366 ## ```
367 var stmt = setupQuery(db, query, args)
368 var L = (column_count(stmt))
369 var result = newRow(L)
370 try:
371 while step(stmt) == SQLITE_ROW:
372 setRow(stmt, result, L)
373 yield result
374 finally:
375 if finalize(stmt) != SQLITE_OK: dbError(db)
376
377 iterator fastRows*(db: DbConn, stmtName: SqlPrepared): Row
378 {.tags: [ReadDbEffect,WriteDbEffect], since: (1, 3).} =
379 discard setupQuery(db, stmtName)
380 var L = (column_count(stmtName.PStmt))
381 var result = newRow(L)
382 try:
383 while step(stmtName.PStmt) == SQLITE_ROW:
384 setRow(stmtName.PStmt, result, L)
385 yield result
386 except:
387 dbError(db)
388
389 iterator instantRows*(db: DbConn, query: SqlQuery,
390 args: varargs[string, `$`]): InstantRow
391 {.tags: [ReadDbEffect].} =
392 ## Similar to `fastRows iterator <#fastRows.i,DbConn,SqlQuery,varargs[string,]>`_
393 ## but returns a handle that can be used to get column text
394 ## on demand using `[]`. Returned handle is valid only within the iterator body.
395 ##
396 ## **Examples:**
397 ##
398 ## ```Nim
399 ## let db = open("mytest.db", "", "", "")
400 ##
401 ## # Records of my_table:
402 ## # | id | name |
403 ## # |----|----------|
404 ## # | 1 | item#1 |
405 ## # | 2 | item#2 |
406 ##
407 ## for row in db.instantRows(sql"SELECT * FROM my_table"):
408 ## echo "id:" & row[0]
409 ## echo "name:" & row[1]
410 ## echo "length:" & $len(row)
411 ##
412 ## # Output:
413 ## # id:1
414 ## # name:item#1
415 ## # length:2
416 ## # id:2
417 ## # name:item#2
418 ## # length:2
419 ##
420 ## db.close()
421 ## ```
422 var stmt = setupQuery(db, query, args)
423 try:
424 while step(stmt) == SQLITE_ROW:
425 yield stmt
426 finally:
427 if finalize(stmt) != SQLITE_OK: dbError(db)
428
429 iterator instantRows*(db: DbConn, stmtName: SqlPrepared): InstantRow
430 {.tags: [ReadDbEffect,WriteDbEffect], since: (1, 3).} =
431 var stmt = setupQuery(db, stmtName).PStmt
432 try:
433 while step(stmt) == SQLITE_ROW:
434 yield stmt
435 except:
436 dbError(db)
437
438 proc toTypeKind(t: var DbType; x: int32) =
439 case x
440 of SQLITE_INTEGER:
441 t.kind = dbInt
442 t.size = 8
443 of SQLITE_FLOAT:
444 t.kind = dbFloat
445 t.size = 8
446 of SQLITE_BLOB: t.kind = dbBlob
447 of SQLITE_NULL: t.kind = dbNull
448 of SQLITE_TEXT: t.kind = dbVarchar
449 else: t.kind = dbUnknown
450
451 proc setColumns(columns: var DbColumns; x: PStmt) =
452 let L = column_count(x)
453 setLen(columns, L)
454 for i in 0'i32 ..< L:
455 columns[i].name = $column_name(x, i)
456 columns[i].typ.name = $column_decltype(x, i)
457 toTypeKind(columns[i].typ, column_type(x, i))
458 columns[i].tableName = $column_table_name(x, i)
459
460 iterator instantRows*(db: DbConn; columns: var DbColumns; query: SqlQuery,
461 args: varargs[string, `$`]): InstantRow
462 {.tags: [ReadDbEffect].} =
463 ## Similar to `instantRows iterator <#instantRows.i,DbConn,SqlQuery,varargs[string,]>`_,
464 ## but sets information about columns to `columns`.
465 ##
466 ## **Examples:**
467 ##
468 ## ```Nim
469 ## let db = open("mytest.db", "", "", "")
470 ##
471 ## # Records of my_table:
472 ## # | id | name |
473 ## # |----|----------|
474 ## # | 1 | item#1 |
475 ## # | 2 | item#2 |
476 ##
477 ## var columns: DbColumns
478 ## for row in db.instantRows(columns, sql"SELECT * FROM my_table"):
479 ## discard
480 ## echo columns[0]
481 ##
482 ## # Output:
483 ## # (name: "id", tableName: "my_table", typ: (kind: dbNull,
484 ## # notNull: false, name: "INTEGER", size: 0, maxReprLen: 0, precision: 0,
485 ## # scale: 0, min: 0, max: 0, validValues: @[]), primaryKey: false,
486 ## # foreignKey: false)
487 ##
488 ## db.close()
489 ## ```
490 var stmt = setupQuery(db, query, args)
491 setColumns(columns, stmt)
492 try:
493 while step(stmt) == SQLITE_ROW:
494 yield stmt
495 finally:
496 if finalize(stmt) != SQLITE_OK: dbError(db)
497
498 proc `[]`*(row: InstantRow, col: int32): string {.inline.} =
499 ## Returns text for given column of the row.
500 ##
501 ## See also:
502 ## * `instantRows iterator <#instantRows.i,DbConn,SqlQuery,varargs[string,]>`_
503 ## example code
504 $column_text(row, col)
505
506 proc unsafeColumnAt*(row: InstantRow, index: int32): cstring {.inline.} =
507 ## Returns cstring for given column of the row.
508 ##
509 ## See also:
510 ## * `instantRows iterator <#instantRows.i,DbConn,SqlQuery,varargs[string,]>`_
511 ## example code
512 column_text(row, index)
513
514 proc len*(row: InstantRow): int32 {.inline.} =
515 ## Returns number of columns in a row.
516 ##
517 ## See also:
518 ## * `instantRows iterator <#instantRows.i,DbConn,SqlQuery,varargs[string,]>`_
519 ## example code
520 column_count(row)
521
522 proc getRow*(db: DbConn, query: SqlQuery,
523 args: varargs[string, `$`]): Row {.tags: [ReadDbEffect].} =
524 ## Retrieves a single row. If the query doesn't return any rows, this proc
525 ## will return a `Row` with empty strings for each column.
526 ##
527 ## **Examples:**
528 ##
529 ## ```Nim
530 ## let db = open("mytest.db", "", "", "")
531 ##
532 ## # Records of my_table:
533 ## # | id | name |
534 ## # |----|----------|
535 ## # | 1 | item#1 |
536 ## # | 2 | item#2 |
537 ##
538 ## doAssert db.getRow(sql"SELECT id, name FROM my_table"
539 ## ) == Row(@["1", "item#1"])
540 ## doAssert db.getRow(sql"SELECT id, name FROM my_table WHERE id = ?",
541 ## 2) == Row(@["2", "item#2"])
542 ##
543 ## # Returns empty.
544 ## doAssert db.getRow(sql"INSERT INTO my_table (id, name) VALUES (?, ?)",
545 ## 3, "item#3") == @[]
546 ## doAssert db.getRow(sql"DELETE FROM my_table WHERE id = ?", 3) == @[]
547 ## doAssert db.getRow(sql"UPDATE my_table SET name = 'ITEM#1' WHERE id = ?",
548 ## 1) == @[]
549 ## db.close()
550 ## ```
551 var stmt = setupQuery(db, query, args)
552 var L = (column_count(stmt))
553 result = newRow(L)
554 if step(stmt) == SQLITE_ROW:
555 setRow(stmt, result, L)
556 if finalize(stmt) != SQLITE_OK: dbError(db)
557
558 proc getAllRows*(db: DbConn, query: SqlQuery,
559 args: varargs[string, `$`]): seq[Row] {.tags: [ReadDbEffect].} =
560 ## Executes the query and returns the whole result dataset.
561 ##
562 ## **Examples:**
563 ##
564 ## ```Nim
565 ## let db = open("mytest.db", "", "", "")
566 ##
567 ## # Records of my_table:
568 ## # | id | name |
569 ## # |----|----------|
570 ## # | 1 | item#1 |
571 ## # | 2 | item#2 |
572 ##
573 ## doAssert db.getAllRows(sql"SELECT id, name FROM my_table") == @[Row(@["1", "item#1"]), Row(@["2", "item#2"])]
574 ## db.close()
575 ## ```
576 result = @[]
577 for r in fastRows(db, query, args):
578 result.add(r)
579
580 proc getAllRows*(db: DbConn, stmtName: SqlPrepared): seq[Row]
581 {.tags: [ReadDbEffect,WriteDbEffect], since: (1, 3).} =
582 result = @[]
583 for r in fastRows(db, stmtName):
584 result.add(r)
585
586 iterator rows*(db: DbConn, query: SqlQuery,
587 args: varargs[string, `$`]): Row {.tags: [ReadDbEffect].} =
588 ## Similar to `fastRows iterator <#fastRows.i,DbConn,SqlQuery,varargs[string,]>`_,
589 ## but slower and safe.
590 ##
591 ## **Examples:**
592 ##
593 ## ```Nim
594 ## let db = open("mytest.db", "", "", "")
595 ##
596 ## # Records of my_table:
597 ## # | id | name |
598 ## # |----|----------|
599 ## # | 1 | item#1 |
600 ## # | 2 | item#2 |
601 ##
602 ## for row in db.rows(sql"SELECT id, name FROM my_table"):
603 ## echo row
604 ##
605 ## ## Output:
606 ## ## @["1", "item#1"]
607 ## ## @["2", "item#2"]
608 ##
609 ## db.close()
610 ## ```
611 for r in fastRows(db, query, args): yield r
612
613 iterator rows*(db: DbConn, stmtName: SqlPrepared): Row
614 {.tags: [ReadDbEffect,WriteDbEffect], since: (1, 3).} =
615 for r in fastRows(db, stmtName): yield r
616
617 proc getValue*(db: DbConn, query: SqlQuery,
618 args: varargs[string, `$`]): string {.tags: [ReadDbEffect].} =
619 ## Executes the query and returns the first column of the first row of the
620 ## result dataset. Returns `""` if the dataset contains no rows or the database
621 ## value is `NULL`.
622 ##
623 ## **Examples:**
624 ##
625 ## ```Nim
626 ## let db = open("mytest.db", "", "", "")
627 ##
628 ## # Records of my_table:
629 ## # | id | name |
630 ## # |----|----------|
631 ## # | 1 | item#1 |
632 ## # | 2 | item#2 |
633 ##
634 ## doAssert db.getValue(sql"SELECT name FROM my_table WHERE id = ?",
635 ## 2) == "item#2"
636 ## doAssert db.getValue(sql"SELECT id, name FROM my_table") == "1"
637 ## doAssert db.getValue(sql"SELECT name, id FROM my_table") == "item#1"
638 ##
639 ## db.close()
640 ## ```
641 var stmt = setupQuery(db, query, args)
642 if step(stmt) == SQLITE_ROW:
643 let cb = column_bytes(stmt, 0)
644 if cb == 0:
645 result = ""
646 else:
647 if column_type(stmt, 0) == SQLITE_BLOB:
648 result.setLen(cb)
649 copyMem(addr(result[0]), column_blob(stmt, 0), cb)
650 else:
651 result = newStringOfCap(cb)
652 add(result, column_text(stmt, 0))
653 else:
654 result = ""
655 if finalize(stmt) != SQLITE_OK: dbError(db)
656
657 proc getValue*(db: DbConn, stmtName: SqlPrepared): string
658 {.tags: [ReadDbEffect,WriteDbEffect], since: (1, 3).} =
659 var stmt = setupQuery(db, stmtName).PStmt
660 if step(stmt) == SQLITE_ROW:
661 let cb = column_bytes(stmt, 0)
662 if cb == 0:
663 result = ""
664 else:
665 if column_type(stmt, 0) == SQLITE_BLOB:
666 result.setLen(cb)
667 copyMem(addr(result[0]), column_blob(stmt, 0), cb)
668 else:
669 result = newStringOfCap(cb)
670 add(result, column_text(stmt, 0))
671 else:
672 result = ""
673
674 proc tryInsertID*(db: DbConn, query: SqlQuery,
675 args: varargs[string, `$`]): int64
676 {.tags: [WriteDbEffect], raises: [DbError].} =
677 ## Executes the query (typically "INSERT") and returns the
678 ## generated ID for the row or -1 in case of an error.
679 ##
680 ## **Examples:**
681 ##
682 ## ```Nim
683 ## let db = open("mytest.db", "", "", "")
684 ## db.exec(sql"CREATE TABLE my_table (id INTEGER, name VARCHAR(50) NOT NULL)")
685 ##
686 ## doAssert db.tryInsertID(sql"INSERT INTO not_exist_table (id, name) VALUES (?, ?)",
687 ## 1, "item#1") == -1
688 ## db.close()
689 ## ```
690 assert(not db.isNil, "Database not connected.")
691 var q = dbFormat(query, args)
692 var stmt: sqlite3.PStmt
693 result = -1
694 if prepare_v2(db, q.cstring, q.len.cint, stmt, nil) == SQLITE_OK:
695 if step(stmt) == SQLITE_DONE:
696 result = last_insert_rowid(db)
697 if finalize(stmt) != SQLITE_OK:
698 result = -1
699 else:
700 discard finalize(stmt)
701
702 proc insertID*(db: DbConn, query: SqlQuery,
703 args: varargs[string, `$`]): int64 {.tags: [WriteDbEffect].} =
704 ## Executes the query (typically "INSERT") and returns the
705 ## generated ID for the row.
706 ##
707 ## Raises a `DbError` exception when failed to insert row.
708 ## For Postgre this adds `RETURNING id` to the query, so it only works
709 ## if your primary key is named `id`.
710 ##
711 ## **Examples:**
712 ##
713 ## ```Nim
714 ## let db = open("mytest.db", "", "", "")
715 ## db.exec(sql"CREATE TABLE my_table (id INTEGER, name VARCHAR(50) NOT NULL)")
716 ##
717 ## for i in 0..2:
718 ## let id = db.insertID(sql"INSERT INTO my_table (id, name) VALUES (?, ?)", i, "item#" & $i)
719 ## echo "LoopIndex = ", i, ", InsertID = ", id
720 ##
721 ## # Output:
722 ## # LoopIndex = 0, InsertID = 1
723 ## # LoopIndex = 1, InsertID = 2
724 ## # LoopIndex = 2, InsertID = 3
725 ##
726 ## db.close()
727 ## ```
728 result = tryInsertID(db, query, args)
729 if result < 0: dbError(db)
730
731 proc tryInsert*(db: DbConn, query: SqlQuery, pkName: string,
732 args: varargs[string, `$`]): int64
733 {.tags: [WriteDbEffect], raises: [DbError], since: (1, 3).} =
734 ## same as tryInsertID
735 tryInsertID(db, query, args)
736
737 proc insert*(db: DbConn, query: SqlQuery, pkName: string,
738 args: varargs[string, `$`]): int64
739 {.tags: [WriteDbEffect], since: (1, 3).} =
740 ## same as insertId
741 result = tryInsert(db, query, pkName, args)
742 if result < 0: dbError(db)
743
744 proc execAffectedRows*(db: DbConn, query: SqlQuery,
745 args: varargs[string, `$`]): int64 {.
746 tags: [ReadDbEffect, WriteDbEffect].} =
747 ## Executes the query (typically "UPDATE") and returns the
748 ## number of affected rows.
749 ##
750 ## **Examples:**
751 ##
752 ## ```Nim
753 ## let db = open("mytest.db", "", "", "")
754 ##
755 ## # Records of my_table:
756 ## # | id | name |
757 ## # |----|----------|
758 ## # | 1 | item#1 |
759 ## # | 2 | item#2 |
760 ##
761 ## doAssert db.execAffectedRows(sql"UPDATE my_table SET name = 'TEST'") == 2
762 ##
763 ## db.close()
764 ## ```
765 exec(db, query, args)
766 result = changes(db)
767
768 proc execAffectedRows*(db: DbConn, stmtName: SqlPrepared): int64
769 {.tags: [ReadDbEffect, WriteDbEffect],since: (1, 3).} =
770 exec(db, stmtName)
771 result = changes(db)
772
773 proc close*(db: DbConn) {.tags: [DbEffect].} =
774 ## Closes the database connection.
775 ##
776 ## **Examples:**
777 ## ```Nim
778 ## let db = open("mytest.db", "", "", "")
779 ## db.close()
780 ## ```
781 if sqlite3.close(db) != SQLITE_OK: dbError(db)
782
783 proc open*(connection, user, password, database: string): DbConn {.
784 tags: [DbEffect].} =
785 ## Opens a database connection. Raises a `DbError` exception if the connection
786 ## could not be established.
787 ##
788 ## **Note:** Only the `connection` parameter is used for `sqlite`.
789 ##
790 ## **Examples:**
791 ## ```Nim
792 ## try:
793 ## let db = open("mytest.db", "", "", "")
794 ## ## do something...
795 ## ## db.getAllRows(sql"SELECT * FROM my_table")
796 ## db.close()
797 ## except:
798 ## stderr.writeLine(getCurrentExceptionMsg())
799 ## ```
800 var db: DbConn
801 if sqlite3.open(connection, db) == SQLITE_OK:
802 result = db
803 else:
804 dbError(db)
805
806 proc setEncoding*(connection: DbConn, encoding: string): bool {.
807 tags: [DbEffect].} =
808 ## Sets the encoding of a database connection, returns `true` for
809 ## success, `false` for failure.
810 ##
811 ## **Note:** The encoding cannot be changed once it's been set.
812 ## According to SQLite3 documentation, any attempt to change
813 ## the encoding after the database is created will be silently
814 ## ignored.
815 exec(connection, sql"PRAGMA encoding = ?", [encoding])
816 result = connection.getValue(sql"PRAGMA encoding") == encoding
817
818 proc finalize*(sqlPrepared:SqlPrepared) {.discardable, since: (1, 3).} =
819 discard finalize(sqlPrepared.PStmt)
820
821 template dbBindParamError*(paramIdx: int, val: varargs[untyped]) =
822 ## Raises a `DbError` exception.
823 var e: ref DbError
824 new(e)
825 e.msg = "error binding param in position " & $paramIdx
826 raise e
827
828 proc bindParam*(ps: SqlPrepared, paramIdx: int, val: int32) {.since: (1, 3).} =
829 ## Binds a int32 to the specified paramIndex.
830 if bind_int(ps.PStmt, paramIdx.int32, val) != SQLITE_OK:
831 dbBindParamError(paramIdx, val)
832
833 proc bindParam*(ps: SqlPrepared, paramIdx: int, val: int64) {.since: (1, 3).} =
834 ## Binds a int64 to the specified paramIndex.
835 if bind_int64(ps.PStmt, paramIdx.int32, val) != SQLITE_OK:
836 dbBindParamError(paramIdx, val)
837
838 proc bindParam*(ps: SqlPrepared, paramIdx: int, val: int) {.since: (1, 3).} =
839 ## Binds a int to the specified paramIndex.
840 when sizeof(int) == 8:
841 bindParam(ps, paramIdx, val.int64)
842 else:
843 bindParam(ps, paramIdx, val.int32)
844
845 proc bindParam*(ps: SqlPrepared, paramIdx: int, val: float64) {.since: (1, 3).} =
846 ## Binds a 64bit float to the specified paramIndex.
847 if bind_double(ps.PStmt, paramIdx.int32, val) != SQLITE_OK:
848 dbBindParamError(paramIdx, val)
849
850 proc bindNull*(ps: SqlPrepared, paramIdx: int) {.since: (1, 3).} =
851 ## Sets the bindparam at the specified paramIndex to null
852 ## (default behaviour by sqlite).
853 if bind_null(ps.PStmt, paramIdx.int32) != SQLITE_OK:
854 dbBindParamError(paramIdx)
855
856 proc bindParam*(ps: SqlPrepared, paramIdx: int, val: string, copy = true) {.since: (1, 3).} =
857 ## Binds a string to the specified paramIndex.
858 ## if copy is true then SQLite makes its own private copy of the data immediately
859 if bind_text(ps.PStmt, paramIdx.int32, val.cstring, val.len.int32, if copy: SQLITE_TRANSIENT else: SQLITE_STATIC) != SQLITE_OK:
860 dbBindParamError(paramIdx, val)
861
862 proc bindParam*(ps: SqlPrepared, paramIdx: int,val: openArray[byte], copy = true) {.since: (1, 3).} =
863 ## binds a blob to the specified paramIndex.
864 ## if copy is true then SQLite makes its own private copy of the data immediately
865 let len = val.len
866 if bind_blob(ps.PStmt, paramIdx.int32, val[0].unsafeAddr, len.int32, if copy: SQLITE_TRANSIENT else: SQLITE_STATIC) != SQLITE_OK:
867 dbBindParamError(paramIdx, val)
868
869 when not defined(testing) and isMainModule:
870 var db = open(":memory:", "", "", "")
871 exec(db, sql"create table tbl1(one varchar(10), two smallint)", [])
872 exec(db, sql"insert into tbl1 values('hello!',10)", [])
873 exec(db, sql"insert into tbl1 values('goodbye', 20)", [])
874 var p1 = db.prepare "create table tbl2(one varchar(10), two smallint)"
875 exec(db, p1)
876 finalize(p1)
877 var p2 = db.prepare "insert into tbl2 values('hello!',10)"
878 exec(db, p2)
879 finalize(p2)
880 var p3 = db.prepare "insert into tbl2 values('goodbye', 20)"
881 exec(db, p3)
882 finalize(p3)
883 #db.query("create table tbl1(one varchar(10), two smallint)")
884 #db.query("insert into tbl1 values('hello!',10)")
885 #db.query("insert into tbl1 values('goodbye', 20)")
886 for r in db.rows(sql"select * from tbl1", []):
887 echo(r[0], r[1])
888 for r in db.instantRows(sql"select * from tbl1", []):
889 echo(r[0], r[1])
890 var p4 = db.prepare "select * from tbl2"
891 for r in db.rows(p4):
892 echo(r[0], r[1])
893 finalize(p4)
894 var i5 = 0
895 var p5 = db.prepare "select * from tbl2"
896 for r in db.instantRows(p5):
897 inc i5
898 echo(r[0], r[1])
899 assert i5 == 2
900 finalize(p5)
901
902 for r in db.rows(sql"select * from tbl2", []):
903 echo(r[0], r[1])
904 for r in db.instantRows(sql"select * from tbl2", []):
905 echo(r[0], r[1])
906 var p6 = db.prepare "select * from tbl2 where one = ? "
907 p6.bindParams("goodbye")
908 var rowsP3 = 0
909 for r in db.rows(p6):
910 rowsP3 = 1
911 echo(r[0], r[1])
912 assert rowsP3 == 1
913 finalize(p6)
914
915 var p7 = db.prepare "select * from tbl2 where two=?"
916 p7.bindParams(20'i32)
917 when sizeof(int) == 4:
918 p7.bindParams(20)
919 var rowsP = 0
920 for r in db.rows(p7):
921 rowsP = 1
922 echo(r[0], r[1])
923 assert rowsP == 1
924 finalize(p7)
925
926 exec(db, sql"CREATE TABLE photos(ID INTEGER PRIMARY KEY AUTOINCREMENT, photo BLOB)")
927 var p8 = db.prepare "INSERT INTO photos (ID,PHOTO) VALUES (?,?)"
928 var d = "abcdefghijklmnopqrstuvwxyz"
929 p8.bindParams(1'i32, "abcdefghijklmnopqrstuvwxyz")
930 exec(db, p8)
931 finalize(p8)
932 var p10 = db.prepare "INSERT INTO photos (ID,PHOTO) VALUES (?,?)"
933 p10.bindParams(2'i32,nil)
934 exec(db, p10)
935 exec( db, p10, 3, nil)
936 finalize(p10)
937 for r in db.rows(sql"select * from photos where ID = 1", []):
938 assert r[1].len == d.len
939 assert r[1] == d
940 var i6 = 0
941 for r in db.rows(sql"select * from photos where ID = 3", []):
942 i6 = 1
943 assert i6 == 1
944 var p9 = db.prepare("select * from photos where PHOTO is ?")
945 p9.bindParams(nil)
946 var rowsP2 = 0
947 for r in db.rows(p9):
948 rowsP2 = 1
949 echo(r[0], repr r[1])
950 assert rowsP2 == 1
951 finalize(p9)
952
953 db_sqlite.close(db)