Patch to add ".dumpdata" to sqlite3 command line program

shell.c.dumpdata.diff

--- 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 ){

This patch add a new command to sqlite3 command line program ".dumpdata" that on dump data from a database, it also wrap the ".schema" and ".fullschema" with a trancation like it does for the ".dump" command.