[sqlite] R-Tree Storage Optimization for Points

Hello! We are using SQLite3 for storing geographical points that can be queried using a bounding box (find everything that lies within this box). Obviously, this query fits the capabilities of the RTree module very well and it is a simple 2 dimensional search using an R-Tree that has 5

Re: [sqlite] Error: database is locked

On Wed, Jun 18, 2014 at 6:17 PM, JohnG <4par...@gmail.com> wrote: > gelmjw@voyager /var/www/sqlite3/finviz $ cd /var/www/sqlite3/finviz/; > sqlite3 -init finviz.init finviz.db > ~SNIP~ > How do I clear this lock condition? > > Maybe try it like this instead: gelmjw@voyager

Re: [sqlite] problem sorting data

On 6/18/2014 12:19 PM, claude.pom...@free.fr wrote: i have query like this SELECT SUM(apport.poid) AS poid, SUM(apport.quantite) as nb, provenance.provenance, touches.label as label, apport.ladate FROM apport JOIN provenance ON apport.id_provenance = provenance.id_provenance JOIN touches ON

Re: [sqlite] Suggestion for SHELL impovement (built-in scripting)

Stored procedures variables, a goto(jump/branch) and a conditional so loops can be made; On the topic of shell results; isn't there a result value of like number of records inserted ? So something like var a = insert... select $a; and then test the output sort of? went searching cause I

[sqlite] problem sorting data

hello i have query like this SELECT SUM(apport.poid) AS poid, SUM(apport.quantite) as nb, provenance.provenance, touches.label as label, apport.ladate FROM apport JOIN provenance ON apport.id_provenance = provenance.id_provenance JOIN touches ON apport.id_touches = touches.id_touches

Re: [sqlite] Suggestion for SHELL impovement (built-in scripting)

My suggestion is to have a sqlite_... table in which to start statements to run at DB open time, so as to: - automatically CREATE temp tables, indexes, views - automatically ATTACH related DBs - automatically load extensions (this should require explicit acquiescence from the API caller

Re: [sqlite] Suggestion for SHELL impovement (built-in scripting)

On 06/18/2014 02:47 PM, to...@acm.org wrote: But you need bash, or TCL, or Perl, or Python, or whatever other than sqlite3.exe So, you're suggesting that an innocent SQLite user should install any of those programming packages just to run SQLite. Hmm... no, thanks! Yes. Quite simply you'll

Re: [sqlite] Suggestion for SHELL impovement (built-in scripting)

to...@acm.org wrote... Hi all, ... So here’s my suggestion for what (I feel) is a significant improvement for the SHELL version of SQLite without being too much of a programming complication in my view. (Those who usually attack any new concept, please pause a moment and give it some

[sqlite] Error: database is locked

gelmjw@voyager /var/www/sqlite3/finviz $ sqlite3 -version 3.7.9 2011-11-01 00:52:41 c7c6050ef060877ebe77b41d959e9df13f8c9b5e gelmjw@voyager /var/www/sqlite3/finviz $ gelmjw@voyager /var/www/sqlite3/finviz $ uname -a Linux voyager 3.2.0-23-generic #36-Ubuntu SMP Tue Apr 10 20:39:51 UTC 2012

[sqlite] Suggestion for SHELL impovement (built-in scripting)

Hi all, First of all, this is not about using SQLite as an embedded library from C, or whatever other language. It does not affect the sqlite3.c file at all. It only applies to the shell (so logically, it only affects shell.c) So here’s my suggestion for what (I feel) is a significant

Re: [sqlite] slowish R*Tree performance

On Wed, Jun 18, 2014 at 3:00 PM, Eric Rubin-Smith wrote: > Cory Nelson wrote: > > > Expand the prefix into the full feed:beef::etc > > > > Insert into a table (start binary(16), mask_length int) > > > > select top 1 binary,length from table where start <= @input order by >

Re: [sqlite] slowish R*Tree performance

Cory Nelson wrote: > Expand the prefix into the full feed:beef::etc > > Insert into a table (start binary(16), mask_length int) > > select top 1 binary,length from table where start <= @input order by > binary desc > > Check if the row is inside the range returned. This will take a

Re: [sqlite] slowish R*Tree performance

Cory Nelson wrote: > The phrase you're looking for here is "CIDR block". Well, I was avoiding the phrase on purpose :-). I was worried that using another bit of jargon -- one that is even more opaque than "prefix" to someone unfamiliar with the space -- did not seem likely to help get the

Re: [sqlite] slowish R*Tree performance

Carlos Ferreira wrote: > Regarding the R.Tree performance problem, > > What is the original problem that is causing slow performance in the > SQlite R-Tree implementation? I was populating my DB with bad data. In particular, I was first choosing a random prefix length, then filling up

Re: [sqlite] slowish R*Tree performance

On Wed, Jun 18, 2014 at 12:18 PM, Eric Rubin-Smith wrote: > Carlos Ferreira wrote: > > > 1 - There a data type named IPV6 Address. 2 - there is a table where > > this data type must be in. ( can be a set of fields, one blob, one > string > > ...) > > > > You want to: > > > >

Re: [sqlite] slowish R*Tree performance

Simon Slavin wrote: > Strongly suspect that although R*Trees produce an elegant solution to > your problem, the fact that they're a general case tool will make them too > slow to use for something like this. > > I propose an alternative solution, though I have not tried it and do not >

Re: [sqlite] slowish R*Tree performance

Carlos Ferreira wrote: > 1 - There a data type named IPV6 Address. 2 - there is a table where > this data type must be in. ( can be a set of fields, one blob, one string > ...) > > You want to: > > Given a certain IPV6, find in the database the existent IPV6 record with > the longest

Re: [sqlite] Help forming query

On 18 Jun 2014, at 7:01am, David M. Cotter wrote: > also: if this query isn't *very* fast, then i'm fine with just "give me the > value of the first cell where there is data in that column" SQL does not have a concept of 'first' row. Rows in a table do not have any order.

Re: [sqlite] Performance regression with multiple lower bound tests

On 16/06/2014, at 11:36 pm, Richard Hipp wrote: > On Mon, Jun 16, 2014 at 5:07 AM, David Empson wrote: > >> It appears SQLite 3.8.1 removed an optimisation where earlier versions of >> the query planner were checking for two or more "lower bound"

Re: [sqlite] Help forming query

On Jun 17, 2014, at 11:01 PM, David M. Cotter wrote: > also: if this query isn't *very* fast, then i'm fine with just "give me the > value of the first cell where there is data in that column" > > in the below case, i'd get a "1". > i would then proceed to delete everything

Re: [sqlite] Help forming query

On 18/06/2014, at 5:54 pm, David M. Cotter wrote: > i have a table with a numeric column (not the key column) > i want to obtain from this table a list of unique numbers appearing in that > one column > > some cells in the column may have nothing, some may have duplicate

Re: [sqlite] Help forming query

also: if this query isn't *very* fast, then i'm fine with just "give me the value of the first cell where there is data in that column" in the below case, i'd get a "1". i would then proceed to delete everything with a "1" in it (from this and other tables) then i'd ask again, this time i'd