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
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
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
Owners
Defines for which objects owner information should be generated.
Privileges
Defines for which objects privileges information should be generated.
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