From d602a35d37f700bc28c89a21255934a0abd0677a Mon Sep 17 00:00:00 2001 From: "Marc G. Fournier" Date: Fri, 12 Jul 1996 05:39:39 +0000 Subject: [PATCH] Brought in extensions to pg_dump Submitted by: david bennett marc g. fournier --- src/bin/pg_dump/Makefile | 2 +- src/bin/pg_dump/README.dhb | 48 +++++++ src/bin/pg_dump/README.scrappy | 23 +++ src/bin/pg_dump/common.c | 51 ++++--- src/bin/pg_dump/pg_dump.c | 312 ++++++++++++++++++++++++++--------------- src/bin/pg_dump/pg_dump.h | 10 +- 6 files changed, 310 insertions(+), 136 deletions(-) create mode 100644 src/bin/pg_dump/README.dhb create mode 100644 src/bin/pg_dump/README.scrappy diff --git a/src/bin/pg_dump/Makefile b/src/bin/pg_dump/Makefile index 5512f249fa..b45e678093 100644 --- a/src/bin/pg_dump/Makefile +++ b/src/bin/pg_dump/Makefile @@ -7,7 +7,7 @@ # # # IDENTIFICATION -# $Header: /cvsroot/pgsql/src/bin/pg_dump/Makefile,v 1.1.1.1 1996/07/09 06:22:14 scrappy Exp $ +# $Header: /cvsroot/pgsql/src/bin/pg_dump/Makefile,v 1.2 1996/07/12 05:39:30 scrappy Exp $ # #------------------------------------------------------------------------- diff --git a/src/bin/pg_dump/README.dhb b/src/bin/pg_dump/README.dhb new file mode 100644 index 0000000000..b239073c18 --- /dev/null +++ b/src/bin/pg_dump/README.dhb @@ -0,0 +1,48 @@ + +This is a modified version of the pg_dump.c program that is distributed with +pg95 1.01. Modifications include: + +* Applied 'insert string' patch from "Marc G. Fournier" + + (see insert.patch & README.scrappy for info on this patch) + +* Added '-t table' option + + By specifying '-t table' on the command line you can output only the + schema (table & index defs) and data for one table of a database. + + Example: + + pg_dump -t descriptions software + +* Added '-a' option + + This is the opposite of the -S option. By specifying -a you can output + only the database data and not the schema. + + Example: + + pg_dump -a zipcodes + +* Added '-da' option + + Marc's '-d' option adds the ability to output insert strings, By using + the 'a' sub-parameter you can also place the attribute names in the + insert strings. Basically, this is useful because ALTER TABLE is + broken in pg95 1.01. + + NOTE: This will create some long hairy output files! Be sure to pipe + through compress or gzip before outputing to disk. + + Example: + + pg_dump -da -t oldfile mydatabase | gzip > oldfile.data.gz + +Comments: + + ----------------------------------------------------- + David Bennett, Bennett Software Solutions + 2608 NW Fawn Drive Blue Springs, MO 64015 + Phone: 816-228-8788, Fax: 816-228-3204 + dave@bensoft.com, http://bensoft.com + PGP key at ftp://bensoft.com/pub/pgp/daveskey.txt diff --git a/src/bin/pg_dump/README.scrappy b/src/bin/pg_dump/README.scrappy new file mode 100644 index 0000000000..9a71008f75 --- /dev/null +++ b/src/bin/pg_dump/README.scrappy @@ -0,0 +1,23 @@ + Here is what Marc had to say about insert.patch included in this archive.... + + In preparation of finally moving all my 1.0 databases over to a 1.01 + database server, I looked at pg_dump and found that, unless I missed + something, it didn't *easily* do what I wanted, which was to dump a database + to a file, and then reload it again on another server (short-term)...but, + also, there doesn't seem to be any mechanism for dumping the database to a + file that can be backed up and quickly reloaded again. + + So, I spent the past several hours modifying pg_dump so that it has an extra + switch for dumping the data in valid 'insert' strings, so that you can + quickly and easily reload a database. + + So, now the output looks like: + + CREATE TABLE scrap (integer int4, real float4, text text) archive = none; + insert into scrap values (1, 1, 'text'); + + Now, the hard part was figuring out what types are available, so that the + insert string works properly for char vs numberic data fields. As such, the + switch statement I'm using in dumpClasses() for this may be missing values + for numeric fields (I'm using PQftype() to figure out numeric vs non-numeric + fields) diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c index 96ea7f7295..0f099a60bd 100644 --- a/src/bin/pg_dump/common.c +++ b/src/bin/pg_dump/common.c @@ -7,7 +7,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/bin/pg_dump/common.c,v 1.1.1.1 1996/07/09 06:22:14 scrappy Exp $ + * $Header: /cvsroot/pgsql/src/bin/pg_dump/common.c,v 1.2 1996/07/12 05:39:33 scrappy Exp $ * *------------------------------------------------------------------------- */ @@ -199,7 +199,7 @@ strInArray(char* pattern, char** arr, int arr_size) */ TableInfo * -dumpSchema(FILE *fout, int *numTablesPtr) +dumpSchema(FILE *fout, int *numTablesPtr, char *tablename) { int numTypes; int numFuncs; @@ -252,31 +252,42 @@ if (g_verbose) fprintf(stderr,"%s reading indices information %s\n", g_comment_start, g_comment_end); indinfo = getIndices(&numIndices); -if (g_verbose) fprintf(stderr,"%s dumping out user-defined types %s\n", - g_comment_start, g_comment_end); - dumpTypes(fout, finfo, numFuncs, tinfo, numTypes); +if (!tablename && fout) { + if (g_verbose) fprintf(stderr,"%s dumping out user-defined types %s\n", + g_comment_start, g_comment_end); + dumpTypes(fout, finfo, numFuncs, tinfo, numTypes); +} -if (g_verbose) fprintf(stderr,"%s dumping out tables %s\n", +if (fout) { + if (g_verbose) fprintf(stderr,"%s dumping out tables %s\n", g_comment_start, g_comment_end); dumpTables(fout, tblinfo, numTables, inhinfo, numInherits, - tinfo, numTypes); + tinfo, numTypes, tablename); +} -if (g_verbose) fprintf(stderr,"%s dumping out user-defined functions %s\n", - g_comment_start, g_comment_end); - dumpFuncs(fout, finfo, numFuncs, tinfo, numTypes); +if (!tablename && fout) { + if (g_verbose) fprintf(stderr,"%s dumping out user-defined functions %s\n", + g_comment_start, g_comment_end); + dumpFuncs(fout, finfo, numFuncs, tinfo, numTypes); +} -if (g_verbose) fprintf(stderr,"%s dumping out user-defined functions %s\n", - g_comment_start, g_comment_end); - dumpAggs(fout, agginfo, numAggregates, tinfo, numTypes); +if (!tablename && fout) { + if (g_verbose) fprintf(stderr,"%s dumping out user-defined functions %s\n", + g_comment_start, g_comment_end); + dumpAggs(fout, agginfo, numAggregates, tinfo, numTypes); +} -if (g_verbose) fprintf(stderr,"%s dumping out user-defined operators %s\n", - g_comment_start, g_comment_end); - dumpOprs(fout, oprinfo, numOperators, tinfo, numTypes); - -if (g_verbose) fprintf(stderr,"%s dumping out indices %s\n", - g_comment_start, g_comment_end); - dumpIndices(fout, indinfo, numIndices, tblinfo, numTables); +if (!tablename && fout) { + if (g_verbose) fprintf(stderr,"%s dumping out user-defined operators %s\n", + g_comment_start, g_comment_end); + dumpOprs(fout, oprinfo, numOperators, tinfo, numTypes); +} +if (fout) { + if (g_verbose) fprintf(stderr,"%s dumping out indices %s\n", + g_comment_start, g_comment_end); + dumpIndices(fout, indinfo, numIndices, tblinfo, numTables, tablename); +} *numTablesPtr = numTables; return tblinfo; } diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 9ef8f8f35e..a25450e31f 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -20,12 +20,18 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v 1.1.1.1 1996/07/09 06:22:14 scrappy Exp $ + * $Header: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v 1.2 1996/07/12 05:39:35 scrappy Exp $ + * + * Modifications - 6/10/96 - dave@bensoft.com + * + * Applied 'insert string' patch from "Marc G. Fournier" + * Added '-t table' option + * Added '-a' option + * Added '-da' option * *------------------------------------------------------------------------- */ - #include #include #include @@ -47,6 +53,8 @@ int g_verbose; /* verbose flag */ int g_last_builtin_oid; /* value of the last builtin oid */ FILE *g_fout; /* the script file */ PGconn *g_conn; /* the database connection */ +int dumpData; /* dump data using proper insert strings */ +int attrNames; /* put attr names into insert strings */ char g_opaque_type[10]; /* name for the opaque type */ @@ -60,10 +68,14 @@ usage(char* progname) { fprintf(stderr, "usage: %s [options] [dbname]\n",progname); fprintf(stderr, "\t -f filename \t\t script output filename\n"); + fprintf(stderr, "\t -d[a] \t\t dump data as proper insert strings\n"); + fprintf(stderr, "\t \t\t (if 'a' then attribute names also)\n"); fprintf(stderr, "\t -H hostname \t\t server host name\n"); fprintf(stderr, "\t -p port \t\t server port number\n"); fprintf(stderr, "\t -v \t\t verbose\n"); fprintf(stderr, "\t -S \t\t dump out only the schema, no data\n"); + fprintf(stderr, "\t -a \t\t dump out only the data, no schema\n"); + fprintf(stderr, "\t -t table \t\t dump for this table only\n"); fprintf(stderr, "\n if dbname is not supplied, then the DATABASE environment name is used\n"); fprintf(stderr, "\n"); @@ -89,25 +101,28 @@ main(int argc, char** argv) char* filename; char* dbname; int schemaOnly; + int dataOnly; char *pghost = NULL; char *pgport = NULL; + char *tablename; TableInfo *tblinfo; int numTables; dbname = NULL; filename = NULL; + tablename = NULL; g_verbose = 0; strcpy(g_comment_start,"-- "); g_comment_end[0] = '\0'; strcpy(g_opaque_type, "opaque"); - schemaOnly = 0; + dataOnly = schemaOnly = dumpData = attrNames = 0; progname = *argv; - while ((c = getopt(argc, argv,"f:H:p:vSD")) != EOF) { + while ((c = getopt(argc, argv,"f:H:p:t:vSDd:a")) != EOF) { switch(c) { case 'f': /* output file name */ filename = optarg; @@ -124,6 +139,16 @@ main(int argc, char** argv) case 'S': /* dump schema only */ schemaOnly = 1; break; + case 'd': /* dump data as proper insert strings */ + dumpData = 1; + attrNames = strstr(optarg,"a") ? 1 : 0; + break; + case 't': /* Dump data for this table only */ + tablename = optarg; + break; + case 'a': /* Dump data only */ + dataOnly = 1; + break; default: usage(progname); break; @@ -159,18 +184,25 @@ main(int argc, char** argv) g_last_builtin_oid = findLastBuiltinOid(); + if (!dataOnly) { + if (g_verbose) fprintf(stderr, "%s last builtin oid is %d %s\n", g_comment_start, g_last_builtin_oid, g_comment_end); - tblinfo = dumpSchema(g_fout, &numTables); + tblinfo = dumpSchema(g_fout, &numTables, tablename); + + } + else { + tblinfo = dumpSchema(NULL, &numTables, tablename); + } if (!schemaOnly) { if (g_verbose) fprintf(stderr,"%s dumping out the contents of each table %s\n", g_comment_start, g_comment_end); - dumpClasses(tblinfo, numTables, g_fout); + dumpClasses(tblinfo, numTables, g_fout, tablename); } fflush(g_fout); @@ -1135,10 +1167,10 @@ dumpAggs(FILE* fout, AggInfo* agginfo, int numAggs, * dumpTables: * write out to fout all the user-define tables */ -void -dumpTables(FILE* fout, TableInfo *tblinfo, int numTables, + +void dumpTables(FILE* fout, TableInfo *tblinfo, int numTables, InhInfo *inhinfo, int numInherits, - TypeInfo *tinfo, int numTypes) + TypeInfo *tinfo, int numTypes, char *tablename) { int i,j,k; char q[MAXQUERYLEN]; @@ -1149,59 +1181,62 @@ dumpTables(FILE* fout, TableInfo *tblinfo, int numTables, for (i=0;i 0) ? ", " : "", - tblinfo[i].attnames[j], - tblinfo[i].typnames[j]); - actual_atts++; + if (!tablename || (!strcmp(tblinfo[i].relname,tablename))) { + + /* skip archive names*/ + if (isArchiveName(tblinfo[i].relname)) + continue; + + parentRels = tblinfo[i].parentRels; + numParents = tblinfo[i].numParents; + + sprintf(q, "CREATE TABLE %s (", tblinfo[i].relname); + actual_atts = 0; + for (j=0;j 0) ? ", " : "", + tblinfo[i].attnames[j], + tblinfo[i].typnames[j]); + actual_atts++; + } } - } - strcat(q,")"); + strcat(q,")"); - if (numParents > 0) { - sprintf(q, "%s inherits ( ",q); - for (k=0;k 0) { + sprintf(q, "%s inherits ( ",q); + for (k=0;k0) ? ", " : "", - parentRels[k]); + q, + (k>0) ? ", " : "", + parentRels[k]); + } + strcat(q,")"); } - strcat(q,")"); - } - switch(tblinfo[i].relarch[0]) { - case 'n': - archiveMode = "none"; - break; - case 'h': - archiveMode = "heavy"; - break; - case 'l': - archiveMode = "light"; - break; - default: - fprintf(stderr, "unknown archive mode\n"); - archiveMode = "none"; - break; - } + switch(tblinfo[i].relarch[0]) { + case 'n': + archiveMode = "none"; + break; + case 'h': + archiveMode = "heavy"; + break; + case 'l': + archiveMode = "light"; + break; + default: + fprintf(stderr, "unknown archive mode\n"); + archiveMode = "none"; + break; + } - sprintf(q, "%s archive = %s;\n", - q, - archiveMode); - fputs(q,fout); + sprintf(q, "%s archive = %s;\n", + q, + archiveMode); + fputs(q,fout); + } } } @@ -1211,7 +1246,7 @@ dumpTables(FILE* fout, TableInfo *tblinfo, int numTables, */ void dumpIndices(FILE* fout, IndInfo* indinfo, int numIndices, - TableInfo* tblinfo, int numTables) + TableInfo* tblinfo, int numTables, char *tablename) { int i; int tableInd; @@ -1243,82 +1278,139 @@ dumpIndices(FILE* fout, IndInfo* indinfo, int numIndices, PQfnumber(res,"proname"))); PQclear(res); } - sprintf(q,"CREATE INDEX %s on %s using %s (", - indinfo[i].indexrelname, - indinfo[i].indrelname, - indinfo[i].indamname); - if (funcname) { - sprintf(q, "%s %s(%s) %s);\n", - q,funcname, attname, indinfo[i].indclassname); - free(funcname); - } else - sprintf(q, "%s %s %s);\n", - q,attname,indinfo[i].indclassname); - - fputs(q,fout); + + if (!tablename || (!strcmp(indinfo[i].indrelname,tablename))) { + + sprintf(q,"CREATE INDEX %s on %s using %s (", + indinfo[i].indexrelname, + indinfo[i].indrelname, + indinfo[i].indamname); + if (funcname) { + sprintf(q, "%s %s(%s) %s);\n", + q,funcname, attname, indinfo[i].indclassname); + free(funcname); + } else + sprintf(q, "%s %s %s);\n", + q,attname,indinfo[i].indclassname); + + fputs(q,fout); + } } } - - - - /* * DumpClasses - * dump the contents of all the classes. */ void -dumpClasses(TableInfo *tblinfo, int numTables, FILE *fout) +dumpClasses(TableInfo *tblinfo, int numTables, FILE *fout, char *onlytable) { char query[255]; #define COPYBUFSIZ 8192 char copybuf[COPYBUFSIZ]; + char q[MAXQUERYLEN]; PGresult *res; - int i; + int i,j; + int actual_atts; /* number of attrs in this a table */ int ret; + int field; + int tuple; int copydone; for(i = 0; i < numTables; i++) { char *classname = tblinfo[i].relname; - /* skip archive names*/ - if (isArchiveName(classname)) - continue; - - fprintf(fout, "COPY %s from stdin;\n", classname); - sprintf(query, "COPY %s to stdout;\n", classname); - res = PQexec(g_conn, query); - if (!res || - PQresultStatus(res) != PGRES_COPY_OUT) { - fprintf(stderr,"dumpClasses(): COPY to stdout failed"); - exit_nicely(g_conn); - } - copydone = 0; - while (!copydone) { - ret = PQgetline(res->conn, copybuf, COPYBUFSIZ); + if (!onlytable || (!strcmp(classname,onlytable))) { + + /* skip archive names*/ + if (isArchiveName(classname)) + continue; + + if(!dumpData) { + fprintf(fout, "COPY %s from stdin;\n", classname); + sprintf(query, "COPY %s to stdout;\n", classname); + res = PQexec(g_conn, query); + if (!res || + PQresultStatus(res) != PGRES_COPY_OUT) { + fprintf(stderr,"dumpClasses(): COPY to stdout failed"); + exit_nicely(g_conn); + } + copydone = 0; + while (!copydone) { + ret = PQgetline(res->conn, copybuf, COPYBUFSIZ); - if (copybuf[0] == '.' && copybuf[1] =='\0') { - copydone = true; /* don't print this... */ - } else { - fputs(copybuf, stdout); - switch (ret) { - case EOF: - copydone = true; - /*FALLTHROUGH*/ - case 0: - fputc('\n', stdout); - break; - case 1: - break; - } + if (copybuf[0] == '.' && copybuf[1] =='\0') { + copydone = true; /* don't print this... */ + } else { + fputs(copybuf, stdout); + switch (ret) { + case EOF: + copydone = true; + /*FALLTHROUGH*/ + case 0: + fputc('\n', stdout); + break; + case 1: + break; + } + } + } + fprintf(fout, ".\n"); + PQclear(res); + PQendcopy(res->conn); + } else { + sprintf(query, "select * from %s;\n", classname); + res = PQexec(g_conn, query); + if (!res || + PQresultStatus(res) != PGRES_TUPLES_OK) { + fprintf(stderr,"dumpClasses(): command failed"); + exit_nicely(g_conn); + } + tuple=0; + while(tuple < PQntuples(res)) { + fprintf(fout, "insert into %s ", classname); + if (attrNames) { + actual_atts = 0; + sprintf(q, "("); + for (j=0;j 0) ? "," : "", + tblinfo[i].attnames[j]); + actual_atts++; + } + } + sprintf(q,"%s%s",q, ") "); + fprintf(fout, q); + } + fprintf(fout, "values ("); + field=0; + do { + switch(PQftype(res,field)) { + case 21: case 22: case 23: /* int types */ + case 810: case 910: /* oldint types */ + case 1005: case 1006: case 1007: /* _int types */ + case 700: case 701: /* float types */ + case 1021: case 1022: /* _float types */ + fprintf(fout, "%s", PQgetvalue(res,tuple,field)); + break; + default: + fprintf(fout, "'%s'", PQgetvalue(res,tuple,field)); + break; + } + field++; + if(field != PQnfields(res)) + fprintf(fout, ","); + } while(field < PQnfields(res)); + fprintf(fout, ");\n"); + tuple++; + } + PQclear(res); } - } - fprintf(fout, ".\n"); - PQclear(res); - PQendcopy(res->conn); - } - + } + } } /* diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index e77960afe4..1ef6d6a032 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -5,7 +5,7 @@ * * Copyright (c) 1994, Regents of the University of California * - * $Id: pg_dump.h,v 1.1.1.1 1996/07/09 06:22:14 scrappy Exp $ + * $Id: pg_dump.h,v 1.2 1996/07/12 05:39:39 scrappy Exp $ * *------------------------------------------------------------------------- */ @@ -135,7 +135,7 @@ extern char g_opaque_type[10]; /* name for the opaque type */ * common utility functions */ -extern TableInfo* dumpSchema(FILE* fout, int *numTablesPtr); +extern TableInfo* dumpSchema(FILE* fout, int *numTablesPtr, char *tablename); extern char* findTypeByOid(TypeInfo* tinfo, int numTypes, char* oid); extern char* findOprByOid(OprInfo *oprinfo, int numOprs, char *oid); @@ -178,11 +178,11 @@ extern void dumpOneFunc(FILE* fout, FuncInfo* finfo, int i, TypeInfo *tinfo, int numTypes); extern void dumpTables(FILE* fout, TableInfo* tbinfo, int numTables, InhInfo *inhinfo, int numInherits, - TypeInfo *tinfo, int numTypes); + TypeInfo *tinfo, int numTypes, char *tablename); extern void dumpIndices(FILE* fout, IndInfo* indinfo, int numIndices, - TableInfo* tbinfo, int numTables); + TableInfo* tbinfo, int numTables, char *tablename); -extern void dumpClasses(TableInfo *tbinfo, int numTables, FILE *fout); +extern void dumpClasses(TableInfo *tbinfo, int numTables, FILE *fout, char *tablename); extern void dumpTuples(PGresult *res, FILE *fout, int *attrmap); extern char* checkForQuote(char* s); extern int findLastBuiltinOid(); -- 2.11.0