[sql] Import SQLite 3.17.0.

third_party/sqlite/src/tool/sqldiff.c

 /*
 ** 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.
 **
@@ skipping 15 matching lines @@
 #include <assert.h>
 #include "sqlite3.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 */
   sqlite3 *db;              /* The database connection */
 } g;
 
 /*
 ** Allowed values for g.fDebug
 */
 #define DEBUG_COLUMN_NAMES  0x000001
 #define DEBUG_DIFF_SQL      0x000002
 
@@ skipping 349 matching lines @@
       const unsigned char *zBlob = sqlite3_value_blob(X);
       int nBlob = sqlite3_value_bytes(X);
       if( zBlob ){
         int i;
         fprintf(out, "x'");
         for(i=0; i<nBlob; i++){
           fprintf(out, "%02x", zBlob[i]);
         }
         fprintf(out, "'");
       }else{
-        fprintf(out, "NULL");
+        /* Could be an OOM, could be a zero-byte blob */
+        fprintf(out, "X''");
       }
       break;
     }
     case SQLITE_TEXT: {
       const unsigned char *zArg = sqlite3_value_text(X);
       int i, j;
 
       if( zArg==0 ){
         fprintf(out, "NULL");
       }else{
@@ skipping 260 matching lines @@
     zTab, zTab);
   while( SQLITE_ROW==sqlite3_step(pStmt) ){
     char *z = safeId((const char*)sqlite3_column_text(pStmt,0));
     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(sql.z);
+    pStmt = db_prepare("%s", sql.z);
     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 */
           fprintf(out, "UPDATE %s", zId);
           zSep = " SET";
           for(i=nPk+1; i<nQ; i+=2){
             if( sqlite3_column_int(pStmt,i)==0 ) continue;
             fprintf(out, "%s %s=", zSep, az2[(i+nPk-1)/2]);
             zSep = ",";
@@ skipping 290 matching lines @@
   ** 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 zDelta - zOrigDelta;
+    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));
@@ skipping 126 matching lines @@
   if( base<lenOut ){
     putInt(lenOut-base, &zDelta);
     *(zDelta++) = ':';
     memcpy(zDelta, &zOut[base], lenOut-base);
     zDelta += lenOut-base;
   }
   /* Output the final checksum record. */
   putInt(checksum(zOut, lenOut), &zDelta);
   *(zDelta++) = ';';
   sqlite3_free(collide);
-  return zDelta - zOrigDelta;
+  return (int)(zDelta - zOrigDelta);
 }
 
 /*
 ** End of code copied from fossil.
 **************************************************************************/
 
 static void strPrintfArray(
   Str *pStr,                      /* String object to append to */
   const char *zSep,               /* Separator string */
   const char *zFmt,               /* Format for each entry */
@@ skipping 16 matching lines @@
   int i;
 
   /* First the newly inserted rows: **/ 
   strPrintf(pSql, "SELECT ");
   strPrintfArray(pSql, ", ", "%s", azCol, -1);
   strPrintf(pSql, ", 0, ");       /* Set ota_control to 0 for an insert */
   strPrintfArray(pSql, ", ", "NULL", azCol, -1);
   strPrintf(pSql, " FROM aux.%Q AS n WHERE NOT EXISTS (\n", zTab);
   strPrintf(pSql, "    SELECT 1 FROM ", zTab);
   strPrintf(pSql, " main.%Q AS o WHERE ", zTab);
-  strPrintfArray(pSql, " AND ", "(n.%Q IS o.%Q)", azCol, nPK);
-  strPrintf(pSql, "\n)");
+  strPrintfArray(pSql, " AND ", "(n.%Q = o.%Q)", azCol, nPK);
+  strPrintf(pSql, "\n) AND ");
+  strPrintfArray(pSql, " AND ", "(n.%Q IS NOT NULL)", azCol, nPK);
 
   /* Deleted rows: */
   strPrintf(pSql, "\nUNION ALL\nSELECT ");
   strPrintfArray(pSql, ", ", "%s", azCol, nPK);
   if( azCol[nPK] ){
     strPrintf(pSql, ", ");
     strPrintfArray(pSql, ", ", "NULL", &azCol[nPK], -1);
   }
   strPrintf(pSql, ", 1, ");       /* Set ota_control to 1 for a delete */
   strPrintfArray(pSql, ", ", "NULL", azCol, -1);
   strPrintf(pSql, " FROM main.%Q AS n WHERE NOT EXISTS (\n", zTab);
   strPrintf(pSql, "    SELECT 1 FROM ", zTab);
   strPrintf(pSql, " aux.%Q AS o WHERE ", zTab);
-  strPrintfArray(pSql, " AND ", "(n.%Q IS o.%Q)", azCol, nPK);
-  strPrintf(pSql, "\n) ");
+  strPrintfArray(pSql, " AND ", "(n.%Q = o.%Q)", azCol, nPK);
+  strPrintf(pSql, "\n) AND ");
+  strPrintfArray(pSql, " AND ", "(n.%Q IS NOT NULL)", azCol, nPK);
 
   /* Updated rows. If all table columns are part of the primary key, there 
   ** can be no updates. In this case this part of the compound SELECT can
   ** be omitted altogether. */
   if( azCol[nPK] ){
     strPrintf(pSql, "\nUNION ALL\nSELECT ");
     strPrintfArray(pSql, ", ", "n.%s", azCol, nPK);
     strPrintf(pSql, ",\n");
     strPrintfArray(pSql, " ,\n", 
         "    CASE WHEN n.%s IS o.%s THEN NULL ELSE n.%s END", &azCol[nPK], -1
@@ skipping 10 matching lines @@
         "    CASE WHEN n.%s IS o.%s THEN '.' ELSE 'x' END", &azCol[nPK], -1
     );
     strPrintf(pSql, "\nAS ota_control, ");
     strPrintfArray(pSql, ", ", "NULL", azCol, nPK);
     strPrintf(pSql, ",\n");
     strPrintfArray(pSql, " ,\n", 
         "    CASE WHEN n.%s IS o.%s THEN NULL ELSE o.%s END", &azCol[nPK], -1
     );
 
     strPrintf(pSql, "\nFROM main.%Q AS o, aux.%Q AS n\nWHERE ", zTab, zTab);
-    strPrintfArray(pSql, " AND ", "(n.%Q IS o.%Q)", azCol, nPK);
+    strPrintfArray(pSql, " AND ", "(n.%Q = o.%Q)", azCol, nPK);
     strPrintf(pSql, " AND ota_control LIKE '%%x%%'");
   }
 
   /* Now add an ORDER BY clause to sort everything by PK. */
   strPrintf(pSql, "\nORDER BY ");
   for(i=1; i<=nPK; i++) strPrintf(pSql, "%s%d", ((i>1)?", ":""), i);
 }
 
 static void rbudiff_one_table(const char *zTab, FILE *out){
   int bOtaRowid;                  /* True to use an ota_rowid column */
   int nPK;                        /* Number of primary key columns in table */
   char **azCol;                   /* NULL terminated array of col names */
   int i;
   int nCol;
   Str ct = {0, 0, 0};             /* The "CREATE TABLE data_xxx" statement */
   Str sql = {0, 0, 0};            /* Query to find differences */
   Str insert = {0, 0, 0};         /* First part of output INSERT statement */
   sqlite3_stmt *pStmt = 0;
+  int nRow = 0;                   /* Total rows in data_xxx table */
 
   /* --rbu mode must use real primary keys. */
   g.bSchemaPK = 1;
 
   /* Check that the schemas of the two tables match. Exit early otherwise. */
   checkSchemasMatch(zTab);
 
   /* Grab the column names and PK details for the table(s). If no usable PK
   ** columns are found, bail out early.  */
   azCol = columnNames("main", zTab, &nPK, &bOtaRowid);
@@ skipping 25 matching lines @@
     /* If this is the first row output, print out the CREATE TABLE 
     ** statement first. And then set ct.z to NULL so that it is not 
     ** printed again.  */
     if( ct.z ){
       fprintf(out, "%s\n", ct.z);
       strFree(&ct);
     }
 
     /* Output the first part of the INSERT statement */
     fprintf(out, "%s", insert.z);
+    nRow++;
 
     if( sqlite3_column_type(pStmt, nCol)==SQLITE_INTEGER ){
       for(i=0; i<=nCol; i++){
         if( i>0 ) fprintf(out, ", ");
         printQuoted(out, sqlite3_column_value(pStmt, i));
       }
     }else{
       char *zOtaControl;
       int nOtaControl = sqlite3_column_bytes(pStmt, nCol);
 
-      zOtaControl = (char*)sqlite3_malloc(nOtaControl);
+      zOtaControl = (char*)sqlite3_malloc(nOtaControl+1);
       memcpy(zOtaControl, sqlite3_column_text(pStmt, nCol), nOtaControl+1);
 
       for(i=0; i<nCol; i++){
         int bDone = 0;
         if( i>=nPK 
             && sqlite3_column_type(pStmt, i)==SQLITE_BLOB
             && sqlite3_column_type(pStmt, nCol+1+i)==SQLITE_BLOB
         ){
           const char *aSrc = sqlite3_column_blob(pStmt, nCol+1+i);
           int nSrc = sqlite3_column_bytes(pStmt, nCol+1+i);
@@ skipping 22 matching lines @@
       }
       fprintf(out, "'%s'", zOtaControl);
       sqlite3_free(zOtaControl);
     }
 
     /* And the closing bracket of the insert statement */
     fprintf(out, ");\n");
   }
 
   sqlite3_finalize(pStmt);
+  if( nRow>0 ){
+    Str cnt = {0, 0, 0};
+    strPrintf(&cnt, "INSERT INTO rbu_count VALUES('data_%q', %d);", zTab, nRow);
+    fprintf(out, "%s\n", cnt.z);
+    strFree(&cnt);
+  }
 
   strFree(&ct);
   strFree(&sql);
   strFree(&insert);
 }
 
 /*
 ** Display a summary of differences between two versions of the same
 ** table table.
 **
@@ skipping 90 matching lines @@
     zSep = " AND";
   }
   strPrintf(&sql, ")\n ORDER BY 1;\n");
 
   if( (g.fDebug & DEBUG_DIFF_SQL)!=0 ){ 
     printf("SQL for %s:\n%s\n", zId, sql.z);
     goto end_summarize_one_table;
   }
 
   /* Run the query and output difference summary */
-  pStmt = db_prepare(sql.z);
+  pStmt = db_prepare("%s", sql.z);
   nUpdate = 0;
   nInsert = 0;
   nDelete = 0;
   nUnchanged = 0;
   while( SQLITE_ROW==sqlite3_step(pStmt) ){
     switch( sqlite3_column_int(pStmt,0) ){
       case 1:
         nUpdate = sqlite3_column_int64(pStmt,2);
         nUnchanged = sqlite3_column_int64(pStmt,1) - nUpdate;
         break;
@@ skipping 255 matching lines @@
   sqlite3_finalize(pStmt);
   
 end_changeset_one_table:
   while( nCol>0 ) sqlite3_free(azCol[--nCol]);
   sqlite3_free(azCol);
   sqlite3_free(aiPk);
   sqlite3_free(zId);
 }
 
 /*
+** Extract the next SQL keyword or quoted string from buffer zIn and copy it
+** (or a prefix of it if it will not fit) into buffer zBuf, size nBuf bytes.
+** Return a pointer to the character within zIn immediately following 
+** the token or quoted string just extracted.
+*/
+const char *gobble_token(const char *zIn, char *zBuf, int nBuf){
+  const char *p = zIn;
+  char *pOut = zBuf;
+  char *pEnd = &pOut[nBuf-1];
+  char q = 0;                     /* quote character, if any */
+
+  if( p==0 ) return 0;
+  while( *p==' ' ) p++;
+  switch( *p ){
+    case '"': q = '"'; break;
+    case '\'': q = '\''; break;
+    case '`': q = '`'; break;
+    case '[': q = ']'; break;
+  }
+
+  if( q ){
+    p++;
+    while( *p && pOut<pEnd ){
+      if( *p==q ){
+        p++;
+        if( *p!=q ) break;
+      }
+      if( pOut<pEnd ) *pOut++ = *p;
+      p++;
+    }
+  }else{
+    while( *p && *p!=' ' && *p!='(' ){
+      if( pOut<pEnd ) *pOut++ = *p;
+      p++;
+    }
+  }
+
+  *pOut = '\0';
+  return p;
+}
+
+/*
+** This function is the implementation of SQL scalar function "module_name":
+**
+**   module_name(SQL)
+**
+** The only argument should be an SQL statement of the type that may appear
+** in the sqlite_master table. If the statement is a "CREATE VIRTUAL TABLE"
+** statement, then the value returned is the name of the module that it
+** uses. Otherwise, if the statement is not a CVT, NULL is returned.
+*/
+static void module_name_func(
+  sqlite3_context *pCtx, 
+  int nVal, sqlite3_value **apVal
+){
+  const char *zSql;
+  char zToken[32];
+
+  assert( nVal==1 );
+  zSql = (const char*)sqlite3_value_text(apVal[0]);
+
+  zSql = gobble_token(zSql, zToken, sizeof(zToken));
+  if( zSql==0 || sqlite3_stricmp(zToken, "create") ) return;
+  zSql = gobble_token(zSql, zToken, sizeof(zToken));
+  if( zSql==0 || sqlite3_stricmp(zToken, "virtual") ) return;
+  zSql = gobble_token(zSql, zToken, sizeof(zToken));
+  if( zSql==0 || sqlite3_stricmp(zToken, "table") ) return;
+  zSql = gobble_token(zSql, zToken, sizeof(zToken));
+  if( zSql==0 ) return;
+  zSql = gobble_token(zSql, zToken, sizeof(zToken));
+  if( zSql==0 || sqlite3_stricmp(zToken, "using") ) return;
+  zSql = gobble_token(zSql, zToken, sizeof(zToken));
+  
+  sqlite3_result_text(pCtx, zToken, -1, SQLITE_TRANSIENT);
+}
+
+/*
+** Return the text of an SQL statement that itself returns the list of
+** tables to process within the database.
+*/
+const char *all_tables_sql(){
+  if( g.bHandleVtab ){
+    int rc;
+  
+    rc = sqlite3_exec(g.db, 
+        "CREATE TEMP TABLE tblmap(module COLLATE nocase, postfix);"
+        "INSERT INTO temp.tblmap VALUES"
+        "('fts3', '_content'), ('fts3', '_segments'), ('fts3', '_segdir'),"
+  
+        "('fts4', '_content'), ('fts4', '_segments'), ('fts4', '_segdir'),"
+        "('fts4', '_docsize'), ('fts4', '_stat'),"
+  
+        "('fts5', '_data'), ('fts5', '_idx'), ('fts5', '_content'),"
+        "('fts5', '_docsize'), ('fts5', '_config'),"
+  
+        "('rtree', '_node'), ('rtree', '_rowid'), ('rtree', '_parent');"
+        , 0, 0, 0
+    );
+    assert( rc==SQLITE_OK );
+  
+    rc = sqlite3_create_function(
+        g.db, "module_name", 1, SQLITE_UTF8, 0, module_name_func, 0, 0
+    );
+    assert( rc==SQLITE_OK );
+  
+    return 
+      "SELECT name FROM main.sqlite_master\n"
+      " WHERE type='table' AND (\n"
+      "    module_name(sql) IS NULL OR \n"
+      "    module_name(sql) IN (SELECT module FROM temp.tblmap)\n"
+      " ) AND name NOT IN (\n"
+      "  SELECT a.name || b.postfix \n"
+        "FROM main.sqlite_master AS a, temp.tblmap AS b \n"
+        "WHERE module_name(a.sql) = b.module\n" 
+      " )\n"
+      "UNION \n"
+      "SELECT name FROM aux.sqlite_master\n"
+      " WHERE type='table' AND (\n"
+      "    module_name(sql) IS NULL OR \n"
+      "    module_name(sql) IN (SELECT module FROM temp.tblmap)\n"
+      " ) AND name NOT IN (\n"
+      "  SELECT a.name || b.postfix \n"
+        "FROM aux.sqlite_master AS a, temp.tblmap AS b \n"
+        "WHERE module_name(a.sql) = b.module\n" 
+      " )\n"
+      " ORDER BY name";
+  }else{
+    return
+      "SELECT name FROM main.sqlite_master\n"
+      " WHERE type='table' AND sql NOT LIKE 'CREATE VIRTUAL%%'\n"
+      " UNION\n"
+      "SELECT name FROM aux.sqlite_master\n"
+      " WHERE type='table' AND sql NOT LIKE 'CREATE VIRTUAL%%'\n"
+      " ORDER BY name";
+  }
+}
+
+/*
 ** Print sketchy documentation for this utility program
 */
 static void showHelp(void){
   printf("Usage: %s [options] DB1 DB2\n", g.zArgv0);
   printf(
 "Output SQL text that would transform DB1 into DB2.\n"
 "Options:\n"
 "  --changeset FILE      Write a CHANGESET into FILE\n"
 "  -L|--lib LIBRARY      Load an SQLite extension library\n"
 "  --primarykey          Use schema-defined PRIMARY KEYs\n"
 "  --rbu                 Output SQL to create/populate RBU table(s)\n"
 "  --schema              Show only differences in the schema\n"
 "  --summary             Show only a summary of the differences\n"
 "  --table TAB           Show only differences in table TAB\n"
 "  --transaction         Show SQL output inside a transaction\n"
+"  --vtab                Handle fts3, fts4, fts5 and rtree tables\n"
   );
 }
 
 int main(int argc, char **argv){
   const char *zDb1 = 0;
   const char *zDb2 = 0;
   int i;
   int rc;
   char *zErrMsg = 0;
   char *zSql;
   sqlite3_stmt *pStmt;
   char *zTab = 0;
   FILE *out = stdout;
   void (*xDiff)(const char*,FILE*) = diff_one_table;
+#ifndef SQLITE_OMIT_LOAD_EXTENSION
   int nExt = 0;
   char **azExt = 0;
+#endif
   int useTransaction = 0;
   int neverUseTransaction = 0;
 
   g.zArgv0 = argv[0];
   sqlite3_config(SQLITE_CONFIG_SINGLETHREAD);
   for(i=1; i<argc; i++){
     const char *z = argv[i];
     if( z[0]=='-' ){
       z++;
       if( z[0]=='-' ) z++;
@@ skipping 32 matching lines @@
       if( strcmp(z,"summary")==0 ){
         xDiff = summarize_one_table;
       }else
       if( strcmp(z,"table")==0 ){
         if( i==argc-1 ) cmdlineError("missing argument to %s", argv[i]);
         zTab = argv[++i];
       }else
       if( strcmp(z,"transaction")==0 ){
         useTransaction = 1;
       }else
+      if( strcmp(z,"vtab")==0 ){
+        g.bHandleVtab = 1;
+      }else
       {
         cmdlineError("unknown option: %s", argv[i]);
       }
     }else if( zDb1==0 ){
       zDb1 = argv[i];
     }else if( zDb2==0 ){
       zDb2 = argv[i];
     }else{
       cmdlineError("unknown argument: %s", argv[i]);
     }
@@ skipping 10 matching lines @@
     cmdlineError("\"%s\" does not appear to be a valid SQLite database", zDb1);
   }
 #ifndef SQLITE_OMIT_LOAD_EXTENSION
   sqlite3_enable_load_extension(g.db, 1);
   for(i=0; i<nExt; i++){
     rc = sqlite3_load_extension(g.db, azExt[i], 0, &zErrMsg);
     if( rc || zErrMsg ){
       cmdlineError("error loading %s: %s", azExt[i], zErrMsg);
     }
   }
+  free(azExt);
 #endif
-  free(azExt);
   zSql = sqlite3_mprintf("ATTACH %Q as aux;", zDb2);
   rc = sqlite3_exec(g.db, zSql, 0, 0, &zErrMsg);
   if( rc || zErrMsg ){
     cmdlineError("cannot attach database \"%s\"", zDb2);
   }
   rc = sqlite3_exec(g.db, "SELECT * FROM aux.sqlite_master", 0, 0, &zErrMsg);
   if( rc || zErrMsg ){
     cmdlineError("\"%s\" does not appear to be a valid SQLite database", zDb2);
   }
 
   if( neverUseTransaction ) useTransaction = 0;
-  if( useTransaction ) printf("BEGIN TRANSACTION;\n");
+  if( useTransaction ) fprintf(out, "BEGIN TRANSACTION;\n");
+  if( xDiff==rbudiff_one_table ){
+    fprintf(out, "CREATE TABLE IF NOT EXISTS rbu_count"
+           "(tbl TEXT PRIMARY KEY COLLATE NOCASE, cnt INTEGER) "
+           "WITHOUT ROWID;\n"
+    );
+  }
   if( zTab ){
     xDiff(zTab, out);
   }else{
     /* Handle tables one by one */
-    pStmt = db_prepare(
-      "SELECT name FROM main.sqlite_master\n"
-      " WHERE type='table' AND sql NOT LIKE 'CREATE VIRTUAL%%'\n"
-      " UNION\n"
-      "SELECT name FROM aux.sqlite_master\n"
-      " WHERE type='table' AND sql NOT LIKE 'CREATE VIRTUAL%%'\n"
-      " ORDER BY name"
-    );
+    pStmt = db_prepare("%s", all_tables_sql() );
     while( SQLITE_ROW==sqlite3_step(pStmt) ){
       xDiff((const char*)sqlite3_column_text(pStmt,0), out);
     }
     sqlite3_finalize(pStmt);
   }
   if( useTransaction ) printf("COMMIT;\n");
 
   /* TBD: Handle trigger differences */
   /* TBD: Handle view differences */
   sqlite3_close(g.db);
   return 0;
 }