/*

** 2015-04-06

**

** The author disclaims copyright to this source code. In place of

** a legal notice, here is a blessing:

**

** May you do good and not evil.

** May you find forgiveness for yourself and forgive others.

** May you share freely, never taking more than you give.

**

*************************************************************************

**

** This is a utility program that computes the differences in content

** between two SQLite databases.

**

** To compile, simply link against SQLite. (Windows builds must also link

** against ext/misc/sqlite3_stdio.c.)

**

** See the showHelp() routine below for a brief description of how to

** run the utility.

*/

#include <stdio.h>

#include <stdlib.h>

#include <stdarg.h>

#include <ctype.h>

#include <string.h>

#include <assert.h>

#include "sqlite3.h"

#include "sqlite3_stdio.h"

/*

** All global variables are gathered into the "g" singleton.

*/

struct GlobalVars {

const char *zArgv0; /* Name of program */

int bSchemaOnly; /* Only show schema differences */

int bSchemaPK; /* Use the schema-defined PK, not the true PK */

int bHandleVtab; /* Handle fts3, fts4, fts5 and rtree vtabs */

unsigned fDebug; /* Debug flags */

int bSchemaCompare; /* Doing single-table sqlite_schema compare */

sqlite3 *db; /* The database connection */

} g;

/*

** Allowed values for g.fDebug

*/

#define DEBUG_COLUMN_NAMES 0x000001

#define DEBUG_DIFF_SQL 0x000002

/*

** Clear and free an sqlite3_str object

*/

static void strFree(sqlite3_str *pStr){

sqlite3_free(sqlite3_str_finish(pStr));

}

/*

** Print an error resulting from faulting command-line arguments and

** abort the program.

*/

static void cmdlineError(const char *zFormat, ...){

sqlite3_str *pOut = sqlite3_str_new(0);

va_list ap;

va_start(ap, zFormat);

sqlite3_str_vappendf(pOut, zFormat, ap);

va_end(ap);

sqlite3_fprintf(stderr, "%s: %s\n", g.zArgv0, sqlite3_str_value(pOut));

strFree(pOut);

sqlite3_fprintf(stderr, "\"%s --help\" for more help\n", g.zArgv0);

exit(1);

}

/*

** Print an error message for an error that occurs at runtime, then

** abort the program.

*/

static void runtimeError(const char *zFormat, ...){

sqlite3_str *pOut = sqlite3_str_new(0);

va_list ap;

va_start(ap, zFormat);

sqlite3_str_vappendf(pOut, zFormat, ap);

va_end(ap);

sqlite3_fprintf(stderr, "%s: %s\n", g.zArgv0, sqlite3_str_value(pOut));

strFree(pOut);

exit(1);

}

/* Safely quote an SQL identifier. Use the minimum amount of transformation

** necessary to allow the string to be used with %s.

**

** Space to hold the returned string is obtained from sqlite3_malloc(). The

** caller is responsible for ensuring this space is freed when no longer

** needed.

*/

static char *safeId(const char *zId){

int i, x;

char c;

if( zId[0]==0 ) return sqlite3_mprintf("\"\"");

for(i=x=0; (c = zId[i])!=0; i++){

if( !isalpha(c) && c!='_' ){

if( i>0 && isdigit(c) ){

x++;

}else{

return sqlite3_mprintf("\"%w\"", zId);

}

}

}

if( x || !sqlite3_keyword_check(zId,i) ){

return sqlite3_mprintf("%s", zId);

}

return sqlite3_mprintf("\"%w\"", zId);

}

/*

** Prepare a new SQL statement. Print an error and abort if anything

** goes wrong.

*/

static sqlite3_stmt *db_vprepare(const char *zFormat, va_list ap){

char *zSql;

int rc;

sqlite3_stmt *pStmt;

zSql = sqlite3_vmprintf(zFormat, ap);

if( zSql==0 ) runtimeError("out of memory");

rc = sqlite3_prepare_v2(g.db, zSql, -1, &pStmt, 0);

if( rc ){

runtimeError("SQL statement error: %s\n\"%s\"", sqlite3_errmsg(g.db),

zSql);

}

sqlite3_free(zSql);

return pStmt;

}

static sqlite3_stmt *db_prepare(const char *zFormat, ...){

va_list ap;

sqlite3_stmt *pStmt;

va_start(ap, zFormat);

pStmt = db_vprepare(zFormat, ap);

va_end(ap);

return pStmt;

}

/*

** Free a list of strings

*/

static void namelistFree(char **az){

if( az ){

int i;

for(i=0; az[i]; i++) sqlite3_free(az[i]);

sqlite3_free(az);

}

}

/*

** Return a list of column names [a] for the table zDb.zTab. Space to

** hold the list is obtained from sqlite3_malloc() and should released

** using namelistFree() when no longer needed.

**

** Primary key columns are listed first, followed by data columns.

** The number of columns in the primary key is returned in *pnPkey.

**

** Normally [a], the "primary key" in the previous sentence is the true

** primary key - the rowid or INTEGER PRIMARY KEY for ordinary tables

** or the declared PRIMARY KEY for WITHOUT ROWID tables. However, if

** the g.bSchemaPK flag is set, then the schema-defined PRIMARY KEY is

** used in all cases. In that case, entries that have NULL values in

** any of their primary key fields will be excluded from the analysis.

**

** If the primary key for a table is the rowid but rowid is inaccessible,

** then this routine returns a NULL pointer.

**

** [a. If the lone, named table is "sqlite_schema", "rootpage" column is

** omitted and the "type" and "name" columns are made to be the PK.]

**

** Examples:

** CREATE TABLE t1(a INT UNIQUE, b INTEGER, c TEXT, PRIMARY KEY(c));

** *pnPKey = 1;

** az = { "rowid", "a", "b", "c", 0 } // Normal case

** az = { "c", "a", "b", 0 } // g.bSchemaPK==1

**

** CREATE TABLE t2(a INT UNIQUE, b INTEGER, c TEXT, PRIMARY KEY(b));

** *pnPKey = 1;

** az = { "b", "a", "c", 0 }

**

** CREATE TABLE t3(x,y,z,PRIMARY KEY(y,z));

** *pnPKey = 1 // Normal case

** az = { "rowid", "x", "y", "z", 0 } // Normal case

** *pnPKey = 2 // g.bSchemaPK==1

** az = { "y", "x", "z", 0 } // g.bSchemaPK==1

**

** CREATE TABLE t4(x,y,z,PRIMARY KEY(y,z)) WITHOUT ROWID;

** *pnPKey = 2

** az = { "y", "z", "x", 0 }

**

** CREATE TABLE t5(rowid,_rowid_,oid);

** az = 0 // The rowid is not accessible

*/

static char **columnNames(

const char *zDb, /* Database ("main" or "aux") to query */

const char *zTab, /* Name of table to return details of */

int *pnPKey, /* OUT: Number of PK columns */

int *pbRowid /* OUT: True if PK is an implicit rowid */

){

char **az = 0; /* List of column names to be returned */

int naz = 0; /* Number of entries in az[] */

sqlite3_stmt *pStmt; /* SQL statement being run */

char *zPkIdxName = 0; /* Name of the PRIMARY KEY index */

int truePk = 0; /* PRAGMA table_info identifies the PK to use */

int nPK = 0; /* Number of PRIMARY KEY columns */

int i, j; /* Loop counters */

if( g.bSchemaPK==0 ){

/* Normal case: Figure out what the true primary key is for the table.

** * For WITHOUT ROWID tables, the true primary key is the same as

** the schema PRIMARY KEY, which is guaranteed to be present.

** * For rowid tables with an INTEGER PRIMARY KEY, the true primary

** key is the INTEGER PRIMARY KEY.

** * For all other rowid tables, the rowid is the true primary key.

*/

pStmt = db_prepare("PRAGMA %s.index_list=%Q", zDb, zTab);

while( SQLITE_ROW==sqlite3_step(pStmt) ){

if( sqlite3_stricmp((const char*)sqlite3_column_text(pStmt,3),"pk")==0 ){

zPkIdxName = sqlite3_mprintf("%s", sqlite3_column_text(pStmt, 1));

break;

}

}

sqlite3_finalize(pStmt);

if( zPkIdxName ){

int nKey = 0;

int nCol = 0;

truePk = 0;

pStmt = db_prepare("PRAGMA %s.index_xinfo=%Q", zDb, zPkIdxName);

while( SQLITE_ROW==sqlite3_step(pStmt) ){

nCol++;

if( sqlite3_column_int(pStmt,5) ){ nKey++; continue; }

if( sqlite3_column_int(pStmt,1)>=0 ) truePk = 1;

}

if( nCol==nKey ) truePk = 1;

if( truePk ){

nPK = nKey;

}else{

nPK = 1;

}

sqlite3_finalize(pStmt);

sqlite3_free(zPkIdxName);

}else{

truePk = 1;

nPK = 1;

}

pStmt = db_prepare("PRAGMA %s.table_info=%Q", zDb, zTab);

}else{

/* The g.bSchemaPK==1 case: Use whatever primary key is declared

** in the schema. The "rowid" will still be used as the primary key

** if the table definition does not contain a PRIMARY KEY.

*/

nPK = 0;

pStmt = db_prepare("PRAGMA %s.table_info=%Q", zDb, zTab);

while( SQLITE_ROW==sqlite3_step(pStmt) ){

if( sqlite3_column_int(pStmt,5)>0 ) nPK++;

}

sqlite3_reset(pStmt);

if( nPK==0 ) nPK = 1;

truePk = 1;

}

if( g.bSchemaCompare ){

assert( sqlite3_stricmp(zTab,"sqlite_schema")==0

|| sqlite3_stricmp(zTab,"sqlite_master")==0 );

/* For sqlite_schema, will use type and name as the PK. */

nPK = 2;

truePk = 0;

}

*pnPKey = nPK;

naz = nPK;

az = sqlite3_malloc( sizeof(char*)*(nPK+1) );

if( az==0 ) runtimeError("out of memory");

memset(az, 0, sizeof(char*)*(nPK+1));

if( g.bSchemaCompare ){

az[0] = sqlite3_mprintf("%s", "type");

az[1] = sqlite3_mprintf("%s", "name");

}

while( SQLITE_ROW==sqlite3_step(pStmt) ){

char * sid = safeId((char*)sqlite3_column_text(pStmt,1));

int iPKey;

if( truePk && (iPKey = sqlite3_column_int(pStmt,5))>0 ){

az[iPKey-1] = sid;

}else{

if( !g.bSchemaCompare

|| !(strcmp(sid,"rootpage")==0

||strcmp(sid,"name")==0

||strcmp(sid,"type")==0)){

az = sqlite3_realloc(az, sizeof(char*)*(naz+2) );

if( az==0 ) runtimeError("out of memory");

az[naz++] = sid;

}

}

}

sqlite3_finalize(pStmt);

if( az ) az[naz] = 0;

/* If it is non-NULL, set *pbRowid to indicate whether or not the PK of

** this table is an implicit rowid (*pbRowid==1) or not (*pbRowid==0). */

if( pbRowid ) *pbRowid = (az[0]==0);

/* If this table has an implicit rowid for a PK, figure out how to refer

** to it. There are usually three options - "rowid", "_rowid_" and "oid".

** Any of these will work, unless the table has an explicit column of the

** same name or the sqlite_schema tables are to be compared. In the latter

** case, pretend that the "true" primary key is the name column, which

** avoids extraneous diffs against the schemas due to rowid variance. */

if( az[0]==0 ){

const char *azRowid[] = { "rowid", "_rowid_", "oid" };

for(i=0; i<sizeof(azRowid)/sizeof(azRowid[0]); i++){

for(j=1; j<naz; j++){

if( sqlite3_stricmp(az[j], azRowid[i])==0 ) break;

}

if( j>=naz ){

az[0] = sqlite3_mprintf("%s", azRowid[i]);

break;

}

}

if( az[0]==0 ){

for(i=1; i<naz; i++) sqlite3_free(az[i]);

sqlite3_free(az);

az = 0;

}

}

return az;

}

/*

** Print the sqlite3_value X as an SQL literal.

*/

static void printQuoted(FILE *out, sqlite3_value *X){

switch( sqlite3_value_type(X) ){

case SQLITE_FLOAT: {

double r1;

char zBuf[50];

r1 = sqlite3_value_double(X);

sqlite3_snprintf(sizeof(zBuf), zBuf, "%!.15g", r1);

sqlite3_fprintf(out, "%s", zBuf);

break;

}

case SQLITE_INTEGER: {

sqlite3_fprintf(out, "%lld", sqlite3_value_int64(X));

break;

}

case SQLITE_BLOB: {

const unsigned char *zBlob = sqlite3_value_blob(X);

int nBlob = sqlite3_value_bytes(X);

if( zBlob ){

int i;

sqlite3_fprintf(out, "x'");

for(i=0; i<nBlob; i++){

sqlite3_fprintf(out, "%02x", zBlob[i]);

}

sqlite3_fprintf(out, "'");

}else{

/* Could be an OOM, could be a zero-byte blob */

sqlite3_fprintf(out, "X''");

}

break;

}

case SQLITE_TEXT: {

const unsigned char *zArg = sqlite3_value_text(X);

if( zArg==0 ){

sqlite3_fprintf(out, "NULL");

}else{

int inctl = 0;

int i, j;

sqlite3_fprintf(out, "'");

for(i=j=0; zArg[i]; i++){

char c = zArg[i];

int ctl = iscntrl((unsigned char)c);

if( ctl>inctl ){

inctl = ctl;

sqlite3_fprintf(out, "%.*s'||X'%02x", i-j, &zArg[j], c);

j = i+1;

}else if( ctl ){

sqlite3_fprintf(out, "%02x", c);

j = i+1;

}else{

if( inctl ){

inctl = 0;

sqlite3_fprintf(out, "'\n||'");

}

if( c=='\'' ){

sqlite3_fprintf(out, "%.*s'", i-j+1, &zArg[j]);

j = i+1;

}

}

}

sqlite3_fprintf(out, "%s'", &zArg[j]);

}

break;

}

case SQLITE_NULL: {

sqlite3_fprintf(out, "NULL");

break;

}

}

}

/*

** Output SQL that will recreate the aux.zTab table.

*/

static void dump_table(const char *zTab, FILE *out){

char *zId = safeId(zTab); /* Name of the table */

char **az = 0; /* List of columns */

int nPk; /* Number of true primary key columns */

int nCol; /* Number of data columns */

int i; /* Loop counter */

sqlite3_stmt *pStmt; /* SQL statement */

const char *zSep; /* Separator string */

sqlite3_str *pIns; /* Beginning of the INSERT statement */

pStmt = db_prepare("SELECT sql FROM aux.sqlite_schema WHERE name=%Q", zTab);

if( SQLITE_ROW==sqlite3_step(pStmt) ){

sqlite3_fprintf(out, "%s;\n", sqlite3_column_text(pStmt,0));

}

sqlite3_finalize(pStmt);

if( !g.bSchemaOnly ){

az = columnNames("aux", zTab, &nPk, 0);

pIns = sqlite3_str_new(0);

if( az==0 ){

pStmt = db_prepare("SELECT * FROM aux.%s", zId);

sqlite3_str_appendf(pIns,"INSERT INTO %s VALUES", zId);

}else{

sqlite3_str *pSql = sqlite3_str_new(0);

zSep = "SELECT";

for(i=0; az[i]; i++){

sqlite3_str_appendf(pSql, "%s %s", zSep, az[i]);

zSep = ",";

}

sqlite3_str_appendf(pSql," FROM aux.%s", zId);

zSep = " ORDER BY";

for(i=1; i<=nPk; i++){

sqlite3_str_appendf(pSql, "%s %d", zSep, i);

zSep = ",";

}

pStmt = db_prepare("%s", sqlite3_str_value(pSql));

strFree(pSql);

sqlite3_str_appendf(pIns, "INSERT INTO %s", zId);

zSep = "(";

for(i=0; az[i]; i++){

sqlite3_str_appendf(pIns, "%s%s", zSep, az[i]);

zSep = ",";

}

sqlite3_str_appendf(pIns,") VALUES");

namelistFree(az);

}

nCol = sqlite3_column_count(pStmt);

while( SQLITE_ROW==sqlite3_step(pStmt) ){

sqlite3_fprintf(out, "%s",sqlite3_str_value(pIns));

zSep = "(";

for(i=0; i<nCol; i++){

sqlite3_fprintf(out, "%s",zSep);

printQuoted(out, sqlite3_column_value(pStmt,i));

zSep = ",";

}

sqlite3_fprintf(out, ");\n");

}

sqlite3_finalize(pStmt);

strFree(pIns);

} /* endif !g.bSchemaOnly */

pStmt = db_prepare("SELECT sql FROM aux.sqlite_schema"

" WHERE type='index' AND tbl_name=%Q AND sql IS NOT NULL",

zTab);

while( SQLITE_ROW==sqlite3_step(pStmt) ){

sqlite3_fprintf(out, "%s;\n", sqlite3_column_text(pStmt,0));

}

sqlite3_finalize(pStmt);

sqlite3_free(zId);

}

/*

** Compute all differences for a single table, except if the

** table name is sqlite_schema, ignore the rootpage column.

*/

static void diff_one_table(const char *zTab, FILE *out){

char *zId = safeId(zTab); /* Name of table (translated for us in SQL) */

char **az = 0; /* Columns in main */

char **az2 = 0; /* Columns in aux */

int nPk; /* Primary key columns in main */

int nPk2; /* Primary key columns in aux */

int n = 0; /* Number of columns in main */

int n2; /* Number of columns in aux */

int nQ; /* Number of output columns in the diff query */

int i; /* Loop counter */

const char *zSep; /* Separator string */

sqlite3_str *pSql; /* Comparison query */

sqlite3_stmt *pStmt; /* Query statement to do the diff */

const char *zLead = /* Becomes line-comment for sqlite_schema */

(g.bSchemaCompare)? "-- " : "";

pSql = sqlite3_str_new(0);

if( g.fDebug==DEBUG_COLUMN_NAMES ){

/* Simply run columnNames() on all tables of the origin

** database and show the results. This is used for testing

** and debugging of the columnNames() function.

*/

az = columnNames("aux",zTab, &nPk, 0);

if( az==0 ){

sqlite3_fprintf(stdout, "Rowid not accessible for %s\n", zId);

}else{

sqlite3_fprintf(stdout, "%s:", zId);

for(i=0; az[i]; i++){

sqlite3_fprintf(stdout, " %s", az[i]);

if( i+1==nPk ) sqlite3_fprintf(stdout, " *");

}

sqlite3_fprintf(stdout, "\n");

}

goto end_diff_one_table;

}

if( sqlite3_table_column_metadata(g.db,"aux",zTab,0,0,0,0,0,0) ){

if( !sqlite3_table_column_metadata(g.db,"main",zTab,0,0,0,0,0,0) ){

/* Table missing from second database. */

if( g.bSchemaCompare )

sqlite3_fprintf(out, "-- 2nd DB has no %s table\n", zTab);

else

sqlite3_fprintf(out, "DROP TABLE %s;\n", zId);

}

goto end_diff_one_table;

}

if( sqlite3_table_column_metadata(g.db,"main",zTab,0,0,0,0,0,0) ){

/* Table missing from source */

if( g.bSchemaCompare ){

sqlite3_fprintf(out, "-- 1st DB has no %s table\n", zTab);

}else{

dump_table(zTab, out);

}

goto end_diff_one_table;

}

az = columnNames("main", zTab, &nPk, 0);

az2 = columnNames("aux", zTab, &nPk2, 0);

if( az && az2 ){

for(n=0; az[n] && az2[n]; n++){

if( sqlite3_stricmp(az[n],az2[n])!=0 ) break;

}

}

if( az==0

|| az2==0

|| nPk!=nPk2

|| az[n]

){

/* Schema mismatch */

sqlite3_fprintf(out, "%sDROP TABLE %s; -- due to schema mismatch\n", zLead, zId);

dump_table(zTab, out);

goto end_diff_one_table;

}

/* Build the comparison query */

for(n2=n; az2[n2]; n2++){

char *zNTab = safeId(az2[n2]);

sqlite3_fprintf(out, "ALTER TABLE %s ADD COLUMN %s;\n", zId, zNTab);

sqlite3_free(zNTab);

}

nQ = nPk2+1+2*(n2-nPk2);

if( n2>nPk2 ){

zSep = "SELECT ";

for(i=0; i<nPk; i++){

sqlite3_str_appendf(pSql, "%sB.%s", zSep, az[i]);

zSep = ", ";

}

sqlite3_str_appendf(pSql, ", 1 /* changed row */");

while( az[i] ){

sqlite3_str_appendf(pSql, ", A.%s IS NOT B.%s, B.%s",

az[i], az2[i], az2[i]);

i++;

}

while( az2[i] ){

sqlite3_str_appendf(pSql, ", B.%s IS NOT NULL, B.%s",

az2[i], az2[i]);

i++;

}

sqlite3_str_appendf(pSql, "\n FROM main.%s A, aux.%s B\n", zId, zId);

zSep = " WHERE";

for(i=0; i<nPk; i++){

sqlite3_str_appendf(pSql, "%s A.%s=B.%s", zSep, az[i], az[i]);

zSep = " AND";

}

zSep = "\n AND (";

while( az[i] ){

sqlite3_str_appendf(pSql, "%sA.%s IS NOT B.%s%s\n",

zSep, az[i], az2[i], az2[i+1]==0 ? ")" : "");

zSep = " OR ";

i++;

}

while( az2[i] ){

sqlite3_str_appendf(pSql, "%sB.%s IS NOT NULL%s\n",

zSep, az2[i], az2[i+1]==0 ? ")" : "");

zSep = " OR ";

i++;

}

sqlite3_str_appendf(pSql, " UNION ALL\n");

}

zSep = "SELECT ";

for(i=0; i<nPk; i++){

sqlite3_str_appendf(pSql, "%sA.%s", zSep, az[i]);

zSep = ", ";

}

sqlite3_str_appendf(pSql, ", 2 /* deleted row */");

while( az2[i] ){

sqlite3_str_appendf(pSql, ", NULL, NULL");

i++;

}

sqlite3_str_appendf(pSql, "\n FROM main.%s A\n", zId);

sqlite3_str_appendf(pSql, " WHERE NOT EXISTS(SELECT 1 FROM aux.%s B\n", zId);

zSep = " WHERE";

for(i=0; i<nPk; i++){

sqlite3_str_appendf(pSql, "%s A.%s=B.%s", zSep, az[i], az[i]);

zSep = " AND";

}

sqlite3_str_appendf(pSql, ")\n");

zSep = " UNION ALL\nSELECT ";

for(i=0; i<nPk; i++){

sqlite3_str_appendf(pSql, "%sB.%s", zSep, az[i]);

zSep = ", ";

}

sqlite3_str_appendf(pSql, ", 3 /* inserted row */");

while( az2[i] ){

sqlite3_str_appendf(pSql, ", 1, B.%s", az2[i]);

i++;

}

sqlite3_str_appendf(pSql, "\n FROM aux.%s B\n", zId);

sqlite3_str_appendf(pSql, " WHERE NOT EXISTS(SELECT 1 FROM main.%s A\n", zId);

zSep = " WHERE";

for(i=0; i<nPk; i++){

sqlite3_str_appendf(pSql, "%s A.%s=B.%s", zSep, az[i], az[i]);

zSep = " AND";

}

sqlite3_str_appendf(pSql, ")\n ORDER BY");

zSep = " ";

for(i=1; i<=nPk; i++){

sqlite3_str_appendf(pSql, "%s%d", zSep, i);

zSep = ", ";

}

sqlite3_str_appendf(pSql, ";\n");

if( g.fDebug & DEBUG_DIFF_SQL ){

printf("SQL for %s:\n%s\n", zId, sqlite3_str_value(pSql));

goto end_diff_one_table;

}

/* Drop indexes that are missing in the destination */

pStmt = db_prepare(

"SELECT name FROM main.sqlite_schema"

" WHERE type='index' AND tbl_name=%Q"

" AND sql IS NOT NULL"

" AND sql NOT IN (SELECT sql FROM aux.sqlite_schema"

" WHERE type='index' AND tbl_name=%Q"

" AND sql IS NOT NULL)",

zTab, zTab);

while( SQLITE_ROW==sqlite3_step(pStmt) ){

char *z = safeId((const char*)sqlite3_column_text(pStmt,0));

sqlite3_fprintf(out, "DROP INDEX %s;\n", z);

sqlite3_free(z);

}

sqlite3_finalize(pStmt);

/* Run the query and output differences */

if( !g.bSchemaOnly ){

pStmt = db_prepare("%s", sqlite3_str_value(pSql));

while( SQLITE_ROW==sqlite3_step(pStmt) ){

int iType = sqlite3_column_int(pStmt, nPk);

if( iType==1 || iType==2 ){

if( iType==1 ){ /* Change the content of a row */

sqlite3_fprintf(out, "%sUPDATE %s", zLead, zId);

zSep = " SET";

for(i=nPk+1; i<nQ; i+=2){

if( sqlite3_column_int(pStmt,i)==0 ) continue;

sqlite3_fprintf(out, "%s %s=", zSep, az2[(i+nPk-1)/2]);

zSep = ",";

printQuoted(out, sqlite3_column_value(pStmt,i+1));

}

}else{ /* Delete a row */

sqlite3_fprintf(out, "%sDELETE FROM %s", zLead, zId);

}

zSep = " WHERE";

for(i=0; i<nPk; i++){

sqlite3_fprintf(out, "%s %s=", zSep, az2[i]);

printQuoted(out, sqlite3_column_value(pStmt,i));

zSep = " AND";

}

sqlite3_fprintf(out, ";\n");

}else{ /* Insert a row */

sqlite3_fprintf(out, "%sINSERT INTO %s(%s", zLead, zId, az2[0]);

for(i=1; az2[i]; i++) sqlite3_fprintf(out, ",%s", az2[i]);

sqlite3_fprintf(out, ") VALUES");

zSep = "(";

for(i=0; i<nPk2; i++){

sqlite3_fprintf(out, "%s", zSep);

zSep = ",";

printQuoted(out, sqlite3_column_value(pStmt,i));

}

for(i=nPk2+2; i<nQ; i+=2){

sqlite3_fprintf(out, ",");

printQuoted(out, sqlite3_column_value(pStmt,i));

}

sqlite3_fprintf(out, ");\n");

}

}

sqlite3_finalize(pStmt);

} /* endif !g.bSchemaOnly */

/* Create indexes that are missing in the source */

pStmt = db_prepare(

"SELECT sql FROM aux.sqlite_schema"

" WHERE type='index' AND tbl_name=%Q"

" AND sql IS NOT NULL"

" AND sql NOT IN (SELECT sql FROM main.sqlite_schema"

" WHERE type='index' AND tbl_name=%Q"

" AND sql IS NOT NULL)",

zTab, zTab);

while( SQLITE_ROW==sqlite3_step(pStmt) ){

sqlite3_fprintf(out, "%s;\n", sqlite3_column_text(pStmt,0));

}

sqlite3_finalize(pStmt);

end_diff_one_table:

strFree(pSql);

sqlite3_free(zId);

namelistFree(az);

namelistFree(az2);

return;

}

/*

** Check that table zTab exists and has the same schema in both the "main"

** and "aux" databases currently opened by the global db handle. If they

** do not, output an error message on stderr and exit(1). Otherwise, if

** the schemas do match, return control to the caller.

*/

static void checkSchemasMatch(const char *zTab){

sqlite3_stmt *pStmt = db_prepare(

"SELECT A.sql=B.sql FROM main.sqlite_schema A, aux.sqlite_schema B"

" WHERE A.name=%Q AND B.name=%Q", zTab, zTab

);

if( SQLITE_ROW==sqlite3_step(pStmt) ){

if( sqlite3_column_int(pStmt,0)==0 ){

runtimeError("schema changes for table %s", safeId(zTab));

}

}else{

runtimeError("table %s missing from one or both databases", safeId(zTab));

}

sqlite3_finalize(pStmt);

}

/**************************************************************************

** The following code is copied from fossil. It is used to generate the

** fossil delta blobs sometimes used in RBU update records.

*/

typedef unsigned short u16;

typedef unsigned int u32;

typedef unsigned char u8;

/*

** The width of a hash window in bytes. The algorithm only works if this

** is a power of 2.

*/

#define NHASH 16

/*

** The current state of the rolling hash.

**

** z[] holds the values that have been hashed. z[] is a circular buffer.

** z[i] is the first entry and z[(i+NHASH-1)%NHASH] is the last entry of

** the window.

**

** Hash.a is the sum of all elements of hash.z[]. Hash.b is a weighted

** sum. Hash.b is z[i]*NHASH + z[i+1]*(NHASH-1) + ... + z[i+NHASH-1]*1.

** (Each index for z[] should be module NHASH, of course. The %NHASH operator

** is omitted in the prior expression for brevity.)

*/

typedef struct hash hash;

struct hash {

u16 a, b; /* Hash values */

u16 i; /* Start of the hash window */

char z[NHASH]; /* The values that have been hashed */

};

/*

** Initialize the rolling hash using the first NHASH characters of z[]

*/

static void hash_init(hash *pHash, const char *z){

u16 a, b, i;

a = b = 0;

for(i=0; i<NHASH; i++){

a += z[i];

b += (NHASH-i)*z[i];

pHash->z[i] = z[i];

}

pHash->a = a & 0xffff;

pHash->b = b & 0xffff;

pHash->i = 0;

}

/*

** Advance the rolling hash by a single character "c"

*/

static void hash_next(hash *pHash, int c){

u16 old = pHash->z[pHash->i];

pHash->z[pHash->i] = (char)c;

pHash->i = (pHash->i+1)&(NHASH-1);

pHash->a = pHash->a - old + (char)c;

pHash->b = pHash->b - NHASH*old + pHash->a;

}

/*

** Return a 32-bit hash value

*/

static u32 hash_32bit(hash *pHash){

return (pHash->a & 0xffff) | (((u32)(pHash->b & 0xffff))<<16);

}

/*

** Write an base-64 integer into the given buffer.

*/

static void putInt(unsigned int v, char **pz){

static const char zDigits[] =

"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwxyz~";

/* 123456789 123456789 123456789 123456789 123456789 123456789 123 */

int i, j;

char zBuf[20];

if( v==0 ){

*(*pz)++ = '0';

return;

}

for(i=0; v>0; i++, v>>=6){

zBuf[i] = zDigits[v&0x3f];

}

for(j=i-1; j>=0; j--){

*(*pz)++ = zBuf[j];

}

}

/*

** Return the number digits in the base-64 representation of a positive integer

*/

static int digit_count(int v){

unsigned int i, x;

for(i=1, x=64; (unsigned int)v>=x; i++, x <<= 6){}

return i;

}

/*

** Compute a 32-bit checksum on the N-byte buffer. Return the result.

*/

static unsigned int checksum(const char *zIn, size_t N){

const unsigned char *z = (const unsigned char *)zIn;

unsigned sum0 = 0;

unsigned sum1 = 0;

unsigned sum2 = 0;

unsigned sum3 = 0;

while(N >= 16){

sum0 += ((unsigned)z[0] + z[4] + z[8] + z[12]);

sum1 += ((unsigned)z[1] + z[5] + z[9] + z[13]);

sum2 += ((unsigned)z[2] + z[6] + z[10]+ z[14]);

sum3 += ((unsigned)z[3] + z[7] + z[11]+ z[15]);

z += 16;

N -= 16;

}

while(N >= 4){

sum0 += z[0];

sum1 += z[1];

sum2 += z[2];

sum3 += z[3];

z += 4;

N -= 4;

}

sum3 += (sum2 << 8) + (sum1 << 16) + (sum0 << 24);

switch(N){

case 3: sum3 += (z[2] << 8);

case 2: sum3 += (z[1] << 16);

case 1: sum3 += (z[0] << 24);

default: ;

}

return sum3;

}

/*

** Create a new delta.

**

** The delta is written into a preallocated buffer, zDelta, which

** should be at least 60 bytes longer than the target file, zOut.

** The delta string will be NUL-terminated, but it might also contain

** embedded NUL characters if either the zSrc or zOut files are

** binary. This function returns the length of the delta string

** in bytes, excluding the final NUL terminator character.

**

** Output Format:

**

** The delta begins with a base64 number followed by a newline. This

** number is the number of bytes in the TARGET file. Thus, given a

** delta file z, a program can compute the size of the output file

** simply by reading the first line and decoding the base-64 number

** found there. The delta_output_size() routine does exactly this.

**

** After the initial size number, the delta consists of a series of

** literal text segments and commands to copy from the SOURCE file.

** A copy command looks like this:

**

** NNN@MMM,

**

** where NNN is the number of bytes to be copied and MMM is the offset

** into the source file of the first byte (both base-64). If NNN is 0

** it means copy the rest of the input file. Literal text is like this:

**

** NNN:TTTTT

**

** where NNN is the number of bytes of text (base-64) and TTTTT is the text.

**

** The last term is of the form

**

** NNN;

**

** In this case, NNN is a 32-bit bigendian checksum of the output file

** that can be used to verify that the delta applied correctly. All

** numbers are in base-64.

**

** Pure text files generate a pure text delta. Binary files generate a

** delta that may contain some binary data.

**

** Algorithm:

**

** The encoder first builds a hash table to help it find matching

** patterns in the source file. 16-byte chunks of the source file

** sampled at evenly spaced intervals are used to populate the hash

** table.

**

** Next we begin scanning the target file using a sliding 16-byte

** window. The hash of the 16-byte window in the target is used to

** search for a matching section in the source file. When a match

** is found, a copy command is added to the delta. An effort is

** made to extend the matching section to regions that come before

** and after the 16-byte hash window. A copy command is only issued

** if the result would use less space that just quoting the text

** literally. Literal text is added to the delta for sections that

** do not match or which can not be encoded efficiently using copy

** commands.

*/

static int rbuDeltaCreate(

const char *zSrc, /* The source or pattern file */

unsigned int lenSrc, /* Length of the source file */

const char *zOut, /* The target file */

unsigned int lenOut, /* Length of the target file */

char *zDelta /* Write the delta into this buffer */

){

unsigned int i, base;

char *zOrigDelta = zDelta;

hash h;

int nHash; /* Number of hash table entries */

int *landmark; /* Primary hash table */

int *collide; /* Collision chain */

int lastRead = -1; /* Last byte of zSrc read by a COPY command */

/* Add the target file size to the beginning of the delta

*/

putInt(lenOut, &zDelta);

*(zDelta++) = '\n';

/* If the source file is very small, it means that we have no

** chance of ever doing a copy command. Just output a single

** literal segment for the entire target and exit.

*/

if( lenSrc<=NHASH ){

putInt(lenOut, &zDelta);

*(zDelta++) = ':';

memcpy(zDelta, zOut, lenOut);

zDelta += lenOut;

putInt(checksum(zOut, lenOut), &zDelta);

*(zDelta++) = ';';

return (int)(zDelta - zOrigDelta);

}

/* Compute the hash table used to locate matching sections in the

** source file.

*/

nHash = lenSrc/NHASH;

collide = sqlite3_malloc( nHash*2*sizeof(int) );

landmark = &collide[nHash];

memset(landmark, -1, nHash*sizeof(int));

memset(collide, -1, nHash*sizeof(int));

for(i=0; i<lenSrc-NHASH; i+=NHASH){

int hv;

hash_init(&h, &zSrc[i]);

hv = hash_32bit(&h) % nHash;

collide[i/NHASH] = landmark[hv];

landmark[hv] = i/NHASH;

}

/* Begin scanning the target file and generating copy commands and

** literal sections of the delta.

*/

base = 0; /* We have already generated everything before zOut[base] */