db.pg #

Description

pg is a wrapper for the PostgreSQL client library. It provides access to a PostgreSQL database server.

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.

Fedora 31

sudo dnf install postgresql-server postgresql-contrib
sudo systemctl enable postgresql # to autostart on startup
sudo systemctl start  postgresql

Ubuntu/Debian

sudo apt 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

MacOSX (MacPorts)

gem install pg -- --with-pg-config=/opt/local/lib/postgresql[version number]/bin/pg_config

Installing libpq-dev or its equivalent for your OS:

Ubuntu/Debian: sudo apt install libpq-dev

Red Hat Linux (RHEL): yum install postgresql-devel

OpenSuse: zypper in postgresql-devel

ArchLinux: pacman -S postgresql-libs

Windows:

1. Download PostgreSQL SDK from official site
2. Extract archive to postgres-master folder
3. Copy folder postgres-master/src/interfaces/libpq to v/thirdparty/pg
4. Copy file postgres-master/src/include/postgres_ext.h to v/thirdparty/pg/libpq

If you build PostgreSQL from source pg_config_ext.h and pg_config.h will be created automatically:
5. Copy file postgres-master/src/include/pg_config_ext.h to v/thirdparty/pg/libpq
6. Copy file postgres-master/src/include/pg_config.h to v/thirdparty/pg/libpq

If you do not build PostgreSQL from source code:
5. Copy file postgres-master/src/include/pg_config_ext.h.in to v/thirdparty/pg/libpq
- rename pg_config_ext.h.in to pg_config_ext.h
- in pg_config_ext.h change line **#undef PG_INT64_TYPE** to **#define PG_INT64_TYPE long int**
6. Copy file postgres-master/src/include/pg_config.h.in to v/thirdparty/pg/libpq
- rename pg_config.h.in to pg_config.h
- in pg_config.h change line **#undef PG_VERSION_NUM** to **#define PG_VERSION_NUM X0Y0Z**
where X is major db version, Y is minor version, Z is patch version. So if your version is 17.1.2
PG_VERSION_NUM will be 170102, PG_VERSION_NUM should be number, dot sign is changing to 0,
format *PG_VERSION_NUM 17* without 0 also should work

7. Add libpq.dll to v/thirdparty/pg/win64

If you are going to use the msvc compiler:
7. Add libpq.lib(C:\Program Files\PostgreSQL\{version}\lib) to v/thirdparty/pg/win64/msvc

8. Add libpq.dll, libcrypto-3-x64.dll, libssl-3-x64.dll to where your executable is.

To get the libpq.dll file, you can install the PostgreSQL database,
and get this dll from its bin/ folder, or compile DB from source code.

Getting Started with PostgreSQL

Read this section to learn how to install and connect to PostgreSQL Windows; Linux; macOS.

Using Parameterized Queries

Parameterized queries (exec_param, etc.) in V require the use of the following syntax: ($n).

The number following the $ specifies which parameter from the argument array to use.

db.exec_param_many('INSERT INTO users (username, password) VALUES ($1, $2)', ['tom', 'securePassword']) or { panic(err) }
db.exec_param('SELECT * FROM users WHERE username = ($1) limit 1', 'tom') or { panic(err) }

fn connect(config Config) !DB

connect makes a new connection to the database server using the parameters from the Config structure, returning a connection error when something goes wrong

fn connect_with_conninfo(conninfo string) !DB

connect_with_conninfo makes a new connection to the database server using the conninfo connection string, returning a connection error when something goes wrong

enum ConnStatusType {
	ok  = C.CONNECTION_OK
	bad = C.CONNECTION_BAD
	// Non-blocking mode only below here
	// The existence of these should never be relied upon - they should only be used for user feedback or similar purposes.
	started           = C.CONNECTION_STARTED           // Waiting for connection to be made.
	made              = C.CONNECTION_MADE              // Connection OK; waiting to send.
	awaiting_response = C.CONNECTION_AWAITING_RESPONSE // Waiting for a response from the postmaster.
	auth_ok           = C.CONNECTION_AUTH_OK           // Received authentication; waiting for backend startup.
	setenv            = C.CONNECTION_SETENV            // Negotiating environment.
	ssl_startup       = C.CONNECTION_SSL_STARTUP       // Negotiating SSL.
	needed            = C.CONNECTION_NEEDED            // Internal state: connect() needed . Available in PG 8
	check_writable    = C.CONNECTION_CHECK_WRITABLE    // Check if we could make a writable connection. Available since PG 10
	consume           = C.CONNECTION_CONSUME           // Wait for any pending message and consume them. Available since PG 10
	gss_startup       = C.CONNECTION_GSS_STARTUP       // Negotiating GSSAPI; available since PG 12
}

enum ExecStatusType {
	empty_query    = C.PGRES_EMPTY_QUERY    // empty query string was executed
	command_ok     = C.PGRES_COMMAND_OK     // a query command that doesn't return anything was executed properly by the backend
	tuples_ok      = C.PGRES_TUPLES_OK      // a query command that returns tuples was executed properly by the backend, PGresult contains the result tuples
	copy_out       = C.PGRES_COPY_OUT       // Copy Out data transfer in progress
	copy_in        = C.PGRES_COPY_IN        // Copy In data transfer in progress
	bad_response   = C.PGRES_BAD_RESPONSE   // an unexpected response was recv'd from the backend
	nonfatal_error = C.PGRES_NONFATAL_ERROR // notice or warning message
	fatal_error    = C.PGRES_FATAL_ERROR    // query failed
	copy_both      = C.PGRES_COPY_BOTH      // Copy In/Out data transfer in progress
	single_tuple   = C.PGRES_SINGLE_TUPLE   // single tuple from larger resultset
}

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
}

struct C.PGconn {}

struct C.PGresult {}

struct C.pg_conn {}

struct C.pg_result {}

struct Config {
pub:
	host     string = 'localhost'
	port     int    = 5432
	user     string
	password string
	dbname   string
}

struct DB {
mut:
	conn voidptr = unsafe { nil }
}

fn (db DB) close()

close frees the underlying resource allocated by the database connection

fn (db DB) copy_expert(query string, mut file io.ReaderWriter) !int

copy_expert executes COPY command https://www.postgresql.org/docs/9.5/libpq-copy.html

fn (db DB) create(table string, fields []orm.TableField) !

create is used internally by V's ORM for processing table creation queries (DDL)

fn (db DB) delete(table string, where orm.QueryData) !

delete is used internally by V's ORM for processing DELETE queries

fn (db DB) drop(table string) !

drop is used internally by V's ORM for processing table destroying queries (DDL)

fn (db DB) exec(query string) ![]Row

exec submits a command to the database server and wait for the result, returning an error on failure and a row set on success

fn (db DB) exec_one(query string) !Row

exec_one executes a query and returns its first row as a result, or an error on failure

fn (db DB) exec_param(query string, param string) ![]Row

exec_param executes a query with 1 parameter ($1), and returns either an error on failure, or the full result set on success

fn (db DB) exec_param2(query string, param string, param2 string) ![]Row

exec_param2 executes a query with 2 parameters ($1) and ($2), and returns either an error on failure, or the full result set on success

fn (db DB) exec_param_many(query string, params []string) ![]Row

exec_param_many executes a query with the parameters provided as ($1), ($2), ($n)

fn (db DB) insert(table string, data orm.QueryData) !

insert is used internally by V's ORM for processing INSERT queries

fn (db DB) last_id() int

last_id is used internally by V's ORM for post-processing INSERT queries

fn (db DB) q_int(query string) !int

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

fn (db DB) q_string(query string) !string

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

fn (db DB) q_strings(query string) ![]Row

q_strings submit a command to the database server and returns the resulting row set. Alias of exec

fn (db DB) select(config orm.SelectConfig, data orm.QueryData, where orm.QueryData) ![][]orm.Primitive

select is used internally by V's ORM for processing SELECT queries

fn (db DB) update(table string, data orm.QueryData, where orm.QueryData) !

update is used internally by V's ORM for processing UPDATE queries

struct Row {
pub mut:
	vals []?string
}