Re: [sqlite] Help speed up query
On Tue, Apr 16, 2013 at 8:52 PM, ven...@intouchmi.com wrote: Simon and everyone who has thrown ideas into the mix, I very much appreciate the effort that you folks have put into this! If nothing else, I am learning from this exorcise. At this point, sounds like the only way to possibly make forward progress on this would be to post your DB somewhere, for hands-on testing by anyone. The fact you haven't done so suggests you cannot do that, but perhaps if you could somehow anonymize the data, like dropping all selected columns that do not participate in the where and join clauses, and dropping irrelevant tables, then maybe you could share something where the query continues to be slower than MS Access, that someone could explain or improve on somehow. Just my $0.02. --DD PS: I would also allow to load that same data into another RDBMS and see how it performs there too. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RTree Documentation Error?
On Tue, Apr 16, 2013 at 1:15 PM, Mohit Sindhwani m...@onghu.com wrote: Hi, I was looking at the RTree documentation page with one of my colleagues - http://www.sqlite.org/rtree.**htmlhttp://www.sqlite.org/rtree.html We tried the example with the schema in 3.1, populated the data from 3.2 and then queried it using the query of 3.3 - we got no results while the documentation says that the query would very quickly locate the id of 1 even if the R*Tree contained millions of entries. Is the line AND minY=35.00 AND maxY=35.44; supposed to be AND minY=33.00 AND maxY=35.44; (33 instead of 35.00)? I copy/pasted the code and it all seems to work for me. My copy/paste follows: CREATE VIRTUAL TABLE demo_index USING rtree( id, -- Integer primary key minX, maxX, -- Minimum and maximum X coordinate minY, maxY -- Minimum and maximum Y coordinate ); INSERT INTO demo_index VALUES( 1, -- Primary key -80.7749, -80.7747, -- Longitude range 33.3776, 35.3778 -- Latitude range ); INSERT INTO demo_index VALUES( 2, -81.0, -79.6, 35.0, 36.2 ); .print --- one SELECT * FROM demo_index WHERE id=1; SELECT id FROM demo_index WHERE minX=-81.08 AND maxX=-80.58 AND minY=35.00 AND maxY=35.44; .print --- two SELECT id FROM demo_index WHERE maxX=-81.08 AND minX=-80.58 AND maxY=35.00 AND minY=35.44; .print --- three SELECT id FROM demo_index WHERE maxY=35.0 AND minY=35.0; -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RTree Documentation Error?
Richard Hipp wrote: On Tue, Apr 16, 2013 at 1:15 PM, Mohit Sindhwani m...@onghu.com wrote: Hi, I was looking at the RTree documentation page with one of my colleagues - http://www.sqlite.org/rtree.html We tried the example with the schema in 3.1, populated the data from 3.2 and then queried it using the query of 3.3 - we got no results while the documentation says that the query would very quickly locate the id of 1 even if the R*Tree contained millions of entries. Is the line AND minY=35.00 AND maxY=35.44; supposed to be AND minY=33.00 AND maxY=35.44; (33 instead of 35.00)? I copy/pasted the code and it all seems to work for me. My copy/paste follows: [...] .print --- one SELECT * FROM demo_index WHERE id=1; SELECT id FROM demo_index WHERE minX=-81.08 AND maxX=-80.58 AND minY=35.00 AND maxY=35.44; These are two queries. The second one outputs nothing, although its description implies it should. .print --- two [...] Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RTree Documentation Error?
On Wed, Apr 17, 2013 at 7:48 AM, Clemens Ladisch clem...@ladisch.de wrote: Richard Hipp wrote: On Tue, Apr 16, 2013 at 1:15 PM, Mohit Sindhwani m...@onghu.com wrote: Hi, I was looking at the RTree documentation page with one of my colleagues - http://www.sqlite.org/rtree.html We tried the example with the schema in 3.1, populated the data from 3.2 and then queried it using the query of 3.3 - we got no results while the documentation says that the query would very quickly locate the id of 1 even if the R*Tree contained millions of entries. Is the line AND minY=35.00 AND maxY=35.44; supposed to be AND minY=33.00 AND maxY=35.44; (33 instead of 35.00)? I copy/pasted the code and it all seems to work for me. My copy/paste follows: [...] .print --- one SELECT * FROM demo_index WHERE id=1; SELECT id FROM demo_index WHERE minX=-81.08 AND maxX=-80.58 AND minY=35.00 AND maxY=35.44; These are two queries. The second one outputs nothing, although its description implies it should. I get: drh@tallis:~/sqlite/bld$ ./sqlite3 r1.txt --- one 1|-80.77490234375|-80.7746963500977|33.3775978088379|35.3778038024902 --- two 1 2 --- three 1 2 .print --- two [...] Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug report: small bug in shell.c
I have found that the following command in my opinion returns an incorrect exit code: eric@sirius:~/src/sqlite-amalgamation-3071602$ ./sqlite3 appl.db .quit eric@sirius:~/src/sqlite-amalgamation-3071602$ echo $? 2 This should (in my opinion) return 0 for success instead of 2. In interactive mode the correct value is returned: eric@sirius:~/src/sqlite-amalgamation-3071602$ ./sqlite3 appl.db SQLite version 3.7.16.2 2013-04-12 11:52:43 Enter .help for instructions Enter SQL statements terminated with a ; sqlite .quit eric@sirius:~/src/sqlite-amalgamation-3071602$ echo $? 0 The cause is that do_meta_command() returns 2 if an exit is requested. In non-interactive mode this value is not filtered and passed directly back to the shell. I do not know if this is intentional or not. If so, please ignore this message. Below is a patch which will fix the issue and have the shell return 0 even if an exit is requested non-interactively. The fix is rather crude, it will set rc to 0 if do_meta_command() returned 2. As far as I can tell the return code 2 is used exclusively for exit/quit requests so this should be safe to do. === --- shell.c.org 2013-04-12 14:21:39.0 +0200 +++ shell.c 2013-04-16 20:04:12.0 +0200 @@ -3128,6 +3128,7 @@ z = cmdline_option_value(argc,argv,++i); if( z[0]=='.' ){ rc = do_meta_command(z, data); +rc = rc == 2 ? 0 : rc; if( rc bail_on_error ) return rc; }else{ open_db(data); @@ -3152,6 +3153,7 @@ */ if( zFirstCmd[0]=='.' ){ rc = do_meta_command(zFirstCmd, data); + rc = rc == 2 ? 0 : rc; }else{ open_db(data); rc = shell_exec(data.db, zFirstCmd, shell_callback, data, zErrMsg); === Of course the above snippet is hereby donated to the Public Domain. Thanks for all the hard work, I appreciate it immensely. Regards, Eric. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Beginning database question
This is a tough one to answer because there is so much context to consider. SQLite, or any database, could easily solve the problem you mention (storing values that can change without needing to recompile your program). Whether it's more efficient is another question. If you think you'll have many more than 10 entries (likes thousands or millions), or the 10 entries change very often (once a minute??) and you could automate entering them, a simple app using a database like SQLite would be a decent way to go. Does this need to get put on a website? Is the app going to be mailed around? (in which case a spreadsheet might be just as easy). If you'd like to use this as an excuse to learn more about databases, this project would be a perfect opportunity. It's surprising how often databases, especially a small and simple one like SQLite come in handy. Compared to using Excel: coding efficiency - no coding needed to use Excel, so Excel wins processing speed - your app would almost certainly start up and run quicker than Excel memory usage - your app would definitely use less memory than Excel, but does this really matter with only 10 entries? That's my best shot. Doug -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Carl Gross Sent: Tuesday, April 16, 2013 3:28 AM To: sqlite-users@sqlite.org Subject: [sqlite] Beginning database question Hi All, I'm an amateur programmer who has never worked with databases before. I *think* that getting started with SQLite may help with some of my projects, but I'm having trouble determining exactly how. I'm hoping someone in this list may be able to point me in the right direction. My simplified situation is this. There are 10 discrete heights' and 10 discrete weights,' and each height/weight combination corresponds to one of two 'teams.' All of this information is hardcoded into my program. My program asks a user to enter his own height and weight, and the program will output the user's corresponding team based on the hardcoded data. My SQLite question is: Can using SQLite instead of Microsoft Excel be beneficial in terms of coding efficiency, processing speed, memory usage, or any other aspect? If so, how? Sorry for the long, broad, vague question. I'm not looking for someone to tell me how to do something. I'm looking for someone to assure me that SQLite (rather than a simple spreadsheet) is right for me, and to hopefully steer me towards some documentation that may be beneficial to me. Thanks, Carl ___ 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 Cross Compile SQLite3 inside the SBC
May I know the step of cross compile the sqlite inside the single board computer(SBC-TS5500)?I have done my code on the terminal.Now I need the guide on cross compile and then to test my sqlite code in the sbc environment. -- View this message in context: http://sqlite.1065341.n5.nabble.com/How-to-Cross-Compile-SQLite3-inside-the-SBC-tp68258.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 or sqlite4 for a new project?
Dear list, The subject pretty much says it all. I want to use sqlite in a small software I am writing, and I would like to know what are the reason of using sqlite3 or 4. The newer version seems quite an improvement, but as far as I can tell it not very used yet. What is the reason? It is not stable? Simply too many project continues with the version they alway used? Any insight is welcome, Paolo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 or sqlite4 for a new project?
On Wed, Apr 17, 2013 at 8:30 AM, Paolo Bolzoni paolo.bolzoni.br...@gmail.com wrote: Dear list, The subject pretty much says it all. I want to use sqlite in a small software I am writing, and I would like to know what are the reason of using sqlite3 or 4. sqlite4 is still experimental. Use sqlite3. The newer version seems quite an improvement, but as far as I can tell it not very used yet. What is the reason? It is not stable? Simply too many project continues with the version they alway used? Any insight is welcome, Paolo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RTree Documentation Error?
I think the point is that this query that produces nothing: SELECT id FROM demo_index WHERE minX=-81.08 AND maxX=-80.58 AND minY=35.00 AND maxY=35.44; Should produce something as the directions imply. It's just that SQL HQ is not actually inside the city limits apparently (mailing addresses are not restricted to city boundaries). So add another data point of something downtown. Like the Carolina Panthers stadium -80.854201,-80.851489, 35.224511, 35.226925 sqlite insert into demo_index values(3,-80.854201,-80.851489,35.224511,35.226925); sqlite SELECT id FROM demo_index WHERE minX=-81.08 AND maxX=-80.58 AND minY=35.00 AND maxY=35.44; id 3 Michael Black ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RTree Documentation Error?
On Wed, Apr 17, 2013 at 8:41 AM, Michael Black mdblac...@yahoo.com wrote: I think the point is that this query that produces nothing: SELECT id FROM demo_index WHERE minX=-81.08 AND maxX=-80.58 AND minY=35.00 AND maxY=35.44; Should produce something as the directions imply. And my point is that the query DOES produce a result. Are y'all saying you are getting an empty result for the second query? What version of SQLite are you running? What platform? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RTree Documentation Error?
Correct -- no results for the real 2nd query ...you got no results for it either.\ Then 2nd query is this and produces no results...nor should it with the data given. SELECT id FROM demo_index WHERE minX=-81.08 AND maxX=-80.58 AND minY=35.00 AND maxY=35.44; I think the data point you have for SQL HQ is supposed to be: 1|-80.77490234375|-80.7746963500977|35.3775978088379|35.3778038024902 You have 33 for miny which puts HQ outside of the query since the query doesn't do intersections. Change 33 to 35 and the query will work. I don't think HQ is really 2 degrees N/S :-) SQLite version 3.7.16.2 2013-04-12 11:52:43 Enter .help for instructions Enter SQL statements terminated with a ; sqlite CREATE VIRTUAL TABLE demo_index USING rtree( ...id, -- Integer primary key ...minX, maxX, -- Minimum and maximum X coordinate ...minY, maxY -- Minimum and maximum Y coordinate ... ); sqlite INSERT INTO demo_index VALUES( ... 1, -- Primary key ... -80.7749, -80.7747, -- Longitude range ... 33.3776, 35.3778 -- Latitude range ... ); sqlite INSERT INTO demo_index VALUES( ... 2, ... -81.0, -79.6, ... 35.0, 36.2 ... ); sqlite SELECT * FROM demo_index WHERE id=1; 1|-80.77490234375|-80.7746963500977|33.3775978088379|35.3778038024902 sqlite SELECT id FROM demo_index ... WHERE minX=-81.08 AND maxX=-80.58 ...AND minY=35.00 AND maxY=35.44; Note that there's no output here And there shouldn't beminY is 33.377 for ID#1 So let's put in some correct numbers sqlite delete from demo_index where id=1; sqlite INSERT INTO demo_index VALUES( ... 1, -- Primary key ... -80.7749, -80.7747, -- Longitude range ... 35.3776, 35.3778 -- Latitude range ... ); sqlite SELECT id FROM demo_index ... WHERE minX=-81.08 AND maxX=-80.58 ...AND minY=35.00 AND maxY=35.44; 1 sqlite SELECT id FROM demo_index ... WHERE maxY=35.0 AND minY=35.0; 2 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Wednesday, April 17, 2013 8:07 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] RTree Documentation Error? On Wed, Apr 17, 2013 at 8:41 AM, Michael Black mdblac...@yahoo.com wrote: I think the point is that this query that produces nothing: SELECT id FROM demo_index WHERE minX=-81.08 AND maxX=-80.58 AND minY=35.00 AND maxY=35.44; Should produce something as the directions imply. And my point is that the query DOES produce a result. Are y'all saying you are getting an empty result for the second query? What version of SQLite are you running? What platform? -- D. Richard Hipp d...@sqlite.org ___ 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] RTree Documentation Error?
On Wed, Apr 17, 2013 at 9:25 AM, Michael Black mdblac...@yahoo.com wrote: Correct -- no results for the real 2nd query I see. I ran two queries together in my script so what I thought was the 2nd query was really the 3rd. The SQLite HQ coordinates are fixed in http://www.sqlite.org/draft/rtree.html now. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is there a command to lock a database for a short while (0.25 sec, say)
Hi everyone Can my application lock the database for its own exclusive use at the beginning of a series of INSERTs UPDATEs and SELECTs, then unlock it again afterwards? I've read about the five locking states (unlocked, shared, etc.) but I assume the dll handles all that at INSERT or UPDATE time. I'm looking for a way to ensure other users aren't allowed to write to the database during this quarter-second period. Thanks in advance. Jonathan Trahair ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a command to lock a database for a short while (0.25 sec, say)
On 17 April 2013 14:39, J Trahair j.trah...@foreversoftware.co.uk wrote: Hi everyone Can my application lock the database for its own exclusive use at the beginning of a series of INSERTs UPDATEs and SELECTs, then unlock it again afterwards? I've read about the five locking states (unlocked, shared, etc.) but I assume the dll handles all that at INSERT or UPDATE time. I'm looking for a way to ensure other users aren't allowed to write to the database during this quarter-second period. http://www.sqlite.org/lang_transaction.html BEGIN EXCLUSIVE Thanks in advance. Jonathan Trahair Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RTree Documentation Error?
It would also be nice if 3.3 showed the expected output too. Would be a bit easier for the beginner to ensure they are sane. Also would've prevented this GIGO error me thinkst. Michael Black ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a command to lock a database for a short while (0.25 sec, say)
Don't forget that your other applications will need to extend their connection time outs, otherwise, while this application has the lock, the other applications will time out and throw errors. On Wed, Apr 17, 2013 at 9:43 AM, Simon Davies simon.james.dav...@gmail.comwrote: On 17 April 2013 14:39, J Trahair j.trah...@foreversoftware.co.uk wrote: Hi everyone Can my application lock the database for its own exclusive use at the beginning of a series of INSERTs UPDATEs and SELECTs, then unlock it again afterwards? I've read about the five locking states (unlocked, shared, etc.) but I assume the dll handles all that at INSERT or UPDATE time. I'm looking for a way to ensure other users aren't allowed to write to the database during this quarter-second period. http://www.sqlite.org/lang_transaction.html BEGIN EXCLUSIVE Thanks in advance. Jonathan Trahair Regards, Simon ___ 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] CLI Source Code
Maybe I'm blind, but, I don't see the source code for the CLI on the download page. Is this an oversight, or by design? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CLI Source Code
Oh never mind. Instructions are at the bottom of the CLI page itself. :] On Wed, Apr 17, 2013 at 11:54 AM, Stephen Chrzanowski pontia...@gmail.comwrote: Maybe I'm blind, but, I don't see the source code for the CLI on the download page. Is this an oversight, or by design? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a command to lock a database for a short while (0.25 sec, say)
On Wed, 17 Apr 2013 15:39:50 +0200 J Trahair j.trah...@foreversoftware.co.uk wrote: I'm looking for a way to ensure other users aren't allowed to write to the database during this quarter-second period. That might work for you this time, but it doesn't scale well; historically that's why the DBMS took over locking. You might instead want to consider a more relational approach. For example, you could have a current version table somewhere with one row. Various tables would have version columns. The application could select from views that join to the current-version table. Your update could add rows with a new version, update the current-version row, and at its leisure delete rows for the prior version. Because the update is atomic, the transition is instantaneous; no one is ever blocked but for an instant. Of course I don't know specifically what you're trying to do. I'm just saying that lock-the-world isn't a general solution, and the DBMS supports many general alternatives. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users