[sqlite] .import FILE TABLE

[Larry Siden]

What is the format of the file?  I cannot find it documented anywhere.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] delete constraint

[BareFeetWare]

On 04/08/2010, at 11:12 PM, Igor Tandetnik wrote:

> I believe the two are equivalent. SQLite essentially rewrites IN to EXISTS - 
> it certainly doesn't generate the complete resultset from the nested select 
> and then go searching inside that.
> 
> I noticed anecdotally that SQLite may optimize complex statements involving 
> IN better than those involving EXISTS, but I don't have formal evidence.

That's good to know. Thanks Igor. I wasn't aware that "in" prevents a full 
table scan in the same way (or better) than EXISTS does.

Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] [PATCH] cache preloading

[Jim Wilcoxson]

On 8/10/10, Paweł Hajdan, Jr.  wrote:
> So this is another chromium patch I'd like to submit:
> http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/preload-cache.patch?revision=26596=markup
>
> I'm not the author of that one, but the main idea seems to be that with
> preloading we get better performance with many scenarios chromium uses
> sqlite (like for omnibox local suggestions). What do you think about the
> patch and the idea? Is there better way to do the same thing? Would you like
> to see some benchmarks?

The benefit of preloading is to replace random I/O with sequential
I/O.  I have definitely seen this be effective in some cases.  For
those who want something like this, doing a cat or dd command, or
using OS reads in a loop, will probably give the same benefit: the
database won't be loaded into SQLite's cache, but will be loaded into
the OS buffer cache.

An advantage of preloading outside SQLite is that the dd/cat/read
could happen in a separate thread, so it wouldn't impact app startup
time.  And it could be that one use of a database might benefit from
preloading, whereas another use might not, so it seems that preloading
inside SQLite would have to be something requested with a pragma
rather than always happening by default.

What I would like to see more than SQLite preloading is better page
allocation strategies and incremental defragmentation, to cut down on
the amount of random I/O and keep related pages in sequential order.

Jim
--
HashBackup: easy onsite and offsite Unix backup
http://sites.google.com/site/hashbackup
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [PATCH] cache preloading

[Paweł Hajdan , Jr .]

So this is another chromium patch I'd like to submit:
http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/preload-cache.patch?revision=26596=markup

I'm not the author of that one, but the main idea seems to be that with
preloading we get better performance with many scenarios chromium uses
sqlite (like for omnibox local suggestions). What do you think about the
patch and the idea? Is there better way to do the same thing? Would you like
to see some benchmarks?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Will sqlite3_shutdown() in one web page affect the other web page threads?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/11/2010 12:12 AM, Rosbicn wrote:
> ... sqlite3_initialize() ... sqlite3_shutdown() ...

Why are you calling these functions - ie what do you hope to achieve?

Unless you are in a very small device, or doing behind the scenes
infrastructure, or using a non-mainstream operating system, there is
absolutely no need to call them nor any benefit from doing so.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkxh7z0ACgkQmOOfHg372QRF1QCdH8px0V4RhlVNaEnTGLFxC4uF
I8wAn2+haufK9ZULE6ldwFhcKJFaTfyV
=U/Sw
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] trigger or application code

[Simon Slavin]

On 10 Aug 2010, at 7:45pm, David Bicking wrote:

> Actually, the amount in items is a running balance of the changes. Mind you, 
> there are other fields in items (and in changes) that I did not show as they 
> didn't seem to impact this decision.
> 
> In that 8% case where an item is to be created, the "change" is in fact the 
> creation of the item. 
> 
> Originally, I would check to see if the item existed, and create it if 
> needed. But given it is such an infrequent event, it seemed better to try to 
> update and only create if the update failed. Though looking at Igor's 
> suggested trigger, it is just doing the whole select where not exists thing 
> anyway, actually twice, once to test if the ABORT should be raised, and 
> second time to see if the record should be inserted. 

Two more possibilities:

A) Change your trigger.  Have the trigger check to see whether the 'item' 
exists, if not, create a new row in the "items" table, if it does, update the 
existing row.

B) Abandon the "items" table altogether.  If it's only purpose is to provide 
you with the running total, just calculate the running total whenever you need 
it using something like

SELECT sum(AdjAmount) FROM changes WHERE Code = 'nnn'

If you have an index on the Code column this may be pretty fast.

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

Re: [sqlite] [PATCH] Fix locale-unsafe usage of tolower (upstreaming Chromium change)

[Paweł Hajdan , Jr .]

Thank you, I have backported it to chromium as
http://src.chromium.org/viewvc/chrome?view=rev=55504

Now, how about fts1 and fts2? The original chromium patch is at
http://codereview.chromium.org/174387 . Could you take a look and suggest a
way to upstream those fixes to SQLite?

On Fri, Aug 6, 2010 at 12:10, Richard Hipp  wrote:

> FTS3 updated here:  http://www.sqlite.org/src/ci/b8b465ed2c
>
> On Fri, Aug 6, 2010 at 2:24 PM, Scott Hess  wrote:
>
> > This bug comment describes the problem:
> >   http://code.google.com/p/chromium/issues/detail?id=15261#c20
> >
> > excerpt:
> > > Apparently the problem is caused by tolower(), whose behavior is
> affected
> > by current
> > > locale. Under locale tr_TR.UTF-8, tolower('I') returns 'I' rather than
> > 'i', because
> > > lower case of 'I' defined in tr_TR is 'ı' (U+0131).
> >
> > I think at the time the bug was being diagnosed, sqlite3_strnicmp()
> > wasn't being exposed.  I think that does the right thing because it
> > uses the internal UpperToLower table.
> >
> > -scott
> >
> >
> > On Fri, Aug 6, 2010 at 11:11 AM, Richard Hipp  wrote:
> > > If "ch" is an unsigned char then how is the following unsafe:
> > >
> > > ch = (ch<0x80) ? tolower(ch) : ch
> > >
> > > And why does it need to be changed to
> > >
> > >ch = (ch>='A' && ch<='Z') ? ch - 'A' + 'a' : ch;
> > >
> > > There is only one such instance of code remaining in FTS3 (at
> > > fts3_tokenizer1.c:196) but I want to understand what the issue is
> before
> > I
> > > change it.
> > >
> > > On Fri, Aug 6, 2010 at 1:30 PM, Paweł Hajdan, Jr.
> > > wrote:
> > >
> > >> On Wed, Aug 4, 2010 at 15:23, Paweł Hajdan, Jr. <
> > phajdan...@chromium.org
> > >> >wrote:
> > >>
> > >> > I'm attaching a suggested patch to fix locale-unsafe usage of
> tolower
> > in
> > >> > FTS code. The goal is to make Chromium closer to the upstream, so if
> > you
> > >> > have a better solution, that's great.
> > >>
> > >>
> > >> Oh, I have just noticed that the mailing list removes all attachments.
> > What
> > >> is the best way to send patches then?
> > >>
> > >> By the way, any suggestions about the Chromium patch I linked to (
> > >>
> > >>
> >
> http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/safe-tolower.patch?view=markup
> > >> )?
> > >> It seems that it has somehow been fixed in fts3 code. I'm not yet very
> > >> familiar with the SQLite codebase though, so could you point me to the
> > >> fixes?
> > >> ___
> > >> sqlite-users mailing list
> > >> sqlite-users@sqlite.org
> > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >>
> > >
> > >
> > >
> > > --
> > > -
> > > D. Richard Hipp
> > > d...@sqlite.org
> > > ___
> > > 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
> >
>
>
>
> --
> -
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] severe slowdown for sub-queries, solved in 3.7?

[Peter Pawlowski]

Hello -- we recently noticed that some SQL statements containing
subqueries (see below) are very slow in 3.6.23 as compared to the
version we were using before, 3.3.17. I tested the newest release of
3.7.0.1 and the queries are fast again.

The problem is that this is a showstopper for us, but we cannot
immediately upgrade to 3.7, it is just too new and it takes a lot of QA
resources to do so. We'd prefer a more stable solution in the short term.

So I'm trying to figure out if this is a known issue that was fixed for
3.7 in a way that we might be able to patch 3.6. I've been looking for
clues in changelogs and tickets but haven't found anything promising
yet. Can anyone help?

I'm including an example of our query and database...

This is slow:

   select count (*) from reporting
   left outer join (select * from type) as A0
   on a0.id = reporting.id;

This is fast:

   select count (*) from reporting
   left outer join type as A0
   on a0.id = reporting.id;

Download the database (~100kb) from here:

   http://peterpawlowski.com/db.gz


Thanks,
Peter Pawlowski

-- 
Peter Pawlowski | Senior Software Engineer
Office: +1.412.422.2499 x116
pawlow...@vivisimo.com | Connect: www.vivisimo.com
Vivisimo - Information Optimized

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

Re: [sqlite] Write-ahead logging and database locking

[David Barrett]

On 08/08/2010 10:09 PM, Dan Kennedy wrote:
>>> 2) However, one process cannot read from the database while another
>>> is writing -- WAL is irrelevant here.
>>
>> Unless shared-cache mode is turned on, multiple threads each using
>> their own sqlite3* connection should behave in the same way as
>> multiple processes do wrt to sqlite locking.
>
> I should be clearer: The above was meant to imply that (2)
> is not a true statement. The others are all correct.

Very interesting!  To confirm I understand, if shared-cache mode is 
enabled, then one process can read while another process is writing.

Also, I see in the documentation that when shared-cache mode is enabled, 
SQLite uses table-level locking (instead of the default file-locking). 
Taken all together, it suggests that you can get table-level locking 
*and* write-ahead logging *and* atomic multi-table commits -- all within 
a single file -- simply by enabling shared cache mode.

Am I reading this correctly, or does shared-cache table-level locking 
still require that each table be put in different files as described here:

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

(If so, then it means the only way to get table-locking with WAL is to 
put the tables in different database files, but then WAL "disadvantage 
#3" says you lose atomicity.)

Thanks Dan, I really appreciate your help!

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

Re: [sqlite] trigger or application code

[David Bicking]

Actually, the amount in items is a running balance of the changes. Mind you, 
there are other fields in items (and in changes) that I did not show as they 
didn't seem to impact this decision.

In that 8% case where an item is to be created, the "change" is in fact the 
creation of the item. 

Originally, I would check to see if the item existed, and create it if needed. 
But given it is such an infrequent event, it seemed better to try to update and 
only create if the update failed. Though looking at Igor's suggested trigger, 
it is just doing the whole select where not exists thing anyway, actually 
twice, once to test if the ABORT should be raised, and second time to see if 
the record should be inserted. 

So maybe I should drop this down to the application:

'pseudo code
exec update items
if sqlite3_changes = 0 
  if creatable = 'N'
return error
  else
exec insert into items
exec insert into changes

It just seemed that if I let sqlite handle this, I could just do one insert in 
whatever host language makes sense, and not have to rewrite that pseudo code in 
whatever language I chose to use for a particular task.


David


PS. Thanks Igor. Your solution was, of course, a thousand times better than the 
best solution I had come up with.

--- On Tue, 8/10/10, Tim Romano  wrote:

> From: Tim Romano 
> Subject: Re: [sqlite] trigger or application code
> To: "General Discussion of SQLite Database" 
> Date: Tuesday, August 10, 2010, 2:08 PM
> David,
> Your approach contravenes "best practice"  by
> violating the core referential
> integrity paradigm: your CHANGES table refers to an item
> not yet in the
> ITEMS table and actually governs whether an ITEM item can
> be created.  The
> child is giving birth to the parent. This is unnecessarily
> convoluted.
> 
> In your example,  you have the ITEMS table track the
> most recent amount.
>  That's all it's doing. Now, if that's all you want this
> table to do ( you
> don't want to have a full ITEMS master table with
> item-description, UPC
> codes, etc etc, for example), you can eliminate the ITEMS
> table. You could
> always get the most recent amount with a simple query.
> 
> select amount from changes where code = ?
>    and changedate =
>   ( select max(changedate) from changes where code = ?
> )
> 
> or in the alternative
> 
> select amount from changes where code = ?
> order by changedate desc limit 1
> 
> 
> The problem with this approach is that any [code] value
> under the sun is
> acceptable; there's no ITEMS table to prevent invalid codes
> via a foreign
> key declaration.
> 
> Regards
> Tim Romano
> 
> 
> 
> 
> On Tue, Aug 10, 2010 at 1:20 PM, Igor Tandetnik 
> wrote:
> 
> > David Bicking 
> wrote:
> > > I am building an application with these two
> tables:
> > >
> > > CREATE TABLE changes(ChangeDate, Code, AdjAmount,
> Descr, Creatable);
> > > CREATE TABLE items(Code, Amount)
> > >
> > > Now, what I would like to happen, I insert in to
> changes, and it updates
> > the Amount in items.
> > >
> > > I can get that with
> > >
> > > CREATE TRIGGER changes_after_insert on changes
> > > BEGIN
> > >  Update items set Amount = Amount +
> new.AdjAmount where code = new.code;
> > > END;
> > >
> > > And for 90% of the time, that will do what I
> want.
> > > But for 8% of the time, the items entry won't be
> there, so I would like
> > to insert a new items entry but only if the Creatable
> > > flag is not 'N'.
> > > The remaining 2% of the time, the items entry
> doesn't exist and the
> > Createable flag is 'N', and I need to ABORT the insert
> and
> > > report the error back to the application.
> > >
> > > My question is can all this be done in a
> trigger?
> >
> > Yes, but it's pretty awkward:
> >
> > BEGIN
> >  select raise(ABORT, 'Item does not exist')
> >  where new.Creatable = 'N' and new.Code not in
> (select Code from items);
> >
> >  insert into items(Code, Amount)
> >  select new.Code, 0
> >  where new.Code not in (select Code from items);
> >
> >  update items set Amount = Amount +
> new.AdjAmount
> >  where code = new.code;
> > END;
> >
> > > Or is this type of logic better handled at the
> application level?
> >
> > Quite possibly.
> > --
> > 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
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Backup API, .backup command, and writing to handle live backups?

[Dan Kennedy]

On Aug 11, 2010, at 1:25 AM, David Barrett wrote:

> On 08/08/2010 10:00 PM, Dan Kennedy wrote:
>>
>> On Aug 9, 2010, at 11:17 AM, David Barrett wrote:
>>> 3) When an application performs read/write queries on the database  
>>> in
>>> parallel to the .backup command being run on the database, will the
>>> application occasionally get the SQLITE_LOCKED return value to the
>>> sqlite3_exec() command?  I think the answer is "yes".
>>
>> The answer to (3) would be "No." with a WAL mode database.
>
> Ah, got it.  But to clarify, it will occasionally get SQLITE_BUSY if
> non-WAL, right?  Thanks!

Yes.

When you backup a database, your backup process is a
database reader.

In WAL mode, a database reader will block neither another
reader or a writer. So no chance of the application getting
an SQLITE_BUSY.

In non-WAL (rollback) mode, a database reader will block
a writer. So if the app tries to write while the shell
is running the ".backup" command, it may get SQLITE_BUSY.



>
> -david
> ___
> 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] Backup API, .backup command, and writing to handle live backups?

[David Barrett]

On 08/08/2010 10:00 PM, Dan Kennedy wrote:
>
> On Aug 9, 2010, at 11:17 AM, David Barrett wrote:
>> 3) When an application performs read/write queries on the database in
>> parallel to the .backup command being run on the database, will the
>> application occasionally get the SQLITE_LOCKED return value to the
>> sqlite3_exec() command?  I think the answer is "yes".
>
> The answer to (3) would be "No." with a WAL mode database.

Ah, got it.  But to clarify, it will occasionally get SQLITE_BUSY if 
non-WAL, right?  Thanks!

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

Re: [sqlite] trigger or application code

[Tim Romano]

David,
Your approach contravenes "best practice"  by violating the core referential
integrity paradigm: your CHANGES table refers to an item not yet in the
ITEMS table and actually governs whether an ITEM item can be created.  The
child is giving birth to the parent. This is unnecessarily convoluted.

In your example,  you have the ITEMS table track the most recent amount.
 That's all it's doing. Now, if that's all you want this table to do ( you
don't want to have a full ITEMS master table with item-description, UPC
codes, etc etc, for example), you can eliminate the ITEMS table. You could
always get the most recent amount with a simple query.

select amount from changes where code = ?
   and changedate =
  ( select max(changedate) from changes where code = ? )

or in the alternative

select amount from changes where code = ?
order by changedate desc limit 1


The problem with this approach is that any [code] value under the sun is
acceptable; there's no ITEMS table to prevent invalid codes via a foreign
key declaration.

Regards
Tim Romano




On Tue, Aug 10, 2010 at 1:20 PM, Igor Tandetnik  wrote:

> David Bicking  wrote:
> > I am building an application with these two tables:
> >
> > CREATE TABLE changes(ChangeDate, Code, AdjAmount, Descr, Creatable);
> > CREATE TABLE items(Code, Amount)
> >
> > Now, what I would like to happen, I insert in to changes, and it updates
> the Amount in items.
> >
> > I can get that with
> >
> > CREATE TRIGGER changes_after_insert on changes
> > BEGIN
> >  Update items set Amount = Amount + new.AdjAmount where code = new.code;
> > END;
> >
> > And for 90% of the time, that will do what I want.
> > But for 8% of the time, the items entry won't be there, so I would like
> to insert a new items entry but only if the Creatable
> > flag is not 'N'.
> > The remaining 2% of the time, the items entry doesn't exist and the
> Createable flag is 'N', and I need to ABORT the insert and
> > report the error back to the application.
> >
> > My question is can all this be done in a trigger?
>
> Yes, but it's pretty awkward:
>
> BEGIN
>  select raise(ABORT, 'Item does not exist')
>  where new.Creatable = 'N' and new.Code not in (select Code from items);
>
>  insert into items(Code, Amount)
>  select new.Code, 0
>  where new.Code not in (select Code from items);
>
>  update items set Amount = Amount + new.AdjAmount
>  where code = new.code;
> END;
>
> > Or is this type of logic better handled at the application level?
>
> Quite possibly.
> --
> 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] trigger or application code

[Igor Tandetnik]

David Bicking  wrote:
> I am building an application with these two tables:
> 
> CREATE TABLE changes(ChangeDate, Code, AdjAmount, Descr, Creatable);
> CREATE TABLE items(Code, Amount)
> 
> Now, what I would like to happen, I insert in to changes, and it updates the 
> Amount in items.
> 
> I can get that with
> 
> CREATE TRIGGER changes_after_insert on changes
> BEGIN
>  Update items set Amount = Amount + new.AdjAmount where code = new.code;
> END;
> 
> And for 90% of the time, that will do what I want.
> But for 8% of the time, the items entry won't be there, so I would like to 
> insert a new items entry but only if the Creatable
> flag is not 'N'. 
> The remaining 2% of the time, the items entry doesn't exist and the 
> Createable flag is 'N', and I need to ABORT the insert and
> report the error back to the application. 
> 
> My question is can all this be done in a trigger?

Yes, but it's pretty awkward:

BEGIN
  select raise(ABORT, 'Item does not exist')
  where new.Creatable = 'N' and new.Code not in (select Code from items);

  insert into items(Code, Amount)
  select new.Code, 0
  where new.Code not in (select Code from items);

  update items set Amount = Amount + new.AdjAmount
  where code = new.code;
END;

> Or is this type of logic better handled at the application level?

Quite possibly.
-- 
Igor Tandetnik


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

[sqlite] trigger or application code

[David Bicking]

I am building an application with these two tables:

CREATE TABLE changes(ChangeDate, Code, AdjAmount, Descr, Creatable);
CREATE TABLE items(Code, Amount)

Now, what I would like to happen, I insert in to changes, and it updates the 
Amount in items.

I can get that with

CREATE TRIGGER changes_after_insert on changes
BEGIN
  Update items set Amount = Amount + new.AdjAmount where code = new.code;
END;

And for 90% of the time, that will do what I want.
But for 8% of the time, the items entry won't be there, so I would like to 
insert a new items entry but only if the Creatable flag is not 'N'.
The remaining 2% of the time, the items entry doesn't exist and the Createable 
flag is 'N', and I need to ABORT the insert and report the error back to the 
application.

My question is can all this be done in a trigger? Or is this type of logic 
better handled at the application level?

I kind of want to have it in a trigger because I would like to have different 
client applications update the database and I don't want to have the Create 
items entry logic sitting in the code of each application. (Especially since 
one possible application could be a simple shell script that uses the command 
line tool to insert the values.)

What would be a "best practice" in this type of situation?

Thanks,
David

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

[sqlite] Will sqlite3_shutdown() in one web page affect the other web page threads?

[Rosbicn]

  I use sqlite in a .Net dll, which is used in a aspx web page under 
IIS. In the code, sqlite3_initialize() is called in the beginning of all 
codes, and sqlite3_shutdown() is called in the end of all codes. Now I 
find the database file is "hanged" by IIS when many browsers fetch the 
web server.

I read the sqlite document again, it says sqlite3_shutdown() is not 
thread safe. So my question is:
When many browsers fetch web page from IIS concurrently, will 
sqlite3_shutdown() in one web page affect the other web page threads? If 
it will,  sqlite3_initialize() and sqlite3_shutdown() should not used in 
web server programming?

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

Re: [sqlite] performance, transactions and wal checkpoints

[Yoni Londner]

Hi,
Yes they are.
With "PRAGMA wal_checkpoint" it works as expected.
Yoni.

On Tue, Aug 10, 2010 at 2:50 PM, Dan Kennedy  wrote:

>
> On Aug 10, 2010, at 6:03 PM, Yoni Londner wrote:
>
> > Hi,
> > Yes, it explains allot.
> > But, according to you, the following program should work.
> > I don't use transactions, shared cache or threads.
> > Just run the checkpoint from another connection.
> > And still - WAL file is getting bigger and bigger without limit.
> > If I do the checkpoint with the same connection, everything work
> > perfectly.
>
> Interesting. Perhaps the call to sqlite3_wal_checkpoint()
> is not working because the second connection never really
> connects to the database (because connection usually happens
> as part of the first SQL statement run).
>
> Are things any different if you change the sqlite3_wal_checkpoint()
> to sqlite3_exec(conn, "PRAGMA wal_checkpoint", 0, 0, 0)?
>
> Dan.
>
> > #include "sqlite3.h"
> > #include "stdio.h"
> > #include "stdlib.h"
> > #include "fcntl.h"
> >
> > static void sql_exec(sqlite3 *conn, char *query)
> > {
> >char *err;
> >if (sqlite3_exec(conn, query, NULL, 0, ))
> >{
> >printf("sqlite: failed exec %s. err: %s\n", query, err);
> > exit(1);
> >}
> > }
> >
> > static sqlite3 *sql_open_conn(void)
> > {
> >sqlite3 *conn;
> >if (sqlite3_open_v2("test.db", , SQLITE_OPEN_READWRITE, NULL))
> >{
> > printf("sqlite3_open_v2 failed\n");
> > exit(1);
> >}
> >return conn;
> > }
> >
> > static int do_checkpoint()
> > {
> >sqlite3 *conn;
> >conn = sql_open_conn();
> >if (sqlite3_wal_checkpoint(conn, NULL))
> >{
> > printf("sqlite3_wal_autocheckpoint failed\n");
> > exit(1);
> >}
> >sqlite3_close(conn);
> > }
> >
> > int main(int argc, char **argv)
> > {
> >char *err_msg = NULL;
> >pthread_t thread;
> >int fd, i;
> >sqlite3 *conn;
> >if (unlink("test.db") || unlink("test.db-wal"))
> >{
> > printf("failed unlink test.db\n");
> > exit(1);
> >}
> >fd = open("test.db", O_CREAT|O_RDWR, 0666);
> >if (fd<0)
> >{
> >printf("could not open test.db\n");
> >exit(1);
> >}
> >close(fd);
> >conn = sql_open_conn();
> >sql_exec(conn, "PRAGMA journal_mode=WAL");
> >sql_exec(conn, "PRAGMA synchronous=normal");
> >sql_exec(conn, "PRAGMA temp_store=memory");
> >sql_exec(conn, "PRAGMA wal_autocheckpoint=-1");
> >sql_exec(conn, "create table tbl1 (one varchar(20), two
> > varchar(20))");
> >while (1)
> >{
> >if (!(i++%1000))
> >do_checkpoint();
> > sql_exec(conn, "INSERT INTO tbl1 values('aaa', "
> >"'bbb')");
> >}
> >sqlite3_close(conn);
> >return 0;
> > }
> >
> > On Tue, Aug 10, 2010 at 1:52 PM, Dan Kennedy 
> > wrote:
> >
> >>
> >>
> >>> Hi,
> >>> I just wanted to add that I changed the program a little bit to not
> >>> use
> >>> transactions and threads, and I still get the same problem (huge WAL
> >>> file).
> >>> All I do is endless loop of insert, and every X insert, I perform a
> >>> checkpoint on another sqlite connection (but in the same thread).
> >>> It only worked if I do the checkpoint on the same connection.
> >>
> >> You cannot run a checkpoint from within a transaction. If
> >> you are in shared-cache mode, this means you cannot run a
> >> checkpoint while any connection to the same database has
> >> an open transaction.
> >>
> >> Does that explain anything?
> >>
> >> 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
>
> ___
> 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] EXTERNAL:Re: commit transaction/savepoints on program crash

[Black, Michael (IS)]

I've done this before (especially with db's that don't have rollback).
 
Add an update flag.  Set it to 2 for the records you update.  Then set it to 1 
when done.  Then set it to 0.
2 -- transaction in progress
1 -- transaction being completed
0 -- transaction completed
 
When you start up the database if you have any records with 2 then they have to 
be reinserted.  If you have records with 1 you're OK as your "transaction" 
completed but it died during the last update and you can just set them all to 0.
  
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Simon Slavin
Sent: Mon 8/9/2010 4:56 PM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] commit transaction/savepoints on program crash



[New text /below/ any text you want to quote, please.]

On 9 Aug 2010, at 9:09pm, Josh wrote:

> Thanks for the answers, but I guess I should have made my question more
> clear. I knew that you can commit every sql statement individually, then
> the question would be, how can I roll them back?
>
> In other words I'd like something like savepoint and rollback to
> savepoint, while not loosing the transactions if there is a power failure.
> I know I can have either one of these, but can I have both!?

The difficulty is that there's no way to ask the database which savepoints it 
was in the middle of when it crashed, and how far through each savepoint it 
was.  For instance, suppose you were working on a SAVEPOINT concerning 20 rows 
when you lost power.  With the system you describe, when power is restored you 
need to know you were on row 16 of SAVEPOINT A, so you can write the other 4 
rows, then RELEASE it.  But you can't find that information useful in any way, 
because the system crashed before the other 4 rows made it to disk, so you've 
lost all the data about them.  So your only real option is to ROLLBACK the 
whole savepoint.  So you might as well not have the partial savepoint 
information there in the first place.

Simon.
___
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] performance, transactions and wal checkpoints

[Dan Kennedy]

On Aug 10, 2010, at 6:03 PM, Yoni Londner wrote:

> Hi,
> Yes, it explains allot.
> But, according to you, the following program should work.
> I don't use transactions, shared cache or threads.
> Just run the checkpoint from another connection.
> And still - WAL file is getting bigger and bigger without limit.
> If I do the checkpoint with the same connection, everything work  
> perfectly.

Interesting. Perhaps the call to sqlite3_wal_checkpoint()
is not working because the second connection never really
connects to the database (because connection usually happens
as part of the first SQL statement run).

Are things any different if you change the sqlite3_wal_checkpoint()
to sqlite3_exec(conn, "PRAGMA wal_checkpoint", 0, 0, 0)?

Dan.

> #include "sqlite3.h"
> #include "stdio.h"
> #include "stdlib.h"
> #include "fcntl.h"
>
> static void sql_exec(sqlite3 *conn, char *query)
> {
>char *err;
>if (sqlite3_exec(conn, query, NULL, 0, ))
>{
>printf("sqlite: failed exec %s. err: %s\n", query, err);
> exit(1);
>}
> }
>
> static sqlite3 *sql_open_conn(void)
> {
>sqlite3 *conn;
>if (sqlite3_open_v2("test.db", , SQLITE_OPEN_READWRITE, NULL))
>{
> printf("sqlite3_open_v2 failed\n");
> exit(1);
>}
>return conn;
> }
>
> static int do_checkpoint()
> {
>sqlite3 *conn;
>conn = sql_open_conn();
>if (sqlite3_wal_checkpoint(conn, NULL))
>{
> printf("sqlite3_wal_autocheckpoint failed\n");
> exit(1);
>}
>sqlite3_close(conn);
> }
>
> int main(int argc, char **argv)
> {
>char *err_msg = NULL;
>pthread_t thread;
>int fd, i;
>sqlite3 *conn;
>if (unlink("test.db") || unlink("test.db-wal"))
>{
> printf("failed unlink test.db\n");
> exit(1);
>}
>fd = open("test.db", O_CREAT|O_RDWR, 0666);
>if (fd<0)
>{
>printf("could not open test.db\n");
>exit(1);
>}
>close(fd);
>conn = sql_open_conn();
>sql_exec(conn, "PRAGMA journal_mode=WAL");
>sql_exec(conn, "PRAGMA synchronous=normal");
>sql_exec(conn, "PRAGMA temp_store=memory");
>sql_exec(conn, "PRAGMA wal_autocheckpoint=-1");
>sql_exec(conn, "create table tbl1 (one varchar(20), two  
> varchar(20))");
>while (1)
>{
>if (!(i++%1000))
>do_checkpoint();
> sql_exec(conn, "INSERT INTO tbl1 values('aaa', "
>"'bbb')");
>}
>sqlite3_close(conn);
>return 0;
> }
>
> On Tue, Aug 10, 2010 at 1:52 PM, Dan Kennedy   
> wrote:
>
>>
>>
>>> Hi,
>>> I just wanted to add that I changed the program a little bit to not
>>> use
>>> transactions and threads, and I still get the same problem (huge WAL
>>> file).
>>> All I do is endless loop of insert, and every X insert, I perform a
>>> checkpoint on another sqlite connection (but in the same thread).
>>> It only worked if I do the checkpoint on the same connection.
>>
>> You cannot run a checkpoint from within a transaction. If
>> you are in shared-cache mode, this means you cannot run a
>> checkpoint while any connection to the same database has
>> an open transaction.
>>
>> Does that explain anything?
>>
>> 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

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

Re: [sqlite] performance, transactions and wal checkpoints

[Yoni Londner]

Hi,
Yes, it explains allot.
But, according to you, the following program should work.
I don't use transactions, shared cache or threads.
Just run the checkpoint from another connection.
And still - WAL file is getting bigger and bigger without limit.
If I do the checkpoint with the same connection, everything work perfectly.

What am I doing wrong?

===

#include "sqlite3.h"
#include "stdio.h"
#include "stdlib.h"
#include "fcntl.h"

static void sql_exec(sqlite3 *conn, char *query)
{
char *err;
if (sqlite3_exec(conn, query, NULL, 0, ))
{
printf("sqlite: failed exec %s. err: %s\n", query, err);
exit(1);
}
}

static sqlite3 *sql_open_conn(void)
{
sqlite3 *conn;
if (sqlite3_open_v2("test.db", , SQLITE_OPEN_READWRITE, NULL))
{
printf("sqlite3_open_v2 failed\n");
exit(1);
}
return conn;
}

static int do_checkpoint()
{
sqlite3 *conn;
conn = sql_open_conn();
if (sqlite3_wal_checkpoint(conn, NULL))
{
printf("sqlite3_wal_autocheckpoint failed\n");
exit(1);
}
sqlite3_close(conn);
}

int main(int argc, char **argv)
{
char *err_msg = NULL;
pthread_t thread;
int fd, i;
sqlite3 *conn;
if (unlink("test.db") || unlink("test.db-wal"))
{
printf("failed unlink test.db\n");
exit(1);
}
fd = open("test.db", O_CREAT|O_RDWR, 0666);
if (fd<0)
{
printf("could not open test.db\n");
exit(1);
}
close(fd);
conn = sql_open_conn();
sql_exec(conn, "PRAGMA journal_mode=WAL");
sql_exec(conn, "PRAGMA synchronous=normal");
sql_exec(conn, "PRAGMA temp_store=memory");
sql_exec(conn, "PRAGMA wal_autocheckpoint=-1");
sql_exec(conn, "create table tbl1 (one varchar(20), two varchar(20))");
while (1)
{
if (!(i++%1000))
do_checkpoint();
sql_exec(conn, "INSERT INTO tbl1 values('aaa', "
"'bbb')");
}
sqlite3_close(conn);
return 0;
}

On Tue, Aug 10, 2010 at 1:52 PM, Dan Kennedy  wrote:

>
>
> > Hi,
> > I just wanted to add that I changed the program a little bit to not
> > use
> > transactions and threads, and I still get the same problem (huge WAL
> > file).
> > All I do is endless loop of insert, and every X insert, I perform a
> > checkpoint on another sqlite connection (but in the same thread).
> > It only worked if I do the checkpoint on the same connection.
>
> You cannot run a checkpoint from within a transaction. If
> you are in shared-cache mode, this means you cannot run a
> checkpoint while any connection to the same database has
> an open transaction.
>
> Does that explain anything?
>
> 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] performance, transactions and wal checkpoints

[Dan Kennedy]

> Hi,
> I just wanted to add that I changed the program a little bit to not  
> use
> transactions and threads, and I still get the same problem (huge WAL  
> file).
> All I do is endless loop of insert, and every X insert, I perform a
> checkpoint on another sqlite connection (but in the same thread).
> It only worked if I do the checkpoint on the same connection.

You cannot run a checkpoint from within a transaction. If
you are in shared-cache mode, this means you cannot run a
checkpoint while any connection to the same database has
an open transaction.

Does that explain anything?

Dan.

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

Re: [sqlite] Process memory space exhausted in 3.7.0

[Dan Kennedy]

On Aug 10, 2010, at 11:55 AM, Victor Morales-Duarte wrote:

> As it turns out, I can reproduce the failure using a single huge  
> insert.
> The code that I'm including below compiles under bcc32 from
> Embarcadero's C++ Builder 2007 and cl from vs2005. Since it's more
> likely that people have MS compilers available, the compilation line
> having this source file and the sqlite3 3.7.0 amalgamation files in  
> the
> same folder is:
>
> cl -EHsc -Fefail.exe main.cpp sqlite3.c
>
> You can then invoke fail.exe with a single command line argument of
> 8000 like this:
>
> Fail.exe 8000
>
> The source for the executable is listed below. If you're wondering  
> about
> why the numbers being inserted are more complicated than need be, it's
> because I just wanted the table and indices to look as much as  
> possible
> like the actual data that our application stores in sqlite because I  
> had
> not realized that the failure could be reproduced with simply  
> inserting.
> Beware that there is no handling of incorrect command line arguments.
>
> If you monitor this executable run with perfmon and look at its  
> virtual
> bytes, you'll see them hit 2GB and then the next time the insert
> statement is stepped, it fails with an I/O disc error.

When this happens, how large are the "test.db-wal" and "test.db-shm"
files on disk?

Here, using g++/Linux, the test.db-shm file (the one memory mapped
into the processes address space) grows to about 24 MB here. The  
test.db-wal file
grows to about 12 GB. This is as expected - the *-shm file
requires 8 bytes of space for each page written into the *-wal
log. So with your 4KB pages, the *-wal file should be roughly
512 times the size of the *-shm.

When the transaction is committed, the database is checkpointed.
In the checkpoint code there is a big heap memory allocation -
Say 2.5 bytes for each page in the WAL. So in this case maybe
6-7MB. It's not ideal to be making allocations this big, but
it shouldn't cause any trouble for a desktop PC.

Memory usage here peaks at around 130MB. That's the 85MB of
configured cache space (20,000 * 4KB pages), plus the mapping
of the test.db-shm file plus the big allocation made during
the checkpoint. Plus something else I suppose.

> If you break up the insert into chunks
> _and_close_the_connection_between_chunks_ then the error does not  
> occur.

Does this imply that if you add an sqlite3_exec("COMMIT;BEGIN")
every 10,000 inserts the program still does not run to completion?

Dan.

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

Re: [sqlite] performance, transactions and wal checkpoints

[Yoni Londner]

Hi,
I just wanted to add that I changed the program a little bit to not use
transactions and threads, and I still get the same problem (huge WAL file).
All I do is endless loop of insert, and every X insert, I perform a
checkpoint on another sqlite connection (but in the same thread).
It only worked if I do the checkpoint on the same connection.
Yoni.

On Tue, Aug 10, 2010 at 11:46 AM, Yoni Londner  wrote:

> Hi Richard,
> Thanks for the quick response.
> I wrote a little program that demonstrate the error (below).
> It opens a new DB, create a table, and start an endless loop of INSERT's.
> In a background thread it performs a wal checkpoint.
> There is no difference in WAL file size - with or without transactions
> (passed by argument to the program).
> This is the result after 45 seconds:
> -rw-r--r-- 1  1.0K 2010-08-10 11:41 test.db
> -rw-r--r-- 1  1.1G 2010-08-10 11:42 test.db-wal
> -rw-r--r-- 1  8.0M 2010-08-10 11:42 test.db-shm
>
> Program compiled with GCC on debian lenny, using sqlite 3.7.0.
>
> #include "sqlite3.h"
> #include "stdio.h"
> #include "stdlib.h"
> #include "fcntl.h"
>
> static void sql_exec(sqlite3 *conn, char *query)
> {
> char *err;
> if (sqlite3_exec(conn, query, NULL, 0, ))
> {
> printf("sqlite: failed exec %s. err: %s\n", query, err);
> exit(1);
> }
> }
>
> static sqlite3 *sql_open_conn(void)
> {
> sqlite3 *conn;
> if (sqlite3_open_v2("test.db", , SQLITE_OPEN_READWRITE, NULL))
> {
> printf("sqlite3_open_v2 failed\n");
> exit(1);
> }
> return conn;
> }
>
> static int do_checkpoint()
> {
> sqlite3 *conn;
> while (1)
> {
> sleep(2);
>  printf("calling wal checkpoint\n");
> fflush(0);
> conn = sql_open_conn();
>  if (sqlite3_wal_checkpoint(conn, NULL))
> {
> printf("sqlite3_wal_autocheckpoint failed\n");
> exit(1);
> }
> sqlite3_close(conn);
> }
> }
>
> int main(int argc, char **argv)
> {
> sqlite3 *conn = NULL;
> char *err_msg = NULL;
> pthread_t thread;
> int fd, i, use_transactions = 0;
> time_t start;
> if (argc>1)
> use_transactions = atoi(argv[1]);
> printf("use_transactions=%d\n", use_transactions);
> printf("Start\n");
> if (unlink("test.db") || unlink("test.db-wal"))
> {
> printf("failed unlink test.db\n");
> exit(1);
> }
> fd = open("test.db", O_CREAT|O_RDWR, 0666);
> if (fd<0)
> {
> printf("could not open test.db\n");
> exit(1);
> }
> close(fd);
> conn = sql_open_conn();
> sqlite3_enable_shared_cache(1);
> sql_exec(conn, "PRAGMA journal_mode=WAL");
> sql_exec(conn, "PRAGMA synchronous=normal");
> sql_exec(conn, "PRAGMA temp_store=memory");
> sql_exec(conn, "PRAGMA wal_autocheckpoint=-1");
> sql_exec(conn, "create table tbl1 (one varchar(20), two varchar(20))");
> if (pthread_create(, NULL, do_checkpoint, NULL))
> {
> printf("could not start thread\n");
> exit(1);
> }
> start = time();
> if (use_transactions)
> sql_exec(conn, "BEGIN TRANSACTION");
> while (1)
> {
> if (use_transactions && !(i++%10))
> {
> printf("END BEGIN transactions\n");
> fflush(stdout);
> sql_exec(conn, "END TRANSACTION");
> sql_exec(conn, "BEGIN TRANSACTION");
>  }
> sql_exec(conn, "INSERT INTO tbl1 values('aaa', "
> "'bbb')");
> }
> if (use_transactions)
> sql_exec(conn, "END TRANSACTION");
> sqlite3_close(conn);
> printf("Finished\n");
> return 0;
> }
> On Tue, Aug 10, 2010 at 9:36 AM, Richard Hipp  wrote:
>
>> On Tue, Aug 10, 2010 at 2:24 AM, Yoni Londner  wrote:
>>
>> > Hello,
>> > I have a questions about the correct use of transactions and WAL.
>> >
>> > I am writing an application that:
>> >  1. should very fast
>> >  2. should be very responsive
>> >  3. don't care if the last N minutes of data will be lost (but DB should
>> > never be corrupted)
>> >
>> > What I tried to do:
>> >  1. open a transactions and close it every 3 minutes (So most of the
>> tune
>> > all work is on memory, and thus is very fast)
>> >  2. disable wal auto checkpoint (So I wont have very slow queries due to
>> a
>> > checkpoint)
>> >  3. run wal checkpoint in another thread (with another connection) - so
>> it
>> > wont affect the responsiveness of the main thread.
>> >
>> > Results:
>> >  1. process memory is increasing with no upper limit
>> >  2. eventually I get an I/O error
>> >
>>
>> I'm guessing your write transactions are preventing the checkpoint from
>> running to completion.  Hence, the WAL grows without bound and the
>> wal-index
>> (an in-memory structure proportional in size to the WAL file) eventually
>> uses up all memory.
>>
>> Set PRAGMA synchronous=NORMAL.  This prevents all fsync() calls on the
>> writer thread at the cost of durability, which you say you don't care
>> about.
>> Omit the 3-minute transactions, allowing each write to be its own

Re: [sqlite] performance, transactions and wal checkpoints

[Yoni Londner]

Hi Richard,
Thanks for the quick response.
I wrote a little program that demonstrate the error (below).
It opens a new DB, create a table, and start an endless loop of INSERT's.
In a background thread it performs a wal checkpoint.
There is no difference in WAL file size - with or without transactions
(passed by argument to the program).
This is the result after 45 seconds:
-rw-r--r-- 1  1.0K 2010-08-10 11:41 test.db
-rw-r--r-- 1  1.1G 2010-08-10 11:42 test.db-wal
-rw-r--r-- 1  8.0M 2010-08-10 11:42 test.db-shm

Program compiled with GCC on debian lenny, using sqlite 3.7.0.

#include "sqlite3.h"
#include "stdio.h"
#include "stdlib.h"
#include "fcntl.h"

static void sql_exec(sqlite3 *conn, char *query)
{
char *err;
if (sqlite3_exec(conn, query, NULL, 0, ))
{
printf("sqlite: failed exec %s. err: %s\n", query, err);
exit(1);
}
}

static sqlite3 *sql_open_conn(void)
{
sqlite3 *conn;
if (sqlite3_open_v2("test.db", , SQLITE_OPEN_READWRITE, NULL))
{
printf("sqlite3_open_v2 failed\n");
exit(1);
}
return conn;
}

static int do_checkpoint()
{
sqlite3 *conn;
while (1)
{
sleep(2);
printf("calling wal checkpoint\n");
fflush(0);
conn = sql_open_conn();
if (sqlite3_wal_checkpoint(conn, NULL))
{
printf("sqlite3_wal_autocheckpoint failed\n");
exit(1);
}
sqlite3_close(conn);
}
}

int main(int argc, char **argv)
{
sqlite3 *conn = NULL;
char *err_msg = NULL;
pthread_t thread;
int fd, i, use_transactions = 0;
time_t start;
if (argc>1)
use_transactions = atoi(argv[1]);
printf("use_transactions=%d\n", use_transactions);
printf("Start\n");
if (unlink("test.db") || unlink("test.db-wal"))
{
printf("failed unlink test.db\n");
exit(1);
}
fd = open("test.db", O_CREAT|O_RDWR, 0666);
if (fd<0)
{
printf("could not open test.db\n");
exit(1);
}
close(fd);
conn = sql_open_conn();
sqlite3_enable_shared_cache(1);
sql_exec(conn, "PRAGMA journal_mode=WAL");
sql_exec(conn, "PRAGMA synchronous=normal");
sql_exec(conn, "PRAGMA temp_store=memory");
sql_exec(conn, "PRAGMA wal_autocheckpoint=-1");
sql_exec(conn, "create table tbl1 (one varchar(20), two varchar(20))");
if (pthread_create(, NULL, do_checkpoint, NULL))
{
printf("could not start thread\n");
exit(1);
}
start = time();
if (use_transactions)
sql_exec(conn, "BEGIN TRANSACTION");
while (1)
{
if (use_transactions && !(i++%10))
{
printf("END BEGIN transactions\n");
fflush(stdout);
sql_exec(conn, "END TRANSACTION");
sql_exec(conn, "BEGIN TRANSACTION");
}
sql_exec(conn, "INSERT INTO tbl1 values('aaa', "
"'bbb')");
}
if (use_transactions)
sql_exec(conn, "END TRANSACTION");
sqlite3_close(conn);
printf("Finished\n");
return 0;
}
On Tue, Aug 10, 2010 at 9:36 AM, Richard Hipp  wrote:

> On Tue, Aug 10, 2010 at 2:24 AM, Yoni Londner  wrote:
>
> > Hello,
> > I have a questions about the correct use of transactions and WAL.
> >
> > I am writing an application that:
> >  1. should very fast
> >  2. should be very responsive
> >  3. don't care if the last N minutes of data will be lost (but DB should
> > never be corrupted)
> >
> > What I tried to do:
> >  1. open a transactions and close it every 3 minutes (So most of the tune
> > all work is on memory, and thus is very fast)
> >  2. disable wal auto checkpoint (So I wont have very slow queries due to
> a
> > checkpoint)
> >  3. run wal checkpoint in another thread (with another connection) - so
> it
> > wont affect the responsiveness of the main thread.
> >
> > Results:
> >  1. process memory is increasing with no upper limit
> >  2. eventually I get an I/O error
> >
>
> I'm guessing your write transactions are preventing the checkpoint from
> running to completion.  Hence, the WAL grows without bound and the
> wal-index
> (an in-memory structure proportional in size to the WAL file) eventually
> uses up all memory.
>
> Set PRAGMA synchronous=NORMAL.  This prevents all fsync() calls on the
> writer thread at the cost of durability, which you say you don't care
> about.
> Omit the 3-minute transactions, allowing each write to be its own
> transaction.  Writes then will still be in-memory (if you count the
> operating system filesystem cache as "in-memory").  But then the
> checkpoints
> will be able to run and keep the size of the WAL file under control.
>
>
>
> >
> > Questions:
> >  1. what am I doing wrong.
> >  2. what is the correct way to achieve the goals I mentioned before.
> >
> > Thanks,
> > Jon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> 

Re: [sqlite] Incremental row number associated to the current Query?

[Kees Nuyt]

On Mon, 9 Aug 2010 17:53:02 -0500, Mike Henshaw
 wrote:

>>> Is there a way to create an incremental row or show a row number that is 
>>> linked to the current select query that can be used in
>>> calculations? 
>> 
>>> Basically a row counter for the current query that can be used in 
>>> calculations.
>
>>Your application makes a series of sqlite3_step calls, one for each row. 
>>Can't it just increment a counter on each step?
>
>>> 2. The application is written in C/CPP so no counters or variables from 
>>> C/CPP can be used.
>
>>I don't see how the second statement follows from the first. In fact, it 
>>appears that just the opposite should be the case.
>
>1. The application is from a third party with no access to the source code but 
>the SQL query can be updated since the SQL query is used in a custom HTML 
>template which can also be updated.
>2. The calculations to the RowNum would then have to be in the C/CPP 
>application which would nullify the use of customizable HTML template.
>
css will do that for you.

= Demo file 

http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd;>

http://www.w3.org/1999/xhtml; xml:lang="en"
lang="en">

Test pages


table { counter-reset: line-number; }
td:first-child:before {
content: counter(line-number) ".";
counter-increment: line-number;
padding-right: 0.3em; }




#. col1col2
val1.1val1.2
val2.1val2.1
val3.1val3.2



=== EOF ===
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] performance, transactions and wal checkpoints

[Richard Hipp]

On Tue, Aug 10, 2010 at 2:24 AM, Yoni Londner  wrote:

> Hello,
> I have a questions about the correct use of transactions and WAL.
>
> I am writing an application that:
>  1. should very fast
>  2. should be very responsive
>  3. don't care if the last N minutes of data will be lost (but DB should
> never be corrupted)
>
> What I tried to do:
>  1. open a transactions and close it every 3 minutes (So most of the tune
> all work is on memory, and thus is very fast)
>  2. disable wal auto checkpoint (So I wont have very slow queries due to a
> checkpoint)
>  3. run wal checkpoint in another thread (with another connection) - so it
> wont affect the responsiveness of the main thread.
>
> Results:
>  1. process memory is increasing with no upper limit
>  2. eventually I get an I/O error
>

I'm guessing your write transactions are preventing the checkpoint from
running to completion.  Hence, the WAL grows without bound and the wal-index
(an in-memory structure proportional in size to the WAL file) eventually
uses up all memory.

Set PRAGMA synchronous=NORMAL.  This prevents all fsync() calls on the
writer thread at the cost of durability, which you say you don't care about.
Omit the 3-minute transactions, allowing each write to be its own
transaction.  Writes then will still be in-memory (if you count the
operating system filesystem cache as "in-memory").  But then the checkpoints
will be able to run and keep the size of the WAL file under control.



>
> Questions:
>  1. what am I doing wrong.
>  2. what is the correct way to achieve the goals I mentioned before.
>
> Thanks,
> Jon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] performance, transactions and wal checkpoints

[Yoni Londner]

Hello,
I have a questions about the correct use of transactions and WAL.

I am writing an application that:
  1. should very fast
  2. should be very responsive
  3. don't care if the last N minutes of data will be lost (but DB should
never be corrupted)

What I tried to do:
  1. open a transactions and close it every 3 minutes (So most of the tune
all work is on memory, and thus is very fast)
  2. disable wal auto checkpoint (So I wont have very slow queries due to a
checkpoint)
  3. run wal checkpoint in another thread (with another connection) - so it
wont affect the responsiveness of the main thread.

Results:
  1. process memory is increasing with no upper limit
  2. eventually I get an I/O error

Questions:
  1. what am I doing wrong.
  2. what is the correct way to achieve the goals I mentioned before.

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