diff options
Diffstat (limited to 'v_windows/v/vlib/pg')
| -rw-r--r-- | v_windows/v/vlib/pg/README.md | 25 | ||||
| -rw-r--r-- | v_windows/v/vlib/pg/oid.v | 171 | ||||
| -rw-r--r-- | v_windows/v/vlib/pg/orm.v | 272 | ||||
| -rw-r--r-- | v_windows/v/vlib/pg/pg.v | 277 | ||||
| -rw-r--r-- | v_windows/v/vlib/pg/pg_orm_test.v | 77 |
5 files changed, 822 insertions, 0 deletions
diff --git a/v_windows/v/vlib/pg/README.md b/v_windows/v/vlib/pg/README.md new file mode 100644 index 0000000..bcdae98 --- /dev/null +++ b/v_windows/v/vlib/pg/README.md @@ -0,0 +1,25 @@ +Before you can use this module, you must first have PostgreSQL installed on +your system. To do this, find your OS and perform the actions listed. + +**NOTE**: These instructions are meant only as a convenience. If your OS is not +listed or you need extra help, [go here](https://www.postgresql.org/download/). + +### Fedora 31 +``` +sudo dnf install postgresql-server postgresql-contrib +sudo systemctl enable postgresql # to autostart on startup +sudo systemctl start postgresql +``` + +### Debian 10/11 +``` +sudo apt-get install postgresql postgresql-client +sudo systemctl enable postgresql # to autostart on startup +sudo systemctl start postgresql +``` + +### MacOSX (Homebrew) +``` +brew install postgresql +brew services start postgresql +``` diff --git a/v_windows/v/vlib/pg/oid.v b/v_windows/v/vlib/pg/oid.v new file mode 100644 index 0000000..2f8004d --- /dev/null +++ b/v_windows/v/vlib/pg/oid.v @@ -0,0 +1,171 @@ +module pg + +pub enum Oid { + t_bool = 16 + t_bytea = 17 + t_char = 18 + t_name = 19 + t_int8 = 20 + t_int2 = 21 + t_int2vector = 22 + t_int4 = 23 + t_regproc = 24 + t_text = 25 + t_oid = 26 + t_tid = 27 + t_xid = 28 + t_cid = 29 + t_vector = 30 + t_pg_ddl_command = 32 + t_pg_type = 71 + t_pg_attribute = 75 + t_pg_proc = 81 + t_pg_class = 83 + t_json = 114 + t_xml = 142 + t__xml = 143 + t_pg_node_tree = 194 + t__json = 199 + t_smgr = 210 + t_index_am_handler = 325 + t_point = 600 + t_lseg = 601 + t_path = 602 + t_box = 603 + t_polygon = 604 + t_line = 628 + t__line = 629 + t_cidr = 650 + t__cidr = 651 + t_float4 = 700 + t_float8 = 701 + t_abstime = 702 + t_reltime = 703 + t_tinterval = 704 + t_unknown = 705 + t_circle = 718 + t__circle = 719 + t_money = 790 + t__money = 791 + t_macaddr = 829 + t_inet = 869 + t__bool = 1000 + t__bytea = 1001 + t__char = 1002 + t__name = 1003 + t__int2 = 1005 + t__int2vector = 1006 + t__int4 = 1007 + t__regproc = 1008 + t__text = 1009 + t__tid = 1010 + t__xid = 1011 + t__cid = 1012 + t__vector = 1013 + t__bpchar = 1014 + t__varchar = 1015 + t__int8 = 1016 + t__point = 1017 + t__lseg = 1018 + t__path = 1019 + t__box = 1020 + t__float4 = 1021 + t__float8 = 1022 + t__abstime = 1023 + t__reltime = 1024 + t__tinterval = 1025 + t__polygon = 1027 + t__ = 1028 + t_aclitem = 1033 + t__aclitem = 1034 + t__macaddr = 1040 + t__inet = 1041 + t_bpchar = 1042 + t_varchar = 1043 + t_date = 1082 + t_time = 1083 + t_timestamp = 1114 + t__timestamp = 1115 + t__date = 1182 + t__time = 1183 + t_timestamptz = 1184 + t__timestamptz = 1185 + t_interval = 1186 + t__interval = 1187 + t__numeric = 1231 + t_pg_database = 1248 + t__cstring = 1263 + t_timetz = 1266 + t__timetz = 1270 + t_bit = 1560 + t__bit = 1561 + t_varbit = 1562 + t__varbit = 1563 + t_numeric = 1700 + t_refcursor = 1790 + t__refcursor = 2201 + t_regprocedure = 2202 + t_regoper = 2203 + t_regoperator = 2204 + t_regclass = 2205 + t_regtype = 2206 + t__regprocedure = 2207 + t__regoper = 2208 + t__regoperator = 2209 + t__regclass = 2210 + t__regtype = 2211 + t_record = 2249 + t_cstring = 2275 + t_any = 2276 + t_anyarray = 2277 + t_v = 2278 + t_trigger = 2279 + t_language_handler = 2280 + t_internal = 2281 + t_opaque = 2282 + t_anyelement = 2283 + t__record = 2287 + t_anynonarray = 2776 + t_pg_authid = 2842 + t_pg_auth_members = 2843 + t__txid_snapshot = 2949 + t_uuid = 2950 + t__uuid = 2951 + t_txid_snapshot = 2970 + t_fdw_handler = 3115 + t_pg_lsn = 3220 + t__pg_lsn = 3221 + t_tsm_handler = 3310 + t_anyenum = 3500 + t_tsvector = 3614 + t_tsquery = 3615 + t_gtsvector = 3642 + t__tsvector = 3643 + t__gtsvector = 3644 + t__tsquery = 3645 + t_regconfig = 3734 + t__regconfig = 3735 + t_regdictionary = 3769 + t__regdictionary = 3770 + t_jsonb = 3802 + t__jsonb = 3807 + t_anyrange = 3831 + t_event_trigger = 3838 + t_int4range = 3904 + t__int4range = 3905 + t_numrange = 3906 + t__numrange = 3907 + t_tsrange = 3908 + t__tsrange = 3909 + t_tstzrange = 3910 + t__tstzrange = 3911 + t_daterange = 3912 + t__daterange = 3913 + t_int8range = 3926 + t__int8range = 3927 + t_pg_shseclabel = 4066 + t_regnamespace = 4089 + t__regnamespace = 4090 + t_regrole = 4096 + t__regrole = 4097 +} diff --git a/v_windows/v/vlib/pg/orm.v b/v_windows/v/vlib/pg/orm.v new file mode 100644 index 0000000..b08992f --- /dev/null +++ b/v_windows/v/vlib/pg/orm.v @@ -0,0 +1,272 @@ +module pg + +import orm +import time +import net.conv + +// sql expr + +pub fn (db DB) @select(config orm.SelectConfig, data orm.QueryData, where orm.QueryData) ?[][]orm.Primitive { + query := orm.orm_select_gen(config, '"', true, '$', 1, where) + mut ret := [][]orm.Primitive{} + + res := pg_stmt_worker(db, query, orm.QueryData{}, where) ? + + for row in res { + mut row_data := []orm.Primitive{} + for i, val in row.vals { + field := str_to_primitive(val, config.types[i]) ? + row_data << field + } + ret << row_data + } + + return ret +} + +// sql stmt + +pub fn (db DB) insert(table string, data orm.QueryData) ? { + query := orm.orm_stmt_gen(table, '"', .insert, true, '$', 1, data, orm.QueryData{}) + pg_stmt_worker(db, query, data, orm.QueryData{}) ? +} + +pub fn (db DB) update(table string, data orm.QueryData, where orm.QueryData) ? { + query := orm.orm_stmt_gen(table, '"', .update, true, '$', 1, data, where) + pg_stmt_worker(db, query, data, where) ? +} + +pub fn (db DB) delete(table string, where orm.QueryData) ? { + query := orm.orm_stmt_gen(table, '"', .delete, true, '$', 1, orm.QueryData{}, where) + pg_stmt_worker(db, query, orm.QueryData{}, where) ? +} + +pub fn (db DB) last_id() orm.Primitive { + query := 'SELECT LASTVAL();' + id := db.q_int(query) or { 0 } + return orm.Primitive(id) +} + +// table + +pub fn (db DB) create(table string, fields []orm.TableField) ? { + query := orm.orm_table_gen(table, '"', true, 0, fields, pg_type_from_v, false) or { return err } + pg_stmt_worker(db, query, orm.QueryData{}, orm.QueryData{}) ? +} + +pub fn (db DB) drop(table string) ? { + query := 'DROP TABLE "$table";' + pg_stmt_worker(db, query, orm.QueryData{}, orm.QueryData{}) ? +} + +// utils + +fn pg_stmt_worker(db DB, query string, data orm.QueryData, where orm.QueryData) ?[]Row { + mut param_types := []u32{} + mut param_vals := []&char{} + mut param_lens := []int{} + mut param_formats := []int{} + + pg_stmt_binder(mut param_types, mut param_vals, mut param_lens, mut param_formats, + data) + pg_stmt_binder(mut param_types, mut param_vals, mut param_lens, mut param_formats, + where) + + res := C.PQexecParams(db.conn, query.str, param_vals.len, param_types.data, param_vals.data, + param_lens.data, param_formats.data, 0) + return db.handle_error_or_result(res, 'orm_stmt_worker') +} + +fn pg_stmt_binder(mut types []u32, mut vals []&char, mut lens []int, mut formats []int, d orm.QueryData) { + for data in d.data { + pg_stmt_match(mut types, mut vals, mut lens, mut formats, data) + } +} + +fn pg_stmt_match(mut types []u32, mut vals []&char, mut lens []int, mut formats []int, data orm.Primitive) { + d := data + match data { + bool { + types << u32(Oid.t_bool) + vals << &char(&(d as bool)) + lens << int(sizeof(bool)) + formats << 1 + } + byte { + types << u32(Oid.t_char) + vals << &char(&(d as byte)) + lens << int(sizeof(byte)) + formats << 1 + } + u16 { + types << u32(Oid.t_int2) + num := conv.htn16(&data) + vals << &char(&num) + lens << int(sizeof(u16)) + formats << 1 + } + u32 { + types << u32(Oid.t_int4) + num := conv.htn32(&data) + vals << &char(&num) + lens << int(sizeof(u32)) + formats << 1 + } + u64 { + types << u32(Oid.t_int8) + num := conv.htn64(&data) + vals << &char(&num) + lens << int(sizeof(u64)) + formats << 1 + } + i8 { + types << u32(Oid.t_char) + vals << &char(&(d as i8)) + lens << int(sizeof(i8)) + formats << 1 + } + i16 { + types << u32(Oid.t_int2) + num := conv.htn16(unsafe { &u16(&data) }) + vals << &char(&num) + lens << int(sizeof(i16)) + formats << 1 + } + int { + types << u32(Oid.t_int4) + num := conv.htn32(unsafe { &u32(&data) }) + vals << &char(&num) + lens << int(sizeof(int)) + formats << 1 + } + i64 { + types << u32(Oid.t_int8) + num := conv.htn64(unsafe { &u64(&data) }) + vals << &char(&num) + lens << int(sizeof(i64)) + formats << 1 + } + f32 { + types << u32(Oid.t_float4) + vals << &char(unsafe { &f32(&(d as f32)) }) + lens << int(sizeof(f32)) + formats << 1 + } + f64 { + types << u32(Oid.t_float8) + vals << &char(unsafe { &f64(&(d as f64)) }) + lens << int(sizeof(f64)) + formats << 1 + } + string { + types << u32(Oid.t_text) + vals << data.str + lens << data.len + formats << 0 + } + time.Time { + types << u32(Oid.t_int4) + vals << &char(&int(data.unix)) + lens << int(sizeof(u32)) + formats << 1 + } + orm.InfixType { + pg_stmt_match(mut types, mut vals, mut lens, mut formats, data.right) + } + } +} + +fn pg_type_from_v(typ int) ?string { + str := match typ { + 6, 10 { + 'SMALLINT' + } + 7, 11, orm.time { + 'INT' + } + 8, 12 { + 'BIGINT' + } + 13 { + 'REAL' + } + 14 { + 'DOUBLE PRECISION' + } + orm.string { + 'TEXT' + } + -1 { + 'SERIAL' + } + else { + '' + } + } + if str == '' { + return error('Unknown type $typ') + } + return str +} + +fn str_to_primitive(str string, typ int) ?orm.Primitive { + match typ { + // bool + 16 { + return orm.Primitive(str.i8() == 1) + } + // i8 + 5 { + return orm.Primitive(str.i8()) + } + // i16 + 6 { + return orm.Primitive(str.i16()) + } + // int + 7 { + return orm.Primitive(str.int()) + } + // i64 + 8 { + return orm.Primitive(str.i64()) + } + // byte + 9 { + data := str.i8() + return orm.Primitive(*unsafe { &byte(&data) }) + } + // u16 + 10 { + data := str.i16() + return orm.Primitive(*unsafe { &u16(&data) }) + } + // u32 + 11 { + data := str.int() + return orm.Primitive(*unsafe { &u32(&data) }) + } + // u64 + 12 { + data := str.i64() + return orm.Primitive(*unsafe { &u64(&data) }) + } + // f32 + 13 { + return orm.Primitive(str.f32()) + } + // f64 + 14 { + return orm.Primitive(str.f64()) + } + orm.string { + return orm.Primitive(str) + } + orm.time { + timestamp := str.int() + return orm.Primitive(time.unix(timestamp)) + } + else {} + } + return error('Unknown field type $typ') +} diff --git a/v_windows/v/vlib/pg/pg.v b/v_windows/v/vlib/pg/pg.v new file mode 100644 index 0000000..9e2d7e7 --- /dev/null +++ b/v_windows/v/vlib/pg/pg.v @@ -0,0 +1,277 @@ +module pg + +import io + +#flag -lpq +#flag linux -I/usr/include/postgresql +#flag darwin -I/opt/local/include/postgresql11 +#flag windows -I @VEXEROOT/thirdparty/pg/include +#flag windows -L @VEXEROOT/thirdparty/pg/win64 + +// PostgreSQL Source Code +// https://doxygen.postgresql.org/libpq-fe_8h.html +#include <libpq-fe.h> +// for orm +#include <arpa/inet.h> + +pub struct DB { +mut: + conn &C.PGconn +} + +pub struct Row { +pub mut: + vals []string +} + +struct C.PGResult { +} + +pub struct Config { +pub: + host string + port int = 5432 + user string + password string + dbname string +} + +fn C.PQconnectdb(a &byte) &C.PGconn + +fn C.PQerrorMessage(voidptr) &byte + +fn C.PQgetvalue(&C.PGResult, int, int) &byte + +fn C.PQstatus(voidptr) int + +fn C.PQresultStatus(voidptr) int + +fn C.PQntuples(&C.PGResult) int + +fn C.PQnfields(&C.PGResult) int + +fn C.PQexec(voidptr, &byte) &C.PGResult + +// Params: +// const Oid *paramTypes +// const char *const *paramValues +// const int *paramLengths +// const int *paramFormats +fn C.PQexecParams(conn voidptr, command &byte, nParams int, paramTypes int, paramValues &byte, paramLengths int, paramFormats int, resultFormat int) &C.PGResult + +fn C.PQputCopyData(conn voidptr, buffer &byte, nbytes int) int + +fn C.PQputCopyEnd(voidptr, &byte) int + +fn C.PQgetCopyData(conn voidptr, buffer &&byte, async int) int + +fn C.PQclear(&C.PGResult) voidptr + +fn C.PQfreemem(voidptr) + +fn C.PQfinish(voidptr) + +// connect makes a new connection to the database server using +// the parameters from the `Config` structure, returning +// a connection error when something goes wrong +pub fn connect(config Config) ?DB { + conninfo := 'host=$config.host port=$config.port user=$config.user dbname=$config.dbname password=$config.password' + conn := C.PQconnectdb(conninfo.str) + if conn == 0 { + return error('libpq memory allocation error') + } + status := C.PQstatus(conn) + if status != C.CONNECTION_OK { + // We force the construction of a new string as the + // error message will be freed by the next `PQfinish` + // call + c_error_msg := unsafe { C.PQerrorMessage(conn).vstring() } + error_msg := '$c_error_msg' + C.PQfinish(conn) + return error('Connection to a PG database failed: $error_msg') + } + return DB{ + conn: conn + } +} + +fn res_to_rows(res voidptr) []Row { + nr_rows := C.PQntuples(res) + nr_cols := C.PQnfields(res) + + mut rows := []Row{} + for i in 0 .. nr_rows { + mut row := Row{} + for j in 0 .. nr_cols { + val := C.PQgetvalue(res, i, j) + sval := unsafe { val.vstring() } + row.vals << sval + } + rows << row + } + + C.PQclear(res) + return rows +} + +// close frees the underlying resource allocated by the database connection +pub fn (db DB) close() { + C.PQfinish(db.conn) +} + +// q_int submit a command to the database server and +// returns an the first field in the first tuple +// converted to an int. If no row is found or on +// command failure, an error is returned +pub fn (db DB) q_int(query string) ?int { + rows := db.exec(query) ? + if rows.len == 0 { + return error('q_int "$query" not found') + } + row := rows[0] + if row.vals.len == 0 { + return 0 + } + val := row.vals[0] + return val.int() +} + +// q_string submit a command to the database server and +// returns an the first field in the first tuple +// as a string. If no row is found or on +// command failure, an error is returned +pub fn (db DB) q_string(query string) ?string { + rows := db.exec(query) ? + if rows.len == 0 { + return error('q_string "$query" not found') + } + row := rows[0] + if row.vals.len == 0 { + return '' + } + val := row.vals[0] + return val +} + +// q_strings submit a command to the database server and +// returns the resulting row set. Alias of `exec` +pub fn (db DB) q_strings(query string) ?[]Row { + return db.exec(query) +} + +// exec submit a command to the database server and wait +// for the result, returning an error on failure and a +// row set on success +pub fn (db DB) exec(query string) ?[]Row { + res := C.PQexec(db.conn, query.str) + return db.handle_error_or_result(res, 'exec') +} + +fn rows_first_or_empty(rows []Row) ?Row { + if rows.len == 0 { + return error('no row') + } + return rows[0] +} + +pub fn (db DB) exec_one(query string) ?Row { + res := C.PQexec(db.conn, query.str) + e := unsafe { C.PQerrorMessage(db.conn).vstring() } + if e != '' { + return error('pg exec error: "$e"') + } + row := rows_first_or_empty(res_to_rows(res)) ? + return row +} + +// exec_param_many executes a query with the provided parameters +pub fn (db DB) exec_param_many(query string, params []string) ?[]Row { + mut param_vals := []&char{len: params.len} + for i in 0 .. params.len { + param_vals[i] = params[i].str + } + + res := C.PQexecParams(db.conn, query.str, params.len, 0, param_vals.data, 0, 0, 0) + return db.handle_error_or_result(res, 'exec_param_many') +} + +pub fn (db DB) exec_param2(query string, param string, param2 string) ?[]Row { + return db.exec_param_many(query, [param, param2]) +} + +pub fn (db DB) exec_param(query string, param string) ?[]Row { + return db.exec_param_many(query, [param]) +} + +fn (db DB) handle_error_or_result(res voidptr, elabel string) ?[]Row { + e := unsafe { C.PQerrorMessage(db.conn).vstring() } + if e != '' { + C.PQclear(res) + return error('pg $elabel error:\n$e') + } + return res_to_rows(res) +} + +// copy_expert execute COPY commands +// https://www.postgresql.org/docs/9.5/libpq-copy.html +pub fn (db DB) copy_expert(query string, file io.ReaderWriter) ?int { + res := C.PQexec(db.conn, query.str) + status := C.PQresultStatus(res) + + defer { + C.PQclear(res) + } + + e := unsafe { C.PQerrorMessage(db.conn).vstring() } + if e != '' { + return error('pg copy error:\n$e') + } + + if status == C.PGRES_COPY_IN { + mut buf := []byte{len: 4 * 1024} + for { + n := file.read(mut buf) or { + msg := 'pg copy error: Failed to read from input' + C.PQputCopyEnd(db.conn, msg.str) + return err + } + if n <= 0 { + break + } + + code := C.PQputCopyData(db.conn, buf.data, n) + if code == -1 { + return error('pg copy error: Failed to send data, code=$code') + } + } + + code := C.PQputCopyEnd(db.conn, 0) + + if code != 1 { + return error('pg copy error: Failed to finish copy command, code: $code') + } + } else if status == C.PGRES_COPY_OUT { + for { + address := &byte(0) + n_bytes := C.PQgetCopyData(db.conn, &address, 0) + if n_bytes > 0 { + mut local_buf := []byte{len: n_bytes} + unsafe { C.memcpy(&byte(local_buf.data), address, n_bytes) } + file.write(local_buf) or { + C.PQfreemem(address) + return err + } + } else if n_bytes == -1 { + break + } else if n_bytes == -2 { + // consult PQerrorMessage for the reason + return error('pg copy error: read error') + } + if address != 0 { + C.PQfreemem(address) + } + } + } + + return 0 +} diff --git a/v_windows/v/vlib/pg/pg_orm_test.v b/v_windows/v/vlib/pg/pg_orm_test.v new file mode 100644 index 0000000..eb0fd12 --- /dev/null +++ b/v_windows/v/vlib/pg/pg_orm_test.v @@ -0,0 +1,77 @@ +module main + +import orm +import pg + +fn test_pg_orm() { + mut db := pg.connect( + host: 'localhost' + user: 'postgres' + password: '' + dbname: 'postgres' + ) or { panic(err) } + + db.create('Test', [ + orm.TableField{ + name: 'id' + typ: 7 + attrs: [ + StructAttribute{ + name: 'primary' + }, + StructAttribute{ + name: 'sql' + has_arg: true + kind: .plain + arg: 'serial' + }, + ] + }, + orm.TableField{ + name: 'name' + typ: 18 + attrs: [] + }, + orm.TableField{ + name: 'age' + typ: 7 + }, + ]) or { panic(err) } + + db.insert('Test', orm.QueryData{ + fields: ['name', 'age'] + data: [orm.string_to_primitive('Louis'), orm.int_to_primitive(101)] + }) or { panic(err) } + + res := db.@select(orm.SelectConfig{ + table: 'Test' + has_where: true + fields: ['id', 'name', 'age'] + types: [7, 18, 8] + }, orm.QueryData{}, orm.QueryData{ + fields: ['name'] + data: [orm.Primitive('Louis'), i64(101)] + types: [18] + is_and: [true] + kinds: [.eq] + }) or { panic(err) } + + id := res[0][0] + name := res[0][1] + age := res[0][2] + + assert id is int + if id is int { + assert id == 1 + } + + assert name is string + if name is string { + assert name == 'Louis' + } + + assert age is i64 + if age is i64 { + assert age == 101 + } +} |