EPUB | CHM | PDF

How to Generate a Database

Top Previous Next

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.

To generate database, start the Database Generation tool by selecting the Database | Generate Database menu item or pressing Ctrl-G. Database Generation tool consist of five tabs, which contain SQL generation options. Let's explore them. The following pictures demonstrate Database Generation tool interface.

Database Generation

dialog-dbgeneration-db-n-tables

File name

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

Notes as SQL Comments

This option enables using notes of database as comments in SQL script.

Role Group

Create Role

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.

Comment

This option enables inserting of table comments in SQL script.

Columns Default Values

This option enables generation of columns' default values.

Create Primary Keys

This option enables generation of table primary keys.

Create Check Constraints

This option enables generation of check constraints for the tables.

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

dialog-dbgeneration-views-n-sp

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.

Comment

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.

Owners & Privileges

dialog-dbgeneration-owners-n-acl

Owners

Defines for which objects owner information should be generated.

Privileges

Defines for which objects privileges information should be generated.

Options

dialog-dbgeneration-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.

Type names

Determines what type names will be used in the generated SQL, PostgreSQL (int2, int4, int8, varchar, char, bool, float8, float4) or standard ones (smallint, integer, bigint, character varying, character, boolean, double precision, real).

Before generation:

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.

Default application associated with SQL files

Send the generated SQL statements into the application associated with SQL files

Don't run, just save

Do nothing with output script, just save it on disk.

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, Types & Domains and Sequences. 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 established 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.

See also:
Database Accessing: SQL Executor
Database Functions: Database Modification