sqliteral

Search:
Group by:

A high level SQLite API with support for multi-threading, prepared statements, proper typing, zero-copy data paths, debugging, JSON, optimizing, backups, and more.

Example (using JSON extensions)

import sqliteral, malebolgia
from strutils import find

const Schema = "CREATE TABLE IF NOT EXISTS Example(name TEXT NOT NULL, jsondata TEXT NOT NULL) STRICT"

type SqlStatements = enum
  Insert = """INSERT INTO Example (name, jsondata)
  VALUES (json_extract(?, '$.name'), json_extract(?, '$.data'))"""
  Count = "SELECT count(*) FROM Example"
  Select = "SELECT json_extract(jsondata, '$.array') FROM Example"

let httprequest = """header BODY:{"name":"Alice", "data":{"info":"xxx", "array":["a","b","c"]}}"""

var
  db: SQLiteral
  prepared {.threadvar.}: bool

when not defined(release): db.setLogger(proc(db: SQLiteral, msg: string, code: int) = echo msg)

proc select() =
  {.gcsafe.}:
    if not prepared:
      db.prepareStatements(SqlStatements)
      prepared = true
    for row in db.rows(Select):
      stdout.write(row.getCString(0))
      stdout.write('\n')
    finalizeStatements()

proc run() =
  db.openDatabase("ex.db", Schema)
  defer: db.close()
  db.prepareStatements(SqlStatements)
  let body = httprequest.toDb(httprequest.find("BODY:") + 5, httprequest.len - 1)
  if not db.json_valid(body): quit(0)
  
  echo "inserting 10000 rows..."
  db.transaction:
    for i in 1 .. 10000: discard db.insert(Insert, body, body)
  
  echo "10000 rows inserted. Press <Enter> to select all in 4 threads..."
  discard stdin.readChar()
  var m = createMaster()
  m.awaitAll:
    for i in 1 .. 4: m.spawn select()
  stdout.flushFile()
  echo "Selected 4 * ", db.getTheInt(Count), " = " & $(4 * db.getTheInt(Count)) & " rows."

run()

Compiling with sqlite3.c

First, sqlite3.c amalgamation must be on compiler search path.
You can extract it from a zip available at https://www.sqlite.org/download.html.
Then, -d:staticSqlitecompiler option must be used.

For your convenience, -d:staticSqlite triggers some useful SQLite compiler options, consult sqliteral source code or about() proc for details. These can be turned off with -d:disableSqliteoptions option.

Types

DbValue = object
  case kind*: DbValueKind
  of sqliteInteger:
    intVal*: int64
  of sqliteReal:
    floatVal*: float64
  of sqliteText:
    textVal*: tuple[chararray: cstring, len: int32]
  of sqliteBlob:
    blobVal*: seq[byte]

Represents a value in a SQLite database.
https://www.sqlite.org/datatype3.html
NULL values are not possible to avoid the billion-dollar mistake.

SQLError = ref object of CatchableError
  rescode*: int
https://www.sqlite.org/rescode.html 
SQLiteral = object
  sqlite*: PSqlite3
  dbname*: string
  inreadonlymode*: bool
  backupsinprogress*: int

Consts

MaxDatabases {.intdefine.} = 2
Compile time define pragma that limits amount of databases 
MaxStatements {.intdefine.} = 200
Compile time define pragma that limits amount of prepared statements 
SQLiteralVersion = "4.0.1"

Procs

proc `$`[T: DbValue](val: T): string {.inline.}
proc about(db: SQLiteral) {....raises: [SQLError, OSError],
                            tags: [RootEffect, ReadIOEffect], forbids: [].}
Echoes some info about the database. 
proc bindParams(sql: PStmt; params: varargs[DbValue]): int {.inline, ...raises: [],
    tags: [], forbids: [].}
proc cancelBackup(db: var SQLiteral; backupdb: PSqlite3;
                  backuphandle: PSqlite3_Backup) {....raises: [SQLError],
    tags: [RootEffect], forbids: [].}
Cancels an ongoing backup process. 
proc close(db: var SQLiteral) {....raises: [SQLError], tags: [RootEffect],
                                forbids: [].}
Closes the database. 
proc columnExists(db: SQLiteral; table: string; column: string): bool {.
    ...raises: [Exception, SQLError], tags: [RootEffect], forbids: [].}
Returns true if given column exists in given table 
proc doLog(db: SQLiteral; statement: string; params: varargs[DbValue, toDb]) {.
    inline, ...raises: [], tags: [RootEffect], forbids: [].}
proc exec(db: SQLiteral; pstatement: PStmt; params: varargs[DbValue, toDb]) {.
    inline, ...raises: [SQLError], tags: [RootEffect], forbids: [].}
Executes given prepared statement 
proc exec(db: SQLiteral; statement: enum; params: varargs[DbValue, toDb]) {.
    inline.}
Executes given statement 
proc exes(db: SQLiteral; sql: string) {....raises: [SQLError], tags: [RootEffect],
                                        forbids: [].}

Prepares, executes and finalizes given semicolon-separated sql statements.
For security and performance reasons, this proc should be used with caution.

proc finalizeStatements() {....raises: [], tags: [], forbids: [].}

Counterpart to prepareStatements: Call this once just before a thread is going to get destroyed. Unlike prepareStatements, this does not need to be invoked for every database, one call per thread is enough. Also unlike prepareStatements, extra calls do no harm.

There is some code that tries to trigger this automatically. For example, calling close triggers this automatically for that thread. Unfortunately this automation does not always work. In that case, you will get an "unable to close due to unfinalized statements" message when calling close, and in that case, you will have to do the finalizeStatements calls manually (see the main example for an example).

proc getAsStrings(prepared: PStmt): seq[string] {....raises: [], tags: [],
    forbids: [].}
Returns values of all result columns as a sequence of strings. This proc is mainly useful for debugging purposes. 
proc getCString(prepared: PStmt; col: int32 = 0): cstring {.inline, ...raises: [],
    tags: [], forbids: [].}

Returns value of TEXT -type column at given column index as cstring.
Zero-copy, but result is not available after cursor movement or statement reset.

proc getFloat(prepared: PStmt; col: int32 = 0): float64 {.inline, ...raises: [],
    tags: [], forbids: [].}
Returns value of REAL -type column at given column index 
proc getInt(prepared: PStmt; col: int32 = 0): int64 {.inline, ...raises: [],
    tags: [], forbids: [].}
Returns value of INTEGER -type column at given column index 
proc getLastInsertRowid(db: SQLiteral): int64 {.inline, ...raises: [], tags: [],
    forbids: [].}
https://www.sqlite.org/c3ref/last_insert_rowid.html 
proc getSeq(prepared: PStmt; col: int32 = 0): seq[byte] {.inline, ...raises: [],
    tags: [], forbids: [].}
Returns value of BLOB -type column at given column index 
proc getStatus(db: SQLiteral; status: int; resethighest = false): (int, int) {.
    ...raises: [SQLError], tags: [RootEffect], forbids: [].}

Retrieves queried status info. See https://www.sqlite.org/c3ref/c_dbstatus_options.html

Example:

const SQLITE_DBSTATUS_CACHE_USED = 1
echo "current cache usage: ", db.getStatus(SQLITE_DBSTATUS_CACHE_USED)[0]
proc getString(prepared: PStmt; col: int32 = 0): string {.inline, ...raises: [],
    tags: [], forbids: [].}
Returns value of TEXT -type column at given column index as string 
proc getTheInt(db: SQLiteral; s: string): int64 {.inline,
    ...raises: [SQLError, SQLError], tags: [RootEffect], forbids: [].}
| Dynamically prepares, executes and finalizes given query and returns value of INTEGER -type column at column index 0 of first result row.

If query does not return any rows, returns -2147483647 (low(int32) + 1).
For security and performance reasons, this proc should be used with caution.

proc getTheInt(db: SQLiteral; statement: enum; params: varargs[DbValue, toDb]): int64 {.
    inline.}

Executes query and returns value of INTEGER -type column at column index 0 of first result row.
If query does not return any rows, returns -2147483647 (low(int32) + 1).
Automatically resets the statement.

proc getTheString(db: SQLiteral; s: string): string {.inline,
    ...raises: [SQLError, SQLError], tags: [RootEffect], forbids: [].}
| Dynamically prepares, executes and finalizes given query and returns value of TEXT -type column at column index 0 of first result row.

If query does not return any rows, returns empty string.
For security and performance reasons, this proc should be used with caution.

proc getTheString(db: SQLiteral; statement: enum; params: varargs[DbValue, toDb]): string {.
    inline.}

Executes query and returns value of TEXT -type column at column index 0 of first result row.
If query does not return any rows, returns empty string.
Automatically resets the statement.

proc initBackup(db: var SQLiteral; backupfilename: string): tuple[
    backupdb: PSqlite3, backuphandle: PSqlite3_Backup] {.
    ...raises: [SQLError, SQLError], tags: [RootEffect], forbids: [].}

Initializes backup processing, returning variables to use with stepBackup proc.

Note that close will fail with SQLITE_BUSY if there's an unfinished backup process going on.

proc insert(db: SQLiteral; statement: enum; params: varargs[DbValue, toDb]): int64 {.
    inline.}

Executes given statement and, if succesful, returns db.getLastinsertRowid().
If not succesful, returns -2147483647 (low(int32) + 1).

proc interrupt(db: var SQLiteral) {....raises: [], tags: [], forbids: [].}
Interrupts long running operations by calling https://www.sqlite.org/c3ref/interrupt.html 
proc isIntransaction(db: SQLiteral): bool {.inline, ...raises: [], tags: [],
    forbids: [].}
proc json_extract(db: var SQLiteral; path: string;
                  jsonstring: varargs[DbValue, toDb]): string {.
    ...raises: [SQLError, Exception], tags: [RootEffect], forbids: [].}
proc json_patch(db: var SQLiteral; patch: string;
                jsonstring: varargs[DbValue, toDb]): string {.
    ...raises: [SQLError, Exception], tags: [RootEffect], forbids: [].}
proc json_valid(db: var SQLiteral; jsonstring: varargs[DbValue, toDb]): bool {.
    ...raises: [SQLError, Exception], tags: [RootEffect], forbids: [].}
proc openDatabase(db: var SQLiteral; dbname: string; schema: string;
                  maxKbSize = 0; wal = true) {.inline,
    ...raises: [SQLError, Exception], tags: [RootEffect], forbids: [].}
Open database with a single schema. 
proc openDatabase(db: var SQLiteral; dbname: string; schemas: openArray[string];
                  maxKbSize = 0; wal = true; ignorableschemaerrors: openArray[
    string] = @["duplicate column name", "no such column"]) {.
    ...raises: [SQLError, Exception], tags: [RootEffect], forbids: [].}

Opens an exclusive connection, boots up the database, executes given schemas and prepares given statements.

If dbname is not a path, current working directory will be used.

If wal = true, database is opened in WAL mode with NORMAL synchronous setting.
If wal = false, database is opened in PERSIST mode with FULL synchronous setting.
https://www.sqlite.org/wal.html
https://www.sqlite.org/pragma.html#pragma_synchronous

If maxKbSize == 0, database size is limited only by OS or hardware with possibly severe consequences.

ignorableschemaerrors is a list of error message snippets for sql errors that are to be ignored. If a clause may error, it must be given in a separate schema as its unique clause. If * is given as ignorable error, it means that all errors will be ignored.

Note that by default, "duplicate column name" (ADD COLUMN) and "no such column" (DROP COLUMN) -errors will be ignored. Example below.

const
  Schema1 = "CREATE TABLE IF NOT EXISTS Example(data TEXT NOT NULL) STRICT"
  Schema2 = "this is to be ignored"
  Schema3 = """ALTER TABLE Example ADD COLUMN newcolumn TEXT NOT NULL DEFAULT """""

var db1, db2, db3: SQLiteral

proc logger(db: SQLiteral, msg: string, code: int) = echo msg
db1.setLogger(logger); db2.setLogger(logger); db3.setLogger(logger)

db1.openDatabase("example1.db", [Schema1, Schema3]); db1.close()
db2.openDatabase("example2.db", [Schema1, Schema2],
 ignorableschemaerrors = ["""this": syntax error"""]); db2.close()
db3.openDatabase("example3.db", [Schema1, Schema2, Schema3],
 ignorableschemaerrors = ["*"]); db3.close()
proc optimize(db: var SQLiteral; pagesize = -1; walautocheckpoint = -1) {.
    ...raises: [], tags: [RootEffect], forbids: [].}

Vacuums and optimizes the database.

This proc should be run just before closing, when no other thread accesses the database.

In addition, database read/write performance ratio may be adjusted with parameters:
https://sqlite.org/pragma.html#pragma_page_size
https://www.sqlite.org/pragma.html#pragma_wal_checkpoint

proc prepareSql(db: SQLiteral; sql: cstring): PStmt {.inline,
    ...raises: [SQLError], tags: [RootEffect], forbids: [].}
Prepares a cstring into an executable statement 
proc prepareStatements(db: var SQLiteral; Statements: typedesc[enum])
Prepares the statements given as enum parameter. Call this exactly once from every thread that is going to access the database. Main example shows how this "exactly once"-requirement can be achieved with a boolean threadvar. 
proc rowExists(db: SQLiteral; sql: string): bool {.inline, ...raises: [SQLError],
    tags: [RootEffect], forbids: [].}

Returns true if query returns any rows.
For security reasons, this proc should be used with caution.

proc rowExists(db: SQLiteral; statement: enum; params: varargs[DbValue, toDb]): bool {.
    inline.}
Returns true if query returns any rows 
proc setLogger(db: var SQLiteral; logger: proc (sqliteral: SQLiteral;
    statement: string; code: int) {....gcsafe, raises: [].}; paramtruncat = 50) {.
    ...raises: [], tags: [], forbids: [].}

Set callback procedure to gather all executed statements with their parameters.

If code > 0, log concerns sqlite error with error code in question.

If code == -1, log may be of minor interest (originating from exes or statement preparation).

Paramtruncat parameter limits the maximum log length of parameters so that long inputs won't clutter logs. Value < 1 disables truncation.

You can use the same logger for multiple sqliterals, the caller is also given as parameter.

proc setOnCommitCallback(db: var SQLiteral; oncommit: proc (sqliteral: SQLiteral) {.
    ...gcsafe, raises: [].}) {....raises: [], tags: [], forbids: [].}
Set callback procedure that is triggered inside transaction proc, when commit to database has been executed. 
proc setReadonly(db: var SQLiteral; readonly: bool) {....raises: [SQLError],
    tags: [RootEffect], forbids: [].}
When in readonly mode:
  1. All transactions will be silently discarded
  2. Journal mode is changed to PERSIST in order to be able to change locking mode
  3. Locking mode is changed from EXCLUSIVE to NORMAL, allowing other connections access the database

Setting readonly fails with exception "cannot change into wal mode from within a transaction" when a statement is being executed, for example a result of a select is being iterated.

inreadonlymode property tells current mode.

proc stepBackup(db: var SQLiteral; backupdb: PSqlite3;
                backuphandle: PSqlite3_Backup; pagesperportion = 5.int32): int {.
    ...raises: [SQLError], tags: [RootEffect], forbids: [].}

Backs up a portion of the database pages (default: 5) to a destination initialized with initBackup.

Returns percentage of progress; 100% means that backup has been finished.

The idea (check example 2) is to put the thread to sleep between portions so that other operations can proceed concurrently.

Example:

from os import sleep
let (backupdb , backuphandle) = db.initBackup("./backup.db")
var progress: int
while progress < 100:
  sleep(250)
  progress = db.stepBackup(backupdb, backuphandle)
proc toDb(val: cstring; first, last: int): DbValue {.inline, ...raises: [],
    tags: [], forbids: [].}
proc toDb(val: cstring; len = -1): DbValue {.inline, ...raises: [], tags: [],
    forbids: [].}
proc toDb(val: openArray[char]; len = -1): DbValue {.inline, ...raises: [],
    tags: [], forbids: [].}
proc toDb(val: seq[byte]): DbValue {.inline, ...raises: [], tags: [], forbids: [].}
proc toDb(val: string; first, last: int): DbValue {.inline, ...raises: [],
    tags: [], forbids: [].}
proc toDb(val: string; len = -1): DbValue {.inline, ...raises: [], tags: [],
    forbids: [].}
proc toDb[T: DbValue](val: T): DbValue {.inline.}
proc toDb[T: Ordinal](val: T): DbValue {.inline.}
proc toDb[T: SomeFloat](val: T): DbValue {.inline.}
proc update(db: SQLiteral; sql: string; column: string; newvalue: DbValue;
            where: DbValue) {....raises: [Exception, SQLError, SQLError],
                              tags: [RootEffect], forbids: [].}

Dynamically constructs, prepares, executes and finalizes given update query.
Update must target one column and WHERE -clause must contain one value.
For security and performance reasons, this proc should be used with caution.

Iterators

iterator json_tree(db: SQLiteral; jsonstring: varargs[DbValue, toDb]): PStmt {.
    ...raises: [SQLError], tags: [RootEffect], forbids: [].}
iterator rows(db: SQLiteral; pstatement: PStmt; params: varargs[DbValue, toDb]): PStmt {.
    ...raises: [SQLError], tags: [RootEffect], forbids: [].}
Iterates over the query results 
iterator rows(db: SQLiteral; statement: enum; params: varargs[DbValue, toDb]): PStmt
Iterates over the query results

Templates

template checkRc(db: SQLiteral; resultcode: int)

Raises SQLError if resultcode notin SQLITE_OK, SQLITE_ROW, SQLITE_DONE
https://www.sqlite.org/rescode.html

template transaction(db: var SQLiteral; body: untyped)

Every write to database must happen inside some transaction.
Groups of reads must be wrapped in same transaction if mutual consistency required.
In WAL mode (the default), independent reads must NOT be wrapped in transaction to allow parallel processing.

template transactionsDisabled(db: var SQLiteral; body: untyped)
Executes body in between transactions (ie. does not start transaction, but transactions are blocked during this operation). 
template withRow(db: SQLiteral; sql: string; row, body: untyped)

Dynamically prepares and finalizes an sql query.
Name for the resulting prepared statement is given with row parameter.
The code block will be executed only if query returns a row.
For security and performance reasons, this proc should be used with caution.

template withRow(db: SQLiteral; statement: enum; params: varargs[DbValue, toDb];
                 row, body: untyped) {.dirty.}

Executes given statement.
Name for the prepared statement is given with row parameter.
The code block will be executed only if query returns a row.

template withRowOr(db: SQLiteral; sql: string; row, body1, body2: untyped)

Dynamically prepares and finalizes an sql query.
Name for the resulting prepared statement is given with row parameter.
First block will be executed if query returns a row, otherwise the second block.
For security and performance reasons, this proc should be used with caution.

Example:

db.withRowOr("SELECT (1) FROM sqlite_master", rowname):
  echo "database has some tables because first column = ", rowname.getInt(0)
do:
  echo "we have a fresh database"
template withRowOr(db: SQLiteral; statement: enum;
                   params: varargs[DbValue, toDb]; row, body1, body2: untyped)

Executes given statement.
Name for the prepared statement is given with row parameter.
First block will be executed if query returns a row, otherwise the second block.


Made with Nim. Generated: 2024-10-28 08:27:55 UTC