RE: [sqlite] Re: File Syste

2006-12-13 Thread Pat Wibbeler
The public C API is well-documented here:
http://www.sqlite.org/capi3.html

There may be other documentation of the internals, but I'd imagine the
public API will get you pretty far.


Pat

-Original Message-
From: Cesar Rodas [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, December 13, 2006 6:21 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Re: File Syste

I mean.. is there a manual that explain how to use SQLite Core in low
level
API, like berkeley DB, without SQL.

Thank to all

On 13/12/06, Cesar Rodas <[EMAIL PROTECTED]> wrote:
>
> Hello to all
>
>
>
> I starting a project http://code.google.com/p/gxdfs/ that is a
Distributed
> File System, following the white paper of GFS(Google File system). If
some
> one want to contribute please send emails with ideas.
>
> I would like to know how sqlite works.. is there a manual that explain
> every ".c" file?
>
> --
> Cesar Rodas
> http://www.phpclasses.org/grank (A PHP implementation of PageRank)




-- 
Cesar Rodas
http://www.phpclasses.org/grank (A PHP implementation of PageRank)

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] windows dll

2006-12-11 Thread Pat Wibbeler
All static goodness aside, I think the OP's question is still
reasonable.  The best suggestion I've heard for those who want to
version the DLL is to do it themselves, but  many feel much more
comfortable with a project-approved binary.  Perhaps someone who feels
strongly enough about a versioned DLL could post a patch to the build
and propose having it added to the distribution.

Pat

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Monday, December 11, 2006 12:12 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] windows dll

I agree with you 100%.  Statically linking Sqlite to applications gives 
you some freedom from "DLL HELL" and "REV LOCK SHOCK" and can only 
result in inefficiency if you have multiple different application 
programs using Sqlite on the individual workstation.

To my way of implementing KISS, using an Sqlite DLL makes no sense.

Teg wrote:
> Hello Art,
> 
> I don't depend on the DLL version because I compile SQLite in. There's
> never an issue. Why not generate your own DLL and include the version
> information? You can then rename the DLL so, you're not competing with
> other versions of the DLL.
> 
> These days, I'd rather have a larger executable that I know works
> everywhere and doesn't depend on DLL versioning.
> 
> C
> 
> Sunday, December 10, 2006, 12:31:33 PM, you wrote:
> 
> A> is there a reason that the version information cannot be
> A> included in the resource of the windows dll?  its not easy to
> A> cleanup a system that you might encounter or have to debug aa
> A> product installation on if there's multiple versions of sqlite3.dll
> A> on a user's system without any version resource information
> A> available in the dll.  this is standard practice to include this
> A> information.
> 
> A> thank you
> 
> 
> A> Thank you, 
> 
> A> Art Zerger
>  
> A> [EMAIL PROTECTED]
>  
> 
> 
>  
> A> -
> A> Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call
rates.
> 
> 
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] about call back of sqlite.

2006-12-08 Thread Pat Wibbeler
You might also be able to watch the database file itself for changes using a 
platform specific mechanism.  On UNIX, you could use kqueue's and on Windows a 
combination of FindFirstChangeNotification, FindNextChangeNotification and 
WaitForMultipleObjects.  This would allow you to watch for database changes 
without polling.  

Pat  

-Original Message-
From: Roberto [mailto:[EMAIL PROTECTED] 
Sent: Friday, December 08, 2006 4:23 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] about call back of sqlite.

On 08/12/06, hongsion <[EMAIL PROTECTED]> wrote:
> Hi sqlite,
> I want to using sqlite this way.  Application  A  register callback
> to sqlite db. And application B  will modify sqlite db. I want each time
> sqlite was modified by B, A is notified by callback. Is this possible in
> sqlite?  A and B run in different process. Thanks!
>


No it is not possible, you have to implement your own cross process
mechanism to notify applications of changes. Another idea DRH brough
up in the past, is to poll a table which stores ID's of items that
have changed.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-



RE: [sqlite] outputting select in cpp file?

2006-07-28 Thread Pat Wibbeler
Check out the callback parameter to sqlite_exec (the third parameter).
The quickstart gives an example of this:

http://www.sqlite.org/quickstart.html

Alternatively, check out sqlite3_prepare, sqlite3_bind, and
sqlite3_step, sqlite3_result*, and sqlite3_finalize.

All of these functions are documented here:
http://www.sqlite.org/capi3ref.html

Pat


-Original Message-
From: Keiichi McGuire [mailto:[EMAIL PROTECTED] 
Sent: Friday, July 28, 2006 3:36 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] outputting select in cpp file?

This sounds like a very simple problem, but I cannot figure it out!

Basically what I have is this settings table that has a boolean data
type, and I want to check it via a cpp program.

sqlite_exec(db,"select flag from setting",0,0,&errMsg);

and I want to be able to return a 0, or a 1 according to what I put into
the flag entry.

Thanks!
-Keiichi



RE: [sqlite] help with win32/iis install

2006-07-22 Thread Pat Wibbeler
The error probably comes from this line of code:
$db = sqlite_open("test.db") or die("failed to open/create the
database");

I know nothing about this configuration, but when sqlite_open fails, my
first instinct is a permissions issue. sqlite_open("test.db") attempts
to create or open a file "./test.db" ("./" is relative to the working
directory of the process, whatever that may be).  The process needs
appropriate read/write/create permissions to do this.

You can test your permissions by creating another plain file in the same
script.  If you don't have create permissions here, you could alter the
permissions for the directory, or give a full or relative path to a
directory in which you do have permissions.  

I'd also note that this script does not look threadsafe (a real issue if
you're actually going to have multiple users querying it from web
browsers).  There is a race condition that begins with the test for file
existence and ends with the creation of the database table based on that
condition.

Pat


-Original Message-
From: greenshire [mailto:[EMAIL PROTECTED] 
Sent: Friday, July 21, 2006 7:55 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] help with win32/iis install

 Greetings.  I recently installed php 5 on Win 2k3 server (iis 6).  I
enabled the pdo and sqlite extensions for sqlite, but using the test
script
from
 http://www.tanguay.at/installPhp5.php5?step=8  ,
I get error message:  "failed to open/create the database."  Any ideas?

Thank you,
-- 
greenshire


RE: [sqlite] Encoding with spanish characters

2006-07-13 Thread Pat Wibbeler
It depends (I know, not the answer you had hoped for!)

How are your query and parameter strings obtained (command line, compiled 
static strings, files, gui, other)? 

What encoding are these input strings?

Are you using sqlite3_exec, sqlite3_prepare or sqlite3_prepare16 to execute the 
query?

The key is to identify how the strings are encoded in the first place.  Then, 
you need to decide whether you'll use the sqlite UTF8 or UTF16 functions.  Once 
you have know these, you can figure out how to convert from input to output 
using platform specific or other conversion code.  If you are on a platform 
that has Unicode strings available to you, you can go straight from those 
Unicode strings to the sqlite3_*16 functions without conversion.

Pat

-Original Message-
From: Pedro J. Vivancos [mailto:[EMAIL PROTECTED] 
Sent: Thursday, July 13, 2006 12:19 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Encoding with spanish characters

No, I'm no sure. How can I test it? I've just started to learn C/C++ and 
I have no idea how to do it.

Thanks again.
Pedro.


Will Leshner escribió:
> On 7/12/06, Pedro J. Vivancos <[EMAIL PROTECTED]> wrote:
>
>> I'm developing a program using C++ and SQLite3 as database. I have a
>> problem with encoding when I try to execute a SQL query with some
>> characters like "ñ, á, é, í, ó, ú". My db is UTF-8 encoded and when I'm
>> debugging I can see that the sentence is right but I have no result.
>> However, when I try to execute the same sentence by using the SQLite3
>> console I have no problem.
>
> Are you sure your query string is also encoded as UTF8?
>
>



RE: [sqlite] sqlite3 on MacOSX

2006-07-09 Thread Pat Wibbeler
On my mac, sqlite3 -version shows 3.1.3 which is not forward compatible
with the default format for 3.3.6 unless you use the PRAGMA:

PRAGMA legacy_file_format=TRUE;

Before you create the database.

To use the old version as the default, you can compile the lib yourself
with:
 -DSQLITE_DEFAULT_FILE_FORMAT=1

Alternatively, you could get the source and build a compatible sqlite
client.

Pat

-Original Message-
From: Hartwig Wiesmann [mailto:[EMAIL PROTECTED] 
Sent: Sunday, July 09, 2006 3:25 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] sqlite3 on MacOSX

Hello!

I have created a database (let's name it "Test.db") on the Mac by  
using the sqlite lib (sqlite version 3.3.6) in my program. Everything  
works fine.
I also wanted to check the database contents using the sqlite command  
line program. Calling "sqlite3 Test.db" from the terminal and  
afterwards sending the command ".databases" leads to an error message  
"unsupported file format".

What am I doing wrong?

Hartwig



RE: [sqlite] endian-specific code in pure c source release?

2006-07-03 Thread Pat Wibbeler
This isn't quite what I'm looking for.  I understand that resulting
binaries are endian dependent, and the database file itself is endian
portable after version 3.  What I'm really asking (though not as clearly
as I had hoped ;-)) is whether or not the sqlite source code is endian
dependent. 

If it is, I assume that configure/make work some "magic" to generate
sources for the target endian platform.  If the source is endian
independent, I expect that I could use the pure c source provided for
windows users and let xcode target mactel, ppc or universal during
compilation.  

A quick search through the sqlite source release led me to believe that
the source may be coded around endian issues.  I guess what I'm looking
for is affirmation or denial of my cursory reading.

If the source is endian dependent, how does sqlite configure and/or make
determine the endian nature of the platform on which it is building?
What does it change?  If I know these things, I suspect that I can build
a project that either runs configure as part of the process, or does the
same things that make/configure do to make endian-ness correct.  

If these aren't documented somewhere, I can reverse engineer
configure/make, but I was hoping that someone here might have the
answers. 

Thanks again!

Pat

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Monday, July 03, 2006 10:04 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] endian-specific code in pure c source release?

Sqlite data is endian agnostic, but the executables are, like any 
executables, dependent upon the endian nature of the host processor.

Just compile the Sqlite library for each platform and share the data.

On platforms other than Windows use configure, otherwise use the 
prepared windows source.

If you make any extensions to Sqlite, such as your own functions, they 
will be platform independent.  By using the regular Sqlite source 
distribution you will be able to upgrade easily, and not have your 
application rev-locked.

Pat Wibbeler wrote:
> For a couple of reasons:
> * I'd like to use xcode to build a universal binary.  If I run
> ./configure, I imagine that any endian specific code that is fixed
using
> configure will be set to whatever platform I run configure on (i386 or
> ppc).
> * I already have the packaged source for a windows build using visual
> studio and I'd like to use the same sources if possible to avoid
> confusion.  
> 
> Pat
> 
> 
> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED] 
> Sent: Monday, July 03, 2006 9:24 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] endian-specific code in pure c source release?
> 
> Why not use the regular source and run configure?
> 
> Pat Wibbeler wrote:
> 
>>I'd like to build an xcode project for sqlite.  One straightforward
>>approach is to take the sqlite-source-3_3_6.zip "pure c" source
> 
> release
> 
>>and build the xcode project from that.
>>
>>Is there any endian specific code in that source release that might
> 
> trip
> 
>>me up on power pc processors?  I ask this because I know that this
>>release is "provided as a service to MS-Windows users who lack the
> 
> build
> 
>>support infrastructure of Unix."
>>
>>Thanks!
>>
>>Pat
> 
> 



RE: [sqlite] endian-specific code in pure c source release?

2006-07-03 Thread Pat Wibbeler
For a couple of reasons:
* I'd like to use xcode to build a universal binary.  If I run
./configure, I imagine that any endian specific code that is fixed using
configure will be set to whatever platform I run configure on (i386 or
ppc).
* I already have the packaged source for a windows build using visual
studio and I'd like to use the same sources if possible to avoid
confusion.  

Pat


-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Monday, July 03, 2006 9:24 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] endian-specific code in pure c source release?

Why not use the regular source and run configure?

Pat Wibbeler wrote:
> I'd like to build an xcode project for sqlite.  One straightforward
> approach is to take the sqlite-source-3_3_6.zip "pure c" source
release
> and build the xcode project from that.
> 
> Is there any endian specific code in that source release that might
trip
> me up on power pc processors?  I ask this because I know that this
> release is "provided as a service to MS-Windows users who lack the
build
> support infrastructure of Unix."
> 
> Thanks!
> 
> Pat



RE: [sqlite] default value in hex

2006-07-03 Thread Pat Wibbeler
Are you performing computations with that number?  For example, will you
be performing addition, subtraction, or bitwise and/or?

If not, you could just leave it as a string (and likely change the
create to be "... text(32) default '0xFF'").

I'm not an expert on the topic, but sqlite uses "manifest typing"
meaning that it doesn't enforce the type of a given column.  This allows
you to put whatever you want, wherever you want.  SQLite will try to
"coerce" the data into the type you've specified.  So, when it sees
xFF, it decides that you really want a string and makes it so.  When
it sees 0xFF it likely coerces that into the integer type specified
in the create.

When storing the number as an integer, it's stored as binary.  The fact
that you see it in base 10 when you select it is an artifact of whatever
you are using to display the number.  The sqlite command line utility
displays integers in base 10 (for good reason, most of the time, you
expect this!).

I had a quick look at the sqlite built-in sql functions to see if there
is a function that could convert an integer to a hex string (like
TO_CHAR() in oracle, or STR() in MSSQL), but it looks like there is not.


SQLite is intentinonally sparse in the included functions, so you can
either write or find a custom sqlite function to do this.
Alternatively, simply use whatever number->string conversion routines
the language/platform you are using supplies (e.g. printf for c).

Pat



-Original Message-
From: Wilfried Mestdagh [mailto:[EMAIL PROTECTED] 
Sent: Sunday, July 02, 2006 11:16 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] default value in hex


Hi,

How to specify a hexadecimal value in the default by create of a table ?
I
try thinks like:

create table Test ( [Name] char, [Color] integer default 0xFF );

But have syntax errors. When I leave out the 0 then the xFF seems to
be
stored as a string. it is just that 0xFF makes more sence that
16777215,
specially if it is a color.
-- 
View this message in context:
http://www.nabble.com/default-value-in-hex-tf1882855.html#a5147011
Sent from the SQLite forum at Nabble.com.



[sqlite] endian-specific code in pure c source release?

2006-07-03 Thread Pat Wibbeler
I'd like to build an xcode project for sqlite.  One straightforward
approach is to take the sqlite-source-3_3_6.zip "pure c" source release
and build the xcode project from that.

Is there any endian specific code in that source release that might trip
me up on power pc processors?  I ask this because I know that this
release is "provided as a service to MS-Windows users who lack the build
support infrastructure of Unix."

Thanks!

Pat


RE: [sqlite] real time gui updates

2006-06-29 Thread Pat Wibbeler
Another alternative might be using an API that waits for events on the
database file - for instance kqueues some unix variants or
WaitForMultipleObjects and
FindFirstChangeNotification/FindNextChangeNotification on windows. 

I agree that polling causes issues.  It doesn't require much CPU if you
use the right query, but it may access the disk, and it may interfere
with OS power management.
 
Pat

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Thursday, June 29, 2006 6:50 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] real time gui updates

In that case you might use some form of inter-process communication like

a named pipe or a semaphore which is activated by a change to the DB. 
The viewing process would have a thread waiting on the IPC channel or 
semaphore.  When the DB changed it would automatically trigger a refresh

in the viewer process/processes.

If you were ambitious you could write an Sqlite function to perform the 
synchronization and drive it from a trigger.  That would make the logic 
simpler in your application.

Rob Menegon wrote:
> Not sure whether I understand how this would occur.  
> 
> The application is not doing or responsible for the updates to the
database.
> Its only function in life is to retrieve and display data. Updates,
> modifications occur via another application, so I was incorrect in my
> previous response to you - one user (app) doing updates and another
> displaying data - independent processes.
> 
> 
> 
> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, 29 June 2006 2:51 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] real time gui updates
> 
> In that case your application knows whenever the database is changed
and can
> call a refresh routine.
> 
> Rob Menegon wrote:
> 
>>No a single user/instance of the application reading from the
database. 
>>
>>
>>
>>-Original Message-
>>From: John Stanton [mailto:[EMAIL PROTECTED]
>>Sent: Wednesday, 28 June 2006 10:45 PM
>>To: sqlite-users@sqlite.org
>>Subject: Re: [sqlite] real time gui updates
>>
>>Rob Menegon wrote:
>>
>>
>>>I have an application that displays data retrieved from various
SQLite 
>>>tables.  As the data within the tables changes I want these changes 
>>>reflected in the application real time.
>>>
>>>Can someone advise on the best way of doing this?
>>>
>>>Rob Menegon
>>>
>>>
>>
>>Do you have multiple independent users?
>>
> 
> 
> 



RE: [sqlite] Problems with multiple threads?

2006-06-08 Thread Pat Wibbeler
No problem.  In fact, I had to consult the docs again to be sure!  I've
sorted through these several times myself.

They are quite good, though I have to admit that I'm constantly having
to consult them to remember how the locking works.  I do agree that a
BEGIN SHARED would be nice for cases where you'd like consistent reads
across multiple statements without using a BEGIN IMMEDIATE and locking
other threads doing the same out.

Pat

-Original Message-
From: A. Pagaltzis [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 07, 2006 7:01 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Problems with multiple threads?

* Pat Wibbeler <[EMAIL PROTECTED]> [2006-06-07 22:55]:
> It's entirely possible I'm reading these docs incorrectly, but
> this strategy has worked quite well for me.

No, I don't see any error in your reading. My apologies; I should
have consulted the docs instead of going by mailing list posts.

It's interesting that there's no way to force a SHARED lock to be
obtained immediately. The available mechanisms allow serialising
write operations with respect to each other, but not forcing a
well-defined sequence of read operations relative to write
operations.

Regards,
-- 
Aristotle Pagaltzis // <http://plasmasturm.org/>


RE: [sqlite] Problems with multiple threads?

2006-06-07 Thread Pat Wibbeler
Reading the BEGIN docs here:
http://www.sqlite.org/lang_transaction.html

I saw "After a BEGIN IMMEDIATE, you are guaranteed that no other thread
or process will be able to write to the database or do a BEGIN
IMMEDIATE".  This is because "If the transaction is immediate, then
RESERVED locks are acquired on all databases as soon as the BEGIN
command is executed".

To me, this means that if all threads do BEGIN IMMEDIATE, it will
prevent other threads from doing BEGIN IMMEDIATE, thus serializing all
transactions.  

It also says that "The first read operation against a database creates a
SHARED lock".  I took this to mean that a BEGIN (meaning deferred)
transaction would only escalate to SHARED on the first select, where a
BEGIN IMMEDIATE would escalate to RESERVED.

So, for transactions where a write occurs (usually including a read), I
do a BEGIN IMMEDIATE.  For transactions where I know that I'm only
reading (select), I simply do BEGIN and let the database escalate to
SHARED.

It's entirely possible I'm reading these docs incorrectly, but this
strategy has worked quite well for me.

Pat

-Original Message-
From: A. Pagaltzis [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 07, 2006 1:27 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Problems with multiple threads?

* Pat Wibbeler <[EMAIL PROTECTED]> [2006-06-07 20:50]:
> Beginning everything with BEGIN IMMEDIATE should eliminate the
> possibility of deadlock, but you will serialize read-only
> operations.

Why? BEGIN IMMEDIATE acquires a for-read lock. Multiple for-read
locks can be acquired concurrently. It is only for-write locks
that can only be acquired in the absence of any other locks,
which leads to serialisation. Putting all your read operations in
BEGIN IMMEDIATE means that all your write operations will be
serialised in relation to all other operations taking place, but
read operations can proceed apace.

Of course, if your writes are short and frequent, they will
likely take much longer than necessary if all your operations
acquire read locks before they *really* need them.

Regards,
-- 
Aristotle Pagaltzis // <http://plasmasturm.org/>


RE: [sqlite] Problems with multiple threads?

2006-06-07 Thread Pat Wibbeler
Beginning everything with BEGIN IMMEDIATE should eliminate the
possibility of deadlock, but you will serialize read-only operations.
If your transactions are short or contention is low, using BEGIN
IMMEDIATE makes things easy.

However, if you find that you have a set of read-only operations that
run frequently, or take a long time, you may want to consider simply
using BEGIN on these (or for single statement reads, you could leave the
transaction wrapping out altogether).

Pat  

-Original Message-
From: Jiri Hajek [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 07, 2006 11:33 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Problems with multiple threads?

> If it is inconvenient to rollback and retry the entire transaction,
then
start the transaction initially with BEGIN EXCLUSIVE.  
> This will acquire the reserved lock immediately (instead of waiting to
the
first write occurs) and so you will either get an 
> SQLITE_BUSY right away (when it is a simple matter to just rerun the
BEGIN
EXCLUSIVE statement until it works) or you can be
> assured of never getting another SQLITE_BUSY again until you try to
COMMIT
(and there too, you can simply rerun COMMIT 
> repeatedly until it works.)

Thanks, I overlooked that by default transactions are DEFERRED in
SQLite. It
really fixes the problem.

As I think about it, if I make _all_ transactions in my application
IMMEDIATE, there shouldn't be any risk of a deadlock, right?

Thanks,
Jiri



RE: [sqlite] Problems with multiple threads?

2006-06-07 Thread Pat Wibbeler
Do you have any transactions that look like:

BEGIN
SELECT
INSERT/DELETE/UPDATE
COMMIT

If you do, you may have multiple threads trying to escalate from a
SHARED to a RESERVED lock as described here:
http://sqlite.org/capi3ref.html#sqlite3_busy_handler

It's important that if you have multithreaded access and a strategy that
involves either retrying or waiting on SQLITE_BUSY that you specify at
the beginning of write transactions that the transaction requires a
higher level lock.  I do this using "BEGIN IMMEDIATE".  

The thread currently on this list with subject
"[sqlite] BEGIN and Backup [was [sqlite] Problems with multiple
threads?]"
Discusses a similar issue.

Pat

-Original Message-
From: Jiri Hajek [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 07, 2006 9:26 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Problems with multiple threads?

Thanks for an additional explanation, I used sqlite3_get_autocommit()
for
debugging and it helped me to find out that it really was my fault.
There
was an incorrect processing after COMMIT returned SQLITE_BUSY. So sorry
for
this.

However, right after fixing this, I found another problem. It certainly
can
be my fault, but I don't see how could it be: If I don't use
transactions,
multiple threads seem to proceed well, but then right after I add BEGIN
and
COMMIT to some place, all threads lock eventually. I debugged it and
found
that _all_ threads accessing SQLite are in a loop waiting for an action
to
proceed and all of them keep getting SQLITE_BUSY result. I wonder, can
it be
somehow my fault, or is it some kind of a dead-lock in SQLite?

In the simpliest form it takes only two threads to reproduce and the
problem
looks like:

Thread 1:

BEGIN TRANSACTION<-- proceeded
INSERT INTO ...  <-- Processing stops here, waiting
in a
loop, Sqlite3_step() infinitely returns SQLITE_BUSY
COMMIT


Thread 2:

  // no explicit transaction start here
DELETE FROM ...   <-- Processing stops here, waiting in
a
loop, Sqlite3_step() infinitely returns SQLITE_BUSY


Thread 2 can also look like this in order to reproduce the problem
(situation in Thread 1 remains the same):

BEGIN TRANSACTION<-- proceeded
DELETE FROM ...  <-- proceeded 
COMMIT <-- Processing stops here,
waiting in
a loop, Sqlite3_step() infinitely returns SQLITE_BUSY

No other thread calls any SQLite function.

Do you have any idea what could be wrong?

Thanks,
Jiri




RE: [sqlite] BEGIN and Backup [was Re: [sqlite] Problems with multiple threads?]

2006-06-07 Thread Pat Wibbeler
You can use BEGIN IMMEDIATE or BEGIN EXCLUSIVE depending on the type of
lock you'd like.

SQLite BEGIN syntax:
http://sqlite.org/lang_transaction.html

SQLite locks:
http://sqlite.org/lockingv3.html

SQLite Busy Handler:
http://sqlite.org/capi3ref.html#sqlite3_busy_handler

Pat

-Original Message-
From: Russell Leighton [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 07, 2006 8:24 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] BEGIN and Backup [was Re: [sqlite] Problems with
multiple threads?]

So, this was very enlightening...I have a simple backup function that I 
now question is correct.

It does:
- execute "begin" // lock from writes
-copy db file to new file byte by byte
- execute "commit" // unlock

...I was thinking that "begin" would lock the file.

If I use an flock()  [or should it be lockf() ?? ] to bracket the file 
copy will I be safe?

I was hoping to have something more efficient than a table by table copy

for backup.

Thx

[EMAIL PROTECTED] wrote:

>As various people search for application and/or SQLite bugs
>related to multiple threads and BEGIN, let me try to aid the
>effort by better describing exactly what BEGIN does and 
>suggesting some debugging tricks.
>
>Realize that BEGIN does not actually create any file locks
>or check to see if any file locks already exist, nor
>interact in any other way with the filesystem.  File locks
>are only created by SELECT, UPDATE, INSERT, and DELETE
>statements.  (OK, also CREATE and DROP statements, but 
>let's ignore those for now for simplicity.  Presumably 
>the schema is fixed at the point where the problems are
>occuring.)  All BEGIN does is to set a flag that says
>"do not automatically perform a COMMIT after each write
>to the database".  This is the autoCommit flag that I
>mentioned in a prior email.  autoCommit is a boolean
>member of the sqlite3 structure.  A lock is acquired
>at the beginning of each UPDATE, INSERT, or DELETE
>if it does not already exists.  After each UPDATE,
>INSERT, or DELETE, sqlite checks the value of the
>autoCommit flag, and if it is true it automatically
>does a COMMIT.  A read-lock is acquired before each
>SELECT if it does not already exists, and after the
>SELECT is done, the read-lock is dropped if autoCommit
>is true.
>
>So the BEGIN instruction does not do anything with the
>filesystem.  It does not interact in any way with the
>operating system or with other database connections.
>All BEGIN does is clear the autoCommit flag.  So it is
>hard to imagine how having other threads could possibly
>effect its behavior.
>
>At any time, you can determine the value of the autoCommit
>flag using the sqlite3_get_autocommit() API.  See
>
>  http://www.sqlite.org/capi3ref.html#sqlite3_get_autocommit
>
>I suggest that people who are getting back unexpected
>"cannot start a transaction within a transaction" errors
>should use the sqlite3_get_autocommit() API in some printf()s
>to trace the status of the autocommit flag within their
>application.  Prior to running BEGIN, it should always
>be the case that sqlite3_get_autocommit() returns TRUE.
>If sqlite3_get_autocommit() returns FALSE, then the BEGIN
>that follows will give the "cannot start..." error.
>
>I suspect what is happening is that some prior COMMIT
>or ROLLBACK is not setting the autoCommit flag back to
>TRUE.  This might be because the COMMIT or ROLLBACK
>failed.  Or, there could be some kind of bug in SQLite
>that is causing the autoCommit flag to not be set
>correctly.  I suspect the former, but am open to evidence
>pointing to the latter.  It might be useful to use the
>sqlite3_get_autocommit() function to print out the value
>of the autoCommit flag after each COMMIT and ROLLBACK is
>executed.  This might help to isolate the problem.
>
>--
>D. Richard Hipp   <[EMAIL PROTECTED]>
>
>  
>



RE: [sqlite] Problems with multiple threads?

2006-06-06 Thread Pat Wibbeler
One means of troubleshooting this is to emit a log statement that
includes the thread id with every BEGIN/COMMIT (e.g. printf("%d - %s",
thread, sql)).  It may be useful to log other sql statements this way as
well.  

This sort of troubleshooting has always shown the mistake to be mine,
not SQLite when I see this issue.

Pat

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 06, 2006 6:36 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Problems with multiple threads?

Bill KING <[EMAIL PROTECTED]> wrote:
> Could this be cause by one thread opening a transaction, then a second
> on a second connection trying to open a transaction on it, and failing
> to open the transaction file (as it already exists?). 
> 

No.

Each database connection (each sqlite3* pointer) has a boolean
field called "autoCommit".  If autoCommit is false, that means
a transaction is active.  If autoCommit is true, that means you
are not inside a transaction.  autoCommit is true by default,
of course.

Executing BEGIN does not do anything with the disk.  All it does
is change the autoCommit flag from true to false.  If the flag
was already false at the time BEGIN is executed, it generates
the "cannot start a transaction within a transaction" error.

The important thing to note here is that BEGIN never does
any system calls - it never interacts with the database file
in any way.  All it does is set what should be a private boolean
variable to false.

Now, if you do BEGIN EXCLUSIVE or some other variation on BEGIN,
that is a different matter.  But you said you just did a simple
BEGIN.  And in that case, the nothing visible to other database
connections ever gets touched.

This leads me to conjecture that you have multiple threads
messing with the same database connection

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



RE: [sqlite] Memory DB: Load from file

2006-06-06 Thread Pat Wibbeler
This sounded fun, so I thought I'd give it a try.  Here's a sample
pulling schema and data from an on-disk to an in-memory database in c.
I've omitted error handling and debug output to make it shorter.  

int process_ddl_row(void * pData, int nColumns, 
char **values, char **columns);
int process_dml_row(void *pData, int nColumns, 
char **values, char **columns);

void test()
{
sqlite3* memorydb;
sqlite3* budb;

sqlite3_open(":memory:", &memorydb);
// Looks for backup.db in pwd.  For testing, you may want to
// initialize the database to a known state.
sqlite3_open("backup.db", &budb);   

// Create the in-memory schema from the backup
sqlite3_exec(budb, "BEGIN", NULL, NULL, NULL);
sqlite3_exec(budb, "SELECT sql FROM sqlite_master WHERE sql NOT
NULL",
&process_ddl_row, memorydb, NULL);
sqlite3_exec(budb, "COMMIT", NULL, NULL, NULL);
sqlite3_close(budb);

// Attach the backup to the in memory
sqlite3_exec(memorydb, "ATTACH DATABASE 'backup.db' as backup", 
NULL, NULL, NULL);

// Copy the data from the backup to the in memory
sqlite3_exec(memorydb, "BEGIN", NULL, NULL, NULL);
sqlite3_exec(memorydb, 
"SELECT name FROM backup.sqlite_master WHERE
type='table'", 
&process_dml_row, memorydb, NULL);
sqlite3_exec(memorydb, "COMMIT", NULL, NULL, NULL);

sqlite3_exec(memorydb, "DETACH DATABASE backup", NULL, NULL,
NULL);
sqlite3_close(memorydb);
}

/**
 * Exec an sql statement in values[0] against
 * the database in pData.
 */
int process_ddl_row(void * pData, int nColumns, 
char **values, char **columns)
{
if (nColumns != 1)
return 1; // Error

sqlite3* db = (sqlite3*)pData;
sqlite3_exec(db, values[0], NULL, NULL, NULL);

return 0;
}

/**
 * Insert from a table named by backup.{values[0]}
 * into main.{values[0]} in database pData.
 */
int process_dml_row(void *pData, int nColumns, 
char **values, char **columns)
{
if (nColumns != 1)
return 1; // Error

sqlite3* db = (sqlite3*)pData;

char *stmt = sqlite3_mprintf("insert into main.%q "
"select * from backup.%q", values[0], values[0]);
sqlite3_exec(db, stmt, NULL, NULL, NULL);
sqlite3_free(stmt); 

return 0;
}
-Original Message-
From: Dave Gierok [mailto:[EMAIL PROTECTED] 
Sent: Monday, June 05, 2006 5:45 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Memory DB: Load from file

Thank you for the help Andrew and D. Richard Hipp.  But how would I do
this (page 24 & 25 of the slides) using the C/C++ interface?

{
   sqlite3* pFileDB;
   sqlite3* pMemoryDB;

   sqlite3_open(fileName, &pFileDB);
   sqlite3_open(":memory:", &pMemoryDB);

   //
   //WHAT DO I DO NOW?
   //
}

Thanks,
Dave Gierok

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Monday, June 05, 2006 12:49 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Memory DB: Load from file

Andrew Piskorski <[EMAIL PROTECTED]> wrote:
> On Mon, Jun 05, 2006 at 12:05:52PM -0700, Dave Gierok wrote:
> 
> > Is there a way to load a Sqlite file based DB and then specify we
want
> > that to go into memory?  Or is there a more efficient way to create
our
> > memory DB?
> 
> You could use attach to copy from an on-disk SQLite database:
> 
>   http://www.sqlite.org/google-talk-slides/page-024.html
>   http://www.sqlite.org/google-talk-slides/page-025.html
> 

Andrews suggestion above is better than the sqlite3_raw_write()
routine of my previous post because Andrew's idea is portable.
Use this idea if it is fast enough for you.  The raw_write()
thing will be a little bit faster, but as I said, it is subject
to break without warning.
--
D. Richard Hipp   <[EMAIL PROTECTED]>



RE: [sqlite] Multithreading. Again.

2006-06-02 Thread Pat Wibbeler
I don't think it's that uncommon to ask the user of the statement to
finalize database resources explicitly, even in a managed environment.
For example, Java collects memory, but the programmer must explicitly
close network, file, database, and other resources.  In java, I do this
in a finally block.  In c++, I do it using a stack based destructor
guard.

Pat

-Original Message-
From: Florian Weimer [mailto:[EMAIL PROTECTED] 
Sent: Friday, June 02, 2006 9:19 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Multithreading. Again.

> As long as no prepared statements are outstanding, you should
> be safe moving sqlite database connections across threads, as
> of version 3.3.1.  The rules are not really quite that strict,
> but the exact rules are more complex and this strict rule
> gives you an extra margin of safety.

Is it possible to finalize statements in a separate thread?  This
would be a rather important feature because on most multi-threaded
VMs, user-defined finalizers (which would be used to clean up SQLite
objects which are no longer used) run in a separate thread.


RE: [sqlite] Multithreading. Again.

2006-06-01 Thread Pat Wibbeler
I was reading sqlite3_open documentation earlier this week and noticed
that the docs say:

"The returned sqlite3* can only be used in the same thread in which it
was created. It is an error to call sqlite3_open() in one thread then
pass the resulting database handle off to another thread to use. This
restriction is due to goofy design decisions (bugs?) in the way some
threading implementations interact with file locks."

http://www.sqlite.org/capi3ref.html#sqlite3_open

I was surprised to see this because I was under the impression that this
issue had been fixed as you mentioned below.  I'm glad to hear that it
really is fixed.  Is this a "documentation bug" leftover from previous
versions?

Thanks!

Pat


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Thursday, June 01, 2006 8:38 AM
To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
Subject: Re: [sqlite] Multithreading. Again.

"Peter Cunderlik" <[EMAIL PROTECTED]> wrote:
> Hello everybody,
> 
> I'd like to ask a couple of silly questions before newcomers like me
> get moderated. :-) I've browsed through the documentation and this
> mailing list, trying to understand issues with multithreading. I'd
> like if someone could confirm my conclusions.
> 
> 1. As of SQLite 3.3.5, there is no multithreading (MT) problem with
> the SQLite itself. All problems come from the underlying OS libraries.

I would argue that this has always been the case.  But beginning
in version 3.3.1, SQLite has taken additional steps to partially 
work around problems in the OS.  So the OS problems are less
troublesome.

> 
> 2. The only MT problem is with locking the database in a case when a
> thread uses sqlite3 structure created by a different thread. Thus,
> using connection pool (allocated by one thread, used by other threads)
> is not safe.

This was the case prior to version 3.3.1.  Beginning with 3.3.1
and following, you can move an sqlite3 structure from one thread
to another as long as there are no locks being held by that
structure.  The easiest way to make sure that no locks are held
is to finalize all statements associated with the sqlite3 structure.


> 
> 3. Just a thought: In case of in-memory database, no file locking is
> required, so MT is no issue. (?)

Correct.

Of course, it has always been the case and probably always will be
that you cannot use the same sqlite3 connection in two or more
threads at the same time.  You can use different sqlite3 connections
at the same time in different threads, or you can move the same
sqlite3 connection across threads (subject to the constraints above)
but never, never try to use the same connection simultaneously in
two or more threads.  

Often you will get an SQLITE_MISUSE error if you try to use the
same sqlite3 connection in two threads at the same time, but there
are race conditions which can cause this error check to be missed,
so do not count on it.

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



[sqlite] sqlite3_exec returning SQLITE_CANTOPEN

2006-05-22 Thread Pat Wibbeler
I'm periodically seeing sqlite3_exec return SQLITE_CANTOPEN.
Unfortunately, I haven't boiled this down to a simple sample that I can
send to this list, but I thought I'd check to see if there is a known
solution before I dive too deep.  I'm using sqlite version 3.2.7 on
windows.

Thanks!
Pat

The Details
---
The sql statement that causes this always seems to be:
"BEGIN IMMEDIATE"

When I inspect my threads in the debugger, there is generally another
transaction closing the database after a "COMMIT", and a third doing
"BEGIN IMMEDIATE" concurrently.  One peculiarity of my system is that I
open and close a new "connection" to the database for each transaction.
I also use a busy_handler that returns 1.  I have debug log statement in
my busy handler and I usually see it called just before this problem
occurs.  

The threads look like this (snipped to sqlite calls):

THREAD A - The thread from which the SQLITE_CANTOPEN is returned.  The
windows GetLastError appears to be "Access is denied" in an attempt to
open the journal file.  I've paused this stack at the line where the
SQLITE_CANTOPEN is returned - line 286 of os_win.c

App.exe!sqlite3OsOpenExclusive(const char * zFilename=0x02425b6a, OsFile
* id=0x02423a58, int delFlag=0)  Line 286
App.exe!pager_open_journal(Pager * pPager=0x024239f8)  Line 2654
App.exe!sqlite3pager_begin(void * pData=0x024276f8, int exFlag=0)  Line
2748 
App.exe!sqlite3BtreeBeginTrans(Btree * pBt=0x02421730, int wrflag=1)
Line 1638 
App.exe!sqlite3VdbeExec(Vdbe * p=0x0240ccb8)  Line 2337
App.exe!sqlite3_step(sqlite3_stmt * pStmt=0x0240ccb8)  Line 217
App.exe!sqlite3_exec(sqlite3 * db=0x02427240, const char *
zSql=0x0268c7e4, int (void *, int, char * *, char * *)*
xCallback=0x, void * pArg=0x, char * *
pzErrMsg=0x0268c468) Line 79
  

THREAD B, also doing a "BEGIN IMMEDIATE"
App.exe!sqlite3OsFileExists(const char * zFilename=0x0241bb72)  Line
148App.exe!hasHotJournal(Pager * pPager=0x02419a00)  Line 2270
App.exe!sqlite3pager_get(Pager * pPager=0x02419a00, unsigned int pgno=1,
void * * ppPage=0x0278b90c)  Line 2336
App.exe!getPage(Btree * pBt=0x0241f7c0, unsigned int pgno=1, MemPage * *
ppPage=0x0278ba10)  Line 1125
App.exe!lockBtree(Btree * pBt=0x0241f7c0)  Line 1445
App.exe!sqlite3BtreeBeginTrans(Btree * pBt=0x0241f7c0, int wrflag=1)
Line 1634
App.exe!sqlite3VdbeExec(Vdbe * p=0x02422d58)  Line 2337
App.exe!sqlite3_step(sqlite3_stmt * pStmt=0x02422d58)  Line 217
App.exe!sqlite3_exec(sqlite3 * db=0x0241f970, const char *
zSql=0x0278d02c, int (void *, int, char * *, char * *)*
xCallback=0x, void * pArg=0x, char * *
pzErrMsg=0x0278ccb0)  Line 79

THREAD C, calling close after finishing up a transaction:
App.exe!sqlite3OsClose(OsFile * id=0x0240a770)  Line 416
App.exe!sqlite3pager_close(Pager * pPager=0x0240a718)  Line 2011
App.exe!sqlite3BtreeClose(Btree * pBt=0x0243e800)  Line 1299
App.exe!sqlite3_close(sqlite3 * db=0x0240d088)  Line 159


[sqlite] means for explicitly escalating a transaction from RESERVED to PENDING/EXCLUSIVE

2006-05-04 Thread Pat Wibbeler
Is there a means for explicitly escalating an existing transaction from
RESERVED to EXCLUSIVE (either through issuing sql statements or the C
API)? 

I'm using the following locking strategy:
* Read only transactions start with a BEGIN and allow sqlite to escalate
to SHARED on read.
* Transactions that may (or are known to) write use BEGIN IMMEDIATE to
avoid the potential lock escalation issue that might occur with two
simultaneous SHARED transactions that each attempt to escalate to
RESERVED by issuing a read followed by a write.
* Install a busy handler that directs sqlite to continue trying to
obtain the lock. 

Unfortunately, I also have some transactions that I'd like to be
EXCLUSIVE.  In some cases, I know when beginning the transaction that
these should be EXCLUSIVE, and in some cases, I don't know until I'm
within the transaction that I'd like to be EXCLUSIVE.  Even in cases
where I know I'll eventually be exclusive, I'd like to delay exclusivity
as long as possible, allowing read operations to continue.  

Wwhat I'd like to do is begin this transaction BEGIN IMMEDIATE, allowing
SHARED locks to be acquired and the database read.  At some point within
the transaction, I'd like to force an escalation to EXCLUSIVE, forcing
all SHARED and other locks to clear before continuing.

Is there a way to do this?

Thanks!

Pat