#
#
# Nim's Runtime Library
# (c) Copyright 2015 Andreas Rumpf
#
# See the file "copying.txt", included in this
# distribution, for details about the copyright.
#
## A higher level `PostgreSQL`:idx: database wrapper. This interface
## is implemented for other databases also.
##
## See also: `db_odbc <db_odbc.html>`_, `db_sqlite <db_sqlite.html>`_,
## `db_mysql <db_mysql.html>`_.
##
## Parameter substitution
## ======================
##
## All ``db_*`` modules support the same form of parameter substitution.
## That is, using the ``?`` (question mark) to signify the place where a
## value should be placed. For example:
##
## .. code-block:: Nim
## sql"INSERT INTO myTable (colA, colB, colC) VALUES (?, ?, ?)"
##
## **Note**: There are two approaches to parameter substitution support by
## this module.
##
## 1. ``SqlQuery`` using ``?, ?, ?, ...`` (same as all the ``db_*`` modules)
##
## 2. ``SqlPrepared`` using ``$1, $2, $3, ...``
##
## .. code-block:: Nim
## prepare(db, "myExampleInsert",
## sql"""INSERT INTO myTable
## (colA, colB, colC)
## VALUES ($1, $2, $3)""",
## 3)
##
## Examples
## ========
##
## Opening a connection to a database
## ----------------------------------
##
## .. code-block:: Nim
## import db_postgres
## let db = open("localhost", "user", "password", "dbname")
## db.close()
##
## Creating a table
## ----------------
##
## .. code-block:: Nim
## db.exec(sql"DROP TABLE IF EXISTS myTable")
## db.exec(sql("""CREATE TABLE myTable (
## id integer,
## name varchar(50) not null)"""))
##
## Inserting data
## --------------
##
## .. code-block:: Nim
## db.exec(sql"INSERT INTO myTable (id, name) VALUES (0, ?)",
## "Dominik")
import strutils, postgres
import db_common
export db_common
type
DbConn* = PPGconn ## encapsulates a database connection
Row* = seq[string] ## a row of a dataset. NULL database values will be
## converted to nil.
InstantRow* = object ## a handle that can be
res: PPGresult ## used to get a row's
line: int ## column text on demand
SqlPrepared* = distinct string ## a identifier for the prepared queries
proc dbError*(db: DbConn) {.noreturn.} =
## raises a DbError exception.
var e: ref DbError
new(e)
e.msg = $pqErrorMessage(db)
raise e
proc dbQuote*(s: string): string =
## DB quotes the string.
result = "'"
for c in items(s):
if c == '\'': add(result, "''")
else: add(result, c)
add(result, '\'')
proc dbFormat(formatstr: SqlQuery, args: varargs[string]): string =
result = ""
var a = 0
if args.len > 0 and not string(formatstr).contains("?"):
dbError("""parameter substitution expects "?" """)
if args.len == 0:
return string(formatstr)
else:
for c in items(string(formatstr)):
if c == '?':
add(result, dbQuote(args[a]))
inc(a)
else:
add(result, c)
proc tryExec*(db: DbConn, query: SqlQuery,
args: varargs[string, `$`]): bool {.tags: [ReadDbEffect, WriteDbEffect].} =
## tries to execute the query and returns true if successful, false otherwise.
var res = pqexecParams(db, dbFormat(query, args), 0, nil, nil,
nil, nil, 0)
result = pqresultStatus(res) == PGRES_COMMAND_OK
pqclear(res)
proc tryExec*(db: DbConn, stmtName: SqlPrepared,
args: varargs[string, `$`]): bool {.tags: [
ReadDbEffect, WriteDbEffect].} =
## tries to execute the query and returns true if successful, false otherwise.
var arr = allocCStringArray(args)
var res = pqexecPrepared(db, stmtName.string, int32(args.len), arr,
nil, nil, 0)
deallocCStringArray(arr)
result = pqresultStatus(res) == PGRES_COMMAND_OK
pqclear(res)
proc exec*(db: DbConn, query: SqlQuery, args: varargs[string, `$`]) {.
tags: [ReadDbEffect, WriteDbEffect].} =
## executes the query and raises EDB if not successful.
var res = pqexecParams(db, dbFormat(query, args), 0, nil, nil,
nil, nil, 0)
if pqresultStatus(res) != PGRES_COMMAND_OK: dbError(db)
pqclear(res)
proc exec*(db: DbConn, stmtName: SqlPrepared,
args: varargs[string]) {.tags: [ReadDbEffect, WriteDbEffect].} =
var arr = allocCStringArray(args)
var res = pqexecPrepared(db, stmtName.string, int32(args.len), arr,
nil, nil, 0)
deallocCStringArray(arr)
if pqResultStatus(res) != PGRES_COMMAND_OK: dbError(db)
pqclear(res)
proc newRow(L: int): Row =
newSeq(result, L)
for i in 0..L-1: result[i] = ""
proc setupQuery(db: DbConn, query: SqlQuery,
args: varargs[string]): PPGresult =
result = pqexec(db, dbFormat(query, args))
if pqResultStatus(result) != PGRES_TUPLES_OK: dbError(db)
proc setupQuery(db: DbConn, stmtName</