/ Check-in [93449e70]
Login
Home Timeline Branches Tags Tickets Wiki More...
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 Unified Diffs Ignore Whitespace Patch

Changes to ext/rbu/rbudiff.test. 

49
50
51
52
53
54
55


56

57
58
59
60


61

62
63
64
65
66


67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89



















90
91
92
93
94
95

96











97

98
99
100

  sqlite3 rbudb rbu.db
  rbudb eval $sql
  rbudb close
  step_rbu $target rbu.db
}

proc rbudiff_cksum {db1} {


  sqlite3 dbtmp $db1

  set txt [dbtmp eval {
    SELECT a || '.' || b || '.' || c FROM t1 ORDER BY 1;
    SELECT a || '.' || b || '.' || c FROM t2 ORDER BY 1;
  }]


  dbtmp close

  md5 $txt
}

sqlite3 db test.db
do_execsql_test 1.0 {


  CREATE TABLE t1(a PRIMARY KEY, b, c);
  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(4, 5, 6);

  CREATE TABLE t2(a, b, c, PRIMARY KEY(b, c));
  INSERT INTO t2 VALUES(1, 2, 3);
  INSERT INTO t2 VALUES(4, 5, 6);
}

db close
forcedelete test.db2
forcecopy test.db test.db2

sqlite3 db test.db
do_execsql_test 1.1 {
  INSERT INTO t1 VALUES(7, 8, 9);
  DELETE FROM t1 WHERE a=4;
  UPDATE t1 SET c = 11 WHERE a = 1;

  INSERT INTO t2 VALUES(7, 8, 9);
  DELETE FROM t2 WHERE a=4;
  UPDATE t2 SET c = 11 WHERE a = 1;
}



















db close

do_test 1.2 {
  set sql [get_rbudiff_sql test.db test.db2]
  apply_rbudiff $sql test.db
} {SQLITE_DONE}













do_test 1.3 { rbudiff_cksum test.db } [rbudiff_cksum test.db2]


finish_test









>
>

>
|
|
<
<
>
>

>



<
<
>
>
|
|
|
|
|
|
|
|
<
<
<
<
<
<
<
|
|
|
|
|
|
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|

|
|
|
|
>

>
>
>
>
>
>
>
>
>
>
>
|
>




49
50
51
52
53
54
55
56
57
58
59
60
61


62
63
64
65
66
67
68


69
70
71
72
73
74
75
76
77
78







79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129

  sqlite3 rbudb rbu.db
  rbudb eval $sql
  rbudb close
  step_rbu $target rbu.db
}

proc rbudiff_cksum {db1} {
  set txt ""

  sqlite3 dbtmp $db1
  foreach tbl [dbtmp eval {SELECT name FROM sqlite_master WHERE type='table'}] {
    append txt [dbtmp eval \
      "SELECT a || '.' || b || '.' || c FROM $tbl ORDER BY 1"


    ]
  }
  dbtmp close

  md5 $txt
}



foreach {tn init mod} {
  1 {
    CREATE TABLE t1(a PRIMARY KEY, b, c);
    INSERT INTO t1 VALUES(1, 2, 3);
    INSERT INTO t1 VALUES(4, 5, 6);
  
    CREATE TABLE t2(a, b, c, PRIMARY KEY(b, c));
    INSERT INTO t2 VALUES(1, 2, 3);
    INSERT INTO t2 VALUES(4, 5, 6);
  } {







    INSERT INTO t1 VALUES(7, 8, 9);
    DELETE FROM t1 WHERE a=4;
    UPDATE t1 SET c = 11 WHERE a = 1;
  
    INSERT INTO t2 VALUES(7, 8, 9);
    DELETE FROM t2 WHERE a=4;
    UPDATE t2 SET c = 11 WHERE a = 1;
  }

  2 {
    CREATE TABLE t1(a, b, c, PRIMARY KEY(a, b, c));
    INSERT INTO t1 VALUES('u', 'v', 'w');
    INSERT INTO t1 VALUES('x', 'y', 'z');
  } {
    DELETE FROM t1 WHERE a='u';
    INSERT INTO t1 VALUES('a', 'b', 'c');
  }

} {
  
  catch { db close }

  forcedelete test.db test.db2
  sqlite3 db test.db
  db eval "$init"
  sqlite3 db test.db2
  db eval "$init ; $mod"
  db close

  do_test 1.$tn.2 {
    set sql [get_rbudiff_sql test.db test.db2]
    apply_rbudiff $sql test.db
  } {SQLITE_DONE}
  do_test 1.$tn.3 { rbudiff_cksum test.db } [rbudiff_cksum test.db2]

  forcedelete test.db test.db2
  sqlite3 db test.db
  db eval "$init ; $mod"
  sqlite3 db test.db2
  db eval "$init"
  db close

  do_test 1.$tn.4 {
    set sql [get_rbudiff_sql test.db test.db2]
    apply_rbudiff $sql test.db
  } {SQLITE_DONE}
  do_test 1.$tn.5 { rbudiff_cksum test.db } [rbudiff_cksum test.db2]
}

finish_test

Changes to tool/sqldiff.c. 

785
786
787
788
789
790
791

792
793

794
795
796
797
798
799
800
801



802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823

824
825
826
827
828
829
830

  strPrintf(pSql, " main.%Q AS o WHERE ", zTab);
  strPrintfArray(pSql, " AND ", "(n.%Q IS o.%Q)", azCol, nPK);
  strPrintf(pSql, "\n)");

  /* Deleted rows: */
  strPrintf(pSql, "\nUNION ALL\nSELECT ");
  strPrintfArray(pSql, ", ", "%s", azCol, nPK);

  strPrintf(pSql, ", ");
  strPrintfArray(pSql, ", ", "NULL", &azCol[nPK], -1);

  strPrintf(pSql, ", 1");         /* Set ota_control to 1 for a delete */
  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) ");

  /* Updated rows: */



  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
  );

  if( bOtaRowid==0 ){
    strPrintf(pSql, ", '");
    strPrintfArray(pSql, "", ".", azCol, nPK);
    strPrintf(pSql, "' ||\n");
  }else{
    strPrintf(pSql, ",\n");
  }
  strPrintfArray(pSql, " ||\n", 
      "    CASE WHEN n.%s IS o.%s THEN '.' ELSE 'x' END", &azCol[nPK], -1
  );
  strPrintf(pSql, "\nAS ota_control");

  strPrintf(pSql, "\nFROM main.%Q AS o, aux.%Q AS n\nWHERE ", zTab, zTab);
  strPrintfArray(pSql, " AND ", "(n.%Q IS 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){








>
|
|
>







|
>
>
>
|
|
|
|
|
|

|
|
|
|
|
|
|
|
|
|
|

|
|
|
>








785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836

  strPrintf(pSql, " main.%Q AS o WHERE ", zTab);
  strPrintfArray(pSql, " AND ", "(n.%Q IS o.%Q)", azCol, nPK);
  strPrintf(pSql, "\n)");

  /* 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 */
  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) ");

  /* 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
    );

    if( bOtaRowid==0 ){
      strPrintf(pSql, ", '");
      strPrintfArray(pSql, "", ".", azCol, nPK);
      strPrintf(pSql, "' ||\n");
    }else{
      strPrintf(pSql, ",\n");
    }
    strPrintfArray(pSql, " ||\n", 
        "    CASE WHEN n.%s IS o.%s THEN '.' ELSE 'x' END", &azCol[nPK], -1
    );
    strPrintf(pSql, "\nAS ota_control");

    strPrintf(pSql, "\nFROM main.%Q AS o, aux.%Q AS n\nWHERE ", zTab, zTab);
    strPrintfArray(pSql, " AND ", "(n.%Q IS 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){

This page was generated in about 0.008s by Fossil 2.10 [9d9ef82234] 2019-10-04 21:41:13