Hello again !
After sending the first patch I also realized that when sqlite3 dumps
".schema" or ".fullschema" it doesn't surround the dump with a transaction
and that takes longer and makes the hard disk work hard. So I also surrounded
".schema" and ".fullschema" with a transaction with this extended patch.
Again the same license of sqlite apply to this patch.
=============patch to shell.c
--- shell.c
+++ shell.c
@@ -550,10 +550,12 @@
?? sqlite3_stmt *pStmt;?? /* Current statement if any */
?? FILE *pLog;??????????? /* Write log output here */
?? 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;" */
?};
?
?/*
?** These are the allowed shellFlgs values
?*/
@@ -908,10 +910,11 @@
???????? }
?????? }
?????? 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++){
?????????? fprintf(p->out,"%s%s",azCol[i],
?????????????????? i==nArg-1 ? p->rowSeparator :
p->colSeparator);
@@ -1658,31 +1661,33 @@
?? if( nArg!=3 ) return 1;
?? zTable = azArg[0];
?? zType = azArg[1];
?? zSql = azArg[2];
??
-? if( strcmp(zTable, "sqlite_sequence")==0 ){
-??? zPrepStmt = "DELETE FROM sqlite_sequence;\n";
-? }else if( sqlite3_strglob("sqlite_stat?", zTable)==0 ){
-??? fprintf(p->out, "ANALYZE sqlite_master;\n");
-? }else if( strncmp(zTable, "sqlite_", 7)==0 ){
-??? return 0;
-? }else if( strncmp(zSql, "CREATE VIRTUAL TABLE", 20)==0 ){
-??? char *zIns;
-??? if( !p->writableSchema ){
-????? fprintf(p->out, "PRAGMA writable_schema=ON;\n");
-????? p->writableSchema = 1;
-??? }
-??? zIns = sqlite3_mprintf(
-?????? "INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"
-?????? "VALUES('table','%q','%q',0,'%q');",
-?????? zTable, zTable, zSql);
-??? fprintf(p->out, "%s\n", zIns);
-??? sqlite3_free(zIns);
-??? return 0;
-? }else{
-??? fprintf(p->out, "%s;\n", zSql);
+? if( !p->dumpDataOnly ){
+??? if( strcmp(zTable, "sqlite_sequence")==0 ){
+????? zPrepStmt = "DELETE FROM sqlite_sequence;\n";
+??? }else if( sqlite3_strglob("sqlite_stat?", zTable)==0 ){
+????? fprintf(p->out, "ANALYZE sqlite_master;\n");
+??? }else if( strncmp(zTable, "sqlite_", 7)==0 ){
+????? return 0;
+??? }else if( strncmp(zSql, "CREATE VIRTUAL TABLE", 20)==0 ){
+????? char *zIns;
+????? if( !p->writableSchema ){
+??????? fprintf(p->out, "PRAGMA writable_schema=ON;\n");
+??????? p->writableSchema = 1;
+????? }
+????? zIns = sqlite3_mprintf(
+???????? "INSERT INTO
sqlite_master(type,name,tbl_name,rootpage,sql)"
+???????? "VALUES('table','%q','%q',0,'%q');",
+???????? zTable, zTable, zSql);
+????? fprintf(p->out, "%s\n", zIns);
+????? sqlite3_free(zIns);
+????? return 0;
+??? }else{
+????? fprintf(p->out, "%s;\n", zSql);
+??? }
?? }
?
?? if( strcmp(zType, "table")==0 ){
???? sqlite3_stmt *pTableInfo = 0;
???? char *zSelect = 0;
@@ -1789,10 +1794,11 @@
?? ".databases???????????? List names and files of attached
databases\n"
?? ".dbinfo ?DB??????????? Show status information about the
database\n"
?? ".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"
?? ".explain ?on|off?????? Turn output mode suitable for EXPLAIN on or
off.\n"
?? "???????????????????????? With no args, it turns
EXPLAIN on.\n"
@@ -2770,11 +2776,12 @@
?
?? if( c=='d' && strncmp(azArg[0], "dbinfo", n)==0 ){
???? rc = shell_dbinfo_command(p, nArg, azArg);
?? }else
?
-? if( c=='d' && strncmp(azArg[0], "dump", n)==0 ){
+? if( c=='d' && ((strncmp(azArg[0], "dump", n)==0) ||
+????? (p->dumpDataOnly = (strncmp(azArg[0], "dumpdata", 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.
???? ** So disable foreign-key constraint enforcement to prevent
problems. */
???? if( nArg!=1 && nArg!=2 ){
@@ -2790,32 +2797,36 @@
???? if( nArg==1 ){
?????? run_schema_dump_query(p,
???????? "SELECT name, type, sql FROM sqlite_master "
???????? "WHERE sql NOT NULL AND type=='table' AND
name!='sqlite_sequence'"
?????? );
-????? 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
-????? );
+????? 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
+??????? );
+????? }
???? }else{
?????? int i;
?????? for(i=1; i<nArg; i++){
???????? zShellStatic = azArg[i];
???????? run_schema_dump_query(p,
?????????? "SELECT name, type, sql FROM sqlite_master "
?????????? "WHERE tbl_name LIKE shellstatic() AND type=='table'"
?????????? "? AND sql NOT NULL");
-??????? 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
-??????? );
+??????? 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
+????????? );
+??????? }
???????? zShellStatic = 0;
?????? }
???? }
???? if( p->writableSchema ){
?????? fprintf(p->out, "PRAGMA writable_schema=OFF;\n");
@@ -2822,10 +2833,11 @@
?????? p->writableSchema = 0;
???? }
???? sqlite3_exec(p->db, "PRAGMA writable_schema=OFF;", 0, 0, 0);
???? sqlite3_exec(p->db, "RELEASE dump;", 0, 0, 0);
???? fprintf(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 ){
???? if( nArg==2 ){
?????? p->echoOn = booleanValue(azArg[1]);
@@ -2895,10 +2907,11 @@
???? }
???? open_db(p, 0);
???? memcpy(&data, p, sizeof(data));
???? data.showHeader = 0;
???? 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"
??????? "???? FROM sqlite_master UNION ALL"
??????? "?? SELECT sql, type, tbl_name, name, rowid FROM
sqlite_temp_master) "
@@ -2931,10 +2944,11 @@
?????? data.zDestTable = "sqlite_stat4";
?????? shell_exec(p->db, "SELECT * FROM sqlite_stat4",
????????????????? shell_callback, &data, &zErrMsg);
?????? fprintf(p->out, "ANALYZE sqlite_master;\n");
???? }
+??? if(data.cnt) fprintf(p->out, "COMMIT;\n");
?? }else
?
?? if( c=='h' && strncmp(azArg[0], "headers", n)==0 ){
???? if( nArg==2 ){
?????? p->showHeader = booleanValue(azArg[1]);
@@ -3525,10 +3539,11 @@
???? char *zErrMsg = 0;
???? open_db(p, 0);
???? memcpy(&data, p, sizeof(data));
???? data.showHeader = 0;
???? 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]);
?????? if( strcmp(azArg[1],"sqlite_master")==0 ){
???????? char *new_argv[2], *new_colv[2];
@@ -3594,10 +3609,11 @@
?????? fprintf(stderr,"Error: querying schema information\n");
?????? rc = 1;
???? }else{
?????? rc = 0;
???? }
+??? if((rc == 0) && data.cnt) fprintf(data.out, "COMMIT;\n");
?? }else
?
?
?#if defined(SQLITE_DEBUG) && defined(SQLITE_ENABLE_SELECTTRACE)
?? if( c=='s' && n==11 && strncmp(azArg[0], "selecttrace", n)==0 ){
======================
Cheers !
