migrations


Description

Records which database updates have been applied to the system.

General Usage

Available database migrations are stored in a file system directory where individual files are named starting with the version number taking a fixed number of numeric digits. In the simple case, the migration process will get a sorted listing of available migrations from the migrations file system directory and compare the version of the file with the minimum version number not already checked against the maximum version applied to the database according to this table. If the file version is greater, the migration is applied to the database, otherwise the file is skipped and the version checking process repeats until there are no more migration files to evaluate.

Finally, during the migration process, the ms_syst_db.migrations record is created once the corresponding migration file has been successfully applied to the database. Both the migration file and the ms_syst_db.migrations record should be processed in the same database transaction.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
id uuid 2147483647 uuid_generate_v7()

The record’s primary key. The definitive identifier of the record in the system.
release int2 5 null

The release number to which the migration applies. The release number is any value in the range of 1 to 1295 (01 - ZZ in base36 notation). Release 00 is a special value used by the application and should not be used for any other purpose.
version int2 5 null

The version of the release to which the migration applies. Version numbers are subordinate to releases. The version number is any value in the range of 1 to 1295 (01 - ZZ in base36 notation). Version 00 is a special value used by the application and should not be used for any other purpose.
update int4 10 null

The patch, or update, to the release version. Update numbers are subordinate to version numbers. The update number may be any value in the range of 0 to 46655 (000 - ZZZ in base36 notation).
int8 19 null

Identifies the entity that sponsored the development of the migration. The expected value is in the range 0 to 2176782335 (0 - ZZZZZZ in base36 notation), though there are additional rules which must be observed:

  • Values in the range of 0 - 1295 (000000 - 0000ZZ) are reserved for Muse Systems special purposes.

    • Value 820 (0000MS) identifies Muse Systems as the sponsor of the general availability software release and so will appear on all regularly released migrations.

    • Values in the range of 1296 - 2176782335 are identifiers that are randomly assigned to clients and correspond to specific “instances” (application databases). The primary instance for a client will always be the reference point for those clients with more than one instance.
int4 10 null

The specific migration implementing special or custom changes. Sponsor Modification numbers are subordinate to Update numbers. The sponsor migration number may be any value in the range of 0 to 46655 (000 - ZZZ in base36 notation). In most cases this value will just be 0 (000).
migration_version text 2147483647 null

The full migration version number represented as a series of digits in base 36 notation. Each of the individual versioning fields are represented in a dot separated notation: RR.VV.UUU.CCCCCC.MMM

  • RR - Release Number
  • VV - Version Number of the Release
  • UUU - Update Number of the Version
  • CCCCCC - Client identifier for sponsored modifications
  • MMM - Client specific modification sequence

This sequence is the same as the file name of each migration as saved in the file system. This field in the database is primarily for convenience of cross- referencing applied migrations to the file system.
diag_timestamp_created timestamptz 35,6 now()

The database server date/time when the transaction which created the record started.
diag_role_created text 2147483647 null

The database role which created the record.
diag_timestamp_modified timestamptz 35,6 now()

The database server date/time when the transaction which modified the record started. This field will be the same as diag_timestamp_created for inserted records.
diag_wallclock_modified timestamptz 35,6 clock_timestamp()

The database server date/time at the moment the record was actually modified. For long running transactions this time may be significantly later than the value of diag_timestamp_modified.
diag_role_modified text 2147483647 null

The database role which modified the record.
diag_row_version int8 19 1

The current version of the row. The value here indicates how many actual data changes have been made to the row. If an update of the row leaves all data fields the same, disregarding the updates to the diag_* columns, the row version is not updated, nor are any updates made to the other diag_* columns other than diag_update_count.
diag_update_count int8 19 0

Records the number of times the record has been updated regardless as to if the update actually changed any data. In this way needless or redundant record updates can be found. This row starts at 0 and therefore may be the same as the diag_row_version - 1.

Indexes

Constraint Name Type Sort Column(s)
migrations_pk Primary key Asc id
migrations_migration_version_udx Must be unique Asc migration_version

Check Constraints

Constraint Name Constraint
migrations_version_range_chk (((version)::integer <@ '[1,1296)'::int4range))
migrations_sponsor_range_chk ((sponsor <@ '[0,2176782336)'::int8range))
migrations_sponsor_modification_range_chk ((sponsor_modification <@ '[0,46656)'::int4range))
migrations_release_range_chk (((release)::integer <@ '[1,1296)'::int4range))
migrations_update_range_chk ((update <@ '[0,46656)'::int4range))

Relationships

Close relationships within degrees of separation