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:

1. Your schema design is database-independent. This enables you to deploy the schema on multiple databases, generate migration scripts, etc.
2. 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.
3. 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.
4. 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

If you created a schema from scratch and want to update it in the database, all you have to do is:

1. Click on Schema in the upper menu.
2. Select Create or Upgrade Schemas from the Database.
3. 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.
4. After establishing a connection, choose the schemas that you want to create/ update.
5. 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.

1. Select the Refresh button from the upper tool menu;
2. If your schema is not synchronized, the next window should pop-up
3. If you choose Refresh, the changes will be taken over into your local model;
4. 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.

1. Open Model -> Model Properties;
2. Insert a new name for your schema;
3. Select the new database from the RDBMS field. Click Ok;




4. A new dialog will appear. This shows you how the data types will be converted from the initial database to the new one;
5. 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.

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.