[sqlite] crash4 test fails (3.5.9)
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
crash4-1.1000.1.
the messages are :
Expected: [1 {child process exited abnormally}]
Got: [1 {couldn't create error file for command: no such file or
directory}]
for each crash4-1.x.1, then :
*** Giving up...
1000 errors out of 26715 tests
the crash4-1.x.2 and crash4-1.x.3 return Ok.
Any ideas as to what may be wrong ?
Thanks in advance
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Forming a query with BLOB with null characters
Hello,
I have a simple question here. I want to generate a insert query (char*)
which will insert a blob in a table. This blob is nothing but a C
structure in my program.
So the query would be something like this
INSERT INTO table1 VALUES
('12323232\0\0\0\023232323\0\0\023232323\0\03445\0')
where the blob field is having some null characters in it. I know there
is a way to use bind blob but I want to create this query. Are there any
APIs that sqlite provides so that given a blob of x bytes, it will
generate corresponding string to be appended in the query?
I looked at sqlite3_snprintf, but it will print only till first \0
character in the string. I want to escape all the \0s till my x bytes of
buffer?
Can someone please give me any pointers for this ?
Thanks ,
Shailesh
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Corrupted sqlite_sequence table
I would remove the leading/trailing quotes external to the import of the file, using something like sed or gawk. I couldn't work out how to do this purely using sqlite, however. _ It's simple! Sell your car for just $30 at CarPoint.com.au http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F859641_t=762955845_r=tig_OCT07_m=EXT ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite on RAM
Hello.- but how can i make a different between all db on RAM if you define as :memory: ? -- Ing. Hildemaro Carrasquel Ingeniero de Proyectos Cel.: 04164388917/04121832139 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite on RAM
All your dbs will have different values. It is a C pointer value not an enumeration value. On Tue, Jun 3, 2008 at 8:28 AM, Hildemaro Carrasquel [EMAIL PROTECTED] wrote: Hello.- but how can i make a different between all db on RAM if you define as :memory: ? -- Ing. Hildemaro Carrasquel Ingeniero de Proyectos Cel.: 04164388917/04121832139 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] crash4 test fails (3.5.9)
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
crash4-1.1000.1.
the messages are :
Expected: [1 {child process exited abnormally}]
Got: [1 {couldn't create error file for command: no such file or
directory}]
for each crash4-1.x.1, then :
*** Giving up...
1000 errors out of 26715 tests
the crash4-1.x.2 and crash4-1.x.3 return Ok.
Any ideas as to what may be wrong ?
Thanks in advance
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
So this is apparently what is happening...
this code in tester.tcl :
set r [catch {
exec [info nameofexec] crash.tcl @stdout
} msg]
is returning :
1 {couldn't create error file for command: no such file or directory}
so i think that @stdout is trying to treat stdout as an open file...
but it doesn't exist.
I'm guessing that stdout is supposed to be a fixed thing in tcl... and
that this is supposed to take the output from the exec and pipe it to
stdout.
So... if this is how its supposed to work, then why isn't it ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Forming a query with BLOB with null characters
Thank you for the input. THis is what I tried,
char*data = THIS \\\IS 'BLAH
This is a blob data of 40 bytes that I wanted to store, so all bytes
after BLAH are all '\0'.
So I created a insert query like this, by converting the blob in
hexadecimal format,
Insert into table values
(X'54484953205c2249532027424c41480'); - 0 are 40 times.
in the sqlite shell, select * from table, shows me the data as it is in
X'hex' format.
X'54484953205c2249532027424c4148
00'
And when I try to retrieve it using sqlite3_column_blob, and try to
print that blob I see the same X'hex' string instead of THIS \\\IS
'BLAH.
Why is this behaviour? We give input in hex format for sqlite to
understand that this is a blob and not a text and it shoudl make the
appropriate conversion to store it internally. I know sqlite treats blob
and text the same, but is there no way that I can get the binary data as
I have? Do I again need to convert the hex string into my binary blob
buffer?
I hope people understood what I am trying to ask out here.
-Shailesh
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Shailesh Birari
Sent: Tuesday, June 03, 2008 5:02 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Forming a query with BLOB with null characters
Thanks I found that later.
sprintf would rather be a costly operation.
-Shailesh
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dan
Sent: Tuesday, June 03, 2008 3:47 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Forming a query with BLOB with null characters
On Jun 3, 2008, at 4:20 PM, Shailesh Birari wrote:
Hello,
I have a simple question here. I want to generate a insert query
(char*)
which will insert a blob in a table. This blob is nothing but a C
structure in my program.
So the query would be something like this
INSERT INTO table1 VALUES
('12323232\0\0\0\023232323\0\0\023232323\0\03445\0')
where the blob field is having some null characters in it. I know
there is a way to use bind blob but I want to create this
query. Are
there any APIs that sqlite provides so that given a blob of
x bytes,
it will generate corresponding string to be appended in the query?
I looked at sqlite3_snprintf, but it will print only till
first \0
character in the string. I want to escape all the \0s till
my x bytes
of buffer?
Can someone please give me any pointers for this ?
Use the blob literal syntax described here:
http://www.sqlite.org/lang_expr.html
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Forming a query with BLOB with null characters
Shailesh Birari wrote: Thank you for the input. THis is what I tried, char*data = THIS \\\IS 'BLAH This is a blob data of 40 bytes that I wanted to store, so all bytes after BLAH are all '\0'. No, data is a pointer to a literal character string of length 15. So I created a insert query like this, by converting the blob in hexadecimal format, Insert into table values (X'54484953205c2249532027424c41480'); - 0 are 40 times. in the sqlite shell, select * from table, shows me the data as it is in X'hex' format. X'54484953205c2249532027424c4148 00' And when I try to retrieve it using sqlite3_column_blob, and try to print that blob I see the same X'hex' string instead of THIS \\\IS 'BLAH. You encoded the data as hex characters before you inserted it, why are you surprised to get the data back encoded as hex characters? If you want to get the data back as text you can cast the result of your query, but then you will run into your original problem of access to data after the first zero character. select cast(blob_field as text) from table; Why is this behaviour? We give input in hex format for sqlite to understand that this is a blob and not a text and it shoudl make the appropriate conversion to store it internally. I know sqlite treats blob and text the same, but is there no way that I can get the binary data as I have? Do I again need to convert the hex string into my binary blob buffer? You would really be much better off using bound parameters and the C API functions to store and retrieve blob data. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
Darko Filipovic [EMAIL PROTECTED] wrote: I've tried...(not with UFO :D ). Nothing happens, database is not corrupted and that is what confuses me...I thought it should not be readable (malformed) ?! Not necessarily. Suppose you issued an update statement that was supposed to update 100 records. Before the process crashed, 50 of them were successfully updated (e.g. they just happened to sit on the same page), but the other 50 were not. The database is not physically corrupted - the table and record structure is intact. But it's logically corrupted, in that some database invariants important to your application may have been violated. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
On 6/3/08, Darko Filipovic [EMAIL PROTECTED] wrote: I've tried...(not with UFO :D ). Nothing happens, database is not corrupted and that is what confuses me...I thought it should not be readable (malformed) ?! The journal file is happily delete-able. So is the db itself. Try it. My sense is that if you delete the journal file, you lose the ability to roll back your database. Other than that, life continues to exist. Greetings, Darko F. Federico Granata wrote: 2008/6/3 Darko Filipovic [EMAIL PROTECTED]: But, what happen if journal file is deleted before starting B process? what if a UFO stole your pc ? :-D try to delete journal file and see what happens ... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users __ NOD32 3154 (20080603) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
The thing is that my system sometimes produces malformed database, but I don't know what cause that. I'm trying to collect possible cases when database gets malformed. I'm going in this direction because it is not possible to debug system to reproduce condition. Because of that question is: can lack of journal file produce malformed database file? Greetings, Darko F. Igor Tandetnik wrote: Darko Filipovic [EMAIL PROTECTED] wrote: I've tried...(not with UFO :D ). Nothing happens, database is not corrupted and that is what confuses me...I thought it should not be readable (malformed) ?! Not necessarily. Suppose you issued an update statement that was supposed to update 100 records. Before the process crashed, 50 of them were successfully updated (e.g. they just happened to sit on the same page), but the other 50 were not. The database is not physically corrupted - the table and record structure is intact. But it's logically corrupted, in that some database invariants important to your application may have been violated. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users __ NOD32 3154 (20080603) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
I've tried...(not with UFO :D ). Nothing happens, database is not corrupted and that is what confuses me...I thought it should not be readable (malformed) ?! Greetings, Darko F. Federico Granata wrote: 2008/6/3 Darko Filipovic [EMAIL PROTECTED]: But, what happen if journal file is deleted before starting B process? what if a UFO stole your pc ? :-D try to delete journal file and see what happens ... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users __ NOD32 3154 (20080603) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
2008/6/3 Darko Filipovic [EMAIL PROTECTED]: But, what happen if journal file is deleted before starting B process? what if a UFO stole your pc ? :-D try to delete journal file and see what happens ... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] crash4 test fails (3.5.9)
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
crash4-1.1000.1.
the messages are :
Expected: [1 {child process exited abnormally}]
Got: [1 {couldn't create error file for command: no such file or
directory}]
for each crash4-1.x.1, then :
*** Giving up...
1000 errors out of 26715 tests
the crash4-1.x.2 and crash4-1.x.3 return Ok.
Any ideas as to what may be wrong ?
Thanks in advance
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
So this is apparently what is happening...
this code in tester.tcl :
set r [catch {
exec [info nameofexec] crash.tcl @stdout
} msg]
is returning :
1 {couldn't create error file for command: no such file or directory}
so i think that @stdout is trying to treat stdout as an open file...
but it doesn't exist.
I'm guessing that stdout is supposed to be a fixed thing in tcl... and
that this is supposed to take the output from the exec and pipe it to
stdout.
So... if this is how its supposed to work, then why isn't it ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Never mind that last message, @stdout puts the error message i'm
getting in msg through the catch.
So that leaves, that the testfixture command is not being found. I
checked what info nameofexec is returning, and it returns the full
path and name of testfixture.
When I run testfixture crash.tcl, I get no error... I am confused.
Forgive my babbling, this is my first day with tcl.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
On 6/3/08, Federico Granata [EMAIL PROTECTED] wrote: 2008/6/3 Darko Filipovic [EMAIL PROTECTED]: But, what happen if journal file is deleted before starting B process? what if a UFO stole your pc ? :-D try to delete journal file and see what happens ... my vote for the funniest message in a long time. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
Darko Filipovic [EMAIL PROTECTED] wrote: The thing is that my system sometimes produces malformed database, but I don't know what cause that. I'm trying to collect possible cases when database gets malformed. I'm going in this direction because it is not possible to debug system to reproduce condition. Because of that question is: can lack of journal file produce malformed database file? Yes (but not 100% of the time, as you seem to expect). See also http://sqlite.org/atomiccommit.html in particular section 2 Hardware Assumptions and 9 Things That Can Go Wrong Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
But, what happen if journal file is deleted before starting B process? Pozdrav, Darko F. Igor Tandetnik wrote: Robert Lehr [EMAIL PROTECTED] wrote: I have a question about recovering from a transaction that was not completed by a process b/c it terminated abnormally, e.g., careless SIGKILL or segfault. The scenario involves multiple processes having the database open. * process A opens the database * process B opens the database * process A starts a transaction * process A terminates abnormally BEFORE completing the transaction * process B starts a transaction the database is now in an indeterminate state. what happens in process B? http://sqlite.org/atomiccommit.html When B starts a transaction, it notices a hot rollback journal left behind by process A. It then uses this journal to undo (roll back) any changes process A may have made in the database file but haven't committed. The database is restored to the state it was in before process A started its transaction. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users __ NOD32 3153 (20080602) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Authorizer Feature Suggestion
Hi All,
For those of us that use SQLite mostly in-memory. Our context is mostly not
{sqlite3*} database pointer, it is {sqlite3_stmt*}.
Current API?
int sqlite3_set_authorizer(
sqlite3*,
int (*xAuth)(void*,int,const char*,const char*,const char*,const char*),
void *pUserData
);
Can we add the following API in the future?
int sqlite3_stmt_set_authorizer(
sqlite3_stmt*,
int (*xAuth)(void*,int,const char*,const char*,const char*,const char*),
void *pUserData
);
I have a user interface RPC for my application that configures and gets
status from my in-memory server database.
I like to be able to limit that activity. Any body else would like to see
this feature?
I hope Dr. Hipp reads this email.
Thanks,
-Alex
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
On 6/3/08, Christophe Leske [EMAIL PROTECTED] wrote: Hi, i am new to this list, Welcome. But you have hijacked an existing thread. That will decrease your chances of getting folks to reply to you positively. Tip: Start a new thread for a different query. can anyone point me to a good FAQ document on how to improve the speed of a SQLite database? I got a city database (a geographical database) that I need to query for lat/long values, and importance of the city (class value). For my smallest query, i am waiting several hundred milliseconds in a database that is about 40Mb in size and that has indices on latitude and longitude, as well as the class itself. I have indexed the database, analyzed it (in order to get the stats table), and vacuumed it. Any other hint on how one can speed up the queries? I ahve set PRAGME CACHE as well... You might do well to provide info on your exact schema as well as your exact query. Also, try the EXPLAIN command. Your query might not be using your indexes at all. Thanks in advance, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
That's why I continue to monitor SQLite messages even when I'm not actively developing with SQLite. There is an inherent entertainment value that appears built in. Over time I'm certain I have been guilty of posting some shall we say entertaining messages myself. Sometimes I think SQLite is so perfect a solution that many users just sit around bored and come up with some off the wall subject just for the pure potential entertainment value. I find most of the feature requests and subsequent whining both for and against most entertaining. Many users must work for US auto makers. When a US auto maker builds a successful small car the first thing they do is start making it bigger, to the point it loses all its original market share and subsequent value. Fred -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of P Kishor Sent: Tuesday, June 03, 2008 10:06 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] transaction recovery question On 6/3/08, Federico Granata [EMAIL PROTECTED] wrote: 2008/6/3 Darko Filipovic [EMAIL PROTECTED]: But, what happen if journal file is deleted before starting B process? what if a UFO stole your pc ? :-D try to delete journal file and see what happens ... my vote for the funniest message in a long time. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
Hi, i am new to this list, can anyone point me to a good FAQ document on how to improve the speed of a SQLite database? I got a city database (a geographical database) that I need to query for lat/long values, and importance of the city (class value). For my smallest query, i am waiting several hundred milliseconds in a database that is about 40Mb in size and that has indices on latitude and longitude, as well as the class itself. I have indexed the database, analyzed it (in order to get the stats table), and vacuumed it. Any other hint on how one can speed up the queries? I ahve set PRAGME CACHE as well... Thanks in advance, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
First - some sample code or queries would be helpful. Second - start a new topic (http://en.wikipedia.org/wiki/Thread_hijacking). Yes, sorry, my fault, i am a lazy bum these days. My apologies. This was also an indirect test if this list is still alive.. Will start a new thread right away, thanks, Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up my queries?
Hi Christophe, 1. Please give us an example query. SELECT * FROM Cities where LONGITUDE_DDS=? AND LATITUDE_DDS=? 2. Paste in the EXPLAIN results from the command line tool. 3. Is the database file local or are you accessing it over a network? RW Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Christophe Leske Sent: Tuesday, June 03, 2008 1:27 PM To: General Discussion of SQLite Database Subject: [sqlite] How to speed up my queries? Hi, i am a new member of this list and interested in speeding up my sqlite queries. I am using SQlite in a 3d environment which is close to Google Earth or Nasa WorldWind. We have a city database that is being queried regurlarly depending on the lat/long position of the viewport in order to show city names and labels. Plus, there are additional databases for special features, like natural hazards and catastrophies. The city database has around 840.000 records, the following schema and weights currently short under 40Mb: sqlite .schema cities CREATE TABLE Cities (NAME_DDS TEXT, CLASS_DDS NUMERIC, POPEST_DDS NUMERIC, LONGI TUDE_DDS NUMERIC, LATITUDE_DDS NUMERIC); CREATE INDEX class ON Cities(CLASS_DDS ASC); CREATE INDEX latlon on Cities(latitude_dds,longitude_dds); My questions are: - how do I speed up the queries? For small lat/long windows, and high classes for the cities, i get long query times (e.g. about 600ms) Is this reasonable to ask for, or IS that already a top speed for this kind of query? - I have indexed latitude AND longitude,as you can see above. Is this ok? - I came across the EXLPAIN command, and have read an email by someone on this list on how to analyze my queries. I should probably do that, yet i am unfamiliar with reading the output of the Explain command. Thanks for your time and eventual help, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
Welcome to the list! First - some sample code or queries would be helpful. Second - start a new topic (http://en.wikipedia.org/wiki/Thread_hijacking). RW Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Christophe Leske Sent: Tuesday, June 03, 2008 12:58 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] transaction recovery question Hi, i am new to this list, can anyone point me to a good FAQ document on how to improve the speed of a SQLite database? I got a city database (a geographical database) that I need to query for lat/long values, and importance of the city (class value). For my smallest query, i am waiting several hundred milliseconds in a database that is about 40Mb in size and that has indices on latitude and longitude, as well as the class itself. I have indexed the database, analyzed it (in order to get the stats table), and vacuumed it. Any other hint on how one can speed up the queries? I ahve set PRAGME CACHE as well... Thanks in advance, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to speed up my queries?
Hi, i am a new member of this list and interested in speeding up my sqlite queries. I am using SQlite in a 3d environment which is close to Google Earth or Nasa WorldWind. We have a city database that is being queried regurlarly depending on the lat/long position of the viewport in order to show city names and labels. Plus, there are additional databases for special features, like natural hazards and catastrophies. The city database has around 840.000 records, the following schema and weights currently short under 40Mb: sqlite .schema cities CREATE TABLE Cities (NAME_DDS TEXT, CLASS_DDS NUMERIC, POPEST_DDS NUMERIC, LONGI TUDE_DDS NUMERIC, LATITUDE_DDS NUMERIC); CREATE INDEX class ON Cities(CLASS_DDS ASC); CREATE INDEX latlon on Cities(latitude_dds,longitude_dds); My questions are: - how do I speed up the queries? For small lat/long windows, and high classes for the cities, i get long query times (e.g. about 600ms) Is this reasonable to ask for, or IS that already a top speed for this kind of query? - I have indexed latitude AND longitude,as you can see above. Is this ok? - I came across the EXLPAIN command, and have read an email by someone on this list on how to analyze my queries. I should probably do that, yet i am unfamiliar with reading the output of the Explain command. Thanks for your time and eventual help, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up my queries?
Wilson, Ron P schrieb: Hi Christophe, 1. Please give us an example query. SELECT * FROM Cities where LONGITUDE_DDS=? AND LATITUDE_DDS=? 2. Paste in the EXPLAIN results from the command line tool. 3. Is the database file local or are you accessing it over a network? Hi, the database file is local, right next to the app using it. I am using the sqlite3.exe command line tool for the queries, but would eventually like to ditch it for the native support. However, since the app I am using is a single threaded application (Adobe Director), eventual queries that take too long to complete do completely block the app which is why i have threaded the queries using a multi-threaded shell extension which does the queries, then reports back the results. A typical query that causes problems would be: SELECT * FROM Cities WHERE class_dds11 and (longitude_DDS BETWEEN 6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 44.424779) ORDER BY class_dds ASC Limit 20 Am i right that no matter what limit is given to the SQL statement, the complete query is executed first, AND THEN filtered according to the limit? This is what i think i a seeing here... I am therefore also after something that cuts off the query after a certain amount of results have been found. The explain results from the command line tool: sqlite EXPLAIN SELECT * FROM Cities WHERE class_dds11 and (longitude_DDS BETWE EN 6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 44.424779) ORD ER BY class_dds ASC Limit 20 ... ; addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 0 EXPLAIN SELECT * FROM Cities WHERE class_ dds11 and (longitude_DDS BETWEEN 6.765103 and 7.089129) AND (latitude_DDS BETWE EN 44.261771 and 44.424779) ORDER BY class_dds ASC Limit 20 ; 00 1 Noop 0 0 000 2 Integer201 000 3 MustBeInt 1 0 000 4 IfZero 1 42000 5 Integer112 000 6 Real 0 3 0 6.765103 00 7 Real 0 4 0 7.089129 00 8 Real 0 5 0 44.2617710001 00 9 Real 0 6 0 44.424779 00 10Goto 0 43000 11SetNumColumns 0 6 000 12OpenRead 0 3 000 13SetNumColumns 0 2 000 14OpenRead 2 6 0 keyinfo(1,BINARY) 00 15Rewind 2 408 0 00 16SCopy 2 8 000 17IsNull 8 40000 18Affinity 8 1 0 cb 00 19IdxGE 2 408 1 00 20Column 2 0 11 00 21IsNull 1139000 22IdxRowid 2 11000 23MoveGe 0 0 11 00 24Column 0 3 12 00 25Lt 3 3912collseq(BINARY) 6b 26Gt 4 3912collseq(BINARY) 6b 27Column 0 4 17 00 28Lt 5 3917collseq(BINARY) 6b 29Gt 6 3917collseq(BINARY) 6b 30Column 0 0 22 00 31Column 2 0 23 00 32Column 0 2 24 00 33Column 0 3 25 00 34Column 0 4 26 00 35Column 0 5 27 00 36ResultRow 226 000 37AddImm 1 -1000 38IfZero 1 40000 39Next 2 19000 40Close 0 0 000 41Close 2 0 000 42Halt 0 0 000 43Transaction0 0 000 44VerifyCookie 0 202 000 45TableLock 0 3 0 Cities 00 46Goto 0 11000 -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up my queries?
On Jun 3, 2008, at 7:27 PM, Christophe Leske wrote: i am a new member of this list and interested in speeding up my sqlite queries. There are no magic bullets, but The SQLite Query Optimizer Overview is a good read: http://www.sqlite.org/optoverview.html As well as Query Plans: http://www.sqlite.org/cvstrac/wiki?p=QueryPlans Once the basics are covered and you still need to speed up things, take a look at physically partitioning your data in several database files. This might reduce quite noticeably the overall processing time for a given query at the cost of a bit of added code complexity. -- PA. http://alt.textdrive.com/nanoki/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up my queries?
On Tue, Jun 03, 2008 at 07:56:11PM +0200, Christophe Leske scratched on the wall: A typical query that causes problems would be: SELECT * FROM Cities WHERE class_dds11 and (longitude_DDS BETWEEN 6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 44.424779) ORDER BY class_dds ASC Limit 20 Am i right that no matter what limit is given to the SQL statement, the complete query is executed first, AND THEN filtered according to the limit? This is what i think i a seeing here... The limit is applied *after* the ORDER so the system has no choice but to find every match for the WHERE statement, then ORDER it, then LIMIT it. In theory, the system could walk the index on class_dds to get the ORDER BY for free (and could then terminate the query as soon as the LIMIT is reached), but I can guess the nature of class_dss will prevent this. Basically if any one value is contained in 5 to 10% of the rows, an index won't be used and the system will do a full table-scan (this isn't unique to SQLite; nearly all DBs do this because it is faster in the general case). It also seems unlikely that the index will be of much use unless you're looking for specific values. An index can be used for a range, but not a double-range like you've got going here. This is part of the reason why many databases offer GIS extensions... the indexing problem for space is non-trivial. I'd try dropping the latlog index and just making one on lat. Put the lat and long conditions first, as they're going to give you the most filtering for the least cost. Try using dual GT/LT clauses rather than BETWEEN if the lat index still isn't used. I am therefore also after something that cuts off the query after a certain amount of results have been found. That isn't going to happen unless you can get rid of the ORDER *or* make the ORDER on something that is used as an index. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H 'People who live in bamboo houses should not throw pandas.' Jesus said that. - The Ninja, www.AskANinja.com, Special Delivery 10: Pop!Tech 2006 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Authorizer Feature Suggestion
Alex Katebi wrote:
Hi All,
For those of us that use SQLite mostly in-memory. Our context is mostly not
{sqlite3*} database pointer, it is {sqlite3_stmt*}.
Current API?
int sqlite3_set_authorizer(
sqlite3*,
int (*xAuth)(void*,int,const char*,const char*,const char*,const char*),
void *pUserData
);
Can we add the following API in the future?
int sqlite3_stmt_set_authorizer(
sqlite3_stmt*,
int (*xAuth)(void*,int,const char*,const char*,const char*,const char*),
void *pUserData
);
Why overload the API when you could simply use sqlite3_db_handle? Pass
it the your sqlite3_stmt* and you'll get the sqlite3 * to which your
prepared statement belongs. You could even (ab)use the preprocessor to
redefine the authorizer callback setter to always implicitely call
sqlite3_db_handle (I personally dislike preprocessor magic that violates
the principle of least astonishment, but you expect to be the sole
maintainer for the code it's very much OK.)
I have a user interface RPC for my application that configures and gets
status from my in-memory server database.
That sounds like you are using raw pointers as handles passing them to
remote consumers. If at all possible, I recommend you avoid this
practice - it's terrible from a security standpoint, and it's
questionable from a robustness standpoint. User mode pointers should be
considered valid only within their defined domain, i.e. the address
space of the process, and any type of pointers (including kernel
pointers) should be considered valid only on the local machine.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up my queries?
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. 19IdxGE 2 408 1 00 22IdxRowid 2 11000 LIMIT 20 should also limit the query to the first 20 matches; i.e. I don't think it is actually finding N results and filtering down to the first 20. At least I think that's what this means: 37AddImm 1 -1000 38IfZero 1 40000 RW Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Christophe Leske Sent: Tuesday, June 03, 2008 1:56 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] How to speed up my queries? Wilson, Ron P schrieb: Hi Christophe, 1. Please give us an example query. SELECT * FROM Cities where LONGITUDE_DDS=? AND LATITUDE_DDS=? 2. Paste in the EXPLAIN results from the command line tool. 3. Is the database file local or are you accessing it over a network? Hi, the database file is local, right next to the app using it. I am using the sqlite3.exe command line tool for the queries, but would eventually like to ditch it for the native support. However, since the app I am using is a single threaded application (Adobe Director), eventual queries that take too long to complete do completely block the app which is why i have threaded the queries using a multi-threaded shell extension which does the queries, then reports back the results. A typical query that causes problems would be: SELECT * FROM Cities WHERE class_dds11 and (longitude_DDS BETWEEN 6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 44.424779) ORDER BY class_dds ASC Limit 20 Am i right that no matter what limit is given to the SQL statement, the complete query is executed first, AND THEN filtered according to the limit? This is what i think i a seeing here... I am therefore also after something that cuts off the query after a certain amount of results have been found. The explain results from the command line tool: sqlite EXPLAIN SELECT * FROM Cities WHERE class_dds11 and (longitude_DDS BETWE EN 6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 44.424779) ORD ER BY class_dds ASC Limit 20 ... ; addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 0 EXPLAIN SELECT * FROM Cities WHERE class_ dds11 and (longitude_DDS BETWEEN 6.765103 and 7.089129) AND (latitude_DDS BETWE EN 44.261771 and 44.424779) ORDER BY class_dds ASC Limit 20 ; 00 1 Noop 0 0 000 2 Integer201 000 3 MustBeInt 1 0 000 4 IfZero 1 42000 5 Integer112 000 6 Real 0 3 0 6.765103 00 7 Real 0 4 0 7.089129 00 8 Real 0 5 0 44.2617710001 00 9 Real 0 6 0 44.424779 00 10Goto 0 43000 11SetNumColumns 0 6 000 12OpenRead 0 3 000 13SetNumColumns 0 2 000 14OpenRead 2 6 0 keyinfo(1,BINARY) 00 15Rewind 2 408 0 00 16SCopy 2 8 000 17IsNull 8 40000 18Affinity 8 1 0 cb 00 19IdxGE 2 408 1 00 20Column 2 0 11 00 21IsNull 1139000 22IdxRowid 2 11000 23MoveGe 0 0 11 00 24Column 0 3 12 00 25Lt 3 3912collseq(BINARY) 6b 26Gt 4 3912collseq(BINARY) 6b 27Column 0 4 17 00 28Lt 5 3917collseq(BINARY) 6b 29Gt 6 3917collseq(BINARY) 6b 30Column 0 0 22 00 31Column 2 0 23 00 32Column 0 2 24 00 33Column 0 3 25 00 34Column 0 4 26 00 35Column 0 5 27 00 36ResultRow 226 000 37AddImm 1 -10
Re: [sqlite] How to speed up my queries?
PS. Also, I am not sure about the BETWEEN command - does it use indices? If not you could write the query without BETWEEN. RW Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Christophe Leske Sent: Tuesday, June 03, 2008 1:56 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] How to speed up my queries? Wilson, Ron P schrieb: Hi Christophe, 1. Please give us an example query. SELECT * FROM Cities where LONGITUDE_DDS=? AND LATITUDE_DDS=? 2. Paste in the EXPLAIN results from the command line tool. 3. Is the database file local or are you accessing it over a network? Hi, the database file is local, right next to the app using it. I am using the sqlite3.exe command line tool for the queries, but would eventually like to ditch it for the native support. However, since the app I am using is a single threaded application (Adobe Director), eventual queries that take too long to complete do completely block the app which is why i have threaded the queries using a multi-threaded shell extension which does the queries, then reports back the results. A typical query that causes problems would be: SELECT * FROM Cities WHERE class_dds11 and (longitude_DDS BETWEEN 6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 44.424779) ORDER BY class_dds ASC Limit 20 Am i right that no matter what limit is given to the SQL statement, the complete query is executed first, AND THEN filtered according to the limit? This is what i think i a seeing here... I am therefore also after something that cuts off the query after a certain amount of results have been found. The explain results from the command line tool: sqlite EXPLAIN SELECT * FROM Cities WHERE class_dds11 and (longitude_DDS BETWE EN 6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 44.424779) ORD ER BY class_dds ASC Limit 20 ... ; addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 0 EXPLAIN SELECT * FROM Cities WHERE class_ dds11 and (longitude_DDS BETWEEN 6.765103 and 7.089129) AND (latitude_DDS BETWE EN 44.261771 and 44.424779) ORDER BY class_dds ASC Limit 20 ; 00 1 Noop 0 0 000 2 Integer201 000 3 MustBeInt 1 0 000 4 IfZero 1 42000 5 Integer112 000 6 Real 0 3 0 6.765103 00 7 Real 0 4 0 7.089129 00 8 Real 0 5 0 44.2617710001 00 9 Real 0 6 0 44.424779 00 10Goto 0 43000 11SetNumColumns 0 6 000 12OpenRead 0 3 000 13SetNumColumns 0 2 000 14OpenRead 2 6 0 keyinfo(1,BINARY) 00 15Rewind 2 408 0 00 16SCopy 2 8 000 17IsNull 8 40000 18Affinity 8 1 0 cb 00 19IdxGE 2 408 1 00 20Column 2 0 11 00 21IsNull 1139000 22IdxRowid 2 11000 23MoveGe 0 0 11 00 24Column 0 3 12 00 25Lt 3 3912collseq(BINARY) 6b 26Gt 4 3912collseq(BINARY) 6b 27Column 0 4 17 00 28Lt 5 3917collseq(BINARY) 6b 29Gt 6 3917collseq(BINARY) 6b 30Column 0 0 22 00 31Column 2 0 23 00 32Column 0 2 24 00 33Column 0 3 25 00 34Column 0 4 26 00 35Column 0 5 27 00 36ResultRow 226 000 37AddImm 1 -1000 38IfZero 1 40000 39Next 2 19000 40Close 0 0 000 41Close 2 0 000 42Halt 0 0 000 43Transaction0 0 000 44VerifyCookie 0 202 000 45TableLock 0 3 0 Cities 00 46Goto 0 11000 --
Re: [sqlite] sqlite-users Digest, Vol 6, Issue 6
Gentlemen, Thank you for your replies. The definitive answers are helpful. However, there seems to be some confusion around the question. RE: simple enough to test and ...see what happens... empirical analysis of basic reliability issues is insufficient when one is building an application for which one will be accountable to end-users that are relying on the application to do valuable work. I need to KNOW what SQLite does so that I can design around that behaviour, not what I observe that it does. RE: Doctor, it hurts... this seems to imply that the question does not warrant analysis. that is incorrect. such scenarios can occur. if I were to ask the question, I would have been seeking to understand how SQLite copes with it so so that the application in question can be written to that specification. Whether or not another DB SW can recover from it is uninteresting, particularly in that context. that being the case, I suspect that SQLite will return an SQLITE_IOERROR (something from the SQLITE_IOERROR* family). -rlehr Robert Lehr ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 6, Issue 6
Robert Lehr [EMAIL PROTECTED] wrote: RE: Doctor, it hurts... this seems to imply that the question does not warrant analysis. that is incorrect. such scenarios can occur. if I were to ask the question, I would have been seeking to understand how SQLite copes with it You don't quote any context, but I'll guess you are talking about a situation where a) a process crashes in the middle of a transaction, and b) somehow the rollback journal file gets deleted. SQLite does not cope with this situation. The database is corrupted. SQLite may or may not be able to detect this corruption: if you are lucky, you'll get an error when opening the database; otherwise, it'll open successfully and you will work with inconsistent data. So, don't delete the journal file (except when also deleting the database file). Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] transaction locks w/ multiple DBs open/ATTACHed
This mailing list is amazingly responsive. That, among SQLite's features, makes it very easy use SQLite in our application. Thank you. My current question focuses on SQLite's locking policy for transactions on connections which have multiple DBs are open/ATTACHed. Specifically, does SQLite lock the DBs incrementally, as they are accessed within the transaction? Or does it lock ALL DBs? If a transaction accesses only ONE DB, will all DBs be locked for that transaction? what locking sequence does SQLite execute? If a transaction reads from multiple DBs but WRITES to only one DB, what locking sequence does SQLite execute? -rlehr Robert Lehr ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bind arguments for insert and not null columns with default values
On Tue, May 20, 2008 at 2:56 PM, D. Richard Hipp [EMAIL PROTECTED] wrote: OK. How about: INSERT INTO foo(bar) VALUES(coalesce(?,'default-value')); This approach is working well for us, but as Dennis pointed out it won't work for all situations. I wonder if it's worth adding something like sqlite3_bind_default_value() to explicitly bind the default value, or NULL if no default is specified? -Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Authorizer Feature Suggestion
Hi Mihia,
Since I am using in-memory database I only have one connection. I don't
want the limiting factors to limit all stmt, only some.
Regarding pointers used in a remote process. There is no harm if one
is aware of the pointer belonging to a remote process. A handle can be
anything as long as it is unique.
Thanks,
-Alex
On Tue, Jun 3, 2008 at 2:23 PM, Mihai Limbasan [EMAIL PROTECTED] wrote:
Alex Katebi wrote:
Hi All,
For those of us that use SQLite mostly in-memory. Our context is mostly
not
{sqlite3*} database pointer, it is {sqlite3_stmt*}.
Current API?
int sqlite3_set_authorizer(
sqlite3*,
int (*xAuth)(void*,int,const char*,const char*,const char*,const char*),
void *pUserData
);
Can we add the following API in the future?
int sqlite3_stmt_set_authorizer(
sqlite3_stmt*,
int (*xAuth)(void*,int,const char*,const char*,const char*,const char*),
void *pUserData
);
Why overload the API when you could simply use sqlite3_db_handle? Pass it
the your sqlite3_stmt* and you'll get the sqlite3 * to which your prepared
statement belongs. You could even (ab)use the preprocessor to redefine the
authorizer callback setter to always implicitely call sqlite3_db_handle (I
personally dislike preprocessor magic that violates the principle of least
astonishment, but you expect to be the sole maintainer for the code it's
very much OK.)
I have a user interface RPC for my application that configures and gets
status from my in-memory server database.
That sounds like you are using raw pointers as handles passing them to
remote consumers. If at all possible, I recommend you avoid this practice -
it's terrible from a security standpoint, and it's questionable from a
robustness standpoint. User mode pointers should be considered valid only
within their defined domain, i.e. the address space of the process, and any
type of pointers (including kernel pointers) should be considered valid only
on the local machine.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bind arguments for insert and not null columns with default values
On Tue, Jun 03, 2008 at 02:51:57PM -0500, Jeff Hamilton scratched on the wall: On Tue, May 20, 2008 at 2:56 PM, D. Richard Hipp [EMAIL PROTECTED] wrote: OK. How about: INSERT INTO foo(bar) VALUES(coalesce(?,'default-value')); This approach is working well for us, but as Dennis pointed out it won't work for all situations. I wonder if it's worth adding something like sqlite3_bind_default_value() to explicitly bind the default value, or NULL if no default is specified? That, or something like sqlite3_clear_bindings() that actually *clears* the bindings (e.g. whatever state they are in just after a prepare), and not just sets them to an explicit NULL, as the current function does. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H 'People who live in bamboo houses should not throw pandas.' Jesus said that. - The Ninja, www.AskANinja.com, Special Delivery 10: Pop!Tech 2006 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bind arguments for insert and not null columns with default values
Jay A. Kreibich wrote: That, or something like sqlite3_clear_bindings() that actually *clears* the bindings (e.g. whatever state they are in just after a prepare), and not just sets them to an explicit NULL, as the current function does. Jay, That wouldn't help. The default value is *only* used when no value is provided by the insert statement. If the insert statement has a parameter that can be bound, it is supplying a non-default value, and that value will be used instead of the default value. You really do need different statements, with and without a value for the column that is to get the default value. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up my queries?
On Tue, Jun 3, 2008 at 1:27 PM, Christophe Leske [EMAIL PROTECTED] wrote: Hi, i am a new member of this list and interested in speeding up my sqlite queries. I am using SQlite in a 3d environment which is close to Google Earth or Nasa WorldWind. We have a city database that is being queried regurlarly depending on the lat/long position of the viewport in order to show city names and labels. rest snipped From what I gather, you've got something very similar to the 2D-spatial problem: I need to find cities that are within R miles of (X,Y). This translates as I need to find objects that are within the circle with origin (X, Y) and radius=R. This is fundamentally a collision-detection algorithm, and I have a suggestion that might help, based on the way old DOS game Doom optimized its collision detection code. Here's the setup: You divide the world into equally-sized blocks of width W and height H. Let's say, for the sake of argument, that W=1' and H=1' (this is huge, but it helps illustrate the point) Since the world is 180 degrees north-to-south and 360 degrees around the equator, this gives 64,800 blocks. So, for example: CREATE TABLE blockmap (id integer not null primary key, lat real, long real); Then you need to build a correspondence table: CREATE TABLE blockmapCity(blockmapId, cityId); A naive implementation might only mark a city's center, while a more advanced version might get fancy and have an approximate size of the city and place it in multiple blockmaps, in case it was big enough to spill over into adjacent blocks. What you do then, in order to do a lookup, is to find all of the blocks that intersect with your circle. This can be done easily with the right math. Then, once you've figured out which blocks to include, you just filter out the relevant cities from blockmapCity. Once you have *those* cities you can filter them out as precisely as you were doing before. Some notes: -- Even if you only go down to 1'-by-1' granularity, you've divided the world into 64,800 blocks. Assuming that your 840K cities are all over the globe, and that about 70% of Earth is covered by water, that means that only about 20,000 blocks would actually have cities in them. But with 840K cities, that means you're only considering about 42 cities for a single block. -- The algorithm used to prune down the set of blocks to include doesn't need to be perfect. Remember, this is all an optimization; even if you return every blockmap in the same *hemisphere*, you'd still be searching through only 420K cities instead of 840K! If you need any more help implementing something like this, go ahead and reply to the list. If you can provide a concrete set of data (for example, all or most or at least a significant number of the cities in the US) I can help put together a more concrete example. Plus, there are additional databases for special features, like natural hazards and catastrophies. The city database has around 840.000 records, the following schema and weights currently short under 40Mb: sqlite .schema cities CREATE TABLE Cities (NAME_DDS TEXT, CLASS_DDS NUMERIC, POPEST_DDS NUMERIC, LONGI TUDE_DDS NUMERIC, LATITUDE_DDS NUMERIC); CREATE INDEX class ON Cities(CLASS_DDS ASC); CREATE INDEX latlon on Cities(latitude_dds,longitude_dds); My questions are: - how do I speed up the queries? For small lat/long windows, and high classes for the cities, i get long query times (e.g. about 600ms) Is this reasonable to ask for, or IS that already a top speed for this kind of query? - I have indexed latitude AND longitude,as you can see above. Is this ok? - I came across the EXLPAIN command, and have read an email by someone on this list on how to analyze my queries. I should probably do that, yet i am unfamiliar with reading the output of the Explain command. Thanks for your time and eventual help, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bind arguments for insert and not null columns with default values
It would be nice to be able to revert back to the default value for a column. I don't think SQLite support this right now. The closest thing I found is pragma table_info(foo). If you prepare this and then grab the dflt_value for your column. On Tue, May 20, 2008 at 2:33 PM, Jeff Hamilton [EMAIL PROTECTED] wrote: Hi all, I have a table like this CREATE TABLE foo (bar TEXT NOT NULL DEFAULT 'default_value'); and I'd like to create a reusable statement to do inserts into foo, like this: INSERT INTO foo (bar) VALUES (?); Sometimes I have values for bar and sometimes I don't and want the default. Is there any way to indicate to the statement that I want the bound parameter to be nothing therefore giving me the default value? If I bind that column to NULL I get a constraint error. -Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bind arguments for insert and not null columns with default values
On Tue, Jun 3, 2008 at 5:09 PM, Alex Katebi [EMAIL PROTECTED] wrote: It would be nice to be able to revert back to the default value for a column. I don't think SQLite support this right now. The closest thing I found is pragma table_info(foo). If you prepare this and then grab the dflt_value for your column. On Tue, May 20, 2008 at 2:33 PM, Jeff Hamilton [EMAIL PROTECTED] wrote: I've been following this discussion and here's my $0.02: there are three ways I can imagine going with this. 1. Extend the API with a sqlite_bind_default function, and then modify the VDBE to inject instructions that are the equivalent of if (value==usedefault) value=default into INSERT statements 2. Extend the API with a sqlite_bind_default function, which somehow does this 'pragma table-Info(foo)' and binds *that* value. 3. Don't support it. If people want to allow default values they have to prepare multiple versions of INSERT statements. #2 wouldn't actually work 100% without being insanely complicated; consider columns with a default value derived from CURRENT_TIMESTAMP. This leaves #1 and #3. #1 is appealing because it enables certain scenarios a *lot* simpler to maintain, but it also means that *every single application* has to pay the penalty, in memory and extra CPU cycles, to allow them to specify defaults, even if they have absolutely no intention of doing so. There's something about that on the SQLite site, but I can't find it... it boils down to If we implement this feature and document it, DRH has to support it *forever after*. I offer you an option #4 that came to me while I was writing this email: CREATE TABLE Foo(fooName text, fooDate date default(current_timestamp), fooLevel integer default(42)); INSERT INTO Foo (fooName, fooDate, fooLevel) values (?, ifnull(?, current_timestamp), ifnull(?, 42)); If you want to get really fancy you can construct the above statement using Alex's suggested pragma table_info(Foo) in order to find out what the defaults are when you're preparing the query. -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bind arguments for insert and not null columns with default values
On Tue, Jun 03, 2008 at 02:27:01PM -0600, Dennis Cote scratched on the wall: Jay A. Kreibich wrote: That, or something like sqlite3_clear_bindings() that actually *clears* the bindings (e.g. whatever state they are in just after a prepare), and not just sets them to an explicit NULL, as the current function does. That wouldn't help. The default value is *only* used when no value is provided by the insert statement. If the insert statement has a parameter that can be bound, it is supplying a non-default value, and that value will be used instead of the default value. You really do need different statements, with and without a value for the column that is to get the default value. *ding* [Light bulb does on] Ahhh... yes, that makes tons of sense on a number of different levels. For some reason I was under the impression that statements that were prepared but didn't bind anything had their default parameters used. I now understand that this assumption is incorrect, and from that the rest makes sense. Jeff's suggestion is interesting, but kind of assumes there is a clear one-to-one corrispondence between bind params and specific columns. I can see a lot of cases when this might not be true, even for INSERT or UPDATE statements. For those trying to deal with this problem, it is worth pointing out that you can extract the default value for any column of any table with the PRAGMA table_info(table) command. You can then incorporate that into your application logic and explicitly bind the default value should you find yourself valueless. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H 'People who live in bamboo houses should not throw pandas.' Jesus said that. - The Ninja, www.AskANinja.com, Special Delivery 10: Pop!Tech 2006 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up my queries?
-- Even if you only go down to 1'-by-1' granularity, you've divided the world into 64,800 blocks. Assuming that your 840K cities are all over the globe, and that about 70% of Earth is covered by water, that means that only about 20,000 blocks would actually have cities in them. But with 840K cities, that means you're only considering about 42 cities for a single block. Nice! Though I haven't been part of this thread, I wanted to comment that this is a very elegant efficient solution. Kind of like a 2-D hash table. I can see other areas where a similar approach would be helpful. Thanks for a good tip-- I'm sure it will come in handy at some point. ~Eric ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multitable query question
I read the replies in this thread on the sqlite mailing list. It's true that the data in the two tables are not really related in the relational sense, where one table contains a foreign key, which is a key in another table. This implies a one to many relationship -- for example, many calls relating to a given letter and many letters relating to a given house. When the same foreign key (houseid) occurs in both tables, the best you can hope for is to join all the values in eacj row of one table with all the values each row of the other which share the same foreign key. I thought about the problem of displaying unrelated data side by side in sql queries. But first a quick and dirty solution, if all you want is a reference. This also shows how some sorts of formatting can be done directly in a query. The data appears in it's own column, but not side by side. Also, using the standard date data type is really the *best* way to store dates. .separator .mode list select House 16: Letters Calls; select --; select , date from letters where houseid = 16; select, date from calls where houseid = 16; To create a side by side report in SQLite, you would need to create a relationship between the columns you want to display. This can be done by creating temporary tables with auto-incrementing primary keys. Select the unrelated data you want to display into these tables, as well as a number of blank rows into each table so there can be columns of different lengths. Now, you have a relationship between the data based on the auto-incrementing key (id, for instance). Do a select from columns in these tables where table1.id = table2.id and table1.id = table3.id etc. limit 40 (or whatever is the maximum length of your report. If somebody has found another way to do what you want, at least this technique can be used when the data is completely unrelated and you want a report which can be done completely in SQLite. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up my queries?
On Jun 3, 2008, at 10:27 AM, Christophe Leske wrote: We have a city database that is being queried regurlarly depending on the lat/long position of the viewport in order to show city names and labels. SQLite has an optional R-Tree engine. The R-Tree is a new addition and has not appeared in any release of SQLite, but it is available via CVS. There is little documentation other than a README file in the source directory. Nevertheless, an R-Tree is specifically designed to solve the kind of query you describe above. If you are willing to work on the bleeding edge, you might want to investigate SQLite's R- Tree capabilities. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up my queries?
On 6/3/08, D. Richard Hipp [EMAIL PROTECTED] wrote: On Jun 3, 2008, at 10:27 AM, Christophe Leske wrote: We have a city database that is being queried regurlarly depending on the lat/long position of the viewport in order to show city names and labels. SQLite has an optional R-Tree engine. The R-Tree is a new addition and has not appeared in any release of SQLite, but it is available via CVS. There is little documentation other than a README file in the source directory. Nevertheless, an R-Tree is specifically designed to solve the kind of query you describe above. If you are willing to work on the bleeding edge, you might want to investigate SQLite's R- Tree capabilities. Richard, I am very interested in this. A few of us GIS-types have been tinkering with this (http://sqlitegis.org), but working off what you have been doing would likely be a lot better. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction recovery question
On Jun 3, 2008, at 10:03 PM, Darko Filipovic wrote: I've tried...(not with UFO :D ). Nothing happens, database is not corrupted and that is what confuses me...I thought it should not be readable (malformed) ?! When SQLite needs to modify the content of a database page, it does two things: * writes the contents of that page out to the journal (so that it can be rolled back later if necessary), and * makes the change to an in-memory copy of the page. Later on, when the transaction is committed or enough changes have accumulated in memory, all pending changes are flushed through to the file. You probably abandoned the transaction to early for this to happen - so the journal file was in the file-system, but no actual changes had been made to the database file. Try it with a really big transaction and you will see the corruption. Dan. Greetings, Darko F. Federico Granata wrote: 2008/6/3 Darko Filipovic [EMAIL PROTECTED]: But, what happen if journal file is deleted before starting B process? what if a UFO stole your pc ? :-D try to delete journal file and see what happens ... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users __ NOD32 3154 (20080603) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
