ext/misc/dbdump.c

   1 /*
   2 ** 2016-03-13
   3 **
   4 ** The author disclaims copyright to this source code.  In place of
   5 ** a legal notice, here is a blessing:
   6 **
   7 **    May you do good and not evil.
   8 **    May you find forgiveness for yourself and forgive others.
   9 **    May you share freely, never taking more than you give.
  10 **
  11 ******************************************************************************
  12 **
  13 ** This file implements a C-language subroutine that converts the content
  14 ** of an SQLite database into UTF-8 text SQL statements that can be used
  15 ** to exactly recreate the original database.  ROWID values are preserved.
  16 **
  17 ** A prototype of the implemented subroutine is this:
  18 **
  19 **   int sqlite3_db_dump(
  20 **          sqlite3 *db,
  21 **          const char *zSchema,
  22 **          const char *zTable,
  23 **          void (*xCallback)(void*, const char*),
  24 **          void *pArg
  25 **   );
  26 **
  27 ** The db parameter is the database connection.  zSchema is the schema within
  28 ** that database which is to be dumped.  Usually the zSchema is "main" but
  29 ** can also be "temp" or any ATTACH-ed database.  If zTable is not NULL, then
  30 ** only the content of that one table is dumped.  If zTable is NULL, then all
  31 ** tables are dumped.
  32 **
  33 ** The generate text is passed to xCallback() in multiple calls.  The second
  34 ** argument to xCallback() is a copy of the pArg parameter.  The first
  35 ** argument is some of the output text that this routine generates.  The
  36 ** signature to xCallback() is designed to make it compatible with fputs().
  37 **
  38 ** The sqlite3_db_dump() subroutine returns SQLITE_OK on success or some error
  39 ** code if it encounters a problem.
  40 **
  41 ** If this file is compiled with -DDBDUMP_STANDALONE then a "main()" routine
  42 ** is included so that this routine becomes a command-line utility.  The
  43 ** command-line utility takes two or three arguments which are the name
  44 ** of the database file, the schema, and optionally the table, forming the
  45 ** first three arguments of a single call to the library routine.
  46 */
  47 #include "sqlite3.h"
  48 #include <stdarg.h>
  49 #include <string.h>
  50 #include <ctype.h>
  51
  52 /*
  53 ** The state of the dump process.
  54 */
  55 typedef struct DState DState;
  56 struct DState {
  57   sqlite3 *db;                /* The database connection */
  58   int nErr;                   /* Number of errors seen so far */
  59   int rc;                     /* Error code */
  60   int writableSchema;                    /* True if in writable_schema mode */
  61   int (*xCallback)(const char*,void*);   /* Send output here */
  62   void *pArg;                            /* Argument to xCallback() */
  63 };
  64
  65 /*
  66 ** A variable length string to which one can append text.
  67 */
  68 typedef struct DText DText;
  69 struct DText {
  70   char *z;           /* The text */
  71   int n;             /* Number of bytes of content in z[] */
  72   int nAlloc;        /* Number of bytes allocated to z[] */
  73 };
  74
  75 /*
  76 ** Initialize and destroy a DText object
  77 */
  78 static void initText(DText *p){
  79   memset(p, 0, sizeof(*p));
  80 }
  81 static void freeText(DText *p){
  82   sqlite3_free(p->z);
  83   initText(p);
  84 }
  85
  86 /* zIn is either a pointer to a NULL-terminated string in memory obtained
  87 ** from malloc(), or a NULL pointer. The string pointed to by zAppend is
  88 ** added to zIn, and the result returned in memory obtained from malloc().
  89 ** zIn, if it was not NULL, is freed.
  90 **
  91 ** If the third argument, quote, is not '\0', then it is used as a
  92 ** quote character for zAppend.
  93 */
  94 static void appendText(DText *p, char const *zAppend, char quote){
  95   int len;
  96   int i;
  97   int nAppend = (int)(strlen(zAppend) & 0x3fffffff);
  98
  99   len = nAppend+p->n+1;
 100   if( quote ){
 101     len += 2;
 102     for(i=0; i<nAppend; i++){
 103       if( zAppend[i]==quote ) len++;
 104     }
 105   }
 106
 107   if( p->n+len>=p->nAlloc ){
 108     char *zNew;
 109     p->nAlloc = p->nAlloc*2 + len + 20;
 110     zNew = sqlite3_realloc(p->z, p->nAlloc);
 111     if( zNew==0 ){
 112       freeText(p);
 113       return;
 114     }
 115     p->z = zNew;
 116   }
 117
 118   if( quote ){
 119     char *zCsr = p->z+p->n;
 120     *zCsr++ = quote;
 121     for(i=0; i<nAppend; i++){
 122       *zCsr++ = zAppend[i];
 123       if( zAppend[i]==quote ) *zCsr++ = quote;
 124     }
 125     *zCsr++ = quote;
 126     p->n = (int)(zCsr - p->z);
 127     *zCsr = '\0';
 128   }else{
 129     memcpy(p->z+p->n, zAppend, nAppend);
 130     p->n += nAppend;
 131     p->z[p->n] = '\0';
 132   }
 133 }
 134
 135 /*
 136 ** Attempt to determine if identifier zName needs to be quoted, either
 137 ** because it contains non-alphanumeric characters, or because it is an
 138 ** SQLite keyword.  Be conservative in this estimate:  When in doubt assume
 139 ** that quoting is required.
 140 **
 141 ** Return '"' if quoting is required.  Return 0 if no quoting is required.
 142 */
 143 static char quoteChar(const char *zName){
 144   /* All SQLite keywords, in alphabetical order */
 145   static const char *azKeywords[] = {
 146     "ABORT", "ACTION", "ADD", "AFTER", "ALL", "ALTER", "ANALYZE", "AND", "AS",
 147     "ASC", "ATTACH", "AUTOINCREMENT", "BEFORE", "BEGIN", "BETWEEN", "BY",
 148     "CASCADE", "CASE", "CAST", "CHECK", "COLLATE", "COLUMN", "COMMIT",
 149     "CONFLICT", "CONSTRAINT", "CREATE", "CROSS", "CURRENT_DATE",
 150     "CURRENT_TIME", "CURRENT_TIMESTAMP", "DATABASE", "DEFAULT", "DEFERRABLE",
 151     "DEFERRED", "DELETE", "DESC", "DETACH", "DISTINCT", "DROP", "EACH",
 152     "ELSE", "END", "ESCAPE", "EXCEPT", "EXCLUSIVE", "EXISTS", "EXPLAIN",
 153     "FAIL", "FOR", "FOREIGN", "FROM", "FULL", "GLOB", "GROUP", "HAVING", "IF",
 154     "IGNORE", "IMMEDIATE", "IN", "INDEX", "INDEXED", "INITIALLY", "INNER",
 155     "INSERT", "INSTEAD", "INTERSECT", "INTO", "IS", "ISNULL", "JOIN", "KEY",
 156     "LEFT", "LIKE", "LIMIT", "MATCH", "NATURAL", "NO", "NOT", "NOTNULL",
 157     "NULL", "OF", "OFFSET", "ON", "OR", "ORDER", "OUTER", "PLAN", "PRAGMA",
 158     "PRIMARY", "QUERY", "RAISE", "RECURSIVE", "REFERENCES", "REGEXP",
 159     "REINDEX", "RELEASE", "RENAME", "REPLACE", "RESTRICT", "RIGHT",
 160     "ROLLBACK", "ROW", "SAVEPOINT", "SELECT", "SET", "TABLE", "TEMP",
 161     "TEMPORARY", "THEN", "TO", "TRANSACTION", "TRIGGER", "UNION", "UNIQUE",
 162     "UPDATE", "USING", "VACUUM", "VALUES", "VIEW", "VIRTUAL", "WHEN", "WHERE",
 163     "WITH", "WITHOUT",
 164   };
 165   int i, lwr, upr, mid, c;
 166   if( !isalpha((unsigned char)zName[0]) && zName[0]!='_' ) return '"';
 167   for(i=0; zName[i]; i++){
 168     if( !isalnum((unsigned char)zName[i]) && zName[i]!='_' ) return '"';
 169   }
 170   lwr = 0;
 171   upr = sizeof(azKeywords)/sizeof(azKeywords[0]) - 1;
 172   while( lwr<=upr ){
 173     mid = (lwr+upr)/2;
 174     c = sqlite3_stricmp(azKeywords[mid], zName);
 175     if( c==0 ) return '"';
 176     if( c<0 ){
 177       lwr = mid+1;
 178     }else{
 179       upr = mid-1;
 180     }
 181   }
 182   return 0;
 183 }
 184
 185
 186 /*
 187 ** Release memory previously allocated by tableColumnList().
 188 */
 189 static void freeColumnList(char **azCol){
 190   int i;
 191   for(i=1; azCol[i]; i++){
 192     sqlite3_free(azCol[i]);
 193   }
 194   /* azCol[0] is a static string */
 195   sqlite3_free(azCol);
 196 }
 197
 198 /*
 199 ** Return a list of pointers to strings which are the names of all
 200 ** columns in table zTab.   The memory to hold the names is dynamically
 201 ** allocated and must be released by the caller using a subsequent call
 202 ** to freeColumnList().
 203 **
 204 ** The azCol[0] entry is usually NULL.  However, if zTab contains a rowid
 205 ** value that needs to be preserved, then azCol[0] is filled in with the
 206 ** name of the rowid column.
 207 **
 208 ** The first regular column in the table is azCol[1].  The list is terminated
 209 ** by an entry with azCol[i]==0.
 210 */
 211 static char **tableColumnList(DState *p, const char *zTab){
 212   char **azCol = 0;
 213   sqlite3_stmt *pStmt = 0;
 214   char *zSql;
 215   int nCol = 0;
 216   int nAlloc = 0;
 217   int nPK = 0;       /* Number of PRIMARY KEY columns seen */
 218   int isIPK = 0;     /* True if one PRIMARY KEY column of type INTEGER */
 219   int preserveRowid = 1;
 220   int rc;
 221
 222   zSql = sqlite3_mprintf("PRAGMA table_info=%Q", zTab);
 223   if( zSql==0 ) return 0;
 224   rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
 225   sqlite3_free(zSql);
 226   if( rc ) return 0;
 227   while( sqlite3_step(pStmt)==SQLITE_ROW ){
 228     if( nCol>=nAlloc-2 ){
 229       char **azNew;
 230       nAlloc = nAlloc*2 + nCol + 10;
 231       azNew = sqlite3_realloc(azCol, nAlloc*sizeof(azCol[0]));
 232       if( azNew==0 ) goto col_oom;
 233       azCol = azNew;
 234       azCol[0] = 0;
 235     }
 236     azCol[++nCol] = sqlite3_mprintf("%s", sqlite3_column_text(pStmt, 1));
 237     if( azCol[nCol]==0 ) goto col_oom;
 238     if( sqlite3_column_int(pStmt, 5) ){
 239       nPK++;
 240       if( nPK==1
 241        && sqlite3_stricmp((const char*)sqlite3_column_text(pStmt,2),
 242                           "INTEGER")==0
 243       ){
 244         isIPK = 1;
 245       }else{
 246         isIPK = 0;
 247       }
 248     }
 249   }
 250   sqlite3_finalize(pStmt);
 251   pStmt = 0;
 252   azCol[nCol+1] = 0;
 253
 254   /* The decision of whether or not a rowid really needs to be preserved
 255   ** is tricky.  We never need to preserve a rowid for a WITHOUT ROWID table
 256   ** or a table with an INTEGER PRIMARY KEY.  We are unable to preserve
 257   ** rowids on tables where the rowid is inaccessible because there are other
 258   ** columns in the table named "rowid", "_rowid_", and "oid".
 259   */
 260   if( isIPK ){
 261     /* If a single PRIMARY KEY column with type INTEGER was seen, then it
 262     ** might be an alise for the ROWID.  But it might also be a WITHOUT ROWID
 263     ** table or a INTEGER PRIMARY KEY DESC column, neither of which are
 264     ** ROWID aliases.  To distinguish these cases, check to see if
 265     ** there is a "pk" entry in "PRAGMA index_list".  There will be
 266     ** no "pk" index if the PRIMARY KEY really is an alias for the ROWID.
 267     */
 268     zSql = sqlite3_mprintf("SELECT 1 FROM pragma_index_list(%Q)"
 269                            " WHERE origin='pk'", zTab);
 270     if( zSql==0 ) goto col_oom;
 271     rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
 272     sqlite3_free(zSql);
 273     if( rc ){
 274       freeColumnList(azCol);
 275       return 0;
 276     }
 277     rc = sqlite3_step(pStmt);
 278     sqlite3_finalize(pStmt);
 279     pStmt = 0;
 280     preserveRowid = rc==SQLITE_ROW;
 281   }
 282   if( preserveRowid ){
 283     /* Only preserve the rowid if we can find a name to use for the
 284     ** rowid */
 285     static char *azRowid[] = { "rowid", "_rowid_", "oid" };
 286     int i, j;
 287     for(j=0; j<3; j++){
 288       for(i=1; i<=nCol; i++){
 289         if( sqlite3_stricmp(azRowid[j],azCol[i])==0 ) break;
 290       }
 291       if( i>nCol ){
 292         /* At this point, we know that azRowid[j] is not the name of any
 293         ** ordinary column in the table.  Verify that azRowid[j] is a valid
 294         ** name for the rowid before adding it to azCol[0].  WITHOUT ROWID
 295         ** tables will fail this last check */
 296         int rc;
 297         rc = sqlite3_table_column_metadata(p->db,0,zTab,azRowid[j],0,0,0,0,0);
 298         if( rc==SQLITE_OK ) azCol[0] = azRowid[j];
 299         break;
 300       }
 301     }
 302   }
 303   return azCol;
 304
 305 col_oom:
 306   sqlite3_finalize(pStmt);
 307   freeColumnList(azCol);
 308   p->nErr++;
 309   p->rc = SQLITE_NOMEM;
 310   return 0;
 311 }
 312
 313 /*
 314 ** Send mprintf-formatted content to the output callback.
 315 */
 316 static void output_formatted(DState *p, const char *zFormat, ...){
 317   va_list ap;
 318   char *z;
 319   va_start(ap, zFormat);
 320   z = sqlite3_vmprintf(zFormat, ap);
 321   va_end(ap);
 322   p->xCallback(z, p->pArg);
 323   sqlite3_free(z);
 324 }
 325
 326 /*
 327 ** Find a string that is not found anywhere in z[].  Return a pointer
 328 ** to that string.
 329 **
 330 ** Try to use zA and zB first.  If both of those are already found in z[]
 331 ** then make up some string and store it in the buffer zBuf.
 332 */
 333 static const char *unused_string(
 334   const char *z,                    /* Result must not appear anywhere in z */
 335   const char *zA, const char *zB,   /* Try these first */
 336   char *zBuf                        /* Space to store a generated string */
 337 ){
 338   unsigned i = 0;
 339   if( strstr(z, zA)==0 ) return zA;
 340   if( strstr(z, zB)==0 ) return zB;
 341   do{
 342     sqlite3_snprintf(20,zBuf,"(%s%u)", zA, i++);
 343   }while( strstr(z,zBuf)!=0 );
 344   return zBuf;
 345 }
 346
 347 /*
 348 ** Output the given string as a quoted string using SQL quoting conventions.
 349 ** Additionallly , escape the "\n" and "\r" characters so that they do not
 350 ** get corrupted by end-of-line translation facilities in some operating
 351 ** systems.
 352 */
 353 static void output_quoted_escaped_string(DState *p, const char *z){
 354   int i;
 355   char c;
 356   for(i=0; (c = z[i])!=0 && c!='\'' && c!='\n' && c!='\r'; i++){}
 357   if( c==0 ){
 358     output_formatted(p,"'%s'",z);
 359   }else{
 360     const char *zNL = 0;
 361     const char *zCR = 0;
 362     int nNL = 0;
 363     int nCR = 0;
 364     char zBuf1[20], zBuf2[20];
 365     for(i=0; z[i]; i++){
 366       if( z[i]=='\n' ) nNL++;
 367       if( z[i]=='\r' ) nCR++;
 368     }
 369     if( nNL ){
 370       p->xCallback("replace(", p->pArg);
 371       zNL = unused_string(z, "\\n", "\\012", zBuf1);
 372     }
 373     if( nCR ){
 374       p->xCallback("replace(", p->pArg);
 375       zCR = unused_string(z, "\\r", "\\015", zBuf2);
 376     }
 377     p->xCallback("'", p->pArg);
 378     while( *z ){
 379       for(i=0; (c = z[i])!=0 && c!='\n' && c!='\r' && c!='\''; i++){}
 380       if( c=='\'' ) i++;
 381       if( i ){
 382         output_formatted(p, "%.*s", i, z);
 383         z += i;
 384       }
 385       if( c=='\'' ){
 386         p->xCallback("'", p->pArg);
 387         continue;
 388       }
 389       if( c==0 ){
 390         break;
 391       }
 392       z++;
 393       if( c=='\n' ){
 394         p->xCallback(zNL, p->pArg);
 395         continue;
 396       }
 397       p->xCallback(zCR, p->pArg);
 398     }
 399     p->xCallback("'", p->pArg);
 400     if( nCR ){
 401       output_formatted(p, ",'%s',char(13))", zCR);
 402     }
 403     if( nNL ){
 404       output_formatted(p, ",'%s',char(10))", zNL);
 405     }
 406   }
 407 }
 408
 409 /*
 410 ** This is an sqlite3_exec callback routine used for dumping the database.
 411 ** Each row received by this callback consists of a table name,
 412 ** the table type ("index" or "table") and SQL to create the table.
 413 ** This routine should print text sufficient to recreate the table.
 414 */
 415 static int dump_callback(void *pArg, int nArg, char **azArg, char **azCol){
 416   int rc;
 417   const char *zTable;
 418   const char *zType;
 419   const char *zSql;
 420   DState *p = (DState*)pArg;
 421   sqlite3_stmt *pStmt;
 422
 423   (void)azCol;
 424   if( nArg!=3 ) return 1;
 425   zTable = azArg[0];
 426   zType = azArg[1];
 427   zSql = azArg[2];
 428
 429   if( strcmp(zTable, "sqlite_sequence")==0 ){
 430     p->xCallback("DELETE FROM sqlite_sequence;\n", p->pArg);
 431   }else if( sqlite3_strglob("sqlite_stat?", zTable)==0 ){
 432     p->xCallback("ANALYZE sqlite_master;\n", p->pArg);
 433   }else if( strncmp(zTable, "sqlite_", 7)==0 ){
 434     return 0;
 435   }else if( strncmp(zSql, "CREATE VIRTUAL TABLE", 20)==0 ){
 436     if( !p->writableSchema ){
 437       p->xCallback("PRAGMA writable_schema=ON;\n", p->pArg);
 438       p->writableSchema = 1;
 439     }
 440     output_formatted(p,
 441        "INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"
 442        "VALUES('table','%q','%q',0,'%q');",
 443        zTable, zTable, zSql);
 444     return 0;
 445   }else{
 446     if( sqlite3_strglob("CREATE TABLE ['\"]*", zSql)==0 ){
 447       p->xCallback("CREATE TABLE IF NOT EXISTS ", p->pArg);
 448       p->xCallback(zSql+13, p->pArg);
 449     }else{
 450       p->xCallback(zSql, p->pArg);
 451     }
 452     p->xCallback(";\n", p->pArg);
 453   }
 454
 455   if( strcmp(zType, "table")==0 ){
 456     DText sSelect;
 457     DText sTable;
 458     char **azCol;
 459     int i;
 460     int nCol;
 461
 462     azCol = tableColumnList(p, zTable);
 463     if( azCol==0 ) return 0;
 464
 465     initText(&sTable);
 466     appendText(&sTable, "INSERT INTO ", 0);
 467
 468     /* Always quote the table name, even if it appears to be pure ascii,
 469     ** in case it is a keyword. Ex:  INSERT INTO "table" ... */
 470     appendText(&sTable, zTable, quoteChar(zTable));
 471
 472     /* If preserving the rowid, add a column list after the table name.
 473     ** In other words:  "INSERT INTO tab(rowid,a,b,c,...) VALUES(...)"
 474     ** instead of the usual "INSERT INTO tab VALUES(...)".
 475     */
 476     if( azCol[0] ){
 477       appendText(&sTable, "(", 0);
 478       appendText(&sTable, azCol[0], 0);
 479       for(i=1; azCol[i]; i++){
 480         appendText(&sTable, ",", 0);
 481         appendText(&sTable, azCol[i], quoteChar(azCol[i]));
 482       }
 483       appendText(&sTable, ")", 0);
 484     }
 485     appendText(&sTable, " VALUES(", 0);
 486
 487     /* Build an appropriate SELECT statement */
 488     initText(&sSelect);
 489     appendText(&sSelect, "SELECT ", 0);
 490     if( azCol[0] ){
 491       appendText(&sSelect, azCol[0], 0);
 492       appendText(&sSelect, ",", 0);
 493     }
 494     for(i=1; azCol[i]; i++){
 495       appendText(&sSelect, azCol[i], quoteChar(azCol[i]));
 496       if( azCol[i+1] ){
 497         appendText(&sSelect, ",", 0);
 498       }
 499     }
 500     nCol = i;
 501     if( azCol[0]==0 ) nCol--;
 502     freeColumnList(azCol);
 503     appendText(&sSelect, " FROM ", 0);
 504     appendText(&sSelect, zTable, quoteChar(zTable));
 505
 506     rc = sqlite3_prepare_v2(p->db, sSelect.z, -1, &pStmt, 0);
 507     if( rc!=SQLITE_OK ){
 508       p->nErr++;
 509       if( p->rc==SQLITE_OK ) p->rc = rc;
 510     }else{
 511       while( SQLITE_ROW==sqlite3_step(pStmt) ){
 512         p->xCallback(sTable.z, p->pArg);
 513         for(i=0; i<nCol; i++){
 514           if( i ) p->xCallback(",", p->pArg);
 515           switch( sqlite3_column_type(pStmt,i) ){
 516             case SQLITE_INTEGER: {
 517               output_formatted(p, "%lld", sqlite3_column_int64(pStmt,i));
 518               break;
 519             }
 520             case SQLITE_FLOAT: {
 521               double r = sqlite3_column_double(pStmt,i);
 522               output_formatted(p, "%!.20g", r);
 523               break;
 524             }
 525             case SQLITE_NULL: {
 526               p->xCallback("NULL", p->pArg);
 527               break;
 528             }
 529             case SQLITE_TEXT: {
 530               output_quoted_escaped_string(p,
 531                    (const char*)sqlite3_column_text(pStmt,i));
 532               break;
 533             }
 534             case SQLITE_BLOB: {
 535               int nByte = sqlite3_column_bytes(pStmt,i);
 536               unsigned char *a = (unsigned char*)sqlite3_column_blob(pStmt,i);
 537               int j;
 538               p->xCallback("x'", p->pArg);
 539               for(j=0; j<nByte; j++){
 540                 char zWord[3];
 541                 zWord[0] = "0123456789abcdef"[(a[j]>>4)&15];
 542                 zWord[1] = "0123456789abcdef"[a[j]&15];
 543                 zWord[2] = 0;
 544                 p->xCallback(zWord, p->pArg);
 545               }
 546               p->xCallback("'", p->pArg);
 547               break;
 548             }
 549           }
 550         }
 551         p->xCallback(");\n", p->pArg);
 552       }
 553     }
 554     sqlite3_finalize(pStmt);
 555     freeText(&sTable);
 556     freeText(&sSelect);
 557   }
 558   return 0;
 559 }
 560
 561
 562 /*
 563 ** Execute a query statement that will generate SQL output.  Print
 564 ** the result columns, comma-separated, on a line and then add a
 565 ** semicolon terminator to the end of that line.
 566 **
 567 ** If the number of columns is 1 and that column contains text "--"
 568 ** then write the semicolon on a separate line.  That way, if a
 569 ** "--" comment occurs at the end of the statement, the comment
 570 ** won't consume the semicolon terminator.
 571 */
 572 static void output_sql_from_query(
 573   DState *p,               /* Query context */
 574   const char *zSelect,     /* SELECT statement to extract content */
 575   ...
 576 ){
 577   sqlite3_stmt *pSelect;
 578   int rc;
 579   int nResult;
 580   int i;
 581   const char *z;
 582   char *zSql;
 583   va_list ap;
 584   va_start(ap, zSelect);
 585   zSql = sqlite3_vmprintf(zSelect, ap);
 586   va_end(ap);
 587   if( zSql==0 ){
 588     p->rc = SQLITE_NOMEM;
 589     p->nErr++;
 590     return;
 591   }
 592   rc = sqlite3_prepare_v2(p->db, zSql, -1, &pSelect, 0);
 593   sqlite3_free(zSql);
 594   if( rc!=SQLITE_OK || !pSelect ){
 595     output_formatted(p, "/**** ERROR: (%d) %s *****/\n", rc,
 596                 sqlite3_errmsg(p->db));
 597     p->nErr++;
 598     return;
 599   }
 600   rc = sqlite3_step(pSelect);
 601   nResult = sqlite3_column_count(pSelect);
 602   while( rc==SQLITE_ROW ){
 603     z = (const char*)sqlite3_column_text(pSelect, 0);
 604     p->xCallback(z, p->pArg);
 605     for(i=1; i<nResult; i++){
 606       p->xCallback(",", p->pArg);
 607       p->xCallback((const char*)sqlite3_column_text(pSelect,i), p->pArg);
 608     }
 609     if( z==0 ) z = "";
 610     while( z[0] && (z[0]!='-' || z[1]!='-') ) z++;
 611     if( z[0] ){
 612       p->xCallback("\n;\n", p->pArg);
 613     }else{
 614       p->xCallback(";\n", p->pArg);
 615     }
 616     rc = sqlite3_step(pSelect);
 617   }
 618   rc = sqlite3_finalize(pSelect);
 619   if( rc!=SQLITE_OK ){
 620     output_formatted(p, "/**** ERROR: (%d) %s *****/\n", rc,
 621                      sqlite3_errmsg(p->db));
 622     if( (rc&0xff)!=SQLITE_CORRUPT ) p->nErr++;
 623   }
 624 }
 625
 626 /*
 627 ** Run zQuery.  Use dump_callback() as the callback routine so that
 628 ** the contents of the query are output as SQL statements.
 629 **
 630 ** If we get a SQLITE_CORRUPT error, rerun the query after appending
 631 ** "ORDER BY rowid DESC" to the end.
 632 */
 633 static void run_schema_dump_query(
 634   DState *p,
 635   const char *zQuery,
 636   ...
 637 ){
 638   char *zErr = 0;
 639   char *z;
 640   va_list ap;
 641   va_start(ap, zQuery);
 642   z = sqlite3_vmprintf(zQuery, ap);
 643   va_end(ap);
 644   sqlite3_exec(p->db, z, dump_callback, p, &zErr);
 645   sqlite3_free(z);
 646   if( zErr ){
 647     output_formatted(p, "/****** %s ******/\n", zErr);
 648     sqlite3_free(zErr);
 649     p->nErr++;
 650     zErr = 0;
 651   }
 652 }
 653
 654 /*
 655 ** Convert an SQLite database into SQL statements that will recreate that
 656 ** database.
 657 */
 658 int sqlite3_db_dump(
 659   sqlite3 *db,               /* The database connection */
 660   const char *zSchema,       /* Which schema to dump.  Usually "main". */
 661   const char *zTable,        /* Which table to dump.  NULL means everything. */
 662   int (*xCallback)(const char*,void*),   /* Output sent to this callback */
 663   void *pArg                             /* Second argument of the callback */
 664 ){
 665   DState x;
 666   memset(&x, 0, sizeof(x));
 667   x.rc = sqlite3_exec(db, "BEGIN", 0, 0, 0);
 668   if( x.rc ) return x.rc;
 669   x.db = db;
 670   x.xCallback = xCallback;
 671   x.pArg = pArg;
 672   xCallback("PRAGMA foreign_keys=OFF;\nBEGIN TRANSACTION;\n", pArg);
 673   if( zTable==0 ){
 674     run_schema_dump_query(&x,
 675       "SELECT name, type, sql FROM \"%w\".sqlite_master "
 676       "WHERE sql NOT NULL AND type=='table' AND name!='sqlite_sequence'",
 677       zSchema
 678     );
 679     run_schema_dump_query(&x,
 680       "SELECT name, type, sql FROM \"%w\".sqlite_master "
 681       "WHERE name=='sqlite_sequence'", zSchema
 682     );
 683     output_sql_from_query(&x,
 684       "SELECT sql FROM sqlite_master "
 685       "WHERE sql NOT NULL AND type IN ('index','trigger','view')", 0
 686     );
 687   }else{
 688     run_schema_dump_query(&x,
 689       "SELECT name, type, sql FROM \"%w\".sqlite_master "
 690       "WHERE tbl_name=%Q COLLATE nocase AND type=='table'"
 691       "  AND sql NOT NULL",
 692       zSchema, zTable
 693     );
 694     output_sql_from_query(&x,
 695       "SELECT sql FROM \"%w\".sqlite_master "
 696       "WHERE sql NOT NULL"
 697       "  AND type IN ('index','trigger','view')"
 698       "  AND tbl_name=%Q COLLATE nocase",
 699       zSchema, zTable
 700     );
 701   }
 702   if( x.writableSchema ){
 703     xCallback("PRAGMA writable_schema=OFF;\n", pArg);
 704   }
 705   xCallback(x.nErr ? "ROLLBACK; -- due to errors\n" : "COMMIT;\n", pArg);
 706   sqlite3_exec(db, "COMMIT", 0, 0, 0);
 707   return x.rc;
 708 }
 709
 710
 711
 712 /* The generic subroutine is above.  The code the follows implements
 713 ** the command-line interface.
 714 */
 715 #ifdef DBDUMP_STANDALONE
 716 #include <stdio.h>
 717
 718 /*
 719 ** Command-line interface
 720 */
 721 int main(int argc, char **argv){
 722   sqlite3 *db;
 723   const char *zDb;
 724   const char *zSchema;
 725   const char *zTable = 0;
 726   int rc;
 727
 728   if( argc<2 || argc>4 ){
 729     fprintf(stderr, "Usage: %s DATABASE ?SCHEMA? ?TABLE?\n", argv[0]);
 730     return 1;
 731   }
 732   zDb = argv[1];
 733   zSchema = argc>=3 ? argv[2] : "main";
 734   zTable = argc==4 ? argv[3] : 0;
 735
 736   rc = sqlite3_open(zDb, &db);
 737   if( rc ){
 738     fprintf(stderr, "Cannot open \"%s\": %s\n", zDb, sqlite3_errmsg(db));
 739     sqlite3_close(db);
 740     return 1;
 741   }
 742   rc = sqlite3_db_dump(db, zSchema, zTable,
 743           (int(*)(const char*,void*))fputs, (void*)stdout);
 744   if( rc ){
 745     fprintf(stderr, "Error: sqlite3_db_dump() returns %d\n", rc);
 746   }
 747   sqlite3_close(db);
 748   return rc!=SQLITE_OK;
 749 }
 750 #endif /* DBDUMP_STANDALONE */