/ Check-in [7b051698]
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:Remove some redundant code from sqlite3rbu.c. Add test cases for RBU vacuum.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | rbu-opt
Files: files | file ages | folders
SHA3-256: 7b051698d8a763d9db190fd662931266d24a4982f34f100a28641934c222e37b
User & Date: dan 2019-05-07 16:28:50
Context
2019-05-07
16:37
Optimize the restarting of an RBU vacuum. check-in: 82062351 user: dan tags: trunk
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
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/rbu/rbuvacuum4.test.

67
68
69
70
71
72
73

74
75
76
77
78
79
80
81
82
83
84





85



86













  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






























>










<
>
>
>
>
>

>
>
>

>
>
>
>
>
>
>
>
>
>
>
>
>
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
  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);
  CREATE INDEX x1null ON x1(d, a) WHERE d>15;
}

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


#-------------------------------------------------------------------------
reset_db
do_execsql_test 5.0 {
  CREATE TABLE "a b c"(a, "b b" PRIMARY KEY, "c c");
  CREATE INDEX abc1 ON "a b c"(a, "c c");

  INSERT INTO "a b c" VALUES(NULL, 'a', NULL);
  INSERT INTO "a b c" VALUES(NULL, 'b', NULL);
  INSERT INTO "a b c" VALUES(NULL, 'c', NULL);

  INSERT INTO "a b c" VALUES(1, 2, 3);
  INSERT INTO "a b c" VALUES(3, 9, 1);
  INSERT INTO "a b c" VALUES('aaa', 'bbb', 'ccc');

  CREATE TABLE x(a);
  INSERT INTO x VALUES('a'), ('b'), ('d');
  CREATE UNIQUE INDEX y ON x(a);
}

do_rbu_vacuum_test 5.1 1

finish_test

Changes to ext/rbu/sqlite3rbu.c.

926
927
928
929
930
931
932

933
934
935
936
937
938
939
940
....
1413
1414
1415
1416
1417
1418
1419






1420
1421
1422
1423


1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434

1435
1436
1437
1438
1439
1440
1441
1442



















1443
1444
1445
1446

1447
1448
1449
1450
1451
1452
1453
1454
....
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
....
1500
1501
1502
1503
1504
1505
1506






















1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
....
2270
2271
2272
2273
2274
2275
2276
2277
2278
2279
2280
2281
2282
2283
2284
....
2431
2432
2433
2434
2435
2436
2437
2438
2439
2440
2441
2442
2443
2444
2445
  sqlite3rbu *p = sqlite3_user_data(pCtx);
  const char *zIn;
  assert( argc==1 || argc==2 );

  zIn = (const char*)sqlite3_value_text(argv[0]);
  if( zIn ){
    if( rbuIsVacuum(p) ){

      if( argc==1 || 0==sqlite3_value_int(argv[1]) ){
        sqlite3_result_text(pCtx, zIn, -1, SQLITE_STATIC);
      }
    }else{
      if( strlen(zIn)>4 && memcmp("data", zIn, 4)==0 ){
        int i;
        for(i=4; zIn[i]>='0' && zIn[i]<='9'; i++);
        if( zIn[i]=='_' && zIn[i+1] ){
................................................................................
    const char *z = pIter->azTblCol[i];
    zList = rbuMPrintf(p, "%z%s\"%w\"", zList, zSep, z);
    zSep = ", ";
  }
  return zList;
}







static char *rbuObjIterGetPkList(
  sqlite3rbu *p,                  /* RBU object */
  RbuObjIter *pIter,              /* Object iterator for column names */
  const char *zExtra


){
  int iPk = 1;
  char *zRet = 0;
  const char *zSep = "";
  while( 1 ){
    int i;
    for(i=0; i<pIter->nTblCol; i++){
      if( (int)pIter->abTblPk[i]==iPk ){
        const char *zCol = pIter->azTblCol[i];
        zRet = rbuMPrintf(p, "%z%s\"%w\"%s", zRet, zSep, zCol, zExtra);
        zSep = ", ";

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




















static char *rbuVacuumTableStart(
  sqlite3rbu *p, 
  RbuObjIter *pIter,
  int bRowid,

  const char *zWrite
){
  sqlite3_stmt *pMax = 0;
  char *zRet = 0;
  if( bRowid ){
    p->rc = prepareFreeAndCollectError(p->dbMain, &pMax, &p->zErrmsg, 
        sqlite3_mprintf(
          "SELECT max(_rowid_) FROM \"%s%w\"", zWrite, pIter->zTbl
................................................................................
    );
    if( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pMax) ){
      sqlite3_int64 iMax = sqlite3_column_int64(pMax, 0);
      zRet = rbuMPrintf(p, " WHERE _rowid_ > %lld ", iMax);
    }
    rbuFinalize(p, pMax);
  }else{
    char *zOrder = 0;
    char *zSelect = 0;
    char *zList = 0;
    int iPk = 1;
    const char *zSep = "";
    const char *zSep2 = "";
    while( 1 ){
      int i;
      for(i=0; i<pIter->nTblCol; i++){
        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 ){
      p->rc = prepareFreeAndCollectError(p->dbMain, &pMax, &p->zErrmsg, 
          sqlite3_mprintf(
            "SELECT %s FROM \"%s%w\" ORDER BY %s LIMIT 1", 
                zSelect, zWrite, pIter->zTbl, zOrder
          )
................................................................................
    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;
................................................................................
        );
      }

      /* 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;
            }
          }
................................................................................
              sqlite3_free(zLimit);
              zLimit = 0;
            }
          }
          if( bRbuRowid ){
            zOrder = rbuMPrintf(p, "_rowid_");
          }else{
            zOrder = rbuObjIterGetPkList(p, pIter, "");
          }
        }

        if( p->rc==SQLITE_OK ){
          p->rc = prepareFreeAndCollectError(p->dbRbu, &pIter->pSelect, pz,
              sqlite3_mprintf(
                "SELECT %s,%s rbu_control%s FROM '%q'%s %s %s %s",







>
|







 







>
>
>
>
>
>



|
>
>









|
|
>








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

|
|
<
>
|







 







|
|
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







 







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

|
|







 







|







 







|







926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
....
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474

1475
1476
1477
1478
1479
1480
1481
1482
1483
....
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494



















1495
1496
1497
1498
1499
1500
1501
....
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
....
2302
2303
2304
2305
2306
2307
2308
2309
2310
2311
2312
2313
2314
2315
2316
....
2463
2464
2465
2466
2467
2468
2469
2470
2471
2472
2473
2474
2475
2476
2477
  sqlite3rbu *p = sqlite3_user_data(pCtx);
  const char *zIn;
  assert( argc==1 || argc==2 );

  zIn = (const char*)sqlite3_value_text(argv[0]);
  if( zIn ){
    if( rbuIsVacuum(p) ){
      assert( argc==2 );
      if( 0==sqlite3_value_int(argv[1]) ){
        sqlite3_result_text(pCtx, zIn, -1, SQLITE_STATIC);
      }
    }else{
      if( strlen(zIn)>4 && memcmp("data", zIn, 4)==0 ){
        int i;
        for(i=4; zIn[i]>='0' && zIn[i]<='9'; i++);
        if( zIn[i]=='_' && zIn[i+1] ){
................................................................................
    const char *z = pIter->azTblCol[i];
    zList = rbuMPrintf(p, "%z%s\"%w\"", zList, zSep, z);
    zSep = ", ";
  }
  return zList;
}

/*
** Return a comma separated list of the quoted PRIMARY KEY column names,
** in order, for the current table. Before each column name, add the text
** zPre. After each column name, add the zPost text. Use zSeparator as
** the separator text (usually ", ").
*/
static char *rbuObjIterGetPkList(
  sqlite3rbu *p,                  /* RBU object */
  RbuObjIter *pIter,              /* Object iterator for column names */
  const char *zPre,               /* Before each quoted column name */
  const char *zSeparator,         /* Separator to use between columns */
  const char *zPost               /* After each quoted column name */
){
  int iPk = 1;
  char *zRet = 0;
  const char *zSep = "";
  while( 1 ){
    int i;
    for(i=0; i<pIter->nTblCol; i++){
      if( (int)pIter->abTblPk[i]==iPk ){
        const char *zCol = pIter->azTblCol[i];
        zRet = rbuMPrintf(p, "%z%s%s\"%w\"%s", zRet, zSep, zPre, zCol, zPost);
        zSep = zSeparator;
        break;
      }
    }
    if( i==pIter->nTblCol ) break;
    iPk++;
  }
  return zRet;
}

/*
** This function is called as part of restarting an RBU vacuum within 
** stage 1 of the process (while the *-oal file is being built) while
** updating a table (not an index). The table may be a rowid table or
** a WITHOUT ROWID table. It queries the target database to find the 
** largest key that has already been written to the target table and
** constructs a WHERE clause that can be used to extract the remaining
** rows from the source table. For a rowid table, the WHERE clause
** is of the form:
**
**     "WHERE _rowid_ > ?"
**
** and for WITHOUT ROWID tables:
**
**     "WHERE (key1, key2) > (?, ?)"
**
** Instead of "?" placeholders, the actual WHERE clauses created by
** this function contain literal SQL values.
*/
static char *rbuVacuumTableStart(
  sqlite3rbu *p,                  /* RBU handle */
  RbuObjIter *pIter,              /* RBU iterator object */

  int bRowid,                     /* True for a rowid table */
  const char *zWrite              /* Target table name prefix */
){
  sqlite3_stmt *pMax = 0;
  char *zRet = 0;
  if( bRowid ){
    p->rc = prepareFreeAndCollectError(p->dbMain, &pMax, &p->zErrmsg, 
        sqlite3_mprintf(
          "SELECT max(_rowid_) FROM \"%s%w\"", zWrite, pIter->zTbl
................................................................................
    );
    if( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pMax) ){
      sqlite3_int64 iMax = sqlite3_column_int64(pMax, 0);
      zRet = rbuMPrintf(p, " WHERE _rowid_ > %lld ", iMax);
    }
    rbuFinalize(p, pMax);
  }else{
    char *zOrder = rbuObjIterGetPkList(p, pIter, "", ", ", " DESC");
    char *zSelect = rbuObjIterGetPkList(p, pIter, "quote(", "||','||", ")");
    char *zList = rbuObjIterGetPkList(p, pIter, "", ", ", "");




















    if( p->rc==SQLITE_OK ){
      p->rc = prepareFreeAndCollectError(p->dbMain, &pMax, &p->zErrmsg, 
          sqlite3_mprintf(
            "SELECT %s FROM \"%s%w\" ORDER BY %s LIMIT 1", 
                zSelect, zWrite, pIter->zTbl, zOrder
          )
................................................................................
    sqlite3_free(zOrder);
    sqlite3_free(zSelect);
    sqlite3_free(zList);
  }
  return zRet;
}

/*
** This function is called as part of restating an RBU vacuum when the
** current operation is writing content to an index. If possible, it
** queries the target index b-tree for the largest key already written to
** it, then composes and returns an expression that can be used in a WHERE 
** clause to select the remaining required rows from the source table. 
** It is only possible to return such an expression if:
**
**   * The index contains no DESC columns, and
**   * The last key written to the index before the operation was 
**     suspended does not contain any NULL values.
**
** The expression is of the form:
**
**   (index-field1, index-field2, ...) > (?, ?, ...)
**
** except that the "?" placeholders are replaced with literal values.
**
** If the expression cannot be created, NULL is returned. In this case,
** the caller has to use an OFFSET clause to extract only the required 
** rows from the sourct table, just as it does for an RBU update operation.
*/
char *rbuVacuumIndexStart(
  sqlite3rbu *p,                  /* RBU handle */
  RbuObjIter *pIter               /* RBU iterator object */
){
  char *zOrder = 0;
  char *zLhs = 0;
  char *zSelect = 0;
  char *zVector = 0;
  char *zRet = 0;
  int bFailed = 0;
................................................................................
        );
      }

      /* Create the SELECT statement to read keys in sorted order */
      if( p->rc==SQLITE_OK ){
        char *zSql;
        if( rbuIsVacuum(p) ){
          char *zStart = 0;
          if( nOffset ){
            zStart = rbuVacuumIndexStart(p, pIter);
            if( zStart ){
              sqlite3_free(zLimit);
              zLimit = 0;
            }
          }
................................................................................
              sqlite3_free(zLimit);
              zLimit = 0;
            }
          }
          if( bRbuRowid ){
            zOrder = rbuMPrintf(p, "_rowid_");
          }else{
            zOrder = rbuObjIterGetPkList(p, pIter, "", ", ", "");
          }
        }

        if( p->rc==SQLITE_OK ){
          p->rc = prepareFreeAndCollectError(p->dbRbu, &pIter->pSelect, pz,
              sqlite3_mprintf(
                "SELECT %s,%s rbu_control%s FROM '%q'%s %s %s %s",