The Basics of Qualitative Schema Comparison

Contents

• Convenient Setting Up of Comparison Parameters
• Easy Handling of Schema Differences
• Safe Schemas Synchronization
• Summary

Schema comparison plays an important role in databases development and management. But comparison has its own rocks, each of them requires a separate article to discuss. However, we would like to focus not on the variety of difficulties, but on how to make schema comparison smooth and ensure the best comparison results.

This article describes the basics of qualitative schema comparison and examines the functionality of Schema Comparison, a professional tool that automates MySQL schema comparison and synchronization. It is integrated into dbForge Studio for MySQL and provided as part of dbForge Fusion for MySQL.

Convenient Setting Up of Comparison Parameters

Each schema comparison begins with setting parameters: finding required source and target schemas, selecting connections, databases, etc. Schema Comparison wizard replaces manual setting of parameters and significantly saves your time. You can quickly select comparison settings to compare either two schemas or a schema and a project.

A project is a set of SQL files, united into one logical structure, which are used to create a new schema or to add new objects to the existing schemas. (You can read about working with projects in the article " Database Projects - Modern Approach in Database Development".)

The wizard is visually divided into left part (it's for source schema setting) and right part (it's for target schema settings). This design will help you to choose schemas and never mix their settings.

To compare two schemas, specify the following:

• Comparison Type - (Click the Type fields on the left and right parts of the wizard and select Database from the drop-down lists. If no project is open at the moment, the Type fields are hidden.)
• Connection - (Open the drop-down lists of the Connection fields and select the required connections. Also you can edit them or create new ones pressing the corresponding buttons on the wizard.)
• Database - (The Database fields provide the lists of databases related to the selected connection.)

You can use the quickest way to compare two schemas: select two schemas in Database Explorer (a convenient tool for enhanced work with databases), pressing the Ctrl key, and choose the New Schema Comparison option from the right-click menu. Schema Comparison wizard opens with all schema comparison parameters already selected.

To compare a schema and a project:

Check that the project, which should participate in comparison, is open at the moment.

Set the schema parameters as was previously described either in the left or right part of the wizard, then on the opposite part select the Project type and choose the existing project from the drop-down list.

When all parameters are specified, you can change your mind and swap the source and target schemas pressing the button in the middle of the wizard. Click OK to close the wizard window and start the comparison.

Schema comparison is asynchronous. Usually, comparing large amount of data can take some time, so to do any other tasks, you should wait till the process is finished. Asynchronous synchronization does not prevent you from doing other tasks. If any errors occur during comparison, error notifications are shown. You can either stop or retry comparison, or ignore the errors. When the comparison is finished, you can see the results in the SchemaComparison window, which is automatically opens to provide essential functionality for easy management of schema differences.

You can additionally save your time while setting up schema comparison next time. Save the opened SchemaComparison document as a file. It will contain source and target schema settings, skipped objects, and filtering. You will enjoy using this option, because it helps to escape some monotonous work.

Easy Handling of Schema Differences

Nowadays, you can find a lot of new tools for schema comparison and stick to any of them. They all provide good comparison, but sometimes you fail to quickly analyze presented schema differences. Understanding and analysis of schema differences are even more important than a quick preparation to comparison and the process itself. The clearer schema differences are presented, the better and correspondingly quicker you understand what schema updates to apply. It's obvious, the quality of schema comparison and synchronization greatly depends on mere understanding of schema differences.

Getting schema differences and understanding them

The SchemaComparison window is specially designed to ease processing of schema differences. When the comparison is finished, this window automatically opens to show the comparison results.

Schema differences are neatly presented both in the grid, which is a good way for perception, and in SQL editors under the grid, which in detail show SQL differences of the compared schema objects. As well as in Schema Comparison wizard, the source schema name and connection are displayed on the left part of the window and the target schema name and connection are on the right.

The grid shows the comparison results in the following way:

• Source object name
• Status - the type of difference. Objects can have the following statuses:
  • Different - for objects with the same names, but different DDL.
  • Equal - for identical objects.
  • Only in Source - for objects existing only in the source schema.
  • Only in Target - for objects existing only in the target schema.
  • Inconsistent - for objects that are different, but a target object can not be created or updated due to incompatibility between the source and target MySQL servers. For example, if the source table contains a BIT column and the target MySQL server does not support BIT columns.
  • Unknown - for objects which metadata cannot be retrieved.
• Check box - it is used to include compared objects into synchronization or exclude them.
• Operation - the variant of the action you can apply to the current schema object.
• Target object name

By default, schema objects are grouped by status, so if you intend to update objects existing only in the Source database, for example, you will quickly find such objects in the comparison results.

For better view, you can also group objects by type and update operation. Also you can filter the list of objects by name or status. Enter a name of a required database object or just first few letters and see the object in the grid. It's convenient.

Analyzing SQL differences of the compared schema objects

You often have to analyze SQL differences of the compared schema objects and quickly update them. Any text comparer, which can be used through the command line, will solve this task, but Schema Comparison expands your choice. In the top menu Tools→Options→Schema Comparer→General you can select one of the following text comparers:

• Internal - If this option is selected, under the grid in the SchemaComparison window, two SQL editors will show DDL of the selected source and target schema objects. It is a convenient option, as you don't lose your time and are not distracted. You can see the object differences and SQL differences of the selected objects at once.
• Araxis Merge - Before selecting this option, check that Araxis Merge is installed on your PC.
• Custom - You can set and use any other text comparer, which can be used through the command line

You will intuitively find and analyze differences in DDL, as they are marked with three colors:

• light green is for lines existing in the source schema but missing in the target one
• light red - lines existing in the target schema but missing in the source one
• light blue - lines different in the source and target schemas

Corresponding blocks of text, but with any differences, are connected with lines. If required, you will copy text from the SQL editors and then edit or save it in a new SQL document.

Safe Schemas Synchronization

After a good overview of schema differences, the next important element of successful schema comparison is to accurately select what objects should be created, updated, dropped, or excluded from synchronization. This step causes many errors, so you should understand what to do.

The SchemaComparison window simplifies this task. You can see the possible operations in the Operations column. These operations are automatically selected based on the object status. If it's necessary, you can change them, selecting the required ones from the drop-down list.

The following operations are available:

• Skip - to exclude the object from synchronization. It can be applied to all objects.
• Update - for objects with Different status.
• Drop - for Only in Source and Only in Target objects.
• Create - for Only in Source objects.

To ensure the exact result after synchronization, preview the script for any compared object. Select the required object (or several ones) and click the View Schema Update Script button on the toolbar or select the same option from the right-click menu.

When all update operations are selected and the script is checked, you are ready to synchronize the schemas. Click the button on the toolbar to synchronize the schemas. As well as comparison, schema synchronization is asynchronous, i.e., the one, which does not prevent you from doing other tasks.

While synchronizing a project with a schema, the synchronization is performed like this:

• If Drop operation is selected for the object, the SQL file with the corresponding object is deleted from the project.
• If Create operation is selected, the SQL file with the corresponding object is added to the project.
• If Update operation is selected, DDL of the selected object is updated in the the SQL file(s).

The updates can be exported either to a file or to the Editor window. You will appreciate this option, because it gives you many benefits. For example, if synchronization can't be done due to the unavailability of a server where the target schema is located, you can apply the updates from the file later. In case, any errors occur during synchronization, you can open the synchronization script to find the errors and, after the errors are corrected, apply the updates. No confusion or extra efforts in the future. You can review the updates at any moment and have clear understanding of your work.

Summary

While comparing schemas, you can use various approaches and multiple tools. But whatever tools you've chosen, pay attention to the aforementioned stepping stones to have accurate comparison. Setting up of comparison parameters should be easy and quick. You must understand the presented schema differences at first glance and, moreover, have no confusion while analyzing how to update schema objects. The Schema Comparison tool meets these requirements and, as a result, ensures the qualitative comparison as well as satisfaction that you have successfully done your tasks.