Re: [sqlite] Database design and SQLite

[michael munson]

Thanks, I'll pick it up.



The first thing to ask yourself here is whether or not it makes sense to
use a database.  Certainly that makes sense if you need concurrent read
and write access, but if you only need to write from one source at a
time an XML file sounds more like what you need.  It supports your need
for infinite hierarchy, and if you work without a DTD it allows setting
of any properties that you would like.  If you don't like the general
bloat of an XML file you can make use of a library like libxml2
(http://www.xmlsoft.org) which natively and transparently supports
compressed XML files.

If you're absolutely sold on the need that this be in a database, buy a
copy of Joe Selko's _SQL for Smarties_.  It covers these hierarchical
structures in great detail.  Even after implementing this kind of
structure before I wouldn't try it again without consulting Selko's book.

Clay Dowling

Re: [sqlite] Quick question about locking

[Clay Dowling]

JD Smith wrote:

An old associate of mine wrote a quick and dirty C++ SQLite wrapper when he 
started using SQLite.  He passed me a copy of it to test out a few years ago 
and I've been using it and SQLite when I need database access.
Somehow, up until now, I never ran across this particular problem... I 
perform a query and then, while iterating through the result set, do updates to 
the database based upon some of the things I see in that query.  It is saying 
that the database is locked and thus kicks me back.
Is it normal that a query locks the database for writing?  Below I will 
include some of the pertinent code... I'm just a bit confused.


JD,

This is perfectly normal.  See http://www.sqlite.org/lockingv3.html for 
an explanation that might clear things up for you.


Although it's not ideal, you have a couple of solutions.  First, you 
could do your calculations and updates entirely in SQL, although that's 
potentially a major pain in the tail.  The alternative is that you store 
you updates outside of the database until you have completed your read. 
 Once you have gone through the data to figure out what updates are 
necessary, you then perform the updates based on your cached data.


Clay Dowling
--
http://www.lazarusid.com/notes/
Lazarus Notes
Articles and Commentary on Web Development

[sqlite] Quick question about locking

[JD Smith]

An old associate of mine wrote a quick and dirty C++ SQLite wrapper when he 
started using SQLite.  He passed me a copy of it to test out a few years ago 
and I've been using it and SQLite when I need database access.
Somehow, up until now, I never ran across this particular problem... I 
perform a query and then, while iterating through the result set, do updates to 
the database based upon some of the things I see in that query.  It is saying 
that the database is locked and thus kicks me back.
Is it normal that a query locks the database for writing?  Below I will 
include some of the pertinent code... I'm just a bit confused.

RS rs;

try {
CPPSQLite::Instance()->Query(rs, "SELECT * FROM org_data");

while (rs.MoveNext()) {
// stuff snipped
long balance = atol(rs.GetString("balance").c_str());
// stuff snipped
sprintf(buf, "UPDATE org_data SET standing = %d WHERE id = %d", 
standing, oid);
CPPSQLite::Instance()->Execute(buf);
}
} catch (CPPSQLiteException) {
logs("org_update: %s", ex.ToString().c_str());
}

Upon reaching the Execute statement it throws an exceptation saying the 
database is locked.  Is it normal for a read to lock the database for 
operating, or is his wrapper doing something odd?  If it's doing something 
wrong, I can paste the related code into another mail and hopefully someone can 
help point it out for me.  I really appreciate the time spent reading this!

Cordially,

JD Smith

Re: [sqlite] Database design and SQLite

[Clay Dowling]

michael munson wrote:
> Greetings,
> I'm a bit new to SQL and SQLite so pardon me if I ask silly questions but I 
> have run into a bit of a wall while attempting to design a database for a C++ 
> program I am attempting to write.
> 
> The needs of the database are to: represent an object oriented hierarchy of 
> any number of objects, where each object may have custom properties of 
> several different datatypes and permission bits.
> 
> The table fields that I have so far are a primary integer key (ID), text 
> representing the object's name, (NAME), and two fields "OWNER" and "PARENT" 
> which are integers representing other objects in this table.
> 
> The problem I am running into is the object hierarchy and custom properties. 
> For example: If object #1 is a parent of object #2, and #1 has a property 
> named "location" then #2 should also have that property (although the value 
> and permissions may be different from the parent).
> 
> I'm trying to see if I can do this some way I do not currently know how, 
> because the only thing I can think of is some delimited BLOB and recalculate 
> all the parent properties whenever the parent is changed which I imagine may 
> significently slow down my database.

The first thing to ask yourself here is whether or not it makes sense to
use a database.  Certainly that makes sense if you need concurrent read
and write access, but if you only need to write from one source at a
time an XML file sounds more like what you need.  It supports your need
for infinite hierarchy, and if you work without a DTD it allows setting
of any properties that you would like.  If you don't like the general
bloat of an XML file you can make use of a library like libxml2
(http://www.xmlsoft.org) which natively and transparently supports
compressed XML files.

If you're absolutely sold on the need that this be in a database, buy a
copy of Joe Selko's _SQL for Smarties_.  It covers these hierarchical
structures in great detail.  Even after implementing this kind of
structure before I wouldn't try it again without consulting Selko's book.

Clay Dowling

Re: [sqlite] Database design and SQLite

[Teg]

Hello michael,

If I was doing that, I'd have another table of nothing but "properties".
Each property would have an integer that represents which object the
property belongs to. In that way, there's no limit to the number of
properties you can assign to an object. In the case of your
parent/child relationships, you're probably going to have to pull the
records all the way up the chain and decide which one overrides the
other programmatically.

I don't think the child necessarily has to have the same list of
properties as the parent (though this depends on your design) I was
thinking the child's property might be the union of the parent and
child's properties with whatever policy you use to resolve cases where
they both have the same property but, different values.

I'm not really an SQL expert either, but in cases where you have an
indeterminate number of values for an item, I tend to think vertical
table structure instead of having a bunch of columns you might or
might not use.

C



Saturday, January 14, 2006, 12:35:19 PM, you wrote:

mm> Greetings,
mm> I'm a bit new to SQL and SQLite so pardon me if I ask silly
mm> questions but I have run into a bit of a wall while attempting to
mm> design a database for a C++ program I am attempting to write.

mm> The needs of the database are to: represent an object
mm> oriented hierarchy of any number of objects, where each object may
mm> have custom properties of several different datatypes and
mm> permission bits.

mm> The table fields that I have so far are a primary integer key
mm> (ID), text representing the object's name, (NAME), and two fields
mm> "OWNER" and "PARENT" which are integers representing other objects
mm> in this table.

mm> The problem I am running into is the object hierarchy and
mm> custom properties. For example: If object #1 is a parent of object
mm> #2, and #1 has a property named "location" then #2 should also
mm> have that property (although the value and permissions may be
mm> different from the parent).

mm> I'm trying to see if I can do this some way I do not
mm> currently know how, because the only thing I can think of is some
mm> delimited BLOB and recalculate all the parent properties whenever
mm> the parent is changed which I imagine may significently slow down
mm> my database.

mm> Opinions?

mm> Regards,
mm> Michael Munson



-- 
Best regards,
 Tegmailto:[EMAIL PROTECTED]

[sqlite] Database design and SQLite

[michael munson]

Greetings,
I'm a bit new to SQL and SQLite so pardon me if I ask silly questions but I 
have run into a bit of a wall while attempting to design a database for a C++ 
program I am attempting to write.

The needs of the database are to: represent an object oriented hierarchy of any 
number of objects, where each object may have custom properties of several 
different datatypes and permission bits.

The table fields that I have so far are a primary integer key (ID), text 
representing the object's name, (NAME), and two fields "OWNER" and "PARENT" 
which are integers representing other objects in this table.

The problem I am running into is the object hierarchy and custom properties. 
For example: If object #1 is a parent of object #2, and #1 has a property named 
"location" then #2 should also have that property (although the value and 
permissions may be different from the parent).

I'm trying to see if I can do this some way I do not currently know how, 
because the only thing I can think of is some delimited BLOB and recalculate 
all the parent properties whenever the parent is changed which I imagine may 
significently slow down my database.

Opinions?

Regards,
Michael Munson

Re: [sqlite] Windows threading help needed. Was: Thread handling in Windows

[Teg]

Hello drh,

Your mail server was unhappy about the attachments.

C

Saturday, January 14, 2006, 9:51:46 AM, you wrote:

dhc> Teg <[EMAIL PROTECTED]> wrote:
>> 
>> To me the proper solution would be to avoid using TLS in the first
>> place since, you (sqlite) neither create nor destroy the thread.
>> 

dhc> I'm trying to move in this direction - to eliminate the need
dhc> for TLS entirely.  But in order to do so I am going to need
dhc> some additional thread locking primitives.  The standard
dhc> pthreads library on Unix provides everything I need.  But
dhc> locking primitives on windows seem to be - well - more
dhc> "primitive".  I'm looking for suggestions on how to implement 
dhc> the features I need on windows.  All hints are appreciated.

dhc> I have consulted the pthreads-win32 library (an open-source 
dhc> implementaton of pthreads for windows systems.)  They clearly
dhc> get all of this to work.  But it is a lot of code.  I'm 
dhc> wondering if there isn't an easier way.

dhc> In summary, what I need is a recursive mutex.  Here are the
dhc> requirements:

dhc>   (1)  An abstract (a.k.a opaque) type RecursiveMutex that can
dhc>be an element of a structure.

dhc>   (2)  Procedures InitializeRecursiveMutex() and 
dhc>DestroyRecursiveMutex() used to initialize and clear
dhc>RecursiveMutex elements when they are allocated and
dhc>deallocated.

dhc>   (3)  Procedures EnterRecursiveMutex() and LeaveRecursiveMutex().
dhc>Only one thread at a time is allowed in a mutex, though
dhc>the same thread can enter a mutex multiple times (hence
dhc>the "recursive" in the name.)  If a thread enters a mutex
dhc>multiple times, it must also leave the same number of times
dhc>before another thread can enter.  A call to EnterRecursiveMutex()
dhc>will block if a different thread is already in the mutex.
dhc>Execution will resume after the other thread leaves.

dhc>   (4)  Procedure TryRecursiveMutex().  This is similar to 
dhc>EnterRecursiveMutex() except that it does not block.  If
dhc>entry into the mutex is allowed, TryRecursiveMutex() enters
dhc>the mutex and returns TRUE.  If another thread is holding 
dhc>the mutex, then TryRecuriveMutex() returns FALSE.

dhc> Thanks in advance for your help.

dhc> --
dhc> D. Richard Hipp <[EMAIL PROTECTED]>


#ifdef _WIN32

void*   InitializeRecursiveMutex(void);

void EnterRecursiveMutex(void* pHandle);

void LeaveRecursiveMutex(void* pHandle);

int TryEnterRecursiveMutex(void* pHandle);

void DestroyRecursiveMutex(void* pHandle);

#endif // WIN32



#include 

#ifdef _WIN32

#include 
#include 
#include 
#include "SqliteMutex.h"



#ifdef DOCS
dhc>   (1)  An abstract (a.k.a opaque) type RecursiveMutex that can
dhc>be an element of a structure.

dhc>   (2)  Procedures InitializeRecursiveMutex() and 
dhc>DestroyRecursiveMutex() used to initialize and clear
dhc>RecursiveMutex elements when they are allocated and
dhc>deallocated.

dhc>   (3)  Procedures EnterRecursiveMutex() and LeaveRecursiveMutex().
dhc>Only one thread at a time is allowed in a mutex, though
dhc>the same thread can enter a mutex multiple times (hence
dhc>the "recursive" in the name.)  If a thread enters a mutex
dhc>multiple times, it must also leave the same number of times
dhc>before another thread can enter.  A call to EnterRecursiveMutex()
dhc>will block if a different thread is already in the mutex.
dhc>Execution will resume after the other thread leaves.

dhc>   (4)  Procedure TryRecursiveMutex().  This is similar to 
dhc>EnterRecursiveMutex() except that it does not block.  If
dhc>entry into the mutex is allowed, TryRecursiveMutex() enters
dhc>the mutex and returns TRUE.  If another thread is holding 
dhc>the mutex, then TryRecuriveMutex() returns FALSE.

dhc> Thanks in advance for your help.

dhc> --
dhc> D. Richard Hipp <[EMAIL PROTECTED]

#endif

void*   InitializeRecursiveMutex(void)
{
//
// NULL - Security contect
// 0- Not initial owner
// NULL - Not assigning a name to the mutex
//
HANDLE hMutex = CreateMutex(NULL,0,NULL);
return((void*)hMutex);
}

void EnterRecursiveMutex(void* pHandle)
{
//
// Wait on the mutex for an infinite period
//
WaitForSingleObject((HANDLE)pHandle,INFINITE);
}

void LeaveRecursiveMutex(void* pHandle)
{
ReleaseMutex((HANDLE)pHandle);
}

int TryEnterRecursiveMutex(void* pHandle)
{
//
// Wait on the mutex for a 0 period. We either get the lock immediately
// or fail out
//
int nRetval = WaitForSingleObject((HANDLE)pHandle,0);
if( nRetval == WAIT_TIMEOUT )
{
return(-1);
}
return(0);
}


void DestroyRecursiveMutex(void* pHandle)
{

Re: [sqlite] Windows threading help needed. Was: Thread handling in Windows

[Teg]

Hello drh,

Like Igor, I like the critical section, the problem is that pesky
"Try" function which doesn't exist in older windows versions. The
mutex though lets you try by attempting the lock with a timeout. I'm a
little leery of the timeout in the "Try", I think it smells a bit of a
race condition.

So, if you're going to abandon 98 and ME, I'd use Igor's idea. The
critical section is faster than the mutex too.

The attached files are something I whipped out by pulling apart one of
my mutex classes. It seems too simple to get wrong but, I didn't test
them other then getting them to compile. Microsoft claims that the
Mutex is re-enterable from the same thread.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dllproc/base/tryentercriticalsection.asp

If the attachment doesn't work, I can embed them in an email. They're
pretty small.

C


Saturday, January 14, 2006, 9:51:46 AM, you wrote:

dhc> Teg <[EMAIL PROTECTED]> wrote:
>> 
>> To me the proper solution would be to avoid using TLS in the first
>> place since, you (sqlite) neither create nor destroy the thread.
>> 

dhc> I'm trying to move in this direction - to eliminate the need
dhc> for TLS entirely.  But in order to do so I am going to need
dhc> some additional thread locking primitives.  The standard
dhc> pthreads library on Unix provides everything I need.  But
dhc> locking primitives on windows seem to be - well - more
dhc> "primitive".  I'm looking for suggestions on how to implement 
dhc> the features I need on windows.  All hints are appreciated.

dhc> I have consulted the pthreads-win32 library (an open-source 
dhc> implementaton of pthreads for windows systems.)  They clearly
dhc> get all of this to work.  But it is a lot of code.  I'm 
dhc> wondering if there isn't an easier way.

dhc> In summary, what I need is a recursive mutex.  Here are the
dhc> requirements:

dhc>   (1)  An abstract (a.k.a opaque) type RecursiveMutex that can
dhc>be an element of a structure.

dhc>   (2)  Procedures InitializeRecursiveMutex() and 
dhc>DestroyRecursiveMutex() used to initialize and clear
dhc>RecursiveMutex elements when they are allocated and
dhc>deallocated.

dhc>   (3)  Procedures EnterRecursiveMutex() and LeaveRecursiveMutex().
dhc>Only one thread at a time is allowed in a mutex, though
dhc>the same thread can enter a mutex multiple times (hence
dhc>the "recursive" in the name.)  If a thread enters a mutex
dhc>multiple times, it must also leave the same number of times
dhc>before another thread can enter.  A call to EnterRecursiveMutex()
dhc>will block if a different thread is already in the mutex.
dhc>Execution will resume after the other thread leaves.

dhc>   (4)  Procedure TryRecursiveMutex().  This is similar to 
dhc>EnterRecursiveMutex() except that it does not block.  If
dhc>entry into the mutex is allowed, TryRecursiveMutex() enters
dhc>the mutex and returns TRUE.  If another thread is holding 
dhc>the mutex, then TryRecuriveMutex() returns FALSE.

dhc> Thanks in advance for your help.

dhc> --
dhc> D. Richard Hipp <[EMAIL PROTECTED]>




-- 
Best regards,
 Tegmailto:[EMAIL PROTECTED]#ifdef _WIN32

void*   InitializeRecursiveMutex(void);

void EnterRecursiveMutex(void* pHandle);

void LeaveRecursiveMutex(void* pHandle);

int TryEnterRecursiveMutex(void* pHandle);

void DestroyRecursiveMutex(void* pHandle);

#endif // WIN32


#include 

#ifdef _WIN32

#include 
#include 
#include 
#include "SqliteMutex.h"



#ifdef DOCS
dhc>   (1)  An abstract (a.k.a opaque) type RecursiveMutex that can
dhc>be an element of a structure.

dhc>   (2)  Procedures InitializeRecursiveMutex() and 
dhc>DestroyRecursiveMutex() used to initialize and clear
dhc>RecursiveMutex elements when they are allocated and
dhc>deallocated.

dhc>   (3)  Procedures EnterRecursiveMutex() and LeaveRecursiveMutex().
dhc>Only one thread at a time is allowed in a mutex, though
dhc>the same thread can enter a mutex multiple times (hence
dhc>the "recursive" in the name.)  If a thread enters a mutex
dhc>multiple times, it must also leave the same number of times
dhc>before another thread can enter.  A call to EnterRecursiveMutex()
dhc>will block if a different thread is already in the mutex.
dhc>Execution will resume after the other thread leaves.

dhc>   (4)  Procedure TryRecursiveMutex().  This is similar to 
dhc>EnterRecursiveMutex() except that it does not block.  If
dhc>entry into the mutex is allowed, TryRecursiveMutex() enters
dhc>the mutex and returns TRUE.  If another thread is holding 
dhc>the mutex, then TryRecuriveMutex() returns FALSE.

dhc> Thanks in advance for your help.

dhc> --
dhc> D. Richard Hipp <[EMAIL PROTECTED]

#endif

void*   InitializeRecursiveMutex(void)
{
//
// NULL 

Re: [sqlite] Optimizing for space and speed

[Dan Kennedy]

--- Martin O'Leary <[EMAIL PROTECTED]> wrote:

> Hi guys,
> 
> I have a table like the following:
> 
> CREATE TABLE user_actions (
> uid INTEGER NOT NULL,
> actionid INTEGER NOT NULL,
> time INTEGER NOT NULL,
> status INTEGER NOT NULL,
> PRIMARY KEY (uid, actionid, time, status)
> );
> 
> And I want to carry out a query something like this:
> 
> SELECT uid, actionid, MAX(time), status FROM user_actions GROUP BY
> uid, actionid;
> 
> i.e. finding the last time each user performed each action (numbers of
> users and distinct actions are small in comparison to number of times
> each user performs each action).
> 
> Here, I find two problems. Firstly, because my table doesn't have an
> INTEGER PRIMARY KEY, I get an autogenerated index on my compound key.
> No problem there. However, and I may be missing something, it seems
> that there's a lot of data duplication going on. All the data from my
> main table is available in the index (status shouldn't really be part
> of the key, but I added it in order to increase query performance). As
> far as I can tell, this means that the main table is never consulted,
> but just sits there, doubling the size of my database. Is there any
> way around this?

You can't do anything about that unfortunately. 

> Secondly, query performance is quite slow. It seems to me that no
> optimisation is being carried out on the MAX(time) expression. Is this
> the case, and if so, why not? Surely it's possible to do this in a
> nice, logarithmic way.

New versions of SQLite should execute this query in O(N) time. Are you
seeing otherwise? I don't really see how you could improve on that 
given the query and index.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

[sqlite] (More Info) problem using triggers to update primary key on FK type relationship

[RCS Computers]

I am using SQLite 2.8.14.  I realize I should upgrade, but I using it 
with PHP and would need to upgrade to PDO first, which be a lot more 
work than the triggers I need help with :).


--
--
Randy Syring
RCS Computers
502-896-4143
www.rcs-comp.com

"Whether, then, you eat or drink or 
whatever you do, do all to the glory

of God." 1 Cor 10:31

[sqlite] Windows threading help needed. Was: Thread handling in Windows

[drh]

Teg <[EMAIL PROTECTED]> wrote:
> 
> To me the proper solution would be to avoid using TLS in the first
> place since, you (sqlite) neither create nor destroy the thread.
> 

I'm trying to move in this direction - to eliminate the need
for TLS entirely.  But in order to do so I am going to need
some additional thread locking primitives.  The standard
pthreads library on Unix provides everything I need.  But
locking primitives on windows seem to be - well - more
"primitive".  I'm looking for suggestions on how to implement 
the features I need on windows.  All hints are appreciated.

I have consulted the pthreads-win32 library (an open-source 
implementaton of pthreads for windows systems.)  They clearly
get all of this to work.  But it is a lot of code.  I'm 
wondering if there isn't an easier way.

In summary, what I need is a recursive mutex.  Here are the
requirements:

  (1)  An abstract (a.k.a opaque) type RecursiveMutex that can
   be an element of a structure.

  (2)  Procedures InitializeRecursiveMutex() and 
   DestroyRecursiveMutex() used to initialize and clear
   RecursiveMutex elements when they are allocated and
   deallocated.

  (3)  Procedures EnterRecursiveMutex() and LeaveRecursiveMutex().
   Only one thread at a time is allowed in a mutex, though
   the same thread can enter a mutex multiple times (hence
   the "recursive" in the name.)  If a thread enters a mutex
   multiple times, it must also leave the same number of times
   before another thread can enter.  A call to EnterRecursiveMutex()
   will block if a different thread is already in the mutex.
   Execution will resume after the other thread leaves.

  (4)  Procedure TryRecursiveMutex().  This is similar to 
   EnterRecursiveMutex() except that it does not block.  If
   entry into the mutex is allowed, TryRecursiveMutex() enters
   the mutex and returns TRUE.  If another thread is holding 
   the mutex, then TryRecuriveMutex() returns FALSE.

Thanks in advance for your help.

--
D. Richard Hipp <[EMAIL PROTECTED]>

[sqlite] Re: Best possible concurrency

[Igor Tandetnik]

Doug Nebeker <[EMAIL PROTECTED]>
wrote:

I have a small number of read and writer threads.  A few months ago I
had some deadlock issues and one simple solution was to run all
transactions as exclusive (ie BEGIN EXCLUSIVE).  That works
perfectly--no hint of deadlock.  But now that I've been using it for a
few months, I'm starting to feel the pain of serializing all calls to
the database, particularly the readers which previously were able to
overlap.


If you know up front which transactions are readers-only and which would 
write, you could start readers with plain BEGIN and only writers with 
BEGIN EXCLUSIVE. Also, BEGIN EXCLUSIVE may be too drastic - BEGIN 
IMMEDIATE prevents deadlocks too, and does not block readers. The 
difference between IMMEDIATE and EXCLUSIVE is that with the former, you 
may get SQLITE_BUSY (but not the deadlock kind) at any place in the 
middle of the transaction and will have to implement "wait and retry" 
logic. With EXCLUSIVE, you can only get SQLITE_BUSY for the BEGIN 
statement itself - once it succeeds, you'll never get SQLITE_BUSY to the 
end of the transaction (but of course everyone else will).



I've read through most of the documentation but haven't quite found
the
best way to increase concurrency.  I understand that SQLITE_BUSY is
returned when a statement can't be executed because another thread has
temporarily locked the database.  What I'm trying to do is figure out
when I need to just wait and try again (my earlier approach which
works
great if deadlock isn't the underlying cause) or let go of the current
resources and then try again (so that the other transaction can
complete).


My understanding of the previous discussions is as follows. You should 
register a busy handler with sqlite3_busy_handler. SQLite encounters a 
lock that it believes would clear eventually, it would call your handler 
and, if the handler returns zero, return SQLITE_BUSY to the caller. If 
however SQLite encounters a deadlock, it will return SQLITE_BUSY without 
calling the busy handler. This is your cue that retrying is futile.



And naturally, in the second case, it would be nice for
both
transaction to not let go since one of them (assuming only two are
participating in the deadlock) could run to completion once the other
lets go.


I don't think both transactions will get the deadlock cue. The deadlock 
occurs when the writer waits for the reader to clear, while the reader 
tries to become a writer. Only the latter case is diagnosed as a 
deadlock: whoever manages to become writer first will get a regular busy 
signal.



So my questions boil down to:
1. When SQLITE_BUSY is returned, is it possible to tell if a deadlock
state has been reached?


Yes, with a careful coordination between the calling code and a busy 
handler.



2. If I need to 'let go' of the resources/current transaction, do I:
sqlite3_finalize on the current statement which returned
SQLITE_BUSY


sqlite3_reset would be sufficient. After sqlite3_finalize you will have 
to prepare the statement again and bind all parameters.



sqlite3_prepare("ROLLBACK TRANSACTION")
sqlite3_step
sqlite3_finalize
...wait... and then try the statement again?


I think you should reset the failed statement before you run ROLLBACK


3. Is there any kind of algorithm I could use which would direct one
thread in a deadlock to release while the other tries again (anything
in
the API that would give a hint??).


See above. In SQLite, deadlock is asymmetric. If coded correctly, you 
should not get into a livelock situation.



4. When can sqlite3_prepare return SQLITE_BUSY?


Never, as far as I can tell.

Igor Tandetnik 

[sqlite] Re: bug in NOT EXISTS?

[Igor Tandetnik]

Miha Vrhovnik wrote:

using sqlite 3.2.8.

CREATE TABLE movies (id INTEGER PRIMARY KEY, catalogIDNumeric
INTEGER);
INSERT INTO movies (catalogIDNumeric) VALUES(1);
INSERT INTO movies (catalogIDNumeric) VALUES(2);
INSERT INTO movies (catalogIDNumeric) VALUES(3);
INSERT INTO movies (catalogIDNumeric) VALUES(5);
INSERT INTO movies (catalogIDNumeric) VALUES(7);
INSERT INTO movies (catalogIDNumeric) VALUES(8);

CREATE TEMP TABLE tmpCatalogID (catalogIDNumeric INTEGER);
INSERT INTO tmpCatalogID VALUES(1);
INSERT INTO tmpCatalogID VALUES(2);
INSERT INTO tmpCatalogID VALUES(3);
INSERT INTO tmpCatalogID VALUES(4);
INSERT INTO tmpCatalogID VALUES(5);
INSERT INTO tmpCatalogID VALUES(6);
INSERT INTO tmpCatalogID VALUES(7);
INSERT INTO tmpCatalogID VALUES(8);

query returns empty resultset instead of number 4:
SELECT MIN(catalogIDNumeric) FROM tmpCatalogID WHERE NOT EXISTS
(SELECT catalogIDNumeric FROM movies);


Why should it return number 4? The subquery under NOT EXISTS very 
obviously returns a non-empty set (movies table is not empty). Perhaps 
you meant


SELECT MIN(catalogIDNumeric) FROM tmpCatalogID tmp
WHERE NOT EXISTS (
   SELECT * FROM movies m
   where m.catalogIDNumeric = tmp.catalogIDNumeric
);

Igor Tandetnik 

[sqlite] bug in NOT EXISTS?

[Miha Vrhovnik]

using sqlite 3.2.8.

CREATE TABLE movies (id INTEGER PRIMARY KEY, catalogIDNumeric INTEGER);
INSERT INTO movies (catalogIDNumeric) VALUES(1);
INSERT INTO movies (catalogIDNumeric) VALUES(2);
INSERT INTO movies (catalogIDNumeric) VALUES(3);
INSERT INTO movies (catalogIDNumeric) VALUES(5);
INSERT INTO movies (catalogIDNumeric) VALUES(7);
INSERT INTO movies (catalogIDNumeric) VALUES(8);

CREATE TEMP TABLE tmpCatalogID (catalogIDNumeric INTEGER);
INSERT INTO tmpCatalogID VALUES(1);
INSERT INTO tmpCatalogID VALUES(2);
INSERT INTO tmpCatalogID VALUES(3);
INSERT INTO tmpCatalogID VALUES(4);
INSERT INTO tmpCatalogID VALUES(5);
INSERT INTO tmpCatalogID VALUES(6);
INSERT INTO tmpCatalogID VALUES(7);
INSERT INTO tmpCatalogID VALUES(8);

query returns empty resultset instead of number 4:
SELECT MIN(catalogIDNumeric) FROM tmpCatalogID WHERE NOT EXISTS (SELECT 
catalogIDNumeric FROM movies);

thus
SELECT MIN(catalogIDNumeric) FROM tmpCatalogID WHERE EXISTS (SELECT 
catalogIDNumeric FROM movies);

returns 1.

For the time beeing I rowrote query so it uses IN keyword.

Regards,
Miha