test/pg.c

   1 #include "pgconn.h"
   2
   3 static void print_server_state (pgconn_t *conn) {
   4     printf("Date style: %s\n", conn->date_style ? conn->date_style : "");
   5     printf("Client encoding: %s\n", conn->client_encoding ? conn->client_encoding : "");
   6     printf("Server encoding: %s\n", conn->server_encoding  ? conn->server_encoding : "");
   7     printf("Session authorization: %s\n", conn->session_auth ? conn->session_auth : "");
   8     printf("Integer datetimes: %s\n", 1 == conn->is_int_datetimes ? "yes" : "no");
   9     printf("Superuser: %s\n", 1 == conn->is_superuser ? "yes" : "no");
  10     printf("Server version: %d.%d\n", conn->major_ver, conn->minor_ver);
  11 }
  12
  13 static void print_server_error(pgconn_t *conn) {
  14     printf("%s CODE: %s; %s\n", conn->error->text, conn->error->code, conn->error->message);
  15 }
  16
  17 static void out_result (pgconn_t *conn) {
  18     for (int i = 0; i < pg_nrecs(conn); ++i) {
  19         strptr_t s;
  20         unsigned char *u;
  21         char uuid [UUID_STR_LEN];
  22         str_t *str;
  23         mpq_t n;
  24         int64_t m;
  25         struct tm tm;
  26         uint16_t dscale;
  27         printf("id: %d; ", pg_geti32(conn, i, 0));
  28         s = pg_getstr(conn, i, 1);
  29         printf("name: %s; ", s.ptr);
  30         free(s.ptr);
  31         printf("idb: %ld; ", pg_geti64(conn, i, 2));
  32         printf("fl: %.*f; ", 6, pg_getf(conn, i, 3));
  33         printf("dbl: %.*f; ", 14, pg_getd(conn, i, 4));
  34         printf("b: %d; ", pg_getbool(conn, i, 5));
  35         u = pg_getuuid(conn, i, 6);
  36         uuid_unparse(u, uuid);
  37         printf("uu: %s; ", uuid);
  38         printf("bi: 0x%08X; ", pg_getx32(conn, i, 7));
  39         m = pg_geti64(conn, i, 8);
  40         printf("m: %ld.%ld; ", m/100, m%100);
  41         s = pg_getstr(conn, i, 9);
  42         printf("t: %s; ", s.ptr);
  43         free(s.ptr);
  44         pg_getnum(conn, i, 10, n, &dscale);
  45         str = pg_numstr(n, 10, dscale, 6);
  46         printf("n: %s; ", str->ptr);
  47         free(str);
  48         mpq_clear(n);
  49         date_to_tm(pg_getdate(conn, i, 11), &tm);
  50         printf("d: %d-%02d-%02d; ", tm.tm_year, tm.tm_mon, tm.tm_mday);
  51         timestamp_to_tm(pg_gettm(conn, i, 12), &tm);
  52         printf("tm: %d-%02d-%02d %02d:%02d:%02d\n", tm.tm_year, tm.tm_mon, tm.tm_mday, tm.tm_hour, tm.tm_min, tm.tm_sec);
  53     }
  54 }
  55
  56
  57 static void sql00 (pgconn_t *conn) {
  58     printf("\ninsert 100 records using prepared query\n");
  59     pgfld_t a_id = PG_DEF_INT,
  60             a_name = PG_DEF_VARCHAR,
  61             a_idb = PG_DEF_BIGINT,
  62             a_fl = PG_DEF_FLOAT,
  63             a_dbl = PG_DEF_DOUBLE,
  64             a_bool = PG_DEF_BOOL,
  65             a_uu = PG_DEF_UUID,
  66             a_bi = PG_DEF_BIT32,
  67             a_m = PG_DEF_MONEY,
  68             a_t = PG_DEF_TEXT,
  69             a_n = PG_DEF;
  70     if (0 == pg_prepare(conn, "insert into t1 (id, name, idb, fl, dbl, b, uu, bi, m, t, n) values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)", 0,
  71                         &a_id, &a_name, &a_idb, &a_fl, &a_dbl, &a_bool, &a_uu, &a_bi, &a_m, &a_t, &a_n, NULL)) {
  72         for (int i = 1; i < 100; ++i) {
  73             float f = i;
  74             char buf_name [64], buf_n [64];
  75             int len_name = snprintf(buf_name, sizeof buf_name, "name%d", i),
  76                 len_n = snprintf(buf_n, sizeof buf_n, "%f", f + 1/f);
  77             uuid_t uuid;
  78             uuid_generate(uuid);
  79             f = f + 1/f;
  80             PG_SET_INT(a_id, i);
  81             PG_SET_VARCHAR(a_name, buf_name, len_name);
  82             PG_SET_BIGINT(a_idb, i);
  83             PG_SET_FLOAT(a_fl, f);
  84             PG_SET_DOUBLE(a_dbl, f);
  85             PG_SET_BOOL(a_bool, 0 == i % 2);
  86             PG_SET_UUID(a_uu, uuid);
  87             PG_SET_BIT32(a_bi, i);
  88             PG_SET_MONEY(a_m, i*100+i);
  89             PG_SET_TEXT(a_t, buf_name, len_name);
  90             PG_SET(a_n, buf_n, len_n);
  91             pg_exec(conn, PG_FULL, PG_BIN, &a_id, &a_name, &a_idb, &a_fl, &a_dbl, &a_bool, &a_uu, &a_bi, &a_m, &a_t, &a_n, NULL);
  92         }
  93     }
  94     pg_close(conn);
  95 }
  96
  97 static void sql01 (pgconn_t *conn) {
  98     printf("\nsimple select\n");
  99     if (0 == pg_execsql(conn, "select id, name from t1", 0)) {
 100         for (int i = 0; i < pg_nrecs(conn); ++i) {
 101             for (int j = 0; j < pg_nflds(conn); ++j) {
 102                 if (pg_isnull(conn, i, j))
 103                     printf("null; ");
 104                 else {
 105                     strptr_t s = pg_getstr(conn, i, j);
 106                     printf("%s; ", s.ptr);
 107                     free(s.ptr);
 108                 }
 109             }
 110             printf("\n");
 111         }
 112     }
 113     pg_close(conn);
 114 }
 115
 116 static void sql02 (pgconn_t *conn) {
 117     printf("\nselect with parameter\n");
 118     pgfld_t a_id = PG_DEF_INT;
 119     PG_SET_INT(a_id, 35);
 120     if (0 == pg_prepare(conn, "select id, name, idb, fl, dbl, b, uu, bi, m, t, n, d, tm from t1 where id = $1", 0, &a_id, NULL) &&
 121         0 == pg_exec(conn, PG_FULL, PG_BIN, &a_id, NULL))
 122         out_result(conn);
 123     pg_close(conn);
 124 }
 125
 126 static void sql03 (pgconn_t *conn) {
 127     pgfld_t a_id = PG_DEF_INT;
 128     printf("\nselect by portions\n");
 129     PG_SET_INT(a_id, 51);
 130     pg_execsql(conn, "start transaction", 0);
 131     if (0 == pg_prepare(conn, "select id, name, idb, fl, dbl, b, uu, bi, m, t, n, d, tm from t1 where id > $1", 0, &a_id, NULL) &&
 132         0 == pg_exec(conn, 7, PG_BIN, &a_id, NULL)) {
 133         while (conn->suspended) {
 134             out_result(conn);
 135             printf("- next\n");
 136             pg_next(conn, 7);
 137         };
 138         out_result(conn);
 139     }
 140     pg_execsql(conn, "commit", 0);
 141     pg_close(conn);
 142 }
 143
 144 static void sql04 (pgconn_t *conn) {
 145     pgfld_t a_id1 = PG_DEF_INT, a_id2 = PG_DEF_INT;
 146     printf("\nselect with parameter and text output\n");
 147     PG_SET_INT(a_id1, 5);
 148     PG_SET_INT(a_id2, 10);
 149     if (0 == pg_prepare(conn, "select * from t1 where id between $1 and $2", 0, &a_id1, &a_id2, NULL) &&
 150         0 == pg_exec(conn, PG_FULL, PG_TEXT, &a_id1, &a_id2, NULL)) {
 151         for (int i = 0; i < pg_nrecs(conn); ++i) {
 152             for (int j = 0; j < pg_nflds(conn); ++j) {
 153                 if (pg_isnull(conn, i, j))
 154                     printf("null; ");
 155                 else {
 156                     strptr_t s = pg_getstr(conn, i, j);
 157                     printf("%s; ", s.ptr);
 158                     free(s.ptr);
 159                 }
 160             }
 161             printf("\n");
 162         }
 163     }
 164     pg_close(conn);
 165 }
 166
 167 static void sql05 (pgconn_t *conn) {
 168     printf("\nerror\n");
 169     pgfld_t a_id = PG_DEF_INT;
 170     PG_SET_INT(a_id, 1);
 171     if (-1 == pg_prepare(conn, "select id, name from t2 where id = $1", 0, &a_id, NULL) ||
 172         -1 == pg_exec(conn, PG_FULL, PG_BIN, &a_id, NULL)) {
 173         pgmsg_error_t *e = conn->error;
 174         printf("TYPE: %c\nSEVERITY: %s\nTEXT: %s\nCODE: %s\nMESSAGE: %s\nFILE: %s\nLINE: %s\nROUTINE: %s\n", e->type, e->severity, e->text, e->code, e->message, e->file, e->line, e->routine);
 175     }
 176     pg_close(conn);
 177 }
 178
 179 int main (int argc, const char *argv[]) {
 180     pgconn_t *conn = pg_connect("dbname=pgtest host=127.0.0.1");
 181     if (conn->error)
 182         print_server_error(conn);
 183     else
 184     if (conn->pg_error)
 185         printf("%s\n", pg_strerror(conn));
 186     else
 187     if (PG_IDLE == pg_ready(conn)) {
 188         print_server_state(conn);
 189         sql00(conn);
 190         sql01(conn);
 191         sql02(conn);
 192         sql03(conn);
 193         sql04(conn);
 194         sql05(conn);
 195     }
 196     pg_disconnect(conn);
 197     return 0;
 198 }