util/pgsql_cmd.c

   1 /*Copyright (c) Brian B.
   2
   3   This library is free software; you can redistribute it and/or
   4   modify it under the terms of the GNU Lesser General Public
   5   License as published by the Free Software Foundation; either
   6   version 3 of the License, or (at your option) any later version.
   7   See the file LICENSE included with this distribution for more
   8   information.
   9 */
  10 #include "pgsql_cmd.h"
  11
  12 void show (pgconn_t *conn, char **line, size_t *line_len) {
  13     strptr_t tok = { .ptr = NULL, .len = 0 };
  14     strntok(line, line_len, CONST_STR_LEN(STR_SPACES), &tok);
  15     if (-1 == strntok(line, line_len, CONST_STR_LEN(STR_SPACES), &tok))
  16         return;
  17     if (0 == cmpstr(tok.ptr, tok.len, CONST_STR_LEN("table")))
  18         show_table(conn, line, line_len);
  19     else if (0 == cmpstr(tok.ptr, tok.len, CONST_STR_LEN("tables")))
  20         show_tables(conn);
  21 }
  22
  23 cstr_t *get_current_schema (pgconn_t *conn) {
  24     pg_execsql(conn, CONST_STR_LEN("select current_schema()"));
  25     return pg_getstr(conn, 0, 0);
  26 }
  27
  28 cstr_t *get_dbsize (pgconn_t *conn, char *dbname) {
  29     str_t *sql = strprintf("SELECT pg_size_pretty(pg_database_size('%s'))", dbname);
  30     pg_execsql(conn, sql->ptr, sql->len);
  31     cstr_t *res = pg_getstr(conn, 0, 0);
  32     free(sql);
  33     return res;
  34 }
  35
  36 cstr_t *get_tablesize (pgconn_t *conn, char *schema, char *table) {
  37     str_t *sql = strprintf("SELECT pg_size_pretty(pg_total_relation_size('%s.%s'))",
  38         schema, table);
  39     pg_execsql(conn, sql->ptr, sql->len);
  40     cstr_t *res = pg_getstr(conn, 0, 0);
  41     free(sql);
  42     return res;
  43 }
  44
  45 cstr_t *get_tablerecs (pgconn_t *conn, char *schema, char *table) {
  46     str_t *sql = strprintf("SELECT count(*) from %s.%s",
  47         schema, table);
  48     pg_execsql(conn, sql->ptr, sql->len);
  49     cstr_t *res = pg_getstr(conn, 0, 0);
  50     free(sql);
  51     return res;
  52 }
  53
  54 static int *get_max_lens (pgconn_t *conn) {
  55     int *max_lens = calloc(pg_nrecs(conn), sizeof(int));
  56
  57     for (int i = 0; i < pg_nrecs(conn); ++i)
  58         for (int j = 0; j < pg_nflds(conn); ++j)
  59             if (!pg_isnull(conn, i, j) && pg_fldlen(conn, i, j) > max_lens[j])
  60                 max_lens[j] = pg_fldlen(conn, i, j);
  61
  62     for (int i = 0; i < pg_nflds(conn); ++i) {
  63         int l = strlen(conn->rowdesc->fields[i].fname);
  64         if (l > max_lens[i]) max_lens[i] = l;
  65     }
  66
  67     return max_lens;
  68 }
  69
  70 static void print_field_names (pgconn_t *conn, int *max_lens) {
  71     int max_len = 0;
  72     for (int i = 0; i < pg_nflds(conn); ++i) {
  73         const char *fldnam = conn->rowdesc->fields[i].fname;
  74         cstr_t *s = mkcstrpad(fldnam, strlen(fldnam), ' ', max_lens[i]);
  75         max_len += max_lens[i];
  76         for (int j = 0; j < s->len; ++j)
  77             s->ptr[j] = toupper(s->ptr[j]);
  78         if (0 == i)
  79             printf("|%s|", s->ptr);
  80         else
  81             printf("%s|", s->ptr);
  82         free(s);
  83     }
  84     cstr_t *s = mkcstrfill(max_len+pg_nflds(conn), '-');
  85     printf("\n%s\n", s->ptr);
  86     free(s);
  87 }
  88
  89 void print_result (pgconn_t *conn) {
  90     int *max_lens = get_max_lens(conn);
  91
  92     print_field_names(conn, max_lens);
  93     for (int i = 0; i < pg_nrecs(conn); ++i) {
  94         for (int j = 0; j < pg_nflds(conn); ++j) {
  95             cstr_t *str;
  96             if (pg_isnull(conn, i, j))
  97                 str = mkcstrfill(max_lens[j], ' ');
  98             else {
  99                 strptr_t s = pg_get(conn, i, j);
 100                 str = mkcstrpad(s.ptr, s.len, ' ', max_lens[j]);
 101             }
 102             if (0 == j)
 103                 printf("|%s|", str->ptr);
 104             else
 105                 printf("%s|", str->ptr);
 106             free(str);
 107         }
 108         printf("\n");
 109     }
 110
 111     free(max_lens);
 112 }
 113
 114 typedef struct {
 115     cstr_t *name;
 116     cstr_t *size;
 117 } table_t;
 118 DECLARE_LIST(tables,table_t)
 119 DECLARE_LIST_FUN(tables,table_t)
 120
 121 typedef struct {
 122     int *max_lens;
 123     pgconn_t *conn;
 124 } stmt_info_t;
 125
 126 static void clear_table (table_t *t) {
 127     if (t->name) free(t->name);
 128     if (t->size) free(t->size);
 129 }
 130
 131 static void print_table_field (cstr_t *val, int max_len) {
 132     cstr_t *s;
 133     if (val)
 134         s = mkcstrpad(val->ptr, val->len, ' ', max_len);
 135     else
 136         s = mkcstrfill(max_len, ' ');
 137     printf("|%s", s->ptr);
 138     free(s);
 139 }
 140
 141 static void print_table_size (pgconn_t *conn, cstr_t *table_name) {
 142     str_t *sql = strprintf("select count(*) from %s", table_name->ptr);
 143     cstr_t *s = NULL;
 144     if (0 == pg_execsql(conn, sql->ptr, sql->len))
 145         s = pg_getstr(conn, 0, 0);
 146     if (s) printf("|%s\n", s->ptr); else printf("|0\n");
 147     free(s);
 148     free(sql);
 149 }
 150
 151 static int print_table (table_t *t, void *userdata) {
 152     stmt_info_t *si = userdata;
 153
 154     print_table_field(t->name, si->max_lens[0]);
 155     print_table_field(t->size, si->max_lens[1]);
 156     print_table_size(si->conn, t->name);
 157
 158     return ENUM_CONTINUE;
 159 }
 160
 161 static size_t print_header_col (const char *name, size_t name_len, int len) {
 162     size_t rc;
 163     cstr_t *s = mkcstrpad(name, name_len, ' ', len);
 164     printf("|%s", s->ptr);
 165     rc = s->len;
 166     free(s);
 167     return rc;
 168 }
 169
 170 static void print_tables_header (int *max_lens) {
 171     size_t n = print_header_col(CONST_STR_LEN("NAME"), max_lens[0]);
 172     n += print_header_col(CONST_STR_LEN("SIZE"), max_lens[1]);
 173     n += print_header_col(CONST_STR_LEN("RECORDS"), max_lens[2]);
 174     cstr_t *s = mkcstrfill(n + 3, '-');
 175     printf("\n%s\n", s->ptr);
 176     free(s);
 177 }
 178
 179 #define SQL_TABLES \
 180     "select distinct i.table_name," \
 181     "  pg_size_pretty(pg_total_relation_size(i.table_schema || '.' || i.table_name)) " \
 182     "from information_schema.tables i, pg_catalog.pg_tables p " \
 183     "where i.table_schema = p.schemaname" \
 184     "  and tableowner <> 'postgres'" \
 185     "  and table_type = 'BASE TABLE'" \
 186     "  and i.table_schema = current_schema()"
 187 void show_tables (pgconn_t *conn) {
 188     pg_execsql(conn, CONST_STR_LEN(SQL_TABLES));
 189
 190     if (!pg_nrecs(conn)) {
 191         printf("Nothing\n");
 192         return;
 193     }
 194
 195     stmt_info_t si = {
 196         .max_lens = get_max_lens(conn),
 197         .conn = conn,
 198     };
 199     tables_t *tables = create_tables(CF_UNDEFINED, CF_UNDEFINED, CF_UNDEFINED);
 200
 201     tables->on_free = clear_table;
 202     for (int i = 0; i < pg_nrecs(conn); ++i) {
 203         table_t item = {
 204             .name = pg_getstr(conn, i, 0),
 205             .size = pg_getstr(conn, i, 1)
 206         };
 207         add_tables(&tables, &item, CF_TAIL);
 208     }
 209     pg_close(conn);
 210
 211     print_tables_header(si.max_lens);
 212     foreach_tables(tables, print_table, &si, 0);
 213
 214     free_tables(&tables);
 215     free(si.max_lens);
 216 }
 217
 218 #define SQL_TABLE \
 219     "select ordinal_position, column_name, case is_nullable when 'YES' then '' else 'not null' end case, data_type, character_maximum_length " \
 220     "from information_schema.columns " \
 221     "where table_schema = '%s' and table_name = '%s'"
 222 void show_table (pgconn_t *conn, char **line, size_t *line_len) {
 223     str_t *sql;
 224     strptr_t tok = { .ptr = NULL, .len = 0 };
 225     cstr_t *schema, *table;
 226     char *p, *e;
 227
 228     if (-1 == strntok(line, line_len, CONST_STR_LEN(STR_SPACES), &tok))
 229         return;
 230     e = tok.ptr + tok.len;
 231     if ((p = strnchr(tok.ptr, '.', tok.len))) {
 232         schema = mkcstr(tok.ptr, (uintptr_t)p - (uintptr_t)tok.ptr);
 233         table = mkcstr(p+1, (uintptr_t)e - (uintptr_t)p-1);
 234     } else {
 235         schema = get_current_schema(conn);
 236         table = mkcstr(tok.ptr, tok.len);
 237     }
 238
 239     sql = strprintf(SQL_TABLE, schema->ptr, table->ptr);
 240     if (!pg_execsql(conn, sql->ptr, sql->len)) {
 241         if (pg_nrecs(conn))
 242             print_result(conn);
 243         else
 244             printf("Nothing\n");
 245     }
 246     free(sql);
 247     pg_close(conn);
 248
 249     cstr_t *table_size = get_tablesize(conn, schema->ptr, table->ptr),
 250            *table_nrecs = get_tablerecs(conn, schema->ptr, table->ptr);
 251     printf("Table size: %s; Records: %s\n", table_size->ptr, table_nrecs->ptr);
 252     free(table_nrecs);
 253     free(table_size);
 254
 255     free(schema);
 256     free(table);
 257 }