Re: [sqlite] Automatic index creation for unique columns

[Christian Smith]

On Fri, 16 Dec 2005, Eggert, Henri wrote:

>
>Hi all,
>
>Can anyone confirm following ?
>
>Create table t ( t1 integer primary key , t2 text unique , t3 ... )
>The table is created with 2 indexes :
>First index : t1 ( the primary key )
>Second index : t2 because t2 has the attribute unique


Being a pedant, no, there is only one external index (on t2). t1, being an
integer primary key, is the row Id and keyed directly in the table.

Nitpicking aside, yes. t2 is indexed.


>
>Thank's in advance
>
>Henri
>

Christian

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

[sqlite] bug with progress handler and sql functions.

[Guillaume Fougnies]

Hello,

Using sqlite3_progress_handler with functions in the sql
request (like ifnull()) seems to make the nProgressOps
(in vdbe.c) not increment properly.

Set a sqlite3_progress_handler with a frequency of 10
virtual machine opcodes.
The sql request contains 9 calls on ifnull.
sqlite3_step never activate the progress handler.

I've corrected the problem by adding a nProgressOpsI in
the sqlite3 structure which increments properly despite
many sqlite3VdbeExec calls. The patch is trivial.

Bye.
--
Guillaume FOUGNIES
Eulerian Technologies

[sqlite] [ANN] DragonDB - MVB Edition 1.1 Released

[John Oliva]

Danic Systems releases DragonDB - MVB Edition 1.1 for AppForge MobileVB.
 
EAST BRUNSWICK, NJ, USA (December 16, 2005) -- Danic Systems, LLC announced
today availability of an updated version of its DragonDB - MVB Edition
product.
 
Release 1.1 is a free update for those with an existing DragonDB - MVB
Edition license.
 
*** Whats New ***
 
* Support for SQLite 3.2.7
  - many new SQL language features
  - improved transaction control
  - improved performance of indexed queries
  - ability to modify structure of existing tables
  - minor bug fixes
 
* New licensing options & support policy
  - Commercial license
  - Non-commercial license
  - Standard support is free for licensed products.
  - High priority support is offered through fee based support packages.
 
*** About DragonDB - MVB Edition ***
 
DragonDB - MVB Edition is an SQL database engine and software development
toolkit for creating Microsoft Pocket PC and Smartphone database
applications using AppForge MobileVB (version 3.2 or later).  DragonDB is
built upon SQLite3 so its databases are portable to all platforms and
applications which support the SQLite3 database format.
 
*** Pricing & Availability ***
 
DragonDB - MVB Edition 1.1 is available now from the Danic Systems website
at   http://www.danicsystems.com.
 
The commercial version is $179 per development license without any royalties
for your deployed applications. The non-commercial version is $20 and is
strictly for non-commercial use. Volume discounts are available. A 30 day
free trial version is also available.
 
Priority support packages may be purchased starting from $45.
 
*** About Danic Systems, LLC ***
 
Danic Systems, LLC is a privately held software company providing
applications and tools for the Microsoft Windows desktop and mobile
operating systems.  Our specialty is in providing applications and tools
which bridge the power of the desktop computer with the flexibility offered
by mobile devices.
 
*** Contact Information ***
 
Company:  Danic Systems, LLC
E-mail: [EMAIL PROTECTED]
Website:    http://www.danicsystems.com

Re: [sqlite] Threads and locking

[John Stanton]

Have you thought of using a lock to synchronise access to the databaseso 
that only one thread at a time could change the database although both 
could read simultaneously?

JS

Barry Paul wrote:
 
Hi, I am having some unexpected locking issues with SQLite.


I have a desktop application that uses SQLite. The application has a low
priority worker thread that is constantly analyzing/adding/updating/deleting
records in the database. The main application thread mainly reads from the
database but also does some updating/deleting. Both threads have their own
SQLite connection.
 
My problem is that when I do updates in the main application thread I quite

often fail with a return value of SQLITE_BUSY. I have messed around with
busy_timeouts and busy_handlers without much success. My current busy
handler (culled either from this list or the web) is:

int busyHandler(void *pArg1, int iPriorCalls)
{

// sleep if handler has been called less than threshold value
if (iPriorCalls < 20)
{
// adding a random value here greatly reduces locking
if (pArg1 < 0)
Sleep((rand() % 500) + 400);
else Sleep(500);
return 1;
}

// have sqlite3_exec immediately return SQLITE_BUSY
return 0;
}

If I increase the transaction size on the low priority thread I get more
update failures on the main thread.

My schema is fairly simple and my tables contain < 90,000 rows. It would
seem to me that with just two threads and this busy handler I should never
(or very rarely) get SQLITE_BUSY. 
 
My theory is that the main application thread is getting locked out because

it is waiting for the low priority thread to release the lock on the
database. Meanwhile something else is happing on the machine at a higher
priority and not letting the low priority thread back in to finish the
transaction and release the lock.

Does this sound reasonable and is there a good way of dealing with this
situation?  Should I try to increase the priority of the background thread
when I get a lock? Or is there some way to make sure that transactions in
the low priority thread are executed all at once without interruption?

Thanks for your time,

--
BP
<< www.planet-hood.com >> Welcome to our world <<
 

RE: [sqlite] Threads and locking

[Barry Paul]

Yes, but I think that will just lead to the same problem. Essentially that
is what SQLite is doing for me already.

What is happening is that the high priority user interface thread is waiting
for the low priority worker thread to complete its transaction. This
effectively is reducing the priority of the user interface which either
times out or becomes sluggish... 

In the busy handler can you find out what thread has the lock? If so, I
could probably temporarily increase the priority of the locking thread and
speed up the transaction processing/unlocking...

Thanks again,

--
BP
<< www.planet-hood.com >> Welcome to our world <<
 
 

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED] 
> Sent: Friday, December 16, 2005 1:07 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Threads and locking
> 
> Have you thought of using a lock to synchronise access to the 
> databaseso that only one thread at a time could change the 
> database although both could read simultaneously?
> JS
> 
> Barry Paul wrote:
> >  
> > Hi, I am having some unexpected locking issues with SQLite.
> > 
> > I have a desktop application that uses SQLite. The 
> application has a 
> > low priority worker thread that is constantly 
> > analyzing/adding/updating/deleting
> > records in the database. The main application thread mainly 
> reads from 
> > the database but also does some updating/deleting. Both 
> threads have 
> > their own SQLite connection.
> >  
> > My problem is that when I do updates in the main 
> application thread I 
> > quite often fail with a return value of SQLITE_BUSY. I have messed 
> > around with busy_timeouts and busy_handlers without much 
> success. My 
> > current busy handler (culled either from this list or the web) is:
> > 
> > int busyHandler(void *pArg1, int iPriorCalls) {
> > 
> > // sleep if handler has been called less than 
> threshold value
> > if (iPriorCalls < 20)
> > {
> > // adding a random value here greatly 
> reduces locking
> > if (pArg1 < 0)
> > Sleep((rand() % 500) + 400);
> > else Sleep(500);
> > return 1;
> > }
> > 
> > // have sqlite3_exec immediately return SQLITE_BUSY
> > return 0;
> > }
> > 
> > If I increase the transaction size on the low priority thread I get 
> > more update failures on the main thread.
> > 
> > My schema is fairly simple and my tables contain < 90,000 rows. It 
> > would seem to me that with just two threads and this busy handler I 
> > should never (or very rarely) get SQLITE_BUSY.
> >  
> > My theory is that the main application thread is getting locked out 
> > because it is waiting for the low priority thread to 
> release the lock 
> > on the database. Meanwhile something else is happing on the 
> machine at 
> > a higher priority and not letting the low priority thread 
> back in to 
> > finish the transaction and release the lock.
> > 
> > Does this sound reasonable and is there a good way of dealing with 
> > this situation?  Should I try to increase the priority of the 
> > background thread when I get a lock? Or is there some way 
> to make sure 
> > that transactions in the low priority thread are executed 
> all at once without interruption?
> > 
> > Thanks for your time,
> > 
> > --
> > BP
> > << www.planet-hood.com >> Welcome to our world <<
> >  
> > 
> 
> 
> 

RE: [sqlite] Threads and locking

[Doug Nebeker]

I used to have the same issue.  I finally did two things:

1. The background worker thread is at least normal priority.  If you own
the DB, you need to get in and get out.  I put sleeps in to make sure I
wasn't hitting the DB too often from this thread

2. All connections to the database happen inside a "begin exclusive"
transaction.  I'm guaranteed not to hit deadlock this way.

With those two in place, I believe you can let a busy handler spin (ie
keep trying) forever because it is guaranteed to get in sooner or later.

-Original Message-
From: Barry Paul [mailto:[EMAIL PROTECTED] 
Sent: Friday, December 16, 2005 2:20 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Threads and locking

 
Hi, I am having some unexpected locking issues with SQLite.

I have a desktop application that uses SQLite. The application has a low
priority worker thread that is constantly
analyzing/adding/updating/deleting
records in the database. The main application thread mainly reads from
the database but also does some updating/deleting. Both threads have
their own SQLite connection.
 
My problem is that when I do updates in the main application thread I
quite often fail with a return value of SQLITE_BUSY. I have messed
around with busy_timeouts and busy_handlers without much success. My
current busy handler (culled either from this list or the web) is:

int busyHandler(void *pArg1, int iPriorCalls) {

// sleep if handler has been called less than threshold value
if (iPriorCalls < 20)
{
// adding a random value here greatly reduces locking
if (pArg1 < 0)
Sleep((rand() % 500) + 400);
else Sleep(500);
return 1;
}

// have sqlite3_exec immediately return SQLITE_BUSY
return 0;
}

If I increase the transaction size on the low priority thread I get more
update failures on the main thread.

My schema is fairly simple and my tables contain < 90,000 rows. It would
seem to me that with just two threads and this busy handler I should
never (or very rarely) get SQLITE_BUSY. 
 
My theory is that the main application thread is getting locked out
because it is waiting for the low priority thread to release the lock on
the database. Meanwhile something else is happing on the machine at a
higher priority and not letting the low priority thread back in to
finish the transaction and release the lock.

Does this sound reasonable and is there a good way of dealing with this
situation?  Should I try to increase the priority of the background
thread when I get a lock? Or is there some way to make sure that
transactions in the low priority thread are executed all at once without
interruption?

Thanks for your time,

--
BP
<< www.planet-hood.com >> Welcome to our world <<
 



To find out more about Reuters visit www.about.reuters.com

Any views expressed in this message are those of the individual sender, except 
where the sender specifically states them to be the views of Reuters Ltd.

Re: [sqlite] Threads and locking

[John Stanton]

Increasing priority does not speed up anything, it just denies time to 
lower priority threads.  If you use a lock to prevent the background 
thread from running while you have foreground activity then you won't 
get a busy situation and your foreground thread will run unimpeded.


Having your background thread at a low priority and sharing the database 
between threads will give you more BUSY states, not fewer.


You could make a third thread which services SQLite and feed it from a 
queue which gives absolute priority to your foreground requests.  Then 
you cannot have a BUSY and get maximum throughput.   I would make the 
server thread the same priority as the user ones since it runs 
synchronously.  The priority of your background thread is unimportant, 
and could be the same as the others.


I had a case recently where an ASP couldn't figure out why his server 
ran so badly and kept "freezing", despite the fact that he had raised 
the priority of the processes to maximum.  Of course putting the 
priority back down solved his problem and stopped the "freezing".  A 
high priority process in a busy wait or polling creates an ice age for 
everything else.

JS

Barry Paul wrote:

Yes, but I think that will just lead to the same problem. Essentially that
is what SQLite is doing for me already.

What is happening is that the high priority user interface thread is waiting
for the low priority worker thread to complete its transaction. This
effectively is reducing the priority of the user interface which either
times out or becomes sluggish... 


In the busy handler can you find out what thread has the lock? If so, I
could probably temporarily increase the priority of the locking thread and
speed up the transaction processing/unlocking...

Thanks again,

--
BP
<< www.planet-hood.com >> Welcome to our world <<
 
 




-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Friday, December 16, 2005 1:07 PM

To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Threads and locking

Have you thought of using a lock to synchronise access to the 
databaseso that only one thread at a time could change the 
database although both could read simultaneously?

JS

Barry Paul wrote:



Hi, I am having some unexpected locking issues with SQLite.

I have a desktop application that uses SQLite. The 


application has a 

low priority worker thread that is constantly 
analyzing/adding/updating/deleting
records in the database. The main application thread mainly 


reads from 

the database but also does some updating/deleting. Both 


threads have 


their own SQLite connection.

My problem is that when I do updates in the main 


application thread I 

quite often fail with a return value of SQLITE_BUSY. I have messed 
around with busy_timeouts and busy_handlers without much 


success. My 


current busy handler (culled either from this list or the web) is:

int busyHandler(void *pArg1, int iPriorCalls) {

   // sleep if handler has been called less than 


threshold value


   if (iPriorCalls < 20)
   {
   // adding a random value here greatly 


reduces locking


   if (pArg1 < 0)
   Sleep((rand() % 500) + 400);
   else Sleep(500);
   return 1;
   }

   // have sqlite3_exec immediately return SQLITE_BUSY
   return 0;
}

If I increase the transaction size on the low priority thread I get 
more update failures on the main thread.


My schema is fairly simple and my tables contain < 90,000 rows. It 
would seem to me that with just two threads and this busy handler I 
should never (or very rarely) get SQLITE_BUSY.


My theory is that the main application thread is getting locked out 
because it is waiting for the low priority thread to 


release the lock 

on the database. Meanwhile something else is happing on the 


machine at 

a higher priority and not letting the low priority thread 


back in to 


finish the transaction and release the lock.

Does this sound reasonable and is there a good way of dealing with 
this situation?  Should I try to increase the priority of the 
background thread when I get a lock? Or is there some way 


to make sure 

that transactions in the low priority thread are executed 


all at once without interruption?


Thanks for your time,

--
BP
<< www.planet-hood.com >> Welcome to our world <<

Re[2]: [sqlite] Threads and locking

[Teg]

Hello Barry,

Essentially, if it might lock ever, it shouldn't be done in the
context of the GUI. You may not have run into it yet but, any locking
in the GUI is sure to cause deadlocks when things get busy. At least
in Microsoft Windows, message handling is all reentrant so, it's very
easy to hold a lock, try to talk to a GUI element and deadlock.
Microsoft specifically warns against talking to the GUI from a worker
thread.

If you hold a lock in the GUI context but, don't try to update
anything in the display, then this is a non-issue. For example, with a
shared data structure, I might lock, copy then clear the shared
struct, then unlock and display updates from the copy (feed a list
control for example). This also lets the worker continue adding to the
shared data while the GUI works using the local copy.

As someone else suggested, I'd use a second worker thread for
inserts (or a single worker that reads jobs out of a queue and does
both insert and retrievals), then I'd post the status to the GUI,
perhaps stalling the workers until the GUI says it's done with the
data.

In this way the GUI will never stall no matter how hard the worker
threads are banging on the database.

C

Friday, December 16, 2005, 5:05:46 PM, you wrote:


BP> Yes, but I think that will just lead to the same problem. Essentially that
BP> is what SQLite is doing for me already.

BP> What is happening is that the high priority user interface thread is waiting
BP> for the low priority worker thread to complete its transaction. This
BP> effectively is reducing the priority of the user interface which either
BP> times out or becomes sluggish... 

BP> In the busy handler can you find out what thread has the lock? If so, I
BP> could probably temporarily increase the priority of the locking thread and
BP> speed up the transaction processing/unlocking...

BP> Thanks again,

BP> --
BP> BP
BP> << www.planet-hood.com >> Welcome to our world <<
 
 

>> -Original Message-
>> From: John Stanton [mailto:[EMAIL PROTECTED] 
>> Sent: Friday, December 16, 2005 1:07 PM
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] Threads and locking
>> 
>> Have you thought of using a lock to synchronise access to the 
>> databaseso that only one thread at a time could change the 
>> database although both could read simultaneously?
>> JS
>> 
>> Barry Paul wrote:
>> >  
>> > Hi, I am having some unexpected locking issues with SQLite.
>> > 
>> > I have a desktop application that uses SQLite. The 
>> application has a 
>> > low priority worker thread that is constantly 
>> > analyzing/adding/updating/deleting
>> > records in the database. The main application thread mainly 
>> reads from 
>> > the database but also does some updating/deleting. Both 
>> threads have 
>> > their own SQLite connection.
>> >  
>> > My problem is that when I do updates in the main 
>> application thread I 
>> > quite often fail with a return value of SQLITE_BUSY. I have messed
>> > around with busy_timeouts and busy_handlers without much 
>> success. My 
>> > current busy handler (culled either from this list or the web) is:
>> > 
>> > int busyHandler(void *pArg1, int iPriorCalls) {
>> > 
>> > // sleep if handler has been called less than 
>> threshold value
>> > if (iPriorCalls < 20)
>> > {
>> > // adding a random value here greatly 
>> reduces locking
>> > if (pArg1 < 0)
>> > Sleep((rand() % 500) + 400);
>> > else Sleep(500);
>> > return 1;
>> > }
>> > 
>> > // have sqlite3_exec immediately return SQLITE_BUSY
>> > return 0;
>> > }
>> > 
>> > If I increase the transaction size on the low priority thread I get
>> > more update failures on the main thread.
>> > 
>> > My schema is fairly simple and my tables contain < 90,000 rows. It
>> > would seem to me that with just two threads and this busy handler I
>> > should never (or very rarely) get SQLITE_BUSY.
>> >  
>> > My theory is that the main application thread is getting locked out
>> > because it is waiting for the low priority thread to 
>> release the lock 
>> > on the database. Meanwhile something else is happing on the 
>> machine at 
>> > a higher priority and not letting the low priority thread 
>> back in to 
>> > finish the transaction and release the lock.
>> > 
>> > Does this sound reasonable and is there a good way of dealing with
>> > this situation?  Should I try to increase the priority of the 
>> > background thread when I get a lock? Or is there some way 
>> to make sure 
>> > that transactions in the low priority thread are executed 
>> all at once without interruption?
>> > 
>> > Thanks for your time,
>> > 
>> > --
>> > BP
>> > << www.planet-hood.com >> Welcome to our world <<
>> >  
>> > 
>> 
>> 
>> 




-- 
Best regards,
 Tegmailto:[EMAIL PROTECTED]

[sqlite] seem to need write permission on db's parent directory

[raf]

hi,

using sqlite-3.2.7 via the perl module, DBD-SQLite-1.11,
from a cgi script via apache on linux on a local filesystem,
i received this error:

  DBD::SQLite::st execute failed: unable to open database file(1) at dbdimp.c 
line 398

the database, /var/db/mydb/mydb.db, had already been
successfully opened and this error happened when the
first insert into the database was attempted. i don't
know why it seems to want to open a database called "1".

the database was publically writable (for use via apache)
but its parent directory wasn't. making its parent
directory world writable as well made the insert work
and made the error message stop appearing.

i don't know whether sqlite or DBD::SQLite is the
cause here but i thought i should mention it on
this list because i started looking at this list's
archives for a solution and couldn't find one.

cheers,
raf

[sqlite] Re: seem to need write permission on db's parent directory

[Igor Tandetnik]

raf wrote:

using sqlite-3.2.7 via the perl module, DBD-SQLite-1.11,
from a cgi script via apache on linux on a local filesystem,
i received this error:

 DBD::SQLite::st execute failed: unable to open database file(1) at
dbdimp.c line 398

the database was publically writable (for use via apache)
but its parent directory wasn't. making its parent
directory world writable as well made the insert work
and made the error message stop appearing.


SQLite needs to be able to create a journal file in the same directory 
as the DB, before any modifications can take place. The journal is used 
to support transaction rollback.


Igor Tandetnik 

Re: [sqlite] Re: seem to need write permission on db's parent directory

[raf]

Igor Tandetnik wrote:

> raf wrote:
> >using sqlite-3.2.7 via the perl module, DBD-SQLite-1.11,
> >from a cgi script via apache on linux on a local filesystem,
> >i received this error:
> >
> > DBD::SQLite::st execute failed: unable to open database file(1) at
> >dbdimp.c line 398
> >
> >the database was publically writable (for use via apache)
> >but its parent directory wasn't. making its parent
> >directory world writable as well made the insert work
> >and made the error message stop appearing.
> 
> SQLite needs to be able to create a journal file in the same directory 
> as the DB, before any modifications can take place. The journal is used 
> to support transaction rollback.
> 
> Igor Tandetnik 

thanks for the explanation.

cheers,
raf

[sqlite] Can I have access to in-memory DB from different threads?

[Alexander Kozlovsky]

Hi, all!

Can I have access to in-memory DB from two different threads?



Best regards,
 Alexander  mailto:[EMAIL PROTECTED]

Re: [sqlite] Can I have access to in-memory DB from different threads?

[Dan Kennedy]

Both thread would have to use the same sqlite3*
handle. And access would have to be protected 
externally by a mutex or similar. 

With in-memory databases I don't think there are
any problems with using a single handle from 
multiple threads. With regular databases you can
run into trouble doing this on some types of unix.



--- Alexander Kozlovsky <[EMAIL PROTECTED]> wrote:

> Hi, all!
> 
> Can I have access to in-memory DB from two different threads?
> 
> 
> 
> Best regards,
>  Alexander  mailto:[EMAIL PROTECTED]
> 
> 


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