The Design Model & Schema Synchronization
DbSchema uses its own model with a copy of the schema structure, separate from the database.
The DbSchema model can be created by reverse engineering schema from an existing database or by designing the schema from scratch.
Working with a DbSchema model means that:
-
Your schema design is database-independent. This enables you to deploy the schema on multiple databases, generate migration scripts, etc.
-
To ensure the schema is the same as in the database choose 'refresh schema from the database' from the DbSchema menu. Read more about the
synchronization process.
-
The schema and layouts will be saved to the model file. You can open the schema without being connected to the database
and pick up where you left off. After you connect to the database, you can synchronize the schema.
-
You can work on the schema while connected to the database (online) or without a database connection (offline)
-
In the online mode, all the changes made to the schema will be applied to the DbSchema model and in the database.
The executed statements can be seen
on the left side of the screen, in SQL History Pane.
-
In the offline mode, changes will be applied only to the DbSchema model. When you reconnect to the database
you have to press 'Refresh the schema from the database'.
Create or Upgrade a Schema into the Database
Before starting this operation, please save the model to a file. In this way, if you encounter problems,
your schema won't be lost.
If you created a schema from scratch and want to update it in the database, all you have to do is:
- Click on Schema in the upper menu.
- Select Create or Upgrade Schemas from the Database.
- If you are not connected to a database, the next step will be to establish a connection. Learn more
about how to connect to a database here.
- After establishing a connection, choose the schemas that you want to create/ update.
- After executing the script, your schema will be updated in the database.
Synchronize the Model with any Database
These features are useful if you work on the schema without being connected to the database.
-
Select the Refresh button from the upper tool menu;
-
If your schema is not synchronized, the next window should pop-up
- If you choose Refresh, the changes will be taken over into your local model;
- The Review Changes button will open the Sync Dialog
Convert Schema to a Different Database
What does it mean to convert a schema to a different database?
- Convert the data types to the new database.
- Re-Write the triggers, functions, and procedures. This has to be done manually.
Changing the databases will modify the internal model column types. We recommend that you make a copy of the
current model before you change the database.
-
Open Model -> Model Properties;
-
Insert a new name for your schema;
-
Select the new database from the RDBMS field. Click Ok;
-
A new dialog will appear. This shows you how the data types will be converted from the initial database
to the new one;
-
Save the model to a new file and connect it to the new database.
Share the Model in a Team
The DbSchema model (.dbs) is an XML file, that can be opened in any text editor. This file can be stored in a
version repository like GIT, Mercurial, SVN, CVS, etc. Multiple developers can work on it and synchronize the changes.
Synchronize the Model with other Model Files
Model files store the schema, so why not to synchronize it with another model file? Suppose
you develop a schema, save it to the model file and store this file in a GIT or other versioning repository. Later you want
to see the schema differences between the two different versions. You can open one file
in DbSchema and compare it with the second. Then you can generate the migration script from one to the other.
Synchronization Filters
In the Synchronization Dialog, there is a button for creating custom synchronization filters.
Using these filters you may decide to reject certain differences. For example you may want to ignore
tables existing on the server having a certain pattern.
In the Sync Dialog, you will find a button for creating custom Synchronization Filters. Use this
to create a rule for always rejecting certain differences. For example, you may always want to ignore tables that contain a
certain person.
import com.wisecoders.dbs.dbms.sync.engine.diffs.*
import com.wisecoders.dbs.schema.*
return new SyncFilter() {
public boolean rejectDiff( AbstractDiff diff ) {
if ( diff instanceof TableExistsDiff){
SyncPair pair = ((TableExistsDiff) diff).pair;
if ( pair.right != null && pair.right.getName().startsWith("OTHER")) return true;
}
return false;
}
};
The complete list of DbSchema classes is documented in the DbSchema API.
DbSchema API
For more examples regarding task automation using Groovy scripts, visit the DbSchema Automation page.
Automate Schema Synchronization
Schema synchronization can be done without the Graphical User Interface. DbSchema supports Groovy scripts with full access to
DbSchema API. This can be saved to the file and passed as a parameter to DbSchema executable.
Look for examples on our DbSchema Automation page.