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 PostgreSQL server. Please examine Connect to a Database section to explore the database connection process;
• Generate a script to be executed on PostgreSQL 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 can set tables and database generation options.

Database Group

Run 'Create New Database Wizard'

Mark this checkbox if you want to create a new physical PostgreSQL database before generating the database structure presented in the diagram.

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.

Role Group

Create Roles

This option enables generation of model roles.

in Wizard before Database creation

This option enables executing the CREATE ROLE statements before the CREATE DATABASE statement right in New Database Wizard.

in Wizard after Database creation

This option enables executing the CREATE ROLE statements after the CREATE DATABASE statement right in New Database Wizard.

in the script before database objects

This option enables executing the CREATE ROLE statements in the script before tables, views, stored routines etc.

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 Rules

This option enables generation of rules for the tables.

Create 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 "<TABLE_NAME>" statement before CREATE TABLE statement.

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.

Create Rules

This option enables generation of rules for the views.

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.

Before generation, run

Check diagram

Enables checking the diagram before generation.

Drop objects

Include IF EXISTS clause

Do not throw an error if the object does not exist. A notice is issued in this case while executing generated script. Server must be 8.2.x or higher.

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 PostgreSQL is installed.

Send the generated SQL statements into the advanced SQL editor MicroOLAP Interactive SQL for PostgreSQL.

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 Types & Domains. 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.

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.