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         rc = sqlite3_table_column_metadata(p->db,0,zTab,azRowid[j],0,0,0,0,0);
 297         if( rc==SQLITE_OK ) azCol[0] = azRowid[j];
 298         break;
 299       }
 300     }
 301   }
 302   return azCol;
 303
 304 col_oom:
 305   sqlite3_finalize(pStmt);
 306   freeColumnList(azCol);
 307   p->nErr++;
 308   p->rc = SQLITE_NOMEM;
 309   return 0;
 310 }
 311
 312 /*
 313 ** Send mprintf-formatted content to the output callback.
 314 */
 315 static void output_formatted(DState *p, const char *zFormat, ...){
 316   va_list ap;
 317   char *z;
 318   va_start(ap, zFormat);
 319   z = sqlite3_vmprintf(zFormat, ap);
 320   va_end(ap);
 321   p->xCallback(z, p->pArg);
 322   sqlite3_free(z);
 323 }
 324
 325 /*
 326 ** Find a string that is not found anywhere in z[].  Return a pointer
 327 ** to that string.
 328 **
 329 ** Try to use zA and zB first.  If both of those are already found in z[]
 330 ** then make up some string and store it in the buffer zBuf.
 331 */
 332 static const char *unused_string(
 333   const char *z,                    /* Result must not appear anywhere in z */
 334   const char *zA, const char *zB,   /* Try these first */
 335   char *zBuf                        /* Space to store a generated string */
 336 ){
 337   unsigned i = 0;
 338   if( strstr(z, zA)==0 ) return zA;
 339   if( strstr(z, zB)==0 ) return zB;
 340   do{
 341     sqlite3_snprintf(20,zBuf,"(%s%u)", zA, i++);
 342   }while( strstr(z,zBuf)!=0 );
 343   return zBuf;
 344 }
 345
 346 /*
 347 ** Output the given string as a quoted string using SQL quoting conventions.
 348 ** Additionallly , escape the "\n" and "\r" characters so that they do not
 349 ** get corrupted by end-of-line translation facilities in some operating
 350 ** systems.
 351 */
 352 static void output_quoted_escaped_string(DState *p, const char *z){
 353   int i;
 354   char c;
 355   for(i=0; (c = z[i])!=0 && c!='\'' && c!='\n' && c!='\r'; i++){}
 356   if( c==0 ){
 357     output_formatted(p,"'%s'",z);
 358   }else{
 359     const char *zNL = 0;
 360     const char *zCR = 0;
 361     int nNL = 0;
 362     int nCR = 0;
 363     char zBuf1[20], zBuf2[20];
 364     for(i=0; z[i]; i++){
 365       if( z[i]=='\n' ) nNL++;
 366       if( z[i]=='\r' ) nCR++;
 367     }
 368     if( nNL ){
 369       p->xCallback("replace(", p->pArg);
 370       zNL = unused_string(z, "\\n", "\\012", zBuf1);
 371     }
 372     if( nCR ){
 373       p->xCallback("replace(", p->pArg);
 374       zCR = unused_string(z, "\\r", "\\015", zBuf2);
 375     }
 376     p->xCallback("'", p->pArg);
 377     while( *z ){
 378       for(i=0; (c = z[i])!=0 && c!='\n' && c!='\r' && c!='\''; i++){}
 379       if( c=='\'' ) i++;
 380       if( i ){
 381         output_formatted(p, "%.*s", i, z);
 382         z += i;
 383       }
 384       if( c=='\'' ){
 385         p->xCallback("'", p->pArg);
 386         continue;
 387       }
 388       if( c==0 ){
 389         break;
 390       }
 391       z++;
 392       if( c=='\n' ){
 393         p->xCallback(zNL, p->pArg);
 394         continue;
 395       }
 396       p->xCallback(zCR, p->pArg);
 397     }
 398     p->xCallback("'", p->pArg);
 399     if( nCR ){
 400       output_formatted(p, ",'%s',char(13))", zCR);
 401     }
 402     if( nNL ){
 403       output_formatted(p, ",'%s',char(10))", zNL);
 404     }
 405   }
 406 }
 407
 408 /*
 409 ** This is an sqlite3_exec callback routine used for dumping the database.
 410 ** Each row received by this callback consists of a table name,
 411 ** the table type ("index" or "table") and SQL to create the table.
 412 ** This routine should print text sufficient to recreate the table.
 413 */
 414 static int dump_callback(void *pArg, int nArg, char **azArg, char **azCol){
 415   int rc;
 416   const char *zTable;
 417   const char *zType;
 418   const char *zSql;
 419   DState *p = (DState*)pArg;
 420   sqlite3_stmt *pStmt;
 421
 422   (void)azCol;
 423   if( nArg!=3 ) return 1;
 424   zTable = azArg[0];
 425   zType = azArg[1];
 426   zSql = azArg[2];
 427
 428   if( strcmp(zTable, "sqlite_sequence")==0 ){
 429     p->xCallback("DELETE FROM sqlite_sequence;\n", p->pArg);
 430   }else if( sqlite3_strglob("sqlite_stat?", zTable)==0 ){
 431     p->xCallback("ANALYZE sqlite_master;\n", p->pArg);
 432   }else if( strncmp(zTable, "sqlite_", 7)==0 ){
 433     return 0;
 434   }else if( strncmp(zSql, "CREATE VIRTUAL TABLE", 20)==0 ){
 435     if( !p->writableSchema ){
 436       p->xCallback("PRAGMA writable_schema=ON;\n", p->pArg);
 437       p->writableSchema = 1;
 438     }
 439     output_formatted(p,
 440        "INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"
 441        "VALUES('table','%q','%q',0,'%q');",
 442        zTable, zTable, zSql);
 443     return 0;
 444   }else{
 445     if( sqlite3_strglob("CREATE TABLE ['\"]*", zSql)==0 ){
 446       p->xCallback("CREATE TABLE IF NOT EXISTS ", p->pArg);
 447       p->xCallback(zSql+13, p->pArg);
 448     }else{
 449       p->xCallback(zSql, p->pArg);
 450     }
 451     p->xCallback(";\n", p->pArg);
 452   }
 453
 454   if( strcmp(zType, "table")==0 ){
 455     DText sSelect;
 456     DText sTable;
 457     char **azTCol;
 458     int i;
 459     int nCol;
 460
 461     azTCol = tableColumnList(p, zTable);
 462     if( azTCol==0 ) return 0;
 463
 464     initText(&sTable);
 465     appendText(&sTable, "INSERT INTO ", 0);
 466
 467     /* Always quote the table name, even if it appears to be pure ascii,
 468     ** in case it is a keyword. Ex:  INSERT INTO "table" ... */
 469     appendText(&sTable, zTable, quoteChar(zTable));
 470
 471     /* If preserving the rowid, add a column list after the table name.
 472     ** In other words:  "INSERT INTO tab(rowid,a,b,c,...) VALUES(...)"
 473     ** instead of the usual "INSERT INTO tab VALUES(...)".
 474     */
 475     if( azTCol[0] ){
 476       appendText(&sTable, "(", 0);
 477       appendText(&sTable, azTCol[0], 0);
 478       for(i=1; azTCol[i]; i++){
 479         appendText(&sTable, ",", 0);
 480         appendText(&sTable, azTCol[i], quoteChar(azTCol[i]));
 481       }
 482       appendText(&sTable, ")", 0);
 483     }
 484     appendText(&sTable, " VALUES(", 0);
 485
 486     /* Build an appropriate SELECT statement */
 487     initText(&sSelect);
 488     appendText(&sSelect, "SELECT ", 0);
 489     if( azTCol[0] ){
 490       appendText(&sSelect, azTCol[0], 0);
 491       appendText(&sSelect, ",", 0);
 492     }
 493     for(i=1; azTCol[i]; i++){
 494       appendText(&sSelect, azTCol[i], quoteChar(azTCol[i]));
 495       if( azTCol[i+1] ){
 496         appendText(&sSelect, ",", 0);
 497       }
 498     }
 499     nCol = i;
 500     if( azTCol[0]==0 ) nCol--;
 501     freeColumnList(azTCol);
 502     appendText(&sSelect, " FROM ", 0);
 503     appendText(&sSelect, zTable, quoteChar(zTable));
 504
 505     rc = sqlite3_prepare_v2(p->db, sSelect.z, -1, &pStmt, 0);
 506     if( rc!=SQLITE_OK ){
 507       p->nErr++;
 508       if( p->rc==SQLITE_OK ) p->rc = rc;
 509     }else{
 510       while( SQLITE_ROW==sqlite3_step(pStmt) ){
 511         p->xCallback(sTable.z, p->pArg);
 512         for(i=0; i<nCol; i++){
 513           if( i ) p->xCallback(",", p->pArg);
 514           switch( sqlite3_column_type(pStmt,i) ){
 515             case SQLITE_INTEGER: {
 516               output_formatted(p, "%lld", sqlite3_column_int64(pStmt,i));
 517               break;
 518             }
 519             case SQLITE_FLOAT: {
 520               double r = sqlite3_column_double(pStmt,i);
 521               output_formatted(p, "%!.20g", r);
 522               break;
 523             }
 524             case SQLITE_NULL: {
 525               p->xCallback("NULL", p->pArg);
 526               break;
 527             }
 528             case SQLITE_TEXT: {
 529               output_quoted_escaped_string(p,
 530                    (const char*)sqlite3_column_text(pStmt,i));
 531               break;
 532             }
 533             case SQLITE_BLOB: {
 534               int nByte = sqlite3_column_bytes(pStmt,i);
 535               unsigned char *a = (unsigned char*)sqlite3_column_blob(pStmt,i);
 536               int j;
 537               p->xCallback("x'", p->pArg);
 538               for(j=0; j<nByte; j++){
 539                 char zWord[3];
 540                 zWord[0] = "0123456789abcdef"[(a[j]>>4)&15];
 541                 zWord[1] = "0123456789abcdef"[a[j]&15];
 542                 zWord[2] = 0;
 543                 p->xCallback(zWord, p->pArg);
 544               }
 545               p->xCallback("'", p->pArg);
 546               break;
 547             }
 548           }
 549         }
 550         p->xCallback(");\n", p->pArg);
 551       }
 552     }
 553     sqlite3_finalize(pStmt);
 554     freeText(&sTable);
 555     freeText(&sSelect);
 556   }
 557   return 0;
 558 }
 559
 560
 561 /*
 562 ** Execute a query statement that will generate SQL output.  Print
 563 ** the result columns, comma-separated, on a line and then add a
 564 ** semicolon terminator to the end of that line.
 565 **
 566 ** If the number of columns is 1 and that column contains text "--"
 567 ** then write the semicolon on a separate line.  That way, if a
 568 ** "--" comment occurs at the end of the statement, the comment
 569 ** won't consume the semicolon terminator.
 570 */
 571 static void output_sql_from_query(
 572   DState *p,               /* Query context */
 573   const char *zSelect,     /* SELECT statement to extract content */
 574   ...
 575 ){
 576   sqlite3_stmt *pSelect;
 577   int rc;
 578   int nResult;
 579   int i;
 580   const char *z;
 581   char *zSql;
 582   va_list ap;
 583   va_start(ap, zSelect);
 584   zSql = sqlite3_vmprintf(zSelect, ap);
 585   va_end(ap);
 586   if( zSql==0 ){
 587     p->rc = SQLITE_NOMEM;
 588     p->nErr++;
 589     return;
 590   }
 591   rc = sqlite3_prepare_v2(p->db, zSql, -1, &pSelect, 0);
 592   sqlite3_free(zSql);
 593   if( rc!=SQLITE_OK || !pSelect ){
 594     output_formatted(p, "/**** ERROR: (%d) %s *****/\n", rc,
 595                 sqlite3_errmsg(p->db));
 596     p->nErr++;
 597     return;
 598   }
 599   rc = sqlite3_step(pSelect);
 600   nResult = sqlite3_column_count(pSelect);
 601   while( rc==SQLITE_ROW ){
 602     z = (const char*)sqlite3_column_text(pSelect, 0);
 603     p->xCallback(z, p->pArg);
 604     for(i=1; i<nResult; i++){
 605       p->xCallback(",", p->pArg);
 606       p->xCallback((const char*)sqlite3_column_text(pSelect,i), p->pArg);
 607     }
 608     if( z==0 ) z = "";
 609     while( z[0] && (z[0]!='-' || z[1]!='-') ) z++;
 610     if( z[0] ){
 611       p->xCallback("\n;\n", p->pArg);
 612     }else{
 613       p->xCallback(";\n", p->pArg);
 614     }
 615     rc = sqlite3_step(pSelect);
 616   }
 617   rc = sqlite3_finalize(pSelect);
 618   if( rc!=SQLITE_OK ){
 619     output_formatted(p, "/**** ERROR: (%d) %s *****/\n", rc,
 620                      sqlite3_errmsg(p->db));
 621     if( (rc&0xff)!=SQLITE_CORRUPT ) p->nErr++;
 622   }
 623 }
 624
 625 /*
 626 ** Run zQuery.  Use dump_callback() as the callback routine so that
 627 ** the contents of the query are output as SQL statements.
 628 **
 629 ** If we get a SQLITE_CORRUPT error, rerun the query after appending
 630 ** "ORDER BY rowid DESC" to the end.
 631 */
 632 static void run_schema_dump_query(
 633   DState *p,
 634   const char *zQuery,
 635   ...
 636 ){
 637   char *zErr = 0;
 638   char *z;
 639   va_list ap;
 640   va_start(ap, zQuery);
 641   z = sqlite3_vmprintf(zQuery, ap);
 642   va_end(ap);
 643   sqlite3_exec(p->db, z, dump_callback, p, &zErr);
 644   sqlite3_free(z);
 645   if( zErr ){
 646     output_formatted(p, "/****** %s ******/\n", zErr);
 647     sqlite3_free(zErr);
 648     p->nErr++;
 649     zErr = 0;
 650   }
 651 }
 652
 653 /*
 654 ** Convert an SQLite database into SQL statements that will recreate that
 655 ** database.
 656 */
 657 int sqlite3_db_dump(
 658   sqlite3 *db,               /* The database connection */
 659   const char *zSchema,       /* Which schema to dump.  Usually "main". */
 660   const char *zTable,        /* Which table to dump.  NULL means everything. */
 661   int (*xCallback)(const char*,void*),   /* Output sent to this callback */
 662   void *pArg                             /* Second argument of the callback */
 663 ){
 664   DState x;
 665   memset(&x, 0, sizeof(x));
 666   x.rc = sqlite3_exec(db, "BEGIN", 0, 0, 0);
 667   if( x.rc ) return x.rc;
 668   x.db = db;
 669   x.xCallback = xCallback;
 670   x.pArg = pArg;
 671   xCallback("PRAGMA foreign_keys=OFF;\nBEGIN TRANSACTION;\n", pArg);
 672   if( zTable==0 ){
 673     run_schema_dump_query(&x,
 674       "SELECT name, type, sql FROM \"%w\".sqlite_master "
 675       "WHERE sql NOT NULL AND type=='table' AND name!='sqlite_sequence'",
 676       zSchema
 677     );
 678     run_schema_dump_query(&x,
 679       "SELECT name, type, sql FROM \"%w\".sqlite_master "
 680       "WHERE name=='sqlite_sequence'", zSchema
 681     );
 682     output_sql_from_query(&x,
 683       "SELECT sql FROM sqlite_master "
 684       "WHERE sql NOT NULL AND type IN ('index','trigger','view')", 0
 685     );
 686   }else{
 687     run_schema_dump_query(&x,
 688       "SELECT name, type, sql FROM \"%w\".sqlite_master "
 689       "WHERE tbl_name=%Q COLLATE nocase AND type=='table'"
 690       "  AND sql NOT NULL",
 691       zSchema, zTable
 692     );
 693     output_sql_from_query(&x,
 694       "SELECT sql FROM \"%w\".sqlite_master "
 695       "WHERE sql NOT NULL"
 696       "  AND type IN ('index','trigger','view')"
 697       "  AND tbl_name=%Q COLLATE nocase",
 698       zSchema, zTable
 699     );
 700   }
 701   if( x.writableSchema ){
 702     xCallback("PRAGMA writable_schema=OFF;\n", pArg);
 703   }
 704   xCallback(x.nErr ? "ROLLBACK; -- due to errors\n" : "COMMIT;\n", pArg);
 705   sqlite3_exec(db, "COMMIT", 0, 0, 0);
 706   return x.rc;
 707 }
 708
 709
 710
 711 /* The generic subroutine is above.  The code the follows implements
 712 ** the command-line interface.
 713 */
 714 #ifdef DBDUMP_STANDALONE
 715 #include <stdio.h>
 716
 717 /*
 718 ** Command-line interface
 719 */
 720 int main(int argc, char **argv){
 721   sqlite3 *db;
 722   const char *zDb;
 723   const char *zSchema;
 724   const char *zTable = 0;
 725   int rc;
 726
 727   if( argc<2 || argc>4 ){
 728     fprintf(stderr, "Usage: %s DATABASE ?SCHEMA? ?TABLE?\n", argv[0]);
 729     return 1;
 730   }
 731   zDb = argv[1];
 732   zSchema = argc>=3 ? argv[2] : "main";
 733   zTable = argc==4 ? argv[3] : 0;
 734
 735   rc = sqlite3_open(zDb, &db);
 736   if( rc ){
 737     fprintf(stderr, "Cannot open \"%s\": %s\n", zDb, sqlite3_errmsg(db));
 738     sqlite3_close(db);
 739     return 1;
 740   }
 741   rc = sqlite3_db_dump(db, zSchema, zTable,
 742           (int(*)(const char*,void*))fputs, (void*)stdout);
 743   if( rc ){
 744     fprintf(stderr, "Error: sqlite3_db_dump() returns %d\n", rc);
 745   }
 746   sqlite3_close(db);
 747   return rc!=SQLITE_OK;
 748 }
 749 #endif /* DBDUMP_STANDALONE */