diff options
Diffstat (limited to 'lib/impure')
| -rw-r--r-- | lib/impure/db_mysql.nim | 169 | ||||
| -rw-r--r-- | lib/impure/db_odbc.nim | 463 | ||||
| -rw-r--r-- | lib/impure/db_postgres.nim | 87 | ||||
| -rw-r--r-- | lib/impure/db_sqlite.nim | 111 |
4 files changed, 680 insertions, 150 deletions
diff --git a/lib/impure/db_mysql.nim b/lib/impure/db_mysql.nim index 7f7511264..170fee8b8 100644 --- a/lib/impure/db_mysql.nim +++ b/lib/impure/db_mysql.nim @@ -1,7 +1,7 @@ # # # Nim's Runtime Library -# (c) Copyright 2012 Andreas Rumpf +# (c) Copyright 2015 Andreas Rumpf # # See the file "copying.txt", included in this # distribution, for details about the copyright. @@ -43,45 +43,26 @@ import strutils, mysql +import db_common +export db_common + type - DbConn* = PMySQL ## encapsulates a database connection + 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 - - FDb* = object of IOEffect ## effect that denotes a database operation - FReadDb* = object of FDb ## effect that denotes a read operation - FWriteDb* = object of FDb ## effect that denotes a write operation -{.deprecated: [TRow: Row, TSqlQuery: SqlQuery, TDbConn: DbConn].} - -proc sql*(query: string): SqlQuery {.noSideEffect, inline.} = - ## constructs a SqlQuery 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 = SqlQuery(query) + ## converted to nil. + InstantRow* = object ## a handle that can be used to get a row's + ## column text on demand + row: cstringArray + len: int +{.deprecated: [TRow: Row, TDbConn: DbConn].} -proc dbError(db: DbConn) {.noreturn.} = - ## raises an EDb exception. - var e: ref EDb +proc dbError*(db: DbConn) {.noreturn.} = + ## raises a DbError exception. + var e: ref DbError new(e) e.msg = $mysql.error(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 - when false: proc dbQueryOpt*(db: DbConn, query: string, args: varargs[string, `$`]) = var stmt = mysql_stmt_init(db) @@ -114,7 +95,7 @@ proc dbFormat(formatstr: SqlQuery, args: varargs[string]): string = add(result, c) proc tryExec*(db: DbConn, query: SqlQuery, args: varargs[string, `$`]): bool {. - tags: [FReadDB, FWriteDb].} = + tags: [ReadDbEffect, WriteDbEffect].} = ## tries to execute the query and returns true if successful, false otherwise. var q = dbFormat(query, args) return mysql.realQuery(db, q, q.len) == 0'i32 @@ -124,7 +105,7 @@ proc rawExec(db: DbConn, query: SqlQuery, args: varargs[string, `$`]) = if mysql.realQuery(db, q, q.len) != 0'i32: dbError(db) proc exec*(db: DbConn, query: SqlQuery, args: varargs[string, `$`]) {. - tags: [FReadDB, FWriteDb].} = + tags: [ReadDbEffect, WriteDbEffect].} = ## executes the query and raises EDB if not successful. var q = dbFormat(query, args) if mysql.realQuery(db, q, q.len) != 0'i32: dbError(db) @@ -139,7 +120,7 @@ proc properFreeResult(sqlres: mysql.PRES, row: cstringArray) = mysql.freeResult(sqlres) iterator fastRows*(db: DbConn, query: SqlQuery, - args: varargs[string, `$`]): Row {.tags: [FReadDB].} = + args: varargs[string, `$`]): Row {.tags: [ReadDbEffect].} = ## executes the query and iterates over the result dataset. ## ## This is very fast, but potentially dangerous. Use this iterator only @@ -167,31 +148,113 @@ iterator fastRows*(db: DbConn, query: SqlQuery, 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 the interator body. + {.tags: [ReadDbEffect].} = + ## Same as fastRows but returns a handle that can be used to get column text + ## on demand using []. Returned handle is valid only within the iterator 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 InstantRow(row: row, len: L) + properFreeResult(sqlres, row) + +proc setTypeName(t: var DbType; f: PFIELD) = + shallowCopy(t.name, $f.name) + t.maxReprLen = Natural(f.max_length) + if (NOT_NULL_FLAG and f.flags) != 0: t.notNull = true + case f.ftype + of TYPE_DECIMAL: + t.kind = dbDecimal + of TYPE_TINY: + t.kind = dbInt + t.size = 1 + of TYPE_SHORT: + t.kind = dbInt + t.size = 2 + of TYPE_LONG: + t.kind = dbInt + t.size = 4 + of TYPE_FLOAT: + t.kind = dbFloat + t.size = 4 + of TYPE_DOUBLE: + t.kind = dbFloat + t.size = 8 + of TYPE_NULL: + t.kind = dbNull + of TYPE_TIMESTAMP: + t.kind = dbTimestamp + of TYPE_LONGLONG: + t.kind = dbInt + t.size = 8 + of TYPE_INT24: + t.kind = dbInt + t.size = 3 + of TYPE_DATE: + t.kind = dbDate + of TYPE_TIME: + t.kind = dbTime + of TYPE_DATETIME: + t.kind = dbDatetime + of TYPE_YEAR: + t.kind = dbDate + of TYPE_NEWDATE: + t.kind = dbDate + of TYPE_VARCHAR, TYPE_VAR_STRING, TYPE_STRING: + t.kind = dbVarchar + of TYPE_BIT: + t.kind = dbBit + of TYPE_NEWDECIMAL: + t.kind = dbDecimal + of TYPE_ENUM: t.kind = dbEnum + of TYPE_SET: t.kind = dbSet + of TYPE_TINY_BLOB, TYPE_MEDIUM_BLOB, TYPE_LONG_BLOB, + TYPE_BLOB: t.kind = dbBlob + of TYPE_GEOMETRY: + t.kind = dbGeometry + +proc setColumnInfo(columns: var DbColumns; res: PRES; L: int) = + setLen(columns, L) + for i in 0..<L: + let fp = mysql.fetch_field_direct(res, cint(i)) + setTypeName(columns[i].typ, fp) + columns[i].name = $fp.name + columns[i].tableName = $fp.table + columns[i].primaryKey = (fp.flags and PRI_KEY_FLAG) != 0 + #columns[i].foreignKey = there is no such thing in mysql + +iterator instantRows*(db: DbConn; columns: var DbColumns; query: SqlQuery; + args: varargs[string, `$`]): InstantRow = + ## Same as fastRows but returns a handle that can be used to get column text + ## on demand using []. Returned handle is valid only within the iterator body. rawExec(db, query, args) var sqlres = mysql.useResult(db) if sqlres != nil: let L = int(mysql.numFields(sqlres)) + setColumnInfo(columns, sqlres, L) var row: cstringArray while true: row = mysql.fetchRow(sqlres) if row == nil: break - yield (row: row, len: L) + yield InstantRow(row: row, len: L) properFreeResult(sqlres, row) + proc `[]`*(row: InstantRow, col: int): string {.inline.} = - ## returns text for given column of the row + ## Returns text for given column of the row. $row.row[col] proc len*(row: InstantRow): int {.inline.} = - ## returns number of columns in the row + ## 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 + args: varargs[string, `$`]): Row {.tags: [ReadDbEffect].} = + ## Retrieves a single row. If the query doesn't return any rows, this proc ## will return a Row with empty strings for each column. rawExec(db, query, args) var sqlres = mysql.useResult(db) @@ -209,7 +272,7 @@ proc getRow*(db: DbConn, query: SqlQuery, properFreeResult(sqlres, row) proc getAllRows*(db: DbConn, query: SqlQuery, - args: varargs[string, `$`]): seq[Row] {.tags: [FReadDB].} = + args: varargs[string, `$`]): seq[Row] {.tags: [ReadDbEffect].} = ## executes the query and returns the whole result dataset. result = @[] rawExec(db, query, args) @@ -232,19 +295,19 @@ proc getAllRows*(db: DbConn, query: SqlQuery, mysql.freeResult(sqlres) iterator rows*(db: DbConn, query: SqlQuery, - args: varargs[string, `$`]): Row {.tags: [FReadDB].} = + args: varargs[string, `$`]): Row {.tags: [ReadDbEffect].} = ## same as `fastRows`, but slower and safe. for r in items(getAllRows(db, query, args)): yield r proc getValue*(db: DbConn, query: SqlQuery, - args: varargs[string, `$`]): string {.tags: [FReadDB].} = + args: varargs[string, `$`]): string {.tags: [ReadDbEffect].} = ## executes the query and returns the first column of the first row of the ## result dataset. Returns "" if the dataset contains no rows or the database ## value is NULL. result = getRow(db, query, args)[0] proc tryInsertId*(db: DbConn, query: SqlQuery, - args: varargs[string, `$`]): int64 {.tags: [FWriteDb].} = + args: varargs[string, `$`]): int64 {.tags: [WriteDbEffect].} = ## executes the query (typically "INSERT") and returns the ## generated ID for the row or -1 in case of an error. var q = dbFormat(query, args) @@ -254,7 +317,7 @@ proc tryInsertId*(db: DbConn, query: SqlQuery, result = mysql.insertId(db) proc insertId*(db: DbConn, query: SqlQuery, - args: varargs[string, `$`]): int64 {.tags: [FWriteDb].} = + args: varargs[string, `$`]): int64 {.tags: [WriteDbEffect].} = ## executes the query (typically "INSERT") and returns the ## generated ID for the row. result = tryInsertID(db, query, args) @@ -262,18 +325,18 @@ proc insertId*(db: DbConn, query: SqlQuery, proc execAffectedRows*(db: DbConn, query: SqlQuery, args: varargs[string, `$`]): int64 {. - tags: [FReadDB, FWriteDb].} = + tags: [ReadDbEffect, WriteDbEffect].} = ## runs the query (typically "UPDATE") and returns the ## number of affected rows rawExec(db, query, args) result = mysql.affectedRows(db) -proc close*(db: DbConn) {.tags: [FDb].} = +proc close*(db: DbConn) {.tags: [DbEffect].} = ## closes the database connection. if db != nil: mysql.close(db) proc open*(connection, user, password, database: string): DbConn {. - tags: [FDb].} = + tags: [DbEffect].} = ## opens a database connection. Raises `EDb` if the connection could not ## be established. result = mysql.init(nil) @@ -291,7 +354,7 @@ proc open*(connection, user, password, database: string): DbConn {. dbError(errmsg) proc setEncoding*(connection: DbConn, encoding: string): bool {. - tags: [FDb].} = + tags: [DbEffect].} = ## sets the encoding of a database connection, returns true for ## success, false for failure. result = mysql.set_character_set(connection, encoding) == 0 diff --git a/lib/impure/db_odbc.nim b/lib/impure/db_odbc.nim new file mode 100644 index 000000000..6af69d842 --- /dev/null +++ b/lib/impure/db_odbc.nim @@ -0,0 +1,463 @@ +# +# +# Nim's Runtime Library +# (c) Copyright 2015 Nim Contributors +# +# See the file "copying.txt", included in this +# distribution, for details about the copyright. +# + +## A higher level `ODBC` database wrapper. +## +## This is the same interface that is implemented for other databases. +## +## This has NOT yet been (extensively) tested agains ODBC drivers for +## Teradata, Oracle, Sybase, MSSqlvSvr, et. al. databases +## +## Currently all queries are ANSI calls, not Unicode. +## +## Example: +## +## .. code-block:: Nim +## +## import db_odbc, math +## +## let theDb = open("localhost", "nim", "nim", "test") +## +## theDb.exec(sql"Drop table if exists myTestTbl") +## theDb.exec(sql("create table myTestTbl (" & +## " Id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, " & +## " Name VARCHAR(50) NOT NULL, " & +## " i INT(11), " & +## " f DECIMAL(18,10))")) +## +## theDb.exec(sql"START TRANSACTION") +## for i in 1..1000: +## theDb.exec(sql"INSERT INTO myTestTbl (name,i,f) VALUES (?,?,?)", +## "Item#" & $i, i, sqrt(i.float)) +## theDb.exec(sql"COMMIT") +## +## for x in theDb.fastRows(sql"select * from myTestTbl"): +## echo x +## +## let id = theDb.tryInsertId(sql"INSERT INTO myTestTbl (name,i,f) VALUES (?,?,?)", +## "Item#1001", 1001, sqrt(1001.0)) +## echo "Inserted item: ", theDb.getValue(sql"SELECT name FROM myTestTbl WHERE id=?", id) +## +## theDb.close() + + +import strutils, odbcsql + +import db_common +export db_common + +type + OdbcConnTyp = tuple[hDb: SqlHDBC, env: SqlHEnv, stmt: SqlHStmt] + DbConn* = OdbcConnTyp ## encapsulates a database connection + Row* = seq[string] ## a row of a dataset. NULL database values will be + ## converted to nil. + InstantRow* = tuple[row: seq[string], len: int] ## a handle that can be + ## used to get a row's + ## column text on demand + +{.deprecated: [TRow: Row, TSqlQuery: SqlQuery, TDbConn: DbConn].} + +var + buf: array[0..4096, char] + +proc properFreeResult(hType: int, sqlres: var SqlHandle) {. + tags: [WriteDbEffect], raises: [].} = + try: + discard SQLFreeHandle(hType.TSqlSmallInt, sqlres) + sqlres = nil + except: discard + +proc getErrInfo(db: var DbConn): tuple[res: int, ss, ne, msg: string] {. + tags: [ReadDbEffect], raises: [].} = + ## Returns ODBC error information + var + sqlState: array[0..512, char] + nativeErr: array[0..512, char] + errMsg: array[0..512, char] + retSz: TSqlSmallInt = 0 + res: TSqlSmallInt = 0 + try: + sqlState[0] = '\0' + nativeErr[0] = '\0' + errMsg[0] = '\0' + res = SQLErr(db.env, db.hDb, db.stmt, + cast[PSQLCHAR](sqlState.addr), + cast[PSQLCHAR](nativeErr.addr), + cast[PSQLCHAR](errMsg.addr), + 511.TSqlSmallInt, retSz.addr.PSQLSMALLINT) + except: + discard + return (res.int, $sqlState, $nativeErr, $errMsg) + +proc dbError*(db: var DbConn) {. + tags: [ReadDbEffect, WriteDbEffect], raises: [DbError] .} = + ## Raises an `[DbError]` exception with ODBC error information + var + e: ref DbError + ss, ne, msg: string = "" + isAnError = false + res: int = 0 + prevSs = "" + while true: + prevSs = ss + (res, ss, ne, msg) = db.getErrInfo() + if prevSs == ss: + break + # sqlState of 00000 is not an error + elif ss == "00000": + break + elif ss == "01000": + echo "\nWarning: ", ss, " ", msg + continue + else: + isAnError = true + echo "\nError: ", ss, " ", msg + if isAnError: + new(e) + e.msg = "ODBC Error" + if db.stmt != nil: + properFreeResult(SQL_HANDLE_STMT, db.stmt) + properFreeResult(SQL_HANDLE_DBC, db.hDb) + properFreeResult(SQL_HANDLE_ENV, db.env) + raise e + +proc SqlCheck(db: var DbConn, resVal: TSqlSmallInt) {.raises: [DbError]} = + ## Wrapper that checks if ``resVal`` is not SQL_SUCCESS and if so, raises [EDb] + if resVal != SQL_SUCCESS: dbError(db) + +proc SqlGetDBMS(db: var DbConn): string {. + tags: [ReadDbEffect, WriteDbEffect], raises: [] .} = + ## Returns the ODBC SQL_DBMS_NAME string + const + SQL_DBMS_NAME = 17.SqlUSmallInt + var + sz: TSqlSmallInt = 0 + buf[0] = '\0' + try: + db.SqlCheck(SQLGetInfo(db.hDb, SQL_DBMS_NAME, cast[SqlPointer](buf.addr), + 4095.TSqlSmallInt, sz.addr)) + except: discard + return $buf.cstring + +proc dbQuote*(s: string): string {.noSideEffect.} = + ## 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 {. + noSideEffect.} = + ## Replace any ``?`` placeholders with `args`, + ## and quotes the arguments + result = "" + var a = 0 + for c in items(string(formatstr)): + if c == '?': + if args[a] == nil: + add(result, "NULL") + else: + add(result, dbQuote(args[a])) + inc(a) + else: + add(result, c) + +proc prepareFetch(db: var DbConn, query: SqlQuery, + args: varargs[string, `$`]) {. + tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].} = + # Prepare a statement, execute it and fetch the data to the driver + # ready for retrieval of the data + # Used internally by iterators and retrieval procs + # requires calling + # properFreeResult(SQL_HANDLE_STMT, db.stmt) + # when finished + db.SqlCheck(SQLAllocHandle(SQL_HANDLE_STMT, db.hDb, db.stmt)) + var q = dbFormat(query, args) + db.SqlCheck(SQLPrepare(db.stmt, q.PSQLCHAR, q.len.TSqlSmallInt)) + db.SqlCheck(SQLExecute(db.stmt)) + db.SqlCheck(SQLFetch(db.stmt)) + +proc prepareFetchDirect(db: var DbConn, query: SqlQuery, + args: varargs[string, `$`]) {. + tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].} = + # Prepare a statement, execute it and fetch the data to the driver + # ready for retrieval of the data + # Used internally by iterators and retrieval procs + # requires calling + # properFreeResult(SQL_HANDLE_STMT, db.stmt) + # when finished + db.SqlCheck(SQLAllocHandle(SQL_HANDLE_STMT, db.hDb, db.stmt)) + var q = dbFormat(query, args) + db.SqlCheck(SQLExecDirect(db.stmt, q.PSQLCHAR, q.len.TSqlSmallInt)) + db.SqlCheck(SQLFetch(db.stmt)) + +proc tryExec*(db: var DbConn, query: SqlQuery, args: varargs[string, `$`]): bool {. + tags: [ReadDbEffect, WriteDbEffect], raises: [].} = + ## Tries to execute the query and returns true if successful, false otherwise. + var + res:TSqlSmallInt = -1 + try: + db.prepareFetchDirect(query, args) + var + rCnt = -1 + res = SQLRowCount(db.stmt, rCnt) + if res != SQL_SUCCESS: dbError(db) + properFreeResult(SQL_HANDLE_STMT, db.stmt) + except: discard + return res == SQL_SUCCESS + +proc rawExec(db: var DbConn, query: SqlQuery, args: varargs[string, `$`]) {. + tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].} = + db.prepareFetchDirect(query, args) + +proc exec*(db: var DbConn, query: SqlQuery, args: varargs[string, `$`]) {. + tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].} = + ## Executes the query and raises EDB if not successful. + db.prepareFetchDirect(query, args) + properFreeResult(SQL_HANDLE_STMT, db.stmt) + +proc newRow(L: int): Row {.noSideEFfect.} = + newSeq(result, L) + for i in 0..L-1: result[i] = "" + +iterator fastRows*(db: var DbConn, query: SqlQuery, + args: varargs[string, `$`]): Row {. + tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].} = + ## Executes the query and iterates over the result dataset. + ## + ## This is very fast, but potentially dangerous. Use this iterator only + ## if you require **ALL** the rows. + ## + ## Breaking the fastRows() iterator during a loop may cause a driver error + ## for subsequenct queries + ## + ## Rows are retrieved from the server at each iteration. + var + rowRes: Row + sz: TSqlSmallInt = 0 + cCnt: TSqlSmallInt = 0.TSqlSmallInt + rCnt = -1 + + db.prepareFetch(query, args) + db.SqlCheck(SQLNumResultCols(db.stmt, cCnt)) + db.SqlCheck(SQLRowCount(db.stmt, rCnt)) + rowRes = newRow(cCnt) + for rNr in 1..rCnt: + for colId in 1..cCnt: + buf[0] = '\0' + db.SqlCheck(SQLGetData(db.stmt, colId.SqlUSmallInt, SQL_C_CHAR, + cast[cstring](buf.addr), 4095.TSqlSmallInt, sz.addr)) + rowRes[colId-1] = $buf.cstring + db.SqlCheck(SQLFetchScroll(db.stmt, SQL_FETCH_NEXT, 1)) + yield rowRes + properFreeResult(SQL_HANDLE_STMT, db.stmt) + +iterator instantRows*(db: var DbConn, query: SqlQuery, + args: varargs[string, `$`]): InstantRow + {.tags: [ReadDbEffect, WriteDbEffect].} = + ## Same as fastRows but returns a handle that can be used to get column text + ## on demand using []. Returned handle is valid only within the interator body. + var + rowRes: Row + sz: TSqlSmallInt = 0 + cCnt: TSqlSmallInt = 0.TSqlSmallInt + rCnt = -1 + db.prepareFetch(query, args) + db.SqlCheck(SQLNumResultCols(db.stmt, cCnt)) + db.SqlCheck(SQLRowCount(db.stmt, rCnt)) + rowRes = newRow(cCnt) + for rNr in 1..rCnt: + for colId in 1..cCnt: + buf[0] = '\0' + db.SqlCheck(SQLGetData(db.stmt, colId.SqlUSmallInt, SQL_C_CHAR, + cast[cstring](buf.addr), 4095.TSqlSmallInt, sz.addr)) + rowRes[colId-1] = $buf.cstring + db.SqlCheck(SQLFetchScroll(db.stmt, SQL_FETCH_NEXT, 1)) + yield (row: rowRes, len: cCnt.int) + properFreeResult(SQL_HANDLE_STMT, db.stmt) + +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: var DbConn, query: SqlQuery, + args: varargs[string, `$`]): Row {. + tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].} = + ## Retrieves a single row. If the query doesn't return any rows, this proc + ## will return a Row with empty strings for each column. + var + sz: TSqlSmallInt = 0.TSqlSmallInt + cCnt: TSqlSmallInt = 0.TSqlSmallInt + rCnt = -1 + result = @[] + db.prepareFetch(query, args) + db.SqlCheck(SQLNumResultCols(db.stmt, cCnt)) + + db.SqlCheck(SQLRowCount(db.stmt, rCnt)) + for colId in 1..cCnt: + db.SqlCheck(SQLGetData(db.stmt, colId.SqlUSmallInt, SQL_C_CHAR, + cast[cstring](buf.addr), 4095.TSqlSmallInt, sz.addr)) + result.add($buf.cstring) + db.SqlCheck(SQLFetchScroll(db.stmt, SQL_FETCH_NEXT, 1)) + properFreeResult(SQL_HANDLE_STMT, db.stmt) + +proc getAllRows*(db: var DbConn, query: SqlQuery, + args: varargs[string, `$`]): seq[Row] {. + tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].} = + ## Executes the query and returns the whole result dataset. + var + rowRes: Row + sz: TSqlSmallInt = 0 + cCnt: TSqlSmallInt = 0.TSqlSmallInt + rCnt = -1 + db.prepareFetch(query, args) + db.SqlCheck(SQLNumResultCols(db.stmt, cCnt)) + db.SqlCheck(SQLRowCount(db.stmt, rCnt)) + result = @[] + for rNr in 1..rCnt: + rowRes = @[] + buf[0] = '\0' + for colId in 1..cCnt: + db.SqlCheck(SQLGetData(db.stmt, colId.SqlUSmallInt, SQL_C_CHAR, + cast[SqlPointer](buf.addr), 4095.TSqlSmallInt, sz.addr)) + rowRes.add($buf.cstring) + db.SqlCheck(SQLFetchScroll(db.stmt, SQL_FETCH_NEXT, 1)) + result.add(rowRes) + properFreeResult(SQL_HANDLE_STMT, db.stmt) + +iterator rows*(db: var DbConn, query: SqlQuery, + args: varargs[string, `$`]): Row {. + tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].} = + ## Same as `fastRows`, but slower and safe. + ## + ## This retrieves ALL rows into memory before + ## iterating through the rows. + ## Large dataset queries will impact on memory usage. + for r in items(getAllRows(db, query, args)): yield r + +proc getValue*(db: var DbConn, query: SqlQuery, + args: varargs[string, `$`]): string {. + tags: [ReadDbEffect, WriteDbEffect], raises: [].} = + ## Executes the query and returns the first column of the first row of the + ## result dataset. Returns "" if the dataset contains no rows or the database + ## value is NULL. + result = "" + try: + result = getRow(db, query, args)[0] + except: discard + +proc tryInsertId*(db: var DbConn, query: SqlQuery, + args: varargs[string, `$`]): int64 {. + tags: [ReadDbEffect, WriteDbEffect], raises: [].} = + ## Executes the query (typically "INSERT") and returns the + ## generated ID for the row or -1 in case of an error. + if not tryExec(db, query, args): + result = -1'i64 + else: + echo "DBMS: ",SqlGetDBMS(db).toLower() + result = -1'i64 + try: + case SqlGetDBMS(db).toLower(): + of "postgresql": + result = getValue(db, sql"SELECT LASTVAL();", []).parseInt + of "mysql": + result = getValue(db, sql"SELECT LAST_INSERT_ID();", []).parseInt + of "sqlite": + result = getValue(db, sql"SELECT LAST_INSERT_ROWID();", []).parseInt + of "microsoft sql server": + result = getValue(db, sql"SELECT SCOPE_IDENTITY();", []).parseInt + of "oracle": + result = getValue(db, sql"SELECT id.currval FROM DUAL;", []).parseInt + else: result = -1'i64 + except: discard + +proc insertId*(db: var DbConn, query: SqlQuery, + args: varargs[string, `$`]): int64 {. + tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].} = + ## Executes the query (typically "INSERT") and returns the + ## generated ID for the row. + result = tryInsertID(db, query, args) + if result < 0: dbError(db) + +proc execAffectedRows*(db: var DbConn, query: SqlQuery, + args: varargs[string, `$`]): int64 {. + tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].} = + ## Runs the query (typically "UPDATE") and returns the + ## number of affected rows + result = -1 + var res = SQLAllocHandle(SQL_HANDLE_STMT, db.hDb, db.stmt.SqlHandle) + if res != SQL_SUCCESS: dbError(db) + var q = dbFormat(query, args) + res = SQLPrepare(db.stmt, q.PSQLCHAR, q.len.TSqlSmallInt) + if res != SQL_SUCCESS: dbError(db) + rawExec(db, query, args) + var rCnt = -1 + result = SQLRowCount(db.hDb, rCnt) + if res != SQL_SUCCESS: dbError(db) + properFreeResult(SQL_HANDLE_STMT, db.stmt) + result = rCnt + +proc close*(db: var DbConn) {. + tags: [WriteDbEffect], raises: [].} = + ## Closes the database connection. + if db.hDb != nil: + try: + var res = SQLDisconnect(db.hDb) + if db.stmt != nil: + res = SQLFreeHandle(SQL_HANDLE_STMT, db.stmt) + res = SQLFreeHandle(SQL_HANDLE_DBC, db.hDb) + res = SQLFreeHandle(SQL_HANDLE_ENV, db.env) + db = (hDb: nil, env: nil, stmt: nil) + except: + discard + +proc open*(connection, user, password, database: string): DbConn {. + tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].} = + ## Opens a database connection. + ## + ## Raises `EDb` if the connection could not be established. + ## + ## Currently the database parameter is ignored, + ## but included to match ``open()`` in the other db_xxxxx library modules. + var + val: TSqlInteger = SQL_OV_ODBC3 + resLen = 0 + result = (hDb: nil, env: nil, stmt: nil) + # allocate environment handle + var res = SQLAllocHandle(SQL_HANDLE_ENV, result.env, result.env) + if res != SQL_SUCCESS: dbError("Error: unable to initialise ODBC environment.") + res = SQLSetEnvAttr(result.env, + SQL_ATTR_ODBC_VERSION.TSqlInteger, + val, resLen.TSqlInteger) + if res != SQL_SUCCESS: dbError("Error: unable to set ODBC driver version.") + # allocate hDb handle + res = SQLAllocHandle(SQL_HANDLE_DBC, result.env, result.hDb) + if res != SQL_SUCCESS: dbError("Error: unable to allocate connection handle.") + + # Connect: connection = dsn str, + res = SQLConnect(result.hDb, + connection.PSQLCHAR , connection.len.TSqlSmallInt, + user.PSQLCHAR, user.len.TSqlSmallInt, + password.PSQLCHAR, password.len.TSqlSmallInt) + if res != SQL_SUCCESS: + result.dbError() + +proc setEncoding*(connection: DbConn, encoding: string): bool {. + tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].} = + ## Currently not implemented for ODBC. + ## + ## Sets the encoding of a database connection, returns true for + ## success, false for failure. + #result = set_character_set(connection, encoding) == 0 + dbError("setEncoding() is currently not implemented by the db_odbc module") diff --git a/lib/impure/db_postgres.nim b/lib/impure/db_postgres.nim index 5603d9686..9bdbae4c2 100644 --- a/lib/impure/db_postgres.nim +++ b/lib/impure/db_postgres.nim @@ -62,47 +62,28 @@ ## "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 - ## transformed always to the empty string. + ## converted to nil. 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 SqlPrepared* = distinct string ## a identifier for the prepared queries - FDb* = object of IOEffect ## effect that denotes a database operation - FReadDb* = object of FDb ## effect that denotes a read operation - FWriteDb* = object of FDb ## effect that denotes a write operation -{.deprecated: [TRow: Row, TSqlQuery: SqlQuery, TDbConn: DbConn, +{.deprecated: [TRow: Row, TDbConn: DbConn, TSqlPrepared: SqlPrepared].} -proc sql*(query: string): SqlQuery {.noSideEffect, inline.} = - ## constructs a SqlQuery 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 = SqlQuery(query) - proc dbError*(db: DbConn) {.noreturn.} = - ## raises an EDb exception. - var e: ref EDb + ## raises a DbError exception. + var e: ref DbError new(e) e.msg = $pqErrorMessage(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 = ## DB quotes the string. result = "'" @@ -127,7 +108,7 @@ proc dbFormat(formatstr: SqlQuery, args: varargs[string]): string = add(result, c) proc tryExec*(db: DbConn, query: SqlQuery, - args: varargs[string, `$`]): bool {.tags: [FReadDB, FWriteDb].} = + 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) @@ -135,7 +116,8 @@ proc tryExec*(db: DbConn, query: SqlQuery, pqclear(res) proc tryExec*(db: DbConn, stmtName: SqlPrepared, - args: varargs[string, `$`]): bool {.tags: [FReadDB, FWriteDb].} = + 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, @@ -145,7 +127,7 @@ proc tryExec*(db: DbConn, stmtName: SqlPrepared, pqclear(res) proc exec*(db: DbConn, query: SqlQuery, args: varargs[string, `$`]) {. - tags: [FReadDB, FWriteDb].} = + 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) @@ -153,7 +135,7 @@ proc exec*(db: DbConn, query: SqlQuery, args: varargs[string, `$`]) {. pqclear(res) proc exec*(db: DbConn, stmtName: SqlPrepared, - args: varargs[string]) {.tags: [FReadDB, FWriteDb].} = + args: varargs[string]) {.tags: [ReadDbEffect, WriteDbEffect].} = var arr = allocCStringArray(args) var res = pqexecPrepared(db, stmtName.string, int32(args.len), arr, nil, nil, 0) @@ -196,7 +178,7 @@ proc setRow(res: PPGresult, r: var Row, line, cols: int32) = add(r[col], x) iterator fastRows*(db: DbConn, query: SqlQuery, - args: varargs[string, `$`]): Row {.tags: [FReadDB].} = + args: varargs[string, `$`]): Row {.tags: [ReadDbEffect].} = ## 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 Postgres it is safe though. @@ -209,7 +191,7 @@ iterator fastRows*(db: DbConn, query: SqlQuery, pqclear(res) iterator fastRows*(db: DbConn, stmtName: SqlPrepared, - args: varargs[string, `$`]): Row {.tags: [FReadDB].} = + args: varargs[string, `$`]): Row {.tags: [ReadDbEffect].} = ## executes the prepared query and iterates over the result dataset. var res = setupQuery(db, stmtName, args) var L = pqNfields(res) @@ -221,9 +203,9 @@ iterator fastRows*(db: DbConn, stmtName: SqlPrepared, iterator instantRows*(db: DbConn, query: SqlQuery, args: varargs[string, `$`]): InstantRow - {.tags: [FReadDb].} = + {.tags: [ReadDbEffect].} = ## 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. + ## on demand using []. Returned handle is valid only within iterator body. var res = setupQuery(db, query, args) for i in 0..pqNtuples(res)-1: yield (res: res, line: i) @@ -231,9 +213,9 @@ iterator instantRows*(db: DbConn, query: SqlQuery, iterator instantRows*(db: DbConn, stmtName: SqlPrepared, args: varargs[string, `$`]): InstantRow - {.tags: [FReadDb].} = + {.tags: [ReadDbEffect].} = ## 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. + ## on demand using []. Returned handle is valid only within iterator body. var res = setupQuery(db, stmtName, args) for i in 0..pqNtuples(res)-1: yield (res: res, line: i) @@ -248,7 +230,7 @@ proc len*(row: InstantRow): int32 {.inline.} = pqNfields(row.res) proc getRow*(db: DbConn, query: SqlQuery, - args: varargs[string, `$`]): Row {.tags: [FReadDB].} = + args: varargs[string, `$`]): Row {.tags: [ReadDbEffect].} = ## retrieves a single row. If the query doesn't return any rows, this proc ## will return a Row with empty strings for each column. var res = setupQuery(db, query, args) @@ -258,7 +240,7 @@ proc getRow*(db: DbConn, query: SqlQuery, pqclear(res) proc getRow*(db: DbConn, stmtName: SqlPrepared, - args: varargs[string, `$`]): Row {.tags: [FReadDB].} = + args: varargs[string, `$`]): Row {.tags: [ReadDbEffect].} = var res = setupQuery(db, stmtName, args) var L = pqNfields(res) result = newRow(L) @@ -266,31 +248,34 @@ proc getRow*(db: DbConn, stmtName: SqlPrepared, pqClear(res) proc getAllRows*(db: DbConn, query: SqlQuery, - args: varargs[string, `$`]): seq[Row] {.tags: [FReadDB].} = + args: varargs[string, `$`]): seq[Row] {. + tags: [ReadDbEffect].} = ## executes the query and returns the whole result dataset. result = @[] for r in fastRows(db, query, args): result.add(r) proc getAllRows*(db: DbConn, stmtName: SqlPrepared, - args: varargs[string, `$`]): seq[Row] {.tags: [FReadDB].} = + args: varargs[string, `$`]): seq[Row] {.tags: + [ReadDbEffect].} = ## executes the prepared query and returns the whole result dataset. result = @[] for r in fastRows(db, stmtName, args): result.add(r) iterator rows*(db: DbConn, query: SqlQuery, - args: varargs[string, `$`]): Row {.tags: [FReadDB].} = + args: varargs[string, `$`]): Row {.tags: [ReadDbEffect].} = ## same as `fastRows`, but slower and safe. for r in items(getAllRows(db, query, args)): yield r iterator rows*(db: DbConn, stmtName: SqlPrepared, - args: varargs[string, `$`]): Row {.tags: [FReadDB].} = + args: varargs[string, `$`]): Row {.tags: [ReadDbEffect].} = ## same as `fastRows`, but slower and safe. for r in items(getAllRows(db, stmtName, args)): yield r proc getValue*(db: DbConn, query: SqlQuery, - args: varargs[string, `$`]): string {.tags: [FReadDB].} = + args: varargs[string, `$`]): string {. + tags: [ReadDbEffect].} = ## executes the query and returns the first column of the first row of the ## result dataset. Returns "" if the dataset contains no rows or the database ## value is NULL. @@ -298,7 +283,8 @@ proc getValue*(db: DbConn, query: SqlQuery, result = if isNil(x): "" else: $x proc tryInsertID*(db: DbConn, query: SqlQuery, - args: varargs[string, `$`]): int64 {.tags: [FWriteDb].}= + args: varargs[string, `$`]): int64 {. + tags: [WriteDbEffect].}= ## executes the query (typically "INSERT") and returns the ## generated ID for the row or -1 in case of an error. For Postgre this adds ## ``RETURNING id`` to the query, so it only works if your primary key is @@ -311,7 +297,8 @@ proc tryInsertID*(db: DbConn, query: SqlQuery, result = -1 proc insertID*(db: DbConn, query: SqlQuery, - args: varargs[string, `$`]): int64 {.tags: [FWriteDb].} = + args: varargs[string, `$`]): int64 {. + tags: [WriteDbEffect].} = ## 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 @@ -321,7 +308,7 @@ proc insertID*(db: DbConn, query: SqlQuery, proc execAffectedRows*(db: DbConn, query: SqlQuery, args: varargs[string, `$`]): int64 {.tags: [ - FReadDB, FWriteDb].} = + ReadDbEffect, WriteDbEffect].} = ## executes the query (typically "UPDATE") and returns the ## number of affected rows. var q = dbFormat(query, args) @@ -332,7 +319,7 @@ proc execAffectedRows*(db: DbConn, query: SqlQuery, proc execAffectedRows*(db: DbConn, stmtName: SqlPrepared, args: varargs[string, `$`]): int64 {.tags: [ - FReadDB, FWriteDb].} = + ReadDbEffect, WriteDbEffect].} = ## executes the query (typically "UPDATE") and returns the ## number of affected rows. var arr = allocCStringArray(args) @@ -343,12 +330,12 @@ proc execAffectedRows*(db: DbConn, stmtName: SqlPrepared, result = parseBiggestInt($pqcmdTuples(res)) pqclear(res) -proc close*(db: DbConn) {.tags: [FDb].} = +proc close*(db: DbConn) {.tags: [DbEffect].} = ## closes the database connection. if db != nil: pqfinish(db) proc open*(connection, user, password, database: string): DbConn {. - tags: [FDb].} = + tags: [DbEffect].} = ## opens a database connection. Raises `EDb` if the connection could not ## be established. ## @@ -370,7 +357,7 @@ proc open*(connection, user, password, database: string): DbConn {. if pqStatus(result) != CONNECTION_OK: dbError(result) # result = nil proc setEncoding*(connection: DbConn, encoding: string): bool {. - tags: [FDb].} = + tags: [DbEffect].} = ## sets the encoding of a database connection, returns true for ## success, false for failure. return pqsetClientEncoding(connection, encoding) == 0 diff --git a/lib/impure/db_sqlite.nim b/lib/impure/db_sqlite.nim index 8366fdadc..c0d221a0d 100644 --- a/lib/impure/db_sqlite.nim +++ b/lib/impure/db_sqlite.nim @@ -1,7 +1,7 @@ # # # Nim's Runtime Library -# (c) Copyright 2012 Andreas Rumpf +# (c) Copyright 2015 Andreas Rumpf # # See the file "copying.txt", included in this # distribution, for details about the copyright. @@ -40,47 +40,30 @@ ## ## theDb.close() +{.deadCodeElim:on.} + import strutils, sqlite3 +import db_common +export db_common + 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. + ## converted to nil. 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 +{.deprecated: [TRow: Row, TDbConn: DbConn].} - FDb* = object of IOEffect ## effect that denotes a database operation - FReadDb* = object of FDb ## effect that denotes a read operation - FWriteDb* = object of FDb ## effect that denotes a write operation -{.deprecated: [TRow: Row, TSqlQuery: SqlQuery, TDbConn: DbConn].} - -proc sql*(query: string): SqlQuery {.noSideEffect, inline.} = - ## constructs a SqlQuery 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 = SqlQuery(query) - -proc dbError(db: DbConn) {.noreturn.} = - ## raises an EDb exception. - var e: ref EDb +proc dbError*(db: DbConn) {.noreturn.} = + ## raises a DbError exception. + var e: ref DbError 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 = +proc dbQuote*(s: string): string = + ## DB quotes the string. if s.isNil: return "NULL" result = "'" for c in items(s): @@ -99,7 +82,8 @@ proc dbFormat(formatstr: SqlQuery, args: varargs[string]): string = add(result, c) proc tryExec*(db: DbConn, query: SqlQuery, - args: varargs[string, `$`]): bool {.tags: [FReadDb, FWriteDb].} = + args: varargs[string, `$`]): bool {. + tags: [ReadDbEffect, WriteDbEffect].} = ## tries to execute the query and returns true if successful, false otherwise. var q = dbFormat(query, args) var stmt: sqlite3.Pstmt @@ -108,8 +92,8 @@ proc tryExec*(db: DbConn, query: SqlQuery, result = finalize(stmt) == SQLITE_OK proc exec*(db: DbConn, query: SqlQuery, args: varargs[string, `$`]) {. - tags: [FReadDb, FWriteDb].} = - ## executes the query and raises EDB if not successful. + tags: [ReadDbEffect, WriteDbEffect].} = + ## executes the query and raises DbError if not successful. if not tryExec(db, query, args): dbError(db) proc newRow(L: int): Row = @@ -129,14 +113,14 @@ proc setRow(stmt: Pstmt, r: var Row, cols: cint) = if not isNil(x): add(r[col], x) iterator fastRows*(db: DbConn, query: SqlQuery, - args: varargs[string, `$`]): Row {.tags: [FReadDb].} = + args: varargs[string, `$`]): Row {.tags: [ReadDbEffect].} = ## Executes the query and iterates over the result dataset. ## ## This is very fast, but potentially dangerous. Use this iterator only ## if you require **ALL** the rows. ## ## Breaking the fastRows() iterator during a loop will cause the next - ## database query to raise an [EDb] exception ``unable to close due to ...``. + ## database query to raise a DbError exception ``unable to close due to ...``. var stmt = setupQuery(db, query, args) var L = (column_count(stmt)) var result = newRow(L) @@ -147,10 +131,43 @@ iterator fastRows*(db: DbConn, query: SqlQuery, iterator instantRows*(db: DbConn, query: SqlQuery, args: varargs[string, `$`]): InstantRow - {.tags: [FReadDb].} = + {.tags: [ReadDbEffect].} = + ## same as fastRows but returns a handle that can be used to get column text + ## on demand using []. Returned handle is valid only within the iterator body. + var stmt = setupQuery(db, query, args) + while step(stmt) == SQLITE_ROW: + yield stmt + if finalize(stmt) != SQLITE_OK: dbError(db) + +proc toTypeKind(t: var DbType; x: int32) = + case x + of SQLITE_INTEGER: + t.kind = dbInt + t.size = 8 + of SQLITE_FLOAT: + t.kind = dbFloat + t.size = 8 + of SQLITE_BLOB: t.kind = dbBlob + of SQLITE_NULL: t.kind = dbNull + of SQLITE_TEXT: t.kind = dbVarchar + else: t.kind = dbUnknown + +proc setColumns(columns: var DbColumns; x: PStmt) = + let L = column_count(x) + setLen(columns, L) + for i in 0'i32 ..< L: + columns[i].name = $column_name(x, i) + columns[i].typ.name = $column_decltype(x, i) + toTypeKind(columns[i].typ, column_type(x, i)) + columns[i].tableName = $column_table_name(x, i) + +iterator instantRows*(db: DbConn; columns: var DbColumns; query: SqlQuery, + args: varargs[string, `$`]): InstantRow + {.tags: [ReadDbEffect].} = ## same as fastRows but returns a handle that can be used to get column text - ## on demand using []. Returned handle is valid only within the interator body. + ## on demand using []. Returned handle is valid only within the iterator body. var stmt = setupQuery(db, query, args) + setColumns(columns, stmt) while step(stmt) == SQLITE_ROW: yield stmt if finalize(stmt) != SQLITE_OK: dbError(db) @@ -164,7 +181,7 @@ proc len*(row: InstantRow): int32 {.inline.} = column_count(row) proc getRow*(db: DbConn, query: SqlQuery, - args: varargs[string, `$`]): Row {.tags: [FReadDb].} = + args: varargs[string, `$`]): Row {.tags: [ReadDbEffect].} = ## retrieves a single row. If the query doesn't return any rows, this proc ## will return a Row with empty strings for each column. var stmt = setupQuery(db, query, args) @@ -175,19 +192,19 @@ proc getRow*(db: DbConn, query: SqlQuery, if finalize(stmt) != SQLITE_OK: dbError(db) proc getAllRows*(db: DbConn, query: SqlQuery, - args: varargs[string, `$`]): seq[Row] {.tags: [FReadDb].} = + args: varargs[string, `$`]): seq[Row] {.tags: [ReadDbEffect].} = ## executes the query and returns the whole result dataset. result = @[] for r in fastRows(db, query, args): result.add(r) iterator rows*(db: DbConn, query: SqlQuery, - args: varargs[string, `$`]): Row {.tags: [FReadDb].} = + args: varargs[string, `$`]): Row {.tags: [ReadDbEffect].} = ## same as `FastRows`, but slower and safe. for r in fastRows(db, query, args): yield r proc getValue*(db: DbConn, query: SqlQuery, - args: varargs[string, `$`]): string {.tags: [FReadDb].} = + args: varargs[string, `$`]): string {.tags: [ReadDbEffect].} = ## executes the query and returns the first column of the first row of the ## result dataset. Returns "" if the dataset contains no rows or the database ## value is NULL. @@ -205,7 +222,7 @@ proc getValue*(db: DbConn, query: SqlQuery, proc tryInsertID*(db: DbConn, query: SqlQuery, args: varargs[string, `$`]): int64 - {.tags: [FWriteDb], raises: [].} = + {.tags: [WriteDbEffect], raises: [].} = ## executes the query (typically "INSERT") and returns the ## generated ID for the row or -1 in case of an error. var q = dbFormat(query, args) @@ -218,7 +235,7 @@ proc tryInsertID*(db: DbConn, query: SqlQuery, result = -1 proc insertID*(db: DbConn, query: SqlQuery, - args: varargs[string, `$`]): int64 {.tags: [FWriteDb].} = + args: varargs[string, `$`]): int64 {.tags: [WriteDbEffect].} = ## 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 @@ -228,18 +245,18 @@ proc insertID*(db: DbConn, query: SqlQuery, proc execAffectedRows*(db: DbConn, query: SqlQuery, args: varargs[string, `$`]): int64 {. - tags: [FReadDb, FWriteDb].} = + tags: [ReadDbEffect, WriteDbEffect].} = ## executes the query (typically "UPDATE") and returns the ## number of affected rows. exec(db, query, args) result = changes(db) -proc close*(db: DbConn) {.tags: [FDb].} = +proc close*(db: DbConn) {.tags: [DbEffect].} = ## closes the database connection. if sqlite3.close(db) != SQLITE_OK: dbError(db) proc open*(connection, user, password, database: string): DbConn {. - tags: [FDb].} = + tags: [DbEffect].} = ## opens a database connection. Raises `EDb` if the connection could not ## be established. Only the ``connection`` parameter is used for ``sqlite``. var db: DbConn @@ -249,7 +266,7 @@ proc open*(connection, user, password, database: string): DbConn {. dbError(db) proc setEncoding*(connection: DbConn, encoding: string): bool {. - tags: [FDb].} = + tags: [DbEffect].} = ## sets the encoding of a database connection, returns true for ## success, false for failure. ## |