SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[10.0.172 Pro] - Schema Compare issues

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[10.0.172 Pro] - Schema Compare issues
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post [10.0.172 Pro] - Schema Compare issues Reply with quote
I've noticed a couple of issues with the schema comparison feature (see in attached screenshots).



At the first step of schema comparison, I've set names of the constraints to be ignored. This will be important later.






The results show that the Adatblokk table is different because it has different names for its primary key on the left and the right side (and different columns and indexes, but let's just disregard those now). As the names are set to be ignored, I'd expect the difference at #2 to be ignored as well, regardless of there being another difference between the two constraints: left side shows that though the constraint name looks suspiciously as system name, it is not. I guess it is still shown because IsSystem differs on left and right. But even if that's so, that line lacks color highlighting for different objects (I forgot to mark that on the screenshot, I'm sorry). The blue color highlight is also removed when the line is selected (#1)






The third screenshot also shows multiple anomalies:
At #1 the column FI is not highlighted in blue, although there are differences in its definition. Its collate property mismatches (#2), which is the only difference there, but it isn't highlighted either.
At #3 there are several properties that are displayed, even though there are no differences between those, and the Identical objects filter in the Filter pane is turned off.

Now, for #3 one might argue that filter is for objects only (its name tells so), not for properties. However, I'd say that only removing the different objects and leaving tons of not differing properties is much less helpful than removing those as well, so I'd recommend either putting them to the rest too or introducing another checkbox for hiding the identical properties. The less unimportant stuff the eyes have to skip, the better.
Tue Sep 18, 2018 7:44 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Thank you very much for the extensive details. I will need to share this with the team.

One thing I can see right away and I'm not sure it's a bug, the primary key isn't really matched. What I see that the right side a table that has been likely created using a script like
CREATE TABLE A (id INTEGER PRIMARY KEY, .... -- here the primary key constraint name is system generated
and the other has been created using previously reverse-engineered DDL script like

CREATE TABLE B (id INTEGER, .... CONSTRAINT PK_something_bunch_of_numbers PRIMARY KEY (id), ... -- here the primary key constrain name is user defined

So beyond column names which are used for constraint matching in case the "Name" matching is unchecked, it found something else different and that's why it treated these constrains as different even though their columns are the same.
Tue Sep 18, 2018 8:47 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Could you please tell us if in the Schema Comparison options you have/had the "Collate" attribute selected "Not for Comparison"
FYI, This option is used for secondary attributes that are retrieved for reverse-engineering the original DDL, but not compared by default



Tue Sep 18, 2018 9:54 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
By the way, after use of the BETA version and installing the retail version it's a good idea to reload default options.
Tue Sep 18, 2018 9:55 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
SysOp wrote:

One thing I can see right away and I'm not sure it's a bug, the primary key isn't really matched. What I see that the right side a table that has been likely created using a script like
CREATE TABLE A (id INTEGER PRIMARY KEY, .... -- here the primary key constraint name is system generated
and the other has been created using previously reverse-engineered DDL script like

CREATE TABLE B (id INTEGER, .... CONSTRAINT PK_something_bunch_of_numbers PRIMARY KEY (id), ... -- here the primary key constrain name is user defined

So beyond column names which are used for constraint matching in case the "Name" matching is unchecked, it found something else different and that's why it treated these constrains as different even though their columns are the same.


Yes, that's exactly what had to happen there, I'm sure. The mismatching IsSystem properties for the two side also point in that direction. I guess someone (whose name surely doesn't match mine, khm, khm) had created the primary key without giving it a name and then later that database structure survived several export/import steps until it got to that other server.

That being a bug or not depends on how "ignore names" is interpreted here.

If it means "okay, there might be differences, but hey, who cares about this one", then it's a bug. Properly working this would leave the constrain name alone, and only sync the rest of the differences.
But if it means, it won't whisper a sigh about it in case that's the only thing that differs, then it's not a bug and works as intended. However, this approach has the side-effect of changing the name of the constraint if there are any other differences. Now, I'm thinking about if there are any reasons one could have to keep the constraint name but nothing comes to my mind. Other than the ultimate truth that one should never trust the system naming objects. That's (one) root of all evil. Well, sort of. It's definitely a nightmare, maintenance-wise.

Parsing the help I found this below. Regarding that entry, I consider this one not to be a bug. After all, the IsSystem property did not match. With that said, the differences weren't highlighted, and I still think that's a bug. A minor one. A flea, I mite say.

Quote:

Ignore options
In the Ignore box you can choose generalized options that you want ignored for all applicable schema objects
during their comparison. The following options are supported:
.
.
.
Names (Constraints, Indexes) – ignore names of constraints and indexes. Compare their columns and
attributes only. This option is helpful when working with system generated constraint and index names that
vary in every schema.

Tue Sep 18, 2018 10:18 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
SysOp wrote:
Could you please tell us if in the Schema Comparison options you have/had the "Collate" attribute selected "Not for Comparison"
FYI, This option is used for secondary attributes that are retrieved for reverse-engineering the original DDL, but not compared by default


No, that setting wasn't select "Not for Comparison".


SysOp wrote:
By the way, after use of the BETA version and installing the retail version it's a good idea to reload default options.

Yes, it definitely is. Though I tend to avoid that like fire because I've tons of customizations that I have to rebuild from scratch after that.
Tue Sep 18, 2018 10:38 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Hi,

This issue seems to be caused by incorrect installation /configuration setup (on our part) which has an undesired side effect on processing of "Not for Comparison" settings in the schema compare rules. We are going to release a new build soon with a fix for that issue. The fix has been already made and it's being tested internally.
Wed Sep 19, 2018 11:02 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Great news, thanks! Looking forward to it!
Wed Sep 19, 2018 4:00 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.