db: InstantRow and instantRows

It is drop-in replacement for Row and fastRows, however instantRows returns a
handle, not seq[string], so no Nim string is created until [] operator is
called on the given handle. Also there is a len() proc returning number of
columns in the handle.

In some situations, when we iterate through many rows, but later we just read
few columns this solution will be quicker than converting all column to Nim
seq[string] on each iteration.

3 files changed, 71 insertions, 1 deletions

diff --git a/lib/impure/db_mysql.nim b/lib/impure/db_mysql.nim
index 619c2a656..7c2901efd 100644
--- a/lib/impure/db_mysql.nim
+++ b/lib/impure/db_mysql.nim
@@ -16,6 +16,9 @@ type
   DbConn* = PMySQL    ## encapsulates a database connection
   Row* = seq[string]   ## a row of a dataset. NULL database values will be
                        ## transformed always to the empty string.
+  InstantRow* = tuple[row: cstringArray, len: int]  ## a handle that can be
+                                                    ## used to get a row's
+                                                    ## column text on demand
   EDb* = object of IOError ## exception that is raised if a database error occurs
 
   SqlQuery* = distinct string ## an SQL query string
@@ -127,6 +130,30 @@ iterator fastRows*(db: DbConn, query: SqlQuery,
       yield result
     properFreeResult(sqlres, row)
 
+iterator instantRows*(db: DbConn, query: SqlQuery,
+                      args: varargs[string, `$`]): InstantRow
+                      {.tags: [FReadDb].} =
+  ## same as fastRows but returns a handle that can be used to get column text
+  ## on demand using []. Returned handle is valid only within interator body.
+  rawExec(db, query, args)
+  var sqlres = mysql.useResult(db)
+  if sqlres != nil:
+    let L = int(mysql.numFields(sqlres))
+    var row: cstringArray
+    while true:
+      row = mysql.fetchRow(sqlres)
+      if row == nil: break
+      yield (row: row, len: L)
+    properFreeResult(sqlres, row)
+
+proc `[]`*(row: InstantRow, col: int): string {.inline.} =
+  ## returns text for given column of the row
+  $row.row[col]
+
+proc len*(row: InstantRow): int {.inline.} =
+  ## returns number of columns in the row
+  row.len
+
 proc getRow*(db: DbConn, query: SqlQuery,
              args: varargs[string, `$`]): Row {.tags: [FReadDB].} =
   ## retrieves a single row. If the query doesn't return any rows, this proc
diff --git a/lib/impure/db_postgres.nim b/lib/impure/db_postgres.nim
index 774cb1510..c88e660f4 100644
--- a/lib/impure/db_postgres.nim
+++ b/lib/impure/db_postgres.nim
@@ -16,6 +16,9 @@ type
   DbConn* = PPGconn   ## encapsulates a database connection
   Row* = seq[string]  ## a row of a dataset. NULL database values will be
                        ## transformed always to the empty string.
+  InstantRow* = tuple[res: PPGresult, line: int32]  ## a handle that can be
+                                                    ## used to get a row's
+                                                    ## column text on demand
   EDb* = object of IOError ## exception that is raised if a database error occurs
   
   SqlQuery* = distinct string ## an SQL query string
@@ -159,6 +162,24 @@ iterator fastRows*(db: DbConn, stmtName: SqlPrepared,
     yield result
   pqClear(res)
 
+iterator instantRows*(db: DbConn, query: SqlQuery,
+                      args: varargs[string, `$`]): InstantRow
+                      {.tags: [FReadDb].} =
+  ## same as fastRows but returns a handle that can be used to get column text
+  ## on demand using []. Returned handle is valid only within interator body.
+  var res = setupQuery(db, query, args)
+  for i in 0..pqNtuples(res)-1:
+    yield (res: res, line: i)
+  pqClear(res)
+
+proc `[]`*(row: InstantRow, col: int32): string {.inline.} =
+  ## returns text for given column of the row
+  $pqgetvalue(row.res, row.line, col)
+
+proc len*(row: InstantRow): int32 {.inline.} =
+  ## returns number of columns in the row
+  pqNfields(row.res)
+
 proc getRow*(db: DbConn, query: SqlQuery,
              args: varargs[string, `$`]): Row {.tags: [FReadDB].} =
   ## retrieves a single row. If the query doesn't return any rows, this proc
diff --git a/lib/impure/db_sqlite.nim b/lib/impure/db_sqlite.nim
index 47e7c1900..1a037becc 100644
--- a/lib/impure/db_sqlite.nim
+++ b/lib/impure/db_sqlite.nim
@@ -16,6 +16,8 @@ type
   DbConn* = PSqlite3  ## encapsulates a database connection
   Row* = seq[string]  ## a row of a dataset. NULL database values will be
                        ## transformed always to the empty string.
+  InstantRow* = Pstmt  ## a handle that can be used to get a row's column
+                       ## text on demand
   EDb* = object of IOError ## exception that is raised if a database error occurs
   
   SqlQuery* = distinct string ## an SQL query string
@@ -109,6 +111,24 @@ iterator fastRows*(db: DbConn, query: SqlQuery,
     yield result
   if finalize(stmt) != SQLITE_OK: dbError(db)
 
+iterator instantRows*(db: DbConn, query: SqlQuery,
+                      args: varargs[string, `$`]): InstantRow
+                      {.tags: [FReadDb].} =
+  ## same as fastRows but returns a handle that can be used to get column text
+  ## on demand using []. Returned handle is valid only within interator body.
+  var stmt = setupQuery(db, query, args)
+  while step(stmt) == SQLITE_ROW:
+    yield stmt
+  if finalize(stmt) != SQLITE_OK: dbError(db)
+
+proc `[]`*(row: InstantRow, col: int32): string {.inline.} =
+  ## returns text for given column of the row
+  $column_text(row, col)
+
+proc len*(row: InstantRow): int32 {.inline.} =
+  ## returns number of columns in the row
+  column_count(row)
+
 proc getRow*(db: DbConn, query: SqlQuery,
              args: varargs[string, `$`]): Row {.tags: [FReadDb].} =
   ## retrieves a single row. If the query doesn't return any rows, this proc
@@ -216,5 +236,7 @@ when not defined(testing) and isMainModule:
   #db.query("insert into tbl1 values('goodbye', 20)")
   for r in db.rows(sql"select * from tbl1", []):
     echo(r[0], r[1])
-  
+  for r in db.instantRows(sql"select * from tbl1", []):
+    echo(r[0], r[1])
+
   db_sqlite.close(db)