/ Check-in [6b3261bf]
Login
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:Optimize further 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: 6b3261bfa1d762aa29a57244a30cf5e35655f1fe9c30ee682c186dec29d7a2c2
User & Date: dan 2019-05-06 20:40:23
Context
2019-05-07
16:28
Remove some redundant code from sqlite3rbu.c. Add test cases for RBU vacuum. Closed-Leaf check-in: 7b051698 user: dan tags: rbu-opt
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
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Added ext/rbu/rbuvacuum4.test.













































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
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
# 2019 Jan 3
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file contains tests for the RBU module. More specifically, it
# contains tests to ensure that the sqlite3rbu_vacuum() API works as
# expected.
#

source [file join [file dirname [info script]] rbu_common.tcl]
set testprefix rbuvacuum4

set step 1

do_execsql_test 1.0 {
  CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID;
  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(4, 5, 6);
  INSERT INTO t1 VALUES(7, 8, 9);
}
do_rbu_vacuum_test 1.1 1

#-------------------------------------------------------------------------
reset_db

do_execsql_test 2.0 {
  CREATE TABLE t1(a, b, c, PRIMARY KEY(a, b, c)) WITHOUT ROWID;
  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(4, 5, 6);
  INSERT INTO t1 VALUES(7, 8, 9);
}
do_rbu_vacuum_test 2.1 1
do_execsql_test 2.2 {
  SELECT * FROM t1;
} {1 2 3 4 5 6 7 8 9}

#-------------------------------------------------------------------------
reset_db

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

do_rbu_vacuum_test 3.1 1

do_execsql_test 3.2 {
  SELECT * FROM t1;
} {1 2 3 4 5 6 7 8 9}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 4.0 {
  CREATE TABLE x1(a, b, c, d, PRIMARY KEY(c, b)) WITHOUT ROWID;
  INSERT INTO x1 VALUES(1, 1, 1, 1);
  INSERT INTO x1 VALUES(1, 1, 2, 1);
  INSERT INTO x1 VALUES(1, 2, 2, 1);

  INSERT INTO x1 VALUES(NULL, 2, 3, NULL);
  INSERT INTO x1 VALUES(NULL, 2, 4, NULL);
  INSERT INTO x1 VALUES(NULL, 2, 5, NULL);

  CREATE INDEX x1ad ON x1(d, a);
}

do_rbu_vacuum_test 4.1.1 1

do_execsql_test 4.2 {
  SELECT count(*) fROM x1
} 6

do_rbu_vacuum_test 4.1.2 0

finish_test


Changes to ext/rbu/sqlite3rbu.c.

1472
1473
1474
1475
1476
1477
1478

1479
1480
1481
1482
1483
1484
1485
....
1498
1499
1500
1501
1502
1503
1504






























































































1505
1506
1507
1508
1509
1510
1511
....
2175
2176
2177
2178
2179
2180
2181









2182
2183
2184
2185


2186
2187

2188
2189
2190
2191
2192
2193
2194
....
2203
2204
2205
2206
2207
2208
2209

2210



2211
2212
2213
2214
2215
2216
2217
        if( (int)pIter->abTblPk[i]==iPk ){
          const char *zCol = pIter->azTblCol[i];
          zOrder = rbuMPrintf(p, "%z%s\"%w\" DESC", zOrder, zSep, zCol);
          zList = rbuMPrintf(p, "%z%s\"%w\"", zList, zSep, zCol);
          zSelect = rbuMPrintf(p, "%z%squote(\"%w\")", zSelect, zSep2, zCol);
          zSep = ", ";
          zSep2 = "||','||";

        }
      }
      if( i==pIter->nTblCol ) break;
      iPk++;
    }

    if( p->rc==SQLITE_OK ){
................................................................................

    sqlite3_free(zOrder);
    sqlite3_free(zSelect);
    sqlite3_free(zList);
  }
  return zRet;
}































































































/*
** This function is used to create a SELECT list (the list of SQL 
** expressions that follows a SELECT keyword) for a SELECT statement 
** used to read from an data_xxx or rbu_tmp_xxx table while updating the 
** index object currently indicated by the iterator object passed as the 
** second argument. A "PRAGMA index_xinfo = <idxname>" statement is used 
................................................................................
        );
      }

      /* Create the SELECT statement to read keys in sorted order */
      if( p->rc==SQLITE_OK ){
        char *zSql;
        if( rbuIsVacuum(p) ){









          zSql = sqlite3_mprintf(
              "SELECT %s, 0 AS rbu_control FROM '%q' %s ORDER BY %s%s",
              zCollist, 
              pIter->zDataTbl,


              zPart, zCollist, zLimit
          );

        }else

        if( pIter->eType==RBU_PK_EXTERNAL || pIter->eType==RBU_PK_NONE ){
          zSql = sqlite3_mprintf(
              "SELECT %s, rbu_control FROM %s.'rbu_tmp_%q' %s ORDER BY %s%s",
              zCollist, p->zStateDb, pIter->zDataTbl,
              zPart, zCollist, zLimit
................................................................................
              zCollist, p->zStateDb, pIter->zDataTbl, zPart,
              zCollist, pIter->zDataTbl, 
              zPart,
              (zPart ? "AND" : "WHERE"),
              zCollist, zLimit
          );
        }

        p->rc = prepareFreeAndCollectError(p->dbRbu, &pIter->pSelect, pz, zSql);



      }

      sqlite3_free(zImposterCols);
      sqlite3_free(zImposterPK);
      sqlite3_free(zWhere);
      sqlite3_free(zBind);
      sqlite3_free(zPart);







>







 







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







 







>
>
>
>
>
>
>
>
>

|


>
>
|

>







 







>
|
>
>
>







1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
....
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
....
2270
2271
2272
2273
2274
2275
2276
2277
2278
2279
2280
2281
2282
2283
2284
2285
2286
2287
2288
2289
2290
2291
2292
2293
2294
2295
2296
2297
2298
2299
2300
2301
....
2310
2311
2312
2313
2314
2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
        if( (int)pIter->abTblPk[i]==iPk ){
          const char *zCol = pIter->azTblCol[i];
          zOrder = rbuMPrintf(p, "%z%s\"%w\" DESC", zOrder, zSep, zCol);
          zList = rbuMPrintf(p, "%z%s\"%w\"", zList, zSep, zCol);
          zSelect = rbuMPrintf(p, "%z%squote(\"%w\")", zSelect, zSep2, zCol);
          zSep = ", ";
          zSep2 = "||','||";
          break;
        }
      }
      if( i==pIter->nTblCol ) break;
      iPk++;
    }

    if( p->rc==SQLITE_OK ){
................................................................................

    sqlite3_free(zOrder);
    sqlite3_free(zSelect);
    sqlite3_free(zList);
  }
  return zRet;
}

char *rbuVacuumIndexStart(
  sqlite3rbu *p, 
  RbuObjIter *pIter
){
  char *zOrder = 0;
  char *zLhs = 0;
  char *zSelect = 0;
  char *zVector = 0;
  char *zRet = 0;
  int bFailed = 0;

  if( p->rc==SQLITE_OK ){
    const char *zSep = "";
    int iCol = 0;
    sqlite3_stmt *pXInfo = 0;
    p->rc = prepareFreeAndCollectError(p->dbMain, &pXInfo, &p->zErrmsg,
        sqlite3_mprintf("PRAGMA main.index_xinfo = %Q", pIter->zIdx)
    );
    while( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pXInfo) ){
      int iCid = sqlite3_column_int(pXInfo, 1);
      const char *zCollate = (const char*)sqlite3_column_text(pXInfo, 4);
      const char *zCol;
      if( sqlite3_column_int(pXInfo, 3) ){
        bFailed = 1;
        break;
      }

      if( iCid<0 ){
        if( pIter->eType==RBU_PK_IPK ){
          int i;
          for(i=0; pIter->abTblPk[i]==0; i++);
          assert( i<pIter->nTblCol );
          zCol = pIter->azTblCol[i];
        }else{
          zCol = "_rowid_";
        }
      }else{
        zCol = pIter->azTblCol[iCid];
      }

      zLhs = rbuMPrintf(p, "%z%s \"%w\" COLLATE %Q",
          zLhs, zSep, zCol, zCollate
      );
      zOrder = rbuMPrintf(p, "%z%s \"rbu_imp_%d%w\" COLLATE %Q DESC",
          zOrder, zSep, iCol, zCol, zCollate
      );
      zSelect = rbuMPrintf(p, "%z%s quote(\"rbu_imp_%d%w\")",
          zSelect, zSep, iCol, zCol
      );
      zSep = ", ";
      iCol++;
    }
    rbuFinalize(p, pXInfo);
  }
  if( bFailed ) goto index_start_out;

  if( p->rc==SQLITE_OK ){
    int iCol;
    sqlite3_stmt *pSel = 0;

    if( p->rc==SQLITE_OK ){
      p->rc = prepareFreeAndCollectError(p->dbMain, &pSel, &p->zErrmsg,
          sqlite3_mprintf("SELECT %s FROM \"rbu_imp_%w\" ORDER BY %s LIMIT 1",
            zSelect, pIter->zTbl, zOrder
          )
      );
    }
    if( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSel) ){
      const char *zSep = "";
      for(iCol=0; iCol<pIter->nCol; iCol++){
        const char *zQuoted = (const char*)sqlite3_column_text(pSel, iCol);
        if( zQuoted[0]=='N' ){
          bFailed = 1;
          break;
        }
        zVector = rbuMPrintf(p, "%z%s%s", zVector, zSep, zQuoted);
        zSep = ", ";
      }

      if( !bFailed ){
        zRet = rbuMPrintf(p, "(%s) > (%s)", zLhs, zVector);
      }
    }
    rbuFinalize(p, pSel);
  }

 index_start_out:
  sqlite3_free(zOrder);
  sqlite3_free(zSelect);
  sqlite3_free(zVector);
  sqlite3_free(zLhs);
  return zRet;
}

/*
** This function is used to create a SELECT list (the list of SQL 
** expressions that follows a SELECT keyword) for a SELECT statement 
** used to read from an data_xxx or rbu_tmp_xxx table while updating the 
** index object currently indicated by the iterator object passed as the 
** second argument. A "PRAGMA index_xinfo = <idxname>" statement is used 
................................................................................
        );
      }

      /* Create the SELECT statement to read keys in sorted order */
      if( p->rc==SQLITE_OK ){
        char *zSql;
        if( rbuIsVacuum(p) ){
          const char *zStart = 0;
          if( nOffset ){
            zStart = rbuVacuumIndexStart(p, pIter);
            if( zStart ){
              sqlite3_free(zLimit);
              zLimit = 0;
            }
          }

          zSql = sqlite3_mprintf(
              "SELECT %s, 0 AS rbu_control FROM '%q' %s %s %s ORDER BY %s%s",
              zCollist, 
              pIter->zDataTbl,
              zPart, 
              (zStart ? (zPart ? "AND" : "WHERE") : ""), zStart,
              zCollist, zLimit
          );
          sqlite3_free(zStart);
        }else

        if( pIter->eType==RBU_PK_EXTERNAL || pIter->eType==RBU_PK_NONE ){
          zSql = sqlite3_mprintf(
              "SELECT %s, rbu_control FROM %s.'rbu_tmp_%q' %s ORDER BY %s%s",
              zCollist, p->zStateDb, pIter->zDataTbl,
              zPart, zCollist, zLimit
................................................................................
              zCollist, p->zStateDb, pIter->zDataTbl, zPart,
              zCollist, pIter->zDataTbl, 
              zPart,
              (zPart ? "AND" : "WHERE"),
              zCollist, zLimit
          );
        }
        if( p->rc==SQLITE_OK ){
          p->rc = prepareFreeAndCollectError(p->dbRbu,&pIter->pSelect,pz,zSql);
        }else{
          sqlite3_free(zSql);
        }
      }

      sqlite3_free(zImposterCols);
      sqlite3_free(zImposterPK);
      sqlite3_free(zWhere);
      sqlite3_free(zBind);
      sqlite3_free(zPart);