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 * $PostgreSQL: pgsql/contrib/oid2name/oid2name.c,v 1.34 2009/02/25 13:24:40 petere Exp $
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 void get_opts(int, char **, struct options *);
51 void *myalloc(size_t size);
52 char *mystrdup(const char *str);
53 void add_one_elt(char *eltname, eary * eary);
54 char *get_comma_elts(eary * eary);
55 PGconn *sql_conn(struct options *);
56 int sql_exec(PGconn *, const char *sql, bool quiet);
57 void sql_exec_dumpalldbs(PGconn *, struct options *);
58 void sql_exec_dumpalltables(PGconn *, struct options *);
59 void sql_exec_searchtables(PGconn *, struct options *);
60 void sql_exec_dumpalltbspc(PGconn *, struct options *);
62 /* function to parse command line options and check for some usage errors. */
64 get_opts(int argc, char **argv, struct options * my_opts)
68 /* set the defaults */
69 my_opts->quiet = false;
70 my_opts->systables = false;
71 my_opts->indexes = false;
72 my_opts->nodb = false;
73 my_opts->extended = false;
74 my_opts->tablespaces = false;
75 my_opts->dbname = NULL;
76 my_opts->hostname = NULL;
78 my_opts->username = NULL;
81 while ((c = getopt(argc, argv, "H:p:U:d:t:o:f:qSxish?")) != -1)
85 /* specify the database */
87 my_opts->dbname = mystrdup(optarg);
90 /* specify one tablename to show */
92 add_one_elt(optarg, my_opts->tables);
95 /* specify one Oid to show */
97 add_one_elt(optarg, my_opts->oids);
100 /* specify one filenode to show */
102 add_one_elt(optarg, my_opts->filenodes);
105 /* don't show headers */
107 my_opts->quiet = true;
110 /* host to connect to */
112 my_opts->hostname = mystrdup(optarg);
115 /* port to connect to on remote host */
117 my_opts->port = mystrdup(optarg);
122 my_opts->username = mystrdup(optarg);
125 /* display system tables */
127 my_opts->systables = true;
130 /* also display indexes */
132 my_opts->indexes = true;
135 /* display extra columns */
137 my_opts->extended = true;
140 /* dump tablespaces only */
142 my_opts->tablespaces = true;
145 /* help! (ugly in code for easier editing) */
149 "Usage: oid2name [-s|-d database] [-S][-i][-q][-x] [-t table|-o oid|-f file] ...\n"
150 " default action show all database Oids\n"
151 " -d database database to connect to\n"
152 " -s show all tablespaces\n"
153 " -S show system objects too\n"
154 " -i show indexes and sequences too\n"
155 " -x extended (show additional columns)\n"
156 " -q quiet (don't show headers)\n"
157 " -t <table> show info for table named <table>\n"
158 " -o <oid> show info for table with Oid <oid>\n"
159 " -f <filenode> show info for table with filenode <filenode>\n"
160 " -H host connect to remote host\n"
161 " -p port host port to connect to\n"
162 " -U username username to connect with\n"
173 void *ptr = malloc(size);
177 fprintf(stderr, "out of memory");
184 mystrdup(const char *str)
186 char *result = strdup(str);
190 fprintf(stderr, "out of memory");
199 * Add one element to a (possibly empty) eary struct.
202 add_one_elt(char *eltname, eary * eary)
204 if (eary->alloc == 0)
207 eary->array = (char **) myalloc(8 * sizeof(char *));
209 else if (eary->num >= eary->alloc)
212 eary->array = (char **)
213 realloc(eary->array, eary->alloc * sizeof(char *));
216 fprintf(stderr, "out of memory");
221 eary->array[eary->num] = mystrdup(eltname);
228 * Return the elements of an eary as a (freshly allocated) single string, in
229 * single quotes, separated by commas and properly escaped for insertion in an
233 get_comma_elts(eary * eary)
244 * PQescapeString wants 2 * length + 1 bytes of breath space. Add two
245 * chars per element for the single quotes and one for the comma.
247 for (i = 0; i < eary->num; i++)
248 length += strlen(eary->array[i]);
250 ret = (char *) myalloc(length * 2 + 4 * eary->num);
253 for (i = 0; i < eary->num; i++)
258 ptr += PQescapeString(ptr, eary->array[i], strlen(eary->array[i]));
265 /* establish connection with database. */
267 sql_conn(struct options * my_opts)
270 char *password = NULL;
274 * Start the connection. Loop until we have a password if requested by
280 conn = PQsetdbLogin(my_opts->hostname,
289 fprintf(stderr, "%s: could not connect to database %s\n",
290 "oid2name", my_opts->dbname);
294 if (PQstatus(conn) == CONNECTION_BAD &&
295 PQconnectionNeedsPassword(conn) &&
299 password = simple_prompt("Password: ", 100, false);
307 /* check to see that the backend connection was successfully made */
308 if (PQstatus(conn) == CONNECTION_BAD)
310 fprintf(stderr, "%s: could not connect to database %s: %s",
311 "oid2name", my_opts->dbname, PQerrorMessage(conn));
316 /* return the conn if good */
321 * Actual code to make call to the database and print the output data.
324 sql_exec(PGconn *conn, const char *todo, bool quiet)
337 res = PQexec(conn, todo);
339 /* check and deal with errors */
340 if (!res || PQresultStatus(res) > 2)
342 fprintf(stderr, "oid2name: query failed: %s\n", PQerrorMessage(conn));
343 fprintf(stderr, "oid2name: query was: %s\n", todo);
350 /* get the number of fields */
351 nrows = PQntuples(res);
352 nfields = PQnfields(res);
354 /* for each field, get the needed width */
355 length = (int *) myalloc(sizeof(int) * nfields);
356 for (j = 0; j < nfields; j++)
357 length[j] = strlen(PQfname(res, j));
359 for (i = 0; i < nrows; i++)
361 for (j = 0; j < nfields; j++)
363 l = strlen(PQgetvalue(res, i, j));
365 length[j] = strlen(PQgetvalue(res, i, j));
372 for (j = 0, l = 0; j < nfields; j++)
374 fprintf(stdout, "%*s", length[j] + 2, PQfname(res, j));
377 fprintf(stdout, "\n");
378 pad = (char *) myalloc(l + 1);
381 fprintf(stdout, "%s\n", pad);
385 /* for each row, dump the information */
386 for (i = 0; i < nrows; i++)
388 for (j = 0; j < nfields; j++)
389 fprintf(stdout, "%*s", length[j] + 2, PQgetvalue(res, i, j));
390 fprintf(stdout, "\n");
401 * Dump all databases. There are no system objects to worry about.
404 sql_exec_dumpalldbs(PGconn *conn, struct options * opts)
408 /* get the oid and database name from the system pg_database table */
409 snprintf(todo, sizeof(todo),
410 "SELECT d.oid AS \"Oid\", datname AS \"Database Name\", "
411 "spcname AS \"Tablespace\" FROM pg_database d JOIN pg_tablespace t ON "
412 "(dattablespace = t.oid) ORDER BY 2");
414 sql_exec(conn, todo, opts->quiet);
418 * Dump all tables, indexes and sequences in the current database.
421 sql_exec_dumpalltables(PGconn *conn, struct options * opts)
424 char *addfields = ",c.oid AS \"Oid\", nspname AS \"Schema\", spcname as \"Tablespace\" ";
426 snprintf(todo, sizeof(todo),
427 "SELECT relfilenode as \"Filenode\", relname as \"Table Name\" %s "
429 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace "
430 " LEFT JOIN pg_catalog.pg_database d ON d.datname = current_database(),"
431 " pg_catalog.pg_tablespace t "
432 "WHERE relkind IN ('r'%s%s) AND "
435 " WHEN reltablespace <> 0 THEN reltablespace"
436 " ELSE dattablespace"
439 opts->extended ? addfields : "",
440 opts->indexes ? ", 'i', 'S'" : "",
441 opts->systables ? ", 't'" : "",
442 opts->systables ? "" : "n.nspname NOT IN ('pg_catalog', 'information_schema') AND n.nspname !~ '^pg_toast' AND");
444 sql_exec(conn, todo, opts->quiet);
448 * Show oid, relfilenode, name, schema and tablespace for each of the
449 * given objects in the current database.
452 sql_exec_searchtables(PGconn *conn, struct options * opts)
460 bool written = false;
461 char *addfields = ",c.oid AS \"Oid\", nspname AS \"Schema\", spcname as \"Tablespace\" ";
463 /* get tables qualifiers, whether names, relfilenodes, or OIDs */
464 comma_oids = get_comma_elts(opts->oids);
465 comma_tables = get_comma_elts(opts->tables);
466 comma_filenodes = get_comma_elts(opts->filenodes);
468 /* 80 extra chars for SQL expression */
469 qualifiers = (char *) myalloc(strlen(comma_oids) + strlen(comma_tables) +
470 strlen(comma_filenodes) + 80);
473 if (opts->oids->num > 0)
475 ptr += sprintf(ptr, "c.oid IN (%s)", comma_oids);
478 if (opts->filenodes->num > 0)
481 ptr += sprintf(ptr, " OR ");
482 ptr += sprintf(ptr, "c.relfilenode IN (%s)", comma_filenodes);
485 if (opts->tables->num > 0)
488 ptr += sprintf(ptr, " OR ");
489 sprintf(ptr, "c.relname ~~ ANY (ARRAY[%s])", comma_tables);
493 free(comma_filenodes);
495 /* now build the query */
496 todo = (char *) myalloc(650 + strlen(qualifiers));
497 snprintf(todo, 650 + strlen(qualifiers),
498 "SELECT relfilenode as \"Filenode\", relname as \"Table Name\" %s\n"
500 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace \n"
501 " LEFT JOIN pg_catalog.pg_database d ON d.datname = current_database(),\n"
502 " pg_catalog.pg_tablespace t \n"
503 "WHERE relkind IN ('r', 'i', 'S', 't') AND \n"
505 " WHEN reltablespace <> 0 THEN reltablespace\n"
506 " ELSE dattablespace\n"
509 "ORDER BY relname\n",
510 opts->extended ? addfields : "",
515 sql_exec(conn, todo, opts->quiet);
519 sql_exec_dumpalltbspc(PGconn *conn, struct options * opts)
523 snprintf(todo, sizeof(todo),
524 "SELECT oid AS \"Oid\", spcname as \"Tablespace Name\"\n"
525 "FROM pg_tablespace");
527 sql_exec(conn, todo, opts->quiet);
531 main(int argc, char **argv)
533 struct options *my_opts;
536 my_opts = (struct options *) myalloc(sizeof(struct options));
538 my_opts->oids = (eary *) myalloc(sizeof(eary));
539 my_opts->tables = (eary *) myalloc(sizeof(eary));
540 my_opts->filenodes = (eary *) myalloc(sizeof(eary));
542 my_opts->oids->num = my_opts->oids->alloc = 0;
543 my_opts->tables->num = my_opts->tables->alloc = 0;
544 my_opts->filenodes->num = my_opts->filenodes->alloc = 0;
547 get_opts(argc, argv, my_opts);
549 if (my_opts->dbname == NULL)
551 my_opts->dbname = "postgres";
552 my_opts->nodb = true;
554 pgconn = sql_conn(my_opts);
556 /* display only tablespaces */
557 if (my_opts->tablespaces)
560 printf("All tablespaces:\n");
561 sql_exec_dumpalltbspc(pgconn, my_opts);
567 /* display the given elements in the database */
568 if (my_opts->oids->num > 0 ||
569 my_opts->tables->num > 0 ||
570 my_opts->filenodes->num > 0)
573 printf("From database \"%s\":\n", my_opts->dbname);
574 sql_exec_searchtables(pgconn, my_opts);
580 /* no elements given; dump the given database */
581 if (my_opts->dbname && !my_opts->nodb)
584 printf("From database \"%s\":\n", my_opts->dbname);
585 sql_exec_dumpalltables(pgconn, my_opts);
591 /* no database either; dump all databases */
593 printf("All databases:\n");
594 sql_exec_dumpalldbs(pgconn, my_opts);