Changeset: 3375a722bb1e for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=3375a722bb1e
Modified Files:
clients/ChangeLog
clients/src/mapiclient/dump.c
clients/src/mapiclient/mclient.1
clients/src/mapiclient/mclient.c
clients/src/mapiclient/msqldump.1
clients/src/mapiclient/msqldump.c
clients/src/mapiclient/msqldump.h
Branch: default
Log Message:
implement bug #2727: allow using INSERT INTO statements in dump
INSERT INTO statements are more portable, and a necessity when using
loaders that have to adhere to a query & answer protocol, such as most
data source abstractions (most well known and standardised ones: ODBC
and JDBC).
diffs (truncated from 380 to 300 lines):
diff -r 0b23355e3a17 -r 3375a722bb1e clients/ChangeLog
--- a/clients/ChangeLog Fri Dec 10 12:46:17 2010 +0100
+++ b/clients/ChangeLog Fri Dec 10 13:52:46 2010 +0100
@@ -1,6 +1,11 @@
# ChangeLog file for clients
# This file is updated with Maddlog
+* Fri Dec 10 2010 Fabian Groffen <fab...@cwi.nl>
+- Allow to dump table data using INSERT INTO statements, rather than COPY
+ INTO + CSV data using the -N/--inserts flag of mclient and msqldump.
+ Bug #2727
+
* Wed Dec 8 2010 Fabian Groffen <fab...@cwi.nl>
- Added support for \dn to list schemas or describe a specific one
diff -r 0b23355e3a17 -r 3375a722bb1e clients/src/mapiclient/dump.c
--- a/clients/src/mapiclient/dump.c Fri Dec 10 12:46:17 2010 +0100
+++ b/clients/src/mapiclient/dump.c Fri Dec 10 13:52:46 2010 +0100
@@ -34,14 +34,23 @@
#endif
static void
-quoted_print(stream *f, const char *s)
+quoted_print(stream *f, const char *s, const char singleq)
{
- mnstr_write(f, "\"", 1, 1);
+ mnstr_write(f, singleq ? "'" : "\"", 1, 1);
while (*s) {
switch (*s) {
case '\\':
+ mnstr_write(f, "\\", 1, 1);
+ mnstr_write(f, s, 1, 1);
+ break;
case '"':
- mnstr_write(f, "\\", 1, 1);
+ if (!singleq)
+ mnstr_write(f, "\\", 1, 1);
+ mnstr_write(f, s, 1, 1);
+ break;
+ case '\'':
+ if (singleq)
+ mnstr_write(f, "\\", 1, 1);
mnstr_write(f, s, 1, 1);
break;
case '\n':
@@ -59,7 +68,7 @@
}
s++;
}
- mnstr_write(f, "\"", 1, 1);
+ mnstr_write(f, singleq ? "'" : "\"", 1, 1);
}
static char *actions[] = {
@@ -1004,7 +1013,8 @@
}
int
-dump_table_data(Mapi mid, char *schema, char *tname, stream *toConsole)
+dump_table_data(Mapi mid, char *schema, char *tname, stream *toConsole,
+ const char useInserts)
{
int cnt, i;
MapiHdl hdl = NULL;
@@ -1042,9 +1052,9 @@
goto bailout;
if (mapi_rows_affected(hdl) != 1) {
if (mapi_rows_affected(hdl) == 0)
- fprintf(stderr, "Table %s.%s does not exist.\n",
schema, tname);
+ fprintf(stderr, "table '%s.%s' does not exist\n",
schema, tname);
else
- fprintf(stderr, "Table %s.%s not unique.\n", schema,
tname);
+ fprintf(stderr, "table '%s.%s' is not unique\n",
schema, tname);
goto bailout;
}
while ((mapi_fetch_row(hdl)) != 0) {
@@ -1058,25 +1068,27 @@
mapi_close_handle(hdl);
hdl = NULL;
- snprintf(query, maxquerylen, "SELECT count(*) FROM \"%s\".\"%s\"",
- schema, tname);
- if ((hdl = mapi_query(mid, query)) == NULL || mapi_error(mid))
- goto bailout;
- if (mapi_fetch_row(hdl)) {
- char *cntfld = mapi_fetch_field(hdl, 0);
+ if (!useInserts) {
+ snprintf(query, maxquerylen, "SELECT count(*) FROM
\"%s\".\"%s\"",
+ schema, tname);
+ if ((hdl = mapi_query(mid, query)) == NULL || mapi_error(mid))
+ goto bailout;
+ if (mapi_fetch_row(hdl)) {
+ char *cntfld = mapi_fetch_field(hdl, 0);
- if (strcmp(cntfld, "0") == 0) {
- /* no records to dump, so return early */
- goto doreturn;
+ if (strcmp(cntfld, "0") == 0) {
+ /* no records to dump, so return early */
+ goto doreturn;
+ }
+
+ mnstr_printf(toConsole,
+ "COPY %s RECORDS INTO \"%s\".\"%s\" "
+ "FROM stdin USING DELIMITERS
'\\t','\\n','\"';\n",
+ cntfld, schema, tname);
}
-
- mnstr_printf(toConsole,
- "COPY %s RECORDS INTO \"%s\".\"%s\" "
- "FROM stdin USING DELIMITERS '\\t','\\n','\"';\n",
- cntfld, schema, tname);
+ mapi_close_handle(hdl);
+ hdl = NULL;
}
- mapi_close_handle(hdl);
- hdl = NULL;
snprintf(query, maxquerylen, "SELECT * FROM \"%s\".\"%s\"",
schema, tname);
@@ -1096,20 +1108,32 @@
while (mapi_fetch_row(hdl)) {
char *s;
+ if (useInserts)
+ mnstr_printf(toConsole, "INSERT INTO \"%s\".\"%s\"
VALUES (",
+ schema, tname);
+
for (i = 0; i < cnt; i++) {
s = mapi_fetch_field(hdl, i);
if (s == NULL)
mnstr_printf(toConsole, "NULL");
else if (string[i]) {
- /* write double-quoted string with
+ /* write double or single-quoted string with
certain characters escaped */
- quoted_print(toConsole, s);
+ quoted_print(toConsole, s, useInserts);
} else
mnstr_printf(toConsole, "%s", s);
- if (i < cnt - 1)
- mnstr_write(toConsole, "\t", 1, 1);
- else
- mnstr_write(toConsole, "\n", 1, 1);
+
+ if (useInserts) {
+ if (i < cnt - 1)
+ mnstr_printf(toConsole, ", ");
+ else
+ mnstr_printf(toConsole, ");\n");
+ } else {
+ if (i < cnt - 1)
+ mnstr_write(toConsole, "\t", 1, 1);
+ else
+ mnstr_write(toConsole, "\n", 1, 1);
+ }
}
if (mnstr_errnr(toConsole))
goto bailout;
@@ -1144,13 +1168,13 @@
}
int
-dump_table(Mapi mid, char *schema, char *tname, stream *toConsole, int
describe, int foreign)
+dump_table(Mapi mid, char *schema, char *tname, stream *toConsole, int
describe, int foreign, const char useInserts)
{
int rc;
rc = describe_table(mid, schema, tname, toConsole, foreign);
if (rc == 0 && !describe)
- rc = dump_table_data(mid, schema, tname, toConsole);
+ rc = dump_table_data(mid, schema, tname, toConsole, useInserts);
return rc;
}
@@ -1364,7 +1388,7 @@
}
int
-dump_database(Mapi mid, stream *toConsole, int describe)
+dump_database(Mapi mid, stream *toConsole, int describe, const char useInserts)
{
const char *start = "START TRANSACTION";
const char *end = "ROLLBACK";
@@ -1715,7 +1739,7 @@
if (schema)
schema = strdup(schema);
tname = strdup(tname);
- rc = dump_table(mid, schema, tname, toConsole,
describe, describe);
+ rc = dump_table(mid, schema, tname, toConsole,
describe, describe, useInserts);
if (schema)
free(schema);
free(tname);
diff -r 0b23355e3a17 -r 3375a722bb1e clients/src/mapiclient/mclient.1
--- a/clients/src/mapiclient/mclient.1 Fri Dec 10 12:46:17 2010 +0100
+++ b/clients/src/mapiclient/mclient.1 Fri Dec 10 13:52:46 2010 +0100
@@ -218,6 +218,12 @@
.TP
\fB\-\-dump\fP (\fB\-D\fP)
Create an SQL dump.
+.TP
+\fB\-\-inserts\fP (\fB\-N\fP)
+Use INSERT INTO statements instead of COPY INTO + CSV values when
+dumping the data of a table. This option can be used when trying to
+load data from MonetDB into another database, or when e.g. JDBC
+applications are used to reload the dump.
.SS
XQuery Options
.TP
diff -r 0b23355e3a17 -r 3375a722bb1e clients/src/mapiclient/mclient.c
--- a/clients/src/mapiclient/mclient.c Fri Dec 10 12:46:17 2010 +0100
+++ b/clients/src/mapiclient/mclient.c Fri Dec 10 13:52:46 2010 +0100
@@ -1769,7 +1769,7 @@
#define MD_SCHEMA 16
static int
-doFileByLines(Mapi mid, FILE *fp, const char *prompt)
+doFileByLines(Mapi mid, FILE *fp, const char *prompt, const char useinserts)
{
char *line = NULL;
char *oldbuf = NULL, *buf = NULL;
@@ -2202,10 +2202,10 @@
#endif
if (*line) {
mnstr_printf(toConsole, "START
TRANSACTION;\n");
- dump_table(mid, NULL, line,
toConsole, 0, 1);
+ dump_table(mid, NULL, line,
toConsole, 0, 1, useinserts);
mnstr_printf(toConsole,
"COMMIT;\n");
} else
- dump_database(mid, toConsole,
0);
+ dump_database(mid, toConsole,
0, useinserts);
#ifdef HAVE_POPEN
end_pager(saveFD, saveFD_raw);
#endif
@@ -2448,6 +2448,7 @@
fprintf(stderr, " -r nr | --rows=nr for pagination\n");
fprintf(stderr, " -w nr | --width=nr for pagination\n");
fprintf(stderr, " -D | --dump create an SQL
dump\n");
+ fprintf(stderr, " -N | --inserts use INSERT INTO
statements when dumping\n");
fprintf(stderr, "\nXQuery specific options\n");
fprintf(stderr, " -C colname | --collection=colname collection
name\n");
@@ -2473,6 +2474,7 @@
char *colname = NULL;
int trace = 0;
int dump = 0;
+ int useinserts = 0;
int algebra = -1;
int c = 0;
Mapi mid;
@@ -2489,6 +2491,7 @@
{"collection", 1, 0, 'C'},
{"database", 1, 0, 'd'},
{"dump", 0, 0, 'D'},
+ {"inserts", 0, 0, 'N'},
{"echo", 0, 0, 'e'},
#ifdef HAVE_ICONV
{"encoding", 1, 0, 'E'},
@@ -2632,7 +2635,7 @@
mnstr_destroy(config);
}
- while ((c = getopt_long(argc, argv, "C:Dd:e"
+ while ((c = getopt_long(argc, argv, "C:DNd:e"
#ifdef HAVE_ICONV
"E:"
#endif
@@ -2728,6 +2731,9 @@
case 'D':
dump = 1;
break;
+ case 'N':
+ useinserts = 1;
+ break;
case 'd':
dbname = optarg;
break;
@@ -2836,7 +2842,7 @@
mapi_cache_limit(mid, -1);
if (dump) {
if (mode == SQL) {
- exit(dump_database(mid, toConsole, 0));
+ exit(dump_database(mid, toConsole, 0, useinserts));
} else {
fprintf(stderr, "Dump only supported for SQL\n");
exit(1);
@@ -2948,7 +2954,7 @@
} else {
/* note that since fp != stdin,
we don't treat \ special */
- c |= doFileByLines(mid, fp, NULL);
+ c |= doFileByLines(mid, fp, NULL,
useinserts);
fclose(fp);
}
} else
@@ -2972,7 +2978,7 @@
fromConsole = stdin;
}
/* use default rendering if not overruled at commandline */
- c = doFileByLines(mid, stdin, prompt);
_______________________________________________
Checkin-list mailing list
Checkin-list@monetdb.org
http://mail.monetdb.org/mailman/listinfo/checkin-list
