Support for database migration

[stcarrez]

When a database schema is modified, it is necessary to apply some migration SQL scripts to update an existing database.

The library can provide some support to help in implementing such migration. Because this is a complex task, the framework cannot magically perform and handle all the work for the database migration. In the lifetime of an application, the database schema may be changed and these changes must be applied to the database when a new version of the application is installed. Each application can implement its own mechanism to track the database schema change and apply the necessary change to the database when the application is upgrated.

An automatic tracking and update is too difficult for this library because such database migration is specific to each application. However, the Ada Database Objects library provides a mechanism to help in updating the database schema. The proposed mechanism is based on:

• the ado_version database table which tracks for each application module, a schema version. The schema version is changed by the developer when a new database schema is made for the module and some SQL migration script must be executed. The version is a simple integer that is incremented when the schema is modified.
• a set of SQL migration scripts that control the migration of a module from a version to the next one. The SQL migration scripts are written manually by the developer (or by some generation tool if they exist) when the database schema is modified.

The SQL migration scripts must be organized on the file system so that the library can find them and decide whether they must be executed or not. Each SQL migration script of a module must be stored in a directory with the name of that module. Then, each upgrade for a schema version of the module must also be stored in a directory with the name of the version. To control the order of execution of SQL migration scripts, each script file must be prefixed by a unique order. SQL scripts are executed in increasing order.

The layout is the following:

db/migrate/<module>/<version>/depend.conf
db/migrate/<module>/<version>/<order>-<name>.sql
db/migrate/<module>/<version>/<order>-<driver>-<name>.sql