Re: [sqlite] Comparing Value of a Table and creating a Delta Report

Ralf Jantschek wrote: > Thanks a lot for that. > > Considering my configuration: > > Table1: T1.id, t2.id, t1.name (, some other fields) > > Table2: T2.id, t3.id (m:n relation) > > Table3: T3.id, name, value, status (values: 0, 1, 9) > > > I still got a problem with this one: > > select * f

[sqlite] Creating directed graphs and simple examples

In the interests of itch-scratching, I've created a simple-ish tool to render directed graphs from sqlite databases with foreign keys [using graphviz]. I occasionally see people asking after such things in the sqlite IRC channel. It produces output like this: http://icculus.org/~chunky/stuff/sqlit

Re: [sqlite] Comparing Value of a Table and creating a Delta Report

Thanks a lot for that. Considering my configuration: Table1: T1.id, t2.id, t1.name (, some other fields) Table2: T2.id, t3.id (m:n relation) Table3: T3.id, name, value, status (values: 0, 1, 9) I still got a problem with this one: select * from MyTable t1 join MyTable t2 on (t1.name = t2.nam

Re: [sqlite] Comparing Value of a Table and creating a Delta Report

On Mar 15, 2011, at 7:29 PM, Ralf Jantschek wrote: > I have to compare entries with status=0 to status=1 and find out the > differences in name, value fields. Tangentially related: "DiffKit is like the Unix diff utility, but for tables instead of lines of text" http://www.diffkit.org/

Re: [sqlite] Comparing Value of a Table and creating a Delta Report

On 3/15/2011 2:29 PM, Ralf Jantschek wrote: > I have to compare entries with status=0 to status=1 and find out the > differences in name, value fields. > - are there name entries with Status=0 that are not there with > Status=1 select * from MyTable where status = 0 and name not in ( sele

[sqlite] Comparing Value of a Table and creating a Delta Report

Hello, I've got s.th. like this T1.id, t2.id(, some other fields) T2.id, t3.id (m:n relation) T3.id, name, value, status (values: 0, 1, 9) What I'm trying to do is the following: I have to compare entries with status=0 to status=1 and find out the differences in name, value fields. -

Re: [sqlite] Running TCL tests on 3.7.5

Some of the 3.7.5 tests use a new TCL construction, {*}, which requires TCL 8.5 You can bypass that particular one in tester.tcl: proc do_execsql_test {testname sql {result {}}} { fix_testname testname # uplevel do_test $testname [list "execsql {$sql}"] [list [list {*}$result]] uplevel do_t

Re: [sqlite] Only select most recent results takes longer

> My tests show that, counter-intuitively, the second query takes between > 3 and 5 times as long as the first query. > > This seems weird to me - any ideas why this would be? What does EXPLAIN QUERY PLAN for both queries say? And what SQLite version do you use? Also do you understand that '?' (e

Re: [sqlite] Running TCL tests on 3.7.5

On Tue, Mar 15, 2011 at 7:16 AM, Andrew Novoselsky < andrew.novosel...@gmail.com> wrote: > Hello > > I've got 3.7.5 sources via ZIP archive from > http://www.sqlite.org/cgi/src/info/ed759d5a9e - (SQLite version 3.7.5 > release candidate 2) > Configure and install under Ubuntu 10.10 was ok. > After

Re: [sqlite] DELETE and alias

Oliver Peters wrote: > sqlite> DELETE FROM a w > ...> WHERE EXISTS( > ...> SELECT 1 FROM b WHERE id = w.id) > ...> ; > Error: near "w": syntax error SQL syntax doesn't allow for an alias in DELETE statement (nor in INSERT or UPDATE). See if this helps: delete from a where id in (select i

Re: [sqlite] Optimization SQLite

Dear Richard, First of all, thank you for your fast answer. I am Thiago's advisor and we are facing some problems trying to optimizing SQLite, specially to understand its code and data structure. So, in order to help us, I would kindly ask you to provide us any documentation about the code and / o

[sqlite] Only select most recent results takes longer

Hey guys. Due to some help I had yesterday, I was advised to change this query: SELECT * FROM multiturnTable WHERE rowid in (SELECT rowid FROM multiturnTable WHERE player1 ='?' UNION ALL SELECT rowid FROM multiturnTable WHERE player2 = '?') AND (complete=0 OR p1SubmitScore=0 OR p2SubmitScore=0

[sqlite] Running TCL tests on 3.7.5

Hello I've got 3.7.5 sources via ZIP archive from http://www.sqlite.org/cgi/src/info/ed759d5a9e - (SQLite version 3.7.5 release candidate 2) Configure and install under Ubuntu 10.10 was ok. After that i run "make fulltest" and got following error: autoindex1-212... Ok autoindex1-300... Ok autoind

[sqlite] DELETE and alias

Hello, I tried the following with the CLI (3.7.5 under WinXP): CREATE TABLE a( id INTEGER PRIMARY KEY AUTOINCREMENT, sometext TEXT); INSERT INTO "a" VALUES(1,'hannes'); INSERT INTO "a" VALUES(2,'walther'); INSERT INTO "a" VALUES(3,'homer'); CREATE TABLE b( id INTEGER PRIMARY KEY); INSERT INTO "b"