/ Check-in [93449e70]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix a problem causing [sqldiff --rbu] to fail on tables for which all columns are part of the PRIMARY KEY.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 93449e7046d60cad020ca439ded82e759c2e3cd9
User & Date: dan 2015-07-31 15:13:29
Context
2015-07-31
15:14
When building the VSIX package for UAP, omit the AppLocal suffix for the MSVC runtime library because it is no longer needed. check-in: fee7ad73 user: mistachkin tags: trunk
15:13
Fix a problem causing [sqldiff --rbu] to fail on tables for which all columns are part of the PRIMARY KEY. check-in: 93449e70 user: dan tags: trunk
14:43
Fix a bug in the fts5 porter tokenizer preventing it from passing xCreate() arguments through to its parent tokenizer. check-in: c3c672af user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/rbu/rbudiff.test.

    49     49     sqlite3 rbudb rbu.db
    50     50     rbudb eval $sql
    51     51     rbudb close
    52     52     step_rbu $target rbu.db
    53     53   }
    54     54   
    55     55   proc rbudiff_cksum {db1} {
           56  +  set txt ""
           57  +
    56     58     sqlite3 dbtmp $db1
    57         -  set txt [dbtmp eval {
    58         -    SELECT a || '.' || b || '.' || c FROM t1 ORDER BY 1;
    59         -    SELECT a || '.' || b || '.' || c FROM t2 ORDER BY 1;
    60         -  }]
           59  +  foreach tbl [dbtmp eval {SELECT name FROM sqlite_master WHERE type='table'}] {
           60  +    append txt [dbtmp eval \
           61  +      "SELECT a || '.' || b || '.' || c FROM $tbl ORDER BY 1"
           62  +    ]
           63  +  }
    61     64     dbtmp close
           65  +
    62     66     md5 $txt
    63     67   }
    64     68   
    65         -sqlite3 db test.db
    66         -do_execsql_test 1.0 {
    67         -  CREATE TABLE t1(a PRIMARY KEY, b, c);
    68         -  INSERT INTO t1 VALUES(1, 2, 3);
    69         -  INSERT INTO t1 VALUES(4, 5, 6);
           69  +foreach {tn init mod} {
           70  +  1 {
           71  +    CREATE TABLE t1(a PRIMARY KEY, b, c);
           72  +    INSERT INTO t1 VALUES(1, 2, 3);
           73  +    INSERT INTO t1 VALUES(4, 5, 6);
           74  +  
           75  +    CREATE TABLE t2(a, b, c, PRIMARY KEY(b, c));
           76  +    INSERT INTO t2 VALUES(1, 2, 3);
           77  +    INSERT INTO t2 VALUES(4, 5, 6);
           78  +  } {
           79  +    INSERT INTO t1 VALUES(7, 8, 9);
           80  +    DELETE FROM t1 WHERE a=4;
           81  +    UPDATE t1 SET c = 11 WHERE a = 1;
           82  +  
           83  +    INSERT INTO t2 VALUES(7, 8, 9);
           84  +    DELETE FROM t2 WHERE a=4;
           85  +    UPDATE t2 SET c = 11 WHERE a = 1;
           86  +  }
           87  +
           88  +  2 {
           89  +    CREATE TABLE t1(a, b, c, PRIMARY KEY(a, b, c));
           90  +    INSERT INTO t1 VALUES('u', 'v', 'w');
           91  +    INSERT INTO t1 VALUES('x', 'y', 'z');
           92  +  } {
           93  +    DELETE FROM t1 WHERE a='u';
           94  +    INSERT INTO t1 VALUES('a', 'b', 'c');
           95  +  }
           96  +
           97  +} {
           98  +  
           99  +  catch { db close }
          100  +
          101  +  forcedelete test.db test.db2
          102  +  sqlite3 db test.db
          103  +  db eval "$init"
          104  +  sqlite3 db test.db2
          105  +  db eval "$init ; $mod"
          106  +  db close
    70    107   
    71         -  CREATE TABLE t2(a, b, c, PRIMARY KEY(b, c));
    72         -  INSERT INTO t2 VALUES(1, 2, 3);
    73         -  INSERT INTO t2 VALUES(4, 5, 6);
    74         -}
          108  +  do_test 1.$tn.2 {
          109  +    set sql [get_rbudiff_sql test.db test.db2]
          110  +    apply_rbudiff $sql test.db
          111  +  } {SQLITE_DONE}
          112  +  do_test 1.$tn.3 { rbudiff_cksum test.db } [rbudiff_cksum test.db2]
    75    113   
    76         -db close
    77         -forcedelete test.db2
    78         -forcecopy test.db test.db2
    79         -
    80         -sqlite3 db test.db
    81         -do_execsql_test 1.1 {
    82         -  INSERT INTO t1 VALUES(7, 8, 9);
    83         -  DELETE FROM t1 WHERE a=4;
    84         -  UPDATE t1 SET c = 11 WHERE a = 1;
          114  +  forcedelete test.db test.db2
          115  +  sqlite3 db test.db
          116  +  db eval "$init ; $mod"
          117  +  sqlite3 db test.db2
          118  +  db eval "$init"
          119  +  db close
    85    120   
    86         -  INSERT INTO t2 VALUES(7, 8, 9);
    87         -  DELETE FROM t2 WHERE a=4;
    88         -  UPDATE t2 SET c = 11 WHERE a = 1;
          121  +  do_test 1.$tn.4 {
          122  +    set sql [get_rbudiff_sql test.db test.db2]
          123  +    apply_rbudiff $sql test.db
          124  +  } {SQLITE_DONE}
          125  +  do_test 1.$tn.5 { rbudiff_cksum test.db } [rbudiff_cksum test.db2]
    89    126   }
    90         -db close
    91         -
    92         -do_test 1.2 {
    93         -  set sql [get_rbudiff_sql test.db test.db2]
    94         -  apply_rbudiff $sql test.db
    95         -} {SQLITE_DONE}
    96         -
    97         -do_test 1.3 { rbudiff_cksum test.db } [rbudiff_cksum test.db2]
    98    127   
    99    128   finish_test
   100    129   

Changes to tool/sqldiff.c.

   785    785     strPrintf(pSql, " main.%Q AS o WHERE ", zTab);
   786    786     strPrintfArray(pSql, " AND ", "(n.%Q IS o.%Q)", azCol, nPK);
   787    787     strPrintf(pSql, "\n)");
   788    788   
   789    789     /* Deleted rows: */
   790    790     strPrintf(pSql, "\nUNION ALL\nSELECT ");
   791    791     strPrintfArray(pSql, ", ", "%s", azCol, nPK);
   792         -  strPrintf(pSql, ", ");
   793         -  strPrintfArray(pSql, ", ", "NULL", &azCol[nPK], -1);
          792  +  if( azCol[nPK] ){
          793  +    strPrintf(pSql, ", ");
          794  +    strPrintfArray(pSql, ", ", "NULL", &azCol[nPK], -1);
          795  +  }
   794    796     strPrintf(pSql, ", 1");         /* Set ota_control to 1 for a delete */
   795    797     strPrintf(pSql, " FROM main.%Q AS n WHERE NOT EXISTS (\n", zTab);
   796    798     strPrintf(pSql, "    SELECT 1 FROM ", zTab);
   797    799     strPrintf(pSql, " aux.%Q AS o WHERE ", zTab);
   798    800     strPrintfArray(pSql, " AND ", "(n.%Q IS o.%Q)", azCol, nPK);
   799    801     strPrintf(pSql, "\n) ");
   800    802   
   801         -  /* Updated rows: */
   802         -  strPrintf(pSql, "\nUNION ALL\nSELECT ");
   803         -  strPrintfArray(pSql, ", ", "n.%s", azCol, nPK);
   804         -  strPrintf(pSql, ",\n");
   805         -  strPrintfArray(pSql, " ,\n", 
   806         -      "    CASE WHEN n.%s IS o.%s THEN NULL ELSE n.%s END", &azCol[nPK], -1
   807         -  );
   808         -
   809         -  if( bOtaRowid==0 ){
   810         -    strPrintf(pSql, ", '");
   811         -    strPrintfArray(pSql, "", ".", azCol, nPK);
   812         -    strPrintf(pSql, "' ||\n");
   813         -  }else{
          803  +  /* Updated rows. If all table columns are part of the primary key, there 
          804  +  ** can be no updates. In this case this part of the compound SELECT can
          805  +  ** be omitted altogether. */
          806  +  if( azCol[nPK] ){
          807  +    strPrintf(pSql, "\nUNION ALL\nSELECT ");
          808  +    strPrintfArray(pSql, ", ", "n.%s", azCol, nPK);
   814    809       strPrintf(pSql, ",\n");
          810  +    strPrintfArray(pSql, " ,\n", 
          811  +        "    CASE WHEN n.%s IS o.%s THEN NULL ELSE n.%s END", &azCol[nPK], -1
          812  +    );
          813  +
          814  +    if( bOtaRowid==0 ){
          815  +      strPrintf(pSql, ", '");
          816  +      strPrintfArray(pSql, "", ".", azCol, nPK);
          817  +      strPrintf(pSql, "' ||\n");
          818  +    }else{
          819  +      strPrintf(pSql, ",\n");
          820  +    }
          821  +    strPrintfArray(pSql, " ||\n", 
          822  +        "    CASE WHEN n.%s IS o.%s THEN '.' ELSE 'x' END", &azCol[nPK], -1
          823  +    );
          824  +    strPrintf(pSql, "\nAS ota_control");
          825  +
          826  +    strPrintf(pSql, "\nFROM main.%Q AS o, aux.%Q AS n\nWHERE ", zTab, zTab);
          827  +    strPrintfArray(pSql, " AND ", "(n.%Q IS o.%Q)", azCol, nPK);
          828  +    strPrintf(pSql, " AND ota_control LIKE '%%x%%'");
   815    829     }
   816         -  strPrintfArray(pSql, " ||\n", 
   817         -      "    CASE WHEN n.%s IS o.%s THEN '.' ELSE 'x' END", &azCol[nPK], -1
   818         -  );
   819         -  strPrintf(pSql, "\nAS ota_control");
   820         -
   821         -  strPrintf(pSql, "\nFROM main.%Q AS o, aux.%Q AS n\nWHERE ", zTab, zTab);
   822         -  strPrintfArray(pSql, " AND ", "(n.%Q IS o.%Q)", azCol, nPK);
   823         -  strPrintf(pSql, " AND ota_control LIKE '%%x%%'");
   824    830   
   825    831     /* Now add an ORDER BY clause to sort everything by PK. */
   826    832     strPrintf(pSql, "\nORDER BY ");
   827    833     for(i=1; i<=nPK; i++) strPrintf(pSql, "%s%d", ((i>1)?", ":""), i);
   828    834   }
   829    835   
   830    836   static void rbudiff_one_table(const char *zTab, FILE *out){