New Features
Enhanced Features

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

  1. Create Relational Model
    1. Create a new Entity Relationship Diagram.
      create_erd.jpg

    2. Create a new Entity called Student
      NameTypeConstraint
      idvarchar(255)Primary Key
      namevarchar(255)
      ageinteger(10)
      gendervarchar(1)

    3. Create a new Entity called College
      NameTypeConstraint
      idvarchar(255)Primary Key
      namevarchar(255)
      addressvarchar(255)

    4. Create a one-to-many relationship from College to Student
      whole_erd.jpg

  2. Open the Generate SQL dialog box and configure the database connection
    1. 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.
        open_generate_sql_dialog.jpg

      • Right-click the relationship between entities, and select Generate SQL... from the pop-up menu.
        select_relationship_open.jpg

      • 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.
        open_generate_dialog_erd.jpg

    2. If the database connection was not configured before generating the SQL, you will be prompted to configure the databases for SQL generation.
      no_db_dialog.jpg

    3. Click Yes to open the Database Configuration dialog box.
      db_config_no_conf.jpg

    4. 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.
      db_config_configed.jpg

  3. 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.
      ddl_tab.jpg

    • 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.
      dml_tab.jpg

  4. 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.
    expanded_block.jpg

    collapse_block.jpg

    You can modify the database settings by clicking on the Database Configuration button on the Generate SQL dialog box.
    db_config_bt.jpg

  5. You can select other database from the Database SQL statements based on the syntax of the selected database.
    select_support_db.jpg

  6. Now you can click the Execute button for the Create statments to create tables, Student and College to the connected MySQL database.
    create_tables.jpg

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.

  1. Let’s add a new column called address in the Student entity.
    modify_student_table.jpg

  2. Open the Generate SQL and expand the Alter statement block.
  3. 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.
    alter_warning.jpg

  4. Click OK, the Alter statement is created and shown on the editor.
    created_alter_sql.jpg

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.
syntax_highlight.jpg

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.

  1. Click Save button.
    save_button.jpg

  2. Enter an output path and file name for the file.
    save_dialog.jpg

  3. Let’s open the saved file, the Select statements are exported.
    create_ddl_file.jpg

Copy

You can copy the SQL statement to clipboard such that you can modify and use the SQL statements on the database directly.

  1. Click Copy button to copy the selected SQL statement.
    copy_button.jpg

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

  1. Click Revert button to revert the modified SQL statement.
    revert_button.jpg

  2. The SQL statement is reverted to the original generated SQL statement.
    syntax_highlight.jpg

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.
execute.jpg

Downloads

Related Articles

Resources

 
 
Last modified: 2006/03/30 09:26
 
 
Home | Recent Topics | Highlights | UML Diagrams | Tips and Tricks | Object-Relational Mapping
visual-paradigm.com Home | Training Center | UML Center | VP Gallery | Discussion Forum | UML Open Directory