pg_dump.c

关系型数据库 Postgresql 6.5.2

/*-------------------------------------------------------------------------
 *
 * pg_dump.c
 *	  pg_dump is an utility for dumping out a postgres database
 * into a script file.
 *
 *	pg_dump will read the system catalogs in a database and
 *	dump out a script that reproduces
 *	the schema of the database in terms of
 *		  user-defined types
 *		  user-defined functions
 *		  tables
 *		  indices
 *		  aggregates
 *		  operators
 *		  ACL - grant/revoke
 *
 * the output script is SQL that is understood by PostgreSQL
 *
 * Copyright (c) 1994, Regents of the University of California
 *
 *
 * IDENTIFICATION
 *	  $Header: /usr/local/cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v 1.115.2.1 1999/09/01 23:06:26 momjian Exp $
 *
 * Modifications - 6/10/96 - [e-mail] - version 1.13.dhb
 *
 *	 Applied 'insert string' patch from "Marc G. Fournier" <[e-mail]>
 *	 Added '-t table' option
 *	 Added '-a' option
 *	 Added '-da' option
 *
 * Modifications - 6/12/96 - [e-mail] - version 1.13.dhb.2
 *
 *	 - Fixed dumpTable output to output lengths for char and varchar types!
 *	 - Added single. quote to twin single quote expansion for 'insert' string
 *	   mode.
 *
 * Modifications - 7/26/96 - [e-mail]
 *
 *	 - Fixed ouput lengths for char and varchar type where the length is variable (-1)
 *
 * Modifications - 6/1/97 - [e-mail]
 * - Added functions to free allocated memory used for retrieving
 *	 indices,tables,inheritance,types,functions and aggregates.
 *	 No more leaks reported by Purify.
 *
 *
 * Modifications - 1/26/98 - [e-mail]
 *		 - Added support for password authentication
 *-------------------------------------------------------------------------
 */

#include <stdlib.h>
#include <unistd.h>				/* for getopt() */
#include <stdio.h>
#include <string.h>
#include <ctype.h>
#include <sys/param.h>			/* for MAXHOSTNAMELEN on most */
#ifdef solaris_sparc
#include <netdb.h>				/* for MAXHOSTNAMELEN on some */
#endif

#include "postgres.h"
#include "access/htup.h"
#include "catalog/pg_type.h"
#include "catalog/pg_language.h"
#include "catalog/pg_index.h"
#include "catalog/pg_trigger.h"
#include "libpq-fe.h"
#ifndef HAVE_STRDUP
#include "strdup.h"
#endif

#ifdef HAVE_TERMIOS_H
#include <termios.h>
#endif

#ifdef __CYGWIN32__
#include <getopt.h>
#endif

#include "pg_dump.h"

static void dumpSequence(FILE *fout, TableInfo tbinfo);
static void dumpACL(FILE *fout, TableInfo tbinfo);
static void dumpTriggers(FILE *fout, const char *tablename,
			 TableInfo *tblinfo, int numTables);
static void dumpRules(FILE *fout, const char *tablename,
		  TableInfo *tblinfo, int numTables);
static char *checkForQuote(const char *s);
static void clearTableInfo(TableInfo *, int);
static void dumpOneFunc(FILE *fout, FuncInfo *finfo, int i,
			TypeInfo *tinfo, int numTypes);
static int	findLastBuiltinOid(void);
static bool isViewRule(char *relname);
static void setMaxOid(FILE *fout);

static void AddAcl(char *aclbuf, const char *keyword);
static char *GetPrivileges(const char *s);
static void becomeUser(FILE *fout, const char *username);

extern char *optarg;
extern int	optind,
			opterr;

/* global decls */
bool		g_verbose;			/* User wants verbose narration of our
								 * activities. */
int			g_last_builtin_oid; /* value of the last builtin oid */
FILE	   *g_fout;				/* the script file */
PGconn	   *g_conn;				/* the database connection */

bool		force_quotes;		/* User wants to suppress double-quotes */
bool		dumpData;			/* dump data using proper insert strings */
bool		attrNames;			/* put attr names into insert strings */
bool		schemaOnly;
bool		dataOnly;
bool		aclsSkip;
bool		dropSchema;

char		g_opaque_type[10];	/* name for the opaque type */

/* placeholders for the delimiters for comments */
char		g_comment_start[10];
char		g_comment_end[10];


static void
usage(const char *progname)
{
	fprintf(stderr,
			"usage:  %s [options] dbname\n", progname);
	fprintf(stderr,
			"\t -a          \t\t dump out only the data, no schema\n");
	fprintf(stderr,
			"\t -c          \t\t clean(drop) schema prior to create\n");
	fprintf(stderr,
			"\t -d          \t\t dump data as proper insert strings\n");
	fprintf(stderr,
			"\t -D          \t\t dump data as inserts"
			" with attribute names\n");
	fprintf(stderr,
			"\t -f filename \t\t script output filename\n");
	fprintf(stderr,
			"\t -h hostname \t\t server host name\n");
	fprintf(stderr,
		"\t -n          \t\t suppress most quotes around identifiers\n");
	fprintf(stderr,
		  "\t -N          \t\t enable most quotes around identifiers\n");
	fprintf(stderr,
			"\t -o          \t\t dump object id's (oids)\n");
	fprintf(stderr,
			"\t -p port     \t\t server port number\n");
	fprintf(stderr,
			"\t -s          \t\t dump out only the schema, no data\n");
	fprintf(stderr,
			"\t -t table    \t\t dump for this table only\n");
	fprintf(stderr,
			"\t -u          \t\t use password authentication\n");
	fprintf(stderr,
			"\t -v          \t\t verbose\n");
	fprintf(stderr,
			"\t -x          \t\t do not dump ACL's (grant/revoke)\n");
	fprintf(stderr,
			"\nIf dbname is not supplied, then the DATABASE environment "
			"variable value is used.\n");
	fprintf(stderr, "\n");

	exit(1);
}

static void
exit_nicely(PGconn *conn)
{
	PQfinish(conn);
	exit(1);
}


/*
 * isViewRule
 *				Determine if the relation is a VIEW
 *
 */
static bool
isViewRule(char *relname)
{
	PGresult   *res;
	int			ntups;
	char		query[MAX_QUERY_SIZE];

	sprintf(query, "select relname from pg_class, pg_rewrite "
			"where pg_class.oid = ev_class "
			"and pg_rewrite.ev_type = '1' "
			"and rulename = '_RET%s'", relname);

	res = PQexec(g_conn, query);
	if (!res ||
		PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		fprintf(stderr, "isViewRule(): SELECT failed.  Explanation from backend: '%s'.\n", PQerrorMessage(g_conn));
		exit_nicely(g_conn);
	}

	ntups = PQntuples(res);

	PQclear(res);
	return ntups > 0 ? TRUE : FALSE;
}

#define COPYBUFSIZ		8192


static void
dumpClasses_nodumpData(FILE *fout, const char *classname, const bool oids)
{

	PGresult   *res;
	char		query[255];
	int			ret;
	bool		copydone;
	char		copybuf[COPYBUFSIZ];

	if (oids == true)
	{
		fprintf(fout, "COPY %s WITH OIDS FROM stdin;\n",
				fmtId(classname, force_quotes));
		sprintf(query, "COPY %s WITH OIDS TO stdout;\n",
				fmtId(classname, force_quotes));
	}
	else
	{
		fprintf(fout, "COPY %s FROM stdin;\n", fmtId(classname, force_quotes));
		sprintf(query, "COPY %s TO stdout;\n", fmtId(classname, force_quotes));
	}
	res = PQexec(g_conn, query);
	if (!res ||
		PQresultStatus(res) == PGRES_FATAL_ERROR)
	{
		fprintf(stderr, "SQL query to dump the contents of Table '%s' "
				"did not execute.  Explanation from backend: '%s'.\n"
				"The query was: '%s'.\n",
				classname, PQerrorMessage(g_conn), query);
		exit_nicely(g_conn);
	}
	else
	{
		if (PQresultStatus(res) != PGRES_COPY_OUT)
		{
			fprintf(stderr, "SQL query to dump the contents of Table '%s' "
					"executed abnormally.\n"
					"PQexec() returned status %d when %d was expected.\n"
					"The query was: '%s'.\n",
				  classname, PQresultStatus(res), PGRES_COPY_OUT, query);
			exit_nicely(g_conn);
		}
		else
		{
			copydone = false;
			while (!copydone)
			{
				ret = PQgetline(g_conn, copybuf, COPYBUFSIZ);

				if (copybuf[0] == '\\' &&
					copybuf[1] == '.' &&
					copybuf[2] == '\0')
				{
					copydone = true;	/* don't print this... */
				}
				else
				{
					fputs(copybuf, fout);
					switch (ret)
					{
						case EOF:
							copydone = true;
							/* FALLTHROUGH */
						case 0:
							fputc('\n', fout);
							break;
						case 1:
							break;
					}
				}
			}
			fprintf(fout, "\\.\n");
		}
		ret = PQendcopy(g_conn);
		if (ret != 0)
		{
			fprintf(stderr, "SQL query to dump the contents of Table '%s' "
					"did not execute correctly.  After we read all the "
				 "table contents from the backend, PQendcopy() failed.  "
					"Explanation from backend: '%s'.\n"
					"The query was: '%s'.\n",
					classname, PQerrorMessage(g_conn), query);
			PQclear(res);
			exit_nicely(g_conn);
		}
	}
}



static void
dumpClasses_dumpData(FILE *fout, const char *classname,
					 const TableInfo tblinfo, bool oids)
{
	PGresult   *res;
	char		q[MAX_QUERY_SIZE];
	int			tuple;
	int			field;
	char	   *expsrc;

	sprintf(q, "SELECT * FROM %s", fmtId(classname, force_quotes));
	res = PQexec(g_conn, q);
	if (!res ||
		PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		fprintf(stderr, "dumpClasses(): command failed.  Explanation from backend: '%s'.\n", PQerrorMessage(g_conn));
		exit_nicely(g_conn);
	}
	for (tuple = 0; tuple < PQntuples(res); tuple++)
	{
		fprintf(fout, "INSERT INTO %s ", fmtId(classname, force_quotes));
		if (attrNames == true)
		{
			sprintf(q, "(");
			for (field = 0; field < PQnfields(res); field++)
			{
				if (field > 0)
					strcat(q, ",");
				strcat(q, fmtId(PQfname(res, field), force_quotes));
			}
			strcat(q, ") ");
			fprintf(fout, "%s", q);
		}
		fprintf(fout, "VALUES (");
		for (field = 0; field < PQnfields(res); field++)
		{
			if (field > 0)
				fprintf(fout, ",");
			if (PQgetisnull(res, tuple, field))
			{
				fprintf(fout, "NULL");
				continue;
			}
			switch (PQftype(res, field))
			{
				case INT2OID:
				case INT4OID:
				case OIDOID:		/* int types */
				case FLOAT4OID:
				case FLOAT8OID:		/* float types */
					/* These types are printed without quotes */
					fprintf(fout, "%s",
							PQgetvalue(res, tuple, field));
					break;
				default:
					/*
					 * All other types are printed as string literals,
					 * with appropriate escaping of special
					 * characters. Quote mark ' goes to '' per SQL
					 * standard, other stuff goes to \ sequences.
					 */
					putc('\'', fout);
					expsrc = PQgetvalue(res, tuple, field);
					while (*expsrc)
					{
						char		ch = *expsrc++;

						if (ch == '\\' || ch == '\'')
						{
							putc(ch, fout);			/* double these */
							putc(ch, fout);
						}
						else if (ch < '\040')
						{
							/* generate octal escape for control chars */
							putc('\\', fout);
							putc(((ch >> 6) & 3) + '0', fout);
							putc(((ch >> 3) & 7) + '0', fout);
							putc((ch & 7) + '0', fout);
						}
						else
							putc(ch, fout);
					}
					putc('\'', fout);
					break;
			}
		}
		fprintf(fout, ");\n");
	}
	PQclear(res);
}



/*
 * DumpClasses -
 *	  dump the contents of all the classes.
 */
static void
dumpClasses(const TableInfo *tblinfo, const int numTables, FILE *fout,
			const char *onlytable, const bool oids)
{

	int			i;
	char	   *all_only;

	if (onlytable == NULL)
		all_only = "all";
	else
		all_only = "only";

	if (g_verbose)
		fprintf(stderr, "%s dumping out the contents of %s %d table%s/sequence%s %s\n",
				g_comment_start, all_only,
				(onlytable == NULL) ? numTables : 1,
				(onlytable == NULL) ? "s" : "", (onlytable == NULL) ? "s" : "",
				g_comment_end);

	/* Dump SEQUENCEs first (if dataOnly) */
	if (dataOnly)
	{
		for (i = 0; i < numTables; i++)
		{
			if (!(tblinfo[i].sequence))
				continue;
			if (!onlytable || (!strcmp(tblinfo[i].relname, onlytable)))
			{
				if (g_verbose)
					fprintf(stderr, "%s dumping out schema of sequence '%s' %s\n",
					 g_comment_start, tblinfo[i].relname, g_comment_end);
				becomeUser(fout, tblinfo[i].usename);
				dumpSequence(fout, tblinfo[i]);
			}
		}
	}

	for (i = 0; i < numTables; i++)
	{
		const char *classname = tblinfo[i].relname;

		/* Skip VIEW relations */
		if (isViewRule(tblinfo[i].relname))
			continue;

		if (tblinfo[i].sequence)/* already dumped */
			continue;

		if (!onlytable || (!strcmp(classname, onlytable)))
		{
			if (g_verbose)
				fprintf(stderr, "%s dumping out the contents of Table '%s' %s\n",
						g_comment_start, classname, g_comment_end);

			becomeUser(fout, tblinfo[i].usename);

			if (!dumpData)
				dumpClasses_nodumpData(fout, classname, oids);
			else
				dumpClasses_dumpData(fout, classname, tblinfo[i], oids);
		}
	}
}


static void
prompt_for_password(char *username, char *password)
{
	char		buf[512];
	int			length;

#ifdef HAVE_TERMIOS_H
	struct termios t_orig,
				t;

#endif

	printf("Username: ");
	fgets(username, 100, stdin);
	length = strlen(username);
	/* skip rest of the line */
	if (length > 0 && username[length - 1] != '\n')
	{
		do
		{
			fgets(buf, 512, stdin);
		} while (buf[strlen(buf) - 1] != '\n');
	}
	if (length > 0 && username[length - 1] == '\n')
		username[length - 1] = '\0';

	printf("Password: ");
#ifdef HAVE_TERMIOS_H
	tcgetattr(0, &t);
	t_orig = t;
	t.c_lflag &= ~ECHO;
	tcsetattr(0, TCSADRAIN, &t);
#endif
	fgets(password, 100, stdin);
#ifdef HAVE_TERMIOS_H
	tcsetattr(0, TCSADRAIN, &t_orig);
#endif

	length = strlen(password);
	/* skip rest of the line */
	if (length > 0 && password[length - 1] != '\n')
	{
		do
		{
			fgets(buf, 512, stdin);
		} while (buf[strlen(buf) - 1] != '\n');
	}
	if (length > 0 && password[length - 1] == '\n')
		password[length - 1] = '\0';

	printf("\n\n");
}


int
main(int argc, char **argv)