For a list of the properties accessible through the Properties palette see Section 7. After the schema has been added to the current model, you can see the schema objects on the Physical Schemata panel on the MySQL Model page. The Routines and Routine Groups are listed there. MySQL Workbench unifies both stored procedures and stored functions into one logical object called a Routine. Routine Groups are used to group routines that are related. You can decide how many Routine Groups you want to create and you can use the Routine Group Editor to assign specific routines to a group, using a drag and drop interface.
Placing individual routines on the diagram is not permitted, as it would clutter the canvas. If a routine with this name already exists, the new routine is named routine2. Adding a new routine automatically opens the routine editor docked at the bottom of the application. Using the routine editor is described in Section 7. The Edit Routine The cut and paste options are useful for copying routines between different schemata.
Deleting the code for routine from the Routines tab of the Routine Group Editor will result in removal of the routine object from the model. To remove a routine from a routine group use the controls on the Routine Group tab of the Routine Group Editor.
Doing this opens the routine editor docked at the bottom of the application. Any number of routines may be open at the same time. Each additional routine appears as a tab at the top of the routine editor. There are two tabs at the bottom of the routine editor, the Routine and the Privileges tabs. From the Routine tab of the routine editor you can perform the following tasks:.
Rename the routine using the Name text box. The Privileges tab of the routine editor functions in exactly the same way as the Privileges tab of the table editor. If a routine group with this name already exists, the new routine group is named routines2.
Adding a new routine group automatically opens the routine groups editor docked at the bottom of the application. Using the routine groups editor is described in Section 7.
The Edit Routine Group Using the routine group editor is described in Section 7. The cut and paste options are useful for copying routine groups between different schemata.
Deleting a routine group from the MySQL Model page removes the group but does not remove any routines contained in that group. Any routine groups added to the Physical Schemata also show up in the Catalog palette on the right side of the application. They may be added to an EER Digram by dragging and dropping them from this palette. To do this make sure that the EER Diagram tab is selected, and right-click the routine groups icon on the vertical toolbar.
The routine groups icon is immediately above the lowest toolbar separator. Clicking the mouse on this icon changes the mouse pointer to a routine group pointer. You can also change the mouse pointer to a routine pointer by pressing the G key. Choosing the Routine Group tool changes the contents of the toolbar that appears immediately below the menu bar. When the Routine Groups pointer is active, this toolbar contains a drop down list box of schemata and a drop down color chart.
Use these list boxes to select the appropriate schema and color accent for the new routine group. Make sure that you associate the new routine group with a database.
The color of your routine group can easily be changed later using the Properties palette. Create a routine group by clicking anywhere on the EER Diagram canvas. This creates a new routine group with the default name routines1.
Right-clicking a routine group opens a pop-up menu. With the exception of the delete option and rename options these menu options function as described in Section 7. There is no rename option and the behavior of the delete option is determined by your MySQL Workbench options settings.
You can invoke the routine group editor by double-clicking a routine group object on the EER Diagram canvas or by double clicking a routine group in the Physical Schemata section on the MySQL Model page. Doing this opens the routine group editor docked at the bottom of the application.
Any number of routine groups may be open at the same time. Each additional routine group appears as a tab at the top of the routine editor,. There are two tabs at the bottom of the routine editor, the Routine group and the Privileges tabs. From the Routine Groups tab of the routine groups editor you can perform the following tasks:.
Rename the routine group using the Name text box. The Privileges tab of the routine group editor functions in exactly the same way as the Privileges tab of the table editor. When you select a routine group on the EER Diagram canvas, its properties are displayed in the Properties palette. All of the properties accessible from the Properties palette apply to the appearance of a routine group on the EER Diagram canvas. You can add layers to a database only from an EER Diagram.
Layers are used to help organize objects on the canvas. Typically, related objects are added to the same layer; for example, you may choose to add all your views to one layer. To do this select an EER Diagram tab and right-click the layer icon on the vertical toolbar.
Clicking the mouse on this icon changes the mouse pointer to a layer pointer. You can also change the mouse pointer to a layer pointer by pressing the L key. Choosing the Layer tool changes the contents of the toolbar that appears immediately below the menu bar. When the Layers pointer is active, this toolbar contains a drop down color chart. Use this list box to select the color accent for the new layer.
The color of your layer can easily be changed later using the Properties palette. Create a layer by clicking anywhere on the EER Diagram canvas and, holding the left mouse button down, draw a rectangle of a suitable size. This creates a new layer with the default name layer1. Use the name property of the Properties palette to change the name of a layer. Since layers are not schema objects, no confirmation dialog box opens when you delete a layer regardless of how you have configured MySQL Workbench.
Deleting a layer does not delete schema objects from the catalog. Add an object to a layer by dragging and dropping it directly from the Catalog palette onto a layer.
If you pick up an object from an EER diagram you need to press Ctrl as you drag it on to the layer, otherwise it will not be "locked" inside the layer. Locking objects to a layer prevents their accidental removal. You cannot remove them simply by clicking and dragging; in order to remove an object, you also need to press the Ctrl key while dragging it.
As a visual cue that the object is being "locked", the outline of the layer is highlighted as the object is dragged over it.
If you drag a layer over a table object, the table object will automatically be added to the layer. This also works for multiple table objects. Note that layers cannot be nested, that is, a layer cannot contain another layer object. When you select a layer on the EER Diagram canvas, its properties are displayed in the Properties palette. The properties accessible from the Properties palette apply to the appearance of a layer on the EER Diagram canvas.
In some circumstances you may want to make a layer invisible. To do this, select the layer and, in the Properties palette, set the visible property to False. To locate an invisible object, open the Layers palette and select the object by double-clicking it. Once an object is selected you can reset the visible property from the Properties palette. In addition to the properties listed there, a layer also has a description property.
Use this property to document the purpose of the layer. Notes are typically used to help document the design process. If a note with this name already exists, the new note is named note2.
Adding a new note automatically opens the note editor docked at the bottom of the application. Using the note editor is described in Section 7. The Edit Note Doing this opens the note editor docked at the bottom of the application. Double-clicking the note tab undocks the editor. Double-click the title bar to redock it. Any number of notes may be open at the same time. Each additional note appears as a tab at the top of the note editor.
Text objects are applicable to an EER diagram only. They can be used for documentation purposes, for example, to explain a grouping of schema objects. To do this make sure that the EER Diagram tab is selected, and right-click the text object icon on the vertical toolbar. The text object icon is the rectangular icon found below the label icon. Clicking the mouse on this icon changes the mouse pointer to a text object pointer. You can also change the mouse pointer to a text object pointer by pressing the N key.
Choosing the Text Object tool changes the contents of the toolbar that appears immediately below the menu bar. When the Text Object pointer is active, this toolbar contains a drop down color chart. Use this list box to select the color accent for the new text object. The color of your text object can easily be changed later using the Properties palette.
Create a text object by clicking anywhere on the EER Diagram canvas. This creates a new text object with the default name text1. Right-clicking a text object opens a pop-up menu. These menu options are identical to the options for other objects.
However, since a text object is not a database object, there is no confirmation dialog box when you delete a text object. You can invoke the text object editor by double-clicking a text object on the EER Diagram canvas.
Doing this opens the editor docked at the bottom of the application. Double-clicking the text object table undocks the editor. Any number of text objects may be open at the same time. Each additional text objects appears as a tab at the top of the text editor. When you select a text object on the EER Diagram canvas, its properties are displayed in the Properties palette. There is no property in the Properties palette for changing the font used by a text object.
To change the font used by a text object choose the Appearance tab of the Workbench Preferences dialog. To add an image make sure that the EER Diagram tab is selected, and right-click the image icon on the vertical toolbar. The image icon is the icon just above the table icon. Clicking the mouse on this icon changes the mouse pointer to an image pointer. You can also change the mouse pointer to an image pointer by pressing the I key.
Create a image by clicking anywhere on the EER Diagram canvas. This opens a file open dialog box. Select the desired image, and close the dialog box to create an image on the canvas. Delete 'Image'. These menu options function in exactly the same way as they do for other objects on an EER diagram. However, images are not database objects so there is no confirmation dialog box when they are deleted. You can invoke the image editor by double-clicking a image object on an EER Diagram canvas.
Doing this opens the image editor docked at the bottom of the application. Double-clicking the image editor tab undocks the editor. Any number of images may be open at the same time. Each additional image appears as a tab at the top of the image editor,. From the Image tab of the image editor you can perform the following tasks:.
Rename the image using the Name text box. Browse for an image using the Browse button. Doing this opens a file open dialog box with the default file type set to an SQL script file, a file with the extension sql.
Using the --no-data option ensures that the script contains DDL statements only. However, if you are working with a script that also contains DML statements you need not remove them; they will be ignored.
Use the --databases option with mysqldump if you wish to create the database as well as all its objects. Any database objects may be imported from a script file in this fashion; tables, views, routines, and routine groups.
Any indexes, keys, and constraints are also imported. Before exiting, be sure to save the schema. Select the Database , Reverse Engineer Doing this opens the Reverse Engineer Database wizard. The first page of the wizard enables you to set up a connection to the live database you wish to reverse engineer. This enables you to set up a new connection, or select a previously created connection. Typical information required for the connection includes hostname, username and password.
Once this information has been entered, or you have selected a stored connection, click the Next button to proceed to the next page:. Review the displayed information to make sure that the connection did not generate errors, then click Next. On the next page you can select the database schema you want to connect to among those available on the server. Simply click the checkbox or checkboxes associated with the schema you wish to process:. Once you have selected the desired schema, click the Next button to continue.
The wizard then displays the tasks it carried out and summarizes the results of the operation:. The next page is the Select Objects page. It is sectioned off by object type. This screen is of special interest if you do not wish to import all the objects from the existing database—this screen gives you the option of filtering which objects are imported. Each section has a Show Filter button.
Click this button if you do not want to import all the objects of a specific type. This enables you to select specific tables that you wish to import. Having selected the desired tables you can optionally hide the filter by clicking the Hide Filter button.
The wizard then displays the tasks that have been carried out and whether the operation was successful or not. If errors were generated then you can click the Show Logs button to see the nature of the errors.
The final screen of the wizard provides a summary of the reverse engineered objects:. During reverse engineering the application checks for tables and views that duplicate existing names and disallows duplicate names if necessary.
If you attempt to import an object that duplicates the name of an existing object you will be notified with an error message. To see any errors that have occurred during reverse engineering you can click the button Show Logs. This will create a panel containing a list of messages, including any error messages than may have been generated.
Click the Hide Logs button to close the panel. If you wish to import an object with the same name as an existing object, rename the existing object before reverse engineering. If you import objects from more than one schema, there will be a tab in the Physical Schemata section of the MySQL Model page for each schema imported. You cannot reverse engineer a live database that has the same name as an existing schema.
If you wish to do this, first rename the existing schema. It is possible to forward engineer a database using an SQL script or by connecting to a live database. To create a script of your database model use the Export option found under the File menu.
You may export a script to alter an existing database or create a new database. If you choose to create a database, there are a number of export options that you may choose from.
The first page of the wizard is:. You can enter the name of your output file by entering it into the Output SQL Script File text box, or by using the Browse button to select a file. If this text box is left blank you will be able to view the generated script, but it will not be saved to a file.
Omit Schema Qualifier in Object Names. Do Not Create Users. Only Export Privileges. To update the privileges of existing users as opposed to creating new users, select this check box. Exporting users that already exist, will also result in an error. For more information about inserting records see Section 7. Precise control over the objects to be exported can be fine tuned by clicking the Show Filter button. Once the objects to be exported have been selected it is possible to reduce the expanded panel by clicking the same button, now labelled Hide Filter.
Having selected the objects you wish to export you can click the Next button to review the script that has been generated:. The Finish button saves the script file and exits. You may return to the previous screen using the Back button. 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 a database to alter it to reflect the adjusted model.
For instructions on importing a DDL script see Section 7. You will be presented with the first page:. The difference between the two models will be used to create an alter script that can be used to modify the target schema so that it matches the model held in MySQL Workbench. You can also simply view the script generated, rather than saving it to a file, by leaving the Output File text box empty.
Here you can review and change the alter script that will be generated. You can also click the Execute button to write the script to the previously specified output file. Select the schema that you wish to forward engineer and then choose Database , Forward Engineer The first page to be displayed is Catalog Validation validation is only available in the Standard Edition :. The next page enables you to set options for the database to be created. These options are as described in Section 7.
Once you have selected your objects click Next to continue. Click Next to continue if you are satisfied with the generated script. The next step of the process is to connect to a MySQL server in order to create the new database schema. This page enables you to use a previously stored connection, or enter the connection parameters:.
Once the connection parameters have been set click Execute. The next page of the wizard displays the results of the forward engineering process:. By default, the synchronization process will change the live database to be the same as the model, but this is configurable during the synchronization process. MySQL Workbench enables control over the direction of synchronization, and the objects synchronized, in a completely flexible way. You can choose to synchronize only certain tables, allow synchronization to the live database only, allow synchronization from the live database to the model only, or a combination of directions.
In effect you have complete control as to whether the synchronization is unidirectional or bidirectional, and which objects exactly are subject to synchronization. This is all controlled in the Select Changes to Apply page of the synchronization wizard:. In the above example the live database consists of table1 , table2 and table3.
In MySQL Workbench an additional table, table4 , has been created, along with a relationship between it and table3. Further, table5 exists in the live database, but not in the model.
The actions that are configured to occur would result in table3 being altered to include the relationship with table4 , table4 being created and table5 being dropped, in the live database. It is possible to reconfigure this though.
In this case the synchronization direction has been changed so that rather than the default action of table5 being dropped from the live database, it will be incorporated into the MySQL Workbench model. For convenience the wizard provides three additional buttons to allows synchronization directions to be applied to a group of selected changes. The Update Model button causes the selected changes to only be applied to the model itself:.
In the above example table7 would be added to the model. The Ignore button causes the selected changes to be ignored, no synchronization will take place for those changes:.
The Update Source button causes the selected changes to only update the live database:. In this example table6 would be added to the live database and table7 would be dropped from the live database. It is also possible to control individual changes by clicking the arrows. Clicking an arrow causes it to change between the three available synchronization directions:. In the above example table6 will be created in the live database, and table7 will be created in the model. This facility enables you to create a report detailing the differences between your MySQL Workbench model, and a live database or script.
Having started the wizard the first step is to specify the catalogs you wish to compare. For example, you may simply wish to compare your live database against your current MySQL Workbench model:. You then proceed through the wizard, providing connection information if accessing a live database.
The wizard will then produce a catalog diff report showing the differences between the compared catalogs:. This chapter contains three short tutorials intended to familiarize you with the basics of MySQL Workbench. Creating a database from scratch is the focus of Section 7. Examples taken from the sakila database are used throughout the documentation so doing this tutorial can be very helpful in understanding MySQL Workbench.
After downloading the file, extract it to a convenient location. Find and import the sakila-schema. This is the script that contains the data definition statements for the sakila database. To view the newly imported script, expand the Physical Schemata section by double-clicking the arrow on the left of the Physical Schemata title bar. Select the tab labelled sakila. Yo may also wish to remove the default schema tab, mydb.
Do this by selecting this tab and then clicking the - button on the upper right in the Physical Schemata panel. To view all the objects in the sakila schema, you may need to expand the Physical Schemata window. To do this move the mouse pointer anywhere over the gray area that defines the lower edge of the Physical Schemata window.
Hold down the right mouse button and move the mouse to adjust the size of the window. Once you've expanded the window, all the objects in the sakila database should be visible. Tables appear at the top followed by views and then routines. There are no routine groups in this schema, but you should see the Routine Groups section and an Add Group icon. This should create and open a new EER Diagram. The EER Diagram canvas is where object modeling takes place. To add a table to the canvas, select the Catalog tab in the middle panel on the right side of the application.
Expand the tables list in the same way. You can add tables to the EER canvas by picking them up from the Catalog panel and placing them on the canvas. Drop the address table and the city table onto the canvas. MySQL Workbench automatically discovers that address.
Drop the country table onto the canvas and immediately you should see the relationship between the country table and the city table. See Figure 7. Choose the Properties tab of the panel on the lower right and then click one of the tables on the canvas. This displays the properties of the table in the Properties window.
While a table is selected you can use the Properties window to change a table's properties. For example, entering FF for the color value will change the color accent to red. Changing the color of a table is a good way to identify a table quickly—something that becomes more important as the number of tables increases. Changing the color of a table is also an easy way to identify a table in the Model Navigator panel. This panel, the uppermost panel on the left side of the screen, gives a bird's eye view of the entire EER canvas.
You can begin designing a database by using this default schema. To change the name of the default schema, double-click the schema tab. This opens a schema editor window docked at the bottom of the application.
To undock or redock this window, simply double-click anywhere in the editor title bar. To rename the schema, use the text box labeled Name. Once you have renamed the schema a lightning bolt icon appears right aligned in the Name text box, indicating that other changes are pending.
Click the Comments text area and a dialog box opens asking if you wish to rename all schema occurrences. Clicking Yes ensures that your changes are propagated throughout the application. Add comments to the database and change the collation if you wish. Close the schema editor by clicking the x button. Create a new table by double-clicking the Add Table icon in the Physical Schemata panel. Doing this opens the table editor docked at the bottom of the application.
If you wish, you can undock or dock this editor in exactly the same way as the schema editor window. Use the first tab of the table editor to change the name, collation, and engine. You may also add a comment. Add columns to the new table by selecting the Columns tab. Use the default column name or enter a new name of your choosing.
Use the Tab key to move to the next column and set the column's data type. Altering the table by adding indexes or other features is also easily done using the table editor. Additional objects such as views or routines can be added in the same way as tables. Any objects you have created can be found in the Catalog palette on the right. To view these schema objects select the Catalog tab in the middle palette on the right.
This creates and opens a new EER Diagram. Start by creating a table using the table tool. The table tool is the rectangular grid in the middle of the vertical toolbar.
Mousing over it shows the message, Place a New Table T. Clicking this tool changes the mouse pointer to a hand with a rectangular grid. Create a table on the canvas by clicking anywhere on the EER Diagram grid. Right-click the table and choose Edit in New Window from the pop-up menu. This opens the table editor, docked at the bottom of the application. The table name defaults to table1. Change the name by entering invoice into the Name: text box.
Notice that the name of the tab in the table editor and the name of the table on the canvas, both change to this new value. Pressing Tab or Enter while the cursor is in the table name text box, selects the Columns tab of the table editor and creates a default column named, idinvoice.
Notice that a field has been added to the table on the EER canvas. Pressing Tab yet again and the focus shifts to adding a second column. When you are finished, close the table editor, by clicking the x button on the top left of the table editor. Select the table tool again and place another table on the canvas. Next click the 1:n Non-Identifying Relationship tool. Next click the invoice table. This relationship between the two tables is shown graphically in crow's foot notation.
Revert to the default mouse pointer by clicking the arrow at the top of the vertical toolbar. Click the Foreign key Name text box. The referenced table should show in the Referenced Table column and the appropriate column in the Referenced Column column.
To delete the relationship between two tables, click the line joining the tables and then press Ctrl Delete. Experiment with the other tools on the vertical toolbar. Delete a relationship by selecting the eraser tool and clicking the line joining two tables.
Create a view, add a text object, or add a layer. This chapter highlights the capabilities of MySQL Workbench as a documentation tool using the sakila database as an example. An EER diagram is an invaluable aid to a quick understanding of any database. There is no need to read through table definition statements; glancing at an EER diagram can immediately indicate that various tables are related. You can also see how tables are related; what the foreign keys are and what the nature of the relationship is.
Find below an EER digram showing all the tables in the sakila database. The object notation style used in Figure 7. This notation only shows primary keys and no other columns so it is especially useful where space is at a premium. The relationship notation is the default, Crow's Foot. Some tables have two foreign keys that relate to the same table.
Where there is more than one relationship between two tables, the connection lines run concurrently. Identifying and nonidentifying relationships are indicated by solid and broken lines respectively. The printing options are used to create printouts of your EER Diagrams and are found under the File menu. For creating documentation of your models see Section 7. The printing menu options are grayed if an EER Diagram is not active.
Use this option to send your EER Diagram directly to the printer. This option generates a preview before printing. From the preview you can adjust the scale of the view and also choose a multi-page view. Clicking the printer icon at the top left of this window, prints the currently selected EER Diagram.
Close the print preview window if you need to adjust the placement of objects on the EER Diagram canvas. MySQL Workbench provides validation modules so that you can test your models before implementing them. Documenting the sakila Database. Forward and Reverse Engineering. Forward Engineering to a Live Server. Reverse Engineering Using a Create Script. Reverse Engineering a Live Database.
Schema Synchronization and Comparison. Database Synchronization. Compare and Report Differences in Catalogs. Supported Template Markers. Creating a Custom Template. Connection Properties. Adding Views to the Physical Schemas. Modifying a View Using the Properties Palette. Creating Routines and Routine Groups. Modifying a Layer Using the Properties Palette. Additional Modeling Tools.
Schema Validation Plugins. Creating a Model. Creating Other Schema Objects. Documenting the sakila Database. Forward and Reverse Engineering. Forward Engineering to a Live Server. Reverse Engineering Using a Create Script. Reverse Engineering a Live Database. Schema Synchronization and Comparison. Database Synchronization. Compare and Report Differences in Catalogs. Supported Template Markers.
0コメント