SQLite

# 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

        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);