Re: [sqlite] Help with simple query

[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 GroupMembers
>> gName
>> aName
>> 
>> What SQL query would total the account balances (aBalance) for all the
>> accounts (aName) associated with each group (gName)?
> 
> select gName, sum(aBalance)
> from Accounts a join GroupMembers g on (a.aName = g.aName)
> group by gName;
> 
> -- 
> Igor Tandetnik
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Help-with-simple-query-tp29293030p29293103.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Help with simple query

[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) associated with each group (gName)?

select gName, sum(aBalance)
from Accounts a join GroupMembers g on (a.aName = g.aName)
group by gName;

-- 
Igor Tandetnik

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

[sqlite] Help with simple query

[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 account has its
own group, and can be a member of any number of other groups.

For instance, given the following database, how could I create the results
listed below it?
CREATE TABLE Accounts (aName TEXT(20), aBalance FLOAT);
CREATE TABLE GroupMembers (gName TEXT(20), aName TEXT(20));
INSERT INTO Accounts VALUES ("Visa","100");
INSERT INTO Accounts VALUES ("Savings","125");
INSERT INTO Accounts VALUES ("Cheque","150");
INSERT INTO GroupMembers VALUES ("Visa","Visa");
INSERT INTO GroupMembers VALUES ("Savings","Savings");
INSERT INTO GroupMembers VALUES ("Cheque","Cheque");
INSERT INTO GroupMembers VALUES ("Wells Fargo","Visa");
INSERT INTO GroupMembers VALUES ("Wells Fargo","Savings");
INSERT INTO GroupMembers VALUES ("Wells Fargo","Cheque");
INSERT INTO GroupMembers VALUES ("KeyCard","Cheque");
INSERT INTO GroupMembers VALUES ("KeyCard","Savings");

DESIRED RESULT expressed as gName|total

Visa|100
Savings|125
Cheque|150
KeyCard|275
Wells Fargo|375


The closest I can get to the right results is the following query, but as
you can see below it, it is still very wrong:

select GroupMembers.gName, sum(Accounts.aBalance) from Accounts,
GroupMembers where Accounts.aName in (select GroupMembers.aName from
GroupMembers) group by GroupMembers.gName

Cheque|375
KeyCard|750
Savings|375
Visa|375
Wells Fargo|1125

And now I am out of knowhow. Anyone?

Thanks
John

-- 
View this message in context: 
http://old.nabble.com/Help-with-simple-query-tp29293030p29293030.html
Sent from the SQLite mailing list archive at Nabble.com.

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

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

[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.

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

Re: [sqlite] count distinct group by

[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 r7 t7
> s3r6 r8 t9
> s4r9 r10   t10
> 
> I want to select only the rows where if S column are the same, R1
> column is the same and R2 column is the same.

select * from mytable where s in
(select s from mytable
 group by s
 having min(r1)=max(r1) and min(r2)=max(r2)
);

-- 
Igor Tandetnik


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

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

[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,FOREIGN KEY(x,y) REFERENCES 
t1(a,b));
   INSERT INTO t2 VALUES(300,100,200);
   UPDATE t1 set b = 200 where a = 100;

The UPDATE on table t1 will fail, even if it does not violate the foreign key 
on table t2.

This is related somewhat to http://www.sqlite.org/src/info/dd08e5a988  and  
http://www.sqlite.org/src/info/636f86095e .

The real database has over 240 tables, doing CRUD, using a lot of "surrogate 
key with propagation", and each UPDATE statement always specify all fields of 
the table (except for primary key fields), so updating fields with same values 
on FK happens often.

Thanks!
George Somers

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

Re: [sqlite] count distinct group by

[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
   from (select distinct s, r1, r2 from table_name)
   group by s) t2
 where t1.s = t2.s
  and t2.cnt = 1;

I believe the only index that'll help you is on (s, r1, r2).


Pavel

On Wed, Jul 28, 2010 at 4:15 PM, Peng Yu  wrote:
> Suppose that I have a table of 4 columns.
>
> S      R1           R2       T
> 
> s1    r1             r2         t1
> s1    r1             r2         t2
> s2    r3             r4         t5
> s2    r5             r4         t6
> s3    r6             r7         t7
> s3    r6             r8         t9
> s4    r9             r10       t10
>
> I want to select only the rows where if S column are the same, R1
> column is the same and R2 column is the same.
>
> For the above examples, I want to keep only the following rows,
> because for 's3', 'r7'!='r8' and for 's2', 'r3'!='r5'. Could you show
> me what is the correct query to do this? What index I should create on
> this table to speed up this query?
>
> s1    r1             r2         t1
> s1    r1             r2         t2
> s4    r9             r10       t10
>
> --
> Regards,
> Peng
> ___
> 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] count distinct group by

[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 r8 t9
s4r9 r10   t10

I want to select only the rows where if S column are the same, R1
column is the same and R2 column is the same.

For the above examples, I want to keep only the following rows,
because for 's3', 'r7'!='r8' and for 's2', 'r3'!='r5'. Could you show
me what is the correct query to do this? What index I should create on
this table to speed up this query?

s1r1 r2 t1
s1r1 r2 t2
s4r9 r10   t10

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

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

[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: Re: [sqlite] Any way to make xUpdate only alter the columns that are   
changed?

-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 on the VT implementation.
>
> This must be a frequent question?

It isn't a frequent question and the current semantics do make sense in
various situations.  For example one VT I wrote deals with CouchDB where all
writes going back to it must be complete (you can't update just one field).
 SQLite itself packs the fields together as it does need all of their values
in order update a row.

It is also possible for more than one field to modified at a time with an
update, not to mention being set to the value they already have:

  UPDATE people SET age=age, name=name WHERE 1

You are however right that there are times when you only want to know what
changed, not the complete row.  This could only be done by updating the VT
api which means lots of new code, having to support the current xUpdate as
well as the new one so that existing VT implementations continue to work,
testing of all possible code paths etc.

Or you could just live with it :-)  For my code I keep a cache of the most
recently requested rows as there was an extra id I needed that isn't
presented to SQLite, so when the xUpdate comes I don't need the read/verify
burden.

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

iEYEARECAAYFAkxQWuUACgkQmOOfHg372QTNLQCgycZ5O67MVmQbkcT5Y9UzquO/
Lg8An2Pj8O3a3DxfvCPm3+7T284tzzzv
=J9p8
-END PGP SIGNATURE-
___
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] fcntl file locks from different threads? (what constitutes a "troublesome system"?)

[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 database connections from threadpools, and so,
often, I am jumping the connection between threads within the
threadpool, with open transactions, prepared statements, etc, though
never concurrently.

I was just pointed to http://www.sqlite.org/faq.html#q6

We are using SQLite 3.6.22 on Debian Etch (sadness), and I just wrote
some test code around using fcntl to lock and unlock a file from
different threads, and I can't seem to get it to break, nor have we
seen any trouble with our deployment.

So, my questions are:

1) Did we somehow magically avoid this bullet?
2) What situations with fcntl in multiple threads cause sadness?
3) Is Etch a "troublesome system"?
4) What would failures in this scenario look like? Are we risking corruption?

My test code is Python, but Python uses native fcntl and pthreads in
the interpreter to implement its modules, so this should be similar to
what we're doing in our deployment. I run these two python scripts
concurrently on Etch and it works as expected.

#!/usr/bin/python
import fcntl, threading, time
f = file("/tmp/test-fcntl", 'w')
def thread1():
fcntl.lockf(f.fileno(), fcntl.LOCK_EX)
print "locked"
time.sleep(5)
def thread2():
time.sleep(5)
print "unlocking"
fcntl.lockf(f.fileno(), fcntl.LOCK_UN)
print "unlocked"
threads = [threading.Thread(target=t) for t in (thread1, thread2)]
for thread in threads: thread.start()
for thread in threads: thread.join()
# make sure other process locks when we unlock and not when we exit
time.sleep(3)

#!/usr/bin/python
import fcntl, time
time.sleep(1)
f = file("/tmp/test-fcntl", 'w')
print "locking 2"
fcntl.lockf(f.fileno(), fcntl.LOCK_EX)
print "locked 2"

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

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

[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 on the VT implementation.
> 
> This must be a frequent question?

It isn't a frequent question and the current semantics do make sense in
various situations.  For example one VT I wrote deals with CouchDB where all
writes going back to it must be complete (you can't update just one field).
 SQLite itself packs the fields together as it does need all of their values
in order update a row.

It is also possible for more than one field to modified at a time with an
update, not to mention being set to the value they already have:

  UPDATE people SET age=age, name=name WHERE 1

You are however right that there are times when you only want to know what
changed, not the complete row.  This could only be done by updating the VT
api which means lots of new code, having to support the current xUpdate as
well as the new one so that existing VT implementations continue to work,
testing of all possible code paths etc.

Or you could just live with it :-)  For my code I keep a cache of the most
recently requested rows as there was an extra id I needed that isn't
presented to SQLite, so when the xUpdate comes I don't need the read/verify
burden.

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

iEYEARECAAYFAkxQWuUACgkQmOOfHg372QTNLQCgycZ5O67MVmQbkcT5Y9UzquO/
Lg8An2Pj8O3a3DxfvCPm3+7T284tzzzv
=J9p8
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Coping with database growth/fragmentation

[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 in this list. It helps
sometimes solving old tasks :). As many noticed Windows system cache is hard
to control. So for example it almost impossible to clear reading cache for
testing purposes, once you read the file, it's in the cache so the following
timing numbers are irrelevant. There's an option for CreateFile,
FILE_FLAG_NO_BUFFERING, it can be used to disable the cache for a file when
one wants to work with it. I thought maybe to change the sources and prepare
a special version of sqlite allowing to open without cache. But a better
solution at least on XP came, if I "touch" a file with
CreateFile(..FILE_FLAG_NO_BUFFERING) and close it, it won't use the cache
for the next file opening, so for testing purposes I just made a checkbox in
an admin that "touches" the file before passing it to sqlite. And it seems
it really works.

So, Taras, thank for your post )

Also with this approach I tried to test places.sqlite, particularly
moz_places table, the query was

SELECT * FROM moz_places WHERE url Like "%double%"

I suppose that mozilla team probably uses different queries, but since there
are no fts table recognizable, there should be some kind of full-scan.

So, my tests on two hard drives showed that windows fragmentation had small
effect on the performance of the query, while VACUUM; results had
significant. Before Vacuum, my long time places.sqlite 13M in size, having
moz_places with 16893 records, return results after 8-10 seconds, depending
on the place it lived, but after VACUUM, the results were between 150ms and
300ms.

I think that this can be due to the nature of windows read ahead cache, so
when sqlite tables are placed in consequent blocks of file, Windows loads
the same pages sqlite expects. So before any file system defragmentation,
internal sqlite defragmentation (VACUUM) have to be applied.

Max Vlasov,
maxerist.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 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 on the VT implementation.

This must be a frequent question?

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

Re: [sqlite] Memory leak in sqlite 3.7.0

[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
>> journaltest2.log file is the output from:
>>
>> ./testfixture test/permutations.test journaltest fts3cov.test
>> fts3malloc.test memsubsys2.test
>>
>> I tried a similar run with inmemory_journal instead of journaltest, but
>> this gave me a "database disk image is malformed" error (see
>> inmemory_journal2.log), but this is not an error I received when running 
>> the
>> full test suite, so it is probably because I'm running a test sequence 
>> that
>> I shouldn't.
>
>
> Correct.  fts3cov.test is not compatible with inmemory_journal.
>

So does this mean that the memory leak that comes out when running "make 
fulltest" or through "./testfixture test/permutations.test journaltest 
fts3cov.test fts3malloc.test memsubsys2.test" is not really a memory leak, 
just an incompatible test that has made it into the test suite?

Oh, and thanks for merging my suggested change for "configure" into your 
repository - I spotted it on my daily browse through.

Regards
Andy

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

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

[Simon Slavin]

On 28 Jul 2010, at 6:56am, Roger Binns wrote:

> This works:
> 
>  create table ""("" "");

The obfuscated SQLite contest closed three months ago.

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