Working with Project

Last modified: July 9, 2021

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.

Prerequisites

In order to complete this walkthrough, you will need access to MySQL server 5.0.13 or higher.

You should have the following privileges:

• CREATE privilege on the demobase database
• DROP privilege on the demobase database
• INSERT privilege on the demobase database
• DELETE privilege on the demobase database
• UPDATE privilege on the demobase database
• SELECT global privilege
• CREATE USER global privilege
• GRANT OPTION

Creating Project

Creating Folder Structure

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.

1. In Project Explorer, right-click the project node and select Add Folder.

-or-

In Project Explorer, click the project node and select Add Folder on the Project menu.

2. Enter the new folder name (Security).

3. Repeat steps 1 - 3 for creating Database objects and Data folders.

4. In Project Explorer, right-click the Database objects folder node and select Add Folder.

5. Enter the new folder name (Tables).

6. Repeat steps 4 - 6 for creating Views and Triggers folders.

Now, you have a folder structure for the 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.

Adding Scripts to the Project

Now it’s necessary to create the 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.

To create files and add them to the project

1. On the Standard toolbar, click New SQL.

2. Type or copy/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) 
)

3. On the Standard toolbar, click Save to keep the changes.

4. In the Save As dialog box that appears, choose the file location, enter the file name (for example, Table Dept), and save the file.

5. In Project Explorer, right-click the Tables folder and select Add Existing File.

-or-

In Project Explorer, click on the Tables folder and select Add Existing File on the Project menu.

6. In the Open dialog box, choose the created file.

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 <file name> into, and then click Project <project name>.

To add an existing file

1. On the Standard toolbar, click New SQL.

2. 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) 
)

3. On the Standard toolbar, click Save.

4. In the Save As dialog box that appeared, select the file location, enter the file name (for example, Table Emp), and save the file.

5. On the File menu, select Move Table emp.sql into Project and then click New Project1.

6. In Project Explorer, drag the file shortcut node to the Tables folder node.

You can add not only existing files to the project but also create new files that belong to the project too. You can use file templates when adding a new file to the project.

To create a SQL file for creating the STAFF view

1. In Project Explorer, right-click the Views folder node and select Add New File.

-or-

In Project Explorer, click the Views folder node and select Add New File on the Project menu.

2. In the Templates box, select the View item and click Add.

3. In the SQL document that appears, replace the view name Test.VIEW1 with staff.

4. Type or copy/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

5. Save the document with the name View Staff.

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 the project, save the project and all unsaved files for future use.

To save the project and files

1. In Project Explorer, right-click the project node and select Save NewProject1.

-or-

In Project Explorer, select the project node and then select Save NewProject1 or Save NewProject1 As on the File menu.

2. Type the project name Demobase project and choose the project location.

3. Click OK to save the project.

Working with Project

Setting Project Options

Let’s set our project options for building and deploying the project.

To set project options

1. In Project Explorer, right-click the project and select Properties

-or-

On the Project menu, select Demobase project Properties.

2. On the Build tab, customize build results if needed.

3. Switch to the Build Configuration tab. When building the project, project scripts will be processed and placed in the result script according to the build order. The build order should be the following: at first, you create tables and triggers, then populate tables with data. After that, create a user and view.

So, the build order must be as follows:

• Table Dept.sql
• Table Emp.sql
• Trigger SalsumIns.sql
• Trigger SalsumDel.sql
• Trigger SalsumUpd.sql
• Dept data.sql
• Emp data.sql
• User Jones.sql
• View Staff.sql

Use Move Up and Move Down or drag-n-drop to change the build order.

4. On the Database tab, set the target database options:

• Set a target database name to demobase.
• Specify a MySQL Server version you are connected to.
• Select the Always re-create database option.

Creating project configuration

Suppose you should deploy the project on MySQL 5.0.13 or higher, and in some cases on the older version of MySQL, such as 4.0 or 4.1. Let’s use project configurations to build the project in two different variants of the result script. The default configuration will be used for MySQL 5 and OldServer configuration will be used for MySQL 4.

To create a configuration

1. On the Project toolbar, select Configuration Manager from the Configuration drop-down list.

2. In the Configuration Manager window that opens, click Add.

3. In the New Project Configuration dialog, enter the configuration name (OldServer) and select Default from the Copy settings drop-down list.

4. Click OK to add the configuration.

5. Click OK to close the Configuration Manager.

Now, you can set options for new project configuration. In the Project properties document, select OldServer from the Configuration drop-down list. On the Database tab, specify a server version and delete the view script and trigger scripts on the Build Configuration tab.

Building and deploying the project

Now, the project is ready to build. To start the build, click Build. The project will be built to the 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. In this case, you can use the existing connection or create a new one for the user account with administrative rights.

To assign a connection to a project, perform one of the following steps:

• In Project Explorer, right-click the project and select Assign Connection. In the Connection Manager, select the required connection from the list and click OK.
• in Database Explorer, right-click on the connection and select Assign to Project.
• Drag the connection from Database Explorer to Project Explorer.

After that, you can deploy the project. To deploy the project, on the Project menu, select Deploy [Project_Name] and click Yes in the dialog box that appears. You will see the deployment log in the General tab of the Output window. After deployment is finished, you can access demobase database and its objects.

After deploying the 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 in Database Explorer, right-click the object node and select Add to Project Demobase. Database object shortcuts can be used to open 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.