As you develop new code, you need to update the database schema in a way that automatically upgrades the schema of all other developers and eventually for customers upgrading to the next release.

All changes to database schema have to take into account the upgrade process. While we do not plan on supporting upgrades between each and every minor schema revision, we have to ensure that upgrades between all released version of software are supported.

Upgrading the schema for a new feature

Submit all schema changes as an SQL code snippet (patch) located in the /neoconf/etc/database directory. Register a new patch in the database.xml file (in the same directory).

Never modify an existing patch. Always write a new patch, which will be applied incrementally over the old one. (The DB has a 'patch' table, which records which SQL patches have been applied. If you modify a patch, that change will not take effect on any system where that patch has already been run. Confusion will likely ensure, and a DB drop will be required.)

Follow the instructions in the database.xml file on where and how to make your entry. Patches will run automatically before the web ui comes up and when unittest db is reset.

As a developer of a new feature, your job is done!

Upgrading the schema to prepare for a new release

Before a release when database schema is decalred stable, a project commiter makes a decision to migrate the contents of the patches to the /neoconf/etc/database/schema.sql script.

It works based on this princible.

So project commiter manually integrates all the patches into the schema file, then updates the declared schema version but leaves the patches as they are!. They are used as is, for customers upgrading from any previous release, that is not installing a new system.

Notes for preparing schema for new release

Because this steps involves some manual steps, following these steps will remove much of the chance for user error.

• Run this command to capture existing schema

In database.xml there will typically be a number of patch SQL files listed for the previous version. Review these and apply the modifications to schema.sql. In most cases this is*not a copy and paste.
There will sometimes be patch SQL commands are embedded in database.xml for the previous version. Also review these and apply the modifications to schema.sql. Again, in most cases this is*not a copy and paste.

• In the schema.sql file, add a line with the value of the version field in version_history table.
• create a new ANT target in database.xml called versionN, where N matches the version you entered in step above.
• Add version(N-1) target reference to the depends attribute in the upgrade-no-init target.
• Add following task to version(N-1) target after all patches have been applied. This denotes that after an upgrade schema is now at this version.

• Run this command to capture existing schema

• You may have to sort

  sort before.sql > before sort after.sql > after diff before after You should see the lack of entries in patch table.

• Edit web/bin/sipxconfig.sh.in, and update

Remove the oldest release

We've decided (see here) to only carry over three releases worth of patches. As you freeze the schema for a new release, remove the patches the oldest release. This includes the versionOLDEST string and the patch node entries from database.xml, as well as the actual patch files.