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   int i;
 145   if( !isalpha((unsigned char)zName[0]) && zName[0]!='_' ) return '"';
 146   for(i=0; zName[i]; i++){
 147     if( !isalnum((unsigned char)zName[i]) && zName[i]!='_' ) return '"';
 148   }
 149   return sqlite3_keyword_check(zName, i) ? '"' : 0;
 150 }
 151
 152
 153 /*
 154 ** Release memory previously allocated by tableColumnList().
 155 */
 156 static void freeColumnList(char **azCol){
 157   int i;
 158   for(i=1; azCol[i]; i++){
 159     sqlite3_free(azCol[i]);
 160   }
 161   /* azCol[0] is a static string */
 162   sqlite3_free(azCol);
 163 }
 164
 165 /*
 166 ** Return a list of pointers to strings which are the names of all
 167 ** columns in table zTab.   The memory to hold the names is dynamically
 168 ** allocated and must be released by the caller using a subsequent call
 169 ** to freeColumnList().
 170 **
 171 ** The azCol[0] entry is usually NULL.  However, if zTab contains a rowid
 172 ** value that needs to be preserved, then azCol[0] is filled in with the
 173 ** name of the rowid column.
 174 **
 175 ** The first regular column in the table is azCol[1].  The list is terminated
 176 ** by an entry with azCol[i]==0.
 177 */
 178 static char **tableColumnList(DState *p, const char *zTab){
 179   char **azCol = 0;
 180   sqlite3_stmt *pStmt = 0;
 181   char *zSql;
 182   int nCol = 0;
 183   int nAlloc = 0;
 184   int nPK = 0;       /* Number of PRIMARY KEY columns seen */
 185   int isIPK = 0;     /* True if one PRIMARY KEY column of type INTEGER */
 186   int preserveRowid = 1;
 187   int rc;
 188
 189   zSql = sqlite3_mprintf("PRAGMA table_info=%Q", zTab);
 190   if( zSql==0 ) return 0;
 191   rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
 192   sqlite3_free(zSql);
 193   if( rc ) return 0;
 194   while( sqlite3_step(pStmt)==SQLITE_ROW ){
 195     if( nCol>=nAlloc-2 ){
 196       char **azNew;
 197       nAlloc = nAlloc*2 + nCol + 10;
 198       azNew = sqlite3_realloc64(azCol, nAlloc*sizeof(azCol[0]));
 199       if( azNew==0 ) goto col_oom;
 200       azCol = azNew;
 201       azCol[0] = 0;
 202     }
 203     azCol[++nCol] = sqlite3_mprintf("%s", sqlite3_column_text(pStmt, 1));
 204     if( azCol[nCol]==0 ) goto col_oom;
 205     if( sqlite3_column_int(pStmt, 5) ){
 206       nPK++;
 207       if( nPK==1
 208        && sqlite3_stricmp((const char*)sqlite3_column_text(pStmt,2),
 209                           "INTEGER")==0
 210       ){
 211         isIPK = 1;
 212       }else{
 213         isIPK = 0;
 214       }
 215     }
 216   }
 217   sqlite3_finalize(pStmt);
 218   pStmt = 0;
 219   azCol[nCol+1] = 0;
 220
 221   /* The decision of whether or not a rowid really needs to be preserved
 222   ** is tricky.  We never need to preserve a rowid for a WITHOUT ROWID table
 223   ** or a table with an INTEGER PRIMARY KEY.  We are unable to preserve
 224   ** rowids on tables where the rowid is inaccessible because there are other
 225   ** columns in the table named "rowid", "_rowid_", and "oid".
 226   */
 227   if( isIPK ){
 228     /* If a single PRIMARY KEY column with type INTEGER was seen, then it
 229     ** might be an alise for the ROWID.  But it might also be a WITHOUT ROWID
 230     ** table or a INTEGER PRIMARY KEY DESC column, neither of which are
 231     ** ROWID aliases.  To distinguish these cases, check to see if
 232     ** there is a "pk" entry in "PRAGMA index_list".  There will be
 233     ** no "pk" index if the PRIMARY KEY really is an alias for the ROWID.
 234     */
 235     zSql = sqlite3_mprintf("SELECT 1 FROM pragma_index_list(%Q)"
 236                            " WHERE origin='pk'", zTab);
 237     if( zSql==0 ) goto col_oom;
 238     rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
 239     sqlite3_free(zSql);
 240     if( rc ){
 241       freeColumnList(azCol);
 242       return 0;
 243     }
 244     rc = sqlite3_step(pStmt);
 245     sqlite3_finalize(pStmt);
 246     pStmt = 0;
 247     preserveRowid = rc==SQLITE_ROW;
 248   }
 249   if( preserveRowid ){
 250     /* Only preserve the rowid if we can find a name to use for the
 251     ** rowid */
 252     static char *azRowid[] = { "rowid", "_rowid_", "oid" };
 253     int i, j;
 254     for(j=0; j<3; j++){
 255       for(i=1; i<=nCol; i++){
 256         if( sqlite3_stricmp(azRowid[j],azCol[i])==0 ) break;
 257       }
 258       if( i>nCol ){
 259         /* At this point, we know that azRowid[j] is not the name of any
 260         ** ordinary column in the table.  Verify that azRowid[j] is a valid
 261         ** name for the rowid before adding it to azCol[0].  WITHOUT ROWID
 262         ** tables will fail this last check */
 263         rc = sqlite3_table_column_metadata(p->db,0,zTab,azRowid[j],0,0,0,0,0);
 264         if( rc==SQLITE_OK ) azCol[0] = azRowid[j];
 265         break;
 266       }
 267     }
 268   }
 269   return azCol;
 270
 271 col_oom:
 272   sqlite3_finalize(pStmt);
 273   freeColumnList(azCol);
 274   p->nErr++;
 275   p->rc = SQLITE_NOMEM;
 276   return 0;
 277 }
 278
 279 /*
 280 ** Send mprintf-formatted content to the output callback.
 281 */
 282 static void output_formatted(DState *p, const char *zFormat, ...){
 283   va_list ap;
 284   char *z;
 285   va_start(ap, zFormat);
 286   z = sqlite3_vmprintf(zFormat, ap);
 287   va_end(ap);
 288   p->xCallback(z, p->pArg);
 289   sqlite3_free(z);
 290 }
 291
 292 /*
 293 ** Find a string that is not found anywhere in z[].  Return a pointer
 294 ** to that string.
 295 **
 296 ** Try to use zA and zB first.  If both of those are already found in z[]
 297 ** then make up some string and store it in the buffer zBuf.
 298 */
 299 static const char *unused_string(
 300   const char *z,                    /* Result must not appear anywhere in z */
 301   const char *zA, const char *zB,   /* Try these first */
 302   char *zBuf                        /* Space to store a generated string */
 303 ){
 304   unsigned i = 0;
 305   if( strstr(z, zA)==0 ) return zA;
 306   if( strstr(z, zB)==0 ) return zB;
 307   do{
 308     sqlite3_snprintf(20,zBuf,"(%s%u)", zA, i++);
 309   }while( strstr(z,zBuf)!=0 );
 310   return zBuf;
 311 }
 312
 313 /*
 314 ** Output the given string as a quoted string using SQL quoting conventions.
 315 ** Additionallly , escape the "\n" and "\r" characters so that they do not
 316 ** get corrupted by end-of-line translation facilities in some operating
 317 ** systems.
 318 */
 319 static void output_quoted_escaped_string(DState *p, const char *z){
 320   int i;
 321   char c;
 322   for(i=0; (c = z[i])!=0 && c!='\'' && c!='\n' && c!='\r'; i++){}
 323   if( c==0 ){
 324     output_formatted(p,"'%s'",z);
 325   }else{
 326     const char *zNL = 0;
 327     const char *zCR = 0;
 328     int nNL = 0;
 329     int nCR = 0;
 330     char zBuf1[20], zBuf2[20];
 331     for(i=0; z[i]; i++){
 332       if( z[i]=='\n' ) nNL++;
 333       if( z[i]=='\r' ) nCR++;
 334     }
 335     if( nNL ){
 336       p->xCallback("replace(", p->pArg);
 337       zNL = unused_string(z, "\\n", "\\012", zBuf1);
 338     }
 339     if( nCR ){
 340       p->xCallback("replace(", p->pArg);
 341       zCR = unused_string(z, "\\r", "\\015", zBuf2);
 342     }
 343     p->xCallback("'", p->pArg);
 344     while( *z ){
 345       for(i=0; (c = z[i])!=0 && c!='\n' && c!='\r' && c!='\''; i++){}
 346       if( c=='\'' ) i++;
 347       if( i ){
 348         output_formatted(p, "%.*s", i, z);
 349         z += i;
 350       }
 351       if( c=='\'' ){
 352         p->xCallback("'", p->pArg);
 353         continue;
 354       }
 355       if( c==0 ){
 356         break;
 357       }
 358       z++;
 359       if( c=='\n' ){
 360         p->xCallback(zNL, p->pArg);
 361         continue;
 362       }
 363       p->xCallback(zCR, p->pArg);
 364     }
 365     p->xCallback("'", p->pArg);
 366     if( nCR ){
 367       output_formatted(p, ",'%s',char(13))", zCR);
 368     }
 369     if( nNL ){
 370       output_formatted(p, ",'%s',char(10))", zNL);
 371     }
 372   }
 373 }
 374
 375 /*
 376 ** This is an sqlite3_exec callback routine used for dumping the database.
 377 ** Each row received by this callback consists of a table name,
 378 ** the table type ("index" or "table") and SQL to create the table.
 379 ** This routine should print text sufficient to recreate the table.
 380 */
 381 static int dump_callback(void *pArg, int nArg, char **azArg, char **azCol){
 382   int rc;
 383   const char *zTable;
 384   const char *zType;
 385   const char *zSql;
 386   DState *p = (DState*)pArg;
 387   sqlite3_stmt *pStmt;
 388
 389   (void)azCol;
 390   if( nArg!=3 ) return 1;
 391   zTable = azArg[0];
 392   zType = azArg[1];
 393   zSql = azArg[2];
 394
 395   if( strcmp(zTable, "sqlite_sequence")==0 ){
 396     p->xCallback("DELETE FROM sqlite_sequence;\n", p->pArg);
 397   }else if( sqlite3_strglob("sqlite_stat?", zTable)==0 ){
 398     p->xCallback("ANALYZE sqlite_schema;\n", p->pArg);
 399   }else if( strncmp(zTable, "sqlite_", 7)==0 ){
 400     return 0;
 401   }else if( strncmp(zSql, "CREATE VIRTUAL TABLE", 20)==0 ){
 402     if( !p->writableSchema ){
 403       p->xCallback("PRAGMA writable_schema=ON;\n", p->pArg);
 404       p->writableSchema = 1;
 405     }
 406     output_formatted(p,
 407        "INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql)"
 408        "VALUES('table','%q','%q',0,'%q');",
 409        zTable, zTable, zSql);
 410     return 0;
 411   }else{
 412     if( sqlite3_strglob("CREATE TABLE ['\"]*", zSql)==0 ){
 413       p->xCallback("CREATE TABLE IF NOT EXISTS ", p->pArg);
 414       p->xCallback(zSql+13, p->pArg);
 415     }else{
 416       p->xCallback(zSql, p->pArg);
 417     }
 418     p->xCallback(";\n", p->pArg);
 419   }
 420
 421   if( strcmp(zType, "table")==0 ){
 422     DText sSelect;
 423     DText sTable;
 424     char **azTCol;
 425     int i;
 426     int nCol;
 427
 428     azTCol = tableColumnList(p, zTable);
 429     if( azTCol==0 ) return 0;
 430
 431     initText(&sTable);
 432     appendText(&sTable, "INSERT INTO ", 0);
 433
 434     /* Always quote the table name, even if it appears to be pure ascii,
 435     ** in case it is a keyword. Ex:  INSERT INTO "table" ... */
 436     appendText(&sTable, zTable, quoteChar(zTable));
 437
 438     /* If preserving the rowid, add a column list after the table name.
 439     ** In other words:  "INSERT INTO tab(rowid,a,b,c,...) VALUES(...)"
 440     ** instead of the usual "INSERT INTO tab VALUES(...)".
 441     */
 442     if( azTCol[0] ){
 443       appendText(&sTable, "(", 0);
 444       appendText(&sTable, azTCol[0], 0);
 445       for(i=1; azTCol[i]; i++){
 446         appendText(&sTable, ",", 0);
 447         appendText(&sTable, azTCol[i], quoteChar(azTCol[i]));
 448       }
 449       appendText(&sTable, ")", 0);
 450     }
 451     appendText(&sTable, " VALUES(", 0);
 452
 453     /* Build an appropriate SELECT statement */
 454     initText(&sSelect);
 455     appendText(&sSelect, "SELECT ", 0);
 456     if( azTCol[0] ){
 457       appendText(&sSelect, azTCol[0], 0);
 458       appendText(&sSelect, ",", 0);
 459     }
 460     for(i=1; azTCol[i]; i++){
 461       appendText(&sSelect, azTCol[i], quoteChar(azTCol[i]));
 462       if( azTCol[i+1] ){
 463         appendText(&sSelect, ",", 0);
 464       }
 465     }
 466     nCol = i;
 467     if( azTCol[0]==0 ) nCol--;
 468     freeColumnList(azTCol);
 469     appendText(&sSelect, " FROM ", 0);
 470     appendText(&sSelect, zTable, quoteChar(zTable));
 471
 472     rc = sqlite3_prepare_v2(p->db, sSelect.z, -1, &pStmt, 0);
 473     if( rc!=SQLITE_OK ){
 474       p->nErr++;
 475       if( p->rc==SQLITE_OK ) p->rc = rc;
 476     }else{
 477       while( SQLITE_ROW==sqlite3_step(pStmt) ){
 478         p->xCallback(sTable.z, p->pArg);
 479         for(i=0; i<nCol; i++){
 480           if( i ) p->xCallback(",", p->pArg);
 481           switch( sqlite3_column_type(pStmt,i) ){
 482             case SQLITE_INTEGER: {
 483               output_formatted(p, "%lld", sqlite3_column_int64(pStmt,i));
 484               break;
 485             }
 486             case SQLITE_FLOAT: {
 487               double r = sqlite3_column_double(pStmt,i);
 488               sqlite3_uint64 ur;
 489               memcpy(&ur,&r,sizeof(r));
 490               if( ur==0x7ff0000000000000LL ){
 491                 p->xCallback("1e999", p->pArg);
 492               }else if( ur==0xfff0000000000000LL ){
 493                 p->xCallback("-1e999", p->pArg);
 494               }else{
 495                 output_formatted(p, "%!.20g", r);
 496               }
 497               break;
 498             }
 499             case SQLITE_NULL: {
 500               p->xCallback("NULL", p->pArg);
 501               break;
 502             }
 503             case SQLITE_TEXT: {
 504               output_quoted_escaped_string(p,
 505                    (const char*)sqlite3_column_text(pStmt,i));
 506               break;
 507             }
 508             case SQLITE_BLOB: {
 509               int nByte = sqlite3_column_bytes(pStmt,i);
 510               unsigned char *a = (unsigned char*)sqlite3_column_blob(pStmt,i);
 511               int j;
 512               p->xCallback("x'", p->pArg);
 513               for(j=0; j<nByte; j++){
 514                 char zWord[3];
 515                 zWord[0] = "0123456789abcdef"[(a[j]>>4)&15];
 516                 zWord[1] = "0123456789abcdef"[a[j]&15];
 517                 zWord[2] = 0;
 518                 p->xCallback(zWord, p->pArg);
 519               }
 520               p->xCallback("'", p->pArg);
 521               break;
 522             }
 523           }
 524         }
 525         p->xCallback(");\n", p->pArg);
 526       }
 527     }
 528     sqlite3_finalize(pStmt);
 529     freeText(&sTable);
 530     freeText(&sSelect);
 531   }
 532   return 0;
 533 }
 534
 535
 536 /*
 537 ** Execute a query statement that will generate SQL output.  Print
 538 ** the result columns, comma-separated, on a line and then add a
 539 ** semicolon terminator to the end of that line.
 540 **
 541 ** If the number of columns is 1 and that column contains text "--"
 542 ** then write the semicolon on a separate line.  That way, if a
 543 ** "--" comment occurs at the end of the statement, the comment
 544 ** won't consume the semicolon terminator.
 545 */
 546 static void output_sql_from_query(
 547   DState *p,               /* Query context */
 548   const char *zSelect,     /* SELECT statement to extract content */
 549   ...
 550 ){
 551   sqlite3_stmt *pSelect;
 552   int rc;
 553   int nResult;
 554   int i;
 555   const char *z;
 556   char *zSql;
 557   va_list ap;
 558   va_start(ap, zSelect);
 559   zSql = sqlite3_vmprintf(zSelect, ap);
 560   va_end(ap);
 561   if( zSql==0 ){
 562     p->rc = SQLITE_NOMEM;
 563     p->nErr++;
 564     return;
 565   }
 566   rc = sqlite3_prepare_v2(p->db, zSql, -1, &pSelect, 0);
 567   sqlite3_free(zSql);
 568   if( rc!=SQLITE_OK || !pSelect ){
 569     output_formatted(p, "/**** ERROR: (%d) %s *****/\n", rc,
 570                 sqlite3_errmsg(p->db));
 571     p->nErr++;
 572     return;
 573   }
 574   rc = sqlite3_step(pSelect);
 575   nResult = sqlite3_column_count(pSelect);
 576   while( rc==SQLITE_ROW ){
 577     z = (const char*)sqlite3_column_text(pSelect, 0);
 578     p->xCallback(z, p->pArg);
 579     for(i=1; i<nResult; i++){
 580       p->xCallback(",", p->pArg);
 581       p->xCallback((const char*)sqlite3_column_text(pSelect,i), p->pArg);
 582     }
 583     if( z==0 ) z = "";
 584     while( z[0] && (z[0]!='-' || z[1]!='-') ) z++;
 585     if( z[0] ){
 586       p->xCallback("\n;\n", p->pArg);
 587     }else{
 588       p->xCallback(";\n", p->pArg);
 589     }
 590     rc = sqlite3_step(pSelect);
 591   }
 592   rc = sqlite3_finalize(pSelect);
 593   if( rc!=SQLITE_OK ){
 594     output_formatted(p, "/**** ERROR: (%d) %s *****/\n", rc,
 595                      sqlite3_errmsg(p->db));
 596     if( (rc&0xff)!=SQLITE_CORRUPT ) p->nErr++;
 597   }
 598 }
 599
 600 /*
 601 ** Run zQuery.  Use dump_callback() as the callback routine so that
 602 ** the contents of the query are output as SQL statements.
 603 **
 604 ** If we get a SQLITE_CORRUPT error, rerun the query after appending
 605 ** "ORDER BY rowid DESC" to the end.
 606 */
 607 static void run_schema_dump_query(
 608   DState *p,
 609   const char *zQuery,
 610   ...
 611 ){
 612   char *zErr = 0;
 613   char *z;
 614   va_list ap;
 615   va_start(ap, zQuery);
 616   z = sqlite3_vmprintf(zQuery, ap);
 617   va_end(ap);
 618   sqlite3_exec(p->db, z, dump_callback, p, &zErr);
 619   sqlite3_free(z);
 620   if( zErr ){
 621     output_formatted(p, "/****** %s ******/\n", zErr);
 622     sqlite3_free(zErr);
 623     p->nErr++;
 624     zErr = 0;
 625   }
 626 }
 627
 628 /*
 629 ** Convert an SQLite database into SQL statements that will recreate that
 630 ** database.
 631 */
 632 int sqlite3_db_dump(
 633   sqlite3 *db,               /* The database connection */
 634   const char *zSchema,       /* Which schema to dump.  Usually "main". */
 635   const char *zTable,        /* Which table to dump.  NULL means everything. */
 636   int (*xCallback)(const char*,void*),   /* Output sent to this callback */
 637   void *pArg                             /* Second argument of the callback */
 638 ){
 639   DState x;
 640   memset(&x, 0, sizeof(x));
 641   x.rc = sqlite3_exec(db, "BEGIN", 0, 0, 0);
 642   if( x.rc ) return x.rc;
 643   x.db = db;
 644   x.xCallback = xCallback;
 645   x.pArg = pArg;
 646   xCallback("PRAGMA foreign_keys=OFF;\nBEGIN TRANSACTION;\n", pArg);
 647   if( zTable==0 ){
 648     run_schema_dump_query(&x,
 649       "SELECT name, type, sql FROM \"%w\".sqlite_schema "
 650       "WHERE sql NOT NULL AND type=='table' AND name!='sqlite_sequence'",
 651       zSchema
 652     );
 653     run_schema_dump_query(&x,
 654       "SELECT name, type, sql FROM \"%w\".sqlite_schema "
 655       "WHERE name=='sqlite_sequence'", zSchema
 656     );
 657     output_sql_from_query(&x,
 658       "SELECT sql FROM sqlite_schema "
 659       "WHERE sql NOT NULL AND type IN ('index','trigger','view')", 0
 660     );
 661   }else{
 662     run_schema_dump_query(&x,
 663       "SELECT name, type, sql FROM \"%w\".sqlite_schema "
 664       "WHERE tbl_name=%Q COLLATE nocase AND type=='table'"
 665       "  AND sql NOT NULL",
 666       zSchema, zTable
 667     );
 668     output_sql_from_query(&x,
 669       "SELECT sql FROM \"%w\".sqlite_schema "
 670       "WHERE sql NOT NULL"
 671       "  AND type IN ('index','trigger','view')"
 672       "  AND tbl_name=%Q COLLATE nocase",
 673       zSchema, zTable
 674     );
 675   }
 676   if( x.writableSchema ){
 677     xCallback("PRAGMA writable_schema=OFF;\n", pArg);
 678   }
 679   xCallback(x.nErr ? "ROLLBACK; -- due to errors\n" : "COMMIT;\n", pArg);
 680   sqlite3_exec(db, "COMMIT", 0, 0, 0);
 681   return x.rc;
 682 }
 683
 684
 685
 686 /* The generic subroutine is above.  The code the follows implements
 687 ** the command-line interface.
 688 */
 689 #ifdef DBDUMP_STANDALONE
 690 #include <stdio.h>
 691
 692 /*
 693 ** Command-line interface
 694 */
 695 int main(int argc, char **argv){
 696   sqlite3 *db;
 697   const char *zDb;
 698   const char *zSchema;
 699   const char *zTable = 0;
 700   int rc;
 701
 702   if( argc<2 || argc>4 ){
 703     fprintf(stderr, "Usage: %s DATABASE ?SCHEMA? ?TABLE?\n", argv[0]);
 704     return 1;
 705   }
 706   zDb = argv[1];
 707   zSchema = argc>=3 ? argv[2] : "main";
 708   zTable = argc==4 ? argv[3] : 0;
 709
 710   rc = sqlite3_open(zDb, &db);
 711   if( rc ){
 712     fprintf(stderr, "Cannot open \"%s\": %s\n", zDb, sqlite3_errmsg(db));
 713     sqlite3_close(db);
 714     return 1;
 715   }
 716   rc = sqlite3_db_dump(db, zSchema, zTable,
 717           (int(*)(const char*,void*))fputs, (void*)stdout);
 718   if( rc ){
 719     fprintf(stderr, "Error: sqlite3_db_dump() returns %d\n", rc);
 720   }
 721   sqlite3_close(db);
 722   return rc!=SQLITE_OK;
 723 }
 724 #endif /* DBDUMP_STANDALONE */