gulpf / tiny_sqlite Goto Github PK

I noticed in the tiny_sqlite.nim source there is a comment that SQLite doesn't support nested transactions. Sort of true, but it does support them with nested savepoints. I have not personally used savepoints, though they would come in handy for HashBackup (I'm the author) to start a savepoint at the beginning of a new file and either commit the savepoint or roll it back depending on whether the file actually was saved. Right now is kind of messy with a lot of try/except/finally code that has to make sure the db state is correct. With savepoints, I could let an exception bubble up further and rollback that one file's savepoint.

Feel free to close this - just letting you know about savepoints.

proc fromDbValue*(value: DbValue, T: typedesc[Ordinal]): T =
    # Convert a DbValue to an ordinal.
    value.intval.T

should that not be

[...]
    value.intVal.T

with capital V

Right now query proc use string type. I think it would be better (arguable, safer) to use the type SqlQuery from https://nim-lang.org/docs/db_common.html.

Hi, i'm trying to compile the test program:

import tiny_sqlite, std / options

let db = openDatabase("aaaa.db")
db.execScript("""
CREATE TABLE Person(
    name TEXT,
    age INTEGER
);

INSERT INTO
    Person(name, age)
VALUES
    ("John Doe", 47);
""")

db.exec("INSERT INTO Person VALUES(?, ?)", "Jane Doe", nil)

for row in db.iterate("SELECT name, age FROM Person"):
    let (name, age) = row.unpack((string, Option[int]))
    echo name, " ", age

And im getting this error:

piotr@Intel-NUC:~/nim$ nim c tsqlite.nim
Hint: used config file '/snap/nim-lang/1111/config/nim.cfg' [Conf]
Hint: used config file '/snap/nim-lang/1111/config/config.nims' [Conf]
...............
/home/piotr/nim/tsqlite.nim(18, 14) Error: attempting to call undeclared routine: 'iterate'

With last loop commented out it compiles and runs.

Nim version 1.4.2 from snap on Ubuntu.

tiny_sqlite/tests/tests.nim(43, 16) Error: undeclared identifier: 'AssertionDefect'

As a "fix", I propose just changing requires "nim >= 1.0.0" in the nimble file.

I haven't tried it yet, but this looks pretty awesome as a substitute for Python's sqlite3 module. I posted some comments about the db_sqlite module here: nim-lang/Nim#13559

A great addition to this would be a prepared stmt cache like Python's sqlite3 has. I think it caches something like the last 50 prepared statements, though you can change that size (I think - never messed with it). A simple way is to hash the string, check a Nim table to see if it's already been prepared, and if so, use the bind calls to associate new parameters with the already-prepared statement.

In my tests (on that Nim GH issue), Python was about 30% faster than Nim, I'm guessing because it avoided recompiling statements on every exec.

When I run

db.exec("PRAGMA foreign_keys;")

A row with just 0 is returned

When I run

db.exec("PRAGMA foreign_keys = ON;")
db.exec("PRAGMA foreign_keys;")

A row with just 1 is returned, but I am still able to perform insertions which should be raising foreign key constraint errors. The cascade deletes I have setup also are not executed when I delete a referenced row.

Feature request: a proc exec() that does not auto-convert toDbValue
Sometimes it can be handy to do the toDbValue conversion by hand.

dbWriteStatement is of type Table[string, SqlStatement]

if dbWriteStatement.hasKey(topic):
  if datatype[topic] == "float":
    let data = parseFloat(payload).float64
    dbWriteStatement[topic].exec(topic, data)
  elif datatype[topic] == "integer":
    let data = parseInt(payload).int64
    dbWriteStatement[topic].exec(topic, data)
  elif datatype[topic] == "string":
    let data = payload 
    dbWriteStatement[topic].exec(topic, data)

versus:

if dbWriteStatement.hasKey(topic):
  if datatype[topic] == "float":
    let data = toDbValue(parseFloat(payload).float64)
  elif datatype[topic] == "integer":
    let data = toDbValue(parseInt(payload).int64)
  elif datatype[topic] == "string":
    let data = toDbValue(payload )
  dbWriteStatement[topic].exec(topic, data)

https://www.sqlite.org/c3ref/clear_bindings.html

To read a single row with tiny_sqlite, I used:

for row in db.rows("select v from t where k = 13"):
  let (v,) = row.unpack((int,))
  echo v

It seems a little odd to me because the for + db.rows implies processing multiple rows. Maybe it could be made clearer by adding a break to the end.

A simpler, and I think clearer alternative would be something like:

  let (v,) = db.row("select v from t where k = 13").unpack((int,))

I added this to my copy of tiny_sqlite and it works fine:

proc row*(db: DbConn, sql: string,
          params: varargs[DbValue, toDbValue]): seq[DbValue] =
    ## Executes ``sql`` and returns 1 row.
    for row in db.rows(sql, params):
        return row

Here's the original test program using db.rows:

import os
import tiny_sqlite

const
  dbname = "bttest4.nim.db"

let nrows = 1_000_000
#let nrows = 100
os.removeFile(dbname)

let db = openDatabase(dbname)

db.exec("""create table t (
  k    integer primary key,
  v    integer)""")

db.transaction:
  for i in 0..<nrows:
    db.exec("insert into t values (?,?)", i, i)

db.transaction:
  for i in 0..<3:
    for j in 0..<nrows:
      for row in db.rows("select v from t where k = ?", j):
        let (v,) = row.unpack((int,))
        db.exec("update t set v = ? where k = ?", v+1, j)

for row in db.rows("select v from t where k = 13"):
  let (v,) = row.unpack((int,))
  echo v

db.close()

Here's a snippet from the new version, to get a visual of the difference. It's only 2 lines shorter but looks clearer to me:

db.transaction:
  for i in 0..<3:
    for j in 0..<nrows:
      let (v,) = db.row("select v from t where k = ?", j).unpack((int,))
      db.exec("update t set v = ? where k = ?", v+1, j)

let (v,) = db.row("select v from t where k = 13").unpack((int,))
echo v

Mistakenly switching db.row and db.rows could be a source of bugs, but db.1row isn't legal, and db.aRow, db.oneRow, and db.singleRow seem a little much.

Another option could be something like db.unpackRow that accepts a query string:

let (v,) = db.unpackRow("select v from t where k = 13", (int,))
echo v

That's not as flexible because you can't get the row itself. Nim's db_sqlite has GetValue to read the first value from a row, but that won't work for a multi-value row, like selecting count(*), avg(size).

It seems that a read only connection is not closed properly when using a read only connection. The wal files still exist after closing.

import tiny_sqlite

const dbfile = "test.db3"

let conn = openDatabase(dbfile)
conn.exec("PRAGMA journal_mode=WAL;")

conn.exec("""
  create table if not exists test(
    a text
  );
  """
)

conn.exec("""
  insert into test(a)
  values("aA");
  """
)
conn.close()


let readconn = openDatabase(dbfile, dbRead)

let a = readconn.all("""select * from test""")

readconn.close()