/ Changes On Branch dbdump
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Changes In Branch dbdump Excluding Merge-Ins

This is equivalent to a diff from 68f6dc7a to 59241a50

2017-03-13
22:10
Add the dbdump.c extension that implements functionality similar to the ".dump" command of the CLI, though in a separate library. (check-in: 2b9980a2 user: drh tags: trunk)
22:02
Add dbdump.exe to the MSVC makefile. (Closed-Leaf check-in: 59241a50 user: drh tags: dbdump)
21:49
Fixes to the dump logic. All appears to be working in preliminary tests. (check-in: 007b11e3 user: drh tags: dbdump)
19:26
Infrastructure for an extension C-library to implement sqlite3_db_dump() and a corresponding "dbdump" command-line utility - both of which do the same work as the ".dump" command of the CLI. (check-in: 74c5ace4 user: drh tags: dbdump)
18:31
Merge all recent changes from trunk. (check-in: 3d04b2cd user: drh tags: apple-osx)
18:24
In the output of the ".dump" command in the CLI, quote newline and carriage-return characters using the char() function, so that they do not get eaten by end-of-line processing logic in the OS or in other command-line utilities and/or libraries. (check-in: 68f6dc7a user: drh tags: trunk)
17:37
Fix the sqlite3TreeViewSelect() routine so that it works with a null pointer to the Select object. (check-in: 9034cf7e user: drh tags: trunk)
2017-03-11
01:56
The output of the ".dump" command in the CLI quotes newline and carriage-return characters using "char(10)" and "char(13)". (Closed-Leaf check-in: 8b2954dd user: drh tags: string-quoting-dump)

Changes to Makefile.in.

  1150   1150   	echo "static const char *zMainloop = " >> $@
  1151   1151   	$(TCLSH_CMD) $(TOP)/tool/tostr.tcl $(TOP)/tool/spaceanal.tcl >> $@
  1152   1152   	echo "; return zMainloop; }" >> $@
  1153   1153   
  1154   1154   sqlite3_analyzer$(TEXE): sqlite3_analyzer.c
  1155   1155   	$(LTLINK) sqlite3_analyzer.c -o $@ $(LIBTCL) $(TLIBS)
  1156   1156   
         1157  +dbdump$(TEXE): $(TOP)/ext/misc/dbdump.c sqlite3.lo
         1158  +	$(LTLINK) -DDBDUMP_STANDALONE -o $@ \
         1159  +           $(TOP)/ext/misc/dbdump.c sqlite3.lo $(TLIBS)
         1160  +
  1157   1161   showdb$(TEXE):	$(TOP)/tool/showdb.c sqlite3.lo
  1158   1162   	$(LTLINK) -o $@ $(TOP)/tool/showdb.c sqlite3.lo $(TLIBS)
  1159   1163   
  1160   1164   showstat4$(TEXE):	$(TOP)/tool/showstat4.c sqlite3.lo
  1161   1165   	$(LTLINK) -o $@ $(TOP)/tool/showstat4.c sqlite3.lo $(TLIBS)
  1162   1166   
  1163   1167   showjournal$(TEXE):	$(TOP)/tool/showjournal.c sqlite3.lo

Changes to Makefile.msc.

  2174   2174   	$(TCLSH_CMD) $(TOP)\tool\tostr.tcl $(TOP)\tool\spaceanal.tcl >> $@
  2175   2175   	echo ; return zMainloop; } >> $@
  2176   2176   
  2177   2177   sqlite3_analyzer.exe:	sqlite3_analyzer.c $(LIBRESOBJS)
  2178   2178   	$(LTLINK) $(NO_WARN) -DBUILD_sqlite -I$(TCLINCDIR) sqlite3_analyzer.c \
  2179   2179   		/link $(LDFLAGS) $(LTLINKOPTS) $(LTLIBPATHS) $(LIBRESOBJS) $(LTLIBS) $(TLIBS)
  2180   2180   
         2181  +dbdump.exe:	$(TOP)\ext\misc\dbdump.c sqlite3.c
         2182  +	$(LTLINK) $(NO_WARN) -DDBDUMP_STANDALONE $(TOP)\ext\misc\dbdump.c sqlite3.c \
         2183  +                /link $(LDFLAGS) $(LTLINKOPTS) $(LTLIBPATHS) $(LIBRESOBJS) $(LTLIBS)
         2184  +
  2181   2185   testloadext.lo:	$(TOP)\src\test_loadext.c
  2182   2186   	$(LTCOMPILE) $(NO_WARN) -c $(TOP)\src\test_loadext.c
  2183   2187   
  2184   2188   testloadext.dll:	testloadext.lo
  2185   2189   	$(LD) $(LDFLAGS) $(LTLINKOPTS) $(LTLIBPATHS) /DLL /OUT:$@ testloadext.lo
  2186   2190   
  2187   2191   showdb.exe:	$(TOP)\tool\showdb.c $(SQLITE3C) $(SQLITE3H)

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

Changes to main.mk.

   757    757   	echo "static const char *zMainloop = " >> $@
   758    758   	tclsh $(TOP)/tool/tostr.tcl $(TOP)/tool/spaceanal.tcl >> $@
   759    759   	echo "; return zMainloop; }" >> $@
   760    760   
   761    761   sqlite3_analyzer$(EXE): sqlite3_analyzer.c
   762    762   	$(TCCX) $(TCL_FLAGS) sqlite3_analyzer.c -o $@ $(LIBTCL) $(THREADLIB) 
   763    763   
          764  +dbdump$(EXE):	$(TOP)/ext/misc/dbdump.c sqlite3.o
          765  +	$(TCCX) -DDBDUMP_STANDALONE -o dbdump$(EXE) \
          766  +            $(TOP)/ext/misc/dbdump.c sqlite3.o $(THREADLIB)
          767  +
   764    768   # Rules to build the 'testfixture' application.
   765    769   #
   766    770   TESTFIXTURE_FLAGS  = -DSQLITE_TEST=1 -DSQLITE_CRASH_TEST=1
   767    771   TESTFIXTURE_FLAGS += -DSQLITE_SERVER=1 -DSQLITE_PRIVATE="" -DSQLITE_CORE
   768    772   TESTFIXTURE_FLAGS += -DSQLITE_SERIES_CONSTRAINT_VERIFY=1
   769    773   TESTFIXTURE_FLAGS += -DSQLITE_DEFAULT_PAGE_SIZE=1024
   770    774