I realise this would be a huge project, but might it be possible to create some sort of "DIFF ENGINE" type product which could compare EDB Databases in the same way Beyond Compare compares Code?

i.e. you would point it at 2 tables & it would show a "diff diagram" with (say) records present in T1 not in T2, and vice-versa marked with "+" and "-", also rows which differed could be highlighted etc.

The tables would have to share structures and a common primary-key structure (I think).

Now that I have multiple instances of the "same" DB with replication there are times when this sort of tool would be really useful.

On 4/7/2014 12:28 PM, Adam Brett wrote:
> I realise this would be a huge project, but might it be possible to create some sort of "DIFF ENGINE" type product which could compare EDB Databases in the same way Beyond Compare compares Code?
>
> i.e. you would point it at 2 tables & it would show a "diff diagram" with (say) records present in T1 not in T2, and vice-versa marked with "+" and "-", also rows which differed could be highlighted etc.

Why not generate create SQL and then use Beyond Compare on that ?

Raul

<Adam Brett> wrote in message
news:F0A89241-0EE0-4A23-B2B9-74B2E74CF248@news.elevatesoft.com...
>I realise this would be a huge project, but might it be possible to create
>some sort of "DIFF ENGINE" type product which could compare EDB Databases
>in the same way Beyond Compare compares Code?
>
> i.e. you would point it at 2 tables & it would show a "diff diagram" with
> (say) records present in T1 not in T2, and vice-versa marked with "+" and
> "-", also rows which differed could be highlighted etc.
>
> The tables would have to share structures and a common primary-key
> structure (I think).
>
> Now that I have multiple instances of the "same" DB with replication there
> are times when this sort of tool would be really useful.
>

Hi Adam

I know it is not the same as Beyond Compare, but won't this do what you
want?

8.8 COMPARE DATABASE

Compares a source database to a target database and generates the
differences as SQL statements.

Syntax

COMPARE DATABASE <SourceDatabaseName>
TO <TargetDatabaseName>
[STATEMENT TERM CHAR <TermChar>]


Cheers

Jeff
--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

Yes Jeff,

For structural comparisons of 2 DBs the new features of COMPARE are brilliant.

I am actually talking about a row-by-row comparison. I have multiple users with their own separate DBs, these are then synchronized using EDB's replication (UPDATE) processes.

The issue is when 1 user gets out of sync it is really hard to know what records they have added which are not present in the main database.

Adam

>I am actually talking about a row-by-row comparison. I have multiple users with their own separate DBs, these are then synchronized using EDB's replication (UPDATE) processes.
>
>The issue is when 1 user gets out of sync it is really hard to know what records they have added which are not present in the main database.

If the two tables have the same structure and have unique primary keys (or other keys) that are common to the two tables then once they can be addressed in the same session a simple bit of SQL should do it

Otherwise it gets more complex.

Roy

Thanks Roy

>>If the two tables have the same structure and have unique primary keys
>>(or other keys) that are common to the two tables then once they can be
>>addressed in the same session a simple bit of SQL should do it

Yes this would be a really useful start, I am considering writing something myself, just a simple grid-type app with a UNION clause, which could then iterate the Grid do a bit of logic to compute similarities & differences,

i.e.

Return all records from both tables
If Record is identical in DB1 and DB2 just list it once with no highlighting.
If Record is present in DB1 but not DB2 make that row GREEN.
If Record is present in DB2 but not DB1 make that row RED.
If the 2 rows are not identical return BOTH and mark them BLUE.

App could also provide a summary along the lines of:
DB1 table contains XXX rows.
DB2 table contains YYY rows.
ZZ rows in DB1 are not present in DB2
XX rows in DB2 are not present in DB1.
YY rows are present in both, but contain differing data.

This would be a useful start, I'm sure there are then loads of other features which could be added.

--

The thing is usually when I think about doing something like this I put some effort in & then find that someone else has already built something 100 times better

If such a product already exists I'd rather just use it, even if I have to pay a bit for it!

Adam


Hmmm. I think you'd already spotted this but just to reiterate The first constraint is that the table structures would need to be identical. Either what you do

would need to reflect the primary key for existence / non existence and the primary key(s) would need to be shared between the tables and unique (eg you could not have two separate autoinc series)

or

would need to ignore the existence of the primary keys and just compare the record data and that data would have to be guaranteed unique.

The latter concept could be very dangerous if actions were based on its results. The former case is relatively trivial as long as you use a proper programming language and environment (ie don't try and do everything in SQL).

Roughly what I'd do is

1. Create two temporary tables using CREATE AS, add an index for the primary key
2. Add flag column(s) - probably get away with one  - Flags for same, does not exist, different, exists - these would be used in a dbgrid to set row colours
3. Populate the primary keys for both tables from the combined set of primary keys for both table (INSERT UNION of primary key)
4. Set the status flag to does not exist
5. Populate the tables individually (SELECT FROM table WHERE primary key IN (SELECT primary key FROM table) and set the status flag to exists as part of the UPDATE
6. Now switch to Delphi and run through the tables comparing fields - its easy because the two tables now have exactly the same rows even if the content is different
7. Display in grid

Roy Lambert

Good plan Roy ... I am waist deep in a development process at the moment, but as soon as I have time I will try to do it.

My only addition was to perhaps do a column-match on the tables first so in the case tables without exactly matched columns you could at least do a compare of the shared fields.

As I _always_ use a unique Primary Key I would use this as the first "match" to check duplication between tables.

I would probably do a lot of the work in Delphi to be honest. I know it can be done in EDB SQL, but I would feel more comfortable with a couple of EDBQueries, iterating these & doing comparisons.

I still wait in hope that someone else will chime in on the Group and say there is already a product that does roughly what I am discussing, to save myself the work

Adam


If there are a few people interested I might just waste some of my time and do one.

Roy Lambert

Adam Brett wrote on Thu, 10 Apr 2014 10:18:38 -0400

>Good plan Roy ... I am waist deep in a development process at the moment, but as soon as I have time I will try to do it.
>
>My only addition was to perhaps do a column-match on the tables first so in the case tables without exactly matched columns you could at least do a compare of the shared fields.
>
>As I _always_ use a unique Primary Key I would use this as the first "match" to check duplication between tables.
>
>I would probably do a lot of the work in Delphi to be honest. I know it can be done in EDB SQL, but I would feel more comfortable with a couple of EDBQueries, iterating these & doing comparisons.
>
>I still wait in hope that someone else will chime in on the Group and say there is already a product that does roughly what I am discussing, to save myself the work
>

Hi Adam

How about using EXPORT TABLE to get two CSV files and use BeyondCompare on
them?

Difficulties with blobby fields.

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz