this module is a new rdbms-api for sqlite3.

main changes compared to the classic (db_sqlite) API:

no exceptions
faster (for a speed comparison please take a look at the examples directory)
transaction- and cleanup templates
the prepared statement is exposed to the API so it´s possible to bulk load huge datasets into the database
bulk row bindings per object or raw
huge ResultSets can be consumed with a stream-like API
binding and fetching is typed (the API comes in two flavours : Option and nim-type(integer,float64, string and blob) )
text and blob-types can be consumed by pointer (raw) or by the nim-types string and seq[byte]
backup-interface
metadata api (column-names and columntypes of tables can be retrieved)
db_sqlite importable without symbol clash

Note about string- and blob-handling: this comes in two flavours: traced and untraced. The traced version (which should be sufficient for most use cases) copies the blob/varchar immediately into a seq[byte]/nimstring while reading. The unmanaged version only provides the backend pointer and length; the content must copied (before consuming the resultSet's next row ) into the application domain.

storing into db is straightforward; the optional destructor callback is provided for both flavours. if the destructor callback is not used the object/ptr should stay valid till the transaction ends.

no implicit type conversion is performed - the caller needs to take care of the types expected. If the type does not match, sqlite tries a implicit type conversion. Further reading: https://www.sqlite.org/datatype3.html

in-memory-operation: keep in mind that each open(::inmemory::) opens a new database unless in shared cache mode - if you like to use a connectionpool be sure that the shared cache of sqlite3 is activated (untested)

At the moment no examples are within this doc. for examples please take a look at the examples subdirectory. the entire source was compiled with compiler version 0.19.9 / git hash: 28a83a838821cbe3efc8ddd412db966ca164ef5c

Imports

nimdb_common, nimdb_sqlite3w

Types

DbConn = PSqlite3
PreparedStatement = nimdb_sqlite3w.Pstmt: sqlite3's preparedStatement handle
ResultSet = PreparedStatement: needed to obtain the backend's results on a 'per row' base. update is not supported
RCode = nimdb_common.RCode: backends returncode collector. the vendorcode can be evaluated with the 'eval' templates (evalBindError/evalHasError/hasRows)
BulkBindToColsCb[T] = proc (ps: PreparedStatement; bindIdx: int; bind_obj: var T) {...}{.inline.}
PopulateCb[T] = proc (ps: ResultSet; firstidx: int; maxcols: int; out_obj: var T) {...}{.inline.}
SQLite3ColumnMetaData = object of DbTableColumnMetaData tableName*: string columnName*: string declDataType*: string ## decleared data type (note: sqlite stores the data typeless) collationSeqName*: string ## how things are sorted notNull*: bool ## true if column is defined as not null partOfPK*: bool ## true if column is part of a pk autoInc*: bool ## true if autoincremented

Consts

BindIdxStart = 1: defines the vendors leftmost parameter of the parameterized sql-query

Procs

proc getResultSet(ps: PreparedStatement; out_rc: var RCode): ResultSet {...}{.inline, raises: [], tags: [].}

the resultSet is automatically advanced to the first row ready to read. If the out_columnCount is 0, the statement was no select statement or already returned SQLITE_DONE. The PreparedStatement performs a 'wind-back' on each call of getResultSet (query is re-executed)

proc sqlite3VersionStr(): string {...}{.raises: [], tags: [].}

debugging purpose. returns the version and libversion_number as string

proc interrupt(conn: DbConn) {...}{.raises: [], tags: [].}

interrupts the current running query. Rollback is executed for a insert/update/delete. call this from a different thread (see sqlite3_busy_handler)

proc open(filename: string; user: string; password: string; out_param: var RCode; out_param_ext: var int; flags: int = 2 or 4): DbConn {...}{.tags: [DbEffect], raises: [].}

Opens a connection to the specified database. Utilizes the sqlite open_v2 interface. Parameters 'user' and 'password' are unused. if SQLITE_CANTOPEN is returned the out_param_ext is set by the wrapper call 'sqlite3_system_errno'.

The filename can be specified in a uri-style format. see:

https://www.sqlite.org/uri.html#uri_filenames_in_sqlite

proc openReadonly(filename: string; user: string; password: string; out_param: var RCode; out_param_ext: var int): DbConn {...}{. tags: [DbEffect], raises: [].}

vendor specific operation. Opens a database in readonly mode. The database file is opened readonly by sqlite3 at os file-level. The zVfsName is internally nil (default vfs)

proc close(db: DbConn; out_par: var RCode) {...}{.tags: [DbEffect], raises: [].}

closes the database connection.

proc dumpDbTo(srcConn: DbConn; dstConn: DbConn; outpar: var RCode; srcDbName: string = "main"; dstDbName: string = "main") {...}{.raises: [], tags: [].}

Vendor specific operation. Performs a hot database backup (snapshot) from the src to destionation db.

This proc call blocks till the entire database is copied completely. The dstConn is not allowed to have any pending transactions open.

The src and destination db could be both of type in memory or file based. just ensure that the dest db is completely empty and the page_size is equal to the src db (see pragma page_size)

for further reading please look at https://www.sqlite.org/backup.html

proc `$`(p: var Pstmt): string {...}{.raises: [], tags: [].}

Gets the normalized string representation out of the prepared statement. Could be used as a key for caching the statements in a map

proc `$`(par: RCode): string {...}{.raises: [], tags: [].}

debugging support converts the driver returncode into a string representation

proc bindInt32(ps: PreparedStatement; paramIdx: int; val: int32): int {...}{.inline, raises: [], tags: [].}

Binds a 32bit integer. no null handling is performed

proc bindInt32(ps: PreparedStatement; paramIdx: int; val: Option[int32]): int {...}{.inline, raises: [], tags: [].}

Binds a 32bit integer to the specified paramIndex. performs null handling. The return value is the sqlite returncode which can be evaluated by the evalHasError/evalBindError template

proc bindInt64(ps: PreparedStatement; paramIdx: int; val: int64): int {...}{.inline, raises: [], tags: [].}

Binds a 64bit integer to thespecified paramIndex. this version performs no null handling and is suitable for bulk update/insert. the return value is the sqlite returncode which can be evaluated by the evalHasError/evalBindError template

proc bindInt64(ps: PreparedStatement; paramIdx: int; val: Option[int64]): int {...}{.inline, raises: [], tags: [].}

Binds a 64bit integer to the specified paramIndex. performs null handling. the return value is the sqlite returncode which can be evaluated by the evalHasError/evalBindError template

proc bindFloat64(ps: PreparedStatement; paramIdx: int; val: Option[float64]): int {...}{. inline, raises: [], tags: [].}

Binds a 64bit float to the specified paramIndex. performs null handling. the return value is the sqlite returncode which can be evaluated by evalHasError/evalBindError template

proc bindFloat64(ps: PreparedStatement; paramIdx: int; val: float64): int {...}{.inline, raises: [], tags: [].}

Binds a 64bit float to the specified paramIndex. This version performs no null handling and is suitable for bulk update/insert. The return value is the sqlite returncode which can be evaluated by the evalHasError/evalBindError template

proc bindNull(ps: PreparedStatement; paramIdx: int): int {...}{.inline, raises: [], tags: [].}

Sets the bindparam at the specified paramIndex to null (default behaviour by sqlite). the return value is the sqlite returncode which can be evaluated by the evalHasError/evalBindError template

proc bindString(ps: PreparedStatement; paramIdx: int; val: var Option[string]; freeCb: SQLite3UnbindCb = nil): int {...}{.raises: [Exception], tags: [].}

Binds a string to the specified paramIndex. managed version with null handling. The return value is the sqlite returncode which can be evaluated by the evalHasError/evalBindError template

proc bindString(ps: PreparedStatement; paramIdx: int; val: var string; freeCb: SQLite3UnbindCb = nil): int {...}{.raises: [Exception], tags: [].}

binds a string to the specified paramIndex. managed version. this version is faster than the option type but nullhandling is not performed. the return value is the sqlite returncode which can be evaluated by the evalBindError template

proc bindStringUT(ps: PreparedStatement; paramIdx: int; val: pointer; val_len: int32; freeCb: SQLite3UnbindCb = nil): int {...}{.raises: [Exception], tags: [].}

Binds a string to the specified paramIndex. untraced version. The return value is the raw sqlite returncode which can be evaluated by the evalBindError template

proc bindBlob(ps: PreparedStatement; paramIdx: int; val: var Option[seq[byte]]; freeCb: SQLite3UnbindCb = nil): int {...}{.raises: [Exception], tags: [].}

binds a blob to the specified paramIndex. managed version. unless this callback is executed by the backend the sequence and it's contents need to stay valid. If no callback was specified the binding method is SQLITE_STATIC. The return value is the sqlite returncode which can be evaluated by the evalHasError/evalBindError template

proc bindBlob(ps: PreparedStatement; paramIdx: int; val: var seq[byte]; freeCb: SQLite3UnbindCb = nil): int {...}{.raises: [Exception], tags: [].}

binds a blob to the specified paramIndex. managed version. unless the callback was called by the backend the sequence needs to stay valid. if no callback was specified the binding method is SQLITE_STATIC this version is faster than the option type - nullhandling is not performed return value is the sqlite returncode which can be evaluated by the evalHasError/evalBindError template

proc bindBlobUT(ps: PreparedStatement; paramIdx: int; val: pointer; val_len: int32; freeCb: SQLite3UnbindCb = nil): int {...}{.raises: [Exception], tags: [].}

binds a blob to the specified index. untraced version. unless the callback was called the pointer needs to stay valid. if no callback was specified the binding method is SQLITE_STATIC return value is the sqlite returncode which can be evaluated by the evalHasError/evalBindError template

proc bulkBind[T](ps: PreparedStatement; binds: var openArray[T]; bindCols: BulkBindToColsCb; rowParamCount: int; out_msg: var RCode; paramIndex: int = BindIdxStart): int

binds a object collection to the preparedStatement. Binding is performed on a object per row base. the number of parameters within the query must be multiple of rowParamCount because all parameters should be bound or null values are written into the database. bulkBind returns 1 if all parameters are bound. In case of an error the returned index points to the faulted parameter row.

If unbound params present (seq length is smaller) it returns the next paramIndex which can be used to continue on the next call. If a error condition occurs while binding, the returned index points to the erronous row (start parameter)

proc newPreparedStatement(db: DbConn; query: SqlQuery; out_ps: var PreparedStatement; out_returncode: var RCode) {...}{.raises: [], tags: [].}

returns a new compiled preparedStatement. out_returncode can be consumed the the 'eval' templates

proc modifiedRowCount(db: DbConn): int32 {...}{.inline, raises: [], tags: [].}

reports how many rows were modified after the last statement/step execution

proc fetchInt64Opt(ps: ResultSet; cpos: int): Option[int64] {...}{.raises: [], tags: [].}

fetches an integer at the given column position. in case of null option(none) is returned. only valid if SQLITE_ROW was returned from step(). the leftmost column is defined in the const ResultIdxStart

proc fetchInt32Opt(ps: ResultSet; cpos: int): Option[int32] {...}{.raises: [], tags: [].}

fetches an 32 bit integer at the given column position. only valid if SQLITE_ROW was returned from step(). the leftmost column starts with 0

proc fetchInt64(ps: ResultSet; cpos: int): int64 {...}{.inline, raises: [], tags: [].}

Fast fetch an integer at the given column position(without SQLITE_NULL check). only valid if SQLITE_ROW was returned from step(). If the column type does not match an internal conversion is performed - no error delivery is performed.

proc fetchInt32(ps: ResultSet; cpos: int): int32 {...}{.inline, raises: [], tags: [].}

fast fetch an integer at the given column position(without SQLITE_NULL check). Only valid if SQLITE_ROW was returned from step() if the column type does not match an internal conversion is performed no error delivery is performed.

proc fetchFloat64Opt(ps: ResultSet; cpos: int): Option[float64] {...}{.raises: [], tags: [].}

fetches a float at the given column position. managed version. null check are performed.

proc fetchFloat64(ps: ResultSet; cpos: int): float64 {...}{.inline, raises: [], tags: [].}

fast fetch a float at the given column position. if the column type does not match an internal conversion is performed

proc fetchString(ps: ResultSet; cpos: int; out_rc: var RCode): Option[string] {...}{. raises: [], tags: [].}

Fetches a string at the given column position. traced version. In case of null option(none) is returned the NullValue is validated

proc fetchString(ps: ResultSet; cpos: int): string {...}{.inline, raises: [], tags: [].}

fast fetch a string at the given column position. traced version.

proc fetchStringUT(ps: ResultSet; cpos: int; out_rc: var RCode): tuple[ stringptr: ptr char, len: int] {...}{.raises: [], tags: [].}

fetches a string at the given column position. untraced version. the pointer is valid till the preparedStatement is moved to the next row. before advancing to the next row ensure that the data is copied into the application domain.

proc fetchBlob(ps: ResultSet; cpos: int; out_rc: var RCode): Option[seq[byte]] {...}{. raises: [], tags: [].}

fetches an blob at the given column position. traced version. The blob is copied immediately into a sequence. If none is returned the errcode could be checked if dbNull wasn´t the result of an error condition

proc fetchBlobUT(ps: ResultSet; cpos: int): tuple[blobptr: pointer, len: int] {...}{.inline, raises: [], tags: [].}

Fetches a blob at the given column position. untraced version. The pointer is valid till the preparedStatement is advanced to the next row with step() the content needs to be copied into the application domain.

proc fetchRows[T](ps: ResultSet; out_rows: var openArray[T]; fetchCols: PopulateCb[T]; out_msg: var RCode; rowOffset: int = 0): int

Fetches multiple rows out of the PreparedStatement. How much rows are read is constrainted by the length of the given out_rows sequence. The sequence must be preinitialized with the appropriate instances. The integer returned indicates the number of rows iterated. rowOffset is optional and should not exceed the out_rows container length

proc fetchColumnCount(rs: var ResultSet): int {...}{.inline, raises: [], tags: [].}

retrieves the number of columns of the ResultSet

proc fetchColumnNames(ps: ResultSet; out_colnames: var seq[string]) {...}{.raises: [], tags: [].}

Fetches the column-names of the active prepared statement into the second parameter. The sequence is initialized internally. The columnnames of the result could only gathered if the preparedStatement is active.

If the preparedStatement already returned SQLITE_DONE, the result will be unspecified

proc cleanupAllocatedResources(db: DbConn; resultcode: var RCode) {...}{.raises: [], tags: [].}

cleanup. finalizes all open prepared Statements for the specified connection.

proc exec(db: DbConn; query: SqlQuery; rc: var RCode) {...}{.raises: [], tags: [].}

Suitable for execution of commands which do not return any results or executed only once. If an error happens the backend returns with an error message. in this case the errStr field of the returncode is set.

proc exec(db: DbConn; query: SqlQuery; out_rc: var RCode; out_results: var seq[seq[string]]; buffer: pointer; buffsize: int) {...}{. raises: [], tags: [].}

suitable for execution of commands which do not return any or much results.

if an error happens the backends message is filled into out_rc.errstr. in case of success out_rc.errstr is empty.

if out_results is initialized, the sequence will be filled with the returned rows till the given shared buffer's limit is reached.

always initialize outer and nested seqs. first row is always filled with the returned columnNames;

to avoid reading huge sets, preset the sequence with the number of expected/needed elements. the internal header occupies 4 integer; each returned string occupies an extra 32bit-word for the lengthfield.

proc exec(db: DbConn; query: SqlQuery; rc: var RCode; out_results: var seq[seq[string]]; buffsize: int = 1024) {...}{.raises: [Exception], tags: [].}

convenience proc. allocates shared memory for given buffsize

proc queryOneRow(db: DbConn; query: SqlQuery; rc: var RCode; numCols: int): seq[string] {...}{. raises: [Exception], tags: [].}

convenience proc which returns only the first row of the query without the column names. if numCols is smaller than the returned number of columns (ResultSet) the row is truncated

proc incarnateDbFromTemplate(targetDb: DbConn; absPathWithFilename: string; out_rc: RCode) {...}{.raises: [], tags: [DbEffect].}

vendor specific helper to incarnate a targetDb from a source database file (template). the source database is opened in readonly mode and copied into the targetDb connection. Databasename 'main' is assumed. keep in mind that the targetDbs connection is not allowed to have any pending transactions open. out_rc is the returncode.

proc `$`(p: DbTableColumnMetaData): string {...}{.raises: [], tags: [].}

debugging purpose

proc tableColumnMetadata(dbConn: DbConn; tableName: string; columnName: string; out_rc: var RCode; dbName: string = "main"): SQLite3ColumnMetaData {...}{. raises: [], tags: [].}

returns metadata for a specified column and table. the dbName could be present or nil

proc columnNamesForTable(dbConn: DbConn; tableName: string; rc: var RCode): seq[string] {...}{. raises: [], tags: [].}

proc allUserTableNames(dbConn: DbConn; rc: var RCode; dbName: string = "main"): seq[string] {...}{. raises: [], tags: [].}

helper to retrieve all db-object table types from specified db

proc allUserIndexNames(dbConn: DbConn; rc: var RCode; dbName: string = "main"): seq[string] {...}{. raises: [], tags: [].}

fetches all dbobjects of type : index

proc allUserViewNames(dbConn: DbConn; rc: var RCode; dbName: string = "main"): seq[string] {...}{. raises: [], tags: [].}

fetches all dbobjects of type : view fetches all dbobjects of type : index

proc metadataForTable(dbConn: DbConn; tableName: string; rc: var RCode; dbName: string = "main"): seq[SQLite3ColumnMetaData] {...}{. raises: [], tags: [].}

returns the metadata for specified table

Iterators

iterator allOpenPreparedStatements(db: DbConn): PreparedStatement {...}{.raises: [], tags: [].}: iterates over all open (non-finalized) preparedStatements for the specified connection
iterator validateSql(dbconn: DbConn; queries: openArray[SqlQuery]; rc: var RCode): string {...}{. raises: [], tags: [].}: experimental iterator which validates each sql. each query is wrapped into a transaction before execution and a rollback is performed after. each error is yielded. the intention is to detect syntax errors. keep in mind that at least one vendor does not support transactional ddl and performs a forced commit if a ddl is executed. resultcode SQLITE_CONSTRAINT: not null constraint is suppressed

Templates

template evalHasError(rc: var RCode): bool

template which checks for error return codes. for instance SQLITE_BUSY (obj-lock) will be handled as an error

template forceRollback(rc: var RCode)

usable within a transaction to force a non technical rollback without throwing an exception

template hasRows(ec: var RCode): bool

true if the backend returned SQLITE_ROW

template evalBindError(vendorcode: int): bool

returns true if a bind error happened

template fetchErrcode(dbconn: DbConn): int

fetches the errorcode according to the vendor spec only needed if the raw-type bind/fetching procs are used

template fetchErrcode(ps: PreparedStatement): int

fetches the errorcode according to the vendors spec only needed if the raw bind/fetching procs are used

template prepareNextRow(rs: ResultSet; r: var RCode)

raw API: invokes step() of the query-interface. the vendorcode is set.

template resetPreparedStatement(ps: PreparedStatement; out_rc: var RCode)

reset the preparedStatement ready to re-execute. needed to reset the prepared statement for re-execute later on.

template withTransaction(dbconn: DbConn; rc: var RCode; body: untyped)

used to encapsulate the operation within a transaction. rollback is performed by a technical fault condition, if an exception is thrown or if the RCode container was tagged with forceRollback()

this template is not nestable if the vendor does not support it

template withFinalisePreparedStatement(ps: PreparedStatement; rcode: var RCode; body: untyped)

finalizes the preparedStatement after leaving the block. it can't be used later on.

template rawBind(ps: PreparedStatement; out_rc: var RCode; body: untyped): untyped {...}{. dirty.}

template for raw-binding used within a custom loop. null type fetching must be handled manually. suitable for insert/update/upsert statements The variable "baseIdx" inside template's scope starts with the vendors defined leftmost index. It needs to be advanced to the next parameter if an iteration is inside the template´s scope (multiple parameters present) (see speed_comparison.nim for an example)

template rawFetch(ps: ResultSet; out_rc: var RCode; body: untyped) {...}{.dirty.}

template for raw fetching results used within a custom loop. always call 'getResultSet' before using this template. the variable colIdx inside the template's scope starts with the vendors defined leftmost index.

template withRollbackTransaction(dbconn: DbConn; rc: var RCode; body: untyped)

performs always a rollback, regardless if error or not. suitable for testing purposes.

template withNestedTransaction(dbconn: DbConn; rc: var RCode; transactionName: string; body: untyped)

use this template if you need nestable statement encapsulation. transactionName's should be unique within the transaction-tree. further reading: