Comparing Schema and Project

Last modified: October 18, 2023

The following walkthrough describes how to edit project and how to synchronize a schema and a project. A project from the walkthrough will be used there.

Prerequisites

You should have the following privileges:

• CREATE privilege on the demobase database
• DROP privilege on the demobase database
• ALTER privilege on the demobase database
• SELECT global privilege

To complete this walkthrough, you must have completed the walkthrough.

Updating project

In the previous walkthrough the project Demobase was created and deployed. Now you will add stored procedures to it. For this:

1. Right-click the Database objects folder node in Project Explorer.
2. Select Add Folder on the shortcut menu.
3. Enter the new folder name (Procedures).
4. Click the New SQL button on the Standard toolbar.

5. Type or paste the following code into the SQL document:

    CREATE PROCEDURE demobase.addemp(IN EmpName CHAR(20), IN Salary INT)
    BEGIN 
    DECLARE e_No   INT;
    DECLARE e_Name CHAR (20) DEFAULT 'Unnamed';
    DECLARE e_Sal  INT DEFAULT 1100;
    IF EmpName IS NOT NULL THEN
    SET e_Name = EmpName;
    END IF;
    IF Salary IS NOT NULL THEN
    SET e_Sal = Salary;
    END IF;
    SELECT
    MAX(empno)
    INTO
    e_No
    FROM
    demobase.emp;
    INSERT INTO demobase.emp (empno, ename, sal, hiredate)
    VALUES
    (e_no + 10, e_name, e_sal, CURDATE());
    END
   

6. Click the Save button on the Standard toolbar.
7. Choose file location and enter file name (for example, Procedure Addemp) in the appeared Save As dialog box.
8. Save the file.

9. Right-click the Procedures folder in Project Explorer and choose Add Existing File on the shortcut menu

   or

   Click on the Procedures folder in Project Explorer and choose Add Existing File on the Project menu.

10. Choose the created file in the Open dialog box.

11. Repeat steps 1-7 for the RaiseSalary procedure.

     CREATE PROCEDURE demobase.RaiseSalary(IN department VARCHAR(14), IN rate FLOAT)
     COMMENT 'Raise salary of all employee working in this department'
     BEGIN 
     DECLARE  d_No INT DEFAULT 0;
     SELECT DeptNo INTO d_No FROM Dept WHERE Dname=Department;
     UPDATE Emp SET Sal=Sal*Rate WHERE DeptNo=d_No;
     END
    

Suppose that you don’t need to store sum of the employee salary for the departments. Delete salsum column in the dept table and all triggers, updating it.

1. Expand the Tables folder in Project Explorer and double-click the Table Dept.sql file.
2. Delete the salsum FLOAT, line and save the document.
3. Click on the Triggers folder and press the DELETE key.
4. Click Yes in the appeared dialog box.

Comparing the project and the schema

Now, you’ll compare your project with the schema. Perform the following steps to create new schema comparison document.

1. Select New Schema Comparison on the Comparison menu.

   Click Project in source or in target, then choose the project in the field below.

2. Select connection for the compared schema. You can edit the selected connections or create a new one by clicking the Edit Connection or New Connection buttons.
3. Select the compared database demobase.
4. Click the OK button.

The schema comparison document will open. You can see comparison status of all schema and project options here. Table emp has Equal status. It has the synchronization operation Skip and will not be affected when synchronizing schema and project. Table dept has Different status. Click on it to see differences in the DDL. It has the Update synchronization operation and will be updated during synchronization.

View staff also has Different status. That’s because view select statement is stored in unformatted and single-line form. Views in project and in database will always have Different status, unless you use the same unformatted view form in the project. You don’t need to synchronize view now, click on its operation and choose Skip from the drop-down list.

Procedures have Only in Source status. They exist only in project and will be created during synchronization. Triggers exist only in target and will be dropped during synchronization.

Click the View Schema Update Script button on the Comparison toolbar to preview update script. To synchronize project, return back to the schema comparison document and click the Synchronize button on the Comparison toolbar. Select what you want to do with the synchronization script in the displayed Schema synchronization wizard. Click the Synchronize button to close the wizard and complete the synchronization. Review the synchronization script (if you selected to view it in the editor) and then execute it by clicking the Execute button on the toolbar or selecting SQL -> Execute on the top menu.