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

2007-02-21 Thread Ion Silvestru
>* 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.

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

2007-02-21 Thread Ion Silvestru
>>I'm fairly sure disk space requirements will be nearly identical in >>each case... In case of blobs in SQLite there will be less disk space used than in case of file system (cluster size etc.) - To unsubscribe, send

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

2007-02-21 Thread Martin Jenkins
[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. ;) Martin

Re: [sqlite] Effect of blobs on performance

2007-02-21 Thread Dan Kennedy
> 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). Three technical reasons for me personally: * Can include blob operations as part of atomic transactions. * In

Re: [sqlite] Using AVG() Correctly

2007-02-21 Thread miguel manese
On 2/22/07, Rich Shepard <[EMAIL PROTECTED]> wrote: However, I'm stuck on the proper SQL syntax. A nudge in the right direction -- including pointers to the appropriate documentation -- would be much appreciated. The "rule of thumb" is that anything that appears in the group-by clause can

[sqlite] Re: Using AVG() Correctly

2007-02-21 Thread Igor Tandetnik
Rich Shepard <[EMAIL PROTECTED]> wrote: I have a table, 'voting,' with 31 columns. For each of 28 REAL columns I need to calculate averages both by groups and total. I tried: sqlite> select AVG(pos) from voting where cat = 'eco'; and 0.0 was returned. The query looks good. What's the data

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

2007-02-21 Thread miguel manese
This is not actually about SQLite. man umask M. Manese On 2/22/07, Shan, Zhe (Jay) <[EMAIL PROTECTED]> wrote: Hi, 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

[sqlite] about default file permission of SQLite database file

2007-02-21 Thread Shan, Zhe (Jay)
Hi, 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? Thanks. Jay

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

2007-02-21 Thread Dennis Cote
[EMAIL PROTECTED] wrote: Sure you can. You just have to put the expression in parentheses (to avoid a parsing conflict). Try this: CREATE TABLE test1( date TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now')), dummy int ); INSERT INTO test1(dummy) VALUES(1); SELECT

RE: [sqlite] Effect of blobs on performance

2007-02-21 Thread Brett Keating
Thanks I think this answers my question well! Brett -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 21, 2007 1:41 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Effect of blobs on performance "Brett Keating" <[EMAIL PROTECTED]>

RE: [sqlite] Effect of blobs on performance

2007-02-21 Thread Brett Keating
Thanks for the quick reply. I was lysdexic, I meant to say 20 columns. Probably would never exceed 20,000 rows, most likely would hover around 2-4K rows in a typical situation. If it has no effect on performance, I'd rather hold it in the database because I do like the idea of having a "neat

Re: [sqlite] Effect of blobs on performance

2007-02-21 Thread Eric Scouten
My suggestion: Do a quick experiment. I had a similar question a year or so ago. I wrote some code a year or so that generated random blobs of varying sizes and tossed them into a SQLite DB and onto files on the file system (Mac OS). There are some complicating variables, such as our

Re: [sqlite] Effect of blobs on performance

2007-02-21 Thread drh
"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

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

2007-02-21 Thread drh
"P Kishor" <[EMAIL PROTECTED]> wrote: > > is it possible to add usage such as the above, and many, many > wonderful SQL suggestions routinely provided by Igor Tandetnik (thanks > Igor!) to the syntax docs in the form of user-submitted comments? > I was trying to move all of the documentation

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

2007-02-21 Thread P Kishor
On 2/21/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Dennis Cote <[EMAIL PROTECTED]> wrote: > > The problem is that you can't use a function like strftime as the > default value for a column when you create a tbale. It only accepts > NULL, a string constant, a number, or one of the magic

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

2007-02-21 Thread drh
Dennis Cote <[EMAIL PROTECTED]> wrote: > > The problem is that you can't use a function like strftime as the > default value for a column when you create a tbale. It only accepts > NULL, a string constant, a number, or one of the magic current_* values. > Sure you can. You just have to put

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

2007-02-21 Thread Dennis Cote
[EMAIL PROTECTED] wrote: This isn't a bug. The magic current_timestamp keyword is really an alias for "datetime('now')". And datetime('now') returns you a text string in the format "YY-MM-DD HH:MM:SS". Yes, this means that the seconds have been rounded to the nearest whole second. But

Re: [sqlite] Re: Unexpected Query Results

2007-02-21 Thread Rich Shepard
On Wed, 21 Feb 2007, Igor Tandetnik wrote: Yes, that's what GROUP BY does. One representative for each group. Igor, A-ha! It has been a long time for me. select * from voting order by cat, pos; Thank you very much. Makes sense now. Rich -- Richard B. Shepard, Ph.D. |

[sqlite] Re: Unexpected Query Results

2007-02-21 Thread Igor Tandetnik
Rich Shepard <[EMAIL PROTECTED]> wrote: The table has 180 rows and 31 columns. What I need to do is extract the records and group them by two columns (one as a sub-group of the other). However, even one 'group by' retrieves only three records, the last one for each group: Yes, that's what

Re: [sqlite] types3-1.3 fails on x86_64

2007-02-21 Thread drh
Alexey Tourbin <[EMAIL PROTECTED]> wrote: > Hello, > > All tests pass on i386 but the following test fails on x86_64: > > types3-1.3... > Expected: [wideInt integer] > Got: [int integer] This is a failure in the test harness, not in SQLite itself. This is nothing to worry about. -- D.

[sqlite] types3-1.3 fails on x86_64

2007-02-21 Thread Alexey Tourbin
Hello, All tests pass on i386 but the following test fails on x86_64: types3-1.3... Expected: [wideInt integer] Got: [int integer] pgpOit3QhrHc1.pgp Description: PGP signature