Re: [sqlite] Query to compare two sqlite databases

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

"delta" database has
only differences beetween two databases.

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query to compare two sqlite databases

2010-09-25 Thread 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@web.de> wrote:

From: Oliver Peters <oliver@web.de>
Subject: Re: [sqlite] Query to compare two sqlite databases
To: sqlite-users@sqlite.org
Date: Friday, September 24, 2010, 2:54 PM

Oliver Peters <oliver@...> writes:

sry
 

> 
> the result is what is not in table01
> 

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

Oliver


___
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

Re: [sqlite] Query to compare two sqlite databases

2010-09-24 Thread Oliver Peters 
Oliver Peters  writes:

sry
 

> 
> the result is what is not in table01
> 

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

Oliver


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

Re: [sqlite] Query to compare two sqlite databases

2010-09-24 Thread Oliver Peters 
Am 24.09.2010 20:41, schrieb 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query to compare two sqlite databases

2010-09-24 Thread 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 <rshep...@appl-ecosys.com> wrote:

From: Rich Shepard <rshep...@appl-ecosys.com>
Subject: Re: [sqlite] Query to compare two sqlite databases
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Date: Friday, September 24, 2010, 12:51 PM

On Fri, 24 Sep 2010, luuk34 wrote:

> 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 );

   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
___
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

Re: [sqlite] Query to compare two sqlite databases

2010-09-24 Thread Rich Shepard 
On Fri, 24 Sep 2010, luuk34 wrote:

> 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 );

   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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query to compare two sqlite databases

2010-09-24 Thread luuk34 
  On 24-09-10 21:06, Rich Shepard wrote:
> On Fri, 24 Sep 2010, John Reed wrote:
>
>> 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
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.

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

Re: [sqlite] Query to compare two sqlite databases

2010-09-24 Thread Rich Shepard 
On Fri, 24 Sep 2010, John Reed wrote:

> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query to compare two sqlite databases

2010-09-24 Thread 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.





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