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.
You should have the following privileges:
To complete this walkthrough, you must have completed the walkthrough.
In the previous walkthrough the project Demobase was created and deployed. Now, you will add stored procedures to it. For this:
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
Note
Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. These elements are determined by your Visual Studio edition and settings. For more information, see Visual Studio Settings.
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
Now, suppose 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.
Now, you’ll compare your project with the schema. Perform the following steps to create new schema comparison document.
Select New Schema Comparison on the Tools -> dbForge Fusion for MySQL menu.
or
Click the Open Schema Comparison Wizard button on the Devart - Main toolbar.
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 Devart - Comparison toolbar to preview update script. To synchronize project, return back to the schema comparison document and click the Synchronize button on the Devart - 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.