SQLite

# 2010 August 28
#
# 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.
#
#***********************************************************************
# This file contains tests for the r-tree module. Specifically, it tests
# that custom r-tree queries (geometry callbacks) work.
# 

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

  SELECT id FROM rt32 WHERE id MATCH cube(5.5, 5.5, 1, 1, 1) ORDER BY id;
} {1 {SQL logic error or missing database}}
for {set x 2} {$x<200} {incr x 2} {
  do_catchsql_test rtree9-4.2.[expr $x/2] {
    SELECT id FROM rt WHERE id MATCH randomblob($x)
  } {1 {SQL logic error or missing database}}
}

do_catchsql_test rtree9-4.3 {
  SELECT id FROM rt WHERE id MATCH CAST( 
    (cube(5.5, 5.5, 5.5, 1, 1, 1) || X'1234567812345678') AS blob 
  )
} {1 {SQL logic error or missing database}}


#-------------------------------------------------------------------------
# Test the example 2d "circle" geometry callback.
#
register_circle_geom db

breakpoint
do_execsql_test rtree9-5.1 {
  CREATE VIRTUAL TABLE rt2 USING rtree(id, xmin, xmax, ymin, ymax);

  INSERT INTO rt2 VALUES(1,    1,   2,  1,  2);
  INSERT INTO rt2 VALUES(2,    1,   2, -2, -1);
  INSERT INTO rt2 VALUES(3,    -2, -1, -2, -1);
  INSERT INTO rt2 VALUES(4,    -2, -1,  1,  2);

  INSERT INTO rt2 VALUES(5,    2,   3,  2,  3);
  INSERT INTO rt2 VALUES(6,    2,   3, -3, -2);
  INSERT INTO rt2 VALUES(7,    -3, -2, -3, -2);
  INSERT INTO rt2 VALUES(8,    -3, -2,  2,  3);

  INSERT INTO rt2 VALUES(9,    1.8,   3,  1.8,  3);
  INSERT INTO rt2 VALUES(10,   1.8,   3, -3, -1.8);
  INSERT INTO rt2 VALUES(11,   -3, -1.8, -3, -1.8);
  INSERT INTO rt2 VALUES(12,   -3, -1.8,  1.8,  3);

  INSERT INTO rt2 VALUES(13,   -15, 15, 1.8, 2.2);
  INSERT INTO rt2 VALUES(14,   -15, 15, -2.2, -1.8);
  INSERT INTO rt2 VALUES(15,   1.8, 2.2, -15, 15);
  INSERT INTO rt2 VALUES(16,   -2.2, -1.8, -15, 15);

  INSERT INTO rt2 VALUES(17,   -100, 100, -100, 100);
} {}

do_execsql_test rtree9-5.2 {
  SELECT id FROM rt2 WHERE id MATCH circle(0.0, 0.0, 2.0);
} {1 2 3 4 13 14 15 16 17}

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}

finish_test

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing all sorts of SQLite interfaces. This code
** is not included in the SQLite library. 
*/

#include <sqlite3.h>

/* 
** Type used to cache parameter information for the "circle" r-tree geometry
** callback.
*/
typedef struct Circle Circle;
struct Circle {
  struct Box {
    double xmin;
    double xmax;
    double ymin;
    double ymax;
  } aBox[2];
  double centerx;
  double centery;
  double radius;
};

/*
** Destructor function for Circle objects allocated by circle_geom().
*/
static void circle_del(void *p){
  sqlite3_free(p);
}

/*
** Implementation of "circle" r-tree geometry callback.
*/
static int circle_geom(
  sqlite3_rtree_geometry *p,
  int nCoord, 
  double *aCoord, 
  int *pRes
){
  int i;                          /* Iterator variable */
  Circle *pCircle;                /* Structure defining circular region */
  double xmin, xmax;              /* X dimensions of box being tested */
  double ymin, ymax;              /* X dimensions of box being tested */

  if( p->pUser==0 ){
    /* If pUser is still 0, then the parameter values have not been tested
    ** for correctness or stored into a Circle structure yet. Do this now. */

    /* This geometry callback is for use with a 2-dimensional r-tree table.
    ** Return an error if the table does not have exactly 2 dimensions. */
    if( nCoord!=4 ) return SQLITE_ERROR;

    /* Test that the correct number of parameters (3) have been supplied,
    ** and that the parameters are in range (that the radius of the circle 
    ** radius is greater than zero). */
    if( p->nParam!=3 || p->aParam[2]<0.0 ) return SQLITE_ERROR;

    /* Allocate a structure to cache parameter data in. Return SQLITE_NOMEM
    ** if the allocation fails. */
    pCircle = (Circle *)(p->pUser = sqlite3_malloc(sizeof(Circle)));
    if( !pCircle ) return SQLITE_NOMEM;
    p->xDelUser = circle_del;

    /* Record the center and radius of the circular region. One way that
    ** tested bounding boxes that intersect the circular region are detected
    ** is by testing if each corner of the bounding box likes within radius
    ** units of the center of the circle. */
    pCircle->centerx = p->aParam[0];
    pCircle->centery = p->aParam[1];
    pCircle->radius = p->aParam[2];

    /* Define two bounding box regions. The first, aBox[0], extends to
    ** infinity in the X dimension. It covers the same range of the Y dimension
    ** as the circular region. The second, aBox[1], extends to infinity in
    ** the Y dimension and is constrained to the range of the circle in the
    ** X dimension.
    **
    ** Then imagine each box is split in half along its short axis by a line
    ** that intersects the center of the circular region. A bounding box
    ** being tested can be said to intersect the circular region if it contains
    ** points from each half of either of the two infinite bounding boxes.
    */
    pCircle->aBox[0].xmin = pCircle->centerx;
    pCircle->aBox[0].xmax = pCircle->centerx;
    pCircle->aBox[0].ymin = pCircle->centery + pCircle->radius;
    pCircle->aBox[0].ymax = pCircle->centery - pCircle->radius;
    pCircle->aBox[1].xmin = pCircle->centerx + pCircle->radius;
    pCircle->aBox[1].xmax = pCircle->centerx - pCircle->radius;
    pCircle->aBox[1].ymin = pCircle->centery;
    pCircle->aBox[1].ymax = pCircle->centery;
  }

  pCircle = (Circle *)p->pUser;
  xmin = aCoord[0];
  xmax = aCoord[1];
  ymin = aCoord[2];
  ymax = aCoord[3];

  /* Check if any of the 4 corners of the bounding-box being tested lie 
  ** inside the circular region. If they do, then the bounding-box does
  ** intersect the region of interest. Set the output variable to true and
  ** return SQLITE_OK in this case. */
  for(i=0; i<4; i++){
    double x = (i&0x01) ? xmax : xmin;
    double y = (i&0x02) ? ymax : ymin;
    double d2;
    
    d2  = (x-pCircle->centerx)*(x-pCircle->centerx);
    d2 += (y-pCircle->centery)*(y-pCircle->centery);
    if( d2<(pCircle->radius*pCircle->radius) ){
      *pRes = 1;
      return SQLITE_OK;
    }
  }

  /* Check if the bounding box covers any other part of the circular region.
  ** See comments above for a description of how this test works. If it does
  ** cover part of the circular region, set the output variable to true
  ** and return SQLITE_OK. */
  for(i=0; i<2; i++){
    if( xmin<=pCircle->aBox[i].xmin 
     && xmax>=pCircle->aBox[i].xmax 
     && ymin<=pCircle->aBox[i].ymin 
     && ymax>=pCircle->aBox[i].ymax 
    ){
      *pRes = 1;
      return SQLITE_OK;
    }
  }

  /* The specified bounding box does not intersect the circular region. Set
  ** the output variable to zero and return SQLITE_OK. */
  *pRes = 0;
  return SQLITE_OK;
}

/* END of implementation of "circle" geometry callback.
**************************************************************************
*************************************************************************/

#include <assert.h>
#include "tcl.h"

typedef struct Cube Cube;
struct Cube {
  double x;
  double y;

  if( objc!=2 ){
    Tcl_WrongNumArgs(interp, 1, objv, "DB");
    return TCL_ERROR;
  }
  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
  rc = sqlite3_rtree_geometry_callback(db, "cube", cube_geom, (void *)&gHere);
  Tcl_SetResult(interp, (char *)sqlite3TestErrorName(rc), TCL_STATIC);
#endif
  return TCL_OK;
}

static int register_circle_geom(
  void * clientData,
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
#ifdef SQLITE_ENABLE_RTREE
  extern int getDbPointer(Tcl_Interp*, const char*, sqlite3**);
  extern const char *sqlite3TestErrorName(int);
  sqlite3 *db;
  int rc;

  if( objc!=2 ){
    Tcl_WrongNumArgs(interp, 1, objv, "DB");
    return TCL_ERROR;
  }
  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
  rc = sqlite3_rtree_geometry_callback(db, "circle", circle_geom, 0);
  Tcl_SetResult(interp, (char *)sqlite3TestErrorName(rc), TCL_STATIC);
#endif
  return TCL_OK;
}

int Sqlitetestrtree_Init(Tcl_Interp *interp){
  Tcl_CreateObjCommand(interp, "register_cube_geom", register_cube_geom, 0, 0);
  Tcl_CreateObjCommand(interp, "register_circle_geom",register_circle_geom,0,0);
  return TCL_OK;
}