Index: ext/rtree/rtree.c ================================================================== --- ext/rtree/rtree.c +++ ext/rtree/rtree.c @@ -207,11 +207,11 @@ /* ** The smallest possible node-size is (512-64)==448 bytes. And the largest ** supported cell size is 48 bytes (8 byte rowid + ten 4 byte coordinates). ** Therefore all non-root nodes must contain at least 3 entries. Since -** 2^40 is greater than 2^64, an r-tree structure always has a depth of +** 3^40 is greater than 2^64, an r-tree structure always has a depth of ** 40 or less. */ #define RTREE_MAX_DEPTH 40 @@ -3605,10 +3605,431 @@ }else{ u8 *zBlob = (u8 *)sqlite3_value_blob(apArg[0]); sqlite3_result_int(ctx, readInt16(zBlob)); } } + +/* +** Context object passed between the various routines that make up the +** implementation of integrity-check function rtreecheck(). +*/ +typedef struct RtreeCheck RtreeCheck; +struct RtreeCheck { + sqlite3 *db; /* Database handle */ + const char *zDb; /* Database containing rtree table */ + const char *zTab; /* Name of rtree table */ + int bInt; /* True for rtree_i32 table */ + int nDim; /* Number of dimensions for this rtree tbl */ + sqlite3_stmt *pGetNode; /* Statement used to retrieve nodes */ + sqlite3_stmt *aCheckMapping[2]; /* Statements to query %_parent/%_rowid */ + int nLeaf; /* Number of leaf cells in table */ + int nNonLeaf; /* Number of non-leaf cells in table */ + int rc; /* Return code */ + char *zReport; /* Message to report */ + int nErr; /* Number of lines in zReport */ +}; + +#define RTREE_CHECK_MAX_ERROR 100 + +/* +** Reset SQL statement pStmt. If the sqlite3_reset() call returns an error, +** and RtreeCheck.rc==SQLITE_OK, set RtreeCheck.rc to the error code. +*/ +static void rtreeCheckReset(RtreeCheck *pCheck, sqlite3_stmt *pStmt){ + int rc = sqlite3_reset(pStmt); + if( pCheck->rc==SQLITE_OK ) pCheck->rc = rc; +} + +/* +** The second and subsequent arguments to this function are a format string +** and printf style arguments. This function formats the string and attempts +** to compile it as an SQL statement. +** +** If successful, a pointer to the new SQL statement is returned. Otherwise, +** NULL is returned and an error code left in RtreeCheck.rc. +*/ +static sqlite3_stmt *rtreeCheckPrepare( + RtreeCheck *pCheck, /* RtreeCheck object */ + const char *zFmt, ... /* Format string and trailing args */ +){ + va_list ap; + char *z; + sqlite3_stmt *pRet = 0; + + va_start(ap, zFmt); + z = sqlite3_vmprintf(zFmt, ap); + + if( pCheck->rc==SQLITE_OK ){ + pCheck->rc = sqlite3_prepare_v2(pCheck->db, z, -1, &pRet, 0); + } + + sqlite3_free(z); + va_end(ap); + return pRet; +} + +/* +** The second and subsequent arguments to this function are a printf() +** style format string and arguments. This function formats the string and +** appends it to the report being accumuated in pCheck. +*/ +static void rtreeCheckAppendMsg(RtreeCheck *pCheck, const char *zFmt, ...){ + va_list ap; + va_start(ap, zFmt); + if( pCheck->rc==SQLITE_OK && pCheck->nErrrc = SQLITE_NOMEM; + }else{ + pCheck->zReport = sqlite3_mprintf("%z%s%z", + pCheck->zReport, (pCheck->zReport ? "\n" : ""), z + ); + if( pCheck->zReport==0 ){ + pCheck->rc = SQLITE_NOMEM; + } + } + pCheck->nErr++; + } + va_end(ap); +} + +/* +** This function is a no-op if there is already an error code stored +** in the RtreeCheck object indicated by the first argument. NULL is +** returned in this case. +** +** Otherwise, the contents of rtree table node iNode are loaded from +** the database and copied into a buffer obtained from sqlite3_malloc(). +** If no error occurs, a pointer to the buffer is returned and (*pnNode) +** is set to the size of the buffer in bytes. +** +** Or, if an error does occur, NULL is returned and an error code left +** in the RtreeCheck object. The final value of *pnNode is undefined in +** this case. +*/ +static u8 *rtreeCheckGetNode(RtreeCheck *pCheck, i64 iNode, int *pnNode){ + u8 *pRet = 0; /* Return value */ + + assert( pCheck->rc==SQLITE_OK ); + if( pCheck->pGetNode==0 ){ + pCheck->pGetNode = rtreeCheckPrepare(pCheck, + "SELECT data FROM %Q.'%q_node' WHERE nodeno=?", + pCheck->zDb, pCheck->zTab + ); + } + + if( pCheck->rc==SQLITE_OK ){ + sqlite3_bind_int64(pCheck->pGetNode, 1, iNode); + if( sqlite3_step(pCheck->pGetNode)==SQLITE_ROW ){ + int nNode = sqlite3_column_bytes(pCheck->pGetNode, 0); + const u8 *pNode = (const u8*)sqlite3_column_blob(pCheck->pGetNode, 0); + pRet = sqlite3_malloc(nNode); + if( pRet==0 ){ + pCheck->rc = SQLITE_NOMEM; + }else{ + memcpy(pRet, pNode, nNode); + *pnNode = nNode; + } + } + rtreeCheckReset(pCheck, pCheck->pGetNode); + if( pCheck->rc==SQLITE_OK && pRet==0 ){ + rtreeCheckAppendMsg(pCheck, "Node %lld missing from database", iNode); + } + } + + return pRet; +} + +/* +** This function is used to check that the %_parent (if bLeaf==0) or %_rowid +** (if bLeaf==1) table contains a specified entry. The schemas of the +** two tables are: +** +** CREATE TABLE %_parent(nodeno INTEGER PRIMARY KEY, parentnode INTEGER) +** CREATE TABLE %_rowid(rowid INTEGER PRIMARY KEY, nodeno INTEGER) +** +** In both cases, this function checks that there exists an entry with +** IPK value iKey and the second column set to iVal. +** +*/ +static void rtreeCheckMapping( + RtreeCheck *pCheck, /* RtreeCheck object */ + int bLeaf, /* True for a leaf cell, false for interior */ + i64 iKey, /* Key for mapping */ + i64 iVal /* Expected value for mapping */ +){ + int rc; + sqlite3_stmt *pStmt; + const char *azSql[2] = { + "SELECT parentnode FROM %Q.'%q_parent' WHERE nodeno=?", + "SELECT nodeno FROM %Q.'%q_rowid' WHERE rowid=?" + }; + + assert( bLeaf==0 || bLeaf==1 ); + if( pCheck->aCheckMapping[bLeaf]==0 ){ + pCheck->aCheckMapping[bLeaf] = rtreeCheckPrepare(pCheck, + azSql[bLeaf], pCheck->zDb, pCheck->zTab + ); + } + if( pCheck->rc!=SQLITE_OK ) return; + + pStmt = pCheck->aCheckMapping[bLeaf]; + sqlite3_bind_int64(pStmt, 1, iKey); + rc = sqlite3_step(pStmt); + if( rc==SQLITE_DONE ){ + rtreeCheckAppendMsg(pCheck, "Mapping (%lld -> %lld) missing from %s table", + iKey, iVal, (bLeaf ? "%_rowid" : "%_parent") + ); + }else if( rc==SQLITE_ROW ){ + i64 ii = sqlite3_column_int64(pStmt, 0); + if( ii!=iVal ){ + rtreeCheckAppendMsg(pCheck, + "Found (%lld -> %lld) in %s table, expected (%lld -> %lld)", + iKey, ii, (bLeaf ? "%_rowid" : "%_parent"), iKey, iVal + ); + } + } + rtreeCheckReset(pCheck, pStmt); +} + +static void rtreeCheckCellCoord( + RtreeCheck *pCheck, + i64 iNode, + int iCell, + u8 *pCell, /* Pointer to cell coordinates */ + u8 *pParent /* Pointer to parent coordinates */ +){ + RtreeCoord c1, c2; + RtreeCoord p1, p2; + int i; + + for(i=0; inDim; i++){ + readCoord(&pCell[4*2*i], &c1); + readCoord(&pCell[4*(2*i + 1)], &c2); + + /* printf("%e, %e\n", c1.u.f, c2.u.f); */ + if( pCheck->bInt ? c1.i>c2.i : c1.f>c2.f ){ + rtreeCheckAppendMsg(pCheck, + "Dimension %d of cell %d on node %lld is corrupt", i, iCell, iNode + ); + } + + if( pParent ){ + readCoord(&pParent[4*2*i], &p1); + readCoord(&pParent[4*(2*i + 1)], &p2); + + if( (pCheck->bInt ? c1.ibInt ? c2.i>p2.i : c2.f>p2.f) + ){ + rtreeCheckAppendMsg(pCheck, + "Dimension %d of cell %d on node %lld is corrupt relative to parent" + , i, iCell, iNode + ); + } + } + } +} + +static void rtreeCheckNode( + RtreeCheck *pCheck, + int iDepth, /* Depth of iNode (0==leaf) */ + u8 *aParent, /* Buffer containing parent coords */ + i64 iNode /* Node to check */ +){ + u8 *aNode = 0; + int nNode = 0; + + assert( iNode==1 || aParent!=0 ); + assert( pCheck->nDim>0 ); + + aNode = rtreeCheckGetNode(pCheck, iNode, &nNode); + if( aNode ){ + if( nNode<4 ){ + rtreeCheckAppendMsg(pCheck, + "Node %lld is too small (%d bytes)", iNode, nNode + ); + }else{ + int nCell; /* Number of cells on page */ + int i; /* Used to iterate through cells */ + if( aParent==0 ){ + iDepth = readInt16(aNode); + if( iDepth>RTREE_MAX_DEPTH ){ + rtreeCheckAppendMsg(pCheck, "Rtree depth out of range (%d)", iDepth); + sqlite3_free(aNode); + return; + } + } + nCell = readInt16(&aNode[2]); + if( (4 + nCell*(8 + pCheck->nDim*2*4))>nNode ){ + rtreeCheckAppendMsg(pCheck, + "Node %lld is too small for cell count of %d (%d bytes)", + iNode, nCell, nNode + ); + } + for(i=0; inDim*2*4)]; + i64 iVal = readInt64(pCell); + rtreeCheckCellCoord(pCheck, iNode, i, &pCell[8], aParent); + + if( iDepth>0 ){ + rtreeCheckMapping(pCheck, 0, iVal, iNode); + rtreeCheckNode(pCheck, iDepth-1, &pCell[8], iVal); + pCheck->nNonLeaf++; + }else{ + rtreeCheckMapping(pCheck, 1, iVal, iNode); + pCheck->nLeaf++; + } + } + } + sqlite3_free(aNode); + } +} + +static void rtreeCheckCount( + RtreeCheck *pCheck, const char *zTbl, i64 nExpected +){ + if( pCheck->rc==SQLITE_OK ){ + sqlite3_stmt *pCount; + pCount = rtreeCheckPrepare(pCheck, "SELECT count(*) FROM %Q.'%q%s'", + pCheck->zDb, pCheck->zTab, zTbl + ); + if( pCount ){ + if( sqlite3_step(pCount)==SQLITE_ROW ){ + i64 nActual = sqlite3_column_int64(pCount, 0); + if( nActual!=nExpected ){ + rtreeCheckAppendMsg(pCheck, "Wrong number of entries in %%%s table" + " - expected %lld, actual %lld" , zTbl, nExpected, nActual + ); + } + } + pCheck->rc = sqlite3_finalize(pCount); + } + } +} + +static int rtreeCheck( + sqlite3 *db, /* Database handle to access db through */ + const char *zDb, /* Name of db ("main", "temp" etc.) */ + const char *zTab, /* Name of rtree table to check */ + char **pzReport /* OUT: sqlite3_malloc'd report text */ +){ + RtreeCheck check; /* Common context for various routines */ + sqlite3_stmt *pStmt = 0; /* Used to find column count of rtree table */ + int bEnd = 0; /* True if transaction should be closed */ + + /* Initialize the context object */ + memset(&check, 0, sizeof(check)); + check.db = db; + check.zDb = zDb; + check.zTab = zTab; + + /* If there is not already an open transaction, open one now. This is + ** to ensure that the queries run as part of this integrity-check operate + ** on a consistent snapshot. */ + if( sqlite3_get_autocommit(db) ){ + check.rc = sqlite3_exec(db, "BEGIN", 0, 0, 0); + bEnd = 1; + } + + /* Find number of dimensions in the rtree table. */ + pStmt = rtreeCheckPrepare(&check, "SELECT * FROM %Q.%Q", zDb, zTab); + if( pStmt ){ + int rc; + check.nDim = (sqlite3_column_count(pStmt) - 1) / 2; + if( check.nDim<1 ){ + rtreeCheckAppendMsg(&check, "Schema corrupt or not an rtree"); + }else if( SQLITE_ROW==sqlite3_step(pStmt) ){ + check.bInt = (sqlite3_column_type(pStmt, 1)==SQLITE_INTEGER); + } + rc = sqlite3_finalize(pStmt); + if( rc!=SQLITE_CORRUPT ) check.rc = rc; + } + + /* Do the actual integrity-check */ + if( check.rc==SQLITE_OK ){ + rtreeCheckNode(&check, 0, 0, 1); + } + rtreeCheckCount(&check, "_rowid", check.nLeaf); + rtreeCheckCount(&check, "_parent", check.nNonLeaf); + + /* Finalize SQL statements used by the integrity-check */ + sqlite3_finalize(check.pGetNode); + sqlite3_finalize(check.aCheckMapping[0]); + sqlite3_finalize(check.aCheckMapping[1]); + + /* If one was opened, close the transaction */ + if( bEnd ){ + int rc = sqlite3_exec(db, "END", 0, 0, 0); + if( check.rc==SQLITE_OK ) check.rc = rc; + } + *pzReport = check.zReport; + return check.rc; +} + +/* +** Usage: +** +** rtreecheck(); +** rtreecheck(, ); +** +** Invoking this SQL function runs an integrity-check on the named rtree +** table. The integrity-check verifies the following: +** +** 1. For each cell in the r-tree structure (%_node table), that: +** +** a) for each dimension, (coord1 <= coord2). +** +** b) unless the cell is on the root node, that the cell is bounded +** by the parent cell on the parent node. +** +** c) for leaf nodes, that there is an entry in the %_rowid +** table corresponding to the cell's rowid value that +** points to the correct node. +** +** d) for cells on non-leaf nodes, that there is an entry in the +** %_parent table mapping from the cell's child node to the +** node that it resides on. +** +** 2. That there are the same number of entries in the %_rowid table +** as there are leaf cells in the r-tree structure, and that there +** is a leaf cell that corresponds to each entry in the %_rowid table. +** +** 3. That there are the same number of entries in the %_parent table +** as there are non-leaf cells in the r-tree structure, and that +** there is a non-leaf cell that corresponds to each entry in the +** %_parent table. +*/ +static void rtreecheck( + sqlite3_context *ctx, + int nArg, + sqlite3_value **apArg +){ + if( nArg!=1 && nArg!=2 ){ + sqlite3_result_error(ctx, + "wrong number of arguments to function rtreecheck()", -1 + ); + }else{ + int rc; + char *zReport = 0; + const char *zDb = (const char*)sqlite3_value_text(apArg[0]); + const char *zTab; + if( nArg==1 ){ + zTab = zDb; + zDb = "main"; + }else{ + zTab = (const char*)sqlite3_value_text(apArg[1]); + } + rc = rtreeCheck(sqlite3_context_db_handle(ctx), zDb, zTab, &zReport); + if( rc==SQLITE_OK ){ + sqlite3_result_text(ctx, zReport ? zReport : "ok", -1, SQLITE_TRANSIENT); + }else{ + sqlite3_result_error_code(ctx, rc); + } + sqlite3_free(zReport); + } +} + /* ** Register the r-tree module with database handle db. This creates the ** virtual table module "rtree" and the debugging/analysis scalar ** function "rtreenode". @@ -3619,10 +4040,13 @@ rc = sqlite3_create_function(db, "rtreenode", 2, utf8, 0, rtreenode, 0, 0); if( rc==SQLITE_OK ){ rc = sqlite3_create_function(db, "rtreedepth", 1, utf8, 0,rtreedepth, 0, 0); } + if( rc==SQLITE_OK ){ + rc = sqlite3_create_function(db, "rtreecheck", -1, utf8, 0,rtreecheck, 0,0); + } if( rc==SQLITE_OK ){ #ifdef SQLITE_RTREE_INT_ONLY void *c = (void *)RTREE_COORD_INT32; #else void *c = (void *)RTREE_COORD_REAL32; Index: ext/rtree/rtree1.test ================================================================== --- ext/rtree/rtree1.test +++ ext/rtree/rtree1.test @@ -517,11 +517,11 @@ do_catchsql_test $testname.1 $sql $res($error) do_test $testname.2 [list sql_uses_stmt db $sql] $uses do_execsql_test $testname.3 { SELECT * FROM t1 ORDER BY idx } $data - do_test $testname.4 { rtree_check db t1 } 0 + do_rtree_integrity_test $testname.4 t1 db close } } #------------------------------------------------------------------------- Index: ext/rtree/rtree2.test ================================================================== --- ext/rtree/rtree2.test +++ ext/rtree/rtree2.test @@ -79,13 +79,11 @@ puts $t2 } set rc } {1} - do_test rtree2-$module.$nDim.3 { - rtree_check db t1 - } 0 + do_rtree_integrity_test rtree2-$module.$nDim.3 t1 set OPS [list < > <= >= =] for {set ii 0} {$ii < $::NSELECT} {incr ii} { do_test rtree2-$module.$nDim.4.$ii.1 { set where [list] @@ -131,13 +129,11 @@ puts $t1 puts $t2 } set rc } {1} - do_test rtree2-$module.$nDim.5.$ii.2 { - rtree_check db t1 - } {0} + do_rtree_integrity_test rtree2-$module.$nDim.5.$ii.2 t1 } do_test rtree2-$module.$nDim.6 { execsql { DROP TABLE t1; Index: ext/rtree/rtree4.test ================================================================== --- ext/rtree/rtree4.test +++ ext/rtree/rtree4.test @@ -13,10 +13,11 @@ # if {![info exists testdir]} { set testdir [file join [file dirname [info script]] .. .. test] } +source [file join [file dirname [info script]] rtree_util.tcl] source $testdir/tester.tcl ifcapable !rtree { finish_test return @@ -244,8 +245,9 @@ do_test rtree4-$nDim.2.$i.8 { list $where [db eval "SELECT id FROM rx $where ORDER BY id"] } [list $where [db eval "SELECT id FROM bx $where ORDER BY id"]] } + do_rtree_integrity_test rtree4-$nDim.3 rx } finish_test Index: ext/rtree/rtree5.test ================================================================== --- ext/rtree/rtree5.test +++ ext/rtree/rtree5.test @@ -14,10 +14,11 @@ # if {![info exists testdir]} { set testdir [file join [file dirname [info script]] .. .. test] } +source [file join [file dirname [info script]] rtree_util.tcl] source $testdir/tester.tcl ifcapable !rtree { finish_test return @@ -74,7 +75,8 @@ SELECT * FROM t1 WHERE x1=2147483643 AND x2=2147483647 AND y1=-2147483648 AND y2=-2147483643 } } {2 2147483643 2147483647 -2147483648 -2147483643} +do_rtree_integrity_test rtree5-1.14 t1 finish_test Index: ext/rtree/rtree7.test ================================================================== --- ext/rtree/rtree7.test +++ ext/rtree/rtree7.test @@ -15,10 +15,11 @@ # if {![info exists testdir]} { set testdir [file join [file dirname [info script]] .. .. test] } +source [file join [file dirname [info script]] rtree_util.tcl] source $testdir/tester.tcl ifcapable !rtree||!vacuum { finish_test return @@ -64,7 +65,9 @@ PRAGMA page_size = 512; VACUUM; SELECT sum(x1), sum(x2), sum(y1), sum(y2) FROM rt } } {51 102 153 204} + +do_rtree_integrity_test rtree7-1.6 rt finish_test Index: ext/rtree/rtree8.test ================================================================== --- ext/rtree/rtree8.test +++ ext/rtree/rtree8.test @@ -12,10 +12,11 @@ # if {![info exists testdir]} { set testdir [file join [file dirname [info script]] .. .. test] } +source [file join [file dirname [info script]] rtree_util.tcl] source $testdir/tester.tcl ifcapable !rtree { finish_test ; return } #------------------------------------------------------------------------- # The following block of tests - rtree8-1.* - feature reading and writing @@ -62,10 +63,11 @@ # This test runs many SELECT queries simultaneously against a large # table, causing a collision in the hash-table used to store r-tree # nodes internally. # populate_t1 1500 +do_rtree_integrity_test rtree8-1.3.0 t1 do_execsql_test rtree8-1.3.1 { SELECT max(nodeno) FROM t1_node } {164} do_test rtree8-1.3.2 { set rowids [execsql {SELECT min(rowid) FROM t1_rowid GROUP BY nodeno}] set stmt_list [list] foreach row $rowids { @@ -156,15 +158,17 @@ for {set i 100} {$i < 200} {incr i} { execsql { INSERT INTO t2 VALUES($i, 1000, 1001) } } execsql COMMIT } {} -do_test rtree8-5.3 { +do_rtree_integrity_test rtree8-5.3 t2 +do_test rtree8-5.4 { execsql BEGIN for {set i 0} {$i < 200} {incr i} { execsql { DELETE FROM t2 WHERE id = $i } } execsql COMMIT } {} +do_rtree_integrity_test rtree8-5.5 t2 finish_test Index: ext/rtree/rtree9.test ================================================================== --- ext/rtree/rtree9.test +++ ext/rtree/rtree9.test @@ -13,10 +13,11 @@ # if {![info exists testdir]} { set testdir [file join [file dirname [info script]] .. .. test] } +source [file join [file dirname [info script]] rtree_util.tcl] source $testdir/tester.tcl ifcapable !rtree { finish_test ; return } ifcapable rtree_int_only { finish_test; return } register_cube_geom db @@ -40,27 +41,29 @@ set x [expr $i%10] set y [expr ($i/10)%10] set z [expr ($i/100)%10] execsql { INSERT INTO rt VALUES($i, $x, $x+1, $y, $y+1, $z, $z+1) } } +do_rtree_integrity_test rtree9-2.0 rt do_execsql_test rtree9-2.1 { SELECT id FROM rt WHERE id MATCH cube(2.5, 2.5, 2.5, 1, 1, 1) ORDER BY id; } {222 223 232 233 322 323 332 333} do_execsql_test rtree9-2.2 { SELECT id FROM rt WHERE id MATCH cube(5.5, 5.5, 5.5, 1, 1, 1) ORDER BY id; } {555 556 565 566 655 656 665 666} -do_execsql_test rtree9-3.1 { +do_execsql_test rtree9-3.0 { CREATE VIRTUAL TABLE rt32 USING rtree_i32(id, x1, x2, y1, y2, z1, z2); } {} for {set i 0} {$i < 1000} {incr i} { set x [expr $i%10] set y [expr ($i/10)%10] set z [expr ($i/100)%10] execsql { INSERT INTO rt32 VALUES($i, $x, $x+1, $y, $y+1, $z, $z+1) } } +do_rtree_integrity_test rtree9-3.1 rt32 do_execsql_test rtree9-3.2 { SELECT id FROM rt32 WHERE id MATCH cube(3, 3, 3, 1, 1, 1) ORDER BY id; } {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} do_execsql_test rtree9-3.3 { SELECT id FROM rt32 WHERE id MATCH cube(5.5, 5.5, 5.5, 1, 1, 1) ORDER BY id; @@ -119,7 +122,8 @@ do_execsql_test rtree9-5.3 { UPDATE rt2 SET xmin=xmin+5, ymin=ymin+5, xmax=xmax+5, ymax=ymax+5; SELECT id FROM rt2 WHERE id MATCH circle(5.0, 5.0, 2.0); } {1 2 3 4 13 14 15 16 17} +do_rtree_integrity_test rtree9-5.4 rt2 finish_test Index: ext/rtree/rtreeA.test ================================================================== --- ext/rtree/rtreeA.test +++ ext/rtree/rtreeA.test @@ -106,10 +106,16 @@ 2 "SELECT * FROM t1 WHERE rowid=5" 3 "INSERT INTO t1 VALUES(1000, 1, 2, 3, 4)" 4 "SELECT * FROM t1 WHERE x1<10 AND x2>12" } +do_execsql_test rtreeA-1.1.1 { + SELECT rtreecheck('main', 't1') +} {{Node 1 missing from database +Wrong number of entries in %_rowid table - expected 0, actual 500 +Wrong number of entries in %_parent table - expected 0, actual 23}} + do_execsql_test rtreeA-1.2.0 { DROP TABLE t1_node } {} do_corruption_tests rtreeA-1.2 -error "database disk image is malformed" { 1 "SELECT * FROM t1" 2 "SELECT * FROM t1 WHERE rowid=5" 3 "INSERT INTO t1 VALUES(1000, 1, 2, 3, 4)" @@ -154,10 +160,14 @@ do_corruption_tests rtreeA-3.1 { 1 "SELECT * FROM t1" 2 "SELECT * FROM t1 WHERE rowid=5" 3 "INSERT INTO t1 VALUES(1000, 1, 2, 3, 4)" } + +do_execsql_test rtreeA-3.1.0.3 { + SELECT rtreecheck('main', 't1')!="ok" +} {1} do_test rtreeA-3.2.0 { set_tree_depth t1 1000 } {1000} do_corruption_tests rtreeA-3.2 { 1 "SELECT * FROM t1" 2 "SELECT * FROM t1 WHERE rowid=5" @@ -174,10 +184,16 @@ 1 "SELECT * FROM t1" 2 "SELECT * FROM t1 WHERE rowid=5" 3 "INSERT INTO t1 VALUES(1000, 1, 2, 3, 4)" } +do_execsql_test rtreeA-3.3.3.4 { + SELECT rtreecheck('main', 't1') +} {{Rtree depth out of range (65535) +Wrong number of entries in %_rowid table - expected 0, actual 499 +Wrong number of entries in %_parent table - expected 0, actual 23}} + #------------------------------------------------------------------------- # Set the "number of entries" field on some nodes incorrectly. # create_t1 populate_t1 @@ -200,10 +216,14 @@ do_execsql_test rtreeA-5.1.0 { DELETE FROM t1_parent } {} do_corruption_tests rtreeA-5.1 { 1 "DELETE FROM t1 WHERE rowid = 5" 2 "DELETE FROM t1" } + +do_execsql_test rtreeA-5.2 { + SELECT rtreecheck('main', 't1')!="ok" +} {1} #------------------------------------------------------------------------- # Add some bad entries to the %_parent table. # create_t1 @@ -213,10 +233,14 @@ } {} do_corruption_tests rtreeA-6.1 { 1 "DELETE FROM t1 WHERE rowid = 5" 2 "UPDATE t1 SET x1=x1+1, x2=x2+1" } + +do_execsql_test rtreeA-6.2 { + SELECT rtreecheck('main', 't1')!="ok" +} {1} #------------------------------------------------------------------------- # Truncated blobs in the _node table. # create_t1 @@ -231,7 +255,7 @@ do_test rtreeA-7.120 { sqlite3_extended_errcode db } {SQLITE_CORRUPT_VTAB} - finish_test + Index: ext/rtree/rtreeB.test ================================================================== --- ext/rtree/rtreeB.test +++ ext/rtree/rtreeB.test @@ -13,10 +13,11 @@ # if {![info exists testdir]} { set testdir [file join [file dirname [info script]] .. .. test] } +source [file join [file dirname [info script]] rtree_util.tcl] source $testdir/tester.tcl ifcapable !rtree { finish_test ; return } ifcapable rtree_int_only { do_test rtreeB-1.1-intonly { @@ -41,7 +42,9 @@ INSERT INTO t1 VALUES(9223372036854775807, 150, 150, 400, 400); SELECT rtreenode(2, data) FROM t1_node; } } {{{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}}} } + +do_rtree_integrity_test rtreeB-1.2 t1 finish_test Index: ext/rtree/rtreeC.test ================================================================== --- ext/rtree/rtreeC.test +++ ext/rtree/rtreeC.test @@ -13,10 +13,11 @@ # if {![info exists testdir]} { set testdir [file join [file dirname [info script]] .. .. test] } +source [file join [file dirname [info script]] rtree_util.tcl] source $testdir/tester.tcl ifcapable !rtree { finish_test ; return } set testprefix rtreeC do_execsql_test 1.0 { @@ -178,10 +179,11 @@ INSERT INTO t1(x) SELECT x+256 FROM t1; -- 512 INSERT INTO t1(x) SELECT x+512 FROM t1; --1024 INSERT INTO rt SELECT x, x, x+1 FROM t1 WHERE x<=5; } +do_rtree_integrity_test 5.1.1 rt # First test a query with no ANALYZE data at all. The outer loop is # real table "t1". # do_eqp_test 5.2 { Index: ext/rtree/rtreeE.test ================================================================== --- ext/rtree/rtreeE.test +++ ext/rtree/rtreeE.test @@ -13,10 +13,11 @@ # if {![info exists testdir]} { set testdir [file join [file dirname [info script]] .. .. test] } +source [file join [file dirname [info script]] rtree_util.tcl] source $testdir/tester.tcl ifcapable !rtree { finish_test ; return } ifcapable rtree_int_only { finish_test; return } @@ -23,11 +24,11 @@ #------------------------------------------------------------------------- # Test the example 2d "circle" geometry callback. # register_circle_geom db -do_execsql_test rtreeE-1.1 { +do_execsql_test rtreeE-1.0.0 { PRAGMA page_size=512; CREATE VIRTUAL TABLE rt1 USING rtree(id,x0,x1,y0,y1); /* A tight pattern of small boxes near 0,0 */ WITH RECURSIVE @@ -45,10 +46,11 @@ WITH RECURSIVE x(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM x WHERE x<4), y(y) AS (VALUES(0) UNION ALL SELECT y+1 FROM y WHERE y<4) INSERT INTO rt1 SELECT 200+x+5*y, x*7, x*7+15, y*7+200, y*7+215 FROM x, y; } {} +do_rtree_integrity_test rtreeE-1.0.1 rt1 # Queries against each of the three clusters */ do_execsql_test rtreeE-1.1 { SELECT id FROM rt1 WHERE id MATCH Qcircle(0.0, 0.0, 50.0, 3) ORDER BY id; } {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} @@ -109,10 +111,11 @@ db eval { INSERT INTO rt2 SELECT * FROM t2; COMMIT; } } {} +do_rtree_integrity_test rtreeE-2.1.1 rt2 for {set i 1} {$i<=200} {incr i} { set dx [expr {int(rand()*100)}] set dy [expr {int(rand()*100)}] set x0 [expr {int(rand()*(10000 - $dx))}] Index: ext/rtree/rtreeF.test ================================================================== --- ext/rtree/rtreeF.test +++ ext/rtree/rtreeF.test @@ -26,10 +26,11 @@ # if {![info exists testdir]} { set testdir [file join [file dirname [info script]] .. .. test] } +source [file join [file dirname [info script]] rtree_util.tcl] source $testdir/tester.tcl ifcapable !rtree { finish_test ; return } do_execsql_test rtreeF-1.1 { CREATE TABLE t1(x); @@ -75,7 +76,9 @@ SELECT a FROM t3 ORDER BY a; SELECT '|'; SELECT y FROM t2 ORDER BY y; } {1 4 5 | 1 4} + +do_rtree_integrity_test rtreeF-1.6 t3 finish_test Index: ext/rtree/rtreeG.test ================================================================== --- ext/rtree/rtreeG.test +++ ext/rtree/rtreeG.test @@ -13,10 +13,11 @@ # Verify that no invalid SQL is run during initialization if {![info exists testdir]} { set testdir [file join [file dirname [info script]] .. .. test] } +source [file join [file dirname [info script]] rtree_util.tcl] source $testdir/tester.tcl ifcapable !rtree { finish_test ; return } db close sqlite3_shutdown @@ -35,10 +36,11 @@ do_execsql_test rtreeG-1.2 { INSERT INTO t1 VALUES(1,10,15,5,23),(2,20,21,5,23),(3,10,15,20,30); SELECT id from t1 WHERE x0>8 AND x1<16 AND y0>2 AND y1<25; } {1} +do_rtree_integrity_test rtreeG-1.2.integrity t1 do_test rtreeG-1.2log { set ::log } {} db close Index: ext/rtree/rtree_util.tcl ================================================================== --- ext/rtree/rtree_util.tcl +++ ext/rtree/rtree_util.tcl @@ -188,5 +188,10 @@ proc rtree_treedump {db zTab} { set d [rtree_depth $db $zTab] rtree_nodetreedump $db $zTab "" $d 1 } + +proc do_rtree_integrity_test {tn tbl} { + uplevel [list do_execsql_test $tn "SELECT rtreecheck('$tbl')" ok] +} + ADDED ext/rtree/rtreecheck.test Index: ext/rtree/rtreecheck.test ================================================================== --- /dev/null +++ ext/rtree/rtreecheck.test @@ -0,0 +1,113 @@ +# 2017 August 17 +# +# 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. +# +#*********************************************************************** +# +# + + +if {![info exists testdir]} { + set testdir [file join [file dirname [info script]] .. .. test] +} +source $testdir/tester.tcl +set testprefix rtreecheck + +ifcapable !rtree { + finish_test + return +} + +proc swap_int32 {blob i0 i1} { + binary scan $blob I* L + + set a [lindex $L $i0] + set b [lindex $L $i1] + + lset L $i0 $b + lset L $i1 $a + + binary format I* $L +} + +do_catchsql_test 1.0 { + SELECT rtreecheck(); +} {1 {wrong number of arguments to function rtreecheck()}} + +do_catchsql_test 1.1 { + SELECT rtreecheck(0,0,0); +} {1 {wrong number of arguments to function rtreecheck()}} + + +proc setup_simple_db {{module rtree}} { + reset_db + db func swap_int32 swap_int32 + execsql " + CREATE VIRTUAL TABLE r1 USING $module (id, x1, x2, y1, y2); + INSERT INTO r1 VALUES(1, 5, 5, 5, 5); -- 3 + INSERT INTO r1 VALUES(2, 6, 6, 6, 6); -- 9 + INSERT INTO r1 VALUES(3, 7, 7, 7, 7); -- 15 + INSERT INTO r1 VALUES(4, 8, 8, 8, 8); -- 21 + INSERT INTO r1 VALUES(5, 9, 9, 9, 9); -- 27 + " +} + +setup_simple_db +do_execsql_test 2.1 { + SELECT rtreecheck('r1') +} {ok} + +do_execsql_test 2.2 { + UPDATE r1_node SET data = swap_int32(data, 3, 9); + UPDATE r1_node SET data = swap_int32(data, 23, 29); +} + +do_execsql_test 2.3 { + SELECT rtreecheck('r1') +} {{Dimension 0 of cell 0 on node 1 is corrupt +Dimension 1 of cell 3 on node 1 is corrupt}} + +setup_simple_db +do_execsql_test 2.4 { + DELETE FROM r1_rowid WHERE rowid = 3; + SELECT rtreecheck('r1') +} {{Mapping (3 -> 1) missing from %_rowid table +Wrong number of entries in %_rowid table - expected 5, actual 4}} + +setup_simple_db +do_execsql_test 2.5 { + UPDATE r1_rowid SET nodeno=2 WHERE rowid=3; + SELECT rtreecheck('r1') +} {{Found (3 -> 2) in %_rowid table, expected (3 -> 1)}} + +################ +reset_db +do_execsql_test 3.0 { + CREATE VIRTUAL TABLE r1 USING rtree_i32(id, x1, x2); + INSERT INTO r1 VALUES(1, 0x7FFFFFFF*-1, 0x7FFFFFFF); + INSERT INTO r1 VALUES(2, 0x7FFFFFFF*-1, 5); + INSERT INTO r1 VALUES(3, -5, 5); + INSERT INTO r1 VALUES(4, 5, 0x11111111); + INSERT INTO r1 VALUES(5, 5, 0x00800000); + INSERT INTO r1 VALUES(6, 5, 0x00008000); + INSERT INTO r1 VALUES(7, 5, 0x00000080); + INSERT INTO r1 VALUES(8, 5, 0x40490fdb); + INSERT INTO r1 VALUES(9, 0x7f800000, 0x7f900000); + SELECT rtreecheck('r1') +} {ok} + +breakpoint +do_execsql_test 3.1 { + CREATE VIRTUAL TABLE r2 USING rtree_i32(id, x1, x2); + INSERT INTO r2 VALUES(2, -1*(1<<31), -1*(1<<31)+5); + SELECT rtreecheck('r2') +} {ok} + + +finish_test +