Toad World® Forums

Schema Compare Pain-Point


#1

One item that several of my developers have mentioned as being in the “don’t like” category for TOAD schema and data compares is the following:
Dependencies are not handled gracefully.

A schema compare scenario:
View1 references view2. View1 is updated, and so is view2. I do a schema compare. View1, being earlier alphabetically, gets handled first. It fails, as view2 has not yet been updated. It seems like this could be handled more gracefully. The same is true for FK relationships added into tables.

Table1 has a new FK relationship added to it. It references Table2. Table2 is created at the same time. I do a schema compare. Table1 is brought over first, and the FK relationship fails, as Table2 doesn’t exist yet.

A data compare scenario:
Table1 has a FK relationship to Table2 (a lookup table, for instance). Table2 has some new data entered into it, and then some dependent values are entered into Table1. I do a data compare on the two tables. Table1’s values are put into the script ahead of Table2’s values, even though Table1 is dependent upon Table2 (via the FK relationship). It seems like the order of dependency for this scenario should be fairly easy to determine - which table has the FK relationship? More complex relationships might take more effort, but your digramming tool seems able to figure all the dependencies out just fine, so it might not be too bad. This work may already be pretty much done.

It would be great if v6 could handle these dependency scenarios more gracefully.


#2

Hello,

Can you clarify, you’re talking about how to gracefully display these relationships? Or comparing objects is incorrect because of dependencies?

regards, Julia.


#3

Julia,
Hmm. I guess niether one. The compare is correct. It displays the relationships correctly. The resultant synchronization script does not, however, take the relationships into account.

Example:

FruitTypes

Apple
Orange

FruitColors

Red
Orange

Fruit

FK to FruitType
FK to FruitColor

I add in a new FruitType of Banana, a new FruitColor of Yellow, and a new Fruit of Banana, Yellow.

I do a data compare.

The script that is generated will be similar to this:
Insert Into Fruit (Banana, Yellow)
Insert Into FruitColors (Yellow)
Insert Into FruitTypes (Banana)

The script will fail, because table Fruit is dependent upon the values in FruitColors and FruitTypes. Despite this, TOAD still tries to insert into Fruit first!

TOAD is smart enough to know the dependencies, so it SHOULD generate the following script:
Insert Into FruitColors (Yellow)
Insert Into FruitTypes (Banana)
Insert Into Fruit (Banana, Yellow)

If it does the inserts in this manner, then we won’t have the dependency problem mentioned above. As it works right now, we have to manually re-arrange the scripts or run the tables in one at a time in the proper order. Either that, or we watch the scripts fail.

Does this help to clarify the issue?


#4

Okay, I see what the issue is. Thank you for your wonderful examples :slight_smile:

Actually Data Compare has the special option to drop\re-create FKs for tables that are synchronized. You can use it for your cases. But I think you talk about Data Compare should automatically take into account existing dependencies. Am I right?

Also note that in the TSS 6.0 we now don’t remove FKs but disable them through the ALTER TABLE [table] NOCHECK CONSTRAINT [constraint] script. You can try the last beta drop.

Julia.

Message was edited by: Julia Ponomareva


#5

What about Schema Compare I tried your case with views and actually it works ok on my side.

I have V1 references V2 (V1 is created as SELECT col FROM V2 and V2 as SELECT col from table). I change the column in V2, so it affect V1. Then I do a schema compare.

Now I get this script:

ALTER VIEW [V1]
AS
SELECT CAST(NULL AS char(10)) AS [col]
GO

ALTER VIEW [V2]
AS
SELECT CAST(NULL AS char(10)) AS [col]
GO

ALTER VIEW [V2]
AS
select [col] from [table]
GO

ALTER VIEW [V1]
AS
select [col] from [V2]
GO

As you can see part#1 makes a stub for V1 and V2 and then we generate correct script in part#2. See my screenshot for more details.

Is this what you expect?

regards, Julia.
IMG2.png


#6

But I think you talk about Data Compare should automatically take into account existing dependencies. Am I right?

Yes. We have had problems with FK’s disappearing when that option is selected, so we usually don’t use it.

In any case, it seems like TOAD should be able to add the data into the DB in the correct order pretty easily.


#7

Julia,
You are starting from an existing table with your views. Try synching to a DB that doesn’t yet have those tables.


#8

Regarding Schema compare.
Probably we mislead your scenario, but we still do not see issue here. I just run SC against test db with such objects vs. db without Fruit tables :slight_smile: I selected to sync only two views - all other objects (schema and three tables) were selected automatically by include dependencies option.
I got valid script (I cleaned it from SC formatting, error checks and other stuff just to fit in one screen). See screen attached

As you can see SC generate create script for views-stubs, then for tables, then alter views to have valid sql.
I’m double checking with dev on the first part (why it creates stubs in this simple case) but assume it is outside of discussed question.

Regarding Data Compare.
For right now Data Compare doesn’t take into account data order. You can use Drop/Create FK option. Please note - in 6.0 this option doesn’t drop FK! It just disable FK for a time of execution and enables it at the end. Does it work for you or you can’t use this option for any reason?


#9

Alexander,
Unfortunately, I do not have any great schema compare examples to give you.

As for the data compare, I will test the disabling of the FK constraints and see how that works.

Thanks!