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, select New on the File menu, and then click Project. In the New Project dialog box, double-click Devart Database Projects, then select MySQL Project in the Templates box, and click OK. A project will appear in Solution 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 the Solution Explorer and choose Add New Solution Folder from the Project menu.
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.
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 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)
)
or -
Click on the Tables folder in the Solution Explorer and choose Add Existing Item on the Project menu.
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
Type or paste 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 the Solution Explorer and select Add New File on the shortcut menu
or
Click the Views folder node in the Solution Explorer and select Add New Item 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:
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);
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');
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)
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)
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)
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 it, perform the following actions:
Let’s set our project options for building and deploying project. To set project options, right-click the project in the Solution Explorer and select Properties on the shortcut menu or select 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 the 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 Solution toolbar), select OldServer from the Configuration drop-down list. Specify the Server version on the Database tab and clear checkboxes 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 Build 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 the Database Explorer to the Solution 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 Solution Explorer and selecting Find in Database Explorer on the shortcut menu.