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 */