Re: [sqlite] async io and locks

[Dan]

On Jun 20, 2009, at 6:22 AM, Robert Lehr wrote:

> Once again, I am forced to reply to the top-level, outside the thread
> b/c I am not receiving individual replies.  I apologize for the  
> inconvenience.
>
> On Jun 20, 2009, at 12:30 AM, Dan wrote:
>> On Jun 20, 2009, at 12:06 AM, Robert Lehr wrote:
>>> I finally got around to reviewing SQLite's asynchronous I/O
>>> functionality.
>>>
>>>   http://sqlite.org/asyncvfs.html 
>>>
>>> We actually have an C++ wrapper to uses the same concept, a  
>>> background
>>> thread for I/O.
>>
>> You mean you have implemented the callback methods in struct  
>> sqlite3_vfs to read
>> and write asynchronously just as we have? Or some other technique?
>>
>
> Another technique - the C++ __wrapper__, akin to the numerous other C 
> ++ wrappers
> that are being hacked together around the world.  We required  
> asynchrony.  We
> therefore wrote our own wrapper that implements it.
>
>>>
>>> W/rt to the locking policy w/ multiple updates, are there design
>>> reasons for not releasing and re-acquiring a lock between
>>> transactions?  That would facilitate higher concurrency albeit it a
>>> slightly higher cost than the current implementation.
>>> That cost should match the current cost of multiple transactions,
>>> though.
>>
>> It's to handle this:
>>
>>   BEGIN;
>> UPDATE t1 SET  WHERE ;
>>   COMMIT;
>>   BEGIN
>> UPDATE t2 SET  WHERE ;
>>   COMMIT;
>>
>> If the SQLite user starts the second transaction before the  
>> asynchronous thread
>> has had time to commit the first, it will read the database to  
>> figure out the
>> set of rows to apply the  modifications to.  Once that  
>> has happened,
>> the database file cannot be unlocked before the second transaction is
>> committed. Otherwise, some other client might sneak in while the  
>> database was
>> unlocked and modify table t2, changing the set of rows  
>> 
>> selects.
>>
>> Of course, if the asynchronous thread manages to commit the first  
>> transaction to
>> disk before the user has time to execute the second, the database  
>> file will be
>> unlocked between transactions.
>
> Yes, except that I verified that this interpretation is  
> incorrect according
> to the docs (as I interpret them) before I sent my original query.   
> The relevant
> part of the docs follows.
>
>"If an application using asynchronous IO executes transactions
>in quick succession, other database users may be effectively
>locked out of the database. This is because when a BEGIN is
>executed, a database lock is established immediately."
>
> Particularly note that the following denotes:
>
>   * that the lock is acquired independent of any transaction.
>   * that the lock on the database file persists beyond the duration of
> all transactions.
>
>"But when the corresponding COMMIT or ROLLBACK occurs, the
>lock is not released until the relevant part of the
>write-queue has been flushed through. As a result, if a COMMIT
>is followed by a BEGIN before the write-queue is flushed
>through, the database is never unlocked,preventing other
>processes from accessing the database."
>
> Thus the async functionality does NOT simply wrap each update query  
> (UPDATE,
> DELETE) in a transaction.
>
> If it did then I would be sending a completely different query, one  
> pertaining
> to broken semantics (for our purposes) of automatically wrapping my  
> queries in
> nested transactions which would, again, prevent me from adopting  
> SQLite's
> baseline async functionality.
>
> BTW, it occurred to me as I wrote this reply that the fact the SQLite
> distinguishes FETCHES from CREATES, UPDATES and DELETES indicates  
> that it is
> inspecting the queries themselves before deciding to append them to a
> write-queue or not.  It therefore could detect beginnings and ends of
> transactions.  Thus SQLite could enqueue transactions as it  
> currently enqueues
> write queries.


I think we have quite different approaches.

The SQLite asynchronous IO backend is queueing IO, not queries. It  
intercepts
the equivalent of the write() system call. It does not overload query  
processing
in any way. The things on the write-queue are blobs of data to be  
written
to specific offsets within the database file, not SQL queries.

It's more complex than that of course. But the point is that the write- 
queue is
a layer between SQLite and the OS, not between the user and SQLite.

Dan.


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

Re: [sqlite] search in archive

[John Machin]

On 20/06/2009 3:56 AM, Rizzuto, Raymond wrote:
> Is it possible to have a search feature for the archive?  I.e. rather than 
> having to do a linear search through 18 archives for an answer to a question, 
> have a google-like search across all of the archives?

http://search.gmane.org/

In the box called "group", type in: comp.db.sqlite.general

Based on a reverse-date search for "Richard Hipp", it appears to go back 
to 2002 at least.

HTH,
John

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

Re: [sqlite] async io and locks (CORRECTION)

[Robert Lehr]

(A) I was able to reply inside the thread.  *confused*
 
(B) "that the lock is acquired independent..." is incorrect and irrelevant.
It should be ignored.

-robert

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

Re: [sqlite] async io and locks

[Robert Lehr]

Once again, I am forced to reply to the top-level, outside the thread
b/c I am not receiving individual replies.  I apologize for the inconvenience.

On Jun 20, 2009, at 12:30 AM, Dan wrote:
> On Jun 20, 2009, at 12:06 AM, Robert Lehr wrote:
> > I finally got around to reviewing SQLite's asynchronous I/O
> > functionality.
> >
> >http://sqlite.org/asyncvfs.html 
> >
> > We actually have an C++ wrapper to uses the same concept, a background
> > thread for I/O.
>
> You mean you have implemented the callback methods in struct sqlite3_vfs to 
> read
> and write asynchronously just as we have? Or some other technique?
>

Another technique - the C++ __wrapper__, akin to the numerous other C++ wrappers
that are being hacked together around the world.  We required asynchrony.  We
therefore wrote our own wrapper that implements it.

> >
> > W/rt to the locking policy w/ multiple updates, are there design
> > reasons for not releasing and re-acquiring a lock between
> > transactions?  That would facilitate higher concurrency albeit it a
> > slightly higher cost than the current implementation.
> > That cost should match the current cost of multiple transactions,
> > though.
>
> It's to handle this:
>
>BEGIN;
>  UPDATE t1 SET  WHERE ;
>COMMIT;
>BEGIN
>  UPDATE t2 SET  WHERE ;
>COMMIT;
>
> If the SQLite user starts the second transaction before the asynchronous 
> thread
> has had time to commit the first, it will read the database to figure out the
> set of rows to apply the  modifications to.  Once that has 
> happened,
> the database file cannot be unlocked before the second transaction is
> committed. Otherwise, some other client might sneak in while the database was
> unlocked and modify table t2, changing the set of rows 
> selects.
>
> Of course, if the asynchronous thread manages to commit the first transaction 
> to
> disk before the user has time to execute the second, the database file will be
> unlocked between transactions.

Yes, except that I verified that this interpretation is incorrect according
to the docs (as I interpret them) before I sent my original query.  The relevant
part of the docs follows.

"If an application using asynchronous IO executes transactions
in quick succession, other database users may be effectively
locked out of the database. This is because when a BEGIN is
executed, a database lock is established immediately."

Particularly note that the following denotes:

* that the lock is acquired independent of any transaction.
* that the lock on the database file persists beyond the duration of
  all transactions.

"But when the corresponding COMMIT or ROLLBACK occurs, the
lock is not released until the relevant part of the
write-queue has been flushed through. As a result, if a COMMIT
is followed by a BEGIN before the write-queue is flushed
through, the database is never unlocked,preventing other
processes from accessing the database."

Thus the async functionality does NOT simply wrap each update query (UPDATE,
DELETE) in a transaction.

If it did then I would be sending a completely different query, one pertaining
to broken semantics (for our purposes) of automatically wrapping my queries in
nested transactions which would, again, prevent me from adopting SQLite's
baseline async functionality.

BTW, it occurred to me as I wrote this reply that the fact the SQLite
distinguishes FETCHES from CREATES, UPDATES and DELETES indicates that it is
inspecting the queries themselves before deciding to append them to a
write-queue or not.  It therefore could detect beginnings and ends of
transactions.  Thus SQLite could enqueue transactions as it currently enqueues
write queries.

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

[sqlite] (no subject)

[Robert Lehr]

Date: Fri, 19 Jun 2009 14:53:05 -0700
From: Robert Lehr 
Subject: Re: [sqlite] async io and locks
To: sqlite-users@sqlite.org
In-Reply-To: <43c62cbb-57db-4d1b-af36-2facf239c...@gmail.com>
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes

I have never been able to reply to messages w/in the mailing-list's
thread.  Outlook must be munging the headers to prevent proper
threading.  I'm trying to force the threading by manipulating the
headers manually.  I apologize if this reply winds up in the top-level
as all of my others have.

On Jun 20, 2009, at 12:30 AM, Dan wrote:
> On Jun 20, 2009, at 12:06 AM, Robert Lehr wrote:
> > I finally got around to reviewing SQLite's asynchronous I/O 
> > functionality.
> >
> >http://sqlite.org/asyncvfs.html 
> >
> > We actually have an C++ wrapper to uses the same concept, a background 
> > thread for I/O.
> 
> You mean you have implemented the callback methods in struct sqlite3_vfs to 
> read
> and write asynchronously just as we have? Or some other technique?
> 

Another technique - the C++ __wrapper__, akin to the numerous other C++ wrappers
that are being hacked together around the world.  We required asynchrony.  We
therefore wrote our own wrapper that implements it.

> >
> > W/rt to the locking policy w/ multiple updates, are there design 
> > reasons for not releasing and re-acquiring a lock between 
> > transactions?  That would facilitate higher concurrency albeit it a 
> > slightly higher cost than the current implementation.
> > That cost should match the current cost of multiple transactions, 
> > though.
> 
> It's to handle this:
> 
>BEGIN;
>  UPDATE t1 SET  WHERE ;
>COMMIT;
>BEGIN
>  UPDATE t2 SET  WHERE ;
>COMMIT;
> 
> If the SQLite user starts the second transaction before the asynchronous 
> thread
> has had time to commit the first, it will read the database to figure out the
> set of rows to apply the  modifications to.  Once that has 
> happened,
> the database file cannot be unlocked before the second transaction is
> committed. Otherwise, some other client might sneak in while the database was
> unlocked and modify table t2, changing the set of rows 
> selects.
> 
> Of course, if the asynchronous thread manages to commit the first transaction 
> to
> disk before the user has time to execute the second, the database file will be
> unlocked between transactions.


Yes, except that I verified that this interpretation is incorrect according
to the docs as I interpret them.  The relevant part of the docs follows.

"If an application using asynchronous IO executes transactions
in quick succession, other database users may be effectively
locked out of the database. This is because when a BEGIN is
executed, a database lock is established immediately."

Particularly note that the following denotes that the lock on the database file
persists beyond the duration of all transactions.

"But when the corresponding COMMIT or ROLLBACK occurs, the
lock is not released until the relevant part of the
write-queue has been flushed through. As a result, if a COMMIT
is followed by a BEGIN before the write-queue is flushed
through, the database is never unlocked,preventing other
processes from accessing the database."

Thus the async functionality does NOT simply wrap each update query (UPDATE,
DELETE) in a transaction.

If it did then I would be sending a completely different query, one pertaining
to broken semantics (for our purposes) of automatically wrapping my queries in
nested transactions which would, again, prevent me from adopting SQLite's
baseline async functionality.

BTW, it occurred to me as I wrote this reply that the fact the SQLite
distinguishes FETCHES from CREATES, UPDATES and DELETES indicates that it is
inspecting the queries themselves before deciding to append them to a
write-queue or not.  It therefore could detect beginnings and ends of
transactions.  Thus SQLite could enqueue transactions as it currently enqueues
write queries.

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

Re: [sqlite] Sqlite-3.5.9: getting sqlite_autoindex error

[Tim Bradshaw]

On 19 Jun 2009, at 06:36, hiral wrote:

> I am running application which uses the db over the NFS. If I move  
> the db
> over the local drive then it's working fine.
> So I m observing this bug in NFS environment frequently.
> In the same test environment (in which I am getting db corrupted),  
> if I use
> the sqlite-3.6.4 and above it works perfectly fine.

A couple of things to look at.
* what are the NFS mount options?  In particular do not, ever, use  
soft mounts[*].
* what client and server are you using?  Typically Linux / anything  
not linux is a recepie for trouble, as Linux's NFS implementation is a  
bit of a joke in terms of standards (actually, it has been more than a  
bit of a joke at various times).

--tim

[*] Really, not ever
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] search in archive

[Rizzuto, Raymond]

My apologies for being unclear.  I meant the archive of all the sqlite-users 
messages.  I prefer not to ask a question that has already been answered.

The link http://www.mail-archive.com/sqlite-users@sqlite.org/ suggested by Adam 
DeVita does has such a function.  I was accessing the archives via 
http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/, which doesn't 
have that functionality.

-Original Message-
From: Swithun Crowe [mailto:swit...@swithun.servebeer.com]
Sent: Friday, June 19, 2009 3:16 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] search in archive

Hello

KN On Fri, 19 Jun 2009 13:56:52 -0400, "Rizzuto, Raymond"
KN  wrote:
KN
KN > Is it possible to have a search feature for the
KN > archive?
KN
KN Which archive?

I think Raymond means the sqlite-users archive.

You could download all the txt.gz files, cat them together and then grep
for what you want to find. You wouldn't have the threads, but it might be
easier than checking all the threads via the web site.

Perhaps it is possible to import the files into an email client which can
recreate the messages and threads.

Swithun.


IMPORTANT: The information contained in this email and/or its attachments is 
confidential. If you are not the intended recipient, please notify the sender 
immediately by reply and immediately delete this message and all its 
attachments. Any review, use, reproduction, disclosure or dissemination of this 
message or any attachment by an unintended recipient is strictly prohibited. 
Neither this message nor any attachment is intended as or should be construed 
as an offer, solicitation or recommendation to buy or sell any security or 
other financial instrument. Neither the sender, his or her employer nor any of 
their respective affiliates makes any warranties as to the completeness or 
accuracy of any of the information contained herein or that this message or any 
of its attachments is free of viruses.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] search in archive

[Adam DeVita]

The link I posted (http://www.mail-archive.com/sqlite-users%40sqlite.org/)
only goes back to March 10, 2009  It searches well but is currently
incomplete. Will it be expanded to include older posts?

http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/

goes back much further, but searching is as Raymond described.  Painful,
compared to the nice enter search text here at the mail-archive.com.

http://marc.info/?l=sqlite-users&r=1&w=2 also has nice search
capabilities.   Perhaps the http://www.sqlite.org/support.html should be
updated to advertise that the off site archives have search features? A lot
of questions that have already been answered and keep coming back could be
avoided.






On Fri, Jun 19, 2009 at 3:16 PM, Swithun Crowe <
swit...@swithun.servebeer.com> wrote:

> Hello
>
> KN On Fri, 19 Jun 2009 13:56:52 -0400, "Rizzuto, Raymond"
> KN  wrote:
> KN
> KN > Is it possible to have a search feature for the
> KN > archive?
> KN
> KN Which archive?
>
> I think Raymond means the sqlite-users archive.
>
> You could download all the txt.gz files, cat them together and then grep
> for what you want to find. You wouldn't have the threads, but it might be
> easier than checking all the threads via the web site.
>
> Perhaps it is possible to import the files into an email client which can
> recreate the messages and threads.
>
> Swithun.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] search in archive

[Swithun Crowe]

Hello

KN On Fri, 19 Jun 2009 13:56:52 -0400, "Rizzuto, Raymond"
KN  wrote:
KN 
KN > Is it possible to have a search feature for the
KN > archive? 
KN 
KN Which archive?

I think Raymond means the sqlite-users archive.

You could download all the txt.gz files, cat them together and then grep 
for what you want to find. You wouldn't have the threads, but it might be 
easier than checking all the threads via the web site.

Perhaps it is possible to import the files into an email client which can 
recreate the messages and threads.

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

Re: [sqlite] search in archive

[Adam DeVita]

There is a search of archives at
http://www.mail-archive.com/sqlite-users%40sqlite.org/



On Fri, Jun 19, 2009 at 2:43 PM, Kees Nuyt  wrote:

> On Fri, 19 Jun 2009 13:56:52 -0400, "Rizzuto, Raymond"
>  wrote:
>
> > Is it possible to have a search feature for the
> > archive?
>
> Which archive?
>
> I'll assume you have 18 different databases and you want to
> search them in parallel.
>
> > I.e. rather than having to do a linear
> > search through 18 archives for an answer
> > to a question, have a google-like search
> > across all of the archives?
>
> Yes, make your application multithreaded, one thread for the
> user interface and 18 for databases. Every dbthread would
> open a different database.
>
> It will only really help if your system has multiple
> processor cores, and if the databases are each on a
> different disk.
> --
>  (  Kees Nuyt
>  )
> c[_]
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [noob] merge statement equivalent?

[James Gregurich]

thanks!


On Jun 18, 2009, at 6:01 PM, Dennis Cote wrote:

> James Gregurich wrote:
>> on that update statement, is the SQL optimizer smart enough to not
>> rerun that select statement for each column in the update's set
>> clause? Is it going to run a single select statement to get ,
>> , etc.  or is it going to run one for each column in the
>> update statement?
>>
>>
> James,
>
> No, I don't believe the optimizer is that smart. SQLite will execute
> multiple queries.
>
> If you are concerned that the matches table is large you could add an
> index on the the row1 column of the matches table to speed up the row2
> lookups. The lookups in table2 using the rowid should be very fast,  
> and
> once the page with the required record has been read into the cache  
> the
> subsequent value  lookup queries
> should execute very quickly as well.
>
> HTH
> Dennis Cote
> ___
> 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] search in archive

[Kees Nuyt]

On Fri, 19 Jun 2009 13:56:52 -0400, "Rizzuto, Raymond"
 wrote:

> Is it possible to have a search feature for the
> archive? 

Which archive?

I'll assume you have 18 different databases and you want to
search them in parallel.

> I.e. rather than having to do a linear
> search through 18 archives for an answer
> to a question, have a google-like search
> across all of the archives?

Yes, make your application multithreaded, one thread for the
user interface and 18 for databases. Every dbthread would
open a different database.

It will only really help if your system has multiple
processor cores, and if the databases are each on a
different disk.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_exec() documentation

[Sherief N. Farouk]

The documentation found at http://sqlite.org/c3ref/exec.html is unclear
about some parts: is it possible to not request an error message (via
passing NULL as the errmsg parameter)? And what are the possible return
values?

- Sherief

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

[sqlite] shared library versions

[Rizzuto, Raymond]

I noticed that the sqlite.so (SLES 9 32-bit) is numbered .0.8.6 in 3.5.9 and 
3.6.15 versions.  This caused me some issues when I tried running an 
application build against 3.6.15, but the .so that it found was from 3.5.9.   
It partially worked, depending on the methods I called.

Part of the reason for my question is that I may need to have both 3.5.9 and 
3.6.15 libraries on a production system because our existing apps were built 
against 3.5.9, but I am adding an app built against 3.6.15.  Recompiling the 
existing applications is undesirable at this time due to the retest cycle it 
would incure.

I would appreciate any input on how sqlite is handing versioning so I can 
determine a deployment plan.

Ray

Ray Rizzuto
raymond.rizz...@sig.com
Susquehanna International Group
(610)747-2336 (W)
(215)776-3780 (C)




IMPORTANT: The information contained in this email and/or its attachments is 
confidential. If you are not the intended recipient, please notify the sender 
immediately by reply and immediately delete this message and all its 
attachments. Any review, use, reproduction, disclosure or dissemination of this 
message or any attachment by an unintended recipient is strictly prohibited. 
Neither this message nor any attachment is intended as or should be construed 
as an offer, solicitation or recommendation to buy or sell any security or 
other financial instrument. Neither the sender, his or her employer nor any of 
their respective affiliates makes any warranties as to the completeness or 
accuracy of any of the information contained herein or that this message or any 
of its attachments is free of viruses.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] search in archive

[Rizzuto, Raymond]

Is it possible to have a search feature for the archive?  I.e. rather than 
having to do a linear search through 18 archives for an answer to a question, 
have a google-like search across all of the archives?



Ray Rizzuto
raymond.rizz...@sig.com
Susquehanna International Group
(610)747-2336 (W)
(215)776-3780 (C)




IMPORTANT: The information contained in this email and/or its attachments is 
confidential. If you are not the intended recipient, please notify the sender 
immediately by reply and immediately delete this message and all its 
attachments. Any review, use, reproduction, disclosure or dissemination of this 
message or any attachment by an unintended recipient is strictly prohibited. 
Neither this message nor any attachment is intended as or should be construed 
as an offer, solicitation or recommendation to buy or sell any security or 
other financial instrument. Neither the sender, his or her employer nor any of 
their respective affiliates makes any warranties as to the completeness or 
accuracy of any of the information contained herein or that this message or any 
of its attachments is free of viruses.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] async io and locks

[Dan]

On Jun 20, 2009, at 12:06 AM, Robert Lehr wrote:

> I finally got around to reviewing SQLite's asynchronous I/O  
> functionality.
>
>http://sqlite.org/asyncvfs.html 
>
> We actually have an C++ wrapper to uses the same concept, a  
> background thread for I/O.

You mean you have implemented the callback methods in struct sqlite3_vfs
to read and write asynchronously just as we have? Or some other  
technique?

> The async functionality included w/ SQLite is not a complete  
> replacement for ours, though, although it would be convenient to do  
> so.  However, we do things differently such that we
> cannot take advantage of SQLite's async functionality.
>
>   * We queue transactions, not individual queries.  Note that,  
> because queue
> transactions_ instead of queries, we lock the database per  
> transaction,
> thus avoiding the reduction in concurrency that is described on  
> the async
> I/O page.
>
>   * Our bg thread implementation retains durability by permitting  
> registration
> of callbacks for queries' succeul completion and aborts.
>
> W/rt to the locking policy w/ multiple updates, are there design  
> reasons for
> not releasing and re-acquiring a lock between transactions?  That  
> would facilitate
> higher concurrency albeit it a slightly higher cost than the current  
> implementation.
> That cost should match the current cost of multiple transactions,  
> though.

It's to handle this:

   BEGIN;
 UPDATE t1 SET  WHERE ;
   COMMIT;
   BEGIN
 UPDATE t2 SET  WHERE ;
   COMMIT;

If the SQLite user starts the second transaction before the asynchronous
thread has had time to commit the first, it will read the database to
figure out the set of rows to apply the  modifications to.  
Once
that has happened, the database file cannot be unlocked before the  
second
transaction is committed. Otherwise, some other client might sneak in  
while
the database was unlocked and modify table t2, changing the set of rows
 selects.

Of course, if the asynchronous thread manages to commit the first  
transaction
to disk before the user has time to execute the second, the database  
file
will be unlocked between transactions.

Dan.


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

[sqlite] async io and locks

[Robert Lehr]

I finally got around to reviewing SQLite's asynchronous I/O functionality.
 
http://sqlite.org/asyncvfs.html  
 
We actually have an C++ wrapper to uses the same concept, a background thread 
for I/O.
 
The async functionality included w/ SQLite is not a complete replacement for 
ours, though, although it would be convenient to do so.  However, we do things 
differently such that we
cannot take advantage of SQLite's async functionality.

* We queue transactions, not individual queries.  Note that, because 
queue
  transactions_ instead of queries, we lock the database per 
transaction,
  thus avoiding the reduction in concurrency that is described on the 
async
  I/O page.  

* Our bg thread implementation retains durability by permitting 
registration
  of callbacks for queries' succeul completion and aborts.

W/rt to the locking policy w/ multiple updates, are there design reasons for
not releasing and re-acquiring a lock between transactions?  That would 
facilitate
higher concurrency albeit it a slightly higher cost than the current 
implementation.
That cost should match the current cost of multiple transactions, though.

Many thanks.

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

[sqlite] Group_concat includes null if first in the group

[Edzard Pasma]

Hello, I found an issue in group_concat, that appears mainly cosmetical, 
however may be good to be aware of for those making a more creative use of this 
function. See the ticket http://www.sqlite.org/cvstrac/tktview?tn=3923. Edzard.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3.dll and async io ??

[Christophe Faure]

I want to test sqlite3asynch io but it seems that all the ex\async files 
are :
not it amalgamation ?
not in zipped source files ??
not included in the sqlite3.dll ???

But it seems to work fine with win32 platform using amalgamation + 
sqlite3async files !

Does future sqlite3.dll/libsqlite3.so will include async io ?
On Multi-core cpu  performance seems to be better,  i mostly use db for 
reading  and did not notice any failure (i do not use asynchronous io 
mode for database updates as i have 2 soft one for reading the DB one 
for maintaining the db)
As i do not plan to recompile my code at often as sqlite i must define 
if i can use it or not.

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

Re: [sqlite] Sqlite-3.5.9: getting sqlite_autoindex error

[Simon Davies]

2009/6/19 hiral :
> Hi Simon / John,
>
> Thank you for replies.
>
>> You are able to repeatably corrupt the db under SQLite 3.5.9? It would
>> be worthwhile publishing a script that can do that.
>  > "was getting error often" or "did get error ONCE"??
> I am running application which uses the db over the NFS. If I move the db
> over the local drive then it's working fine.
> So I m observing this bug in NFS environment frequently.
> In the same test environment (in which I am getting db corrupted), if I use
> the sqlite-3.6.4 and above it works perfectly fine.
>
>
.
.
snip
.
.
> Yes, 'disk image malformed' message I am observing on NFS.
> Environment:
> CentOS 4.4
> nfs-utils-lib-1.0.6-3
> system-config-nfs-1.2.8-1
> nfs-utils-1.0.6-70.EL4
> nfs-utils-lib-devel-1.0.6-3
> nfs-utils-1.0.6-70.EL4
> - db is on NFS drive, but not shared by thread/processes/users
> - not using thread
>
> Some interesting information:
> When I debuged the sqlite-3.5.9 code, and observed that it's getting
> corrupted at following condition...

This is where corruption is detected, not where caused.

> - in sqlite3BtreeInitPage() it returns with SQLITE_CORRUPT_BKPT based on
> some condition (see below)...
>
> SQLITE_PRIVATE int sqlite3BtreeInitPage(
>  MemPage *pPage,        /* The page to be initialized */
>  MemPage *pParent       /* The parent.  Might be NULL */
> ){
>     ...
>     if( pPage->nCell==0 && pParent!=0 && pParent->pgno!=1 ){
>     /* All pages must have at least one cell, except for root pages */
>     return SQLITE_CORRUPT_BKPT;
>     }
>     ...
> }
>
> I would appreciate your help in debuging this bug.

If you have found a version that does not manifest the 'bug' why not
use that? (It has already been debugged!)

> Thank you.
> -Hiral
>

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

Re: [sqlite] Literal replacements in prepared statements

[John Machin]

On 20/06/2009 12:06 AM, Shaun Seckman (Firaxis) wrote:
> Not sure I fully understand what you mean.

> Is it not possible to replace the table name in the prepared statement?

It is not possible.

>  What sort of things can I replace then?

You can do replacement at any place where a "literal" (i.e. a constant) 
is legal.

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

Re: [sqlite] Literal replacements in prepared statements

[Shaun Seckman (Firaxis)]

Ah, that makes more sense :)  Thanks a bunch for the clarification!

-Shaun

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Pavel Ivanov
Sent: Friday, June 19, 2009 10:57 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Literal replacements in prepared statements

Yes, it's impossible to replace table or column names. You can replace
any constant values like this:

select table.column2, column3 + ?
from table
where column1 = ? and column2 + ? > column3
limit ?, ?

Pavel

On Fri, Jun 19, 2009 at 10:06 AM, Shaun Seckman
(Firaxis) wrote:
> Not sure I fully understand what you mean.  Is it not possible to replace the 
> table name in the prepared statement?  What sort of things can I replace then?
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
> Sent: Friday, June 19, 2009 10:03 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Literal replacements in prepared statements
>
> You're trying identifier replacement, not literal replacement. It's
> not allowed. You have to write table name without binding.
>
> Pavel
>
> On Fri, Jun 19, 2009 at 9:58 AM, Shaun Seckman
> (Firaxis) wrote:
>> I'm trying to add some literal replacements in my prepared SQL statement
>> but I'm currently getting a SQL syntax error.
>>
>> Here's a snippit of what I'm trying to do:
>>
>>
>>
>> ...
>>
>> sqlite3_stmt* stmt;
>>
>> sqlite3_prepare_v2(db, "Select * from ?", -1, &stmt, NULL);   <-- near
>> "?": syntax error
>>
>> sqlite3_bind_text(stmt, 1, tableName, -1, SQLITE_TRANSIENT);
>>
>> ...
>>
>>
>>
>> Any idea what I'm doing wrong?
>>
>>
>>
>> -Shaun
>>
>>
>>
>>
>>
>>
>>
>> ___
>> 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
>
___
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] Literal replacements in prepared statements

[Pavel Ivanov]

Yes, it's impossible to replace table or column names. You can replace
any constant values like this:

select table.column2, column3 + ?
from table
where column1 = ? and column2 + ? > column3
limit ?, ?

Pavel

On Fri, Jun 19, 2009 at 10:06 AM, Shaun Seckman
(Firaxis) wrote:
> Not sure I fully understand what you mean.  Is it not possible to replace the 
> table name in the prepared statement?  What sort of things can I replace then?
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
> Sent: Friday, June 19, 2009 10:03 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Literal replacements in prepared statements
>
> You're trying identifier replacement, not literal replacement. It's
> not allowed. You have to write table name without binding.
>
> Pavel
>
> On Fri, Jun 19, 2009 at 9:58 AM, Shaun Seckman
> (Firaxis) wrote:
>> I'm trying to add some literal replacements in my prepared SQL statement
>> but I'm currently getting a SQL syntax error.
>>
>> Here's a snippit of what I'm trying to do:
>>
>>
>>
>> ...
>>
>> sqlite3_stmt* stmt;
>>
>> sqlite3_prepare_v2(db, "Select * from ?", -1, &stmt, NULL);   <-- near
>> "?": syntax error
>>
>> sqlite3_bind_text(stmt, 1, tableName, -1, SQLITE_TRANSIENT);
>>
>> ...
>>
>>
>>
>> Any idea what I'm doing wrong?
>>
>>
>>
>> -Shaun
>>
>>
>>
>>
>>
>>
>>
>> ___
>> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Literal replacements in prepared statements

[Shaun Seckman (Firaxis)]

Not sure I fully understand what you mean.  Is it not possible to replace the 
table name in the prepared statement?  What sort of things can I replace then?

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Pavel Ivanov
Sent: Friday, June 19, 2009 10:03 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Literal replacements in prepared statements

You're trying identifier replacement, not literal replacement. It's
not allowed. You have to write table name without binding.

Pavel

On Fri, Jun 19, 2009 at 9:58 AM, Shaun Seckman
(Firaxis) wrote:
> I'm trying to add some literal replacements in my prepared SQL statement
> but I'm currently getting a SQL syntax error.
>
> Here's a snippit of what I'm trying to do:
>
>
>
> ...
>
> sqlite3_stmt* stmt;
>
> sqlite3_prepare_v2(db, "Select * from ?", -1, &stmt, NULL);   <-- near
> "?": syntax error
>
> sqlite3_bind_text(stmt, 1, tableName, -1, SQLITE_TRANSIENT);
>
> ...
>
>
>
> Any idea what I'm doing wrong?
>
>
>
> -Shaun
>
>
>
>
>
>
>
> ___
> 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] Literal replacements in prepared statements

[Pavel Ivanov]

You're trying identifier replacement, not literal replacement. It's
not allowed. You have to write table name without binding.

Pavel

On Fri, Jun 19, 2009 at 9:58 AM, Shaun Seckman
(Firaxis) wrote:
> I'm trying to add some literal replacements in my prepared SQL statement
> but I'm currently getting a SQL syntax error.
>
> Here's a snippit of what I'm trying to do:
>
>
>
> ...
>
> sqlite3_stmt* stmt;
>
> sqlite3_prepare_v2(db, "Select * from ?", -1, &stmt, NULL);   <-- near
> "?": syntax error
>
> sqlite3_bind_text(stmt, 1, tableName, -1, SQLITE_TRANSIENT);
>
> ...
>
>
>
> Any idea what I'm doing wrong?
>
>
>
> -Shaun
>
>
>
>
>
>
>
> ___
> 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] Literal replacements in prepared statements

[Shaun Seckman (Firaxis)]

I'm trying to add some literal replacements in my prepared SQL statement
but I'm currently getting a SQL syntax error.

Here's a snippit of what I'm trying to do:

 

... 

sqlite3_stmt* stmt; 

sqlite3_prepare_v2(db, "Select * from ?", -1, &stmt, NULL);   <-- near
"?": syntax error

sqlite3_bind_text(stmt, 1, tableName, -1, SQLITE_TRANSIENT);

...

 

Any idea what I'm doing wrong?

 

-Shaun

 

 

 

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

[sqlite] sqlite3_auto_extension

[Jean-Christophe Deschamps]

Hi Nico,

Thank you for your answer.

>It's obvious from the function prototype (no DLL/DSO file name
>argument).

Yes, of course.  I'm not confusing a string holding a filename and a 
function pointer!

>However, you can use sqlite3_load_extension() instead per-DB connection,
>OR, you can even do the dlopen(DSO)/dlsym(handle, 
>"sqlite3_extension_init")
>(or Windows equivalent) yourself to get the xEntryPoint argument for
>sqlite3_auto_extension().

Here's what I'm doing from within my extension.  First, remember that 
my problem is having extensions loaded for use with a 3rd-party 
manager.  I can't modify the code of this program, the only code I can 
act upon is the .dll extension itself.  sqlite3_auto_extension gets 
called indirectly thru a select statement to have the default entry 
point (sqlite3_extension_init) called.

select load_extension('filename.dll');

Here's a snippet of my code:

// my registration code
DLL_EXPORT int jcdext_init(sqlite3 *db){
...
// register functions with sqlite3_create_function(...)
}

DLL_EXPORT int sqlite3_extension_init(
   sqlite3 *db,
   char **pzErrMsg,
   const sqlite3_api_routines *pApi
){
   SQLITE_EXTENSION_INIT2(pApi)
   return sqlite3_auto_extension((void*)jcdext_init);
}

At first I thought that the registration code wasn't invoked but it is. 
The catch is that SQLite is still executing a SELECT statement; that 
must be the reason why, when overloading existing functions (like, 
upper, lower, nocase, ...), I receive a return code = SQLITE_BUSY.

New (proprietary) functions register without problem but only for the 
current DB connection: they remain invisible to new connections 
(althought having been registered using auto_extension).

What should I try next?


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

[sqlite] very bad performance with triggers and indexes

[David Jud]

Hi

I have a very strange performance problems involving indexes and triggers. A 
somewhat simplified example of what I want to do (but which allows to reproduce 
the behavior) is the following:

  CREATE TABLE MY_FC (ID integer, DESCRIPTION text);

I then proceed to insert 5 rows from my program (using the 
System.Data.SQLite API; I do 5000 inserts each in a transaction), which runs in 
about 0.4 seconds. I add a logging table and an after insert trigger which logs 
into the logging table:

  CREATE TABLE MY_FC_LOG (ID integer, TIMESTAMP text);
  CREATE TRIGGER my_fc_aid after insert on my_fc for each row
  begin
insert into my_fc_log (id, timestamp) values (new.id, current_timestamp);
  end;

Inserting 5 rows takes about 0.6 seconds, which is still very good :). Then 
I add an index:

  CREATE UNIQUE INDEX my_fc_pk ON my_fc (id);

Now inserting 5 rows takes about 12 seconds, or a factor 20 longer! When I 
drop the trigger to get rid of the logging:

  DROP TRIGGER my_fc_aid;

Then the 5 rows are back at ~0.6 seconds. So the performance is good both 
with the trigger and with the index individually, but if I combine both things 
get very slow. The funny thing is that the trigger doesn't even use the index; 
the two should not relate at all. Is this a known problem, a bug, or am I just 
doing something wrong?

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

[sqlite] FTS

[Martin Pfeifle]

Dear all,
 
we plan to use FTS in embedded devices for address search.
One requirement is to save storage.
 
Assume I create a table FTS_addresses (Field1,Field,2,..Fieldn),
where Field1 is an identifier for my addresses.
If now field1 would be used as document id, and if every fts query returns only 
the field1 value,
we would not need to store the contenttable at all, which would save space.
 
Can we get such a functionality from fts3, or do we have to implement this on 
our own.

Best Martin


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

Re: [sqlite] Database inserts gradually slowing down

[Jens Páll Hafsteinsson]

Yes, I've been looking into using the prepare/bind/step functions instead.

I'm at a complete loss as to what might have caused the behavior I initially 
saw, since every test I run now runs in constant time.

Thanks for the doc links Dennis, and thanks to everyone for their help and 
suggestions.

Cheers,
JP

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dennis Cote
Sent: 19. júní 2009 04:43
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Database inserts gradually slowing down

Jens Páll Hafsteinsson wrote:
> Here's the code I'm using to run the test (it includes the schema). I've been 
> running this code for the last few days and am a bit baffled about my recent 
> results, since I'm not seeing the gradual slowing anymore. This happened 
> after I changed to use version 3.6.15 of sqlite and even if I change back to 
> 3.6.14 it still behaves very consistently, that is, doing the insert and 
> delete in constant time.
>
>   
Jens,

I get similar constant time results using equivalent SQL scripts and the 
command line SQLite program.

I noticed tat you are using the sqlite3_exec() API in your tests. If you 
are concerned about performance, as you seem to be based on the testing 
you are doing, you should look at switching to the preferred 
prepare/bind/step API functions (see http://www.sqlite.org/cintro.html 
and http://www.sqlite.org/cvstrac/wiki?p=SimpleCode for info and samples).

Your test code is probably spending as much time compiling the same 
insert statement over and over again as it is on doing the actual 
inserts into the database. Using the alternate API you would prepare the 
statement once, then bind the values to be used for each insert, execute 
the insert, then reset the statement to be run again for the next insert.

HTH
Dennis Cote
___
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] SQLITE_CORRUPT error

[galeazzi]

Citando "D. Richard Hipp" :

>
> On Jun 18, 2009, at 12:36 PM, galea...@korg.it wrote:
>
>> This statement is giving me truoble:
>> INSERT INTO PlayList_Song(id_song, id_playlist, song_number) VALUES
>> (5235, 9, 256)
>
> That INSERT statement works fine for me.
>
> Did you try recompiling with optimizations turned off?
>
>
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

In order to be more confidence about what I'm saying, I downloaded the  
precompiled sqlite console 3.6.15 (windows  version), I executed the  
statement above and I've got the following error:

sqlite3.exe malformed_db.db
SQLite version 3.6.15
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> INSERT INTO PlayList_Song(id_song, id_playlist, song_number) VALUES
...> (5235, 9, 256);
SQL error: database disk image is malformed
sqlite> .q

Then I tried with a previous version and the statement has been well  
executed. A colleague of mine made the same test  and he had the same  
troubles. So I don't thing it's a compiler issue.
Did you make the test with windows console? Have I send you the  
database again?
Thanks for your helpfulness!

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