diff options
Diffstat (limited to 'contrib/oid2name')
| -rw-r--r-- | contrib/oid2name/.gitignore | 3 | ||||
| -rw-r--r-- | contrib/oid2name/Makefile | 25 | ||||
| -rw-r--r-- | contrib/oid2name/oid2name.c | 649 | ||||
| -rw-r--r-- | contrib/oid2name/t/001_basic.pl | 17 |
4 files changed, 694 insertions, 0 deletions
diff --git a/contrib/oid2name/.gitignore b/contrib/oid2name/.gitignore new file mode 100644 index 0000000..0410fb7 --- /dev/null +++ b/contrib/oid2name/.gitignore @@ -0,0 +1,3 @@ +/oid2name + +/tmp_check/ diff --git a/contrib/oid2name/Makefile b/contrib/oid2name/Makefile new file mode 100644 index 0000000..6179ed5 --- /dev/null +++ b/contrib/oid2name/Makefile @@ -0,0 +1,25 @@ +# contrib/oid2name/Makefile + +PGFILEDESC = "oid2name - examine the file structure" +PGAPPICON = win32 + +PROGRAM = oid2name +OBJS = \ + $(WIN32RES) \ + oid2name.o + +TAP_TESTS = 1 + +PG_CPPFLAGS = -I$(libpq_srcdir) +PG_LIBS_INTERNAL = $(libpq_pgport) + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = contrib/oid2name +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/contrib/oid2name/oid2name.c b/contrib/oid2name/oid2name.c new file mode 100644 index 0000000..a62a5ee --- /dev/null +++ b/contrib/oid2name/oid2name.c @@ -0,0 +1,649 @@ +/* + * oid2name, a PostgreSQL app to map OIDs on the filesystem + * to table and database names. + * + * Originally by + * B. Palmer, bpalmer@crimelabs.net 1-17-2001 + * + * contrib/oid2name/oid2name.c + */ +#include "postgres_fe.h" + +#include "catalog/pg_class_d.h" +#include "common/connect.h" +#include "common/logging.h" +#include "common/string.h" +#include "getopt_long.h" +#include "libpq-fe.h" +#include "pg_getopt.h" + +/* an extensible array to keep track of elements to show */ +typedef struct +{ + char **array; + int num; + int alloc; +} eary; + +/* these are the opts structures for command line params */ +struct options +{ + eary *tables; + eary *oids; + eary *filenodes; + + bool quiet; + bool systables; + bool indexes; + bool nodb; + bool extended; + bool tablespaces; + + char *dbname; + char *hostname; + char *port; + char *username; + const char *progname; +}; + +/* function prototypes */ +static void help(const char *progname); +void get_opts(int, char **, struct options *); +void add_one_elt(char *eltname, eary *eary); +char *get_comma_elts(eary *eary); +PGconn *sql_conn(struct options *); +int sql_exec(PGconn *, const char *sql, bool quiet); +void sql_exec_dumpalldbs(PGconn *, struct options *); +void sql_exec_dumpalltables(PGconn *, struct options *); +void sql_exec_searchtables(PGconn *, struct options *); +void sql_exec_dumpalltbspc(PGconn *, struct options *); + +/* function to parse command line options and check for some usage errors. */ +void +get_opts(int argc, char **argv, struct options *my_opts) +{ + static struct option long_options[] = { + {"dbname", required_argument, NULL, 'd'}, + {"host", required_argument, NULL, 'h'}, + {"host", required_argument, NULL, 'H'}, /* deprecated */ + {"filenode", required_argument, NULL, 'f'}, + {"indexes", no_argument, NULL, 'i'}, + {"oid", required_argument, NULL, 'o'}, + {"port", required_argument, NULL, 'p'}, + {"quiet", no_argument, NULL, 'q'}, + {"tablespaces", no_argument, NULL, 's'}, + {"system-objects", no_argument, NULL, 'S'}, + {"table", required_argument, NULL, 't'}, + {"username", required_argument, NULL, 'U'}, + {"version", no_argument, NULL, 'V'}, + {"extended", no_argument, NULL, 'x'}, + {"help", no_argument, NULL, '?'}, + {NULL, 0, NULL, 0} + }; + + int c; + const char *progname; + int optindex; + + pg_logging_init(argv[0]); + progname = get_progname(argv[0]); + + /* set the defaults */ + my_opts->quiet = false; + my_opts->systables = false; + my_opts->indexes = false; + my_opts->nodb = false; + my_opts->extended = false; + my_opts->tablespaces = false; + my_opts->dbname = NULL; + my_opts->hostname = NULL; + my_opts->port = NULL; + my_opts->username = NULL; + my_opts->progname = progname; + + if (argc > 1) + { + if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0) + { + help(progname); + exit(0); + } + if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0) + { + puts("oid2name (PostgreSQL) " PG_VERSION); + exit(0); + } + } + + /* get opts */ + while ((c = getopt_long(argc, argv, "d:f:h:H:io:p:qsSt:U:x", long_options, &optindex)) != -1) + { + switch (c) + { + /* specify the database */ + case 'd': + my_opts->dbname = pg_strdup(optarg); + break; + + /* specify one filenode to show */ + case 'f': + add_one_elt(optarg, my_opts->filenodes); + break; + + /* host to connect to */ + case 'H': /* deprecated */ + case 'h': + my_opts->hostname = pg_strdup(optarg); + break; + + /* also display indexes */ + case 'i': + my_opts->indexes = true; + break; + + /* specify one Oid to show */ + case 'o': + add_one_elt(optarg, my_opts->oids); + break; + + /* port to connect to on remote host */ + case 'p': + my_opts->port = pg_strdup(optarg); + break; + + /* don't show headers */ + case 'q': + my_opts->quiet = true; + break; + + /* dump tablespaces only */ + case 's': + my_opts->tablespaces = true; + break; + + /* display system tables */ + case 'S': + my_opts->systables = true; + break; + + /* specify one tablename to show */ + case 't': + add_one_elt(optarg, my_opts->tables); + break; + + /* username */ + case 'U': + my_opts->username = pg_strdup(optarg); + break; + + /* display extra columns */ + case 'x': + my_opts->extended = true; + break; + + default: + /* getopt_long already emitted a complaint */ + pg_log_error_hint("Try \"%s --help\" for more information.", progname); + exit(1); + } + } + + if (optind < argc) + { + pg_log_error("too many command-line arguments (first is \"%s\")", + argv[optind]); + pg_log_error_hint("Try \"%s --help\" for more information.", progname); + exit(1); + } +} + +static void +help(const char *progname) +{ + printf("%s helps examining the file structure used by PostgreSQL.\n\n" + "Usage:\n" + " %s [OPTION]...\n" + "\nOptions:\n" + " -f, --filenode=FILENODE show info for table with given file node\n" + " -i, --indexes show indexes and sequences too\n" + " -o, --oid=OID show info for table with given OID\n" + " -q, --quiet quiet (don't show headers)\n" + " -s, --tablespaces show all tablespaces\n" + " -S, --system-objects show system objects too\n" + " -t, --table=TABLE show info for named table\n" + " -V, --version output version information, then exit\n" + " -x, --extended extended (show additional columns)\n" + " -?, --help show this help, then exit\n" + "\nConnection options:\n" + " -d, --dbname=DBNAME database to connect to\n" + " -h, --host=HOSTNAME database server host or socket directory\n" + " -H same as -h, deprecated option\n" + " -p, --port=PORT database server port number\n" + " -U, --username=USERNAME connect as specified database user\n" + "\nThe default action is to show all database OIDs.\n\n" + "Report bugs to <%s>.\n" + "%s home page: <%s>\n", + progname, progname, PACKAGE_BUGREPORT, PACKAGE_NAME, PACKAGE_URL); +} + +/* + * add_one_elt + * + * Add one element to a (possibly empty) eary struct. + */ +void +add_one_elt(char *eltname, eary *eary) +{ + if (eary->alloc == 0) + { + eary ->alloc = 8; + eary ->array = (char **) pg_malloc(8 * sizeof(char *)); + } + else if (eary->num >= eary->alloc) + { + eary ->alloc *= 2; + eary ->array = (char **) pg_realloc(eary->array, + eary->alloc * sizeof(char *)); + } + + eary ->array[eary->num] = pg_strdup(eltname); + eary ->num++; +} + +/* + * get_comma_elts + * + * Return the elements of an eary as a (freshly allocated) single string, in + * single quotes, separated by commas and properly escaped for insertion in an + * SQL statement. + */ +char * +get_comma_elts(eary *eary) +{ + char *ret, + *ptr; + int i, + length = 0; + + if (eary->num == 0) + return pg_strdup(""); + + /* + * PQescapeString wants 2 * length + 1 bytes of breath space. Add two + * chars per element for the single quotes and one for the comma. + */ + for (i = 0; i < eary->num; i++) + length += strlen(eary->array[i]); + + ret = (char *) pg_malloc(length * 2 + 4 * eary->num); + ptr = ret; + + for (i = 0; i < eary->num; i++) + { + if (i != 0) + sprintf(ptr++, ","); + sprintf(ptr++, "'"); + ptr += PQescapeString(ptr, eary->array[i], strlen(eary->array[i])); + sprintf(ptr++, "'"); + } + + return ret; +} + +/* establish connection with database. */ +PGconn * +sql_conn(struct options *my_opts) +{ + PGconn *conn; + char *password = NULL; + bool new_pass; + PGresult *res; + + /* + * Start the connection. Loop until we have a password if requested by + * backend. + */ + do + { +#define PARAMS_ARRAY_SIZE 7 + + const char *keywords[PARAMS_ARRAY_SIZE]; + const char *values[PARAMS_ARRAY_SIZE]; + + keywords[0] = "host"; + values[0] = my_opts->hostname; + keywords[1] = "port"; + values[1] = my_opts->port; + keywords[2] = "user"; + values[2] = my_opts->username; + keywords[3] = "password"; + values[3] = password; + keywords[4] = "dbname"; + values[4] = my_opts->dbname; + keywords[5] = "fallback_application_name"; + values[5] = my_opts->progname; + keywords[6] = NULL; + values[6] = NULL; + + new_pass = false; + conn = PQconnectdbParams(keywords, values, true); + + if (!conn) + pg_fatal("could not connect to database %s", + my_opts->dbname); + + if (PQstatus(conn) == CONNECTION_BAD && + PQconnectionNeedsPassword(conn) && + !password) + { + PQfinish(conn); + password = simple_prompt("Password: ", false); + new_pass = true; + } + } while (new_pass); + + /* check to see that the backend connection was successfully made */ + if (PQstatus(conn) == CONNECTION_BAD) + { + pg_log_error("%s", PQerrorMessage(conn)); + PQfinish(conn); + exit(1); + } + + res = PQexec(conn, ALWAYS_SECURE_SEARCH_PATH_SQL); + if (PQresultStatus(res) != PGRES_TUPLES_OK) + { + pg_log_error("could not clear search_path: %s", + PQerrorMessage(conn)); + PQclear(res); + PQfinish(conn); + exit(1); + } + PQclear(res); + + /* return the conn if good */ + return conn; +} + +/* + * Actual code to make call to the database and print the output data. + */ +int +sql_exec(PGconn *conn, const char *todo, bool quiet) +{ + PGresult *res; + + int nfields; + int nrows; + int i, + j, + l; + int *length; + char *pad; + + /* make the call */ + res = PQexec(conn, todo); + + /* check and deal with errors */ + if (!res || PQresultStatus(res) > 2) + { + pg_log_error("query failed: %s", PQerrorMessage(conn)); + pg_log_error_detail("Query was: %s", todo); + + PQclear(res); + PQfinish(conn); + exit(1); + } + + /* get the number of fields */ + nrows = PQntuples(res); + nfields = PQnfields(res); + + /* for each field, get the needed width */ + length = (int *) pg_malloc(sizeof(int) * nfields); + for (j = 0; j < nfields; j++) + length[j] = strlen(PQfname(res, j)); + + for (i = 0; i < nrows; i++) + { + for (j = 0; j < nfields; j++) + { + l = strlen(PQgetvalue(res, i, j)); + if (l > length[j]) + length[j] = strlen(PQgetvalue(res, i, j)); + } + } + + /* print a header */ + if (!quiet) + { + for (j = 0, l = 0; j < nfields; j++) + { + fprintf(stdout, "%*s", length[j] + 2, PQfname(res, j)); + l += length[j] + 2; + } + fprintf(stdout, "\n"); + pad = (char *) pg_malloc(l + 1); + MemSet(pad, '-', l); + pad[l] = '\0'; + fprintf(stdout, "%s\n", pad); + free(pad); + } + + /* for each row, dump the information */ + for (i = 0; i < nrows; i++) + { + for (j = 0; j < nfields; j++) + fprintf(stdout, "%*s", length[j] + 2, PQgetvalue(res, i, j)); + fprintf(stdout, "\n"); + } + + /* cleanup */ + PQclear(res); + free(length); + + return 0; +} + +/* + * Dump all databases. There are no system objects to worry about. + */ +void +sql_exec_dumpalldbs(PGconn *conn, struct options *opts) +{ + char todo[1024]; + + /* get the oid and database name from the system pg_database table */ + snprintf(todo, sizeof(todo), + "SELECT d.oid AS \"Oid\", datname AS \"Database Name\", " + "spcname AS \"Tablespace\" FROM pg_catalog.pg_database d JOIN pg_catalog.pg_tablespace t ON " + "(dattablespace = t.oid) ORDER BY 2"); + + sql_exec(conn, todo, opts->quiet); +} + +/* + * Dump all tables, indexes and sequences in the current database. + */ +void +sql_exec_dumpalltables(PGconn *conn, struct options *opts) +{ + char todo[1024]; + char *addfields = ",c.oid AS \"Oid\", nspname AS \"Schema\", spcname as \"Tablespace\" "; + + snprintf(todo, sizeof(todo), + "SELECT pg_catalog.pg_relation_filenode(c.oid) as \"Filenode\", relname as \"Table Name\" %s " + "FROM pg_catalog.pg_class c " + " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace " + " LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database()," + " pg_catalog.pg_tablespace t " + "WHERE relkind IN (" CppAsString2(RELKIND_RELATION) "," + CppAsString2(RELKIND_MATVIEW) "%s%s) AND " + " %s" + " t.oid = CASE" + " WHEN reltablespace <> 0 THEN reltablespace" + " ELSE dattablespace" + " END " + "ORDER BY relname", + opts->extended ? addfields : "", + opts->indexes ? "," CppAsString2(RELKIND_INDEX) "," CppAsString2(RELKIND_SEQUENCE) : "", + opts->systables ? "," CppAsString2(RELKIND_TOASTVALUE) : "", + opts->systables ? "" : "n.nspname NOT IN ('pg_catalog', 'information_schema') AND n.nspname !~ '^pg_toast' AND"); + + sql_exec(conn, todo, opts->quiet); +} + +/* + * Show oid, filenode, name, schema and tablespace for each of the + * given objects in the current database. + */ +void +sql_exec_searchtables(PGconn *conn, struct options *opts) +{ + char *todo; + char *qualifiers, + *ptr; + char *comma_oids, + *comma_filenodes, + *comma_tables; + bool written = false; + char *addfields = ",c.oid AS \"Oid\", nspname AS \"Schema\", spcname as \"Tablespace\" "; + + /* get tables qualifiers, whether names, filenodes, or OIDs */ + comma_oids = get_comma_elts(opts->oids); + comma_tables = get_comma_elts(opts->tables); + comma_filenodes = get_comma_elts(opts->filenodes); + + /* 80 extra chars for SQL expression */ + qualifiers = (char *) pg_malloc(strlen(comma_oids) + strlen(comma_tables) + + strlen(comma_filenodes) + 80); + ptr = qualifiers; + + if (opts->oids->num > 0) + { + ptr += sprintf(ptr, "c.oid IN (%s)", comma_oids); + written = true; + } + if (opts->filenodes->num > 0) + { + if (written) + ptr += sprintf(ptr, " OR "); + ptr += sprintf(ptr, "pg_catalog.pg_relation_filenode(c.oid) IN (%s)", comma_filenodes); + written = true; + } + if (opts->tables->num > 0) + { + if (written) + ptr += sprintf(ptr, " OR "); + sprintf(ptr, "c.relname ~~ ANY (ARRAY[%s])", comma_tables); + } + free(comma_oids); + free(comma_tables); + free(comma_filenodes); + + /* now build the query */ + todo = psprintf("SELECT pg_catalog.pg_relation_filenode(c.oid) as \"Filenode\", relname as \"Table Name\" %s\n" + "FROM pg_catalog.pg_class c\n" + " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n" + " LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database(),\n" + " pg_catalog.pg_tablespace t\n" + "WHERE relkind IN (" CppAsString2(RELKIND_RELATION) "," + CppAsString2(RELKIND_MATVIEW) "," + CppAsString2(RELKIND_INDEX) "," + CppAsString2(RELKIND_SEQUENCE) "," + CppAsString2(RELKIND_TOASTVALUE) ") AND\n" + " t.oid = CASE\n" + " WHEN reltablespace <> 0 THEN reltablespace\n" + " ELSE dattablespace\n" + " END AND\n" + " (%s)\n" + "ORDER BY relname\n", + opts->extended ? addfields : "", + qualifiers); + + free(qualifiers); + + sql_exec(conn, todo, opts->quiet); +} + +void +sql_exec_dumpalltbspc(PGconn *conn, struct options *opts) +{ + char todo[1024]; + + snprintf(todo, sizeof(todo), + "SELECT oid AS \"Oid\", spcname as \"Tablespace Name\"\n" + "FROM pg_catalog.pg_tablespace"); + + sql_exec(conn, todo, opts->quiet); +} + +int +main(int argc, char **argv) +{ + struct options *my_opts; + PGconn *pgconn; + + my_opts = (struct options *) pg_malloc(sizeof(struct options)); + + my_opts->oids = (eary *) pg_malloc(sizeof(eary)); + my_opts->tables = (eary *) pg_malloc(sizeof(eary)); + my_opts->filenodes = (eary *) pg_malloc(sizeof(eary)); + + my_opts->oids->num = my_opts->oids->alloc = 0; + my_opts->tables->num = my_opts->tables->alloc = 0; + my_opts->filenodes->num = my_opts->filenodes->alloc = 0; + + /* parse the opts */ + get_opts(argc, argv, my_opts); + + if (my_opts->dbname == NULL) + { + my_opts->dbname = "postgres"; + my_opts->nodb = true; + } + pgconn = sql_conn(my_opts); + + /* display only tablespaces */ + if (my_opts->tablespaces) + { + if (!my_opts->quiet) + printf("All tablespaces:\n"); + sql_exec_dumpalltbspc(pgconn, my_opts); + + PQfinish(pgconn); + exit(0); + } + + /* display the given elements in the database */ + if (my_opts->oids->num > 0 || + my_opts->tables->num > 0 || + my_opts->filenodes->num > 0) + { + if (!my_opts->quiet) + printf("From database \"%s\":\n", my_opts->dbname); + sql_exec_searchtables(pgconn, my_opts); + + PQfinish(pgconn); + exit(0); + } + + /* no elements given; dump the given database */ + if (my_opts->dbname && !my_opts->nodb) + { + if (!my_opts->quiet) + printf("From database \"%s\":\n", my_opts->dbname); + sql_exec_dumpalltables(pgconn, my_opts); + + PQfinish(pgconn); + exit(0); + } + + /* no database either; dump all databases */ + if (!my_opts->quiet) + printf("All databases:\n"); + sql_exec_dumpalldbs(pgconn, my_opts); + + PQfinish(pgconn); + return 0; +} diff --git a/contrib/oid2name/t/001_basic.pl b/contrib/oid2name/t/001_basic.pl new file mode 100644 index 0000000..671bdbd --- /dev/null +++ b/contrib/oid2name/t/001_basic.pl @@ -0,0 +1,17 @@ + +# Copyright (c) 2021-2022, PostgreSQL Global Development Group + +use strict; +use warnings; + +use PostgreSQL::Test::Utils; +use Test::More; + +######################################### +# Basic checks + +program_help_ok('oid2name'); +program_version_ok('oid2name'); +program_options_handling_ok('oid2name'); + +done_testing(); |