Stefan de Konink wrote:
I am looking at this now I agree with you that this could help a lot.
Will need to move some stuff around because of the -D option that (imho)
should inline everything.
As attached.
Ok to commit? If so could anyone help with approving tests?
Stefan
Index: dump.c
===================================================================
RCS file: /cvsroot/monetdb/clients/src/mapiclient/dump.c,v
retrieving revision 1.38
diff -u -r1.38 dump.c
--- dump.c 26 Apr 2009 08:56:59 -0000 1.38
+++ dump.c 10 May 2009 12:50:26 -0000
@@ -105,6 +105,279 @@
return sname;
}
+static void dump_constraints(Mapi mid, char *schema, char *tname, stream
*toConsole) {
+
+ MapiHdl hdl;
+ int cnt;
+ char *query;
+ size_t maxquerylen = 0;
+
+ if (tname == NULL) {
+ query = "SELECT \"c\".\"name\"," /* 0 */
+ "\"c\".\"null\"," /* 1 */
+ "\"c\".\"default\"," /* 2 */
+ "\"c\".\"number\"," /* 3 */
+ "\"s\".\"name\"," /* 4 */
+ "\"t\".\"name\" " /* 5 */
+ "FROM \"sys\".\"_columns\" \"c\", "
+ "\"sys\".\"_tables\" \"t\", "
+ "\"sys\".\"schemas\" \"s\" "
+ "WHERE \"c\".\"table_id\" = \"t\".\"id\" "
+ "AND \"t\".\"schema_id\" = \"s\".\"id\" "
+ "ORDER BY \"s\".\"name\", \"t\".\"name\", \"number\"";
+ if ((hdl = mapi_query(mid, query)) == NULL || mapi_error(mid)) {
+ if (hdl) {
+ mapi_explain_query(hdl, stderr);
+ mapi_close_handle(hdl);
+ } else
+ mapi_explain(mid, stderr);
+ goto bailout;
+ }
+
+ while ((mapi_fetch_row(hdl)) != 0) {
+ char *c_name = mapi_fetch_field(hdl, 0);
+ char *c_null = mapi_fetch_field(hdl, 1);
+ char *c_default = mapi_fetch_field(hdl, 2);
+ char *s_name = mapi_fetch_field(hdl, 4);
+ char *t_name = mapi_fetch_field(hdl, 5);
+
+ if (mapi_error(mid)) {
+ mapi_explain_query(hdl, stderr);
+ mapi_close_handle(hdl);
+ goto bailout;
+ }
+
+ if (strcmp(c_null, "false") == 0) {
+ stream_printf(toConsole, "ALTER TABLE ");
+ quoted_print(toConsole, s_name);
+ stream_printf(toConsole, ".");
+ quoted_print(toConsole, t_name);
+ stream_printf(toConsole, " ALTER COLUMN ");
+ quoted_print(toConsole, c_name);
+ stream_printf(toConsole, " SET NOT NULL;\n");
+ }
+
+ if (c_default != NULL) {
+ stream_printf(toConsole, "ALTER TABLE ");
+ quoted_print(toConsole, s_name);
+ stream_printf(toConsole, ".");
+ quoted_print(toConsole, t_name);
+ stream_printf(toConsole, " ALTER COLUMN ");
+ quoted_print(toConsole, c_name);
+ stream_printf(toConsole, " SET DEFAULT %s;\n",
c_default);
+ }
+ }
+ if (mapi_error(mid)) {
+ mapi_explain_query(hdl, stderr);
+ mapi_close_handle(hdl);
+ goto bailout;
+ }
+ mapi_close_handle(hdl);
+ }
+
+ /* presumably we don't need to order on id, since there should
+ only be a single primary key, but it doesn't hurt, and the
+ code is then close to the code for the uniqueness
+ constraint */
+
+ if (tname != NULL) {
+ maxquerylen = 512 + strlen(tname) + strlen(schema);
+ query = malloc(maxquerylen);
+ snprintf(query, maxquerylen,
+ "SELECT \"kc\".\"column\", " /* 0 */
+ "\"kc\".\"nr\", " /* 1 */
+ "\"k\".\"name\", " /* 2 */
+ "\"k\".\"id\", " /* 3 */
+ "\"s\".\"name\", " /* 4 */
+ "\"t\".\"name\" " /* 5 */
+ "FROM \"sys\".\"keycolumns\" \"kc\", "
+ "\"sys\".\"keys\" \"k\", "
+ "\"sys\".\"schemas\" \"s\", "
+ "\"sys\".\"_tables\" \"t\" "
+ "WHERE \"kc\".\"id\" = \"k\".\"id\" AND "
+ "\"k\".\"table_id\" = \"t\".\"id\" AND "
+ "\"k\".\"type\" = 0 AND "
+ "\"t\".\"schema_id\" = \"s\".\"id\" AND "
+ "\"s\".\"name\" = '%s' AND "
+ "\"t\".\"name\" = '%s' "
+ "ORDER BY \"id\", \"nr\"", schema, tname);
+ } else {
+ query = "SELECT \"kc\".\"column\"," /* 0 */
+ "\"kc\".\"nr\", " /* 1 */
+ "\"k\".\"name\", " /* 2 */
+ "\"k\".\"id\"," /* 3 */
+ "\"s\".\"name\"," /* 4 */
+ "\"t\".\"name\" " /* 5 */
+ "FROM \"sys\".\"keycolumns\" \"kc\", "
+ "\"sys\".\"keys\" \"k\", "
+ "\"sys\".\"schemas\" \"s\", "
+ "\"sys\".\"_tables\" \"t\" "
+ "WHERE \"kc\".\"id\" = \"k\".\"id\" AND "
+ "\"k\".\"table_id\" = \"t\".\"id\" AND "
+ "\"k\".\"type\" = 0 AND "
+ "\"t\".\"schema_id\" = \"s\".\"id\" "
+ "ORDER BY \"s\".\"name\", \"t\".\"name\", \"id\",
\"nr\"";
+ }
+ if ((hdl = mapi_query(mid, query)) == NULL || mapi_error(mid)) {
+ if (hdl) {
+ mapi_explain_query(hdl, stderr);
+ mapi_close_handle(hdl);
+ } else
+ mapi_explain(mid, stderr);
+ goto bailout;
+ }
+ cnt = 0;
+ while ((mapi_fetch_row(hdl)) != 0) {
+ char *c_column = mapi_fetch_field(hdl, 0);
+ char *k_name = mapi_fetch_field(hdl, 2);
+ char *s_name = mapi_fetch_field(hdl, 4);
+ char *t_name = mapi_fetch_field(hdl, 5);
+
+ if (mapi_error(mid)) {
+ mapi_explain_query(hdl, stderr);
+ mapi_close_handle(hdl);
+ goto bailout;
+ }
+ if (cnt == 0) {
+ if (tname) {
+ stream_printf(toConsole, ",\n\t");
+ } else {
+ if (s_name) {
+ if (cnt > 0) stream_printf(toConsole,
");\n");
+ stream_printf(toConsole, "ALTER TABLE
");
+ quoted_print(toConsole, s_name);
+ stream_printf(toConsole, ".");
+ quoted_print(toConsole, t_name);
+ stream_printf(toConsole, " ADD ");
+ }
+ }
+
+ if (k_name) {
+ stream_printf(toConsole, "CONSTRAINT ");
+ quoted_print(toConsole, k_name);
+ stream_write(toConsole, " ", 1, 1);
+ }
+ stream_printf(toConsole, "PRIMARY KEY (");
+ } else
+ stream_printf(toConsole, ", ");
+ quoted_print(toConsole, c_column);
+ cnt++;
+ }
+ if (cnt) {
+ stream_printf(toConsole, ")");
+ if (!tname)
+ stream_printf(toConsole, ";\n");
+ }
+
+ if (mapi_error(mid)) {
+ mapi_explain_query(hdl, stderr);
+ mapi_close_handle(hdl);
+ goto bailout;
+ }
+ mapi_close_handle(hdl);
+
+ if (tname != NULL) {
+ snprintf(query, maxquerylen,
+ "SELECT \"kc\".\"column\"," /* 0 */
+ "\"kc\".\"nr\"," /* 1 */
+ "\"k\".\"name\"," /* 2 */
+ "\"k\".\"id\"," /* 3 */
+ "\"s\".\"name\"," /* 4 */
+ "\"t\".\"name\" " /* 5 */
+ "FROM \"sys\".\"keycolumns\" \"kc\", "
+ "\"sys\".\"keys\" \"k\", "
+ "\"sys\".\"schemas\" \"s\", "
+ "\"sys\".\"_tables\" \"t\" "
+ "WHERE \"kc\".\"id\" = \"k\".\"id\" AND "
+ "\"k\".\"table_id\" = \"t\".\"id\" AND "
+ "\"k\".\"type\" = 1 AND "
+ "\"t\".\"schema_id\" = \"s\".\"id\" AND "
+ "\"s\".\"name\" = '%s' AND "
+ "\"t\".\"name\" = '%s' "
+ "ORDER BY \"id\", \"nr\"", schema, tname);
+ } else {
+ query = "SELECT \"kc\".\"column\"," /* 0 */
+ "\"kc\".\"nr\"," /* 1 */
+ "\"k\".\"name\"," /* 2 */
+ "\"k\".\"id\"," /* 3 */
+ "\"s\".\"name\"," /* 4 */
+ "\"t\".\"name\" " /* 5 */
+ "FROM \"sys\".\"keycolumns\" \"kc\", "
+ "\"sys\".\"keys\" \"k\", "
+ "\"sys\".\"schemas\" \"s\", "
+ "\"sys\".\"_tables\" \"t\" "
+ "WHERE \"kc\".\"id\" = \"k\".\"id\" AND "
+ "\"k\".\"table_id\" = \"t\".\"id\" AND "
+ "\"k\".\"type\" = 1 AND "
+ "\"t\".\"schema_id\" = \"s\".\"id\" "
+ "ORDER BY \"s\".\"name\", \"t\".\"name\",\"id\",
\"nr\"";
+ }
+
+ if ((hdl = mapi_query(mid, query)) == NULL || mapi_error(mid)) {
+ if (hdl) {
+ mapi_explain_query(hdl, stderr);
+ mapi_close_handle(hdl);
+ } else
+ mapi_explain(mid, stderr);
+ goto bailout;
+ }
+ cnt = 0;
+ while ((mapi_fetch_row(hdl)) != 0) {
+ char *c_column = mapi_fetch_field(hdl, 0);
+ char *kc_nr = mapi_fetch_field(hdl, 1);
+ char *k_name = mapi_fetch_field(hdl, 2);
+ char *s_name = mapi_fetch_field(hdl, 4);
+ char *t_name = mapi_fetch_field(hdl, 5);
+
+ if (mapi_error(mid)) {
+ mapi_explain_query(hdl, stderr);
+ mapi_close_handle(hdl);
+ goto bailout;
+ }
+ if (strcmp(kc_nr, "0") == 0) {
+ if (cnt)
+ stream_write(toConsole, ")", 1, 1);
+
+ if (t_name) {
+ stream_printf(toConsole, ",\n\t");
+ } else {
+ if (cnt > 0) stream_printf(toConsole, ");\n");
+ stream_printf(toConsole, "ALTER TABLE ");
+ quoted_print(toConsole, s_name);
+ stream_printf(toConsole, ".");
+ quoted_print(toConsole, t_name);
+ stream_printf(toConsole, " ADD ");
+ }
+
+ if (k_name) {
+ stream_printf(toConsole, "CONSTRAINT ");
+ quoted_print(toConsole, k_name);
+ stream_write(toConsole, " ", 1, 1);
+ }
+ stream_printf(toConsole, "UNIQUE (");
+ cnt = 1;
+ } else
+ stream_printf(toConsole, ", ");
+ quoted_print(toConsole, c_column);
+ }
+ if (cnt) {
+ stream_write(toConsole, ")", 1, 1);
+ if (!tname)
+ stream_printf(toConsole, ";\n");
+ }
+
+ if (mapi_error(mid)) {
+ mapi_explain_query(hdl, stderr);
+ mapi_close_handle(hdl);
+ goto bailout;
+ }
+ mapi_close_handle(hdl);
+
+bailout:
+ if (query != NULL && maxquerylen != 0)
+ free(query);
+}
+
static void
dump_foreign_keys(Mapi mid, char *schema, char *tname, stream *toConsole)
{
@@ -117,14 +390,14 @@
maxquerylen = 1024 + strlen(tname) + strlen(schema);
query = malloc(maxquerylen);
snprintf(query, maxquerylen,
- "SELECT \"ps\".\"name\"," /* 0 */
- "\"pkt\".\"name\"," /* 1 */
- "\"pkkc\".\"column\"," /* 2 */
- "\"fkkc\".\"column\"," /* 3 */
- "\"fkkc\".\"nr\"," /* 4 */
- "\"fkk\".\"name\"," /* 5 */
- "\"fkk\".\"action\"," /* 6 */
- "\"fs\".\"name\"," /* 7 */
+ "SELECT \"ps\".\"name\", " /* 0 */
+ "\"pkt\".\"name\", " /* 1 */
+ "\"pkkc\".\"column\", " /* 2 */
+ "\"fkkc\".\"column\", " /* 3 */
+ "\"fkkc\".\"nr\", " /* 4 */
+ "\"fkk\".\"name\", " /* 5 */
+ "\"fkk\".\"action\", " /* 6 */
+ "\"fs\".\"name\", " /* 7 */
"\"fkt\".\"name\" " /* 8 */
"FROM \"sys\".\"_tables\" \"fkt\","
"\"sys\".\"keycolumns\" \"fkkc\","
@@ -478,71 +751,14 @@
} else {
stream_printf(toConsole, "%s(%s,%s)", c_type,
c_type_digits, c_type_scale);
}
- if (strcmp(c_null, "false") == 0)
- stream_printf(toConsole, " NOT NULL");
- if (c_default != NULL)
- stream_printf(toConsole, " DEFAULT %s", c_default);
- cnt++;
- }
- if (mapi_error(mid)) {
- mapi_explain_query(hdl, stderr);
- mapi_close_handle(hdl);
- goto bailout;
- }
- mapi_close_handle(hdl);
- /* presumably we don't need to order on id, since there should
- only be a single primary key, but it doesn't hurt, and the
- code is then close to the code for the uniqueness
- constraint */
- snprintf(query, maxquerylen,
- "SELECT \"kc\".\"column\"," /* 0 */
- "\"kc\".\"nr\", " /* 1 */
- "\"k\".\"name\", " /* 2 */
- "\"k\".\"id\" " /* 3 */
- "FROM \"sys\".\"keycolumns\" \"kc\", "
- "\"sys\".\"keys\" \"k\", "
- "\"sys\".\"schemas\" \"s\", "
- "\"sys\".\"_tables\" \"t\" "
- "WHERE \"kc\".\"id\" = \"k\".\"id\" AND "
- "\"k\".\"table_id\" = \"t\".\"id\" AND "
- "\"k\".\"type\" = 0 AND "
- "\"t\".\"schema_id\" = \"s\".\"id\" AND "
- "\"s\".\"name\" = '%s' AND "
- "\"t\".\"name\" = '%s' "
- "ORDER BY \"id\", \"nr\"", schema, tname);
- if ((hdl = mapi_query(mid, query)) == NULL || mapi_error(mid)) {
- if (hdl) {
- mapi_explain_query(hdl, stderr);
- mapi_close_handle(hdl);
- } else
- mapi_explain(mid, stderr);
- goto bailout;
- }
- cnt = 0;
- while ((mapi_fetch_row(hdl)) != 0) {
- char *c_column = mapi_fetch_field(hdl, 0);
- char *k_name = mapi_fetch_field(hdl, 2);
-
- if (mapi_error(mid)) {
- mapi_explain_query(hdl, stderr);
- mapi_close_handle(hdl);
- goto bailout;
+ if (describe) {
+ if (strcmp(c_null, "false") == 0)
+ stream_printf(toConsole, " NOT NULL");
+ if (c_default != NULL)
+ stream_printf(toConsole, " DEFAULT %s",
c_default);
}
- if (cnt == 0) {
- stream_printf(toConsole, ",\n\t");
- if (k_name) {
- stream_printf(toConsole, "CONSTRAINT ");
- quoted_print(toConsole, k_name);
- stream_write(toConsole, " ", 1, 1);
- }
- stream_printf(toConsole, "PRIMARY KEY (");
- } else
- stream_printf(toConsole, ", ");
- quoted_print(toConsole, c_column);
cnt++;
}
- if (cnt)
- stream_printf(toConsole, ")");
if (mapi_error(mid)) {
mapi_explain_query(hdl, stderr);
mapi_close_handle(hdl);
@@ -550,64 +766,8 @@
}
mapi_close_handle(hdl);
- snprintf(query, maxquerylen,
- "SELECT \"kc\".\"column\"," /* 0 */
- "\"kc\".\"nr\", " /* 1 */
- "\"k\".\"name\", " /* 2 */
- "\"k\".\"id\" " /* 3 */
- "FROM \"sys\".\"keycolumns\" \"kc\", "
- "\"sys\".\"keys\" \"k\", "
- "\"sys\".\"schemas\" \"s\", "
- "\"sys\".\"_tables\" \"t\" "
- "WHERE \"kc\".\"id\" = \"k\".\"id\" AND "
- "\"k\".\"table_id\" = \"t\".\"id\" AND "
- "\"k\".\"type\" = 1 AND "
- "\"t\".\"schema_id\" = \"s\".\"id\" AND "
- "\"s\".\"name\" = '%s' AND "
- "\"t\".\"name\" = '%s' "
- "ORDER BY \"id\", \"nr\"", schema, tname);
- if ((hdl = mapi_query(mid, query)) == NULL || mapi_error(mid)) {
- if (hdl) {
- mapi_explain_query(hdl, stderr);
- mapi_close_handle(hdl);
- } else
- mapi_explain(mid, stderr);
- goto bailout;
- }
- cnt = 0;
- while ((mapi_fetch_row(hdl)) != 0) {
- char *c_column = mapi_fetch_field(hdl, 0);
- char *kc_nr = mapi_fetch_field(hdl, 1);
- char *k_name = mapi_fetch_field(hdl, 2);
-
- if (mapi_error(mid)) {
- mapi_explain_query(hdl, stderr);
- mapi_close_handle(hdl);
- goto bailout;
- }
- if (strcmp(kc_nr, "0") == 0) {
- if (cnt)
- stream_write(toConsole, ")", 1, 1);
- stream_printf(toConsole, ",\n\t");
- if (k_name) {
- stream_printf(toConsole, "CONSTRAINT ");
- quoted_print(toConsole, k_name);
- stream_write(toConsole, " ", 1, 1);
- }
- stream_printf(toConsole, "UNIQUE (");
- cnt = 1;
- } else
- stream_printf(toConsole, ", ");
- quoted_print(toConsole, c_column);
- }
- if (cnt)
- stream_write(toConsole, ")", 1, 1);
- if (mapi_error(mid)) {
- mapi_explain_query(hdl, stderr);
- mapi_close_handle(hdl);
- goto bailout;
- }
- mapi_close_handle(hdl);
+ if (describe)
+ dump_constraints(mid, schema, tname, toConsole);
if (foreign)
dump_foreign_keys(mid, schema, tname, toConsole);
@@ -1158,6 +1318,7 @@
mapi_close_handle(hdl);
if (!describe) {
+ dump_constraints(mid, NULL, NULL, toConsole);
dump_foreign_keys(mid, NULL, NULL, toConsole);
/* dump sequences, part 2 */
------------------------------------------------------------------------------
The NEW KODAK i700 Series Scanners deliver under ANY circumstances! Your
production scanning environment may not be a perfect world - but thanks to
Kodak, there's a perfect scanner to get the job done! With the NEW KODAK i700
Series Scanner you'll get full speed at 300 dpi even with all image
processing features enabled. http://p.sf.net/sfu/kodak-com
_______________________________________________
Monetdb-developers mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-developers
