Database Generation

The Database Generation tool can generate SQL script, that represents the diagram you developed and (optionally) executes it on the database server.

You can generate database in two ways:

In both cases, the database generation commands are saved in a script file. You must always provide path to the script file.

Database Generation

To generate database, start the Database Generation tool by using Generate Database ( ) item on Database tab of the Ribbon or pressing Ctrl+G. The following pictures demonstrate Database Generation tool interface.

Database Generation tool consist of several tabs, which contain SQL generation options. Let's explore them.

Database Options

In the Database Options tab of the Database Generation tool you can set database generation options.

Create Database
This option enables generation of the database. Set this option on if you want to create a new physical database instead of using an existing one. The table structure (i.e. tables) will be generated in both cases.

Begin Script
This option enables inserting the begin script before the CREATE DATABASE statement. You can edit database begin script in Database Editor.

End Script
This option enables inserting the end script after the CREATE DATABASE statement. You can edit database end script in Database Editor.

Notes as SQL Comment
This option enables showing of database comments in SQL script.

Drop Database
This option enables dropping old database if it was already exist.

Table Options

In the Table Options tab of the Database Generation tool you can set tables generation options.

Create Tables
This option enables generation of tables.

Begin Script
This option enables inserting the begin script (it can be set using Table Editor) before the CREATE TABLE statement.

End Script
This option enables inserting the end script (it can be set using Table Editor) after the CREATE TABLE statement.

Notes as SQL Comments
This option enables showing of table notes in SQL script, which was set in the Table Editor. Notes will be inserted to the SQL script as commented strings.

Create Primary Keys
This option enables generation of table primary keys.

Create Indexes
This option enables generation of indexes for the tables.

Create Table Options
This option enables setting of table options, such as ENGINE, ROW_FORMAT and others in the SQL script.

Create table triggers
Generate triggers set on table.

Generate table and column comments
This option adds COMMENT clause to column and table definitions if some comment set in Table Editor.

Create table partitions
Enable generation of partitions for tables.

Drop table triggers
This option generates DROP TRIGGER IF EXISTS statement before generating CREATE TRIGGER statement. There is no need to set this this option if Drop table option is set.

Drop table
This option enables dropping old tables if they were already exist. I.e. enables generating DROP TABLE IF EXISTS "<TABLE_NAME>" statement before CREATE TABLE statement.

Foreign Keys

Foreign Keys tab of Database Generation tool allows to set up foreign keys generation options.

Generate Foreign Keys
This option enables generation of table foreign keys presented as References on diagram.

FK Generation Method
This option defines foregn keys generation method:

Generate FKs inside table definitions.
All FKs are generated as a part of table definition inside CREATE TABLE statement. This method requires reordering of diagram tables, because some tables may depend on other ones. Sometime there can be cycle references between tables that can not be generated properly using this method. That is why second method of FKs generation is preferred.

Generate FKs after all tables are generated.
When using this method all FKs will be generated using ALTER TABLE ... ADD  CONSTRAINT statements. This method does not require tables reordering.

Views, Stored Routines, Events

Stored Routines Group

Create Stored Routine
This option enables generation of stored routines (i.e. stored functions and procedures).

Begin Script
This option enables inserting the begin script before the SQL generation statement.

End Script
This option enables inserting the end script after the SQL generation statement.

Comment
This option enables showing of stored routines comments in SQL script.

Drop Stored Routine
This option enables dropping old stored routines if it were already exist.

Views Group

Create View
This option enables generation of database views.

Begin Script
This option enables inserting the begin script before the SQL generation statement.

End Script
This option enables inserting the end script after the SQL generation statement.

Comment
This option enables showing of view comments in SQL script.

Drop Views
This option enables dropping old views if it were already exist.

Events Group

Create View
This option enables generation of database events.

Begin Script
This option enables inserting the begin script before the events SQL generation statement.

End Script
This option enables inserting the end script after the events SQL generation statement.

Comment
This option enables showing of event comments in SQL script.

Drop Views
This option enables dropping old events if it were already exist.

Options

This tab allows set generation-related options.

Character case
Defines case of characters, which will be used in generated script. Use Mixed option to leave the characters unmodified.

Body Delimiter
Defines symbol or string to use as body delimiter with DELIMITER statement for multi-statement stored routines, events and triggers.

Use 'TYPE' clause instead of 'ENGINE'
This option enables generation of TYPE clause instead of ENGINE in CREATE TABLE statement. TYPE clause is deprecated since MySQL 5.1 so you probably do not need this option.

Generate name in empty comments
Write the name of an object if the comment field is empty.

Check diagram
Enables checking the diagram before generation.

After generation, run
- Internal SQL Executor - Send the generated SQL statements into the internal SQL Executor.
- MicroOLAP Interactive SQL - This options active only if MicroOLAP Interactive SQL for MySQL is installed. Send the generated SQL statements into the advanced SQL editor MicroOLAP Interactive SQL for MySQL.
- Do not run generated script - Select this option if want just to generate SQL script without running it. For example you can use this option if you want to execute SQL script on remote server.

Selecting objects to generate

You can select diagram objects you want to generate in SQL script or database. Use the Selection tab of the Database Generation tool for it.

There are several subtabs: Tables, Stores Routines, Views and Views. Each of which allows you to select appropriate diagram objects to generate.

To enable particular objects generation, click on the checkbox near it.

The default selection of objects to generate depends on their Generate property.

Pay attention to the buttons on the Selection tab:

Select All
Checks on all checkboxes.

Deselect All
Checks off all checkboxes.

Use graphical selection
Checks on checkboxes for objects, depending on diagram selection.

Move selected items to top
Reorders items in list for checked items to be at the beginning of list.

You can change the order of tables in which they will be placed in the generated SQL script. Use the buttons with arrows for this.

Generating, customizing and executing SQL

Click on the Generate script button on the Database Generation tool to generate SQL script. The generated SQL script will be stored in the file you have set.

If you selected Internal SQL Executor on Options tab, the SQL Executor with generated SQL statements will appear.

You can easily customize statements for your needs. And then send them to the database server by clicking on the Execute SQL button.

Please, examine SQL Executor section to know more about it.

See also:
Database Accessing Tools: SQL Executor
Database Objects: Database Editor | Table Editor