aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--contrib/oid2name/Makefile35
-rw-r--r--contrib/oid2name/README.oid2name72
-rwxr-xr-xcontrib/oid2name/oid2namebin0 -> 18145 bytes
-rw-r--r--contrib/oid2name/oid2name.c448
-rw-r--r--contrib/oid2name/oid2name.obin0 -> 15812 bytes
5 files changed, 555 insertions, 0 deletions
diff --git a/contrib/oid2name/Makefile b/contrib/oid2name/Makefile
new file mode 100644
index 00000000000..616e0da381c
--- /dev/null
+++ b/contrib/oid2name/Makefile
@@ -0,0 +1,35 @@
+#
+#
+
+subdir = contrib/oid2name
+top_builddir = /pgtop/
+include $(top_builddir)/src/Makefile.global
+
+override CPPFLAGS += -I$(libpq_srcdir)
+
+OBJS = oid2name.o
+
+all: oid2name
+
+oid2name: $(OBJS) $(libpq_builddir)/libpq.a
+ $(CC) $(CFLAGS) $(LDFLAGS) $(OBJS) $(libpq) $(LIBS) -o $@ -g
+
+install: all installdirs
+ $(INSTALL_PROGRAM) oid2name$(X) $(bindir)
+ $(INSTALL_DATA) README.oid2name $(docdir)/contrib
+
+installdirs:
+ $(mkinstalldirs) $(bindir) $(docdir)/contrib
+
+uninstall:
+ rm -f $(bindir)/oid2name$(X) $(docdir)/contrib/README.oid2name
+
+clean distclean maintainer-clean:
+ rm -f oid2name$(X) $(OBJS)
+
+depend dep:
+ $(CC) -MM -MG $(CFLAGS) *.c > depend
+
+ifeq (depend,$(wildcard depend))
+include depend
+endif
diff --git a/contrib/oid2name/README.oid2name b/contrib/oid2name/README.oid2name
new file mode 100644
index 00000000000..39a3cc60ab2
--- /dev/null
+++ b/contrib/oid2name/README.oid2name
@@ -0,0 +1,72 @@
+With version 7.1 of PostgreSQL server, the old naming scheme for
+databases and tables (in $PGDATA/base) has changed. The databases
+are put in folders for their OID in pg_database and the tables in
+that folder are named for their OIDs in pg_class. This app connects
+to the database (you can specify host, port, user, pass etc to
+connect to a host other than localhost) and extracts the OID and
+table name information. It has 4 ways it can be run:
+
+ pg_oid2name
+
+ This will connect to the template1 database and display all databases
+ in the system.
+
+ $ ./pg_oid2name
+ All databases:
+ ---------------------------------
+ 18720 = test1
+ 1 = template1
+ 18719 = template0
+ 18721 = test
+ 18735 = postgres
+ 18736 = cssi
+
+ pg_oid2name -d test [-x]
+
+ This connects to the database test and shows all tables and their OIDs.
+
+ $ ./pg_oid2name -d test
+ All tables from database "test":
+ ---------------------------------
+ 18766 = dns
+ 18737 = ips
+ 18722 = testdate
+
+ pg_oid2name -d test -o 18737 or
+ pg_oid2name -d test -t testdate
+
+ This will connect to the database test and display the table name for oid
+ 18737 and the oid for table name testdate respectivly.
+
+ $ ./pg_oid2name -d test -o 18737
+ Tablename of oid 18737 from database "test":
+ ---------------------------------
+ 18737 = ips
+
+
+ $ ./pg_oid2name -d test -t testdate
+ Oid of table testdate from database "test":
+ _______________________________
+ 18722 = testdate
+
+
+There should be a decent amount of error handling in the app, a lot of it
+ dealt with via the postgres function calls.
+
+ $ ./pg_oid2name -d nothere -t testdate
+ Oid of table testdate from database "nothere":
+ _______________________________
+ Connection to database 'nothere' failed.
+ FATAL 1: Database "nothere" does not exist in the system catalog.
+
+ $ ./pg_oid2name -d test -t nothere
+ Oid of table nothere from database "test":
+ _______________________________
+ No tables with that name found
+
+
+Mail me with any problems or additions you would like to see. Clearing
+ house for the code will be at: http://www.crimelabs.net
+
+b. palmer, bpalmer@crimelabs.net
+
diff --git a/contrib/oid2name/oid2name b/contrib/oid2name/oid2name
new file mode 100755
index 00000000000..eec4a6082fe
--- /dev/null
+++ b/contrib/oid2name/oid2name
Binary files differ
diff --git a/contrib/oid2name/oid2name.c b/contrib/oid2name/oid2name.c
new file mode 100644
index 00000000000..ca5ca1d17d6
--- /dev/null
+++ b/contrib/oid2name/oid2name.c
@@ -0,0 +1,448 @@
+/*
+ oid2name; a postgresql 7.1 (+?) app to map OIDs on the filesystem
+ to table and database names.
+
+ b. palmer, bpalmer@crimelabs.net 1-17-2001
+
+
+ * Portions Copyright (c) 1996-2000, PostgreSQL, Inc
+ * Portions Copyright (c) 1994, Regents of the University of California
+
+ */
+
+#include <stdlib.h>
+#include <string.h>
+
+#include "libpq-fe.h"
+
+/* these are the opts structures for command line params */
+struct options {
+ int getdatabase;
+ int gettable;
+ int getoid;
+
+ int systables;
+
+ int remotehost;
+ int remoteport;
+ int remoteuser;
+ int remotepass;
+
+ int _oid;
+ char _dbname[128];
+ char _tbname[128];
+
+ char _hostname[128];
+ char _port[6];
+ char _username[128];
+ char _password[128];
+};
+
+/* function prototypes */
+void get_opts(int, char **, struct options *);
+PGconn *sql_conn(char *, struct options *);
+void sql_exec_error (int);
+int sql_exec(PGconn *, char *, int);
+void sql_exec_dumpdb(PGconn *);
+void sql_exec_dumptable(PGconn *, int);
+void sql_exec_searchtable(PGconn *, char *);
+void sql_exec_searchoid(PGconn *, int);
+
+/* fuction to parse command line options and check for some usage errors. */
+void get_opts(int argc, char **argv, struct options *my_opts)
+{
+ char c;
+
+ /* set the defaults */
+ my_opts->getdatabase = 0;
+ my_opts->gettable = 0;
+ my_opts->getoid = 0;
+
+ my_opts->systables = 0;
+
+ my_opts->remotehost = 0;
+ my_opts->remoteport = 0;
+ my_opts->remoteuser = 0;
+ my_opts->remotepass = 0;
+
+ /* get opts */
+ while( (c = getopt(argc, argv, "H:p:U:P:d:t:o:xh?")) != EOF)
+ {
+ switch(c)
+ {
+ /* specify the database */
+ case 'd':
+ my_opts->getdatabase = 1;
+ sscanf(optarg, "%s", my_opts->_dbname);
+ break;
+
+ /* specify the table name */
+ case 't':
+ /* make sure we set the database first */
+ if(!my_opts->getdatabase)
+ {
+ fprintf(stderr, "Sorry, but you must specify a database to dump from.\n");
+ exit(1);
+ }
+ /* make sure we don't try to do a -o also */
+ if(my_opts->getoid)
+ {
+ fprintf(stderr, "Sorry, you can only specify either oid or table\n");
+ exit(1);
+ }
+
+ my_opts->gettable = 1;
+ sscanf(optarg, "%s", my_opts->_tbname);
+
+ break;
+
+ /* specify the oid int */
+ case 'o':
+ /* make sure we set the database first */
+ if(!my_opts->getdatabase)
+ {
+ fprintf(stderr, "Sorry, but you must specify a database to dump from.\n");
+ exit(1);
+ }
+ /* make sure we don't try to do a -t also */
+ if(my_opts->gettable)
+ {
+ fprintf(stderr, "Sorry, you can only specify either oid or table\n");
+ exit(1);
+ }
+
+ my_opts->getoid = 1;
+ sscanf(optarg, "%i", &my_opts->_oid);
+
+ break;
+
+ /* host to connect to */
+ case 'H':
+ my_opts->remotehost = 1;
+ sscanf(optarg, "%s", my_opts->_hostname);
+ break;
+
+ /* port to connect to on remote host */
+ case 'p':
+ my_opts->remoteport = 1;
+ sscanf(optarg, "%s", my_opts->_port);
+ break;
+
+ /* username */
+ case 'U':
+ my_opts->remoteuser = 1;
+ sscanf(optarg, "%s", my_opts->_username);
+ break;
+
+ /* password */
+ case 'P':
+ my_opts->remotepass = 1;
+ sscanf(optarg, "%s", my_opts->_password);
+ break;
+
+ /* display system tables */
+ case 'x':
+
+ my_opts->systables = 1;
+ break;
+
+ /* help! (ugly in code for easier editing) */
+ case '?':
+ case 'h':
+ fprintf(stderr, "\n\
+Usage: pg_oid2name [-d database [-x] ] [-t table | -o oid] \n\
+ dafault action display all databases
+ -d database database to oid2name\n\
+ -x display system tables\n\
+ -t table | -o oid search for table name (-t) or\n\
+ oid (-o) in -d database
+ -H host connect to remote host\n\
+ -p port host port to connect to\n\
+ -U username username to connect with\n\
+ -P password password for username\n\n\
+");
+ exit(1);
+ break;
+ }
+ }
+}
+
+/* establish connection with database. */
+PGconn *sql_conn(char *dbName, struct options *my_opts)
+{
+ char *pghost, *pgport;
+ char *pgoptions, *pgtty;
+ char *pguser, *pgpass;
+
+ PGconn *conn;
+
+ pghost = NULL;
+ pgport = NULL;
+
+ pgoptions = NULL; /* special options to start up the backend
+ * server */
+ pgtty = NULL; /* debugging tty for the backend server */
+
+ pguser = NULL;
+ pgpass = NULL;
+
+ /* override the NULLs with the user params if passed */
+ if(my_opts->remotehost)
+ {
+ pghost = (char *) malloc (128);
+ sscanf(my_opts->_hostname, "%s", pghost);
+ }
+
+ if(my_opts->remoteport)
+ {
+ pgport = (char *) malloc (6);
+ sscanf(my_opts->_port, "%s", pgport);
+ }
+
+ if(my_opts->remoteuser)
+ {
+ pguser = (char *) malloc (128);
+ sscanf(my_opts->_username, "%s", pguser);
+ }
+
+ if(my_opts->remotepass)
+ {
+ pgpass = (char *) malloc (128);
+ sscanf(my_opts->_password, "%s", pgpass);
+ }
+
+ /* login */
+ conn = PQsetdbLogin(pghost, pgport, pgoptions, pgtty, dbName, pguser, pgpass);
+
+ /* deal with errors */
+ if (PQstatus(conn) == CONNECTION_BAD)
+ {
+ fprintf(stderr, "Connection to database '%s' failed.\n", dbName);
+ fprintf(stderr, "%s", PQerrorMessage(conn));
+
+
+ PQfinish(conn);
+ exit(1);
+
+ }
+
+ /* return the conn if good */
+ return conn;
+}
+
+/* If the sql_ command has an error, this function looks up the error number and prints it out. */
+void sql_exec_error (int error_number)
+{
+ fprintf(stderr, "Error number %i.\n", error_number);
+ switch(error_number)
+ {
+ case 3:
+ fprintf(stderr, "Error: PGRES_COPY_OUT\n");
+ break;
+
+ case 4:
+ fprintf(stderr, "Error: PGRES_COPY_IN\n");
+ break;
+
+ case 5:
+ fprintf(stderr, "Error: PGRES_BAD_RESPONCE\n");
+ break;
+
+ case 6:
+ fprintf(stderr, "Error: PGRES_NONFATAL_ERROR\n");
+ break;
+
+ case 7:
+ fprintf(stderr, "Error: PGRES_FATAL_ERROR\n");
+ break;
+ }
+}
+
+/* actual code to make call to the database and print the output data */
+int sql_exec(PGconn *conn, char *todo, int match)
+{
+ PGresult *res;
+
+ int numbfields;
+ int error_number;
+ int i, len;
+
+ /* make the call */
+ res = PQexec(conn, todo);
+
+ /* check and deal with errors */
+ if (!res || PQresultStatus(res) > 2)
+ {
+ error_number = PQresultStatus(res);
+ fprintf(stderr, "There was an error in the SQL command:\n%s\n", todo);
+ sql_exec_error(error_number);
+ fprintf(stderr, "PQerrorMessage = %s\n", PQerrorMessage(conn));
+
+ PQclear(res);
+ PQfinish(conn);
+ exit(-1);
+ }
+
+ /* get the number of fields */
+ numbfields = PQntuples(res);
+
+ /* if we only expect 1 and there mode than, return -2 */
+ if(match == 1 && numbfields > 1)
+ return -2;
+
+ /* return -1 if there aren't any returns */
+ if(match == 1 && numbfields < 1)
+ return -1;
+
+ /* for each row, dump the information */
+ for(i = 0; i < numbfields; i++)
+ {
+ len = strlen(PQgetvalue(res, i, 0));
+
+ fprintf(stdout, "%-6s = %s\n", PQgetvalue(res, i, 0), PQgetvalue(res, i, 1));
+ }
+
+ /* clean the PGconn once done */
+ PQclear(res);
+
+ return 0;
+}
+
+/* dump all databases know by the system table */
+void sql_exec_dumpdb(PGconn *conn)
+{
+ char *todo;
+
+ todo = (char *) malloc (1024);
+
+ /* get the oid and database name from the system pg_database table */
+ sprintf(todo, "select oid,datname from pg_database");
+
+ sql_exec(conn, todo, NULL);
+}
+
+/* display all tables in whatever db we are connected to. don't display the
+ system tables by default */
+void sql_exec_dumptable(PGconn *conn, int systables)
+{
+ char *todo;
+
+ todo = (char *) malloc (1024);
+
+ /* don't exclude the systables if this is set */
+ if(systables == 1)
+ sprintf(todo, "select oid,relname from pg_class order by relname");
+ else
+ sprintf(todo, "select oid,relname from pg_class where relname not like 'pg_%%' order by relname");
+
+ sql_exec(conn, todo, NULL);
+}
+
+/* display the oid for a given tablename for whatever db we are connected
+ to. do we want to allow %bar% in the search? Not now. */
+void sql_exec_searchtable(PGconn *conn, char *tablename)
+{
+ int returnvalue;
+ char *todo;
+
+ todo = (char *) malloc (1024);
+
+ /* get the oid and tablename where the name matches tablename */
+ sprintf(todo, "select oid,relname from pg_class where relname = '%s'", tablename);
+
+ returnvalue = sql_exec(conn, todo, 1);
+
+ /* deal with the return errors */
+ if(returnvalue == -1)
+ {
+ printf("No tables with that name found\n");
+ }
+
+ if(returnvalue == -2)
+ {
+ printf("VERY scary: more than one table with that name found!!\n");
+ }
+}
+
+/* same as above */
+void sql_exec_searchoid(PGconn *conn, int oid)
+{
+ int returnvalue;
+ char *todo;
+
+ todo = (char *) malloc (1024);
+
+ sprintf(todo, "select oid,relname from pg_class where oid = %i", oid);
+
+ returnvalue = sql_exec(conn, todo, 1);
+
+ if(returnvalue == -1)
+ {
+ printf("No tables with that oid found\n");
+ }
+
+ if(returnvalue == -2)
+ {
+ printf("VERY scary: more than one table with that oid found!!\n");
+ }
+}
+
+int main(int argc, char **argv)
+{
+ struct options *my_opts;
+ PGconn *pgconn;
+
+ my_opts = (struct options *) malloc (sizeof(struct options));
+
+ /* parse the opts */
+ get_opts(argc, argv, my_opts);
+
+ /* display all the tables in the database */
+ if(my_opts->getdatabase & my_opts->gettable)
+ {
+ printf("Oid of table %s from database \"%s\":\n", my_opts->_tbname, my_opts->_dbname);
+ printf("_______________________________\n");
+
+ pgconn = sql_conn(my_opts->_dbname, my_opts);
+ sql_exec_searchtable(pgconn, my_opts->_tbname);
+ PQfinish(pgconn);
+
+ exit(1);
+ }
+
+ /* search for the tablename of the given OID */
+ if(my_opts->getdatabase & my_opts->getoid)
+ {
+ printf("Tablename of oid %i from database \"%s\":\n", my_opts->_oid, my_opts->_dbname);
+ printf("---------------------------------\n");
+
+ pgconn = sql_conn(my_opts->_dbname, my_opts);
+ sql_exec_searchoid(pgconn, my_opts->_oid);
+ PQfinish(pgconn);
+
+ exit(1);
+ }
+
+ /* search for the oid for the given tablename */
+ if(my_opts->getdatabase)
+ {
+ printf("All tables from database \"%s\":\n", my_opts->_dbname);
+ printf("---------------------------------\n");
+
+ pgconn = sql_conn(my_opts->_dbname, my_opts);
+ sql_exec_dumptable(pgconn, my_opts->systables);
+ PQfinish(pgconn);
+
+ exit(1);
+ }
+
+ /* display all the databases for the server we are connected to.. */
+ printf("All databases:\n");
+ printf("---------------------------------\n");
+
+ pgconn = sql_conn("template1", my_opts);
+ sql_exec_dumpdb(pgconn);
+ PQfinish(pgconn);
+
+ exit(0);
+}
diff --git a/contrib/oid2name/oid2name.o b/contrib/oid2name/oid2name.o
new file mode 100644
index 00000000000..801882c2d2f
--- /dev/null
+++ b/contrib/oid2name/oid2name.o
Binary files differ
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-29 07:56:18 +0000