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