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