Project

General

Profile

Schema files maintenance

This document describes schema files layout and assumptions about it. In addition it describes how and when it should be updated.

Assumptions

Following assumptions are in place:

  • All schema files are loadable multiple times - this is by far most important assumptions and it's allow to get away without explicit and detailed checking of loaded version (it's already handled on the schema level as of version 8.0.0)
  • Required schema version is calculated from the component version (which is set in the project configuration file - usually pom.xml, but it's possible to override it in code via annotations - please see Developer Guild in Server documentation for details)
  • we will maintain "3 versions schema files", i.e. in the distribution package we will provide schema versions for the current_version and two major versions behind (and all maintenance version schema files) - this will allow "quick upgrade" even from rather older versions
  • SNAPSHOT versions will print a log entry indicating that there may have been changes in schema and it's recommended to run the upgrade (we are aiming at frequent releases thus mandatory schema version check will be done only with final version)

Checks

We will check:

  • if it's possible to upgrade the schema (based on the current schema version in the database and available SQL files and their respective versions - if );
  • if it's required to upgrade the schema during server startup (until 7.1.x [inclusive] it was done only for tigase-server, will be done by all components)
  • if it's required to upgrade the schema during run of upgrade-schema) (if schema is already in the latest required version, executing all SQL files is not required hence speeding up upgrade)
    • During startup of SNAPSHOT version, even if the schema version match, a prompt to re-run upgrade-schema will be printed in the logs/tigase-console.log

Schema files layout

Filename layout

Basic schema filename layout consists of 3 basic parts:

  • name of relational database management system (RDBMS) for which it's intended (e.g. derby, mysql, postgresql, sqlserver);
  • name of the Tigase component for which it's intended;
  • version of the schema file.

For each component and version it's possible (but not mandatory) to split all database related functionality into multiple files but it's essential that they would be linked/included in the base file for particular database/component/version file. This allows separating Stored Procedures (-sp), base schema (-schema) and setting properties (-props). In principle the filename pattern looks as follows

  <RDBMS_name>-<tigase_component>-schema-<version>[-<sub_schema>].sql

For example schema file for version 7.0.0 of Tigase Server for Derby looks as follow:

  derby-server-schema-7.0.0-schema.sql

Files structure

As mentioned before, we should support all versions matching old-stable, stable and master, which translates to two main versions behind current-version, that is version: current-version - 2). This results in having 3 versions of the schema in the repository at any given time (two of them being "upgrades" to the oldest, base schema):

  • current-version - 2: base schema
  • current-version - 1: all changes from current-version - 2 to current-version - 1
  • current-version: all changes from current-version - 1 to current-version

NOTE: current-version MUST always match version of the component (defined in pom.xml).
NOTE: it's possible to have multipe files within version (related to smaller, maintenance upgrade) as the SchemaLoader would collect all files which version falls within range and .

For example with the release of version 8.0.0 this will translate to following versions:

  • 7.0.0: base schema
  • 7.1.0: all changes from 7.0.0 to 7.1.0
  • 8.0.0: all changes from 7.1.0 to 8.0.0

NOTE: All schema files must be stored under src/main/database/

Handling of changes in the schema

There are two main workflows defined

During release of the version

As we keep at the most only 3 versions of the schema, after release of the version we need to adjust (flatten) the files to maintain structure defined in Files structure (it may happen, that there wouldn't be any changes in the schema for particular version which will result in relatively empty current-version schema file -- only setting current version for component with setVersion(‘component’,'<current-version></current-version>')).

For example we are about to release version 8.0.0. This results in the following versions of the schema (in the example for the server) in the repository:

  • <database>-server-schema-7.0.0.sql: base schema
  • <database>-server-schema-7.1.0.sql: including changes for 7.1.0
  • <database>-server-schema-8.0.0.sql: including changes for 8.0.0

NOTE: It's possible that there will be maintenance versions in the list as well, e.g.: <database>-server-schema-7.1.1.sql and <database>-server-schema-7.1.2.sql

After the release we specify the version of the next release in pom.xml (for example 8.1.0 and the same version will be the current-version making the oldest available version 7.1.0. Because of that we MUST incorporate all the changes in 7.1.0 onto 7.0.0 creating new base file with version 7.1.0, i.e.:

  • <database>-server-schema-7.1.0.sql: base schema
  • <database>-server-schema-8.0.0.sql: including changes for 8.0.0
  • <database>-server-schema-8.1.0.sql: including changes for 8.1.0

Maintenance releases

Following cases will be discussed with solid-version examples. Comments will be provided in-line Following assumptions are made:

  • Version succession: 5.1.0, 5.2.0, 7.0.0, 7.1.0, 8.0.0
  • Versions mapping: master (8.0.0), stable (7.1.0), old-stable (7.0.0):
    • schema files in old-stable branch
      • 5.1.0 (base)
      • 5.2.0 (upgrade)
      • 7.0.0 (upgrade)
    • schema files in stable branch
      • 5.2.0 (base)
      • 7.0.0 (upgrade)
      • 7.1.0 (upgrade)
    • schema files in master branch
      • 7.0.0 (base)
      • 7.1.0 (upgrade)
      • 8.0.0 (upgrade)

Making a change in old-stable (and stable)

If we made a schema change in old-stable version (and it's branch) we must:

  • create a new file with upgraded version number;
  • propagate the change to the stable and master branch.

Repository changes:

  • schema files in old-stable branch
    • 5.1.0 (base)
    • 5.2.0 (upgrade)
    • 7.0.0 (upgrade)
    • 7.0.1 (upgrade) <- making a change here results in the schema version being bumped to 7.0.1
  • schema files in stable branch
    • 5.2.0 (base)
    • 7.0.0 (upgrade)
    • 7.0.1 (upgrade) <- we must port the change here
    • 7.1.0 (upgrade)
  • schema files in master branch
    • 7.0.0 (base)
    • 7.0.1 (upgrade) <- we must port the change here
    • 7.1.0 (upgrade)
    • 8.0.0 (upgrade)

Making a change in master

If we made a schema change in master version we don't propagate the change to the stable and old-stable branch.

  • schema files in old-stable branch
    • 5.1.0 (base)
    • 5.2.0 (upgrade)
    • 7.0.0 (upgrade)
  • schema files in stable branch
    • 5.2.0 (base)
    • 7.0.0 (upgrade)
    • 7.1.0 (upgrade)
  • schema files in master branch
    • 7.0.0 (base)
    • 7.1.0 (upgrade)
    • 8.0.0 (upgrade) <- we make the change here, as this is the development version schema version remains the same.

Implementation details

In-file control

There are two main control instructions (intended for SchemaLoader):

  • denoting Queries with -- QUERY START: and -- QUERY END: - each must be placed in own, separate file with the query being enclosed by the two of them, for example:
  -- QUERY START:
  call TigPutDBProperty('schema-version', '5.1');
  -- QUERY END:
  • sourcing other file with -- LOAD FILE: <path to .sql file> - path must be on the same line, following control instruction, for example:
  -- LOAD FILE: database/mysql-server-schema-7.0.0-schema.sql

Storing version in the database

Each repository will have a table tig_schema_versions with the information about all installed components and it's versions in that particular repository. There will be an associated stored procedure to obtain and set version:

  • table:
  tig_schema_versions (
    component varchar(100) NOT NULL,
    version varchar(100) NOT NULL,
    last_update timestamp NOT NULL,
    primary key (component)
  );
  • stored procedures get/setVersion(‘component’,'version');

It will be stored and maintained in the file named <RDBMS_name>-common-schema-<version>.sql