1 /*-------------------------------------------------------------------------
4 * an interactive front-end to postgres95
6 * Copyright (c) 1996, Regents of the University of California
10 * $Header: /cvsroot/pgsql/src/bin/psql/Attic/psql.c,v 1.28 1996/11/11 05:55:30 momjian Exp $
12 *-------------------------------------------------------------------------
18 #include <sys/types.h>
24 #include "stringutils.h"
31 /* from the GNU readline library */
36 #include <readline/readline.h>
37 #include <readline/history.h>
41 #define MAX_QUERY_BUFFER 20000
43 #define COPYBUFSIZ 8192
45 #define DEFAULT_FIELD_SEP "|"
46 #define DEFAULT_EDITOR "vi"
47 #define DEFAULT_SHELL "/bin/sh"
49 typedef struct _psqlSettings {
50 PGconn *db; /* connection to backend */
51 FILE *queryFout; /* where to send the query results */
52 PQprintOpt opt; /* options to be passed to PQprint */
53 char *prompt; /* prompt to display */
54 char *gfname; /* one-shot file output argument for \g */
55 bool notty; /* input or output is not a tty */
56 bool pipe; /* queryFout is from a popen() */
57 bool echoQuery; /* echo the query before sending it */
58 bool quiet; /* run quietly, no messages, no promt */
59 bool singleStep; /* prompt before for each query */
60 bool singleLineMode; /* query terminated by newline */
61 bool useReadline; /* use libreadline routines */
64 /* declarations for functions in this file */
65 static void usage(char *progname);
66 static void slashUsage();
67 static void handleCopyOut(PGresult *res, bool quiet, FILE *copystream);
68 static void handleCopyIn(PGresult *res, const bool mustprompt,
70 static int tableList(PsqlSettings *ps, bool deep_tablelist);
71 static int tableDesc(PsqlSettings *ps, char *table);
73 char *gets_noreadline(char *prompt, FILE *source);
74 char *gets_readline(char *prompt, FILE *source);
75 char *gets_fromFile(char *prompt, FILE *source);
76 int listAllDbs(PsqlSettings *settings);
77 void SendQuery(bool *success_p, PsqlSettings *settings, const char *query,
78 const bool copy_in, const bool copy_out, FILE *copystream);
79 int HandleSlashCmds(PsqlSettings *settings,
82 int MainLoop(PsqlSettings *settings, FILE *source);
83 /* probably should move this into libpq */
84 void PQprint(FILE *fp,
89 FILE *setFout(PsqlSettings *ps, char *fname);
93 * print out usage for command line arguments
99 fprintf(stderr,"Usage: %s [options] [dbname]\n",progname);
100 fprintf(stderr,"\t -a authsvc set authentication service\n");
101 fprintf(stderr,"\t -A turn off alignment when printing out attributes\n");
102 fprintf(stderr,"\t -c query run single query (slash commands too)\n");
103 fprintf(stderr,"\t -d dbName specify database name\n");
104 fprintf(stderr,"\t -e echo the query sent to the backend\n");
105 fprintf(stderr,"\t -f filename use file as a source of queries\n");
106 fprintf(stderr,"\t -F sep set the field separator (default is " ")\n");
107 fprintf(stderr,"\t -h host set database server host\n");
108 fprintf(stderr,"\t -H turn on html3.0 table output\n");
109 fprintf(stderr,"\t -l list available databases\n");
110 fprintf(stderr,"\t -n don't use readline library\n");
111 fprintf(stderr,"\t -o filename send output to filename or (|pipe)\n");
112 fprintf(stderr,"\t -p port set port number\n");
113 fprintf(stderr,"\t -q run quietly (no messages, no prompts)\n");
114 fprintf(stderr,"\t -s single step mode (prompts for each query)\n");
115 fprintf(stderr,"\t -S single line mode (i.e. query terminated by newline)\n");
116 fprintf(stderr,"\t -t turn off printing of headings and row count\n");
117 fprintf(stderr,"\t -T html set html3.0 table command options (cf. -H)\n");
118 fprintf(stderr,"\t -x turn on expanded output (field names on left)\n");
124 * print out usage for the backslash commands
127 static char *on(bool f)
129 return f? "on": "off";
133 slashUsage(PsqlSettings *ps)
135 fprintf(stderr,"\t \\? -- help\n");
136 fprintf(stderr,"\t \\a -- toggle field-alignment (currenty %s)\n", on(ps->opt.align));
137 fprintf(stderr,"\t \\C [<captn>] -- set html3 caption (currently '%s')\n", ps->opt.caption? ps->opt.caption: "");
138 fprintf(stderr,"\t \\connect <dbname> -- connect to new database (currently '%s')\n", PQdb(ps->db));
139 fprintf(stderr,"\t \\copy <dbname> -- copy table to/from a file\n");
140 fprintf(stderr,"\t \\d [<table>] -- list tables in database or columns in <table>,* for all\n");
141 fprintf(stderr,"\t \\e [<fname>] -- edit the current query buffer or <fname>,\\E execute too\n");
142 fprintf(stderr,"\t \\f [<sep>] -- change field separater (currently '%s')\n", ps->opt.fieldSep);
143 fprintf(stderr,"\t \\g [<fname>] -- send query to backend [and place results in <fname>]\n");
144 fprintf(stderr,"\t \\g |<cmd> -- send query to backend and pipe results into <cmd>\n");
145 fprintf(stderr,"\t \\h [<cmd>] -- help on syntax of sql commands, * for all commands\n");
146 fprintf(stderr,"\t \\H -- toggle html3 output (currently %s)\n", on(ps->opt.html3));
147 fprintf(stderr,"\t \\i <fname> -- read and execute queries from filename\n");
148 fprintf(stderr,"\t \\l -- list all databases\n");
149 fprintf(stderr,"\t \\m -- toggle monitor-like table display (currently %s)\n", on(ps->opt.standard));
150 fprintf(stderr,"\t \\o [<fname>] -- send all query results to <fname> or stdout\n");
151 fprintf(stderr,"\t \\o |<cmd> -- pipe all query results through <cmd>\n");
152 fprintf(stderr,"\t \\p -- print the current query buffer\n");
153 fprintf(stderr,"\t \\q -- quit\n");
154 fprintf(stderr,"\t \\r -- reset(clear) the query buffer\n");
155 fprintf(stderr,"\t \\s [<fname>] -- print history or save it in <fname>\n");
156 fprintf(stderr,"\t \\t -- toggle table headings and row count (currently %s)\n", on(ps->opt.header));
157 fprintf(stderr,"\t \\T [<html>] -- set html3.0 <table ...> options (currently '%s')\n", ps->opt.tableOpt? ps->opt.tableOpt: "");
158 fprintf(stderr,"\t \\x -- toggle expanded output (currently %s)\n", on(ps->opt.expanded));
159 fprintf(stderr,"\t \\! [<cmd>] -- shell escape or command\n");
163 PSQLexec(PsqlSettings *ps, char *query)
165 PGresult *res = PQexec(ps->db, query);
167 fputs(PQerrorMessage(ps->db), stderr);
170 if (PQresultStatus(res)==PGRES_COMMAND_OK ||
171 PQresultStatus(res)==PGRES_TUPLES_OK)
174 fputs(PQerrorMessage(ps->db), stderr);
182 * list all the databases in the system
183 * returns 0 if all went well
189 listAllDbs(PsqlSettings *ps)
192 char *query = "select * from pg_database;";
194 if (!(results=PSQLexec(ps, query)))
198 PQprint(ps->queryFout,
208 * List The Database Tables
209 * returns 0 if all went well
213 tableList (PsqlSettings *ps, bool deep_tablelist)
224 strcat(listbuf,"SELECT usename, relname, relkind, relhasrules");
225 strcat(listbuf," FROM pg_class, pg_user ");
226 strcat(listbuf,"WHERE ( relkind = 'r' OR relkind = 'i') ");
227 strcat(listbuf," and relname !~ '^pg_'");
228 strcat(listbuf," and relname !~ '^Inv[0-9]+'");
229 /* the usesysid = relowner won't work on stock 1.0 dbs, need to
230 add in the int4oideq function */
231 strcat(listbuf," and usesysid = relowner");
232 strcat(listbuf," ORDER BY relname ");
233 if (!(res=PSQLexec(ps, listbuf)))
236 /* first, print out the attribute names */
237 nColumns = PQntuples(res);
240 if ( deep_tablelist ) {
241 /* describe everything here */
243 table = (char**)malloc(nColumns * sizeof(char*));
247 /* load table table */
248 for (i=0; i < nColumns; i++) {
249 table[i] = (char *) malloc(PQgetlength(res,i,1) * sizeof(char) + 1);
250 if ( table[i] == NULL )
252 strcpy(table[i],PQgetvalue(res,i,1));
256 for (i=0; i < nColumns; i++) {
257 tableDesc(ps, table[i]);
262 /* Display the information */
264 printf ("\nDatabase = %s\n", PQdb(ps->db));
265 printf (" +------------------+----------------------------------+----------+\n");
266 printf (" | Owner | Relation | Type |\n");
267 printf (" +------------------+----------------------------------+----------+\n");
269 /* next, print out the instances */
270 for (i=0; i < PQntuples(res); i++) {
271 printf (" | %-16.16s", PQgetvalue(res,i,0));
272 printf (" | %-32.32s | ", PQgetvalue(res,i,1));
273 rk = PQgetvalue(res,i,2);
274 rr = PQgetvalue(res,i,3);
275 if (strcmp(rk, "r") == 0)
276 printf ("%-8.8s |", (rr[0] == 't') ? "view?" : "table" );
278 printf ("%-8.8s |", "index");
281 printf (" +------------------+----------------------------------+----------+\n");
287 fprintf (stderr, "Couldn't find any tables!\n");
295 * Describe the columns in a database table.
296 * returns 0 if all went well
301 tableDesc (PsqlSettings *ps, char *table)
311 /* Build the query */
314 strcat(descbuf,"SELECT a.attnum, a.attname, t.typname, a.attlen");
315 strcat(descbuf," FROM pg_class c, pg_attribute a, pg_type t ");
316 strcat(descbuf," WHERE c.relname = '");
317 strcat(descbuf,table);
319 strcat(descbuf," and a.attnum > 0 ");
320 strcat(descbuf," and a.attrelid = c.oid ");
321 strcat(descbuf," and a.atttypid = t.oid ");
322 strcat(descbuf," ORDER BY attnum ");
323 if (!(res = PSQLexec(ps, descbuf)))
325 /* first, print out the attribute names */
326 nColumns = PQntuples(res);
330 ** Display the information
333 printf ("\nTable = %s\n", table);
334 printf ("+----------------------------------+----------------------------------+-------+\n");
335 printf ("| Field | Type | Length|\n");
336 printf ("+----------------------------------+----------------------------------+-------+\n");
338 /* next, print out the instances */
339 for (i=0; i < PQntuples(res); i++) {
340 printf ("| %-32.32s | ", PQgetvalue(res,i,1));
341 rtype = PQgetvalue(res,i,2);
342 rsize = atoi(PQgetvalue(res,i,3));
343 if (strcmp(rtype, "text") == 0) {
344 printf ("%-32.32s |", rtype);
345 printf ("%6s |", "var" );
347 else if (strcmp(rtype, "bpchar") == 0) {
348 printf ("%-32.32s |", "(bp)char");
349 printf ("%6i |", rsize > 0 ? rsize - 4 : 0 );
351 else if (strcmp(rtype, "varchar") == 0) {
352 printf ("%-32.32s |", rtype);
353 printf ("%6i |", rsize > 0 ? rsize - 4 : 0 );
356 /* array types start with an underscore */
358 printf ("%-32.32s |", rtype);
361 newname = malloc(strlen(rtype) + 2);
362 strcpy(newname, rtype+1);
363 strcat(newname, "[]");
364 printf ("%-32.32s |", newname);
368 printf ("%6i |", rsize);
370 printf ("%6s |", "var");
374 printf ("+----------------------------------+----------------------------------+-------+\n");
380 fprintf (stderr, "Couldn't find table %s!\n", table);
385 typedef char *(*READ_ROUTINE)(char *prompt, FILE *source);
387 /* gets_noreadline prompt source
388 gets a line of input without calling readline, the source is ignored
391 gets_noreadline(char *prompt, FILE *source)
393 fputs(prompt, stdout);
395 return(gets_fromFile(prompt,stdin));
399 * gets_readline prompt source
400 * the routine to get input from GNU readline(), the source is ignored
401 * the prompt argument is used as the prompting string
404 gets_readline(char *prompt, FILE *source)
406 return (readline(prompt));
410 * gets_fromFile prompt source
412 * the routine to read from a file, the prompt argument is ignored
413 * the source argument is a FILE *
416 gets_fromFile(char *prompt, FILE *source)
421 line = malloc(MAX_QUERY_BUFFER+1);
423 /* read up to MAX_QUERY_BUFFER characters */
424 if (fgets(line, MAX_QUERY_BUFFER, source) == NULL)
427 line[MAX_QUERY_BUFFER-1] = '\0';
429 if (len == MAX_QUERY_BUFFER)
431 fprintf(stderr, "line read exceeds maximum length. Truncating at %d\n",
439 * SendQuery: send the query string to the backend
440 * return *success_p = 1 if the query executed successfully
441 * returns *success_p = 0 otherwise
444 SendQuery(bool *success_p, PsqlSettings *settings, const char *query,
445 const bool copy_in, const bool copy_out, FILE *copystream) {
450 if (settings->singleStep)
451 fprintf(stdout, "\n**************************************"
452 "*****************************************\n");
454 if (settings->echoQuery || settings->singleStep) {
455 fprintf(stderr,"QUERY: %s\n",query);
459 if (settings->singleStep) {
460 fprintf(stdout, "\n**************************************"
461 "*****************************************\n");
463 printf("\npress return to continue ..\n");
464 gets_fromFile("",stdin);
467 results = PQexec(settings->db, query);
468 if (results == NULL) {
469 fprintf(stderr,"%s",PQerrorMessage(settings->db));
472 switch (PQresultStatus(results)) {
473 case PGRES_TUPLES_OK:
474 if (settings->gfname) {
475 PsqlSettings ps=*settings;
478 fp=setFout(&ps, settings->gfname);
479 if (!fp || fp==stdout) {
482 } else *success_p = true;
490 settings->gfname=NULL;
494 PQprint(settings->queryFout,
497 fflush(settings->queryFout);
501 case PGRES_EMPTY_QUERY:
504 case PGRES_COMMAND_OK:
506 if (!settings->quiet)
507 fprintf(stdout,"%s\n", PQcmdStatus(results));
512 handleCopyOut(results, settings->quiet, copystream);
514 if (!settings->quiet)
515 fprintf(stdout, "Copy command returns...\n");
517 handleCopyOut(results, settings->quiet, stdout);
523 handleCopyIn(results, false, copystream);
527 * eat extra newline still in input buffer
531 if ((c = getc(stdin)) != '\n' && c != EOF)
532 (void) ungetc(c, stdin);
533 handleCopyIn(results, !settings->quiet, stdin);
536 case PGRES_NONFATAL_ERROR:
537 case PGRES_FATAL_ERROR:
538 case PGRES_BAD_RESPONSE:
540 fprintf(stderr,"%s",PQerrorMessage(settings->db));
544 /* check for asynchronous returns */
545 notify = PQnotifies(settings->db);
548 "ASYNC NOTIFY of '%s' from backend pid '%d' received\n",
549 notify->relname, notify->be_pid);
558 editFile(char *fname)
562 editorName = getenv("EDITOR");
564 editorName = DEFAULT_EDITOR;
565 sys=malloc(strlen(editorName)+strlen(fname)+32+1);
571 sprintf(sys, "exec '%s' '%s'", editorName, fname);
577 toggle(PsqlSettings *settings, bool *sw, char *msg)
580 if (!settings->quiet)
581 fprintf(stderr, "turned %s %s\n", on(*sw), msg);
588 unescape(char *dest, const char *source) {
589 /*-----------------------------------------------------------------------------
590 Return as the string <dest> the value of string <source> with escape
591 sequences turned into the bytes they represent.
592 -----------------------------------------------------------------------------*/
594 bool esc; /* Last character we saw was the escape character (/) */
596 esc = false; /* Haven't seen escape character yet */
597 for (p = (char *)source; *p; p++) {
598 char c; /* Our output character */
624 c = ' '; /* meaningless, but compiler doesn't know that */
629 if (!esc) *dest ++= c;
631 *dest = '\0'; /* Terminating null character */
637 parse_slash_copy(const char *args, char *table, const int table_len,
638 char *file, const int file_len,
639 bool *from_p, bool *error_p) {
642 /* A copy of the \copy command arguments, except that we modify it
643 as we parse to suit our parsing needs.
645 char *table_tok, *fromto_tok;
647 strncpy(work_args, args, sizeof(work_args));
648 work_args[sizeof(work_args)-1] = '\0';
650 *error_p = false; /* initial assumption */
652 table_tok = strtok(work_args, " ");
653 if (table_tok == NULL) {
654 fprintf(stderr, "\\copy needs arguments.\n");
657 strncpy(table, table_tok, table_len);
658 file[table_len-1] = '\0';
660 fromto_tok = strtok(NULL, " ");
661 if (fromto_tok == NULL) {
662 fprintf(stderr, "'FROM' or 'TO' must follow table name.\n");
665 if (strcasecmp(fromto_tok, "from") == 0) *from_p = true;
666 else if (strcasecmp(fromto_tok, "to") == 0) *from_p = false;
669 "Unrecognized token found where "
670 "'FROM' or 'TO' expected: '%s'.\n",
677 file_tok = strtok(NULL, " ");
678 if (file_tok == NULL) {
679 fprintf(stderr, "A file pathname must follow '%s'.\n",
683 strncpy(file, file_tok, file_len);
684 file[file_len-1] = '\0';
685 if (strtok(NULL, " ") != NULL) {
687 "You have extra tokens after the filename.\n");
699 do_copy(const char *args, PsqlSettings *settings) {
700 /*---------------------------------------------------------------------------
701 Execute a \copy command (frontend copy). We have to open a file, then
702 submit a COPY query to the backend and either feed it data from the
703 file or route its response into the file.
705 We do a text copy with default (tab) column delimiters. Some day, we
706 should do all the things a backend copy can do.
708 ----------------------------------------------------------------------------*/
710 /* The COPY command we send to the back end */
712 /* The direction of the copy is from a file to a table. */
713 char file[MAXPATHLEN+1];
714 /* The pathname of the file from/to which we copy */
715 char table[NAMEDATALEN+1];
716 /* The name of the table from/to which we copy */
718 /* The \c command has invalid syntax */
721 parse_slash_copy(args, table, sizeof(table), file, sizeof(file),
722 &from, &syntax_error);
725 strcpy(query, "COPY ");
726 strcat(query, table);
729 strcat(query, " FROM stdin");
731 strcat(query, " TO stdout");
734 copystream = fopen(file, "r");
736 copystream = fopen(file, "w");
740 "Unable to open file %s which to copy, errno = %s (%d).",
741 from ? "from" : "to", strerror(errno), errno);
743 bool success; /* The query succeeded at the backend */
745 SendQuery(&success, settings, query, from, !from, copystream);
747 if (!settings->quiet) {
749 fprintf(stdout, "Successfully copied.\n");
751 fprintf(stdout, "Copy failed.\n");
759 do_connect(const char *new_dbname, PsqlSettings *settings) {
761 char *dbname=PQdb(settings->db);
763 fprintf(stderr,"\\connect must be followed by a database name\n");
765 PGconn *olddb=settings->db;
767 printf("closing connection to database: %s\n", dbname);
768 settings->db = PQsetdb(PQhost(olddb), PQport(olddb),
769 NULL, NULL, new_dbname);
770 printf("connecting to new database: %s\n", new_dbname);
771 if (PQstatus(settings->db) == CONNECTION_BAD) {
772 fprintf(stderr,"%s\n", PQerrorMessage(settings->db));
773 printf("reconnecting to %s\n", dbname);
774 settings->db = PQsetdb(PQhost(olddb), PQport(olddb),
776 if (PQstatus(settings->db) == CONNECTION_BAD) {
778 "could not reconnect to %s. exiting\n", dbname);
783 free(settings->prompt);
784 settings->prompt = malloc(strlen(PQdb(settings->db)) + 10);
785 sprintf(settings->prompt,"%s=> ", PQdb(settings->db));
792 do_edit(const char *filename_arg, char *query, int *retcode_p) {
798 const int ql = strlen(query);
802 fname=(char *)filename_arg;
805 sprintf(tmp, "/tmp/psql.%ld.%ld", (long)geteuid(), (long)getpid());
809 if ((fd=open(tmp, O_EXCL|O_CREAT|O_WRONLY, 0600)) == -1) {
813 if (query[ql-1] != '\n')
815 if (write(fd, query, ql) != ql) {
826 if (error) *retcode_p=1;
829 if ((fd=open(fname, O_RDONLY))==-1) {
835 if ((cc=read(fd, query, MAX_QUERY_BUFFER))==-1) {
847 if (query[strlen(query)-1]==';') *retcode_p=0;
857 do_help(const char *topic) {
860 char left_center_right; /* Which column we're displaying */
861 int i; /* Index into QL_HELP[] */
863 printf("type \\h <cmd> where <cmd> is one of the following:\n");
865 left_center_right = 'L'; /* Start with left column */
867 while (QL_HELP[i].cmd != NULL) {
868 switch(left_center_right) {
870 printf(" %-25s", QL_HELP[i].cmd);
871 left_center_right = 'C';
874 printf("%-25s", QL_HELP[i].cmd);
875 left_center_right = 'R';
878 printf("%-25s\n", QL_HELP[i].cmd);
879 left_center_right = 'L';
884 if (left_center_right != 'L') puts("\n");
885 printf("type \\h * for a complete description of all commands\n");
887 int i; /* Index into QL_HELP[] */
888 bool help_found; /* We found the help he asked for */
890 help_found = false; /* Haven't found it yet */
891 for (i=0; QL_HELP[i].cmd; i++) {
892 if (strcmp(QL_HELP[i].cmd, topic) == 0 ||
893 strcmp(topic, "*") == 0 ) {
895 printf("Command: %s\n",QL_HELP[i].cmd);
896 printf("Description: %s\n", QL_HELP[i].help);
898 printf("%s\n", QL_HELP[i].syntax);
903 printf("command not found, "
904 "try \\h with no arguments to see available help\n");
911 do_shell(const char *command) {
917 shellName = getenv("SHELL");
918 if (shellName == NULL)
919 shellName = DEFAULT_SHELL;
920 sys = malloc(strlen(shellName)+16);
925 sprintf(sys,"exec %s", shellName);
928 } else system(command);
936 Handles all the different commands that start with \
937 db_ptr is a pointer to the TgDb* structure
938 line is the current input line
939 prompt_ptr is a pointer to the prompt string,
940 a pointer is used because the prompt can be used with
941 a connection to a new database
943 0 - send currently constructed query to backend (i.e. we got a \g)
944 1 - skip processing of this line, continue building up query
945 2 - terminate processing of this query entirely
948 HandleSlashCmds(PsqlSettings *settings,
954 /* Pointer inside the <cmd> string to the argument of the slash
955 command, assuming it is a one-character slash command. If it's
956 not a one-character command, this is meaningless.
959 /* Pointer inside the <cmd> string to the argument of the slash
960 command assuming it's not a one-character command. If it's a
961 one-character command, this is meaningless.
964 /* String: value of the slash command, less the slash and with escape
968 /* Offset within <cmd> of first blank */
970 cmd = malloc(strlen(line)); /* unescaping better not make string grow. */
972 unescape(cmd, line+1); /* sets cmd string */
974 /* Originally, there were just single character commands. Now,
975 we define some longer, friendly commands, but we have to keep
976 the old single character commands too. \c used to be what
977 \connect is now. Complicating matters is the fact that with
978 the single-character commands, you can start the argument
979 right after the single character, so "\copy" would mean
980 "connect to database named 'opy'".
983 if (strlen(cmd) > 1) optarg = cmd+1 + strspn(cmd+1, " \t");
986 blank_loc = strcspn(cmd, " \t");
987 if (blank_loc == 0) optarg2 = NULL;
988 else optarg2 = cmd + blank_loc + strspn(cmd+blank_loc, " \t");
993 case 'a': /* toggles to align fields on output */
994 toggle(settings, &settings->opt.align, "field alignment");
996 case 'C': /* define new caption */
997 if (settings->opt.caption)
998 free(settings->opt.caption);
1000 settings->opt.caption=NULL;
1002 if (!(settings->opt.caption=strdup(optarg)))
1009 if (strncmp(cmd, "copy ", strlen("copy ")) == 0)
1010 do_copy(optarg2, settings);
1011 else if (strncmp(cmd, "connect ", strlen("connect ")) == 0)
1012 do_connect(optarg2, settings);
1014 do_connect(optarg, settings);
1017 case 'd': /* \d describe tables or columns in a table */
1019 tableList(settings, 0);
1022 if (strcmp(optarg, "*") == 0 ) {
1023 tableList(settings, 0);
1024 tableList(settings, 1);
1027 tableDesc(settings, optarg);
1030 case 'e': /* edit */
1032 do_edit(optarg, query, &status);
1038 static char *lastfile;
1039 struct stat st, st2;
1044 lastfile=malloc(strlen(optarg+1));
1050 strcpy(lastfile, optarg);
1051 } else if (!lastfile)
1053 fprintf(stderr,"\\r must be followed by a file name initially\n");
1056 stat(lastfile, &st);
1058 if ((stat(lastfile, &st2) == -1) || ((fd = fopen(lastfile, "r")) == NULL))
1063 if (st2.st_mtime==st.st_mtime)
1065 if (!settings->quiet)
1066 fprintf(stderr, "warning: %s not modified. query not executed\n", lastfile);
1070 MainLoop(settings, fd);
1076 char *fs=DEFAULT_FIELD_SEP;
1079 if (settings->opt.fieldSep);
1080 free(settings->opt.fieldSep);
1081 if (!(settings->opt.fieldSep=strdup(fs)))
1086 if (!settings->quiet)
1087 fprintf(stderr, "field separater changed to '%s'\n", settings->opt.fieldSep);
1090 case 'g': /* \g means send query */
1091 settings->gfname = optarg;
1094 case 'h': /* help */
1099 case 'i': /* \i is include file */
1104 fprintf(stderr,"\\i must be followed by a file name\n");
1108 if ((fd = fopen(optarg, "r")) == NULL)
1110 fprintf(stderr,"file named %s could not be opened\n",optarg);
1113 MainLoop(settings, fd);
1117 case 'l': /* \l is list database */
1118 listAllDbs(settings);
1121 if (toggle(settings, &settings->opt.html3, "HTML3.0 tabular output"))
1122 settings->opt.standard = 0;
1125 setFout(settings, optarg);
1130 fputs(query, stdout);
1131 fputc('\n', stdout);
1134 case 'q': /* \q is quit */
1137 case 'r': /* reset(clear) the buffer */
1139 if (!settings->quiet)
1140 fprintf(stderr, "buffer reset(cleared)\n");
1142 case 's': /* \s is save history to a file */
1145 if (write_history(optarg) != 0)
1146 fprintf(stderr,"cannot write history to %s\n",optarg);
1148 case 'm': /* monitor like type-setting */
1149 if (toggle(settings, &settings->opt.standard, "standard SQL separaters and padding"))
1151 settings->opt.html3 = settings->opt.expanded = 0;
1152 settings->opt.align = settings->opt.header = 1;
1153 free(settings->opt.fieldSep);
1154 settings->opt.fieldSep=strdup("|");
1155 if (!settings->quiet)
1156 fprintf(stderr, "field separater changed to '%s'\n", settings->opt.fieldSep);
1159 free(settings->opt.fieldSep);
1160 settings->opt.fieldSep=strdup(DEFAULT_FIELD_SEP);
1161 if (!settings->quiet)
1162 fprintf(stderr, "field separater changed to '%s'\n", settings->opt.fieldSep);
1165 case 't': /* toggle headers */
1166 toggle(settings, &settings->opt.header, "output headings and row count");
1168 case 'T': /* define html <table ...> option */
1169 if (settings->opt.tableOpt)
1170 free(settings->opt.tableOpt);
1172 settings->opt.tableOpt=NULL;
1174 if (!(settings->opt.tableOpt=strdup(optarg)))
1181 toggle(settings, &settings->opt.expanded, "expanded table representation");
1187 case '?': /* \? is help */
1188 slashUsage(settings);
1196 MainLoop: main processing loop for reading lines of input
1197 and sending them to the backend
1199 this loop is re-entrant. May be called by \i command
1200 which reads input from a file
1202 *db_ptr must be initialized and set
1206 MainLoop(PsqlSettings *settings, FILE *source)
1208 char *line; /* line of input */
1209 int len; /* length of the line */
1210 char query[MAX_QUERY_BUFFER]; /* multi-line query storage */
1212 int slashCmdStatus = 0;
1213 /* slashCmdStatus can be:
1214 0 - send currently constructed query to backend (i.e. we got a \g)
1215 1 - skip processing of this line, continue building up query
1216 2 - terminate processing of this query entirely
1222 READ_ROUTINE GetNextLine;
1224 /* We are connected to the backend (last time we looked) */
1226 /* We've reached the end of our command input. */
1228 interactive = ((source == stdin) && !settings->notty);
1229 #define PROMPT "=> "
1231 if (settings->prompt)
1232 free(settings->prompt);
1234 malloc(strlen(PQdb(settings->db)) + strlen(PROMPT) + 1);
1235 if (settings->quiet)
1236 settings->prompt[0] = '\0';
1238 sprintf(settings->prompt,"%s%s", PQdb(settings->db), PROMPT);
1239 if (settings->useReadline) {
1241 GetNextLine = gets_readline;
1242 } else GetNextLine = gets_noreadline;
1243 } else GetNextLine = gets_fromFile;
1247 /* main loop for getting queries and executing them */
1248 while (connected && !eof) {
1249 line = GetNextLine(settings->prompt, source);
1250 if (line == NULL) { /* No more input. Time to quit */
1251 printf("EOF\n"); /* Goes on prompt line */
1255 line = rightTrim(line);
1256 /* remove whitespaces on the right, incl. \n's */
1258 if (line[0] == '\0') {
1263 /* filter out comment lines that begin with --,
1264 this could be incorrect if -- is part of a quoted string.
1265 But we won't go through the trouble of detecting that.
1266 If you have -- in your quoted string, be careful and don't
1267 start a line with it
1269 if (line[0] == '-' && line[1] == '-') {
1270 if (settings->singleStep)
1271 /* in single step mode, show comments */
1272 fprintf(stdout,"%s\n",line);
1276 if (line[0] != '\\' && querySent) {
1283 if (interactive && settings->useReadline)
1284 add_history(line); /* save non-empty lines in history */
1286 /* do the query immediately if we are doing single line queries
1287 or if the last character is a semicolon
1289 sendQuery = settings->singleLineMode || (line[len-1] == ';') ;
1291 /* normally, \ commands have to be start the line,
1292 but for backwards compatibility with monitor,
1293 check for \g at the end of line */
1294 if (len > 2 && !sendQuery) {
1295 if (line[len-1]=='g' && line[len-2]=='\\') {
1301 /* slash commands have to be on their own line */
1302 if (line[0] == '\\') {
1303 slashCmdStatus = HandleSlashCmds(settings,
1306 if (slashCmdStatus == 1) {
1310 if (slashCmdStatus == 2) {
1314 if (slashCmdStatus == 0)
1316 } else if (strlen(query) + len > MAX_QUERY_BUFFER) {
1317 fprintf(stderr,"query buffer max length of %d exceeded\n",
1319 fprintf(stderr,"query line ignored\n");
1320 } else if (query[0]!='\0') {
1323 } else strcpy(query,line);
1324 if (sendQuery && query[0] != '\0') {
1325 /* echo the line read from the file,
1326 unless we are in single_step mode, because single_step mode
1329 bool success; /* The query succeeded at the backend */
1331 if (!interactive && !settings->singleStep && !settings->quiet)
1332 fprintf(stderr,"%s\n", query);
1334 SendQuery(&success, settings, query, false, false, 0);
1335 exitStatus = success ? 0 : 1;
1337 if (PQstatus(settings->db) == CONNECTION_BAD) {
1340 "We have lost the connection to the backend, so "
1341 "further processing is impossible. "
1345 free(line); /* free storage malloc'd by GetNextLine */
1354 main(int argc, char **argv)
1356 extern char *optarg;
1359 char *dbname = NULL;
1362 char *qfilename = NULL;
1363 char errbuf[ERROR_MSG_LENGTH];
1365 PsqlSettings settings;
1367 char *singleQuery = NULL;
1369 bool listDatabases = 0 ;
1371 bool singleSlashCmd = 0;
1374 memset(&settings, 0, sizeof settings);
1375 settings.opt.align = 1;
1376 settings.opt.header = 1;
1377 settings.queryFout = stdout;
1378 settings.opt.fieldSep=strdup(DEFAULT_FIELD_SEP);
1379 settings.opt.pager = 1;
1380 if (!isatty(0) || !isatty(1))
1381 settings.quiet = settings.notty = 1;
1384 settings.useReadline = 1;
1387 while ((c = getopt(argc, argv, "Aa:c:d:ef:F:lh:Hnso:p:qStT:x")) != EOF) {
1390 settings.opt.align = 0;
1393 fe_setauthsvc(optarg, errbuf);
1396 singleQuery = optarg;
1397 if ( singleQuery[0] == '\\' ) {
1405 settings.echoQuery = 1;
1411 settings.opt.fieldSep=optarg;
1420 settings.opt.html3 = 1;
1423 settings.useReadline = 0;
1426 setFout(&settings, optarg);
1435 settings.singleStep = 1;
1438 settings.singleLineMode = 1;
1441 settings.opt.header = 0;
1444 settings.opt.tableOpt = optarg;
1447 settings.opt.expanded = 1;
1454 /* if we still have an argument, use it as the database name */
1455 if (argc - optind == 1)
1456 dbname = argv[optind];
1459 dbname = "template1";
1461 settings.db = PQsetdb(host, port, NULL, NULL, dbname);
1462 dbname = PQdb(settings.db);
1464 if (PQstatus(settings.db) == CONNECTION_BAD) {
1465 fprintf(stderr,"Connection to database '%s' failed.\n", dbname);
1466 fprintf(stderr,"%s",PQerrorMessage(settings.db));
1469 if (listDatabases) {
1470 exit(listAllDbs(&settings));
1473 if (!settings.quiet && !singleQuery && !qfilename) {
1474 printf("Welcome to the POSTGRES95 interactive sql monitor:\n");
1475 printf(" Please read the file COPYRIGHT for copyright terms "
1476 "of POSTGRES95\n\n");
1477 printf(" type \\? for help on slash commands\n");
1478 printf(" type \\q to quit\n");
1479 printf(" type \\g or terminate with semicolon to execute query\n");
1480 printf(" You are currently connected to the database: %s\n\n", dbname);
1483 if (qfilename || singleSlashCmd) {
1484 /* read in a file full of queries instead of reading in queries
1488 if ( singleSlashCmd ) {
1489 /* Not really a query, but "Do what I mean, not what I say." */
1492 line = malloc(strlen(qfilename) + 5);
1493 sprintf(line,"\\i %s", qfilename);
1495 HandleSlashCmds(&settings, line, "");
1498 bool success; /* The query succeeded at the backend */
1499 SendQuery(&success, &settings, singleQuery, false, false, 0);
1500 exitStatus = success ? 0 : 1;
1501 } else exitStatus = MainLoop(&settings, stdin);
1504 PQfinish(settings.db);
1509 #define COPYBUFSIZ 8192
1512 handleCopyOut(PGresult *res, bool quiet, FILE *copystream) {
1514 char copybuf[COPYBUFSIZ];
1517 copydone = false; /* Can't be done; haven't started. */
1520 ret = PQgetline(res->conn, copybuf, COPYBUFSIZ);
1522 if (copybuf[0] == '\\' &&
1523 copybuf[1] == '.' &&
1524 copybuf[2] =='\0') {
1525 copydone = true; /* don't print this... */
1527 fputs(copybuf, copystream);
1533 fputc('\n', copystream);
1541 PQendcopy(res->conn);
1547 handleCopyIn(PGresult *res, const bool mustprompt, FILE *copystream) {
1548 bool copydone = false;
1551 char copybuf[COPYBUFSIZ];
1557 fputs("Enter info followed by a newline\n", stdout);
1558 fputs("End with a backslash and a "
1559 "period on a line by itself.\n", stdout);
1562 while (!copydone) { /* for each input line ... */
1564 fputs(">> ", stdout);
1569 while (!linedone) { /* for each buffer ... */
1571 buflen = COPYBUFSIZ;
1572 for (; buflen > 1 &&
1573 !(linedone = (c = getc(copystream)) == '\n' || c == EOF);
1578 PQputline(res->conn, "\\.");
1583 PQputline(res->conn, copybuf);
1585 if (!strcmp(copybuf, "\\.")) {
1591 PQputline(res->conn, "\n");
1593 PQendcopy(res->conn);
1598 /* try to open fname and return a FILE *,
1599 if it fails, use stdout, instead */
1602 setFout(PsqlSettings *ps, char *fname)
1604 if (ps->queryFout && ps->queryFout != stdout)
1607 pclose(ps->queryFout);
1609 fclose(ps->queryFout);
1612 ps->queryFout = stdout;
1617 signal(SIGPIPE, SIG_IGN);
1618 ps->queryFout = popen(fname+1, "w");
1623 ps->queryFout = fopen(fname, "w");
1626 if (!ps->queryFout) {
1628 ps->queryFout = stdout;
1631 return ps->queryFout;