Changeset: 6799efb0f254 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/6799efb0f254 Modified Files: clients/ChangeLog clients/mapiclient/dump.c clients/mapiclient/mclient.c clients/mapiclient/msqldump.1 clients/mapiclient/msqldump.c clients/mapiclient/msqldump.h tools/monetdbe/monetdbe_mapi.c Branch: default Log Message:
Implemented --output and --outputdir options in msqldump. diffs (truncated from 488 to 300 lines): diff --git a/clients/ChangeLog b/clients/ChangeLog --- a/clients/ChangeLog +++ b/clients/ChangeLog @@ -1,6 +1,13 @@ # ChangeLog file for clients # This file is updated with Maddlog +* Thu Jan 25 2024 Sjoerd Mullender <[email protected]> +- Msqldump now accepts --output and --outputdir options. When the + --outputdir option is used, the dump is placed in the file dump.sql in + the specified directory and all tables are dumped to separate CSV files. + In this way it is feasible to edit the dump script by hand if needed, + even for a large database. + * Wed Jan 24 2024 Sjoerd Mullender <[email protected]> - The --table (-t) option of msqldump now accepts SQL-style % wildcard characters to dump all tables that match the pattern. E.g. -t diff --git a/clients/mapiclient/dump.c b/clients/mapiclient/dump.c --- a/clients/mapiclient/dump.c +++ b/clients/mapiclient/dump.c @@ -1720,7 +1720,8 @@ describe_schema(Mapi mid, const char *sn static int dump_table_data(Mapi mid, const char *schema, const char *tname, - stream *sqlf, bool useInserts, bool noescape) + stream *sqlf, const char *ddir, const char *ext, + bool useInserts, bool noescape) { int cnt, i; int64_t rows; @@ -1729,6 +1730,7 @@ dump_table_data(Mapi mid, const char *sc size_t maxquerylen; unsigned char *string = NULL; char *s, *t; + stream *datf = sqlf; maxquerylen = 5120 + 2*strlen(tname) + 2*strlen(schema); query = malloc(maxquerylen); @@ -1809,8 +1811,33 @@ dump_table_data(Mapi mid, const char *sc mnstr_printf(sqlf, "COPY %" PRId64 " RECORDS INTO ", rows); dquoted_print(sqlf, schema, "."); dquoted_print(sqlf, tname, NULL); - mnstr_printf(sqlf, " FROM stdin USING DELIMITERS " - "E'\\t',E'\\n','\"'%s;\n", noescape ? " NO ESCAPE" : ""); + if (ddir) { + size_t fnl = strlen(ddir) + strlen(schema) + strlen(tname) + (ext ? strlen(ext) + 1 : 0) + 7; + char *fn = malloc(fnl); + if (fn == NULL) + goto bailout; + int off; +#ifdef MSC_VER + /* bloody Windows: enable %n format specifier */ + _set_printf_count_output(1); +#endif + if (ext) + snprintf(fn, fnl, "%s%c%n%s.%s.csv.%s", ddir, DIR_SEP, &off, schema, tname, ext); + else + snprintf(fn, fnl, "%s%c%n%s.%s.csv", ddir, DIR_SEP, &off, schema, tname); + mnstr_printf(sqlf, " FROM E"); + squoted_print(sqlf, fn + off, '\'', false); + mnstr_printf(sqlf, " ON CLIENT"); + datf = open_wastream(fn); + free(fn); + if (datf == NULL) { + goto bailout; + } + } else { + mnstr_printf(sqlf, " FROM stdin"); + } + mnstr_printf(sqlf, " USING DELIMITERS E'\\t',E'\\n','\"'%s;\n", + noescape ? " NO ESCAPE" : ""); } string = malloc(sizeof(unsigned char) * cnt); if (string == NULL) @@ -1830,68 +1857,68 @@ dump_table_data(Mapi mid, const char *sc const char *s; if (useInserts) { - mnstr_printf(sqlf, "INSERT INTO "); - dquoted_print(sqlf, schema, "."); - dquoted_print(sqlf, tname, " VALUES ("); + mnstr_printf(datf, "INSERT INTO "); + dquoted_print(datf, schema, "."); + dquoted_print(datf, tname, " VALUES ("); } for (i = 0; i < cnt; i++) { const char *tp = mapi_get_type(hdl, i); s = mapi_fetch_field(hdl, i); if (s == NULL) - mnstr_printf(sqlf, "NULL"); + mnstr_printf(datf, "NULL"); else if (useInserts) { if (strcmp(tp, "day_interval") == 0 || strcmp(tp, "sec_interval") == 0) { const char *p = strchr(s, '.'); if (p == NULL) p = s + strlen(s); - mnstr_printf(sqlf, "INTERVAL '%.*s' SECOND", (int) (p - s), s); + mnstr_printf(datf, "INTERVAL '%.*s' SECOND", (int) (p - s), s); } else if (strcmp(tp, "month_interval") == 0) - mnstr_printf(sqlf, "INTERVAL '%s' MONTH", s); + mnstr_printf(datf, "INTERVAL '%s' MONTH", s); else if (strcmp(tp, "timestamptz") == 0) - mnstr_printf(sqlf, "TIMESTAMP WITH TIME ZONE '%s'", s); + mnstr_printf(datf, "TIMESTAMP WITH TIME ZONE '%s'", s); else if (strcmp(tp, "timestamp") == 0) - mnstr_printf(sqlf, "TIMESTAMP '%s'", s); + mnstr_printf(datf, "TIMESTAMP '%s'", s); else if (strcmp(tp, "timetz") == 0) - mnstr_printf(sqlf, "TIME WITH TIME ZONE '%s'", s); + mnstr_printf(datf, "TIME WITH TIME ZONE '%s'", s); else if (strcmp(tp, "time") == 0) - mnstr_printf(sqlf, "TIME '%s'", s); + mnstr_printf(datf, "TIME '%s'", s); else if (strcmp(tp, "date") == 0) - mnstr_printf(sqlf, "DATE '%s'", s); + mnstr_printf(datf, "DATE '%s'", s); else if (strcmp(tp, "blob") == 0) - mnstr_printf(sqlf, "BINARY LARGE OBJECT '%s'", s); + mnstr_printf(datf, "BINARY LARGE OBJECT '%s'", s); else if (strcmp(tp, "inet") == 0 || strcmp(tp, "json") == 0 || strcmp(tp, "url") == 0 || strcmp(tp, "uuid") == 0 || string[i]) - squoted_print(sqlf, s, '\'', false); + squoted_print(datf, s, '\'', false); else - mnstr_printf(sqlf, "%s", s); + mnstr_printf(datf, "%s", s); } else if (string[i]) { /* write double-quoted string with certain characters escaped */ - squoted_print(sqlf, s, '"', noescape); + squoted_print(datf, s, '"', noescape); } else if (strcmp(tp, "blob") == 0) { /* inside blobs, special characters don't occur */ - mnstr_printf(sqlf, "\"%s\"", s); + mnstr_printf(datf, "\"%s\"", s); } else - mnstr_printf(sqlf, "%s", s); + mnstr_printf(datf, "%s", s); if (useInserts) { if (i < cnt - 1) - mnstr_printf(sqlf, ", "); + mnstr_printf(datf, ", "); else - mnstr_printf(sqlf, ");\n"); + mnstr_printf(datf, ");\n"); } else { if (i < cnt - 1) - mnstr_write(sqlf, "\t", 1, 1); + mnstr_write(datf, "\t", 1, 1); else - mnstr_write(sqlf, "\n", 1, 1); + mnstr_write(datf, "\n", 1, 1); } } - if (mnstr_errnr(sqlf) != MNSTR_NO__ERROR) + if (mnstr_errnr(datf) != MNSTR_NO__ERROR) goto bailout; } if (mapi_error(mid)) @@ -1899,6 +1926,8 @@ dump_table_data(Mapi mid, const char *sc free(string); doreturn: + if (datf != sqlf) + close_stream(datf); if (hdl) mapi_close_handle(hdl); if (query != NULL) @@ -1906,6 +1935,8 @@ dump_table_data(Mapi mid, const char *sc return 0; bailout: + if (datf != NULL && datf != sqlf) + close_stream(datf); if (hdl) { if (mapi_result_error(hdl)) mapi_explain_result(hdl, stderr); @@ -2085,6 +2116,7 @@ dump_table_defaults(Mapi mid, const char int dump_table(Mapi mid, const char *schema, const char *tname, stream *sqlf, + const char *ddir, const char *ext, bool describe, bool foreign, bool useInserts, bool databaseDump, bool noescape, bool percent) { @@ -2180,6 +2212,7 @@ dump_table(Mapi mid, const char *schema, mapi_close_handle(hdl); for (int64_t i = 0; i < rows; i++) { rc = dump_table(mid, tables[i].schema, tables[i].table, sqlf, + ddir, ext, describe, foreign, useInserts, databaseDump, noescape, false); if (rc != 0) @@ -2198,7 +2231,7 @@ dump_table(Mapi mid, const char *schema, if (rc == 0) rc = dump_table_storage(mid, schema, tname, sqlf); if (rc == 0 && !describe) - rc = dump_table_data(mid, schema, tname, sqlf, useInserts, noescape); + rc = dump_table_data(mid, schema, tname, sqlf, ddir, ext, useInserts, noescape); if (rc == 0) rc = dump_table_access(mid, schema, tname, sqlf); if (rc == 0 && !databaseDump) @@ -2622,7 +2655,7 @@ bailout: } int -dump_database(Mapi mid, stream *sqlf, bool describe, bool useInserts, bool noescape) +dump_database(Mapi mid, stream *sqlf, const char *ddir, const char *ext, bool describe, bool useInserts, bool noescape) { const char *start_trx = "START TRANSACTION"; const char *end = "ROLLBACK"; @@ -3162,7 +3195,7 @@ dump_database(Mapi mid, stream *sqlf, bo } } int ptype = atoi(type), dont_describe = (ptype == 3 || ptype == 5); - rc = dump_table(mid, schema, name, sqlf, dont_describe || describe, describe, useInserts, true, noescape, false); + rc = dump_table(mid, schema, name, sqlf, ddir, ext, dont_describe || describe, describe, useInserts, true, noescape, false); free(id); free(schema); free(name); diff --git a/clients/mapiclient/mclient.c b/clients/mapiclient/mclient.c --- a/clients/mapiclient/mclient.c +++ b/clients/mapiclient/mclient.c @@ -2641,7 +2641,7 @@ doFile(Mapi mid, stream *fp, bool useins start_pager(&saveFD); #endif if (x & (MD_TABLE | MD_VIEW)) - dump_table(mid, NULL, line, toConsole, true, true, false, false, false, false); + dump_table(mid, NULL, line, toConsole, NULL, NULL, true, true, false, false, false, false); if (x & MD_SEQ) describe_sequence(mid, NULL, line, toConsole); if (x & MD_FUNC) @@ -2799,10 +2799,10 @@ doFile(Mapi mid, stream *fp, bool useins #endif if (*line) { mnstr_printf(toConsole, "START TRANSACTION;\n"); - dump_table(mid, NULL, line, toConsole, false, true, useinserts, false, false, false); + dump_table(mid, NULL, line, toConsole, NULL, NULL, false, true, useinserts, false, false, false); mnstr_printf(toConsole, "COMMIT;\n"); } else - dump_database(mid, toConsole, false, useinserts, false); + dump_database(mid, toConsole, NULL, NULL, false, useinserts, false); #ifdef HAVE_POPEN end_pager(saveFD); #endif @@ -3733,7 +3733,7 @@ main(int argc, char **argv) } if (dump) { if (mode == SQL) { - exit(dump_database(mid, toConsole, false, useinserts, false)); + exit(dump_database(mid, toConsole, NULL, NULL, false, useinserts, false)); } else { mnstr_printf(stderr_stream, "Dump only supported for SQL\n"); exit(1); diff --git a/clients/mapiclient/msqldump.1 b/clients/mapiclient/msqldump.1 --- a/clients/mapiclient/msqldump.1 +++ b/clients/mapiclient/msqldump.1 @@ -110,6 +110,46 @@ or contains percent characters, all tables matching the (SQL) search pattern are dumped. .TP +\fB\-\-output=\fP\fIfilename\fP (\fB\-o\fP \fIfilename\fP) +Write the dump to the specified file. +If +.I filename +contains a recognized compression scheme suffix, the file will be +compressed using that scheme. +.TP +\fB\-\-outputdir=\fP\fIdirectory\fP (\fB\-O\fP \fIdirectory\fP) +Write the dump to the file +.B dump.sql +in the specified directory which is created if it does not exist. +The data of the tables will be stored in separate CSV files in the +directory. +If the +.B \-\-compression +option is used, the CSV files will be compressed using the specified +compression scheme. +If both the +.B \-\-outputdir +option and the +.B \-\-output +option are specified, the last one is used. +The +.B \-\-outputdir +option is not compatible with the +\B \-\-inserts +option. +.TP +\fB\-\-compression=\fP\fIextension\fP (\fB\-x\fP \fIextension\fP) +Compress the CSV files produced with the +.B \-\-outputdir +option using the specified compression scheme. +Depending on how the program is compiled, the following compression _______________________________________________ checkin-list mailing list -- [email protected] To unsubscribe send an email to [email protected]
