I think I've proposed/mentioned this before, but just in case here is my implementation of ".dumpdata" command.
I release it with the same license as sqlite (meaning do whatever yo want with it, I hope it can be included in sqlite3).
====
Index: src/shell.c.in
==================================================================
--- src/shell.c.in
+++ src/shell.c.in
@@ -1138,10 +1138,12 @@
#if defined(SQLITE_ENABLE_SESSION)
int nSession; /* Number of active sessions */
OpenSession aSession[4]; /* Array of sessions. [0] is in focus. */
#endif
ExpertInfo expert; /* Valid if previous command was ".expert OPT..." */
+ int dumpDataOnly; /*when dump a database exclude schema */
+ int doStartTransaction; /* when dumping schema only before first record output "BEGIN;" */
};
/* Allowed values for ShellState.autoEQP
*/
@@ -2106,10 +2108,11 @@
}
printSchemaLine(p->out, z, ";\n");
sqlite3_free(z);
break;
}
+ 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++){
utf8_printf(p->out,"%s%s",azCol[i],
i==nArg-1 ? p->rowSeparator : p->colSeparator);
@@ -3709,10 +3712,11 @@
if( nArg!=3 || azArg==0 ) return 0;
zTable = azArg[0];
zType = azArg[1];
zSql = azArg[2];
+ if( !p->dumpDataOnly ){
if( strcmp(zTable, "sqlite_sequence")==0 ){
raw_printf(p->out, "DELETE FROM sqlite_sequence;\n");
}else if( sqlite3_strglob("sqlite_stat?", zTable)==0 ){
raw_printf(p->out, "ANALYZE sqlite_schema;\n");
}else if( strncmp(zTable, "sqlite_", 7)==0 ){
@@ -3730,10 +3734,11 @@
utf8_printf(p->out, "%s\n", zIns);
sqlite3_free(zIns);
return 0;
}else{
printSchemaLine(p->out, zSql, ";\n");
+ }
}
if( strcmp(zType, "table")==0 ){
ShellText sSelect;
ShellText sTable;
@@ -3890,10 +3895,12 @@
".dump ?TABLE? Render database content as SQL",
" Options:",
" --preserve-rowids Include ROWID values in the output",
" --newlines Allow unescaped newline characters in output",
" TABLE is a LIKE pattern for the tables to dump",
+ ".dumpdata ?TABLE? ... Like .dump without schema\n"
+ ".dumpdata2 ?TABLE? ... Like .dumpdata but without pragmas\n"
" Additional LIKE patterns can be given in subsequent arguments",
".echo on|off Turn command echo on or off",
".eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN",
" Other Modes:",
#ifdef SQLITE_DEBUG
@@ -7684,11 +7691,13 @@
open_db(p, 0);
rc = recoverDatabaseCmd(p, nArg, azArg);
}else
#endif /* !(SQLITE_OMIT_VIRTUALTABLE) && defined(SQLITE_ENABLE_DBPAGE_VTAB) */
- 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)) ||
+ (p->dumpDataOnly = ((strncmp(azArg[0], "dumpdata2", n)==0) ? 2 : 0)))){
char *zLike = 0;
char *zSql;
int i;
int savedShowHeader = p->showHeader;
int savedShellFlags = p->shellFlgs;
@@ -7728,11 +7737,11 @@
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. */
- raw_printf(p->out, "PRAGMA foreign_keys=OFF;\n");
+ if(p->dumpDataOnly != 2) raw_printf(p->out, "PRAGMA foreign_keys=OFF;\n");
raw_printf(p->out, "BEGIN TRANSACTION;\n");
p->writableSchema = 0;
p->showHeader = 0;
/* Set writable_schema=ON since doing so forces SQLite to initialize
** as much of the schema as it can even if the sqlite_schema table is
@@ -7745,19 +7754,21 @@
"WHERE (%s) AND type=='table'"
" AND sql NOT NULL"
" ORDER BY tbl_name='sqlite_sequence', rowid",
zLike
);
+ if(!p->dumpDataOnly){
run_schema_dump_query(p,zSql);
sqlite3_free(zSql);
zSql = sqlite3_mprintf(
"SELECT sql FROM sqlite_schema "
"WHERE (%s) AND sql NOT NULL"
" AND type IN ('index','trigger','view')",
zLike
);
run_table_dump_query(p, zSql);
+ }
sqlite3_free(zSql);
sqlite3_free(zLike);
if( p->writableSchema ){
raw_printf(p->out, "PRAGMA writable_schema=OFF;\n");
p->writableSchema = 0;
@@ -7765,10 +7776,11 @@
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->showHeader = savedShowHeader;
p->shellFlgs = savedShellFlags;
+ p->dumpDataOnly = 0; /* reset data only flag */
}else
if( c=='e' && strncmp(azArg[0], "echo", n)==0 ){
if( nArg==2 ){
setOrClearFlag(p, SHFLG_Echo, azArg[1]);
@@ -7995,10 +8007,11 @@
char *zErrMsg = 0;
int doStats = 0;
memcpy(&data, p, sizeof(data));
data.showHeader = 0;
data.cMode = data.mode = MODE_Semi;
+ data.doStartTransaction = 1;
if( nArg==2 && optionMatch(azArg[1], "indent") ){
data.cMode = data.mode = MODE_Pretty;
nArg = 1;
}
if( nArg!=1 ){
@@ -9094,10 +9107,11 @@
open_db(p, 0);
memcpy(&data, p, sizeof(data));
data.showHeader = 0;
data.cMode = data.mode = MODE_Semi;
+ data.doStartTransaction = 1;
initText(&sSelect);
for(ii=1; ii<nArg; ii++){
if( optionMatch(azArg[ii],"indent") ){
data.cMode = data.mode = MODE_Pretty;
}else if( optionMatch(azArg[ii],"debug") ){
====
Cheers !