Am 28.05.2008 um 17:52 schrieb D. Richard Hipp:
On May 28, 2008, at 11:25 AM, Jens Miltner wrote:
s there any work being done trying to either minimize the
synchronization needed or provide a memory allocator that doesn't do
all the alerting
Yes. This has been requested before and will
kgs wrote:
kgs wrote:
kgs wrote:
Hi all,
I've compiled sqlite3 for arm-linux(armv5t) as well as the tcl environment.
sqlite3 3.5.9
tcl 8.4
I've run testfixture all.test on the hardware it's compiled for.
Everything runs great until we come to the crash4-1.1.1 up to
Hi,
I need some help on a particular sql statement syntax. Consider the following
tables:
T1:
ID, NAME
1, John
T2:
REFID, COUNT, TYPE
1, 9, B
1, 5, U
1, 8, T
I have the following statement:
select T1.NAME, group_concat(T2.COUNT), group_concat(T2.TYPE) from T1 left join
T2 on T1.ID=T2.REFID;
On Jun 4, 2008, at 2:23 PM, kgs wrote:
kgs wrote:
kgs wrote:
kgs wrote:
Hi all,
I've compiled sqlite3 for arm-linux(armv5t) as well as the tcl
environment.
sqlite3 3.5.9
tcl 8.4
I've run testfixture all.test on the hardware it's compiled for.
Everything runs great until we come
Dan wrote:
On Jun 4, 2008, at 2:23 PM, kgs wrote:
kgs wrote:
kgs wrote:
kgs wrote:
Hi all,
I've compiled sqlite3 for arm-linux(armv5t) as well as the tcl
environment.
sqlite3 3.5.9
tcl 8.4
I've run testfixture all.test on the hardware it's compiled for.
Hi,
I've got a problem in combining expressions. On combining multiple ANDs with
an OR within a SELECT WHERE clause, SQLite takes years to respond. (Database
has a size of only about 1 MB).
Using two SELECTs and combining them by UNION works like a charm! What could
be the problem here?
*System.Data.SQLite *is an enhanced version of the original SQLite
http://www.sqlite.org/database engine. It is a complete drop-in
replacement for the original sqlite3.dll
If you don't use original sqlite I think you can't get any help here ...
--
[image: Just A Little Bit Of
Well why not? They've just integrated the ADO.NET provider into the DLL. It IS
SQLITE...
-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED] [mailto:sqlite-users-
[EMAIL PROTECTED] Im Auftrag von Federico Granata
Gesendet: Mittwoch, 4. Juni 2008 11:28
An: General Discussion of SQLite
Hi Markus,
http://www.sqlite.org/optoverview.html may help...
From earlier performance discussions, ORs can prevent indices being
used - do your tables have indices?
Try EXPLAIN QUERY PLAN preceding your SELECTs to determine whether
indices are being used.
Rgds,
Simon
2008/6/4 Markus Wolters
2008/6/4 Markus Wolters [EMAIL PROTECTED]:
Well why not? They've just integrated the ADO.NET provider into the DLL.
It IS SQLITE...
I have just quote what they write: It is a complete drop-in replacement for
the original sqlite3.dll I don't know what they rewrite and how ...
Wilson, Ron P schrieb:
I'm not a guru yet, but I think you are not using the latlon index in
your query. Perhaps if you index on lat and lon separately your query
will use those indices. I think the lines below indicate using the
indices on class_dds and rowid.
Thanks to everyone who
Can someone tell me what kind of performance one is to expect from a
40Mb Sqlite database like the one I have?
if you put it on a floppy and throw it out of the window it fall at 9.8 m/s
...
Can you give me some row of your db (also fake data are ok) so I try to
populate a db with 840k row
Can you give me some row of your db (also fake data are ok) so I try to
populate a db with 840k row and test your query on my machine ...
You can either take these rows here:
Pietraporzio|5|-1|7.032936|44.345913
Sambuco|5|-1|7.081367|44.33763
Le Pra|6|-1|6.88|44.316667
There is virtually no difference in using indices or not in my query.
I also tried to reformulate my statement in order not to use BETWEEN but
a sandwiched and statement:
SELECT * FROM Cities WHERE class_dds11 and (longitude_DDS BETWEEN
6.765103 and 7.089129) AND (latitude_DDS BETWEEN
can you post those rows with
.mode insert
so I can do a fast try ?
Tnx.
--
[image: Just A Little Bit Of
Geekness]http://feeds.feedburner.com/%7Er/JustALittleBitOfGeekness/%7E6/1
Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza.
(Larry Wall).
2008/6/4 Christophe Leske
Federico Granata schrieb:
can you post those rows with
.mode insert
so I can do a fast try ?
INSERT INTO table VALUES('Pietraporzio',5,-1,7.032936,44.345913);
INSERT INTO table VALUES('Sambuco',5,-1,7.081367,44.33763);
INSERT INTO table VALUES('Le Pra',6,-1,6.88,44.316667);
INSERT INTO
Markus Wolters [EMAIL PROTECTED] wrote
in message news:[EMAIL PROTECTED]
I've got a problem in combining expressions. On combining multiple
ANDs with an OR within a SELECT WHERE clause, SQLite takes years to
respond. (Database has a size of only about 1 MB).
Using two SELECTs and combining
Christophe Leske wrote:
There is virtually no difference in using indices or not in my query.
I also tried to reformulate my statement in order not to use BETWEEN but
a sandwiched and statement:
SELECT * FROM Cities WHERE class_dds11 and (longitude_DDS BETWEEN
6.765103 and 7.089129) AND
He Shiming [EMAIL PROTECTED] wrote
in message news:[EMAIL PROTECTED]
I need some help on a particular sql statement syntax. Consider the
following tables:
T1:
ID, NAME
1, John
T2:
REFID, COUNT, TYPE
1, 9, B
1, 5, U
1, 8, T
I have the following statement:
select T1.NAME,
Question, have you tried an index on class_dds, longitude_DDS, and
latitude_DDS?
CREATE INDEX tableidx ON table (class_dds, longitude_DDS, latitude_DDS);
Since all three fields are used in the query, I am curious if that would
help in any way.
Doesn´t do anything, there is something
Hello,
Hi, I'm a student studying SQLite. I am trying to create a memory DB and
dump it into a file when the program closes, and opening the memory DB back
from a file when SQLite is first executed. Actually there is a patch
regarding this feature in SQLite-Wiki index, I downloaded it and tried
First, note that left join is a red herring here, since you don't in fact
have any records in T1 without a matching record in T2.
Try this:
select NAME, group_concat(COUNT), group_concat(TYPE)
from (
select T1.NAME NAME, T2.COUNT COUNT, T2.TYPE TYPE
from T1 left join T2 on
Christophe Leske schrieb:
Question, have you tried an index on class_dds, longitude_DDS, and
latitude_DDS?
CREATE INDEX tableidx ON table (class_dds, longitude_DDS, latitude_DDS);
Since all three fields are used in the query, I am curious if that would
help in any way.
Doesn´t
Hello, how do I save a information + Date in a SQlite DB? I want later to
enter a date and get the Information. Like information from
07.08.05-03.04.06 or so. I tryed SQlite browser but found no way. I need to
share the DB betwen a Linux and WinXP system wihtout a server. Any hints how
to
class_dds has a maximum value of 6, so there where-clause class_dds11
is totally unecessary - if i ditch this part, the response time is
coming down to 900ms from 2700ms for my request.
I will now time again.
Some new timings - i basically got it. What I find to be weird is that
just
Hi Christophe,
Regarding: What I find to be weird is that just ONE index seems to
yield the same results as several fields indexed:
Perhaps you're using this already, but prefixing your SELECT with
EXPLAIN QUERY PLAN will quickly identify exactly which, if any
indicies are used. It's a much
Hi folks,
I want to move my sqlite db's to another location and wants to mount on
nfs.
when i try to select some data from my sqlitedb on nfs i'm getting error
below:
SQL error: Database is locked.
What's is the solution.
Thanks.
ps : I googled but cant find exact solution.
Sent from my BlackBerry® wireless device
-Original Message-
From: Van Ry, Wayne [EMAIL PROTECTED]
Date: Wed, 4 Jun 2008 14:17:33
To:[EMAIL PROTECTED]
Subject: FW: Sqlite SUM erroneous return value
__
From: Van Ry, Wayne
On Wed, Jun 4, 2008 at 7:12 AM, Christophe Leske [EMAIL PROTECTED] wrote:
Wilson, Ron P schrieb:
I'm not a guru yet, but I think you are not using the latlon index in
your query. Perhaps if you index on lat and lon separately your query
will use those indices. I think the lines below
M.Kursad DARA wrote:
Hi folks,
I want to move my sqlite db's to another location and wants to mount on
nfs.
when i try to select some data from my sqlitedb on nfs i'm getting error
below:
SQL error: Database is locked.
What's is the solution.
Thanks.
ps : I googled but cant find
Thanks
I'll try.
-Original Message-
From: Mihai Limbasan [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 04, 2008 4:29 PM
To: [EMAIL PROTECTED]; General Discussion of SQLite Database
Subject: Re: [sqlite] SQL error: database is locked
M.Kursad DARA wrote:
Hi folks,
I want to move
Hi,
I have created a table with the following values.
CREATE TABLE [test] ([Id] TEXT DEFAULT(0), [PV] REAL DEFAULT(0));
insert into [test] (Id, PV) values ('485314EL', '-720.237');
insert into [test] (Id, PV) values ('485314L', '-720.237');
insert into [test] (Id, PV) values ('485314LE',
On Jun 4, 2008, at 6:18 AM, [EMAIL PROTECTED] wrote:
I have created a table with the following values.
CREATE TABLE [test] ([Id] TEXT DEFAULT(0), [PV] REAL DEFAULT(0));
insert into [test] (Id, PV) values ('485314EL', '-720.237');
insert into [test] (Id, PV) values ('485314L',
Hi,
i am still fiddling around with my database and was wondering which kind
of query would be quicker?
I have three values i am interested in my request:
- longitude_dds
- latitude_dds
- class_dds (being the importance of the city, with 1 = capital and
6=village)
I have 2 indices so far:
On Jun 4, 2008, at 6:53 AM, Christophe Leske wrote:
Hi,
i am still fiddling around with my database and was wondering which
kind
of query would be quicker?
Let me strongly reiterate that you look into using the new R-Tree
virtual table available for SQLite. R-Trees are specifically
On 6/4/08, D. Richard Hipp [EMAIL PROTECTED] wrote:
On Jun 4, 2008, at 6:53 AM, Christophe Leske wrote:
Hi,
i am still fiddling around with my database and was wondering which
kind
of query would be quicker?
Let me strongly reiterate that you look into using the new R-Tree
Let me strongly reiterate that you look into using the new R-Tree
virtual table available for SQLite. R-Trees are specifically designed
to do exactly the kind of query you are asking to do. See
http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/rtree/READMEv=1.2
R-Trees will be
I understand that BETWEEN gets translated to = and = (bigger or
equal, and small or equal).
Some time ago I tested and observed different behaviour for BETWEEN in
SQLite (sometime as = and =, sometime = and ), so test it for yourself.
I think it is better to use comparison signs (=,,) than
On Wed, Jun 4, 2008 at 10:01 AM, D. Richard Hipp [EMAIL PROTECTED] wrote:
Let me strongly reiterate that you look into using the new R-Tree
virtual table available for SQLite. R-Trees are specifically designed
to do exactly the kind of query you are asking to do. See
On Jun 4, 2008, at 7:06 AM, Christophe Leske wrote:
Let me strongly reiterate that you look into using the new R-Tree
virtual table available for SQLite. R-Trees are specifically
designed
to do exactly the kind of query you are asking to do. See
현시욱 wrote:
Hello,
Hi, I'm a student studying SQLite. I am trying to create a memory DB and
dump it into a file when the program closes, and opening the memory DB back
from a file when SQLite is first executed. Actually there is a patch
regarding this feature in SQLite-Wiki index, I
On Jun 4, 2008, at 7:13 AM, Derrell Lipman wrote:
On Wed, Jun 4, 2008 at 10:01 AM, D. Richard Hipp [EMAIL PROTECTED]
wrote:
Let me strongly reiterate that you look into using the new R-Tree
virtual table available for SQLite. R-Trees are specifically
designed
to do exactly the kind
the_chill wrote:
Hello, how do I save a information + Date in a SQlite DB? I want later to
enter a date and get the Information. Like information from
07.08.05-03.04.06 or so. I tryed SQlite browser but found no way. I need to
share the DB betwen a Linux and WinXP system wihtout a server. Any
Hi Christophe,
On 6/4/08, Christophe Leske [EMAIL PROTECTED] wrote:
..
AND (latitude_DDS BETWEEN 44.261771 and 44.424779)
You might want to check if you can somehow store this data *without*
the decimal point (with the point implied - counting six digits from
right to left) and
To compile the R-Tree extension, you only need rtree.c and rtree.h,
which you can pull directly from the website without having to use
CVS. (OK, you'll probably also need sqlite3.h and sqlite3ext.h, but I
assumed you already have those.)
Hi,
thank you for this, i got the files.
This wiki page (http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions)
talks about
SQLite's loadable extension functionality.
If you can tell me what platform you're compiling for (processor, O/S
version, etc.), and what build tools
(cygwin/gcc, mingw, MSVC, etc.) you're using, I will try and
If you can tell me what platform you're compiling for (processor, O/S
version, etc.), and what build tools
(cygwin/gcc, mingw, MSVC, etc.) you're using, I will try and walk you
through the steps for building the
RTree module as a separate DLL.
Hi,
i am on Windows, and I got myself the
Sorry, I was too quick - i now got a 80Kb rtree.dll file which seems
fine. I will test it.
Thanks to everyone for your support,
Christophe Leske
www.multimedial.de - [EMAIL PROTECTED]
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249
D. Richard Hipp wrote:
On Jun 4, 2008, at 7:13 AM, Derrell Lipman wrote:
On Wed, Jun 4, 2008 at 10:01 AM, D. Richard Hipp [EMAIL PROTECTED]
wrote:
Let me strongly reiterate that you look into using the new R-Tree
virtual table available for SQLite. R-Trees are specifically
designed
If you can tell me what platform you're compiling for (processor, O/S
version, etc.), and what build tools
(cygwin/gcc, mingw, MSVC, etc.) you're using, I will try and walk you
through the steps for building the
RTree module as a separate DLL.
Shane,
I got a version, but it is
On Jun 4, 2008, at 8:05 AM, Steve Kallenborn wrote:
In the same document:
For each pair of real values, the first (leftmost) must be
less than or greater than the second.
should presumable read less than or equal to
OK. Try
Christophe Leske wrote:
Hi,
i am still fiddling around with my database and was wondering which kind
of query would be quicker?
I have three values i am interested in my request:
- longitude_dds
- latitude_dds
- class_dds (being the importance of the city, with 1 = capital and
Cole,
thanks for your help.
I doubt that you will. They are going to produce the same code. I would
stick with between since it is logically clearer.
Yes, this is also what I am seeing here from my timings so far.
I would also use a single index on either longitude or latitude not
Without creating a .DEF file for MSVC to use, you need to tell it which
functions to export.
The easiest way to do this is with the __declspec(dllexport).
You should modify the rtree.c source file and add the following before each
public function:
__declspec(dllexport)
So for instance, line
You should modify the rtree.c source file and add the following before each
public function:
__declspec(dllexport)
So for instance, line 2772:
int sqlite3_extension_init(
becomes:
__declspec(dllexport) int sqlite3_extension_init(
Thank you, I got it to work!!!
Now, let´s see
Ok,
so i got the rtree extension to work. It does load and creates the
tables wanted.
Now I am studying the ReadMe
(http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/rtree/READMEv=1.4)
and there is this requirement:
All r-tree virtual tables have an odd number of columns between
3 and
On Wed, Jun 04, 2008 at 06:03:45PM +0200, Christophe Leske scratched on the
wall:
Ok,
so i got the rtree extension to work. It does load and creates the
tables wanted.
Now I am studying the ReadMe
(http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/rtree/READMEv=1.4)
and there is
The R in R-Tree is for rectangle. The structure is designed to
hold spaces, not points. You want to do something like:
... rtree(id, long-min, long-max, lat-min, lat-max)
For cities where you only have point locations, enter each lat and
long twice.
Well, my database holds
On Wed, Jun 04, 2008 at 06:18:22PM +0200, Christophe Leske scratched on the
wall:
The R in R-Tree is for rectangle. The structure is designed to
hold spaces, not points. You want to do something like:
... rtree(id, long-min, long-max, lat-min, lat-max)
For cities where you
You can read a very good overview of R-Trees at Wikipedia.
http://en.wikipedia.org/wiki/R-tree which includes some coordinate based
examples.
On 6/4/08, Jay A. Kreibich [EMAIL PROTECTED] wrote:
On Wed, Jun 04, 2008 at 06:18:22PM +0200, Christophe Leske scratched on the
wall:
The R in
Once you get it working with your data, you may want to play around with the
defines at the top of rtree.c.
/* Either, both or none of the following may be set to activate
** r*tree variant algorithms.
*/
#define VARIANT_RSTARTREE_CHOOSESUBTREE 0
#define VARIANT_RSTARTREE_REINSERT 1
/*
He Shiming [EMAIL PROTECTED] wrote
in message news:[EMAIL PROTECTED]
select NAME, group_concat(COUNT), group_concat(TYPE)
from (
select T1.NAME NAME, T2.COUNT COUNT, T2.TYPE TYPE
from T1 left join T2 on T1.ID=T2.REFID
order by T2.COUNT);
Thanks for the hint. It works, but I noticed the
D. Richard Hipp wrote:
Let me strongly reiterate that you look into using the new R-Tree
virtual table available for SQLite. R-Trees are specifically designed
to do exactly the kind of query you are asking to do. See
Dennis-
Your last simplification:
-- a further simplification of the general case that removes
-- redundant terms
select * from City
where id in
(
select id from CityLoc
where (lat_min :max_lat and lat_max :min_lat)
and (long_min
Is this how you expect the RTree tables to be used in a case like the OP
is interested in?
create table City (
id integer primary key,
nametext,
lat real,
longreal,
class integer
);
create virtual table
Shane Harrelson schrieb:
Dennis-
Your last simplification:
I never got that email from Dennis, I would be very interested in it.
Dennis, this is actually what i am currently doing.
However:
i see no speed up for large areas (half the globe, e.g.), but
considerable ones for small areas
I added an index on the ID field for the search in the city database,
that helped a bit, but i am dissapointed that the rtree search is not
faster than the normal search for bigger areas..
Here are the query times in ms for full globe view with zooming in to
Romania:
-- 21290 -- full
Christophe Leske wrote:
Shane Harrelson schrieb:
Dennis-
Your last simplification:
I never got that email from Dennis, I would be very interested in it.
Dennis, this is actually what i am currently doing.
However:
i see no speed up for large areas (half the globe, e.g.), but
The default R-Tree code uses the R*-tree algorithm for splitting. While
this should typically perform better than the other two variants (Guttman
Quadratic and Linear splitting), you may want to test them with your data
set and queries to see if either performs better. You will need to compile
Regarding:
-- 21290 -- full globe view
... is way to slow, it blocks the app 21 seconds - granted, it is the
inital start up, but still.
Well, some people aren't satisfied unless they can have the whole world,
apparently.;-)
1) Have you already exhausted tuning the database with larger
Also note that it's often possible to open a corrupted database and
operate on it with no error, nothing goes bad until you touch a
corrupted row/table/index. We've found that doing a PRAGMA
integrity_check is effective for discovering any/all corruption in a
database early and avoiding random
In addition to playing with the splitting algorithms, since you are
compiling your own DLL, you can customize the R-Tree module for 2 dimensions
-- i.e. hard-code it for 5 columns of data.
This would *roughly* be accomplished by replacing all occurrences of
pRtree-nDim
with
(2)
and replacing
Hi,
every SQlite file that has extension files in it is being reported to me
by SQLite Database Browser as being empty.
Is there any GUI app on Windows that can handle databases which have
extension tables in it?
--
Christophe Leske
www.multimedial.de - [EMAIL PROTECTED]
With the recent discussions on R-Tree's and spherical coordinates (i.e.
latitude and longitude), I was wondering how boundary conditions would be
handled.
If I choose a location on the equator as far from the prime meridian as
possible, and try to query for all the locations near that,
within a
On 6/4/08, Shane Harrelson [EMAIL PROTECTED] wrote:
With the recent discussions on R-Tree's and spherical coordinates (i.e.
latitude and longitude), I was wondering how boundary conditions would be
handled.
If I choose a location on the equator as far from the prime meridian as
possible,
Shane Harrelson wrote:
-- a further simplification of the general case that removes
-- redundant terms
select * from City
where id in
(
select id from CityLoc
where (lat_min :max_lat and lat_max :min_lat)
and (long_min :max_long and
Hello!
While testing our application we occasionally got the Unable to open
database error on several machines from time to time. Same problem is
also discussed on http://sqlite.phxsoftware.com/forums/t/689.aspx. It
was very hard to reproduce since it was timing dependent, but we
managed to pin
Dennis Cote schrieb:
Shane Harrelson wrote:
-- a further simplification of the general case that removes
-- redundant terms
select * from City
where id in
(
select id from CityLoc
where (lat_min :max_lat and lat_max :min_lat)
and
- TSVNCache.exe monitors files and for whatever reason it opens them
and performs some queries on them.
- SQLite creates a journal file.
- TSVNCache notices the new file and opens it.
- SQLite deletes the journal file and it now enters the Delete
Pending state since TSVNCache still has
On Wed, Jun 4, 2008 at 11:41 PM, Eric Minbiole
[EMAIL PROTECTED] wrote:
It looks like you can configure TortoiseSVN to include / exclude
specific paths during its searches. Though I've not tried it, I would
think you could simply exclude any paths that contain SQLite databases.
This thread
Christophe Leske wrote:
I need lat and long pos from CityLoc.
I got this currently,
sqlite Select cities.*, citylookup.longitude_min from cities,citylookup
where c
ities.id in (select id from citylookup where
(citylookup.longitude_min-45.0
0 and citylookup.longitude_max45.00)
If you aren't storing the lat and long data in the main table anymore,
you will have to join the RTree table on the id to get that data. I'm
guessing about your tables definitions, but you should get the idea from
this:
Yes, that is my setup, however, the new query is slow as hell.. so
I'm told that Firefox expects the full version number in the sqlite3.pc
file, not just 3.5.
Is this true? If so, why does configure.ac strip out the micro number?
(configure.ac does this:
VERSION=[`cat $srcdir/VERSION | sed 's/^\([0-9]*\.*[0-9]*\).*/\1/'`]
and substitutes the resulting
Sounds like the new journal_mode pragma might be a solution for you.
http://www.sqlite.org/pragma.html#pragma_journal_mode
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Filip Navara
Sent: Wednesday, June 04, 2008 2:54 PM
To: General Discussion of
On Jun 5, 2008, at 12:55 AM, Shane Harrelson wrote:
Once you get it working with your data, you may want to play around
with the
defines at the top of rtree.c.
/* Either, both or none of the following may be set to activate
** r*tree variant algorithms.
*/
#define
85 matches
Mail list logo