/ Check-in [ea400eca]
Login

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

Overview
Comment:Fix a performance problem in sqlite3session_diff().
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sessions
Files: files | file ages | folders
SHA1:ea400eca314d81761fe5c0e329b906c045f0dfe4
User & Date: dan 2015-04-23 14:40:17
Context
2015-04-23
15:03
Fix the error message returned by sqlite3session_diff() for tables with no PRIMARY KEY. check-in: 4d34a3d4 user: dan tags: sessions
14:40
Fix a performance problem in sqlite3session_diff(). check-in: ea400eca user: dan tags: sessions
13:49
Merge the latest trunk enhancements and fixes into the sessions branch. check-in: 4e5f17d1 user: drh tags: sessions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/session/sessionD.test.

    61     61     uplevel do_test $tn.1 [list {execsql { PRAGMA integrity_check } db2}] ok
    62     62     db2 close
    63     63   
    64     64     set cksum [scksum db main]
    65     65     uplevel do_test $tn.2 [list {scksum db aux}] [list $cksum]
    66     66   }
    67     67   
           68  +# Ensure that the diff produced by comparing the current contents of [db]
           69  +# with itself is empty.
           70  +proc do_empty_diff_test {tn} {
           71  +  forcedelete test.db2
           72  +  forcecopy test.db test.db2
           73  +
           74  +  execsql { ATTACH 'test.db2' AS aux }
           75  +  sqlite3session S db main
           76  +  foreach tbl [db eval {SELECT name FROM sqlite_master WHERE type='table'}] {
           77  +    S attach $tbl
           78  +    S diff aux $tbl
           79  +  }
           80  +
           81  +  set ::C [S changeset]
           82  +  S delete
           83  +
           84  +  uplevel [list do_test $tn {string length $::C} 0]
           85  +}
           86  +
    68     87   
    69     88   forcedelete test.db2
    70     89   do_execsql_test 1.0 {
    71     90     CREATE TABLE t2(a PRIMARY KEY, b);
    72     91     INSERT INTO t2 VALUES(1, 'one');
    73     92     INSERT INTO t2 VALUES(2, 'two');
    74     93   
................................................................................
   142    161     INSERT INTO aux.t1 VALUES(-851726,-161992,-469943,-159541);
   143    162     INSERT INTO aux.t1 VALUES(X'4A6A667F858938',185083,X'7A',NULL);
   144    163     INSERT INTO aux.t1 VALUES(-204877.54,X'1704C253D5F3AFA8',155120.88, 4);
   145    164     INSERT INTO aux.t1 
   146    165     VALUES('ckmqmzoeuvxisxqy',X'EB5A5D3A1DD22FD1','tidgtsplhjcbvbppdt',-642987.3);
   147    166   }
   148    167   
          168  +reset_db
          169  +do_execsql_test 3.0 {
          170  +  CREATE TABLE t1(a, b, c, PRIMARY KEY(a));
          171  +  INSERT INTO t1 VALUES(1, 2, 3);
          172  +  INSERT INTO t1 VALUES(4, 5, 6);
          173  +  INSERT INTO t1 VALUES(7, 8, 9);
          174  +
          175  +  CREATE TABLE t2(a, b, c, PRIMARY KEY(a, b));
          176  +  INSERT INTO t2 VALUES(1, 2, 3);
          177  +  INSERT INTO t2 VALUES(4, 5, 6);
          178  +  INSERT INTO t2 VALUES(7, 8, 9);
          179  +
          180  +  CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b, c));
          181  +  INSERT INTO t3 VALUES(1, 2, 3);
          182  +  INSERT INTO t3 VALUES(4, 5, 6);
          183  +  INSERT INTO t3 VALUES(7, 8, 9);
          184  +}
          185  +do_empty_diff_test 3.1
          186  +
   149    187   finish_test
   150    188   

Changes to ext/session/sqlite3session.c.

  1413   1413     char *zExpr2 = sessionExprCompareOther(pTab->nCol,
  1414   1414         pSession->zDb, zFrom, pTab->zName, pTab->azCol, pTab->abPK
  1415   1415     );
  1416   1416     if( zExpr2==0 ){
  1417   1417       rc = SQLITE_NOMEM;
  1418   1418     }else{
  1419   1419       char *zStmt = sqlite3_mprintf(
  1420         -        "SELECT * FROM \"%w\".\"%w\", \"%w\".\"%w\" WHERE %s AND %z",
         1420  +        "SELECT * FROM \"%w\".\"%w\", \"%w\".\"%w\" WHERE %s AND (%z)",
  1421   1421           pSession->zDb, pTab->zName, zFrom, pTab->zName, zExpr, zExpr2
  1422   1422       );
  1423   1423       if( zStmt==0 ){
  1424   1424         rc = SQLITE_NOMEM;
  1425   1425       }else{
  1426   1426         sqlite3_stmt *pStmt;
  1427   1427         rc = sqlite3_prepare(pSession->db, zStmt, -1, &pStmt, 0);