SQLite

  sqlite3 *db;
};

struct CidxCursor {
  sqlite3_vtab_cursor base;       /* Base class.  Must be first */
  sqlite3_stmt *pStmt;
};

typedef struct CidxColumn CidxColumn;
struct CidxColumn {
  char *zName;
  char *zColl;
  int bDesc;
};

static void *cidxMalloc(int *pRc, int n){
  void *pRet = 0;
  assert( n!=0 );
  if( *pRc==SQLITE_OK ){
    pRet = sqlite3_malloc(n);
    if( pRet ){

/* We have reached EOF if previous sqlite3_step() returned
** anything other than SQLITE_ROW;
*/
static int cidxEof(sqlite3_vtab_cursor *pCursor){
  CidxCursor *pCsr = (CidxCursor*)pCursor;
  return pCsr->pStmt==0;
}

static char *cidxMprintf(int *pRc, const char *zFmt, ...){
  char *zRet = 0;
  va_list ap;
  va_start(ap, zFmt);
  zRet = sqlite3_vmprintf(zFmt, ap);
  if( *pRc==SQLITE_OK ){
    if( zRet==0 ){
      *pRc = SQLITE_NOMEM;
    }
  }else{
    sqlite3_free(zRet);
    zRet = 0;
  }
  va_end(ap);
  return zRet;
}

static sqlite3_stmt *cidxPrepare(
  int *pRc, CidxCursor *pCsr, const char *zFmt, ...
){
  sqlite3_stmt *pRet = 0;
  char *zSql;
  va_list ap;                     /* ... printf arguments */

  }
  return zRet;
}

static int cidxLookupIndex(
  CidxCursor *pCsr,               /* Cursor object */
  const char *zIdx,               /* Name of index to look up */
  int *pnCol,                     /* OUT: Number of columns in index */
  CidxColumn **paCol,             /* OUT: Columns */
  char **pzTab,                   /* OUT: Table name */
  char **pzCurrentKey,            /* OUT: Expression for current_key */
  char **pzOrderBy,               /* OUT: ORDER BY expression list */
  char **pzSubWhere,              /* OUT: sub-query WHERE clause */
  char **pzSubExpr                /* OUT: sub-query WHERE clause */
){
  int rc = SQLITE_OK;
  char *zTab = 0;
  char *zCurrentKey = 0;
  char *zOrderBy = 0;
  char *zSubWhere = 0;
  char *zSubExpr = 0;
  CidxColumn *aCol = 0;

  sqlite3_stmt *pFindTab = 0;
  sqlite3_stmt *pGroup = 0;
    
  /* Find the table */
  pFindTab = cidxPrepare(&rc, pCsr, 
      "SELECT tbl_name FROM sqlite_master WHERE name=%Q AND type='index'",
      zIdx
  );
  if( rc==SQLITE_OK && sqlite3_step(pFindTab)==SQLITE_ROW ){
    zTab = cidxStrdup(&rc, (const char*)sqlite3_column_text(pFindTab, 0));
  }
  cidxFinalize(&rc, pFindTab);
  if( rc==SQLITE_OK && zTab==0 ){
    rc = SQLITE_ERROR;
  }

  pGroup = cidxPrepare(&rc, pCsr,
      "SELECT group_concat("
      "  coalesce('quote(' || name || ')', 'rowid'), '|| '','' ||'"
      ") AS zCurrentKey,"
      "       group_concat("
      "  coalesce(name, 'rowid') || CASE WHEN desc THEN ' DESC' ELSE '' END,"
      "  ', '"
      ") AS zOrderBy,"
      "       group_concat("
      "         CASE WHEN key==1 THEN NULL ELSE "
      "  coalesce(name, 'rowid') || ' IS \"%w\".' || coalesce(name, 'rowid') "
      "         END,"
      "  ' AND '"
      ") AS zSubWhere,"
      "       group_concat("
      "         CASE WHEN key==0 THEN NULL ELSE "
      "  coalesce(name, 'rowid') || ' IS \"%w\".' || coalesce(name, 'rowid') "
      "         END,"
      "  ' AND '"
      ") AS zSubExpr,"
      "       count(*) AS nCol"
      " FROM pragma_index_xinfo(%Q);"
      , zIdx, zIdx, zIdx
  );
  if( rc==SQLITE_OK && sqlite3_step(pGroup)==SQLITE_ROW ){
    zCurrentKey = cidxStrdup(&rc, (const char*)sqlite3_column_text(pGroup, 0));
    zOrderBy = cidxStrdup(&rc, (const char*)sqlite3_column_text(pGroup, 1));
    zSubWhere = cidxStrdup(&rc, (const char*)sqlite3_column_text(pGroup, 2));
    zSubExpr = cidxStrdup(&rc, (const char*)sqlite3_column_text(pGroup, 3));
    *pnCol = sqlite3_column_int(pGroup, 4);
  }
  cidxFinalize(&rc, pGroup);

  pGroup = cidxPrepare(&rc, pCsr, "PRAGMA index_xinfo(%Q)", zIdx);
  if( rc==SQLITE_OK ){
    int nByte = 0;
    int nCol = 0;
    while( sqlite3_step(pGroup)==SQLITE_ROW ){
      const char *zName = (const char*)sqlite3_column_text(pGroup, 2);
      const char *zColl = (const char*)sqlite3_column_text(pGroup, 4);
      if( zName==0 ) zName = "rowid";
      nCol++;
      nByte += strlen(zName)+1 + strlen(zColl)+1;
    }
    rc = sqlite3_reset(pGroup);
    aCol = (CidxColumn*)cidxMalloc(&rc, sizeof(CidxColumn)*nCol + nByte);

    if( rc==SQLITE_OK ){
      int iCol = 0;
      char *z = (char*)&aCol[nCol];
      while( sqlite3_step(pGroup)==SQLITE_ROW ){
        int nName, nColl;
        const char *zName = (const char*)sqlite3_column_text(pGroup, 2);
        const char *zColl = (const char*)sqlite3_column_text(pGroup, 4);
        if( zName==0 ) zName = "rowid";

        nName = strlen(zName);
        nColl = strlen(zColl);
        memcpy(z, zName, nName);
        aCol[iCol].zName = z;
        z += nName+1;

        memcpy(z, zColl, nColl);
        aCol[iCol].zColl = z;
        z += nColl+1;

        aCol[iCol].bDesc = sqlite3_column_int(pGroup, 3);
        iCol++;
      }
    }
    cidxFinalize(&rc, pGroup);
  }
  
  if( rc!=SQLITE_OK ){
    sqlite3_free(zTab);
    sqlite3_free(zCurrentKey);
    sqlite3_free(zOrderBy);
    sqlite3_free(zSubWhere);
    sqlite3_free(zSubExpr);
    sqlite3_free(aCol);
  }else{
    *pzTab = zTab;
    *pzCurrentKey = zCurrentKey;
    *pzOrderBy = zOrderBy;
    *pzSubWhere = zSubWhere;
    *pzSubExpr = zSubExpr;
    *paCol = aCol;
  }

  return rc;
}

static int cidxDecodeAfter(
  CidxCursor *pCsr, 
  int nCol, 
  const char *zAfterKey, 
  char ***pazAfter
){
  char **azAfter;
  int rc = SQLITE_OK;
  int nAfterKey = strlen(zAfterKey);

  azAfter = cidxMalloc(&rc, sizeof(char*)*nCol + nAfterKey+1);
  if( rc==SQLITE_OK ){
    int i;
    char *zCopy = (char*)&azAfter[nCol];
    char *p = zCopy;
    memcpy(zCopy, zAfterKey, nAfterKey+1);
    for(i=0; i<nCol; i++){
      while( *p==' ' ) p++;

      /* Check NULL values */
      if( *p=='N' ){
        if( memcmp(p, "NULL", 4) ) goto parse_error;
        p += 4;
      }

      /* Check strings and blob literals */
      else if( *p=='X' || *p=='\'' ){
        azAfter[i] = p;
        if( *p=='X' ) p++;
        if( *p!='\'' ) goto parse_error;
        p++;
        while( 1 ){
          if( *p=='\0' ) goto parse_error;
          if( *p=='\'' ){
            p++;
            if( *p!='\'' ) break;
          }
          p++;
        }
      }

      /* Check numbers */
      else{
        azAfter[i] = p;
        while( (*p>='0' && *p<='9') 
            || *p=='.' || *p=='+' || *p=='-' || *p=='e' || *p=='E'
        ){
          p++;
        }
      }

      while( *p==' ' ) p++;
      if( *p!=(i==(nCol-1) ? '\0' : ',') ){
        goto parse_error;
      }
      *p++ = '\0';
    }
  }

  *pazAfter = azAfter;
  return rc;

 parse_error:
  sqlite3_free(azAfter);
  *pazAfter = 0;
  cidxCursorError(pCsr, "%s", "error parsing after value");
  return SQLITE_ERROR;
}

static char *cidxWhere(
  int *pRc, CidxColumn *aCol, char **azAfter, int iGt, int bLastIsNull
){
  char *zRet = 0;
  const char *zSep = "";
  int i;

  for(i=0; i<iGt; i++){
    zRet = cidxMprintf(pRc, "%z%s%s COLLATE %s IS %s", zRet, 
        zSep, aCol[i].zName, aCol[i].zColl, (azAfter[i] ? azAfter[i] : "NULL")
    );
    zSep = " AND ";
  }

  if( bLastIsNull ){
    zRet = cidxMprintf(pRc, "%z%s%s IS NULL", zRet, zSep, aCol[iGt].zName);
  }
  else if( azAfter[iGt] ){
    zRet = cidxMprintf(pRc, "%z%s%s COLLATE %s %s %s", zRet, 
        zSep, aCol[iGt].zName, aCol[iGt].zColl, (aCol[iGt].bDesc ? "<" : ">"), 
        azAfter[iGt]
    );
  }else{
    zRet = cidxMprintf(pRc, "%z%s%s IS NOT NULL", zRet, zSep, aCol[iGt].zName);
  }

  return zRet;
}

static char *cidxColumnList(int *pRc, CidxColumn *aCol, int nCol){
  int i;
  char *zRet = 0;
  const char *zSep = "";
  for(i=0; i<nCol; i++){
    zRet = cidxMprintf(pRc, "%z%s%s", zRet, zSep, aCol[i].zName);
    zSep = ",";
  }
  return zRet;
}

/* 
** Position a cursor back to the beginning.
*/
static int cidxFilter(
  sqlite3_vtab_cursor *pCursor, 

    zIdxName = (const char*)sqlite3_value_text(argv[0]);
    if( argc>1 ){
      zAfterKey = (const char*)sqlite3_value_text(argv[1]);
    }
  }

  if( zIdxName ){
    int nCol = 0;
    char *zTab = 0;
    char *zCurrentKey = 0;
    char *zOrderBy = 0;
    char *zSubWhere = 0;
    char *zSubExpr = 0;
    char **azAfter = 0;
    CidxColumn *aCol = 0;

    rc = cidxLookupIndex(pCsr, zIdxName, 
        &nCol, &aCol, &zTab, &zCurrentKey, &zOrderBy, &zSubWhere, &zSubExpr
    );

    if( rc==SQLITE_OK && zAfterKey ){
      rc = cidxDecodeAfter(pCsr, nCol, zAfterKey, &azAfter);
    }

    if( rc || zAfterKey==0 ){
      pCsr->pStmt = cidxPrepare(&rc, pCsr, 
          "SELECT (SELECT %s FROM %Q WHERE %s), %s FROM %Q AS %Q ORDER BY %s",
          zSubExpr, zTab, zSubWhere, zCurrentKey, zTab, zIdxName, zOrderBy
      );
      /* printf("SQL: %s\n", sqlite3_sql(pCsr->pStmt)); */
    }else{
      char *zList = cidxColumnList(&rc, aCol, nCol);
      const char *zSep = "";
      char *zSql;
      int i;

      zSql = cidxMprintf(&rc, "SELECT (SELECT %s FROM %Q WHERE %s), %s FROM (",
          zSubExpr, zTab, zSubWhere, zCurrentKey
      );
      for(i=nCol-1; i>=0; i--){
        int j;
        if( aCol[i].bDesc && azAfter[i]==0 ) continue;
        for(j=0; j<2; j++){
          char *zWhere = cidxWhere(&rc, aCol, azAfter, i, j);
          zSql = cidxMprintf(&rc, 
              "%z%s SELECT * FROM (SELECT %s FROM %Q WHERE %z ORDER BY %s)",
              zSql, zSep, zList, zTab, zWhere, zOrderBy
              );
          zSep = " UNION ALL ";
          if( aCol[i].bDesc==0 ) break;
        }
      }
      zSql = cidxMprintf(&rc, "%z) AS %Q", zSql, zIdxName);
      sqlite3_free(zList);

      /* printf("SQL: %s\n", zSql); */
      pCsr->pStmt = cidxPrepare(&rc, pCsr, "%z", zSql);
    }

    sqlite3_free(zTab);
    sqlite3_free(zCurrentKey);
    sqlite3_free(zOrderBy);
    sqlite3_free(zSubWhere);
    sqlite3_free(zSubExpr);
    sqlite3_free(aCol);
    sqlite3_free(azAfter);
  }

  if( pCsr->pStmt ){
    assert( rc==SQLITE_OK );
    rc = cidxNext(pCursor);
  }
  return rc;

  CREATE TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a);
  INSERT INTO t1 VALUES('one', 2);
  INSERT INTO t1 VALUES('two', 4);
  INSERT INTO t1 VALUES('three', 6);
  INSERT INTO t1 VALUES('four', 8);
  INSERT INTO t1 VALUES('five', 10);

  CREATE INDEX i2 ON t1(a DESC);
}

db enable_load_extension 1
do_execsql_test 1.1 {
  SELECT load_extension('../checkindex.so');
} {{}}

proc incr_index_check {idx nStep} {
  set Q {
    SELECT errmsg, current_key FROM incremental_index_check($idx, $after)
    LIMIT $nStep
  }

  set res [list]
  while {1} {
    unset -nocomplain current_key
    set res1 [db eval $Q]
    if {[llength $res1]==0} break
    set res [concat $res $res1]
    set after [lindex $res end]
  }

  return $res
}

proc do_index_check_test {tn idx res} {
  uplevel [list do_execsql_test $tn.1 "
    SELECT errmsg, current_key FROM incremental_index_check('$idx');
  " $res]

  uplevel [list do_test $tn.2 "incr_index_check $idx 1" [list {*}$res]]
  #uplevel [list do_test $tn.3 "incr_index_check $idx 2" [list {*}$res]]
  #uplevel [list do_test $tn.4 "incr_index_check $idx 5" [list {*}$res]]
}

do_execsql_test 1.2 {
  SELECT errmsg IS NULL, current_key FROM incremental_index_check('i1');
} {
  1 'five',5
  1 'four',4
  1 'one',1
  1 'three',3
  1 'two',2
}

do_index_check_test 1.3 i1 {
  {} 'five',5
  {} 'four',4
  {} 'one',1
  {} 'three',3
  {} 'two',2
}

do_index_check_test 1.4 i2 {
  {} 'two',2
  {} 'three',3
  {} 'one',1
  {} 'four',4
  {} 'five',5
}

do_test 1.5 {
  set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t1' }]
  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $tblroot
  db eval {CREATE TABLE xt1(a, b)}
  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 0

  execsql {
    UPDATE xt1 SET a='six' WHERE rowid=3;
    DELETE FROM xt1 WHERE rowid = 5;
  }

  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 1
} {}

do_index_check_test 1.6 i1 {
  {row missing} 'five',5
  {} 'four',4
  {} 'one',1
  {row data mismatch} 'three',3
  {} 'two',2
}

do_index_check_test 1.7 i2 {
  {} 'two',2
  {row data mismatch} 'three',3
  {} 'one',1
  {} 'four',4
  {row missing} 'five',5
}

#--------------------------------------------------------------------------
do_execsql_test 2.0 {

  CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c, d);

  INSERT INTO t2 VALUES(1, NULL, 1, 1);
  INSERT INTO t2 VALUES(2, 1, NULL, 1);
  INSERT INTO t2 VALUES(3, 1, 1, NULL);

  INSERT INTO t2 VALUES(4, 2, 2, 1);
  INSERT INTO t2 VALUES(5, 2, 2, 2);
  INSERT INTO t2 VALUES(6, 2, 2, 3);

  INSERT INTO t2 VALUES(7, 2, 2, 1);
  INSERT INTO t2 VALUES(8, 2, 2, 2);
  INSERT INTO t2 VALUES(9, 2, 2, 3);

  CREATE INDEX i3 ON t2(b, c, d);
  CREATE INDEX i4 ON t2(b DESC, c DESC, d DESC);
  CREATE INDEX i5 ON t2(d, c DESC, b);
}

do_index_check_test 2.1 i3 {
  {} NULL,1,1,1 
  {} 1,NULL,1,2 
  {} 1,1,NULL,3 
  {} 2,2,1,4 
  {} 2,2,1,7 
  {} 2,2,2,5
  {} 2,2,2,8 
  {} 2,2,3,6 
  {} 2,2,3,9
}

do_index_check_test 2.2 i4 {
  {} 2,2,3,6 
  {} 2,2,3,9
  {} 2,2,2,5
  {} 2,2,2,8 
  {} 2,2,1,4 
  {} 2,2,1,7 
  {} 1,1,NULL,3 
  {} 1,NULL,1,2 
  {} NULL,1,1,1 
}

do_index_check_test 2.3 i5 {
  {} NULL,1,1,3 
  {} 1,2,2,4 
  {} 1,2,2,7 
  {} 1,1,NULL,1 
  {} 1,NULL,1,2 
  {} 2,2,2,5 
  {} 2,2,2,8 
  {} 3,2,2,6 
  {} 3,2,2,9
}

do_execsql_test 3.0 {

  CREATE TABLE t3(w, x, y, z PRIMARY KEY) WITHOUT ROWID;
  CREATE INDEX t3wxy ON t3(w, x, y);
  CREATE INDEX t3wxy2 ON t3(w DESC, x DESC, y DESC);

  INSERT INTO t3 VALUES(NULL, NULL, NULL, 1);
  INSERT INTO t3 VALUES(NULL, NULL, NULL, 2);
  INSERT INTO t3 VALUES(NULL, NULL, NULL, 3);

  INSERT INTO t3 VALUES('a', NULL, NULL, 4);
  INSERT INTO t3 VALUES('a', NULL, NULL, 5);
  INSERT INTO t3 VALUES('a', NULL, NULL, 6);

  INSERT INTO t3 VALUES('a', 'b', NULL, 7);
  INSERT INTO t3 VALUES('a', 'b', NULL, 8);
  INSERT INTO t3 VALUES('a', 'b', NULL, 9);

}

do_index_check_test 3.1 t3wxy {
  {} NULL,NULL,NULL,1 {} NULL,NULL,NULL,2 {} NULL,NULL,NULL,3 
  {} 'a',NULL,NULL,4  {} 'a',NULL,NULL,5  {} 'a',NULL,NULL,6 
  {} 'a','b',NULL,7   {} 'a','b',NULL,8   {} 'a','b',NULL,9 
}
do_index_check_test 3.2 t3wxy2 {
  {} 'a','b',NULL,7   {} 'a','b',NULL,8   {} 'a','b',NULL,9 
  {} 'a',NULL,NULL,4  {} 'a',NULL,NULL,5  {} 'a',NULL,NULL,6 
  {} NULL,NULL,NULL,1 {} NULL,NULL,NULL,2 {} NULL,NULL,NULL,3 
}

finish_test