Re: [sqlite] SQLite3 and threading

2011-02-04 Thread Pavel Ivanov 
> I cite from http://sqlite.org/faq.html#q6:
>
> "The restriction on moving database connections across threads was
> relaxed somewhat in version 3.3.1. With that and subsequent versions, it
> is safe to move a connection handle across threads as long as the
> connection is not holding any fcntl() locks. You can safely assume that
> no locks are being held if no transaction is pending and all statements
> have been finalized."

I can't say why this was written and what does that mean. But I had an
application that moved all SQLite handles across threads and did that
with open transactions too. And it had no problems. IIRC, there were
examples of such behavior on this list too, also no problems were
reported. So maybe this is really old stuff.


Pavel

On Thu, Feb 3, 2011 at 1:59 PM, Ulrich Telle  wrote:
> Am 03.02.2011 15:53, schrieb Pavel Ivanov:
>> It seems that this explanation as well as all other statements in the
>> thread you linked are coming from the wrong assumption that SQLite's
>> handles cannot be used from any thread other than the one created that
>> handle.
>
> The explanation I gave to Stefano is *not* based on the assumption that
> SQlite handles can't be passed from one thread to another. But certainly
> passing them around *can* lead to problems.
>
>> Although this was true in some earlier versions of SQLite it's
>> not true in the current version. So if SQLite is compiled with
>> THREADSAFE=1 (as mentioned in that thread) then you can do with it
>> whatever you want. Just beware of possible data races and potentially
>> uncommitted transactions because of some open statement handles. And
>> if as you say there's no simultaneous access to the database from
>> different threads then there's no difference in your usage pattern
>> from single-threaded one.
>
> I cite from http://sqlite.org/faq.html#q6:
>
> "The restriction on moving database connections across threads was
> relaxed somewhat in version 3.3.1. With that and subsequent versions, it
> is safe to move a connection handle across threads as long as the
> connection is not holding any fcntl() locks. You can safely assume that
> no locks are being held if no transaction is pending and all statements
> have been finalized."
>
> The problem is that Stefano wants to pass a result set from one thread
> to another. The result set has an associated SQLite statement handle
> which is inherently *not* finalized. And that might cause trouble.
>
> If access to the database handle is serialized as Stefano claims passing
> the statement handle between threads shouldn't cause problems, true, but
> in that case I don't see the advantages of having a separate thread over
> using a global database access instance.
>
> Regards,
>
> Ulrich
>
> ___
> 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] Auto Reply: Auto Reply: Re: sqlite-users Digest, Vol 38, Issue 4

2011-02-04 Thread nicolas . williams 
This is an auto-replied message. I have left Oracle.  My new e-mail address is 
n...@cryptonector.com.

Nico
-- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Auto Reply: Re: sqlite-users Digest, Vol 38, Issue 4

2011-02-04 Thread nicolas . williams 
This is an auto-replied message. I have left Oracle.  My new e-mail address is 
n...@cryptonector.com.

Nico
-- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 38, Issue 4

2011-02-04 Thread Igor Tandetnik 
On 2/4/2011 11:14 AM, Scott Baker wrote:
> On 02/04/2011 04:00 AM, sqlite-users-requ...@sqlite.org wrote:
>> On 2/3/2011 12:10 PM, Scott Baker wrote:
   #3) Query for customers who bought exactly 2 apples?
>> select CustomerID from Customers
>> group by CustomerID
>> having sum(Type = 'Apple') = 2;
>
> I thought of one other case that I couldn't figure out. How would I get
> a count (instead of a list) of all the customers that bought exactly two
> apples?

select count(*) from (
   select CustomerID from Customers
   group by CustomerID
   having sum(Type = 'Apple') = 2;
)

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 38, Issue 4

2011-02-04 Thread Scott Baker 
On 02/04/2011 04:00 AM, sqlite-users-requ...@sqlite.org wrote:
> On 2/3/2011 12:10 PM, Scott Baker wrote:
>> >  CREATE Table Customers (
>> >EntryID INTEGER PRIMARY KEY,
>> >CustomerID INT,
>> >Type ENUM
>> >  );
>> >
>> >  #1) Query for customers who*ONLY*  bought apples
> select CustomerID from Customers
> group by CustomerID
> having sum(Type = 'Apple')>0 and sum(Type != 'Apple')=0;
>
>> >  #2) Query for customers who bought apples*AND*  bananas
> select CustomerID from Customers
> group by CustomerID
> having sum(Type = 'Apple')>0 and sum(Type = 'Banana')>0;
>
>> >  #3) Query for customers who bought exactly 2 apples?
> select CustomerID from Customers
> group by CustomerID
> having sum(Type = 'Apple') = 2;
>
> -- or
>
> select CustomerID from Customers
> where Type = 'Apple'
> group by CustomerID
> having count(*) = 2;

This is exactly what I needed thank you so much!

I thought of one other case that I couldn't figure out. How would I get 
a count (instead of a list) of all the customers that bought exactly two 
apples? I spent about 45 minutes hacking on the above but I couldn't get 
it. I'm thinking that would have to be a subselect?

-- 
Scott Baker - Canby Telcom
System Administrator - RHCE - 503.266.8253
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger for incrementing a column is slow

2011-02-04 Thread Richard Hipp 
On Fri, Feb 4, 2011 at 7:26 AM, Simon Slavin  wrote:

>
> On 4 Feb 2011, at 9:42am, Dan Kennedy wrote:
>
> > The problem is that the trigger version is creating a statement journal
> > for each INSERT statement. It doesn't *really* need to, as there is no
> > way that this statement/trigger can hit a constraint after modifying
> > any rows. However at the moment I think SQLite opens a statement
> > transaction for any statement that will fire one or more triggers.
>
> I'm not quite up with the terminology but now I'm interested.
>
> First, do you feel that this behaviour is a bug and can be addressed in
> future versions ?
>


I strive to be more precise and reserve the word "bug" for cases where the
software gets the wrong answer.  When the correct answer is obtained, just
more slowly than one hopes, that is not a "bug" but a "performance
enhancement opportunity."

We will look into taking advantage of this performance enhancement
opportunity to make SQLite run faster in a future release.  But we have to
proceed carefully here.  Performance enhancements are our #1 source of
bugs, since performance enhancements typically result in more complex code,
and complication tends to lead to bugs.  Our first priority is to avoid
bugs.  It is seldom helpful to get the wrong answer quickly.


> Second, is this the same as the automatic creation of transactions if the
> programmer doesn't create a transaction ?  In other words, if the programmer
> doesn't do a BEGIN, does SQLite make one transaction for the INSERT, and
> another for each action inside anything that triggers ?  Or do all the
> actions triggered by the INSERT get included within the same automatic
> transaction ?
>

When a statement is one of several statements that occur in the middle of a
larger BEGIN...END and that statement might fail, we have to open a separate
journal that can be used to rollback partial results of that one statement
without having to rollback the entire transaction.  The statement journal is
used for that purpose.


>
> Simon.
> ___
> 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] Trigger for incrementing a column is slow

2011-02-04 Thread Simon Slavin 

On 4 Feb 2011, at 9:42am, Dan Kennedy wrote:

> The problem is that the trigger version is creating a statement journal
> for each INSERT statement. It doesn't *really* need to, as there is no
> way that this statement/trigger can hit a constraint after modifying
> any rows. However at the moment I think SQLite opens a statement
> transaction for any statement that will fire one or more triggers.

I'm not quite up with the terminology but now I'm interested.

First, do you feel that this behaviour is a bug and can be addressed in future 
versions ?

Second, is this the same as the automatic creation of transactions if the 
programmer doesn't create a transaction ?  In other words, if the programmer 
doesn't do a BEGIN, does SQLite make one transaction for the INSERT, and 
another for each action inside anything that triggers ?  Or do all the actions 
triggered by the INSERT get included within the same automatic transaction ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multithreading problem

2011-02-04 Thread Dan Kennedy 
On 02/04/2011 01:04 AM, Tiberio, Sylvain wrote:
> You're right!
>
> I checked the Solaris documentation the correct flag to compile
> multithread program is -D_REENTRANT.
>
> I have reconfigured and remade sqlite libs:
>./configure --enable-threadsafe CFLAGS=-D_REENTRANT
>make
>
> I have remade my test file ...and it is working well now!
> Thanks you very much!!!
>
> Another question: Why ./configure doesn't set itself this flag when I
> use --enable-threadsafe in a Solaris system?

A bug I suppose. Will fix it for 3.7.6.

Dan.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger for incrementing a column is slow

2011-02-04 Thread Dan Kennedy 
On 02/04/2011 03:24 PM, Kevin Wojniak wrote:
>
> On Feb 3, 2011, at 10:35 PM, Dan Kennedy wrote:
>
>> Do you have a test program that we can use to reproduce this phenomenon?
>>
>> Dan.
>
> Here is a complete program:
> http://pastie.org/pastes/1527560
>
> Set USE_TRIGGER to see the trigger version.

Thanks for this.

The problem is that the trigger version is creating a statement journal
for each INSERT statement. It doesn't *really* need to, as there is no
way that this statement/trigger can hit a constraint after modifying
any rows. However at the moment I think SQLite opens a statement
transaction for any statement that will fire one or more triggers.

Statement journals:

   http://www.sqlite.org/tempfiles.html#stmtjrnl

The effect is that when using the no-trigger version, all that most
of your INSERT and UPDATE statements have to do is modify the database
within the cache. However the trigger version has to copy the original
page data into the statement journal before it can modify them. If the
statement journal is stored in a temporary file, this means many calls
to write().

You can improve the situation some by using an in-memory statement
journal:

   PRAGMA temp_store = memory;

But the trigger version is still slower. Because of the statement
transaction SQLite is opening. Sub-optimal, that.

Dan.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] beginner question: help required to retrieve "filename" from an open "sqlite3*" handle

2011-02-04 Thread Simon Davies 
On 4 February 2011 07:38, Andreas Otto  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Hi,
>
> with "sqlite2_open" the parameter "filename" is used to specify the
> database location.
>
> Q: how I can retrieve this parameter from an open handle?

http://www.sqlite.org/pragma.html#pragma_database_list

>
> sqlite-version: latest
>
>
> mfg, Andreas Otto

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger for incrementing a column is slow

2011-02-04 Thread Kevin Wojniak 

On Feb 3, 2011, at 10:35 PM, Dan Kennedy wrote:

> Do you have a test program that we can use to reproduce this phenomenon?
> 
> Dan.

Here is a complete program:
http://pastie.org/pastes/1527560

Set USE_TRIGGER to see the trigger version.

Thanks,
Kevin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users