Re: [sqlite] Deadlock when doing threaded updates and inserts

[dave]

On Aug 11, 2004, at 3:48 PM, Dave Hayden wrote:
Since only one of the competing threads will have completed a write 
(right?), can't the others "postpone" their transactions somehow until 
they can get a write lock?
That is, postpone the "begin transaction" action. Since they haven't 
really done any transaction business yet, anyway, because they haven't 
made a write.

-D

Re: [sqlite] Deadlock when doing threaded updates and inserts

[Tito Ciuro]

I'm definitely not happy about this...
Let me get this right... it seems that you're cruising along fine with 
SQLITE_OK's all over the place when suddenly one of your 
threads/processes get a SQLITE_BUSY signal in the middle of a 
transaction. In order to solve the crisis, one of the transactions 
*must* be rolled back:

Questions:
1) Which one? Do I toss a coin?
2) At the time when SQLITE_BUSY pops up, the app may very well be too 
deep in a transaction. Dr. Hipp suggests retrying the transaction that 
was rolled back, a solution I believe should be handled by the engine. 
Who is then responsible to keep track of the operations that make up a 
currently openened transaction? The app I suppose? It will add an 
amazing amount of ugly code testing/retesting/solving a SQLITE_BUSY 
signal.

Richard Boulton staterd earlier:
The change in 3.0.4 means that when using a busy handler (e.g.
sqlite3_busy_timeout) the threads trying to get RESERVED locks will not
retry, but instead will return immediately with SQLITE_BUSY. If these
transactions are rolled back the thread with the PENDING lock is free 
to
proceed when the busy handler retries the lock.
I would assume that every thread/process has its own journal, right? Or 
that there is a way of knowing which set of operations has a 
thread/process performed. If this is so, a thread/process could receive 
SQLITE_BUSY signals while an EXCLUSIVE lock is in place, then have 
SQLite re-execute the series of statements collected in the journal, 
that is, an auto-retry. If an error occurs, then the app can attempt to 
solve the issue. Is this something that can be done, or are there other 
impediments?

Best regards,
-- Tito
On Aug 12, 2004, at 00:48, Dave Hayden wrote:
On Aug 11, 2004, at 6:49 AM, D. Richard Hipp wrote:
Oops.  The db1 should do a ROLLBACK, not a COMMIT.  Or db2 can
do an END TRANSACTION (since it never made any changes) and allow
db1 to complete instead.  The point is that when two threads or
processes are trying to write at the same time, one of the two
must back off, abandon their transaction (using ROLLBACK) and let
the other proceed.
Wow. That adds a whole lot of complexity to my code. Every transaction 
would be inside a loop that checks for a busy return from any 
statement within. And most of the places I'm using a transaction, I'm 
doing a few hundred inserts or updates from a number of different 
functions.

This really is something I'd expect to run under the hood. Since only 
one of the competing threads will have completed a write (right?), 
can't the others "postpone" their transactions somehow until they can 
get a write lock?

For now, I've solved the problem by adding my own locks to exclude 
simultaneous transactions on the same database file. I'm only using 
transactions for writes (is there any reason for a read-only 
transaction?) so if there's no way to resolve two opened write 
transactions, you shouldn't be able to open two in the first place.

Please let me know if there's something I'm missing here..
Thanks,
-Dave

[sqlite] Query maximum JOIN

[Simon Berthiaume]

Hi, I would like to know if there is any maximum to the number of JOIN
(LEFT JOIN actualy) I can put in a query. I tried to make a query with
31-32 JOIN and the engine returned no row, but returned no error message
either so I don't know. Just a single JOIN before, the query was still
returning data, but not anymore.

Thank you for your time.


Simon Berthiaume

Re: [sqlite] Deadlock when doing threaded updates and inserts

[tezozomoc]

Good point...

I have several different tasks(vxworks)

Perhaps, this is why its working well for me...

Tezo.

- Original Message - 
From: "Dave Hayden" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, August 11, 2004 4:30 PM
Subject: Re: [sqlite] Deadlock when doing threaded updates and inserts


> On Aug 11, 2004, at 4:05 PM, tezozomoc wrote:
> 
> > I have solved this problem by writing a wrappers around sql_exec and
> > sql_query, sql_step, etc...
> > In these wrappers I handle the waiting for busy and the lock file 
> > issue...
> 
> I was doing the same, calling usleep() whenever I got a SQLITE_BUSY 
> return and trying the command again, but it doesn't help in the case 
> where two threads are both in a transaction and trying to write..
> 
> -D
> 
> 

Re: [sqlite] typeless / bug or feature??

[Kurt Welgehausen]

> sqlite is typeless. OK!
>
>but how can i save dates in sqlite ...


describes ALL the date/time functionality of SQLite.  You
can store dates as strings or numbers; it's your decision.

Regards

Re: [sqlite] Deadlock when doing threaded updates and inserts

[Paolo Vernazza]

For now, I've solved the problem by adding my own locks to exclude 
simultaneous transactions on the same database file. 

Ok, but this works only if your app is the only one that can access the 
DB. If some other app tries to access the same DB you can go in the 
usual deadlock.

Paolo

Re: [sqlite] typeless / bug or feature??

[Paolo Vernazza]

how can i realise that, i think its not a good idea to save dates in
this format 20041030 (it means the year 2004, month 10 and 30th day)
to solve my problem---
 

Why do you think that? It is really simple to read, write & compare
what's the matter?
for example i have many rows with each one date (everdy needs a row) from the
beginning of the year 2002 until 2004 and i will start a query where i will get all 
rows
from july of the year 2002 until 2004.

SELEC * FROM mytable WHERE date > '20020701' AND date < '20041231'
Paolo

Re: [sqlite] Deadlock when doing threaded updates and inserts

[Dave Hayden]

On Aug 11, 2004, at 4:05 PM, tezozomoc wrote:
I have solved this problem by writing a wrappers around sql_exec and
sql_query, sql_step, etc...
In these wrappers I handle the waiting for busy and the lock file 
issue...
I was doing the same, calling usleep() whenever I got a SQLITE_BUSY 
return and trying the command again, but it doesn't help in the case 
where two threads are both in a transaction and trying to write..

-D

Re: [sqlite] Deadlock when doing threaded updates and inserts

[tezozomoc]

I have solved this problem by writing a wrappers around sql_exec and
sql_query, sql_step, etc...
In these wrappers I handle the waiting for busy and the lock file issue...

It is not elegant but it allowed me to preserve the interface the same way
without having to do it at the application level.

Tezozomoc.

- Original Message - 
From: "Dave Hayden" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, August 11, 2004 3:48 PM
Subject: Re: [sqlite] Deadlock when doing threaded updates and inserts


> On Aug 11, 2004, at 6:49 AM, D. Richard Hipp wrote:
>
> > Oops.  The db1 should do a ROLLBACK, not a COMMIT.  Or db2 can
> > do an END TRANSACTION (since it never made any changes) and allow
> > db1 to complete instead.  The point is that when two threads or
> > processes are trying to write at the same time, one of the two
> > must back off, abandon their transaction (using ROLLBACK) and let
> > the other proceed.
>
> Wow. That adds a whole lot of complexity to my code. Every transaction
> would be inside a loop that checks for a busy return from any statement
> within. And most of the places I'm using a transaction, I'm doing a few
> hundred inserts or updates from a number of different functions.
>
> This really is something I'd expect to run under the hood. Since only
> one of the competing threads will have completed a write (right?),
> can't the others "postpone" their transactions somehow until they can
> get a write lock?
>
> For now, I've solved the problem by adding my own locks to exclude
> simultaneous transactions on the same database file. I'm only using
> transactions for writes (is there any reason for a read-only
> transaction?) so if there's no way to resolve two opened write
> transactions, you shouldn't be able to open two in the first place.
>
> Please let me know if there's something I'm missing here..
>
> Thanks,
> -Dave
>
>

Re[2]: [sqlite] full text search working from CD ROM ( Steve O'Hara's solution )

[Matt Sergeant]

On Thu, 12 Aug 2004, Pavel wrote:

> MS> There are some modules on CPAN which provide generic full text search
> MS> backed on any RDBMS. Try http://search.cpan.org/
> 
> CPAN was my first search, only was I found was DBIx::FullTextSearch
> that require MySQL and DBIx::TextIndex that search in BLOB column (and
> not in index).
> 
> Or I am missing something (most likely)?

Just port the code to SQLite. It won't be much work. Or use Plucene.

Matt.


__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__

Re: [sqlite] typeless / bug or feature??

[Will Leshner]

yesso wrote:
hi,
sqlite is typeless. OK!
but how can i save dates in sqlite because i need dates in my
query...??

As long as you store your dates in proper SQL format you should have no trouble querying for them.

Re: [sqlite] Deadlock when doing threaded updates and inserts

[Dave Hayden]

On Aug 11, 2004, at 6:49 AM, D. Richard Hipp wrote:
Oops.  The db1 should do a ROLLBACK, not a COMMIT.  Or db2 can
do an END TRANSACTION (since it never made any changes) and allow
db1 to complete instead.  The point is that when two threads or
processes are trying to write at the same time, one of the two
must back off, abandon their transaction (using ROLLBACK) and let
the other proceed.
Wow. That adds a whole lot of complexity to my code. Every transaction 
would be inside a loop that checks for a busy return from any statement 
within. And most of the places I'm using a transaction, I'm doing a few 
hundred inserts or updates from a number of different functions.

This really is something I'd expect to run under the hood. Since only 
one of the competing threads will have completed a write (right?), 
can't the others "postpone" their transactions somehow until they can 
get a write lock?

For now, I've solved the problem by adding my own locks to exclude 
simultaneous transactions on the same database file. I'm only using 
transactions for writes (is there any reason for a read-only 
transaction?) so if there's no way to resolve two opened write 
transactions, you shouldn't be able to open two in the first place.

Please let me know if there's something I'm missing here..
Thanks,
-Dave

Re: [sqlite] Deadlock when doing threaded updates and inserts

[Michael Roth]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Paolo Vernazza wrote:
| But doing in that way, you can have this behaviour (and this is what
| happends to me):
|
| db1: BEGIN TRANSACTION;-> SQLITE_OK
| db2: BEGIN TRANSACTION;-> SQLITE_OK
|
| db1: INSERT INTO test VALUES ( 1 );-> SQLITE_BUSY
| db2: INSERT INTO test VALUES ( 1 );-> SQLITE_BUSY
|
| db1: -> ROLLBACK  -> SQLITE_OK
| db2: -> ROLLBACK  -> SQLITE_OK
|
| and then again
|
| db1: BEGIN TRANSACTION;-> SQLITE_OK
| db2: BEGIN TRANSACTION;-> SQLITE_OK
|
| db1: INSERT INTO test VALUES ( 1 );-> SQLITE_BUSY
| db2: INSERT INTO test VALUES ( 1 );-> SQLITE_BUSY
|
| db1: -> ROLLBACK  -> SQLITE_OK
| db2: -> ROLLBACK  -> SQLITE_OK
Maybe a solution would be something like this:
All open transactions should have the same chance to commit. The first
transaction that commits, will win. After a transaction won, all other
transaction should return BUSY.
This will result to the following:
case a.)
db1: BEGIN TRANSACTION;  -> SQLITE_OK
db2: BEGIN TRANSACTION;  -> SQLITE_OK
db1: INSERT ...;  -> SQLITE_OK
db2: INSERT ...;  -> SQLITE_OK
db1: COMMIT;  -> SQLITE_OK
db2: COMMIT;  -> SQLITE_BUSY
case b.)
db1: BEGIN TRANSACTION;  -> SQLITE_OK
db2: BEGIN TRANSACTION;  -> SQLITE_OK
db2: INSERT ...;  -> SQLITE_OK
db2: COMMIT;  -> SQLITE_OK
db1: INSERT ...; -> SQLITE_BUSY
db1: ROLLBACK;   -> SQLITE_OK
case c.)
db1: BEGIN TRANSACTION;  -> SQLITE_OK
db2: BEGIN TRANSACTION;  -> SQLITE_OK
db2: INSERT ...;  -> SQLITE_OK
db1: INSERT ...;  -> SQLITE_OK
db1: ROLLBACK;-> SQLITE_OK
db2: COMMIT;  -> SQLITE_OK
case d.)
db1: BEGIN TRANSACTION;  -> SQLITE_OK
db2: BEGIN TRANSACTION;  -> SQLITE_OK
db1: SELECT ...;  -> SQLITE_OK
db2: INSERT ...;  -> SQLITE_OK
db2: COMMIT;  -> SQLITE_OK
db1: COMMIT;  -> SQLITE_OK
-BEGIN PGP SIGNATURE-
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFBGognSIrOxc3jOmoRAkm1AJ9NJb5GHanjL2kMCtVK4Wu7V7df6ACaA+IE
k6UiJvLi5U18REV6zTaXegs=
=vfJu
-END PGP SIGNATURE-

Re: [sqlite] SQLite DB synchronization

[John LeSueur]

John Oliva wrote:
I haven't worked out a synchronization scheme yet. One possibility is to
model it on how the Palm PDB databases are synchronized.  They keep
meta-data for each row indicating whether the data is dirty, new, has been
deleted, etc. This is optimized for a one-to-one pairing between two
databases.  A more complex process would be required to support
synchronization between members of a set of databases which all may be
synced.
Do you support uni-directional and bi-directional synchronization?
You mentioned that you create a hash on the contents of the row to produce
a GUID.  I presume this is only done when you first create the row,
otherwise any changes to the row data would change the GUID.  Correct?
John
 


Correct, the GUID is calculated on insert only. We support 
bi-directional synchronization between
multiple databases. Since we don't have dirty bits for each field, we 
only take the newest version during
any sync. So when changes are made on multiple databases between 
syncing, we lose all but the
newest change. For the application we are writing, it's unlikely that 
someone will change one field of a
row in one database and then change a different field in another. Like I 
said, there are some drawbacks.
I have yet to figure out how best to deal with deleting records. We have 
a delete flag, but with mutliple
copies of the database, it's not possible to just delete a record on the 
two syncing machines.

John LeSueur

Re: [sqlite] Deadlock when doing threaded updates and inserts

[Richard Boulton]

> > The point is that when two threads or
> > processes are trying to write at the same time, one of the two
> > must back off, abandon their transaction (using ROLLBACK) and let
> > the other proceed.
>
> And how can this be done? What if there are more threads involved? Who
> decides?
>

I found the document http://www.sqlite.org/lockingv3.html provides a very
good discussion of the locking for Version 3.

Only one thread can hold a PENDING lock, this is the thread that is trying
to commit it's transaction (i.e. get an EXCLUSIVE lock). Other threads
trying to update can only have SHARED locks and be trying to get RESERVED
locks. The change in 3.0.4 means that when using a busy handler (e.g.
sqlite3_busy_timeout) the threads trying to get RESERVED locks will not
retry, but instead will return immediately with SQLITE_BUSY. If these
transactions are rolled back the thread with the PENDING lock is free to
proceed when the busy handler retries the lock.

Re: [sqlite] Row Count

[tezozomoc]

BlankConsider doing a 

SELECT COUNT (*) FROM db WHERE etc

This should give you the number of rows before you... do the select again... Use where 
to match the condition.


Tezo.
  - Original Message - 
  From: Drew, Stephen 
  To: '[EMAIL PROTECTED]' 
  Sent: Wednesday, August 11, 2004 4:59 AM
  Subject: [sqlite] Row Count


  Hello,

  Is there any way to obtain the number of rows returned by a SELECT statement, before 
receiving all the rows returned?  I am using the sqlite_exec() / sqlite_step() 
functions to return the data, but need to populate a progress bar.

  Regards,
  Stephen Drew 

  DISCLAIMER: This e-mail and any files transmitted with it contain confidential and 
proprietary information and is intended solely for the use of the intended recipient. 
If you are not the intended recipient, please return the e-mail to the sender and 
delete it from your computer and you must not use, disclose, distribute, copy, print 
or rely on this e-mail. The contents of this e-mail and any files transmitted with it 
may have been changed or altered without the consent of the author. Email transmission 
cannot be guaranteed to be secure or error-free. 

Re: [sqlite] Deadlock when doing threaded updates and inserts

[Tito Ciuro]

Hello,
The point is that when two threads or
processes are trying to write at the same time, one of the two
must back off, abandon their transaction (using ROLLBACK) and let
the other proceed.
And how can this be done? What if there are more threads involved? Who 
decides?

-- Tito
On Aug 11, 2004, at 15:49, D. Richard Hipp wrote:
Paolo Vernazza wrote:
D. Richard Hipp wrote:
Dave Hayden wrote:
I'm running into a deadlock,
db1: BEGIN TRANSACTION;
db2: BEGIN TRANSACTION;
db1: INSERT INTO test VALUES ( 1 );
At this point, both of these return SQLITE_BUSY:
db2: UPDATE test SET num = 2 WHERE num = 1;
db1: END TRANSACTION;
Is this a bug? Or do I have to do something with sqlite 3 I didn't 
with 2?

After the db1 transaction ends, the db2 UPDATE should be able to
complete.  In version 2, db2 would have blocked when it tried to
begin the transaction.  Version 3 allows db2 to continue future,
but you still cannot have two threads changing the same database
at the same time, so it also eventually blocks.
Works as designed.
But db1 transaction never ends it will ever return SQLITE_BUSY!
Paolo
Oops.  The db1 should do a ROLLBACK, not a COMMIT.  Or db2 can
do an END TRANSACTION (since it never made any changes) and allow
db1 to complete instead.  The point is that when two threads or
processes are trying to write at the same time, one of the two
must back off, abandon their transaction (using ROLLBACK) and let
the other proceed.
In version 2, it was impossible for two threads to hold a
transaction at the same time, so this issue never came up.
Version 3 allows other threads to do read transactions while
one thread is doing a write transaction provided that the
read transactions all finish before the write transaction
commits.  If a reader tries to write, it gets SQLITE_BUSY.
If the writer tries to commit before all the readers finish,
it gets SQLITE_BUSY.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

Re: [sqlite] Database locked

[Felipe Lopes]

Thank you!! 

Was just wondering, does it escape the double quotes around the data?? 

Thanx again 

Felipe Lopes 

Em 11 Aug 2004, [EMAIL PROTECTED] escreveu: 

>Felipe Lopes wrote: 
>> 
>> I am trying to import a csv file (ip-to-country to be especific) to a 
>table 
>> on sqlite db. 
>> 
> 
>Version 2.8: 
> 
> COPY FROM ' ' USING DELIMITERS ','; 
> 
>Version 3.0: 
> 
> .mode csv 
> .import 
> 
>-- 
>D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 
> 
>-- 

_
Voce quer um iGMail protegido contra vírus e spams? 
Clique aqui: http://www.igmailseguro.ig.com.br
Ofertas imperdíveis! Link: http://www.americanas.com.br/ig/

[sqlite] Views and performances...

[Paolo Vernazza]

Hi,
I would like to know if using views instead of queries has some 
performance advantages.
If I must perform many times the same query (a select with a complex 
where clause), is it better to have a precompiled query or to create a 
view?

Thanks
Paolo

Re: [sqlite] Row Count

[Derrell . Lipman]

"Drew, Stephen" <[EMAIL PROTECTED]> writes:

> It's not a problem if I read the whole rowset into memory and then start
> sending it, but this can cause massive memory usage and time delays. It's
> not such a big deal if it's not possible, I'll just have to use a Microsoft
> style progress bar (i.e. one that keeps resetting). 

Or present the number of rows processed so far rather than a traditional
progress bar.  That way, you impress people with how fast it's processing
without resorting to what looks like a progress bar but really isn't (the MS
style).

Derrell

RE: [sqlite] Row Count

[Drew, Stephen]

Yes, I know it sounds a little optimistic :)

It's not a problem if I read the whole rowset into memory and then start
sending it, but this can cause massive memory usage and time delays. It's
not such a big deal if it's not possible, I'll just have to use a Microsoft
style progress bar (i.e. one that keeps resetting). 

 

-Original Message-
From: Will Leshner [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 11, 2004 4:46 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Row Count


On Aug 11, 2004, at 8:41 AM, Drew, Stephen wrote:

> This will be a big overhead on really small queries
>
> I need to be able to say roughly how many rows, but obviously the 
> exact number would be good.  Some queries are very time critical and 
> complex so I don't want to have to do a COUNT(*) on them.
>

So you would like a way to get a count of rows a SELECT will return 
without actually processing the SELECT?

[sqlite] SQLite 3 for Pocket PC

[Jakub Adamek]

Somebody mentioned here he/she has SQLite 3 working on Pocket PC. Could 
you please send the sources or diffs?

Thanks,
Jakub

Re: [sqlite] QNX and PPC

[Jakub Adamek]

Still not running on Power PC QNX. I tried to comment out locking in 
os.c and the test program works fine. So the problem is really hidden in 
the findLockInfo() & co. functions. Does somebody have some experience?

Thanks,
Jakub
Jakub Adamek wrote:
Thank you for your help. I am trying to cross-compile SQLite 2.8.15 on 
QNX-x86 to QNX-PPC. Is it possible to do so with some params for configure?

I tried to create the Makefile for QNX-x86 by just running "./configure" 
and to change it by hand:

TCC = qcc -g -O2 -V gcc_ntoppc -fno-inline -fno-pack-struct -EB 
-DOS_UNIX=1 -DOS_WIN=0 -DHAVE_USLEEP=1 -I. -I${TOP}/src

LTCOMPILE = $(LIBTOOL) --mode=compile --tag=CC $(TCC)
LTLINK = $(LIBTOOL) --mode=link --tag=CC $(TCC)
LTINSTALL = $(LIBTOOL) --mode=install --tag=CC $(INSTALL)
but the libtool linker failed with "/usr/bin/ntox86-ld: warning: 
powerpc:common architecture of input file '.libs/attach.o' is 
incompatible with i386 output'

thus I had to change the "libtool" script, replacing "gcc" with "qcc -V 
gcc_ntoppc". Which does not seem an elegant solution.

Jakub
Christian Smith wrote:
On Mon, 9 Aug 2004, Jakub Adamek wrote:

Hi Armin,
thank you for your notes. Could you please tell me more about "That's
the case"? Is it possible to use SQLite on PPC or not possible? Did you
hear about a big-endian port of SQLite?


I've used SQLite on SPARC, PowerPC and PA-RISC, all big endian, with no
problems.
The SQLite database file is platform independent, being endian agnostic
and 32/64 bit clean.
BTW, you may want to upgrade to a later version of SQLite, 2.8.15 being
the latest 2.x release.

Thanks,
Jakub
Armin Steinhoff wrote:

[EMAIL PROTECTED] schrieb am 06.08.04 19:40:55:

Hello everybody,


does someone have any experience with building for the QNX operating
system? And for the Power PC processor?


QNX 6.2.1 is a real-time Linux but it behaves strangely sometimes.

No .. it doesn't behave 'strangely" at all :)

It has not gcc but a qcc compiler.

qcc is only a frontend of the gcc ... so QNX has gcc.

Our testing program runs correctly on
the x86 PC. It creates a database, a table, inserts and selects data.
But the same program cross-compiled on the same computer for PPC 
ends at
the first "CREATE TABLE" statement with SQLITE_BUSY.


I am using SQLite 2.8.6. Is it possible that the little-or-big-endian
thing could cause this trouble?

That's the case ..
Regards
  Armin Steinhoff



Or do you have any other ideas?


Thank you very much,
Jakub



Aufnehmen, abschicken, nah sein - So einfach ist
WEB.DE Video-Mail: http://freemail.web.de/?mc=021200

RE: [sqlite] Row Count

[Drew, Stephen]

This will be a big overhead on really small queries

I need to be able to say roughly how many rows, but obviously the exact
number would be good.  Some queries are very time critical and complex so I
don't want to have to do a COUNT(*) on them.

-Original Message-
From: Scott Baker [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 11, 2004 4:10 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Row Count

SELECT count(*) FROM Table WHERE Foo = 'bar';



Drew, Stephen wrote:
> Hello,
>  
> Is there any way to obtain the number of rows returned by a SELECT 
> statement, before receiving all the rows returned?  I am using the
> sqlite_exec() / sqlite_step() functions to return the data, but need 
> to populate a progress bar.
>  
> Regards,
> Stephen Drew

Re: [sqlite] SQLite DB synchronization

[John Oliva]

I haven't worked out a synchronization scheme yet. One possibility is to
model it on how the Palm PDB databases are synchronized.  They keep
meta-data for each row indicating whether the data is dirty, new, has been
deleted, etc. This is optimized for a one-to-one pairing between two
databases.  A more complex process would be required to support
synchronization between members of a set of databases which all may be
synced.

Do you support uni-directional and bi-directional synchronization?

You mentioned that you create a hash on the contents of the row to produce
a GUID.  I presume this is only done when you first create the row,
otherwise any changes to the row data would change the GUID.  Correct?

John

> John Oliva wrote:
>
>>Are any of you performing synchronization between multiple SQLite DBs?
>>What tools or techniques are you using?
>>
>>Thanks,
>>John Oliva
>>
>>
> That's something I'd be interested in as well.  At the moment, we are
> using a system where each table to be synchronized has at least the
> following fields:
>
> guid (a global unique identifier, composed of a md5 hash of all the
> contained data in the row plus a couple other random bits)
> date_added
> date_modified
>
> Using these, we can sync updates and inserts across database instances.
> Unfortunately, there doesn't seem to be a simple method of deleting a
> record and then syncing.
> This is obviously a pretty simplistic way to do this, so any other
> suggestions I would find helpful.
>
> John LeSueur
>

Re: [sqlite] Row Count

[Scott Baker]

SELECT count(*) FROM Table WHERE Foo = 'bar';

Drew, Stephen wrote:
Hello,
 
Is there any way to obtain the number of rows returned by a SELECT
statement, before receiving all the rows returned?  I am using the
sqlite_exec() / sqlite_step() functions to return the data, but need to
populate a progress bar.
 
Regards,
Stephen Drew 

Re: [sqlite] Deadlock when doing threaded updates and inserts

[Paolo Vernazza]

D. Richard Hipp wrote:
Paolo Vernazza wrote:
D. Richard Hipp wrote:
Dave Hayden wrote:
I'm running into a deadlock,
db1: BEGIN TRANSACTION;
db2: BEGIN TRANSACTION;
db1: INSERT INTO test VALUES ( 1 );
At this point, both of these return SQLITE_BUSY:
db2: UPDATE test SET num = 2 WHERE num = 1;
db1: END TRANSACTION;
Is this a bug? Or do I have to do something with sqlite 3 I didn't 
with 2?

After the db1 transaction ends, the db2 UPDATE should be able to
complete.  In version 2, db2 would have blocked when it tried to
begin the transaction.  Version 3 allows db2 to continue future,
but you still cannot have two threads changing the same database
at the same time, so it also eventually blocks.
Works as designed.

But db1 transaction never ends it will ever return SQLITE_BUSY!
Paolo
Oops.  The db1 should do a ROLLBACK, not a COMMIT.  Or db2 can
do an END TRANSACTION (since it never made any changes) and allow
db1 to complete instead.  The point is that when two threads or
processes are trying to write at the same time, one of the two
must back off, abandon their transaction (using ROLLBACK) and let
the other proceed. 
But doing in that way, you can have this behaviour (and this is what 
happends to me):

db1: BEGIN TRANSACTION;-> SQLITE_OK
db2: BEGIN TRANSACTION;-> SQLITE_OK
db1: INSERT INTO test VALUES ( 1 );-> SQLITE_BUSY
db2: INSERT INTO test VALUES ( 1 );-> SQLITE_BUSY
db1: -> ROLLBACK  -> SQLITE_OK
db2: -> ROLLBACK  -> SQLITE_OK
and then again
db1: BEGIN TRANSACTION;-> SQLITE_OK
db2: BEGIN TRANSACTION;-> SQLITE_OK
db1: INSERT INTO test VALUES ( 1 );-> SQLITE_BUSY
db2: INSERT INTO test VALUES ( 1 );-> SQLITE_BUSY
db1: -> ROLLBACK  -> SQLITE_OK
db2: -> ROLLBACK  -> SQLITE_OK
Paolo

[sqlite] Row Count

[Drew, Stephen]

Hello,
 
Is there any way to 
obtain the number of rows returned by a SELECT statement, before receiving all 
the rows returned?  I am using the sqlite_exec() / sqlite_step() functions 
to return the data, but need to populate a progress bar.
 
Regards,Stephen Drew 
DISCLAIMER: This e-mail and any files transmitted with it 
contain confidential and proprietary information and is intended solely for the 
use of the intended recipient. If you are not the intended recipient, please 
return the e-mail to the sender and delete it from your computer and you must 
not use, disclose, distribute, copy, print or rely on this e-mail. The contents 
of this e-mail and any files transmitted with it may have been changed or 
altered without the consent of the author. Email transmission cannot be 
guaranteed to be secure or error-free. 
 

Re: [sqlite] Database locked

[D. Richard Hipp]

Brass Tilde wrote:
Version 2.8:
   COPY  FROM '' USING DELIMITERS ',';

You weren't a Clipper/XBase programmer at one time, were you?  :->

Never.  The COPY command in version 2.8 was taken from PostgreSQL.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

Re: [sqlite] Converting 2.8.x database file into 3.x database file

[D. Richard Hipp]

[EMAIL PROTECTED] wrote:
In the webpage (http://www.sqlite.org/version3.html), it is mentioned that 
for converting the 2.8.x database file into 3.0 database file, the command 
line shell is available both in 2.8 and 3.0. I find that the command as 
mentioned there works with 2.8 executable. But I tried it with the 3.0 
(sqlite3) application and I get error saying that the database file is 
encypted. 
Reread the command carefully.  The command line uses *both* version 2
and version 3 shells at the same time.
Please reply asap.
Sankara Narayanan
Philips Innovation Campus
So is your group the one that designed the Philips MP3 player
(model HDD060) that incorporates SQLite that Nils Faerber told
us about a couple of months ago?
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

Re: [sqlite] Deadlock when doing threaded updates and inserts

[D. Richard Hipp]

Dave Hayden wrote:
I'm running into a deadlock, as the subject says, when doing updates on 
a table in one thread while another thread is inserting into the same 
table. (Oh, and this is on 3.0.4, compiled with --enable-threadsafe)

The update thread returns from its UPDATE command (within a transaction) 
with SQLITE_BUSY when it sees a pending lock. The insert thread returns 
SQLITE_BUSY from END TRANSACTION when it can't get an exclusive lock.

Attached is a simple C program that demonstrates this. I open two 
database handles on the same file (with a table "test" with a single 
column "num") and do:

db1: BEGIN TRANSACTION;
db2: BEGIN TRANSACTION;
db1: INSERT INTO test VALUES ( 1 );
At this point, both of these return SQLITE_BUSY:
db2: UPDATE test SET num = 2 WHERE num = 1;
db1: END TRANSACTION;
Is this a bug? Or do I have to do something with sqlite 3 I didn't with 2?
After the db1 transaction ends, the db2 UPDATE should be able to
complete.  In version 2, db2 would have blocked when it tried to
begin the transaction.  Version 3 allows db2 to continue future,
but you still cannot have two threads changing the same database
at the same time, so it also eventually blocks.
Works as designed.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

Re: [sqlite] Database locked

[Brass Tilde]

> > I am trying to import a csv file (ip-to-country to be especific) to a
table
> > on sqlite db.
> >
>
> Version 2.8:
>
> COPY  FROM '' USING DELIMITERS ',';

You weren't a Clipper/XBase programmer at one time, were you?  :->