The following walkthrough describes how to create a sample project, add folders and files to it, deploy the project and export it to a single script. A sample project contains scripts, that create database objects, that were created manually in previous walkthroughs.
To create a new project, on the File menu, select New, and then click Blank Project. A project will appear in Project Explorer.
In order to complete this walkthrough, you will need access to MySQL server 5.0.13 or higher.
You should have the following privileges:
Folders help to organize related scripts and queries into groups, so, first of all, it’s necessary to create a folder structure for the new project. The root folder will contain three folders: Security, Database objects, Data. Folders for every database object type that is created in the project will be available in the Database objects folder. To create such folder structure, perform the following steps.
or -
Select the project node in Project Explorer and choose Add Folder on the Project menu.
Now, you have a folder structure for our project. Note that project folders are not real Windows folders, information about these folders is stored in a project file. When you create or delete folders, corresponding Windows folders are not created or deleted.
Now it’s necessary to create required SQL script files and add them to the project. You’ll learn how to add existing files to the project first. Existing files can be added to a project in two ways. Perform the following actions to create files and add them to the project:
Type or paste the following code into the SQL document:
CREATE TABLE dept (
DeptNo INT(11) NOT NULL default 0,
Dname VARCHAR(14)NOT NULL,
loc VARCHAR(13),
SALSUM FLOAT,
PRIMARY KEY (DeptNo)
)
Right-click on the Tables folder in Project Explorer and choose Add Existing File on the shortcut menu.
or
Click on the Tables folder in Project Explorer and choose Add Existing File on the Project menu.
Now, the file is added to the project. Another way to add an existing file to a project is to open it. Then, on the File menu, select **Move
Type or paste the following code into the SQL document:
CREATE TABLE emp (
EmpNo INT(11) NOT NULL default 0,
EName VARCHAR(10),
Job VARCHAR(9),
MGR INT(11),
HireDate DATETIME,
Sal FLOAT,
Commission FLOAT,
DeptNo INT(11),
PRIMARY KEY (EmpNo),
UNIQUE INDEX index1 USING BTREE (EmpNo)
)
You can add not only existing files to the project, but also create new files, that will belong to the project, too. You can use file templates when adding a new file to the project. Perform the following actions to create an SQL file for creating the view STAFF.
Right-click the Views folder node in Project Explorer and select Add New File on the shortcut menu.
or
Click the Views folder node in Project Explorer and select Add New File on the Project menu.
Type or paste the following query text after the AS keyword.
SELECT
emp.ename AS name,
emp.job,
emp.sal AS salary,
dept.dname AS department
FROM emp, dept
WHERE emp.deptno = dept.deptno
To complete the project, you should add some more scripts to it. Create and add the following scripts to the project in the same way as the previous ones:
Emp data to the Data folder
INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, 0, 20);
INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-15', 2975, 0, 20);
INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, 0, 30);
INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, 0, 10);
INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-08-19', 3000, 0, 20);
INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, 0, 10);
INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-07-13', 1100, 0, 20);
INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, 0, 30);
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, 0, 20);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, 0, 10);
Dept data to the Data folder
INSERT INTO dept(deptno, dname, loc) VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept(deptno, dname, loc) VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept(deptno, dname, loc) VALUES (30, 'SALES', 'CHICAGO');
Trigger SalsumIns to the Triggers folder
CREATE TRIGGER SalsumIns
AFTER INSERT
ON emp FOR EACH ROW
UPDATE DEPT
SET SALSUM = (
SELECT SUM(EMP.SAL)
FROM EMP
WHERE EMP.DEPTNO = DEPT.DEPTNO)
Trigger SalsumUpd to the Triggers folder
CREATE TRIGGER SalsumUpd
AFTER UPDATE
ON emp FOR EACH ROW
UPDATE DEPT
SET SALSUM = (
SELECT SUM(EMP.SAL)
FROM EMP
WHERE EMP.DEPTNO = DEPT.DEPTNO)
Trigger SalsumDel to the Triggers folder
CREATE TRIGGER SalsumDel
AFTER DELETE
ON emp FOR EACH ROW
UPDATE DEPT
SET SALSUM = (
SELECT SUM(EMP.SAL)
FROM EMP
WHERE EMP.DEPTNO = DEPT.DEPTNO)
User Jones to the Security folder
CREATE USER `Jones`@`localhost`;
GRANT SELECT ON * TO `Jones`@`localhost`;
GRANT INSERT, UPDATE(Sal) ON emp TO `Jones`@`localhost`;
After you added these scripts to project, save the project and all unsaved files for future use. To do this, perform the following actions:
Right-click the project node in Project Explorer and choose Save NewProject1 on the shortcut menu.
or
Select the project node in Project Explorer and select Save NewProject1 or Save NewProject1 As on the File menu.
Let’s set our project options for building and deploying project. To set project options, right-click the project in Project Explorer and select Properties on the shortcut menu or select Demobase project Properties on the Project menu.
On the Build tab, you can customize build results. In this walkthrough you don’t need to change anything on this tab. Click Database to set Target Database Options and perform the following actions.
Then switch to the Build order tab. When building project, project scripts will be processed and placed to result script according to build order. Build order should be the following: at first you should create tables, then triggers, then tables should be filled with data, after that user and view should be created.
So, the build order must be the following.
Use Move Up and Move Down buttons or drag-n-drop to change build order.
Suppose you should deploy the project on MySQL 5.0.13 or higher, and in some cases on older version of MySQL, such as 4.0 or 4.1. Let’s use project configurations to build project in two different variants of result script. Default configuration will be used for MySQL 5 and OldServer configuration will be used for MySQL 4.
To create a configuration perform the following steps.
Now, you can set options for new project configuration. In the Project properties document (not on the Project toolbar), select OldServer from the Configuration drop-down list. Specify Server version on the Database tab and clear check boxes for view script and trigger scripts on the Build order tab.
Now, our project is ready to build it. To start the build, click the Build button. The project will be built to result script which will be located by default in the default subfolder of the project folder.
When deploying the project, the build result script will be executed using the project connection. If the project does not have a connection, it can not be deployed. So, you should assign a connection to the project. It must be a connection to account of a user that have administrative rights, because deploying this project creates a database. So, create such a connection or use the existing one. To assign a connection to a project perform one of the following.
After performing this you can deploy project. To deploy the project select Deploy on the Project menu and click Yes in the appeared dialog box. You will see deployment log in the General tab of the Output window. After deployment is finished, you can access demobase database and its objects.
After deploying project you can add database object shortcuts to it. To add a database object shortcut drag the database object from the project connection in Database Explorer to Project Explorer or select Add to Project Demobase on the shortcut menu of the object node in Database Explorer. Database object shortcuts can be used for fast opening of object editors by double-clicking the shortcuts. You can also easily find the object in Database Explorer by right-clicking its shortcut in Project Explorer and selecting Find in Database Explorer on the shortcut menu.