1 /* 2 ** 2015-04-06 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 is a utility program that computes the differences in content 14 ** between two SQLite databases. 15 ** 16 ** To compile, simply link against SQLite. 17 ** 18 ** See the showHelp() routine below for a brief description of how to 19 ** run the utility. 20 */ 21 #include <stdio.h> 22 #include <stdlib.h> 23 #include <stdarg.h> 24 #include <ctype.h> 25 #include <string.h> 26 #include <assert.h> 27 #include "sqlite3.h" 28 29 /* 30 ** All global variables are gathered into the "g" singleton. 31 */ 32 struct GlobalVars { 33 const char *zArgv0; /* Name of program */ 34 int bSchemaOnly; /* Only show schema differences */ 35 int bSchemaPK; /* Use the schema-defined PK, not the true PK */ 36 unsigned fDebug; /* Debug flags */ 37 sqlite3 *db; /* The database connection */ 38 } g; 39 40 /* 41 ** Allowed values for g.fDebug 42 */ 43 #define DEBUG_COLUMN_NAMES 0x000001 44 #define DEBUG_DIFF_SQL 0x000002 45 46 /* 47 ** Dynamic string object 48 */ 49 typedef struct Str Str; 50 struct Str { 51 char *z; /* Text of the string */ 52 int nAlloc; /* Bytes allocated in z[] */ 53 int nUsed; /* Bytes actually used in z[] */ 54 }; 55 56 /* 57 ** Initialize a Str object 58 */ 59 static void strInit(Str *p){ 60 p->z = 0; 61 p->nAlloc = 0; 62 p->nUsed = 0; 63 } 64 65 /* 66 ** Print an error resulting from faulting command-line arguments and 67 ** abort the program. 68 */ 69 static void cmdlineError(const char *zFormat, ...){ 70 va_list ap; 71 fprintf(stderr, "%s: ", g.zArgv0); 72 va_start(ap, zFormat); 73 vfprintf(stderr, zFormat, ap); 74 va_end(ap); 75 fprintf(stderr, "\n\"%s --help\" for more help\n", g.zArgv0); 76 exit(1); 77 } 78 79 /* 80 ** Print an error message for an error that occurs at runtime, then 81 ** abort the program. 82 */ 83 static void runtimeError(const char *zFormat, ...){ 84 va_list ap; 85 fprintf(stderr, "%s: ", g.zArgv0); 86 va_start(ap, zFormat); 87 vfprintf(stderr, zFormat, ap); 88 va_end(ap); 89 fprintf(stderr, "\n"); 90 exit(1); 91 } 92 93 /* 94 ** Free all memory held by a Str object 95 */ 96 static void strFree(Str *p){ 97 sqlite3_free(p->z); 98 strInit(p); 99 } 100 101 /* 102 ** Add formatted text to the end of a Str object 103 */ 104 static void strPrintf(Str *p, const char *zFormat, ...){ 105 int nNew; 106 for(;;){ 107 if( p->z ){ 108 va_list ap; 109 va_start(ap, zFormat); 110 sqlite3_vsnprintf(p->nAlloc-p->nUsed, p->z+p->nUsed, zFormat, ap); 111 va_end(ap); 112 nNew = (int)strlen(p->z + p->nUsed); 113 }else{ 114 nNew = p->nAlloc; 115 } 116 if( p->nUsed+nNew < p->nAlloc-1 ){ 117 p->nUsed += nNew; 118 break; 119 } 120 p->nAlloc = p->nAlloc*2 + 1000; 121 p->z = sqlite3_realloc(p->z, p->nAlloc); 122 if( p->z==0 ) runtimeError("out of memory"); 123 } 124 } 125 126 127 128 /* Safely quote an SQL identifier. Use the minimum amount of transformation 129 ** necessary to allow the string to be used with %s. 130 ** 131 ** Space to hold the returned string is obtained from sqlite3_malloc(). The 132 ** caller is responsible for ensuring this space is freed when no longer 133 ** needed. 134 */ 135 static char *safeId(const char *zId){ 136 /* All SQLite keywords, in alphabetical order */ 137 static const char *azKeywords[] = { 138 "ABORT", "ACTION", "ADD", "AFTER", "ALL", "ALTER", "ANALYZE", "AND", "AS", 139 "ASC", "ATTACH", "AUTOINCREMENT", "BEFORE", "BEGIN", "BETWEEN", "BY", 140 "CASCADE", "CASE", "CAST", "CHECK", "COLLATE", "COLUMN", "COMMIT", 141 "CONFLICT", "CONSTRAINT", "CREATE", "CROSS", "CURRENT_DATE", 142 "CURRENT_TIME", "CURRENT_TIMESTAMP", "DATABASE", "DEFAULT", "DEFERRABLE", 143 "DEFERRED", "DELETE", "DESC", "DETACH", "DISTINCT", "DROP", "EACH", 144 "ELSE", "END", "ESCAPE", "EXCEPT", "EXCLUSIVE", "EXISTS", "EXPLAIN", 145 "FAIL", "FOR", "FOREIGN", "FROM", "FULL", "GLOB", "GROUP", "HAVING", "IF", 146 "IGNORE", "IMMEDIATE", "IN", "INDEX", "INDEXED", "INITIALLY", "INNER", 147 "INSERT", "INSTEAD", "INTERSECT", "INTO", "IS", "ISNULL", "JOIN", "KEY", 148 "LEFT", "LIKE", "LIMIT", "MATCH", "NATURAL", "NO", "NOT", "NOTNULL", 149 "NULL", "OF", "OFFSET", "ON", "OR", "ORDER", "OUTER", "PLAN", "PRAGMA", 150 "PRIMARY", "QUERY", "RAISE", "RECURSIVE", "REFERENCES", "REGEXP", 151 "REINDEX", "RELEASE", "RENAME", "REPLACE", "RESTRICT", "RIGHT", 152 "ROLLBACK", "ROW", "SAVEPOINT", "SELECT", "SET", "TABLE", "TEMP", 153 "TEMPORARY", "THEN", "TO", "TRANSACTION", "TRIGGER", "UNION", "UNIQUE", 154 "UPDATE", "USING", "VACUUM", "VALUES", "VIEW", "VIRTUAL", "WHEN", "WHERE", 155 "WITH", "WITHOUT", 156 }; 157 int lwr, upr, mid, c, i, x; 158 if( zId[0]==0 ) return sqlite3_mprintf("\"\""); 159 for(i=x=0; (c = zId[i])!=0; i++){ 160 if( !isalpha(c) && c!='_' ){ 161 if( i>0 && isdigit(c) ){ 162 x++; 163 }else{ 164 return sqlite3_mprintf("\"%w\"", zId); 165 } 166 } 167 } 168 if( x ) return sqlite3_mprintf("%s", zId); 169 lwr = 0; 170 upr = sizeof(azKeywords)/sizeof(azKeywords[0]) - 1; 171 while( lwr<=upr ){ 172 mid = (lwr+upr)/2; 173 c = sqlite3_stricmp(azKeywords[mid], zId); 174 if( c==0 ) return sqlite3_mprintf("\"%w\"", zId); 175 if( c<0 ){ 176 lwr = mid+1; 177 }else{ 178 upr = mid-1; 179 } 180 } 181 return sqlite3_mprintf("%s", zId); 182 } 183 184 /* 185 ** Prepare a new SQL statement. Print an error and abort if anything 186 ** goes wrong. 187 */ 188 static sqlite3_stmt *db_vprepare(const char *zFormat, va_list ap){ 189 char *zSql; 190 int rc; 191 sqlite3_stmt *pStmt; 192 193 zSql = sqlite3_vmprintf(zFormat, ap); 194 if( zSql==0 ) runtimeError("out of memory"); 195 rc = sqlite3_prepare_v2(g.db, zSql, -1, &pStmt, 0); 196 if( rc ){ 197 runtimeError("SQL statement error: %s\n\"%s\"", sqlite3_errmsg(g.db), 198 zSql); 199 } 200 sqlite3_free(zSql); 201 return pStmt; 202 } 203 static sqlite3_stmt *db_prepare(const char *zFormat, ...){ 204 va_list ap; 205 sqlite3_stmt *pStmt; 206 va_start(ap, zFormat); 207 pStmt = db_vprepare(zFormat, ap); 208 va_end(ap); 209 return pStmt; 210 } 211 212 /* 213 ** Free a list of strings 214 */ 215 static void namelistFree(char **az){ 216 if( az ){ 217 int i; 218 for(i=0; az[i]; i++) sqlite3_free(az[i]); 219 sqlite3_free(az); 220 } 221 } 222 223 /* 224 ** Return a list of column names for the table zDb.zTab. Space to 225 ** hold the list is obtained from sqlite3_malloc() and should released 226 ** using namelistFree() when no longer needed. 227 ** 228 ** Primary key columns are listed first, followed by data columns. 229 ** The number of columns in the primary key is returned in *pnPkey. 230 ** 231 ** Normally, the "primary key" in the previous sentence is the true 232 ** primary key - the rowid or INTEGER PRIMARY KEY for ordinary tables 233 ** or the declared PRIMARY KEY for WITHOUT ROWID tables. However, if 234 ** the g.bSchemaPK flag is set, then the schema-defined PRIMARY KEY is 235 ** used in all cases. In that case, entries that have NULL values in 236 ** any of their primary key fields will be excluded from the analysis. 237 ** 238 ** If the primary key for a table is the rowid but rowid is inaccessible, 239 ** then this routine returns a NULL pointer. 240 ** 241 ** Examples: 242 ** CREATE TABLE t1(a INT UNIQUE, b INTEGER, c TEXT, PRIMARY KEY(c)); 243 ** *pnPKey = 1; 244 ** az = { "rowid", "a", "b", "c", 0 } // Normal case 245 ** az = { "c", "a", "b", 0 } // g.bSchemaPK==1 246 ** 247 ** CREATE TABLE t2(a INT UNIQUE, b INTEGER, c TEXT, PRIMARY KEY(b)); 248 ** *pnPKey = 1; 249 ** az = { "b", "a", "c", 0 } 250 ** 251 ** CREATE TABLE t3(x,y,z,PRIMARY KEY(y,z)); 252 ** *pnPKey = 1 // Normal case 253 ** az = { "rowid", "x", "y", "z", 0 } // Normal case 254 ** *pnPKey = 2 // g.bSchemaPK==1 255 ** az = { "y", "x", "z", 0 } // g.bSchemaPK==1 256 ** 257 ** CREATE TABLE t4(x,y,z,PRIMARY KEY(y,z)) WITHOUT ROWID; 258 ** *pnPKey = 2 259 ** az = { "y", "z", "x", 0 } 260 ** 261 ** CREATE TABLE t5(rowid,_rowid_,oid); 262 ** az = 0 // The rowid is not accessible 263 */ 264 static char **columnNames( 265 const char *zDb, /* Database ("main" or "aux") to query */ 266 const char *zTab, /* Name of table to return details of */ 267 int *pnPKey, /* OUT: Number of PK columns */ 268 int *pbRowid /* OUT: True if PK is an implicit rowid */ 269 ){ 270 char **az = 0; /* List of column names to be returned */ 271 int naz = 0; /* Number of entries in az[] */ 272 sqlite3_stmt *pStmt; /* SQL statement being run */ 273 char *zPkIdxName = 0; /* Name of the PRIMARY KEY index */ 274 int truePk = 0; /* PRAGMA table_info indentifies the PK to use */ 275 int nPK = 0; /* Number of PRIMARY KEY columns */ 276 int i, j; /* Loop counters */ 277 278 if( g.bSchemaPK==0 ){ 279 /* Normal case: Figure out what the true primary key is for the table. 280 ** * For WITHOUT ROWID tables, the true primary key is the same as 281 ** the schema PRIMARY KEY, which is guaranteed to be present. 282 ** * For rowid tables with an INTEGER PRIMARY KEY, the true primary 283 ** key is the INTEGER PRIMARY KEY. 284 ** * For all other rowid tables, the rowid is the true primary key. 285 */ 286 pStmt = db_prepare("PRAGMA %s.index_list=%Q", zDb, zTab); 287 while( SQLITE_ROW==sqlite3_step(pStmt) ){ 288 if( sqlite3_stricmp((const char*)sqlite3_column_text(pStmt,3),"pk")==0 ){ 289 zPkIdxName = sqlite3_mprintf("%s", sqlite3_column_text(pStmt, 1)); 290 break; 291 } 292 } 293 sqlite3_finalize(pStmt); 294 if( zPkIdxName ){ 295 int nKey = 0; 296 int nCol = 0; 297 truePk = 0; 298 pStmt = db_prepare("PRAGMA %s.index_xinfo=%Q", zDb, zPkIdxName); 299 while( SQLITE_ROW==sqlite3_step(pStmt) ){ 300 nCol++; 301 if( sqlite3_column_int(pStmt,5) ){ nKey++; continue; } 302 if( sqlite3_column_int(pStmt,1)>=0 ) truePk = 1; 303 } 304 if( nCol==nKey ) truePk = 1; 305 if( truePk ){ 306 nPK = nKey; 307 }else{ 308 nPK = 1; 309 } 310 sqlite3_finalize(pStmt); 311 sqlite3_free(zPkIdxName); 312 }else{ 313 truePk = 1; 314 nPK = 1; 315 } 316 pStmt = db_prepare("PRAGMA %s.table_info=%Q", zDb, zTab); 317 }else{ 318 /* The g.bSchemaPK==1 case: Use whatever primary key is declared 319 ** in the schema. The "rowid" will still be used as the primary key 320 ** if the table definition does not contain a PRIMARY KEY. 321 */ 322 nPK = 0; 323 pStmt = db_prepare("PRAGMA %s.table_info=%Q", zDb, zTab); 324 while( SQLITE_ROW==sqlite3_step(pStmt) ){ 325 if( sqlite3_column_int(pStmt,5)>0 ) nPK++; 326 } 327 sqlite3_reset(pStmt); 328 if( nPK==0 ) nPK = 1; 329 truePk = 1; 330 } 331 *pnPKey = nPK; 332 naz = nPK; 333 az = sqlite3_malloc( sizeof(char*)*(nPK+1) ); 334 if( az==0 ) runtimeError("out of memory"); 335 memset(az, 0, sizeof(char*)*(nPK+1)); 336 while( SQLITE_ROW==sqlite3_step(pStmt) ){ 337 int iPKey; 338 if( truePk && (iPKey = sqlite3_column_int(pStmt,5))>0 ){ 339 az[iPKey-1] = safeId((char*)sqlite3_column_text(pStmt,1)); 340 }else{ 341 az = sqlite3_realloc(az, sizeof(char*)*(naz+2) ); 342 if( az==0 ) runtimeError("out of memory"); 343 az[naz++] = safeId((char*)sqlite3_column_text(pStmt,1)); 344 } 345 } 346 sqlite3_finalize(pStmt); 347 if( az ) az[naz] = 0; 348 349 /* If it is non-NULL, set *pbRowid to indicate whether or not the PK of 350 ** this table is an implicit rowid (*pbRowid==1) or not (*pbRowid==0). */ 351 if( pbRowid ) *pbRowid = (az[0]==0); 352 353 /* If this table has an implicit rowid for a PK, figure out how to refer 354 ** to it. There are three options - "rowid", "_rowid_" and "oid". Any 355 ** of these will work, unless the table has an explicit column of the 356 ** same name. */ 357 if( az[0]==0 ){ 358 const char *azRowid[] = { "rowid", "_rowid_", "oid" }; 359 for(i=0; i<sizeof(azRowid)/sizeof(azRowid[0]); i++){ 360 for(j=1; j<naz; j++){ 361 if( sqlite3_stricmp(az[j], azRowid[i])==0 ) break; 362 } 363 if( j>=naz ){ 364 az[0] = sqlite3_mprintf("%s", azRowid[i]); 365 break; 366 } 367 } 368 if( az[0]==0 ){ 369 for(i=1; i<naz; i++) sqlite3_free(az[i]); 370 sqlite3_free(az); 371 az = 0; 372 } 373 } 374 return az; 375 } 376 377 /* 378 ** Print the sqlite3_value X as an SQL literal. 379 */ 380 static void printQuoted(FILE *out, sqlite3_value *X){ 381 switch( sqlite3_value_type(X) ){ 382 case SQLITE_FLOAT: { 383 double r1; 384 char zBuf[50]; 385 r1 = sqlite3_value_double(X); 386 sqlite3_snprintf(sizeof(zBuf), zBuf, "%!.15g", r1); 387 fprintf(out, "%s", zBuf); 388 break; 389 } 390 case SQLITE_INTEGER: { 391 fprintf(out, "%lld", sqlite3_value_int64(X)); 392 break; 393 } 394 case SQLITE_BLOB: { 395 const unsigned char *zBlob = sqlite3_value_blob(X); 396 int nBlob = sqlite3_value_bytes(X); 397 if( zBlob ){ 398 int i; 399 fprintf(out, "x'"); 400 for(i=0; i<nBlob; i++){ 401 fprintf(out, "%02x", zBlob[i]); 402 } 403 fprintf(out, "'"); 404 }else{ 405 fprintf(out, "NULL"); 406 } 407 break; 408 } 409 case SQLITE_TEXT: { 410 const unsigned char *zArg = sqlite3_value_text(X); 411 int i, j; 412 413 if( zArg==0 ){ 414 fprintf(out, "NULL"); 415 }else{ 416 fprintf(out, "'"); 417 for(i=j=0; zArg[i]; i++){ 418 if( zArg[i]=='\'' ){ 419 fprintf(out, "%.*s'", i-j+1, &zArg[j]); 420 j = i+1; 421 } 422 } 423 fprintf(out, "%s'", &zArg[j]); 424 } 425 break; 426 } 427 case SQLITE_NULL: { 428 fprintf(out, "NULL"); 429 break; 430 } 431 } 432 } 433 434 /* 435 ** Output SQL that will recreate the aux.zTab table. 436 */ 437 static void dump_table(const char *zTab, FILE *out){ 438 char *zId = safeId(zTab); /* Name of the table */ 439 char **az = 0; /* List of columns */ 440 int nPk; /* Number of true primary key columns */ 441 int nCol; /* Number of data columns */ 442 int i; /* Loop counter */ 443 sqlite3_stmt *pStmt; /* SQL statement */ 444 const char *zSep; /* Separator string */ 445 Str ins; /* Beginning of the INSERT statement */ 446 447 pStmt = db_prepare("SELECT sql FROM aux.sqlite_master WHERE name=%Q", zTab); 448 if( SQLITE_ROW==sqlite3_step(pStmt) ){ 449 fprintf(out, "%s;\n", sqlite3_column_text(pStmt,0)); 450 } 451 sqlite3_finalize(pStmt); 452 if( !g.bSchemaOnly ){ 453 az = columnNames("aux", zTab, &nPk, 0); 454 strInit(&ins); 455 if( az==0 ){ 456 pStmt = db_prepare("SELECT * FROM aux.%s", zId); 457 strPrintf(&ins,"INSERT INTO %s VALUES", zId); 458 }else{ 459 Str sql; 460 strInit(&sql); 461 zSep = "SELECT"; 462 for(i=0; az[i]; i++){ 463 strPrintf(&sql, "%s %s", zSep, az[i]); 464 zSep = ","; 465 } 466 strPrintf(&sql," FROM aux.%s", zId); 467 zSep = " ORDER BY"; 468 for(i=1; i<=nPk; i++){ 469 strPrintf(&sql, "%s %d", zSep, i); 470 zSep = ","; 471 } 472 pStmt = db_prepare("%s", sql.z); 473 strFree(&sql); 474 strPrintf(&ins, "INSERT INTO %s", zId); 475 zSep = "("; 476 for(i=0; az[i]; i++){ 477 strPrintf(&ins, "%s%s", zSep, az[i]); 478 zSep = ","; 479 } 480 strPrintf(&ins,") VALUES"); 481 namelistFree(az); 482 } 483 nCol = sqlite3_column_count(pStmt); 484 while( SQLITE_ROW==sqlite3_step(pStmt) ){ 485 fprintf(out, "%s",ins.z); 486 zSep = "("; 487 for(i=0; i<nCol; i++){ 488 fprintf(out, "%s",zSep); 489 printQuoted(out, sqlite3_column_value(pStmt,i)); 490 zSep = ","; 491 } 492 fprintf(out, ");\n"); 493 } 494 sqlite3_finalize(pStmt); 495 strFree(&ins); 496 } /* endif !g.bSchemaOnly */ 497 pStmt = db_prepare("SELECT sql FROM aux.sqlite_master" 498 " WHERE type='index' AND tbl_name=%Q AND sql IS NOT NULL", 499 zTab); 500 while( SQLITE_ROW==sqlite3_step(pStmt) ){ 501 fprintf(out, "%s;\n", sqlite3_column_text(pStmt,0)); 502 } 503 sqlite3_finalize(pStmt); 504 } 505 506 507 /* 508 ** Compute all differences for a single table. 509 */ 510 static void diff_one_table(const char *zTab, FILE *out){ 511 char *zId = safeId(zTab); /* Name of table (translated for us in SQL) */ 512 char **az = 0; /* Columns in main */ 513 char **az2 = 0; /* Columns in aux */ 514 int nPk; /* Primary key columns in main */ 515 int nPk2; /* Primary key columns in aux */ 516 int n = 0; /* Number of columns in main */ 517 int n2; /* Number of columns in aux */ 518 int nQ; /* Number of output columns in the diff query */ 519 int i; /* Loop counter */ 520 const char *zSep; /* Separator string */ 521 Str sql; /* Comparison query */ 522 sqlite3_stmt *pStmt; /* Query statement to do the diff */ 523 524 strInit(&sql); 525 if( g.fDebug==DEBUG_COLUMN_NAMES ){ 526 /* Simply run columnNames() on all tables of the origin 527 ** database and show the results. This is used for testing 528 ** and debugging of the columnNames() function. 529 */ 530 az = columnNames("aux",zTab, &nPk, 0); 531 if( az==0 ){ 532 printf("Rowid not accessible for %s\n", zId); 533 }else{ 534 printf("%s:", zId); 535 for(i=0; az[i]; i++){ 536 printf(" %s", az[i]); 537 if( i+1==nPk ) printf(" *"); 538 } 539 printf("\n"); 540 } 541 goto end_diff_one_table; 542 } 543 544 545 if( sqlite3_table_column_metadata(g.db,"aux",zTab,0,0,0,0,0,0) ){ 546 if( !sqlite3_table_column_metadata(g.db,"main",zTab,0,0,0,0,0,0) ){ 547 /* Table missing from second database. */ 548 fprintf(out, "DROP TABLE %s;\n", zId); 549 } 550 goto end_diff_one_table; 551 } 552 553 if( sqlite3_table_column_metadata(g.db,"main",zTab,0,0,0,0,0,0) ){ 554 /* Table missing from source */ 555 dump_table(zTab, out); 556 goto end_diff_one_table; 557 } 558 559 az = columnNames("main", zTab, &nPk, 0); 560 az2 = columnNames("aux", zTab, &nPk2, 0); 561 if( az && az2 ){ 562 for(n=0; az[n] && az2[n]; n++){ 563 if( sqlite3_stricmp(az[n],az2[n])!=0 ) break; 564 } 565 } 566 if( az==0 567 || az2==0 568 || nPk!=nPk2 569 || az[n] 570 ){ 571 /* Schema mismatch */ 572 fprintf(out, "DROP TABLE %s; -- due to schema mismatch\n", zId); 573 dump_table(zTab, out); 574 goto end_diff_one_table; 575 } 576 577 /* Build the comparison query */ 578 for(n2=n; az2[n2]; n2++){ 579 fprintf(out, "ALTER TABLE %s ADD COLUMN %s;\n", zId, safeId(az2[n2])); 580 } 581 nQ = nPk2+1+2*(n2-nPk2); 582 if( n2>nPk2 ){ 583 zSep = "SELECT "; 584 for(i=0; i<nPk; i++){ 585 strPrintf(&sql, "%sB.%s", zSep, az[i]); 586 zSep = ", "; 587 } 588 strPrintf(&sql, ", 1%s -- changed row\n", nPk==n ? "" : ","); 589 while( az[i] ){ 590 strPrintf(&sql, " A.%s IS NOT B.%s, B.%s%s\n", 591 az[i], az2[i], az2[i], az2[i+1]==0 ? "" : ","); 592 i++; 593 } 594 while( az2[i] ){ 595 strPrintf(&sql, " B.%s IS NOT NULL, B.%s%s\n", 596 az2[i], az2[i], az2[i+1]==0 ? "" : ","); 597 i++; 598 } 599 strPrintf(&sql, " FROM main.%s A, aux.%s B\n", zId, zId); 600 zSep = " WHERE"; 601 for(i=0; i<nPk; i++){ 602 strPrintf(&sql, "%s A.%s=B.%s", zSep, az[i], az[i]); 603 zSep = " AND"; 604 } 605 zSep = "\n AND ("; 606 while( az[i] ){ 607 strPrintf(&sql, "%sA.%s IS NOT B.%s%s\n", 608 zSep, az[i], az2[i], az2[i+1]==0 ? ")" : ""); 609 zSep = " OR "; 610 i++; 611 } 612 while( az2[i] ){ 613 strPrintf(&sql, "%sB.%s IS NOT NULL%s\n", 614 zSep, az2[i], az2[i+1]==0 ? ")" : ""); 615 zSep = " OR "; 616 i++; 617 } 618 strPrintf(&sql, " UNION ALL\n"); 619 } 620 zSep = "SELECT "; 621 for(i=0; i<nPk; i++){ 622 strPrintf(&sql, "%sA.%s", zSep, az[i]); 623 zSep = ", "; 624 } 625 strPrintf(&sql, ", 2%s -- deleted row\n", nPk==n ? "" : ","); 626 while( az2[i] ){ 627 strPrintf(&sql, " NULL, NULL%s\n", i==n2-1 ? "" : ","); 628 i++; 629 } 630 strPrintf(&sql, " FROM main.%s A\n", zId); 631 strPrintf(&sql, " WHERE NOT EXISTS(SELECT 1 FROM aux.%s B\n", zId); 632 zSep = " WHERE"; 633 for(i=0; i<nPk; i++){ 634 strPrintf(&sql, "%s A.%s=B.%s", zSep, az[i], az[i]); 635 zSep = " AND"; 636 } 637 strPrintf(&sql, ")\n"); 638 zSep = " UNION ALL\nSELECT "; 639 for(i=0; i<nPk; i++){ 640 strPrintf(&sql, "%sB.%s", zSep, az[i]); 641 zSep = ", "; 642 } 643 strPrintf(&sql, ", 3%s -- inserted row\n", nPk==n ? "" : ","); 644 while( az2[i] ){ 645 strPrintf(&sql, " 1, B.%s%s\n", az2[i], az2[i+1]==0 ? "" : ","); 646 i++; 647 } 648 strPrintf(&sql, " FROM aux.%s B\n", zId); 649 strPrintf(&sql, " WHERE NOT EXISTS(SELECT 1 FROM main.%s A\n", zId); 650 zSep = " WHERE"; 651 for(i=0; i<nPk; i++){ 652 strPrintf(&sql, "%s A.%s=B.%s", zSep, az[i], az[i]); 653 zSep = " AND"; 654 } 655 strPrintf(&sql, ")\n ORDER BY"); 656 zSep = " "; 657 for(i=1; i<=nPk; i++){ 658 strPrintf(&sql, "%s%d", zSep, i); 659 zSep = ", "; 660 } 661 strPrintf(&sql, ";\n"); 662 663 if( g.fDebug & DEBUG_DIFF_SQL ){ 664 printf("SQL for %s:\n%s\n", zId, sql.z); 665 goto end_diff_one_table; 666 } 667 668 /* Drop indexes that are missing in the destination */ 669 pStmt = db_prepare( 670 "SELECT name FROM main.sqlite_master" 671 " WHERE type='index' AND tbl_name=%Q" 672 " AND sql IS NOT NULL" 673 " AND sql NOT IN (SELECT sql FROM aux.sqlite_master" 674 " WHERE type='index' AND tbl_name=%Q" 675 " AND sql IS NOT NULL)", 676 zTab, zTab); 677 while( SQLITE_ROW==sqlite3_step(pStmt) ){ 678 char *z = safeId((const char*)sqlite3_column_text(pStmt,0)); 679 fprintf(out, "DROP INDEX %s;\n", z); 680 sqlite3_free(z); 681 } 682 sqlite3_finalize(pStmt); 683 684 /* Run the query and output differences */ 685 if( !g.bSchemaOnly ){ 686 pStmt = db_prepare(sql.z); 687 while( SQLITE_ROW==sqlite3_step(pStmt) ){ 688 int iType = sqlite3_column_int(pStmt, nPk); 689 if( iType==1 || iType==2 ){ 690 if( iType==1 ){ /* Change the content of a row */ 691 fprintf(out, "UPDATE %s", zId); 692 zSep = " SET"; 693 for(i=nPk+1; i<nQ; i+=2){ 694 if( sqlite3_column_int(pStmt,i)==0 ) continue; 695 fprintf(out, "%s %s=", zSep, az2[(i+nPk-1)/2]); 696 zSep = ","; 697 printQuoted(out, sqlite3_column_value(pStmt,i+1)); 698 } 699 }else{ /* Delete a row */ 700 fprintf(out, "DELETE FROM %s", zId); 701 } 702 zSep = " WHERE"; 703 for(i=0; i<nPk; i++){ 704 fprintf(out, "%s %s=", zSep, az2[i]); 705 printQuoted(out, sqlite3_column_value(pStmt,i)); 706 zSep = " AND"; 707 } 708 fprintf(out, ";\n"); 709 }else{ /* Insert a row */ 710 fprintf(out, "INSERT INTO %s(%s", zId, az2[0]); 711 for(i=1; az2[i]; i++) fprintf(out, ",%s", az2[i]); 712 fprintf(out, ") VALUES"); 713 zSep = "("; 714 for(i=0; i<nPk2; i++){ 715 fprintf(out, "%s", zSep); 716 zSep = ","; 717 printQuoted(out, sqlite3_column_value(pStmt,i)); 718 } 719 for(i=nPk2+2; i<nQ; i+=2){ 720 fprintf(out, ","); 721 printQuoted(out, sqlite3_column_value(pStmt,i)); 722 } 723 fprintf(out, ");\n"); 724 } 725 } 726 sqlite3_finalize(pStmt); 727 } /* endif !g.bSchemaOnly */ 728 729 /* Create indexes that are missing in the source */ 730 pStmt = db_prepare( 731 "SELECT sql FROM aux.sqlite_master" 732 " WHERE type='index' AND tbl_name=%Q" 733 " AND sql IS NOT NULL" 734 " AND sql NOT IN (SELECT sql FROM main.sqlite_master" 735 " WHERE type='index' AND tbl_name=%Q" 736 " AND sql IS NOT NULL)", 737 zTab, zTab); 738 while( SQLITE_ROW==sqlite3_step(pStmt) ){ 739 fprintf(out, "%s;\n", sqlite3_column_text(pStmt,0)); 740 } 741 sqlite3_finalize(pStmt); 742 743 end_diff_one_table: 744 strFree(&sql); 745 sqlite3_free(zId); 746 namelistFree(az); 747 namelistFree(az2); 748 return; 749 } 750 751 /* 752 ** Check that table zTab exists and has the same schema in both the "main" 753 ** and "aux" databases currently opened by the global db handle. If they 754 ** do not, output an error message on stderr and exit(1). Otherwise, if 755 ** the schemas do match, return control to the caller. 756 */ 757 static void checkSchemasMatch(const char *zTab){ 758 sqlite3_stmt *pStmt = db_prepare( 759 "SELECT A.sql=B.sql FROM main.sqlite_master A, aux.sqlite_master B" 760 " WHERE A.name=%Q AND B.name=%Q", zTab, zTab 761 ); 762 if( SQLITE_ROW==sqlite3_step(pStmt) ){ 763 if( sqlite3_column_int(pStmt,0)==0 ){ 764 runtimeError("schema changes for table %s", safeId(zTab)); 765 } 766 }else{ 767 runtimeError("table %s missing from one or both databases", safeId(zTab)); 768 } 769 sqlite3_finalize(pStmt); 770 } 771 772 /************************************************************************** 773 ** The following code is copied from fossil. It is used to generate the 774 ** fossil delta blobs sometimes used in RBU update records. 775 */ 776 777 typedef unsigned short u16; 778 typedef unsigned int u32; 779 typedef unsigned char u8; 780 781 /* 782 ** The width of a hash window in bytes. The algorithm only works if this 783 ** is a power of 2. 784 */ 785 #define NHASH 16 786 787 /* 788 ** The current state of the rolling hash. 789 ** 790 ** z[] holds the values that have been hashed. z[] is a circular buffer. 791 ** z[i] is the first entry and z[(i+NHASH-1)%NHASH] is the last entry of 792 ** the window. 793 ** 794 ** Hash.a is the sum of all elements of hash.z[]. Hash.b is a weighted 795 ** sum. Hash.b is z[i]*NHASH + z[i+1]*(NHASH-1) + ... + z[i+NHASH-1]*1. 796 ** (Each index for z[] should be module NHASH, of course. The %NHASH operator 797 ** is omitted in the prior expression for brevity.) 798 */ 799 typedef struct hash hash; 800 struct hash { 801 u16 a, b; /* Hash values */ 802 u16 i; /* Start of the hash window */ 803 char z[NHASH]; /* The values that have been hashed */ 804 }; 805 806 /* 807 ** Initialize the rolling hash using the first NHASH characters of z[] 808 */ 809 static void hash_init(hash *pHash, const char *z){ 810 u16 a, b, i; 811 a = b = 0; 812 for(i=0; i<NHASH; i++){ 813 a += z[i]; 814 b += (NHASH-i)*z[i]; 815 pHash->z[i] = z[i]; 816 } 817 pHash->a = a & 0xffff; 818 pHash->b = b & 0xffff; 819 pHash->i = 0; 820 } 821 822 /* 823 ** Advance the rolling hash by a single character "c" 824 */ 825 static void hash_next(hash *pHash, int c){ 826 u16 old = pHash->z[pHash->i]; 827 pHash->z[pHash->i] = (char)c; 828 pHash->i = (pHash->i+1)&(NHASH-1); 829 pHash->a = pHash->a - old + (char)c; 830 pHash->b = pHash->b - NHASH*old + pHash->a; 831 } 832 833 /* 834 ** Return a 32-bit hash value 835 */ 836 static u32 hash_32bit(hash *pHash){ 837 return (pHash->a & 0xffff) | (((u32)(pHash->b & 0xffff))<<16); 838 } 839 840 /* 841 ** Write an base-64 integer into the given buffer. 842 */ 843 static void putInt(unsigned int v, char **pz){ 844 static const char zDigits[] = 845 "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwxyz~"; 846 /* 123456789 123456789 123456789 123456789 123456789 123456789 123 */ 847 int i, j; 848 char zBuf[20]; 849 if( v==0 ){ 850 *(*pz)++ = '0'; 851 return; 852 } 853 for(i=0; v>0; i++, v>>=6){ 854 zBuf[i] = zDigits[v&0x3f]; 855 } 856 for(j=i-1; j>=0; j--){ 857 *(*pz)++ = zBuf[j]; 858 } 859 } 860 861 /* 862 ** Return the number digits in the base-64 representation of a positive integer 863 */ 864 static int digit_count(int v){ 865 unsigned int i, x; 866 for(i=1, x=64; (unsigned int)v>=x; i++, x <<= 6){} 867 return i; 868 } 869 870 /* 871 ** Compute a 32-bit checksum on the N-byte buffer. Return the result. 872 */ 873 static unsigned int checksum(const char *zIn, size_t N){ 874 const unsigned char *z = (const unsigned char *)zIn; 875 unsigned sum0 = 0; 876 unsigned sum1 = 0; 877 unsigned sum2 = 0; 878 unsigned sum3 = 0; 879 while(N >= 16){ 880 sum0 += ((unsigned)z[0] + z[4] + z[8] + z[12]); 881 sum1 += ((unsigned)z[1] + z[5] + z[9] + z[13]); 882 sum2 += ((unsigned)z[2] + z[6] + z[10]+ z[14]); 883 sum3 += ((unsigned)z[3] + z[7] + z[11]+ z[15]); 884 z += 16; 885 N -= 16; 886 } 887 while(N >= 4){ 888 sum0 += z[0]; 889 sum1 += z[1]; 890 sum2 += z[2]; 891 sum3 += z[3]; 892 z += 4; 893 N -= 4; 894 } 895 sum3 += (sum2 << 8) + (sum1 << 16) + (sum0 << 24); 896 switch(N){ 897 case 3: sum3 += (z[2] << 8); 898 case 2: sum3 += (z[1] << 16); 899 case 1: sum3 += (z[0] << 24); 900 default: ; 901 } 902 return sum3; 903 } 904 905 /* 906 ** Create a new delta. 907 ** 908 ** The delta is written into a preallocated buffer, zDelta, which 909 ** should be at least 60 bytes longer than the target file, zOut. 910 ** The delta string will be NUL-terminated, but it might also contain 911 ** embedded NUL characters if either the zSrc or zOut files are 912 ** binary. This function returns the length of the delta string 913 ** in bytes, excluding the final NUL terminator character. 914 ** 915 ** Output Format: 916 ** 917 ** The delta begins with a base64 number followed by a newline. This 918 ** number is the number of bytes in the TARGET file. Thus, given a 919 ** delta file z, a program can compute the size of the output file 920 ** simply by reading the first line and decoding the base-64 number 921 ** found there. The delta_output_size() routine does exactly this. 922 ** 923 ** After the initial size number, the delta consists of a series of 924 ** literal text segments and commands to copy from the SOURCE file. 925 ** A copy command looks like this: 926 ** 927 ** NNN@MMM, 928 ** 929 ** where NNN is the number of bytes to be copied and MMM is the offset 930 ** into the source file of the first byte (both base-64). If NNN is 0 931 ** it means copy the rest of the input file. Literal text is like this: 932 ** 933 ** NNN:TTTTT 934 ** 935 ** where NNN is the number of bytes of text (base-64) and TTTTT is the text. 936 ** 937 ** The last term is of the form 938 ** 939 ** NNN; 940 ** 941 ** In this case, NNN is a 32-bit bigendian checksum of the output file 942 ** that can be used to verify that the delta applied correctly. All 943 ** numbers are in base-64. 944 ** 945 ** Pure text files generate a pure text delta. Binary files generate a 946 ** delta that may contain some binary data. 947 ** 948 ** Algorithm: 949 ** 950 ** The encoder first builds a hash table to help it find matching 951 ** patterns in the source file. 16-byte chunks of the source file 952 ** sampled at evenly spaced intervals are used to populate the hash 953 ** table. 954 ** 955 ** Next we begin scanning the target file using a sliding 16-byte 956 ** window. The hash of the 16-byte window in the target is used to 957 ** search for a matching section in the source file. When a match 958 ** is found, a copy command is added to the delta. An effort is 959 ** made to extend the matching section to regions that come before 960 ** and after the 16-byte hash window. A copy command is only issued 961 ** if the result would use less space that just quoting the text 962 ** literally. Literal text is added to the delta for sections that 963 ** do not match or which can not be encoded efficiently using copy 964 ** commands. 965 */ 966 static int rbuDeltaCreate( 967 const char *zSrc, /* The source or pattern file */ 968 unsigned int lenSrc, /* Length of the source file */ 969 const char *zOut, /* The target file */ 970 unsigned int lenOut, /* Length of the target file */ 971 char *zDelta /* Write the delta into this buffer */ 972 ){ 973 unsigned int i, base; 974 char *zOrigDelta = zDelta; 975 hash h; 976 int nHash; /* Number of hash table entries */ 977 int *landmark; /* Primary hash table */ 978 int *collide; /* Collision chain */ 979 int lastRead = -1; /* Last byte of zSrc read by a COPY command */ 980 981 /* Add the target file size to the beginning of the delta 982 */ 983 putInt(lenOut, &zDelta); 984 *(zDelta++) = '\n'; 985 986 /* If the source file is very small, it means that we have no 987 ** chance of ever doing a copy command. Just output a single 988 ** literal segment for the entire target and exit. 989 */ 990 if( lenSrc<=NHASH ){ 991 putInt(lenOut, &zDelta); 992 *(zDelta++) = ':'; 993 memcpy(zDelta, zOut, lenOut); 994 zDelta += lenOut; 995 putInt(checksum(zOut, lenOut), &zDelta); 996 *(zDelta++) = ';'; 997 return (int)(zDelta - zOrigDelta); 998 } 999 1000 /* Compute the hash table used to locate matching sections in the 1001 ** source file. 1002 */ 1003 nHash = lenSrc/NHASH; 1004 collide = sqlite3_malloc( nHash*2*sizeof(int) ); 1005 landmark = &collide[nHash]; 1006 memset(landmark, -1, nHash*sizeof(int)); 1007 memset(collide, -1, nHash*sizeof(int)); 1008 for(i=0; i<lenSrc-NHASH; i+=NHASH){ 1009 int hv; 1010 hash_init(&h, &zSrc[i]); 1011 hv = hash_32bit(&h) % nHash; 1012 collide[i/NHASH] = landmark[hv]; 1013 landmark[hv] = i/NHASH; 1014 } 1015 1016 /* Begin scanning the target file and generating copy commands and 1017 ** literal sections of the delta. 1018 */ 1019 base = 0; /* We have already generated everything before zOut[base] */ 1020 while( base+NHASH<lenOut ){ 1021 int iSrc, iBlock; 1022 int bestCnt, bestOfst=0, bestLitsz=0; 1023 hash_init(&h, &zOut[base]); 1024 i = 0; /* Trying to match a landmark against zOut[base+i] */ 1025 bestCnt = 0; 1026 while( 1 ){ 1027 int hv; 1028 int limit = 250; 1029 1030 hv = hash_32bit(&h) % nHash; 1031 iBlock = landmark[hv]; 1032 while( iBlock>=0 && (limit--)>0 ){ 1033 /* 1034 ** The hash window has identified a potential match against 1035 ** landmark block iBlock. But we need to investigate further. 1036 ** 1037 ** Look for a region in zOut that matches zSrc. Anchor the search 1038 ** at zSrc[iSrc] and zOut[base+i]. Do not include anything prior to 1039 ** zOut[base] or after zOut[outLen] nor anything after zSrc[srcLen]. 1040 ** 1041 ** Set cnt equal to the length of the match and set ofst so that 1042 ** zSrc[ofst] is the first element of the match. litsz is the number 1043 ** of characters between zOut[base] and the beginning of the match. 1044 ** sz will be the overhead (in bytes) needed to encode the copy 1045 ** command. Only generate copy command if the overhead of the 1046 ** copy command is less than the amount of literal text to be copied. 1047 */ 1048 int cnt, ofst, litsz; 1049 int j, k, x, y; 1050 int sz; 1051 1052 /* Beginning at iSrc, match forwards as far as we can. j counts 1053 ** the number of characters that match */ 1054 iSrc = iBlock*NHASH; 1055 for( 1056 j=0, x=iSrc, y=base+i; 1057 (unsigned int)x<lenSrc && (unsigned int)y<lenOut; 1058 j++, x++, y++ 1059 ){ 1060 if( zSrc[x]!=zOut[y] ) break; 1061 } 1062 j--; 1063 1064 /* Beginning at iSrc-1, match backwards as far as we can. k counts 1065 ** the number of characters that match */ 1066 for(k=1; k<iSrc && (unsigned int)k<=i; k++){ 1067 if( zSrc[iSrc-k]!=zOut[base+i-k] ) break; 1068 } 1069 k--; 1070 1071 /* Compute the offset and size of the matching region */ 1072 ofst = iSrc-k; 1073 cnt = j+k+1; 1074 litsz = i-k; /* Number of bytes of literal text before the copy */ 1075 /* sz will hold the number of bytes needed to encode the "insert" 1076 ** command and the copy command, not counting the "insert" text */ 1077 sz = digit_count(i-k)+digit_count(cnt)+digit_count(ofst)+3; 1078 if( cnt>=sz && cnt>bestCnt ){ 1079 /* Remember this match only if it is the best so far and it 1080 ** does not increase the file size */ 1081 bestCnt = cnt; 1082 bestOfst = iSrc-k; 1083 bestLitsz = litsz; 1084 } 1085 1086 /* Check the next matching block */ 1087 iBlock = collide[iBlock]; 1088 } 1089 1090 /* We have a copy command that does not cause the delta to be larger 1091 ** than a literal insert. So add the copy command to the delta. 1092 */ 1093 if( bestCnt>0 ){ 1094 if( bestLitsz>0 ){ 1095 /* Add an insert command before the copy */ 1096 putInt(bestLitsz,&zDelta); 1097 *(zDelta++) = ':'; 1098 memcpy(zDelta, &zOut[base], bestLitsz); 1099 zDelta += bestLitsz; 1100 base += bestLitsz; 1101 } 1102 base += bestCnt; 1103 putInt(bestCnt, &zDelta); 1104 *(zDelta++) = '@'; 1105 putInt(bestOfst, &zDelta); 1106 *(zDelta++) = ','; 1107 if( bestOfst + bestCnt -1 > lastRead ){ 1108 lastRead = bestOfst + bestCnt - 1; 1109 } 1110 bestCnt = 0; 1111 break; 1112 } 1113 1114 /* If we reach this point, it means no match is found so far */ 1115 if( base+i+NHASH>=lenOut ){ 1116 /* We have reached the end of the file and have not found any 1117 ** matches. Do an "insert" for everything that does not match */ 1118 putInt(lenOut-base, &zDelta); 1119 *(zDelta++) = ':'; 1120 memcpy(zDelta, &zOut[base], lenOut-base); 1121 zDelta += lenOut-base; 1122 base = lenOut; 1123 break; 1124 } 1125 1126 /* Advance the hash by one character. Keep looking for a match */ 1127 hash_next(&h, zOut[base+i+NHASH]); 1128 i++; 1129 } 1130 } 1131 /* Output a final "insert" record to get all the text at the end of 1132 ** the file that does not match anything in the source file. 1133 */ 1134 if( base<lenOut ){ 1135 putInt(lenOut-base, &zDelta); 1136 *(zDelta++) = ':'; 1137 memcpy(zDelta, &zOut[base], lenOut-base); 1138 zDelta += lenOut-base; 1139 } 1140 /* Output the final checksum record. */ 1141 putInt(checksum(zOut, lenOut), &zDelta); 1142 *(zDelta++) = ';'; 1143 sqlite3_free(collide); 1144 return (int)(zDelta - zOrigDelta); 1145 } 1146 1147 /* 1148 ** End of code copied from fossil. 1149 **************************************************************************/ 1150 1151 static void strPrintfArray( 1152 Str *pStr, /* String object to append to */ 1153 const char *zSep, /* Separator string */ 1154 const char *zFmt, /* Format for each entry */ 1155 char **az, int n /* Array of strings & its size (or -1) */ 1156 ){ 1157 int i; 1158 for(i=0; az[i] && (i<n || n<0); i++){ 1159 if( i!=0 ) strPrintf(pStr, "%s", zSep); 1160 strPrintf(pStr, zFmt, az[i], az[i], az[i]); 1161 } 1162 } 1163 1164 static void getRbudiffQuery( 1165 const char *zTab, 1166 char **azCol, 1167 int nPK, 1168 int bOtaRowid, 1169 Str *pSql 1170 ){ 1171 int i; 1172 1173 /* First the newly inserted rows: **/ 1174 strPrintf(pSql, "SELECT "); 1175 strPrintfArray(pSql, ", ", "%s", azCol, -1); 1176 strPrintf(pSql, ", 0, "); /* Set ota_control to 0 for an insert */ 1177 strPrintfArray(pSql, ", ", "NULL", azCol, -1); 1178 strPrintf(pSql, " FROM aux.%Q AS n WHERE NOT EXISTS (\n", zTab); 1179 strPrintf(pSql, " SELECT 1 FROM ", zTab); 1180 strPrintf(pSql, " main.%Q AS o WHERE ", zTab); 1181 strPrintfArray(pSql, " AND ", "(n.%Q IS o.%Q)", azCol, nPK); 1182 strPrintf(pSql, "\n)"); 1183 1184 /* Deleted rows: */ 1185 strPrintf(pSql, "\nUNION ALL\nSELECT "); 1186 strPrintfArray(pSql, ", ", "%s", azCol, nPK); 1187 if( azCol[nPK] ){ 1188 strPrintf(pSql, ", "); 1189 strPrintfArray(pSql, ", ", "NULL", &azCol[nPK], -1); 1190 } 1191 strPrintf(pSql, ", 1, "); /* Set ota_control to 1 for a delete */ 1192 strPrintfArray(pSql, ", ", "NULL", azCol, -1); 1193 strPrintf(pSql, " FROM main.%Q AS n WHERE NOT EXISTS (\n", zTab); 1194 strPrintf(pSql, " SELECT 1 FROM ", zTab); 1195 strPrintf(pSql, " aux.%Q AS o WHERE ", zTab); 1196 strPrintfArray(pSql, " AND ", "(n.%Q IS o.%Q)", azCol, nPK); 1197 strPrintf(pSql, "\n) "); 1198 1199 /* Updated rows. If all table columns are part of the primary key, there 1200 ** can be no updates. In this case this part of the compound SELECT can 1201 ** be omitted altogether. */ 1202 if( azCol[nPK] ){ 1203 strPrintf(pSql, "\nUNION ALL\nSELECT "); 1204 strPrintfArray(pSql, ", ", "n.%s", azCol, nPK); 1205 strPrintf(pSql, ",\n"); 1206 strPrintfArray(pSql, " ,\n", 1207 " CASE WHEN n.%s IS o.%s THEN NULL ELSE n.%s END", &azCol[nPK], -1 1208 ); 1209 1210 if( bOtaRowid==0 ){ 1211 strPrintf(pSql, ", '"); 1212 strPrintfArray(pSql, "", ".", azCol, nPK); 1213 strPrintf(pSql, "' ||\n"); 1214 }else{ 1215 strPrintf(pSql, ",\n"); 1216 } 1217 strPrintfArray(pSql, " ||\n", 1218 " CASE WHEN n.%s IS o.%s THEN '.' ELSE 'x' END", &azCol[nPK], -1 1219 ); 1220 strPrintf(pSql, "\nAS ota_control, "); 1221 strPrintfArray(pSql, ", ", "NULL", azCol, nPK); 1222 strPrintf(pSql, ",\n"); 1223 strPrintfArray(pSql, " ,\n", 1224 " CASE WHEN n.%s IS o.%s THEN NULL ELSE o.%s END", &azCol[nPK], -1 1225 ); 1226 1227 strPrintf(pSql, "\nFROM main.%Q AS o, aux.%Q AS n\nWHERE ", zTab, zTab); 1228 strPrintfArray(pSql, " AND ", "(n.%Q IS o.%Q)", azCol, nPK); 1229 strPrintf(pSql, " AND ota_control LIKE '%%x%%'"); 1230 } 1231 1232 /* Now add an ORDER BY clause to sort everything by PK. */ 1233 strPrintf(pSql, "\nORDER BY "); 1234 for(i=1; i<=nPK; i++) strPrintf(pSql, "%s%d", ((i>1)?", ":""), i); 1235 } 1236 1237 static void rbudiff_one_table(const char *zTab, FILE *out){ 1238 int bOtaRowid; /* True to use an ota_rowid column */ 1239 int nPK; /* Number of primary key columns in table */ 1240 char **azCol; /* NULL terminated array of col names */ 1241 int i; 1242 int nCol; 1243 Str ct = {0, 0, 0}; /* The "CREATE TABLE data_xxx" statement */ 1244 Str sql = {0, 0, 0}; /* Query to find differences */ 1245 Str insert = {0, 0, 0}; /* First part of output INSERT statement */ 1246 sqlite3_stmt *pStmt = 0; 1247 1248 /* --rbu mode must use real primary keys. */ 1249 g.bSchemaPK = 1; 1250 1251 /* Check that the schemas of the two tables match. Exit early otherwise. */ 1252 checkSchemasMatch(zTab); 1253 1254 /* Grab the column names and PK details for the table(s). If no usable PK 1255 ** columns are found, bail out early. */ 1256 azCol = columnNames("main", zTab, &nPK, &bOtaRowid); 1257 if( azCol==0 ){ 1258 runtimeError("table %s has no usable PK columns", zTab); 1259 } 1260 for(nCol=0; azCol[nCol]; nCol++); 1261 1262 /* Build and output the CREATE TABLE statement for the data_xxx table */ 1263 strPrintf(&ct, "CREATE TABLE IF NOT EXISTS 'data_%q'(", zTab); 1264 if( bOtaRowid ) strPrintf(&ct, "rbu_rowid, "); 1265 strPrintfArray(&ct, ", ", "%s", &azCol[bOtaRowid], -1); 1266 strPrintf(&ct, ", rbu_control);"); 1267 1268 /* Get the SQL for the query to retrieve data from the two databases */ 1269 getRbudiffQuery(zTab, azCol, nPK, bOtaRowid, &sql); 1270 1271 /* Build the first part of the INSERT statement output for each row 1272 ** in the data_xxx table. */ 1273 strPrintf(&insert, "INSERT INTO 'data_%q' (", zTab); 1274 if( bOtaRowid ) strPrintf(&insert, "rbu_rowid, "); 1275 strPrintfArray(&insert, ", ", "%s", &azCol[bOtaRowid], -1); 1276 strPrintf(&insert, ", rbu_control) VALUES("); 1277 1278 pStmt = db_prepare("%s", sql.z); 1279 1280 while( sqlite3_step(pStmt)==SQLITE_ROW ){ 1281 1282 /* If this is the first row output, print out the CREATE TABLE 1283 ** statement first. And then set ct.z to NULL so that it is not 1284 ** printed again. */ 1285 if( ct.z ){ 1286 fprintf(out, "%s\n", ct.z); 1287 strFree(&ct); 1288 } 1289 1290 /* Output the first part of the INSERT statement */ 1291 fprintf(out, "%s", insert.z); 1292 1293 if( sqlite3_column_type(pStmt, nCol)==SQLITE_INTEGER ){ 1294 for(i=0; i<=nCol; i++){ 1295 if( i>0 ) fprintf(out, ", "); 1296 printQuoted(out, sqlite3_column_value(pStmt, i)); 1297 } 1298 }else{ 1299 char *zOtaControl; 1300 int nOtaControl = sqlite3_column_bytes(pStmt, nCol); 1301 1302 zOtaControl = (char*)sqlite3_malloc(nOtaControl); 1303 memcpy(zOtaControl, sqlite3_column_text(pStmt, nCol), nOtaControl+1); 1304 1305 for(i=0; i<nCol; i++){ 1306 int bDone = 0; 1307 if( i>=nPK 1308 && sqlite3_column_type(pStmt, i)==SQLITE_BLOB 1309 && sqlite3_column_type(pStmt, nCol+1+i)==SQLITE_BLOB 1310 ){ 1311 const char *aSrc = sqlite3_column_blob(pStmt, nCol+1+i); 1312 int nSrc = sqlite3_column_bytes(pStmt, nCol+1+i); 1313 const char *aFinal = sqlite3_column_blob(pStmt, i); 1314 int nFinal = sqlite3_column_bytes(pStmt, i); 1315 char *aDelta; 1316 int nDelta; 1317 1318 aDelta = sqlite3_malloc(nFinal + 60); 1319 nDelta = rbuDeltaCreate(aSrc, nSrc, aFinal, nFinal, aDelta); 1320 if( nDelta<nFinal ){ 1321 int j; 1322 fprintf(out, "x'"); 1323 for(j=0; j<nDelta; j++) fprintf(out, "%02x", (u8)aDelta[j]); 1324 fprintf(out, "'"); 1325 zOtaControl[i-bOtaRowid] = 'f'; 1326 bDone = 1; 1327 } 1328 sqlite3_free(aDelta); 1329 } 1330 1331 if( bDone==0 ){ 1332 printQuoted(out, sqlite3_column_value(pStmt, i)); 1333 } 1334 fprintf(out, ", "); 1335 } 1336 fprintf(out, "'%s'", zOtaControl); 1337 sqlite3_free(zOtaControl); 1338 } 1339 1340 /* And the closing bracket of the insert statement */ 1341 fprintf(out, ");\n"); 1342 } 1343 1344 sqlite3_finalize(pStmt); 1345 1346 strFree(&ct); 1347 strFree(&sql); 1348 strFree(&insert); 1349 } 1350 1351 /* 1352 ** Display a summary of differences between two versions of the same 1353 ** table table. 1354 ** 1355 ** * Number of rows changed 1356 ** * Number of rows added 1357 ** * Number of rows deleted 1358 ** * Number of identical rows 1359 */ 1360 static void summarize_one_table(const char *zTab, FILE *out){ 1361 char *zId = safeId(zTab); /* Name of table (translated for us in SQL) */ 1362 char **az = 0; /* Columns in main */ 1363 char **az2 = 0; /* Columns in aux */ 1364 int nPk; /* Primary key columns in main */ 1365 int nPk2; /* Primary key columns in aux */ 1366 int n = 0; /* Number of columns in main */ 1367 int n2; /* Number of columns in aux */ 1368 int i; /* Loop counter */ 1369 const char *zSep; /* Separator string */ 1370 Str sql; /* Comparison query */ 1371 sqlite3_stmt *pStmt; /* Query statement to do the diff */ 1372 sqlite3_int64 nUpdate; /* Number of updated rows */ 1373 sqlite3_int64 nUnchanged; /* Number of unmodified rows */ 1374 sqlite3_int64 nDelete; /* Number of deleted rows */ 1375 sqlite3_int64 nInsert; /* Number of inserted rows */ 1376 1377 strInit(&sql); 1378 if( sqlite3_table_column_metadata(g.db,"aux",zTab,0,0,0,0,0,0) ){ 1379 if( !sqlite3_table_column_metadata(g.db,"main",zTab,0,0,0,0,0,0) ){ 1380 /* Table missing from second database. */ 1381 fprintf(out, "%s: missing from second database\n", zTab); 1382 } 1383 goto end_summarize_one_table; 1384 } 1385 1386 if( sqlite3_table_column_metadata(g.db,"main",zTab,0,0,0,0,0,0) ){ 1387 /* Table missing from source */ 1388 fprintf(out, "%s: missing from first database\n", zTab); 1389 goto end_summarize_one_table; 1390 } 1391 1392 az = columnNames("main", zTab, &nPk, 0); 1393 az2 = columnNames("aux", zTab, &nPk2, 0); 1394 if( az && az2 ){ 1395 for(n=0; az[n]; n++){ 1396 if( sqlite3_stricmp(az[n],az2[n])!=0 ) break; 1397 } 1398 } 1399 if( az==0 1400 || az2==0 1401 || nPk!=nPk2 1402 || az[n] 1403 ){ 1404 /* Schema mismatch */ 1405 fprintf(out, "%s: incompatible schema\n", zTab); 1406 goto end_summarize_one_table; 1407 } 1408 1409 /* Build the comparison query */ 1410 for(n2=n; az[n2]; n2++){} 1411 strPrintf(&sql, "SELECT 1, count(*)"); 1412 if( n2==nPk2 ){ 1413 strPrintf(&sql, ", 0\n"); 1414 }else{ 1415 zSep = ", sum("; 1416 for(i=nPk; az[i]; i++){ 1417 strPrintf(&sql, "%sA.%s IS NOT B.%s", zSep, az[i], az[i]); 1418 zSep = " OR "; 1419 } 1420 strPrintf(&sql, ")\n"); 1421 } 1422 strPrintf(&sql, " FROM main.%s A, aux.%s B\n", zId, zId); 1423 zSep = " WHERE"; 1424 for(i=0; i<nPk; i++){ 1425 strPrintf(&sql, "%s A.%s=B.%s", zSep, az[i], az[i]); 1426 zSep = " AND"; 1427 } 1428 strPrintf(&sql, " UNION ALL\n"); 1429 strPrintf(&sql, "SELECT 2, count(*), 0\n"); 1430 strPrintf(&sql, " FROM main.%s A\n", zId); 1431 strPrintf(&sql, " WHERE NOT EXISTS(SELECT 1 FROM aux.%s B ", zId); 1432 zSep = "WHERE"; 1433 for(i=0; i<nPk; i++){ 1434 strPrintf(&sql, "%s A.%s=B.%s", zSep, az[i], az[i]); 1435 zSep = " AND"; 1436 } 1437 strPrintf(&sql, ")\n"); 1438 strPrintf(&sql, " UNION ALL\n"); 1439 strPrintf(&sql, "SELECT 3, count(*), 0\n"); 1440 strPrintf(&sql, " FROM aux.%s B\n", zId); 1441 strPrintf(&sql, " WHERE NOT EXISTS(SELECT 1 FROM main.%s A ", zId); 1442 zSep = "WHERE"; 1443 for(i=0; i<nPk; i++){ 1444 strPrintf(&sql, "%s A.%s=B.%s", zSep, az[i], az[i]); 1445 zSep = " AND"; 1446 } 1447 strPrintf(&sql, ")\n ORDER BY 1;\n"); 1448 1449 if( (g.fDebug & DEBUG_DIFF_SQL)!=0 ){ 1450 printf("SQL for %s:\n%s\n", zId, sql.z); 1451 goto end_summarize_one_table; 1452 } 1453 1454 /* Run the query and output difference summary */ 1455 pStmt = db_prepare(sql.z); 1456 nUpdate = 0; 1457 nInsert = 0; 1458 nDelete = 0; 1459 nUnchanged = 0; 1460 while( SQLITE_ROW==sqlite3_step(pStmt) ){ 1461 switch( sqlite3_column_int(pStmt,0) ){ 1462 case 1: 1463 nUpdate = sqlite3_column_int64(pStmt,2); 1464 nUnchanged = sqlite3_column_int64(pStmt,1) - nUpdate; 1465 break; 1466 case 2: 1467 nDelete = sqlite3_column_int64(pStmt,1); 1468 break; 1469 case 3: 1470 nInsert = sqlite3_column_int64(pStmt,1); 1471 break; 1472 } 1473 } 1474 sqlite3_finalize(pStmt); 1475 fprintf(out, "%s: %lld changes, %lld inserts, %lld deletes, %lld unchanged\n", 1476 zTab, nUpdate, nInsert, nDelete, nUnchanged); 1477 1478 end_summarize_one_table: 1479 strFree(&sql); 1480 sqlite3_free(zId); 1481 namelistFree(az); 1482 namelistFree(az2); 1483 return; 1484 } 1485 1486 /* 1487 ** Write a 64-bit signed integer as a varint onto out 1488 */ 1489 static void putsVarint(FILE *out, sqlite3_uint64 v){ 1490 int i, n; 1491 unsigned char p[12]; 1492 if( v & (((sqlite3_uint64)0xff000000)<<32) ){ 1493 p[8] = (unsigned char)v; 1494 v >>= 8; 1495 for(i=7; i>=0; i--){ 1496 p[i] = (unsigned char)((v & 0x7f) | 0x80); 1497 v >>= 7; 1498 } 1499 fwrite(p, 8, 1, out); 1500 }else{ 1501 n = 9; 1502 do{ 1503 p[n--] = (unsigned char)((v & 0x7f) | 0x80); 1504 v >>= 7; 1505 }while( v!=0 ); 1506 p[9] &= 0x7f; 1507 fwrite(p+n+1, 9-n, 1, out); 1508 } 1509 } 1510 1511 /* 1512 ** Write an SQLite value onto out. 1513 */ 1514 static void putValue(FILE *out, sqlite3_value *pVal){ 1515 int iDType = sqlite3_value_type(pVal); 1516 sqlite3_int64 iX; 1517 double rX; 1518 sqlite3_uint64 uX; 1519 int j; 1520 1521 putc(iDType, out); 1522 switch( iDType ){ 1523 case SQLITE_INTEGER: 1524 iX = sqlite3_value_int64(pVal); 1525 memcpy(&uX, &iX, 8); 1526 for(j=56; j>=0; j-=8) putc((uX>>j)&0xff, out); 1527 break; 1528 case SQLITE_FLOAT: 1529 rX = sqlite3_value_double(pVal); 1530 memcpy(&uX, &rX, 8); 1531 for(j=56; j>=0; j-=8) putc((uX>>j)&0xff, out); 1532 break; 1533 case SQLITE_TEXT: 1534 iX = sqlite3_value_bytes(pVal); 1535 putsVarint(out, (sqlite3_uint64)iX); 1536 fwrite(sqlite3_value_text(pVal),1,(size_t)iX,out); 1537 break; 1538 case SQLITE_BLOB: 1539 iX = sqlite3_value_bytes(pVal); 1540 putsVarint(out, (sqlite3_uint64)iX); 1541 fwrite(sqlite3_value_blob(pVal),1,(size_t)iX,out); 1542 break; 1543 case SQLITE_NULL: 1544 break; 1545 } 1546 } 1547 1548 /* 1549 ** Generate a CHANGESET for all differences from main.zTab to aux.zTab. 1550 */ 1551 static void changeset_one_table(const char *zTab, FILE *out){ 1552 sqlite3_stmt *pStmt; /* SQL statment */ 1553 char *zId = safeId(zTab); /* Escaped name of the table */ 1554 char **azCol = 0; /* List of escaped column names */ 1555 int nCol = 0; /* Number of columns */ 1556 int *aiFlg = 0; /* 0 if column is not part of PK */ 1557 int *aiPk = 0; /* Column numbers for each PK column */ 1558 int nPk = 0; /* Number of PRIMARY KEY columns */ 1559 Str sql; /* SQL for the diff query */ 1560 int i, k; /* Loop counters */ 1561 const char *zSep; /* List separator */ 1562 1563 /* Check that the schemas of the two tables match. Exit early otherwise. */ 1564 checkSchemasMatch(zTab); 1565 1566 pStmt = db_prepare("PRAGMA main.table_info=%Q", zTab); 1567 while( SQLITE_ROW==sqlite3_step(pStmt) ){ 1568 nCol++; 1569 azCol = sqlite3_realloc(azCol, sizeof(char*)*nCol); 1570 if( azCol==0 ) runtimeError("out of memory"); 1571 aiFlg = sqlite3_realloc(aiFlg, sizeof(int)*nCol); 1572 if( aiFlg==0 ) runtimeError("out of memory"); 1573 azCol[nCol-1] = safeId((const char*)sqlite3_column_text(pStmt,1)); 1574 aiFlg[nCol-1] = i = sqlite3_column_int(pStmt,5); 1575 if( i>0 ){ 1576 if( i>nPk ){ 1577 nPk = i; 1578 aiPk = sqlite3_realloc(aiPk, sizeof(int)*nPk); 1579 if( aiPk==0 ) runtimeError("out of memory"); 1580 } 1581 aiPk[i-1] = nCol-1; 1582 } 1583 } 1584 sqlite3_finalize(pStmt); 1585 if( nPk==0 ) goto end_changeset_one_table; 1586 strInit(&sql); 1587 if( nCol>nPk ){ 1588 strPrintf(&sql, "SELECT %d", SQLITE_UPDATE); 1589 for(i=0; i<nCol; i++){ 1590 if( aiFlg[i] ){ 1591 strPrintf(&sql, ",\n A.%s", azCol[i]); 1592 }else{ 1593 strPrintf(&sql, ",\n A.%s IS NOT B.%s, A.%s, B.%s", 1594 azCol[i], azCol[i], azCol[i], azCol[i]); 1595 } 1596 } 1597 strPrintf(&sql,"\n FROM main.%s A, aux.%s B\n", zId, zId); 1598 zSep = " WHERE"; 1599 for(i=0; i<nPk; i++){ 1600 strPrintf(&sql, "%s A.%s=B.%s", zSep, azCol[aiPk[i]], azCol[aiPk[i]]); 1601 zSep = " AND"; 1602 } 1603 zSep = "\n AND ("; 1604 for(i=0; i<nCol; i++){ 1605 if( aiFlg[i] ) continue; 1606 strPrintf(&sql, "%sA.%s IS NOT B.%s", zSep, azCol[i], azCol[i]); 1607 zSep = " OR\n "; 1608 } 1609 strPrintf(&sql,")\n UNION ALL\n"); 1610 } 1611 strPrintf(&sql, "SELECT %d", SQLITE_DELETE); 1612 for(i=0; i<nCol; i++){ 1613 if( aiFlg[i] ){ 1614 strPrintf(&sql, ",\n A.%s", azCol[i]); 1615 }else{ 1616 strPrintf(&sql, ",\n 1, A.%s, NULL", azCol[i]); 1617 } 1618 } 1619 strPrintf(&sql, "\n FROM main.%s A\n", zId); 1620 strPrintf(&sql, " WHERE NOT EXISTS(SELECT 1 FROM aux.%s B\n", zId); 1621 zSep = " WHERE"; 1622 for(i=0; i<nPk; i++){ 1623 strPrintf(&sql, "%s A.%s=B.%s", zSep, azCol[aiPk[i]], azCol[aiPk[i]]); 1624 zSep = " AND"; 1625 } 1626 strPrintf(&sql, ")\n UNION ALL\n"); 1627 strPrintf(&sql, "SELECT %d", SQLITE_INSERT); 1628 for(i=0; i<nCol; i++){ 1629 if( aiFlg[i] ){ 1630 strPrintf(&sql, ",\n B.%s", azCol[i]); 1631 }else{ 1632 strPrintf(&sql, ",\n 1, NULL, B.%s", azCol[i]); 1633 } 1634 } 1635 strPrintf(&sql, "\n FROM aux.%s B\n", zId); 1636 strPrintf(&sql, " WHERE NOT EXISTS(SELECT 1 FROM main.%s A\n", zId); 1637 zSep = " WHERE"; 1638 for(i=0; i<nPk; i++){ 1639 strPrintf(&sql, "%s A.%s=B.%s", zSep, azCol[aiPk[i]], azCol[aiPk[i]]); 1640 zSep = " AND"; 1641 } 1642 strPrintf(&sql, ")\n"); 1643 strPrintf(&sql, " ORDER BY"); 1644 zSep = " "; 1645 for(i=0; i<nPk; i++){ 1646 strPrintf(&sql, "%s %d", zSep, aiPk[i]+2); 1647 zSep = ","; 1648 } 1649 strPrintf(&sql, ";\n"); 1650 1651 if( g.fDebug & DEBUG_DIFF_SQL ){ 1652 printf("SQL for %s:\n%s\n", zId, sql.z); 1653 goto end_changeset_one_table; 1654 } 1655 1656 putc('T', out); 1657 putsVarint(out, (sqlite3_uint64)nCol); 1658 for(i=0; i<nCol; i++) putc(aiFlg[i]!=0, out); 1659 fwrite(zTab, 1, strlen(zTab), out); 1660 putc(0, out); 1661 1662 pStmt = db_prepare("%s", sql.z); 1663 while( SQLITE_ROW==sqlite3_step(pStmt) ){ 1664 int iType = sqlite3_column_int(pStmt,0); 1665 putc(iType, out); 1666 putc(0, out); 1667 switch( sqlite3_column_int(pStmt,0) ){ 1668 case SQLITE_UPDATE: { 1669 for(k=1, i=0; i<nCol; i++){ 1670 if( aiFlg[i] ){ 1671 putValue(out, sqlite3_column_value(pStmt,k)); 1672 k++; 1673 }else if( sqlite3_column_int(pStmt,k) ){ 1674 putValue(out, sqlite3_column_value(pStmt,k+1)); 1675 k += 3; 1676 }else{ 1677 putc(0, out); 1678 k += 3; 1679 } 1680 } 1681 for(k=1, i=0; i<nCol; i++){ 1682 if( aiFlg[i] ){ 1683 putc(0, out); 1684 k++; 1685 }else if( sqlite3_column_int(pStmt,k) ){ 1686 putValue(out, sqlite3_column_value(pStmt,k+2)); 1687 k += 3; 1688 }else{ 1689 putc(0, out); 1690 k += 3; 1691 } 1692 } 1693 break; 1694 } 1695 case SQLITE_INSERT: { 1696 for(k=1, i=0; i<nCol; i++){ 1697 if( aiFlg[i] ){ 1698 putValue(out, sqlite3_column_value(pStmt,k)); 1699 k++; 1700 }else{ 1701 putValue(out, sqlite3_column_value(pStmt,k+2)); 1702 k += 3; 1703 } 1704 } 1705 break; 1706 } 1707 case SQLITE_DELETE: { 1708 for(k=1, i=0; i<nCol; i++){ 1709 if( aiFlg[i] ){ 1710 putValue(out, sqlite3_column_value(pStmt,k)); 1711 k++; 1712 }else{ 1713 putValue(out, sqlite3_column_value(pStmt,k+1)); 1714 k += 3; 1715 } 1716 } 1717 break; 1718 } 1719 } 1720 } 1721 sqlite3_finalize(pStmt); 1722 1723 end_changeset_one_table: 1724 while( nCol>0 ) sqlite3_free(azCol[--nCol]); 1725 sqlite3_free(azCol); 1726 sqlite3_free(aiPk); 1727 sqlite3_free(zId); 1728 } 1729 1730 /* 1731 ** Print sketchy documentation for this utility program 1732 */ 1733 static void showHelp(void){ 1734 printf("Usage: %s [options] DB1 DB2\n", g.zArgv0); 1735 printf( 1736 "Output SQL text that would transform DB1 into DB2.\n" 1737 "Options:\n" 1738 " --changeset FILE Write a CHANGESET into FILE\n" 1739 " -L|--lib LIBRARY Load an SQLite extension library\n" 1740 " --primarykey Use schema-defined PRIMARY KEYs\n" 1741 " --rbu Output SQL to create/populate RBU table(s)\n" 1742 " --schema Show only differences in the schema\n" 1743 " --summary Show only a summary of the differences\n" 1744 " --table TAB Show only differences in table TAB\n" 1745 " --transaction Show SQL output inside a transaction\n" 1746 ); 1747 } 1748 1749 int main(int argc, char **argv){ 1750 const char *zDb1 = 0; 1751 const char *zDb2 = 0; 1752 int i; 1753 int rc; 1754 char *zErrMsg = 0; 1755 char *zSql; 1756 sqlite3_stmt *pStmt; 1757 char *zTab = 0; 1758 FILE *out = stdout; 1759 void (*xDiff)(const char*,FILE*) = diff_one_table; 1760 int nExt = 0; 1761 char **azExt = 0; 1762 int useTransaction = 0; 1763 int neverUseTransaction = 0; 1764 1765 g.zArgv0 = argv[0]; 1766 sqlite3_config(SQLITE_CONFIG_SINGLETHREAD); 1767 for(i=1; i<argc; i++){ 1768 const char *z = argv[i]; 1769 if( z[0]=='-' ){ 1770 z++; 1771 if( z[0]=='-' ) z++; 1772 if( strcmp(z,"changeset")==0 ){ 1773 if( i==argc-1 ) cmdlineError("missing argument to %s", argv[i]); 1774 out = fopen(argv[++i], "wb"); 1775 if( out==0 ) cmdlineError("cannot open: %s", argv[i]); 1776 xDiff = changeset_one_table; 1777 neverUseTransaction = 1; 1778 }else 1779 if( strcmp(z,"debug")==0 ){ 1780 if( i==argc-1 ) cmdlineError("missing argument to %s", argv[i]); 1781 g.fDebug = strtol(argv[++i], 0, 0); 1782 }else 1783 if( strcmp(z,"help")==0 ){ 1784 showHelp(); 1785 return 0; 1786 }else 1787 #ifndef SQLITE_OMIT_LOAD_EXTENSION 1788 if( strcmp(z,"lib")==0 || strcmp(z,"L")==0 ){ 1789 if( i==argc-1 ) cmdlineError("missing argument to %s", argv[i]); 1790 azExt = realloc(azExt, sizeof(azExt[0])*(nExt+1)); 1791 if( azExt==0 ) cmdlineError("out of memory"); 1792 azExt[nExt++] = argv[++i]; 1793 }else 1794 #endif 1795 if( strcmp(z,"primarykey")==0 ){ 1796 g.bSchemaPK = 1; 1797 }else 1798 if( strcmp(z,"rbu")==0 ){ 1799 xDiff = rbudiff_one_table; 1800 }else 1801 if( strcmp(z,"schema")==0 ){ 1802 g.bSchemaOnly = 1; 1803 }else 1804 if( strcmp(z,"summary")==0 ){ 1805 xDiff = summarize_one_table; 1806 }else 1807 if( strcmp(z,"table")==0 ){ 1808 if( i==argc-1 ) cmdlineError("missing argument to %s", argv[i]); 1809 zTab = argv[++i]; 1810 }else 1811 if( strcmp(z,"transaction")==0 ){ 1812 useTransaction = 1; 1813 }else 1814 { 1815 cmdlineError("unknown option: %s", argv[i]); 1816 } 1817 }else if( zDb1==0 ){ 1818 zDb1 = argv[i]; 1819 }else if( zDb2==0 ){ 1820 zDb2 = argv[i]; 1821 }else{ 1822 cmdlineError("unknown argument: %s", argv[i]); 1823 } 1824 } 1825 if( zDb2==0 ){ 1826 cmdlineError("two database arguments required"); 1827 } 1828 rc = sqlite3_open(zDb1, &g.db); 1829 if( rc ){ 1830 cmdlineError("cannot open database file \"%s\"", zDb1); 1831 } 1832 rc = sqlite3_exec(g.db, "SELECT * FROM sqlite_master", 0, 0, &zErrMsg); 1833 if( rc || zErrMsg ){ 1834 cmdlineError("\"%s\" does not appear to be a valid SQLite database", zDb1); 1835 } 1836 #ifndef SQLITE_OMIT_LOAD_EXTENSION 1837 sqlite3_enable_load_extension(g.db, 1); 1838 for(i=0; i<nExt; i++){ 1839 rc = sqlite3_load_extension(g.db, azExt[i], 0, &zErrMsg); 1840 if( rc || zErrMsg ){ 1841 cmdlineError("error loading %s: %s", azExt[i], zErrMsg); 1842 } 1843 } 1844 #endif 1845 free(azExt); 1846 zSql = sqlite3_mprintf("ATTACH %Q as aux;", zDb2); 1847 rc = sqlite3_exec(g.db, zSql, 0, 0, &zErrMsg); 1848 if( rc || zErrMsg ){ 1849 cmdlineError("cannot attach database \"%s\"", zDb2); 1850 } 1851 rc = sqlite3_exec(g.db, "SELECT * FROM aux.sqlite_master", 0, 0, &zErrMsg); 1852 if( rc || zErrMsg ){ 1853 cmdlineError("\"%s\" does not appear to be a valid SQLite database", zDb2); 1854 } 1855 1856 if( neverUseTransaction ) useTransaction = 0; 1857 if( useTransaction ) printf("BEGIN TRANSACTION;\n"); 1858 if( zTab ){ 1859 xDiff(zTab, out); 1860 }else{ 1861 /* Handle tables one by one */ 1862 pStmt = db_prepare( 1863 "SELECT name FROM main.sqlite_master\n" 1864 " WHERE type='table' AND sql NOT LIKE 'CREATE VIRTUAL%%'\n" 1865 " UNION\n" 1866 "SELECT name FROM aux.sqlite_master\n" 1867 " WHERE type='table' AND sql NOT LIKE 'CREATE VIRTUAL%%'\n" 1868 " ORDER BY name" 1869 ); 1870 while( SQLITE_ROW==sqlite3_step(pStmt) ){ 1871 xDiff((const char*)sqlite3_column_text(pStmt,0), out); 1872 } 1873 sqlite3_finalize(pStmt); 1874 } 1875 if( useTransaction ) printf("COMMIT;\n"); 1876 1877 /* TBD: Handle trigger differences */ 1878 /* TBD: Handle view differences */ 1879 sqlite3_close(g.db); 1880 return 0; 1881 } 1882