The Database Generation tool can generate SQL script, that represents the diagram you developed and executes it on the database server.
You can generate database in two ways:
- Directly execute a script on a MySQL server. Please examine Connect to a Database section to explore the database connection process;
- Generate a script to be executed on MySQL server at a later time.
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 selecting the Database | Generate Database menu item or pressing Ctrl-G. The following pictures demonstrate Database Generation tool interface.
File name field
This field allows you to set file, in which generated SQL statements will be stored. Click on the ... button near the field to browse to file on the file system.
Database Generation tool consist of two tabs, which contain SQL generation options. Let's explore them.
Tables & Database
In the Tables & Database tab of the Database Generation tool you are able to set generation options relative to tables and database.
Tables Group
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 Foreign Keys
This option enables generation of foreign keys for the tables.
Create Indexes
This option enables generation of indexes for the tables.
Create Table Options
This option enables setting of table options, such as TYPE, ROW_FORMAT and others in the SQL script.
Create Table Triggers
Generate triggers set on table.
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.
Table and column comments
Generate COMMENT clause for tables and columns that have a comment.
Database Group
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.
End Script
This option enables inserting the end script after the CREATE DATABASE statement.
Comments
This option enables showing of database comments in SQL script.
Drop Database
This option enables dropping old database if it was already exist.
Views & Stored Routines
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.
Comments
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.
Comments
This option enables showing of view comments in SQL script.
Drop Views
This option enables dropping old views 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.
Generate name in empty comments
Write the name of an object if the comment field is empty.
Before generation, run
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.
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 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 at 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.
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 OK 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 have establish connection to database, 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.
Tutorials: Tutorial - Creating Your First Diagram
Database Accessing: SQL Executor
Database Functions: Database Modification
©2005 MicroOLAP Technologies LTD. All Rights Reserved. All trademarks are the sole property of their respective owners.
