SQLite

Check-in [ea400eca31]
Login

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

Overview
Comment:Fix a performance problem in sqlite3session_diff().
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | sessions
Files: files | file ages | folders
SHA1: ea400eca314d81761fe5c0e329b906c045f0dfe4
User & Date: dan 2015-04-23 14:40:17.627
Context
2015-04-23
15:03
Fix the error message returned by sqlite3session_diff() for tables with no PRIMARY KEY. (check-in: 4d34a3d40d user: dan tags: sessions)
14:40
Fix a performance problem in sqlite3session_diff(). (check-in: ea400eca31 user: dan tags: sessions)
13:49
Merge the latest trunk enhancements and fixes into the sessions branch. (check-in: 4e5f17d189 user: drh tags: sessions)
Changes
Unified Diff Ignore Whitespace Patch
Changes to ext/session/sessionD.test.
︙︙
61
62
63
64
65
66
67



















68
69
70
71
72
73
74

  uplevel do_test $tn.1 [list {execsql { PRAGMA integrity_check } db2}] ok
  db2 close

  set cksum [scksum db main]
  uplevel do_test $tn.2 [list {scksum db aux}] [list $cksum]
}





















forcedelete test.db2
do_execsql_test 1.0 {
  CREATE TABLE t2(a PRIMARY KEY, b);
  INSERT INTO t2 VALUES(1, 'one');
  INSERT INTO t2 VALUES(2, 'two');









>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>








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

  uplevel do_test $tn.1 [list {execsql { PRAGMA integrity_check } db2}] ok
  db2 close

  set cksum [scksum db main]
  uplevel do_test $tn.2 [list {scksum db aux}] [list $cksum]
}

# Ensure that the diff produced by comparing the current contents of [db]
# with itself is empty.
proc do_empty_diff_test {tn} {
  forcedelete test.db2
  forcecopy test.db test.db2

  execsql { ATTACH 'test.db2' AS aux }
  sqlite3session S db main
  foreach tbl [db eval {SELECT name FROM sqlite_master WHERE type='table'}] {
    S attach $tbl
    S diff aux $tbl
  }

  set ::C [S changeset]
  S delete

  uplevel [list do_test $tn {string length $::C} 0]
}


forcedelete test.db2
do_execsql_test 1.0 {
  CREATE TABLE t2(a PRIMARY KEY, b);
  INSERT INTO t2 VALUES(1, 'one');
  INSERT INTO t2 VALUES(2, 'two');
︙︙
142
143
144
145
146
147
148



















149
150

  INSERT INTO aux.t1 VALUES(-851726,-161992,-469943,-159541);
  INSERT INTO aux.t1 VALUES(X'4A6A667F858938',185083,X'7A',NULL);
  INSERT INTO aux.t1 VALUES(-204877.54,X'1704C253D5F3AFA8',155120.88, 4);
  INSERT INTO aux.t1 
  VALUES('ckmqmzoeuvxisxqy',X'EB5A5D3A1DD22FD1','tidgtsplhjcbvbppdt',-642987.3);
}




















finish_test









>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188

  INSERT INTO aux.t1 VALUES(-851726,-161992,-469943,-159541);
  INSERT INTO aux.t1 VALUES(X'4A6A667F858938',185083,X'7A',NULL);
  INSERT INTO aux.t1 VALUES(-204877.54,X'1704C253D5F3AFA8',155120.88, 4);
  INSERT INTO aux.t1 
  VALUES('ckmqmzoeuvxisxqy',X'EB5A5D3A1DD22FD1','tidgtsplhjcbvbppdt',-642987.3);
}

reset_db
do_execsql_test 3.0 {
  CREATE TABLE t1(a, b, c, PRIMARY KEY(a));
  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(4, 5, 6);
  INSERT INTO t1 VALUES(7, 8, 9);

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

  CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b, c));
  INSERT INTO t3 VALUES(1, 2, 3);
  INSERT INTO t3 VALUES(4, 5, 6);
  INSERT INTO t3 VALUES(7, 8, 9);
}
do_empty_diff_test 3.1

finish_test
Changes to ext/session/sqlite3session.c.
︙︙
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427

  char *zExpr2 = sessionExprCompareOther(pTab->nCol,
      pSession->zDb, zFrom, pTab->zName, pTab->azCol, pTab->abPK
  );
  if( zExpr2==0 ){
    rc = SQLITE_NOMEM;
  }else{
    char *zStmt = sqlite3_mprintf(
        "SELECT * FROM \"%w\".\"%w\", \"%w\".\"%w\" WHERE %s AND %z",
        pSession->zDb, pTab->zName, zFrom, pTab->zName, zExpr, zExpr2
    );
    if( zStmt==0 ){
      rc = SQLITE_NOMEM;
    }else{
      sqlite3_stmt *pStmt;
      rc = sqlite3_prepare(pSession->db, zStmt, -1, &pStmt, 0);








|








1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427

  char *zExpr2 = sessionExprCompareOther(pTab->nCol,
      pSession->zDb, zFrom, pTab->zName, pTab->azCol, pTab->abPK
  );
  if( zExpr2==0 ){
    rc = SQLITE_NOMEM;
  }else{
    char *zStmt = sqlite3_mprintf(
        "SELECT * FROM \"%w\".\"%w\", \"%w\".\"%w\" WHERE %s AND (%z)",
        pSession->zDb, pTab->zName, zFrom, pTab->zName, zExpr, zExpr2
    );
    if( zStmt==0 ){
      rc = SQLITE_NOMEM;
    }else{
      sqlite3_stmt *pStmt;
      rc = sqlite3_prepare(pSession->db, zStmt, -1, &pStmt, 0);
︙︙