Example: Specifying and distributing a schema upgrade
The administrator creates a set of scripts for each database type. The scripts change the schema from the current version to the new version. After the scripts are run, the version name is upgraded to the new name.
The new scripts can be distributed to the replica databases by using any data distribution mechanism, including synchronization across an Advanced Replication database hierarchy.
When you define a schema upgrade, the following rules apply:
▪ Any new database objects can be added to, or dropped from, the schema. If a table is dropped from the schema, the table must be removed from publication definitions first.
▪ Publications can be changed by adding and removing result sets or adding and removing columns of a result set. Changing the search criteria of a result set is also possible; however, a change to the search criteria might force the next refresh of that result set to be a full refresh rather than an incremental refresh.
▪ If you change the parameter list for a publication, then you must drop the old publication and create a new one. The refreshes from the new publication will be full refreshes rather than incremental refreshes.
If you write scripts to make changes to the schema, most of the statements will be the same for the replica database as for the master database. For example, if you add a new column to a table on the master database, you probably want to add that new column to the corresponding tables on each replica database.
However, there are some statements that will not be the same on the master and replica databases. For example, the statements to change the publications only apply to master databases. Similarly, if you make changes that do require you to drop and re-create a publication, and thus require a replica database to re-register with the master database, the re-register statements are executed only by the replica databases.
The following example demonstrates how to upgrade a schema of a distributed database system by using the schema upgrade capabilities of solidDB.
Typically the first database schema to be upgraded is that of the master database. After upgrading the master database, the replica databases are upgraded. In a system that has more than 2 tiers (and thus where intermediate-level servers are both master and replica databases), the process is to start at the topmost tier and upgrade it. Upon completion of the scripts in a database, the version name of the affected database catalog is upgraded to a new one. This indicates to the next tier of databases that they need to be upgraded as well.