# Database schema updates

## Usage

DON'T APPLY THE SQL SCRIPTS IN THE DIRECTORIES MANUALLY!

Use the script `schema/update-db.sh` instead, this will guaranty
consistent database updates and maintain the `gemma_schema_version`
table in the database.

Running `update-db.sh` will automatically detect which schema updates
are necessary and apply them.

## Concept

The gemma database schema is versioned.  Each update to the schema
increments the version number of the schema.  To get the version of
the schema your database is currently using, you can use the database
function `get_schema_version()`:

```sql
SELECT get_schema_version();
```

It is an design goal to allow any older version of a gemma data base
to be updated to the most current schema by calling `update-db.sh`
from the current gemma distribution.

## Directory Layout

the `schema/updates` directory contains subdirectories with names
corresponding to the version of the database schema.  Each
subdirectory contains the necessary SQL scripts to update the
database schema from the previous version to the one represented by
the directory name.

Each script is name according to the scheme: `NN.NAME.sql` where `NN`
is a number determining the order of execution and `NAME` is a
short string describing what the script implements.

### Example:

```
schema/updates
├── 0000
│   └── 01.add_schema_version.sql
├── 0301
│   ├── 01.dismar-wwname.sql
│   └── 02.search_functions.sql
└── 1000
    └── 01.pwreset.sql

```

## Adding updates

To implement a change to the gemma database schema:

- Create a new directory `schema/updates/XXXX` where `XXXX` represents
  the number of the new database version.  The new number must be
  greater than the current database version.

- Create the update scripts in the new subdirectory.  When more than
  one script is used, the numeration in the scripts name determines
  the order of execution.

  Note that all scripts in one version update are executed in a single
  transaction by the `update-db.sh` script.

  **Important:** all sql scripts in one update directory have to be
  self contained and *must not* include any files from the regular
  schema definition (or from other update versions).  This is
  necessary to allow for updates over multiple versions of the schema,
  where multiple updates might make incremental changes to the same
  functions/files of the schema.

- Modify the scripts in `schema/` (outside the updates directory) to
  reflect the changes.  The schema of a freshly created database using
  `schema/install-db.sh` must always be identical to the schema of a
  database updated using `schema/update-db.sh`!

- Update `schema/version.sql`, so that it reflects the new schema
  version you've just created.