7.7.10.1. Forward Engineering Using an SQL Script

7.7.10.1. Forward Engineering Using an SQL Script
Prev	7.7.10. Forward Engineering	Next

7.7.10.1.1. Creating a Schema
7.7.10.1.2. Altering a Schema

To create a script of your database model, choose the Export item from the File menu. You may export a script to alter an existing database or create a new database. The script to create a database is similar to the one created using the mysqldump db_name command.

If you choose to create a database, there are several export options available.

7.7.10.1.1. Creating a Schema

Select the File, Export, Forward Engineer SQL CREATE Script menu item to start the Forward Engineer SQL Script wizard. The following figure shows the first page of the wizard.

Figure 7.28. SQL Export Options

The SQL Export Options displays the following facilities:

Output SQL Script File
To specify the output file name, enter it into the Output SQL Script File field, or use the Browse button to select a file. If this field is left blank, you will be able to view the generated script, but it will not be saved to a file.
Generate DROP Statements Before Each CREATE Statement
Select this option to generate a statement to drop each object before the statement that creates it. This ensures that any existing instance of each object is removed when the output is executed.
Omit Schema Qualifier in Object Names
Select this option to generate unqualified object names in SQL statements.
Generate Separate CREATE INDEX Statements
Select this option to create separate statements for index creation instead of including index definitions in CREATE TABLE statements.
Add SHOW WARNINGS after every DDL statement
Select this option to add SHOW WARNINGS statements to the output. This causes display of any warnings generated when the output is executed, which can be useful for debugging.
Do Not Create Users. Only Export Privileges
Select this option to update the privileges of existing users, as opposed to creating new users. Exporting privileges for nonexistent users will result in errors when you execute the CREATE script. Exporting users that already exist will also result in an error.
Generate INSERT Statements for Tables
Select this option if you have added any rows to a table. For more information about inserting rows, see Section 7.7.1.3.8, “The Inserts Tab”.

Clicking Next takes you to the SQL Object Export Filter page where you select the objects you wish to export.

Figure 7.29. SQL Object Export Filter

Precise control over the objects to export can be fine tuned by clicking the Show Filter button. After the objects to export have been selected, it is possible to reduce the expanded panel by clicking the same button, now labeled Hide Filter.

After selecting the objects to export, click the Next button to review the script that has been generated.

Figure 7.30. Review Generated Script

You may return to the previous page using the Back button.

The Finish button saves the script file and exits. You can then use the saved script to create a database.

7.7.10.1.2. Altering a Schema

The menu item for altering a schema, Forward Engineer SQL ALTER Script..., is used for updating a database that has been redesigned within MySQL Workbench. Typically, this option is used when the SQL script of a database has been imported into MySQL Workbench and changed, and then you want to create a script that can be run against the database to alter it to reflect the adjusted model. For instructions on importing a DDL script, see Section 7.7.9.1, “Reverse Engineering Using a Create Script”.

Select the File, Export, Forward Engineer SQL ALTER Script menu item to start the Forward Engineer an ALTER Script wizard. You will be presented with the first page showing the available options.

Figure 7.31. Options

This first page enables you to select an SQL script and compare it with the model currently in MySQL Workbench. The difference between the two models will be used to create an alter script that can be used to modify the target schema to match the model held in MySQL Workbench. To view the script generated, rather than saving it to a file, leave the Output File field empty.

Note

The script selected as the Input File must use full schema qualifiers, such as schema_name.table_name. Otherwise, MySQL Workbench cannot generate a useable alter script.

Clicking Next brings you to the Review SQL Script page.

Figure 7.32. Script

Here you can review and change the alter script that will be generated. Make any changes you wish and, if you are happy with the changes, save the ALTER script to file using the Save to File... button. You can also click the Execute button to tell MySQL Workbench to write the script to the previously specified output file.

The generated script can then be used to update the database.