util/pgsql.c

   1 #include <stdio.h>
   2 #include <libex/list.h>
   3 #include "libpgcli/pgconn.h"
   4 #include "linenoise.h"
   5
   6 #define NULL_STR "<null>"
   7
   8 static void usage (const char *progname) {
   9     dprintf(STDERR_FILENO, "usage: %s <connection_string>\n", progname);
  10     exit(1);
  11 }
  12
  13 static void print_server_state (pgconn_t *conn) {
  14     printf("Date style: %s\n", conn->date_style ? conn->date_style : "");
  15     printf("Client encoding: %s\n", conn->client_encoding ? conn->client_encoding : "");
  16     printf("Server encoding: %s\n", conn->server_encoding  ? conn->server_encoding : "");
  17     printf("Session authorization: %s\n", conn->session_auth ? conn->session_auth : "");
  18     printf("Integer datetimes: %s\n", 1 == conn->is_int_datetimes ? "yes" : "no");
  19     printf("Superuser: %s\n", 1 == conn->is_superuser ? "yes" : "no");
  20     printf("Server version: %d.%d\n", conn->major_ver, conn->minor_ver);
  21     printf("Server time zone: %s\n", conn->timezone);
  22 }
  23
  24 static int print_error (pgconn_t *conn) {
  25     int rc;
  26     pgerror_t e;
  27     if (-1 == (rc = pg_error(conn, &e)))
  28         dprintf(STDERR_FILENO, "%s: %s; CODE: %s\n", e.text, e.msg, e.code);
  29     return rc;
  30 }
  31
  32 static int exec_sql (pgconn_t *conn, const char *sql, size_t sql_len) {
  33     if (pg_execsql(conn, sql, sql_len)) {
  34         print_error(conn);
  35         pg_close(conn);
  36         return -1;
  37     }
  38     if (pg_nflds(conn) > 0) {
  39         int *lens = malloc(sizeof(int) * pg_nflds(conn));
  40         for (int i = 0; i < pg_nflds(conn); ++i)
  41             lens[i] = sizeof(NULL_STR)-1;
  42         for (int i = 0; i < pg_nrecs(conn); ++i) {
  43             for (int j = 0; j < pg_nflds(conn); ++j) {
  44                 if (!pg_isnull(conn, i, j)) {
  45                     strptr_t s = pg_getstr(conn, i, j);
  46                     if (lens[j] < s.len) lens[j] = s.len;
  47                     free(s.ptr);
  48                 }
  49             }
  50         }
  51         for (int i = 0; i < pg_nrecs(conn); ++i) {
  52             for (int j = 0; j < pg_nflds(conn); ++j) {
  53                 str_t *str;
  54                 if (pg_isnull(conn, i, j))
  55                     str = mkstr(CONST_STR_LEN(NULL_STR), 0);
  56                 else {
  57                     strptr_t s = pg_getstr(conn, i, j);
  58                     str = mkstr(s.ptr, s.len, 0);
  59                     free(s.ptr);
  60                 }
  61                 strpad(&str, lens[j], ' ', STR_LEFT);
  62                 if (0 == j)
  63                     printf("|%s|", str->ptr);
  64                 else
  65                     printf("%s|", str->ptr);
  66                 free(str);
  67             }
  68             printf("\n");
  69         }
  70         free(lens);
  71     }
  72     if (conn->complete)
  73         printf("%s\n", conn->complete->tag);
  74     pg_close(conn);
  75     return 0;
  76 }
  77
  78 static void usage_show () {
  79     printf("usage:\n"
  80         "show server state\n"
  81         "show schemas\n"
  82         "show tables\n"
  83         "show table <table_name>\n");
  84 }
  85
  86 #define SQL_TABLE "select ordinal_position, column_name, is_nullable, udt_name, character_maximum_length " \
  87     "from information_schema.columns " \
  88     "where table_name = '%s'"
  89 static void show_table (pgconn_t *conn, char **str, size_t *len, strptr_t *t) {
  90     if (-1 == strntok(str, len, CONST_STR_LEN(STR_SPACES), t)) {
  91         printf("usage: show table_name\n");
  92         return;
  93     }
  94     char *table_name = strndup(t->ptr, t->len);
  95     str_t *sql = strprintf(SQL_TABLE, table_name);
  96     exec_sql(conn, sql->ptr, sql->len);
  97     free(sql);
  98     free(table_name);
  99 }
 100
 101 static void show_server (pgconn_t *conn, char **str, size_t *len, strptr_t *t) {
 102     if (-1 == strntok(str, len, CONST_STR_LEN(STR_SPACES), t)) {
 103         printf("usage: show server state\n");
 104         return;
 105     }
 106     print_server_state(conn);
 107 }
 108
 109 #define SQL_SCHEMAS "select * from pg_catalog.pg_namespace"
 110 #define SQL_TABLES "select distinct i.table_schema, i.table_name " \
 111     "from information_schema.tables i, pg_catalog.pg_tables p " \
 112     "where i.table_schema = p.schemaname " \
 113     "and tableowner <> 'postgres' " \
 114     "and table_type = 'BASE TABLE'"
 115 static void show (pgconn_t *conn, char **str) {
 116     size_t len = strlen(*str);
 117     strptr_t t = { .ptr = NULL, .len = 0 };
 118     strntok(str, &len, CONST_STR_LEN(STR_SPACES), &t);
 119     if (-1 == strntok(str, &len, CONST_STR_LEN(STR_SPACES), &t)) {
 120         usage_show();
 121         return;
 122     }
 123     if (!cmpstr(t.ptr, t.len, CONST_STR_LEN("schemas")))
 124         exec_sql(conn, CONST_STR_LEN(SQL_SCHEMAS));
 125     else if (!cmpstr(t.ptr, t.len, CONST_STR_LEN("tables")))
 126         exec_sql(conn, CONST_STR_LEN(SQL_TABLES));
 127     else if (!cmpstr(t.ptr, t.len, CONST_STR_LEN("table")))
 128         show_table(conn, str, &len, &t);
 129     else if (!cmpstr(t.ptr, t.len, CONST_STR_LEN("server")))
 130         show_server(conn, str, &len, &t);
 131     else
 132         usage_show();
 133 }
 134
 135 int main (int argc, const char *argv[]) {
 136     if (argc < 2 || argc > 3) usage(argv[0]);
 137     pgconn_t *conn = pg_connect(argv[1]);
 138     if (0 == print_error(conn) && PG_IDLE == pg_ready(conn)) {
 139         int is_quit = 0;
 140         char *homedir = getenv("HOME");
 141         str_t *hist = NULL;
 142         if (homedir) {
 143             hist = strprintf("%s/.pgsql_history", homedir);
 144             linenoiseHistoryLoadFile(hist->ptr);
 145         }
 146         if (3 == argc)
 147             exec_sql(conn, argv[2], strlen(argv[2]));
 148         else while (!is_quit) {
 149             str_t *sql = NULL;
 150             char *line;
 151             while ((line = linenoise(sql ? "# " : "> ", NULL))) {
 152                 if (sql)
 153                     strnadd(&sql, line, strlen(line));
 154                 else {
 155                     if (!strcmp(line, "quit") || !strcmp(line, "exit")) {
 156                         is_quit = 1;
 157                         break;
 158                     } else if (!strncmp(line, "show", 4) && isspace(*(line+4))) {
 159                         show(conn, &line);
 160                         continue;
 161                     }
 162                     sql = mkstr(line, strlen(line), 0);
 163                 }
 164                 char *e = sql->ptr + sql->len - 1;
 165                 while (e > sql->ptr && isspace(*e)) --e;
 166                 if (';' == *e) {
 167                     *e = ' ';
 168                     break;
 169                 } else
 170                     strnadd(&sql, CONST_STR_LEN(" "));
 171             }
 172             if (!is_quit && sql) {
 173                 if (0 == exec_sql(conn, sql->ptr, sql->len)) {
 174                     sql->ptr[sql->len-1] = ';';
 175                     linenoiseHistoryAdd(sql->ptr);
 176                 }
 177             }
 178             if (sql) free(sql);
 179             sql = NULL;
 180         }
 181         if (hist) {
 182             linenoiseHistorySaveFile(hist->ptr);
 183             free(hist);
 184         }
 185     }
 186     pg_disconnect(conn);
 187     return 0;
 188 }