dbForge Studio for MySQL

2004 statement at the end of the query and press F5 to execute it. The goal is achieved. As you can see that Intellisense feature provided by dbForge Studio allows you to create complex queries in a few keystrokes. Creating a Query in Query Builder As an example, we are going to create a sample query using visual designer. We will use the sakila database and display all the films stored in the sakila database, that were released after 2004.26 To create a query visually: 1. Create a server connection. For more information about how to create a server connection see How To: Connect to a Database. 2. On the ​Start page click ​SQL Development and then click ​Query Builder​. Now, you can drag-and-drop tables from the ​Database Explorer to the editor. Holding ​Ctrl​, select the category, film, and film_category tables in ​Database Explorer​, and drag-and-drop them over to the visual editor. Alternatively, right-click any of the selected tables, point to ​Send to​, and then click ​Query Builder on the shortcut menu. 3. Select the film_id and title checkboxes in the film table, and the ​name checkbox in the ​category​ table. Actually, our query is ready to be executed. However, we need to add a ​WHERE condition, to display the films that were released after 2004. 4. Go to the ​Where​ tab to insert the condition. Click the ​green plus​ icon.27 5. Click ​enter value​. 6. Select ​release_year​ from the list. 7. Click the ​equals​ sign and select ​greater than​ instead. 8. Click ​enter a value​ and type 2004. 9. Click ​Execute​. The result set displays the information we have requested. Visual Query Builder is a powerful tool that allows you to build complex queries quickly and with no code typing.28 Editing Data When dbForge Studio executes any statement returning a result set, the retrieved data is displayed in ​Data Editor of the ​Data view of the same SQL document or the Data window. This topic tells how to edit the data in grid; for information about customizing data grid, as well as sorting, grouping and filtering, visit the ​Viewing Data in Grid​ topic. Editing Data 1. In ​Data Editor available in the Data view of SQL document, Object Viewer, or in the Data window, click a cell you want to edit. 2. After editing, confirm the changes by clicking the ​End Edit button under the grid or selecting the ​End Edit​ option on the shortcut menu. Tip: To discard changes, click the ​Cancel Edit button or select the ​Cancel Edit option on the shortcut menu. In dbForge Studio, you can also edit data right after retrieving it from a table or in the data view after any query was executed. To retrieve data, select ​Retrieve Data on the shortcut menu of the table in ​Database Explorer​. After this a window asking if you want to make the received data grid editable or not will appear. If you want to make the grid editable, click the ​Make Editable button, if you want the grid to be read-only, click ​Keep Read-Only​. You can check the ​Don’t show this message again checkbox, and the message won’t appear when you click ​Retrieve Data any more. To make data received after clicking ​Retrieve Data editable you can also choose the table name from the Table drop-down list on the toolbar , or leave it read-only by selecting (read-only). If you choose the read-only mode, you will gain in the performance aspect, as the application will work faster in this mode. To edit data in join query results, you should set the table you want to edit data from to the editable mode, as described above. After you set one table as editable, the data of other tables included into the result will become light-gray.29 Note: ​When you change the mode of any table from read-only to editable or vice versa, a balloon with the corresponding message is shown on the status bar. Inserting, Deleting, and Copying Data Use the shortcut menu or the corresponding buttons under the grid. For example, you can do the following: ● To insert NULL (instead of empty string), select ​Set Value To -> Null on the shortcut menu. You can also use the ​CTRL+0​ key combination. Tip:​ Use ​Set Value To​ menu to quickly set an empty string, zero, or a current date. ● To insert SET and ENUM data, dbForge Studio offers convenient editors. Click a field of the ENUM data type and select a required value from the drop-down list. Click a field of SET data type and tick off a required value (or values) in the drop-down editor. ● To add a new record, select the ​Append option from the shortcut menu or click the Append button under the grid. To delete a record from the grid, select the ​Delete option on the shortcut menu or click the ​’-‘ button under the grid, or press CTRL+DELETE​ keys. ● To copy and paste cell values, use the corresponding options from the shortcut menu. Selecting Data You can easily select and copy the data just like cells in a spreadsheet. Do either of these actions: ● Move the mouse pointer across the grid holding the left mouse button.30 ● Click the first cell of the data range, press ​SHIFT​, and, holding the ​SHIFT key, click the last cell. A rectangular range of cells will be selected. Tip: Using the ​CTRL key, you may include and exclude cells to selection. Any number of rows may be copied and pasted to some other application, for example, a spreadsheet, but when you paste data in the data grid, only the first row will be pasted. Note: ​Some queries return read only data, for example, queries with aggregate functions (count, avg, etc.), or data, retrieved from non-updatable views. Such data can’t be edited. It is also impossible to edit the result of executing of the script with several select statements. Row and Cell Indicators When working with a grid, you can see special indicators near to the focused cell. These indicators reflect current editing state. The row is focused. The row is being edited. The row has been edited. Incorrect value was entered into a cell. You must either fix the value or press the ESCAPE​ key to cancel changes made to the cell. Creating a Table in Table Editor As an example, we will create a new table that does not contain any data. Nevertheless, while creating the table you must decide what types of data you want to store in it. The data is held in fields, and each field can be declared as one of the data types that are available. The sakila database contains the film table. We are going to create a table that will store film ratings descriptions. To create a table: 1. You must be connected to a database. For more information about how to create a server connection see ​Connecting to a Database​. 2. In ​Database Explorer​, double-click a required database wherein you need to create a table.31 3. Right-click the ​Tables​ folder and select ​New Table​. The Table Editor opens. 4. In the ​Name text box, enter a table name. You can give it a name such as ratings_description. 5. In the grid below, click the first empty cell and enter the column name. You can give it a name such as id. Note that the data type for that column is defined automatically. Select the checkbox next to id, in order to set this column as the primary key. 6. Click next empty cell and enter the column name. You can give it a name such as description. Note that the data type for that column is defined automatically. 7. Click ​Apply Changes​. The new empty table is created. It now appears in the ​Database Explorer in the ​Tables folder. Backup a Database In this example we will create the sakila database backup. To backup the database: 1. Click ​Backup Database on the ​Administration tab of ​Start ​Page​. The Database Backup Wizard opens. 2. Select a ​connection or click ​ in the ​Connection ​list, and then click ​New to create a new connection.32 3. Once the connection is established, select a database you want to backup in the Database​ list. 4. Specify a folder path to store backups in the ​Path​ text box. 5. Specify an output file name in the respective text box. 6. Set up some optional settings such as: append timestamp to the file name, auto delete old files, use compression etc, at your own decision. Click ​Next​. 7. Select database objects to backup. Click ​Next​.33 8. Specify detailed options on how backup should be performed. Click ​Next​. 9. Select the ​Write a report to a log file checkbox to save the backup process information to a log file. Enter the path where to store the file, for instance C:\Backups\Backup.log. 10.Click ​Backup​.34 11.As soon as the backup process has finished, click ​Save Project​. A project file contains all the information that you have entered while walkthrough the wizard pages. Running Backup from the Command Line To run a backup from the command line: 1. Click ​Start​. 2. Type ​cmd​ in the ​Search programs and files​ text box and press the ​ENTER​ button. Note: ​To run the Command Line in Windows 8: From the Desktop Mode ● Move the mouse cursor to the exact lower left corner until the desktop shortcuts menu appears. ● Right-click to see the shortcut menu and then click ​Run From the Charms Bar ● Move your mouse to the upper right corner until the ​Charms Bar​ appears. ● Select ​Apps​ from the list and type ​run​ in the search box. ● Click ​Run​ from the search results. Use Windows + R Shortcut Keys ● In desktop mode, press the Windows key and R at the same time to show the Run command line.35 3. Enter the command line as follows: c:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com /backup /projectfile:c:\Backups\sakila.backup. Press ​ENTER​. After the process ends, a backup file appears in the specified directory. Scheduling Database Backup You can use the ​Windows Task Scheduler to create and manage backup tasks that your computer will carry out automatically at the times you specify. To create Windows task: 1. Run the ​Windows Task Scheduler​. Note: ​To run the Windows Task Scheduler: 1. Click Start and then click Control Panel. 2. Click Administrative Tools. If your current view is Category, click System and Security, and then click Administrative Tools. 1. Click Task Scheduler 2. Click the ​Action​ menu, and then click ​Create Basic Task​. 3. Type a name for the task and an optional description, and then click ​Next​. 4. Do one of the following: ○ To select a schedule based on the calendar, click Daily, Weekly, Monthly, or One time, click Next; specify the schedule you want to use, and then click Next​. ○ To select a schedule based on common recurring events, click ​When the computer starts​ or ​When I log on​, and then click ​Next​.36 ○ To select a schedule based on specific events, click When a specific event is logged, click ​Next​; specify the event log and other information using the drop-down lists, and then click ​Next​. 5. To schedule a program to start automatically, click ​Start a program​, and then click Next​. 6. Click ​Browse to enter a path to the .bat file that stores the command line string. For instance, a .bat file can contain a command line string as follows: c:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com /backup /projectfile:c:\Backups\sakila.backup. 7. Click ​Next​. 8. Click ​Finish​.37 2. Connecting to a Database Managing Database Connections dbForge Studio for MySQL offers you visual, quick, and simple way to manage database connections through the ​Database Explorer​ window. By default, Database Explorer displays recently used database connections. If you open Database Explorer​ for the first time, no connections are shown. Use Database Explorer to do the following: Create a database connection 1. Open the ​Database Connection Properties​ dialog box by either of these ways: ○ Click the ​New Connection​ button on the ​Database Explorer​ toolbar ○ Right-click in the ​Database Explorer window and select ​New Connection on the shortcut menu 2. Set up connection parameters. You can find detailed information about ​Managing Database Connections at our documentation center: ​https://docs.devart.com/studio-for-mysql/ Creating SSH Connection SSH (Secure Shell Host) connection is established between an SSH server and a client (it is built in dbForge Studio). The client is used to access remote machines and execute commands. SSH protocol offers ​SSH tunneling to provide secure transmit of data. All the data transmitted over the insecure network is encrypted on one side of SSH connection and decrypted on another side. This ensures privacy, authenticity, and integrity of transmitted data and allows you to connect to a remote MySQL Server, when a port is blocked. SSH connection includes the following stages: 1. Connection between the SSH Server and the MySQL Server is created. 2. Authentication between the SSH Server and the MySQL Server is successfully done. The SSH Server can use two types of authentication: ○ Password authentication - it uses a password of a user account on the SSH Server.38 ○ Public key authentication - it uses a pair of public and private keys which you can generate by a key generator tool, for example, ​PuTTygen​. 3. The client and MySQL Server exchange the data which is transmitted through the SSH Server. SSH Connection using Password authentication: 1. Check that you have the SSH Server installed and set up. If no, see ​Installing and Setting Up SSH Server​ topic. 2. Open the ​Database Connection Properties​ dialog box by any of these ways: ○ On the ​Database​ menu, select ​New Connection​. ○ Click the ​New Connection button​ on the ​Database Explorer​ toolbar. ○ Right-click the ​Database Explorer window and select ​New Connection on the shortcut menu. 3. Switch to the ​Security​ tab, check ​Use security protocol​ and select ​Use SSH​. 4. Select ​Password authentication and input login information required to connect to the SSH server: ○ Host​ - the name or ip address of the SSH Server. ○ Port​ - the TCP/IP port to connect to the SSH Server. By default, it is 22. ○ User​ - the name of the user account on the SSH Server. ○ Password​ - the password of a user account on the SSH Server. Tip: Select the ​Save password checkbox, otherwise while opening the connection, the ​Connect to MySQL Database ​dialog box will appear and dbForge Studio will ask you to enter the password again.39 5. On the ​General tab, set login information required to connect to the MySQL Server. It should be set relatively to the SSH Server. Specify the following: ○ Host - the name of the MySQL Server host. If SSH and MySQL Servers are on the same machine, the host name can be ​localhost​. It is highly recommended to specify the IP address explicitly (especially for the Linux OS). ○ Port​ - the TCP/IP port to connect to the MySQL Server. By default, it is 3306. ○ User​ - the name of the user account on the MySQL Server. ○ Password​ - the password of the user account on the MySQL Server. 6. Specify the default database of the MySQL Server. To see all databases of the selected MySQL Server in the ​Database Explorer window, select ​Show all databases​, otherwise you will see only the selected one. 7. (Optional) To test the created connection, click the ​Test Connection​ button. 8. Click ​OK​ to establish the database connection.40 SSH Connection using Public key authentication: 1. Check that you have the SSH Server installed and set up to allow public key authentication. If no, see ​Installing and Setting Up SSH Server​. 2. Open the ​Database Connection Properties​ dialog box by any of these ways: ○ On the ​Database​ menu, select ​New Connection ○ Click the ​New Connection​ button on the ​Database Explorer​ toolbar ○ Right-click in the ​Database Explorer window and select ​New Connection on the menu Note: ​The Database Explorer is not available in the dbForge Data Compare for MySQL and dbForge Schema Compare for MySQL. 3. Switch to the ​Security​ tab, check ​Use security protocol​ and select ​Use SSH​. 4. Select Public key authentication and input login information required to connect to the SSH Server: ○ Host​ - the name or ip address of the SSH Server. ○ Port​ - the TCP/IP port to connect to the SSH Server. By default, it is 22. ○ User​ - the name of the user account on the SSH Server. ○ Passphrase - the passphrase for a private key. You can set it while generating public and private keys through a key generator tool, for example PuTTygen. ○ Private key - the private key file location on your local machine. Click the button in the field to specify the location or enter it manually. (You can generate the private key along with a public key using a key generator tool. Note if you are using PuTTygen, you can either convert a generated private key into OpenSSH format (by selecting ​Conversions>Export OpenSSH key in the menu) or use it without conversion. dbForge Studio supports both formats.41 5. On the ​General tab set login information required to connect to the MySQL server. It should be set relatively to the SSH server. Specify the following: ○ Host - the host name of the MySQL Server. If SSH and MySQL Servers are on the same machine, the host name can be ​localhost​. ○ Port​ - the TCP/IP port to connect to the MySQL Server. By default, it is 3306. ○ User​ - the user of the MySQL Server. ○ Password​ - the password of the user account on the MySQL Server. 6. Specify the default database of the MySQL Server. To see all databases of the selected MySQL Server in the ​Database Explorer window, select ​Show all databases​, otherwise you will see only the selected one. 7. (Optional) To test the created connection, click the ​Test Connection​ button.42 8. Click ​OK​ to establish the database connection. Tip:​ To see in the Database Explorer a more appropriate name for the created connection, change the default connection name. By default, it is generated like this: selected database.MySQL Server host. You can find detailed information about ​Setting Up SSH Server at our documentation center: ​https://docs.devart.com/studio-for-mysql/ Creating SSL Connection 1. Open the ​Database Connection Properties​ dialog box by any of these ways: ○ On the ​Database​ menu, select ​New Connection​. ○ Click the ​New Connection​ button on the ​Database Explorer​ toolbar ○ Right-click in the ​Database Explorer window and select ​New Connection on the menu. Note: ​The Database Explorer is not available in the dbForge Data Compare for MySQL and dbForge Schema Compare for MySQL. 2. Switch to the ​Security​ tab, check ​Use security protocol​ and select ​Use SSL​. 3. Specify the location of the ​client key​, ​client certificate​, and ​authority certificate in the corresponding fields. If you have no certificates, see ​Generating Certificates for43 SSL Server and Client​ topic for more information. 4. Optionally, you can specify the required cipher or select all available ones. All ciphers are checked by default. The following ciphers are allowed in dbForge Studio for MySQL: ○ DES(40) ○ DES(56) ○ 3DES(168) ○ AES(128) ○ AES(256) ○ RC4(40) ○ RC4(128) ○ RC2(40) 5. On the ​General​ tab, set login information required to connect to the MySQL Server. ○ Host​ - the name or ip address of the MySQL Server host. ○ Port​ - the TCP/IP port to connect to the MySQL Server. By default, it is 3306. ○ User​ - the name of the user account on the MySQL Server. ○ Password​ - the password of the user account on the MySQL Server.44 6. Specify the default database of the MySQL Server. To see all databases of the selected MySQL Server in the ​Database Explorer window, select ​Show all databases​, otherwise you will see only the selected one. 7. (Optional) To test the connection, click the ​Test Connection​ button. 8. Click ​OK​ to establish the database connection. You can find detailed information about ​Generating Certificates at our documentation center: ​https://docs.devart.com/studio-for-mysql/ Setting Up HTTP tunneling To set up HTTP tunnel: 1. Check that ​tunnel.php script is successfully uploaded to the web server. If no, see Uploading the Tunneling Script​ topic. 2. Click the ​New Connection button on the ​Database Explorer toolbar to open the Database Connection Properties ​dialog box. Note: ​The Database Explorer is not available in the dbForge Data Compare for MySQL, dbForge Schema Compare for MySQL. 3. Switch to the ​HTTP​ tab, check ​Use HTTP tunnel​. 4. Enter the URL of the tunnel.php script uploaded to the web server. Note that if the web server is located on the port different from default 80, you should enter an URL like this: http://_web_server_name:port/script_location.45 Select ​Keep connection alive to make the web server preserve the created connection open between requests. 5. If the tunneling script is located on the password-protected server, select ​Use Credentials and input login information (user, password) required to connect to the web server. ​Tip: Select the ​Save password checkbox, otherwise while opening the connection, the ​Connect to MySQL Database ​dialog box will appear and dbForge Studio for MySQL will ask you to enter the password again.46 6. If you can not access the web server directly, but only through a proxy server, select Use Proxy​ and specify proxy settings. 7. On the ​General tab set login information required to connect to the MySQL Server. Specify the following: ● Host​ - the host name of the remote MySQL Server located on the web server. ● Port​ - the TCP/IP port to connect to the remote MySQL Server. By default, it is 3306. ● User​ - the name of the user account on the remote MySQL Server. ● Password​ - the password of the user account on the remote MySQL Server.47 8. Specify the default database of the MySQL Server. To see all available databases in the ​Database Explorer window, select ​Show all databases​, otherwise you will see only the selected one. 9. (Optional) To test the created connection, click the ​Test Connection​ button. 10. Click ​OK​ to establish the database connection. Tip: To see in ​Database Explorer a more appropriate name for the created connection, change the default connection name. By default, it is generated like this: selected database.MySQL Server host. You can find detailed information about ​Uploading the Tunneling Script at our documentation center: ​https://docs.devart.com/studio-for-mysql/ Working with Embedded Server Embedded Server is an easy at installation server to be used by applications that do not require multi-user work with data. As an example, Embedded Server can be used for money access machines, automatic cash desks, different electronic facilities and so on. Please refer to ​MySQL Reference Manual for more details about Embedded Server’s features, usage, and licensing. Limitations Here are known restrictions of current Embedded Server version. All of them are caused by the server itself, those are not a problem of dbForge Studio. For troubleshooting tips, refer to MySQL documentation. ● Simultaneous usage of several copies of Embedded Server at the same machine can cause unexpected errors. ● Running Embedded and common Servers on the same machine can cause troubles. Simultaneous access to the same data from different instances of MySQL Server (for example, from MySQL Server and Embedded Server) can be a reason for data loss. ● Switching between Embedded MySQL Server and MySQL Server in case of using InnoDB also can cause unexpected problems.48 ● Your application may suddenly shut down if you have wrong configuration for Embedded Server. The same thing may happen because of some errors inside the Server. To install Embedded Server: 1. Download the server installation package from MySQL AB web site (​http://dev.mysql.com/downloads/mysql​). 2. Install the server. 3. Create a directory for your Embedded Server. This will be your ​basedir​ directory. 4. Make sure you have libmysqld.dll available through PATH environment variable (for example, place it to your Windows\System32 directory), or place it to a directory, where dbForge Studio is installed. 5. Copy ​share​ folder from the MySQL server installation to the basedir. 6. Create a folder for data inside the basedir directory. This is your ​datadir ​directory. You can copy here ​mysql ​folder from your Server installation. 7. Now, you can use MySQL Embedded Server. To configure the Embedded Server: Perform either of these ways to configure the Embedded Server before using it: ● Use ​my.ini​ file ● Specify data as parameters in connection string To use ​my.ini file, you have to create it at the root Windows folder and fill its following sections: [Server] - common settings for MySQL usual and Embedded servers.    [Embedded] - settings specific to Embedded server. Tip:​ Find the details about the list of settings in MySQL Reference Manual. Minimal ​my.ini​ example: [embedded]  basedir=c:/servers/embedded/  datadir=c:/servers/embedded/data/49 According to this configuration, the contents of the ​share folder can be found in the c:\servers\embedded\share\ path, data is located in the c:\servers\embedded\data\ folder. Notice the use of forward slashes instead of backslashes. To connect to the Embedded Server: 1. Open the ​Database Connection Properties​ dialog box by any of these ways: ● On the ​Database​ menu, select ​New Connection ● Click the ​New Connection​ button on the ​Database Explorer​ toolbar ● Right-click in the ​Database Explorer window and select ​New Connection on the menu Note: ​The Database Explorer is not available in the dbForge Data Compare for MySQL and dbForge Schema Compare for MySQL. Switch to the ​Embedded​ tab of the ​Database Connection Properties​ dialog box. 1. Check ​Use embedded server​. 2. Clear the ​Use my.ini file checkbox and fill ​Base directory and ​Data directory fields if you want to set these directories manually and don’t want to use my.ini file. 3. Click ​OK​ to establish the database connection.50 Viewing Database Schema Information After database connection is created and opened, you can explore database schema hierarchy by expanding tree nodes. ​Database Explorer allows you to view, edit, create and drop database objects. To see what actions are available for the specific node, right-click it to display shortcut menu. As other users modify the database, you can refresh the list of database objects displayed in ​Database Explorer​ and their properties by clicking the ​Refresh​ button. To rename a database object quickly, focus on it in ​Database Explorer and press the ​F2 key or click it again after short pause. The following node types are available for MySQL server connection: ● Database ● Table ● Column ● Index ● Key ● Function ● View (in MySQL v5.0.1 and higher) ● Stored Procedure (in MySQL v5.0 and higher) ● Stored Function (in MySQL v5.0 and higher) ● Stored procedure or function parameter (in MySQL v5.0 and higher) ● Trigger (in MySQL v5.0.13 and higher) ● Event (in MySQL 5.1.11 and higher)51 You can view database object properties in the ​Properties window. Select ​Properties on the ​View​ menu or press ​F4​. To view additional information about the selected schema in Database Explorer, open the Object Viewer window by selecting Object Viewer on the View menu. You can double-click tables, views, procedures, functions, triggers, events folders in the Database Explorer and the Object Viewer will open automatically.52 3. Writing and Executing SQL Statements SQL Document Overview Most of the time you interact with a database by writing and executing SQL queries. In this way, SQL document is an essential part of every database tool in dbForge product line. It is a place where you create SQL statements in order to run them against a database server. This topic gives you an overview of SQL document layout, views, and functions. To create a new SQL Document: On the Standard toolbar, click New SQL. The following image is an example of the SQL document. Note: ​Actual document may look slightly differently, depending on the product you use. SQL Document Overview Each SQL document includes different representations of the content. They are called document views. On the screenshot above you can see two document views: Text and Data. ● Text view contains SQL editor which allows you to write SQL statements. ● Data view, typically shown after a query is executed, displays a data editor with result sets returned by a database server.53 To switch to a specific view: ● Click a button with a view name on the bottom-panel of the document. ● Right-click document bottom-panel to open the shortcut menu and select a view from the list. Representing Query Execution Results The query execution results can be represented in two ways - in a text and grid formats. To represent results as a text, on the SQL toolbar, click the ​Results as a Text button before query execution. The following image represents a sample of query execution results in text format. To represent results as a grid, on the SQL toolbar, click the ​Results as a Grid button before query execution. The following image represents a sample of query execution results in a grid format.54 Document Layout SQL document has two types of view layout: Split Layout and Combined Layout. To switch document layout use appropriate buttons at the bottom-right corner of the document. Split Layout In the split layout a document has one prominent (main) view which is aligned to the top of the document view port. Other views are called additional, they are displayed in the bottom area of the document view port. Main view remains always in split layout visible and you can toggle only additional views. But you can see two views at a time to avoid unnecessary switching. To hide additional document views, click the Hide/Show additional document views button at the bottom-right corner of the document, or just press CTRL+R. To change the main document view, use the Swap main and additional documents views button to swap the main view and active additional view. Combined Layout In the combined layout you can see only one view at a time. A document view fills entire document view port. Typically, you need this layout when you a focusing on one specific document representation (for example, when you extensively work with data). SQL Statements: Writing and Executing Writing SQL Statements Every SQL document contains the ​Text view with SQL editor in it. You switch to this view to edit your SQL queries. When you type text in SQL editor, it automatically recognizes and highlights SQL syntax. For ease of SQL writing and reviewing, you can also use such supported shortcut keys as: CTRL+PLUS (+) KEY (to zoom the query in), CTRL+MINUS (-) KEY (to zoom the query out), CTRL+0 (to return an SQL document to the one-to-one scale). You write and execute SQL queries not only one at a time but you may as well compose SQL scripts. SQL scripts consist of multiple SQL statements separated by the appropriate statement delimiter.55 Executing SQL Statements Running Entire Script To execute the entire script, click the ​Execute​ button on the ​SQL​ toolbar. Alternatively, on the ​SQL​ menu, select the ​Execute​ command. Running Selected Block of Text To execute an arbitrary text block: 1. Select the text block using the mouse or keyboard. 2. Run the ​Execute command as when executing the entire script. Alternatively, select Execute Selection​ on the shortcut menu of SQL Editor Running Current Statement To execute current statement, click the ​Execute Current Statement button on the SQL toolbar. Alternatively, right-click a required statement, and select ​Execute Current Statement command on the shortcut menu. Execute to Cursor To execute script to the current position of the cursor: 1. Move cursor to the required position. 2. Click ​Execute to Cursor​ on the shortcut menu or press ​Ctrl+Shift+F10​. How to Stop Execution To stop SQL execution, click ​Stop Execution​ on the ​SQL​ toolbar. Representing Query Execution Results The query execution results can be represented in two ways - in a text and grid formats. To represent results as a text, click ​Results as a Text on the SQL toolbar before query execution. To represent results as a grid, click ​Results as a Grid on the SQL toolbar before query execution. You can find detailed information about ​Statement Expansion​ ​at our documentation center: ​https://docs.devart.com/studio-for-mysql Viewing Server Messages and Errors When you run a query against a database server, it returns a response to the client. There are three types of server response to an SQL query:56 ● Tabular data ● Status (info) message ● Error Tabular data is displayed in the data editor. Status messages and errors are logged to the Output window. Errors are not only logged to the ​Output window, but are also shown in the Error List​ window. Using Output Window The following image shows the general view of the Output window. Note: ​Depending on the product, the Output window appearance may vary. Output Panes The window can contain up to three output panes (depending on the specific product): ● The ​General pane contains most output messages, not only from a database server, but from the application itself. ● The ​SQL Log pane displays all SQL queries executed by the application during the current session. To see these queries, select the ​Write queries sent by the program to SQL Log​ option in the ​Output, Environment, Options ​dialog box. ● The ​Debug​ pane contains messages sent by a stored procedure debugger. As you can see from the previous screenshot, server errors are also logged to the ​Output window. You can navigate to the place of an error by double-click or from the shortcut menu. Searching Output Messages57 You can search text in Output window. To find specific text: 1. Focus the ​Output​ window. 2. Open the ​Find and Replace​ window by pressing ​CTRL+F​. 3. Enter search text and press the ​Enter key on the keyboard or click the ​Search button. Using Error List Window When something goes wrong, typically you see an error. If this is a user or database server error, such as mistyped query, in most cases you will see it in the ​Error List​ window. Below is an example of an error displayed in ​Error List​. Error List shows not only errors, but also warnings and informational messages which require user attention. Actually, warning is a non-blocking error. And informational message is just a user notification. You can filter messages by type using appropriate buttons on the toolbar of the ​Error List window. You can navigate to the place of an error by double-clicking it in the list or by selecting ​Go to Error​ on the shortcut menu. How to View Errors from the Current Document Only Error List aggregates all error messages in the application. And if you have multiple open documents with errors, the error processing can quickly become awkward. But the window has one more filter button - ​In Active Document​. This button allows to view only those errors that are located in the current document window.58 Using Parameters What is a Parameter? Parameter is a placeholder for a variable that contains a value of some type that is passed to a database server along with the SQL text at the query execution time. Also, parameter can hold values returned by a server after a query or stored procedure execution. You can benefit from using parameters in the following situations: ● When you execute a query multiple times with different input values ● When you debug a query from your application code. Adding Parameters to a Query Text There are two types of parameters, that you can add to a query text: ● Named parameters ● Unnamed parameters. Named Parameters Unnamed Parameters Parameters are declared using ​: or ​@ prefix Unnamed parameters can be specified as ​? followed by the name of the parameter. symbol. For example: For example: SELECT  SELECT  employee.*  employee.*  FROM  FROM  hr.employee  hr.employee  WHERE  WHERE  employee.title = :job AND  employee.title = ? AND  employee.vacation_hours <= :level  employee.vacation_hours <= ?  :job and ​:level are parameters in this query.59 Modifying Parameter Values and Types When you run a query that contains parameters with empty values, you will automatically be prompted to initialize them. To set parameter values and types, click ​Edit Parameters on the SQL toolbar, or main menu. In the ​Edit Parameters​ dialog box, set parameter type, value, and other properties. Note: ​When you execute a query with preset parameter values, you will not be automatically prompted to edit them. To edit parameters, press ​F8​ or select ​Edit Parameters​ on the ​SQL​ toolbar. Query Execution History The ​Execution History window allows you to view, search, and edit the executed queries. To open the window, click on the SQL toolbar. Alternatively, point to ​Other Windows on the ​View​ menu, and then click ​Execution History​. The window looks in the following way: Setting Up Execution History behavior To set up behavior of execution history: 1. On the ​Tools​ menu, click ​Options​. The Options dialog box opens. 2. Navigate to ​Environment, Documents, Execution History​.60 3. To enable storing query execution history, select the checkbox next to the ​Store executed queries within option, and set the number of days to store executed queries. 4. Optionally, modify the path to store the execution history database, maximum script size to store, and timeframe for default output of execution history. Filtering Execution History To quickly find the required query, you can: ● Type the search string into the Search box and specify query execution time frame. ● Use sorting, grouping and filtering.61 Editing Query To edit an executed query, select it in the list and click ​Edit Query in the ​Query DDL section of the window. The query will be opened in a new SQL document. Executing Large Scripts dbForge Studio allows you to execute large scripts without opening them in the SQL editor and loading the whole script in memory. When you try to open a large script, you will be prompted to execute it with help of the Execute Script Wizard. To execute a large script, do the following: 1. To open the Execute Script Wizard, go to the Database menu, and click Execute Large Script. 2. Click the ​Connection field to select a connection to a required database server against which you want to execute your script. In the drop-down list of the field, you can click the ​Manage… link to open the ​Connection Manager dialog box. It will help you create a connection if necessary. 3. Click the Database drop-down list to select a required from the list of those databases that are available on the selected database server. 4. In the ​File name​ box, specify the path to the script you want to execute. 5. In the SQL file encoding field, check that the file encoding is selected correctly. The program automatically selects it basing on the script, if it fails, the default encoding (the current Windows encoding on the machine where the program is running) is selected. You can always select a required encoding from the drop-down list.62 6. Click Execute to execute the script. Tip:​ See all script execution messages in the Output window. Opening Large Scripts in SQL Editor To avoid performance slowdown, some features are disabled when working with large SQL scripts. If script size exceeds 10 Mb, the SQL syntax highlight is disabled. It is not enabled when you reduce the script in the editor. You should reopen script to re-enable the feature. Note: ​Maximum file size the application can open depends on available memory on your computer. SQL Document Shortcut Keys Icon Command Shortcut Keys Action Where is Name used Execute F5 Execute query associated SQL Editor, with current document Query Builder Execute Current F8 Execute current statement SQL Editor Statement Execution History CTRL+ALT+H Opens the Execution SQL Editor History window Edit Parameters CTRL+SHIFT+P Edit Parameters SQL Editor List Members CTRL+J List members SQL Editor List Members CTRL+K, L List members SQL Editor Complete Word CTRL+SPACE Complete Word SQL Editor Complete Word CTRL+K, W Complete Word SQL Editor63 Complete Word Alt+RIGHT ARROW Complete Word SQL Editor Parameter Info CTRL+SHIFT+SPACE Parameter information SQL Editor Parameter Info CTRL+K, P Parameter information SQL Editor Insert Snippet CTRL+K, X Insert Snippet SQL Editor Insert Template CTRL+K, CTRL+X Insert code template SQL Editor Quick Info CTRL+K, I Display quick information SQL Editor Quick Info CTRL+K, CTRL+I Display quick information SQL Editor Comment CTRL+E, C Comment selection SQL Editor Selection Uncomment CTRL+E, U Uncomment selection SQL Editor Selection Toggle Outlining CTRL+M, M Toggle outlining expansion SQL Editor Expansion Toggle All Outlining CTRL+M, L Toggle all outlining SQL Editor Stop Outlining CTRL+M, P Stop outlining SQL Editor CTRL+F2 Switch to Navigation Bar SQL Editor CTRL+ALT+PGUP Switch from the current SQL Editor document tab to the previous tab CTRL+ALT+PG DOWN Switch form the current SQL Editor document tab to the next tab Go To Definition F12 Go to definition SQL Editor Jump Between Ctrl+F12 Jump Between Syntax SQL Editor Syntax Pairs Pairs CTRL+R Hide/Show additional SQL Editor document views CTRL+L Delete current code line SQL Editor and copy it to clipboard64 Working with Code Snippets Code snippets help boost SQL code typing. You can use predefined snippets provided in the application and create your own to insert them in SQL scripts and queries. dbForge Studio code snippets are XML files with the .snippet file name extension. The ​Code Snippets window contains a rich collection of templates organized in folders. In this window, you can arrange and preview snippets. To view a snippet, select it in the window. If the preview pane is not visible, right-click a snippet and select ​Toggle preview​ on the menu. Inserting Code Snippets 1. Click ​Code Snippets​ on the ​View​ menu. The Code Snippets window opens. 2. In the ​Code Snippets window, choose the required template and drag it to the SQL editor. 3. Specify variable names and parameter values in the lines marked green, and the press ​ENTER​ to complete code fragment editing. Alternatively 1. Click ​Insert Snippet​ on the SQL Editor shortcut menu. 2. Select the required template. 3. Use the ​UP ARROW​ and ​DOWN ARROW​ keys to select the item in the snippets list. 4. Specify variable names and parameter values in the lines marked green and press ENTER​ to complete code fragment editing. Code snippets can also be inserted by entering template shortcut word or words and pressing the ​TAB​ key. Note: ​If you want to include a code fragment into the inserted template, select it with the mouse pointer before placing snippet to the editor. If the snippet does not provide surrounding, selected text will be deleted. To move the snippet to the root folder of the ​Code Snippets window, drag it on the one that already is in the root folder or to the upper border of the ​Code Snippets​ window. Creating Code Snippets 1. Click ​Snippets Manager​ on the ​Tools​ menu. The Snippets Manager window opens. 2. Click ​New Snippet​ on the manager toolbar. 3. In the ​Text​ field, enter the text of the snippet.65 4. Specify a shortcut and snippet description in the corresponding ​Shortcut and Description​ fields. Note: ​The Location field is read-only and is filled automatically when you choose the folder available in the list of snippets. The shortcut (if any) will be taken as snippet name. If there is no shortcut specified, the new snippet will be named Snippet. To change the snippet name, double click it in the list of snippets and enter the needed name. 5. Click Save on the toolbar. Editing Code Snippets 1. Click ​Snippets Manager​ on the ​Tools​ menu. The Snippets Manager window opens. 2. Select the required snippet from the list of available snippets. 3. Modify the snippet description, shortcut, and text in the corresponding fields. 4. Click the ​Save​ button on the manager toolbar. 5. To change the snippet name, double click it in the list of available snippets. Default Snippets Location By default, all snippets are stored in a folder that is located at C:\ProgramData\Devart\dbForge Studio for MySQL\Snippets. You can change the default location when editing an existing snippet or creating a new one by setting a new path in the Location field of Snippets Manager. You can find detailed information about ​Working with Literals at our documentation center: ​https://docs.devart.com/studio-for-mysql/ You can find detailed information about ​Phrase Completion at our documentation center: ​https://docs.devart.com/studio-for-mysql/ You can find detailed information about ​Executing Scripts through the Command Line ​and ​Formatting Code through the Command Line at our documentation center: ​https://docs.devart.com/studio-for-mysql/66 4. Building Queries with Query Builder Query Builder is designed to enhance productivity and simplify SQL query building tasks. Query Builder provides a graphical user interface for creating SQL queries. You can drag-and-drop multiple tables, views and their columns onto a visual designer to generate SQL statements. You can use Query Builder to perform the following tasks: Working with a graphical representation of a query or with SQL code Query Builder includes a pane that displays your query graphically and a pane that displays the SQL text of your query. You can work in either the graphical pane or the text pane. Query Builder synchronizes the views so that they are always current. Joining related tables If you add more than one table to your query, Query Builder automatically determines how the tables are related and constructs the appropriate join command. Querying or updating databases You can use Query Builder to return data by using SELECT statements and to create queries that update, add, or delete records in a database. Viewing and editing results immediately You can run your query and work with a recordset in a grid that allows you to scroll through and edit records in the database. Adding Tables to Query The first step in building a ​SELECT query is to decide which tables and views you need to add to SELECT list. To add tables and views to the query, simply drag-and-drop them from the ​Database Explorer​ tree to the query diagram. You can also select required tables in Database Explorer, right-click the selected tables, and then click ​Send To​, and then click ​Query Builder​. Table shapes with the lists of table columns appear. Select the columns to include them into the ​SELECT ​list. Check ​All Columns​ option if you want to select all the fields in a table.67 After you have added tables to the query diagram, you can use the ​Selection tab of the tabbed editor to adjust the query to your needs. Note: ​A connection you use while dragging a table must be the connection of the query document. You will not be able to perform a drag-and-drop operation from another connection. Note: ​The appearance may slightly vary depending on the product you use. You can add to the diagram not only tables, but also views. They are displayed as shapes with the list of columns which you can mark to include in the SELECT​ list.68 Removing Tables from Query To remove a table or a table-structured object ● In the ​Diagram Pane, select a table, view, function, synonym, or query, and then press ​DELETE​, or right-click the object and then click ​Remove from the diagram. You can select and remove multiple objects at one time. Alternatively ● Remove all references to the object in the SQL editor. When you remove a table or a table-valued object, the Query Builder automatically removes joins that involve that table or table-valued object and removes references to the object’s columns in the SQL editor. Note: ​Removing a table or a table-valued object does not delete anything from the database, it only removes it from the current query. You can find detailed information about ​Including Columns in Query and Making Joins Between Tables at our documentation center: https://docs.devart.com/studio-for-mysql/ Building WHERE or HAVING Clause When retrieving data, you may want to set up a filtering expression or relation between tables. You can do it using WHERE and HAVING clauses. Refer to SQL manual to learn the differences in these sections. In dbForge Studio visual interface for ​WHERE and ​HAVING clauses is the same. In the tabbed editor conditions are displayed as a tree with condition and group nodes. A group consists of several conditions or subgroups, united with a logical operator. You can change a group logical operator by clicking it and selecting another one from the menu. To navigate between conditions and groups, use the UP ARROW and DOWN ARROW keys. Use the LEFT ARROW and RIGHT ARROW keys to navigate between criteria operator and operands.69 To create a new group, do one of the following: ● Click the upper-level group operator and choose ​Add Group on the menu that appears. ● Press ​CTRL+PLUS SIGN​. Building WHERE and HAVING Clause To create a new condition, do the following: 1. Navigate to the group you want to enhance and click the button or press ​INSERT or ​PLUS SIGN key. Alternatively, click the upper-level group operator and choose Add Condition​ on the appeared menu. 2. Click the condition column and select the column you need from the appeared menu. 3. Click the condition criteria operator and select the criteria operator you want from the list. 4. If the condition operator requires operand value(s), specify it (them). To remove a condition, click the condition button or navigate to condition and press DELETE or ​MINUS SIGN key. To remove a group click its logical operator and choose Remove Group ​from the appeared menu or navigate to the group and press ​DELETE or MINUS SIGN​ key. You also can copy, cut, and paste conditions and groups with ​CTRL+C​, ​CTRL+X​, and CTRL+V​ keys. The following clause is generated according to the sample image: WHERE  Product.Color = ''silver'' AND  ProductCategory.Name >= ''components''  You can find detailed information about ​Building Queries with Query Builder at our documentation center: ​https://docs.devart.com/studio-for-mysql/70 5. Working with Data in Data Editor Viewing Data in Grid Overview The Data Editor allows you to view and quickly edit data right in the grid. To Access Data ● Execute a query that returns data. ● Open the table or view editor and switch to the data view. ● Right-click the table or view in the Database Explorer and choose Retrieve Data from the shortcut menu. ● Double-click a table or view in Database Explorer and switch to the Data view of the Object Viewer. In ​Query Builder​, right-click the table or view in the ​Data Editor and select ​Data View​ on the shortcut menu. ● Drag the table or view from the database explorer to the ​Data Window​. Use the rich set of data management options on the shortcut menu. Note: ​Actual document may look slightly differently, depending on the product you use. Note: ​If you want to retrieve all data from a table with a large number of records, a message warning you about the possibility of the ​Out of memory exception is shown, and you can choose either of the following actions - continue retrieving data, cancel retrieving data, or turn on Paginal Mode.71 To simplify data analysis, you can use the following features: ● Grouping Data in Grid ● Sorting Data in Grid ● Filtering Data in Grid ● Paginal Mode ● Card View ● Auto-search Mode ● Data Viewer Note: ​By default, Paginal Mode is enabled in data view with 1000 records per page, so only 1000 records are displayed at once. You can change columns order and width, group by order via dragging column headers into required place. You can fit the column’s width to the column content by right-clicking on an appropriate header and choosing Best Fit from shortcut menu. To adjust widths of all columns at once, choose ​Best Fit (all columns) instead. Note that this operation can take some time when there are many records in the grid. To refresh data, click the ​Refresh command in the ​Data menu (for Data view), the shortcut menu, or on the toolbar. To abort the refresh operation, that takes much time, click the ​Stop Refresh​ command on the ​Data​ menu (for Data view) or on the toolbar. Grid rows’ background and foreground colors can be set. You can set them separately for odd and even rows, focused row, selected rows, focused cell, group rows. Data row can be viewed, edited and printed as cards. You can learn more about it at the Card View​ section of our documentation center. You can also export data from the grid, using the ​Export Data button on the toolbar or selecting a corresponding option from the shortcut menu.72 Tip: You can use down arrow to add an empty row to the table displayed in the grid. If you’ve added an empty row accidentally, you can easily delete it using the up arrow. Note: ​Null values 0000-00-00 in fields of DATE data type are displayed as 01.01.0001. Null values of TIMESTAMP fields are displayed as 01.01.0001 0:00:00. You can find detailed information about ​Sorting Data​, ​Grouping Data​, and Filtering Data in Grid at our documentation center: https://docs.devart.com/studio-for-mysql/ Server-Side and Client-Side Sorting and Filtering In dbForge Studio, sorting and filtering of data can be performed both on the client or on the server side. If there are few records in a table, they are all shown in the result grid after retrieving data from a table or after executing a ​SELECT statement. If you want to sort these records or use a filter, the filtering and sorting procedures are performed on the client side and all available records are processed. But if there are many records in a table, they are not displayed after executing a ​SELECT statement or retrieving data at once, because such action can cause hanging of the client machine or some other problems associated with too much data to operate. The other case when not all records are displayed in the grid, is when Paginal Mode is on and there are more records than the number of records displayed on one page. In these cases, if you perform client-side sorting or filtering, only records available on the client machine are processed. That means you will not get a complete result of filtering or sorting. That’s why dbForge Studio performs server-side filtering or sorting when not all records can be displayed in the grid. When server-side filtering or sorting is performed, the application sends a query that is a modification of the starting query to the server. The shortcoming of this way is that server resources are taken, but the benefit is that a user will get a complete result of sorting or filtering.73 But once all records are received, for example, after pressing ​CTRL+END or scrolling to the end, server-side filtering and sorting is turned off and client-side filtering and sorting is used. You can find detailed information about ​Paginal Mode​, ​Auto-Search Mode​, ​and ​Card View​ at our documentation center: https://docs.devart.com/studio-for-mysql/ Editing Data in Grid Overview When dbForge Studio for MySQL executes any statement returning a result set, the retrieved data is displayed in the Data Editor of the data view of the same SQL document or the Object Viewer. This topic shows how to edit the data in a grid; for information about customizing data grid, as well as sorting, grouping and filtering, visit our documentation center,​ ​Viewing Data in Grid​ section. To edit data: 1. In the ​Data Editor​, available in the data view of an SQL document, click a cell you want to edit. 2. After editing, confirm the changes by pressing the ​End Edit button under the grid or selecting the ​End Edit​ option from the shortcut menu. Tip: To discard changes, press the ​Cancel Edit button or select the Cancel Edit option from the shortcut menu. In dbForge Studio for MySQL, you can also edit data right after retrieving it from a table or in the data view after any query was executed. To retrieve data, select ​Retrieve Data on the shortcut menu of the table in ​Database Explorer​. To make received data editable, you can also choose the table name from the Table drop-down list on the toolbar, or leave it read-only by selecting (read-only). If you choose the read-only mode, you will gain in the performance aspect, as the application will work faster in this mode.74 To edit data in join query results, you should set the table you want to edit data from to the editable mode, as described above. After you set one table as editable, the data of other tables included into the result will become light-gray. Note: ​When you change the mode of any table from read-only to editable or vice versa, a balloon with the corresponding message is shown on the status bar. Note: ​To view values of CHAR and VARCHAR fields you should take into account the following points: 1. If length >= 200 is stated in the column type, buttons will be shown in the grid. Click these buttons, and the editor will be shown in a separate window. 2. If there are more than 4000 symbols in a cell, data can not be edited directly in the cell. To edit data in such cells, you should click the […] button. To insert, delete, copy and paste data: Use the shortcut menu or the corresponding buttons under the grid. For example, you can do the following: ● To insert ​NULL (instead of empty string), right-click ​Set Value To​, and then select Null on the shortcut menu. You can also use the ​CTRL+0​ key combination. Tip:​ Use Set Value To menu to quickly set an empty string, zero, or a current date.75 To insert SET and ENUM data, dbForge Studio offers convenient editors. Click a field of ENUM data type and select a required value from the drop-down list. Click a field of SET data type and tick off a required value (or values) in the drop-down editor. ● To add a new record, select the ​Append option from the shortcut menu or click the Append button under the grid. To delete a record from the grid, select the ​Delete option on the shortcut menu or click the ​- button under the grid or press ​CTRL+DEL keys. ● To copy and paste cell values, use the corresponding options on the shortcut menu. To select data: You can easily select and copy the data just like cells in a spreadsheet. Perform either of these actions: ● Move the mouse pointer across the grid holding the left mouse button ● Click the first cell of the data range, press ​SHIFT​, and, holding the ​SHIFT key, click the last cell. A rectangular range of cells will be selected.76 Tip:​ Using the ​CTRL​ key, you may include and exclude cells to selection. Any number of rows may be copied and pasted to some other application, for example, a spreadsheet, but when you paste data in data grid, only the first row will be pasted. Note: ​Actual document may look slightly differently depending on the product you use. Row and cell indicators When working with grid, you can see special indicators near to the focused cell. These indicators reflect current editing state. The row is focused. The row is being edited. The row has been edited. Incorrect value was entered into a cell. You must either fix the value or press the ESCAPE​ key to cancel changes made to the cell. You can find detailed information about ​Inserting and Retrieving Data at our documentation center: ​https://docs.devart.com/studio-for-mysql/77 6. Working with Data Generator It is recommended to backup the database that you are going to populate before you generate the data. You can adjust the settings and repeat the data generation if you are not satisfied with the results. To generate data, first select a MySQL server instance and a database you want to populate. To generate data: 1. On the ​Tools menu, click ​New Data Generation​. The Data Generator wizard will open. 2. Specify the connection and select a required database.78 3. Click ​Next​. The ​Options​ page will appear. 4. On the ​Options​ page, set required options. 5. Click ​Execute​. The following page displays data generation result. You can specify the tables that you want to populate by selecting the checkbox that is located next to the table name. Further, you can define how you want the data to be79 generated: click the table name in the tree view and specify the details in the settings pane. All the changes are displayed in real-time. Note: ​There may be errors during data generation. For example, a column may be a foreign key of a table, that is not included into generation. To resolve such issues, navigate to the ​Related Tables section of the Table generation settings, and include/exclude parent and child tables from data generation. 1. On the ​Data Generator​ toolbar, click ​Generate Data​. The ​Output​ window will open, where you can: ● select the ​Open data population script in the internal editor option to add custom data to the database. ● save the SQL script to a file ● run the script automatically Select a required option and click ​Next​. 2. On the ​Options​ tab, configure the synchronization options. Click ​Next​. 3. The ​Summary​ tab allows you to see the details of an error or warning. When you are setting up the tables and columns that you want to populate, SQL Data Generator displays warning and error messages to inform you when there may be a problem with the data generation. 4. Click ​Generate​ to finish the process. You can find detailed information about ​Using Generators ​at our documentation center: ​https://docs.devart.com/studio-for-mysql/ Generating Data for Empty Database The following use case demonstrates the use of dbForge Studio to quickly populate an empty database with meaningful test data. For this purpose, the following database called salesdb was created. The screenshot below graphically demonstrates the structure of the database, including tables, columns, connections, data types, foreign keys, etc.80 To Generate test data: 1. On the ​Standard​ toolbar, click ​New Data Generation​. 2. Specify the connection and select the salesdb database. Click ​Next​. 3. The Options page will appear. On the ​Options page, set required options and click Open​. The following window opens. The window allows you to: ● select columns to be populated (in the Tables and columns to populate pane); ● select a generator you want to use for each separate column (in the Generation settings pane); ● customize parameter settings for each generator (in the Generation settings pane).81 To populate all the columns, select the checkbox next to the salesdb. Alternatively, you can specify the tables that you want to populate by selecting the checkboxes next to the table names. In addition, dbForge Studio provides the ​Preview window, that shows a table with the generated data. Selecting generators for individual columns For a column you select, dbForge Studio automatically assigns a generator based on the column and table characteristics: name, data type, etc. You may select a different generator for the column and customize the parameters of this generator in the settings pane. Below are several examples of how generators are assigned to specific columns, as well as how they may be customized. First Name column Select the ​First Name column. The ​First Name (any gender) generator is automatically assigned to this column. The generator provides realistic male first names. In the text box, you can see a predefined regexp template to generate names. The template may be customized by inserting various snippets, counters, files, and columns. Middle Initial column Select the ​Middle Initial column. The ​Middle Name generator is automatically assigned to this column. The generator provides a wide range of middle names including both the full names and initials. The values to be generated may be customized using regular expressions. For example, if you want only one-character initials to be generated, specify82 the [A-Z] regular expression in the text box of the settings pane. The values may also be adjusted by inserting predefined regexp snippets, counters, files, and columns. Twitter column Select the ​Twitter ​column. The ​Twitter generator is automatically assigned to this column. The generator yields a great number of twitter user names. The user names will be generated with an at sign (@) at the beginning of the names. To customize the names (e.g. include uppercase letters, numbers, underscore characters, etc), use regular expressions. The names may be further adjusted by inserting predefined regexp snippets, counters, files, and columns. SSN column Select the ​SSN ​column. The ​US Social Security Number (SSN) generator is automatically assigned to this column. The generator will populate the column with random numbers. You can customize the numbers in the text box using various regular expressions or by inserting regexp snippets, counters, files, and columns.83 For example, with the help of regexp snippets, you can add or generate lowercase and uppercase letters, hexadecimal characters, digits without a zero. You can also specify groups, alternatives, repeats, etc. of the generated values. With the help of the counter, you can get a sequence of digits in increment/decrement order, sequence of characters in increment/decrement order, and hexadecimal characters in increment/decrement order. Shipping Method column Select the ​Shipping Method column. The ​Shipping Method generator is automatically assigned to this column. The generator provides a wide range of real world shipping methods, such as USPS Express Mail, FedEx, DHL, Airmail Priority, etc. The list of all shipping methods is visible in the text box of the settings pane. You can customize this list manually or by adding regexp snippets, counters, columns, files from the drop-down lists below the text box.84 Preview window While selecting tables and customizing generators, the ​Preview window displays data to be generated in real-time. Populating Database To Populate Database: On the ​Data Generator toolbar​, click the ​Populate data to the target database…​ button.85 The ​Data Population Wizard​ opens, where you can: ● select the Open data population script in the internal editor option to add custom data to the database ● save the SQL script to a file ● execute the data population script against the database. Select the required option and click ​Next​. On the ​Options tab​, configure the synchronization options. Click ​Next​.86 The ​Summary ​tab allows you to see the data population action plans and warnings. Besides, when you are setting up the tables and columns that you want to populate, dbForge Studio displays warning and error messages to inform you when there may be a problem with data generation. Click ​Generate ​to finish the process. You can find detailed information about ​Creating Custom Generators​, Generating Data through the Command Line​, and ​Exit Codes Used in Command Line for /generatedata ​at our documentation center: https://docs.devart.com/studio-for-mysql/87 7. Exporting and Importing Data Data Export Overview With dbForge Studio you can easily export data from one or several tables to the following file formats: ● HTML (Hypertext Markup Language) - a data storage format used for the web pages. ● Text - plain text format. ● XLS - Microsoft Excel spreadsheet. ● XLSX - Microsoft Excel 2007 spreadsheet. ● Google Spreadsheet. ● MS Access - database of well-known Microsoft DBMS Access (2003, 2007+). Note: ​If you do not have Microsoft Office installed, you need to install ​Open XML SDK 2.5 for Microsoft Office​ to use the result Microsoft Excel spreadsheet. ● RTF - Rich Text Format from Microsoft. ● PDF (Portable Document Format) - file format, created by Adobe Systems for document exchange. (Only Latin charset data can be exported.) ● JSON - an open-standard format that uses human-readable text to transmit data objects consisting of attribute-value pairs. ● XML (XML Schema Definition) - XML data representation with data contained in attributes. Contains XML schema. ● CSV (Comma-separated values) - another name for the comma-delimited format of data storage. ● ODBC (Open Database Connectivity) - a data storage format used to access any data from any application. ● DBF - a database filename extension (Foxpro, dBase III, dBase IV, dBase 7). Other files can also be connected with DBF files, for example: .DBT (dBase III, IV, Clipper), .FPT (FoxBASE/FoxPro). While exporting data, ANSI charset is used. ● SQL - format of the file that contains a SQL script. Note: ​Express Edition of dbForge Studio supports export only to HTML, CSV and ODBC formats. Note: ​When using text/csv ​ODBC provider, on the ​Export Format page enter a filename with an extension in the ​Destination table name​ field, e.g., table.csv.88 Note: ​When exporting data to MS Excel format through ​ODBC provider, the following export limits exist: ● for export to MS Excel 2003 format - 65536 records ● for export to MS Excel 2007 format - 1 048 576 records Data export to MS Excel (not through ODBC provider) is limited only by available system memory. dbForge Studio gives a simple way to export data both from Database Explorer and the grid. Using Database Explorer, you can select any table for export in seconds. Also you can compose a complex query, execute it, and then select exported data from the grid. dbForge Studio includes the ​Data Export Wizard​ which allows you to select the following: ● columns ● data formats for each column ● rows ● additional options for each exported format. Based on your needs, you can save your selected export options into a template file for the further usage in the wizard. Exporting Data Export data to other formats 1. Open the ​Data Export​ wizard by doing either of these actions: ● Click ​Export Data​ on the ​Data Pump​ tab of ​Start​ Page. ● In ​Database Explorer​, right-click a table or a view (or CTRL+click several tables and/or views) you want to export data from and select ​Export Data on the shortcut menu. ● Right-click data grid and choose ​Export Data on the shortcut menu, or click the Export Data​ button on the ​Data Editor​ window toolbar. ● Click ​Export Data​ on the ​Database​ menu. Note: ​The appearance of the ​Data Export Wizard can differ depending on a product you use. 1. On the ​Export format page, select an export format or load export options from a template file if you saved them previously and click ​Next​. 2. On the ​Source page, select a required MySQL server connection, a database, table(s), view(s), which you want to export, and then click ​Next​.89 Note: ​If you opened the wizard from the data grid, the Source tab would allow to specify only a name and a path of the destination file (the one where the exported data will be saved. 3. Set additional settings for the selected export format on the ​Options wizard page and click ​Next​. 4. Select exported columns and click ​Next​. (For CSV, HTML, PDF, RTF, and Text export formats, select formats of data representation, e.g., integer, date, time, etc. For Excel and Excel 2007 formats, the field is editable only for binary columns and includes only two data types (HexString and String) in the drop-down list). 5. Select exported rows. You can export all rows, only selected in the grid ones, or a specified range. 6. Specify how the application should behave when an error is encountered. 7. (Optional) To export options to a template file for future usage, click the ​Save Template​ button and specify a path and a name of the template file. 8. Define how dbForge Studio for MySQL should handle errors during export and whether to write a log file. 9. Click the ​Export​ button and see the result of export process. 10. Select whether to open the exported file or result folder, and click the ​Finish button. Data export is finished. Filtered data export There is a filter in the ​Data Export​ wizard window.90 If you want to filter data, use the required column filter. To do this, firstly write a whole data query using the ​SELECT * FROM ​statement. Click the ​Execute button to implement the query. Then on the required column name, click the filter icon. In the drop-down list, choose the item to filter by. Right-click the filtered grid, and select ​Export Data to call the Data Export wizard. Another and the most reliable way to export filtered data is to filter it directly in the query. Execute the ​SELECT * FROM ​statement with the ​WHERE condition, where you can specify the required filter. And then implement already filtered data export with the ​Data Export wizard, calling it from the filtered grid shortcut menu. Follow the ​Data Export wizard steps, click the ​Export button. If the data export is finished successfully, the Data export complete message appears. You can find detailed information about ​Data Export​ at our documentation center: ​https://docs.devart.com/studio-for-mysql Data Import Overview Data import is a process of converting data stored in one format into another. dbForge Studio automates this process and offers a wizard to guide you. You will get a correct and painless way to: ● Import data into a table from MS Access (2003, 2007+), CSV, DBF (Foxpro, dBase III, dBase IV, dBase 7), MS Excel, Text, XML formats ● Migrate data from other servers ● Customize the import. One more benefit is that you can optimize your import by saving the import settings as a template for future usage for importing data with the help of the wizard, and get a customized import immediately next time. To import data using the ​Data Import​ wizard, you need to do simple steps:91 1. Select an import format and a file with the data that you want to import. 2. Select a destination table. 3. Adjust data formats for the Source data. 4. Set additional options to customize the import. 5. Set a correspondence between the Source file content and columns of the Target table. 6. Select a data processing mode, e.g., whether to add the records to the Target table or update the existing ones, or both. 7. Define how dbForge Studio should handle errors during import and whether to write a log file. You can find detailed information about ​MS Access Import​, C​ SV Import,​ ​DBF Import​, ​MS Excel Import​, ​Google Sheets Import​, ​Text Import​, ​JSON Import​, and XML Import​ at our documentation center: ​https://docs.devart.com/studio-for-mysql Importing Data to a New Table 1. On the ​Database​ menu, click ​Import Data​. The Data Import wizard opens. 2. Select a required import format and specify a location of Source data. Click ​Next​. 3. Specify a Target MySQL connection and a database, select New table, and enter a name of a table to import the data to. To create or edit MySQL server connections, click the corresponding buttons. Click ​Next​. 4. Specify additional options to customize the import. Click ​Next​. 5. Specify data formats for the Source data and click ​Next​. 6. dbForge Studio will automatically create and map all the columns in a ​Target database to the ​Source columns. See the Target columns in the top and the Source columns at the bottom of the wizard page. To remap columns, click ​Source ​column fields and select required columns from the drop-down list. Note: ​To cancel mapping of all the columns, click ​Clear Mappings on the toolbar. To restore it, click ​Fill Mapping​. 7. You can edit the ​Target column properties by double-clicking them in the top grid. Select the ​Key​ checkbox for a column with a primary key and click ​Next​. 8. Select how dbForge Studio should handle errors during import and whether you want to get a log file with details about the import session. 9. Click ​Import and see the import progress. dbForge Studio will notify you whether the import completed successfully or failed. Click the ​Show log file button to open the log file. 10.Click ​Finish​ to finish the import and close the wizard.92 Note: ​You can save the import settings as a template for future uses. Click the Save Template button on any wizard page to save the selected settings. Next time you should only select a template and specify a location of the Source data - all the settings will be already set. Migrating Data from Other Servers 1. Decide what table to import the data to: For a new table: ○ On the ​Database​ menu, click ​Import Data​. The Data Import wizard opens. 2. For an existing table: ○ Right-click a table in ​Database Explorer and select ​Import Data on the shortcut menu. The Data Import wizard opens with predefined parameters: a MySQL connection, a database, and a table to import the data to. ○ Alternatively, on the ​Database menu, click ​Import Data and select a required table on the ​Destination​ table wizard page. 3. Select ​ODBC​ import format and click ​Next​. 4. Specify ODBC data provider options for import: select a system or a user data source name from the drop-down list or specify a connection string by clicking ​Build and selecting a required data source. Enter a user name and a password if the selected data source requires them. Click ​Next​. 5. Select a ​Source table or view. To quickly find them in the list, enter characters of a required name into the ​Filter field. The list will be filtered to show only those that contain such characters in their names. You can use a query for a partial data import. Click the ​Custom Query​ button and edit the query. Click ​OK​ to save the changes. 6. Specify a MySQL connection, a database, a schema and a table to import the data to. If you selected a table in Database Explorer before opening the Data Import wizard, the wizard will open with the predefined connection parameters of the selected table. To create or edit MySQL connections, click the corresponding buttons. Click ​Next​. 7. Specify data formats for the ​Source​ data and click ​Next​. 8. Map the ​Source columns to the ​Target ones. If you are importing the data into a new table, dbForge Studio will automatically create and map all the columns. If you are importing into an existing table, only columns with the same names will be mapped, the rest should be mapped manually. (If no columns with the same name are found, they are mapped successively - the 1st column in Source with the 1st column in Target, etc.)93 See the Target columns in the top and the Source columns at the bottom of the wizard page. Click Source column fields and select required columns from the drop-down list. Note: ​To cancel mapping of all the columns, click ​Clear Mappings on the toolbar. To restore it, click ​Fill Mapping​. 1. If you are importing to a new table, you can edit the ​Target column properties by double-clicking them in the top grid. Select the ​Key checkbox for a column with a primary key and click Next. Note you should select at least one column with a primary key, otherwise some of import modes on the Modes wizard page will be disabled. 2. Select an import mode to define how dbForge Studio should import the data. Click Next​. 3. Select how dbForge Studio should handle errors during import and whether you want to get a log file with details about the import session. 4. Click ​Import and see the import progress. dbForge Studio will notify you whether the import completed successfully or failed. Click the ​Show log file button to open the log file. 5. Click ​Finish​ to finish the import and close the wizard. Note: ​You can save the import settings as a template for future uses. Click the Save Template button on any wizard page to save the selected settings. Next time you should only select a template and specify a location of the Source data - all the settings will be already set. For more information, go to Saving and Using Templates topic. You can find detailed information about ​Saving and Using Templates During Import​, ​Importing Data through Command Line​, and ​Additional /dataimport Arguments​ at our documentation center: ​https://docs.devart.com/studio-for-mysql94 8. Data Analysis What is a Pivot Table? Data analysis is not always a snap. You can look at the same data from different angles and only some of them can bring valuable information. When dealing with data, one often beats his brains with grouping, sorting, or doing anything else to get required results and grasp the whole picture. Pivot Table is designed exactly to end these miseries and enable you to fisk any obscure data with several mouse clicks. It’s possible to be a data-management guru with Pivot Table. Pivot Table is a tool that converts large amounts of data into compact and informative summaries - pivot tables. You can rearrange (or pivot) your data by a simple drag of a mouse until you get the layout best for understanding the data relations and dependencies. Pivot Table in Use Let’s take the ​Sakila database, retrieve some data for analysis, and create a pivot table to see its benefits. Note: ​As the original Sakila database contains a huge number of table records, they were halved to illustrate the example. Suppose you are interested in monthly and yearly income of the stores based on film rental amounts in each film category. You would like to know which of film categories are more profitable to improve the range of films. The main data is stored in Payment table, other tables are required to get full names of film categories and film titles. To get required data from the tables, you should select the following fields and build a query using the ​Query Builder​: ● title​ from ​Film​ table ● name​ from ​Category​ table ● amount​ and ​payment_date​ from ​Payment​ table95 This is what you should have received after executing the query. The usual grid will display the data the same way. To analyze which film category had more rentals and brough the highest revenue in each particular month, you should sum up and then compare rental amounts of each film category. This way is time-consuming and often followed by errors. Let’s see how data can be displayed in a pivot table. To create a pivot table, you need to open the ​Pivot Table view of the SQL document and drag the required fields from the ​Data Source window (which opens automatically with the list of fields specified in the query) into required areas in a pivot table. The Pivot Table view contains the pivot table areas, so it’s easy to understand how the dragged fields will be displayed. Now, following the tips, let’s drag amount field into the ​Data area, category name and film field to the ​Row​ area, and payment date to the ​Column​ area.96 Note: PivotGrid automatically decomposes any fields of date or time type, so you can see payment date field decomposed into year, month, and day fields. Let’s drag only year and month. Sum of all values as well as grand total values are automatically calculated for each row and column in the pivot table. Now you have the following neat summaries: ● Sums of rentals for each film category by month and by year ● Sums of rentals for each film title by month and by year ● Grand Total values for each film category and each film title by month and by year. You can collapse ​Category​ nodes and easily compare the sums of rentals, that’s all. Creating a Pivot Table You can create pivot tables using ​Pivot Table​ view of the SQL document. Some facts about a pivot table creation: ● You should connect a pivot table to the data you want to display and rearrange in the pivot table. The data source can be a query in the SQL or query document. ● Not all the data source tables are good for converting into a pivot table. The data source should contain a column with duplicated values, which can be grouped, and a97 column with numeric data, which can be used to calculate grand totals and custom totals. ● You should place data source fields from the Data Source view into pivot table areas to add the data to the pivot table. Creating a Pivot Table Let’s create a pivot table using a query file retrieving four three columns from the Sakila database. Note: ​As the original Sakila database contains a huge number of table records, they were cut down to half to illustrate the example. Let’s analyze which film category had more rentals and brought the highest revenue in each particular month. To do this: 1. Open a query document by clicking the ​New Query icon on the ​Standard toolbar to select it as a data source for a pivot table. In ​Database Explorer​, select required tables from the Sakila database and drag-and-drop them to the query document. They are displayed as shapes with columns. In required table shapes, select corresponding checkboxes next to these four columns: category name, film title, payment amount, and payment date. 2. Switch to the ​Pivot Table view to see the pivot table template. The Data Source view opens automatically with the fields (when dealing with pivot tables, data source columns are referred to as fields) specified in our query document.98 Note: ​Any field with date or time data format is decomposed in the ​Data Source view to its components. In this case the payment date field has been decomposed to four fields: year, month, day, and master field. The latter allows you to add the three payment date sub-fields to a pivot table at once. 3. Let’s think how to efficiently place the selected fields in the pivot table: ● Category name and film title field contain string values, some of them are rather long. It will be good to place these field as row field to see their values vertically at the left of the pivot table. In this case you will have the long but tighter table. ● Payment amount field has numeric data, so it is reasonable to place it into data area where grand totals and custom totals can be calculated. ● If Payment date field is displayed as column field, i.e., horizontally in the pivot table, you will be able to group payment amount figures by payment day, month, and year. Let’s add only Month and Year of payment date to simplify the analysis. Note, you can change the layout and display category name and film title ​Category Name field as Column field and payment date as Row field. No limitations, the data can be easily rearranged until it becomes the most readable. To add fields to the pivot table, drag-and-drop them from the ​Data Source view to a required pivot table area or highlight a field in the ​Data Source view, select a required area from the Destination area drop-down list and click the ​Add To​ button or just press ​ENTER​. To add two or more fields to the same pivot table area, add the first field and then drop the second one before or after the first field in the area.99 4. Now our pivot table contains the following: ● category name and film title as row fields ● payment date (Year) and (Month) as column fields ● payment amount as data field ● custom totals and grand totals that are automatically calculated for the added fields The film titles may be hidden by collapsing the category name nodes to define the most profitable category by month or year. The names of employees may be hidden by collapsing the department name. To sort the values in the fields, click the Sort button. You can filter payment amounts, for example, to display only payment amounts in 57. Click the button in the payment date (Year) field and uncheck 2006 in the dialog box. For more information about managing data in the pivot table, refer to Managing a Pivot Table topic.100 5. If required, you can apply conditional styles to the pivot table to better process the data. For example, you may need to quickly see what films brought monthly revenue higher than $40. ○ Click the ​Conditional Styles button on the ​Pivot Table toolbar or right-click the pivot table header and select ​Conditional Styles on the menu. In the opened dialog, click the button to add a new condition. ○ Select payment amount in the list of fields, Greater or Equal in the Condition field, enter 0 in the Value1 field. In the ​Apply to section, clear all the checkboxes except the ​Cell checkbox. In the ​Appearance section, click the BackColor​ field and select the ​LightCyan​ color to highlight the cells. 6. See the result in the pivot table.101 Creating a Chart in Pivot Table In dbForge Studio, you can view pivot data as a chart. Such an option helps to visualize data dependencies and compare it. You can view a chart associated with the needed pivot data directly in the pivot table document, in a special view. Let’s build a chart basing on the data of the pivot table created in the ​Create a Pivot Table section. 1. Open the ​Charts view of the pivot table document by clicking the ​Show Chart button on the pivot table document toolbar. 2. Select the data that should be displayed on the chart on the ​Pivot Table view of the document by pressing ​CTRL and clicking the needed cells of the pivot table or by selecting the cells of the table with the left mouse button pressed: 3. Adjust the Chart options using the ​Chart view toolbar, shortcut menu, or ​Chart Designer​. You can find detailed information about ​Reordering​, ​Filtering​, ​Sorting​, ​and ​Grouping Fields​ ​at​ our documentation center: ​https://docs.devart.com/studio-for-mysql Using Summary Functions to Calculate Custom Totals Pivot table offers you a number of summary functions that you can apply to required column or row fields to calculate custom totals for their values. Custom totals are displayed in additional rows or columns highlighted with the grey color.102 The summary functions are applied only to those fields that summarize the data of another field, for example, data of the category name field summarizes data of the film title field. The default function calculates the sum of all values, so you can see the Sum values just after adding row and column fields in a pivot table. You can cancel the default function or, quite the contrary, add others. To apply a summary function to a field, do the following: 1. Select a required field in the ​Data Source view and go to the ​Properties window (to open it, right-click the field and select ​Properties​ on the menu). 2. Click the ​Summary field and check a required function or several ones to apply. See the custom totals in a pivot table. You can find detailed information about ​Applying Conditional Styles​, ​Printing​, Areas​, ​Headers​, ​Filter Field​, ​Column Field​, ​Row Field​, ​Data Field​, ​Totals​, and Grand Totals​ at our documentation center: ​https://docs.devart.com/studio-for-mysql103 9.​ ​Viewing Data in Master-Detail Tables Viewing Data in Related Tables This topic gives a quick guide on how to view data in related tables with the help of Master-Detail Browser​. For example, you need to view details for each actor for all categories in the ​Sakila database. To do this, you should perform the following actions: 1. On the ​Database​ menu, click Master-Detail Browser. 2. Drag the actor, film_actor_id , and film tables from ​Database Explorer to the designer area. 3. Select the actor table and click on the toolbar pane of the master-detail document. 4. Link columns, if needed.104 5. Select checkboxes near the columns you want to be included into the results and deselect the checkboxes near the columns you don’t want to be included into the result. 6. Click on the toolbar panel. 7. Navigate between the records in the master table and click + to view the detail data. Working with Self-Referencing Tables in Master-Detail Browser Self-referencing table is a table that is a parent and a dependent in the same referential constraint. In other words, in such tables a foreign key constraint can reference columns within the same table. The example specified below shows how to view data in a self-referencing table regarding available relations between columns using ​Master-Detail Browser​. Prerequisites To perform the steps described in the ​Viewing Data in a ​Self-Referencing Table part of this topic, you will need a self-referencing table itself. Below is an example of such table (the105 Employees table) and it will be used in the ​Viewing Data in a ​Self-Referencing Table section as a sample table: Viewing Data in a Self-Referencing Table Consider the following task: it is required to view all employees regarding the levels of company hierarchy using the data available in the sample Employees table. Solution: as the manager is also an employee, there is a relationship from the MGR column to the EmpNo column. The EmpNo column contains identification number of all employees, and the MGR column contains the identification number of the manager a person subordinates to (you see, the company president has (null) in the MGR column, as he doesn’t subordinate to anyone). Now, as you’ve understood the relationship between these columns, you can perform the following steps to solve the task specified above using dbForge Studio Master-Detail Browser: 1. On the ​Database menu​, click ​Master-Detail Browser​. An empty Master-Detail Browser document will open. 2. Drag-and-drop the Employees table you have created onto the designer surface.106 3. Create a relationship between the MGR and EmpNo columns by selecting the MGR column and dragging it to the EmpNo column. An arrow indicating the relationship will be displayed on the diagram: Note: ​There is no foreign key in this table, but if there is one and a relation between columns was established earlier, it’s displayed on the diagram after dropping the table onto it. 4. Click the ​Browse Data​ button. 5. In the ​Data view that opened, select the row with PRESIDENT specified in the Job column in the upper part of the view, and click + near each detail record in the lower part of the view to see the next level of company hierarchy and the employees belonging there:107 Working with Master-Detail Browser Document Working with Design View To add one or more tables/views to the diagram drag them from the ​Database Explorer window onto the designer surface. Note: ​The first table/view that you drag will be automatically set as master table/view and marked with peach colour. To set another table/view as master, you can select this table/view and click ​Set Table as Master on the document toolbar or right-click it and select the same command on the shortcut menu.108 Note: ​Arrows between tables/views indicate any existing relationships between these tables/views. Note: ​At least one parent table/view (without any arrows pointing to it) must be added to the document, or an error message will be displayed if you try to view results. When dragging tables onto the designer surface, relationship lines are automatically created if a relationship exists in the database for the added tables. A line with an arrow pointing in the direction of the link indicates a one-to-one relationship. But you can also create links between different columns yourself. To create a link, simply drag a column from one object to a column in the second object that you want to link. You can change the direction of a created or an existing link by right-clicking on it and selecting Change Link Direction​ on the shortcut menu.109 Note: ​You can not make links between columns of different types. Now, you can select columns you want to include into or exclude from your result by checking or unchecking checkboxes near the columns. You can also select all columns by clicking the ​Select All Columns option on the document toolbar or selecting this option on the table/view shortcut menu. To cancel selection of all the columns, click the ​Unselect All Columns option on the document toolbar or select this option on the table/view shortcut menu. You can also sort columns in tables/views in ascending or descending order using the ​Sort Ascending or ​Sort Descending options from the document toolbar or the shortcut menu correspondingly. Under the designer area you can specify a fetch limit for the result set, and a field to specify a ​WHERE condition. Also you can view the name of the currently selected table/view or link here. To view results, click the ​Browse Data button on the document toolbar or press ​F5​. After this the Data view with result set will be opened. To send the available relation to ​Query Builder​, click the ​Send to Query option on the menu. Working with Data View In the upper part of the ​Data view the data of the parent table/view is shown. The lower part of the document is occupied by data available in the child tables/views. You can navigate through the records of the parent table/view, and the corresponding data of the child110 tables/views will be shown in the lower part of the view. If there is more than one child table/view, click + to view data of these child tables/views. For the parent table/view, you can change the order of the available records. For the child tables/views, you can also change the records order and also apply filtering. To learn more about sorting and filtering data in parent and child tables/views, refer to the ​Sorting and Filtering Data in Master-Detail Browser​ topic. In this view, you can click the ​Refresh button to refresh data, ​Stop Refresh to stop refreshing. Click ​Show Design​ to return to the design view. Note: ​In the split document view document is divided in two parts - Design and Data. If you’ve designed the required relationship and you are ready to work with data, you can hide the ​Design view by clicking the ​Hide Design button in the Data view. After this the Data view will occupy all document area. To return to the Design view, click the ​Show Desig​n button on the document toolbar.111 You can find detailed information about ​Sorting and Filtering Data in Master-Detail Browser ​and ​Tips for Working with Master-Detail Browser at our documentation center: ​https://docs.devart.com/studio-for-mysql112 10. Working with Database Objects Creating and Dropping Databases Database is MySQL schema object that represents a folder containing tables. Tables can not exist outside a database. In MySQL 3.23 database has only one property - ​Name​. In the later versions of MySQL, ​Charset​ and ​Collation​ are added. Creating a Database 1. In ​Database Explorer​, right-click a connection node and select ​New Database​. ○ or - 2. Select ​New Database​ on the ​Database​ menu. 3. Input name of the database. 4. Select ​Character set​ and ​Collation​. 5. Click ​OK​, and, if specified name is unique, the database will appear in Database Explorer. Editing a Database 1. In ​Database Explorer​, right-click a database node and select ​Edit Database on the shortcut menu. 2. Select ​Character set​ and ​Collation​. 3. Click ​Update Database​. Note: ​Database can be renamed only in MySQL 5.1.7 - 5.1.23 and 6.0.0. You can not rename database in other MySQL versions. Dropping a Database Drop a database by selecting ​Delete​ on the node shortcut menu. Note: ​When you drop database all tables and data containing within it are lost.113 Creating a Database Object Note: ​This topic contains general guidance on creating database objects. For detailed information on a specific database object, refer to the appropriate topic. To Create a Database Object: 1. On the ​Standard toolbar, click ​New Database Object​. The New Object dialog box opens. 2. In the ​New Object​ dialog box, select a location and object type. 3. Input an object name. 4. Click ​Create​. 5. Proceed with creating an object in a corresponding editor. -OR- 1. In ​Database Explorer​, navigate to a required database. 2. Right-click a database node to open the shortcut menu.114 3. Point to ​New Object​ and then click a required object type. 4. Proceed with creating a database object in a corresponding editor. -OR- 1. In ​Database Explorer​, right-click an appropriate database object node, and then click ​Create Object Name​. 2. Proceed with creating a database object in a corresponding editor. Creating a Copy of a Database Object dbForge Studio for MySQL gives you the duplicate object feature. You can make an exact copy of a database object in the same or different database of the same server. It can be very useful for database designers. To copy an object, right-click its node and select ​Duplicate Object on the shortcut menu. Then select destination database and type a new object name. If the selected object is a table, you can copy data from it or create an empty table copy. Note, that if you copy a table with foreign key constraints, these constraints will not be copied because there may be no referenced tables in the target database. You can copy objects of following types:115 ● Tables ● Views ● Stored procedures and functions. You can find detailed information about ​Displaying the Number of Database Objects and ​Creating, Dropping, and Editing Tables at our documentation center: ​https://docs.devart.com/studio-for-mysql Defining Table Structure A table contains columns and indexes. Besides, a table has options such as a table type, a row format and so on. Defining a table structure means setting up all these elements. To define table structure, create a table or open the existing one from Database Explorer. There are 7 tabs in ​Table editor​: Main, Constraints, Indexes, Options, Partitioning, SQL, Data. Partitioning tab appears only when creating a table and when editing partitioned tables. Working with partitioned tables is described in the ​Partitioned Tables section of our documentation center. Defining Columns Switch to the ​Columns tab of Table editor to define columns and its properties or rename the table. To add a column to table, right-click on the grid and select ​New Column on the shortcut menu. Alternatively, press the ​INSERT​ key. To edit an existing column, double-click a column row. To remove a column, right-click on it and select ​Delete Column​ on the shortcut menu.116 Defining Constraints Switch to the ​Constraints tab of Table editor to add or alter foreign and primary keys and their properties. To add a key to the table, right-click the tab and select ​New Foreign Key or ​New Primary Key​ on the shortcut menu, then choose the type of key you want to create. To edit an existing key and its properties, double-click a key row to accomplish this task. To remove a key, right-click it and select ​Delete Constraint​ on the shortcut menu. Defining Indexes Switch to the ​Indexes tab of Table editor to define indexes. Generally, it is similar to defining columns. To add an index to a table, right-click on the tab and choose ​New Index on the shortcut menu. Another way to do it is to press the ​INSERT​ key. To edit an existing index, right-click on it and choose ​Edit Index on the shortcut menu. You can also double-click on an index row to accomplish this task. To remove an index, right-click it and choose ​Delete Index​ on the shortcut menu.117 When you add a new index or edit the existing one, the ​Index Properties dialog box appears. Defining Options Tune up specific options according to your plans for the table in the ​Options tab of the Table editor. Defining Triggers Switch to the ​Triggers​ tab of Table editor to add or configure triggers and their properties. To add an index to table, right-click the tab and choose ​New Trigger on the shortcut menu. Another way to do this is to press the ​INSERT​ key. To edit an existing index, right-click it and choose ​Edit Trigger on the shortcut menu. You can also double-click on an index row to accomplish this task. To remove an index, right-click it and choose ​Delete Index​ on the shortcut menu. When you add a new trigger or edit an existing one, the Trigger Editor dialog box appears. Defining Partitioning Switch to the ​Partitioning​ tab of Table editor to add partitions or subpartitions.118 To select a partition (subpartition) type, select the required one from the drop-down list. To add a partitioning (subpartition) expression, enter the required one into the corresponding field. To add a partition, click the ​Add button or right-click the field under the ​Add ​button and select ​Add partition​. Then, specify the properties of the partition. Removing Partitioning To remove a partition, select one from the list and click the ​Remove button or right-click the partition and select ​Remove​. Managing Primary and Foreign Keys A Primary Key A ​primary key constraint designates a column as the primary key of a table or view. A composite primary key designates a combination of columns as the primary key. A primary key combines a ​Not Null and unique constraint in one declaration. Therefore, to satisfy a primary key constraint, no primary key value can appear in more than one row in the table, and no column that is a part of the primary key can contain ​Null​. A table or view can have only one primary key. Creating Primary Keys 1. Open a table and select ​New Primary Key​ on the ​Table​ menu. 2. Add required columns to the key and click ​OK​. You can also switch to the Constraints​ tab and create the primary key within the shortcut menu. Editing Primary Keys 1. Navigate to the key in ​Database Explorer and double-click it or open the table that owns the primary key. 2. Switch to the ​Constraints​ tab and choose ​Edit Constraint​ on the shortcut menu. Dropping Primary Keys 1. Navigate to the key in ​Database Explorer​. 2. Select ​Delete​ on the shortcut menu. -or- 1. Open the table that owns the primary key. 2. Switch to the ​Constraints​ tab. 3. Select ​Delete Constraint​ on the key’s shortcut menu.119 A Foreign Key A ​foreign key constraint (also called a referential integrity constraint) designates a column as the foreign key and establishes a relationship between that foreign key and a specified primary or unique key, called the referenced key. The same table or view can contain a foreign key and a referenced key. You can define multiple foreign keys in a table or view. Also, a single column can be a part of more than one foreign key. The following restrictions are actual for foreign constraints: ● Both tables must be of the InnoDB type and they must not be temporary tables. ● In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. ● In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order. Starting with MySQL/InnoDB 4.1.2, such index will be created on the referenced table automatically if it did not exist. ● Index prefixes on foreign key columns are not supported. One consequence of this is that BLOB and TEXT columns cannot be included in a foreign key, because indexes on those columns must always include a prefix length. ● If the CONSTRAINT symbol is given, it must be unique in the database. If it is not given, InnoDB creates the name automatically. Creating Foreign Keys 1. Open the table you need and choose ​New Foreign Key​ on the ​Table​ menu. 2. Add required columns, select referenced table and referenced constraint, and click OK​. -or- 1. Switch to the ​Constraints​ tab. 2. Create a constraint on the shortcut menu Note: ​Note, to create a foreign key, the referenced table should have a unique index, otherwise dbForge Studio will prompt you to create it. Click ​Yes in the dialog and the unique index will be added. For more information, see Create New Unique Index Message. Editing Foreign Keys 1. Navigate to the constraint in ​Database Explorer​, double-click it or open the table that owns the constraint, 2. Switch to the ​Constraints​ tab and select ​Edit Constraint​ on the shortcut menu.120 Dropping Foreign Keys 1. Navigate to ​Constraints​ in Database Explorer 2. Select ​Delete​ on the shortcut menu. -or- 1. Open the table that owns the key. 2. Switch to the ​Constraints​ tab. 3. Select ​Delete Constraint​ on its shortcut menu. Example of Creating a Foreign Key Assume you need to ensure that each employee in table Emp (described in the examples above) has and will have a valid reference to a manager. That is, for each employee value of field MGR there always will be one of the EMPNO values in the same table. This is best implemented with a foreign key. 1. Open the table and create a primary key for field EMPNO as described in topic Managing Primary Keys​. 2. Create the foreign key as described above. Select MGR as constraint column, then select EMP as referenced table, and select the primary key you have created in step 1 as the referenced constraint. That’s all you need to do to establish a referential integrity constraint. You can find detailed information about ​Partitioning Methods​, ​Creating Partitioned Tables​, and ​Adding or Removing Partitions or Subpartitions at our documentation center: ​https://docs.devart.com/studio-for-mysql Using FEDERATED Tables FEDERATED table allows you to work with remote tables and not to store them locally. Creating a FEDERATED Table To create a FEDERATED table, use the database where the table is stored. 1. Go to the ​Columns​ tab of the table editor.121 2. Select the ​FEDERATED​ storage engine type in the Engine combo box. 3. Switch to the ​Federated​ options tab. 4. Specify the connection string. Alternatively, to connect to the required table on the server, select the corresponding server and the table link. 5. Now you can access to the server that store the required table through the federated table, view all changes, and work with table’s data on the remote server. Editing a FEDERATED Table You can edit a federated table by selecting ​Edit Table on the Database Explorer node shortcut menu. All the changes you make will be also shown on the server where the table is stored. Dropping a FEDERATED Table You can drop the federated table by selecting ​Delete on the Database Explorer node shortcut menu. Once the connection is broken between the running servers, you can make changes and view the result changes only on the server that stores the tables. Note: ​If you have MySQL 5.0.13 installed, use COMMENT rather than CONNECTION. Working with Views View is a logical table based on one or more tables or views. A view contains no data itself, but retrieves it from underlying tables and views when requested. Views are introduced in MySQL 5.0.1. dbForge Studio allows you to create, modify, and drop views, retrieve and alter data in underlying tables.122 Creating a View 1. Click the ​Create New Database Object​ button and specify type ​View​ in the dialog. 2. Input name of the view. 3. Click ​Create and, if specified name is unique, the document of this view will appear. Note that views share the same namespace with tables. 4. Type a query that will represent the view. You do not have to type ​CREATE OR REPLACE VIEW … AS​ because dbForge Studio adds this clause for you. 5. Optionally, alter the name or change database (owner) in which the new view will be created. 6. Save the document. If there are any errors in the SQL syntax, you will be notified about it. -or- 1. In Database Explorer, right-click the ​Views​ node and select ​New View​. 2. Input name of the view and type a query that will represent the view. You do not have to type ​CREATE OR REPLACE VIEW … AS because dbForge Studio adds this clause for you. 3. Optionally, alter the name or change database (owner) in which the new view will be created. 4. Save the document. If there are any errors in the SQL syntax, you will be notified about it. Editing a View Edit a view by selecting ​Edit View on the Database Explorer node shortcut menu. In the opened document, you can alter the query text. Once a view is created, you can not modify its name or owner. Save the document to apply changes you’ve made to the query text. Dropping a View Drop a view by selecting ​Delete​ on the Database Explorer node shortcut menu. Retrieving and Modifying View Data To retrieve view data, right-click the view in Database Explorer and choose ​Retrieve Data on the shortcut menu. The data is rendered in grid-based editor. You can browse and edit view data in the same way as you browse and edit table data. Note: ​Some views can be non-updatable. You can not edit their data.123 Working with Stored Programs Events Event is a task that runs according to schedule. An event’s action statement may include most SQL statements permitted within stored routines. dbForge Studio supports events on MySQL servers starting from MySQL 5.1.11. dbForge Studio allows you to create, modify, and drop events. Creating an Event 1. On the ​Standard toolbar, click ​New Database Object and specify the ​Event type in the dialog. 2. Input name of the event. 3. Click the ​Create button and, if specified name is unique, the document of this event will appear. Note that views share the namespace with tables. 4. Select ​Enable​, ​Recurrent and ​Drop on the ​Completion options to set event state and schedule. 5. Optionally, you can alter the name or change database (owner) in which the new event will be created. 6. Go to the ​Timing​ tab and set the event schedule. 7. Save the document. If there are any errors in the SQL syntax, you will be notified about it. -or- 1. In Database Explorer, right-click the ​Events​ node and select ​New Event​. 2. Input name of the event. 3. Type SQL statements that will represent the event body. 4. Select ​Enable​, ​Recurrent and ​Drop on the ​Completion options to set event state and schedule. 5. Optionally, you can alter the name or change database (owner) in which the new event will be created. 6. Go to the ​Timing​ tab and set the event schedule. 7. Save the document. If there are any errors in the SQL syntax, you will be notified about it. Editing an Event Edit event by selecting Edit Event on the Database Explorer node shortcut menu. Note that event can not be saved if the start day of the event already passed. Set it to a correct value (current or future time) to save the event.124 Dropping an Event Drop event by selecting ​Delete​ on the Database Explorer node shortcut menu. Stored Procedures and Functions Stored procedure (function) is a set of statements that resides on server side and can be executed. Using dbForge Studio, you can create, edit, execute and delete stored procedures. Creating a Stored Procedure 1. In Database Explorer, navigate to the ​Procedures​ node. 2. Select ​New Procedure​ on the shortcut menu. 3. Provide procedure name in dialog box opened and click ​Create​. A new document appears with template in the ​Text view. Populate it with statements or switch to ​Design view to edit procedure properties. Save the document to finish procedure creation. Editing a Stored Procedure To edit a stored procedure: 1. In Database Explorer, navigate to the ​Procedures​ node and expand it. 2. Right-click a procedure and select Open from the shortcut menu. ● or - double-click a procedure. You can switch to the ​Design​ view to edit some properties of the procedure.125 Executing a Stored Procedure Right-click a stored procedure in Database Explorer and select ​Execute​. The stored procedure editor opens if it was not opened before. If the procedure accepts parameters, you will see the ​Parameters dialog box where you can assign initial values for procedure arguments. Once the stored procedure is executed, you will see a corresponding record in the Output window. The data returned by the stored procedure will appear in Data view of the stored procedure editor. Stopping Stored Procedure Execution Click the ​Stop Execution​ button on the toolbar. Deleting a Stored Procedure126 Right-click a stored procedure in Database Explorer, and select ​Delete​. Note: ​You can treat stored functions exactly the same way as stored procedures. Triggers Trigger is a routine executed under certain conditions. Using dbForge Studio, you can create, edit and delete triggers. Note: ​dbForge Studio supports triggers only with MySQL server 5.0.13 and higher. Creating a Trigger 1. In Database Explorer, navigate to the ​Triggers​ node. 2. Select ​New Trigger on the shortcut menu. A new document appears with template in the ​Design​ view. 3. Provide trigger name. Optionally, you can change schema for the object. 4. Specify trigger body. 5. Switch to the ​Event​ tab and specify the event to fire the trigger. 6. Save the document. Editing a Trigger 1. In Database Explorer, navigate to the ​Triggers​ node and expand it. 2. Right-click a trigger and select ​Edit Trigger​ on the shortcut menu. The Trigger Editor has two tabs: ​Main and ​Event​. In the ​Main tab, you can choose trigger type and fill its body, as well as switch the trigger on or off. In the ​Event​, tab you can set up event that should execute the trigger. Deleting a Trigger Select ​Delete​ on the trigger shortcut menu. Refactoring Overview Refactoring is used to improve design of your database schemas. You can perform refactoring of database objects and in SQL scripts. Refactoring can be applied in online and offline (database projects) database development. Renaming objects from the ​Database Explorer tree, object editors and scripts generated from the Database Explorer is considered a part of online database development. Renaming objects from the project is considered a part offline database development.127 Database objects that can be renamed during online database development ● Tables ● Columns Note: ​If you rename a column with a trigger on it, the trigger will be recreated. ● Views ● Functions ● Procedures Database objects that can be renamed during offline database development ● Tables ● Views ● Stored routines ● Triggers ● Events ● Table and view columns ● Stored routine parameters ● Stored code variables ● Users ● Aliases Note: ​Changes made by refactoring may be rolled back only if the changed files were opened before refactoring. In that case you may activate the corresponding document and press ​CTRL+Z​ to undo refactoring changes. Refactoring Usage Renaming columns referenced by other columns using foreign keys When renaming a column, it is necessary to make sure that all references using foreign keys are working. When simply renaming the column, you will have to find all foreign keys, delete them, rename the column and recreate the keys. When renaming using refactoring, the application will find all foreign keys and apply the new column name in all database. Renaming columns used in creating views It often happens that the column you need to rename is used in views. In case you just rename the column, you’ll get an error when data is selected for the view. To avoid it, you will have to edit all views that use the renamed column after you actually rename it. Refactoring tool in dbForge Studio will edit all views on its own after you rename the column.128 Renaming objects with code If you rename an object with code, for example, a stored routine and make a mistake in the code, the procedure or function will be lost and you will have to recreate it from memory. dbForge Studio for MySQL refactoring tool uses a special algorithm that changes such scenario: 1. A temporary stored routine is created. It contains the code of the procedure before renaming. 2. All objects that use this routine are deleted. 3. All objects that use this stored routine are recreated, but the routine is renamed. 4. The routine with new name and new code is created. 5. The temporary stored routine is deleted. In case the fifth step fails, the script execution stops and the original code can be restored from the temporary object. Performing Refactoring of Database Objects Renaming database objects using refactoring implies making changes in all dependencies. This way all relations, foreign keys, and references will be kept. Renaming Objects from Database Explorer Basic refactoring process 1. Select the object you want to rename in the ​Database Explorer​ tree and right-click it. 2. On the shortcut menu, point to ​Refactoring​, and then click ​Rename​. 3. In the ​Rename​ dialog box, enter the new name of the object and click ​OK​. 4. In the ​Preview Changes window, view the changes that will be applied to your database. Clear the checkboxes near the ones that you do not need. 5. Click ​Apply​. -or- 1. Double-click an object you want to rename. 2. Change the name and press ​ENTER​. 3. In the message box, click ​Refactor​. 4. In the appeared ​Preview Changes window, view the changes that will be applied to your database. Clear the checkboxes near the ones that you do not need. 5. Click ​Apply​. Using refactoring script You can view changes that will be made to the database before applying them. For this, you can: 1. Check the ​Generate​ script without updating database option.129 2. Click ​Apply​. 3. The generated script will be opened in the SQL Editor. 4. View the script and press ​F5​ to execute it and apply changes to the database. Renaming Database Objects from Object Editors To rename an object with refactoring from object editor, you should: 1. Open the object editor. 2. Rename the object. Note: ​You can also rename columns using refactoring in the table editor. 1. Click the ​Update Database​ or ​Script Changes​ button. 2. In the appeared message, click ​Yes​. 3. View changes in the ​Preview Changes​ window, adjust them, if needed. 4. Click ​Apply or ​Generate (depends on what you are doing - updating a database right away or viewing script changes). Multiple refactoring in table editor You can rename several columns at once using refactoring. To do this: 1. Open the table you want to edit in the table editor. 2. Rename the columns you need to rename. 3. Click the ​Update Database​ or ​Script Changes​ button. 4. View changes in the ​Preview Changes window. Changes in the tree are grouped by columns. 5. Click ​Apply or ​Generate (depends on what you are doing - updating a database right away or viewing script changes). Performing Refactoring in SQL Script To rename a project object or change name of an object in a script using refactoring, perform the following steps: 1. For project object, right-click the object name in the SQL editor or in ​Schema View and choose ​Rename on the shortcut menu. To rename object in a script, open the code, right-click the object name in the SQL editor, and click Rename. 2. The ​Rename​ window will appear. Enter a new object name.130 3. Use the corresponding checkboxes to determine whether to search in text strings and comments and whether to preview changes, then click ​OK​. 4. If the ​Preview reference changes checkbox is selected, the Preview Changes dialog box will appear. Here you can preview, enable, and disable project changes. 5. Click the ​Apply button to apply the selected changes, or click the ​Cancel button to cancel them. You can find detailed information about ​Creating a Database and Two Tables​, Creating a View​, and ​Creating a Trigger at our documentation center: https://docs.devart.com/studio-for-mysql131 11. Working with Search Searching Database Objects Search 1. Click ​Find Object​ on the ​Database​ menu or press ​Ctrl+F, D​. 2. Click to switch to the ​Search​ objects by name and DDL mode. 3. In the ​Search​ text box, enter the search string. 4. Optionally, click to select an additional find option from the drop-down list box. The available options are ​Match Case​, ​Match Whole Word and ​Use WildCards​. By default, the Use WildCards option is selected. 5. Optionally, click to group results by the object type. 6. You can change the scope of search results by specifying the search parameters, such as an object type, database, and connection to search in. ○ Object types​. Select object types, in which the search will be performed. By default, all object types are selected. ○ Database​. Select a database to search in from a drop-down list. The database you are currently connected to is selected by default. ○ Connection​. Select a connection for search from a drop-down list. The connection you are currently using is selected by default. 7. To start the search, click in the Search text box. During the search, you will see the button with a binocular and a progress spinner indicating that the search is in progress. You can stop the search at any point by clicking the binocular button. 8. When the search is completed, the details of search results will be displayed in the Search Results​ grid. 9. Optionally, you can set a filter for each column in the Search Results grid. 10.When you select a result in the grid, the ​Preview window below the grid displays the DDL statements associated with the selected result.132 Searching Table and Viewing Data Data Search 1. Click ​Find Object​ on the ​Database​ menu or press ​Ctrl+F, D​. 2. Click to switch to the ​Search Table and View Data​ mode. 3. In the ​Search​ text box, enter the search string. 4. Optionally, click to select an additional find option from the drop-down list box. The available options are Match Case, Match Whole Word and Use WildCards. By default, the Use WildCards option is selected. 5. Optionally, click to group results by the object type. 6. You can change the scope of search results by specifying the search parameters, such as objects, a database, and a connection to search in. ○ Objects​. Select objects, among which the search will be performed. By default, all objects are selected. ○ Database​. Select a database to search in from a drop-down list. The database you are currently connected to is selected by default. ○ Connection​. Select a connection for search from a drop-down list. The connection you are currently using is selected by default.133 7. To start the search, click in the ​Search text box. During the search, you will see the button with a binocular and a progress spinner indicating that the search is in progress. You can stop the search at any point by clicking the binocular button. 8. When the search is completed, the details of search results will be displayed in the Search Results​ grid. 9. Optionally, you can set a filter for each column in the Search Results grid. 10.When you select a result in the grid, the ​Preview window below the grid displays the records that match the search criteria. Managing Search Results Sort and Filter Search Results dbForge Studio allows you to quickly sort search results. You can sort records in each column by ascending or descending alphabetical order by clicking a column heading.134 To apply filter to a column: 1. Hover a cursor over a column heading. The ​Filter button will appear in its upper right corner. 2. Click the ​Filter button and select an item from the drop-down list. All the records corresponding to this item will be displayed in the grid. 3. Optionally, select the ​Custom ​option from the drop-down list. The ​Custom AutoFilter​ window will appear, where you can fine tune the filter. 4. The filters currently applied to the search results may be edited in the ​Filter Editor​. To evoke the editor, click the ​Edit Filter button in the lower right corner of the Search Results​ grid.135 Find Object in Object Explorer dbForge Studio can navigate you to an object in the ​Object Explorer directly from the dbForge Studio window. 1. Double-click the object in the ​Test Results​ grid or 2. Right-click the object in the ​Test Results grid and select ​Find in Object Explorer on the shortcut menu 3. Select the object in the ​Test Results grid and click the ​Find in Object Explorer button below the grid. Word Wrap Click the ​Word Wrap button to set and clear the ​Word Wrap option for source code displayed in the ​Preview window. When the option is enabled, all the text shall fit the current width of the ​Preview​ window. Line Numbers Click the ​Show line numbers button to set or clear numbers for lines of the source code displayed in the ​Preview ​window.136 12. Designing Databases with Database Designer Creating a Database Diagram To create a database diagram, perform the following steps: 1. On the ​File​ menu, select ​New​, and then click ​Database Diagram​. 2. Drag database objects from ​Database Explorer to the diagram. Alternately, right-click a database object, point to ​Send to​, and then click ​Database Diagram​. You can also create new database objects on the diagram using the ​Diagram toolbar. 3. You may click the ​Layout Diagram button on the ​Layout toolbar to layout diagram for a more convenient view. (Or set the diagram layout manually by dragging shapes and moving the relations. You can also use containers to organize the diagram.) 4. Add a stamp to the diagram to display company and project names, diagram author, version, date, and copyright. To add a stamp, click the ​New Stamp button on the Diagram toolbar. To edit stamp fields, double-click them and enter values. You can also add notes to the diagram to describe its objects. 5. Save the diagram for future use. Navigating the Diagram To navigate the diagram: ● Use the scrollbars. ● Move the mouse while holding the middle mouse button. ● Use the ​Diagram Overview window. Drag square visible area on the Diagram Overview window with the mouse. To display the Diagram Overview window, on the ​View menu, click ​Other Windows​, and then click ​Diagram Overview​, or press ​CTRL+V, W​. ● Search diagram shapes. ● Use the ​Document Outline window. It contains nodes, representing all shapes on the diagram. They are grouped by containers and by object types. Stamps and notes are placed to the Notes kind. Table nodes contain their relations. You can zoom in and out the diagram to simplify the navigating. To quickly move to the area you need, click the ​Fit ​button on the Diagram toolbar, then hold ​CTRL+SHIFT and select the area you need with mouse pointer.137 How to Delete Database Object from Diagram To delete a shape from the diagram but keep a database object in the database, click the object and hit the ​DELETE key or right-click the object and select ​Remove from ​Diagram on the shortcut menu. To remove database object from the database, click the object and press ​CTRL+DELETE or right-click the object and select ​Remove​ from ​Diagram​ on the shortcut menu. Table columns, indexes, constraints, triggers, relations (foreign keys) can only be dropped from a database. Both ​DELETE and ​CTRL+DELETE keys invoke dropping. View columns and stored routine parameters cannot be deleted. How to Edit Database Object on a Diagram To edit a database object, double-click it on the diagram. An object editor dialog will appear. The object editor dialog is the same as object editor document except for the bottom buttons. ​Reset discards all changes to the database object on any tab of the editor (but not the changes to the table or view data on the data view), ​OK button closes the dialog and saves all changes, ​Cancel button closes the dialog and discards all changes. You can edit a database object in this dialog just like in the object editor document. Setting Diagram Options A database diagram can be tuned with a rich set of options. Options can be set either globally for all the diagram documents or separately for a particular one. To set options for the current diagram, right-click it and choose ​Options ​from the shortcut menu to open the ​Diagram Options dialog box. You can reset options for the current diagram to default values (set globally) by using the ​Default ​button. Changing Diagram Appearance Database diagram supports three predefined skins which change its appearance. They are the Default skin, the Simple skin, and the IDEF1X skin. The Default skin uses the richest color palette. The Simple skin is optimized for printing and does not use gradients. The IDEF1X skin is mostly black-and-white to look like IDEF1X diagrams. To choose the skin, use the ​Skin Type ​drop-down list in the notations options.138 You can use either ​IDEF1X or ​IE notation for relations. They can be switched by using the Relation Notation​ drop-down list. With the notations options you can customize greatly the table shape look. You may hide its constraints, indexes, and triggers compartments, change its field detail level to display only column names, names with types or names with full types. Note that field detail level does not affect stored routines. Primary key columns may either be separated with horizontal line or placed with other columns. On the General page of the Database Diagram section of Options Dialog Box you can customize diagram background color, shape shadows, and diagram grid. Setting Diagram Printing Options Page options allow you to set such page settings as orientation (portrait or landscape), paper size and source, printing margins and overlapping, header and footer text. Print options allow setting parameters for positioning printed image in the center, diagram scale, and whether to print diagram grid. Diagram scale may be set either manually, using Scale field, or automatically to fit the desired number of pages. They also allow you to set up printing of the page markup that helps you to glue diagram pages together.139 You can find detailed information about operations connected with Diagrams at our documentation center: ​https://docs.devart.com/studio-for-mysql Working with Relations dbForge Studio allows creating two types of relations: physically existing foreign key relations and virtual relations. Physical relations They represent physically existing foreign key relations between tables. On the diagram, these relations are displayed as connecting lines. Their appearance depends on the selected notation. Options available for managing physical relations are as follows: ● Move - click the relation and drag its bend handle. ● Bend - click the relation and drag its bend handle. ● Reroute - right-click the relation and choose ​Reroute on the shortcut menu. The relation will be rerouted to its default route.140 ● Drag a relation parent end to another table - you may attach the parent edge to any edge of the table shape. How to Delete a Relation Click a relation and press the ​DELETE​ key, then click ​Yes​ in the appeared dialog box. Note: ​When you delete a relation from the diagram, the corresponding foreign key is deleted from the database. How to Edit a Relation 1. Double-click a relation to invoke the ​Foreign Key Properties​ dialog box. 2. Make changes and click ​OK​. To change the relation parent table, drag the parent end of the relation to another table. You cannot change the relation child table. Note: ​The relation cardinality is determined automatically from the child table constraints and cannot be changed by a user except for editing child table constraints. How to Create a Relation 1. Click the ​New Relation​ icon on the ​Database Diagram​ toolbar. 2. Click the child table and move the mouse pointer to the parent table while holding the mouse button, then release the mouse button. A partially initialized ​New Foreign Key dialog box will appear with a referenced table, a database, a constraint, and columns already set. 3. Specify the Constraint Columns in the dialog box and click OK. Note: ​Relations on the diagram correspond to existing foreign keys in a database. When you create a relation on the diagram, a foreign key constraint is created in the database. If the foreign key cannot be created in the database, the relation cannot be created on the diagram. Virtual relations They allow you to create relations between tables if their storage engine does not support foreign keys. Virtual relations do not exist physically, but only stored on the diagram,141 however they can be converted to physical foreign keys. On the diagram they are displayed as thin dotted lines connecting tables. You can move, bend, reroute virtual relations, drag a relation’s parent end to another table the same way as you do with physical relations. How to Create a Virtual Relation 1. Click the ​New Virtual Relation​ icon on the ​Database Diagram​ toolbar. 2. Click the child table and move the mouse pointer to the parent table. Click a required column in the child table and, holding the mouse button pressed, select a required column in the parent table, then release the mouse button. The virtual relation will appear on the diagram. You can also select entire tables to create a virtual relation between them. In this case, the Virtual Relation Properties ​dialog box will appear where you should select constraints and referenced columns. How to Edit a Virtual Relation 1. Double-click it to invoke the ​Virtual Relation Properties​ dialog box. 2. Make changes and click ​OK​. How to Delete a Virtual Relation Do either of these actions: ● Click it on the diagram and press the ​DELETE​ key. ● Select it in the ​Virtual Relation Manager​ window and press the ​DELETE​ key. How to Manage Virtual Relations Use the Virtual Relation Manager window to view, edit, remove virtual relations, generate their DDL, convert to Foreign keys, etc. To open it, click the ​Virtual Relation Manager icon on the toolbar or, on the ​View menu, point to ​Other Windows​, and click ​Virtual Relation Manager​. Right-click a required relation in the grid and select a required command on the menu. Relation Comments Each relation has a comment - a yellow rectangle with a relation foreign key name. Select the ​Show Relation Comment ​checkbox in the diagram options or click ​Show Comments on the ​Diagram toolbar to display comments. By default, they are displayed near the child end of relations. You can drag them to any other place, and they will move with the relation.142 To hide a comment for a specific relation, click it and hit the ​DELETE key. To display it again, right-click the relation and select ​Show Comment​ on the shortcut menu. Relation Notations Foreign key relations are displayed as lines connecting tables. Appearance of the relation depends on the selected notation. dbForge Studio supports IE and IDEF1X notations for the diagrams. IDEF1X Notation IDEF1X (Integration Definition for Information Modeling) is a modeling language for developing a logical model of data. For detailed description of IDEF1X notation refer to FIPS Publication 184 Announcing the IDEF1X Standard December 1993 by the Computer Systems Laboratory of the National Institute of Standards and Technology (NIST). Identifying relations are displayed as continuous lines with black circle on the child end. (Identifying relation means when all foreign key columns are included to the primary key of the child table). See example below. Non-identifying relations are displayed as dotted lines with black circle on the child end. If this relation is an Optional Non-Identifying Relationship (child foreign key columns are nullable), white diamond is displayed on the parent end of the relation. See examples below. Z letter at the child end of the relationship appears if there can be only zero or one child record. It depends on whether the foreign key columns in the child table are unique (belong to the primary key or unique index). Thus, identifying relations are always displayed with letter Z. Non-identifying relations can be displayed both with or without Z letter. IE Notation IE Notation is used by default. Relations are displayed as continuous lines. Ends of the lines depend on relation cardinality. Relation cardinality is determined automatically from the143 foreign key constraint, it cannot be specified by user (except for editing child table constraints). In MySQL one record of the child table corresponds to 0..1 (zero or one) or 1 (exactly one) record from the parent table. It depends on the presence of the NOT NULL constraints on the foreign key columns in the child table. One record of the parent table corresponds to 0..1 (zero or one) or 0..N (zero, one, or more) records from the child table. It depends on whether the foreign key columns in the child table are unique (belong to the primary key or unique index).144 13. Database Backup and Restore There are many reasons that can cause data loss. These are, for example, an operating system crash, power failure, hardware problem and many others. So it is necessary to backup databases periodically. With dbForge Studio for MySQL you can perform database backup easily using ​Database Backup Wizard​. dbForge Studio backs a database up into a simple SQL script. You can read and correct this script. With dbForge Studio, you can perform full database backup or restoring only selected database objects. To call Database Backup Wizard, select ​Backup Database… on the Database menu or on the shortcut menu of the database node of the ​Database Explorer window. Backing Up an Entire Database 1. Select a database to back up, backup script file name and location, click ​Next​. 2. Select ​Structure​ and ​Data​, check ​Include all objects​, click the ​Next​ button. 3. Set the backup options, click the ​Backup​ button. 4. Choose whether to open the backup script file in SQL Editor and whether to save the backup project for future use, and click the ​Finish​ button. Backing Up Certain Database Objects 1. Choose a database to backup, script file name and location, click the ​Next​ button. 2. Select required information to backup. 3. Select database objects to backup their structure if you have chosen ​Structure or Data​ to back up on the ​Backup Content​ page, click the ​Next​ button. 4. Select tables to backup their data if you have chosen ​Data or ​Structure to back up on the ​Backup Content​ page, click the ​Next​ button. 5. Set backup options, click the ​Backup​ button. 6. Choose whether to open the backup script file in SQL Editor and whether to save the backup project for future use, and click the ​Finish​ button. Restoring Database from Backup Script To call ​Database Restore Wizard​ and restore the database from the backup script: 1. Select ​Restore Database… on the ​Database menu or on the shortcut menu of a database node of the ​Database Explorer​ window.145 2. Select backup script file to restore and target database. If backup script file contains the ​CREATE DATABASE statement, backup data will be restored to the created database instead of the selected database. 3. Check that the encoding for the restored file is selected correctly. The program automatically selects it based on the restored file, if it fails, the default encoding (the current Windows encoding on the machine where dbForge Studio is running) is selected. You can always select a required encoding from the drop-down list. 4. Click the ​Restore​ button. Note: ​If you backup and restore database in different MySQL versions, you may encounter errors. Scheduling Database Backup You may need to schedule an automated database backup that will recur periodically. To do this, first of all, you will need to create a project file, containing backup settings. Once the project file is created, you have to schedule Windows task that will perform actions at a specific time or when a certain event occurs. Creating a project file 1. On the ​Database menu, select ​Backup and Restore​, and then click ​Backup Database​. 2. Specify Connection, Database, Path, Output filename. Click ​Next​.146 3. Select backup content. Click ​Next​. 4. Select detailed backup options. Click ​Next​. 5. Set the ​Errors handling​ and ​Log settings​ options. Click ​Save Project​. Scheduling Windows task 1. Start ​Task Scheduler​ for Windows. 2. Select ​Action -> Create Basic Task​ on the top menu. 3. Provide ​Name​ for the new task. Click ​Next​. 4. Select one of the options when you want the task to be started. Click ​Next​. 5. Select time and frequency for starting the task. Click ​Next​. 6. Select ​Start a Program​ and click ​Next​.147 7. Enter path to the dbforgemysql.com file in ​Program/script​. (The default path is C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com.) 8. Also, make sure to specify arguments in the ​Add arguments​ field as follows: /backup /connection:User Id=root;Host=db;Port=3309;Database=sakila;Character  Set=utf8; /projectfile:C:\Users\Documents\dbForge Studio for  MySQL\Export\sakila.backup    9. Click ​Next​. 10. Check all specified settings once again and, if no changes are required, click ​Finish​. Otherwise, click ​Back​ and make the needed changes. You can find detailed information about ​/backup Arguments​ and ​/restore Arguments​ at our documentation center: ​https://docs.devart.com/studio-for-mysql148 14. Managing Users and Privileges Manipulating User Accounts Security Manager provides an easy way to add, alter, or remove users visually without typing SQL code. To open the Security Manager, select ​Security Manager​ on the ​Database​ menu. Creating a User Account 1. Open the ​Security Manager​. 2. Click above the User List -or- 1. right-click a user node and select ​New User​ on the shortcut menu. 2. Fill in the ​Name​, ​Host​, ​Password​, and ​Confirm Password​ fields. 3. If you want to create a proxy user account, fill in the ​Authentication and Authentication String fields as well. Also, set up ​Proxied User for a proxy user account. 4. Grant necessary privileges to user (For information about granting privileges to user, see ​Granting and Revoking System Privileges, Granting Privileges on Database Objects, and Granting and Revoking Roles​ topics). 5. Optionally, set the server resources limit for the user. 6. Click the ​Save​ button on the ​Security Manager​ toolbar. Duplicating Users dbForge Studio allows you to create a new user, based on the existing one. To do this, right-click the user in the ​Security Manager and select ​Duplicate Object on the shortcut menu. The right part of the editor will contain a new user, that is an exact copy of the existing one except its name. Make changes to the user if you need and save it. Note that no user will be created until you ​Save​ the changes. Editing a User Account Open the Security Manager and select the required user account in the tree. After editing, click the ​Save button on the ​Security Manager toolbar to apply changes. If you want to discard changes, click the ​Undo button on the ​Security Manager toolbar. You can view149 SQL statements that should be executed to apply changes. To view them, click the ​View Changes​ button on the ​Security Manager​ toolbar. Deleting a User Account Open the ​Security Manager​, right-click the account you want to delete, and select ​Delete on the shortcut menu, or select the required user in the list and click the ​Delete button above the User List, or press ​DELETE​. Manipulating Roles Note: ​The Role Editor is available for the MariaDB connections only. The Security Manager provides an easy way to add, alter or remove roles visually without code typing. To open the Security Manager, click ​Security Manager on the ​Database menu. Creating a new role 1. Open Security Manager. 2. Click the ​Create Role button above the User List. Alternatively, right-click a role and then select ​New Role​ on the shortcut menu. 3. Fill the ​Name​ field. 4. Grant necessary roles and permissions to a role. 5. Click the ​Save​ button on the ​Security Manager​ toolbar. Editing a role To edit a role account, open ​Security Manager​, expand the ​Roles node, and then select a required role in the tree. After editing, click the ​Save button on the ​Security Manager toolbar to apply changes. If you want to discard changes, click the ​Undo​ button on the ​Security Manager​ toolbar. Deleting a role Open Security Manager, expand the ​Roles node, and select ​Delete on the shortcut menu of a required role; or select a required role in the tree and press ​DELETE​.150 Granting and Revoking Roles Security Manager allows granting and revoking roles. To do this, you should: 1. Open ​Security Manager​. 2. Expand the ​Users node for granting privileges to a user or the ​Roles node for granting privileges to a role. Note: ​The Role Editor is available for the MariaDB connections only. 1. Select the required user or role. 2. Switch to the ​Roles​ tab. 3. Check the needed role or roles. Note that some roles can include other roles. You can determine this by the status of the checkboxes near roles: checked. Privileges included in this role are added to the user privileges. Note that some roles can be checked, if the user already has the corresponding privileges. checked, but inactive. Some other role that is selected includes privileges of this role. unchecked. Privileges of this role are not granted to the user. 4. View the privileges granted by each selected role or roles on the ​Global Privileges​ tab. You can find detailed information about ​Granting and Revoking Global Privileges​, ​Granting Privileges on Database Objects​, ​and ​Working with Multiple Users​ ​at our documentation center: ​https://docs.devart.com/studio-for-mysql151 15. Debugging The dbForge Studio debugger is a powerful tool that allows you to observe the run-time behavior of your database object and locate logic errors. With the debugger, you can break, or suspend the execution of your program to examine your database object, evaluate and edit variables in your script. The dbForge Studio debugger provides the ​Debug menu with access to debugger tools. Debugger windows and dialog boxes display information about your database object and enable you to enter additional information. You can call ​Help ​on any window or a dialog box by pressing ​F1​. The dbForge Studio debugger provides powerful commands for controlling the execution of your code. Here is the list of the tasks you can perform to control execution using debugger commands: ● Starting (or continuing) execution of procedures, functions, triggers, and SQL scripts ● Breaking execution of procedures, functions, triggers, and SQL scripts ● Stopping execution of procedures, functions, triggers, and SQL scripts ● Stepping through the procedures, functions, triggers, and SQL scripts ● Running to a specified location ● Setting the execution point Debug Layout dbForge Studio has two window layouts - default layout and debug layout. Debug layout is applied when you start debugging, the rest of the time you work in the default layout. Both layouts preserve their state when starting/stopping debugging or exiting from dbForge Studio. ​Watches​ and ​Call Stack​ windows are available only in debug layout. Debugging a Stored Procedure This example demonstrates how to debug MySQL stored procedure by stepping into the stored procedure. It also illustrates different debugging techniques such as setting breakpoints, viewing data items, and so on. Creating a Sample Procedure In this topic, some sample database objects are used for demonstrating how to work with the dbForge Studio Debugger. Use scripts specified in this topic to create necessary objects in your test database.152 Database Tables Use the script available in Sample Script to Create Database Tables to create necessary tables. Database Procedure Use the script below to create the procedure that takes two input parameters: ​DateFrom and ​DateTo​. Based on these input parameters the ​schedule_list​ table fills up with data. CREATE PROCEDURE FillSchedule (IN DateFrom date  , IN DateTo date  , IN ScheduleId int)  SQL SECURITY INVOKER  READS SQL DATA  BEGIN     DECLARE DateOut date;  DECLARE TemplateCount tinyint;  DECLARE RowNumber tinyint;  DECLARE TimeSheetDate date;     SET  TemplateCount = (SELECT  COUNT(*)  FROM `schedule` s  JOIN scheduletemplatedetail sdt  ON s.TemplateId = sdt.TemplateId  WHERE s.ScheduleId = ScheduleId), DateOut = DateFrom  , RowNumber = 0;  SET TimeSheetDate = GetFirstDayOfMonth(DateFrom);     DROP TABLE IF EXISTS enumdays;  CREATE TEMPORARY TABLE enumdays (  DayOrder tinyint,  DateOut date,  TimeSheetDate date  );     WHILE (DateOut <= DateTo) DO     IF RowNumber = TemplateCount  THEN  SET RowNumber = 1;  ELSE  SET RowNumber := RowNumber + 1;  END IF;     INSERT INTO enumdays (DateOut  , DayOrder  , TimeSheetDate)  SELECT  DateOut,  RowNumber,  TimeSheetDate;    153 SET DateOut := DATE_ADD(DateOut, INTERVAL 1 DAY);  SET TimeSheetDate = GetFirstDayOfMonth(DateOut);     END WHILE;     DELETE  FROM s  USING scheduledetail s  WHERE s.ScheduleId = ScheduleId  AND s.DateOut BETWEEN DateFrom AND DateTo;     INSERT INTO scheduledetail (DateOut  , TimeSheetDate  , ScheduleId  , AbsenceCode)  SELECT  d.DateOut,  d.TimeSheetDate,  ScheduleId,  s.AbsenceCode  FROM enumdays d  CROSS JOIN (SELECT  s.TemplateId  FROM `schedule` s  WHERE s.ScheduleId = ScheduleId) s2  JOIN scheduletemplatedetail s  ON d.DayOrder = s.DayOrder  AND s.TemplateId = s2.TemplateId;     END  $$  Debugging the Stored Procedure Assume that you need to get Time Sheet for a specific time period. To do this, execute the FillSchedule​ procedure: 1. In ​Database Explorer​, choose your test database. 2. Click ​Procedures and then right-click the ​FillSchedule procedure, and click Execute​ on the shortcut menu.154 3. Enter input parameters. 4. Click ​OK​. Suddenly we get the error message telling us that the ​TimeSheetDate column cannot be null. Note: ​Actual document may look slightly differently, depending on the product you use. A quick look through the code gives you nothing and you have no other choice but to start the debugger. Debugging Procedure To start debugging the stored procedure: 1. To compile the stored procedure for debugging, navigate to the required procedure in Database Explorer​, select ​Compile​, and then click ​Compile Dependants for Debugging​ on the shortcut menu. 2. Click the ​Start Debugging​ button on the ​Debug​ toolbar, or press ​CTRL+F5​.155 3. If you have not compiled the procedure, the dbForge Studio prompts you to compile it. Click ​Yes​. 4. Enter the stored procedure input parameters once again and click ​OK​. You can see that the yellow arrow identifies the stack frame where the execution pointer is currently located. 1. Add the ​TimeSheetDate variable to ​Watches​. Right-click the TimeSheetDate variable in the source code and then select the ​Add Watch option on the shortcut menu. Thus, you will be able to track the TimeSheetDate value while debugging. Now, you can see that the TimeSheetDate variable appears in the ​Watches​ window. 2. Insert a breakpoint in the place where you insert the ​TimeSheetDate variable in the enumdays​ temporary table. 3. Click the ​Step Into button on the ​Debug toolbar, or press ​F11 for stepping through the code.156 4. After several clicks you are hitting this breakpoint which means that the current code isn’t ignored. However, as you can see in the Watches window, the value of the ​TimeSheetDate variable is ​NULL​. We know that the TimeSheetDate field cannot accept NULL values. So it can be concluded that the TimeSheetDate variable should be initialized. 5. Initialize the ​TimeSheetDate​ variable before inserting it into the table. 6. Now, restart the debugging process. To do this, click the ​Restart button on the Debug​ toolbar. 7. Repeat the steps 2-8. 8. Now, once you hit the breakpoint, you can see that the value of the ​TimeSheetDate variable is the date, which is absolutely correct. 9. To stop the debugging process, click the ​Stop​ button. 10.Click ​Execute​ to run the stored procedure. 11.You can see the message telling us that the stored procedure executed successfully. To verify this, you can retrieve the data from the table.157 12. In ​Database Explorer​, choose the required database. 13.Click ​Tables​ and then right-click the required table. 14.Click ​Retrieve Data​ on the shortcut menu. 15.dbForge Studio displays the result table. Debugging Stored Function This example shows how to debug a stored function in dbForge Studio for MySQL. Use provided scripts to create sample objects in your test database. Creating a Sample Function Database Tables Use the script available in ​Sample Script to Create Database Tables to create necessary tables.158 Procedure Use the script available in ​Debugging a Stored Procedure to the procedure used in the sample below. Function Use the script below to create the ​Get First Day Of Month function that returns the first day of a calendar month. CREATE FUNCTION GetFirstDayOfMonth (`date` datetime)  RETURNS datetime  BEGIN       RETURN  DATE_ADD(DATE_ADD(LAST_DAY(`date`),  INTERVAL  -  1  MONTH),  INTERVAL  1  DAY);     END  $$  Debugging Function To debug the MySQL function: 1. In ​Database Explorer​, choose a test database. 2. Click ​Procedures​ and then double-click the ​Fill Schedule​ procedure to open it. 3. Click in the gray margin next to the ​SET Time Sheet Date statement to insert a breakpoint in the place where we call the ​Get First Day Of Month​ stored function. 4. Click Start Debugging, and enter the input parameters values for the procedure. 5. Add the ​CurrDate variable to the ​Watches window. This option allows you to track the value of the variable while stepping through the code. To add the variable to the Watches window, right-click the ​CurrDate variable, and then select ​Add Watch on the shortcut menu. The variable will appear in the Watches window. 6. Step through the code using the F11 key or the ​Step Into button, or press CTRL+F5​ to move directly to the breakpoint. Note when you are at the SET Time Sheet Date statement, the ​CurrDate variable changes its value from ​NULL​ to ​DATE​.159 7. Press ​F11 or click ​Step Into to get inside the ​Get First Day Of Month stored function. Note: ​You may ignore stepping through the function by clicking the ​Step Over button. In this case, you will continue stepping through the stored procedure. Step through the ​GetFirstDayOfMonth function until you exit back to the stored procedure, and continue to the end. Note: ​You may get back to the stored procedure code by clicking the ​Step Out​ button. In this case, you will continue stepping through the stored procedure. Now, the value of the ​CurrDate variable does not appear. The reason is that the variable is declared in the ​FillSchedule ​procedure, and at the moment you are in a different context. You may add any variable to the Watches window from the current context to track its value. You may click the ​FillSchedule procedure in the ​Call Stack window to get back to the parent code. Notice that the date variable now is unidentified unlike the CurrDate one.160 Debugging Trigger This example shows how to debug a trigger in dbForge Studio for MySQL. Use provided scripts to create sample objects in your test database. Creating a Sample Trigger Database Tables Use the script available in Sample Script to Create Database Tables to create necessary tables. Procedure Use the script available in How To: Debug a Stored Procedure to the procedure used in the sample below. Trigger Use the script below to create the ​ScheduleDetail trigger that generates an error message in case inserted data contains NULL. CREATE TRIGGER scheduledetail  BEFORE INSERT  ON scheduledetail  FOR EACH ROW  BEGIN     SET @TimeSheetDate = new.TimeSheetDate;  SET new.AbsenceCode = ''sicklist'';  IF @TimeSheetDate IS NULL  THEN  RESIGNAL SET MYSQL_ERRNO = 5;  END IF;     END  Debugging a Trigger To debug the MySQL trigger: 1. In ​Database Explorer​, choose your test database. 2. Expand the ​Triggers folder, and then double-click the ​ScheduleDetail trigger to open it.161 3. Change the current view from ​Main​ to ​SQL​. Note: ​While opening a trigger the Main view is set as default. You are not able to insert a breakpoint in this view. 4. Set a breakpoint for the trigger. Click in the gray margin next to the ​SET statement to set a breakpoint in the trigger. This step is obligatory: if you do not set a breakpoint in the trigger, you will skip over its code when you try to step into it. 5. Expand the ​Procedures folder, and then double-click the ​FillSchedule procedure to open it. 6. Set a breakpoint for the stored procedure that will fire the trigger. Left-click in the gray margin next to the ​INSERT INTO​schedule_list statement. 7. Click ​Start Debugging​, and enter input parameters for the procedure. 8. Step through the code using the F11 key or the ​Step Into button, or press ​CTRL+F5 to move directly to the breakpoint. At the ​INSERT INTO schedule_list statement, when you press ​F11​ again, you will step into the trigger. 9. Step through the trigger until you exit back to the stored procedure, and continue to the end. Call Stack Window There are two database objects in the ​Call Stack window: the ​Schedule Detail trigger and the ​FillSchedule procedure. You may notice the yellow arrow next to the ​Schedule Detail trigger which identifies the stack frame where the execution pointer is currently located.162 You can get back to the procedure source code by double-clicking the procedure in the Call Stack window. A green arrow indicates that you have stepped back to the parent code that has called the trigger. You can find detailed information about ​Debugging Stored Routine​ at our documentation center: ​https://docs.devart.com/studio-for-mysql163 16. Optimizing SQL Queries Getting Query Profiling Results To obtain query profiling results using Query Profiler: 1. Open a query file and click the ​Query Profiling Mode button on the SQL toolbar to activate the ​Query Profiler​ tool. 2. Execute the query by clicking the ​Execute button or ​F5​. The Profiler document view opens. See the query profiling results displayed on three tabs - Profile​, ​Plan​ and ​Status​. 3. After you have made changes in the query, click the ​Get New Results button in the ​Profiler document view. New profiling results appear in the tree view as a new node with time and date of query execution. You can easily distinguish and navigate to them. Note: ​When you save the query file after getting the query profiling results, the latter will be automatically stored in the .Design file. Comparing Query Profiling Results Each time you get profiling results for the executed query, they appear in the tree view as a new node with time and date of query execution. When editing your query, you want to know whether your changes reduce query execution time or not. Query Profiler can quickly compare the profiling results. Do the following: 1. Select the nodes of the required query profiling results in the tree view by holding the CTRL​ key.164 2. Click the ​Compare Selected Results button on the toolbar or select the corresponding option on the shortcut menu. The compared results appear in the grid where differences are highlighted.165 17. Comparing and Synchronizing Database Schemas dbForge Studio gives you a perfect opportunity to compare and synchronize schemas with many useful features. They include the following: ● Comparing and synchronizing of two schemas or a schema and a database project. Note, you can’t compare and synchronize two projects. ● Schema comparison separates objects existing only in the Target or Source database, identical objects, and ones having the same name, but with different structure. ● Schema comparison results are displayed in a convenient document, easy for analysis and synchronization. ● Schema comparison is asynchronous. Based on your needs, you can stop it in any moment. ● Schema comparison includes object description. When any errors occur during description, error notifications are shown. You can either stop or retry comparison, or ignore the errors. In case of fatal errors, schema comparison is automatically stopped. ● You can quickly synchronize the Target schema with the Source one or export a schema synchronization script. Comparing Schemas 1. Click ​New Schema Comparison on the ​Database Design tab of the ​Start Page. Alternatively, click ​New Schema Comparison on the ​Comparison menu. The Schema Comparison wizard opens.166 Note: ​Comparing projects is available only in dbForge Studio for MySQL. The actual appearance of the window in dbForge Studio may slightly vary. 2. Select the ​Source and ​Target connections. (You can edit selected connections or create a new one by either clicking the ​Edit button or the ​New button.) To quickly compare schemas, you can click the ​Compare button immediately after setting databases and skip step 3. 3. To adjust comparison process, set the comparison options. 4. To set up the object types you want to compare, switch to the ​Object Filter​ tab. Synchronizing Schemas 1. Analyze comparison results, select the database objects you want to synchronize by checking the corresponding checkboxes. 2. Select the data to synchronize in the grid with comparison results. (To do this, select checkboxes next to tables (or views) in the top grid. You may also select checkboxes next to individual records on the ​Only in Source​, ​Only in Target​, and ​Different Records​ tabs of the bottom grid). 3. Click the ​Synchronize button on the ​Comparison toolbar to open the Schema Synchronization wizard. 4. Select where to output the synchronization script in the wizard and then click the Synchronize​ button. Setting up Schema Comparison Comparing Two Schemas 1. Open the ​New Schema Comparison wizard​ by doing any of these actions: ● Highlight two schemas in the ​Database Explorer window, holding the ​CTRL key, and select the ​New Schema Comparison​ option on the shortcut menu. ● On the ​Comparison​ menu, click ​New Schema Comparison​.167 ● Open a project and in the ​Project Explorer window, right-click the top node of the project tree. Select the ​New Schema Comparison​ option on the menu. 1. Click the ​Type fields and select what you are going to compare (two schemas or a schema and a project) from the drop-down lists. 2. In the ​Connection fields, type or select the names of MySQL Server(s) connections required for comparison. Note: ● If no connections are found, click the ​New​ button to create new ones. When the ​Database Connection Properties​ window opens, specify the properties of the server you want to connect to. ● You can edit the selected ​Source and ​Target connections by clicking the Edit​ buttons. ● In the drop-down lists of the ​Connection fields, you can click the Manage… link to open the ​Connection Manager dialog box. It will help you to create, edit, and delete database connections.168 3. In the ​Database fields, type or select the names of the required databases. Each Database field shows the list of all databases available on the selected MySQL server. 4. (Optional) If you want to ignore some schema options during comparison, go to the Options​ wizard page. 5. Click the ​Compare​ button to close the wizard and start the comparison. Tip: To save your time while selecting ​Source​ and ​Target​ databases, you can copy the details from one side of the wizard to the other by clicking the Copy buttons: to copy Source database details, to copy Target database details ● To swap the Source and Target databases, click the Swap button. ● To close the wizard and cancel the comparison, click the ​Cancel​ button. To compare a schema and a project: 1. Open a project to compare. 2. Select ​Comparison​, and then click ​New Schema Comparison​ on the top menu. 3. In the ​Type​ field of Source or Target wizard part, select ​Project​. 4. Perform steps 3-5 as described above in the ​To Compare two schemas​ procedure. 5. Click the ​OK​ button. Note: ​A schema and a project can’t be compared if a project build is not valid. Synchronization can’t be done, if a project was modified after the beginning of comparison. ALTER TABLE statement where more than one object is modified or where a column is modified. You can find more information about schemas comparison and synchronization at our documentation center: ​https://docs.devart.com/studio-for-mysql169 18. Comparing and Synchronizing Data in Databases Data Comparison Process Overview dbForge Studio for MySQL gives you a perfect opportunity to compare data between different databases with many useful features. They include the following: ● Comparison of data between different databases ● Displaying of data comparison results in a convenient UI, easy for analysis and synchronization ● Quick data synchronization of target and source databases ● Asynchronous comparison and synchronization By default data comparison is performed only for the objects with the same names and owners. Process of choosing corresponding database objects in source and target for comparison is called object mapping. You can use dbForge Studio automatic mapping or map database objects manually. dbForge Studio allows you to customize mapping of tables (views) and columns, so you can compare data of objects with non-equal names, owners, and structure. For more information, see ​Object Mapping​. The following tables and views cannot be compared: ● Invalid tables or views. ● Tables or views, metadata of which can not be retrieved. By default, data is compared by a primary or unique key, but other key or custom column set can be defined. A comparison key is used to determine which rows correspond to each other. It is not recommended to use a non-unique comparison key. If the table does not have a suitable key, you should manually select a custom key for this table when comparing. You should always set a custom key for views when comparing data with the Compare Views checkbox selected. Data comparison can be saved to the file and opened later. Setting Up Data Comparison From this topic you will learn all steps that can be taken during data comparison process and alternative ways for accomplishing routine comparison tasks. 1. In the ​Data Comparison Wizard window, select source and target connection from the drop-down list. Also, you can create new connection by clicking ​New or modify existing ones by clicking ​Edit​.170 2. Select the source and target database. You can easily swap source and target databases by clicking the ​Swap button. (To immediately compare the data, click the Compare button. To tune the comparison, click the ​Next button to move to other wizard pages.). 3. Click ​Next​. 4. On the ​Options page, you can customize your comparison by checking required options. Here you can generate a file with command line arguments. It will allow you to automate data comparison and synchronization using the command line and a task scheduler. Click ​Next​. 5. On the ​Mapping page, you can customize mapping. Use this page if you want to change the default mapping or map tables with different names or columns with different names and structure. Read more about mapping in the ​Object Mapping section. Click ​Compare​. 6. After the comparison process is completed, you can view the results in the data comparison document. You can find detailed information about ​Mapping Tables, Views,​ ​and ​Columns at our documentation center: ​https://docs.devart.com/studio-for-mysql Viewing Data Comparison Results After you have compared data, a Data Comparison document will appear with ​Source and Target connections displayed in the upper part of the window and data comparison results displayed in two grids. The top grid lists all the compared tables and views with values indication the quantity of rows that are different, identical, existing only in ​Target or only in Source​. The bottom grid contains the corresponding data records of the selected database171 object in the top grid. The records are grouped by their status and displayed on separate tabs. They are ​Different Records​, ​Only in Source​, ​Only in Target​, ​Identical Records​, and ​Conflict Records (it appears only in case any conflict records exist). The shortcut menu of the Data Comparison document and its top menu provide a number of options to manage the comparison results. The status bar in the bottom of the grid shows the number of differences per a column. Database objects can be filtered by differences. You can select what objects to show: objects with different, identical records, only in source or only in target records. To view the specific kind of tables: 1. Click the down arrow near the ​Filter​ button on the ​Comparison​ toolbar. 2. Select the corresponding option in the appeared menu. Several options can be selected at the same time. Note: ​In case a table has both different and identical records, it will be visible, independently of the selected options (Different or Identical). You can also filter Incorrectly mapped objects and Correctly mapped objects. To clear filtering: 1. Click the down arrow near the ​Filter​ button on the ​Comparison​ toolbar. 2. Select ​All​ on the appeared menu. The bottom grid allows you to view data differences between the corresponding tables or views. Select a table in the compared database object grid, and the data of these tables appears in the tabs below. The Identical Records tab contains a grid with data rows that are identical and there is no need to update such records in target. Other tabs contain an additional column with checkboxes in the grid. This column is named as action, that will be executed when synchronizing data - ​Update​, ​Delete​, or ​Insert​. By selecting or clearing these checkboxes you can control which rows will be affected with data synchronization. This column will not hide when scrolling grid. Note: ​Incorrectly mapped objects are displayed with icon instead of checkbox in the Data Comparison document. The data of such database objects are not compared. Zero count will be displayed for all kinds of records. Objects with no data to synchronize (with only identical records or with no records at all) are displayed without checkboxes. Only in Source and Only in Target tabs contain grid with Insert and Delete columns respectively and data rows, that are stored only in the table of the source and of the target database respectively. Different Records tab contains grid with the following columns. ● Column with checkboxes. ● Comparison key columns. Their value is the same for the compared tables.172 ● Other table columns. Source columns and target columns are grouped in pairs and marked as (Source) and (Target). When the values of the corresponding columns are different, this pair is highlighted with bold. If a non-unique custom comparison key was chosen for any table or view, the fifth column in main grid and the fifth tab, Conflict Records appear. The tab consists of two grids containing all records with non-unique values of the comparison key from the corresponding database. These records cannot be synchronized. If you cleared Update, Delete, or Insert column checkboxes in some tables, you may view only tables with selected records or only tables without selected records. To filter tables, click the down arrow near the ​Filter button on the ​Comparison ​toolbar and select the corresponding option on the appeared menu. To refresh data comparison results, click the ​Refresh button on the ​Comparison toolbar. To view long text or binary fields, use the ​Data Viewer​ window. Generating Data Comparison Report dbForge Studio provides a possibility to view results of data comparison in the form of a report. Generating Report To generate a comparison report after comparison, perform the following steps: 1. Click ​Comparison on the menu bar and then click ​Generate Comparison Report​. The Comparison Report wizard will be opened.173 Note: ​Actual document may look slightly differently, depending on the product you use. 2. On the ​Format​ page, select the required report format (HTML or Excel). 3. Specify the name of the report file. 4. Specify the location of the report file. 5. Check the ​View the report on completion checkbox to view the report upon creation. 6. On the ​Options​, select required options. 7. Click ​Generate​. Data Synchronization Process Overview After you have compared data, dbForge Studio gives you an easy and convenient way to synchronize data. You may manually specify which tables and even which records to synchronize. Use the checkboxes in the first column of the data comparison document grid to include or exclude objects to synchronization.174 Note that it is highly recommended to backup target database before data synchronization. If you synchronize data with different data type, you may encounter synchronization warnings. They are displayed at the Summary, Data Synchronization Wizard. If you have any synchronization warnings, that means that during synchronizing data you may encounter errors or data loss because of rounding, truncation, etc. The Data Synchronization wizard allows you to either apply updates immediately or create an update script for the Target database and save it to a file. Before starting the synchronization, you may view Action Plan that contains all the actions that will be performed during synchronization. The action plan represents the synchronization script structure, so it is good to analyze it before this script will be generated or executed if you have chosen to execute the script immediately. The action Plan is displayed on the Summary page of the Data Synchronization wizard. Synchronizing Data Note: ​Data synchronization can not be rolled back! It may change or delete data in your target database. It is highly recommended to backup target database before synchronizing data. Synchronizing databases To start synchronizing data of two databases: 1. Select the data to synchronize. To do this, select checkboxes in the column with checkboxes for the tables (or views) you want to synchronize. You may also click a separate table and select checkboxes in the column with checkboxes on the Only in source, Only in target, and Different tabs for individual records to determine which records to synchronize. Use the checkbox in the header of the grid to apply settings to all the grid rows.175 Note: ​Incorrectly mapped objects are displayed with icon instead of checkbox in the Data Comparison document. Objects with no data to synchronize (with only identical records or with no records at all) are displayed without checkboxes. You cannot select them for synchronization. 2. On the ​Comparison toolbar, click ​Synchronize​. The Data Synchronization Wizard will be opened. 3. Select whether to backup target database and backup files location. Then select whether to execute a script as a single transaction. You may click the Synchronize button and skip next steps to open the synchronization script in the editor, but this is not recommended if the script is too large. See the ​Executing Large Scripts topic to know how to execute script without opening it in the editor. Click the ​Next​ button. 4. Select where to output synchronization script - open in the editor, save for the future execution, or immediately execute it. 5. You may either click the ​Synchronize button to generate the update script or click the Next button to set synchronization options and view the synchronization action plan and synchronization warnings if any exist, and then click the ​Synchronize button. Comparing and Synchronizing Data through the Command Line You can automate data comparison and synchronization using the command line. Running Comparisons from the Command Line 1. Click ​Start​. 2. Type ​cmd​ in the ​Search​ programs and files text box and press the ​ENTER​ button.176 Note: ​To run the Command Line in Windows 8: From the Desktop Mode ○ Move the mouse cursor to the exact lower left corner until the desktop shortcuts menu appears. ○ Right-click to see the shortcut menu and then click ​Run​. From the Charms Bar ○ Move your mouse to the upper right corner until the Charms Bar appears. ○ Select Apps from the list and type run in the search box. ○ Click Run from the search results. Use Windows + R Shortcut Keys ○ In desktop mode, press the Windows + R keys, to show the Run command line. 3. Type a path to the dbforgemysql.com file located in the dbForge Studio for MySQL installation folder on your PC, and press the SPACE. ​C:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com 4. Specify command line arguments (connection strings and actions you want to run). For example, specify the type of the operation.   C:\Program  Files\Devart\dbForge  Studio  for  MySQL>dbforgemysql.com  /datacompare /? 5. Specify the objects that will be compared and synchronized.   C:\Program  Files\Devart\dbForge  Studio  for  MySQL>dbforgemysql.com  /datacompare /compfile:"D:\mycompdoc.dcomp"  6. Synchronize data directly into the database and generate a synchronization script file.   ​C:\Program  Files\Devart\dbForge  Studio  for  MySQL>dbforgemysql.com  /datacompare /compfile:"D:\mycompdoc.dcomp" /sync:"D:\mysync.sql" Note: ​To synchronize data automatically, use /sync without file name specification: ​C:\Program Files\Devart\dbForge Studio for MySQL>dbforgemysql.com  /datacompare /compfile:"D:\mycompdoc.dcomp" /sync 7. Generate a comparison and synchronization protocol into a file.   C:\Program  Files\Devart\dbForge  Studio  for  MySQL>dbforgemysql.com  /datacompare  /compfile:"D:\mycompdoc.dcomp"  /sync:"D:\mysync.sql"  /log:"D:\mylog.log"   8. Compose a comparison report.   C:\Program  Files\Devart\dbForge  Studio  for  MySQL>dbforgemysql.com 177 /datacompare  /compfile:"D:\mycompdoc.dcomp"  /sync:"D:\mysync.sql"  /log:"D:\mylog.log" /report:"D:\myreport.html"   9. Specify the report format. HTML and EXCEL formats are available.   C:\Program  Files\Devart\dbForge  Studio  for  MySQL>dbforgemysql.com  /datacompare  /compfile:"D:\mycompdoc.dcomp"  /sync:"D:\mysync.sql"  /log:"D:\mylog.log" /report:"D:\myreport.html" /reportformat:HTML  10. Press ​ENTER​ to run the process. Tip:​ To execute the synchronization script, use the /execute switch: C:\Program  Files\Devart\dbForge  Studio  for  MySQL>dbforgemysql.com  /execute  /connection:"User  Id=root;Host=db;Port=3320;Database=test;Character  Set=utf8"  /inputfile:"D:\mysync.sql" Generate File With Command Line Arguments To generate a file with command line arguments, open the ​Data Comparison​ wizard. To access the Data Comparison Wizard, on the ​Comparison menu, click Select ​Source and ​Target for comparison. Go through other wizard pages to tune the data comparison. On the ​Options tab, click ​Save settings to a command line arguments file and specify the file name and location to save the file. The selected Source and Target connection strings along with comparison options will be saved as command line arguments in the file. You can edit the values of comparison options in the file. Note that the generated file does not contain password information for security purposes. You should edit the file and enter the passwords manually. You can find detailed information about ​Additional /datacompare Arguments​ at our documentation center: ​https://docs.devart.com/studio-for-mysql The command line string with /argfile parameter will be written as follows: ~~~  C:\Program  Files\Devart\dbForge  Studio  for  MySQL>dbforgemysql.com  /argfile:file_name.txt  ~~~ 178 Scheduling Database Synchronization You can use Windows Task Scheduler to create and manage synchronization tasks that your computer will carry out automatically at the times you specify. Create Synchronization Task To create Windows task: 1. Run the ​Windows Task Scheduler​. Note: ​To run the Windows Task Scheduler: 1. Click ​Start​ and then click ​Control Panel​. 2. Click ​Administrative Tools​. Tip: ​If your current view is ​Category​, click ​System and Security​, and then click Administrative Tools​. ● Click ​Task Scheduler​. 2. Click the ​Action​ menu, and then click ​Create Basic Task​. 3. Type a name for the task and an optional description, and then click ​Next​. 4. Do one of the following: ○ To select a schedule based on the calendar, click Daily, Weekly, Monthly, or One time, click Next; specify the schedule you want to use, and then click Next. ○ To select a schedule based on common recurring events, click When the computer starts or When I log on, and then click Next. ○ To select a schedule based on specific events, click When a specific event is logged, click Next; specify the event log and other information using the drop-down lists, and then click Next 5. To schedule a program to start automatically, click ​Start a program​, and then click Next​. 6. Click Browse to enter a path to the datacompare.com file in Program/script. (The default path is C:\Program Files\Devart\dbForge Studio for MySQL.) 7. Also, make sure to specify arguments in the Add arguments field. 8. Click ​Next​. 9. Click ​Finish​. You can find detailed information about ​Options​ ​and ​Exit Codes Used in Command Line for /datacompare​, and ​Parameters Used in Connection String​ at our documentation center: ​https://docs.devart.com/studio-for-mysql179 19. Copying Databases With dbForge Studio, you can easily transfer both, multiple database schemas and data from one server to another. To copy databases 1. Click ​Copy Databases on the ​Database menu. Alternatively, click ​Copy Databases on the ​Administration tab of ​Start Page​. Also, the Copy Databases option is available on the ​Database Explorer shortcut menu. For instance, you can right-click several databases from the source connection and the target connection node, point to ​Tasks​, and then click ​Copy Databases​. 2. Select source and target connections. You can use the search text box to locate the required database. 3. Optionally, click to change the default Copy Database Settings. For instance, you can change the maximum number of simultaneous database copings, include or exclude triggers, partition clause, etc.180 4. In the ​Source​ column, select database (databases) you want to copy. 5. To copy data along with schema, select a checkbox in the ​Include Data column next to a database you want to copy. 6. Optionally, select the ​Drop if exists on target to drop databases on the target connection if they duplicate the source databases. 7. Optionally, you can modify the name of the target database. For this, click the database name in the ​Target​ column of the window and type a new name. 8. Click to start copying the selected databases to the target server, and wait till the progress is complete. You can find detailed information about ​Copying Databases through the Command Line​ ​and ​Options Used in Command Line for /copydatabase​ at our documentation center: ​https://docs.devart.com/studio-for-mysql181 20. Developing Database Projects Database Project Overview dbForge Studio for MySQL comes with an extensive project management system that allows you to create database projects and integrate related database development files. dbForge Studio database projects greatly facilitate database development and allow offline database development. Database project can be created from the scratch or imported from an existing database. Database development is clear and logical within a database project. It allows you to do the following: 1. Create a database project guided by the wizard, either empty or with database objects imported from a required database. 2. Manage SQL scripts and query files in the project and edit their contents. 3. Build ready project to create a script that will deploy all project changes to the database on the server. 4. Deploy either the entire project or only required changes to the server. Project building allows you to create a single ready-to deploy script for deploying your database to the server. Building project includes the following: ● Checking SQL syntax of all files from the project. ● Checking references. ● Applying project options to the scripts if necessary. ● Combining project scripts to a single script or generating run script that executes project scripts in a certain order. For more information about project building see ​Building and Deploying Project​. Checking SQL syntax and checking references ensure that your project is valid without using the server connection. Project allows you to manage such items as SQL and query files and database objects easily. It allows you to group scripts and queries into folders and provides fast access to selected database objects. You can also work with schema objects, generated by the project scripts. These objects are displayed in the Schema View window. You can delete such object. When you work with project and exit dbForge Studio for MySQL, it remembers all opened documents, even files and database objects that do not belong to the project, and opens182 them next time when you open this project. Project remembers also all document bookmarks, breakpoints and watches that can help you to restore previous debug session. Note that when you close the project, all opened documents are closed too. Project folder and file structure, connection and database object links, build order is stored in file with extension. Temporary information about opened documents, bookmarks, breakpoints, watches is stored in the file, having the same name and extension ​user​. Benefits of database projects It is recommended to use database projects as they fully meet modern database development standards. ​Projects allow using version control system for database team development​, which is critical for efficient development. As much work in database development accounts for working with SQL scripts, database projects offer ​a great number of features for quick and convenient SQL editing​. You will appreciate automatic syntax check in SQL scripts, check of schema objects references in all statements, check of any object duplicates, etc. Settings and syntax differences in various versions of servers can put obstacles in the phase of deploying the ready tested database to the server. ​Projects can have multiple configurations​, they allow you to customize the database project for deploying to various versions of server. You should select the required connection and settings in a project configuration once, and next time just use the required configuration.183 Project and Schema Synchronization Overview With synchronization of schema and project you can perform incremental project deployment. That means, if you change your project after deploying it, you don’t need to redeploy the entire database or edit and apply the changes manually. You may compare schema and project and then synchronize schema with the project. Only necessary schema changes will be performed. You may preview these changes in the synchronization script. To quickly compare project with its schema in database, click the ​Synchronize With Server button on the ​Project​ toolbar. You also can synchronize a project with a schema. If you made changes to a database, and want to have them in project, you don’t need to re-import the whole project from a database, use the schema synchronization for it. Project can be the source or the target in comparison (but not both of them at the same time). To compare schema with a project, you should open this project first. And when opening the comparison document with schema and project comparison, the project should be opened. Project is being built automatically when comparing with a schema, so if the project contains errors and cannot be built, it cannot be compared with a schema. Note: ​When comparing project and schema, project build results are used for DDL comparison. If the file is not selected in the Build order, the objects from this file will not appear in the comparison. If you set MySQL version, that does not support some database objects from the project, in the Database options, unsupported objects will not appear in comparison, even if they are supported in the compared database. Project Build Overview Building project is a complicated processing of project scripts which results in one ready-to-deploy result script or in several scripts with batch file. At first, project script syntax is checked. If project scripts have syntax errors, the build is stopped. If the declaration cannot be found, an error or warning is raised. Warning is raised in the following cases: ● Database object from schema, other than project schema, is referenced, and this object cannot be found in the schema. ● Unresolved reference is in the DROP statement. ● User is referenced.184 In all other cases an error is raised and build is stopped. You may enforce build to ignore errors by selecting the Ignore errors checkbox in the Build options. After this, project scripts are processed according to build order. You may forbid applying Depending on build options, either single result SQL script is formed or batch script, calling SQL scripts is generated. Note: ​Project build will delete all SQL files having the same names as project files in the build output directory without user confirmation. dbForge Studio asks for confirmation before deleting any other SQL file. Non-SQL files in the build output directory are not affected by build. Build output path can be set in the Build options. Building and Deploying Project Building Project After you have edited your database project, you should build it to create a script that will deploy all the project changes to the database on the server. A project can be built to a single ready-to-deploy script or a set of scripts with a batch to run it in the MySQL Command-Line Tool. To build a project: 1. Choose a project configuration to build on the ​Build tab of the ​Project Properties window. (To open the window, click ​Properties​ on the ​Project​ menu). 2. Right-click in the ​Project Explorer window and select ​Build the Project on the shortcut menu. Project building is enhanced with the following features: ● Syntax check in project scripts. (If any errors are found, the build is stopped.) ● Check of references to schema objects in all statements. ● Check of any object duplicates. Project options to customize build results Before building the project, you can set project options to customize build results. 1. Right-click in the ​Project Explorer window and select the ​Properties option, or on the ​Project menu, click ​Properties​. The Project Properties window opens. It contains three tabs: Build, Build Order, and Database.185 2. Use the ​Build tab to define the settings for the generated SQL file with project changes. For example, you can select whether to generate a single SQL script file or a batch script file, etc. 3. The ​Build Order tab lets you manage the order of files in the build script and deploy execution. You can manage dependencies between objects in different files using build order. For example, table film2 in film_text.Table.sql references table film1 in film_Table.sql, so you should move film_text.Table.sql to locate under film_Table.sql on the Build Order tab. Note: ​Actual document may look slightly differently, depending on the product you use. 4. Move to the ​Database tab to manage target database settings. For example, you can set ​Server version​ to generate SQL statements, using syntax of this server version. Note: ​Actual document may look slightly differently, depending on the product you use. Deploying Project to Server You can deploy either the entire project or only the changes in the project to the database on your server.186 Note: ​Project files can be deployed only if a connection was assigned to the project. Deploying an entire project To deploy an entire project, click ​Deploy the Project​ on the ​Build​ menu. Use Always re-create a database option to guarantee database integrity. Deploying changes from your project When you want to deploy only the changes from your project to the server, you should compare your project with the database on the server and create a special schema synchronization script. The script will deploy only required changes to the server. 1. Right-click in the ​Project Explorer window and select the ​Synchronize Project with Server​ option. The document with comparison results opens. Note: ​Actual document may look slightly differently, depending on the product you use. 2. By default, the synchronization actions are automatically selected for each compared object. You can change them, for example, by selecting the ​Skip action to prevent applying the change. 3. On the ​Comparison menu, click ​Synchronize to deploy the project changes to the database.187 Synchronizing changes from the database on the server with the project To synchronize changes from the database on the server with your project, do the following: 1. Right-click the required database in the ​Database Explorer window, and select ​New Schema Comparison​. The wizard opens. 2. Select the database on the server as ​Source​ and your project as ​Target​. Note: ​Actual document may look slightly differently, depending on the product you use. 3. Click ​OK​ and see the comparison results in the opened window. 4. Analyze the differences. By default, the synchronization actions are automatically selected for each compared object, but you can change them, if required. 5. Click the ​Synchronize button on the toolbar to synchronize the database with the project. While comparing, the project can be either Source or Target. Note that the project is automatically built during comparison, so if any errors occur during this process, the comparison fails. You can find detailed information about ​working with database projects​ at our documentation center: ​https://docs.devart.com/studio-for-mysql188 21. Writing Data Reports Data Reports Overview dbForge Studio for MySQL offers the possibility of creating reports implemented in the well-known and user-friendly interface. With dbForge Studio, the routine of report creating is simplified to several clicks - that means you won’t have to spend a lot of time and efforts for report producing any more. What is Data Report? Data report is a formatted representation of data that can be displayed on a screen, printed or saved to a file. Reports allow getting the needed data from a database and representing it in a human-readable form, and also providing a lot of capabilities for data generalization and analyzing. When printing tables and query results data is presented in nearly the same form as it is stored. So, it is often necessary to present it in the form of a report that has traditional appearance and is easy to read. Such reports include all information from a table or returned by a query, but also contain headers, are repaginated etc. Data Report Creation in dbForge Studio for MySQL Of course, reports are used in bookkeeping. In fact, one can not imagine bookkeeping without reports. So, accountants usually need a report generator to automate the process of report creation and avoid any mistakes. dbForge Studio provides easy-to-use and fast solution for this goal which includes fully-functional report designer, Data Report Wizard for creating different types of reports, and a lot of report examples. Below is a list of the main features for report creation in dbForge Studio: Binding Report to Data - it is possible to create a data source, fill it with data and supply it to a report. Custom Query​ - you can upload an existing query or create a new one to use it in a report. Relations Editor - a comprehensive relations editor allows you to create, change, and remove relations between tables and/or views, and queries.189 Parameterized Queries - support for parameterized queries with the possibility to edit parameters in Parameter Editor. This editor allows you to add and remove parameters and edit their properties. Easy Data Source List Population - data source list can be populated in many ways. To add a data source you can drag-and-drop tables or views from the Database Explorer, use the dialog window for adding tables and/or views, or create a custom query. Mail Merge - it is possible to insert a data field name in brackets right into controls’ text, and these fields will be populated from the specified data source when a document is created. Data Filtering - data can be filtered at several levels data adapter, data set or data view levels. Data Grouping - report bands serve for implementing grouping, making the process easier and faster. Set of Controls - a wide range of available controls allows to display various kinds of information in a report. Chart Control​ - a special control for adding charts to your report. Enhanced Rich Text Control​ - it’s possible to load data from *.txt and *.rtf files. Large Number of Band Types - 10 different band types to display controls on any part of any page. Master-Detail Reports - master-detail reports can be created quickly and easily by editing relations between data tables. Data Source - drag fields from the Data Source window and drop them onto the designer area to create controls which will be automatically bound to an appropriate field. Drop necessary fields onto already created controls to bind them to the fields. Document Outline​ - shows the report structure and is used to navigate within a report. Data Report Toolbar - a toolbar with a set of buttons to operate text options and to call report designer windows, also contains a button to call Data Report Wizard. Toolbox​ - the window holding controls available in dbForge Studio data reports. Event Support - allows to create event handlers at the Report, Band and even Control levels. Advanced Styles - styles provide the possibility to control the look of your report. Styles can be applied conditionally to reflect certain states. dbForge Studio provides a convenient Style Editor.190 Summary Support - to create a summary for textbox or table cell set only two properties - Summary position and Summary type. In-Place Editing - possibility to change the text properties of controls (label, Rich Text, Table Cell) using in-place editing. To use in-place editing, just double-click the needed control. Wide Range of Exporting Formats - possibility to export report documents to different formats like PDF, HTML, MHT, RTF, TXT, CSV, and MS Excel. Also a report can be exported to an image file types, like BMP, EMF, GIF, JPEG, PNG, TIFF, WMF. Save and Load Report Definitions​ - possibility to save and load report definitions. Scripting​ - possibility to use C#, Visual Basic .NET and JScript .NET scripts in reports. Watermarks - a report can contain a collection of watermarks for all or specified report pages. Search in Preview - the possibility to call a search dialog in the preview mode to search a report for specific text. Bookmarks - support for bookmarks makes your report easier to navigate. This structure can also be exported along with the report to PDF, HTML and MHT file formats. Report Wizard - easy to use, convenient, yet powerful wizard to reduce the time you spend on customizing report. Measurement - support for imperial (inches) and metric (centimeters) units of measurement. Use the ReportUnit property to set the measuring system for your report. Report Building Basics This topic describes the main concepts of reports construction in dbForge Studio. It explains the basics of report creation, describe the structure of a simple report. To learn more on the report elements, see the Report Bands and Report Controls topics. The following diagram illustrates the report’s life cycle in dbForge Studio:191 Every report in dbForge Studio consists of bands, and every band contains report controls inside it. Reports can be either bound to data, or unbound. Unbound reports may contain text, images etc. provided manually without using any data source. Bound to data reports are used to present required information in an easy to read form, so this is the most important feature of all report generators. To create a data bound report, bind the report to a data source and then specify data binding options for report controls. A report can be created using ​Data Report Wizard or ​Blank Data Report option. It is possible to save the created report to an RDB file (for more information refer to the Saving and Loading Reports topic). If a report uses stylesheets, they can be saved to an REPSS file. Also you can load styles from a created earlier REPSS file. After a report was created you can preview, save, and print it. You can find detailed information about ​Creating a Static Report​, ​Creating a Simple Data-Aware Report,​ ​and ​Creating a Master-Detail Report​ at our documentation center: ​https://docs.devart.com/studio-for-mysql Designer Elements The following image shows how typical Report Designer and its elements look like: Note: ​Actual document may look slightly differently, depending on the product you use.192 The table below gives a brief description of each element marked on the image: ID Name Description 1 Report Provides quick and easy navigation through the report elements. Structure 2 Data Report Contains buttons to change font properties of the selected Toolbar control, buttons to access the Toolbox, Data Source, and Group and Sort windows, and also provides capability to zoom in or out the Report Designer surface. 3 Toolbox Contains controls available in dbForge Studio for MySQL data Window reports. 4 Smart Tag With smart tags, report controls and bands are enabled to display context-sensitive information and commands. 5 Band Strips A strip with band name, band icon, and the Expand/Collapse button displayed on the top of every report band. Strips are not taken in account when generating a report document. 6 In-Place To invoke this editor, double-click any text-aware control in Editor Designer. To adjust font and color options use Data Report Toolbar (1). 7 Shortcut Right-click any element when designing a report to invoke Menu shortcut menu. It provides access to the most popular options depending on the context. 8 Data Source Displays the schema of the data source currently bound to a Window report. 9 Group and Serves for applying grouping and sorting to a report. Sort Window You can find detailed information about ​Measure Units​, ​Report Bands​, ​Report Controls​, ​Smart Tags​, ​and ​Context Links​ at our documentation center: https://docs.devart.com/studio-for-mysql193 22. Documenting MySQL Databases To Generate a Database Document: 1. Click ​New Documenter…​ on the ​Start​ Page or on the ​Tools​ menu. 2. The ​Select Connections dialog box opens. Select one or several existing connections or create a new connection or select an existing one from the list. Click Select​. The Database Documenter project opens. 3. Optionally, select a style for the document in the ​Style drop-down list box. By default, the ​Default​ style is used. 4. In the Structure pane, select ​Cover Page to setup the cover page of the generated document.194 5. In the ​Structure pane, select databases, object groups and database objects for documenting. 6. Optionally, configure documentation by including/excluding sections and properties of documented databases and database objects. dbForge Studio for MySQL allows you to configure elements of documentation on several levels, including: ● Servers level ● Server level ● Databases level ● Database level ● Objects group level ● Database Object level ● Users level Note: ​You can save the Documenter project file at any time and then reuse it. For this, click ​Save​ on the ​Main​ toolbar. The Documenter project files have the .ddoc​ extension. To load the saved file, click ​Open File​ on the ​Start​ page, or just click its name in Recent Projects​.195 7. Click ​Generate to start generating the document. The Generate Database Documenter dialog box opens. 8. Select format and directory for the document file. 9. Click ​Generate​. You can find detailed information about ​Documenting Databases through the Command Line​ ​and ​Exit Codes used in Command Line for /document​ at our documentation center: ​https://docs.devart.com/studio-for-mysql196 23. Creating Command Line Execution File dbForge Studio for MySQL includes the ​Command Line Settings wizard that allows you to create command line execution files. The option is useful for performing routine tasks: you can simply run the created .bat file instead of configuring all options and parameters every time. To create a command line execution file: 1. Run the Command Line Execution File Settings dialog box: ○ For Data Comparison: click Save Command Line in the Data Synchronization wizard. ○ For Schema Comparison: click Save Command Line in the Schema Synchronization Wizard. ○ For Data Export: click Create Command Line in the Data Export Wizard. ○ For Data Import: click Create Command Line in the Data Import Wizard. ○ For Large Script Execution: click Save Command Line in the Execute Script Wizard. ○ For Data Reports: click Save Command Line in the Data Report Wizard. ○ For Data Generation: click Save Command Line in the Data Population Wizard.197 2. In the dialog box, configure the command line settings. The settings range varies, and depends on the feature. 3. Optionally, click ​Validate​ to check the validity of the command line syntax. 4. Click ​Save​ to save the command line execution file. You can find detailed information about ​User Interface Concepts​ and ​Manipulating Documents​ ​at our documentation center: ​https://docs.devart.com/studio-for-mysql198 23. Performing Administration Tasks Controlling Server Variables dbForge Studio for MySQL allows you to view and modify MySQL system and status variables with the ​Server Variables ​window. To call the Server Variables window, select Server Variables​ on the ​Database​ menu. System variables indicate how the MySQL server is configured. Some of them can be changed dynamically when the MySQL server is running. Status variables provide information about MySQL server operation. Their values cannot be changed. To switch between system and status variables, click the ​System or ​Status buttons on the Server Variables window toolbar. To change variable value, click in the ​Value column for the required variable row and edit variable. Press ​ENTER to apply changes or ​ESC to discard them. Only values of variables with names, marked with bold font, can be changed. Variable can be copied to the clipboard with its name, value and description by right-clicking on it and selecting ​Copy on the shortcut menu. Variables can be grouped by category and exported to an external file for viewing or editing with ​Data Export Wizard​ by clicking the corresponding button on the toolbar. Managing Services dbForge Studio for MySQL allows you to manage MySQL services on local and remote computers. You can start, stop and restart services, associate connections with them. Use the ​Service Control​ window to perform these actions.199 To show Service Control window, select ​Service Control​ on the ​Database​ menu. To manage services, you should add them to the Service Control list at first. Adding Services to the Server Control List To add services to the Server Control list perform the following actions. 1. Click the ​Add Service​ button on the ​Service Control​ toolbar. 2. Specify required ​Host​ in the appeared dialog. 3. Select ​Show all services​, if you need to add services with name that does not contain a string ​MySQL​. 4. Click the ​Scan button. If you have no enough privileges, you will get corresponding message. 5. Select required services and click the ​Add​ button. Note: ​When opening the Add Services dialog box, last entered host will be automatically scanned for MySQL services. The host is also scanned for services when you select it in the ​Host​ box. Associating Connections with a Service Connections can be associated with a service. After associating, connection will close when the service is stopped. To associate connections with a service, perform following steps. 1. Click the ​Associated Connections​ button on the ​Service Control​ toolbar. 2. In the appeared dialog box, select required connections. 3. Click ​OK​. Manipulating Services in the Server Control List After you added required services to the ​Service Control list, you can start, stop and restart them by clicking the corresponding button on the ​Service Control​ toolbar. To refresh the state of the services, use the ​Refresh​ button. To remove a service from the Service Control list, select it and press the ​DELETE key or right-click it and select ​Remove service​ on the shortcut menu.200 Controlling Sessions Prerequisites In order to complete this walkthrough, you will need to use access MySQL server 5.0.13 or higher. Starting Session Execute the following query. SELECT * FROM   demobase.emp e1,  demobase.emp e2,  demobase.emp e3,  demobase.emp e4,  demobase.emp e5  To do this, perform the following steps: 1. Open an SQL editor by clicking the ​New SQL​ button on the ​Standard​ toolbar. 2. Type or paste this code to the document. 3. Execute the document by clicking the button on the SQL toolbar. Managing Sessions Viewing Session Information Launch another instance of the dbForge Studio for MySQL (don’t close the previous one) and open the ​Session Manager ​window in it. To call the ​Session Manager​, select ​Session Manager​ on the ​Database​ menu. If you have the global ​PROCESS privilege, you will see all user sessions for the current connection. Otherwise, you will see only your own sessions. Click on the Host column caption to order all data by host. Find your connections (from your host). Then click on the connection which has ​Query in the Command column. In the bottom part of the Session Manager you can see the text of the queries being executed. Find your connection executing the query above. Killing Session and Query Right-click the session with our query and choose ​Kill Query on the shortcut menu. Click Yes in the appeared dialog. Then switch back to the previous instance of dbForge Studio for201 MySQL and scroll down the data grid. You will get a message ​Query execution was interrupted​. Switch to the dbForge Studio for MySQL with Session Manager open. Again right-click the session and choose ​Kill Session on the shortcut menu. Switch back to the previous instance of dbForge Studio for MySQL and click the ​Refresh button on the Database Explorer toolbar. You will get a message ​Lost connection during query​. If you have the global SUPER privilege you can kill queries and sessions of all users, otherwise, only your own sessions and queries. You can find detailed information about ​Performing Maintenance and Flush Operations​ at our documentation center: ​https://docs.devart.com/studio-for-mysql">