Re: [sqlite] temp files for app file format

[Scott Frankel]

On Nov 22, 2010, at 5:26 AM, Igor Tandetnik wrote:

> Scott Frankel  wrote:
>> Are journal files written to disk by applications that create SQLite
>> DBs on the fly?  If so, what are they called and where are they
>> written to?
>
> You seem to refer to in-memory (:memory:) database. No files,  
> journal or otherwise, are created for those.

OK, scratching that off the list.

Thanks
Scott



> -- 
> 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] temp files for app file format

[Igor Tandetnik]

Scott Frankel  wrote:
> Are journal files written to disk by applications that create SQLite
> DBs on the fly?  If so, what are they called and where are they
> written to?

You seem to refer to in-memory (:memory:) database. No files, journal or 
otherwise, are created for those.
-- 
Igor Tandetnik

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

[sqlite] temp files for app file format

[Scott Frankel]

Hi all,

Are journal files written to disk by applications that create SQLite  
DBs on the fly?  If so, what are they called and where are they  
written to?

I've recently started using SQLite as a file format for the  
application I'm developing.  Tracking down a mysterious bus error  
crash has lead me to consider SQLite temp files.

The Crash Report I'm getting on OSX 10.5 lists  
KERN_PROTECTION_FAILURE.  A few googles later, I find that this  
exception is caused by the thread trying to write to read-only  
memory.  My app mysteriously and suddenly stopped accepting all writes  
to its internal SQLite DB.  A stuck temp file lock could be consistent  
with this behavior.

My app creates an in-memory SQLite DB from PyQt:
theDb = QtSql.QSqlDatabase.addDatabase("QSQLITE")
theDb.setDatabaseName(":memory:")


Thanks!
Scott

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

[sqlite] Temp Files are not closed during a select call.

[Naganathan Rajesh]

We are executing a query which does a select from two different tables and does 
a union.(For eg : 
select phonename,uid from contact_primary_info union select name,itemId from 
Contact_SIM order by 1 ASC;
)
We are seeing that Sqlite lib is calling a openFile call two times with the 
same file name during this select operation.The second time the openFile call 
is 
 
For the first time,the sqlite library passed the following flag values:
isExclusive: 16 isDelete : 8 isCreate : 4 isReadOnly : 0 isReadWrite :2
 
Before closing the file,sqlite library again passed the same flag values with 
the same file name.Can anyone please explain if there is a chance that sqlite 
does like this without closing the previous file that is opened?
 

Best Regards,
N.Rajesh
Courage is the knowledge of how to fear what ought to be feared and how not to 
fear what ought not to be feared.
_
Post free property ads on Yello Classifieds now! www.yello.in
http://ss1.richmedia.in/recurl.asp?pid=221
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] temp files

[Dmytro Bogovych]

On Fri, 10 Sep 2004 15:58:24 -0400, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
OK.  My workaround trick didn't work afterall.  Looks like you
are stuck with a temporary file when doing an UPDATE or a mass
INSERT inside a transaction.
yes. update may be very simple. See the following log:
SQLite version 3.0.6
Enter ".help" for instructions
sqlite> create table ITEMS2(children INTEGER, refcount INTEGER);
sqlite> insert into ITEMS2(children, refcount) values(1,2);
sqlite> insert into ITEMS2(children, refcount) values(2,3);
sqlite> insert into ITEMS2(children, refcount) values(3,4);
sqlite> insert into ITEMS2(children, refcount) values(4,5);
sqlite> insert into ITEMS2(children, refcount) values(5,6);
sqlite> select ROWID from ITEMS2;
1
2
3
4
5
sqlite> begin
   ...> ;
sqlite> update ITEMS2 set children=0 where ROWID=3;
-- breakpoint triggering here --
sqlite> commit;
The OR COMMIT(ROLLBACK) options give me the same results.
--
With best regards,
Dmytro Bogovych

Re: [sqlite] temp files

[D. Richard Hipp]

Dmytro Bogovych wrote:
On Fri, 10 Sep 2004 15:31:00 -0400, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
SQLite is using the temporary file to hold a statement-level
rollback journal so that the partial results of the UPDATE
can be rolled back if it encounters an error half way through.
You can circumvent this by doing
UPDATE OR ROLLBACK

log:
SQLite version 3.0.6
Enter ".help" for instructions
sqlite> begin;
sqlite> update or rollback ITEMS set number=number+1 where number>2;
-- here is breakpoint triggering --
OK.  My workaround trick didn't work afterall.  Looks like you
are stuck with a temporary file when doing an UPDATE or a mass
INSERT inside a transaction.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

Re: [sqlite] temp files

[Dmytro Bogovych]

On Fri, 10 Sep 2004 15:31:00 -0400, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
SQLite is using the temporary file to hold a statement-level
rollback journal so that the partial results of the UPDATE
can be rolled back if it encounters an error half way through.
You can circumvent this by doing
UPDATE OR ROLLBACK
log:
SQLite version 3.0.6
Enter ".help" for instructions
sqlite> begin;
sqlite> update or rollback ITEMS set number=number+1 where number>2;
-- here is breakpoint triggering --
sqlite> commit;
sqlite> select * from ITEMS;
1
2
5
6
sqlite>
or
UPDATE OR IGNORE
The same story as with UPDATE OR ROLLBACK :(
--
With best regards,
Dmytro Bogovych

Re: [sqlite] temp files

[D. Richard Hipp]

Dmytro Bogovych wrote:
Greetings.
I'm trying to run simple update
update TREE set number = number + 1 where child = ? and parent = ? and  
   number > ?

During execution of this query the following func is called:
static int sqlite3pager_opentemp(char *zFile, OsFile *fd)
from pager.c and temporary file is created in my temp directory.
Is it expected behaviour?
Dmytro Bogovych wrote:
sqlite> create table ITEMS(number INTEGER);
sqlite> insert into ITEMS(number) values(1);
sqlite> insert into ITEMS(number) values(2);
sqlite> insert into ITEMS(number) values(3);
sqlite> insert into ITEMS(number) values(4);
sqlite> begin;
sqlite> update ITEMS set number=number+1 where number>2;
-- here I've got breakpoint triggering in sqlite3pager_opentemp.
SQLite is using the temporary file to hold a statement-level
rollback journal so that the partial results of the UPDATE
can be rolled back if it encounters an error half way through.
You can circumvent this by doing
   UPDATE OR ROLLBACK
or
   UPDATE OR IGNORE
instead of just plain
   UPDATE
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

Re: [sqlite] temp files

[Dmytro Bogovych]

On Fri, 03 Sep 2004 17:17:24 +0300, Dmytro Bogovych  
<[EMAIL PROTECTED]> wrote:

On Fri, 03 Sep 2004 17:07:36 +0300, Dmytro Bogovych  
<[EMAIL PROTECTED]> wrote:

On Fri, 03 Sep 2004 09:12:31 -0400, D. Richard Hipp <[EMAIL PROTECTED]>  
wrote:

Unable to reproduce.  I put a breakpoint on sqlite3pager_opentemp()
and did lots of UPDATEs in the style shown, but no temporary file
was ever opened.
I've built simple sqlite3.exe replacement using shell.c as main file with  
debugging info turned on.
The compiler was VC++ 6.0 SP6
There is a log:

SQLite version 3.0.6
Enter ".help" for instructions
sqlite> create table ITEMS(number INTEGER);
sqlite> insert into ITEMS(number) values(1);
sqlite> insert into ITEMS(number) values(2);
sqlite> insert into ITEMS(number) values(3);
sqlite> insert into ITEMS(number) values(4);
sqlite> begin;
sqlite> update ITEMS set number=number+1 where number>2;
-- here I've got breakpoint triggering in sqlite3pager_opentemp.
sqlite> commit;
sqlite> select * from ITEMS;
1
2
4
5
sqlite>
And here is another log without transactions:
SQLite version 3.0.6
Enter ".help" for instructions
sqlite> create table ITEMS(number INTEGER);
sqlite> insert into ITEMS(number) values(1);
sqlite> insert into ITEMS(number) values(2);
sqlite> insert into ITEMS(number) values(3);
sqlite> insert into ITEMS(number) values(4);
sqlite> update ITEMS set number=number+1 where number>2;
sqlite> select * from ITEMS;
1
2
4
5
sqlite>
No temp files.
As I can see this behaviour is caused by my attempts to use transactions.
Is it expected behaviour?
I feel it would be nice to have callbacks for operating with temporary  
files to be able to provide something like in-memory temporary files.

--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

Re: [sqlite] temp files

[Dmytro Bogovych]

On Fri, 03 Sep 2004 17:07:36 +0300, Dmytro Bogovych  
<[EMAIL PROTECTED]> wrote:

On Fri, 03 Sep 2004 09:12:31 -0400, D. Richard Hipp <[EMAIL PROTECTED]>  
wrote:

Unable to reproduce.  I put a breakpoint on sqlite3pager_opentemp()
and did lots of UPDATEs in the style shown, but no temporary file
was ever opened.
I've attached the database files and more additional info.
Seems attachments are not permitted in this mailing list.
I've posted them (7 and 1.4 KB each) to:
http://www.quickoutliner.com/Untitled.qof
http://www.quickoutliner.com/Untitled.qof-journal
--
With best regards,
Dmytro Bogovych

Re: [sqlite] temp files

[Dmytro Bogovych]

On Fri, 03 Sep 2004 09:12:31 -0400, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
Unable to reproduce.  I put a breakpoint on sqlite3pager_opentemp()
and did lots of UPDATEs in the style shown, but no temporary file
was ever opened.
I've attached the database files and more additional info.
If it will not help I'll try to make minimal test to reproduce this  
behaviour.
However current database is very small.

There is a log of SQL statements (I've obtained it from sqlite3_trace()):
PRAGMA synchronous = NORMAL
PRAGMA cache_size = 1000
PRAGMA temp_store = MEMORY
BEGIN;
select parent, child, number from TREE where parent is null order by number
COMMIT;
BEGIN;
select caption, encrypted, compressed, created, modified, type, children,  
refcount, origsize from ITEMS where ROWID=?
COMMIT;
BEGIN;
select blob_data from CFG where id = ?
COMMIT;
BEGIN;
select child from TREE where parent=? order by number
select caption, encrypted, compressed, created, modified, type, children,  
refcount, origsize from ITEMS where ROWID=?
COMMIT;
BEGIN;
select caption, encrypted, compressed, created, modified, type, children,  
refcount, origsize from ITEMS where ROWID=?
COMMIT;
BEGIN;
select caption, data, encrypted, compressed, created, modified, type,  
children, refcount, origsize from ITEMS where ROWID=?
COMMIT;
BEGIN;
select caption, data, encrypted, compressed, created, modified, type,  
children, refcount, origsize from ITEMS where ROWID=?
COMMIT;
BEGIN;
insert into ITEMS (caption, data, encrypted, compressed, created,  
modified, type, children) values (?, ?, ?, ?, ?, ?, ?, ?)
update TREE set number = number + 1 where child = ? and parent = ? and  
number > ?

Here I've got breakpoint on sqlite3pager_opentemp.
Call stack is:
vdbeapi.c, line 159, sqlite3_step:
  rc = sqlite3VdbeExec(p);
vdbe.c, line 2114, sqlite3VdbeExec:
  rc = sqlite3BtreeBeginStmt(pBt);
btree.c, line 1459, sqlite3BtreeBeginStmt:
  rc = pBt->readOnly ? SQLITE_OK : sqlite3pager_stmt_begin(pBt->pPager);
pager.c, line 2952, sqlite3pager_stmt_begin:
  rc = sqlite3pager_opentemp(zTemp, >stfd);
And breakpoint in sqlite3pager_opentemp was triggered.
In any case SQLite is great. Thank you!
--
With best regards,
Dmytro Bogovych
http://www.quickoutliner.com/

Re: [sqlite] temp files

[D. Richard Hipp]

Dmytro Bogovych wrote:
I'm trying to run simple update on the following table
CREATE TABLE tree
(
  parent INTEGER,
  child INTEGER,
  number INTEGER,
  children INTEGER
)
The query itself:
update TREE set number = number + 1 where child = ? and parent = ? and  
number > ?

During execution of this query the following func is called:
static int sqlite3pager_opentemp(char *zFile, OsFile *fd)
from pager.c and temporary file is created in my temp directory.
Unable to reproduce.  I put a breakpoint on sqlite3pager_opentemp()
and did lots of UPDATEs in the style shown, but no temporary file
was ever opened.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565