Database Modification

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 MySQL server. Please examine Connect to a Database section to explore the database connection process
Generate a modification script for executing at a MySQL 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.

The generated modification SQL statements take care about the data already stored in your database. The tool generates statements, which copy existing data from your old tables to the new database structure.

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:

Reverse engineers your existing MySQL database
Compares the result with your current database diagram
Creates a list that contains differences between database objects and diagram objects
After analyzing the difference list, creates neccessary SQL statements, that modify database structure and copy data from old-structure tables.

If a structure of some table must be modified, this table will be renamed in this scheme: tmp_<tableName>. And its data will be copied to a table with the new structure. This allows you to restore the old table with data.

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.

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.

Generate table options

This option enables the setting of table options, such as TYPE, ROW_FORMAT and others in the SQL script.

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

See Also

Database Accessing: SQL Executor | Connect to a Database