Re: [sqlite] Nested Parens Stack Overflow

[drh]

Martin Jenkins <[EMAIL PROTECTED]> wrote:
> 
> I think you said that you generate the queries? If so, do you have to 
> nest so many nested ORs? I'm no SQL expert but
> 
>  AND  ( Customer.FullName in (
>   'Amazon.com.ksdc, Inc. - Campbellsville',
  [...]
>   'Okami2',
>   'Quickspice, Inc.'))
>)
> 
> appears to do the same job and a quick test suggests that it does. I 
> don't [know] what limits (if any) there would be with this approach but I 
> suspect Dr Hipp would rather you fix your SQL generator than have to 
> "fix" his parser. ;)
> 

The query optimizer will convert expressions of the form

(a=V1 or a=V2 or a=V3 ... or a=VN)

Into

(a IN (V1,V2,V3,...,VN))

So you should get the identical result regardless of which
input syntax you use.  The query optimizer makes this change,
by the way, because the second form is able to use an index
on the "a" column whereas the first form is not.

Oh, and Martin's suspicions about my preferences are correct :-)

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


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

Re: [sqlite] Nested Parens Stack Overflow

[drh]

"Matt Froncek" <[EMAIL PROTECTED]> wrote:
> I have a program generator that creates ORs in SQL nested. This causes a
> stack overflow in SQLite. Has this been addressed or will it be? 

SQLite uses a push-down automaton to parse the input SQL.
(See http://en.wikipedia.org/wiki/Pushdown_automaton)
In the SQLite implementation, the stack depth is finite.
It is this push-down automaton stack that you are overflowing.

The default maximum stack depth is 100.  If you are planning
on parsing some deeply right-recursive SQL statements (and apparently
you are) then you will need to increase the depth of the stack.
If you use the version of Lemon that I just checked in to build
the parser, then you do this by compiling the parse.c file using

-DYYSTACKDEPTH=1

If you are using an older version of the code, look in parse.c 
and find the place where it sets YYSTACKDEPTH and change it
to whatever you want.

You can mitigate the deep stack problem by using left 
recursive expressions instead of right recursive expressions.  
This expression is left-recursive:

   a=b OR c=d) OR e=f) OR g=h) OR i=j)

Left-recursive gets by with using a single level of the stack
for each level of parentheses.  The next expression is right-recursive:

   (a=b OR (c=d OR (e=f OR (g=h OR i=j

Right-recursive needs uses multiple levels of stack for each
parenthesis.  So you will overflow the stack much faster using
right-recursion.

If you omit the parentheses all together, like this:

   a=b OR c=d OR e=f OR g=h OR i=j

Then the the parser uses a finite depth stack no matter how long
the expression is.  This is your best option if you can get away
with it.

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


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

Re: [sqlite] Nested Parens Stack Overflow

[Martin Jenkins]

Matt Froncek wrote:

Martin,

Thank you for looking into this. The SQLite3.exe I tested with was 3.1.3.
And yes the SQL statement works fine as it. If you add one more nested OR to
the SQL statement then it stops working. I don't get a stack overflow like I
do in the ODBC driver I was testing with but it errors with a syntax error
about the "=".

Change the last to lines to:
  Customer.FullName = 'Okami' OR (
  Customer.FullName = 'Okami2' OR (
  Customer.FullName = 'Quickspice, Inc.')


OK, if I add another line I do get an error with sqlite3 v3.3.11

> SQL error near line 46: parser stack overflow

I think you said that you generate the queries? If so, do you have to 
nest so many nested ORs? I'm no SQL expert but


... AND  ( Customer.FullName in (
 'Amazon.com.ksdc, Inc. - Campbellsville',
 'Amazon.com.ksdc, Inc. - Coffeyville',
 'Amazon.com.ksdc, Inc. - Fernley',
 'Arizona Select',
 'ATW - Cobb Dist.',
 'SunOpta Food Distribution Group',
 'Callaway Consumer Products, LLC',
 'Cedarlane Natural Foods',
 'City Glatt, Inc.',
 'Columbus Distributing, Inc.',
 'Cost Plus World Markets-West',
 'DeKalb Farmers Market',
 'DPI-Midwest',
 'DPI-Northwest',
 'DPI-Rocky Mountain',
 'DPI-West',
 'Exel Pak',
 'Falcon Trading Co.',
 'Foodguys',
 'Foodology',
 'Gourmet Awards - Milwaukee',
 'Kehe Foods',
 'Marc Popcorn Company',
 'Marukai Markets',
 'Matsukas Food Company',
 'McCain Foods USA, Inc.',
 'Okami',
 'Okami2',
 'Quickspice, Inc.'))
  )

appears to do the same job and a quick test suggests that it does. I 
don't what limits (if any) there would be with this approach but I 
suspect Dr Hipp would rather you fix your SQL generator than have to 
"fix" his parser. ;)


Martin

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

RE: [sqlite] Nested Parens Stack Overflow

[Matt Froncek]

Martin,

Thank you for looking into this. The SQLite3.exe I tested with was 3.1.3.
And yes the SQL statement works fine as it. If you add one more nested OR to
the SQL statement then it stops working. I don't get a stack overflow like I
do in the ODBC driver I was testing with but it errors with a syntax error
about the "=".

Change the last to lines to:
  Customer.FullName = 'Okami' OR (
  Customer.FullName = 'Okami2' OR (
  Customer.FullName = 'Quickspice, Inc.')

Matt Froncek
QODBC Development Support / FLEXquarters.com LLC Consultant
QODBC Driver for QuickBooks - Unleash your data at www.qodbc.com

 

-Original Message-
From: Martin Jenkins [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 20, 2007 10:19 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Nested Parens Stack Overflow

Matt Froncek wrote:
> I have a program generator that creates ORs in SQL nested. This causes a
> stack overflow in SQLite. Has this been addressed or will it be? If so
what
> version was it addressed. I am not sure how to search for the answer to
this
> question.

You ought to post a schema and some sample data and say what version is
failing. I spent about 20 minutes hacking your SQL into a sample script
and the following works with sqlite3 v3.3.13. You might have to fix the
line endings for the sales line inserts but at least there shouldn't be
any embedded spaces. Ahem. ;) Sample output is at the end.

HTH

Martin

Paste this into pp.sql and then "sqlite3 pp.db < pp.sql"

 cut 

DROP TABLE IF EXISTS Item;
CREATE TABLE Item(_Type, FullName, ListID);
INSERT INTO Item VALUES ('ItemInventory', 'FullName0', 0);
INSERT INTO Item VALUES ('ItemInventory', 'FullName1', 1);
INSERT INTO Item VALUES ('ItemInventory', 'FullName2', 2);
INSERT INTO Item VALUES ('ItemInventory', 'FullName3', 3);

DROP TABLE IF EXISTS Customer;
CREATE TABLE Customer(ListID, CustomFieldLevel, FullName);
INSERT INTO Customer VALUES(1, 'CFL123', 'Amazon.com - Campbellsville');
INSERT INTO Customer VALUES(2, 'CFL123', 'Amazon.com - Coffeyville');
INSERT INTO Customer VALUES(3, 'CFL123', 'Amazon.com - Fernley');
INSERT INTO Customer VALUES(4, 'CFL123', 'Arizona Select');
INSERT INTO Customer VALUES(5, 'CFL123', 'ATW - Cobb Dist.');
INSERT INTO Customer VALUES(6, 'CFL123', 'SunOpta Food Distribution');
INSERT INTO Customer VALUES(7, 'CFL123', 'Callaway Consumer Products');
INSERT INTO Customer VALUES(8, 'CFL123', 'Cedarlane Natural Foods');
INSERT INTO Customer VALUES(9, 'CFL123', 'City Glatt, Inc.');
INSERT INTO Customer VALUES(10, 'CFL123', 'Columbus Distributing');
INSERT INTO Customer VALUES(11, 'CFL123', 'Cost Plus World');
INSERT INTO Customer VALUES(12, 'CFL123', 'DeKalb Farmers Market');
INSERT INTO Customer VALUES(13, 'CFL123', 'DPI-Midwest');
INSERT INTO Customer VALUES(14, 'CFL123', 'DPI-Northwest');
INSERT INTO Customer VALUES(15, 'CFL123', 'DPI-Rocky Mountain');
INSERT INTO Customer VALUES(16, 'CFL123', 'DPI-West');
INSERT INTO Customer VALUES(17, 'CFL123', 'Exel Pak');
INSERT INTO Customer VALUES(18, 'CFL123', 'Falcon Trading Co.');
INSERT INTO Customer VALUES(19, 'CFL123', 'Foodguys');
INSERT INTO Customer VALUES(20, 'CFL123', 'Foodology');
INSERT INTO Customer VALUES(21, 'CFL123', 'Gourmet Awards - Milwaukee');
INSERT INTO Customer VALUES(22, 'CFL123', 'Kehe Foods');
INSERT INTO Customer VALUES(23, 'CFL123', 'Marc Popcorn Company');
INSERT INTO Customer VALUES(24, 'CFL123', 'Marukai Markets');
INSERT INTO Customer VALUES(25, 'CFL123', 'Matsukas Food Company');
INSERT INTO Customer VALUES(26, 'CFL123', 'McCain Foods USA, Inc.');
INSERT INTO Customer VALUES(27, 'CFL123', 'Okami');
INSERT INTO Customer VALUES(28, 'CFL123', 'Quickspice, Inc.');

DROP TABLE IF EXISTS SalesLine;
CREATE TABLE SalesLine(SalesLineDesc, SalesLineItemRefFullName, TxnDate, 
SalesLineAmount, _Type, SalesLineItemRefListID, CustomerRefListID);
INSERT INTO SalesLine VALUES ('SalesLineDesc', 
'SalesLineItemRefFullName', '2006-09-01', 123.45, 'InventoryItem', 1, 9);
INSERT INTO SalesLine VALUES ('SalesLineDesc', 
'SalesLineItemRefFullName', '2006-09-01', 123.45, 'InventoryItem', 2, 9);
INSERT INTO SalesLine VALUES ('SalesLineDesc', 
'SalesLineItemRefFullName', '2006-09-01', 123.45, 'InventoryItem', 3, 9);
INSERT INTO SalesLine VALUES ('SalesLineDesc', 
'SalesLineItemRefFullName', '2006-09-01', 123.45, 'InventoryItem', 4, 9);

SELECT  SalesLine.ROWID AS FQROWID,
   Item.FullName AS FQALIAS_1,
   Item._Type AS FQALIAS_2,
   Customer.CustomFieldLevel AS FQALIAS_3,
   Customer.FullName AS FQALIAS_4,
   SalesLine.SalesLineDesc AS FQALIAS_5,
   SalesLine.SalesLineItemRefFullName AS FQALIAS_6,
   SalesLine.TxnDate AS FQALIAS_7,
   SalesLine.SalesLineAmount AS FQALIAS_8,
   SalesLine._Type AS  FQALIAS_9
FROM SalesLine SalesLine
LEFT OUTER JOIN Item Item ON Item.ListID = SalesLine.SalesLineItemRefListID
LEFT OUTER JOIN Customer Customer ON Customer.ListID = 

Re: [sqlite] SQLite in Adobe Lightroom

[Eric Scouten]

On 22 Feb 2007, at 09:45, Jeff Godfrey wrote:

Also, if this is deemed too far off topic for the list, I'll be  
happy to take it offline.


I'm assuming this is getting off-topic for the SQLite list, so I'll  
respond to Jeff offline.


-Eric


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

Re: [sqlite] Effect of blobs on performance

[drh]

Teg <[EMAIL PROTECTED]> wrote:
> Hello drh,
> 
> You tested under Windows with synchronous=OFF? I mean specifically
> that way. I've never seen Sqlite trash a DB when I had synchronous
> turned on even with app crashes.
> 

An application crash should not corrupt the DB even with
synchronous=OFF.  But with synchronous=OFF, an OS crash or
a power failure might corrupt the DB.  So if you feel like
your os is stable (windows never crashes?) and you have a
UPS or something to prevent untimely power loss, then it
is probably safe to set synchronous=OFF.

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


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

Re: [sqlite] SQLite in Adobe Lightroom

[Jeff Godfrey]

- Original Message - 
From: "Eric Scouten" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, February 22, 2007 10:55 AM
Subject: Re: [sqlite] SQLite in Adobe Lightroom



As the Adobe engineer who did much of the work to embed SQLite into 
Lightroom, I do want to express a couple of cautions about directly 
manipulating your Lightroom library.


Eric,

It's good to see an official Adobe representative on the list... ;^)

I understand and appreciate your comments.  In my case, I would only 
(possibly) have an interest in *reading* data from db.  I am an 
Pixmantec RSP user (which was recently absorbed by Adobe and then 
dead-ended) and have desparetely been trying to get Adobe to release 
the format of RSP's proprietary ".RWS" file so I can recover some of 
the work I've put into (my over 10-thousand) RSP RAW conversions. 
While that's looking unlikely to happen (though I'm still holding out 
some hope), I do see that Adobe plans to provide an RSP to Lightroom 
conversion tool.  After noticing that LR uses SQLite, I am now hoping 
that the converted RWS settings will be stored in the SQLite database, 
which will hopefully make them more accessible to me (for use in other 
parts of my DAM workflow) than they currently are locked away in the 
binary RWS file.


Is it safe to assume that conversion settings will be stored in the 
SQLite database?  I don't suppose I we'll see any public schema 
documentation on the database content, will we?


Thanks for any additional insight.

Also, if this is deemed too far off topic for the list, I'll be happy 
to take it offline.


Thanks,

Jeff



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

Re: [sqlite] Effect of blobs on performance

[P Kishor]

see below for a counter perspective

On 2/22/07, John Stanton <[EMAIL PROTECTED]> wrote:

Thomas Fjellstrom wrote:
> On February 21, 2007, [EMAIL PROTECTED] wrote:
>
>>"Brett Keating" <[EMAIL PROTECTED]> wrote:
>>
>>>Hi,
>>>
>>>I'm curious about what the effect of having a blob in the database may
>>>be on performance. I have two design options: 1) put a small image file
>>>(15-30kbyte) into the database as a blob, and 2) store the image in a
>>>separate file on disk and hold the filename in the database. My table
>>>has around 20 rows in it, about half are strings/smaller blobs and half
>>>are integers.
>>>
>>>Option number one, for various reasons, is far more elegant and simple
>>>in terms of its impact on the rest of the code. However, I am concerned
>>>that holding such large amounts of data per record might impact
>>>performance. I could be worried about nothing though, which is why I'm
>>>writing to this list :).
>>
>>When I was designing the SQLite file format, I made the assumption
>>that BLOBs would be used infrequently and would not be very big.
>>The file format design is not optimized for storing BLOBs.  Indeed,
>>BLOBs are stored as a singly-linked list of database pages.  It
>>is hard to imagine a more inefficient design.
>>
>>Much to my surprise, people begin putting multi-megabyte BLOBs
>>in SQLite databases and reporting that performance really was not
>>an issue.  I have lately taken up this practice myself and routinely
>>uses SQLite database with BLOBs that are over 10MiB is size.  And
>>it all seems to work pretty well here on my Linux workstation.  I
>>have no explanation for why it works so well, but it does so I'm not
>>going to complain.
>>
>>If your images are only 30KiB, you should have no problems.
>>
>>Here's a hint though - make the BLOB columns the last column in
>>your tables.  Or even store the BLOBs in a separate table which
>>only has two columns: an integer primary key and the blob itself,
>>and then access the BLOB content using a join if you need to.
>>If you put various small integer fields after the BLOB, then
>>SQLite has to scan through the entire BLOB content (following
>>the linked list of disk pages) to get to the integer fields at
>>the end, and that definitely can slow you down.
>
>
> I still wonder about the utility of storing binary data in the db itself.
> Maybe it makes it more easy to distribute that way, but how often does one
> distribute an entire database in a "vendor specific" format?
>
> I'm quite interested in hearing people's reasoning for going the blob route,
> when you have a perfectly good "database" format for "blobs" already (various
> filesystems).

The BLOB method has two major advantages.  The first is that the data
all reside in one file.  The second is that it does not have a directory
size limit, a curse when storing files and one which requires extra
logic to build a tree structure of directories or some other way of
limiting directory size.

The downside is the relative slowness of retrieving large BLOBs,
although the anecdotal evidence is that this is not as much of a problem
as one would expect.

On balance I would use BLOBs for binary storage except in the case where
there is a predominance of very large files.
>


2 examples --

I had my website as a SQLite db. Since I am using a cheapo hosting
site, I was at the mercy of what they had. At some point, I update my
local version (on my laptop) to the then latest version of SQLite.
That was one of those inflection points at which the SQLite db format
was backward incompatible. The web host was not going to upgrade their
DBD::SQLite, and it was a lot of extra work for me to create my own
libs and use my my own DBD installs. I changed the backend to plain
text files that are stored in a file folder hierarchy like so
?/??/???/file (where ? are the first, the first two, and the first
three letters of the file name). The beauty of this approach is that I
can log in to my website remotely and just use vim to change
individual pages without having to do that from SQLite. I still use
SQLite for metadata.

Second example is for a destop application -- I was looking for an
application in which I could write disjointed thoughts and research
findings. One of the applications (I forget its name), really nice
app, used Coredata on Mac OS X Tiger as its storage. Yes, I could get
to the data, but if I ever stopped using that app, it would be a pain
to get my data out. I changed to another program (Scrivener) which
stores my documents as separate RTF files inside an OS X package. From
the outside, the package looks like a file, but right click on it, and
you can peek inside to find all the separate RTF documents. I can do
what I want to with those documents with TextEdit.

The above examples are not against the use of BLOBs. They are just
examples of where NOT storing BLOBs in a db is a strength. I know that
SQLite is very stable, and a lot of care has been taken to make it
corruption proof, 

Re[2]: [sqlite] Effect of blobs on performance

[Teg]

Hello drh,

You tested under Windows with synchronous=OFF? I mean specifically
that way. I've never seen Sqlite trash a DB when I had synchronous
turned on even with app crashes.

C


Thursday, February 22, 2007, 9:56:57 AM, you wrote:

dhc> Teg <[EMAIL PROTECTED]> wrote:
>> 
>> I'm pretty sure an application crash even without power failure can
>> corrupt to. At least in my experience. I keep synchronous on and
>> simply use "insert or ignore" syntax within a transaction to get
>> performance.
>> 

dhc> It is not suppose to.  There are extensive tests in the test
dhc> suite where we simulate application crashes and verify that the
dhc> database is not corrupted.  If you encounter a situation where
dhc> the database is corrupted, that is a bug and you should report
dhc> it.

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


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




-- 
Best regards,
 Tegmailto:[EMAIL PROTECTED]


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

Re: [sqlite] SQLite in Adobe Lightroom

[Eric Scouten]

As the Adobe engineer who did much of the work to embed SQLite into  
Lightroom, I do want to express a couple of cautions about directly  
manipulating your Lightroom library.


(1) If you change data in the database, this is unsupported; make  
backups as it's certainly possible to create a DB that would be  
unusable by Lightroom.


(2) It's likely that there will be significant changes to the schema  
in future versions to help us implement new features or improve  
performance.


That said, we'll be very interested to see what happens out there.

-Eric




On 22 Feb 2007, at 05:07, Daniel Önnerby wrote:


This is great!
I can imagine that there will be several opensource galleries  
taking advantage of this in the future.



[EMAIL PROTECTED] wrote:

"Jeff Godfrey" <[EMAIL PROTECTED]> wrote:


Though it's only a matter of curiosity, I wonder if anyone
here knows how/where Adobe employed SQLite in the Lightroom product?




Adobe stores just about all of your Lightroom state in an SQLite
database.  Find the database (on your Mac) at

   ~/Pictures/Lightroom/Lightroom\ Database.lrdb

You won't be able to view the database with the version of SQLite
that comes on your Mac (3.1.3) because Lightroom uses version 3.3.4
and thus creates database files with the descending-index feature
and the more efficient boolean format - databases that version 3.1.3
cannot read.  So if you want to peruse the data, download the
SQLite 3.3.14 and use it.

If you are using the windows version of Lightroom, I don't know
where you might find the database, but one can imagine that it
probably has a similar or identical name.

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


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-- 
---

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





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

Re: [sqlite] Effect of blobs on performance

[John Stanton]

Thomas Fjellstrom wrote:

On February 21, 2007, [EMAIL PROTECTED] wrote:


"Brett Keating" <[EMAIL PROTECTED]> wrote:


Hi,

I'm curious about what the effect of having a blob in the database may
be on performance. I have two design options: 1) put a small image file
(15-30kbyte) into the database as a blob, and 2) store the image in a
separate file on disk and hold the filename in the database. My table
has around 20 rows in it, about half are strings/smaller blobs and half
are integers.

Option number one, for various reasons, is far more elegant and simple
in terms of its impact on the rest of the code. However, I am concerned
that holding such large amounts of data per record might impact
performance. I could be worried about nothing though, which is why I'm
writing to this list :).


When I was designing the SQLite file format, I made the assumption
that BLOBs would be used infrequently and would not be very big.
The file format design is not optimized for storing BLOBs.  Indeed,
BLOBs are stored as a singly-linked list of database pages.  It
is hard to imagine a more inefficient design.

Much to my surprise, people begin putting multi-megabyte BLOBs
in SQLite databases and reporting that performance really was not
an issue.  I have lately taken up this practice myself and routinely
uses SQLite database with BLOBs that are over 10MiB is size.  And
it all seems to work pretty well here on my Linux workstation.  I
have no explanation for why it works so well, but it does so I'm not
going to complain.

If your images are only 30KiB, you should have no problems.

Here's a hint though - make the BLOB columns the last column in
your tables.  Or even store the BLOBs in a separate table which
only has two columns: an integer primary key and the blob itself,
and then access the BLOB content using a join if you need to.
If you put various small integer fields after the BLOB, then
SQLite has to scan through the entire BLOB content (following
the linked list of disk pages) to get to the integer fields at
the end, and that definitely can slow you down.



I still wonder about the utility of storing binary data in the db itself. 
Maybe it makes it more easy to distribute that way, but how often does one 
distribute an entire database in a "vendor specific" format?


I'm quite interested in hearing people's reasoning for going the blob route, 
when you have a perfectly good "database" format for "blobs" already (various 
filesystems).


The BLOB method has two major advantages.  The first is that the data 
all reside in one file.  The second is that it does not have a directory 
size limit, a curse when storing files and one which requires extra 
logic to build a tree structure of directories or some other way of 
limiting directory size.


The downside is the relative slowness of retrieving large BLOBs, 
although the anecdotal evidence is that this is not as much of a problem 
as one would expect.


On balance I would use BLOBs for binary storage except in the case where 
there is a predominance of very large files.




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


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








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

Re: [sqlite] SQLite in Adobe Lightroom

[drh]

Dennis Cote <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] wrote:
> >
> > download the
> > SQLite 3.3.14 and use it.
> >
> >   
> I assume that was a typo, and the version should have been either 3.3.4 
> or 3.3.13. Or are you working on another release as we speak (or type)?
> 

Wasn't exactly a "typo" - I've just done so many releases of
SQLite lately that I lost track of which was the latest.

3.3.13 is current and is stable and I do not have any
immediate plans to do another release soon.

The next release might be 3.4.0 which includes some of
those minor incompatible changes that have been accumulating
rapidly in the wiki.

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


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

Re: [sqlite] Effect of blobs on performance

[Dennis Jenkins]

[EMAIL PROTECTED] wrote:

Dennis Jenkins <[EMAIL PROTECTED]> wrote:
  
these are the settings that our app uses when it creates/opens the sqlite 
database:


db.ExecuteImmediate("PRAGMA synchronous=OFF");



With synchronous=OFF, a power failure might result in database
corruption.  Is this an issue for you?
--
D. Richard Hipp  <[EMAIL PROTECTED]>
  


Not really.  The data can be regenerated by the user without too much 
difficulty.  I don't have the numbers handy, but I seem to remember that 
I did some performance experiments and determined that the performance 
gains significantly outweighed the potential problems.  To my knowledge, 
for the past two years, only one user (out of many hundreds) has ever 
gotten a corrupt database.


I suppose I'll put in a ticket into our issue tracking system to review 
this decision.  According to a comment in our source code, I based this 
action on 
http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html#pragma-synchronous




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

Re: [sqlite] SQLite in Adobe Lightroom

[Dennis Cote]

[EMAIL PROTECTED] wrote:


download the
SQLite 3.3.14 and use it.

  
I assume that was a typo, and the version should have been either 3.3.4 
or 3.3.13. Or are you working on another release as we speak (or type)?


Dennis Cote

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

Re: [sqlite] Effect of blobs on performance

[drh]

Teg <[EMAIL PROTECTED]> wrote:
> 
> I'm pretty sure an application crash even without power failure can
> corrupt to. At least in my experience. I keep synchronous on and
> simply use "insert or ignore" syntax within a transaction to get
> performance.
> 

It is not suppose to.  There are extensive tests in the test
suite where we simulate application crashes and verify that the
database is not corrupted.  If you encounter a situation where
the database is corrupted, that is a bug and you should report
it.

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


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

Re: [sqlite] about default file permission of SQLite database file

[Shan, Zhe (Jay)]

I've tried umask, but it does not work for SQLite.

I search this emaillist, and find the following message in 2003 which is
related to this topic. I want to check if it is still true in the current
version. Thanks.

Jay


From: Dong Xuezhang-A19583

<[EMAIL 
PROTECTED]


Subject: SQLITE 
MODE
Newsgroups: 
gmane.comp.db.sqlite.general
Date: 2003-09-30 18:30:11 GMT (3 years, 20 weeks, 4 days, 14 hours and 24

minutes ago)


Hi,

Just want to buy some idea from you guys about the *permission* of

sqlite db *file*. It is hardcode to 644 right

now(user RW, group and other person R). In certain case, like group

developing or ..., you may want 664 or

even 666. There are two way to do this, one is make it become a

compilation option, another one is the pass in a

parameter from sqlite_open method. I notice that there is a parameter

(int mode) in sqlite_open never

been used, is it a good idea to use it for this purpose?

Thanks.


Xuezhang.




On 2/21/07, Joe Wilson <[EMAIL PROTECTED]> wrote:


--- "Shan, Zhe (Jay)" <[EMAIL PROTECTED]> wrote:
> If to use SQLite to create a database in Linux, the database file will
> be granted permission 644 as default.
> Is this value hardcoded in the current version? Is it possible to
> change this default vaule, say to 664 or something else?

man umask






Cheap talk?
Check out Yahoo! Messenger's low PC-to-Phone call rates.
http://voice.yahoo.com


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] SQLite in Adobe Lightroom

[Jeff Godfrey]

- Original Message - 
From: <[EMAIL PROTECTED]>

To: 
Sent: Thursday, February 22, 2007 6:37 AM
Subject: Re: [sqlite] SQLite in Adobe Lightroom




"Jeff Godfrey" <[EMAIL PROTECTED]> wrote:
> Though it's only a matter of curiosity, I wonder if anyone
> here knows how/where Adobe employed SQLite in the Lightroom 
> product?




Adobe stores just about all of your Lightroom state in an SQLite
database.  Find the database (on your Mac) at


Thanks for the info.  I am using the Windows version of LR, and for 
those interested, the database file is located here:


\\My Documents\My 
Pictures\Lightroom\Lightroom Database.lrdb


Seems to be just a standard SQLite database, viewable using the tool 
of your choice.  As a programmer / amateur photog, this is *very* 
interesting... ;^)


Jeff


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

Re[2]: [sqlite] Effect of blobs on performance

[Teg]

Hello drh,

Thursday, February 22, 2007, 9:08:08 AM, you wrote:

dhc> Dennis Jenkins <[EMAIL PROTECTED]> wrote:
>> 
>> these are the settings that our app uses when it creates/opens the sqlite 
>> database:
>> 
>> db.ExecuteImmediate("PRAGMA synchronous=OFF");

dhc> With synchronous=OFF, a power failure might result in database
dhc> corruption.  Is this an issue for you?
dhc> --
dhc> D. Richard Hipp  <[EMAIL PROTECTED]>


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

I'm pretty sure an application crash even without power failure can
corrupt to. At least in my experience. I keep synchronous on and
simply use "insert or ignore" syntax within a transaction to get
performance.



-- 
Best regards,
 Tegmailto:[EMAIL PROTECTED]


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

Re: [sqlite] Effect of blobs on performance

[Denis Sbragion]

Hello Dan,

On Thu, February 22, 2007 06:08, Dan Kennedy wrote:
> * Can include blob operations as part of atomic transactions.

me too. Transactions are a major advantage of database blobs.

Bye,

-- 
Denis Sbragion
InfoTecna
Tel: +39 0362 805396, Fax: +39 0362 805404
URL: http://www.infotecna.it






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

Re: [sqlite] Effect of blobs on performance

[drh]

Dennis Jenkins <[EMAIL PROTECTED]> wrote:
> 
> these are the settings that our app uses when it creates/opens the sqlite 
> database:
> 
> db.ExecuteImmediate("PRAGMA synchronous=OFF");

With synchronous=OFF, a power failure might result in database
corruption.  Is this an issue for you?
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] Using AVG() Correctly

[Rich Shepard]

On Thu, 22 Feb 2007, miguel manese wrote:


Best example using your particular case is Igor's answer:



select cat, pos, avg(col1), avg(col2), ...
from voting
group by cat, pos;



  Thank you, Miguel.

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

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

Re: [sqlite] Re: Using AVG() Correctly

[Rich Shepard]

On Wed, 21 Feb 2007, Igor Tandetnik wrote:


The query looks good. What's the data in the pos column? Could it be that
the average is indeed zero?


Igor,

  Of course! I was not thinking it completely through and now it's quite
clear.


select cat, pos, avg(col1), avg(col2), ...
from voting
group by cat, pos;


  Ah! That's what I was missing at the end of a very long day. Thank you
very much. As I wrote, it's been longer than I thought since I wrote SQL to
any extent. Time to get a basic SQL book to complement my advanced one by
Joe Celko.

Many thanks,

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

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

Re: [sqlite] Effect of blobs on performance

[Dennis Jenkins]

Thomas Fjellstrom wrote:
I still wonder about the utility of storing binary data in the db itself. 
Maybe it makes it more easy to distribute that way, but how often does one 
distribute an entire database in a "vendor specific" format?


I'm quite interested in hearing people's reasoning for going the blob route, 
when you have a perfectly good "database" format for "blobs" already (various 
filesystems).
  


1)
We use the sqlite encryption extension.  We want our blobs encrypted as 
well as our database, so putting the blobs into the database makes 
sense.  We have a special table for the blobs that has a primary key (3 
columns) and the blob.  The rest of the data is contained in other tables.


2)
We don't need to worry about atomically deleting disk blobs and database 
rows.  We take advantage of the ACID nature of sqlite.  This way we 
don't have to code for contingencies where the user has managed to 
delete or corrupt a blob, or a blob that our app can't delete even when 
it deletes the database row.


3)
Having everything in one package.  Makes tech support much easier if the 
user only has to transmit a single file instead of an entire directory.


4)
We modify the blobs at runtime.  ACIDness of sqlite is very nice here.  
I don't want to try to re-implement this directly on the filesystem 
(even if it becomes a simple rename operation).



Our blobs vary in size from 12K to 3M.  Sqlite is not a performance 
bottleneck for us... the client's internet connection is.


I have not done extensive performance tests on these settings, but these 
are the settings that our app uses when it creates/opens the sqlite 
database:


   db.ExecuteImmediate("PRAGMA page_size=4096");
   db.ExecuteImmediate("PRAGMA legacy_file_format=ON");
   db.ExecuteImmediate("PRAGMA cache_size=8000");
   db.ExecuteImmediate("PRAGMA synchronous=OFF");
   db.ExecuteImmediate("PRAGMA temp_store=2");


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

Re[2]: [sqlite] Effect of blobs on performance

[Teg]

Hello drh,

Thursday, February 22, 2007, 7:54:58 AM, you wrote:


dhc> A common use for SQLite is as an application file format.  When
you do File->>Open, instead of reading and parsing a bunch of
dhc> information in a proprietary format, just call sqlite3_open()
dhc> on the file instead.  File->Save becomes a no-op.  There is no
dhc> parser to write.  Performance problems can often be fixed simply
dhc> by adding another index.  All of your data is written to disk 
dhc> atomically and is relatively safe from corruption even if you 
dhc> turn off the power during a write.  There are a lot of advantages
dhc> to this approach.

dhc> Lots of people are starting to use SQLite as an application
dhc> file format.  Remember the SQLite slogan:

dhc> SQLite is not a replacement for Oracle, it is a
dhc> replacement for fopen().

>> 
>> I'm quite interested in hearing people's reasoning for going the blob route, 
>> when you have a perfectly good "database" format for "blobs" already 
>> (various 
>> filesystems).
>> 

dhc> Just yesterday, Eric Scouten posted on this list that he had done
dhc> a study and found that for BLOB smaller than 20-30K it was faster
dhc> to store them in an SQLite database than on disk.
dhc> --
dhc> D. Richard Hipp  <[EMAIL PROTECTED]>

I was reading a similar study that stated that 150K was the knee point
in general for DB blob performance. I store 1000's of JPG images in
SQLite as blobs, ranging in size up to 4 megs or so each (though the
typical size is in the 200-300K range). My only issue with this method
is that enumeration seems slower than enumerating the same files in a
folder. I store them in DB's because they're nice units of data I can
backup and move around anywhere without having worry about losing the
connection between the DB and the files themselves. Because they're
images, I only ever want to read them all at once so, not having
random access within the blobs is unimportant to me.



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

Re: [sqlite] SQLite in Adobe Lightroom

[Daniel Önnerby]

This is great!
I can imagine that there will be several opensource galleries taking 
advantage of this in the future.



[EMAIL PROTECTED] wrote:

"Jeff Godfrey" <[EMAIL PROTECTED]> wrote:
  

Though it's only a matter of curiosity, I wonder if anyone
here knows how/where Adobe employed SQLite in the Lightroom product?




Adobe stores just about all of your Lightroom state in an SQLite
database.  Find the database (on your Mac) at

   ~/Pictures/Lightroom/Lightroom\ Database.lrdb

You won't be able to view the database with the version of SQLite
that comes on your Mac (3.1.3) because Lightroom uses version 3.3.4
and thus creates database files with the descending-index feature
and the more efficient boolean format - databases that version 3.1.3
cannot read.  So if you want to peruse the data, download the
SQLite 3.3.14 and use it.

If you are using the windows version of Lightroom, I don't know
where you might find the database, but one can imagine that it
probably has a similar or identical name.

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


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

  


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

Re: [sqlite] Effect of blobs on performance

[drh]

Thomas Fjellstrom <[EMAIL PROTECTED]> wrote:
> 
> I still wonder about the utility of storing binary data in the db itself. 
> Maybe it makes it more easy to distribute that way, but how often does one 
> distribute an entire database in a "vendor specific" format?

A common use for SQLite is as an application file format.  When
you do File->Open, instead of reading and parsing a bunch of
information in a proprietary format, just call sqlite3_open()
on the file instead.  File->Save becomes a no-op.  There is no
parser to write.  Performance problems can often be fixed simply
by adding another index.  All of your data is written to disk 
atomically and is relatively safe from corruption even if you 
turn off the power during a write.  There are a lot of advantages
to this approach.

Lots of people are starting to use SQLite as an application
file format.  Remember the SQLite slogan:

SQLite is not a replacement for Oracle, it is a
replacement for fopen().

> 
> I'm quite interested in hearing people's reasoning for going the blob route, 
> when you have a perfectly good "database" format for "blobs" already (various 
> filesystems).
> 

Just yesterday, Eric Scouten posted on this list that he had done
a study and found that for BLOB smaller than 20-30K it was faster
to store them in an SQLite database than on disk.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] SQLite in Adobe Lightroom

[drh]

"Jeff Godfrey" <[EMAIL PROTECTED]> wrote:
> Though it's only a matter of curiosity, I wonder if anyone
> here knows how/where Adobe employed SQLite in the Lightroom product?
> 

Adobe stores just about all of your Lightroom state in an SQLite
database.  Find the database (on your Mac) at

   ~/Pictures/Lightroom/Lightroom\ Database.lrdb

You won't be able to view the database with the version of SQLite
that comes on your Mac (3.1.3) because Lightroom uses version 3.3.4
and thus creates database files with the descending-index feature
and the more efficient boolean format - databases that version 3.1.3
cannot read.  So if you want to peruse the data, download the
SQLite 3.3.14 and use it.

If you are using the windows version of Lightroom, I don't know
where you might find the database, but one can imagine that it
probably has a similar or identical name.

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


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

Re: [sqlite] Effect of blobs on performance

[drh]

Ion Silvestru <[EMAIL PROTECTED]> wrote:
> >* In SQLite, my blobs won't be corrupted if the machine loses power
> >  the way they (probably) will be if I write my own code to access
> >  the file-system.
> 
> But, in case of a corruption, you will have entire blob DB corrupted versus
> at least one file (aka one row in DB) corrupted.
> 

Not.  SQLite databases do not corrupt when you turn the power off.
When power is restored and you attempt to access the database again,
the transaction that you were in the middle of at the point of the
power failure automatically rolls back, restoring the database to
a sane state.  This is one of the key reasons why you would want
to use SQLite instead of fopen() for storing miscellaneous data.

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


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

Re: [sqlite] Precision of dates stores as Julian "real"

[drh]

"McDermott, Andrew" <[EMAIL PROTECTED]> wrote:
> Hi, 
> 
> > [EMAIL PROTECTED] wrote:
> > > Guess you can't please everybody :-)  Right now we have some 
> > > documentation in the source tree and some on the wiki, 
> > which I suppose 
> > > is guaranteed to please nobody.
> > 
> > So make the wiki available for download. ;)
> 
> I would like this too. ;)
> 
> Often I'm working without an internet connection and a having a local
> copy of the Wiki would be extremely useful.
> 

Been working on this for years.  Literally.  I just never seem to
find the time to complete the project.

   http://fossil-scm.hwaci.com/

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


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