Skip to content

Migration steps for STH environments to v2.0

Intro

This issue describes the steps to migrate STH environments from v1.x to v2.x

Preparation

  • Copy database from production to staging environment. Use kubernetes job to do this.
  • Re-enable build steps in CICD script
  • Update kubernetes deployment, e.g.: kubectl kustomize stpe-staging > generated/stpe-staging.yaml
    • Includes flyway: T4SMM#402. Complete checkbox in that issue.
  • Re-enable deployment steps in CICD script
  • Migrate data to Google cloud storage, see: kubernetes#29

Step 0 - Upgrade to application v2.0.0

For each environment:

  • Run meta-model installer GET ../api/v1/admin/installer/metapopulation
  • RUN ExecEngine GET ../api/v1/admin/execengine/run
  • Set specProject for every Specification
  • Set/update specId for every Specification
  • Update specName to have proper names
  • Delete/merge ontology specifications that are not needed
  • Add 'List all specifications' menu item to settings menu
  • Cleanup menu items of removed interfaces
  • Adapt homepage, remove link to 'Standards' interface

Step 1 - Determnine which data models must be exported as owl ontology

E.g. export all standard versions that have classes/properties as an owl ontology using the ontology export module. Use the following query to view which standard versions contains classes/properties:

SELECT stdver.StdVersion, stdver.stdVersionVersionId, s.standardName, s.Standard
FROM  (
    SELECT DISTINCT StdVersion
    FROM stdversionproperty
    UNION
    SELECT DISTINCT StdVersion
    FROM stdversionclass
) AS version
JOIN stdversion stdver ON version.StdVersion = stdver.StdVersion
LEFT JOIN standard s ON stdver.stdVersionStd = s.Standard

Step 2 - Check/fix issues

There is an issue with duplicate local name for classes in a standard. Identity of Standard-classLocalName combination was not enforced (yet). Update the classLocalName in the database manually.

Use the following query to select classes with non-unique local name:

SELECT *
FROM class
WHERE classLocalName IN (
	SELECT classLocalName
	FROM class
	GROUP BY classStd, classLocalName
	HAVING count(*) > 1
)
AND classStd IN ('Standard_1565264359_00661527', 'Standard_1605172723_00102030', 'Standard_1565266601_00069132', 'Standard_1574078778_00079132')
ORDER BY classStd, classLocalName;

SELECT *
FROM property
WHERE propertyStd IN ('Standard_1565264359_00661527', 'Standard_1605172723_00102030', 'Standard_1565266601_00069132', 'Standard_1574078778_00079132')
GROUP BY propertyStd, propertyDomain, propertyLocalName
HAVING count(*) > 1;

Note! Check if cleanup of classes is possible. Sometimes classes are still there but not used by/refered to any property or element.

Step 3 - Export the ontologies

Use the API for this: http://localhost/api/v1/ontology/export/standard/<standard id>/version/<version number>?format=ttl or click on the 'export as owl ontology' icon in the Standard overview.

Step 4 - Check exported ontology file

  • Look for WARNINGS displayed as annotation (skos:note) on owl:ontology resource, fix in environment, export again.
  • Check/fix xml namespace
  • Check/fix import statements

Step 5 - In existing database, populate the elmPropertyUri and elmClassUri

Use the following SQL code to pre-check the update Make sure the namespaces are set right for the standards containing properties and class, e.g. the XML Schema standard

SELECT e.element, 
	e.elmName,
    p.propertyLocalName,
    e.elmType,
    nullif(concat_ws('', ps.standardNS, concat(d.classLocalName, '_'), p.propertyLocalName), '') as propertyUri,
    concat(rs.standardNS, r.classLocalName) as classUri
FROM element e
LEFT JOIN property p ON e.elmProperty = p.Property
LEFT JOIN standard ps ON p.propertyStd = ps.Standard
LEFT JOIN class d ON p.propertyDomain = d.Class
LEFT JOIN class r ON e.elmType = r.Class
LEFT JOIN standard rs ON r.classStd = rs.Standard

The update query:

SET SQL_SAFE_UPDATES=0;
UPDATE element e
LEFT JOIN property p ON e.elmProperty = p.Property
LEFT JOIN standard ps ON p.propertyStd = ps.Standard
LEFT JOIN class d ON p.propertyDomain = d.Class
LEFT JOIN class r ON e.elmType = r.Class
LEFT JOIN standard rs ON r.classStd = rs.Standard
SET e.elmPropertyUri = nullif(concat_ws('', ps.standardNS, concat(d.classLocalName, '_'), p.propertyLocalName), ''),
	e.elmClassUri = concat(rs.standardNS, r.classLocalName)
WHERE e.elmPropertyUri IS NULL -- only update those elements that are not created by the FIT wizard
AND e.elmClassUri IS NULL -- only update those elements that are not created by the FIT wizard
;

Step 5b (additional for Ketenstandaard)

Fix for invalid property and class URIs due to . notation

SET SQL_SAFE_UPDATES=0;
UPDATE element SET elmPropertyUri = REPLACE(elmPropertyUri, '.INFO', '_INFO') WHERE elmPropertyUri LIKE '%.INFO%';
UPDATE element SET elmPropertyUri = REPLACE(elmPropertyUri, '.LISTDATE', '_LISTDATE') WHERE elmPropertyUri LIKE '%.LISTDATE%';
UPDATE element SET elmPropertyUri = REPLACE(elmPropertyUri, '.EDXF.', '_EDXF_') WHERE elmPropertyUri LIKE '%.EDXF.%';
UPDATE element SET elmClassUri = REPLACE(elmClassUri, '.EDXF.', '_EDXF_') WHERE elmClassUri LIKE '%.EDXF.%';

Step 6 - Add ontology content to created OntologyVersions

Aftercare

  • Disable Migration Helper Rules.
  • Remove all code, templates and other artefacts related to old concepts Class, Property, Standard, StdVersion, ..
  • Cleanup database tables. Separate issue, see: #96 (closed)
Edited by Michiel Stornebrink