#
#
#            Nimrod's Runtime Library
#        (c) Copyright 2010 Andreas Rumpf
#
#    See the file "copying.txt", included in this
#    distribution, for details about the copyright.
#

## A higher level `SQLite`:idx: database wrapper. This interface 
## is implemented for other databases too.

import strutils, sqlite3

type
  TDbConn* = PSqlite3  ## encapsulates a database connection
  TRow* = seq[string]  ## a row of a dataset
  EDb* = object of EIO ## exception that is raised if a database error occurs
  
  TSqlQuery* = distinct string ## an SQL query string
  
proc sql*(query: string): TSqlQuery {.noSideEffect, inline.} =  
  ## constructs a TSqlQuery from the string `query`. This is supposed to be 
  ## used as a raw-string-literal modifier:
  ## ``sql"update user set counter = counter + 1"``
  ##
  ## If assertions are turned off, it does nothing. If assertions are turned 
  ## on, later versions will check the string for valid syntax.
  result = TSqlQuery(query)
 
proc dbError(db: TDbConn) {.noreturn.} = 
  ## raises an EDb exception.
  var e: ref EDb
  new(e)
  e.msg = $sqlite3.errmsg(db)
  raise e

proc dbError*(msg: string) {.noreturn.} = 
  ## raises an EDb exception with message `msg`.
  var e: ref EDb
  new(e)
  e.msg = msg
  raise e

proc dbQuote(s: string): string =
  result = "'"
  for c in items(s):
    if c == '\'': add(result, "''")
    else: add(result, c)
  add(result, '\'')

proc dbFormat(formatstr: TSqlQuery, args: openarray[string]): string =
  result = ""
  var a = 0
  for c in items(string(formatstr)):
    if c == '?':
      add(result, dbQuote(args[a]))
      inc(a)
    else: 
      add(result, c)
  
proc TryExec*(db: TDbConn, query: TSqlQuery, 
              args: openarray[string]): bool =
  ## tries to execute the query and returns true if successful, false otherwise.
  var q = dbFormat(query, args)
  var stmt: sqlite3.PStmt
  if prepare_v2(db, q, q.len, stmt, nil) == SQLITE_OK:
    if step(stmt) == SQLITE_DONE:
      result = finalize(stmt) == SQLITE_OK

proc Exec*(db: TDbConn, query: TSqlQuery, args: openarray[string]) =
  ## executes the query and raises EDB if not successful.
  if not TryExec(db, query, args): dbError(db)
  
proc newRow(L: int): TRow =
  newSeq(result, L)
  for i in 0..L-1: result[i] = ""
  
proc setupQuery(db: TDbConn, query: TSqlQuery, 
                args: openarray[string]): PStmt = 
  var q = dbFormat(query, args)
  if prepare_v2(db, q, q.len, result, nil) != SQLITE_OK: dbError(db)
  
proc setRow(stmt: PStmt, r: var TRow, cols: int) =
  for col in 0..cols-1:
    setLen(r[col], column_bytes(stmt, col)) # set capacity
    setLen(r[col], 0)
    add(r[col], column_text(stmt, col))
  
iterator FastRows*(db: TDbConn, query: TSqlQuery,
                   args: openarray[string]): TRow =
  ## executes the query and iterates over the result dataset. This is very 
  ## fast, but potenially dangerous: If the for-loop-body executes another
  ## query, the results can be undefined. For Sqlite it is safe though.
  var stmt = setupQuery(db, query, args)
  var L = int(columnCount(stmt))
  var result = newRow(L)
  while step(stmt) == SQLITE_ROW: 
    setRow(stmt, result, L)
    yield result
  if finalize(stmt) != SQLITE_OK: dbError(db)

proc GetAllRows*(db: TDbConn, query: TSqlQuery, 
                 args: openarray[string]): seq[TRow] =
  ## executes the query and returns the whole result dataset.
  result = @[]
  for r in FastRows(db, query, args):
    result.add(r)

iterator Rows*(db: TDbConn, query: TSqlQuery, 
               args: openarray[string]): TRow =
  ## same as `FastRows`, but slower and safe.
  for r in FastRows(db, query, args): yield r

proc GetValue*(db: TDbConn, query: TSqlQuery, 
               args: openarray[string]): string = 
  ## executes the query and returns the result dataset's the first column 
  ## of the first row. Returns "" if the dataset contains no rows.
  var stmt = setupQuery(db, query, args)
  if step(stmt) == SQLITE_ROW: 
    result = newString(column_bytes(stmt, 0))
    setLen(result, 0)
    add(result, column_text(stmt, 0))
    if finalize(stmt) != SQLITE_OK: dbError(db)
  else:
    result = ""
  
proc TryInsertID*(db: TDbConn, query: TSqlQuery, 
                  args: openarray[string]): int64 =
  ## executes the query (typically "INSERT") and returns the 
  ## generated ID for the row or -1 in case of an error. 
  if tryExec(db, query, args): 
    result = last_insert_rowid(db)
  else:
    result = -1

proc InsertID*(db: TDbConn, query: TSqlQuery, 
               args: openArray[string]): int64 = 
  ## executes the query (typically "INSERT") and returns the 
  ## generated ID for the row. For Postgre this adds
  ## ``RETURNING id`` to the query, so it only works if your primary key is
  ## named ``id``. 
  result = TryInsertID(db, query, args)
  if result < 0: dbError(db)
  
proc ExecAffectedRows*(db: TDbConn, query: TSqlQuery, 
                       args: openArray[string]): int64 = 
  ## executes the query (typically "UPDATE") and returns the
  ## number of affected rows.
  Exec(db, query, args)
  result = changes(db)

proc Close*(db: TDbConn) = 
  ## closes the database connection.
  if sqlite3.close(db) != SQLITE_OK:
    dbError(db)
    
proc Open*(connection, user, password, database: string): TDbConn =
  ## opens a database connection. Raises `EDb` if the connection could not
  ## be established. Only the ``connection`` parameter is used for ``sqlite``.
  var db: TDbConn
  if sqlite3.open(connection, db) == SQLITE_OK:
    return db
  else:
    dbError(db)
   
when isMainModule:
  var db = open("db.sql", "", "", "")
  Exec(db, sql"create table tbl1(one varchar(10), two smallint)", [])
  exec(db, sql"insert into tbl1 values('hello!',10)", [])
  exec(db, sql"insert into tbl1 values('goodbye', 20)", [])
  #db.query("create table tbl1(one varchar(10), two smallint)")
  #db.query("insert into tbl1 values('hello!',10)")
  #db.query("insert into tbl1 values('goodbye', 20)")
  for r in db.rows(sql"select * from tbl1", []):
    echo(r[0], r[1])
  
  db_sqlite.close(db)