Make sqldiff able to compare sqlite_schema tables (for detailed schema differences.)

FossilOrigin-Name: 7ff92da9f183bc24a0d93996df771df7a712df1122ad897cb9143cde7d57ef7d

3 files changed, 58 insertions, 31 deletions

diff --git a/manifest b/manifest
index 4245c2ace..624dbdb66 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Fix\sa\smemory\sleak\sin\sfts5\sthat\scould\soccur\swhen\swriting\sto\san\sfts5\stable\sthat\sis\sbeing\sscanned\sby\san\sfts5vocab\scursor.
-D 2021-09-07T16:05:48.374
+C Make\ssqldiff\sable\sto\scompare\ssqlite_schema\stables\s(for\sdetailed\sschema\sdifferences.)
+D 2021-09-07T19:04:42.342
 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
 F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724
@@ -1887,7 +1887,7 @@ F tool/speedtest2.tcl ee2149167303ba8e95af97873c575c3e0fab58ff
 F tool/speedtest8.c 2902c46588c40b55661e471d7a86e4dd71a18224
 F tool/speedtest8inst1.c 7ce07da76b5e745783e703a834417d725b7d45fd
 F tool/split-sqlite3c.tcl 3efcd4240b738f6bb2b5af0aea7e1e0ef9bc1c61654f645076cec883030b710c
-F tool/sqldiff.c a94207d8a8b8ae20973012756362a850ba1f95bb4ed02cf950fd469eb556717c
+F tool/sqldiff.c 76381d75e306e3d8cb8bd16dee9e5f752861442898166899749a645db9463e63
 F tool/sqlite3_analyzer.c.in 7eeaae8b0d7577662acaabbb11107af0659d1b41bc1dfdd4d91422de27127968
 F tool/sqltclsh.c.in 1bcc2e9da58fadf17b0bf6a50e68c1159e602ce057210b655d50bad5aaaef898
 F tool/sqltclsh.tcl 862f4cf1418df5e1315b5db3b5ebe88969e2a784525af5fbf9596592f14ed848
@@ -1922,7 +1922,7 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93
 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc
 F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e
 F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0
-P b9417d400f4585004f434837022709f818044d5844fe208fe01595a6b226ef7d
-R fbfb27d03a48669f1ac219704fa7775a
-U dan
-Z 18191cfbc7f1ec5803fe525828e4b53c
+P 1183552de10b64a776874a23be8962c0693e5e0cd1a191ea8fa5d6a02b691e4a
+R 5fc0569834e7e1f42b0c0df267678b2d
+U larrybr
+Z 0fa72ab31716f5d6918e2bf803ce3e1a
diff --git a/manifest.uuid b/manifest.uuid
index 0375c6ec5..830550a84 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-1183552de10b64a776874a23be8962c0693e5e0cd1a191ea8fa5d6a02b691e4a
\ No newline at end of file
+7ff92da9f183bc24a0d93996df771df7a712df1122ad897cb9143cde7d57ef7d
\ No newline at end of file
diff --git a/tool/sqldiff.c b/tool/sqldiff.c
index b8b9e005b..9bd2c446f 100644
--- a/tool/sqldiff.c
+++ b/tool/sqldiff.c
@@ -35,6 +35,7 @@ struct GlobalVars {
   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;
 
@@ -192,7 +193,7 @@ static void namelistFree(char **az){
 }
 
 /*
-** Return a list of column names for the table zDb.zTab.  Space to
+** 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.
 **
@@ -209,6 +210,8 @@ static void namelistFree(char **az){
 ** If the primary key for a table is the rowid but rowid is inaccessible,
 ** then this routine returns a NULL pointer.
 **
+** [a. If the named table is sqlite_schema, omits the "rootpage" column.]
+
 ** Examples:
 **    CREATE TABLE t1(a INT UNIQUE, b INTEGER, c TEXT, PRIMARY KEY(c));
 **    *pnPKey = 1;
@@ -305,13 +308,16 @@ static char **columnNames(
   if( az==0 ) runtimeError("out of memory");
   memset(az, 0, sizeof(char*)*(nPK+1));
   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] = safeId((char*)sqlite3_column_text(pStmt,1));
+      az[iPKey-1] = sid;
     }else{
-      az = sqlite3_realloc(az, sizeof(char*)*(naz+2) );
-      if( az==0 ) runtimeError("out of memory");
-      az[naz++] = safeId((char*)sqlite3_column_text(pStmt,1));
+      if( !g.bSchemaCompare || strcmp(sid,"rootpage")!=0 ){
+        az = sqlite3_realloc(az, sizeof(char*)*(naz+2) );
+        if( az==0 ) runtimeError("out of memory");
+        az[naz++] = sid;
+      }
     }
   }
   sqlite3_finalize(pStmt);
@@ -322,18 +328,24 @@ static char **columnNames(
   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 three options - "rowid", "_rowid_" and "oid". Any
-  ** of these will work, unless the table has an explicit column of the
-  ** same name.  */
+  ** 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( g.bSchemaCompare ){
+      az[0] = sqlite3_mprintf("%s", "name");
+    }else {
+      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 ){
@@ -494,7 +506,8 @@ static void dump_table(const char *zTab, FILE *out){
 
 
 /*
-** Compute all differences for a single table.
+** 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) */
@@ -509,6 +522,8 @@ static void diff_one_table(const char *zTab, FILE *out){
   const char *zSep;         /* Separator string */
   Str sql;                  /* Comparison query */
   sqlite3_stmt *pStmt;      /* Query statement to do the diff */
+  const char *zLead =       /* Becomes line-comment for sqlite_schema */
+    (g.bSchemaCompare)? "-- " : "";
 
   strInit(&sql);
   if( g.fDebug==DEBUG_COLUMN_NAMES ){
@@ -529,19 +544,24 @@ static void diff_one_table(const char *zTab, FILE *out){
     }
     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. */
-      fprintf(out, "DROP TABLE %s;\n", zId);
+      if( g.bSchemaCompare )
+        fprintf(out, "-- 2nd DB has no %s table\n", zTab);
+      else
+        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 */
-    dump_table(zTab, out);
+    if( g.bSchemaCompare )
+      fprintf(out, "-- 1st DB has no %s table\n", zTab);
+    else
+      dump_table(zTab, out);
     goto end_diff_one_table;
   }
 
@@ -558,7 +578,7 @@ static void diff_one_table(const char *zTab, FILE *out){
    || az[n]
   ){
     /* Schema mismatch */
-    fprintf(out, "DROP TABLE %s; -- due to schema mismatch\n", zId);
+    fprintf(out, "%sDROP TABLE %s; -- due to schema mismatch\n", zLead, zId);
     dump_table(zTab, out);
     goto end_diff_one_table;
   }
@@ -677,7 +697,7 @@ static void diff_one_table(const char *zTab, FILE *out){
       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);
+          fprintf(out, "%sUPDATE %s", zLead, zId);
           zSep = " SET";
           for(i=nPk+1; i<nQ; i+=2){
             if( sqlite3_column_int(pStmt,i)==0 ) continue;
@@ -686,7 +706,7 @@ static void diff_one_table(const char *zTab, FILE *out){
             printQuoted(out, sqlite3_column_value(pStmt,i+1));
           }
         }else{                /* Delete a row */
-          fprintf(out, "DELETE FROM %s", zId);
+          fprintf(out, "%sDELETE FROM %s", zLead, zId);
         }
         zSep = " WHERE";
         for(i=0; i<nPk; i++){
@@ -696,7 +716,7 @@ static void diff_one_table(const char *zTab, FILE *out){
         }
         fprintf(out, ";\n");
       }else{                  /* Insert a row */
-        fprintf(out, "INSERT INTO %s(%s", zId, az2[0]);
+        fprintf(out, "%sINSERT INTO %s(%s", zLead, zId, az2[0]);
         for(i=1; az2[i]; i++) fprintf(out, ",%s", az2[i]);
         fprintf(out, ") VALUES");
         zSep = "(";
@@ -1891,6 +1911,7 @@ static void showHelp(void){
 "  --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"
+"See https://sqlite.org/sqldiff.html for detailed explanation.\n"
   );
 }
 
@@ -1957,6 +1978,9 @@ int main(int argc, char **argv){
       if( strcmp(z,"table")==0 ){
         if( i==argc-1 ) cmdlineError("missing argument to %s", argv[i]);
         zTab = argv[++i];
+        g.bSchemaCompare =
+          sqlite3_stricmp(zTab, "sqlite_schema")
+          || sqlite3_stricmp(zTab, "sqlite_master");
       }else
       if( strcmp(z,"transaction")==0 ){
         useTransaction = 1;
@@ -1978,6 +2002,9 @@ int main(int argc, char **argv){
   if( zDb2==0 ){
     cmdlineError("two database arguments required");
   }
+  if( g.bSchemaOnly && g.bSchemaCompare ){
+    cmdlineError("The --schema option is useless with --table %s .", zTab);
+  }
   rc = sqlite3_open(zDb1, &g.db);
   if( rc ){
     cmdlineError("cannot open database file \"%s\"", zDb1);