From SIPfoundry sipx, The Open Source SIP PBX for Linux - Calivia

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.

[edit] Upgrading the schema for a new feature

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

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!

[edit] 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.

schema version X  + patches to schema X  = schema version X + 1

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.

[edit] 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

 cd neoconf
 ant reset-db
 pg_dump -U postgres SIPXCONFIG_TEST > before.sql

• Review of the patch SQL and apply the modifications to /neoconf/etc/database/schema.sql - in most cases this is not a copy and paste
• Review patches that are embedded in database.xml for the previous version. 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 a upgrade schema is now at this version.

<sipx-version version=" Put N Here "/>

• Run this command to capture existing schema

 cd neoconf
 ant reset-db
 pg_dump -U postgres SIPXCONFIG_TEST > after.sql
 diff before.sql after.sql

• 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

RequiredDatabaseVersion= Put N Here

We never remove or change the contents of the patches. From now on they are only used when database is upgraded. New database version and a new set of patches need to be created if new modifications are required.