diff options
| -rw-r--r-- | lib/impure/db_postgres.nim | 182 | ||||
| -rw-r--r-- | tests/untestable/tpostgres.nim | 229 |
2 files changed, 396 insertions, 15 deletions
diff --git a/lib/impure/db_postgres.nim b/lib/impure/db_postgres.nim index 60bd1f081..fc587b5df 100644 --- a/lib/impure/db_postgres.nim +++ b/lib/impure/db_postgres.nim @@ -69,16 +69,14 @@ 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* = tuple[res: PPGresult, line: int32] ## a handle that can be - ## used to get a row's - ## column text on demand + 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 - -{.deprecated: [TRow: Row, TDbConn: DbConn, - TSqlPrepared: SqlPrepared].} +{.deprecated: [TRow: Row, TDbConn: DbConn, TSqlPrepared: SqlPrepared].} proc dbError*(db: DbConn) {.noreturn.} = ## raises a DbError exception. @@ -213,7 +211,7 @@ iterator instantRows*(db: DbConn, query: SqlQuery, ## 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) + yield InstantRow(res: res, line: i) pqClear(res) iterator instantRows*(db: DbConn, stmtName: SqlPrepared, @@ -223,16 +221,170 @@ iterator instantRows*(db: DbConn, stmtName: SqlPrepared, ## 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) + yield InstantRow(res: res, line: i) + pqClear(res) + +proc getColumnType(res: PPGresult, col: int) : DbType = + ## returns DbType for given column in the row + ## defined in pg_type.h file in the postgres source code + ## Wire representation for types: http://www.npgsql.org/dev/types.html + var oid = pqftype(res, int32(col)) + ## The integer returned is the internal OID number of the type + case oid + of 16: return DbType(kind: DbTypeKind.dbBool, name: "bool") + of 17: return DbType(kind: DbTypeKind.dbBlob, name: "bytea") + + of 21: return DbType(kind: DbTypeKind.dbInt, name: "int2", size: 2) + of 23: return DbType(kind: DbTypeKind.dbInt, name: "int4", size: 4) + of 20: return DbType(kind: DbTypeKind.dbInt, name: "int8", size: 8) + of 1560: return DbType(kind: DbTypeKind.dbBit, name: "bit") + of 1562: return DbType(kind: DbTypeKind.dbInt, name: "varbit") + + of 18: return DbType(kind: DbTypeKind.dbFixedChar, name: "char") + of 19: return DbType(kind: DbTypeKind.dbFixedChar, name: "name") + of 1042: return DbType(kind: DbTypeKind.dbFixedChar, name: "bpchar") + + of 25: return DbType(kind: DbTypeKind.dbVarchar, name: "text") + of 1043: return DbType(kind: DbTypeKind.dbVarChar, name: "varchar") + of 2275: return DbType(kind: DbTypeKind.dbVarchar, name: "cstring") + + of 700: return DbType(kind: DbTypeKind.dbFloat, name: "float4") + of 701: return DbType(kind: DbTypeKind.dbFloat, name: "float8") + + of 790: return DbType(kind: DbTypeKind.dbDecimal, name: "money") + of 1700: return DbType(kind: DbTypeKind.dbDecimal, name: "numeric") + + of 704: return DbType(kind: DbTypeKind.dbTimeInterval, name: "tinterval") + of 702: return DbType(kind: DbTypeKind.dbTimestamp, name: "abstime") + of 703: return DbType(kind: DbTypeKind.dbTimeInterval, name: "reltime") + of 1082: return DbType(kind: DbTypeKind.dbDate, name: "date") + of 1083: return DbType(kind: DbTypeKind.dbTime, name: "time") + of 1114: return DbType(kind: DbTypeKind.dbTimestamp, name: "timestamp") + of 1184: return DbType(kind: DbTypeKind.dbTimestamp, name: "timestamptz") + of 1186: return DbType(kind: DbTypeKind.dbTimeInterval, name: "interval") + of 1266: return DbType(kind: DbTypeKind.dbTime, name: "timetz") + + of 114: return DbType(kind: DbTypeKind.dbJson, name: "json") + of 142: return DbType(kind: DbTypeKind.dbXml, name: "xml") + of 3802: return DbType(kind: DbTypeKind.dbJson, name: "jsonb") + + of 600: return DbType(kind: DbTypeKind.dbPoint, name: "point") + of 601: return DbType(kind: DbTypeKind.dbLseg, name: "lseg") + of 602: return DbType(kind: DbTypeKind.dbPath, name: "path") + of 603: return DbType(kind: DbTypeKind.dbBox, name: "box") + of 604: return DbType(kind: DbTypeKind.dbPolygon, name: "polygon") + of 628: return DbType(kind: DbTypeKind.dbLine, name: "line") + of 718: return DbType(kind: DbTypeKind.dbCircle, name: "circle") + + of 650: return DbType(kind: DbTypeKind.dbInet, name: "cidr") + of 829: return DbType(kind: DbTypeKind.dbMacAddress, name: "macaddr") + of 869: return DbType(kind: DbTypeKind.dbInet, name: "inet") + + of 2950: return DbType(kind: DbTypeKind.dbVarchar, name: "uuid") + of 3614: return DbType(kind: DbTypeKind.dbVarchar, name: "tsvector") + of 3615: return DbType(kind: DbTypeKind.dbVarchar, name: "tsquery") + of 2970: return DbType(kind: DbTypeKind.dbVarchar, name: "txid_snapshot") + + of 27: return DbType(kind: DbTypeKind.dbComposite, name: "tid") + of 1790: return DbType(kind: DbTypeKind.dbComposite, name: "refcursor") + of 2249: return DbType(kind: DbTypeKind.dbComposite, name: "record") + of 3904: return DbType(kind: DbTypeKind.dbComposite, name: "int4range") + of 3906: return DbType(kind: DbTypeKind.dbComposite, name: "numrange") + of 3908: return DbType(kind: DbTypeKind.dbComposite, name: "tsrange") + of 3910: return DbType(kind: DbTypeKind.dbComposite, name: "tstzrange") + of 3912: return DbType(kind: DbTypeKind.dbComposite, name: "daterange") + of 3926: return DbType(kind: DbTypeKind.dbComposite, name: "int8range") + + of 22: return DbType(kind: DbTypeKind.dbArray, name: "int2vector") + of 30: return DbType(kind: DbTypeKind.dbArray, name: "oidvector") + of 143: return DbType(kind: DbTypeKind.dbArray, name: "xml[]") + of 199: return DbType(kind: DbTypeKind.dbArray, name: "json[]") + of 629: return DbType(kind: DbTypeKind.dbArray, name: "line[]") + of 651: return DbType(kind: DbTypeKind.dbArray, name: "cidr[]") + of 719: return DbType(kind: DbTypeKind.dbArray, name: "circle[]") + of 791: return DbType(kind: DbTypeKind.dbArray, name: "money[]") + of 1000: return DbType(kind: DbTypeKind.dbArray, name: "bool[]") + of 1001: return DbType(kind: DbTypeKind.dbArray, name: "bytea[]") + of 1002: return DbType(kind: DbTypeKind.dbArray, name: "char[]") + of 1003: return DbType(kind: DbTypeKind.dbArray, name: "name[]") + of 1005: return DbType(kind: DbTypeKind.dbArray, name: "int2[]") + of 1006: return DbType(kind: DbTypeKind.dbArray, name: "int2vector[]") + of 1007: return DbType(kind: DbTypeKind.dbArray, name: "int4[]") + of 1008: return DbType(kind: DbTypeKind.dbArray, name: "regproc[]") + of 1009: return DbType(kind: DbTypeKind.dbArray, name: "text[]") + of 1028: return DbType(kind: DbTypeKind.dbArray, name: "oid[]") + of 1010: return DbType(kind: DbTypeKind.dbArray, name: "tid[]") + of 1011: return DbType(kind: DbTypeKind.dbArray, name: "xid[]") + of 1012: return DbType(kind: DbTypeKind.dbArray, name: "cid[]") + of 1013: return DbType(kind: DbTypeKind.dbArray, name: "oidvector[]") + of 1014: return DbType(kind: DbTypeKind.dbArray, name: "bpchar[]") + of 1015: return DbType(kind: DbTypeKind.dbArray, name: "varchar[]") + of 1016: return DbType(kind: DbTypeKind.dbArray, name: "int8[]") + of 1017: return DbType(kind: DbTypeKind.dbArray, name: "point[]") + of 1018: return DbType(kind: DbTypeKind.dbArray, name: "lseg[]") + of 1019: return DbType(kind: DbTypeKind.dbArray, name: "path[]") + of 1020: return DbType(kind: DbTypeKind.dbArray, name: "box[]") + of 1021: return DbType(kind: DbTypeKind.dbArray, name: "float4[]") + of 1022: return DbType(kind: DbTypeKind.dbArray, name: "float8[]") + of 1023: return DbType(kind: DbTypeKind.dbArray, name: "abstime[]") + of 1024: return DbType(kind: DbTypeKind.dbArray, name: "reltime[]") + of 1025: return DbType(kind: DbTypeKind.dbArray, name: "tinterval[]") + of 1027: return DbType(kind: DbTypeKind.dbArray, name: "polygon[]") + of 1040: return DbType(kind: DbTypeKind.dbArray, name: "macaddr[]") + of 1041: return DbType(kind: DbTypeKind.dbArray, name: "inet[]") + of 1263: return DbType(kind: DbTypeKind.dbArray, name: "cstring[]") + of 1115: return DbType(kind: DbTypeKind.dbArray, name: "timestamp[]") + of 1182: return DbType(kind: DbTypeKind.dbArray, name: "date[]") + of 1183: return DbType(kind: DbTypeKind.dbArray, name: "time[]") + of 1185: return DbType(kind: DbTypeKind.dbArray, name: "timestamptz[]") + of 1187: return DbType(kind: DbTypeKind.dbArray, name: "interval[]") + of 1231: return DbType(kind: DbTypeKind.dbArray, name: "numeric[]") + of 1270: return DbType(kind: DbTypeKind.dbArray, name: "timetz[]") + of 1561: return DbType(kind: DbTypeKind.dbArray, name: "bit[]") + of 1563: return DbType(kind: DbTypeKind.dbArray, name: "varbit[]") + of 2201: return DbType(kind: DbTypeKind.dbArray, name: "refcursor[]") + of 2951: return DbType(kind: DbTypeKind.dbArray, name: "uuid[]") + of 3643: return DbType(kind: DbTypeKind.dbArray, name: "tsvector[]") + of 3645: return DbType(kind: DbTypeKind.dbArray, name: "tsquery[]") + of 3807: return DbType(kind: DbTypeKind.dbArray, name: "jsonb[]") + of 2949: return DbType(kind: DbTypeKind.dbArray, name: "txid_snapshot[]") + of 3905: return DbType(kind: DbTypeKind.dbArray, name: "int4range[]") + of 3907: return DbType(kind: DbTypeKind.dbArray, name: "numrange[]") + of 3909: return DbType(kind: DbTypeKind.dbArray, name: "tsrange[]") + of 3911: return DbType(kind: DbTypeKind.dbArray, name: "tstzrange[]") + of 3913: return DbType(kind: DbTypeKind.dbArray, name: "daterange[]") + of 3927: return DbType(kind: DbTypeKind.dbArray, name: "int8range[]") + of 2287: return DbType(kind: DbTypeKind.dbArray, name: "record[]") + + of 705: return DbType(kind: DbTypeKind.dbUnknown, name: "unknown") + else: return DbType(kind: DbTypeKind.dbUnknown, name: $oid) ## Query the system table pg_type to determine exactly which type is referenced. + +proc setColumnInfo(columns: var DbColumns; res: PPGresult; L: int32) = + setLen(columns, L) + for i in 0..<L: + columns[i].name = $pqfname(res, i) + columns[i].typ = getColumnType(res, i) + columns[i].tableName = $(pqftable(res, i)) ## Returns the OID of the table from which the given column was fetched. + ## Query the system table pg_class to determine exactly which table is referenced. + #columns[i].primaryKey = libpq does not have a function for that + #columns[i].foreignKey = libpq does not have a function for that + +iterator instantRows*(db: DbConn; columns: var DbColumns; query: SqlQuery; + args: varargs[string, `$`]): InstantRow + {.tags: [ReadDbEffect].} = + var res = setupQuery(db, query, args) + setColumnInfo(columns, res, pqnfields(res)) + for i in 0..<pqntuples(res): + yield InstantRow(res: res, line: i) pqClear(res) -proc `[]`*(row: InstantRow, col: int32): string {.inline.} = +proc `[]`*(row: InstantRow; col: int): string {.inline.} = ## returns text for given column of the row - $pqgetvalue(row.res, row.line, col) + $pqgetvalue(row.res, int32(row.line), int32(col)) -proc len*(row: InstantRow): int32 {.inline.} = +proc len*(row: InstantRow): int {.inline.} = ## returns number of columns in the row - pqNfields(row.res) + int(pqNfields(row.res)) proc getRow*(db: DbConn, query: SqlQuery, args: varargs[string, `$`]): Row {.tags: [ReadDbEffect].} = diff --git a/tests/untestable/tpostgres.nim b/tests/untestable/tpostgres.nim index dcbdaad39..486d0d703 100644 --- a/tests/untestable/tpostgres.nim +++ b/tests/untestable/tpostgres.nim @@ -76,7 +76,236 @@ for i in 1..10: echo(name) discard db.getRow( SqlQuery("INSERT INTO tags(name) VALUES(\'$1\') RETURNING id" % [name])) + +# get column details +db.exec(SqlQuery("DROP TABLE IF EXISTS dbtypes;")) +db.exec(SqlQuery("DROP TYPE IF EXISTS custom_enum;")) +db.exec(SqlQuery("CREATE TYPE custom_enum AS ENUM ('1', '2', '3');")) +db.exec(SqlQuery("DROP TYPE IF EXISTS custom_composite;")) +db.exec(SqlQuery("CREATE TYPE custom_composite AS (r double precision, i double precision);")) +db.exec(SqlQuery("""CREATE TABLE dbtypes( + id serial UNIQUE, + bytea_col bytea, + smallint_col smallint, + integer_col integer, + bigint_col bigint, + decimal_col decimal, + numeric_col numeric, + real_col real, + double_precision_col double precision, + smallserial_col smallserial, + serial_col serial, + bigserial_col bigserial, + money_col money, + varchar_col varchar(10), + character_col character(1), + text_col text, + timestamp_col timestamp, + date_col date, + time_col time, + interval_col interval, + bool_col boolean, + custom_enum_col custom_enum, + point_col point, + line_col line, + lseg_col lseg, + box_col box, + path_col path, + polygon_col polygon, + circle_col circle, + cidr_col cidr, + inet_col inet, + macaddr_col macaddr, + bit_col bit, + varbit_col bit(3), + tsvector_col tsvector, + tsquery_col tsquery, + uuid_col uuid, + xml_col xml, + json_col json, + array_col integer[], + custom_composite_col custom_composite, + range_col int4range + );""")) +db.exec(SqlQuery("INSERT INTO dbtypes (id) VALUES(0);")) +var dbCols : DbColumns = @[] +for row in db.instantRows(dbCols, sql"SELECT * FROM dbtypes"): + doAssert len(dbCols) == 42 + +doAssert dbCols[0].name == "id" +doAssert dbCols[0].typ.kind == DbTypeKind.dbInt +doAssert dbCols[0].typ.name == "int4" +doAssert dbCols[0].typ.size == 4 + +doAssert dbCols[1].name == "bytea_col" +doAssert dbCols[1].typ.kind == DbTypeKind.dbBlob +doAssert dbCols[1].typ.name == "bytea" + +doAssert dbCols[2].name == "smallint_col" +doAssert dbCols[2].typ.kind == DbTypeKind.dbInt +doAssert dbCols[2].typ.name == "int2" +doAssert dbCols[2].typ.size == 2 + +doAssert dbCols[3].name == "integer_col" +doAssert dbCols[3].typ.kind == DbTypeKind.dbInt +doAssert dbCols[3].typ.name == "int4" +doAssert dbCols[3].typ.size == 4 + +doAssert dbCols[4].name == "bigint_col" +doAssert dbCols[4].typ.kind == DbTypeKind.dbInt +doAssert dbCols[4].typ.name == "int8" +doAssert dbCols[4].typ.size == 8 + +doAssert dbCols[5].name == "decimal_col" +doAssert dbCols[5].typ.kind == DbTypeKind.dbDecimal +doAssert dbCols[5].typ.name == "numeric" + +doAssert dbCols[6].name == "numeric_col" +doAssert dbCols[6].typ.kind == DbTypeKind.dbDecimal +doAssert dbCols[6].typ.name == "numeric" + +doAssert dbCols[7].name == "real_col" +doAssert dbCols[7].typ.kind == DbTypeKind.dbFloat +doAssert dbCols[7].typ.name == "float4" + +doAssert dbCols[8].name == "double_precision_col" +doAssert dbCols[8].typ.kind == DbTypeKind.dbFloat +doAssert dbCols[8].typ.name == "float8" + +doAssert dbCols[9].name == "smallserial_col" +doAssert dbCols[9].typ.kind == DbTypeKind.dbInt +doAssert dbCols[9].typ.name == "int2" + +doAssert dbCols[10].name == "serial_col" +doAssert dbCols[10].typ.kind == DbTypeKind.dbInt +doAssert dbCols[10].typ.name == "int4" + +doAssert dbCols[11].name == "bigserial_col" +doAssert dbCols[11].typ.kind == DbTypeKind.dbInt +doAssert dbCols[11].typ.name == "int8" + +doAssert dbCols[12].name == "money_col" +doAssert dbCols[12].typ.kind == DbTypeKind.dbDecimal +doAssert dbCols[12].typ.name == "money" + +doAssert dbCols[13].name == "varchar_col" +doAssert dbCols[13].typ.kind == DbTypeKind.dbVarchar +doAssert dbCols[13].typ.name == "varchar" + +doAssert dbCols[14].name == "character_col" +doAssert dbCols[14].typ.kind == DbTypeKind.dbFixedChar +doAssert dbCols[14].typ.name == "bpchar" + +doAssert dbCols[15].name == "text_col" +doAssert dbCols[15].typ.kind == DbTypeKind.dbVarchar +doAssert dbCols[15].typ.name == "text" + +doAssert dbCols[16].name == "timestamp_col" +doAssert dbCols[16].typ.kind == DbTypeKind.dbTimestamp +doAssert dbCols[16].typ.name == "timestamp" + +doAssert dbCols[17].name == "date_col" +doAssert dbCols[17].typ.kind == DbTypeKind.dbDate +doAssert dbCols[17].typ.name == "date" + +doAssert dbCols[18].name == "time_col" +doAssert dbCols[18].typ.kind == DbTypeKind.dbTime +doAssert dbCols[18].typ.name == "time" + +doAssert dbCols[19].name == "interval_col" +doAssert dbCols[19].typ.kind == DbTypeKind.dbTimeInterval +doAssert dbCols[19].typ.name == "interval" + +doAssert dbCols[20].name == "bool_col" +doAssert dbCols[20].typ.kind == DbTypeKind.dbBool +doAssert dbCols[20].typ.name == "bool" + +doAssert dbCols[21].name == "custom_enum_col" +doAssert dbCols[21].typ.kind == DbTypeKind.dbUnknown +doAssert parseInt(dbCols[21].typ.name) > 0 + +doAssert dbCols[22].name == "point_col" +doAssert dbCols[22].typ.kind == DbTypeKind.dbPoint +doAssert dbCols[22].typ.name == "point" + +doAssert dbCols[23].name == "line_col" +doAssert dbCols[23].typ.kind == DbTypeKind.dbLine +doAssert dbCols[23].typ.name == "line" + +doAssert dbCols[24].name == "lseg_col" +doAssert dbCols[24].typ.kind == DbTypeKind.dbLseg +doAssert dbCols[24].typ.name == "lseg" + +doAssert dbCols[25].name == "box_col" +doAssert dbCols[25].typ.kind == DbTypeKind.dbBox +doAssert dbCols[25].typ.name == "box" + +doAssert dbCols[26].name == "path_col" +doAssert dbCols[26].typ.kind == DbTypeKind.dbPath +doAssert dbCols[26].typ.name == "path" + +doAssert dbCols[27].name == "polygon_col" +doAssert dbCols[27].typ.kind == DbTypeKind.dbPolygon +doAssert dbCols[27].typ.name == "polygon" + +doAssert dbCols[28].name == "circle_col" +doAssert dbCols[28].typ.kind == DbTypeKind.dbCircle +doAssert dbCols[28].typ.name == "circle" + +doAssert dbCols[29].name == "cidr_col" +doAssert dbCols[29].typ.kind == DbTypeKind.dbInet +doAssert dbCols[29].typ.name == "cidr" + +doAssert dbCols[30].name == "inet_col" +doAssert dbCols[30].typ.kind == DbTypeKind.dbInet +doAssert dbCols[30].typ.name == "inet" + +doAssert dbCols[31].name == "macaddr_col" +doAssert dbCols[31].typ.kind == DbTypeKind.dbMacAddress +doAssert dbCols[31].typ.name == "macaddr" + +doAssert dbCols[32].name == "bit_col" +doAssert dbCols[32].typ.kind == DbTypeKind.dbBit +doAssert dbCols[32].typ.name == "bit" + +doAssert dbCols[33].name == "varbit_col" +doAssert dbCols[33].typ.kind == DbTypeKind.dbBit +doAssert dbCols[33].typ.name == "bit" + +doAssert dbCols[34].name == "tsvector_col" +doAssert dbCols[34].typ.kind == DbTypeKind.dbVarchar +doAssert dbCols[34].typ.name == "tsvector" + +doAssert dbCols[35].name == "tsquery_col" +doAssert dbCols[35].typ.kind == DbTypeKind.dbVarchar +doAssert dbCols[35].typ.name == "tsquery" + +doAssert dbCols[36].name == "uuid_col" +doAssert dbCols[36].typ.kind == DbTypeKind.dbVarchar +doAssert dbCols[36].typ.name == "uuid" + +doAssert dbCols[37].name == "xml_col" +doAssert dbCols[37].typ.kind == DbTypeKind.dbXml +doAssert dbCols[37].typ.name == "xml" + +doAssert dbCols[38].name == "json_col" +doAssert dbCols[38].typ.kind == DbTypeKind.dbJson +doAssert dbCols[38].typ.name == "json" + +doAssert dbCols[39].name == "array_col" +doAssert dbCols[39].typ.kind == DbTypeKind.dbArray +doAssert dbCols[39].typ.name == "int4[]" + +doAssert dbCols[40].name == "custom_composite_col" +doAssert dbCols[40].typ.kind == DbTypeKind.dbUnknown +doAssert parseInt(dbCols[40].typ.name) > 0 + +doAssert dbCols[41].name == "range_col" +doAssert dbCols[41].typ.kind == DbTypeKind.dbComposite +doAssert dbCols[41].typ.name == "int4range" + echo("All tests succeeded!") db.close() + |