/ Check-in [dde0bb3e]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add SQL scalar function rtreecheck() to the rtree module. For running checks to ensure the shadow tables used by an rtree virtual table are internally consistent.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | rtree-integrity-check
Files: files | file ages | folders
SHA3-256: dde0bb3eab1316c3247b1755594527ca70955aab4ad4907190731f7ec092b327
User & Date: dan 2017-10-25 16:38:34
Context
2017-10-25
18:00
Fix compilation issue (C99-ism) seen with MSVC. Closed-Leaf check-in: 61870b42 user: mistachkin tags: rtree-integrity-check
16:38
Add SQL scalar function rtreecheck() to the rtree module. For running checks to ensure the shadow tables used by an rtree virtual table are internally consistent. check-in: dde0bb3e user: dan tags: rtree-integrity-check
16:14
Do not reference the ioctl() system call in the unix backend unless it is actually needed by the Batch Atomic Write extension. This should allow the build to work on VxWorks. check-in: adfa7ed2 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/rtree/rtree.c.

   205    205   #define RTREE_REINSERT(p) RTREE_MINCELLS(p)
   206    206   #define RTREE_MAXCELLS 51
   207    207   
   208    208   /*
   209    209   ** The smallest possible node-size is (512-64)==448 bytes. And the largest
   210    210   ** supported cell size is 48 bytes (8 byte rowid + ten 4 byte coordinates).
   211    211   ** Therefore all non-root nodes must contain at least 3 entries. Since 
   212         -** 2^40 is greater than 2^64, an r-tree structure always has a depth of
          212  +** 3^40 is greater than 2^64, an r-tree structure always has a depth of
   213    213   ** 40 or less.
   214    214   */
   215    215   #define RTREE_MAX_DEPTH 40
   216    216   
   217    217   
   218    218   /*
   219    219   ** Number of entries in the cursor RtreeNode cache.  The first entry is
................................................................................
  3603   3603     ){
  3604   3604       sqlite3_result_error(ctx, "Invalid argument to rtreedepth()", -1); 
  3605   3605     }else{
  3606   3606       u8 *zBlob = (u8 *)sqlite3_value_blob(apArg[0]);
  3607   3607       sqlite3_result_int(ctx, readInt16(zBlob));
  3608   3608     }
  3609   3609   }
         3610  +
         3611  +/*
         3612  +** Context object passed between the various routines that make up the
         3613  +** implementation of integrity-check function rtreecheck().
         3614  +*/
         3615  +typedef struct RtreeCheck RtreeCheck;
         3616  +struct RtreeCheck {
         3617  +  sqlite3 *db;                    /* Database handle */
         3618  +  const char *zDb;                /* Database containing rtree table */
         3619  +  const char *zTab;               /* Name of rtree table */
         3620  +  int bInt;                       /* True for rtree_i32 table */
         3621  +  int nDim;                       /* Number of dimensions for this rtree tbl */
         3622  +  sqlite3_stmt *pGetNode;         /* Statement used to retrieve nodes */
         3623  +  sqlite3_stmt *aCheckMapping[2]; /* Statements to query %_parent/%_rowid */
         3624  +  int nLeaf;                      /* Number of leaf cells in table */
         3625  +  int nNonLeaf;                   /* Number of non-leaf cells in table */
         3626  +  int rc;                         /* Return code */
         3627  +  char *zReport;                  /* Message to report */
         3628  +  int nErr;                       /* Number of lines in zReport */
         3629  +};
         3630  +
         3631  +#define RTREE_CHECK_MAX_ERROR 100
         3632  +
         3633  +/*
         3634  +** Reset SQL statement pStmt. If the sqlite3_reset() call returns an error,
         3635  +** and RtreeCheck.rc==SQLITE_OK, set RtreeCheck.rc to the error code.
         3636  +*/
         3637  +static void rtreeCheckReset(RtreeCheck *pCheck, sqlite3_stmt *pStmt){
         3638  +  int rc = sqlite3_reset(pStmt);
         3639  +  if( pCheck->rc==SQLITE_OK ) pCheck->rc = rc;
         3640  +}
         3641  +
         3642  +/*
         3643  +** The second and subsequent arguments to this function are a format string
         3644  +** and printf style arguments. This function formats the string and attempts
         3645  +** to compile it as an SQL statement.
         3646  +**
         3647  +** If successful, a pointer to the new SQL statement is returned. Otherwise,
         3648  +** NULL is returned and an error code left in RtreeCheck.rc.
         3649  +*/
         3650  +static sqlite3_stmt *rtreeCheckPrepare(
         3651  +  RtreeCheck *pCheck,             /* RtreeCheck object */
         3652  +  const char *zFmt, ...           /* Format string and trailing args */
         3653  +){
         3654  +  va_list ap;
         3655  +  va_start(ap, zFmt);
         3656  +  char *z = sqlite3_vmprintf(zFmt, ap);
         3657  +  sqlite3_stmt *pRet = 0;
         3658  +
         3659  +  if( pCheck->rc==SQLITE_OK ){
         3660  +    pCheck->rc = sqlite3_prepare_v2(pCheck->db, z, -1, &pRet, 0);
         3661  +  }
         3662  +
         3663  +  sqlite3_free(z);
         3664  +  va_end(ap);
         3665  +  return pRet;
         3666  +}
         3667  +
         3668  +/*
         3669  +** The second and subsequent arguments to this function are a printf()
         3670  +** style format string and arguments. This function formats the string and
         3671  +** appends it to the report being accumuated in pCheck.
         3672  +*/
         3673  +static void rtreeCheckAppendMsg(RtreeCheck *pCheck, const char *zFmt, ...){
         3674  +  va_list ap;
         3675  +  va_start(ap, zFmt);
         3676  +  if( pCheck->rc==SQLITE_OK && pCheck->nErr<RTREE_CHECK_MAX_ERROR ){
         3677  +    char *z = sqlite3_vmprintf(zFmt, ap);
         3678  +    if( z==0 ){
         3679  +      pCheck->rc = SQLITE_NOMEM;
         3680  +    }else{
         3681  +      pCheck->zReport = sqlite3_mprintf("%z%s%z", 
         3682  +          pCheck->zReport, (pCheck->zReport ? "\n" : ""), z
         3683  +      );
         3684  +      if( pCheck->zReport==0 ){
         3685  +        pCheck->rc = SQLITE_NOMEM;
         3686  +      }
         3687  +    }
         3688  +    pCheck->nErr++;
         3689  +  }
         3690  +  va_end(ap);
         3691  +}
         3692  +
         3693  +/*
         3694  +** This function is a no-op if there is already an error code stored
         3695  +** in the RtreeCheck object indicated by the first argument. NULL is
         3696  +** returned in this case.
         3697  +**
         3698  +** Otherwise, the contents of rtree table node iNode are loaded from
         3699  +** the database and copied into a buffer obtained from sqlite3_malloc().
         3700  +** If no error occurs, a pointer to the buffer is returned and (*pnNode)
         3701  +** is set to the size of the buffer in bytes.
         3702  +**
         3703  +** Or, if an error does occur, NULL is returned and an error code left
         3704  +** in the RtreeCheck object. The final value of *pnNode is undefined in
         3705  +** this case.
         3706  +*/
         3707  +static u8 *rtreeCheckGetNode(RtreeCheck *pCheck, i64 iNode, int *pnNode){
         3708  +  u8 *pRet = 0;                   /* Return value */
         3709  +
         3710  +  assert( pCheck->rc==SQLITE_OK );
         3711  +  if( pCheck->pGetNode==0 ){
         3712  +    pCheck->pGetNode = rtreeCheckPrepare(pCheck,
         3713  +        "SELECT data FROM %Q.'%q_node' WHERE nodeno=?", 
         3714  +        pCheck->zDb, pCheck->zTab
         3715  +    );
         3716  +  }
         3717  +
         3718  +  if( pCheck->rc==SQLITE_OK ){
         3719  +    sqlite3_bind_int64(pCheck->pGetNode, 1, iNode);
         3720  +    if( sqlite3_step(pCheck->pGetNode)==SQLITE_ROW ){
         3721  +      int nNode = sqlite3_column_bytes(pCheck->pGetNode, 0);
         3722  +      const u8 *pNode = (const u8*)sqlite3_column_blob(pCheck->pGetNode, 0);
         3723  +      pRet = sqlite3_malloc(nNode);
         3724  +      if( pRet==0 ){
         3725  +        pCheck->rc = SQLITE_NOMEM;
         3726  +      }else{
         3727  +        memcpy(pRet, pNode, nNode);
         3728  +        *pnNode = nNode;
         3729  +      }
         3730  +    }
         3731  +    rtreeCheckReset(pCheck, pCheck->pGetNode);
         3732  +    if( pCheck->rc==SQLITE_OK && pRet==0 ){
         3733  +      rtreeCheckAppendMsg(pCheck, "Node %lld missing from database", iNode);
         3734  +    }
         3735  +  }
         3736  +
         3737  +  return pRet;
         3738  +}
         3739  +
         3740  +/*
         3741  +** This function is used to check that the %_parent (if bLeaf==0) or %_rowid
         3742  +** (if bLeaf==1) table contains a specified entry. The schemas of the
         3743  +** two tables are:
         3744  +**
         3745  +**   CREATE TABLE %_parent(nodeno INTEGER PRIMARY KEY, parentnode INTEGER)
         3746  +**   CREATE TABLE %_rowid(rowid INTEGER PRIMARY KEY, nodeno INTEGER)
         3747  +**
         3748  +** In both cases, this function checks that there exists an entry with
         3749  +** IPK value iKey and the second column set to iVal.
         3750  +**
         3751  +*/
         3752  +static void rtreeCheckMapping(
         3753  +  RtreeCheck *pCheck,             /* RtreeCheck object */
         3754  +  int bLeaf,                      /* True for a leaf cell, false for interior */
         3755  +  i64 iKey,                       /* Key for mapping */
         3756  +  i64 iVal                        /* Expected value for mapping */
         3757  +){
         3758  +  int rc;
         3759  +  sqlite3_stmt *pStmt;
         3760  +  const char *azSql[2] = {
         3761  +    "SELECT parentnode FROM %Q.'%q_parent' WHERE nodeno=?",
         3762  +    "SELECT nodeno FROM %Q.'%q_rowid' WHERE rowid=?"
         3763  +  };
         3764  +
         3765  +  assert( bLeaf==0 || bLeaf==1 );
         3766  +  if( pCheck->aCheckMapping[bLeaf]==0 ){
         3767  +    pCheck->aCheckMapping[bLeaf] = rtreeCheckPrepare(pCheck,
         3768  +        azSql[bLeaf], pCheck->zDb, pCheck->zTab
         3769  +    );
         3770  +  }
         3771  +  if( pCheck->rc!=SQLITE_OK ) return;
         3772  +
         3773  +  pStmt = pCheck->aCheckMapping[bLeaf];
         3774  +  sqlite3_bind_int64(pStmt, 1, iKey);
         3775  +  rc = sqlite3_step(pStmt);
         3776  +  if( rc==SQLITE_DONE ){
         3777  +    rtreeCheckAppendMsg(pCheck, "Mapping (%lld -> %lld) missing from %s table",
         3778  +        iKey, iVal, (bLeaf ? "%_rowid" : "%_parent")
         3779  +    );
         3780  +  }else if( rc==SQLITE_ROW ){
         3781  +    i64 ii = sqlite3_column_int64(pStmt, 0);
         3782  +   if( ii!=iVal ){
         3783  +      rtreeCheckAppendMsg(pCheck, 
         3784  +          "Found (%lld -> %lld) in %s table, expected (%lld -> %lld)",
         3785  +          iKey, ii, (bLeaf ? "%_rowid" : "%_parent"), iKey, iVal
         3786  +      );
         3787  +    }
         3788  +  }
         3789  +  rtreeCheckReset(pCheck, pStmt);
         3790  +}
         3791  +
         3792  +static void rtreeCheckCellCoord(
         3793  +  RtreeCheck *pCheck, 
         3794  +  i64 iNode,
         3795  +  int iCell,
         3796  +  u8 *pCell,                      /* Pointer to cell coordinates */
         3797  +  u8 *pParent                     /* Pointer to parent coordinates */
         3798  +){
         3799  +  RtreeCoord c1, c2;
         3800  +  RtreeCoord p1, p2;
         3801  +  int i;
         3802  +
         3803  +  for(i=0; i<pCheck->nDim; i++){
         3804  +    readCoord(&pCell[4*2*i], &c1);
         3805  +    readCoord(&pCell[4*(2*i + 1)], &c2);
         3806  +
         3807  +    /* printf("%e, %e\n", c1.u.f, c2.u.f); */
         3808  +    if( pCheck->bInt ? c1.i>c2.i : c1.f>c2.f ){
         3809  +      rtreeCheckAppendMsg(pCheck, 
         3810  +          "Dimension %d of cell %d on node %lld is corrupt", i, iCell, iNode
         3811  +      );
         3812  +    }
         3813  +
         3814  +    if( pParent ){
         3815  +      readCoord(&pParent[4*2*i], &p1);
         3816  +      readCoord(&pParent[4*(2*i + 1)], &p2);
         3817  +
         3818  +      if( (pCheck->bInt ? c1.i<p1.i : c1.f<p1.f) 
         3819  +       || (pCheck->bInt ? c2.i>p2.i : c2.f>p2.f)
         3820  +      ){
         3821  +        rtreeCheckAppendMsg(pCheck, 
         3822  +            "Dimension %d of cell %d on node %lld is corrupt relative to parent"
         3823  +            , i, iCell, iNode
         3824  +        );
         3825  +      }
         3826  +    }
         3827  +  }
         3828  +}
         3829  +
         3830  +static void rtreeCheckNode(
         3831  +  RtreeCheck *pCheck,
         3832  +  int iDepth,                     /* Depth of iNode (0==leaf) */
         3833  +  u8 *aParent,                    /* Buffer containing parent coords */
         3834  +  i64 iNode                       /* Node to check */
         3835  +){
         3836  +  u8 *aNode = 0;
         3837  +  int nNode = 0;
         3838  +
         3839  +  assert( iNode==1 || aParent!=0 );
         3840  +  assert( pCheck->nDim>0 );
         3841  +
         3842  +  aNode = rtreeCheckGetNode(pCheck, iNode, &nNode);
         3843  +  if( aNode ){
         3844  +    if( nNode<4 ){
         3845  +      rtreeCheckAppendMsg(pCheck, 
         3846  +          "Node %lld is too small (%d bytes)", iNode, nNode
         3847  +      );
         3848  +    }else{
         3849  +      int nCell;                  /* Number of cells on page */
         3850  +      int i;                      /* Used to iterate through cells */
         3851  +      if( aParent==0 ){
         3852  +        iDepth = readInt16(aNode);
         3853  +        if( iDepth>RTREE_MAX_DEPTH ){
         3854  +          rtreeCheckAppendMsg(pCheck, "Rtree depth out of range (%d)", iDepth);
         3855  +          sqlite3_free(aNode);
         3856  +          return;
         3857  +        }
         3858  +      }
         3859  +      nCell = readInt16(&aNode[2]);
         3860  +      if( (4 + nCell*(8 + pCheck->nDim*2*4))>nNode ){
         3861  +        rtreeCheckAppendMsg(pCheck, 
         3862  +            "Node %lld is too small for cell count of %d (%d bytes)", 
         3863  +            iNode, nCell, nNode
         3864  +        );
         3865  +      }
         3866  +      for(i=0; i<nCell; i++){
         3867  +        u8 *pCell = &aNode[4 + i*(8 + pCheck->nDim*2*4)];
         3868  +        i64 iVal = readInt64(pCell);
         3869  +        rtreeCheckCellCoord(pCheck, iNode, i, &pCell[8], aParent);
         3870  +
         3871  +        if( iDepth>0 ){
         3872  +          rtreeCheckMapping(pCheck, 0, iVal, iNode);
         3873  +          rtreeCheckNode(pCheck, iDepth-1, &pCell[8], iVal);
         3874  +          pCheck->nNonLeaf++;
         3875  +        }else{
         3876  +          rtreeCheckMapping(pCheck, 1, iVal, iNode);
         3877  +          pCheck->nLeaf++;
         3878  +        }
         3879  +      }
         3880  +    }
         3881  +    sqlite3_free(aNode);
         3882  +  }
         3883  +}
         3884  +
         3885  +static void rtreeCheckCount(
         3886  +  RtreeCheck *pCheck, const char *zTbl, i64 nExpected
         3887  +){
         3888  +  if( pCheck->rc==SQLITE_OK ){
         3889  +    sqlite3_stmt *pCount;
         3890  +    pCount = rtreeCheckPrepare(pCheck, "SELECT count(*) FROM %Q.'%q%s'",
         3891  +        pCheck->zDb, pCheck->zTab, zTbl
         3892  +    );
         3893  +    if( pCount ){
         3894  +      if( sqlite3_step(pCount)==SQLITE_ROW ){
         3895  +        i64 nActual = sqlite3_column_int64(pCount, 0);
         3896  +        if( nActual!=nExpected ){
         3897  +          rtreeCheckAppendMsg(pCheck, "Wrong number of entries in %%%s table"
         3898  +              " - expected %lld, actual %lld" , zTbl, nExpected, nActual
         3899  +          );
         3900  +        }
         3901  +      }
         3902  +      pCheck->rc = sqlite3_finalize(pCount);
         3903  +    }
         3904  +  }
         3905  +}
         3906  +
         3907  +static int rtreeCheck(
         3908  +  sqlite3 *db,                    /* Database handle to access db through */
         3909  +  const char *zDb,                /* Name of db ("main", "temp" etc.) */
         3910  +  const char *zTab,               /* Name of rtree table to check */
         3911  +  char **pzReport                 /* OUT: sqlite3_malloc'd report text */
         3912  +){
         3913  +  RtreeCheck check;               /* Common context for various routines */
         3914  +  sqlite3_stmt *pStmt = 0;        /* Used to find column count of rtree table */
         3915  +  int bEnd = 0;                   /* True if transaction should be closed */
         3916  +
         3917  +  /* Initialize the context object */
         3918  +  memset(&check, 0, sizeof(check));
         3919  +  check.db = db;
         3920  +  check.zDb = zDb;
         3921  +  check.zTab = zTab;
         3922  +
         3923  +  /* If there is not already an open transaction, open one now. This is
         3924  +  ** to ensure that the queries run as part of this integrity-check operate
         3925  +  ** on a consistent snapshot.  */
         3926  +  if( sqlite3_get_autocommit(db) ){
         3927  +    check.rc = sqlite3_exec(db, "BEGIN", 0, 0, 0);
         3928  +    bEnd = 1;
         3929  +  }
         3930  +
         3931  +  /* Find number of dimensions in the rtree table. */
         3932  +  pStmt = rtreeCheckPrepare(&check, "SELECT * FROM %Q.%Q", zDb, zTab);
         3933  +  if( pStmt ){
         3934  +    int rc;
         3935  +    check.nDim = (sqlite3_column_count(pStmt) - 1) / 2;
         3936  +    if( check.nDim<1 ){
         3937  +      rtreeCheckAppendMsg(&check, "Schema corrupt or not an rtree");
         3938  +    }else if( SQLITE_ROW==sqlite3_step(pStmt) ){
         3939  +      check.bInt = (sqlite3_column_type(pStmt, 1)==SQLITE_INTEGER);
         3940  +    }
         3941  +    rc = sqlite3_finalize(pStmt);
         3942  +    if( rc!=SQLITE_CORRUPT ) check.rc = rc;
         3943  +  }
         3944  +
         3945  +  /* Do the actual integrity-check */
         3946  +  if( check.rc==SQLITE_OK ){
         3947  +    rtreeCheckNode(&check, 0, 0, 1);
         3948  +  }
         3949  +  rtreeCheckCount(&check, "_rowid", check.nLeaf);
         3950  +  rtreeCheckCount(&check, "_parent", check.nNonLeaf);
         3951  +
         3952  +  /* Finalize SQL statements used by the integrity-check */
         3953  +  sqlite3_finalize(check.pGetNode);
         3954  +  sqlite3_finalize(check.aCheckMapping[0]);
         3955  +  sqlite3_finalize(check.aCheckMapping[1]);
         3956  +
         3957  +  /* If one was opened, close the transaction */
         3958  +  if( bEnd ){
         3959  +    int rc = sqlite3_exec(db, "END", 0, 0, 0);
         3960  +    if( check.rc==SQLITE_OK ) check.rc = rc;
         3961  +  }
         3962  +  *pzReport = check.zReport;
         3963  +  return check.rc;
         3964  +}
         3965  +
         3966  +/*
         3967  +** Usage:
         3968  +**
         3969  +**   rtreecheck(<rtree-table>);
         3970  +**   rtreecheck(<database>, <rtree-table>);
         3971  +**
         3972  +** Invoking this SQL function runs an integrity-check on the named rtree
         3973  +** table. The integrity-check verifies the following:
         3974  +**
         3975  +**   1. For each cell in the r-tree structure (%_node table), that:
         3976  +**
         3977  +**       a) for each dimension, (coord1 <= coord2).
         3978  +**
         3979  +**       b) unless the cell is on the root node, that the cell is bounded
         3980  +**          by the parent cell on the parent node.
         3981  +**
         3982  +**       c) for leaf nodes, that there is an entry in the %_rowid 
         3983  +**          table corresponding to the cell's rowid value that 
         3984  +**          points to the correct node.
         3985  +**
         3986  +**       d) for cells on non-leaf nodes, that there is an entry in the 
         3987  +**          %_parent table mapping from the cell's child node to the
         3988  +**          node that it resides on.
         3989  +**
         3990  +**   2. That there are the same number of entries in the %_rowid table
         3991  +**      as there are leaf cells in the r-tree structure, and that there
         3992  +**      is a leaf cell that corresponds to each entry in the %_rowid table.
         3993  +**
         3994  +**   3. That there are the same number of entries in the %_parent table
         3995  +**      as there are non-leaf cells in the r-tree structure, and that 
         3996  +**      there is a non-leaf cell that corresponds to each entry in the 
         3997  +**      %_parent table.
         3998  +*/
         3999  +static void rtreecheck(
         4000  +  sqlite3_context *ctx, 
         4001  +  int nArg, 
         4002  +  sqlite3_value **apArg
         4003  +){
         4004  +  if( nArg!=1 && nArg!=2 ){
         4005  +    sqlite3_result_error(ctx, 
         4006  +        "wrong number of arguments to function rtreecheck()", -1
         4007  +    );
         4008  +  }else{
         4009  +    int rc;
         4010  +    char *zReport = 0;
         4011  +    const char *zDb = (const char*)sqlite3_value_text(apArg[0]);
         4012  +    const char *zTab;
         4013  +    if( nArg==1 ){
         4014  +      zTab = zDb;
         4015  +      zDb = "main";
         4016  +    }else{
         4017  +      zTab = (const char*)sqlite3_value_text(apArg[1]);
         4018  +    }
         4019  +    rc = rtreeCheck(sqlite3_context_db_handle(ctx), zDb, zTab, &zReport);
         4020  +    if( rc==SQLITE_OK ){
         4021  +      sqlite3_result_text(ctx, zReport ? zReport : "ok", -1, SQLITE_TRANSIENT);
         4022  +    }else{
         4023  +      sqlite3_result_error_code(ctx, rc);
         4024  +    }
         4025  +    sqlite3_free(zReport);
         4026  +  }
         4027  +}
         4028  +
  3610   4029   
  3611   4030   /*
  3612   4031   ** Register the r-tree module with database handle db. This creates the
  3613   4032   ** virtual table module "rtree" and the debugging/analysis scalar 
  3614   4033   ** function "rtreenode".
  3615   4034   */
  3616   4035   int sqlite3RtreeInit(sqlite3 *db){
................................................................................
  3617   4036     const int utf8 = SQLITE_UTF8;
  3618   4037     int rc;
  3619   4038   
  3620   4039     rc = sqlite3_create_function(db, "rtreenode", 2, utf8, 0, rtreenode, 0, 0);
  3621   4040     if( rc==SQLITE_OK ){
  3622   4041       rc = sqlite3_create_function(db, "rtreedepth", 1, utf8, 0,rtreedepth, 0, 0);
  3623   4042     }
         4043  +  if( rc==SQLITE_OK ){
         4044  +    rc = sqlite3_create_function(db, "rtreecheck", -1, utf8, 0,rtreecheck, 0,0);
         4045  +  }
  3624   4046     if( rc==SQLITE_OK ){
  3625   4047   #ifdef SQLITE_RTREE_INT_ONLY
  3626   4048       void *c = (void *)RTREE_COORD_INT32;
  3627   4049   #else
  3628   4050       void *c = (void *)RTREE_COORD_REAL32;
  3629   4051   #endif
  3630   4052       rc = sqlite3_create_module_v2(db, "rtree", &rtreeModule, c, 0);

Changes to ext/rtree/rtree1.test.

   515    515       set res(1) {1 {UNIQUE constraint failed: t1.idx}}
   516    516       set res(2) {1 {rtree constraint failed: t1.(x1<=x2)}}
   517    517   
   518    518       do_catchsql_test $testname.1 $sql $res($error)
   519    519       do_test $testname.2 [list sql_uses_stmt db $sql] $uses
   520    520       do_execsql_test $testname.3 { SELECT * FROM t1 ORDER BY idx } $data
   521    521   
   522         -    do_test $testname.4 { rtree_check db t1 } 0
          522  +    do_rtree_integrity_test $testname.4 t1
   523    523       db close
   524    524     }
   525    525   }
   526    526   
   527    527   #-------------------------------------------------------------------------
   528    528   # Test that bug [d2889096e7bdeac6d] has been fixed.
   529    529   #

Changes to ext/rtree/rtree2.test.

    77     77         if {$rc != 1} {
    78     78           puts $t1
    79     79           puts $t2
    80     80         }
    81     81         set rc
    82     82       } {1}
    83     83     
    84         -    do_test rtree2-$module.$nDim.3 {
    85         -      rtree_check db t1
    86         -    } 0
           84  +    do_rtree_integrity_test rtree2-$module.$nDim.3 t1
    87     85     
    88     86       set OPS [list < > <= >= =]
    89     87       for {set ii 0} {$ii < $::NSELECT} {incr ii} {
    90     88         do_test rtree2-$module.$nDim.4.$ii.1 {
    91     89           set where [list]
    92     90           foreach look_three_dots! {. . .} {
    93     91             set colidx [expr int(rand()*($nDim*2+1))-1]
................................................................................
   129    127           set rc [expr {$t1 eq $t2}]
   130    128           if {$rc != 1} {
   131    129             puts $t1
   132    130             puts $t2
   133    131           }
   134    132           set rc
   135    133         } {1}
   136         -      do_test rtree2-$module.$nDim.5.$ii.2 {
   137         -        rtree_check db t1
   138         -      } {0}
          134  +      do_rtree_integrity_test rtree2-$module.$nDim.5.$ii.2 t1
   139    135       }
   140    136     
   141    137       do_test rtree2-$module.$nDim.6 {
   142    138         execsql {
   143    139           DROP TABLE t1;
   144    140           DROP TABLE t2;
   145    141         }
   146    142       } {}
   147    143     }
   148    144   }
   149    145   
   150    146   finish_test

Changes to ext/rtree/rtree4.test.

    11     11   #
    12     12   # Randomized test cases for the rtree extension.
    13     13   #
    14     14   
    15     15   if {![info exists testdir]} {
    16     16     set testdir [file join [file dirname [info script]] .. .. test]
    17     17   } 
           18  +source [file join [file dirname [info script]] rtree_util.tcl]
    18     19   source $testdir/tester.tcl
    19     20   
    20     21   ifcapable !rtree {
    21     22     finish_test
    22     23     return
    23     24   }
    24     25   
................................................................................
   242    243       }
   243    244       set where "WHERE [join [scramble $where] { AND }]"
   244    245       do_test rtree4-$nDim.2.$i.8 {
   245    246         list $where [db eval "SELECT id FROM rx $where ORDER BY id"]
   246    247       } [list $where [db eval "SELECT id FROM bx $where ORDER BY id"]]
   247    248     }
   248    249   
          250  +  do_rtree_integrity_test rtree4-$nDim.3 rx
   249    251   }
   250    252   
   251    253   finish_test

Changes to ext/rtree/rtree5.test.

    12     12   # The focus of this file is testing the r-tree extension when it is
    13     13   # configured to store values as 32 bit integers.
    14     14   #
    15     15   
    16     16   if {![info exists testdir]} {
    17     17     set testdir [file join [file dirname [info script]] .. .. test]
    18     18   } 
           19  +source [file join [file dirname [info script]] rtree_util.tcl]
    19     20   source $testdir/tester.tcl
    20     21   
    21     22   ifcapable !rtree {
    22     23     finish_test
    23     24     return
    24     25   }
    25     26   
................................................................................
    72     73   do_test rtree5-1.13 { 
    73     74     execsql { 
    74     75       SELECT * FROM t1 WHERE 
    75     76           x1=2147483643 AND x2=2147483647 AND 
    76     77           y1=-2147483648 AND y2=-2147483643
    77     78     }
    78     79   } {2 2147483643 2147483647 -2147483648 -2147483643}
           80  +do_rtree_integrity_test rtree5-1.14 t1
    79     81   
    80     82   finish_test

Changes to ext/rtree/rtree7.test.

    13     13   # database page-size is modified. At one point (3.6.22), this was causing
    14     14   # malfunctions.
    15     15   #
    16     16   
    17     17   if {![info exists testdir]} {
    18     18     set testdir [file join [file dirname [info script]] .. .. test]
    19     19   } 
           20  +source [file join [file dirname [info script]] rtree_util.tcl]
    20     21   source $testdir/tester.tcl
    21     22   
    22     23   ifcapable !rtree||!vacuum {
    23     24     finish_test
    24     25     return
    25     26   }
    26     27   
................................................................................
    62     63   do_test rtree7-1.5 {
    63     64     execsql_intout { 
    64     65       PRAGMA page_size = 512;
    65     66       VACUUM;
    66     67       SELECT sum(x1), sum(x2), sum(y1), sum(y2) FROM rt
    67     68     }
    68     69   } {51 102 153 204}
           70  +
           71  +do_rtree_integrity_test rtree7-1.6 rt
    69     72   
    70     73   finish_test

Changes to ext/rtree/rtree8.test.

    10     10   #***********************************************************************
    11     11   # 
    12     12   #
    13     13   
    14     14   if {![info exists testdir]} {
    15     15     set testdir [file join [file dirname [info script]] .. .. test]
    16     16   } 
           17  +source [file join [file dirname [info script]] rtree_util.tcl]
    17     18   source $testdir/tester.tcl
    18     19   ifcapable !rtree { finish_test ; return }
    19     20   
    20     21   #-------------------------------------------------------------------------
    21     22   # The following block of tests - rtree8-1.* - feature reading and writing
    22     23   # an r-tree table while there exist open cursors on it.
    23     24   #
................................................................................
    60     61   do_test rtree8-1.2.2 { nested_select 1 } {51}
    61     62   
    62     63   # This test runs many SELECT queries simultaneously against a large 
    63     64   # table, causing a collision in the hash-table used to store r-tree 
    64     65   # nodes internally.
    65     66   #
    66     67   populate_t1 1500
           68  +do_rtree_integrity_test rtree8-1.3.0 t1
    67     69   do_execsql_test rtree8-1.3.1 { SELECT max(nodeno) FROM t1_node } {164}
    68     70   do_test rtree8-1.3.2 {
    69     71     set rowids [execsql {SELECT min(rowid) FROM t1_rowid GROUP BY nodeno}]
    70     72     set stmt_list [list]
    71     73     foreach row $rowids {
    72     74       set stmt [sqlite3_prepare db "SELECT * FROM t1 WHERE id = $row" -1 tail]
    73     75       sqlite3_step $stmt
................................................................................
   154    156       execsql { INSERT INTO t2 VALUES($i, 100, 101) }
   155    157     }
   156    158     for {set i 100} {$i < 200} {incr i} {
   157    159       execsql { INSERT INTO t2 VALUES($i, 1000, 1001) }
   158    160     }
   159    161     execsql COMMIT
   160    162   } {}
   161         -do_test rtree8-5.3 {
          163  +do_rtree_integrity_test rtree8-5.3 t2
          164  +do_test rtree8-5.4 {
   162    165     execsql BEGIN
   163    166     for {set i 0} {$i < 200} {incr i} {
   164    167       execsql { DELETE FROM t2 WHERE id = $i }
   165    168     }
   166    169     execsql COMMIT
   167    170   } {}
          171  +do_rtree_integrity_test rtree8-5.5 t2
   168    172   
   169    173   
   170    174   finish_test

Changes to ext/rtree/rtree9.test.

    11     11   # This file contains tests for the r-tree module. Specifically, it tests
    12     12   # that custom r-tree queries (geometry callbacks) work.
    13     13   # 
    14     14   
    15     15   if {![info exists testdir]} {
    16     16     set testdir [file join [file dirname [info script]] .. .. test]
    17     17   } 
           18  +source [file join [file dirname [info script]] rtree_util.tcl]
    18     19   source $testdir/tester.tcl
    19     20   ifcapable !rtree { finish_test ; return }
    20     21   ifcapable rtree_int_only { finish_test; return }
    21     22   
    22     23   register_cube_geom db
    23     24   
    24     25   do_execsql_test rtree9-1.1 {
................................................................................
    38     39   
    39     40   for {set i 0} {$i < 1000} {incr i} {
    40     41     set x [expr $i%10]
    41     42     set y [expr ($i/10)%10]
    42     43     set z [expr ($i/100)%10]
    43     44     execsql { INSERT INTO rt VALUES($i, $x, $x+1, $y, $y+1, $z, $z+1) }
    44     45   }
           46  +do_rtree_integrity_test rtree9-2.0 rt
    45     47   do_execsql_test rtree9-2.1 {
    46     48     SELECT id FROM rt WHERE id MATCH cube(2.5, 2.5, 2.5, 1, 1, 1) ORDER BY id;
    47     49   } {222 223 232 233 322 323 332 333}
    48     50   do_execsql_test rtree9-2.2 {
    49     51     SELECT id FROM rt WHERE id MATCH cube(5.5, 5.5, 5.5, 1, 1, 1) ORDER BY id;
    50     52   } {555 556 565 566 655 656 665 666}
    51     53   
    52     54   
    53         -do_execsql_test rtree9-3.1 {
           55  +do_execsql_test rtree9-3.0 {
    54     56     CREATE VIRTUAL TABLE rt32 USING rtree_i32(id, x1, x2, y1, y2, z1, z2);
    55     57   } {} 
    56     58   for {set i 0} {$i < 1000} {incr i} {
    57     59     set x [expr $i%10]
    58     60     set y [expr ($i/10)%10]
    59     61     set z [expr ($i/100)%10]
    60     62     execsql { INSERT INTO rt32 VALUES($i, $x, $x+1, $y, $y+1, $z, $z+1) }
    61     63   }
           64  +do_rtree_integrity_test rtree9-3.1 rt32
    62     65   do_execsql_test rtree9-3.2 {
    63     66     SELECT id FROM rt32 WHERE id MATCH cube(3, 3, 3, 1, 1, 1) ORDER BY id;
    64     67   } {222 223 224 232 233 234 242 243 244 322 323 324 332 333 334 342 343 344 422 423 424 432 433 434 442 443 444}
    65     68   do_execsql_test rtree9-3.3 {
    66     69     SELECT id FROM rt32 WHERE id MATCH cube(5.5, 5.5, 5.5, 1, 1, 1) ORDER BY id;
    67     70   } {555 556 565 566 655 656 665 666}
    68     71   
................................................................................
   117    120     SELECT id FROM rt2 WHERE id MATCH circle(0.0, 0.0, 2.0);
   118    121   } {1 2 3 4 13 14 15 16 17}
   119    122   
   120    123   do_execsql_test rtree9-5.3 {
   121    124     UPDATE rt2 SET xmin=xmin+5, ymin=ymin+5, xmax=xmax+5, ymax=ymax+5;
   122    125     SELECT id FROM rt2 WHERE id MATCH circle(5.0, 5.0, 2.0);
   123    126   } {1 2 3 4 13 14 15 16 17}
          127  +do_rtree_integrity_test rtree9-5.4 rt2
   124    128   
   125    129   finish_test

Changes to ext/rtree/rtreeA.test.

   104    104   do_corruption_tests rtreeA-1.1 {
   105    105     1   "SELECT * FROM t1"
   106    106     2   "SELECT * FROM t1 WHERE rowid=5"
   107    107     3   "INSERT INTO t1 VALUES(1000, 1, 2, 3, 4)"
   108    108     4   "SELECT * FROM t1 WHERE x1<10 AND x2>12"
   109    109   }
   110    110   
          111  +do_execsql_test rtreeA-1.1.1 {
          112  +  SELECT rtreecheck('main', 't1')
          113  +} {{Node 1 missing from database
          114  +Wrong number of entries in %_rowid table - expected 0, actual 500
          115  +Wrong number of entries in %_parent table - expected 0, actual 23}}
          116  +
   111    117   do_execsql_test  rtreeA-1.2.0 { DROP TABLE t1_node } {}
   112    118   do_corruption_tests rtreeA-1.2 -error "database disk image is malformed" {
   113    119     1   "SELECT * FROM t1"
   114    120     2   "SELECT * FROM t1 WHERE rowid=5"
   115    121     3   "INSERT INTO t1 VALUES(1000, 1, 2, 3, 4)"
   116    122     4   "SELECT * FROM t1 WHERE x1<10 AND x2>12"
   117    123   }
................................................................................
   152    158   do_test rtreeA-3.1.0.1 { set_tree_depth t1 } {1}
   153    159   do_test rtreeA-3.1.0.2 { set_tree_depth t1 3 } {3}
   154    160   do_corruption_tests rtreeA-3.1 {
   155    161     1   "SELECT * FROM t1"
   156    162     2   "SELECT * FROM t1 WHERE rowid=5"
   157    163     3   "INSERT INTO t1 VALUES(1000, 1, 2, 3, 4)"
   158    164   }
          165  +
          166  +do_execsql_test rtreeA-3.1.0.3 {
          167  +  SELECT rtreecheck('main', 't1')!="ok"
          168  +} {1}
   159    169   
   160    170   do_test rtreeA-3.2.0 { set_tree_depth t1 1000 } {1000}
   161    171   do_corruption_tests rtreeA-3.2 {
   162    172     1   "SELECT * FROM t1"
   163    173     2   "SELECT * FROM t1 WHERE rowid=5"
   164    174     3   "INSERT INTO t1 VALUES(1000, 1, 2, 3, 4)"
   165    175   }
................................................................................
   172    182   } {65535}
   173    183   do_corruption_tests rtreeA-3.3 {
   174    184     1   "SELECT * FROM t1"
   175    185     2   "SELECT * FROM t1 WHERE rowid=5"
   176    186     3   "INSERT INTO t1 VALUES(1000, 1, 2, 3, 4)"
   177    187   }
   178    188   
          189  +do_execsql_test rtreeA-3.3.3.4 {
          190  +  SELECT rtreecheck('main', 't1')
          191  +} {{Rtree depth out of range (65535)
          192  +Wrong number of entries in %_rowid table - expected 0, actual 499
          193  +Wrong number of entries in %_parent table - expected 0, actual 23}}
          194  +
   179    195   #-------------------------------------------------------------------------
   180    196   # Set the "number of entries" field on some nodes incorrectly.
   181    197   #
   182    198   create_t1
   183    199   populate_t1
   184    200   do_test rtreeA-4.1.0 { 
   185    201     set_entry_count t1 1 4000
................................................................................
   198    214   create_t1
   199    215   populate_t1
   200    216   do_execsql_test rtreeA-5.1.0 { DELETE FROM t1_parent } {}
   201    217   do_corruption_tests rtreeA-5.1 {
   202    218     1   "DELETE FROM t1 WHERE rowid = 5"
   203    219     2   "DELETE FROM t1"
   204    220   }
          221  +
          222  +do_execsql_test rtreeA-5.2 {
          223  +  SELECT rtreecheck('main', 't1')!="ok"
          224  +} {1}
   205    225   
   206    226   #-------------------------------------------------------------------------
   207    227   # Add some bad entries to the %_parent table.
   208    228   #
   209    229   create_t1
   210    230   populate_t1
   211    231   do_execsql_test rtreeA-6.1.0 { 
   212    232     UPDATE t1_parent set parentnode = parentnode+1
   213    233   } {}
   214    234   do_corruption_tests rtreeA-6.1 {
   215    235     1   "DELETE FROM t1 WHERE rowid = 5"
   216    236     2   "UPDATE t1 SET x1=x1+1, x2=x2+1"
   217    237   }
          238  +
          239  +do_execsql_test rtreeA-6.2 {
          240  +  SELECT rtreecheck('main', 't1')!="ok"
          241  +} {1}
   218    242   
   219    243   #-------------------------------------------------------------------------
   220    244   # Truncated blobs in the _node table.
   221    245   #
   222    246   create_t1
   223    247   populate_t1
   224    248   sqlite3 db test.db
................................................................................
   229    253     SELECT * FROM t1 WHERE x1>0 AND x1<100 AND x2>0 AND x2<100;
   230    254   } {1 {undersize RTree blobs in "t1_node"}}
   231    255   do_test rtreeA-7.120 {
   232    256     sqlite3_extended_errcode db
   233    257   } {SQLITE_CORRUPT_VTAB}
   234    258   
   235    259   
   236         -
   237    260   finish_test
          261  +

Changes to ext/rtree/rtreeB.test.

    11     11   # Make sure the rtreenode() testing function can handle entries with
    12     12   # 64-bit rowids.
    13     13   # 
    14     14   
    15     15   if {![info exists testdir]} {
    16     16     set testdir [file join [file dirname [info script]] .. .. test]
    17     17   } 
           18  +source [file join [file dirname [info script]] rtree_util.tcl]
    18     19   source $testdir/tester.tcl
    19     20   ifcapable !rtree { finish_test ; return }
    20     21   
    21     22   ifcapable rtree_int_only {
    22     23     do_test rtreeB-1.1-intonly {
    23     24       db eval {
    24     25         CREATE VIRTUAL TABLE t1 USING rtree(ii, x0, y0, x1, y1);
................................................................................
    39     40         INSERT INTO t1 VALUES(4294967296, 0.0, 0.0, 300.0, 300.0);
    40     41         INSERT INTO t1 VALUES(8589934592, 20.0, 20.0, 150.0, 150.0);
    41     42         INSERT INTO t1 VALUES(9223372036854775807, 150, 150, 400, 400);
    42     43         SELECT rtreenode(2, data) FROM t1_node;
    43     44       }
    44     45     } {{{1073741824 0 0 100 100} {2147483646 0 0 200 200} {4294967296 0 0 300 300} {8589934592 20 20 150 150} {9223372036854775807 150 150 400 400}}}
    45     46   }
           47  +
           48  +do_rtree_integrity_test rtreeB-1.2 t1
    46     49   
    47     50   finish_test

Changes to ext/rtree/rtreeC.test.

    11     11   # Make sure the rtreenode() testing function can handle entries with
    12     12   # 64-bit rowids.
    13     13   # 
    14     14   
    15     15   if {![info exists testdir]} {
    16     16     set testdir [file join [file dirname [info script]] .. .. test]
    17     17   } 
           18  +source [file join [file dirname [info script]] rtree_util.tcl]
    18     19   source $testdir/tester.tcl
    19     20   ifcapable !rtree { finish_test ; return }
    20     21   set testprefix rtreeC
    21     22   
    22     23   do_execsql_test 1.0 {
    23     24     CREATE VIRTUAL TABLE r_tree USING rtree(id, min_x, max_x, min_y, max_y);
    24     25     CREATE TABLE t(x, y);
................................................................................
   176    177     INSERT INTO t1(x) SELECT x+64 FROM t1;  -- 128
   177    178     INSERT INTO t1(x) SELECT x+128 FROM t1; -- 256
   178    179     INSERT INTO t1(x) SELECT x+256 FROM t1; -- 512
   179    180     INSERT INTO t1(x) SELECT x+512 FROM t1; --1024
   180    181   
   181    182     INSERT INTO rt SELECT x, x, x+1 FROM t1 WHERE x<=5;
   182    183   }
          184  +do_rtree_integrity_test 5.1.1 rt
   183    185   
   184    186   # First test a query with no ANALYZE data at all. The outer loop is
   185    187   # real table "t1".
   186    188   #
   187    189   do_eqp_test 5.2 {
   188    190     SELECT * FROM t1, rt WHERE x==id;
   189    191   } {

Changes to ext/rtree/rtreeE.test.

    11     11   # This file contains tests for the r-tree module. Specifically, it tests
    12     12   # that new-style custom r-tree queries (geometry callbacks) work.
    13     13   # 
    14     14   
    15     15   if {![info exists testdir]} {
    16     16     set testdir [file join [file dirname [info script]] .. .. test]
    17     17   } 
           18  +source [file join [file dirname [info script]] rtree_util.tcl]
    18     19   source $testdir/tester.tcl
    19     20   ifcapable !rtree { finish_test ; return }
    20     21   ifcapable rtree_int_only { finish_test; return }
    21     22   
    22     23   
    23     24   #-------------------------------------------------------------------------
    24     25   # Test the example 2d "circle" geometry callback.
    25     26   #
    26     27   register_circle_geom db
    27     28   
    28         -do_execsql_test rtreeE-1.1 {
           29  +do_execsql_test rtreeE-1.0.0 {
    29     30     PRAGMA page_size=512;
    30     31     CREATE VIRTUAL TABLE rt1 USING rtree(id,x0,x1,y0,y1);
    31     32     
    32     33     /* A tight pattern of small boxes near 0,0 */
    33     34     WITH RECURSIVE
    34     35       x(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM x WHERE x<4),
    35     36       y(y) AS (VALUES(0) UNION ALL SELECT y+1 FROM y WHERE y<4)
................................................................................
    43     44   
    44     45     /* A looser pattern of larger boxes near 0, 200 */
    45     46     WITH RECURSIVE
    46     47       x(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM x WHERE x<4),
    47     48       y(y) AS (VALUES(0) UNION ALL SELECT y+1 FROM y WHERE y<4)
    48     49     INSERT INTO rt1 SELECT 200+x+5*y, x*7, x*7+15, y*7+200, y*7+215 FROM x, y;
    49     50   } {}
           51  +do_rtree_integrity_test rtreeE-1.0.1 rt1
    50     52   
    51     53   # Queries against each of the three clusters */
    52     54   do_execsql_test rtreeE-1.1 {
    53     55     SELECT id FROM rt1 WHERE id MATCH Qcircle(0.0, 0.0, 50.0, 3) ORDER BY id;
    54     56   } {0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24}
    55     57   do_execsql_test rtreeE-1.1x {
    56     58     SELECT id FROM rt1 WHERE id MATCH Qcircle('x:0 y:0 r:50.0 e:3') ORDER BY id;
................................................................................
   107    109       db eval {INSERT INTO t2 VALUES($id,$x0,$x1,$y0,$y1)}
   108    110     }
   109    111     db eval {
   110    112       INSERT INTO rt2 SELECT * FROM t2;
   111    113       COMMIT;
   112    114     }
   113    115   } {}
          116  +do_rtree_integrity_test rtreeE-2.1.1 rt2
   114    117   
   115    118   for {set i 1} {$i<=200} {incr i} {
   116    119     set dx [expr {int(rand()*100)}]
   117    120     set dy [expr {int(rand()*100)}]
   118    121     set x0 [expr {int(rand()*(10000 - $dx))}]
   119    122     set x1 [expr {$x0+$dx}]
   120    123     set y0 [expr {int(rand()*(10000 - $dy))}]

Changes to ext/rtree/rtreeF.test.

    24     24   #     END;
    25     25   #     DELETE FROM t2 WHERE y=1;
    26     26   # 
    27     27   
    28     28   if {![info exists testdir]} {
    29     29     set testdir [file join [file dirname [info script]] .. .. test]
    30     30   } 
           31  +source [file join [file dirname [info script]] rtree_util.tcl]
    31     32   source $testdir/tester.tcl
    32     33   ifcapable !rtree { finish_test ; return }
    33     34   
    34     35   do_execsql_test rtreeF-1.1 {
    35     36     CREATE TABLE t1(x);
    36     37     CREATE TABLE t2(y);
    37     38     CREATE VIRTUAL TABLE t3 USING rtree(a,b,c);
................................................................................
    73     74   do_execsql_test rtreeF-1.5 {
    74     75     DELETE FROM t2 WHERE y=2;
    75     76   
    76     77     SELECT a FROM t3 ORDER BY a;
    77     78     SELECT '|';
    78     79     SELECT y FROM t2 ORDER BY y;
    79     80   } {1 4 5 | 1 4}
           81  +
           82  +do_rtree_integrity_test rtreeF-1.6 t3
    80     83   
    81     84   finish_test

Changes to ext/rtree/rtreeG.test.

    11     11   # This file contains tests for the r-tree module.
    12     12   #
    13     13   # Verify that no invalid SQL is run during initialization
    14     14   
    15     15   if {![info exists testdir]} {
    16     16     set testdir [file join [file dirname [info script]] .. .. test]
    17     17   } 
           18  +source [file join [file dirname [info script]] rtree_util.tcl]
    18     19   source $testdir/tester.tcl
    19     20   ifcapable !rtree { finish_test ; return }
    20     21   
    21     22   db close
    22     23   sqlite3_shutdown
    23     24   test_sqlite3_log [list lappend ::log]
    24     25   set ::log [list]
................................................................................
    33     34     set ::log
    34     35   } {}
    35     36   
    36     37   do_execsql_test rtreeG-1.2 {
    37     38     INSERT INTO t1 VALUES(1,10,15,5,23),(2,20,21,5,23),(3,10,15,20,30);
    38     39     SELECT id from t1 WHERE x0>8 AND x1<16 AND y0>2 AND y1<25;
    39     40   } {1}
           41  +do_rtree_integrity_test rtreeG-1.2.integrity t1
    40     42   do_test rtreeG-1.2log {
    41     43     set ::log
    42     44   } {}
    43     45   
    44     46   db close
    45     47   sqlite3 db test.db
    46     48   do_execsql_test rtreeG-1.3 {

Changes to ext/rtree/rtree_util.tcl.

   186    186     set ret
   187    187   }
   188    188   
   189    189   proc rtree_treedump {db zTab} {
   190    190     set d [rtree_depth $db $zTab]
   191    191     rtree_nodetreedump $db $zTab "" $d 1
   192    192   }
          193  +
          194  +proc do_rtree_integrity_test {tn tbl} {
          195  +  uplevel [list do_execsql_test $tn "SELECT rtreecheck('$tbl')" ok]
          196  +}
          197  +

Added ext/rtree/rtreecheck.test.

            1  +# 2017 August 17
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +#
           13  +
           14  +
           15  +if {![info exists testdir]} {
           16  +  set testdir [file join [file dirname [info script]] .. .. test]
           17  +} 
           18  +source $testdir/tester.tcl
           19  +set testprefix rtreecheck
           20  +
           21  +ifcapable !rtree {
           22  +  finish_test
           23  +  return
           24  +}
           25  +
           26  +proc swap_int32 {blob i0 i1} {
           27  +  binary scan $blob I* L
           28  +
           29  +  set a [lindex $L $i0]
           30  +  set b [lindex $L $i1]
           31  +
           32  +  lset L $i0 $b
           33  +  lset L $i1 $a
           34  +
           35  +  binary format I* $L
           36  +}
           37  +
           38  +do_catchsql_test 1.0 {
           39  +  SELECT rtreecheck();
           40  +} {1 {wrong number of arguments to function rtreecheck()}}
           41  +
           42  +do_catchsql_test 1.1 {
           43  +  SELECT rtreecheck(0,0,0);
           44  +} {1 {wrong number of arguments to function rtreecheck()}}
           45  +
           46  +
           47  +proc setup_simple_db {{module rtree}} {
           48  +  reset_db
           49  +  db func swap_int32 swap_int32
           50  +  execsql "
           51  +    CREATE VIRTUAL TABLE r1 USING $module (id, x1, x2, y1, y2);
           52  +    INSERT INTO r1 VALUES(1,  5, 5, 5, 5);  --  3
           53  +    INSERT INTO r1 VALUES(2,  6, 6, 6, 6);  --  9
           54  +    INSERT INTO r1 VALUES(3,  7, 7, 7, 7);  -- 15
           55  +    INSERT INTO r1 VALUES(4,  8, 8, 8, 8);  -- 21
           56  +    INSERT INTO r1 VALUES(5,  9, 9, 9, 9);  -- 27
           57  +  "
           58  +}
           59  +
           60  +setup_simple_db
           61  +do_execsql_test 2.1 { 
           62  +  SELECT rtreecheck('r1') 
           63  +} {ok}
           64  +
           65  +do_execsql_test 2.2 {
           66  +  UPDATE r1_node SET data = swap_int32(data, 3, 9);
           67  +  UPDATE r1_node SET data = swap_int32(data, 23, 29);
           68  +}
           69  +
           70  +do_execsql_test 2.3 { 
           71  +  SELECT rtreecheck('r1') 
           72  +} {{Dimension 0 of cell 0 on node 1 is corrupt
           73  +Dimension 1 of cell 3 on node 1 is corrupt}}
           74  +
           75  +setup_simple_db
           76  +do_execsql_test 2.4 {
           77  +  DELETE FROM r1_rowid WHERE rowid = 3;
           78  +  SELECT rtreecheck('r1') 
           79  +} {{Mapping (3 -> 1) missing from %_rowid table
           80  +Wrong number of entries in %_rowid table - expected 5, actual 4}}
           81  +
           82  +setup_simple_db
           83  +do_execsql_test 2.5 {
           84  +  UPDATE r1_rowid SET nodeno=2 WHERE rowid=3;
           85  +  SELECT rtreecheck('r1') 
           86  +} {{Found (3 -> 2) in %_rowid table, expected (3 -> 1)}}
           87  +
           88  +################
           89  +reset_db
           90  +do_execsql_test 3.0 { 
           91  +  CREATE VIRTUAL TABLE r1 USING rtree_i32(id, x1, x2);
           92  +  INSERT INTO r1 VALUES(1, 0x7FFFFFFF*-1, 0x7FFFFFFF);
           93  +  INSERT INTO r1 VALUES(2, 0x7FFFFFFF*-1, 5);
           94  +  INSERT INTO r1 VALUES(3, -5, 5);
           95  +  INSERT INTO r1 VALUES(4, 5, 0x11111111);
           96  +  INSERT INTO r1 VALUES(5, 5, 0x00800000);
           97  +  INSERT INTO r1 VALUES(6, 5, 0x00008000);
           98  +  INSERT INTO r1 VALUES(7, 5, 0x00000080);
           99  +  INSERT INTO r1 VALUES(8, 5, 0x40490fdb);
          100  +  INSERT INTO r1 VALUES(9, 0x7f800000, 0x7f900000);
          101  +  SELECT rtreecheck('r1') 
          102  +} {ok}
          103  +
          104  +breakpoint
          105  +do_execsql_test 3.1 { 
          106  +  CREATE VIRTUAL TABLE r2 USING rtree_i32(id, x1, x2);
          107  +  INSERT INTO r2 VALUES(2, -1*(1<<31), -1*(1<<31)+5);
          108  +  SELECT rtreecheck('r2') 
          109  +} {ok}
          110  +
          111  +
          112  +finish_test
          113  +