Hello !
I had this problem before and asked to add this option to sqlite but somehow
it was not added so now I'm submitting here a patch that adds this
functionality to shell.c updated to today repository.
Please consider add this to sqlite !
Cheers !
?
--- /third-party/sqlite3/src/shell2.c
+++ /third-party/sqlite3/src/shell0.c
@@ -625,8 +625,6 @@
?? int *aiIndent;???????? /* Array of indents used in MODE_Explain
*/
?? int nIndent;?????????? /* Size of array aiIndent[] */
?? int iIndent;?????????? /* Index of current op in aiIndent[] */
-? int dumpDataOnly; /*when dump a database exclude schema */
-? int doStartTransaction; /* when dumping schema only before first record
output "BEGIN;" */
?};
?
?/*
@@ -989,7 +987,6 @@
?????? break;
???? }
???? case MODE_Semi:
-????? if((p->cnt == 0) && p->doStartTransaction ) fprintf(p->out,"BEGIN
TRANSACTION;\n");
???? case MODE_List: {
?????? if( p->cnt++==0 && p->showHeader ){
???????? for(i=0; i<nArg; i++){
@@ -1822,7 +1819,6 @@
?? zType = azArg[1];
?? zSql = azArg[2];
??
-? if( !p->dumpDataOnly ){
?? if( strcmp(zTable, "sqlite_sequence")==0 ){
???? zPrepStmt = "DELETE FROM sqlite_sequence;\n";
?? }else if( sqlite3_strglob("sqlite_stat?", zTable)==0 ){
@@ -1844,7 +1840,6 @@
???? return 0;
?? }else{
???? utf8_printf(p->out, "%s;\n", zSql);
-? }
?? }
?
?? if( strcmp(zType, "table")==0 ){
@@ -1956,7 +1951,6 @@
?? ".dump ?TABLE? ...????? Dump the database in an SQL text format\n"
?? "???????????????????????? If TABLE specified,
only dump tables matching\n"
?? "???????????????????????? LIKE pattern TABLE.\n"
-? ".dumpdata? ?TABLE? ...???? Like .dump without schema\n"
?? ".echo on|off?????????? Turn command echo on or off\n"
?? ".eqp on|off??????????? Enable or disable automatic EXPLAIN
QUERY PLAN\n"
?? ".exit????????????????? Exit this program\n"
@@ -2964,8 +2958,7 @@
???? rc = shell_dbinfo_command(p, nArg, azArg);
?? }else
?
-? if( c=='d' && ((strncmp(azArg[0], "dump", n)==0) ||
-????? (p->dumpDataOnly = (strncmp(azArg[0], "dumpdata", n)==0))) ){
+? if( c=='d' && strncmp(azArg[0], "dump", n)==0 ){
???? open_db(p, 0);
???? /* When playing back a "dump", the content might appear in an order
???? ** which causes immediate foreign key constraints to be violated.
@@ -2985,16 +2978,14 @@
???????? "SELECT name, type, sql FROM sqlite_master "
???????? "WHERE sql NOT NULL AND type=='table' AND
name!='sqlite_sequence'"
?????? );
-????? if(!p->dumpDataOnly){
-??????? run_schema_dump_query(p,
-????????? "SELECT name, type, sql FROM sqlite_master "
-????????? "WHERE name=='sqlite_sequence'"
-??????? );
-??????? run_table_dump_query(p,
-????????? "SELECT sql FROM sqlite_master "
-????????? "WHERE sql NOT NULL AND type IN
('index','trigger','view')", 0
-??????? );
-????? }
+????? run_schema_dump_query(p,
+??????? "SELECT name, type, sql FROM sqlite_master "
+??????? "WHERE name=='sqlite_sequence'"
+????? );
+????? run_table_dump_query(p,
+??????? "SELECT sql FROM sqlite_master "
+??????? "WHERE sql NOT NULL AND type IN ('index','trigger','view')",
0
+????? );
???? }else{
?????? int i;
?????? for(i=1; i<nArg; i++){
@@ -3003,14 +2994,12 @@
?????????? "SELECT name, type, sql FROM sqlite_master "
?????????? "WHERE tbl_name LIKE shellstatic() AND type=='table'"
?????????? "? AND sql NOT NULL");
-??????? if(!p->dumpDataOnly){
-????????? run_table_dump_query(p,
-??????????? "SELECT sql FROM sqlite_master "
-??????????? "WHERE sql NOT NULL"
-??????????? "? AND type IN ('index','trigger','view')"
-??????????? "? AND tbl_name LIKE shellstatic()", 0
-????????? );
-??????? }
+??????? run_table_dump_query(p,
+????????? "SELECT sql FROM sqlite_master "
+????????? "WHERE sql NOT NULL"
+????????? "? AND type IN ('index','trigger','view')"
+????????? "? AND tbl_name LIKE shellstatic()", 0
+??????? );
???????? zShellStatic = 0;
?????? }
???? }
@@ -3021,7 +3010,6 @@
???? sqlite3_exec(p->db, "PRAGMA writable_schema=OFF;", 0, 0, 0);
???? sqlite3_exec(p->db, "RELEASE dump;", 0, 0, 0);
???? raw_printf(p->out, p->nErr ? "ROLLBACK; -- due to errors\n" :
"COMMIT;\n");
-??? p->dumpDataOnly = 0; /* reset data only flag */
?? }else
?
?? if( c=='e' && strncmp(azArg[0], "echo", n)==0 ){
@@ -3082,7 +3070,6 @@
???? memcpy(&data, p, sizeof(data));
???? data.showHeader = 0;
???? data.cMode = data.mode = MODE_Semi;
-??? data.doStartTransaction = 1;
???? rc = sqlite3_exec(p->db,
??????? "SELECT sql FROM"
??????? "? (SELECT sql sql, type type, tbl_name tbl_name, name name,
rowid x"
@@ -3119,7 +3106,6 @@
????????????????? shell_callback, &data, &zErrMsg);
?????? raw_printf(p->out, "ANALYZE sqlite_master;\n");
???? }
-??? if(data.cnt) fprintf(p->out, "COMMIT;\n");
?? }else
?
?? if( c=='h' && strncmp(azArg[0], "headers", n)==0 ){
@@ -3717,7 +3703,6 @@
???? memcpy(&data, p, sizeof(data));
???? data.showHeader = 0;
???? data.cMode = data.mode = MODE_Semi;
-??? data.doStartTransaction = 1;
???? if( nArg==2 ){
?????? int i;
?????? for(i=0; azArg[1][i]; i++) azArg[1][i] = ToLower(azArg[1][i]);
@@ -3787,7 +3772,6 @@
???? }else{
?????? rc = 0;
???? }
-??? if((rc == 0) && data.cnt) fprintf(data.out, "COMMIT;\n");
?? }else
?
?
> Wed Mar 23 2016 10:50:09 AM CET from "Luca Ferrari"
><fluca1978 at infinito.it> Subject: [sqlite] dump only data, change schema,
>reload
>
> Hi all,
> I've a few hundreds sqlite3 database files, all almost equals except
> for some constraint that has changed during time.
> As I know, there is no way to alter constraint (e.g., unique indexes),
> and therefore I have to migrate data to a new schema version.
> That is possible because data will fit into the same table schema, but
> I'm looking for a way to do it massively (and remotely).
> So far I was thinking to wrap a shell script to dump the content of a
> single database, create the new schema and reload the dump in it.
> The problem is that .dump provides data and schema, while I'd like to
> have data only.
> Other commands like .clone and .backup works pretty much the same, as
> far as I understand.
>
> This leads me to either use awk/sed to manipulate the dump or to
> hard-code single select statements into the script to extract data.
> Is there any smarter way to dump only data in a loadable form?
>
> Thanks
> Luca
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
?
