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 !