Query to compare two sqlite databases

[John Reed]

Hello,

I compare an application every few days which has sqlite as it's client database.
I look at the content and check whether documents have made it into the application after it has been built. I also check the metadata in the sqlite client database for changes.
So, I am constantly comparing the last database with the newer database.
Both databases have exactly the same tables, with only the data being changed in most of the 51 tables.The largest table has about 3,700,000 rows. Most other tables have much less rows in them.
Could someone suggest an sql query to find the difference in the same table (ta) for both the last database (db1) and the newer database (db2)?
I can use SQLiteSpy to connect and attach to the databases.


Thanks for any help to point me in the direction.

[Rich Shepard]

Post by John Reed
I compare an application every few days which has sqlite as it's client
database. I look at the content and check whether documents have made it
into the application after it has been built. I also check the metadata in
the sqlite client database for changes. So, I am constantly comparing the
last database with the newer database. Both databases have exactly the
same tables, with only the data being changed in most of the 51 tables.The
largest table has about 3,700,000 rows. Most other tables have much less
rows in them. Could someone suggest an sql query to find the difference in
the same table (ta) for both the last database (db1) and the newer
database (db2)? I can use SQLiteSpy to connect and attach to the
databases.

You'll want to tune the syntax, but try something like:

SELECT colA FROM db1 WHERE (NOT EXIST colA IN db2);

The idea is to match rows in each table and where the equivalent row in db1
is not in db2, add that to the results table.

Rich

[luuk34]

Post by Rich Shepard

Post by John Reed
I compare an application every few days which has sqlite as it's client
database. I look at the content and check whether documents have made it
into the application after it has been built. I also check the metadata in
the sqlite client database for changes. So, I am constantly comparing the
last database with the newer database. Both databases have exactly the
same tables, with only the data being changed in most of the 51 tables.The
largest table has about 3,700,000 rows. Most other tables have much less
rows in them. Could someone suggest an sql query to find the difference in
the same table (ta) for both the last database (db1) and the newer
database (db2)? I can use SQLiteSpy to connect and attach to the
databases.

SELECT colA FROM db1 WHERE (NOT EXIST colA IN db2);
The idea is to match rows in each table and where the equivalent row in db1
is not in db2, add that to the results table.
Rich

you mean something like:
select id1, id2 from callprog a where id1 not in (select b.id1 FROM
callprog b where b.id1=a.id1 );

But what is there is more than 1 column?
it will grow in complexity when you have a lot of columns.....

[Rich Shepard]

Post by luuk34
select id1, id2 from callprog a where id1 not in (select b.id1 FROM
callprog b where b.id1=a.id1 );

Yeah; much better.

Post by luuk34
But what is there is more than 1 column? it will grow in complexity when
you have a lot of columns.....

My understanding of the original problem was that rows were about the
same, so finding a common column in db1 which was not in db2 was the need.
As long as there is one unique column common to both tables it makes no
difference how many other columns there are in each one.

Rich

[John Reed]

Hello,

Thanks for your response.
I'm almost there but I keep getting sql syntax error (near col1) with the last part of the query
where(not exists in db2);

Here's where I'm at:

select 'db1', db1.table1.col1, 'db2', db2.table2.col1 from db1.table1, db2.table2 WHERE (NOT EXISTS col1 IN db2);


Thanks.

--- On Fri, 9/24/10, Rich Shepard <rshepard-***@public.gmane.org> wrote:

From: Rich Shepard <rshepard-***@public.gmane.org>
Subject: Re: [sqlite] Query to compare two sqlite databases
To: "General Discussion of SQLite Database" <sqlite-users-CzDROfG0BjIdnm+***@public.gmane.org>
Date: Friday, September 24, 2010, 12:51 PM

select  id1, id2 from callprog a where id1 not in (select b.id1 FROM
callprog b where b.id1=a.id1 );

   Yeah; much better.

But what is there is more than 1 column? it will grow in complexity when
you have a lot of columns.....

   My understanding of the original problem was that rows were about the
same, so finding a common column in db1 which was not in db2 was the need.
As long as there is one unique column common to both tables it makes no
difference how many other columns there are in each one.

Rich

[Oliver Peters]

Post by John Reed
Hello,
I compare an application every few days which has sqlite as it's client database.
I look at the content and check whether documents have made it into the application after it has been built. I also check the metadata in the sqlite client database for changes.
So, I am constantly comparing the last database with the newer database.
Both databases have exactly the same tables, with only the data being changed in most of the 51 tables.The largest table has about 3,700,000 rows. Most other tables have much less rows in them.
Could someone suggest an sql query to find the difference in the same table (ta) for both the last database (db1) and the newer database (db2)?
I can use SQLiteSpy to connect and attach to the databases.

use EXCEPT


SELECT col01, col02,...
FROM table01
EXCEPT
SELECT col01, col02,...
FROM table02
;

table01 is the newest table while table02 is its predecessor

the result is what is not in table01

greetings
Oliver

[Oliver Peters]

Oliver Peters <***@...> writes:

sry

Post by Oliver Peters
the result is what is not in table01

I meant:
the result is what is in table01 but NOT in table02

Oliver

[John Reed]

Hello,

The following query works for me.

select * from db2.table where not exists (select * from db1.table where db1.table.column1 = db2.table.column1);

Thanks again for the assistance.


--- On Fri, 9/24/10, Oliver Peters <oliver.pet-S0/***@public.gmane.org> wrote:

From: Oliver Peters <oliver.pet-S0/***@public.gmane.org>
Subject: Re: [sqlite] Query to compare two sqlite databases
To: sqlite-users-CzDROfG0BjIdnm+***@public.gmane.org
Date: Friday, September 24, 2010, 2:54 PM

Oliver Peters <***@...> writes:

sry

Post by Oliver Peters
the result is what is not in table01

I meant:
the result is what is in table01 but NOT in table02

Oliver

[Alexey Pechnikov]

See hash-based tool for signature/delta calculation and replication here:
http://sqlite.mobigroup.ru/wiki?name=sqlite3-rdiff

<http://sqlite.mobigroup.ru/wiki?name=sqlite3-rdiff>"delta" database has
only differences beetween two databases.