Re: [sqlite] Is there a command to lock a database for a short while (0.25 sec, say)

[James K. Lowden]

On Wed, 17 Apr 2013 15:39:50 +0200
"J Trahair"  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

Re: [sqlite] CLI Source Code

[Stephen Chrzanowski]

Oh never mind.  Instructions are at the bottom of the CLI page itself. :]


On Wed, Apr 17, 2013 at 11:54 AM, Stephen Chrzanowski
wrote:

> 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

[sqlite] CLI Source Code

[Stephen Chrzanowski]

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)

[Stephen Chrzanowski]

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
wrote:

> On 17 April 2013 14:39, J Trahair  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

Re: [sqlite] RTree Documentation Error?

[Michael Black]

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)

[Simon Davies]

On 17 April 2013 14:39, J Trahair  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] Is there a command to lock a database for a short while (0.25 sec, say)

[J Trahair]

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] RTree Documentation Error?

[Richard Hipp]

On Wed, Apr 17, 2013 at 9:25 AM, Michael Black  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

Re: [sqlite] RTree Documentation Error?

[Michael Black]

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  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?

[Richard Hipp]

On Wed, Apr 17, 2013 at 8:41 AM, Michael Black  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?

[Michael Black]

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] sqlite3 or sqlite4 for a new project?

[Richard Hipp]

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

[sqlite] sqlite3 or sqlite4 for a new project?

[Paolo Bolzoni]

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

[sqlite] How to Cross Compile SQLite3 inside the SBC

[Newbie89]

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

Re: [sqlite] Beginning database question

[Doug Nebeker]

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] Bug report: small bug in shell.c

[Eric Koldeweij]

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, );

+rc = rc == 2 ? 0 : rc;

 if( rc && bail_on_error ) return rc;

   }else{

 open_db();

@@ -3152,6 +3153,7 @@

 */

 if( zFirstCmd[0]=='.' ){

   rc = do_meta_command(zFirstCmd, );

+  rc = rc == 2 ? 0 : rc;

 }else{

   open_db();

   rc = shell_exec(data.db, zFirstCmd, shell_callback, , );

===

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] RTree Documentation Error?

[Richard Hipp]

On Wed, Apr 17, 2013 at 7:48 AM, Clemens Ladisch  wrote:

> Richard Hipp wrote:
> > On Tue, Apr 16, 2013 at 1:15 PM, Mohit Sindhwani  wrote:
> >> Hi, I was looking at the RTree documentation page with one of my
> >> colleagues - 
> >>
> >> 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 
> > .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

Re: [sqlite] RTree Documentation Error?

[Clemens Ladisch]

Richard Hipp wrote:
> On Tue, Apr 16, 2013 at 1:15 PM, Mohit Sindhwani  wrote:
>> Hi, I was looking at the RTree documentation page with one of my
>> colleagues - 
>>
>> 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?

[Richard Hipp]

On Tue, Apr 16, 2013 at 1:15 PM, Mohit Sindhwani  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:

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] Help speed up query

[Dominique Devienne]

On Tue, Apr 16, 2013 at 8:52 PM,  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