Database Modification Overview

Once you have changed your diagram, it's usually necessary to apply these changes to your database. It's easy to do with the Database Modification tool. The Database Modification tool can generate SQL script that leads your database to the current state of your diagram.

You can synchronize database in two ways:

• Directly execute a modification script on a PostgreSQL server. Please examine Connect to a Database section to explore the database connection process;
• Generate a modification script for executing at a PostgreSQL server some time later.

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

Please note, that database modification usually cause multiple complex statements for database structure modification. It is possible that some of them may not execute correctly due to some physical reason. It's recommended to make a backup of your database before applying structure changes to database.

That's how the Database Modification tool works:

1. Reverse engineers your existing PostgreSQL database
2. Compares the result with your current database diagram
3. Creates a list that contains differences between database objects and diagram objects
4. After analyzing the difference list, creates neccessary SQL statements, that modify database structure.

It some table is going to be modified, Database Designer for PostgreSQL makes a backup copy of that table, so you can restore data and table structure later on if there will be some errors during the table structure alteration.

Database Modification Tool

To modify your database, start Database Modification tool by selecting the Database | Modify Database menu item or pressing Ctrl-M. The following pictures demonstrate the Database Modification tool interface.

The Database Modification tool consist of two tabs, which contain SQL generation options. Let's explore them.

Modification Options

In the Options tab of the Database Modification tool you can set modification options.

File name

This field allows you to set a file, in which generated modification SQL statements will be stored. Click on the ... button near the field to browse to the file on the file system.

Trace table renaming

Try to find tables that has been renamed in the diagram by comparing their structure with the structure of the tables in the database. If a structure of a table in the database is identical to a structure of a table in the diagram, but such tables have different names, a table in the database is going to be renamed to match the diagram table's name.

Don't delete existing tables

This option disables deleting tables that already exist in the database, but don't exist in your diagram.

Generate Primary Keys

This option enables the generating of table Primary keys.

Generate references

This option enables generating of foreign keys for the tables.

Show table comments in SQL script

This option enables the showing of table comments in SQL script, which was set in the Table Editor.

Generate indexes

This option enables the generating of indexes for the tables.

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 tables to generate

You can select diagram objects which need to be modified in the database. Use the Selection tab of the Database Modification tool for this.

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

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 Modification tool to generate SQL script. The generated SQL script will be stored in the file you have set. Also SQL Executor with generated SQL statements for database modifications will appear.

You can easily customize statements according to your wants and wishes. 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.