Working with Project

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. Right-click the project node in Project Explorer and choose Add Folder on the shortcut menu.
  • or -

    Select the project node in Project Explorer and choose Add Folder on the Project menu.

  1. Enter the new folder name (Security).
  2. Repeat steps 1 - 3 for creating Database objects and Data folders.
  3. Right-click the Database objects folder node in Project Explorer.
  4. Select Add Folder on the shortcut menu.
  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 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.

Adding Scripts to the Project

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:

  1. Click the New SQL button on the Standard toolbar.
  2. 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) 
     )
    
  3. Click the Save button on the Standard toolbar.
  4. Choose file location and enter file name (for example, Table Dept) in the Save As dialog box that appeared.
  5. Save the file.
  6. 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.

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

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 into**, and then click **Project **. Perform the following actions to do this:

  1. Click the New SQL button on the Standard toolbar.
  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. Click the Save button on the Standard toolbar.
  4. Choose file location and enter file name (for example, Table Emp) in the Save As dialog box that appeared.
  5. Save the file.
  6. On the File menu, select Move Table emp.sql into Project, and then click New Project1.
  7. Drag the file shortcut node in Project Explorer to the Tables folder node.

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.

  1. 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.

  2. Select the View item in the Templates box and click Add.
  3. Replace the view name Test.VIEW1 with staff in the SQL document that appeared.
  4. 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
    
  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 project, save the project and all unsaved files for future use. To do this, perform the following actions:

  1. 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.

  2. Type the project name Demobase project and choose 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 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.

  1. Set Target database name to demobase.
  2. Specify your MySQL Server version.
  3. Click Always re-create database.

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.

  • 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 buttons or drag-n-drop to change build order.

Creating Project Configuration

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.

  1. Select Configuration Manager on the Configuration drop-down list on the Project toolbar.
  2. Click the Add button.
  3. Enter the configuration name (OldServer).
  4. Select Default in the Copy settings from drop-down list.
  5. Click OK to add the configuration.
  6. Click OK to close the Configuration Manager.

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.

Building and Deploying Project

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.

  • Right-click on the project in Project Explorer and select Assign connection on the shortcut menu, then select required connection from the list and click OK.
  • Right-click on the connection in Database Explorer and select Assign to project on the shortcut menu.
  • Drag the connection from Database Explorer to Project Explorer.

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.