Re: [sqlite] R*Tree virtual table / if not exists clause
On 25/01/2012 12:55, Jay A. Kreibich wrote: That makes me think there is some more fundamental reason why IF NOT EXISTS is not supported. Thanks. Indeed. Hence the question. Something of a puzzle. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] R*Tree virtual table / if not exists clause
The console output below indicates that 'if not exists' clause is unsupported for virtual tables. Is this a bug, oversight or by design? sqlite> CREATE VIRTUAL TABLE if not exists gps_index USING rtree(id,minX,maxX,minY,maxY); Error: near "not": syntax error Many thanks. Using SQLite version 3.7.7.1 built with SQLITE_ENABLE_RTREE=1 (Windows 7x64U/VS2008) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] R*Tree insert error ...
On 18/01/2012 18:04, Dan Kennedy wrote: One possibility is that you're hitting one of the r-tree tables built-in constraints by specifying a record with (minval > maxval) for one or more of the dimensions. Rearranging the bind parameters for match minx,miny,maxx,maxy layout I now get this (doubles dumped with 8 decimal places) Inserting ID: 01, 51.51340259, -1.86352760, 51.51340259, -1.86352760 failure at read() error code 19 (constraint failed) This is repeatable using Sqlite3 sqlite> INSERT INTO gps_index VALUES(01, 51.51340259, -1.86352760, 51.51340259, -1.86352760); Error: constraint failed sqlite> (minval > maxval) So how does one insert a point? Is there a magic fudge factor involved? Surely minval >= maxval? I cannot see anything at http://www.sqlite.org/rtree.html Help much appreciated. Thanks. Jerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] R*Tree insert error ...
Hello Dan Are you using sqlite3_prepare() or sqlite3_prepare_v2()? If the former, try switching to prepare_v2() then checking the error message again. The former. I've switched to V2. One possibility is that you're hitting one of the r-tree tables built-in constraints by specifying a record with (minval > maxval) for one or more of the dimensions. Excellent call. I've been inserting lat,long,lat,long rather than lat,lat,long,long - the CREATE VIRTUAL TABLE statement having been copied & pasted from the online example. Apologies for the noise. Many thanks :) Jerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] R*Tree insert error ...
On 18/01/2012 16:41, Richard Hipp wrote: What does sqlite3_errmsg() say? 'SQL logic error or missing database' I should perhaps add this set of inserts is running inside a transaction with a WAL enabled database. There is only one write thread active. All other inserts (save R*Tree) work precisely as expected. Thanks for any pointers. Jerry. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] R*Tree insert error ...
One more oddity. Can anyone help? 1. Create a 2D index as per documentation: "CREATE VIRTUAL TABLE gps_index USING rtree(id,minX, maxX,minY, maxY)" 2. Create a statement using a binding string: "INSERT INTO gps_index VALUES(last_insert_rowid(), ?, ?, ?, ?)" 3. Bind values. Binding indices run from 1 to 4. This works. 4. Call sqlite3_step(). This now returns a 1 - SQLITE_ERROR. Thx++ Using SQLite version 3.7.7.1 built with SQLITE_ENABLE_RTREE=1 (Windows/VS2008) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] R*Tree insert error ...
One more oddity. Can anyone help? 1. Create a 2D index as per documentation: "CREATE VIRTUAL TABLE gps_index USING rtree(id,minX, maxX,minY, maxY)" 2. Create a statement using a binding string: "INSERT INTO gps_index VALUES(last_insert_rowid(), ?, ?, ?, ?)" 3. Bind values. Binding indices run from 1 to 4. This works. 4. Call sqlite3_step(). This now returns a 1 - SQLITE_ERROR. Thx++ Using SQLite version 3.7.7.1 built with SQLITE_ENABLE_RTREE=1 (Windows/VS2008) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using last_row_insert() with sqlite3_prepare() and friends.
Thanks to all. On 18/01/2012 14:53, Petite Abeille wrote: Or just use last_insert_rowid() directly in the next insert statement. That will reuse the rowid of the previous insert for the new one. And both row will end up with the same id, keeping your source table and its R*Tree index in synch. *Splendid* idea, one for the FAQ surely. Presumably this can be used in a binding string as in this? "INSERT INTO gps_index (auto_id,minX, maxX,minY, maxY) VALUES(last_insert_rowid(), ?, ?, ?, ?)" Thank you :) Jerry. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using last_row_insert() with sqlite3_prepare() and friends.
Using SQLite version 3.7.7.1 (Windows/VS2008). A table has an integer autoincrement primary key, the value of which is being used to feed an R*Tree index. I am attempting to insert data and retrieve the latest row_id() in a compound statement like this using the SQLite C API: "INSERT INTO table (columns) VALUES(?,?,?,?); SELECT last_insert_rowid() FROM table;" which works in the Sqlite3 console. However when I run this sqlite3_step() returns SQLITE_DONE. sqlite3_prepare(); sqlite3_bind_xxx(); sqlite3_step(); Can anyone point out what I am missing? Thx++. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] R*Tree virtual table question
Thanks Simon, Neither of those things are simple. Rather than hack an existing library, I recommend that you allow the R*Tree module to maintain its integer keys and make another (normal, not R*Tree) table in the same SQLite database to keep the list of GUIDs vs. integer keys. Ouch :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] R*Tree virtual table question
I've working on a location aware application that uses GUIDs as the primary key for related tables. The SQLite R*Tree module is the natural choice for indexing the image location data but there the issue of the integer primary key type. Is it possible to change the primary key type? Is it possible to add an additional column to the virtual table and retrieve its value in a query? Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
