Re: [sqlite] Help with simple query

2010-07-28 Thread zipforbrains
Brilliant. Thanks a million. It's moments like these when I love the internet. Igor Tandetnik wrote: > > zipforbrains wrote: >> I have two tables, one with bank accounts, one which holds groupings of >> those >> accounts, as follows: >> Table Accounts >> aName >> aBalance >> >> Table GroupMem

Re: [sqlite] Help with simple query

2010-07-28 Thread Igor Tandetnik
zipforbrains wrote: > I have two tables, one with bank accounts, one which holds groupings of those > accounts, as follows: > Table Accounts > aName > aBalance > > Table GroupMembers > gName > aName > > What SQL query would total the account balances (aBalance) for all the > accounts (aName) ass

[sqlite] Help with simple query

2010-07-28 Thread zipforbrains
I have two tables, one with bank accounts, one which holds groupings of those accounts, as follows: Table Accounts aName aBalance Table GroupMembers gName aName What SQL query would total the account balances (aBalance) for all the accounts (aName) associated with each group (gName)? Each accoun

Re: [sqlite] [PHP5-FPM/Sqlite3] PDO can create file but no more

2010-07-28 Thread Gilles Ganault
On Tue, 27 Jul 2010 22:56:23 -0700, Roger Binns wrote: >You can if you quote it. Note use double quotes to quote table & column >names, single quotes for strings. You can also quote names using square >brackets - eg [table name]. Thanks Roger for the tip. __

Re: [sqlite] count distinct group by

2010-07-28 Thread Igor Tandetnik
Peng Yu wrote: > Suppose that I have a table of 4 columns. > > S R1 R2 T > > s1r1 r2 t1 > s1r1 r2 t2 > s2r3 r4 t5 > s2r5 r4 t6 > s3r6

[sqlite] Composite Foreign Key constraint fails when it should not.

2010-07-28 Thread George Somers
Hello, It seems that Composite Foreign Key constraint fails when it should not. For example: PRAGMA foreign_keys=ON; CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER); CREATE UNIQUE INDEX i1 ON t1(a,b); INSERT INTO t1 VALUES(100,200); CREATE TABLE t2(w INTEGER,x INTEGER,y INTEGER

Re: [sqlite] count distinct group by

2010-07-28 Thread Pavel Ivanov
I'm not sure what do you want to return for the case like this: s1r1 r2 t1 s1r1 r2 t2 s1r1 r3 t2 But for your initial request the following query will be good: select t1.* from table_name t1, (select s, count(*) cnt fr

[sqlite] count distinct group by

2010-07-28 Thread Peng Yu
Suppose that I have a table of 4 columns. S R1 R2 T s1r1 r2 t1 s1r1 r2 t2 s2r3 r4 t5 s2r5 r4 t6 s3r6 r7 t7 s3r6

Re: [sqlite] Any way to make xUpdate only alter the columns that are changed?

2010-07-28 Thread Ben Harper
Thanks for the clarity. I can live with it. From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns [rog...@rogerbinns.com] Sent: Wednesday, July 28, 2010 6:29 PM To: General Discussion of SQLite Database Subject: R

[sqlite] fcntl file locks from different threads? (what constitutes a "troublesome system"?)

2010-07-28 Thread JT Olds
I have a large deployment of thousands of SQLite databases accessed from the same multi-threaded process, and up until recently, I didn't even consider thread safety, because 1) I only ever talk to a SQLite database connection from one thread at a time, and 2) I am dumb. I do maintain SQLite data

Re: [sqlite] Any way to make xUpdate only alter the columns that are changed?

2010-07-28 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/28/2010 07:37 AM, Ben Harper wrote: > then it would be great if the xUpdate function could inform one that only the > field 'age' is being altered. As it is now, every field is fed to xUpdate, > causing a circumventable read/verify/write burden

Re: [sqlite] Coping with database growth/fragmentation

2010-07-28 Thread Max Vlasov
On Fri, Jul 23, 2010 at 5:11 AM, Taras Glek wrote: > Hello, > Recently I spent some time investigating sqlite IO patterns in Mozilla. > Two issues came up: keeping sqlite files from getting fragmented and > fixing fragmented sqlite files. > > Funny, that's why I like reading someone's questions

[sqlite] Any way to make xUpdate only alter the columns that are changed?

2010-07-28 Thread Ben Harper
It seems apparent from the docs (and the function headers) that there is no way to cause Sqlite's Virtual Table xUpdate function to notify the implementation of the exact fields that were altered. For example, if I do UPDATE people SET age=40 WHERE name='jim' then it would be great if the xUpda

Re: [sqlite] Memory leak in sqlite 3.7.0

2010-07-28 Thread Andy Gibbs
On Tuesday, July 27, 2010 6:43 PM, Richard Hipp wrote: >> [ ... ] >> >> However, I have also attached journaltest2.log which I think does >> demonstrate the memory leak. Having trawled through the full log file, >> it >> seems that the memory leak is coming from the FTS3 tests, so the >> journal