Some things I ran into so far using db_sqlite. Code below as is runs fine but somewhat unexpectedly.
While putting it together I wondered why the exec macro for prepared statements needed to know the connection again. It (the dB connection handle https://www.sqlite.org/c3ref/db_handle.html) is in the statement after all. So I tried and opened a second connection and used the statement with that. That works.
But I wonder, should it?
I always was under the impression that a SQLite prepared statement is a per connection thing.
import db_sqlite, oids, times, os, strformat
import sqlite3 #else Error: undeclared field: 'PStmt'
var
init: bool
k, v: string
let dBFile = "test.db3"
if not fileExists(dBFile):
init = true
let dB1 = open(dBFile, "", "", "")
dB1.exec(sql"""PRAGMA journal_mode=wal;""")
let dB2 = open(dBFile, "", "", "")
dB2.exec(sql"""PRAGMA journal_mode=wal;""")
if init:
dB1.exec(sql"""
CREATE TABLE IF NOT EXISTS kv(
k TEXT PRIMARY KEY,
v TEXT NOT NULL
)
WITHOUT ROWID;
"""
)
var psPut = dB1.prepare("INSERT INTO kv(k, v) VALUES (?, ?);")
proc put*(dB:DbConn, k: string, v: string) =
dB.exec(psPut, k, v)
k = $genOid()
v = now().utc.format("ddd, d MMM yyyy HH:mm:ss:fff")
dB1.put(k, v)
k = $genOid()
v = now().utc.format("ddd, d MMM yyyy HH:mm:ss:fff")
dB2.put(k, v)
finalize(psPut) #otherwise the dB does not 'close'. Wal file stay alive.
#close(dB) #Error: ambiguous call; both db_sqlite.close(db: DbConn) [declared in ....\impure\db_sqlite.nim(679, 6)] and sqlite3.close(para1: PSqlite3)
db_sqlite.close(dB1)
db_sqlite.close(dB2)
#or
#discard sqlite3.close(dB)
So I think it could be a design-bug.
maybe use https://sqlite.org/c3ref/db_handle.html in the exec template instead of the manually given db: DbConn but I could not find sqlite3_db_handle in the sqlite3 lib.
just wrap it this way:
proc sqlite3_db_handle*(para1: Pstmt): PSqlite3 {.cdecl,
dynlib: Lib, importc: "sqlite3_db_handle".}
That works fine. To sqlite3.nim I added the:
proc sqlite3_db_handle*(para1: Pstmt): PSqlite3 {.cdecl,
dynlib: Lib, importc: "sqlite3_db_handle".}
To db_sqlite.nim i added:
template exec*(
stmtName: SqlPrepared,
args: varargs[typed]
): untyped =
let db = sqlite3_db_handle(stmtName.PStmt)
when untypedLen(args) > 0:
if reset(stmtName.PStmt) != SQLITE_OK:
dbError(db)
if clear_bindings(stmtName.PStmt) != SQLITE_OK:
dbError(db)
stmtName.bindParams(args)
if not tryExec(db, stmtName): dbError(db)
var psPut = dB1.prepare("INSERT INTO kv(k, v) VALUES (?, ?);")
psPut.bindParams(k, v)
k = $genOid()
v = now().utc.format("ddd, d MMM yyyy HH:mm:ss:ffffff")
psPut.exec(k, v)
finalize(psPut)
db_sqlite.close(dB1)
Does this help? https://nimble.directory/search?query=sqlite
I seem to remember a dev mentioning modifying sqlite to add convenient types. Maybe they handled your problem?
though if you handled it already, then that's fine too. Perhaps add an issue on github with this forum url.
https://nimble.directory/search?query=sqlite
I seem to remember a dev mentioning modifying sqlite to add convenient types. Maybe they handled your problem?
[...]Perhaps add an issue on github with this forum url.
Had made an issue already.
For my current project the std lib suffices as it needs strings only. But,
Looking at all the other libs though, most of them are promoted as 'thin', where I think that Nim as well "the most used" lib both deserve a wrapper that covers everything and where appropriate with a fair amount sugar (as a standard library). FatSQLite!
Cheers (without going into ORM territory)
Nim 1.6 will have the feature to import all symbols of a library including private ones, so you could do:
import std/db_sqlite {.all.}
import std/sqlite3 {.all.}
export db_sqlite
proc sqlite3_db_handle*(para1: Pstmt): PSqlite3 {.cdecl,
dynlib: Lib, importc: "sqlite3_db_handle".}
template exec*(
stmtName: SqlPrepared,
args: varargs[typed]
): untyped =
let db = sqlite3_db_handle(stmtName.PStmt)
when untypedLen(args) > 0:
if reset(stmtName.PStmt) != SQLITE_OK:
dbError(db)
if clear_bindings(stmtName.PStmt) != SQLITE_OK:
dbError(db)
stmtName.bindParams(args)
if not tryExec(db, stmtName): dbError(db)
And then import this file instead of db_sqlite
so ideally you'd want to PR that change to the Nim stdlib :)
just had a look at that. It would result in a complete change of API for using prepared statements. All procs of type SqlPrepared would be affected.
Looking at all the other libs though, most of them are promoted as 'thin', where I think that Nim as well "the most used" lib both deserve a wrapper that covers everything and where appropriate with a fair amount sugar (as a standard library). FatSQLite!
That should done as a separate Nimble package. Otherwise the improvements to the SQLite wrapper are tied to Nim's release cycle and deprecation policies. That's useful for some modules, but others (db_*.nim) didn't age all that well.
That's useful for some modules, but others ... didn't age all that well.
as a comment on age... https://www.sqlite.org/draft/stricttables.html
Regarding the Nim release cycle, Nimble/Batteries for wrapped libs and experimental?
just thoughts, not for discussion in this thread.