TOPOLOGY_PRIVATE sqlite3_stmt *
do_create_stmt_getFaceWithinBox2D (GaiaTopologyAccessorPtr accessor)
{
/* attempting to create the getFaceWithinBox2D prepared statement */
struct gaia_topology *topo = (struct gaia_topology *) accessor;
sqlite3_stmt *stmt = NULL;
int ret;
char *sql;
char *table;
char *xtable;
if (topo == NULL)
return NULL;
table = sqlite3_mprintf ("idx_%s_face_mbr", topo->topology_name);
xtable = gaiaDoubleQuotedSql (table);
sql =
sqlite3_mprintf
("SELECT pkid, xmin, ymin, xmax, ymax FROM MAIN.\"%s\" "
"WHERE xmin <= ? AND xmax >= ? AND ymin <= ? AND ymax >= ?", xtable);
free (xtable);
sqlite3_free (table);
ret = sqlite3_prepare_v2 (topo->db_handle, sql, strlen (sql), &stmt, NULL);
sqlite3_free (sql);
if (ret != SQLITE_OK)
{
char *msg =
sqlite3_mprintf ("Prepare_getFaceWithinBox2D error: \"%s\"",
sqlite3_errmsg (topo->db_handle));
gaiatopo_set_last_error_msg (accessor, msg);
sqlite3_free (msg);
return NULL;
}
return stmt;
}
TOPOLOGY_PRIVATE sqlite3_stmt *
do_create_stmt_deleteNodesById (GaiaTopologyAccessorPtr accessor)
{
/* attempting to create the deleteNodesById prepared statement */
struct gaia_topology *topo = (struct gaia_topology *) accessor;
sqlite3_stmt *stmt = NULL;
int ret;
char *sql;
char *table;
char *xtable;
if (topo == NULL)
return NULL;
table = sqlite3_mprintf ("%s_node", topo->topology_name);
xtable = gaiaDoubleQuotedSql (table);
sqlite3_free (table);
sql = sqlite3_mprintf ("DELETE FROM MAIN.\"%s\" WHERE node_id = ?", xtable);
free (xtable);
ret = sqlite3_prepare_v2 (topo->db_handle, sql, strlen (sql), &stmt, NULL);
sqlite3_free (sql);
if (ret != SQLITE_OK)
{
char *msg = sqlite3_mprintf ("Prepare_deleteNodesById error: \"%s\"",
sqlite3_errmsg (topo->db_handle));
gaiatopo_set_last_error_msg (accessor, msg);
sqlite3_free (msg);
return NULL;
}
return stmt;
}
TOPOLOGY_PRIVATE sqlite3_stmt *
do_create_stmt_insertFaces (GaiaTopologyAccessorPtr accessor)
{
/* attempting to create the insertFaces prepared statement */
struct gaia_topology *topo = (struct gaia_topology *) accessor;
sqlite3_stmt *stmt = NULL;
int ret;
char *sql;
char *table;
char *xtable;
if (topo == NULL)
return NULL;
table = sqlite3_mprintf ("%s_face", topo->topology_name);
xtable = gaiaDoubleQuotedSql (table);
sqlite3_free (table);
sql =
sqlite3_mprintf
("INSERT INTO MAIN.\"%s\" (face_id, mbr) VALUES (?, BuildMBR(?, ?, ?, ?, %d))",
xtable, topo->srid);
free (xtable);
ret = sqlite3_prepare_v2 (topo->db_handle, sql, strlen (sql), &stmt, NULL);
sqlite3_free (sql);
if (ret != SQLITE_OK)
{
char *msg = sqlite3_mprintf ("Prepare_insertFaces error: \"%s\"",
sqlite3_errmsg (topo->db_handle));
gaiatopo_set_last_error_msg (accessor, msg);
sqlite3_free (msg);
return NULL;
}
return stmt;
}
static void
vxpath_check (sqlite3 * db, const char *table, const char *column, int *okTable,
int *okCol)
{
/* checking if both Table and Column exist */
char **results;
char *sql;
char *xname;
int ret;
int i;
int n_rows;
int n_columns;
xname = gaiaDoubleQuotedSql (table);
sql = sqlite3_mprintf ("PRAGMA table_info(\"%s\")", xname);
free (xname);
ret = sqlite3_get_table (db, sql, &results, &n_rows, &n_columns, NULL);
sqlite3_free (sql);
if (ret != SQLITE_OK)
return;
if (n_rows >= 1)
{
*okTable = 1;
for (i = 1; i <= n_rows; i++)
{
const char *col_name = results[(i * n_columns) + 1];
if (strcasecmp (col_name, column) == 0)
*okCol = 1;
}
}
sqlite3_free_table (results);
}
TOPOLOGY_PRIVATE sqlite3_stmt *
do_create_stmt_getNodeWithinBox2D (GaiaTopologyAccessorPtr accessor)
{
/* attempting to create the getNodeWithinBox2D prepared statement */
struct gaia_topology *topo = (struct gaia_topology *) accessor;
sqlite3_stmt *stmt = NULL;
int ret;
char *sql;
char *table;
char *xtable;
if (topo == NULL)
return NULL;
table = sqlite3_mprintf ("%s_node", topo->topology_name);
xtable = gaiaDoubleQuotedSql (table);
sql =
sqlite3_mprintf ("SELECT node_id FROM MAIN.\"%s\" WHERE ROWID IN ("
"SELECT ROWID FROM SpatialIndex WHERE f_table_name = %Q AND "
"f_geometry_column = 'geom' AND search_frame = BuildMBR(?, ?, ?, ?))",
xtable, table);
free (xtable);
sqlite3_free (table);
ret = sqlite3_prepare_v2 (topo->db_handle, sql, strlen (sql), &stmt, NULL);
sqlite3_free (sql);
if (ret != SQLITE_OK)
{
char *msg =
sqlite3_mprintf ("Prepare_getNodeWithinBox2D error: \"%s\"",
sqlite3_errmsg (topo->db_handle));
gaiatopo_set_last_error_msg (accessor, msg);
sqlite3_free (msg);
return NULL;
}
return stmt;
}
static int
vxpath_open (sqlite3_vtab * pVTab, sqlite3_vtab_cursor ** ppCursor)
{
/* opening a new cursor */
sqlite3_stmt *stmt;
int ret;
char *sql;
char *xname;
char *xcolumn;
VirtualXPathCursorPtr cursor =
(VirtualXPathCursorPtr) sqlite3_malloc (sizeof (VirtualXPathCursor));
if (cursor == NULL)
return SQLITE_ERROR;
cursor->pVtab = (VirtualXPathPtr) pVTab;
cursor->xmlDoc = NULL;
cursor->xpathContext = NULL;
cursor->xpathObj = NULL;
cursor->xpathExpr = NULL;
cursor->stmt = NULL;
cursor->keyOp1 = 0;
cursor->keyVal1 = 0;
cursor->keyOp2 = 0;
cursor->keyVal2 = 0;
xcolumn = gaiaDoubleQuotedSql (cursor->pVtab->column);
xname = gaiaDoubleQuotedSql (cursor->pVtab->table);
sql = sqlite3_mprintf ("SELECT ROWID, \"%s\" FROM \"%s\""
" WHERE ROWID >= ?", xcolumn, xname);
free (xname);
free (xcolumn);
ret =
sqlite3_prepare_v2 (cursor->pVtab->db, sql, strlen (sql), &stmt, NULL);
sqlite3_free (sql);
if (ret != SQLITE_OK)
{
/* an error occurred */
cursor->eof = 1;
return SQLITE_ERROR;
}
cursor->stmt = stmt;
cursor->current_row = LONG64_MIN;
cursor->eof = 0;
*ppCursor = (sqlite3_vtab_cursor *) cursor;
return SQLITE_OK;
}
static int
vknn_create (sqlite3 * db, void *pAux, int argc, const char *const *argv,
sqlite3_vtab ** ppVTab, char **pzErr)
{
/* creates the virtual table for R*Tree KNN metahandling */
VirtualKnnPtr p_vt;
char *buf;
char *vtable;
char *xname;
if (pAux)
pAux = pAux; /* unused arg warning suppression */
if (argc == 3)
{
vtable = gaiaDequotedSql ((char *) argv[2]);
}
else
{
*pzErr =
sqlite3_mprintf
("[VirtualKNN module] CREATE VIRTUAL: illegal arg list {void}\n");
return SQLITE_ERROR;
}
p_vt = (VirtualKnnPtr) sqlite3_malloc (sizeof (VirtualKnn));
if (!p_vt)
return SQLITE_NOMEM;
p_vt->db = db;
p_vt->pModule = &my_knn_module;
p_vt->nRef = 0;
p_vt->zErrMsg = NULL;
p_vt->knn_ctx = vknn_create_context ();
/* preparing the COLUMNs for this VIRTUAL TABLE */
xname = gaiaDoubleQuotedSql (vtable);
buf = sqlite3_mprintf ("CREATE TABLE \"%s\" (f_table_name TEXT, "
"f_geometry_column TEXT, ref_geometry BLOB, max_items INTEGER, "
"pos INTEGER, fid INTEGER, distance DOUBLE)", xname);
free (xname);
free (vtable);
if (sqlite3_declare_vtab (db, buf) != SQLITE_OK)
{
sqlite3_free (buf);
*pzErr =
sqlite3_mprintf
("[VirtualKNN module] CREATE VIRTUAL: invalid SQL statement \"%s\"",
buf);
return SQLITE_ERROR;
}
sqlite3_free (buf);
*ppVTab = (sqlite3_vtab *) p_vt;
return SQLITE_OK;
}
TOPOLOGY_PRIVATE sqlite3_stmt *
do_create_stmt_getRingEdges (GaiaTopologyAccessorPtr accessor)
{
/* attempting to create the getRingEdges prepared statement */
struct gaia_topology *topo = (struct gaia_topology *) accessor;
sqlite3_stmt *stmt = NULL;
int ret;
char *sql;
char *table;
char *xtable;
if (topo == NULL)
return NULL;
table = sqlite3_mprintf ("%s_edge", topo->topology_name);
xtable = gaiaDoubleQuotedSql (table);
sqlite3_free (table);
sql =
sqlite3_mprintf ("WITH RECURSIVE edgering AS ("
"SELECT ? as signed_edge_id, edge_id, next_left_edge, next_right_edge "
"FROM MAIN.\"%s\" WHERE edge_id = ABS(?) UNION SELECT CASE WHEN "
"p.signed_edge_id < 0 THEN p.next_right_edge ELSE p.next_left_edge END, "
"e.edge_id, e.next_left_edge, e.next_right_edge "
"FROM MAIN.\"%s\" AS e, edgering AS p WHERE "
"e.edge_id = CASE WHEN p.signed_edge_id < 0 THEN "
"ABS(p.next_right_edge) ELSE ABS(p.next_left_edge) END ) "
"SELECT * FROM edgering", xtable, xtable);
free (xtable);
ret = sqlite3_prepare_v2 (topo->db_handle, sql, strlen (sql), &stmt, NULL);
sqlite3_free (sql);
if (ret != SQLITE_OK)
{
char *msg = sqlite3_mprintf ("Prepare_getRingEdges error: \"%s\"",
sqlite3_errmsg (topo->db_handle));
gaiatopo_set_last_error_msg (accessor, msg);
sqlite3_free (msg);
return NULL;
}
return stmt;
}
static int
vspidx_filter (sqlite3_vtab_cursor * pCursor, int idxNum, const char *idxStr,
int argc, sqlite3_value ** argv)
{
/* setting up a cursor filter */
char *db_prefix = NULL;
char *table_name = NULL;
char *geom_column;
char *xtable = NULL;
char *xgeom = NULL;
char *idx_name;
char *idx_nameQ;
char *sql_statement;
gaiaGeomCollPtr geom = NULL;
int ok_table = 0;
int ok_geom = 0;
const unsigned char *blob;
int size;
int exists;
int ret;
sqlite3_stmt *stmt;
float minx;
float miny;
float maxx;
float maxy;
double tic;
double tic2;
VirtualSpatialIndexCursorPtr cursor =
(VirtualSpatialIndexCursorPtr) pCursor;
VirtualSpatialIndexPtr spidx = (VirtualSpatialIndexPtr) cursor->pVtab;
if (idxStr)
idxStr = idxStr; /* unused arg warning suppression */
cursor->eof = 1;
if (idxNum == 1 && argc == 3)
{
/* retrieving the Table/Column/MBR params */
if (sqlite3_value_type (argv[0]) == SQLITE_TEXT)
{
char *tn = (char *) sqlite3_value_text (argv[0]);
vspidx_parse_table_name (tn, &db_prefix, &table_name);
ok_table = 1;
}
if (sqlite3_value_type (argv[1]) == SQLITE_TEXT)
{
geom_column = (char *) sqlite3_value_text (argv[1]);
ok_geom = 1;
}
if (sqlite3_value_type (argv[2]) == SQLITE_BLOB)
{
blob = sqlite3_value_blob (argv[2]);
size = sqlite3_value_bytes (argv[2]);
geom = gaiaFromSpatiaLiteBlobWkb (blob, size);
}
if (ok_table && ok_geom && geom)
;
else
{
/* invalid args */
goto stop;
}
}
if (idxNum == 2 && argc == 2)
{
/* retrieving the Table/MBR params */
if (sqlite3_value_type (argv[0]) == SQLITE_TEXT)
{
char *tn = (char *) sqlite3_value_text (argv[0]);
vspidx_parse_table_name (tn, &db_prefix, &table_name);
ok_table = 1;
}
if (sqlite3_value_type (argv[1]) == SQLITE_BLOB)
{
blob = sqlite3_value_blob (argv[1]);
size = sqlite3_value_bytes (argv[1]);
geom = gaiaFromSpatiaLiteBlobWkb (blob, size);
}
if (ok_table && geom)
;
else
{
/* invalid args */
goto stop;
}
}
/* checking if the corresponding R*Tree exists */
if (ok_geom)
exists =
vspidx_check_rtree (spidx->db, db_prefix, table_name, geom_column,
&xtable, &xgeom);
else
exists =
vspidx_find_rtree (spidx->db, db_prefix, table_name, &xtable,
&xgeom);
if (!exists)
goto stop;
/* building the RTree query */
idx_name = sqlite3_mprintf ("idx_%s_%s", xtable, xgeom);
idx_nameQ = gaiaDoubleQuotedSql (idx_name);
if (db_prefix == NULL)
{
sql_statement = sqlite3_mprintf ("SELECT pkid FROM \"%s\" WHERE "
"xmin <= ? AND xmax >= ? AND ymin <= ? AND ymax >= ?",
idx_nameQ);
}
else
{
char *quoted_db = gaiaDoubleQuotedSql (db_prefix);
sql_statement =
sqlite3_mprintf ("SELECT pkid FROM \"%s\".\"%s\" WHERE "
"xmin <= ? AND xmax >= ? AND ymin <= ? AND ymax >= ?",
quoted_db, idx_nameQ);
free (quoted_db);
}
free (idx_nameQ);
sqlite3_free (idx_name);
ret =
sqlite3_prepare_v2 (spidx->db, sql_statement, strlen (sql_statement),
&stmt, NULL);
sqlite3_free (sql_statement);
if (ret != SQLITE_OK)
goto stop;
/* binding stmt params [MBR] */
gaiaMbrGeometry (geom);
/* adjusting the MBR so to compensate for DOUBLE/FLOAT truncations */
minx = (float) (geom->MinX);
miny = (float) (geom->MinY);
maxx = (float) (geom->MaxX);
maxy = (float) (geom->MaxY);
tic = fabs (geom->MinX - minx);
tic2 = fabs (geom->MinY - miny);
if (tic2 > tic)
tic = tic2;
tic2 = fabs (geom->MaxX - maxx);
if (tic2 > tic)
tic = tic2;
tic2 = fabs (geom->MaxY - maxy);
if (tic2 > tic)
tic = tic2;
tic *= 2.0;
sqlite3_bind_double (stmt, 1, geom->MaxX + tic);
sqlite3_bind_double (stmt, 2, geom->MinX - tic);
sqlite3_bind_double (stmt, 3, geom->MaxY + tic);
sqlite3_bind_double (stmt, 4, geom->MinY - tic);
cursor->stmt = stmt;
cursor->eof = 0;
/* fetching the first ResultSet's row */
ret = sqlite3_step (cursor->stmt);
if (ret == SQLITE_ROW)
cursor->CurrentRowId = sqlite3_column_int64 (cursor->stmt, 0);
else
cursor->eof = 1;
stop:
if (geom)
gaiaFreeGeomColl (geom);
if (xtable)
free (xtable);
if (xgeom)
free (xgeom);
if (db_prefix)
free (db_prefix);
if (table_name)
free (table_name);
return SQLITE_OK;
}
static int
vspidx_find_rtree (sqlite3 * sqlite, const char *db_prefix,
const char *table_name, char **real_table, char **real_geom)
{
/* attempts to find the corresponding RTree Geometry Column */
sqlite3_stmt *stmt;
char *sql_statement;
int ret;
int count = 0;
char *rt = NULL;
char *rg = NULL;
if (db_prefix == NULL)
{
sql_statement =
sqlite3_mprintf
("SELECT f_table_name, f_geometry_column FROM geometry_columns "
"WHERE Upper(f_table_name) = Upper(%Q) AND spatial_index_enabled = 1",
table_name);
}
else
{
char *quoted_db = gaiaDoubleQuotedSql (db_prefix);
sql_statement =
sqlite3_mprintf
("SELECT f_table_name, f_geometry_column FROM \"%s\".geometry_columns "
"WHERE Upper(f_table_name) = Upper(%Q) AND spatial_index_enabled = 1",
quoted_db, table_name);
free (quoted_db);
}
ret =
sqlite3_prepare_v2 (sqlite, sql_statement, strlen (sql_statement),
&stmt, NULL);
sqlite3_free (sql_statement);
if (ret != SQLITE_OK)
return 0;
while (1)
{
/* scrolling the result set rows */
ret = sqlite3_step (stmt);
if (ret == SQLITE_DONE)
break; /* end of result set */
if (ret == SQLITE_ROW)
{
const char *v = (const char *) sqlite3_column_text (stmt, 0);
int len = sqlite3_column_bytes (stmt, 0);
if (rt)
free (rt);
rt = malloc (len + 1);
strcpy (rt, v);
v = (const char *) sqlite3_column_text (stmt, 1);
len = sqlite3_column_bytes (stmt, 1);
if (rg)
free (rg);
rg = malloc (len + 1);
strcpy (rg, v);
count++;
}
}
sqlite3_finalize (stmt);
if (count != 1)
return vspidx_find_view_rtree (sqlite, db_prefix, table_name,
real_table, real_geom);
else
{
*real_table = rt;
*real_geom = rg;
}
return 1;
}
static int
vspidx_find_view_rtree (sqlite3 * sqlite, const char *db_prefix,
const char *table_name, char **real_table,
char **real_geom)
{
/* attempts to find the corresponding RTree Geometry Column - SpatialView */
sqlite3_stmt *stmt;
char *sql_statement;
int ret;
int count = 0;
char *rt = NULL;
char *rg = NULL;
/* testing if views_geometry_columns exists */
if (db_prefix == NULL)
{
sql_statement = sqlite3_mprintf ("SELECT tbl_name FROM sqlite_master "
"WHERE type = 'table' AND tbl_name = 'views_geometry_columns'");
}
else
{
char *quoted_db = gaiaDoubleQuotedSql (db_prefix);
sql_statement =
sqlite3_mprintf ("SELECT tbl_name FROM \"%s\".sqlite_master "
"WHERE type = 'table' AND tbl_name = 'views_geometry_columns'",
quoted_db);
free (quoted_db);
}
ret =
sqlite3_prepare_v2 (sqlite, sql_statement, strlen (sql_statement),
&stmt, NULL);
sqlite3_free (sql_statement);
if (ret != SQLITE_OK)
return 0;
while (1)
{
/* scrolling the result set rows */
ret = sqlite3_step (stmt);
if (ret == SQLITE_DONE)
break; /* end of result set */
if (ret == SQLITE_ROW)
count++;
}
sqlite3_finalize (stmt);
if (count != 1)
return 0;
count = 0;
/* attempting to find the RTree Geometry Column */
if (db_prefix == NULL)
{
sql_statement =
sqlite3_mprintf ("SELECT a.f_table_name, a.f_geometry_column "
"FROM views_geometry_columns AS a "
"JOIN geometry_columns AS b ON ("
"Upper(a.f_table_name) = Upper(b.f_table_name) AND "
"Upper(a.f_geometry_column) = Upper(b.f_geometry_column)) "
"WHERE Upper(a.view_name) = Upper(%Q) AND b.spatial_index_enabled = 1",
table_name);
}
else
{
char *quoted_db = gaiaDoubleQuotedSql (db_prefix);
sql_statement =
sqlite3_mprintf ("SELECT a.f_table_name, a.f_geometry_column "
"FROM \"%s\".views_geometry_columns AS a "
"JOIN \"%s\".geometry_columns AS b ON ("
"Upper(a.f_table_name) = Upper(b.f_table_name) AND "
"Upper(a.f_geometry_column) = Upper(b.f_geometry_column)) "
"WHERE Upper(a.view_name) = Upper(%Q) AND b.spatial_index_enabled = 1",
quoted_db, quoted_db, table_name);
free (quoted_db);
}
ret =
sqlite3_prepare_v2 (sqlite, sql_statement, strlen (sql_statement),
&stmt, NULL);
sqlite3_free (sql_statement);
if (ret != SQLITE_OK)
return 0;
while (1)
{
/* scrolling the result set rows */
ret = sqlite3_step (stmt);
if (ret == SQLITE_DONE)
break; /* end of result set */
if (ret == SQLITE_ROW)
{
const char *v = (const char *) sqlite3_column_text (stmt, 0);
int len = sqlite3_column_bytes (stmt, 0);
if (rt)
free (rt);
rt = malloc (len + 1);
strcpy (rt, v);
v = (const char *) sqlite3_column_text (stmt, 1);
len = sqlite3_column_bytes (stmt, 1);
if (rg)
free (rg);
rg = malloc (len + 1);
strcpy (rg, v);
count++;
}
}
sqlite3_finalize (stmt);
if (count != 1)
return 0;
*real_table = rt;
*real_geom = rg;
return 1;
}
int main (int argc, char *argv[])
{
int result;
char *test_str1;
char *quoted_str;
result = gaiaIsReservedSqliteName ("AUTOINCREMENT");
if (!result) {
fprintf(stderr, "gaiaIsReservedSqliteName() fail for AUTOINCREMENT: %i\n", result);
return -1;
}
result = gaiaIsReservedSqliteName ("AUTOINCREMEN");
if (result) {
fprintf(stderr, "gaiaIsReservedSqliteName() fail for AUTOINCREMEN: %i\n", result);
return -2;
}
result = gaiaIsReservedSqliteName ("AUTOINCREMENTED");
if (result) {
fprintf(stderr, "gaiaIsReservedSqliteName() fail for AUTOINCREMENT: %i\n", result);
return -3;
}
result = gaiaIsReservedSqliteName ("foo");
if (result) {
fprintf(stderr, "gaiaIsReservedSqliteName() fail for foo: %i\n", result);
return -4;
}
result = gaiaIsReservedSqliteName ("ALL"); /* first item */
if (!result) {
fprintf(stderr, "gaiaIsReservedSqliteName() fail for ALL: %i\n", result);
return -5;
}
result = gaiaIsReservedSqliteName ("WHERE"); /* last item */
if (!result) {
fprintf(stderr, "gaiaIsReservedSqliteName() fail for WHERE: %i\n", result);
return -6;
}
result = gaiaIsReservedSqliteName ("autoincrement");
if (!result) {
fprintf(stderr, "gaiaIsReservedSqliteName() fail for autoincrement: %i\n", result);
return -7;
}
result = gaiaIsReservedSqlName ("SELECT");
if (!result) {
fprintf(stderr, "gaiaIsReservedSqlName() fail for SELECT: %i\n", result);
return -8;
}
result = gaiaIsReservedSqlName ("select");
if (!result) {
fprintf(stderr, "gaiaIsReservedSqlName() fail for select: %i\n", result);
return -9;
}
result = gaiaIsReservedSqlName ("foo");
if (result) {
fprintf(stderr, "gaiaIsReservedSqlName() fail for foo: %i\n", result);
return -10;
}
result = gaiaIsReservedSqlName ("ABSOLUTE");
if (!result) {
fprintf(stderr, "gaiaIsReservedSqlName() fail for ABSOLUTE: %i\n", result);
return -11;
}
result = gaiaIsReservedSqlName ("ZONE");
if (!result) {
fprintf(stderr, "gaiaIsReservedSqlName() fail for ZONE: %i\n", result);
return -12;
}
result = gaiaIsReservedSqlName ("SELECTED");
if (result) {
fprintf(stderr, "gaiaIsReservedSqlName() fail for SELECTED: %i\n", result);
return -13;
}
result = gaiaIsReservedSqlName ("SELEC");
if (result) {
fprintf(stderr, "gaiaIsReservedSqlName() fail for SELEC: %i\n", result);
return -14;
}
result = gaiaIllegalSqlName(NULL);
if (!result) {
fprintf(stderr, "gaiaIllegalSqlName() fail for (NULL): %i\n", result);
return -15;
}
result = gaiaIllegalSqlName("a");
if (result) {
fprintf(stderr, "gaiaIllegalSqlName() fail for a: %i\n", result);
return -16;
}
result = gaiaIllegalSqlName("-");
if (!result) {
fprintf(stderr, "gaiaIllegalSqlName() fail for -: %i\n", result);
return -17;
}
result = gaiaIllegalSqlName("AbbcdE0187r");
if (result) {
fprintf(stderr, "gaiaIllegalSqlName() fail for AbbcdE0187r: %i\n", result);
return -18;
}
result = gaiaIllegalSqlName("AbbcdE0187+");
if (!result) {
fprintf(stderr, "gaiaIllegalSqlName() fail for AbbcdE0187r+: %i\n", result);
return -19;
}
result = gaiaIllegalSqlName("");
if (!result) {
fprintf(stderr, "gaiaIllegalSqlName() fail for (empty): %i\n", result);
return -20;
}
result = gaiaIllegalSqlName("_ABCedFg");
if (!result) {
fprintf(stderr, "gaiaIllegalSqlName() fail for (empty): %i\n", result);
return -21;
}
asprintf(&test_str1, "SELECT %s from %s;", "Foo", "Bar");
gaiaCleanSqlString(test_str1);
if (strcmp(test_str1, "SELECT Foo from Bar;") != 0) {
fprintf(stderr, "gaiaCleanSqlString failure: %s\n", test_str1);
free(test_str1);
return -22;
}
free(test_str1);
asprintf(&test_str1, "SELECT %s from %s; ", "Foo", "Bar");
gaiaCleanSqlString(test_str1);
if (strcmp(test_str1, "SELECT Foo from Bar;") != 0) {
fprintf(stderr, "gaiaCleanSqlString failure: %s\n", test_str1);
free(test_str1);
return -23;
}
free(test_str1);
asprintf(&test_str1, "SELECT %s from %s; ", "Foo", "'");
gaiaCleanSqlString(test_str1);
if (strcmp(test_str1, "SELECT Foo from '';") != 0) {
fprintf(stderr, "gaiaCleanSqlString failure: %s\n", test_str1);
free(test_str1);
return -24;
}
free(test_str1);
#if 0
/* TODO: This will cause a buffer overflow */
asprintf(&test_str1, "SELECT %s from %s;", "Foo", "'");
gaiaCleanSqlString(test_str1);
if (strcmp(test_str1, "SELECT Foo from '';") != 0) {
fprintf(stderr, "gaiaCleanSqlString failure: %s\n", test_str1);
free(test_str1);
return -25;
}
free(test_str1);
#endif
asprintf(&test_str1, " ");
gaiaCleanSqlString(test_str1);
if (strcmp(test_str1, "") != 0) {
fprintf(stderr, "gaiaCleanSqlString failure: %s\n", test_str1);
free(test_str1);
return -26;
}
free(test_str1);
asprintf(&test_str1, "SELECT %s from %s;", "Foo", "Bar");
quoted_str = gaiaSingleQuotedSql(test_str1);
if (strcmp(quoted_str, "SELECT Foo from Bar;") != 0) {
fprintf(stderr, "gaiaSingleQuotedSql failure: %s\n", quoted_str);
free(test_str1);
free(quoted_str);
return -27;
}
free(test_str1);
free(quoted_str);
asprintf(&test_str1, "SELECT %s from %s; ", "Foo", "Bar");
quoted_str = gaiaSingleQuotedSql(test_str1);
if (strcmp(quoted_str, "SELECT Foo from Bar;") != 0) {
fprintf(stderr, "gaiaSingleQuotedSql failure: %s\n", quoted_str);
free(test_str1);
free(quoted_str);
return -28;
}
free(test_str1);
free(quoted_str);
asprintf(&test_str1, "SELECT %s from %s;", "Foo", "'");
quoted_str = gaiaSingleQuotedSql(test_str1);
if (strcmp(quoted_str, "SELECT Foo from '';") != 0) {
fprintf(stderr, "gaiaSingleQuotedSql failure: %s\n", quoted_str);
free(test_str1);
free(quoted_str);
return -29;
}
free(test_str1);
free(quoted_str);
asprintf(&test_str1, "SELECT %s from %s ; ", "Foo", "Bar");
quoted_str = gaiaSingleQuotedSql(test_str1);
if (strcmp(quoted_str, "SELECT Foo from Bar ;") != 0) {
fprintf(stderr, "gaiaSingleQuotedSql failure: %s\n", quoted_str);
free(test_str1);
free(quoted_str);
return -30;
}
free(test_str1);
free(quoted_str);
asprintf(&test_str1, "SELECT %s from %s;", "'", "Bar");
quoted_str = gaiaSingleQuotedSql(test_str1);
if (strcmp(quoted_str, "SELECT '' from Bar;") != 0) {
fprintf(stderr, "gaiaSingleQuotedSql failure: %s\n", quoted_str);
free(test_str1);
free(quoted_str);
return -31;
}
free(test_str1);
free(quoted_str);
asprintf(&test_str1, "My Name");
quoted_str = gaiaDoubleQuotedSql(test_str1);
if (strcmp(quoted_str, "My Name") != 0) {
fprintf(stderr, "gaiaDoubleQuotedSql failure: %s\n", quoted_str);
free(test_str1);
free(quoted_str);
return -32;
}
free(test_str1);
free(quoted_str);
quoted_str = gaiaDoubleQuotedSql(NULL);
if (quoted_str != NULL) {
fprintf(stderr, "gaiaDoubleQuotedSql NULL failure: %s\n", quoted_str);
free(quoted_str);
return -33;
}
quoted_str = gaiaSingleQuotedSql(NULL);
if (quoted_str != NULL) {
fprintf(stderr, "gaiaSingleQuotedSql NULL failure: %s\n", quoted_str);
free(quoted_str);
return -34;
}
quoted_str = gaiaQuotedSql("foo", GAIA_SQL_SINGLE_QUOTE);
if (strcmp(quoted_str, "foo") != 0) {
fprintf(stderr, "gaiaQuotedSql failure SINGLE_QUOTE: %s\n", quoted_str);
free(quoted_str);
return -35;
}
free(quoted_str);
quoted_str = gaiaQuotedSql("foo", GAIA_SQL_DOUBLE_QUOTE);
if (strcmp(quoted_str, "foo") != 0) {
fprintf(stderr, "gaiaQuotedSql failure DOUBLE_QUOTE: %s\n", quoted_str);
free(quoted_str);
return -36;
}
free(quoted_str);
quoted_str = gaiaQuotedSql("foo", 1000);
if (quoted_str != NULL) {
fprintf(stderr, "gaiaQuotedSql failure 1000: %s\n", quoted_str);
free(quoted_str);
return -37;
}
asprintf(&test_str1, "My \"Name");
quoted_str = gaiaDoubleQuotedSql(test_str1);
if (strcmp(quoted_str, "My \"\"Name") != 0) {
fprintf(stderr, "gaiaDoubleQuotedSql failure: %s\n", quoted_str);
free(test_str1);
free(quoted_str);
return -38;
}
free(test_str1);
free(quoted_str);
asprintf(&test_str1, "My \"Name ");
quoted_str = gaiaDoubleQuotedSql(test_str1);
if (strcmp(quoted_str, "My \"\"Name") != 0) {
fprintf(stderr, "gaiaDoubleQuotedSql failure: %s\n", quoted_str);
free(test_str1);
free(quoted_str);
return -39;
}
free(test_str1);
free(quoted_str);
asprintf(&test_str1, "");
quoted_str = gaiaDoubleQuotedSql(test_str1);
if (strcmp(quoted_str, "") != 0) {
fprintf(stderr, "gaiaDoubleQuotedSql failure: %s\n", quoted_str);
free(test_str1);
free(quoted_str);
return -40;
}
free(test_str1);
free(quoted_str);
asprintf(&test_str1, " ");
quoted_str = gaiaDoubleQuotedSql(test_str1);
if (strcmp(quoted_str, "") != 0) {
fprintf(stderr, "gaiaDoubleQuotedSql failure: |%s|\n", quoted_str);
free(test_str1);
free(quoted_str);
return -41;
}
free(test_str1);
free(quoted_str);
asprintf(&test_str1, "' ");
quoted_str = gaiaDoubleQuotedSql(test_str1);
if (strcmp(quoted_str, "'") != 0) {
fprintf(stderr, "gaiaDoubleQuotedSql failure: %s\n", quoted_str);
free(test_str1);
free(quoted_str);
return -42;
}
free(test_str1);
free(quoted_str);
asprintf(&test_str1, "'");
quoted_str = gaiaDoubleQuotedSql(test_str1);
if (strcmp(quoted_str, "'") != 0) {
fprintf(stderr, "gaiaDoubleQuotedSql failure: %s\n", quoted_str);
free(test_str1);
free(quoted_str);
return -43;
}
free(test_str1);
free(quoted_str);
asprintf(&test_str1, "\"");
quoted_str = gaiaSingleQuotedSql(test_str1);
if (strcmp(quoted_str, "\"") != 0) {
fprintf(stderr, "gaiaSingleQuotedSql failure: %s\n", quoted_str);
free(test_str1);
free(quoted_str);
return -44;
}
free(test_str1);
free(quoted_str);
return 0;
}
static int
vbbox_open (sqlite3_vtab * pVTab, sqlite3_vtab_cursor ** ppCursor)
{
/* opening a new cursor */
sqlite3_stmt *stmt;
gaiaOutBuffer sql_statement;
int ret;
char *sql;
int ic;
char *xname;
VirtualBBoxCursorPtr cursor =
(VirtualBBoxCursorPtr) sqlite3_malloc (sizeof (VirtualBBoxCursor));
if (cursor == NULL)
return SQLITE_ERROR;
cursor->pVtab = (VirtualBBoxPtr) pVTab;
gaiaOutBufferInitialize (&sql_statement);
gaiaAppendToOutBuffer (&sql_statement, "SELECT ROWID");
xname = gaiaDoubleQuotedSql (cursor->pVtab->MinX);
sql = sqlite3_mprintf (",\"%s\"", xname);
free (xname);
gaiaAppendToOutBuffer (&sql_statement, sql);
sqlite3_free (sql);
xname = gaiaDoubleQuotedSql (cursor->pVtab->MinY);
sql = sqlite3_mprintf (",\"%s\"", xname);
free (xname);
gaiaAppendToOutBuffer (&sql_statement, sql);
sqlite3_free (sql);
xname = gaiaDoubleQuotedSql (cursor->pVtab->MaxX);
sql = sqlite3_mprintf (",\"%s\"", xname);
free (xname);
gaiaAppendToOutBuffer (&sql_statement, sql);
sqlite3_free (sql);
xname = gaiaDoubleQuotedSql (cursor->pVtab->MaxY);
sql = sqlite3_mprintf (",\"%s\"", xname);
free (xname);
gaiaAppendToOutBuffer (&sql_statement, sql);
sqlite3_free (sql);
if (cursor->pVtab->ColSrid == NULL)
gaiaAppendToOutBuffer (&sql_statement, ",NULL");
else
{
xname = gaiaDoubleQuotedSql (cursor->pVtab->ColSrid);
sql = sqlite3_mprintf (",\"%s\"", xname);
free (xname);
gaiaAppendToOutBuffer (&sql_statement, sql);
sqlite3_free (sql);
}
for (ic = 0; ic < cursor->pVtab->nColumns; ic++)
{
value_set_null (*(cursor->pVtab->Value + ic));
if (*(cursor->pVtab->Visible + ic) != 'Y')
continue;
xname = gaiaDoubleQuotedSql (*(cursor->pVtab->Column + ic));
sql = sqlite3_mprintf (",\"%s\"", xname);
free (xname);
gaiaAppendToOutBuffer (&sql_statement, sql);
sqlite3_free (sql);
}
xname = gaiaDoubleQuotedSql (cursor->pVtab->table);
sql = sqlite3_mprintf (" FROM \"%s\" WHERE ROWID >= ?", xname);
free (xname);
gaiaAppendToOutBuffer (&sql_statement, sql);
sqlite3_free (sql);
if (sql_statement.Error == 0 && sql_statement.Buffer != NULL)
ret =
sqlite3_prepare_v2 (cursor->pVtab->db, sql_statement.Buffer,
strlen (sql_statement.Buffer), &stmt, NULL);
else
ret = SQLITE_ERROR;
gaiaOutBufferReset (&sql_statement);
if (ret != SQLITE_OK)
{
/* an error occurred */
cursor->eof = 1;
return SQLITE_ERROR;
}
cursor->stmt = stmt;
cursor->current_row = LONG64_MIN;
cursor->eof = 0;
*ppCursor = (sqlite3_vtab_cursor *) cursor;
vbbox_read_row (cursor);
return SQLITE_OK;
}
static int
vknn_check_rtree (sqlite3 * sqlite, const char *db_prefix,
const char *table_name, const char *geom_column,
char **real_table, char **real_geom, int *is_geographic)
{
/* checks if the required RTree is actually defined */
sqlite3_stmt *stmt;
char *sql_statement;
int ret;
int count = 0;
char *rt = NULL;
char *rg = NULL;
int is_longlat = 0;
if (db_prefix == NULL)
{
sql_statement =
sqlite3_mprintf
("SELECT f_table_name, f_geometry_column, SridIsGeographic(srid) "
"FROM geometry_columns WHERE Upper(f_table_name) = Upper(%Q) AND "
"Upper(f_geometry_column) = Upper(%Q) AND spatial_index_enabled = 1",
table_name, geom_column);
}
else
{
char *quoted_db = gaiaDoubleQuotedSql (db_prefix);
sql_statement =
sqlite3_mprintf
("SELECT f_table_name, f_geometry_column, SridIsGeographic(srid) "
"FROM \"%s\".geometry_columns WHERE Upper(f_table_name) = Upper(%Q) AND "
"Upper(f_geometry_column) = Upper(%Q) AND spatial_index_enabled = 1",
quoted_db, table_name, geom_column);
free (quoted_db);
}
ret =
sqlite3_prepare_v2 (sqlite, sql_statement, strlen (sql_statement),
&stmt, NULL);
sqlite3_free (sql_statement);
if (ret != SQLITE_OK)
return 0;
while (1)
{
/* scrolling the result set rows */
ret = sqlite3_step (stmt);
if (ret == SQLITE_DONE)
break; /* end of result set */
if (ret == SQLITE_ROW)
{
const char *v = (const char *) sqlite3_column_text (stmt, 0);
int len = sqlite3_column_bytes (stmt, 0);
if (rt)
free (rt);
rt = malloc (len + 1);
strcpy (rt, v);
v = (const char *) sqlite3_column_text (stmt, 1);
len = sqlite3_column_bytes (stmt, 1);
if (rg)
free (rg);
rg = malloc (len + 1);
strcpy (rg, v);
is_longlat = sqlite3_column_int (stmt, 2);
count++;
}
}
sqlite3_finalize (stmt);
if (count != 1)
return vknn_check_view_rtree (sqlite, table_name, geom_column,
real_table, real_geom, is_geographic);
else
{
*real_table = rt;
*real_geom = rg;
*is_geographic = is_longlat;
}
return 1;
}
static int
vknn_filter (sqlite3_vtab_cursor * pCursor, int idxNum, const char *idxStr,
int argc, sqlite3_value ** argv)
{
/* setting up a cursor filter */
char *db_prefix = NULL;
char *table_name = NULL;
char *geom_column = NULL;
char *xtable = NULL;
char *xgeom = NULL;
char *xgeomQ;
char *xtableQ;
char *idx_name;
char *idx_nameQ;
char *sql_statement;
gaiaGeomCollPtr geom = NULL;
int ok_table = 0;
int ok_geom = 0;
int ok_max = 0;
int max_items = 3;
int is_geographic;
const unsigned char *blob;
int size;
int exists;
int ret;
sqlite3_stmt *stmt = NULL;
sqlite3_stmt *stmt_dist = NULL;
sqlite3_stmt *stmt_rect = NULL;
VirtualKnnCursorPtr cursor = (VirtualKnnCursorPtr) pCursor;
VirtualKnnPtr knn = (VirtualKnnPtr) cursor->pVtab;
VKnnContextPtr vknn_context = knn->knn_ctx;
if (idxStr)
idxStr = idxStr; /* unused arg warning suppression */
cursor->eof = 1;
if (idxStr)
idxStr = idxStr; /* unused arg warning suppression */
cursor->eof = 1;
if (idxNum == 1 && argc == 3)
{
/* retrieving the Table/Column/Geometry params */
if (sqlite3_value_type (argv[0]) == SQLITE_TEXT)
{
char *tn = (char *) sqlite3_value_text (argv[0]);
vknn_parse_table_name (tn, &db_prefix, &table_name);
ok_table = 1;
}
if (sqlite3_value_type (argv[1]) == SQLITE_TEXT)
{
geom_column = (char *) sqlite3_value_text (argv[1]);
ok_geom = 1;
}
if (sqlite3_value_type (argv[2]) == SQLITE_BLOB)
{
blob = sqlite3_value_blob (argv[2]);
size = sqlite3_value_bytes (argv[2]);
geom = gaiaFromSpatiaLiteBlobWkb (blob, size);
}
if (ok_table && ok_geom && geom)
;
else
{
/* invalid args */
goto stop;
}
}
if (idxNum == 2 && argc == 2)
{
/* retrieving the Table/Geometry params */
if (sqlite3_value_type (argv[0]) == SQLITE_TEXT)
{
char *tn = (char *) sqlite3_value_text (argv[0]);
vknn_parse_table_name (tn, &db_prefix, &table_name);
ok_table = 1;
}
if (sqlite3_value_type (argv[1]) == SQLITE_BLOB)
{
blob = sqlite3_value_blob (argv[1]);
size = sqlite3_value_bytes (argv[1]);
geom = gaiaFromSpatiaLiteBlobWkb (blob, size);
}
if (ok_table && geom)
;
else
{
/* invalid args */
goto stop;
}
}
if (idxNum == 3 && argc == 4)
{
/* retrieving the Table/Column/Geometry/MaxItems params */
if (sqlite3_value_type (argv[0]) == SQLITE_TEXT)
{
char *tn = (char *) sqlite3_value_text (argv[0]);
vknn_parse_table_name (tn, &db_prefix, &table_name);
ok_table = 1;
}
if (sqlite3_value_type (argv[1]) == SQLITE_TEXT)
{
geom_column = (char *) sqlite3_value_text (argv[1]);
ok_geom = 1;
}
if (sqlite3_value_type (argv[2]) == SQLITE_BLOB)
{
blob = sqlite3_value_blob (argv[2]);
size = sqlite3_value_bytes (argv[2]);
geom = gaiaFromSpatiaLiteBlobWkb (blob, size);
}
if (sqlite3_value_type (argv[3]) == SQLITE_INTEGER)
{
max_items = sqlite3_value_int (argv[3]);
if (max_items > 1024)
max_items = 1024;
if (max_items < 1)
max_items = 1;
ok_max = 1;
}
if (ok_table && ok_geom && geom && ok_max)
;
else
{
/* invalid args */
goto stop;
}
}
if (idxNum == 4 && argc == 3)
{
/* retrieving the Table/Geometry/MaxItems params */
if (sqlite3_value_type (argv[0]) == SQLITE_TEXT)
{
char *tn = (char *) sqlite3_value_text (argv[0]);
vknn_parse_table_name (tn, &db_prefix, &table_name);
ok_table = 1;
}
if (sqlite3_value_type (argv[1]) == SQLITE_BLOB)
{
blob = sqlite3_value_blob (argv[1]);
size = sqlite3_value_bytes (argv[1]);
geom = gaiaFromSpatiaLiteBlobWkb (blob, size);
}
if (sqlite3_value_type (argv[2]) == SQLITE_INTEGER)
{
max_items = sqlite3_value_int (argv[2]);
if (max_items > 1024)
max_items = 1024;
if (max_items < 1)
max_items = 1;
ok_max = 1;
}
if (ok_table && geom && ok_max)
;
else
{
/* invalid args */
goto stop;
}
}
/* checking if the corresponding R*Tree exists */
if (ok_geom)
exists =
vknn_check_rtree (knn->db, db_prefix, table_name, geom_column,
&xtable, &xgeom, &is_geographic);
else
exists =
vknn_find_rtree (knn->db, db_prefix, table_name, &xtable,
&xgeom, &is_geographic);
if (!exists)
goto stop;
/* building the Distance query */
xgeomQ = gaiaDoubleQuotedSql (xgeom);
xtableQ = gaiaDoubleQuotedSql (xtable);
if (is_geographic)
sql_statement =
sqlite3_mprintf
("SELECT ST_Distance(?, \"%s\", 1) FROM \"%s\" WHERE rowid = ?",
xgeomQ, xtableQ);
else
sql_statement =
sqlite3_mprintf
("SELECT ST_Distance(?, \"%s\") FROM \"%s\" WHERE rowid = ?",
xgeomQ, xtableQ);
free (xgeomQ);
free (xtableQ);
ret =
sqlite3_prepare_v2 (knn->db, sql_statement, strlen (sql_statement),
&stmt_dist, NULL);
sqlite3_free (sql_statement);
if (ret != SQLITE_OK)
goto stop;
/* building the RTree MBR Distance query */
sql_statement = "SELECT ST_Distance(?, BuildMbr(?, ?, ?, ?))";
ret =
sqlite3_prepare_v2 (knn->db, sql_statement, strlen (sql_statement),
&stmt_rect, NULL);
if (ret != SQLITE_OK)
goto stop;
/* installing the R*Tree query callback */
gaiaMbrGeometry (geom);
vknn_init_context (vknn_context, xtable, xgeom, geom, max_items, stmt_dist,
stmt_rect);
gaiaFreeGeomColl (geom);
geom = NULL; /* releasing ownership on geom */
stmt_dist = NULL; /* releasing ownership on stmt_dist */
stmt_rect = NULL; /* releasing ownership on stmt_rect */
sqlite3_rtree_query_callback (knn->db, "knn_position", vknn_query_callback,
vknn_context, NULL);
/* building the RTree query */
idx_name = sqlite3_mprintf ("idx_%s_%s", xtable, xgeom);
idx_nameQ = gaiaDoubleQuotedSql (idx_name);
if (db_prefix == NULL)
{
sql_statement =
sqlite3_mprintf
("SELECT pkid FROM \"%s\" WHERE pkid MATCH knn_position(1)",
idx_nameQ);
}
else
{
char *quoted_db = gaiaDoubleQuotedSql (db_prefix);
sql_statement =
sqlite3_mprintf
("SELECT pkid FROM \"%s\".\"%s\" WHERE pkid MATCH knn_position(1)",
quoted_db, idx_nameQ);
free (quoted_db);
}
free (idx_nameQ);
sqlite3_free (idx_name);
ret =
sqlite3_prepare_v2 (knn->db, sql_statement, strlen (sql_statement),
&stmt, NULL);
sqlite3_free (sql_statement);
if (ret != SQLITE_OK)
goto stop;
vknn_context->curr_level = -1;
while (vknn_context->curr_level != 0)
{
/* querying the R*Tree: step #1 tree MBRs */
sqlite3_step (stmt);
if (vknn_context->curr_level == -1)
break; /* found an empty R*Tree, preasumably */
vknn_context->curr_level -= 1;
}
if (vknn_context->curr_level == 0)
{
/* querying the R*Tree: step #2 features */
sqlite3_step (stmt);
}
if (vknn_context->curr_items == 0)
cursor->eof = 1;
else
cursor->eof = 0;
cursor->CurrentIndex = 0;
stop:
if (geom)
gaiaFreeGeomColl (geom);
if (xtable)
free (xtable);
if (xgeom)
free (xgeom);
if (db_prefix)
free (db_prefix);
if (table_name)
free (table_name);
if (stmt != NULL)
sqlite3_finalize (stmt);
if (stmt_dist != NULL)
sqlite3_finalize (stmt_dist);
if (stmt_rect != NULL)
sqlite3_finalize (stmt_rect);
return SQLITE_OK;
}
static int
vxpath_create (sqlite3 * db, void *pAux, int argc, const char *const *argv,
sqlite3_vtab ** ppVTab, char **pzErr)
{
/* creates the virtual table for XPath */
VirtualXPathPtr p_vt;
char *vtable = NULL;
char *table = NULL;
char *column = NULL;
char *xname;
char *sql;
int okTable = 0;
int okCol = 0;
if (argc == 5)
{
vtable = gaiaDequotedSql ((char *) argv[2]);
table = gaiaDequotedSql ((char *) argv[3]);
column = gaiaDequotedSql ((char *) argv[4]);
}
else
{
*pzErr =
sqlite3_mprintf
("[VirtualXPath module] CREATE VIRTUAL: illegal arg list {void}\n");
return SQLITE_ERROR;
}
vxpath_check (db, table, column, &okTable, &okCol);
if (!okTable || !okCol)
goto illegal;
xname = gaiaDoubleQuotedSql (vtable);
sql = sqlite3_mprintf ("CREATE TABLE \"%s\" (pkid INTEGER, sub INTEGER, "
"parent TEXT, node TEXT, attribute TEXT, "
"value TEXT, xpath_expr TEXT)", xname);
free (xname);
if (sqlite3_declare_vtab (db, sql) != SQLITE_OK)
{
sqlite3_free (sql);
*pzErr =
sqlite3_mprintf
("[VirtualXPath module] CREATE VIRTUAL: invalid SQL statement \"%s\"",
sql);
goto error;
}
sqlite3_free (sql);
p_vt = (VirtualXPathPtr) sqlite3_malloc (sizeof (VirtualXPath));
if (!p_vt)
return SQLITE_NOMEM;
p_vt->db = db;
p_vt->p_cache = pAux;
if (p_vt->p_cache == NULL)
spatialite_e ("VirtualXPath WARNING - no XML cache is available !!!\n");
p_vt->nRef = 0;
p_vt->zErrMsg = NULL;
p_vt->table = table;
p_vt->column = column;
*ppVTab = (sqlite3_vtab *) p_vt;
free (vtable);
return SQLITE_OK;
illegal:
/* something is going the wrong way */
if (!okTable == 0)
*pzErr =
sqlite3_mprintf
("[VirtualXPath module] table \"%s\" doesn't exists\n", table);
else if (!okCol)
*pzErr =
sqlite3_mprintf
("[VirtualXPath module] table \"%s\" exists, but has no \"%s\" column\n",
table, column);
error:
return SQLITE_ERROR;
}
static int
vdbf_create (sqlite3 * db, void *pAux, int argc, const char *const *argv,
sqlite3_vtab ** ppVTab, char **pzErr)
{
/* creates the virtual table connected to some DBF */
char *sql;
VirtualDbfPtr p_vt;
char path[2048];
char encoding[128];
const char *pEncoding = NULL;
int len;
const char *pPath = NULL;
gaiaDbfFieldPtr pFld;
int cnt;
int col_cnt;
int seed;
int dup;
int idup;
char *xname;
char **col_name = NULL;
gaiaOutBuffer sql_statement;
if (pAux)
pAux = pAux; /* unused arg warning suppression */
/* checking for DBF PATH */
if (argc == 5)
{
pPath = argv[3];
len = strlen (pPath);
if ((*(pPath + 0) == '\'' || *(pPath + 0) == '"')
&& (*(pPath + len - 1) == '\'' || *(pPath + len - 1) == '"'))
{
/* the path is enclosed between quotes - we need to dequote it */
strcpy (path, pPath + 1);
len = strlen (path);
*(path + len - 1) = '\0';
}
else
strcpy (path, pPath);
pEncoding = argv[4];
len = strlen (pEncoding);
if ((*(pEncoding + 0) == '\'' || *(pEncoding + 0) == '"')
&& (*(pEncoding + len - 1) == '\''
|| *(pEncoding + len - 1) == '"'))
{
/* the charset-name is enclosed between quotes - we need to dequote it */
strcpy (encoding, pEncoding + 1);
len = strlen (encoding);
*(encoding + len - 1) = '\0';
}
else
strcpy (encoding, pEncoding);
}
else
{
*pzErr =
sqlite3_mprintf
("[VirtualDbf module] CREATE VIRTUAL: illegal arg list {dbf_path, encoding}");
return SQLITE_ERROR;
}
p_vt = (VirtualDbfPtr) sqlite3_malloc (sizeof (VirtualDbf));
if (!p_vt)
return SQLITE_NOMEM;
p_vt->pModule = &my_dbf_module;
p_vt->nRef = 0;
p_vt->zErrMsg = NULL;
p_vt->db = db;
p_vt->dbf = gaiaAllocDbf ();
/* trying to open file */
gaiaOpenDbfRead (p_vt->dbf, path, encoding, "UTF-8");
if (!(p_vt->dbf->Valid))
{
/* something is going the wrong way; creating a stupid default table */
xname = gaiaDoubleQuotedSql ((const char *) argv[2]);
sql = sqlite3_mprintf ("CREATE TABLE \"%s\" (PKUID INTEGER)", xname);
free (xname);
if (sqlite3_declare_vtab (db, sql) != SQLITE_OK)
{
sqlite3_free (sql);
*pzErr =
sqlite3_mprintf
("[VirtualDbf module] cannot build a table from DBF\n");
return SQLITE_ERROR;
}
sqlite3_free (sql);
*ppVTab = (sqlite3_vtab *) p_vt;
return SQLITE_OK;
}
/* preparing the COLUMNs for this VIRTUAL TABLE */
gaiaOutBufferInitialize (&sql_statement);
xname = gaiaDoubleQuotedSql (argv[2]);
sql = sqlite3_mprintf ("CREATE TABLE \"%s\" (PKUID INTEGER", xname);
free (xname);
gaiaAppendToOutBuffer (&sql_statement, sql);
sqlite3_free (sql);
/* checking for duplicate / illegal column names and antialising them */
col_cnt = 0;
pFld = p_vt->dbf->Dbf->First;
while (pFld)
{
/* counting DBF fields */
col_cnt++;
pFld = pFld->Next;
}
col_name = malloc (sizeof (char *) * col_cnt);
cnt = 0;
seed = 0;
pFld = p_vt->dbf->Dbf->First;
while (pFld)
{
xname = gaiaDoubleQuotedSql (pFld->Name);
dup = 0;
for (idup = 0; idup < cnt; idup++)
{
if (strcasecmp (xname, *(col_name + idup)) == 0)
dup = 1;
}
if (strcasecmp (xname, "\"PKUID\"") == 0)
dup = 1;
if (dup)
{
free (xname);
sql = sqlite3_mprintf ("COL_%d", seed++);
xname = gaiaDoubleQuotedSql (sql);
sqlite3_free (sql);
}
if (pFld->Type == 'N')
{
if (pFld->Decimals > 0 || pFld->Length > 18)
sql = sqlite3_mprintf (", \"%s\" DOUBLE", xname);
else
sql = sqlite3_mprintf (", \"%s\" INTEGER", xname);
}
else if (pFld->Type == 'F')
sql = sqlite3_mprintf (", \"%s\" DOUBLE", xname);
else
sql =
sqlite3_mprintf (", \"%s\" VARCHAR(%d)", xname, pFld->Length);
gaiaAppendToOutBuffer (&sql_statement, sql);
sqlite3_free (sql);
*(col_name + cnt) = xname;
cnt++;
pFld = pFld->Next;
}
gaiaAppendToOutBuffer (&sql_statement, ")");
if (col_name)
{
/* releasing memory allocation for column names */
for (cnt = 0; cnt < col_cnt; cnt++)
free (*(col_name + cnt));
free (col_name);
}
if (sql_statement.Error == 0 && sql_statement.Buffer != NULL)
{
if (sqlite3_declare_vtab (db, sql_statement.Buffer) != SQLITE_OK)
{
*pzErr =
sqlite3_mprintf
("[VirtualDbf module] CREATE VIRTUAL: invalid SQL statement \"%s\"",
sql_statement.Buffer);
gaiaOutBufferReset (&sql_statement);
return SQLITE_ERROR;
}
}
gaiaOutBufferReset (&sql_statement);
*ppVTab = (sqlite3_vtab *) p_vt;
return SQLITE_OK;
}
std::string Auxiliary::doubleQuotedSql(const char * value)
{
return Auxiliary::toString(gaiaDoubleQuotedSql(value),
"Failed doule quoting SQL.");
}
static int
vbbox_create (sqlite3 * db, void *pAux, int argc, const char *const *argv,
sqlite3_vtab ** ppVTab, char **pzErr)
{
/* creates the virtual table connected to some BoundingBox table */
char *vtable = NULL;
char *table = NULL;
char *col_minx = NULL;
char *col_miny = NULL;
char *col_maxx = NULL;
char *col_maxy = NULL;
char *col_srid = NULL;
char *x_force_wgs84 = NULL;
int ret;
int i;
int i2;
int len;
int n_rows;
int n_columns;
const char *col_name;
const char *col_type;
int force_wgs84;
char **results;
char *sql;
char *xname;
gaiaOutBuffer sql_statement;
VirtualBBoxPtr p_vt = NULL;
if (pAux)
pAux = pAux; /* unused arg warning suppression */
gaiaOutBufferInitialize (&sql_statement);
/* checking for table_name */
if (argc >= 10)
{
vtable = gaiaDequotedSql ((char *) argv[2]);
table = gaiaDequotedSql ((char *) argv[3]);
col_minx = gaiaDequotedSql ((char *) argv[4]);
col_miny = gaiaDequotedSql ((char *) argv[5]);
col_maxx = gaiaDequotedSql ((char *) argv[6]);
col_maxy = gaiaDequotedSql ((char *) argv[7]);
col_srid = gaiaDequotedSql ((char *) argv[8]);
x_force_wgs84 = gaiaDequotedSql ((char *) argv[9]);
}
else
{
*pzErr =
sqlite3_mprintf
("[VirtualBBox module] CREATE VIRTUAL: illegal arg list {table_name, col_minx, col_miny, col_maxx, col_maxy, srid, longlat=1|0, columns}\n");
goto error;
}
if (strcmp (x_force_wgs84, "0") == 0)
force_wgs84 = 0;
else if (strcmp (x_force_wgs84, "1") == 0)
force_wgs84 = 1;
else
{
*pzErr =
sqlite3_mprintf
("[VirtualBBox module] CREATE VIRTUAL: illegal arg list {table_name, col_minx, col_miny, col_maxx, col_maxy, srid, longlat=1|0, columns}\n");
goto error;
}
/* retrieving the base table columns */
xname = gaiaDoubleQuotedSql (table);
sql = sqlite3_mprintf ("PRAGMA table_info(\"%s\")", xname);
free (xname);
ret = sqlite3_get_table (db, sql, &results, &n_rows, &n_columns, NULL);
sqlite3_free (sql);
if (ret != SQLITE_OK)
goto illegal;
if (n_rows >= 1)
{
p_vt = (VirtualBBoxPtr) sqlite3_malloc (sizeof (VirtualBBox));
if (!p_vt)
return SQLITE_NOMEM;
p_vt->db = db;
p_vt->p_cache = pAux;
p_vt->nRef = 0;
p_vt->zErrMsg = NULL;
len = strlen (table);
p_vt->table = sqlite3_malloc (len + 1);
strcpy (p_vt->table, table);
p_vt->nColumns = n_rows;
p_vt->Column = sqlite3_malloc (sizeof (char *) * n_rows);
p_vt->Type = sqlite3_malloc (sizeof (char *) * n_rows);
p_vt->Visible = sqlite3_malloc (sizeof (char *) * n_rows);
memset (p_vt->Visible, 'N', n_rows);
p_vt->Value = sqlite3_malloc (sizeof (SqliteValuePtr) * n_rows);
p_vt->Srid = atoi (col_srid);
p_vt->ForceWGS84 = force_wgs84;
#ifndef OMIT_PROJ /* including PROJ.4 */
if (p_vt->ForceWGS84)
spatialite_e
("VirtualBBOX WARNING - WGS84 is requested, but PROJ4 support is currently disabled\n");
#endif /* end including PROJ.4 */
p_vt->ColSrid = NULL;
p_vt->MinX = NULL;
p_vt->MinY = NULL;
p_vt->MaxX = NULL;
p_vt->MaxY = NULL;
p_vt->BBoxGeom = NULL;
for (i = 0; i < n_rows; i++)
{
*(p_vt->Column + i) = NULL;
*(p_vt->Type + i) = NULL;
*(p_vt->Value + i) = value_alloc ();
}
for (i = 1; i <= n_rows; i++)
{
col_name = results[(i * n_columns) + 1];
col_type = results[(i * n_columns) + 2];
len = strlen (col_name);
if (strcasecmp (col_name, col_minx) == 0)
{
p_vt->MinX = sqlite3_malloc (len + 1);
strcpy (p_vt->MinX, col_name);
}
if (strcasecmp (col_name, col_miny) == 0)
{
p_vt->MinY = sqlite3_malloc (len + 1);
strcpy (p_vt->MinY, col_name);
}
if (strcasecmp (col_name, col_maxx) == 0)
{
p_vt->MaxX = sqlite3_malloc (len + 1);
strcpy (p_vt->MaxX, col_name);
}
if (strcasecmp (col_name, col_maxy) == 0)
{
p_vt->MaxY = sqlite3_malloc (len + 1);
strcpy (p_vt->MaxY, col_name);
}
if (strcasecmp (col_name, col_srid) == 0)
{
p_vt->ColSrid = sqlite3_malloc (len + 1);
strcpy (p_vt->ColSrid, col_name);
}
*(p_vt->Column + (i - 1)) = sqlite3_malloc (len + 1);
strcpy (*(p_vt->Column + (i - 1)), col_name);
len = strlen (col_type);
*(p_vt->Type + (i - 1)) = sqlite3_malloc (len + 1);
strcpy (*(p_vt->Type + (i - 1)), col_type);
for (i2 = 10; i2 < argc; i2++)
{
char *extra_col = gaiaDequotedSql ((char *) argv[i2]);
if (strcasecmp (extra_col, col_name) == 0)
*(p_vt->Visible + (i - 1)) = 'Y';
free (extra_col);
}
}
sqlite3_free_table (results);
}
else
{
sqlite3_free_table (results);
goto illegal;
}
if (p_vt->MinX == NULL || p_vt->MinY == NULL || p_vt->MaxX == NULL
|| p_vt->MaxY == NULL)
goto illegal;
/* preparing the COLUMNs for this VIRTUAL TABLE */
xname = gaiaDoubleQuotedSql (vtable);
sql = sqlite3_mprintf ("CREATE TABLE \"%s\" (Geometry Polygon", xname);
free (xname);
gaiaAppendToOutBuffer (&sql_statement, sql);
sqlite3_free (sql);
for (i = 0; i < p_vt->nColumns; i++)
{
if (*(p_vt->Visible + i) != 'Y')
continue;
xname = gaiaDoubleQuotedSql (*(p_vt->Column + i));
sql = sqlite3_mprintf (", \"%s\" %s", xname, *(p_vt->Type + i));
free (xname);
gaiaAppendToOutBuffer (&sql_statement, sql);
sqlite3_free (sql);
}
gaiaAppendToOutBuffer (&sql_statement, ")");
if (sql_statement.Error == 0 && sql_statement.Buffer != NULL)
{
if (sqlite3_declare_vtab (db, sql_statement.Buffer) != SQLITE_OK)
{
*pzErr =
sqlite3_mprintf
("[VirtualBBox module] CREATE VIRTUAL: invalid SQL statement \"%s\"",
sql);
goto error;
}
gaiaOutBufferReset (&sql_statement);
}
else
goto error;
*ppVTab = (sqlite3_vtab *) p_vt;
free (vtable);
free (table);
free (col_minx);
free (col_miny);
free (col_maxx);
free (col_maxy);
free (col_srid);
free (x_force_wgs84);
return SQLITE_OK;
illegal:
/* something is going the wrong way */
gaiaOutBufferReset (&sql_statement);
if (p_vt)
free_table (p_vt);
*pzErr =
sqlite3_mprintf
("[VirtualBBox module] '%s' isn't a valid BoundingBox table\n", table);
error:
if (vtable)
free (vtable);
if (table)
free (table);
if (col_minx)
free (col_minx);
if (col_miny)
free (col_miny);
if (col_maxx)
free (col_maxx);
if (col_maxy)
free (col_maxy);
if (col_srid)
free (col_srid);
if (x_force_wgs84)
free (x_force_wgs84);
gaiaOutBufferReset (&sql_statement);
return SQLITE_ERROR;
}
