Schema Synchronization

DbSchema projects can be started from an existing schema on database or design schema from scratch. Whatever option you choose, is important to know that DbSchema holds its own image of the schema, independent from the database. This means that :
  • If you connect to a database the schema in DbSchema won't reflect the schema in the database unless you decide to refresh the local schema
  • Schema and layouts will be saved to file. You can reopen it without connecting to the database. You can connect to different databases and the initial schema won't be lost.
Let's go together through few scenarios :

Deploy Created Schema from Scratch into Database

If you stared a DbSchema project by design from scratch, you may want to create the schema in the database. To do so, choose from menu to create or upgrade schema into the database.

Next step is to create the connection to database and choose the schemas which should be created.

hint  Save the project to file before starting this operation. If you have problems connecting to the database, you can fix them - your schema design won't be lost !

After executing this statements, your schema is inside the database !

Synchronize Local Schema With the Database

You may have started a project with a schema from the database. You may want to be sure that your local schema is same as in the database. Therefore press the 'Refresh' button from the menu :

If differences exists between local schema and database, the next dialog will show:

If you choose 'Refresh', the changes will be taken over into the local project. If you choose 'Review', the Sync Dialog will show :

Convert Schema to a Different Database

Converting the schema to another database means to :
  • Convert the data types to the new database.
  • Re-write the triggers, functions and procedures which has to be done by hand.
Changing a project database will modify the internal project column types. We recommend to create a copy of your project first, change the project database and save the new project in another file.

To convert schema to a different database open the Project Properties dialog from the File menu. There you can choose a different database for your project.

There will show up a new dialog where you can choose how the data types will be converted :

When you have done this operation, save the project in a new file. Now you can choose from the menu to connect to the new database and create there the schema.

DbSchema Project in Version Repository

The DbSchema project .dbs is a XML file, which 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.

Schema Deployment on Multiple Databases

DbSchema can be used to develop schema in a development environment. You can connect than to production databases and synchronize the schema or generate the migration scripts.

Schema Synchronization with Other Project from File

Project files stores the schema file, so why not to synchronize with another project file ? Think this situation: you develop a schema, save it to project file and put this file in a Subversion or CVS repository. Later you want to see the schema differences between two different versions. You can take the files for each of the versions, open one in DbSchema and compare it with the second. Than 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 this filters you may decide to always reject certain differences. For example you may want to always ignore tables existing on the server having a certain pattern.
    import com.wisecoders.dbs.rdbms.sync.engine.diffs.*
    import com.wisecoders.dbs.schema.*

     return new SyncFilter() {
       public boolean rejectDiff( AbstractDiff diff ) {
         if ( diff instanceof TableExistsDiff){
           SyncPair<Table, Table> pair = ((TableExistsDiff) diff).pair;
           if ( pair.right != null && pair.right.getName().startsWith("OTHER")) return true;
         }
       return false;
       }
     };
The SyncFilter receives a diff, which contains the pair of compared elements. The SyncPair.left is the object in DbSchema and SyncPair.right is the object in the database. The public DbSchema classes are explained here.