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