Case-sensitive comparison makes the repository messy

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

Case-sensitive comparison makes the repository messy

Postby isme » Fri Oct 18, 2013 8:08 pm

I'm in favor of case-sensitive comparison as a principle.

SQL Compare's implementation causes some issues, though.

Enabling Case Sensitivity

Image

Case sensitivity is enabled through the 'Use case sensitive object definition' project option.

The dialog says:

When this option is on SQL Compare will perform case sensitive text comparisons on objects. For example, object names such as ATable and atable will be considered to be different.

You should use this option only if you have databases with binary or case-sensitive sort order.


Consistency is another good reason to use this option. Inconsistency is distracting.

Comparing the schemas

Image

In my database I have an object called 'Config.DeriveDataCentre'. In my repository I have an object called 'config.DeriveDataCentre'. The object bodies are identical.

With case sensitivity enabled, SQL Compare shows that each schema has an object that the other lacks.

To synchronize the schema, SQL Compare wants to drop 'config.DeriveDataCentre' and create 'Config.DeriveDataCentre'.

Writing the changes

Image

SQL Compare names each file after the object definition it contains.

SQL Compare copies these actions to the clipboard:

Modify fileRedGateDatabaseInfo.xml
Modify fileFunctions\\Config.DeriveDataCentre.sql
Create file Functions\\Config.DeriveDataCentre1.sql


I asked it to sync one object, so why are there three changes?

Perhaps Windows' case-insensitivity confuses SQL Compare. Windows always sees 'Config' and 'config' as equal strings.

Instead of just modifying the existing file, it modifies it and creates a new file called Config.DeriveDataCentre1.sql.

Inspecting the output

Image

Inspect the script folder in Explorer with the TortoiseSVN shell overlay to confirm the changes.

The red spot shows that SQL Compare has modified Config.DeriveDataCentre.sql.

The plain icon shows that SQL Compare has created the unversioned file Config.DeriveDataCentre1.sql.

Diffing the new files

Image

Use WinMerge to compare the two files.

Config.DeriveDataCentre1.sql contains the correct definition.

Config.DeriveDataCentre.sql is empty!

Repeating the comparison

Image

Thankfully, SQL Compare does not rely on the file name to work out what definition each file contains. Instead, it parses each file to build an object model.

It sees the definition in the unversioned file and decides that the objects in each schema are now equal.

However, if you naively commit this change, you'll cause issues for your fellow developers.

We expect the file name to match the object name. Adding '1' to the end looks sloppy.

Zero-length files are ignored by SQL Compare, and do nothing else but clutter the script folder.
isme
 
Posts: 81
Joined: Tue Jun 12, 2012 1:49 pm
Location: Edinburgh

Postby Brian Donahue » Tue Oct 22, 2013 9:30 am

Thanks for pointing this out. I did some research and apparently this behavior is intentional. I can't say if it's correct because I have very little practical experience with source control systems and I can't discuss with you whether or not this is the way it should work, but here is the comment one of the developers left the last time someone ran into this issue (SC-3645)...

The reason is source control, which is the prime scenario for scripts. If we delete a file, then the source control will just think that the file is missing, and will not delete it from source control when the changes are checked in. When the latest version are retrieved, the source control would just re add the object (as the file has been deleted), so corrupting the database this way.


If this is not true (or not true for your particular VCS, as we have to support several of them), please let me know the reasoning so I can re-open the issue.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Compare 10

Who is online

Users browsing this forum: Bing [Bot] and 1 guest