Cause CLI .sha3sum to warn of text fields that do not survive CAST(CAST(t as BLOB) AS TEXT) due to invalid UTF encoding.

FossilOrigin-Name: 123f2a0785790addf9c60a0fd09077dda9cb84d33a2594901a2b22bb555be491

1 files changed, 49 insertions, 2 deletions

diff --git a/src/shell.c.in b/src/shell.c.in
index a829fa238..1f0ac24c8 100644
--- a/src/shell.c.in
+++ b/src/shell.c.in
@@ -10077,6 +10077,7 @@ static int do_meta_command(char *zLine, ShellState *p){
     sqlite3_stmt *pStmt;     /* For querying tables names */
     char *zSql;              /* SQL to be run */
     char *zSep;              /* Separator */
+    char *zRevText;          /* Query for reversible to-blob-to-text check */
     ShellText sSql;          /* Complete SQL for the query to run the hash */
     ShellText sQuery;        /* Set of queries used to read all content */
     open_db(p, 0);
@@ -10114,17 +10115,35 @@ static int do_meta_command(char *zLine, ShellState *p){
       }
     }
     if( bSchema ){
-      zSql = "SELECT lower(name) FROM sqlite_schema"
+      zSql = "SELECT lower(name) as tname FROM sqlite_schema"
              " WHERE type='table' AND coalesce(rootpage,0)>1"
              " UNION ALL SELECT 'sqlite_schema'"
              " ORDER BY 1 collate nocase";
     }else{
-      zSql = "SELECT lower(name) FROM sqlite_schema"
+      zSql = "SELECT lower(name) as tname FROM sqlite_schema"
              " WHERE type='table' AND coalesce(rootpage,0)>1"
              " AND name NOT LIKE 'sqlite_%'"
              " ORDER BY 1 collate nocase";
     }
     sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
+    zRevText = sqlite3_mprintf(
+        /* lower-case query is first run, producing upper-case query. */
+        "with text_cols as materialized("
+        "select tname, cname "
+        "from ("
+        " select ss.tname as tname, ti.name as cname, lower(type) as lca"
+        " from (%s) ss join pragma_table_info(tname) ti"
+        " where (instr(lca,'int')=0 and "
+        " instr(lca,'char')+instr(lca,'clob')+instr(lca,'text')>0)"
+        "))"
+        "select 'SELECT total(bad_text_count) AS bad_text_count"
+        " FROM ('||group_concat(query, ' UNION ALL ')||')' as btc_query"
+        " from (select 'SELECT COUNT(*) AS bad_text_count"
+        " FROM '||tname||' WHERE '"
+        " ||group_concat('CAST(CAST('||cname||' AS BLOB) AS TEXT)<>'||cname,"
+        " ' OR ') as query, tname from text_cols group by tname)",
+        zSql);
+    shell_check_oom(zRevText);
     initText(&sQuery);
     initText(&sSql);
     appendText(&sSql, "WITH [sha3sum$query](a,b) AS(",0);
@@ -10180,7 +10199,35 @@ static int do_meta_command(char *zLine, ShellState *p){
     }else{
       shell_exec(p, zSql, 0);
     }
+    {
+      int lrc = sqlite3_prepare_v2(p->db, zRevText, -1, &pStmt, 0);
+      if( bDebug ) utf8_printf(p->out, "%s\n", zRevText);
+      assert(lrc==SQLITE_OK);
+      lrc = SQLITE_ROW==sqlite3_step(pStmt);
+      assert(lrc!=0);
+      if( lrc ){
+        const char *zGenQuery = sqlite3_column_text(pStmt,0);
+        sqlite3_stmt *pCheckStmt;
+        double countIrreversible;
+        lrc = sqlite3_prepare_v2(p->db, zGenQuery, -1, &pCheckStmt, 0);
+        if( bDebug ) utf8_printf(p->out, "%s\n", zGenQuery);
+        assert(lrc==SQLITE_OK);
+        lrc = SQLITE_ROW==sqlite3_step(pCheckStmt);
+        assert(lrc!=0);
+        countIrreversible = sqlite3_column_double(pCheckStmt, 0);
+        sqlite3_finalize(pStmt);
+        sqlite3_finalize(pCheckStmt);
+        if( countIrreversible>0 ){
+          int n = (int)(countIrreversible + 0.5);
+          utf8_printf(stderr,
+                      "Digest includes %d invalidly encoded text field%s.\n",
+                      n, (n>1)? "s": "");
+        }
+      }
+
+    }
     sqlite3_free(zSql);
+    sqlite3_free(zRevText);
   }else
 
 #if !defined(SQLITE_NOHAVE_SYSTEM) && !defined(SQLITE_SHELL_FIDDLE)