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 */
50 /* function prototypes */
51 static void help(const char *progname);
52 void get_opts(int, char **, struct options *);
53 void *pg_malloc(size_t size);
54 char *pg_strdup(const char *str);
55 void add_one_elt(char *eltname, eary *eary);
56 char *get_comma_elts(eary *eary);
57 PGconn *sql_conn(struct options *);
58 int sql_exec(PGconn *, const char *sql, bool quiet);
59 void sql_exec_dumpalldbs(PGconn *, struct options *);
60 void sql_exec_dumpalltables(PGconn *, struct options *);
61 void sql_exec_searchtables(PGconn *, struct options *);
62 void sql_exec_dumpalltbspc(PGconn *, struct options *);
64 /* function to parse command line options and check for some usage errors. */
66 get_opts(int argc, char **argv, struct options * my_opts)
71 progname = get_progname(argv[0]);
73 /* set the defaults */
74 my_opts->quiet = false;
75 my_opts->systables = false;
76 my_opts->indexes = false;
77 my_opts->nodb = false;
78 my_opts->extended = false;
79 my_opts->tablespaces = false;
80 my_opts->dbname = NULL;
81 my_opts->hostname = NULL;
83 my_opts->username = NULL;
84 my_opts->progname = progname;
88 if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
93 if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
95 puts("oid2name (PostgreSQL) " PG_VERSION);
101 while ((c = getopt(argc, argv, "H:p:U:d:t:o:f:qSxish")) != -1)
105 /* specify the database */
107 my_opts->dbname = pg_strdup(optarg);
110 /* specify one tablename to show */
112 add_one_elt(optarg, my_opts->tables);
115 /* specify one Oid to show */
117 add_one_elt(optarg, my_opts->oids);
120 /* specify one filenode to show */
122 add_one_elt(optarg, my_opts->filenodes);
125 /* don't show headers */
127 my_opts->quiet = true;
130 /* host to connect to */
132 my_opts->hostname = pg_strdup(optarg);
135 /* port to connect to on remote host */
137 my_opts->port = pg_strdup(optarg);
142 my_opts->username = pg_strdup(optarg);
145 /* display system tables */
147 my_opts->systables = true;
150 /* also display indexes */
152 my_opts->indexes = true;
155 /* display extra columns */
157 my_opts->extended = true;
160 /* dump tablespaces only */
162 my_opts->tablespaces = true;
171 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
178 help(const char *progname)
180 printf("%s helps examining the file structure used by PostgreSQL.\n\n"
184 " -d DBNAME database to connect to\n"
185 " -f FILENODE show info for table with given file node\n"
186 " -H HOSTNAME database server host or socket directory\n"
187 " -i show indexes and sequences too\n"
188 " -o OID show info for table with given OID\n"
189 " -p PORT database server port number\n"
190 " -q quiet (don't show headers)\n"
191 " -s show all tablespaces\n"
192 " -S show system objects too\n"
193 " -t TABLE show info for named table\n"
194 " -U NAME connect as specified database user\n"
195 " -V, --version output version information, then exit\n"
196 " -x extended (show additional columns)\n"
197 " -?, --help show this help, then exit\n"
198 "\nThe default action is to show all database OIDs.\n\n"
199 "Report bugs to <pgsql-bugs@postgresql.org>.\n",
204 pg_malloc(size_t size)
206 void *ptr = malloc(size);
210 fprintf(stderr, "out of memory");
217 pg_strdup(const char *str)
219 char *result = strdup(str);
223 fprintf(stderr, "out of memory");
232 * Add one element to a (possibly empty) eary struct.
235 add_one_elt(char *eltname, eary *eary)
237 if (eary->alloc == 0)
240 eary ->array = (char **) pg_malloc(8 * sizeof(char *));
242 else if (eary->num >= eary->alloc)
245 eary ->array = (char **)
246 realloc(eary->array, eary->alloc * sizeof(char *));
250 fprintf(stderr, "out of memory");
255 eary ->array[eary->num] = pg_strdup(eltname);
262 * Return the elements of an eary as a (freshly allocated) single string, in
263 * single quotes, separated by commas and properly escaped for insertion in an
267 get_comma_elts(eary *eary)
275 return pg_strdup("");
278 * PQescapeString wants 2 * length + 1 bytes of breath space. Add two
279 * chars per element for the single quotes and one for the comma.
281 for (i = 0; i < eary->num; i++)
282 length += strlen(eary->array[i]);
284 ret = (char *) pg_malloc(length * 2 + 4 * eary->num);
287 for (i = 0; i < eary->num; i++)
292 ptr += PQescapeString(ptr, eary->array[i], strlen(eary->array[i]));
299 /* establish connection with database. */
301 sql_conn(struct options * my_opts)
304 char *password = NULL;
308 * Start the connection. Loop until we have a password if requested by
313 #define PARAMS_ARRAY_SIZE 7
315 const char *keywords[PARAMS_ARRAY_SIZE];
316 const char *values[PARAMS_ARRAY_SIZE];
318 keywords[0] = "host";
319 values[0] = my_opts->hostname;
320 keywords[1] = "port";
321 values[1] = my_opts->port;
322 keywords[2] = "user";
323 values[2] = my_opts->username;
324 keywords[3] = "password";
325 values[3] = password;
326 keywords[4] = "dbname";
327 values[4] = my_opts->dbname;
328 keywords[5] = "fallback_application_name";
329 values[5] = my_opts->progname;
334 conn = PQconnectdbParams(keywords, values, true);
338 fprintf(stderr, "%s: could not connect to database %s\n",
339 "oid2name", my_opts->dbname);
343 if (PQstatus(conn) == CONNECTION_BAD &&
344 PQconnectionNeedsPassword(conn) &&
348 password = simple_prompt("Password: ", 100, false);
356 /* check to see that the backend connection was successfully made */
357 if (PQstatus(conn) == CONNECTION_BAD)
359 fprintf(stderr, "%s: could not connect to database %s: %s",
360 "oid2name", my_opts->dbname, PQerrorMessage(conn));
365 /* return the conn if good */
370 * Actual code to make call to the database and print the output data.
373 sql_exec(PGconn *conn, const char *todo, bool quiet)
386 res = PQexec(conn, todo);
388 /* check and deal with errors */
389 if (!res || PQresultStatus(res) > 2)
391 fprintf(stderr, "oid2name: query failed: %s\n", PQerrorMessage(conn));
392 fprintf(stderr, "oid2name: query was: %s\n", todo);
399 /* get the number of fields */
400 nrows = PQntuples(res);
401 nfields = PQnfields(res);
403 /* for each field, get the needed width */
404 length = (int *) pg_malloc(sizeof(int) * nfields);
405 for (j = 0; j < nfields; j++)
406 length[j] = strlen(PQfname(res, j));
408 for (i = 0; i < nrows; i++)
410 for (j = 0; j < nfields; j++)
412 l = strlen(PQgetvalue(res, i, j));
414 length[j] = strlen(PQgetvalue(res, i, j));
421 for (j = 0, l = 0; j < nfields; j++)
423 fprintf(stdout, "%*s", length[j] + 2, PQfname(res, j));
426 fprintf(stdout, "\n");
427 pad = (char *) pg_malloc(l + 1);
430 fprintf(stdout, "%s\n", pad);
434 /* for each row, dump the information */
435 for (i = 0; i < nrows; i++)
437 for (j = 0; j < nfields; j++)
438 fprintf(stdout, "%*s", length[j] + 2, PQgetvalue(res, i, j));
439 fprintf(stdout, "\n");
450 * Dump all databases. There are no system objects to worry about.
453 sql_exec_dumpalldbs(PGconn *conn, struct options * opts)
457 /* get the oid and database name from the system pg_database table */
458 snprintf(todo, sizeof(todo),
459 "SELECT d.oid AS \"Oid\", datname AS \"Database Name\", "
460 "spcname AS \"Tablespace\" FROM pg_catalog.pg_database d JOIN pg_catalog.pg_tablespace t ON "
461 "(dattablespace = t.oid) ORDER BY 2");
463 sql_exec(conn, todo, opts->quiet);
467 * Dump all tables, indexes and sequences in the current database.
470 sql_exec_dumpalltables(PGconn *conn, struct options * opts)
473 char *addfields = ",c.oid AS \"Oid\", nspname AS \"Schema\", spcname as \"Tablespace\" ";
475 snprintf(todo, sizeof(todo),
476 "SELECT pg_catalog.pg_relation_filenode(c.oid) as \"Filenode\", relname as \"Table Name\" %s "
478 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace "
479 " LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database(),"
480 " pg_catalog.pg_tablespace t "
481 "WHERE relkind IN ('r'%s%s) AND "
484 " WHEN reltablespace <> 0 THEN reltablespace"
485 " ELSE dattablespace"
488 opts->extended ? addfields : "",
489 opts->indexes ? ", 'i', 'S'" : "",
490 opts->systables ? ", 't'" : "",
491 opts->systables ? "" : "n.nspname NOT IN ('pg_catalog', 'information_schema') AND n.nspname !~ '^pg_toast' AND");
493 sql_exec(conn, todo, opts->quiet);
497 * Show oid, filenode, name, schema and tablespace for each of the
498 * given objects in the current database.
501 sql_exec_searchtables(PGconn *conn, struct options * opts)
509 bool written = false;
510 char *addfields = ",c.oid AS \"Oid\", nspname AS \"Schema\", spcname as \"Tablespace\" ";
512 /* get tables qualifiers, whether names, filenodes, or OIDs */
513 comma_oids = get_comma_elts(opts->oids);
514 comma_tables = get_comma_elts(opts->tables);
515 comma_filenodes = get_comma_elts(opts->filenodes);
517 /* 80 extra chars for SQL expression */
518 qualifiers = (char *) pg_malloc(strlen(comma_oids) + strlen(comma_tables) +
519 strlen(comma_filenodes) + 80);
522 if (opts->oids->num > 0)
524 ptr += sprintf(ptr, "c.oid IN (%s)", comma_oids);
527 if (opts->filenodes->num > 0)
530 ptr += sprintf(ptr, " OR ");
531 ptr += sprintf(ptr, "pg_catalog.pg_relation_filenode(c.oid) IN (%s)", comma_filenodes);
534 if (opts->tables->num > 0)
537 ptr += sprintf(ptr, " OR ");
538 sprintf(ptr, "c.relname ~~ ANY (ARRAY[%s])", comma_tables);
542 free(comma_filenodes);
544 /* now build the query */
545 todo = (char *) pg_malloc(650 + strlen(qualifiers));
546 snprintf(todo, 650 + strlen(qualifiers),
547 "SELECT pg_catalog.pg_relation_filenode(c.oid) as \"Filenode\", relname as \"Table Name\" %s\n"
548 "FROM pg_catalog.pg_class c \n"
549 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace \n"
550 " LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database(),\n"
551 " pg_catalog.pg_tablespace t \n"
552 "WHERE relkind IN ('r', 'i', 'S', 't') AND \n"
554 " WHEN reltablespace <> 0 THEN reltablespace\n"
555 " ELSE dattablespace\n"
558 "ORDER BY relname\n",
559 opts->extended ? addfields : "",
564 sql_exec(conn, todo, opts->quiet);
568 sql_exec_dumpalltbspc(PGconn *conn, struct options * opts)
572 snprintf(todo, sizeof(todo),
573 "SELECT oid AS \"Oid\", spcname as \"Tablespace Name\"\n"
574 "FROM pg_catalog.pg_tablespace");
576 sql_exec(conn, todo, opts->quiet);
580 main(int argc, char **argv)
582 struct options *my_opts;
585 my_opts = (struct options *) pg_malloc(sizeof(struct options));
587 my_opts->oids = (eary *) pg_malloc(sizeof(eary));
588 my_opts->tables = (eary *) pg_malloc(sizeof(eary));
589 my_opts->filenodes = (eary *) pg_malloc(sizeof(eary));
591 my_opts->oids->num = my_opts->oids->alloc = 0;
592 my_opts->tables->num = my_opts->tables->alloc = 0;
593 my_opts->filenodes->num = my_opts->filenodes->alloc = 0;
596 get_opts(argc, argv, my_opts);
598 if (my_opts->dbname == NULL)
600 my_opts->dbname = "postgres";
601 my_opts->nodb = true;
603 pgconn = sql_conn(my_opts);
605 /* display only tablespaces */
606 if (my_opts->tablespaces)
609 printf("All tablespaces:\n");
610 sql_exec_dumpalltbspc(pgconn, my_opts);
616 /* display the given elements in the database */
617 if (my_opts->oids->num > 0 ||
618 my_opts->tables->num > 0 ||
619 my_opts->filenodes->num > 0)
622 printf("From database \"%s\":\n", my_opts->dbname);
623 sql_exec_searchtables(pgconn, my_opts);
629 /* no elements given; dump the given database */
630 if (my_opts->dbname && !my_opts->nodb)
633 printf("From database \"%s\":\n", my_opts->dbname);
634 sql_exec_dumpalltables(pgconn, my_opts);
640 /* no database either; dump all databases */
642 printf("All databases:\n");
643 sql_exec_dumpalldbs(pgconn, my_opts);