Compare Database Schemas
- DevaDarshini.S
- Rojer Benny CTOO
Using the Compare DB feature in SIGMA STUDIO you can compare and synchronize two database schemas across two different environments, such as testing or UAT. You can compare both the Data Definition Language (DDL) and Data Manipulation Language (DML) changes between the two schemas and download the queries that pertain to the differences between the schemas.
Refer the following topics:
Comparing database schemas
To compare two database schemas proceed with following steps:
Click Manage > Compare DB in Sigma Studio.
The Compare DB screen appears.
The Name field has an auto generated name by default.You can give a unique name in the Name field.
Choose either mysql or oracle as DB Type in which the connection is established, by default mysql is chosen as DB Type.
Choose the app in App Name field in which by default ModelHouse is chosen.
Select the database connection in Old Connection field to compare.
Select another database connection in New Connection field to compare with the connection in Old Connection field.
After choosing New Connection, the tables in New Connection are displayed.Hover mouse over the table name and choose filter option adjacent to the table name to compare particular value from the column.
Select a column from the drop down.
Enter the data from the chosen column for which you want to compare the data.
By clicking add icon you can add another column and data from the table.
Click the three dot icon and click delete icon to delete the added column and data.
Select the DDL (Data Definition Language) option to get the query for schema level differences in the two connections, e.g. changes in the table definitions.
Select the DML (Data Manipulation Language) option to get the query for data level differences in tables among the two connections, e.g. changes in the functional data in the tables.
You can choose the primary key for a specific table by clicking Primary / Business key column adjacent to each table and choose the column to compare the table.
You can also exclude unnecessary columns before comparison by clicking Columns Exclusion column and select the columns for exclusion in each table.
In common columns to exclude (DML) field by default some columns are already chosen.Select the columns from the drop down in the common columns to exclude (DML) field to exclude before comparison of all the tables in New Connection with Old Connection.
Click Save icon to save the DB comparison.
Click OK to the Success Message.
The saved Database comparison is listed in the Compare list.
Downloading Compared Query
To download the SQL query for the compared schemas, perform the following steps:
Click Download icon to download the query required for Old Connection.
Enter the existing schema name in Schema Name field for which the query needed to be inserted.
By default, Rollback option is enabled. The Rollback option provides the queries to retain the old status of the connection.Uncheck the Rollback option to disable rollback configuration.
Click Download to download the queries. The Zip file containing the SQL scripts for DDL and DML changes gets downloaded to your local Downloads folder.
You can execute the downloaded queries on the database to get it synchronized.Click OK to the success message.
Editing a Database Comparison
To edit existing database comparison, perform the following steps:
Click Manage > Compare DB in Sigma Studio.
The Compare DB screen appears.Click the saved database comparison in Compare list on the left to edit it.
Change any of the configurations, except the Name field of the selected DB comparison.
Click Save icon to save the changes.
Deleting a Database Comparison
To delete existing database comparison, perform the following steps:
Click Manage > Compare DB in Sigma Studio.
The Compare DB screen appears.Hover the mouse over the saved database comparison on the left and click the delete icon to delete it.
Copyright © Intellect Design Arena Limited.
All rights reserved. These materials are confidential and proprietary to Intellect Design Arena Limited and no part of these materials should be reproduced, published in any form by any means, electronic or mechanical including photocopy or any information storage or retrieval system nor should the materials be disclosed to third parties without the express written authorization of Intellect Design Arena Limited.