Generate SQL
DB Visual ARCHITECT (DB-VA) provides the Generate SQL function on the Entity Relationship Diagram (ERD). The Generate SQL function generates different kinds of SQL statements for entities and relationships. It not only generates SQL statement, but also allows you to modify the generated SQL statement and directly execute the statements on the connected database at once. The MySQL 5.0 database server is used for demonstration.
Generating SQL Statements
Create Relational Model
Create a new Entity Relationship Diagram.
Create a new Entity called
Student
| Name | Type | Constraint |
| id | varchar(255) | Primary Key |
| name | varchar(255) | |
| age | integer(10) | |
| gender | varchar(1) | |
Create a new Entity called
College
| Name | Type | Constraint |
| id | varchar(255) | Primary Key |
| name | varchar(255) | |
| address | varchar(255) | |
Create a one-to-many relationship from
College to
Student
Open the Generate SQL dialog box and configure the database connection
There are three ways to open the Generate SQL dialog box:
Select
Student and
College entity, right-click and select
Generate SQL... from the pop-up menu.
Right-click the relationship between entities, and select
Generate SQL... from the pop-up menu.
Right-click the Entity Relationship Diagram (without selecting any models), select
Generate SQL... from the pop-up menu. The
Generate SQL dialog box will show all SQL statements related to the selected models.
If the database connection was not configured before generating the SQL, you will be prompted to configure the databases for SQL generation.
Click
Yes to open the
Database Configuration dialog box.
Select the
MySQL as the default database, and you can also select any other database servers as the supported databases. Please make sure the correct information for database connection is entered so that you can generate and/or execute the
Alter statement to manipulate the database.
Click OK on Database Configuration dialog box to confirm the database configuration settings. The Generate SQL dialog box shows accordingly which shows the generated SQL statements with the default database (MySQL) in two categories:
The
Data definition language (
DDL) that can be used either interactively or within programming language source code to define databases and their components. The
DDL includes
Create,
Drop and
Alter statements.
The
Data manipulation language (
DML) that can be used either interactively or within programming language source code to access and retrieve data stored in a database management system. The
DML includes
Select,
Insert,
Update and
Delete statements.
For each SQL statement block has a triangle button on the left hand side, it allows you to expand or collapse the statement block. When the statement block is collapsed, only the first statement will be shown on the editor. If you click on the editor, the statement block expends once accordingly.
You can modify the database settings by clicking on the
Database Configuration button on the
Generate SQL dialog box.
You can select other database from the
Database SQL statements based on the syntax of the selected database.
Now you can click the
Execute button for the
Create statments to create tables, Student and College to the connected MySQL database.
Generating Alter Statements
The ALTER statement is used to change the table’s definition such as adding or removing an column or a constraint. The Generate SQL feature supports comparing the differences between the database and ERD and generating the alter statements according to the differences.
Let’s add a new column called
address in the
Student entity.
Open the Generate SQL and expand the Alter statement block.
Click the
Generate button, the
Warning dialog box shows alerting you for connecting the database to gather information from the existing database so as to generate the alter statements.
Click
OK, the Alter statement is created and shown on the editor.
Manipulating Generated SQL Statements
As a set of SQL statements are generated from the Generate SQL dialog box, you can edit, save, copy, revert and execute the SQL statements. The following section introduces how you can manipulate the generated SQL statements.
Edit
The SQL query supports a SQL syntax highlight (the reserved words are highlighted in red) to enhance the readability and ease the maintenance of SQL statements.
Example:
The Select statement with specified condition has a standard format:
SELECT TABLE <columns> FROM <table> WHERE <condition>
The reserved words are highlighted in red on the editor.
Save
You can save SQL statement as a file with a .sql extension. The sql file can act as a backup file and be executed for the database server.
Click
Save button.
Enter an output path and file name for the file.
Let’s open the saved file, the Select statements are exported.
Copy
You can copy the SQL statement to clipboard such that you can modify and use the SQL statements on the database directly.
Click
Copy button to copy the selected SQL statement.
You can paste the SQL statement anywhere you want.
Revert
After you modified the SQL statement, you can click the Revert button to restore the SQL statement to the original generated SQL statement.
Click
Revert button to revert the modified SQL statement.
The SQL statement is reverted to the original generated SQL statement.
Execute
You can execute the generated SQL statement in real-time and test the modified statements. By clicking the execute button, the Generate SQL executes the statements immediately and the result is shown on the bottom of the dialog box.
Downloads
Related Articles
Resources
