This document describes schema files layout and assumptions about it. In addition it describes how and when it should be updated.
Following assumptions are in place:
pom.xml, but it’s possible to override it in code via annotations - please see Developer Guild in Server documentation for details)current_version and two major versions behind (and all maintenance version schema files) - this will allow quick upgrade even from rather older versionsSNAPSHOT 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)We will check:
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)
SNAPSHOT version, even if the schema version match, a prompt to re-run upgrade-schema will be printed in the logs/tigase-console.logBasic schema filename layout consists of 3 basic parts:
derby, mysql, postgresql, sqlserver);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
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 minus 2: base schemacurrent-version minus 1: all changes from current-version minus 2 to current-version minus 1current-version: all changes from current-version minus 1 to current-versioncurrent-version MUST always match version of the component (defined in pom.xml).
It’s possible to have multiple 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 schema7.1.0: all changes from 7.0.0 to 7.1.08.0.0: all changes from 7.1.0 to 8.0.0All schema files must be stored under src/main/database/
There are two main workflows defined
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.0It’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.0Following cases will be discussed with solid-version examples. Comments will be provided in-line Following assumptions are made:
5.1.0, 5.2.0, 7.0.0, 7.1.0, 8.0.0Versions mapping: master (8.0.0), stable (7.1.0), old-stable (7.0.0):
schema files in old-stable branch
schema files in stable branch
schema files in master branch
If we made a schema change in old-stable version (and it’s branch) we must:
stable and master branch.Repository changes:
schema files in old-stable branch
schema files in stable branch
schema files in master branch
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
schema files in stable branch
schema files in master branch
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
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) );
get/setVersion(‘component’,'version');It will be stored and maintained in the file named <RDBMS_name>-common-schema-<version>.sql