#!/usr/bin/perl -w

#
#  oid2name; a postgresql 7.1 (+?) app to map OIDs on the filesystem
#   to table and database names.
#
#  original C version by b. palmer, bpalmer@crimelabs.net 1-17-2001
#  rewritten in perl by Dobrica Pavlinusic <dpavlin@rot13.org> 2003-08-10
#

use strict;
use DBI;
use Getopt::Std;

my %opt;
getopt("H:p:U:P:d:t:o:qxh?v", \%opt);


my $database = $opt{'d'} || 'template1';
my ($user,$passwd) = ($opt{'U'},$opt{'P'});

my $info = "dbname=$database";
print "connecting to $info\n" if ($opt{'v'});
my $dbh = DBI->connect("DBI:Pg:$info", $user, $passwd, { PrintError => 1 });

if ($opt{'h'} || $opt{'?'}) {

} elsif ($opt{'d'} && !$opt{'t'}) {
	print STDERR "All tables from database \"$opt{'d'}\":\n";
	print STDERR "---------------------------------\n";
	if ($opt{'x'}) {
		sql_exec("select relfilenode,relname from pg_class order by relname");
	} else {
		sql_exec("select relfilenode,relname from pg_class
				 where relkind not in ('v','s', 'c') and 
				 relname not like 'pg_%' order by relname");
	}

} elsif($opt{'d'} && $opt{'t'}) {

	print STDERR "Oid of table $opt{'t'} from database \"$opt{'d'}\":\n";
	print STDERR "---------------------------------\n";
	sql_exec("select relfilenode,relname from pg_class where relname = '$opt{'t'}'");

} elsif($opt{'d'} && $opt{'o'}) {

	print STDERR "Tablename of oid $opt{'o'} from database "$opt{'d'}":\n";
	print STDERR "---------------------------------\n";
	sql_exec("select relfilenode,relname from pg_class where oid = '$opt{'o'}'");
} else {

	print "All databases:\n";
	print "---------------------------------\n";

	sql_exec("select oid,datname from pg_database");

}

#--------------------------------------------------------------------

sub sql_exec {
	my $sql = shift || die "sql_exec: need sql as argument";
	my $sth = $dbh->prepare($sql) || die $dbh->errstr();
	$sth->execute() || die $dbh->errstr();
	while(my @row = $sth->fetchrow_array()) {
		printf("%-6s = %s\n",@row);
	}
}

exit;

__END__

--------------------------------------------------------------------------
	while ((c = getopt(argc, argv, "H:p:U:P:d:t:o:qxh?")) != -1)
	{
		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, "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, "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, "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, "You can only specify either oid or table\n");
					exit(1);
				}

				my_opts->getoid = 1;
				sscanf(optarg, "%i", &my_opts->_oid);

				break;

			case 'q':
				my_opts->quiet = 1;
				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, "\
Usage: pg_oid2name [-d database [-x] ] [-t table | -o oid]\n\
        default action        display all databases\n\
        -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\n\
        -q                    quiet\n\
        -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\
");
				exit(1);
				break;
		}
	}
}

/* establish connection with database. */
PGconn *
sql_conn(const 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);
	}

	/* free data structures: not strictly necessary */
	if (pghost != NULL)
		free(pghost);
	if (pgport != NULL)
		free(pgport);
	if (pguser != NULL)
		free(pguser);
	if (pgpass != NULL)
		free(pgpass);

	sql_exec(conn, "SET search_path = public;", 0);

	/* 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, const 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 known by the system table */
void
sql_exec_dumpdb(PGconn *conn)
{
	char		todo[1024];

	/* get the oid and database name from the system pg_database table */
	snprintf(todo, 1024, "select oid,datname from pg_database");

	sql_exec(conn, todo, 0);
}

/* 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[1024];

	/* don't exclude the systables if this is set */
	if (systables == 1)
		snprintf(todo, 1024, "select relfilenode,relname from pg_class order by relname");
	else
		snprintf(todo, 1024, "select relfilenode,relname from pg_class "
				 "where relkind not in ('v','s', 'c') and "
				 "relname not like 'pg_%%' order by relname");

	sql_exec(conn, todo, 0);
}

/* 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, const char *tablename)
{
	int			returnvalue;
	char		todo[1024];

	/* get the oid and tablename where the name matches tablename */
	snprintf(todo, 1024, "select relfilenode,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[1024];

	snprintf(todo, 1024, "select relfilenode,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)
	{
		if (!my_opts->quiet)
		{
			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)
	{
		if (!my_opts->quiet)
		{
			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)
	{
		if (!my_opts->quiet)
		{
			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.. */
	if (!my_opts->quiet)
	{
		printf("All databases:\n");
		printf("---------------------------------\n");
	}
	pgconn = sql_conn("template1", my_opts);
	sql_exec_dumpdb(pgconn);
	PQfinish(pgconn);

	exit(0);
}