SQLite

/* Size of hash table Rtree.aHash. This hash table is not expected to
** ever contain very many entries, so a fixed number of buckets is 
** used.
*/
#define HASHSIZE 128

/* The xBestIndex method of this virtual table requires an estimate of
** the number of rows in the virtual table to calculate the costs of
** various strategies. If possible, this estimate is loaded from the
** sqlite_stat1 table (with RTREE_MIN_ROWEST as a hard-coded minimum).
** Otherwise, if no sqlite_stat1 entry is available, use 
** RTREE_DEFAULT_ROWEST.
*/
#define RTREE_DEFAULT_ROWEST 1048576
#define RTREE_MIN_ROWEST         100

/* 
** An rtree virtual-table object.
*/
struct Rtree {
  sqlite3_vtab base;
  sqlite3 *db;                /* Host database connection */
  int iNodeSize;              /* Size in bytes of each node in the node table */
  int nDim;                   /* Number of dimensions */
  int nBytesPerCell;          /* Bytes consumed per cell */
  int iDepth;                 /* Current depth of the r-tree structure */
  char *zDb;                  /* Name of database containing r-tree table */
  char *zName;                /* Name of r-tree table */ 
  RtreeNode *aHash[HASHSIZE]; /* Hash table of in-memory nodes. */ 
  int nBusy;                  /* Current number of users of this structure */
  i64 nRowEst;                /* Estimated number of rows in this table */

  /* List of nodes removed during a CondenseTree operation. List is
  ** linked together via the pointer normally used for hash chains -
  ** RtreeNode.pNext. RtreeNode.iNode stores the depth of the sub-tree 
  ** headed by the node (leaf nodes have RtreeNode.iNode==0).
  */
  RtreeNode *pDeleted;

      assert( rc!=SQLITE_OK || !pCsr->pNode || pCsr->iCell<NCELL(pCsr->pNode) );
    }
  }

  rtreeRelease(pRtree);
  return rc;
}

/*
** Set the pIdxInfo->estimatedRows variable to nRow. Unless this
** extension is currently being used by a version of SQLite too old to
** support estimatedRows. In that case this function is a no-op.
*/
static void setEstimatedRows(sqlite3_index_info *pIdxInfo, i64 nRow){
#if SQLITE_VERSION_NUMBER>=308002
  if( sqlite3_libversion_number()>=300802 ){
    pIdxInfo->estimatedRows = nRow;
  }
#endif
}

/*
** Rtree virtual table module xBestIndex method. There are three
** table scan strategies to choose from (in order from most to 
** least desirable):
**
**   idxNum     idxStr        Strategy

**   ----------------------
**
** The second of each pair of bytes identifies the coordinate column
** to which the constraint applies. The leftmost coordinate column
** is 'a', the second from the left 'b' etc.
*/
static int rtreeBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
  Rtree *pRtree = (Rtree*)tab;
  int rc = SQLITE_OK;
  int ii;
  i64 nRow;                       /* Estimated rows returned by this scan */

  int iIdx = 0;
  char zIdxStr[RTREE_MAX_DIMENSIONS*8+1];
  memset(zIdxStr, 0, sizeof(zIdxStr));


  assert( pIdxInfo->idxStr==0 );
  for(ii=0; ii<pIdxInfo->nConstraint && iIdx<(int)(sizeof(zIdxStr)-1); ii++){
    struct sqlite3_index_constraint *p = &pIdxInfo->aConstraint[ii];

    if( p->usable && p->iColumn==0 && p->op==SQLITE_INDEX_CONSTRAINT_EQ ){
      /* We have an equality constraint on the rowid. Use strategy 1. */
      int jj;
      for(jj=0; jj<ii; jj++){
        pIdxInfo->aConstraintUsage[jj].argvIndex = 0;
        pIdxInfo->aConstraintUsage[jj].omit = 0;
      }
      pIdxInfo->idxNum = 1;
      pIdxInfo->aConstraintUsage[ii].argvIndex = 1;
      pIdxInfo->aConstraintUsage[jj].omit = 1;

      /* This strategy involves a two rowid lookups on an B-Tree structures
      ** and then a linear search of an R-Tree node. This should be 
      ** considered almost as quick as a direct rowid lookup (for which 
      ** sqlite uses an internal cost of 0.0). It is expected to return
      ** a single row.
      */ 
      pIdxInfo->estimatedCost = 30.0;
      setEstimatedRows(pIdxInfo, 1);
      return SQLITE_OK;
    }

    if( p->usable && (p->iColumn>0 || p->op==SQLITE_INDEX_CONSTRAINT_MATCH) ){
      u8 op;
      switch( p->op ){
        case SQLITE_INDEX_CONSTRAINT_EQ: op = RTREE_EQ; break;

  }

  pIdxInfo->idxNum = 2;
  pIdxInfo->needToFreeIdxStr = 1;
  if( iIdx>0 && 0==(pIdxInfo->idxStr = sqlite3_mprintf("%s", zIdxStr)) ){
    return SQLITE_NOMEM;
  }

  nRow = pRtree->nRowEst / (iIdx + 1);
  pIdxInfo->estimatedCost = (double)6.0 * (double)nRow;
  setEstimatedRows(pIdxInfo, nRow);

  return rc;
}

/*
** Return the N-dimensional volumn of the cell stored in *p.
*/
static RtreeDValue cellArea(Rtree *pRtree, RtreeCell *p){

  );
  if( zSql ){
    rc = sqlite3_exec(pRtree->db, zSql, 0, 0, 0);
    sqlite3_free(zSql);
  }
  return rc;
}

/*
** This function populates the pRtree->nRowEst variable with an estimate
** of the number of rows in the virtual table. If possible, this is based
** on sqlite_stat1 data. Otherwise, use RTREE_DEFAULT_ROWEST.
*/
static int rtreeQueryStat1(sqlite3 *db, Rtree *pRtree){
  const char *zSql = "SELECT stat FROM sqlite_stat1 WHERE tbl= ? || '_rowid'";
  sqlite3_stmt *p;
  int rc;
  i64 nRow = 0;

  rc = sqlite3_prepare_v2(db, zSql, -1, &p, 0);
  if( rc==SQLITE_OK ){
    sqlite3_bind_text(p, 1, pRtree->zName, -1, SQLITE_STATIC);
    if( sqlite3_step(p)==SQLITE_ROW ) nRow = sqlite3_column_int64(p, 0);
    rc = sqlite3_finalize(p);
  }else if( rc!=SQLITE_NOMEM ){
    rc = SQLITE_OK;
  }

  if( rc==SQLITE_OK ){
    if( nRow==0 ){
      pRtree->nRowEst = RTREE_DEFAULT_ROWEST;
    }else{
      pRtree->nRowEst = MAX(nRow, RTREE_MIN_ROWEST);
    }
  }

  return rc;
}

static sqlite3_module rtreeModule = {
  0,                          /* iVersion */
  rtreeCreate,                /* xCreate - create a table */
  rtreeConnect,               /* xConnect - connect to an existing table */
  rtreeBestIndex,             /* xBestIndex - Determine search strategy */
  rtreeDisconnect,            /* xDisconnect - Disconnect from a table */

  appStmt[3] = &pRtree->pReadRowid;
  appStmt[4] = &pRtree->pWriteRowid;
  appStmt[5] = &pRtree->pDeleteRowid;
  appStmt[6] = &pRtree->pReadParent;
  appStmt[7] = &pRtree->pWriteParent;
  appStmt[8] = &pRtree->pDeleteParent;

  rc = rtreeQueryStat1(db, pRtree);
  for(i=0; i<N_STATEMENT && rc==SQLITE_OK; i++){
    char *zSql = sqlite3_mprintf(azSql[i], zDb, zPrefix);
    if( zSql ){
      rc = sqlite3_prepare_v2(db, zSql, -1, appStmt[i], 0); 
    }else{
      rc = SQLITE_NOMEM;
    }

  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
}

do_eqp_test rtree6.2.4 {
  SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10
} {
  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:CaEb} 
  0 1 1 {SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX (v=?)}
}

do_eqp_test rtree6.2.5 {
  SELECT * FROM t1,t2 WHERE k=ii AND x1<v
} {
  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:} 
  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}

# 2011 March 2
#
# 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.
#
#***********************************************************************
# Make sure the rtreenode() testing function can handle entries with
# 64-bit rowids.
# 

if {![info exists testdir]} {
  set testdir [file join [file dirname [info script]] .. .. test]
} 
source $testdir/tester.tcl
ifcapable !rtree { finish_test ; return }
set testprefix rtreeC

do_execsql_test 1.0 {
  CREATE VIRTUAL TABLE r_tree USING rtree(id, min_x, max_x, min_y, max_y);
  CREATE TABLE t(x, y);
}

do_eqp_test 1.1 {
  SELECT * FROM r_tree, t 
  WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y
} {
  0 0 1 {SCAN TABLE t}
  0 1 0 {SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:DdBcDbBa}
}

do_eqp_test 1.2 {
  SELECT * FROM t, r_tree
  WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y
} {
  0 0 0 {SCAN TABLE t}
  0 1 1 {SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:DdBcDbBa}
}

do_eqp_test 1.3 {
  SELECT * FROM t, r_tree
  WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND ?<=max_y
} {
  0 0 0 {SCAN TABLE t}
  0 1 1 {SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:DdBcDbBa}
}

do_eqp_test 1.5 {
  SELECT * FROM t, r_tree
} {
  0 0 1 {SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:}
  0 1 0 {SCAN TABLE t} 
}

do_execsql_test 2.0 {
  INSERT INTO t VALUES(0, 0);
  INSERT INTO t VALUES(0, 1);
  INSERT INTO t VALUES(0, 2);
  INSERT INTO t VALUES(0, 3);
  INSERT INTO t VALUES(0, 4);
  INSERT INTO t VALUES(0, 5);
  INSERT INTO t VALUES(0, 6);
  INSERT INTO t VALUES(0, 7);
  INSERT INTO t VALUES(0, 8);
  INSERT INTO t VALUES(0, 9);

  INSERT INTO t SELECT x+1, y FROM t;
  INSERT INTO t SELECT x+2, y FROM t;
  INSERT INTO t SELECT x+4, y FROM t;
  INSERT INTO r_tree SELECT NULL, x-1, x+1, y-1, y+1 FROM t;
  ANALYZE;
}

db close
sqlite3 db test.db

do_eqp_test 2.1 {
  SELECT * FROM r_tree, t 
  WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y
} {
  0 0 1 {SCAN TABLE t}
  0 1 0 {SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:DdBcDbBa}
}

do_eqp_test 2.2 {
  SELECT * FROM t, r_tree
  WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y
} {
  0 0 0 {SCAN TABLE t}
  0 1 1 {SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:DdBcDbBa}
}

do_eqp_test 2.3 {
  SELECT * FROM t, r_tree
  WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND ?<=max_y
} {
  0 0 0 {SCAN TABLE t}
  0 1 1 {SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:DdBcDbBa}
}

do_eqp_test 2.5 {
  SELECT * FROM t, r_tree
} {
  0 0 1 {SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:}
  0 1 0 {SCAN TABLE t} 
}

finish_test

** ^[sqlite3_free()] is used to free idxPtr if and only if
** needToFreeIdxPtr is true.
**
** ^The orderByConsumed means that output from [xFilter]/[xNext] will occur in
** the correct order to satisfy the ORDER BY clause so that no separate
** sorting step is required.
**
** ^The estimatedCost value is an estimate of the cost of a particular
** strategy. A cost of N indicates that the cost of the strategy is similar
** to a linear scan of an SQLite table with N rows. A cost of log(N) 
** indicates that the expense of the operation is similar to that of a
** binary search on a unique indexed field of an SQLite table with N rows.
**
** ^The estimatedRows value is an estimate of the number of rows that
** will be returned by the strategy.
**
** IMPORTANT: The estimatedRows field was added to the sqlite3_index_info
** structure for SQLite version 3.8.2. If a virtual table extension is
** used with an SQLite version earlier than 3.8.2, the results of attempting 
** to read or write the estimatedRows field are undefined (but are likely 
** to included crashing the application). The estimatedRows field should
** therefore only be used if [sqlite3_libversion_number()] returns a
** value greater than or equal to 3008002.
*/
struct sqlite3_index_info {
  /* Inputs */
  int nConstraint;           /* Number of entries in aConstraint */
  struct sqlite3_index_constraint {
     int iColumn;              /* Column on left-hand side of constraint */
     unsigned char op;         /* Constraint operator */

    int argvIndex;           /* if >0, constraint is part of argv to xFilter */
    unsigned char omit;      /* Do not code a test for this constraint */
  } *aConstraintUsage;
  int idxNum;                /* Number used to identify the index */
  char *idxStr;              /* String, possibly obtained from sqlite3_malloc */
  int needToFreeIdxStr;      /* Free idxStr using sqlite3_free() if true */
  int orderByConsumed;       /* True if output is already ordered */
  double estimatedCost;           /* Estimated cost of using this index */
  sqlite3_int64 estimatedRows;    /* Estimated number of rows returned */
};

/*
** CAPI3REF: Virtual Table Constraint Operator Codes
**
** These macros defined the allowed values for the
** [sqlite3_index_info].aConstraint[].op field.  Each value represents

       p->aConstraintUsage[i].argvIndex,
       p->aConstraintUsage[i].omit);
  }
  sqlite3DebugPrintf("  idxNum=%d\n", p->idxNum);
  sqlite3DebugPrintf("  idxStr=%s\n", p->idxStr);
  sqlite3DebugPrintf("  orderByConsumed=%d\n", p->orderByConsumed);
  sqlite3DebugPrintf("  estimatedCost=%g\n", p->estimatedCost);
  sqlite3DebugPrintf("  estimatedRows=%lld\n", p->estimatedRows);
}
#else
#define TRACE_IDX_INPUTS(A)
#define TRACE_IDX_OUTPUTS(A)
#endif

#ifndef SQLITE_OMIT_AUTOMATIC_INDEX

    memset(pUsage, 0, sizeof(pUsage[0])*pIdxInfo->nConstraint);
    if( pIdxInfo->needToFreeIdxStr ) sqlite3_free(pIdxInfo->idxStr);
    pIdxInfo->idxStr = 0;
    pIdxInfo->idxNum = 0;
    pIdxInfo->needToFreeIdxStr = 0;
    pIdxInfo->orderByConsumed = 0;
    pIdxInfo->estimatedCost = SQLITE_BIG_DBL / (double)2;
    pIdxInfo->estimatedRows = 25;
    rc = vtabBestIndex(pParse, pTab, pIdxInfo);
    if( rc ) goto whereLoopAddVtab_exit;
    pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
    pNew->prereq = 0;
    mxTerm = -1;
    assert( pNew->nLSlot>=nConstraint );
    for(i=0; i<nConstraint; i++) pNew->aLTerm[i] = 0;

      pNew->u.vtab.needFree = pIdxInfo->needToFreeIdxStr;
      pIdxInfo->needToFreeIdxStr = 0;
      pNew->u.vtab.idxStr = pIdxInfo->idxStr;
      pNew->u.vtab.isOrdered = (u8)((pIdxInfo->nOrderBy!=0)
                                     && pIdxInfo->orderByConsumed);
      pNew->rSetup = 0;
      pNew->rRun = sqlite3LogEstFromDouble(pIdxInfo->estimatedCost);

      pNew->nOut = sqlite3LogEst(pIdxInfo->estimatedRows);
      whereLoopInsert(pBuilder, pNew);
      if( pNew->u.vtab.needFree ){
        sqlite3_free(pNew->u.vtab.idxStr);
        pNew->u.vtab.needFree = 0;
      }
    }
  }