/*------------------------------------------------------------------------- * * pg4_dump.c-- * pg4_dump is an utility for dumping out a postgres database * into a script file. * * pg4_dump will read the system catalogs from a postgresV4r2 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 * * the output script is either POSTQUEL or SQL * * Copyright (c) 1994, Regents of the University of California * * * IDENTIFICATION * /usr/local/devel/pglite/cvs/src/bin/pg_dump/pg4_dump.c,v 1.1 1995/05/18 19:23:53 jolly Exp * *------------------------------------------------------------------------- */ #include #include #include /* for MAXHOSTNAMELEN on most */ #ifdef PORTNAME_sparc_solaris #include /* for MAXHOSTNAMELEN on some */ #endif #include "tmp/postgres.h" #include "tmp/libpq-fe.h" #include "libpq/auth.h" #include "pg_dump.h" extern char *optarg; extern int optind, opterr; /* these are used in libpq */ extern char *PQhost; /* machine on which the backend is running */ extern char *PQport; /* comm. port with the postgres backend. */ extern char *PQtty; /* the tty where postgres msgs are displayed */ extern char *PQdatabase; /* the postgres db to access. */ /* global decls */ int g_verbose; /* verbose flag */ int g_last_builtin_oid; /* value of the last builtin oid */ FILE *g_fout; /* the script file */ 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]; int g_outputSQL; /* if 1, output SQL, otherwise , output Postquel */ static 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 -H hostname \t\t server host name\n"); fprintf(stderr, "\t -o [SQL|POSTQUEL} \t\t output format\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, "\n if dbname is not supplied, then the DATABASE environment name is used\n"); fprintf(stderr, "\n"); fprintf(stderr, "\tpg4_dump dumps out postgres databases and produces a script file\n"); fprintf(stderr, "\tof query commands to regenerate the schema\n"); fprintf(stderr, "\tThe output format is either POSTQUEL or SQL. The default is SQL\n"); exit(1); } void main(int argc, char** argv) { int c; char* progname; char* filename; char* dbname; char *username, usernamebuf[NAMEDATALEN + 1]; char hostbuf[MAXHOSTNAMELEN]; int schemaOnly; TableInfo *tblinfo; int numTables; dbname = NULL; filename = NULL; g_verbose = 0; g_outputSQL = 1; schemaOnly = 0; progname = *argv; while ((c = getopt(argc, argv,"f:H:o:p:vSD")) != EOF) { switch(c) { case 'f': /* output file name */ filename = optarg; break; case 'H' : /* server host */ PQhost = optarg; break; case 'o': { char *lang = optarg; if (lang) { if (strcmp(lang,"SQL") != 0) g_outputSQL = 0; } } break; case 'p' : /* server port */ PQport = optarg; break; case 'v': /* verbose */ g_verbose = 1; break; case 'S': /* dump schema only */ schemaOnly = 1; break; default: usage(progname); break; } } /* open the output file */ if (filename == NULL) { g_fout = stdout; } else { g_fout = fopen(filename, "w"); if (g_fout == NULL) { fprintf(stderr,"%s: could not open output file named %s for writing\n", progname, filename); exit(2); } } /* Determine our username (according to the authentication system, if * there is one). */ if ((username = fe_getauthname()) == (char *) NULL) { fprintf(stderr, "%s: could not find a valid user name\n",progname); exit(2); } memset(usernamebuf, 0, sizeof(usernamebuf)); (void) strncpy(usernamebuf, username, NAMEDATALEN); username = usernamebuf; /* * Determine the hostname of the database server. Try to avoid using * "localhost" if at all possible. */ if (!PQhost && !(PQhost = getenv("PGHOST"))) PQhost = "localhost"; if (!strcmp(PQhost, "localhost")) { if (gethostname(hostbuf, MAXHOSTNAMELEN) != -1) PQhost = hostbuf; } /* find database */ if (!(dbname = argv[optind]) && !(dbname = getenv("DATABASE")) && !(dbname = username)) { fprintf(stderr, "%s: no database name specified\n",progname); exit (2); } PQsetdb(dbname); /* make sure things are ok before giving users a warm welcome! */ check_conn_and_db(); if (g_outputSQL) { strcpy(g_comment_start,"-- "); g_comment_end[0] = '\0'; strcpy(g_opaque_type, "opaque"); } else { strcpy(g_comment_start,"/* "); strcpy(g_comment_end,"*/ "); strcpy(g_opaque_type, "any"); } g_last_builtin_oid = findLastBuiltinOid(); 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); if (!schemaOnly) { if (g_verbose) { fprintf(stderr, "%s dumping out the contents of each table %s\n", g_comment_start, g_comment_end ); fprintf(stderr, "%s the output language is %s %s\n", g_comment_start, (g_outputSQL) ? "SQL" : "POSTQUEL", g_comment_end); } dumpClasses(tblinfo, numTables, g_fout); } fflush(g_fout); fclose(g_fout); exit(0); } /* * getTypes: * read all base types in the system catalogs and return them in the * TypeInfo* structure * * numTypes is set to the number of types read in * */ TypeInfo* getTypes(int *numTypes) { char* res; PortalBuffer* pbuf; int ntups; int i; char query[MAXQUERYLEN]; TypeInfo *tinfo; int i_oid; int i_typowner; int i_typname; int i_typlen; int i_typprtlen; int i_typinput; int i_typoutput; int i_typreceive; int i_typsend; int i_typelem; int i_typdelim; int i_typdefault; int i_typrelid; int i_typbyval; PQexec("begin"); /* find all base types */ /* we include even the built-in types because those may be used as array elements by user-defined types */ /* we filter out the built-in types when we dump out the types */ /* sprintf(query, "SELECT oid, typowner,typname, typlen, typprtlen, typinput, typoutput, typreceive, typsend, typelem, typdelim, typdefault, typrelid,typbyval from pg_type"); */ sprintf(query, "retrieve (t.oid, t.typowner, t.typname, t.typlen, t.typprtlen, t.typinput, t.typoutput, t.typreceive, t.typsend, t.typelem, t.typdelim, t.typdefault, t.typrelid, t.typbyval) from t in pg_type"); res = PQexec(query); pbuf = PQparray(res+1); ntups = PQntuplesGroup(pbuf,0); tinfo = (TypeInfo*)malloc(ntups * sizeof(TypeInfo)); i_oid = PQfnumberGroup(pbuf,0,"oid"); i_typowner = PQfnumberGroup(pbuf,0,"typowner"); i_typname = PQfnumberGroup(pbuf,0,"typname"); i_typlen = PQfnumberGroup(pbuf,0,"typlen"); i_typprtlen = PQfnumberGroup(pbuf,0,"typprtlen"); i_typinput = PQfnumberGroup(pbuf,0,"typinput"); i_typoutput = PQfnumberGroup(pbuf,0,"typoutput"); i_typreceive = PQfnumberGroup(pbuf,0,"typreceive"); i_typsend = PQfnumberGroup(pbuf,0,"typsend"); i_typelem = PQfnumberGroup(pbuf,0,"typelem"); i_typdelim = PQfnumberGroup(pbuf,0,"typdelim"); i_typdefault = PQfnumberGroup(pbuf,0,"typdefault"); i_typrelid = PQfnumberGroup(pbuf,0,"typrelid"); i_typbyval = PQfnumberGroup(pbuf,0,"typbyval"); for (i=0;i '%d'::oid", g_last_builtin_oid); */ sprintf(query, "retrieve (f.oid, f.proname, f.proowner, f.prolang, f.pronargs, f.prorettype, f.proretset, f.proargtypes, f.prosrc, f.probin) from f in pg_proc where f.oid > \"%d\"::oid", g_last_builtin_oid); res = PQexec(query); pbuf = PQparray(res+1); ntups = PQntuplesGroup(pbuf,0); *numFuncs = ntups; finfo = (FuncInfo*)malloc(ntups * sizeof(FuncInfo)); i_oid = PQfnumberGroup(pbuf,0,"oid"); i_proname = PQfnumberGroup(pbuf,0,"proname"); i_proowner = PQfnumberGroup(pbuf,0,"proowner"); i_prolang = PQfnumberGroup(pbuf,0,"prolang"); i_pronargs = PQfnumberGroup(pbuf,0,"pronargs"); i_proargtypes = PQfnumberGroup(pbuf,0,"proargtypes"); i_prorettype = PQfnumberGroup(pbuf,0,"prorettype"); i_proretset = PQfnumberGroup(pbuf,0,"proretset"); i_prosrc = PQfnumberGroup(pbuf,0,"prosrc"); i_probin = PQfnumberGroup(pbuf,0,"probin"); for (i=0;i 0 order by attnum",tblinfo[i].oid); */ if (g_verbose) fprintf(stderr,"%s finding the attrs and types for table: %s %s\n", g_comment_start, tblinfo[i].relname, g_comment_end); sprintf(q,"retrieve (a.attnum, a.attname, t.typname) from a in pg_attribute, t in pg_type where a.attrelid = \"%s\" and a.atttypid = t.oid and a.attnum > 0 sort by attnum",tblinfo[i].oid); res = PQexec(q); pbuf = PQparray(res+1); ntups = PQntuplesGroup(pbuf,0); i_attname = PQfnumberGroup(pbuf,0,"attname"); i_typname = PQfnumberGroup(pbuf,0,"typname"); tblinfo[i].numatts = ntups; tblinfo[i].attnames = (char**) malloc( ntups * sizeof(char*)); tblinfo[i].out_attnames = (char**) malloc( ntups * sizeof(char*)); tblinfo[i].typnames = (char**) malloc( ntups * sizeof(char*)); tblinfo[i].inhAttrs = (int*) malloc (ntups * sizeof(int)); tblinfo[i].parentRels = NULL; tblinfo[i].numParents = 0; for (j=0;j '%d'::oid and t2.relname !~ '^pg_';", g_last_builtin_oid); */ sprintf(query, "retrieve (indexrelname = t1.relname, indrelname = t2.relname, i.indproc, i.indkey[0], indclassname = o.opcname, indamname = a.amname) from i in pg_index, t1 in pg_class, t2 in pg_class, o in pg_opclass, a in pg_am where t1.oid = i.indexrelid and t2.oid = i.indrelid and o.oid = i.indclass[0] and t1.relam = a.oid and i.indexrelid > \"%d\"::oid and t2.relname !~ \"^pg_\" and t1.relname !~ \"^Xinx\"", g_last_builtin_oid); res = PQexec(query); pbuf = PQparray(res+1); ntups = PQntuplesGroup(pbuf,0); *numIndices = ntups; indinfo = (IndInfo*)malloc(ntups * sizeof (IndInfo)); i_indexrelname = PQfnumberGroup(pbuf,0,"indexrelname"); i_indrelname = PQfnumberGroup(pbuf,0,"indrelname"); i_indamname = PQfnumberGroup(pbuf,0,"indamname"); i_indproc = PQfnumberGroup(pbuf,0,"indproc"); i_indkey = PQfnumberGroup(pbuf,0,"indkey"); i_indclassname = PQfnumberGroup(pbuf,0,"indclassname"); for (i=0;i 0) ? "," : "", typname); } sprintf(q,"%s ) RETURNS %s%s AS '%s' LANGUAGE '%s';\n", q, finfo[i].retset ? " SETOF " : "", findTypeByOid(tinfo, numTypes, finfo[i].prorettype), (finfo[i].lang) ? finfo[i].probin : finfo[i].prosrc, (finfo[i].lang) ? "C" : "SQL"); if (finfo[i].lang != 1) { fprintf(stderr, "%s WARNING: text of function named %s is in POSTQUEL %s\n", g_comment_start, finfo[i].proname, g_comment_end); } } else { sprintf(q,"define function %s ( language = \"%s\", returntype = %s%s) arg is (", finfo[i].proname, (finfo[i].lang) ? "c" : "postquel", finfo[i].retset ? " setof " : "", findTypeByOid(tinfo, numTypes, finfo[i].prorettype) ); for (j=0;j 0) ? "," : "", typname); } sprintf(q,"%s ) as \"%s\"\\g\n", q, (finfo[i].lang) ? finfo[i].probin : finfo[i].prosrc); } fputs(q,fout); fflush(fout); } /* * dumpOprs * writes out to fout the queries to recreate all the user-defined operators * */ void dumpOprs(FILE* fout, OprInfo* oprinfo, int numOperators, TypeInfo *tinfo, int numTypes) { int i; char q[MAXQUERYLEN]; char leftarg[MAXQUERYLEN]; char rightarg[MAXQUERYLEN]; char commutator[MAXQUERYLEN]; char negator[MAXQUERYLEN]; char restrict[MAXQUERYLEN]; char join[MAXQUERYLEN]; char sortop[MAXQUERYLEN]; char comma[2]; for (i=0;i 0) ? ", " : "", tblinfo[i].attnames[j], tblinfo[i].typnames[j]); } else { sprintf(q, "%s%s %s = %s", q, (actual_atts > 0) ? ", " : "", tblinfo[i].attnames[j], tblinfo[i].typnames[j]); } actual_atts++; } } strcat(q,")"); if (numParents > 0) { int oa = 0; /* index for the out_attnames array */ int l; int parentInd; sprintf(q, "%s inherits ( ",q); for (k=0;k0) ? ", " : "", parentRels[k]); parentInd = findTableByName(tblinfo,numTables,parentRels[k]); /* the out_attnames are in order of the out_attnames of the parent tables */ for (l=0; l 0 ) { /* * Print out the tuples but only print tuples with at least * 1 field. */ outVals = (char**)malloc(m * sizeof(char*)); for (j = 0; j < n; j++) { for (k = 0; k < m; k++) { outVals[attrmap[k]] = PQgetvalue(pbuf, j, k); } for (k = 0; k < m; k++) { char *pval = outVals[k]; if (k!=0) fputc('\t', fout); /* delimiter for attribute */ if (pval) { while (*pval != '\0') { /* escape tabs, newlines and backslashes */ if (*pval=='\t' || *pval=='\n' || *pval=='\\') fputc('\\', fout); fputc(*pval, fout); pval++; } } } fputc('\n', fout); /* delimiter for a tuple */ } free (outVals); } } /* * findLastBuiltInOid - * find the last built in oid * we do this by looking up the oid of 'template1' in pg_database, * this is probably not foolproof but comes close */ int findLastBuiltinOid() { char *res; PortalBuffer* pbuf; int ntups; int last_oid; res = PQexec("retrieve (d.oid) from d in pg_database where d.datname = \"template1\""); pbuf = PQparray(res+1); ntups = PQntuplesGroup(pbuf,0); if (ntups != 1) { fprintf(stderr,"pg_dump: couldn't find the template1 database. You are really hosed\nGiving up\n"); exit(2); } return (atoi(PQgetvalue(pbuf,0, PQfnumberGroup(pbuf,0,"oid")))); } /* * checkForQuote: * checks a string for quote characters and backslashes them */ char* checkForQuote(char* s) { char *r; char c; char *result; int j = 0; r = malloc(strlen(s)*3 + 1); /* definitely long enough */ while ( (c = *s) != '\0') { if (c == '\"') { /* backslash the double quotes */ if (g_outputSQL) { r[j++] = '\\'; c = '\''; } else { r[j++] = '\\'; r[j++] = '\\'; } } r[j++] = c; s++; } r[j] = '\0'; result = dupstr(r); free(r); return result; }