.DUMP feature request
(1) By anonymous on 2020-10-12 14:15:51 [link] [source]
Can you please add an option to only dump the data part without the schema?
This will be complete in that
.schema
gives just the schema
.dump
gives everything
.dump --data
gives just the data (.data
is already taken for .databases
)
The only work-around I know of is .mode insert table_name
followed by a corresponding select
but it's tedious as each table needs to be done separately.
(2) By Domingo (mingodad) on 2020-10-12 15:26:40 in reply to 1 [source]
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 !