/ Check-in [cdc09867]
Login

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

Overview
Comment:Optimize some cases of restarting an RBU vacuum.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | rbu-opt
Files: files | file ages | folders
SHA3-256: cdc09867ed6522026ae7bfac1f59cd79b60fba6d07d49b99b030a501a7059ee4
User & Date: dan 2019-05-04 20:04:42
Context
2019-05-06
20:40
Optimize further cases of restarting an RBU vacuum. check-in: 6b3261bf user: dan tags: rbu-opt
2019-05-04
20:04
Optimize some cases of restarting an RBU vacuum. check-in: cdc09867 user: dan tags: rbu-opt
17:32
Fix the NOT NULL logic in the theorem prover that determines when a partial index can be used. Ticket [5c6955204c392ae763a95]. check-in: c2e439bc user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to ext/rbu/rbu_common.tcl.

    85     85     }
    86     86     set rc
    87     87   }
    88     88   
    89     89   proc do_rbu_vacuum_test {tn step {statedb state.db}} {
    90     90     forcedelete $statedb
    91     91     if {$statedb=="" && $step==1} breakpoint
    92         -  uplevel [list do_test $tn.1 [string map [list %state% $statedb] {
    93         -    if {$step==0} { sqlite3rbu_vacuum rbu test.db {%state%}}
           92  +  uplevel [list do_test $tn.1 [string map [list %state% $statedb %step% $step] {
           93  +    if {%step%==0} { sqlite3rbu_vacuum rbu test.db {%state%}}
    94     94       while 1 {
    95         -      if {$step==1} { sqlite3rbu_vacuum rbu test.db {%state%}}
           95  +      if {%step%==1} { sqlite3rbu_vacuum rbu test.db {%state%}}
    96     96         set state [rbu state]
    97     97         check_prestep_state test.db $state
    98     98         set rc [rbu step]
    99     99         check_poststep_state $rc test.db $state
   100    100         if {$rc!="SQLITE_OK"} break
   101         -      if {$step==1} { rbu close }
          101  +      if {%step%==1} { rbu close }
   102    102       }
   103    103       rbu close
   104    104     }] {SQLITE_DONE}]
   105    105   
   106    106     uplevel [list do_execsql_test $tn.2 {
   107    107       PRAGMA integrity_check
   108    108     } ok]
   109    109   }
   110    110   

Changes to ext/rbu/rbupartial.test.

    76     76       SELECT * FROM t1 ORDER BY %A%;
    77     77     } {
    78     78       1 10 {} b   7 8 4 d   10 11 12 e   13 14 {} f
    79     79     }
    80     80   
    81     81     set step 0
    82     82     do_rbu_vacuum_test $tn.1.5 0
           83  +
           84  +  do_test $tn.1.6 {
           85  +    execsql { PRAGMA integrity_check }
           86  +  } {ok}
    83     87     }]
    84     88   }
    85     89   
    86     90   finish_test

Changes to ext/rbu/sqlite3rbu.c.

  1377   1377   
  1378   1378           if( i!=iOrder ){
  1379   1379             SWAP(int, pIter->aiSrcOrder[i], pIter->aiSrcOrder[iOrder]);
  1380   1380             SWAP(char*, pIter->azTblCol[i], pIter->azTblCol[iOrder]);
  1381   1381           }
  1382   1382   
  1383   1383           pIter->azTblType[iOrder] = rbuStrndup(zType, &p->rc);
  1384         -        pIter->abTblPk[iOrder] = (iPk!=0);
         1384  +        assert( iPk>=0 );
         1385  +        pIter->abTblPk[iOrder] = (u8)iPk;
  1385   1386           pIter->abNotNull[iOrder] = (u8)bNotNull || (iPk!=0);
  1386   1387           iOrder++;
  1387   1388         }
  1388   1389       }
  1389   1390   
  1390   1391       rbuFinalize(p, pStmt);
  1391   1392       rbuObjIterCacheIndexedCols(p, pIter);
................................................................................
  1411   1412     for(i=0; i<pIter->nTblCol; i++){
  1412   1413       const char *z = pIter->azTblCol[i];
  1413   1414       zList = rbuMPrintf(p, "%z%s\"%w\"", zList, zSep, z);
  1414   1415       zSep = ", ";
  1415   1416     }
  1416   1417     return zList;
  1417   1418   }
         1419  +
         1420  +static char *rbuObjIterGetPkList(
         1421  +  sqlite3rbu *p,                  /* RBU object */
         1422  +  RbuObjIter *pIter,              /* Object iterator for column names */
         1423  +  const char *zExtra
         1424  +){
         1425  +  int iPk = 1;
         1426  +  char *zRet = 0;
         1427  +  const char *zSep = "";
         1428  +  while( 1 ){
         1429  +    int i;
         1430  +    for(i=0; i<pIter->nTblCol; i++){
         1431  +      if( (int)pIter->abTblPk[i]==iPk ){
         1432  +        const char *zCol = pIter->azTblCol[i];
         1433  +        zRet = rbuMPrintf(p, "%z%s\"%w\"%s", zRet, zSep, zCol, zExtra);
         1434  +        zSep = ", ";
         1435  +      }
         1436  +    }
         1437  +    if( i==pIter->nTblCol ) break;
         1438  +    iPk++;
         1439  +  }
         1440  +  return zRet;
         1441  +}
         1442  +
         1443  +static char *rbuVacuumTableStart(
         1444  +  sqlite3rbu *p, 
         1445  +  RbuObjIter *pIter,
         1446  +  int bRowid,
         1447  +  const char *zWrite
         1448  +){
         1449  +  sqlite3_stmt *pMax = 0;
         1450  +  char *zRet = 0;
         1451  +  if( bRowid ){
         1452  +    p->rc = prepareFreeAndCollectError(p->dbMain, &pMax, &p->zErrmsg, 
         1453  +        sqlite3_mprintf(
         1454  +          "SELECT max(_rowid_) FROM \"%s%w\"", zWrite, pIter->zTbl
         1455  +        )
         1456  +    );
         1457  +    if( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pMax) ){
         1458  +      sqlite3_int64 iMax = sqlite3_column_int64(pMax, 0);
         1459  +      zRet = rbuMPrintf(p, " WHERE _rowid_ > %lld ", iMax);
         1460  +    }
         1461  +    rbuFinalize(p, pMax);
         1462  +  }else{
         1463  +    char *zOrder = 0;
         1464  +    char *zSelect = 0;
         1465  +    char *zList = 0;
         1466  +    int iPk = 1;
         1467  +    const char *zSep = "";
         1468  +    const char *zSep2 = "";
         1469  +    while( 1 ){
         1470  +      int i;
         1471  +      for(i=0; i<pIter->nTblCol; i++){
         1472  +        if( (int)pIter->abTblPk[i]==iPk ){
         1473  +          const char *zCol = pIter->azTblCol[i];
         1474  +          zOrder = rbuMPrintf(p, "%z%s\"%w\" DESC", zOrder, zSep, zCol);
         1475  +          zList = rbuMPrintf(p, "%z%s\"%w\"", zList, zSep, zCol);
         1476  +          zSelect = rbuMPrintf(p, "%z%squote(\"%w\")", zSelect, zSep2, zCol);
         1477  +          zSep = ", ";
         1478  +          zSep2 = "||','||";
         1479  +        }
         1480  +      }
         1481  +      if( i==pIter->nTblCol ) break;
         1482  +      iPk++;
         1483  +    }
         1484  +
         1485  +    if( p->rc==SQLITE_OK ){
         1486  +      p->rc = prepareFreeAndCollectError(p->dbMain, &pMax, &p->zErrmsg, 
         1487  +          sqlite3_mprintf(
         1488  +            "SELECT %s FROM \"%s%w\" ORDER BY %s LIMIT 1", 
         1489  +                zSelect, zWrite, pIter->zTbl, zOrder
         1490  +          )
         1491  +      );
         1492  +      if( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pMax) ){
         1493  +        const char *zVal = (const char*)sqlite3_column_text(pMax, 0);
         1494  +        zRet = rbuMPrintf(p, " WHERE (%s) > (%s) ", zList, zVal);
         1495  +      }
         1496  +      rbuFinalize(p, pMax);
         1497  +    }
         1498  +
         1499  +    sqlite3_free(zOrder);
         1500  +    sqlite3_free(zSelect);
         1501  +    sqlite3_free(zList);
         1502  +  }
         1503  +  return zRet;
         1504  +}
  1418   1505   
  1419   1506   /*
  1420   1507   ** This function is used to create a SELECT list (the list of SQL 
  1421   1508   ** expressions that follows a SELECT keyword) for a SELECT statement 
  1422   1509   ** used to read from an data_xxx or rbu_tmp_xxx table while updating the 
  1423   1510   ** index object currently indicated by the iterator object passed as the 
  1424   1511   ** second argument. A "PRAGMA index_xinfo = <idxname>" statement is used 
................................................................................
  2216   2303   
  2217   2304           rbuObjIterPrepareTmpInsert(p, pIter, zCollist, zRbuRowid);
  2218   2305         }
  2219   2306   
  2220   2307         /* Create the SELECT statement to read keys from data_xxx */
  2221   2308         if( p->rc==SQLITE_OK ){
  2222   2309           const char *zRbuRowid = "";
         2310  +        char *zStart = 0;
         2311  +        char *zOrder = 0;
  2223   2312           if( bRbuRowid ){
  2224   2313             zRbuRowid = rbuIsVacuum(p) ? ",_rowid_ " : ",rbu_rowid";
  2225   2314           }
         2315  +
         2316  +        if( rbuIsVacuum(p) ){
         2317  +          if( nOffset ){
         2318  +            zStart = rbuVacuumTableStart(p, pIter, bRbuRowid, zWrite);
         2319  +            if( zStart ){
         2320  +              sqlite3_free(zLimit);
         2321  +              zLimit = 0;
         2322  +            }
         2323  +          }
         2324  +          if( bRbuRowid ){
         2325  +            zOrder = rbuMPrintf(p, "_rowid_");
         2326  +          }else{
         2327  +            zOrder = rbuObjIterGetPkList(p, pIter, "");
         2328  +          }
         2329  +        }
         2330  +
         2331  +        if( p->rc==SQLITE_OK ){
  2226   2332           p->rc = prepareFreeAndCollectError(p->dbRbu, &pIter->pSelect, pz,
  2227   2333               sqlite3_mprintf(
  2228         -              "SELECT %s,%s rbu_control%s FROM '%q'%s", 
         2334  +                "SELECT %s,%s rbu_control%s FROM '%q'%s %s %s %s",
  2229   2335                 zCollist, 
  2230   2336                 (rbuIsVacuum(p) ? "0 AS " : ""),
  2231   2337                 zRbuRowid,
  2232         -              pIter->zDataTbl, zLimit
         2338  +                pIter->zDataTbl, (zStart ? zStart : ""), 
         2339  +                (zOrder ? "ORDER BY" : ""), zOrder,
         2340  +                zLimit
  2233   2341               )
  2234   2342           );
  2235   2343         }
         2344  +        sqlite3_free(zStart);
         2345  +        sqlite3_free(zOrder);
         2346  +      }
  2236   2347   
  2237   2348         sqlite3_free(zWhere);
  2238   2349         sqlite3_free(zOldlist);
  2239   2350         sqlite3_free(zNewlist);
  2240   2351         sqlite3_free(zBindings);
  2241   2352       }
  2242   2353       sqlite3_free(zCollist);