2
* oid2name, a PostgreSQL app to map OIDs on the filesystem
3
* to table and database names.
6
* B. Palmer, bpalmer@crimelabs.net 1-17-2001
8
* contrib/oid2name/oid2name.c
10
#include "postgres_fe.h"
21
/* an extensible array to keep track of elements to show */
29
/* these are the opts structures for command line params */
49
/* function prototypes */
50
static void help(const char *progname);
51
void get_opts(int, char **, struct options *);
52
void *myalloc(size_t size);
53
char *mystrdup(const char *str);
54
void add_one_elt(char *eltname, eary *eary);
55
char *get_comma_elts(eary *eary);
56
PGconn *sql_conn(struct options *);
57
int sql_exec(PGconn *, const char *sql, bool quiet);
58
void sql_exec_dumpalldbs(PGconn *, struct options *);
59
void sql_exec_dumpalltables(PGconn *, struct options *);
60
void sql_exec_searchtables(PGconn *, struct options *);
61
void sql_exec_dumpalltbspc(PGconn *, struct options *);
63
/* function to parse command line options and check for some usage errors. */
65
get_opts(int argc, char **argv, struct options * my_opts)
70
progname = get_progname(argv[0]);
72
/* set the defaults */
73
my_opts->quiet = false;
74
my_opts->systables = false;
75
my_opts->indexes = false;
76
my_opts->nodb = false;
77
my_opts->extended = false;
78
my_opts->tablespaces = false;
79
my_opts->dbname = NULL;
80
my_opts->hostname = NULL;
82
my_opts->username = NULL;
86
if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
91
if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
93
puts("oid2name (PostgreSQL) " PG_VERSION);
99
while ((c = getopt(argc, argv, "H:p:U:d:t:o:f:qSxish")) != -1)
103
/* specify the database */
105
my_opts->dbname = mystrdup(optarg);
108
/* specify one tablename to show */
110
add_one_elt(optarg, my_opts->tables);
113
/* specify one Oid to show */
115
add_one_elt(optarg, my_opts->oids);
118
/* specify one filenode to show */
120
add_one_elt(optarg, my_opts->filenodes);
123
/* don't show headers */
125
my_opts->quiet = true;
128
/* host to connect to */
130
my_opts->hostname = mystrdup(optarg);
133
/* port to connect to on remote host */
135
my_opts->port = mystrdup(optarg);
140
my_opts->username = mystrdup(optarg);
143
/* display system tables */
145
my_opts->systables = true;
148
/* also display indexes */
150
my_opts->indexes = true;
153
/* display extra columns */
155
my_opts->extended = true;
158
/* dump tablespaces only */
160
my_opts->tablespaces = true;
169
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
176
help(const char *progname)
178
printf("%s helps examining the file structure used by PostgreSQL.\n\n"
182
" -d DBNAME database to connect to\n"
183
" -f FILENODE show info for table with given file node\n"
184
" -H HOSTNAME database server host or socket directory\n"
185
" -i show indexes and sequences too\n"
186
" -o OID show info for table with given OID\n"
187
" -p PORT database server port number\n"
188
" -q quiet (don't show headers)\n"
189
" -s show all tablespaces\n"
190
" -S show system objects too\n"
191
" -t TABLE show info for named table\n"
192
" -U NAME connect as specified database user\n"
193
" -x extended (show additional columns)\n"
194
" --help show this help, then exit\n"
195
" --version output version information, then exit\n"
196
"\nThe default action is to show all database OIDs.\n\n"
197
"Report bugs to <pgsql-bugs@postgresql.org>.\n",
204
void *ptr = malloc(size);
208
fprintf(stderr, "out of memory");
215
mystrdup(const char *str)
217
char *result = strdup(str);
221
fprintf(stderr, "out of memory");
230
* Add one element to a (possibly empty) eary struct.
233
add_one_elt(char *eltname, eary *eary)
235
if (eary->alloc == 0)
238
eary ->array = (char **) myalloc(8 * sizeof(char *));
240
else if (eary->num >= eary->alloc)
243
eary ->array = (char **)
244
realloc(eary->array, eary->alloc * sizeof(char *));
248
fprintf(stderr, "out of memory");
253
eary ->array[eary->num] = mystrdup(eltname);
260
* Return the elements of an eary as a (freshly allocated) single string, in
261
* single quotes, separated by commas and properly escaped for insertion in an
265
get_comma_elts(eary *eary)
276
* PQescapeString wants 2 * length + 1 bytes of breath space. Add two
277
* chars per element for the single quotes and one for the comma.
279
for (i = 0; i < eary->num; i++)
280
length += strlen(eary->array[i]);
282
ret = (char *) myalloc(length * 2 + 4 * eary->num);
285
for (i = 0; i < eary->num; i++)
290
ptr += PQescapeString(ptr, eary->array[i], strlen(eary->array[i]));
297
/* establish connection with database. */
299
sql_conn(struct options * my_opts)
302
char *password = NULL;
306
* Start the connection. Loop until we have a password if requested by
312
conn = PQsetdbLogin(my_opts->hostname,
321
fprintf(stderr, "%s: could not connect to database %s\n",
322
"oid2name", my_opts->dbname);
326
if (PQstatus(conn) == CONNECTION_BAD &&
327
PQconnectionNeedsPassword(conn) &&
331
password = simple_prompt("Password: ", 100, false);
339
/* check to see that the backend connection was successfully made */
340
if (PQstatus(conn) == CONNECTION_BAD)
342
fprintf(stderr, "%s: could not connect to database %s: %s",
343
"oid2name", my_opts->dbname, PQerrorMessage(conn));
348
/* return the conn if good */
353
* Actual code to make call to the database and print the output data.
356
sql_exec(PGconn *conn, const char *todo, bool quiet)
369
res = PQexec(conn, todo);
371
/* check and deal with errors */
372
if (!res || PQresultStatus(res) > 2)
374
fprintf(stderr, "oid2name: query failed: %s\n", PQerrorMessage(conn));
375
fprintf(stderr, "oid2name: query was: %s\n", todo);
382
/* get the number of fields */
383
nrows = PQntuples(res);
384
nfields = PQnfields(res);
386
/* for each field, get the needed width */
387
length = (int *) myalloc(sizeof(int) * nfields);
388
for (j = 0; j < nfields; j++)
389
length[j] = strlen(PQfname(res, j));
391
for (i = 0; i < nrows; i++)
393
for (j = 0; j < nfields; j++)
395
l = strlen(PQgetvalue(res, i, j));
397
length[j] = strlen(PQgetvalue(res, i, j));
404
for (j = 0, l = 0; j < nfields; j++)
406
fprintf(stdout, "%*s", length[j] + 2, PQfname(res, j));
409
fprintf(stdout, "\n");
410
pad = (char *) myalloc(l + 1);
413
fprintf(stdout, "%s\n", pad);
417
/* for each row, dump the information */
418
for (i = 0; i < nrows; i++)
420
for (j = 0; j < nfields; j++)
421
fprintf(stdout, "%*s", length[j] + 2, PQgetvalue(res, i, j));
422
fprintf(stdout, "\n");
433
* Dump all databases. There are no system objects to worry about.
436
sql_exec_dumpalldbs(PGconn *conn, struct options * opts)
440
/* get the oid and database name from the system pg_database table */
441
snprintf(todo, sizeof(todo),
442
"SELECT d.oid AS \"Oid\", datname AS \"Database Name\", "
443
"spcname AS \"Tablespace\" FROM pg_catalog.pg_database d JOIN pg_catalog.pg_tablespace t ON "
444
"(dattablespace = t.oid) ORDER BY 2");
446
sql_exec(conn, todo, opts->quiet);
450
* Dump all tables, indexes and sequences in the current database.
453
sql_exec_dumpalltables(PGconn *conn, struct options * opts)
456
char *addfields = ",c.oid AS \"Oid\", nspname AS \"Schema\", spcname as \"Tablespace\" ";
458
snprintf(todo, sizeof(todo),
459
"SELECT pg_catalog.pg_relation_filenode(c.oid) as \"Filenode\", relname as \"Table Name\" %s "
461
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace "
462
" LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database(),"
463
" pg_catalog.pg_tablespace t "
464
"WHERE relkind IN ('r'%s%s) AND "
467
" WHEN reltablespace <> 0 THEN reltablespace"
468
" ELSE dattablespace"
471
opts->extended ? addfields : "",
472
opts->indexes ? ", 'i', 'S'" : "",
473
opts->systables ? ", 't'" : "",
474
opts->systables ? "" : "n.nspname NOT IN ('pg_catalog', 'information_schema') AND n.nspname !~ '^pg_toast' AND");
476
sql_exec(conn, todo, opts->quiet);
480
* Show oid, filenode, name, schema and tablespace for each of the
481
* given objects in the current database.
484
sql_exec_searchtables(PGconn *conn, struct options * opts)
492
bool written = false;
493
char *addfields = ",c.oid AS \"Oid\", nspname AS \"Schema\", spcname as \"Tablespace\" ";
495
/* get tables qualifiers, whether names, filenodes, or OIDs */
496
comma_oids = get_comma_elts(opts->oids);
497
comma_tables = get_comma_elts(opts->tables);
498
comma_filenodes = get_comma_elts(opts->filenodes);
500
/* 80 extra chars for SQL expression */
501
qualifiers = (char *) myalloc(strlen(comma_oids) + strlen(comma_tables) +
502
strlen(comma_filenodes) + 80);
505
if (opts->oids->num > 0)
507
ptr += sprintf(ptr, "c.oid IN (%s)", comma_oids);
510
if (opts->filenodes->num > 0)
513
ptr += sprintf(ptr, " OR ");
514
ptr += sprintf(ptr, "pg_catalog.pg_relation_filenode(c.oid) IN (%s)", comma_filenodes);
517
if (opts->tables->num > 0)
520
ptr += sprintf(ptr, " OR ");
521
sprintf(ptr, "c.relname ~~ ANY (ARRAY[%s])", comma_tables);
525
free(comma_filenodes);
527
/* now build the query */
528
todo = (char *) myalloc(650 + strlen(qualifiers));
529
snprintf(todo, 650 + strlen(qualifiers),
530
"SELECT pg_catalog.pg_relation_filenode(c.oid) as \"Filenode\", relname as \"Table Name\" %s\n"
531
"FROM pg_catalog.pg_class c \n"
532
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace \n"
533
" LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database(),\n"
534
" pg_catalog.pg_tablespace t \n"
535
"WHERE relkind IN ('r', 'i', 'S', 't') AND \n"
537
" WHEN reltablespace <> 0 THEN reltablespace\n"
538
" ELSE dattablespace\n"
541
"ORDER BY relname\n",
542
opts->extended ? addfields : "",
547
sql_exec(conn, todo, opts->quiet);
551
sql_exec_dumpalltbspc(PGconn *conn, struct options * opts)
555
snprintf(todo, sizeof(todo),
556
"SELECT oid AS \"Oid\", spcname as \"Tablespace Name\"\n"
557
"FROM pg_catalog.pg_tablespace");
559
sql_exec(conn, todo, opts->quiet);
563
main(int argc, char **argv)
565
struct options *my_opts;
568
my_opts = (struct options *) myalloc(sizeof(struct options));
570
my_opts->oids = (eary *) myalloc(sizeof(eary));
571
my_opts->tables = (eary *) myalloc(sizeof(eary));
572
my_opts->filenodes = (eary *) myalloc(sizeof(eary));
574
my_opts->oids->num = my_opts->oids->alloc = 0;
575
my_opts->tables->num = my_opts->tables->alloc = 0;
576
my_opts->filenodes->num = my_opts->filenodes->alloc = 0;
579
get_opts(argc, argv, my_opts);
581
if (my_opts->dbname == NULL)
583
my_opts->dbname = "postgres";
584
my_opts->nodb = true;
586
pgconn = sql_conn(my_opts);
588
/* display only tablespaces */
589
if (my_opts->tablespaces)
592
printf("All tablespaces:\n");
593
sql_exec_dumpalltbspc(pgconn, my_opts);
599
/* display the given elements in the database */
600
if (my_opts->oids->num > 0 ||
601
my_opts->tables->num > 0 ||
602
my_opts->filenodes->num > 0)
605
printf("From database \"%s\":\n", my_opts->dbname);
606
sql_exec_searchtables(pgconn, my_opts);
612
/* no elements given; dump the given database */
613
if (my_opts->dbname && !my_opts->nodb)
616
printf("From database \"%s\":\n", my_opts->dbname);
617
sql_exec_dumpalltables(pgconn, my_opts);
623
/* no database either; dump all databases */
625
printf("All databases:\n");
626
sql_exec_dumpalldbs(pgconn, my_opts);