2 oid2name; a postgresql 7.1 (+?) app to map OIDs on the filesystem
3 to table and database names.
5 b. palmer, bpalmer@crimelabs.net 1-17-2001
8 #include "postgres_fe.h"
17 /* these are the opts structures for command line params */
41 /* function prototypes */
42 void get_opts(int, char **, struct options *);
43 PGconn *sql_conn(const char *, struct options *);
44 void sql_exec_error(int);
45 int sql_exec(PGconn *, const char *, int);
46 void sql_exec_dumpdb(PGconn *);
47 void sql_exec_dumptable(PGconn *, int);
48 void sql_exec_searchtable(PGconn *, const char *);
49 void sql_exec_searchoid(PGconn *, int);
51 /* fuction to parse command line options and check for some usage errors. */
53 get_opts(int argc, char **argv, struct options * my_opts)
57 /* set the defaults */
58 my_opts->getdatabase = 0;
59 my_opts->gettable = 0;
62 my_opts->systables = 0;
64 my_opts->remotehost = 0;
65 my_opts->remoteport = 0;
66 my_opts->remoteuser = 0;
67 my_opts->remotepass = 0;
70 while ((c = getopt(argc, argv, "H:p:U:P:d:t:o:xh?")) != -1)
74 /* specify the database */
76 my_opts->getdatabase = 1;
77 sscanf(optarg, "%s", my_opts->_dbname);
80 /* specify the table name */
82 /* make sure we set the database first */
83 if (!my_opts->getdatabase)
85 fprintf(stderr, "Sorry, but you must specify a database to dump from.\n");
88 /* make sure we don't try to do a -o also */
91 fprintf(stderr, "Sorry, you can only specify either oid or table\n");
95 my_opts->gettable = 1;
96 sscanf(optarg, "%s", my_opts->_tbname);
100 /* specify the oid int */
102 /* make sure we set the database first */
103 if (!my_opts->getdatabase)
105 fprintf(stderr, "Sorry, but you must specify a database to dump from.\n");
108 /* make sure we don't try to do a -t also */
109 if (my_opts->gettable)
111 fprintf(stderr, "Sorry, you can only specify either oid or table\n");
116 sscanf(optarg, "%i", &my_opts->_oid);
120 /* host to connect to */
122 my_opts->remotehost = 1;
123 sscanf(optarg, "%s", my_opts->_hostname);
126 /* port to connect to on remote host */
128 my_opts->remoteport = 1;
129 sscanf(optarg, "%s", my_opts->_port);
134 my_opts->remoteuser = 1;
135 sscanf(optarg, "%s", my_opts->_username);
140 my_opts->remotepass = 1;
141 sscanf(optarg, "%s", my_opts->_password);
144 /* display system tables */
146 my_opts->systables = 1;
149 /* help! (ugly in code for easier editing) */
153 Usage: pg_oid2name [-d database [-x] ] [-t table | -o oid] \n\
154 default action display all databases\n\
155 -d database database to oid2name\n\
156 -x display system tables\n\
157 -t table | -o oid search for table name (-t) or\n\
158 oid (-o) in -d database\n\
159 -H host connect to remote host\n\
160 -p port host port to connect to\n\
161 -U username username to connect with\n\
162 -P password password for username\n\n\
170 /* establish connection with database. */
172 sql_conn(const char *dbName, struct options * my_opts)
185 pgoptions = NULL; /* special options to start up the backend
187 pgtty = NULL; /* debugging tty for the backend server */
191 /* override the NULLs with the user params if passed */
192 if (my_opts->remotehost)
194 pghost = (char *) malloc(128);
195 sscanf(my_opts->_hostname, "%s", pghost);
198 if (my_opts->remoteport)
200 pgport = (char *) malloc(6);
201 sscanf(my_opts->_port, "%s", pgport);
204 if (my_opts->remoteuser)
206 pguser = (char *) malloc(128);
207 sscanf(my_opts->_username, "%s", pguser);
210 if (my_opts->remotepass)
212 pgpass = (char *) malloc(128);
213 sscanf(my_opts->_password, "%s", pgpass);
217 conn = PQsetdbLogin(pghost, pgport, pgoptions, pgtty, dbName, pguser, pgpass);
219 /* deal with errors */
220 if (PQstatus(conn) == CONNECTION_BAD)
222 fprintf(stderr, "Connection to database '%s' failed.\n", dbName);
223 fprintf(stderr, "%s", PQerrorMessage(conn));
229 /* free data structures: not strictly necessary */
239 /* return the conn if good */
243 /* If the sql_ command has an error, this function looks up the error number and prints it out. */
245 sql_exec_error(int error_number)
247 fprintf(stderr, "Error number %i.\n", error_number);
248 switch (error_number)
251 fprintf(stderr, "Error: PGRES_COPY_OUT\n");
255 fprintf(stderr, "Error: PGRES_COPY_IN\n");
259 fprintf(stderr, "Error: PGRES_BAD_RESPONCE\n");
263 fprintf(stderr, "Error: PGRES_NONFATAL_ERROR\n");
267 fprintf(stderr, "Error: PGRES_FATAL_ERROR\n");
272 /* actual code to make call to the database and print the output data */
274 sql_exec(PGconn *conn, const char *todo, int match)
284 res = PQexec(conn, todo);
286 /* check and deal with errors */
287 if (!res || PQresultStatus(res) > 2)
289 error_number = PQresultStatus(res);
290 fprintf(stderr, "There was an error in the SQL command:\n%s\n", todo);
291 sql_exec_error(error_number);
292 fprintf(stderr, "PQerrorMessage = %s\n", PQerrorMessage(conn));
299 /* get the number of fields */
300 numbfields = PQntuples(res);
302 /* if we only expect 1 and there mode than, return -2 */
303 if (match == 1 && numbfields > 1)
306 /* return -1 if there aren't any returns */
307 if (match == 1 && numbfields < 1)
310 /* for each row, dump the information */
311 for (i = 0; i < numbfields; i++)
313 len = strlen(PQgetvalue(res, i, 0));
315 fprintf(stdout, "%-6s = %s\n", PQgetvalue(res, i, 0), PQgetvalue(res, i, 1));
318 /* clean the PGconn once done */
324 /* dump all databases known by the system table */
326 sql_exec_dumpdb(PGconn *conn)
330 /* get the oid and database name from the system pg_database table */
331 sprintf(todo, "select oid,datname from pg_database");
333 sql_exec(conn, todo, 0);
336 /* display all tables in whatever db we are connected to. don't display the
337 system tables by default */
339 sql_exec_dumptable(PGconn *conn, int systables)
343 /* don't exclude the systables if this is set */
345 sprintf(todo, "select relfilenode,relname from pg_class order by relname");
347 sprintf(todo, "select relfilenode,relname from pg_class where relname not like 'pg_%%' order by relname");
349 sql_exec(conn, todo, 0);
352 /* display the oid for a given tablename for whatever db we are connected
353 to. do we want to allow %bar% in the search? Not now. */
355 sql_exec_searchtable(PGconn *conn, const char *tablename)
360 /* get the oid and tablename where the name matches tablename */
361 sprintf(todo, "select relfilenode,relname from pg_class where relname = '%s'", tablename);
363 returnvalue = sql_exec(conn, todo, 1);
365 /* deal with the return errors */
366 if (returnvalue == -1)
367 printf("No tables with that name found\n");
369 if (returnvalue == -2)
370 printf("VERY scary: more than one table with that name found!!\n");
375 sql_exec_searchoid(PGconn *conn, int oid)
380 sprintf(todo, "select relfilenode,relname from pg_class where oid = %i", oid);
382 returnvalue = sql_exec(conn, todo, 1);
384 if (returnvalue == -1)
385 printf("No tables with that oid found\n");
387 if (returnvalue == -2)
388 printf("VERY scary: more than one table with that oid found!!\n");
392 main(int argc, char **argv)
394 struct options *my_opts;
397 my_opts = (struct options *) malloc(sizeof(struct options));
400 get_opts(argc, argv, my_opts);
402 /* display all the tables in the database */
403 if (my_opts->getdatabase & my_opts->gettable)
405 printf("Oid of table %s from database \"%s\":\n", my_opts->_tbname, my_opts->_dbname);
406 printf("_______________________________\n");
408 pgconn = sql_conn(my_opts->_dbname, my_opts);
409 sql_exec_searchtable(pgconn, my_opts->_tbname);
415 /* search for the tablename of the given OID */
416 if (my_opts->getdatabase & my_opts->getoid)
418 printf("Tablename of oid %i from database \"%s\":\n", my_opts->_oid, my_opts->_dbname);
419 printf("---------------------------------\n");
421 pgconn = sql_conn(my_opts->_dbname, my_opts);
422 sql_exec_searchoid(pgconn, my_opts->_oid);
428 /* search for the oid for the given tablename */
429 if (my_opts->getdatabase)
431 printf("All tables from database \"%s\":\n", my_opts->_dbname);
432 printf("---------------------------------\n");
434 pgconn = sql_conn(my_opts->_dbname, my_opts);
435 sql_exec_dumptable(pgconn, my_opts->systables);
441 /* display all the databases for the server we are connected to.. */
442 printf("All databases:\n");
443 printf("---------------------------------\n");
445 pgconn = sql_conn("template1", my_opts);
446 sql_exec_dumpdb(pgconn);