psql.c

关系型数据库 Postgresql 6.5.2

/*-------------------------------------------------------------------------
 *
 * psql.c
 *	  an interactive front-end to postgreSQL
 *
 * Copyright (c) 1996, Regents of the University of California
 *
 *
 * IDENTIFICATION
 *	  $Header: /usr/local/cvsroot/pgsql/src/bin/psql/psql.c,v 1.182 1999/06/04 21:21:13 tgl Exp $
 *
 *-------------------------------------------------------------------------
 */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <signal.h>
#include <errno.h>
#include <sys/types.h>
#ifdef WIN32
#define WIN32_LEAN_AND_MEAN
#include <windows.h>
#include <io.h>
#else
#include <sys/param.h>			/* for MAXPATHLEN */
#include <sys/ioctl.h>
#include <unistd.h>
#endif
#include <sys/stat.h>
#include <fcntl.h>
#include <ctype.h>
#include "postgres.h"
#include "libpq-fe.h"
#include "pqsignal.h"
#include "stringutils.h"
#include "psqlHelp.h"
#ifndef HAVE_STRDUP
#include "strdup.h"
#endif
#ifdef HAVE_TERMIOS_H
#include <termios.h>
#endif
#ifdef __CYGWIN32__
#include <getopt.h>
#endif

#ifdef HAVE_LIBREADLINE
#ifdef HAVE_READLINE_H
#include <readline.h>
#define USE_READLINE 1
#if defined(HAVE_HISTORY_H)
#include <history.h>
#define USE_HISTORY 1
#endif
#else
#if defined(HAVE_READLINE_READLINE_H)
#include <readline/readline.h>
#define USE_READLINE 1
#if defined(HAVE_READLINE_HISTORY_H)
#include <readline/history.h>
#define USE_HISTORY 1
#endif
#endif
#endif
#if defined(HAVE_HISTORY) && !defined(USE_HISTORY)
#define USE_HISTORY 1
#endif
#endif


#ifdef WIN32
#define popen(x,y) _popen(x,y)
#define pclose(x) _pclose(x)
#define open(x,y,z) _open(x,y,z)
#define strcasecmp(x,y) stricmp(x,y)
#define pqsignal(x,y)
#define MAXPATHLEN MAX_PATH
#define R_OK 0

/* getopt is not in the standard includes on Win32 */
extern char *optarg;
extern int	optind,
			opterr,
			optopt;
int			getopt(int, char *const[], const char *);
char	   *__progname = "psql";

#endif

#ifdef MULTIBYTE
/* flag to indicate if PGCLIENTENCODING has been set by a user */
static char *has_client_encoding = 0;

#endif

/* This prompt string is assumed to have at least 3 characters by code in MainLoop().
 * A character two characters from the end is replaced each time by a mode character.
 */
#define PROMPT "=> "

#define PROMPT_READY	'='
#define PROMPT_CONTINUE '-'
#define PROMPT_COMMENT	'*'
#define PROMPT_SINGLEQUOTE	'\''
#define PROMPT_DOUBLEQUOTE	'"'

/* Backslash command handling:
 *	0 - send currently constructed query to backend (i.e. we got a \g)
 *	1 - skip processing of this line, continue building up query
 *	2 - terminate processing of this query entirely
 *	3 - new query supplied by edit
 */
#define CMD_UNKNOWN		-1
#define CMD_SEND		0
#define CMD_SKIP_LINE	1
#define CMD_TERMINATE	2
#define CMD_NEWEDIT		3

#define MAX_QUERY_BUFFER MAX_QUERY_SIZE

#define COPYBUFSIZ	8192

#define DEFAULT_FIELD_SEP "|"
#define DEFAULT_EDITOR	"vi"
#define DEFAULT_SHELL  "/bin/sh"

typedef struct _psqlSettings
{
	PGconn	   *db;				/* connection to backend */
	FILE	   *queryFout;		/* where to send the query results */
	PQprintOpt	opt;			/* options to be passed to PQprint */
	char	   *prompt;			/* prompt to display */
	char	   *gfname;			/* one-shot file output argument for \g */
	bool		notty;			/* input or output is not a tty */
	bool		pipe;			/* queryFout is from a popen() */
	bool		echoQuery;		/* echo the query before sending it */
	bool		echoAllQueries; /* echo all queries before sending it */
	bool		quiet;			/* run quietly, no messages, no promt */
	bool		singleStep;		/* prompt before for each query */
	bool		singleLineMode; /* query terminated by newline */
	bool		useReadline;	/* use libreadline routines */
	bool		getPassword;	/* prompt the user for a username and
								 * password */
} PsqlSettings;

/*
 * cur_cmd_source and cur_cmd_interactive are the top of a stack of
 * source files (one stack level per recursive invocation of MainLoop).
 * It's kinda grotty to make these global variables, but the alternative
 * of passing them around through many function parameter lists seems
 * worse.
 */
static FILE *cur_cmd_source = NULL;		/* current source of command input */
static bool cur_cmd_interactive = false;		/* is it an interactive
												 * source? */


#ifdef TIOCGWINSZ
struct winsize screen_size;

#else
struct winsize
{
	int			ws_row;
	int			ws_col;
}			screen_size;

#endif

/* declarations for functions in this file */
static void usage(char *progname);
static void slashUsage();
static bool handleCopyOut(PGconn *conn, FILE *copystream);
static bool handleCopyIn(PGconn *conn, const bool mustprompt,
			 FILE *copystream);
static int tableList(PsqlSettings *pset, bool deep_tablelist,
		  char info_type, bool system_tables);
static int	tableDesc(PsqlSettings *pset, char *table, FILE *fout);
static int	objectDescription(PsqlSettings *pset, char *object);
static int	rightsList(PsqlSettings *pset);
static void emitNtimes(FILE *fout, const char *str, int N);
static void prompt_for_password(char *username, char *password);

static char *gets_noreadline(char *prompt, FILE *source);
static char *gets_readline(char *prompt, FILE *source);
static char *gets_fromFile(char *prompt, FILE *source);
static int	listAllDbs(PsqlSettings *pset);
static bool SendQuery(PsqlSettings *pset, const char *query,
		  FILE *copy_in_stream, FILE *copy_out_stream);
static int	HandleSlashCmds(PsqlSettings *pset, char *line, char *query);
static int	MainLoop(PsqlSettings *pset, char *query, FILE *source);
static FILE *setFout(PsqlSettings *pset, char *fname);

static char *selectVersion(PsqlSettings *pset);

/*
 * usage print out usage for command line arguments
 */

static void
usage(char *progname)
{
	fprintf(stderr, "Usage: %s [options] [dbname]\n", progname);
	fprintf(stderr, "\t -a authsvc              set authentication service\n");
	fprintf(stderr, "\t -A                      turn off alignment when printing out attributes\n");
	fprintf(stderr, "\t -c query                run single query (slash commands too)\n");
	fprintf(stderr, "\t -d dbName               specify database name\n");
	fprintf(stderr, "\t -e                      echo the query sent to the backend\n");
	fprintf(stderr, "\t -E                      echo all queries sent to the backend\n");
	fprintf(stderr, "\t -f filename             use file as a source of queries\n");
	fprintf(stderr, "\t -F sep                  set the field separator (default is '|')\n");
	fprintf(stderr, "\t -h host                 set database server host\n");
	fprintf(stderr, "\t -H                      turn on html3.0 table output\n");
	fprintf(stderr, "\t -l                      list available databases\n");
	fprintf(stderr, "\t -n                      don't use readline library\n");
	fprintf(stderr, "\t -o filename             send output to filename or (|pipe)\n");
	fprintf(stderr, "\t -p port                 set port number\n");
	fprintf(stderr, "\t -q                      run quietly (no messages, no prompts)\n");
	fprintf(stderr, "\t -s                      single step mode (prompts for each query)\n");
	fprintf(stderr, "\t -S                      single line mode (i.e. query terminated by newline)\n");
	fprintf(stderr, "\t -t                      turn off printing of headings and row count\n");
	fprintf(stderr, "\t -T html                 set html3.0 table command options (cf. -H)\n");
	fprintf(stderr, "\t -u                      ask for a username and password for authentication\n");
	fprintf(stderr, "\t -x                      turn on expanded output (field names on left)\n");
	exit(1);
}

/*
 * slashUsage print out usage for the backslash commands
 */

static char *
on(bool f)
{
	return f ? "on" : "off";
}

static void
slashUsage(PsqlSettings *pset)
{
	int			usePipe = 0;
	char	   *pagerenv;
	FILE	   *fout;

#ifdef TIOCGWINSZ
	if (pset->notty == 0 &&
		(ioctl(fileno(stdout), TIOCGWINSZ, &screen_size) == -1 ||
		 screen_size.ws_col == 0 ||
		 screen_size.ws_row == 0))
	{
#endif
		screen_size.ws_row = 24;
		screen_size.ws_col = 80;
#ifdef TIOCGWINSZ
	}
#endif

	if (pset->notty == 0 &&
		(pagerenv = getenv("PAGER")) &&
		(pagerenv[0] != '\0') &&
		screen_size.ws_row <= 35 &&
		(fout = popen(pagerenv, "w")))
	{
		usePipe = 1;
		pqsignal(SIGPIPE, SIG_IGN);
	}
	else
		fout = stdout;

	/* if you add/remove a line here, change the row test above */
	fprintf(fout, " \\?           -- help\n");
	fprintf(fout, " \\a           -- toggle field-alignment (currently %s)\n", on(pset->opt.align));
	fprintf(fout, " \\C [<captn>] -- set html3 caption (currently '%s')\n", pset->opt.caption ? pset->opt.caption : "");
	fprintf(fout, " \\connect <dbname|-> <user> -- connect to new database (currently '%s')\n", PQdb(pset->db));
	fprintf(fout, " \\copy table {from | to} <fname>\n");
	fprintf(fout, " \\d [<table>] -- list tables and indices, columns in <table>, or * for all\n");
	fprintf(fout, " \\da          -- list aggregates\n");
	fprintf(fout, " \\dd [<object>]- list comment for table, field, type, function, or operator.\n");
	fprintf(fout, " \\df          -- list functions\n");
	fprintf(fout, " \\di          -- list only indices\n");
	fprintf(fout, " \\do          -- list operators\n");
	fprintf(fout, " \\ds          -- list only sequences\n");
	fprintf(fout, " \\dS          -- list system tables and indexes\n");
	fprintf(fout, " \\dt          -- list only tables\n");
	fprintf(fout, " \\dT          -- list types\n");
	fprintf(fout, " \\e [<fname>] -- edit the current query buffer or <fname>\n");
	fprintf(fout, " \\E [<fname>] -- edit the current query buffer or <fname>, and execute\n");
	fprintf(fout, " \\f [<sep>]   -- change field separater (currently '%s')\n", pset->opt.fieldSep);
	fprintf(fout, " \\g [<fname>] [|<cmd>] -- send query to backend [and results in <fname> or pipe]\n");
	fprintf(fout, " \\h [<cmd>]   -- help on syntax of sql commands, * for all commands\n");
	fprintf(fout, " \\H           -- toggle html3 output (currently %s)\n", on(pset->opt.html3));
	fprintf(fout, " \\i <fname>   -- read and execute queries from filename\n");
	fprintf(fout, " \\l           -- list all databases\n");
	fprintf(fout, " \\m           -- toggle monitor-like table display (currently %s)\n", on(pset->opt.standard));
	fprintf(fout, " \\o [<fname>] [|<cmd>] -- send all query results to stdout, <fname>, or pipe\n");
	fprintf(fout, " \\p           -- print the current query buffer\n");
	fprintf(fout, " \\q           -- quit\n");
	fprintf(fout, " \\r           -- reset(clear) the query buffer\n");
	fprintf(fout, " \\s [<fname>] -- print history or save it in <fname>\n");
	fprintf(fout, " \\t           -- toggle table headings and row count (currently %s)\n", on(pset->opt.header));
	fprintf(fout, " \\T [<html>]  -- set html3.0 <table ...> options (currently '%s')\n", pset->opt.tableOpt ? pset->opt.tableOpt : "");
	fprintf(fout, " \\x           -- toggle expanded output (currently %s)\n", on(pset->opt.expanded));
	fprintf(fout, " \\w <fname>   -- output current buffer to a file\n");
	fprintf(fout, " \\z           -- list current grant/revoke permissions\n");
	fprintf(fout, " \\! [<cmd>]   -- shell escape or command\n");

	if (usePipe)
	{
		pclose(fout);
		pqsignal(SIGPIPE, SIG_DFL);
	}
}

static PGresult *
PSQLexec(PsqlSettings *pset, char *query)
{
	PGresult   *res;

	if (pset->echoAllQueries)
	{
		fprintf(stderr, "QUERY: %s\n", query);
		fprintf(stderr, "\n");
		fflush(stderr);
	}

	res = PQexec(pset->db, query);
	if (!res)
		fputs(PQerrorMessage(pset->db), stderr);
	else
	{
		if (PQresultStatus(res) == PGRES_COMMAND_OK ||
			PQresultStatus(res) == PGRES_TUPLES_OK)
			return res;
		if (!pset->quiet)
			fputs(PQerrorMessage(pset->db), stderr);
		PQclear(res);
	}
	return NULL;
}

/*
 * Code to support command cancellation.
 * If interactive, we enable a SIGINT signal catcher that sends
 * a cancel request to the backend.
 * Note that sending the cancel directly from the signal handler
 * is safe only because PQrequestCancel is carefully written to
 * make it so.	We have to be very careful what else we do in the
 * signal handler.
 * Writing on stderr is potentially dangerous, if the signal interrupted
 * some stdio operation on stderr.	On Unix we can avoid trouble by using
 * write() instead; on Windows that's probably not workable, but we can
 * at least avoid trusting printf by using the more primitive fputs.
 */

static PGconn *cancelConn = NULL;		/* connection to try cancel on */

static void
safe_write_stderr(const char *s)
{
#ifdef WIN32
	fputs(s, stderr);
#else
	write(fileno(stderr), s, strlen(s));
#endif
}

static void
handle_sigint(SIGNAL_ARGS)
{
	if (cancelConn == NULL)
		exit(1);				/* accept signal if no connection */
	/* Try to send cancel request */
	if (PQrequestCancel(cancelConn))
		safe_write_stderr("\nCANCEL request sent\n");
	else
	{
		safe_write_stderr("\nCannot send cancel request:\n");
		safe_write_stderr(PQerrorMessage(cancelConn));
	}
}


/*
 * listAllDbs
 *
 * list all the databases in the system returns 0 if all went well
 *
 *
 */

static int
listAllDbs(PsqlSettings *pset)
{
	PGresult   *results;
	char	   *query = "select * from pg_database;";

	if (!(results = PSQLexec(pset, query)))
		return 1;
	else
	{
		PQprint(pset->queryFout,
				results,
				&pset->opt);
		PQclear(results);
		return 0;
	}
}

/*
 * List The Database Tables returns 0 if all went well
 *
 */
static int
tableList(PsqlSettings *pset, bool deep_tablelist, char info_type,
		  bool system_tables)
{
	char		listbuf[512];
	int			nColumns;
	int			i;
	char	   *rk;
	char	   *rr;
	PGresult   *res;
	int			usePipe = 0;
	bool		haveIndexes = false;
	char	   *pagerenv;
	FILE	   *fout;

#ifdef TIOCGWINSZ
	if (pset->notty == 0 &&
		(ioctl(fileno(stdout), TIOCGWINSZ, &screen_size) == -1 ||
		 screen_size.ws_col == 0 ||
		 screen_size.ws_row == 0))
	{
#endif
		screen_size.ws_row = 24;
		screen_size.ws_col = 80;
#ifdef TIOCGWINSZ
	}
#endif

	listbuf[0] = '\0';
	strcat(listbuf, "SELECT usename, relname, relkind, relhasrules ");
	strcat(listbuf, "FROM pg_class, pg_user ");
	strcat(listbuf, "WHERE usesysid = relowner ");
	switch (info_type)
	{
		case 't':
			strcat(listbuf, "and ( relkind = 'r') ");
			break;
		case 'i':
			strcat(listbuf, "and ( relkind = 'i') ");
			haveIndexes = true;
			break;
		case 'S':
			strcat(listbuf, "and ( relkind = 'S') ");
			break;
		case 'b':
		default:
			strcat(listbuf, "and ( relkind = 'r' OR relkind = 'i' OR relkind = 'S') ");
			haveIndexes = true;
			break;
	}
	if (!system_tables)
		strcat(listbuf, "and relname !~ '^pg_' ");
	else
		strcat(listbuf, "and relname ~ '^pg_' ");
	/*
	 * Large-object relations are automatically ignored because they have
	 * relkind 'l'.  However, we want to ignore their indexes as well.
	 * The clean way to do that would be to do a join to find out which
	 * table each index is for.  The ugly but fast way is to know that
	 * large object indexes have names starting with 'xinx'.
	 */
	if (haveIndexes)
		strcat(listbuf, "and (relkind != 'i' OR relname !~ '^xinx') ");

	strcat(listbuf, " ORDER BY relname ");
	if (!(res = PSQLexec(pset, listbuf)))
		return -1;
	/* first, print out the attribute names */
	nColumns = PQntuples(res);
	if (nColumns > 0)
	{
		if (pset->notty == 0 &&
			(pagerenv = getenv("PAGER")) &&
			pagerenv[0] != '\0' &&
			(deep_tablelist ||
			 screen_size.ws_row <= nColumns + 7) &&
			(fout = popen(pagerenv, "w")))
		{
			usePipe = 1;
			pqsignal(SIGPIPE, SIG_IGN);
		}
		else
			fout = stdout;

		if (deep_tablelist)
		{
			/* describe everything here */
			char	  **table;

			table = (char **) malloc(nColumns * sizeof(char *));
			if (table == NULL)
				perror("malloc");

			/* load table table */

			/*
			 * Put double quotes around the table name to allow for
			 * mixed-case and whitespaces in the table name. - BGA
			 * 1998-11-14
			 */
			for (i = 0; i < nColumns; i++)
			{