A database schema is a set of objects such as tables, views, procedures, and functions that cannot be updated without modifying data. dbForge Studio for MySQL allows linking and version-controlling database schemas and static table data in the Source Control Manager.
Static data is a set of fixed values that do not change often or cannot be changed by users. For example, static data can include reference data, code, lookup tables, configuration data, company department abbreviations, geographical locations, and so on.
Since static data can affect the database performance and proper functioning, is a good practice to keep track of what has been changed. This allows developers to access, edit, and share database objects with their static data within a single tool. Version-controlling static data with Source Control also helps optimize database development.
Note
It is important to link static data to source control to prevent any data loss when updating your local database with the latest changes from the remote repository or reverting database changes made to the object and static data in a database.
Before you use the Source Control Manager, you must link a database to the version control repository by following the steps outlined in the relevant how-to topic:
The linked database gets the following icon in Database Explorer:
The Source Control tool checks if there have been any changes made to the database locally or remotely and verifies whether these changes have been committed to the repository. In the same way, it checks if any changes from the version control have been deployed to the linked database.
In addition, the tool identifies conflicts that may occur if several developers are making changes to the same file both in the repository and the local database.
The Refresh progress window opens automatically showing the stages of the refresh operation.
After the refresh operation is complete, the Source Control Manager opens displaying all the changes either in all three sections, two of them, or just one section.
When multiple users are simultaneously making changes to the same file, such as reference or contact data, it leads to a conflict. In such cases, a version source control system cannot automatically decide whose changes should be applied and defines this situation as a conflict. To move forward, users must resolve the conflict before either committing the changes to the repository or updating the database with the latest changes from the remote repository.
So, you can view the conflicting changes in the Different category at the bottom panes: the left pane displays the local changes, while the right one shows the remote changes. To resolve the conflict, do either of the following:
Select the static data and click Get Remote to update your local copy with the changes from the remote repository. These changes will then appear in the Remote changes section of the Source Control Manager, and the change type will turn to Modify from Conflict. Next, select the changes and click Get Latest as described in Get the latest version. This action will synchronize the local database with the database from the remote repository.
Select the static data and click Get Local to overwrite the remote repository data with local changes. These changes will then appear in the Local changes section of the Source Control Manager, and the change type will turn to Modify from Conflict. Next, select the changes and click Commit as described in Commit changes. You can also roll back the local changes that have not been committed yet by clicking Undo.
Note
Conflicts can occur only in the dedicated database development mode.
The Remote changes section displays the changes from the remote repository. Clicking Get Latest will update your local repository with these changes. For more information, see Get the latest version.
The Local changes section displays the changes made locally. You can select the static data, write a comment, and click Commit. This will update the linked database with the latest version of the database stored in the remote repository. For more information, see Commit changes.
Static data must be linked to version control before the deployment. To link static data:
1. In Database Explorer, right-click the database containing required tables with static data and select Source Control > Link / Unlink static data.
2. In the Source Control Link Static Data dialog that opens, select the required tables with static data and click Apply.
Note
You can link and source-control only tables with properly defined primary keys.
The Refresh dialog opens automatically showing the stages of the refresh operation.
4. Once the refresh is complete, the Source Control Manager opens. Navigate to the Local Changes section and select the linked tables with static data. Then, write a comment and click Commit.
Note
Local changes section
- The Add change type indicates that the static data was created locally in the database that is being version-controlled using Source Control and has not been committed to the remote repository yet. Selecting the static data and clicking Undo will remove it from the local database.
- The Remove change type means that the static data was deleted from the local database that is being version-controlled using Source Control but still exists in the remote repository. Selecting the static data and clicking Undo will restore it in the local database.
- The Modify change type indicates that changes were made to the static data in the local database that is being version-controlled using Source Control. As a result, the DDL of the object with the static data in the local database differs from the version stored in the remote repository. Selecting the static data and clicking Undo will discard the changes made to the data in the local database, reverting it to the version from the repository.
Remote changes section
- The Add change type indicates that the static data has been added to the remote repository since the last synchronization and does not exist in the local database linked to this repository via Source Control. Clicking Get Latest will add the static data to the local database.
- The Remove change type means that the static data has been removed from the remote repository since the last synchronization but exists in the local database linked to this repository via Source Control. Clicking Get Latest will remove the static data from the local database.
- The Modify change type indicates that the static data has been modified in the remote repository since the last synchronization. As a result, the DDL of the object with the static data in the repository differs from the version stored in the local database linked to this repository via Source Control. Clicking Get Latest will update the static data in the local database to match the version from the repository.
All changes can be conveniently viewed and selected on the following tabs, as shown in the screenshot above:
Tab | Description |
---|---|
All | List of all records |
Added | List of records that will be added |
Changed | List of records that will be modified |
Removed | List of records that will be removed |
Identical | List of records that will remain identical |
Note
Source-controlling table data, including operations such as Get Latest, Commit, and Refresh, may be time-consuming. Thus, it is highly recommended to source-control smaller data sets. Committing large static data tables with numerous records can potentially impact the performance of dbForge Studio for MySQL or even lead to a time-out.
1. In Database Explorer, right-click the required database and select Source Control > Link/Unlink Static Data.
2. Clear the Table checkbox and click Apply.
Alternatively, you can unlink static data by unlinking the database from source control. To do this, in Database Explorer, right-click the required database and select Source Control > Unlink Database from Source Control. For more information, see Unlink a database from source control.