Re: [sqlite] SQLITE_ENABLE_MEMORY_MANAGEMENT still single threaded?

[Colin Goldie]

Thanks Dan, the upgrade to 3.6.14 sorted out all memory related issues.

"Using the same thread/connection-handle for reading and writing a  
Database might improve performance."

Interesting comment, currently a single connection-handle is opened and
shared amongst threads. Each thread has its own statement handle.
There is an application level read/write lock that prevents writes while
reads occur.

This architecture worked perfectly for years with 3.5.1.

Is this is a really bad idea with 3.6.14 and may lead to cache issues
(such as memory leaks) or crashing?

It was architected this way so that multiple reads can occur in parallel
without the overhead of queuing read requests to/from a single
read/write thread.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan
Sent: Monday, May 18, 2009 7:39 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLITE_ENABLE_MEMORY_MANAGEMENT still single
threaded?


> Hi,
>
> The latest info I can find about SQLITE_ENABLE_MEMORY_MANAGEMENT is  
> from
> mid 2006 - "FAQ said that it might be problematic to use more than one
> thread with SQLITE_ENABLE_MEMORY_MANAGEMENT".
>
> Is this still the case in 3.6.14 ? - The latest FAQ mentions nothing
> about SQLITE_ENABLE_MEMORY_MANAGEMENT being problematic anymore.
>
> Background:
>
> I have a single process that uses sqlite 3.5.1 and opens 350 sqlite
> databases, each db has a dedicated write thread and there is a global
> "reader" thread pool shared amongst the 350 databases.
> The files are never closed because we aim to provide 99.999%
> availability.
> After a few days on rhel5 64bit, sqlite consumes all of the 20gigs of
> physical memory and all of the swap, machine dies.

Why is SQLite eating up all this memory? How large are the configured
page caches?

First thing to do is upgrade to 3.6.14 if it is at all possible. 3.5.1
is officially ancient. The list of bugs fixed since then, thread related
and otherwise, must be enormous.

> Ive played around with SQLITE_ENABLE_MEMORY_MANAGEMENT in 3.5.1 on
> windows (visual c++ 2005)- seems to work okay for a while but  
> eventually
> the heap is corrupted - most probably because of the thread issue with
> memory management.
>
> Looks like my options are:
>
> 1. Multi-threaded memory management now supported in SQLite 3.6.14
> (fingers crossed - don't like my chances).

In 3.6.14 the APIs associated with SQLITE_ENABLE_MEMORY_MANAGEMENT work
across threads. 3.5.1 was the first release to support this,  
incidentally.

> 2. Close each database often - this is a bad approach for 5 x 9's.
> 3. Re-architect the writer threads to do both writes and reads - bad  
> for
> performance.

Using the same thread/connection-handle for reading and writing a  
database
might improve performance.

Dan.







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


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

Re: [sqlite] How sqlite will store the data?

[Rajesh Nair]

If we use VACUUM command will it reassign a new ROWID to each rows and
write it in the ascending order of ROWID?
If yes then that is what Pramoda.M.A needed.


On 5/15/09, John Machin  wrote:
> On 15/05/2009 8:41 PM, Pramoda M. A wrote:
>> But how to get the rows in the same order of insertion?
>> Say, I will insert 2 3 4 and it will store in 2 4 3. But I need in the
>> order 2 3 4. Is it possible?
>
> Possibility (1): Unless you use INTEGER PRIMARY KEY and supply your own
> values for the key column, the ROWID pseudocolumn will be automatically
> given values in ascending sequence. Then you can do queries like:
>
> SELECT * FROM your_table ORDER BY ROWID;
>
> Possibility (2): Have a column named e.g. when_created and populate it
> with CURRENT_TIMESTAMP either implicitly using a DEFAULT column
> constraint in CREATE TABLE, or explicitly when you INSERT. Note: the
> precision of the clock (1 second) may not be enough; you may wish to use
>   ROWID as a tie-breaker in your ORDER BY clause.
>
> ... *but* why do you want to recover stuff in insertion order?
>
> HTH,
>
> John
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] Transaction isolation

[Yang Zhang]

Roger Binns wrote:
> Yang Zhang wrote:
>> Actually, this is only because Python 3 str is Python 2 unicode.  Python 
>> 2 (which I'm currently using, and which I believe most of the world is 
>> using) str is a physical string of bytes, not a logical/decoded 
>> character string.  Python 2.6 introduces bytes as a synonym for str, but 
>> I am using Python 2.5 at the moment.
> 
> This is all pedantically true, but it is still a really bad way to
> structure your program?  Did you read the Joel Unicode and character
> sets link?

I *have* in fact read that article a very, very long time ago, but that 
is besides the point.  I am aware of character encoding issues, thanks.

> 
> It was because Python 2 messed up on bytes versus strings versus unicode
> that they had to clean it up in Python 3.  It is also why the SQLite
> wrappers in Python 2 return blobs as the buffer type so that there is no
> accidental mingling of bytes and strings.  (Disclosure: I am the author
> of the APSW wrapper)  SQLite *only* supports Unicode strings.  Other
> databases do support non-Unicode strings, character set conversions and
> all that other complexity.

I require str because that is what cPickle.loads() requires; you cannot 
pass it a buffer.  I need to store pickled objects in the database 
because I am implementing an SQLite backend for the Python 2 shelve module.
-- 
Yang Zhang
http://www.mit.edu/~y_z/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Accessing sqlite using javascript

[Saurabh Pawar]

I tried both the options.EXTJS lib is for firefox 1.5+.So it wont work.

The snippet is giving an error --
Permission denied to get property XPCComponents.classes

Are you aware of this error?.

Thank you.



- Original Message 
From: Neville Franks 
To: sqlite-users@sqlite.org
Sent: Monday, 18 May, 2009 2:16:52 PM
Subject: Re: [sqlite] Accessing sqlite using javascript

Monday, May 18, 2009, 3:16:45 PM, you wrote:

SP> I had asked the same question a few days back,but need a some more help.
SP> I am having my whole SQLite database on client's machine.I want a
SP> way to access that db using javscript.I heard about gears,but the
SP> problem is my target PC has firefox 1.08 and gears is for 1.5+.So
SP> is there any other way around?...Also is there any tutorial or
SP> guide which i can have online for referring when i am writing the code?.

SP> Thank you.

There are several ways to do this. From Firefox see:
http://codesnippets.joyent.com/posts/show/1030

The ExtJS Library also provides access to SQLite, but I have not used
that part of ExtJS yet.

Finally Google: "sqlite from javascript".

---
Best regards,
  Neville Franks, http://www.surfulater.com http://blog.surfulater.com


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



  Explore and discover exciting holidays and getaways with Yahoo! India 
Travel http://in.travel.yahoo.com/

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

[sqlite] SQLite version 3.6.14.1

[D. Richard Hipp]

SQLite version 3.6.14.1 is now available on the sqlite website:  
http://www.sqlite.org/

Version 3.6.14.1 is a branch of version 3.6.14 with patches applied to  
fix three bugs.  See http://www.sqlite.org/releaselog/3_6_14_1.html  
fpr details.  The changes are minimal and users who are not bothered  
by any of the three bugs do not need not upgrade.  Under normal  
circumstances, we would have let these three fixes accumulate in the  
SQLite source tree until the next regular release in June.  But an  
SQLite Consortium member requested expedited resolution of the bugs  
and so we are making these fixes available in this unscheduled version  
3.6.14.1 release.

As always, please let me know if you encounter any difficulties.

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] How make atomic? Was: Sqlite as a FIFO buffer?

[Allen Fowler]

> Thank you.
> 
> I missed the EXCLUSIVE clause in the docs  comes with the newbie 
> territory, 
> i guess.
> 
> So to confirm, would something like this work?
> 
> Tables:
> task_log => (id, task_data, time_stamp)
> task_fifo = > (id, fk_task_log)
> task_status_log => (id, fk_task_log, status_code, time_stamp)
> 
> And in psudo SQL:  
> 
> TRIGGER ON INSERT INTO task_log:
> BEGIN
> INSERT INTO task_fifo (fk_task_log) VALUES (NEW.id)
> END;
> 
> 
> And then, again in psudo SQL, the worker does something like:
> 
> BEGIN EXCLUSIVE TRANSACTION;
> INSERT INTO task_status_log  FROM SELECT OLDEST IN task_fifo ;
> DELETE FROM task_fifo  OLDEST; 
> COMMIT;
> 
> Is there a better way to do this?  (views?)
> 

OK, wait. I certainly am missing something here.

At this point, how do I now retrieve the id & task_data for the task?

(Yet another newbie question)

Thanks



  

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

Re: [sqlite] How make atomic? Was: Sqlite as a FIFO buffer?

[Allen Fowler]

> Wrap the above two statements in:
> 
> 0) BEGIN EXCLUSIVE
> ...
> 3) COMMIT
> 
> The BEGIN EXCLUSIVE above is all you need (and more, a simple BEGIN  
> may be enough).
> 
> > Can someone with more knowledge of SQLite internals explain the  
> > right way to "atomic"-lly "pop"-off an item from table in SQlite?  
> > (And, in this case, also add it to a 2nd table.)
> 
> The above sequence of 4 statements is atomic.
> 

Thank you.

I missed the EXCLUSIVE clause in the docs  comes with the newbie territory, 
i guess.

So to confirm, would something like this work?

Tables:
task_log => (id, task_data, time_stamp)
task_fifo = > (id, fk_task_log)
task_status_log => (id, fk_task_log, status_code, time_stamp)

And in psudo SQL:  

TRIGGER ON INSERT INTO task_log:
BEGIN
INSERT INTO task_fifo (fk_task_log) VALUES (NEW.id)
END;


And then, again in psudo SQL, the worker does something like:

BEGIN EXCLUSIVE TRANSACTION;
INSERT INTO task_status_log  FROM SELECT OLDEST IN task_fifo ;
DELETE FROM task_fifo  OLDEST; 
COMMIT;

Is there a better way to do this?  (views?)

Thanks again,
:)



  

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

Re: [sqlite] How make atomic? Was: Sqlite as a FIFO buffer?

[Doug Currie]

On May 18, 2009, at 5:32 PM, Allen Fowler wrote:

>>> The simple solution would just create a race condition... i think:
>>>
>>> 1) INSERT INTO status_table FROM SELECT oldest task in queue
>>> 2) DELETE oldest task in queue
>>>
>>> Right?
>>
>> It might work fine if you wrap it in an exclusive
>> transaction.
>>
>
>
> "exclusive transaction"? Great!  How do I do that?  :)

Wrap the above two statements in:

0) BEGIN EXCLUSIVE
...
3) COMMIT

> From reading http://www.sqlite.org/lockingv3.html it sounds like  
> SQLite very rarely will want to gain an exclusive lock.  I think,  
> not even issuing an INSERT will do that until other factors cause it  
> to flush to disk.

The BEGIN EXCLUSIVE above is all you need (and more, a simple BEGIN  
may be enough).

> Can someone with more knowledge of SQLite internals explain the  
> right way to "atomic"-lly "pop"-off an item from table in SQlite?   
> (And, in this case, also add it to a 2nd table.)

The above sequence of 4 statements is atomic.

e

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

Re: [sqlite] How make atomic? Was: Sqlite as a FIFO buffer?

[Allen Fowler]

> Have you considered using a more generic message queuing program?
> Wikipedia has a good page about it:
> 
>   http://en.wikipedia.org/wiki/Message_queue
> 
> There is even a standardised protocol - AMQP:
> 
>   http://en.wikipedia.org/wiki/Advanced_Message_Queuing_Protocol
> 
> You could just go ahead an use a free MQ server such as RabbitMQ.  At
> the very least it would be worthwhile structuring your internal APIs to
> be similar to those in wide use even while using a SQLite backend, since
> that would make it easier to switch to an alternate implementation, or
> to contribute your SQLite based implementation back.
> 

Thank you for the RabbitMQ link   I will read up on it.

For now, though, I was hopping to K.I.S.S. and just use Python for my tasks 
without any extra long-running processes involved.   (Just CGI and cron for 
now.)

I was under the impression that folks use SQL for simple message FIFO's all the 
time  am I wrong on this?


Thank you



  

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

Re: [sqlite] How make atomic? Was: Sqlite as a FIFO buffer?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Have you considered using a more generic message queuing program?
Wikipedia has a good page about it:

  http://en.wikipedia.org/wiki/Message_queue

There is even a standardised protocol - AMQP:

  http://en.wikipedia.org/wiki/Advanced_Message_Queuing_Protocol

You could just go ahead an use a free MQ server such as RabbitMQ.  At
the very least it would be worthwhile structuring your internal APIs to
be similar to those in wide use even while using a SQLite backend, since
that would make it easier to switch to an alternate implementation, or
to contribute your SQLite based implementation back.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkoR2iwACgkQmOOfHg372QTBFgCfdGWCRMpCqZwQnCRPeKU4Vmjj
xgcAn0YW2IRXyyPwkoFFadEn8fW+mLjE
=HduX
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Yang Zhang wrote:
> Actually, this is only because Python 3 str is Python 2 unicode.  Python 
> 2 (which I'm currently using, and which I believe most of the world is 
> using) str is a physical string of bytes, not a logical/decoded 
> character string.  Python 2.6 introduces bytes as a synonym for str, but 
> I am using Python 2.5 at the moment.

This is all pedantically true, but it is still a really bad way to
structure your program?  Did you read the Joel Unicode and character
sets link?

It was because Python 2 messed up on bytes versus strings versus unicode
that they had to clean it up in Python 3.  It is also why the SQLite
wrappers in Python 2 return blobs as the buffer type so that there is no
accidental mingling of bytes and strings.  (Disclosure: I am the author
of the APSW wrapper)  SQLite *only* supports Unicode strings.  Other
databases do support non-Unicode strings, character set conversions and
all that other complexity.

It is your code and you can do whatever pleases you.  However the advice
still stands - keep your strings and bytes/blobs separate, and using the
buffer type in Python 2 (and bytes in Python 3) is an excellent way of
doing that.  The wrappers already do this because it is good practise.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkoR10IACgkQmOOfHg372QQyjwCfXTwHMBsdAznHfDZ8CeaQIGNH
T64Anj5qvy6MjjL/K08xi5CPY7pxueEi
=Zet9
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How make atomic? Was: Sqlite as a FIFO buffer?

[Allen Fowler]

> >The simple solution would just create a race condition... i think:
> >
> >1) INSERT INTO status_table FROM SELECT oldest task in queue
> >2) DELETE oldest task in queue
> >
> >Right?
> 
> It might work fine if you wrap it in an exclusive
> transaction.
> 


"exclusive transaction"? Great!  How do I do that?  :)

>From reading http://www.sqlite.org/lockingv3.html it sounds like SQLite very 
>rarely will want to gain an exclusive lock.  I think, not even issuing an 
>INSERT will do that until other factors cause it to flush to disk.

Can someone with more knowledge of SQLite internals explain the right way to 
"atomic"-lly "pop"-off an item from table in SQlite?  (And, in this case, also 
add it to a 2nd table.)



Thank you,
Allen


  

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

Re: [sqlite] error in documentation of SELECT?

[D. Richard Hipp]

On May 18, 2009, at 2:53 PM, Pavel Ivanov wrote:

> Actually I wanted to know if it can be useful somewhere. :-)

I already shown you one useful thing to do with a SELECT that omits  
the FROM clause:  Determine the version of SQLite you are running  
using "SELECT  sqlite_version()".

In applications I write, I typically have an SQLite database  
connection open and the infrastructure in place to get query results  
easily, and so I find queries such as the following to be useful and  
convenient:

 SELECT datetime('now');-- Get the current date and time in  
IS0-8601

 SELECT lower(hex(randomblob(32)));  -- Get a universally unique  
identifier

The original reason that SELECT without FROM was added is so that one  
could invoke application-defined functions, or the RAISE() function,  
from within triggers:

 CREATE TRIGGER ex1 AFTER UPDATE ON table1 BEGIN
 SELECT do_something_using_c_code();
 END;

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Sqlite as a FIFO buffer? (How make atomic?)

[Kees Nuyt]

On Mon, 18 May 2009 12:17:25 -0700 (PDT), Allen Fowler
 wrote:

>
>> >I have several CGI and cron scripts and that I would like coordinate via a 
>
>> "First In
>> >/ First Out" style buffer.That is, some processes are adding work
>> >units, and some take the oldest and start work on them.
>> >
>> >Could SQLite be used for this?  
>> >
>>
>> For what it's worth, here you go.
>> Perhaps you can borrow a few ideas from it.
>> 
>
>
>Thank you for posting the code.  
>I'll try to look through it. 
> (Like I said, I've never used complex SQL before... and for me this is 
> complex.)

>Can you point me to the part that takes care of making
>an atomic removal of a task from the queue, such that
>one and only one worker process can get access to a task?
>That's what's got me stumped.

It's not guaranteed here, I think. The code is used on a
site with very low concurrency.

My 'solution' has only one worker, the dispatcher.
I use schtask.exe to schedule dispatchers, it was the only
way I could find to run something on windows outside the
context of Apache/PHP.
(the at utility would have been better, but it wasn't
available to my account profile).

Every time a new job is submitted, any previously scheduled
dispatchers (which don't run yet) are removed from the
scheduler queue. Then the new dispatcher is scheduled to
run. Once it starts, the dispatcher runs all waiting jobs it
can find, one by one, and exits when all jobs are done.
In hindsight I don't like my code that much ;)

So I guess this doesn't solve your problem.

On Linux/Unix, you could implement a similar dispatcher,
which would be the only process which removes tasks from the
sqlite queue and starts each task as a background job.

>The simple solution would just create a race condition... i think:
>
>1) INSERT INTO status_table FROM SELECT oldest task in queue
>2) DELETE task in queue
>
>Right?

It might work fine if you wrap it in an exclusive
transaction.

>Thank you,
>AF
>
>
>
>P.S.
>
>Am I correct to assume your code is a more flashed out version of what I was 
>trying to do before  
>
>Table: task_log => (id, task_data, time_stamp)
>Table: task_fifo = > (id, fk_task_log)
>Table: task_status_log => (id, fk_task_log, status_code, time_stamp)
>
>And in psudo SQL:  
>
>TRIGGER ON INSERT INTO task_log:
>BEGIN
>INSERT INTO task_fifo (fk_task_log) VALUES (NEW.id)
>END;
>
>TRIGGER ON DELETE FROM task_fifo:
>BEGIN
>INSERT INTO task_status_log VALUES (OLD.fk_task_log, "CLAIMED")
>END;
>
>
>And then, again in psudo SQL, the worker does something like:
>
>DELETE 1 OLDEST FROM task_fifo;

I don't think it is exactly the same.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error in documentation of SELECT?

[Nuno Lucas]

On Mon, May 18, 2009 at 7:53 PM, Pavel Ivanov  wrote:
> Actually I wanted to know if it can be useful somewhere. :-)

You could have a calculator command on your application and let SQLite
parse the result for you ;-)

sqlite> SELECT 1+2*(3+4*5);
47


Regards,
~Nuno Lucas

>
> Pavel
>
> On Mon, May 18, 2009 at 2:52 PM, Noah Hart  wrote:
>> Just because the syntax allows it, doesn't mean that it will be useful
>>
>> SELECT '1', sqlite_version() as A where A < 'a' group by 1 order by 1
>>
>> Noah
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
>> Sent: Monday, May 18, 2009 11:37 AM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] error in documentation of SELECT?
>>
>> I didn't notice it earlier and now I'm a bit surprised. Can I ask a
>> more elaborate example which will include WHERE and/or GROUP BY but
>> not include FROM?
>>
>> Pavel
>>
>>
>>
>>
>> CONFIDENTIALITY NOTICE:
>> This message may contain confidential and/or privileged information. If you 
>> are not the addressee or authorized to receive this for the addressee, you 
>> must not use, copy, disclose, or take any action based on this message or 
>> any information herein. If you have received this message in error, please 
>> advise the sender immediately by reply e-mail and delete this message. Thank 
>> you for your cooperation.
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[Yang Zhang]

Igor Tandetnik wrote:
> Yang Zhang  wrote:
>> Pavel Ivanov wrote:
>>> BTW, ACID that you mentioned has nothing to do with snapshot
>>> isolation that you want to achieve. AFAIK only Oracle supports this
>>> kind of statement isolation level.
>> Actually, Oracle, Postgresql, SQL Server, Firebird, and others support
>> snapshot isolation.  http://en.wikipedia.org/wiki/Snapshot_isolation
> 
> ... but not between two statements running within _the same 
> transaction_. Isolation level (snapshot or otherwise) describes how two 
> transactions are isolated from each other. In your example, you only 
> have one transaction, so any discussion of isolation levels is moot.

Right, I mean the whole reason why I originally wrote to this list was 
because I was under the (incorrect) impression that I was working with 
two separate transactions.
-- 
Yang Zhang
http://www.mit.edu/~y_z/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite as a FIFO buffer? (How make atomic?)

[Allen Fowler]

> >I have several CGI and cron scripts and that I would like coordinate via a 

> "First In
> >/ First Out" style buffer.That is, some processes are adding work
> >units, and some take the oldest and start work on them.
> >
> >Could SQLite be used for this?  
> >
>
> For what it's worth, here you go.
> Perhaps you can borrow a few ideas from it.
> 


Thank you for posting the code.  I'll try to look through it.  (Like I said, 
I've never used complex SQL before... and for me this is complex.)

Can you point me to the part that takes care of making an atomic removal of a 
task from the queue, such that one and only one worker process can get access 
to a task?   That's what's got me stumped.

The simple solution would just create a race condition... i think:

1) INSERT INTO status_table FROM SELECT oldest task in queue
2) DELETE task in queue

Right?

Thank you,
AF



P.S.

Am I correct to assume your code is a more flashed out version of what I was 
trying to do before  

Table: task_log => (id, task_data, time_stamp)
Table: task_fifo = > (id, fk_task_log)
Table: task_status_log => (id, fk_task_log, status_code, time_stamp)

And in psudo SQL:  

TRIGGER ON INSERT INTO task_log:
BEGIN
INSERT INTO task_fifo (fk_task_log) VALUES (NEW.id)
END;

TRIGGER ON DELETE FROM task_fifo:
BEGIN
INSERT INTO task_status_log VALUES (OLD.fk_task_log, "CLAIMED")
END;


And then, again in psudo SQL, the worker does something like:

DELETE 1 OLDEST FROM task_fifo;









> 
> = mkschema.sql =
> --
> -- schema for database job.db3
> --
> PRAGMA page_size=8192;
> PRAGMA default_cache_size=512;
> 
> CREATE TABLE statustext (
> statusCHAR(1) PRIMARY KEY DEFAULT NULL
> CONSTRAINT sttxt_valid_status CHECK (status IN 
> ('W','I','R','T','A','C')),
> sttext VARCHAR(16)
> );
> INSERT INTO statustext (status,sttext)
> VALUES ('W','Wait');
> INSERT INTO statustext (status,sttext)
> VALUES ('I','Initializing');
> INSERT INTO statustext (status,sttext)
> VALUES ('R','Running');
> INSERT INTO statustext (status,sttext)
> VALUES ('T','Terminated');
> INSERT INTO statustext (status,sttext)
> VALUES ('A','Abend');
> INSERT INTO statustext (status,sttext)
> VALUES ('C','Cancelled'); -- cancelled before dispatched
> 
> CREATE TABLE jobs (
> jobid  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
> TSN   CHAR(4),
> jobprio   INTEGER  DEFAULT 9
> CONSTRAINT jobs_valid_prio
> CHECK (jobprio > 0 AND jobprio < 10),
> statusCHAR(1)  DEFAULT 'W'
> CONSTRAINT jobs_valid_status
> CHECK (status IN ('W','I','R','T','A','C')),
> useridVARCHAR(8) NOT NULL,
> dtcreate  DATETIME DEFAULT CURRENT_TIMESTAMP,
> dtinitDATETIME DEFAULT NULL,
> dtstart   DATETIME DEFAULT NULL,
> dtstopDATETIME DEFAULT NULL,
> dtmodify  DATETIME DEFAULT CURRENT_TIMESTAMP,
> dtdnload  DATETIME DEFAULT NULL,
> cmnd  VARCHAR(254),  -- cmnd\*.cmd to execute
> pars  VARCHAR(254),  -- parameters for procedure 
> (host,userid,filename[,type,elementname])
> rcINTEGER,   -- ERRORLEVEL
> endmsgVARCHAR(254),  -- message from dispatcher
> sysoutVARCHAR(254),  -- logfile
> dnloadVARCHAR(254)   -- file to download
> );
> CREATE INDEX idx_jobs_tsn ON jobs(TSN);
> 
> CREATE TABLE pars (
> parid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
> jobid INTEGER CONSTRAINT fk_pars_jobs REFERENCES jobs (jobid)
> ON DELETE CASCADE,
> partx TEXT
> );
> 
> CREATE TRIGGER jobs_ins AFTER INSERT ON jobs
> FOR EACH ROW BEGIN
> UPDATE jobs 
> SET TSN = substr(1000 + NEW.jobid,5,4)
> WHERE jobid = NEW.jobid;
> DELETE FROM jobs 
> WHERE jobid < (NEW.jobid - ) 
> AND TSN <= NEW.TSN;
> END;
> 
> CREATE TRIGGER jobs_upd AFTER UPDATE ON jobs
> FOR EACH ROW BEGIN
> UPDATE jobs SET
> rc   = CASE
> WHEN OLD.status == 'W' AND  NEW.status == 'I'
> THEN NULL
> ELSE NEW.rc END,
> endmsg   = CASE
> WHEN OLD.status == 'W' AND  NEW.status == 'I'
> THEN NULL
> ELSE NEW.endmsg END,
> dtinit   = CASE
> WHEN OLD.status == 'W' AND  NEW.status == 'I'
> THEN CURRENT_TIMESTAMP
> ELSE OLD.dtinit END,
> dtstart  = CASE
> WHEN OLD.status == 'I' AND  NEW.status == 'R'
> THEN CURRENT_TIMESTAMP
> ELSE OLD.dtstart END,
> dtstop   = CASE
> WHEN OLD.status == 'R' AND (NEW.status == 'A' OR NEW.status == 
> 'T') 
> THEN CURRENT_TIMESTAMP
> ELSE OLD.dtstop END,
> dtmodify = CASE
> WHEN NEW.status = OLD.status THEN dtmodify
> ELSE CURRENT_TIMESTAMP END
> WHERE jobid = NEW.jobid;
> END;
> 
> -- on delete cascade
> CREATE TRIGGER jobs_del AFTER DELETE ON jobs
> FOR EACH ROW BEGIN
> DELETE FROM pars WHERE pars.jobid = OLD.jobid;
> END;
> 
> -- references
> CREATE TRIGGER fki_pars_jobs
> BEFORE INS

Re: [sqlite] Transaction isolation

[Igor Tandetnik]

Yang Zhang  wrote:
> Pavel Ivanov wrote:
>> BTW, ACID that you mentioned has nothing to do with snapshot
>> isolation that you want to achieve. AFAIK only Oracle supports this
>> kind of statement isolation level.
>
> Actually, Oracle, Postgresql, SQL Server, Firebird, and others support
> snapshot isolation.  http://en.wikipedia.org/wiki/Snapshot_isolation

... but not between two statements running within _the same 
transaction_. Isolation level (snapshot or otherwise) describes how two 
transactions are isolated from each other. In your example, you only 
have one transaction, so any discussion of isolation levels is moot.

Igor Tandetnik 



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

Re: [sqlite] error in documentation of SELECT?

[Pavel Ivanov]

Actually I wanted to know if it can be useful somewhere. :-)

Pavel

On Mon, May 18, 2009 at 2:52 PM, Noah Hart  wrote:
> Just because the syntax allows it, doesn't mean that it will be useful
>
> SELECT '1', sqlite_version() as A where A < 'a' group by 1 order by 1
>
> Noah
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
> Sent: Monday, May 18, 2009 11:37 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] error in documentation of SELECT?
>
> I didn't notice it earlier and now I'm a bit surprised. Can I ask a
> more elaborate example which will include WHERE and/or GROUP BY but
> not include FROM?
>
> Pavel
>
>
>
>
> CONFIDENTIALITY NOTICE:
> This message may contain confidential and/or privileged information. If you 
> are not the addressee or authorized to receive this for the addressee, you 
> must not use, copy, disclose, or take any action based on this message or any 
> information herein. If you have received this message in error, please advise 
> the sender immediately by reply e-mail and delete this message. Thank you for 
> your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[Yang Zhang]

Pavel Ivanov wrote:
> BTW, ACID that you mentioned has nothing to do with snapshot isolation
> that you want to achieve. AFAIK only Oracle supports this kind of
> statement isolation level.

Actually, Oracle, Postgresql, SQL Server, Firebird, and others support 
snapshot isolation.  http://en.wikipedia.org/wiki/Snapshot_isolation

And I certainly hope I did not convey that ACID implies snapshot isolation.
-- 
Yang Zhang
http://www.mit.edu/~y_z/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error in documentation of SELECT?

[Noah Hart]

Just because the syntax allows it, doesn't mean that it will be useful

SELECT '1', sqlite_version() as A where A < 'a' group by 1 order by 1

Noah

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
Sent: Monday, May 18, 2009 11:37 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] error in documentation of SELECT?

I didn't notice it earlier and now I'm a bit surprised. Can I ask a
more elaborate example which will include WHERE and/or GROUP BY but
not include FROM?

Pavel




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


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

Re: [sqlite] Transaction isolation

[Yang Zhang]

Roger Binns wrote:
> Yang Zhang wrote:
>> I copied and pasted this code straight from my actual application, which 
>> uses blobs instead of integers, which I need to convert into strings 
>> (since Python interfaces with blobs using the `buffer` type, not `str`).
> 
> And for very good reason.  Blobs are buckets of bytes and those are not
> strings.  In your example there was no need to do the conversion since
> you can supply buffers as values too.  (In Python 3 the bytes type is used.)

Actually, this is only because Python 3 str is Python 2 unicode.  Python 
2 (which I'm currently using, and which I believe most of the world is 
using) str is a physical string of bytes, not a logical/decoded 
character string.  Python 2.6 introduces bytes as a synonym for str, but 
I am using Python 2.5 at the moment.

 From http://mail.python.org/pipermail/python-list/2009-January/696449.html:

> In Python 2.x, str means "string of bytes". This has been renamed "bytes" 
> in Python 3.
> 
> In Python 2.x, unicode means "string of characters". This has been 
> renamed "str" in Python 3.
-- 
Yang Zhang
http://www.mit.edu/~y_z/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[John Elrick]

D. Richard Hipp wrote:
> On May 18, 2009, at 1:13 PM, John Elrick wrote:
>
>   
>> John Elrick wrote:
>> 
>>> SNIP
>>>
>>>   
> I say this because your example implies that the Python wrapper  
> starts
> the transaction automatically inside the execute, and I would not  
> be
> surprised if it did so BEFORE executing the SQL parameter.
>
>
>   
 The cursor() method that I call on the conn for the SELECT should  
 give
 me a separate transaction.

 
>>> Are you certain the wrapper is behaving that way?  As an experiment I
>>> altered my Ruby example to try to force it to go into an endless loop
>>> and failed (see below).  My experiments seem to confirm that Sqlite  
>>> is
>>> behaving as you expect, perhaps it is the wrapper which is not?
>>>
>>>   
>> Attempting this closer to the metal, it appears I was mistaken.  A
>> select from outside a transaction does indeed have visibility to rows
>> added inside the transaction.  I would not have expected this  
>> either, Yang.
>> 
>
>
> Double-check your findings, please John.
>
> The changes within an SQLite transaction are not visible to other  
> database connections until the transaction commits.  (However, they  
> are visible within the same database connection.)
>
> An exception to the previous paragraph is if you are using shared  
> cache mode and you do a PRAGMA read_uncommitted=ON;
>   

If I am understanding you correctly, we are saying the same things.  I 
have a single connection.  I am doing the following in order:

prepare select
start transaction
step select
replace...
loop until no more in select (in practice never terminates)
commit

The code is in Delphi (custom wrapper, heavily unit tested), so I 
started creating a version truly using bare metal calls, until I noticed 
your caveat "...changes within...not visible to other database 
CONNECTIONS...".

 From Yang's original code description he was attempting this within a 
single connection as is my test code.

Am I misunderstanding or is the above Working As Designed?  If I am 
misunderstanding and the above should terminate I will continue creating 
a test version using all direct calls to verify the result, as that 
would imply a bug in our Delphi wrapper.

Thanks,



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

Re: [sqlite] error in documentation of SELECT?

[Pavel Ivanov]

I didn't notice it earlier and now I'm a bit surprised. Can I ask a
more elaborate example which will include WHERE and/or GROUP BY but
not include FROM?

Pavel

On Mon, May 18, 2009 at 2:32 PM, D. Richard Hipp  wrote:
>
> On May 18, 2009, at 2:18 PM, Mitchell L Model wrote:
>
>> Is it really possible to have a SELECT with no FROM? If so, could
>> someone provide an example; i
>
> SELECT sqlite_version();
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error in documentation of SELECT?

[D. Richard Hipp]

On May 18, 2009, at 2:18 PM, Mitchell L Model wrote:

> Is it really possible to have a SELECT with no FROM? If so, could  
> someone provide an example; i

SELECT sqlite_version();

D. Richard Hipp
d...@hwaci.com



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

[sqlite] SQLite spawns multiple processes?

[jkimble]

Thanks for all the great responses.

Disabling threads in SQLite (and removing the pthreads lib from the
application build) seemed to fix things. We're still testing but we went
from crashing constantly to not being able to make it fail. Clearly
something's amiss with threads on this platform. We may or may not get to
the bottom of that but at the moment things are looking up.

Thanks again to all that contributed!

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

[sqlite] error in documentation of SELECT?

[Mitchell L Model]

I may be misreading the select-core diagram on 
http://www.sqlite.org/lang_select.html  but it appears that the down-arrow that 
would allow a query without a FROM clause should not be there. Is it really 
possible to have a SELECT with no FROM? If so, could someone provide an 
example; if not, would someone maintaining the documentation make a note of 
this? Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite as a FIFO buffer?

[Kees Nuyt]

On Sun, 17 May 2009 21:34:58 -0700 (PDT), Allen Fowler  
wrote:

>
>Hello,
>
>I have several CGI and cron scripts and that I would like coordinate via a 
>"First In
>/ First Out" style buffer.That is, some processes are adding work
>units, and some take the oldest and start work on them.
>
>Could SQLite be used for this?  
>
>It would seem very complex to use SQL for just a FIFO, but then again, SQLite 
>would take acre of all ACID / concurrency issues.
>
>Has this been done before?
>
>Thanks,
>:)

For what it's worth, here you go.
Perhaps you can borrow a few ideas from it.


= mkschema.sql =
--
-- schema for database job.db3
--
PRAGMA page_size=8192;
PRAGMA default_cache_size=512;

CREATE TABLE statustext (
statusCHAR(1) PRIMARY KEY DEFAULT NULL
CONSTRAINT sttxt_valid_status CHECK (status IN 
('W','I','R','T','A','C')),
sttext VARCHAR(16)
);
INSERT INTO statustext (status,sttext)
 VALUES ('W','Wait');
INSERT INTO statustext (status,sttext)
 VALUES ('I','Initializing');
INSERT INTO statustext (status,sttext)
 VALUES ('R','Running');
INSERT INTO statustext (status,sttext)
 VALUES ('T','Terminated');
INSERT INTO statustext (status,sttext)
 VALUES ('A','Abend');
INSERT INTO statustext (status,sttext)
 VALUES ('C','Cancelled'); -- cancelled before dispatched

CREATE TABLE jobs (
jobid  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
TSN   CHAR(4),
jobprio   INTEGER  DEFAULT 9
 CONSTRAINT jobs_valid_prio
 CHECK (jobprio > 0 AND jobprio < 10),
statusCHAR(1)  DEFAULT 'W'
 CONSTRAINT jobs_valid_status
 CHECK (status IN ('W','I','R','T','A','C')),
useridVARCHAR(8) NOT NULL,
dtcreate  DATETIME DEFAULT CURRENT_TIMESTAMP,
dtinitDATETIME DEFAULT NULL,
dtstart   DATETIME DEFAULT NULL,
dtstopDATETIME DEFAULT NULL,
dtmodify  DATETIME DEFAULT CURRENT_TIMESTAMP,
dtdnload  DATETIME DEFAULT NULL,
cmnd  VARCHAR(254),  -- cmnd\*.cmd to execute
pars  VARCHAR(254),  -- parameters for procedure 
(host,userid,filename[,type,elementname])
rcINTEGER,   -- ERRORLEVEL
endmsgVARCHAR(254),  -- message from dispatcher
sysoutVARCHAR(254),  -- logfile
dnloadVARCHAR(254)   -- file to download
);
CREATE INDEX idx_jobs_tsn ON jobs(TSN);

CREATE TABLE pars (
parid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
jobid INTEGER CONSTRAINT fk_pars_jobs REFERENCES jobs (jobid)
ON DELETE CASCADE,
partx TEXT
);

CREATE TRIGGER jobs_ins AFTER INSERT ON jobs
FOR EACH ROW BEGIN
UPDATE jobs 
SET TSN = substr(1000 + NEW.jobid,5,4)
WHERE jobid = NEW.jobid;
DELETE FROM jobs 
WHERE jobid < (NEW.jobid - ) 
AND TSN <= NEW.TSN;
END;

CREATE TRIGGER jobs_upd AFTER UPDATE ON jobs
FOR EACH ROW BEGIN
UPDATE jobs SET
rc   = CASE
WHEN OLD.status == 'W' AND  NEW.status == 'I'
 THEN NULL
ELSE NEW.rc END,
endmsg   = CASE
WHEN OLD.status == 'W' AND  NEW.status == 'I'
 THEN NULL
ELSE NEW.endmsg END,
dtinit   = CASE
WHEN OLD.status == 'W' AND  NEW.status == 'I'
 THEN CURRENT_TIMESTAMP
ELSE OLD.dtinit END,
dtstart  = CASE
WHEN OLD.status == 'I' AND  NEW.status == 'R'
 THEN CURRENT_TIMESTAMP
ELSE OLD.dtstart END,
dtstop   = CASE
WHEN OLD.status == 'R' AND (NEW.status == 'A' OR 
NEW.status == 'T') THEN CURRENT_TIMESTAMP
ELSE OLD.dtstop END,
dtmodify = CASE
WHEN NEW.status = OLD.status THEN dtmodify
ELSE CURRENT_TIMESTAMP END
WHERE jobid = NEW.jobid;
END;

-- on delete cascade
CREATE TRIGGER jobs_del AFTER DELETE ON jobs
FOR EACH ROW BEGIN
DELETE FROM pars WHERE pars.jobid = OLD.jobid;
END;

-- references
CREATE TRIGGER fki_pars_jobs
BEFORE INSERT ON pars
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'insert on table "pars" violates foreign key 
constraint "fk_pars_jobs(i)"')
WHERE NEW.jobid IS NOT NULL AND (SELECT jobid FROM jobs WHERE jobid = 
NEW.jobid) IS NULL;
END;

-- references
CREATE TRIGGER fku_pars_jobs
BEFORE UPDATE ON pars
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'update on table "pars" violates foreign key 
constraint "fk_pars_jobs(u)"')
WHERE NEW.jobid IS NOT NULL AND (SELECT jobid FROM jobs WHERE jobid = 
NEW.jobid) IS NULL;
END;

CREATE VIEW shjobsta AS -- for use in .cmd scripts
 SELECT
TSN
,status
,userid
,datetime(dtcreate,'localtime') AS spoolin
,datetime(dtinit  ,'localtime') AS dispatch
,datetime(dtstart ,'localtime') AS logon
,datetime(dtstop  ,

Re: [sqlite] Transaction isolation

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Yang Zhang wrote:
> I copied and pasted this code straight from my actual application, which 
> uses blobs instead of integers, which I need to convert into strings 
> (since Python interfaces with blobs using the `buffer` type, not `str`).

And for very good reason.  Blobs are buckets of bytes and those are not
strings.  In your example there was no need to do the conversion since
you can supply buffers as values too.  (In Python 3 the bytes type is used.)

Converting blobs to str is asking for future problems.  For example
SQLite does not verify that a string value is in fact legal UTF8.  Other
developers have played fast and loose ending up supplying a str when
they really meant a buffer/blob which then ended up in the database as a
string, but was invalid UTF8 when read back and causes an exception.

I also strongly recommend reading this article:

  http://www.joelonsoftware.com/articles/Unicode.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkoRoFUACgkQmOOfHg372QSIagCg5nllDS/Q5hsgfc+WzjS94Ubk
5jAAoONyqlKsczc1f+q01JqR6/Ysih2q
=TODs
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

John Elrick wrote:
> Are you certain the wrapper is behaving that way?  

The pysqlite wrapper (available as a sqlite3 Python module) by default
parses your SQL and starts and ends transactions behind your back, in
order to make it look like SQLite complies with some (IMHO pointless)
part of the Python DBAPI spec.

Fortunately it can be turned off which is what the isolation level stuff
in the docs are all about.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkoRnrIACgkQmOOfHg372QRhMwCeI52EqZ2/MR8AZK+/TPrF1nJt
RMAAn3P3MJ9J6hZHsZCzUQju1nbHr+aU
=Gnpl
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[D. Richard Hipp]

On May 18, 2009, at 1:13 PM, John Elrick wrote:

> John Elrick wrote:
>> SNIP
>>
 I say this because your example implies that the Python wrapper  
 starts
 the transaction automatically inside the execute, and I would not  
 be
 surprised if it did so BEFORE executing the SQL parameter.


>>> The cursor() method that I call on the conn for the SELECT should  
>>> give
>>> me a separate transaction.
>>>
>>
>> Are you certain the wrapper is behaving that way?  As an experiment I
>> altered my Ruby example to try to force it to go into an endless loop
>> and failed (see below).  My experiments seem to confirm that Sqlite  
>> is
>> behaving as you expect, perhaps it is the wrapper which is not?
>>
>
> Attempting this closer to the metal, it appears I was mistaken.  A
> select from outside a transaction does indeed have visibility to rows
> added inside the transaction.  I would not have expected this  
> either, Yang.


Double-check your findings, please John.

The changes within an SQLite transaction are not visible to other  
database connections until the transaction commits.  (However, they  
are visible within the same database connection.)

An exception to the previous paragraph is if you are using shared  
cache mode and you do a PRAGMA read_uncommitted=ON;

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Transaction isolation

[John Elrick]

John Elrick wrote:
> SNIP
>   
>>> I say this because your example implies that the Python wrapper starts 
>>> the transaction automatically inside the execute, and I would not be 
>>> surprised if it did so BEFORE executing the SQL parameter.
>>> 
>>>   
>> The cursor() method that I call on the conn for the SELECT should give 
>> me a separate transaction.
>> 
>
> Are you certain the wrapper is behaving that way?  As an experiment I 
> altered my Ruby example to try to force it to go into an endless loop 
> and failed (see below).  My experiments seem to confirm that Sqlite is 
> behaving as you expect, perhaps it is the wrapper which is not?
>   

Attempting this closer to the metal, it appears I was mistaken.  A 
select from outside a transaction does indeed have visibility to rows 
added inside the transaction.  I would not have expected this either, Yang.


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

Re: [sqlite] Transaction isolation

[John Elrick]

SNIP
>> I say this because your example implies that the Python wrapper starts 
>> the transaction automatically inside the execute, and I would not be 
>> surprised if it did so BEFORE executing the SQL parameter.
>> 
>
> The cursor() method that I call on the conn for the SELECT should give 
> me a separate transaction.

Are you certain the wrapper is behaving that way?  As an experiment I 
altered my Ruby example to try to force it to go into an endless loop 
and failed (see below).  My experiments seem to confirm that Sqlite is 
behaving as you expect, perhaps it is the wrapper which is not?


John

-
require 'sqlite3'
require 'erb'

db = SQLite3::Database.new(':memory:')

db.execute_batch(ERB.new(<').result(binding))
begin transaction;
create table shelf (
  key integer not null,
  value integer not null);

insert into shelf values (1, 1);
insert into shelf values (2, 2);
insert into shelf values (3, 3);

commit;
eof


SAFETY = 10
count = 0
db.transaction {
  db.execute('select key from shelf order by rowid').each do |i|
db.execute('replace into shelf (key, value) values(?,?)', i, i)
count += 1
abort if count == SAFETY
  end
}

puts db.execute('select * from shelf')
puts 'done'


1
1
2
2
3
3
1
1
2
2
3
3
done

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

Re: [sqlite] Transaction isolation

[Pavel Ivanov]

SQLite doesn't support several simultaneous transactions on the same
connection to database. So in fact your select and insert statements
execute in the same transaction. And even more: your inserts are not
committed until your select is completely finished. So for your task
you should use different connections for select and inserts. But it
will not work anyway, because SQLite doesn't support row-level
locking, it locks entire database. Thus your select statement on one
connection will block execution of inserts on another connection. So
you should stick with one of the solutions mentioned or switch to
another database engine that will fit you better.

BTW, ACID that you mentioned has nothing to do with snapshot isolation
that you want to achieve. AFAIK only Oracle supports this kind of
statement isolation level.


Pavel

On Mon, May 18, 2009 at 12:41 PM, Yang Zhang  wrote:
> John Elrick wrote:
>> Yang Zhang wrote:
>>> Roger Binns wrote:
>>>
 Yang Zhang wrote:

> for i in (str(row[0]) for row in conn.cursor().execute('SELECT key FROM
> shelf ORDER BY ROWID')):
>
 You are converting the key which is an integer into a string for no
 apparent reason.

>>> I copied and pasted this code straight from my actual application, which
>>> uses blobs instead of integers, which I need to convert into strings
>>> (since Python interfaces with blobs using the `buffer` type, not `str`).
>>>
>>>
 If you also ask for the ROWID you will see that what is happening is a
 new rowid is generated for the replaced row so that if you are iterating
 over the table while modifying it then you effectively have an infinite
 length table.

>>> This is unusual for a RDBMS that claims to provide ACID properties - in
>>> particular, this is not even as strong an isolation level as snapshot
>>> isolation, as a reader transaction is able to see a concurrent writer
>>> transaction's effects.  In fact, this is weaker than the weakest
>>> isolation level in (say) Postgresql, which is READ COMMITTED (in which
>>> any statement is guaranteed to not see the effects of a transaction that
>>> is committed after the query has started execution).
>>
>> As I am not an expert in the Python wrapper, I could be incorrect;
>> however, your code as written appears to be equivalent to the following:
>>
>> begin transaction
>> for select(
>>   insert stuff
>> end
>> commit
>>
>> rather than your intended:
>>
>> s = select(...
>> begin transaction
>> for s...
>>   insert stuff
>> end
>> commit
>>
>> I say this because your example implies that the Python wrapper starts
>> the transaction automatically inside the execute, and I would not be
>> surprised if it did so BEFORE executing the SQL parameter.
>
> The cursor() method that I call on the conn for the SELECT should give
> me a separate transaction.
> --
> Yang Zhang
> http://www.mit.edu/~y_z/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

[Yang Zhang]

Yang Zhang wrote:
> John Elrick wrote:
>> Yang Zhang wrote:
>>> Roger Binns wrote:
>>>  
 Yang Zhang wrote:

> for i in (str(row[0]) for row in conn.cursor().execute('SELECT key 
> FROM shelf ORDER BY ROWID')):
>   
 You are converting the key which is an integer into a string for no
 apparent reason.
 
>>> I copied and pasted this code straight from my actual application, 
>>> which uses blobs instead of integers, which I need to convert into 
>>> strings (since Python interfaces with blobs using the `buffer` type, 
>>> not `str`).
>>>
>>>  
 If you also ask for the ROWID you will see that what is happening is a
 new rowid is generated for the replaced row so that if you are 
 iterating
 over the table while modifying it then you effectively have an infinite
 length table.
 
>>> This is unusual for a RDBMS that claims to provide ACID properties - 
>>> in particular, this is not even as strong an isolation level as 
>>> snapshot isolation, as a reader transaction is able to see a 
>>> concurrent writer transaction's effects.  In fact, this is weaker 
>>> than the weakest isolation level in (say) Postgresql, which is READ 
>>> COMMITTED (in which any statement is guaranteed to not see the 
>>> effects of a transaction that is committed after the query has 
>>> started execution).
>>
>> As I am not an expert in the Python wrapper, I could be incorrect; 
>> however, your code as written appears to be equivalent to the following:
>>
>> begin transaction
>> for select(
>>   insert stuff
>> end
>> commit
>>
>> rather than your intended:
>>
>> s = select(...
>> begin transaction
>> for s...
>>   insert stuff
>> end
>> commit
>>
>> I say this because your example implies that the Python wrapper starts 
>> the transaction automatically inside the execute, and I would not be 
>> surprised if it did so BEFORE executing the SQL parameter.
> 
> The cursor() method that I call on the conn for the SELECT should give 
> me a separate transaction.

I also tried using separate connections, but that just ends up blocking 
and failing with a timeout on the lock acquisition because it appears 
that SQLite only has full-table locking, and not MVCC/snapshot 
isolation.  Do I need to manually extract out all the data first into 
another store, and then iterate over that to operate on original database?
-- 
Yang Zhang
http://www.mit.edu/~y_z/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite as a FIFO buffer?

[Allen Fowler]

> One thing to watch out for - using SQLITE for a FIFO will have limited 
> throughput, because commits will have to be done after inserting or removing 
> each entry. 

This is fine for now.  Wiling to migrate to MySQL, etc if needed for speed.


> This might not be an issue in some situations - I have actually 
> implemented a FIFO to communicate between two tasks where the work-per-entry 
> was 
> significant, the transaction rate was low, and the protection against 
> accidental 
> loss was paramount.
> 

Same deal.

Do you have any suggestions?  I'm very new SQL beyond the basic "CRUD" level.

As I mentioned in the other post, I'm guessing I need three tables...

task_log => (id, task_data, time_stamp)
task_fifo = > (id, fk_task_log)
task_status_log => (id, fk_task_log, status_code, time_stamp)

And in psudo SQL:  

TRIGGER ON INSERT INTO task_log:
BEGIN
INSERT INTO task_fifo (fk_task_log) VALUES (NEW.id)
END;

TRIGGER ON DELETE FROM task_fifo:
BEGIN
INSERT INTO task_status_log VALUES (OLD.fk_task_log, "CLAIMED")
END;


And then, again in psudo SQL, the worker does something like:

DELETE 1 OLDEST FROM task_fifo;

But I am not sure how get access to the task_id for the worker to retrieve the 
necessary data.

(Is this even the correct approach?)

Thank you,
:)


  

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

Re: [sqlite] Transaction isolation

[Yang Zhang]

John Elrick wrote:
> Yang Zhang wrote:
>> Roger Binns wrote:
>>   
>>> Yang Zhang wrote:
>>> 
 for i in (str(row[0]) for row in conn.cursor().execute('SELECT key FROM 
 shelf ORDER BY ROWID')):
   
>>> You are converting the key which is an integer into a string for no
>>> apparent reason.
>>> 
>> I copied and pasted this code straight from my actual application, which 
>> uses blobs instead of integers, which I need to convert into strings 
>> (since Python interfaces with blobs using the `buffer` type, not `str`).
>>
>>   
>>> If you also ask for the ROWID you will see that what is happening is a
>>> new rowid is generated for the replaced row so that if you are iterating
>>> over the table while modifying it then you effectively have an infinite
>>> length table.
>>> 
>> This is unusual for a RDBMS that claims to provide ACID properties - in 
>> particular, this is not even as strong an isolation level as snapshot 
>> isolation, as a reader transaction is able to see a concurrent writer 
>> transaction's effects.  In fact, this is weaker than the weakest 
>> isolation level in (say) Postgresql, which is READ COMMITTED (in which 
>> any statement is guaranteed to not see the effects of a transaction that 
>> is committed after the query has started execution).
> 
> As I am not an expert in the Python wrapper, I could be incorrect; 
> however, your code as written appears to be equivalent to the following:
> 
> begin transaction
> for select(
>   insert stuff
> end
> commit
> 
> rather than your intended:
> 
> s = select(...
> begin transaction
> for s...
>   insert stuff
> end
> commit
> 
> I say this because your example implies that the Python wrapper starts 
> the transaction automatically inside the execute, and I would not be 
> surprised if it did so BEFORE executing the SQL parameter.

The cursor() method that I call on the conn for the SELECT should give 
me a separate transaction.
-- 
Yang Zhang
http://www.mit.edu/~y_z/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite as a FIFO buffer?

[Christopher Taylor]

--
From: "Douglas E. Fajardo" 
Sent: Monday, May 18, 2009 12:25 PM
To: "General Discussion of SQLite Database" ; 
"AllenFowler" 
Subject: Re: [sqlite] Sqlite as a FIFO buffer?

> One thing to watch out for - using SQLITE for a FIFO will have limited 
> throughput, because commits will have to be done after inserting or 
> removing each entry. This might not be an issue in some situations - I 
> have actually implemented a FIFO to communicate between two tasks where 
> the work-per-entry was significant, the transaction rate was low, and the 
> protection against accidental loss was paramount.
>
> *** Doug F.
>
>
I have seen this.  I have two other tables where the inserts can take up to 
1 second to do.  For my application this is acceptable.  The events 
themselves are queued and there is enough idle time between jobs to catch up 
if needed.  Is there a better way to do this?

Chris 

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

Re: [sqlite] How to install the latest version of sqlite3 on Ubuntu?

[Alex Mandel]

Robert,

1. When you build you want to make sure to override the default
directory settings. ./configure --builddir=/usr/
Check the directions and configure script for options
(make sure to run make clean before you attempt to run make again.)

2. Use Checkinstall, it will build a deb file specific to your system
and install it. Instead of make install

Maybe that will help, FYI I forced the Jaunty deb into Hardy with no
issues, since the dependencies haven't changed.

Alex

Robert Villanoa wrote:
> Thank you very much Dan, because this is exactly the problem.
> I used ldd to check shared library dependencies of the executable file 
> sqlite3 and the result was:
> linux-gate.so.1 =>  (0xb7fc3000)
> libsqlite3.so.0 => /usr/lib/libsqlite3.so.0 (0xb7f4e000)
> libdl.so.2 => /lib/tls/i686/cmov/libdl.so.2 (0xb7f4a000)
> libpthread.so.0 => /lib/tls/i686/cmov/libpthread.so.0 (0xb7f31000)
> libc.so.6 => /lib/tls/i686/cmov/libc.so.6 (0xb7de2000)
> /lib/ld-linux.so.2 (0xb7fc4000)
> However, the file libsqlite3.so.0 from the sqlite3 3.6.14 package is 
> installed to the /usr/local/lib/ folder. I then deleted libsqlite3.so.0 in 
> /user/lib/ and then copied the one from /usr/local/lib/ to /usr/lib/ and I 
> got the correct version of sqlite3.
> Now I wonder is that enough? I mean whether simply copying libsqlite3.so.0 to 
> /usr/lib/ will let me use the new version completely? Or is there any further 
> modifications I must do for me to use sqlite3 3.6.14 'properly'?
> Thank you so much for your help!
> 
> 
> 
> 
> 
> From: Dan 
> To: General Discussion of SQLite Database 
> Sent: Monday, May 18, 2009 4:43:53 PM
> Subject: Re: [sqlite] How to install the latest version of sqlite3 on Ubuntu?
> 
> 
> On May 18, 2009, at 3:33 PM, Robert Villanoa wrote:
> 
>> Thank you for your answer, Jean-Denis.
>> When I type 'which sqlite3', I get the following location:
>> /usr/local/bin/sqlite3
>> And the value of my PATH variable is:
>> /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/ 
>> games
>> So I think the executable file sqlite3 is seen by PATH.
>> Could you please tell me any more suggestions?
> 
> $ ldd /usr/local/bin/sqlite3
> 
> Check it's linking to the correct libsqlite3.so.
> 
> Dan.
> 
> 
>>
>>
>>
>>
>>
>> 
>> From: Jean-Denis Muys 
>> To: General Discussion of SQLite Database 
>> Sent: Monday, May 18, 2009 2:50:38 PM
>> Subject: Re: [sqlite] How to install the latest version of sqlite3  
>> on Ubuntu?
>>
>>
>> On 5/18/09 9:19 AM, "Robert Villanoa"   
>> wrote:
>>
>>> Hi everyone,
>>>
>>> I am new to sqlite3. My OS is Ubuntu 8.04 and it has sqlite3 3.4.2.  
>>> Now I want
>>> to upgrade it to the latest version, 3.6.14, but I don't know how  
>>> to do that.
>>>
>>> Here are the steps I have done (after reading another thread about  
>>> this
>>> issue):
>>> 1. Remove the default version using 'sudo apt-get remove sqlite3'.
>>> 2. Download sqlite-amalgamation-3.6.14.tar.gz, extract the package,  
>>> go to the
>>> sqlite3 directory and run:
>>> - ./configure
>>> - make
>>> - sudo make install
>>> Although I did not encounter any error after executing these above  
>>> commands,
>>> it seemed that I did not install sqlite3 3.6.14 successfully.  
>>> Whenever I type
>>> 'sqlite3' into the GNOME terminal, it shows that the version is  
>>> 3.4.2.
>>> What's wrong with me? Please help! Thanks in advanced!
>> PATH problem? When you type "which sqlite3" in the terminal, what is  
>> the
>> result? Does it match your install location?
>>
>> JD


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

Re: [sqlite] Transaction isolation

[John Elrick]

Yang Zhang wrote:
> Roger Binns wrote:
>   
>> Yang Zhang wrote:
>> 
>>> for i in (str(row[0]) for row in conn.cursor().execute('SELECT key FROM 
>>> shelf ORDER BY ROWID')):
>>>   
>> You are converting the key which is an integer into a string for no
>> apparent reason.
>> 
>
> I copied and pasted this code straight from my actual application, which 
> uses blobs instead of integers, which I need to convert into strings 
> (since Python interfaces with blobs using the `buffer` type, not `str`).
>
>   
>> If you also ask for the ROWID you will see that what is happening is a
>> new rowid is generated for the replaced row so that if you are iterating
>> over the table while modifying it then you effectively have an infinite
>> length table.
>> 
>
> This is unusual for a RDBMS that claims to provide ACID properties - in 
> particular, this is not even as strong an isolation level as snapshot 
> isolation, as a reader transaction is able to see a concurrent writer 
> transaction's effects.  In fact, this is weaker than the weakest 
> isolation level in (say) Postgresql, which is READ COMMITTED (in which 
> any statement is guaranteed to not see the effects of a transaction that 
> is committed after the query has started execution).

As I am not an expert in the Python wrapper, I could be incorrect; 
however, your code as written appears to be equivalent to the following:

begin transaction
for select(
  insert stuff
end
commit

rather than your intended:

s = select(...
begin transaction
for s...
  insert stuff
end
commit

I say this because your example implies that the Python wrapper starts 
the transaction automatically inside the execute, and I would not be 
surprised if it did so BEFORE executing the SQL parameter.

In other words, you seem to be doing a select from INSIDE the 
transaction, not OUTSIDE.  If I understand correctly, that should indeed 
make the altered table visible to the select.  I believe your intention 
can be expressed with this example in Ruby (corrected to have a primary 
key as Igor noted):


require 'sqlite3'
require 'erb'

db = SQLite3::Database.new(':memory:')

db.execute_batch(ERB.new(<').result(binding))
begin transaction;
create table shelf (
  key integer primary key autoincrement,
  value integer not null);

insert into shelf values (1, 1);
insert into shelf values (2, 2);
insert into shelf values (3, 3);

commit;
eof

sel = db.execute('select key from shelf order by rowid')

db.transaction {
  sel.each do |i|
db.execute('replace into shelf (key, value) values(?,?)', i, i)
  end
}

puts 'done'


By executing the select outside of the context of the transaction, I 
obtain the desired transaction isolation.

FWIW,


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

Re: [sqlite] Sqlite as a FIFO buffer?

[Douglas E. Fajardo]

One thing to watch out for - using SQLITE for a FIFO will have limited 
throughput, because commits will have to be done after inserting or removing 
each entry. This might not be an issue in some situations - I have actually 
implemented a FIFO to communicate between two tasks where the work-per-entry 
was significant, the transaction rate was low, and the protection against 
accidental loss was paramount.

*** Doug F.


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Christopher Taylor
Sent: Monday, May 18, 2009 9:13 AM
To: Allen Fowler; General Discussion of SQLite Database
Subject: Re: [sqlite] Sqlite as a FIFO buffer?

The way this table works is that when an insert is made, the trigger is 
activated.  The trigger looks to see if there are more than a set number of 
records.  If so, the oldest record(s) are deleted by the trigger.  This is 
accomplished using an auto increment field.  This number increases by one on 
each insert.  Therefore I know that if the key field is greater than my 
limit I can safely delete any record whose key field is less than the max 
key minus the limit.  Hope this helps.

Chris 

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

Re: [sqlite] Sqlite as a FIFO buffer?

[Christopher Taylor]

The way this table works is that when an insert is made, the trigger is 
activated.  The trigger looks to see if there are more than a set number of 
records.  If so, the oldest record(s) are deleted by the trigger.  This is 
accomplished using an auto increment field.  This number increases by one on 
each insert.  Therefore I know that if the key field is greater than my 
limit I can safely delete any record whose key field is less than the max 
key minus the limit.  Hope this helps.

Chris 

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

Re: [sqlite] Sqlite as a FIFO buffer?

[Allen Fowler]

[Typo fix]

Thanks for the help

Though, I am not quite clear on how to get the FIFO aspect of it.

Assuming three tables:

task_log => (id, task_data, time_stamp)
task_fifo = > (id, fk_task_log)
task_status_log => (id, fk_task_log, status_code, time_stamp)

How do I create the correct stored procedures / triggers to make this work?

(Or, is this even the correct approach?)

Thank you,
:)



> 
> 
> - Original Message 
> > From: Christopher Taylor 
> > To: General Discussion of SQLite Database 
> > Sent: Monday, May 18, 2009 11:42:16 AM
> > Subject: Re: [sqlite] Sqlite as a FIFO buffer?
> > 
> > I took a slightly different approach and used a trigger.  This is the create
> > table function from my event log class.  The string handler is proprietary
> > but other than that there should be enough there to give you an idea.
> > 
> > void DbEventLog::CreateTable(sqlite3* pDatabase)
> > {
> >// create the table and insert the record
> >char* db_err;
> >CmnNarrowString createQuery = "create table tbl_EventLog (";
> >createQuery += "m_key integer primary key autoincrement, ";
> >createQuery += "m_eventCode integer, ";
> >createQuery += "m_timestamp integer, ";
> >createQuery += ");";
> >int rc = sqlite3_exec(pDatabase, createQuery.c_str(), NULL, 0, &db_err);
> >if ( rc != SQLITE_OK )
> >{
> >   // TODO - log error
> >}
> >else
> >{
> >   // create the index on the unique id used for queries from ics
> >   createQuery = "create index idx_EventLog on tbl_EventLog (m_key)";
> >   rc = sqlite3_exec(pDatabase, createQuery.c_str(), NULL, 0, &db_err);
> >   if ( rc != SQLITE_OK )
> >   {
> >  // TODO - log error
> >}
> >   else
> >   {
> >  createQuery = "create trigger trg_EventLog after insert on
> > tbl_EventLog begin delete from tbl_EventLog where m_key <= (SELECT
> > max(m_key) FROM tbl_EventLog) - 4000; end;";
> >  rc = sqlite3_exec(pDatabase, createQuery.c_str(), NULL, 0,
> > &db_err);
> >  if ( rc != SQLITE_OK )
> >  {
> > // TODO - log the error
> >  }
> >   }
> >}
> > }
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
>   
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Sqlite as a FIFO buffer?

[Allen Fowler]

Thanks for the help

Though, I am not quite clear on how to get the FIFO aspect of it.

Assuming three tables:

task_log => (id, code, time_stamp)
task_fifo = > (id, fk_task_log)
task_status_log => (id, fk_task_incoming_log, status_code, time_stamp)

How do I create the correct stored procedures / triggers to make this work?

(Or, is this even the correct approach?)

Thank you,
:)
 



- Original Message 
> From: Christopher Taylor 
> To: General Discussion of SQLite Database 
> Sent: Monday, May 18, 2009 11:42:16 AM
> Subject: Re: [sqlite] Sqlite as a FIFO buffer?
> 
> I took a slightly different approach and used a trigger.  This is the create
> table function from my event log class.  The string handler is proprietary
> but other than that there should be enough there to give you an idea.
> 
> void DbEventLog::CreateTable(sqlite3* pDatabase)
> {
>// create the table and insert the record
>char* db_err;
>CmnNarrowString createQuery = "create table tbl_EventLog (";
>createQuery += "m_key integer primary key autoincrement, ";
>createQuery += "m_eventCode integer, ";
>createQuery += "m_timestamp integer, ";
>createQuery += ");";
>int rc = sqlite3_exec(pDatabase, createQuery.c_str(), NULL, 0, &db_err);
>if ( rc != SQLITE_OK )
>{
>   // TODO - log error
>}
>else
>{
>   // create the index on the unique id used for queries from ics
>   createQuery = "create index idx_EventLog on tbl_EventLog (m_key)";
>   rc = sqlite3_exec(pDatabase, createQuery.c_str(), NULL, 0, &db_err);
>   if ( rc != SQLITE_OK )
>   {
>  // TODO - log error
>}
>   else
>   {
>  createQuery = "create trigger trg_EventLog after insert on
> tbl_EventLog begin delete from tbl_EventLog where m_key <= (SELECT
> max(m_key) FROM tbl_EventLog) - 4000; end;";
>  rc = sqlite3_exec(pDatabase, createQuery.c_str(), NULL, 0,
> &db_err);
>  if ( rc != SQLITE_OK )
>  {
> // TODO - log the error
>  }
>   }
>}
> }
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  

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

Re: [sqlite] Sqlite as a FIFO buffer?

[John Machin]

On 19/05/2009 1:42 AM, Christopher Taylor wrote:
> I took a slightly different approach and used a trigger.  This is the create
> table function from my event log class.  The string handler is proprietary
> but other than that there should be enough there to give you an idea.
> 
> void DbEventLog::CreateTable(sqlite3* pDatabase)
> {
>// create the table and insert the record
>char* db_err;
>CmnNarrowString createQuery = "create table tbl_EventLog (";
>createQuery += "m_key integer primary key autoincrement, ";
>createQuery += "m_eventCode integer, ";
>createQuery += "m_timestamp integer, ";

Are you sure about that comma?

C:\junk>sqlite3
SQLite version 3.6.14
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table foo(col0, col1, col2,);
SQL error: near ")": syntax error
sqlite>

>createQuery += ");";
>int rc = sqlite3_exec(pDatabase, createQuery.c_str(), NULL, 0, &db_err);
>if ( rc != SQLITE_OK )
>{
>   // TODO - log error
>}
>else
>{
>   // create the index on the unique id used for queries from ics
>   createQuery = "create index idx_EventLog on tbl_EventLog (m_key)";
>   rc = sqlite3_exec(pDatabase, createQuery.c_str(), NULL, 0, &db_err);
>   if ( rc != SQLITE_OK )
>   {
>  // TODO - log error
>}
>   else
>   {
>  createQuery = "create trigger trg_EventLog after insert on
> tbl_EventLog begin delete from tbl_EventLog where m_key <= (SELECT
> max(m_key) FROM tbl_EventLog) - 4000; end;";
>  rc = sqlite3_exec(pDatabase, createQuery.c_str(), NULL, 0,
> &db_err);
>  if ( rc != SQLITE_OK )
>  {
> // TODO - log the error
>  }
>   }
>}
> }
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

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

Re: [sqlite] Transaction isolation

[Yang Zhang]

Roger Binns wrote:
> Yang Zhang wrote:
>> for i in (str(row[0]) for row in conn.cursor().execute('SELECT key FROM 
>> shelf ORDER BY ROWID')):
> 
> You are converting the key which is an integer into a string for no
> apparent reason.

I copied and pasted this code straight from my actual application, which 
uses blobs instead of integers, which I need to convert into strings 
(since Python interfaces with blobs using the `buffer` type, not `str`).

> 
> If you also ask for the ROWID you will see that what is happening is a
> new rowid is generated for the replaced row so that if you are iterating
> over the table while modifying it then you effectively have an infinite
> length table.

This is unusual for a RDBMS that claims to provide ACID properties - in 
particular, this is not even as strong an isolation level as snapshot 
isolation, as a reader transaction is able to see a concurrent writer 
transaction's effects.  In fact, this is weaker than the weakest 
isolation level in (say) Postgresql, which is READ COMMITTED (in which 
any statement is guaranteed to not see the effects of a transaction that 
is committed after the query has started execution).

> 
>> Any way to solve this problem?  
> 
> You currently have the SELECT results being read back one at a time
> (lazily) on each iteration of the for loop.  The simplest solution is to
> read them all in first.  Add .fetchall() after the execute.

Unfortunately in the original application the table is large (many GBs). 
  Any way to solve this problem without first reading everything into 
memory, and without manually creating a second copy of the table?  Is 
there no way to request a stronger isolation level, such as snapshot 
isolation?
-- 
Yang Zhang
http://www.mit.edu/~y_z/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_MUTEX_APPDEF doc issue

[Brad House]

As of SQLite 3.6, SQLITE_MUTEX_APPDEF is no longer valid, but it is
still referenced here:
http://www.sqlite.org/c3ref/mutex_alloc.html

Probably want to make that change in the docs, specifically the
section which states:

"If SQLite is compiled with the SQLITE_MUTEX_APPDEF preprocessor macro defined 
(with "-DSQLITE_MUTEX_APPDEF=1"), then no mutex implementation is included with 
the library."

Should probably read:

"If SQLite is compiled with the SQLITE_MUTEX_NOOP preprocessor macro defined 
(with "-DSQLITE_MUTEX_NOOP"), then a stub implementation will be included with 
the library."

The remaining portion of that paragraph (not shown here) appears to be correct
and relevant.

This actually caught me off guard when migrating from 3.5 to 3.6, luckily
this page: http://www.sqlite.org/35to36.html mentioned that SQLITE_MUTEX_APPDEF
is no longer recognized.

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

Re: [sqlite] Sqlite as a FIFO buffer?

[Christopher Taylor]

I took a slightly different approach and used a trigger.  This is the create
table function from my event log class.  The string handler is proprietary
but other than that there should be enough there to give you an idea.

void DbEventLog::CreateTable(sqlite3* pDatabase)
{
   // create the table and insert the record
   char* db_err;
   CmnNarrowString createQuery = "create table tbl_EventLog (";
   createQuery += "m_key integer primary key autoincrement, ";
   createQuery += "m_eventCode integer, ";
   createQuery += "m_timestamp integer, ";
   createQuery += ");";
   int rc = sqlite3_exec(pDatabase, createQuery.c_str(), NULL, 0, &db_err);
   if ( rc != SQLITE_OK )
   {
  // TODO - log error
   }
   else
   {
  // create the index on the unique id used for queries from ics
  createQuery = "create index idx_EventLog on tbl_EventLog (m_key)";
  rc = sqlite3_exec(pDatabase, createQuery.c_str(), NULL, 0, &db_err);
  if ( rc != SQLITE_OK )
  {
 // TODO - log error
   }
  else
  {
 createQuery = "create trigger trg_EventLog after insert on
tbl_EventLog begin delete from tbl_EventLog where m_key <= (SELECT
max(m_key) FROM tbl_EventLog) - 4000; end;";
 rc = sqlite3_exec(pDatabase, createQuery.c_str(), NULL, 0,
&db_err);
 if ( rc != SQLITE_OK )
 {
// TODO - log the error
 }
  }
   }
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Corrupt Database Problems

[Jim Wilcoxson]

On 5/18/09, Kevin Gale  wrote:

> According to the PRAGMA command documentation the database might become
> corrupted if the o/s crashes or the computer loses power before the data has
> been written to the disk surface. From the information we have from the
> customer they continued to use the computer for some time after performing
> the final save. This indicates that there could be other situations that
> also cause this problem.

It really depends on when the OS decides to flush its cache.  All/most
OS's cache data you write before actually putting it on the drive.
These days, drives also cache writes.  When the application says "sync
this to disk", it's supposed to wait until the data is physically
recorded on the drive before returning.  But because of all the
caching going on at various levels, it's sometimes hard to ensure this
happens, and it's hard to verify that the write really did happen:
when you try to read the data, the OS gives it to you, either from its
cache (most likely) or from the drive, but you can't tell which was
the source.

You might want to do some crash tests with a virtual machine to find
out what's happening.

Good luck!
Jim
-- 
Software first.  Software lasts!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite as a FIFO buffer?

[Allen Fowler]

> > I have several CGI and cron scripts and that I would like coordinate via a 
> "First In
> > / First Out" style buffer.That is, some processes are adding work
> > units, and some take the oldest and start work on them.
> >
> > Could SQLite be used for this?  
> >
> > It would seem very complex to use SQL for just a FIFO, but then again, 
> > SQLite 
> would take acre of all ACID / concurrency issues.
> >
> > Has this been done before?
> 
> You should be able to implement a classic circular buffer in SQL and 
> make it a VIEW for easy access.  ROWIDs can be the buffer pointers and a 
> second table can store the current values.
> 


Since this sounds like it's been done before, can you direct me to tutorial or 
how-to on the subject? (SQL newbie here...)


  

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

Re: [sqlite] Corrupt Database Problems

[Kevin Gale]

Hi Jim.

Thanks for your reply.

Unfortunately, it looks like the database is beyond repair. I can pull some of 
the data back via the rowid but it is only the configuration data for the 
document and not the user's data.

According to the PRAGMA command documentation the database might become 
corrupted if the o/s crashes or the computer loses power before the data has 
been written to the disk surface. From the information we have from the 
customer they continued to use the computer for some time after performing the 
final save. This indicates that there could be other situations that also cause 
this problem.

Anyway, in the next build of the software we have removed the PRAGMA command 
and have also changed the save code so that it keeps the previous couple of 
versions in a history sub-folder. If customers still get problems at least they 
should now be able to go back to a previous version.


Regards,
Kev.


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Jim Wilcoxson [pri...@gmail.com]
Sent: 15 May 2009 18:08
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Corrupt Database Problems

I think you have answered your own question.  If you use
synchronous=off, you are saying "I don't care much about this
database."  When you "save" documents, you are merely putting them in
a computer's cache (memory) and then confirming to the user that they
are on the hard drive, when they aren't necessarily there.

So, user clicks Save, program says it saved it, user turns off
computer, database is corrupt.  Don't know why this would happen all
of a sudden, unless maybe they upgraded their OS and it has decided to
cache volatile data longer to increase performance at the expense of
data integrity.

I hope you're able to rescue your data.  Someone else mentioned on
this list a while back that they could recover their data by doing
retrieval based on rowid: do a select * where rowid=1, then 2, then 3,
etc. until you get a failure.  Once you get a failure, the rest is
lost.

Good luck,
Jim

On 5/15/09, Kevin Gale  wrote:
...
> 4. synchronous is OFF (we have stopped setting this in the new build of our
> app).

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

[sqlite] database is locked and is malformed ANSWER

[s . breitholz]

This is an answer to the post of Dr. Hipp to my question. I tried several 
times to send the mail as
"Re: [sqlite] database is locked and is malformed" but it did not work. So 
I have to create a new thread.

Hello Dr. Hipp,

thank you for your response. First off all I want to apologize for my 
entry in the ticket on sqlite.org, feel free to delete it.
I will also look for an other possibility to format my emails, we use a 
Lotus Notes Client in the company and I don`t have
an influence on footers, perhaps I find something about the sending 
format. Should I add some html-tags for line feed?

We don`t use the PRAGMA synchronous=off anymore.

I read the information about the atomic commit behaviour when I started 
programing 2 or 3 years ago and now I had problems,
in studied it in detail again.

As far as I can judge it, we nearly fit all assumtions, but
1. We use a SiliconDrive SSD-D04G-3500 Flash Drive as Harddisk
2. We had problems in the past that fsync() was very slow (that`s why we 
had to use the pragma command)
The compilation of sqlite3 is done by our qnx expert, but I can get all 
settings and arrange changes very fast.

When I logged on to the corrupted machine there was the normal data.s3db 
with around 200k and a data.s3db-journal file.
I tried to use the database, but only got errors. Than I tried to .dump 
it, but this also did not work. Than I, perhaps unfortunately,
deleted the journal file and dumped again, with the result, that the new 
data.s3db was only 80k big and the most important
table was completely empty. Afterwards I told the customer to restore a 
backup which is one week old (absolutely healthy).
I still could get the corrupted database, but after all I read it want 
help without the journal file.
I did that all under extreme time pressure, the machine was standing the 
whole sunday and the customer was very angry.
So I did`t try something with the journal file, I can´t say something 
about the size and the readability. Next time I know better.

To the situation on the machine:
I tried to do most ot the inserts to the database by a single thread. All 
other programs send message to that "database manager"
and it makes the inserts. That is true for about 95% off all inserts. Now, 
after thinking two days about the crash and reading your
advices, I believe that the crash had nothing to do with concurrent 
inserts. On power off I may get many errors from devices of the
machine (most devices loose power earlier than the controll unit). So the 
possibility of writing exact at the time when the controll
unit losses power is very high.

What I`m going to do next:
1. I will try to make the behaviour in case off power loss saver. There 
must be a way to check the situation before starting an insert.

2. I will try to make all Inserts that belong together as one atomic 
commit. Is it enought to write serveral Inserts in one sqlite3_exec() call
or must I do such a procedure with manual steps ?

3. I will try the PRAGMA journal_mode=PERSIST option to increase the speed 
of inserts

4. I will test the sqlite3_busy_timeout() command as I`m not happy with my 
current solution




Best regards / Mit freundlichen Grüssen

Stefan Breitholz

---
Staeubli GmbH - Theodor-Schmidt-Str. 19
DE - 95448 Bayreuth
Phone: +49(0)921/883-126 Fax: +49(0)921/883-58126
mailto:s.breith...@staubli.com
http://www.staubli.com

Registered under HRB 175 - Local Court Bayreuth - Managing Director: Karl 
Kirschner
---


This e-mail and any attachment (the 'message') are confidential and privileged 
and intended solely for the person or the entity to which it is adressed. If 
you have received it in error, please advise the sender by return e-mail and 
delete it immediately. Any use not in accordance with its purpose, any 
dissemination or reproduction, either whole or partial, by entities other than 
the intended recipient is strictly prohibited.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite as a FIFO buffer?

[John Stanton]

You should be able to implement a classic circular buffer in SQL and 
make it a VIEW for easy access.  ROWIDs can be the buffer pointers and a 
second table can store the current values.

Allen Fowler wrote:
> Hello,
>
> I have several CGI and cron scripts and that I would like coordinate via a 
> "First In
> / First Out" style buffer.That is, some processes are adding work
> units, and some take the oldest and start work on them.
>
> Could SQLite be used for this?  
>
> It would seem very complex to use SQL for just a FIFO, but then again, SQLite 
> would take acre of all ACID / concurrency issues.
>
> Has this been done before?
>
> Thanks,
> :)
>
>
>
>   
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>   

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

Re: [sqlite] Building select+in queries from C

[Carl Bretteville]

Thanks Igor, I'll give this a try.
Cheers,
Carl

On Mon, May 18, 2009 at 2:27 PM, Igor Tandetnik  wrote:

> "Carl Bretteville"
>  wrote in message
> news:24f328900905180515n3ef4bd72h96777a3a37054...@mail.gmail.com
> > I'm trying to find the best and most efficient way to build a
> > "select" query that uses "in" from C-code where the number of values
> > in the list varies from query to query. The sql statement will look
> > something like this:
> >
> >select * from table1 where name in (name1... nameX)
>
> Create a temporary table, populate it with your names, then do
>
> select * from table1 where name in (select name from myTempTable);
>
> You can use prepared statements for all parts of this process.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Building select+in queries from C

[Igor Tandetnik]

"Carl Bretteville"
 wrote in message
news:24f328900905180515n3ef4bd72h96777a3a37054...@mail.gmail.com
> I'm trying to find the best and most efficient way to build a
> "select" query that uses "in" from C-code where the number of values
> in the list varies from query to query. The sql statement will look
> something like this:
>
>select * from table1 where name in (name1... nameX)

Create a temporary table, populate it with your names, then do

select * from table1 where name in (select name from myTempTable);

You can use prepared statements for all parts of this process.

Igor Tandetnik



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

Re: [sqlite] SQLite on 64bit Windows XP

[少荃]

Thanks Knightfeng,
 
No it didnt work on my 64-Bit System. i wrote a Script in Matlab with the 
interface mksqlite, it works perfect on 32-bit system, but on 64-Bit System 
Matlab outputs always an Error.

--- 09年5月18日，周一, knightfeng  写道：


发件人: knightfeng 
主题: Re: [sqlite] SQLite on 64bit Windows XP
收件人: "General Discussion of SQLite Database" 
日期: 2009年5月18日,周一,下午7:43


Hi Quan Shao,

       Which one do you want to use? The command-line version or the C/C++ API 
? I don't think you need to do any special configuration. The command-line 
version is a stand-alone .exe file, which could be started directly and you can 
just use MinGW to compile the source code with your own code if you use the 
C/C++ API.  Didn't it work ?

Best.

Zhixing


2009-05-18 



knightfeng 



发件人： 少荃 
发送时间： 2009-05-18  18:23:06 
收件人： sqlite-users 
抄送： 
主题： [sqlite] SQLite on 64bit Windows XP 

Hi all,

does anybody know how i get started with the SQLite suportting the 64-Bit 
WindowsXP? do i need to intall something or do some configurations? i've heard 
somwhere that we have certain dll's those should be appended into the \sqlite 
folder...

thank you for your help!

  ___ 
  好玩贺卡等你发，邮箱贺卡全新上线！ 
http://card.mail.cn.yahoo.com/ 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  ___ 
  好玩贺卡等你发，邮箱贺卡全新上线！ 
http://card.mail.cn.yahoo.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Building select+in queries from C

[Carl Bretteville]

Hi,
I'm trying to find the best and most efficient way to build a "select" query
that uses "in" from C-code where the number of values in the list varies
from query to query. The sql statement will look something like this:

select * from table1 where name in (name1... nameX)

I've found two approaches that work:

- Build the sql statement by creating a long string where the entire list is
sprintf'd into a buffer and it is passed to sqlite3_prepare_v2 before
calling sqlite3_step

 select * from table1 where name in (name1, name2, name3)

- Build an argument string and put that in the sql statement before it goes
to sqlite3_prepare_v2

 select * from table1 where name in (?, ?, ?)

then bind the values one by one using sqlite3_bind_text before calling
sqlite3_step

Both work, but as the values I'm searching for varies for each query both in
the names and the number for names searched for reusing a prepared statement
isn't possible.

Is there a way I can prepare the select and supply a variable list of values
to look for in a bind later on?

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

Re: [sqlite] Transaction isolation

[Igor Tandetnik]

"Roger Binns"  wrote in
message news:4a1127e1.1040...@rogerbinns.com
> If you also ask for the ROWID you will see that what is happening is a
> new rowid is generated for the replaced row so that if you are
> iterating over the table while modifying it then you effectively have
> an infinite length table.

Note further that, since there are no UNIQUE or PRIMARY KEY constraints 
on any columns, REPLACE INTO is equivalent to INSERT INTO. In other 
words, no rows are being deleted, just new ones added.

Igor Tandetnik 



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

Re: [sqlite] Accessing sqlite using javascript

[Saurabh Pawar]

Thanks a lot.I was looking exactly for something like this



- Original Message 
From: Neville Franks 
To: sqlite-users@sqlite.org
Sent: Monday, 18 May, 2009 2:16:52 PM
Subject: Re: [sqlite] Accessing sqlite using javascript

Monday, May 18, 2009, 3:16:45 PM, you wrote:

SP> I had asked the same question a few days back,but need a some more help.
SP> I am having my whole SQLite database on client's machine.I want a
SP> way to access that db using javscript.I heard about gears,but the
SP> problem is my target PC has firefox 1.08 and gears is for 1.5+.So
SP> is there any other way around?...Also is there any tutorial or
SP> guide which i can have online for referring when i am writing the code?.

SP> Thank you.

There are several ways to do this. From Firefox see:
http://codesnippets.joyent.com/posts/show/1030

The ExtJS Library also provides access to SQLite, but I have not used
that part of ExtJS yet.

Finally Google: "sqlite from javascript".

---
Best regards,
  Neville Franks, http://www.surfulater.com http://blog.surfulater.com


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



  Explore and discover exciting holidays and getaways with Yahoo! India 
Travel http://in.travel.yahoo.com/

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

Re: [sqlite] SQLite on 64bit Windows XP

[knightfeng]

Hi Quan Shao,

   Which one do you want to use? The command-line version or the C/C++ API 
? I don't think you need to do any special configuration. The command-line 
version is a stand-alone .exe file, which could be started directly and you can 
just use MinGW to compile the source code with your own code if you use the 
C/C++ API.  Didn't it work ?

Best.

Zhixing


2009-05-18 



knightfeng 



发件人： 少荃 
发送时间： 2009-05-18  18:23:06 
收件人： sqlite-users 
抄送： 
主题： [sqlite] SQLite on 64bit Windows XP 
 
Hi all,

does anybody know how i get started with the SQLite suportting the 64-Bit 
WindowsXP? do i need to intall something or do some configurations? i've heard 
somwhere that we have certain dll's those should be appended into the \sqlite 
folder...

thank you for your help!

Wix
  ___ 
  好玩贺卡等你发，邮箱贺卡全新上线！ 
http://card.mail.cn.yahoo.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite on 64bit Windows XP

[少荃]

Hi all,
 
does anybody know how i get started with the SQLite suportting the 64-Bit 
WindowsXP? do i need to intall something or do some configurations? i've heard 
somwhere that we have certain dll's those should be appended into the \sqlite 
folder...
 
thank you for your help!
 
Wix


  ___ 
  好玩贺卡等你发，邮箱贺卡全新上线！ 
http://card.mail.cn.yahoo.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to install the latest version of sqlite3 on Ubuntu?

[Robert Villanoa]

Thank you very much Dan, because this is exactly the problem.
I used ldd to check shared library dependencies of the executable file sqlite3 
and the result was:
linux-gate.so.1 =>  (0xb7fc3000)
libsqlite3.so.0 => /usr/lib/libsqlite3.so.0 (0xb7f4e000)
libdl.so.2 => /lib/tls/i686/cmov/libdl.so.2 (0xb7f4a000)
libpthread.so.0 => /lib/tls/i686/cmov/libpthread.so.0 (0xb7f31000)
libc.so.6 => /lib/tls/i686/cmov/libc.so.6 (0xb7de2000)
/lib/ld-linux.so.2 (0xb7fc4000)
However, the file libsqlite3.so.0 from the sqlite3 3.6.14 package is installed 
to the /usr/local/lib/ folder. I then deleted libsqlite3.so.0 in /user/lib/ and 
then copied the one from /usr/local/lib/ to /usr/lib/ and I got the correct 
version of sqlite3.
Now I wonder is that enough? I mean whether simply copying libsqlite3.so.0 to 
/usr/lib/ will let me use the new version completely? Or is there any further 
modifications I must do for me to use sqlite3 3.6.14 'properly'?
Thank you so much for your help!





From: Dan 
To: General Discussion of SQLite Database 
Sent: Monday, May 18, 2009 4:43:53 PM
Subject: Re: [sqlite] How to install the latest version of sqlite3 on Ubuntu?


On May 18, 2009, at 3:33 PM, Robert Villanoa wrote:

> Thank you for your answer, Jean-Denis.
> When I type 'which sqlite3', I get the following location:
> /usr/local/bin/sqlite3
> And the value of my PATH variable is:
> /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/ 
> games
> So I think the executable file sqlite3 is seen by PATH.
> Could you please tell me any more suggestions?

$ ldd /usr/local/bin/sqlite3

Check it's linking to the correct libsqlite3.so.

Dan.


>
>
>
>
>
>
> 
> From: Jean-Denis Muys 
> To: General Discussion of SQLite Database 
> Sent: Monday, May 18, 2009 2:50:38 PM
> Subject: Re: [sqlite] How to install the latest version of sqlite3  
> on Ubuntu?
>
>
> On 5/18/09 9:19 AM, "Robert Villanoa"   
> wrote:
>
>> Hi everyone,
>>
>> I am new to sqlite3. My OS is Ubuntu 8.04 and it has sqlite3 3.4.2.  
>> Now I want
>> to upgrade it to the latest version, 3.6.14, but I don't know how  
>> to do that.
>>
>> Here are the steps I have done (after reading another thread about  
>> this
>> issue):
>> 1. Remove the default version using 'sudo apt-get remove sqlite3'.
>> 2. Download sqlite-amalgamation-3.6.14.tar.gz, extract the package,  
>> go to the
>> sqlite3 directory and run:
>> - ./configure
>> - make
>> - sudo make install
>> Although I did not encounter any error after executing these above  
>> commands,
>> it seemed that I did not install sqlite3 3.6.14 successfully.  
>> Whenever I type
>> 'sqlite3' into the GNOME terminal, it shows that the version is  
>> 3.4.2.
>> What's wrong with me? Please help! Thanks in advanced!
>
> PATH problem? When you type "which sqlite3" in the terminal, what is  
> the
> result? Does it match your install location?
>
> JD
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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



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

Re: [sqlite] How to install the latest version of sqlite3 on Ubuntu?

[Robert Villanoa]

Dear Jean Denis, to answer your question, I did the following:

After installing sqlite3 3.6.14, I executed the search command 
sudo find / -name "sqlite3"
to find all possible locations containing the executable file sqlite3. And the 
result I get is:
/home/robert/Desktop/sqlite-3.6.14/.libs/sqlite3
/home/robert/Desktop/sqlite-3.6.14/sqlite3
/usr/local/bin/sqlite3

After that, to be sure of using the newly created executable file, I even 
deleted all of these above sqlite3 files, and then installed the package again. 
After the second installation, I ran the command
sqlite3
and I still got the version 3.4.2.








From: Jean-Denis Muys 
To: General Discussion of SQLite Database 
Sent: Monday, May 18, 2009 4:24:35 PM
Subject: Re: [sqlite] How to install the latest version of sqlite3 on Ubuntu?

On 5/18/09 10:33 AM, "Robert Villanoa"  wrote:

> Thank you for your answer, Jean-Denis.
> When I type 'which sqlite3', I get the following location:
> /usr/local/bin/sqlite3
> And the value of my PATH variable is:
> /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games
> So I think the executable file sqlite3 is seen by PATH.
> Could you please tell me any more suggestions?

My understanding is that /usr/local/bin/sqlite3 is the old version of
sqlite, which obviously is reachable from your PATH.

Now the interesting question is, where was the new version installed?

Jean-Denis

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



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

Re: [sqlite] How to install the latest version of sqlite3 on Ubuntu?

[Dan]

On May 18, 2009, at 3:33 PM, Robert Villanoa wrote:

> Thank you for your answer, Jean-Denis.
> When I type 'which sqlite3', I get the following location:
> /usr/local/bin/sqlite3
> And the value of my PATH variable is:
> /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/ 
> games
> So I think the executable file sqlite3 is seen by PATH.
> Could you please tell me any more suggestions?

$ ldd /usr/local/bin/sqlite3

Check it's linking to the correct libsqlite3.so.

Dan.


>
>
>
>
>
>
> 
> From: Jean-Denis Muys 
> To: General Discussion of SQLite Database 
> Sent: Monday, May 18, 2009 2:50:38 PM
> Subject: Re: [sqlite] How to install the latest version of sqlite3  
> on Ubuntu?
>
>
> On 5/18/09 9:19 AM, "Robert Villanoa"   
> wrote:
>
>> Hi everyone,
>>
>> I am new to sqlite3. My OS is Ubuntu 8.04 and it has sqlite3 3.4.2.  
>> Now I want
>> to upgrade it to the latest version, 3.6.14, but I don't know how  
>> to do that.
>>
>> Here are the steps I have done (after reading another thread about  
>> this
>> issue):
>> 1. Remove the default version using 'sudo apt-get remove sqlite3'.
>> 2. Download sqlite-amalgamation-3.6.14.tar.gz, extract the package,  
>> go to the
>> sqlite3 directory and run:
>> - ./configure
>> - make
>> - sudo make install
>> Although I did not encounter any error after executing these above  
>> commands,
>> it seemed that I did not install sqlite3 3.6.14 successfully.  
>> Whenever I type
>> 'sqlite3' into the GNOME terminal, it shows that the version is  
>> 3.4.2.
>> What's wrong with me? Please help! Thanks in advanced!
>
> PATH problem? When you type "which sqlite3" in the terminal, what is  
> the
> result? Does it match your install location?
>
> JD
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] SQLITE_ENABLE_MEMORY_MANAGEMENT still single threaded?

[Dan]

> Hi,
>
> The latest info I can find about SQLITE_ENABLE_MEMORY_MANAGEMENT is  
> from
> mid 2006 - "FAQ said that it might be problematic to use more than one
> thread with SQLITE_ENABLE_MEMORY_MANAGEMENT".
>
> Is this still the case in 3.6.14 ? - The latest FAQ mentions nothing
> about SQLITE_ENABLE_MEMORY_MANAGEMENT being problematic anymore.
>
> Background:
>
> I have a single process that uses sqlite 3.5.1 and opens 350 sqlite
> databases, each db has a dedicated write thread and there is a global
> "reader" thread pool shared amongst the 350 databases.
> The files are never closed because we aim to provide 99.999%
> availability.
> After a few days on rhel5 64bit, sqlite consumes all of the 20gigs of
> physical memory and all of the swap, machine dies.

Why is SQLite eating up all this memory? How large are the configured
page caches?

First thing to do is upgrade to 3.6.14 if it is at all possible. 3.5.1
is officially ancient. The list of bugs fixed since then, thread related
and otherwise, must be enormous.

> Ive played around with SQLITE_ENABLE_MEMORY_MANAGEMENT in 3.5.1 on
> windows (visual c++ 2005)- seems to work okay for a while but  
> eventually
> the heap is corrupted - most probably because of the thread issue with
> memory management.
>
> Looks like my options are:
>
> 1. Multi-threaded memory management now supported in SQLite 3.6.14
> (fingers crossed - don't like my chances).

In 3.6.14 the APIs associated with SQLITE_ENABLE_MEMORY_MANAGEMENT work
across threads. 3.5.1 was the first release to support this,  
incidentally.

> 2. Close each database often - this is a bad approach for 5 x 9's.
> 3. Re-architect the writer threads to do both writes and reads - bad  
> for
> performance.

Using the same thread/connection-handle for reading and writing a  
database
might improve performance.

Dan.







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

Re: [sqlite] How to install the latest version of sqlite3 on Ubuntu?

[Jean-Denis Muys]

On 5/18/09 10:33 AM, "Robert Villanoa"  wrote:

> Thank you for your answer, Jean-Denis.
> When I type 'which sqlite3', I get the following location:
> /usr/local/bin/sqlite3
> And the value of my PATH variable is:
> /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games
> So I think the executable file sqlite3 is seen by PATH.
> Could you please tell me any more suggestions?

My understanding is that /usr/local/bin/sqlite3 is the old version of
sqlite, which obviously is reachable from your PATH.

Now the interesting question is, where was the new version installed?

Jean-Denis

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

Re: [sqlite] Transaction isolation

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Yang Zhang wrote:
> for i in (str(row[0]) for row in conn.cursor().execute('SELECT key FROM 
> shelf ORDER BY ROWID')):

You are converting the key which is an integer into a string for no
apparent reason.

If you also ask for the ROWID you will see that what is happening is a
new rowid is generated for the replaced row so that if you are iterating
over the table while modifying it then you effectively have an infinite
length table.

> Any way to solve this problem?  

You currently have the SELECT results being read back one at a time
(lazily) on each iteration of the for loop.  The simplest solution is to
read them all in first.  Add .fetchall() after the execute.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkoRJ9kACgkQmOOfHg372QT/JgCfRImM5e85JCgn3bmp45zGm6j6
uQMAn11x9OfWdBUMwq/6zZdvSCSuGGGS
=ABo+
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Accessing sqlite using javascript

[Neville Franks]

Monday, May 18, 2009, 3:16:45 PM, you wrote:

SP> I had asked the same question a few days back,but need a some more help.
SP> I am having my whole SQLite database on client's machine.I want a
SP> way to access that db using javscript.I heard about gears,but the
SP> problem is my target PC has firefox 1.08 and gears is for 1.5+.So
SP> is there any other way around?...Also is there any tutorial or
SP> guide which i can have online for referring when i am writing the code?.

SP> Thank you.

There are several ways to do this. From Firefox see:
http://codesnippets.joyent.com/posts/show/1030

The ExtJS Library also provides access to SQLite, but I have not used
that part of ExtJS yet.

Finally Google: "sqlite from javascript".

---
Best regards,
  Neville Franks, http://www.surfulater.com http://blog.surfulater.com
 

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

Re: [sqlite] How to install the latest version of sqlite3 on Ubuntu?

[Robert Villanoa]

Thank you for your answer, Jean-Denis.
When I type 'which sqlite3', I get the following location: 
/usr/local/bin/sqlite3
And the value of my PATH variable is:
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games
So I think the executable file sqlite3 is seen by PATH.
Could you please tell me any more suggestions? 






From: Jean-Denis Muys 
To: General Discussion of SQLite Database 
Sent: Monday, May 18, 2009 2:50:38 PM
Subject: Re: [sqlite] How to install the latest version of sqlite3 on Ubuntu?


On 5/18/09 9:19 AM, "Robert Villanoa"  wrote:

> Hi everyone,
> 
> I am new to sqlite3. My OS is Ubuntu 8.04 and it has sqlite3 3.4.2. Now I want
> to upgrade it to the latest version, 3.6.14, but I don't know how to do that.
> 
> Here are the steps I have done (after reading another thread about this
> issue):
> 1. Remove the default version using 'sudo apt-get remove sqlite3'.
> 2. Download sqlite-amalgamation-3.6.14.tar.gz, extract the package, go to the
> sqlite3 directory and run:
> - ./configure
> - make
> - sudo make install
> Although I did not encounter any error after executing these above commands,
> it seemed that I did not install sqlite3 3.6.14 successfully. Whenever I type
> 'sqlite3' into the GNOME terminal, it shows that the version is 3.4.2.
> What's wrong with me? Please help! Thanks in advanced!

PATH problem? When you type "which sqlite3" in the terminal, what is the
result? Does it match your install location?

JD

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



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

Re: [sqlite] How to install the latest version of sqlite3 on Ubuntu?

[Jean-Denis Muys]

On 5/18/09 9:19 AM, "Robert Villanoa"  wrote:

> Hi everyone,
> 
> I am new to sqlite3. My OS is Ubuntu 8.04 and it has sqlite3 3.4.2. Now I want
> to upgrade it to the latest version, 3.6.14, but I don't know how to do that.
> 
> Here are the steps I have done (after reading another thread about this
> issue):
> 1. Remove the default version using 'sudo apt-get remove sqlite3'.
> 2. Download sqlite-amalgamation-3.6.14.tar.gz, extract the package, go to the
> sqlite3 directory and run:
> - ./configure
> - make
> - sudo make install
> Although I did not encounter any error after executing these above commands,
> it seemed that I did not install sqlite3 3.6.14 successfully. Whenever I type
> 'sqlite3' into the GNOME terminal, it shows that the version is 3.4.2.
> What's wrong with me? Please help! Thanks in advanced!

PATH problem? When you type "which sqlite3" in the terminal, what is the
result? Does it match your install location?

JD

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

[sqlite] How to install the latest version of sqlite3 on Ubuntu?

[Robert Villanoa]

Hi everyone,

I am new to sqlite3. My OS is Ubuntu 8.04 and it has sqlite3 3.4.2. Now I want 
to upgrade it to the latest version, 3.6.14, but I don't know how to do that. 

Here are the steps I have done (after reading another thread about this issue):
1. Remove the default version using 'sudo apt-get remove sqlite3'.
2. Download sqlite-amalgamation-3.6.14.tar.gz, extract the package, go to the 
sqlite3 directory and run:
- ./configure
- make
- sudo make install
Although I did not encounter any error after executing these above commands, it 
seemed that I did not install sqlite3 3.6.14 successfully. Whenever I type 
'sqlite3' into the GNOME terminal, it shows that the version is 3.4.2.
What's wrong with me? Please help! Thanks in advanced!



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

[sqlite] SQLITE_ENABLE_MEMORY_MANAGEMENT still single threaded?

[Colin Goldie]

Hi,

The latest info I can find about SQLITE_ENABLE_MEMORY_MANAGEMENT is from
mid 2006 - "FAQ said that it might be problematic to use more than one
thread with SQLITE_ENABLE_MEMORY_MANAGEMENT".

Is this still the case in 3.6.14 ? - The latest FAQ mentions nothing
about SQLITE_ENABLE_MEMORY_MANAGEMENT being problematic anymore.

Background:

I have a single process that uses sqlite 3.5.1 and opens 350 sqlite
databases, each db has a dedicated write thread and there is a global
"reader" thread pool shared amongst the 350 databases.
The files are never closed because we aim to provide 99.999%
availability.

After a few days on rhel5 64bit, sqlite consumes all of the 20gigs of
physical memory and all of the swap, machine dies.

Ive played around with SQLITE_ENABLE_MEMORY_MANAGEMENT in 3.5.1 on
windows (visual c++ 2005)- seems to work okay for a while but eventually
the heap is corrupted - most probably because of the thread issue with
memory management.

Looks like my options are:

1. Multi-threaded memory management now supported in SQLite 3.6.14
(fingers crossed - don't like my chances).
2. Close each database often - this is a bad approach for 5 x 9's.
3. Re-architect the writer threads to do both writes and reads - bad for
performance.

Thanks in advance!

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

Re: [sqlite] Kevin Smekens is out of the office.

[Samuel Baldwin]

> I will be out of the office starting  2009/05/18 and will not return until
> 2009/06/01.

Ah, thanks, I've been losing sleep wondering about this.

-- 
Samuel 'Shardz' Baldwin - staticfree.info/~samuel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Transaction isolation

[Yang Zhang]

Hi, for some reason the following program will loop forever:

#!/usr/bin/env python

import sqlite3

conn = sqlite3.connect(':memory:')
conn.text_factory = bytes
conn.execute('CREATE TABLE shelf (key INTEGER NOT NULL, value INTEGER 
NOT NULL)')
for i in xrange(3):
   conn.execute('INSERT INTO shelf (key, value) VALUES (?,?)', (i, i))
conn.commit()

for i in (str(row[0]) for row in conn.cursor().execute('SELECT key FROM 
shelf ORDER BY ROWID')):
   conn.execute('REPLACE INTO shelf (key, value) VALUES (?,?)', (i, i))
   conn.commit()
   print i

Anybody understand why?  I thought the REPLACE and SELECT transactions 
should be (snapshot) isolated from each other, so why does the SELECT 
keep getting updated rows from the REPLACE?  Any way to solve this 
problem?  So far all I've found are commands that can change the 
connection-level isolation/locking, but not the cursor-level 
(transaction-level) isolation.

Thanks in advance for any answers!
-- 
Yang Zhang
http://www.mit.edu/~y_z/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Kevin Smekens is out of the office.

[Kevin Smekens]

I will be out of the office starting  2009/05/18 and will not return until
2009/06/01.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users