Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Enhancement Requests and Suggestions » View Thread |
Messages 1 to 10 of 16 total |
Beyond Compare for EDB? |
Mon, Apr 7 2014 12:28 PM | Permanent Link |
Adam Brett Orixa Systems | 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. |
Mon, Apr 7 2014 12:45 PM | Permanent Link |
Raul Team Elevate | 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 |
Mon, Apr 7 2014 7:37 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | <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 |
Wed, Apr 9 2014 5:30 AM | Permanent Link |
Adam Brett Orixa Systems | 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. |
Wed, Apr 9 2014 6:18 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Apr 10 2014 3:00 AM | Permanent Link |
Adam Brett Orixa Systems | 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! |
Thu, Apr 10 2014 5:23 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Apr 10 2014 10:18 AM | Permanent Link |
Adam Brett Orixa Systems | 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 |
Thu, Apr 10 2014 11:24 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 > |
Thu, Apr 10 2014 5:39 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |