/ Check-in [387b55aa]
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:Change the rtree module to support queries with multiple comparison operators (i.e. > or <) of the same type on a single column.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 387b55aa9bb0f978641f9c2fa40f84fd98662047
User & Date: dan 2011-03-23 10:38:50
Context
2011-03-23
10:52
Fix a numbering issue in rtree6.test. Add a couple of extra tests. check-in: 0b7668a7 user: dan tags: trunk
10:38
Change the rtree module to support queries with multiple comparison operators (i.e. > or <) of the same type on a single column. check-in: 387b55aa user: dan tags: trunk
2011-03-21
17:15
Add the ability to enable and disable foreign key constraints and triggers using calls to sqlite3_db_config(). check-in: 09e167f9 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/rtree/rtree.c.

  1349   1349   **
  1350   1350   ** The second of each pair of bytes identifies the coordinate column
  1351   1351   ** to which the constraint applies. The leftmost coordinate column
  1352   1352   ** is 'a', the second from the left 'b' etc.
  1353   1353   */
  1354   1354   static int rtreeBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
  1355   1355     int rc = SQLITE_OK;
  1356         -  int ii, cCol;
         1356  +  int ii;
  1357   1357   
  1358   1358     int iIdx = 0;
  1359   1359     char zIdxStr[RTREE_MAX_DIMENSIONS*8+1];
  1360   1360     memset(zIdxStr, 0, sizeof(zIdxStr));
  1361   1361     UNUSED_PARAMETER(tab);
  1362   1362   
  1363   1363     assert( pIdxInfo->idxStr==0 );
  1364         -  for(ii=0; ii<pIdxInfo->nConstraint; ii++){
         1364  +  for(ii=0; ii<pIdxInfo->nConstraint && iIdx<(sizeof(zIdxStr)-1); ii++){
  1365   1365       struct sqlite3_index_constraint *p = &pIdxInfo->aConstraint[ii];
  1366   1366   
  1367   1367       if( p->usable && p->iColumn==0 && p->op==SQLITE_INDEX_CONSTRAINT_EQ ){
  1368   1368         /* We have an equality constraint on the rowid. Use strategy 1. */
  1369   1369         int jj;
  1370   1370         for(jj=0; jj<ii; jj++){
  1371   1371           pIdxInfo->aConstraintUsage[jj].argvIndex = 0;
................................................................................
  1381   1381         ** sqlite uses an internal cost of 0.0).
  1382   1382         */ 
  1383   1383         pIdxInfo->estimatedCost = 10.0;
  1384   1384         return SQLITE_OK;
  1385   1385       }
  1386   1386   
  1387   1387       if( p->usable && (p->iColumn>0 || p->op==SQLITE_INDEX_CONSTRAINT_MATCH) ){
  1388         -      int j, opmsk;
  1389         -      static const unsigned char compatible[] = { 0, 0, 1, 1, 2, 2 };
  1390         -      u8 op = 0;
         1388  +      u8 op;
  1391   1389         switch( p->op ){
  1392   1390           case SQLITE_INDEX_CONSTRAINT_EQ: op = RTREE_EQ; break;
  1393   1391           case SQLITE_INDEX_CONSTRAINT_GT: op = RTREE_GT; break;
  1394   1392           case SQLITE_INDEX_CONSTRAINT_LE: op = RTREE_LE; break;
  1395   1393           case SQLITE_INDEX_CONSTRAINT_LT: op = RTREE_LT; break;
  1396   1394           case SQLITE_INDEX_CONSTRAINT_GE: op = RTREE_GE; break;
  1397   1395           default:
  1398   1396             assert( p->op==SQLITE_INDEX_CONSTRAINT_MATCH );
  1399   1397             op = RTREE_MATCH; 
  1400   1398             break;
  1401   1399         }
  1402         -      assert( op!=0 );
  1403         -
  1404         -      /* Make sure this particular constraint has not been used before.
  1405         -      ** If it has been used before, ignore it.
  1406         -      **
  1407         -      ** A <= or < can be used if there is a prior >= or >.
  1408         -      ** A >= or > can be used if there is a prior < or <=.
  1409         -      ** A <= or < is disqualified if there is a prior <=, <, or ==.
  1410         -      ** A >= or > is disqualified if there is a prior >=, >, or ==.
  1411         -      ** A == is disqualifed if there is any prior constraint.
  1412         -      */
  1413         -      assert( compatible[RTREE_EQ & 7]==0 );
  1414         -      assert( compatible[RTREE_LT & 7]==1 );
  1415         -      assert( compatible[RTREE_LE & 7]==1 );
  1416         -      assert( compatible[RTREE_GT & 7]==2 );
  1417         -      assert( compatible[RTREE_GE & 7]==2 );
  1418         -      cCol = p->iColumn - 1 + 'a';
  1419         -      opmsk = compatible[op & 7];
  1420         -      for(j=0; j<iIdx; j+=2){
  1421         -        if( zIdxStr[j+1]==cCol && (compatible[zIdxStr[j] & 7] & opmsk)!=0 ){
  1422         -          op = 0;
  1423         -          break;
  1424         -        }
  1425         -      }
  1426         -      if( op ){
  1427         -        assert( iIdx<sizeof(zIdxStr)-1 );
  1428         -        zIdxStr[iIdx++] = op;
  1429         -        zIdxStr[iIdx++] = cCol;
  1430         -        pIdxInfo->aConstraintUsage[ii].argvIndex = (iIdx/2);
  1431         -        pIdxInfo->aConstraintUsage[ii].omit = 1;
  1432         -      }
         1400  +      zIdxStr[iIdx++] = op;
         1401  +      zIdxStr[iIdx++] = p->iColumn - 1 + 'a';
         1402  +      pIdxInfo->aConstraintUsage[ii].argvIndex = (iIdx/2);
         1403  +      pIdxInfo->aConstraintUsage[ii].omit = 1;
  1433   1404       }
  1434   1405     }
  1435   1406   
  1436   1407     pIdxInfo->idxNum = 2;
  1437   1408     pIdxInfo->needToFreeIdxStr = 1;
  1438   1409     if( iIdx>0 && 0==(pIdxInfo->idxStr = sqlite3_mprintf("%s", zIdxStr)) ){
  1439   1410       return SQLITE_NOMEM;

Changes to ext/rtree/rtree6.test.

   101    101   
   102    102   do_eqp_test rtree6.2.5 {
   103    103     SELECT * FROM t1,t2 WHERE k=ii AND x1<v
   104    104   } {
   105    105     0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2: (~0 rows)} 
   106    106     0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
   107    107   }
          108  +
          109  +do_execsql_test rtree6-3.1 {
          110  +  CREATE VIRTUAL TABLE t3 USING rtree(id, x1, x2, y1, y2);
          111  +  INSERT INTO t3 VALUES(NULL, 1, 1, 2, 2);
          112  +  SELECT * FROM t3 WHERE 
          113  +    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
          114  +    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
          115  +    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
          116  +    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
          117  +    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
          118  +    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5;
          119  +} {1 1.0 1.0 2.0 2.0}
          120  +
          121  +do_test rtree6.3.2 {
          122  +  rtree_strategy {
          123  +    SELECT * FROM t3 WHERE 
          124  +      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
          125  +      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
          126  +      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
          127  +      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 
          128  +  }
          129  +} {EaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEa}
          130  +do_test rtree6.3.4 {
          131  +  rtree_strategy {
          132  +    SELECT * FROM t3 WHERE 
          133  +      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
          134  +      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
          135  +      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
          136  +      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
          137  +      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
          138  +      x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5
          139  +  }
          140  +} {EaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEaEa}
          141  +
   108    142   
   109    143   finish_test