keep ID values (FKs as well) + after data migration set autoincrement for tables as max(id) (for some entities it will be necessary to reset ID values because of ensuring unique values)
approach:
Original planform data migrators and exporters New platform data importers
Logic:
load data
apply migration rules
create JSON objects corresponding to the new data model
export data
all entities are included (even those with no data)
In code:
one admin page for exporting all data using migrator classes
migrator classes with names OriginalTableNameMigrator .java (it includes prefix DRIVES or New if they are new tables)
all migrator classes have these 2 methods:
public JSONObject convert(X x) - implements migration rules, in special cases return type may be different
public List<JSONObject> exportData() - implements data load and apply converting (migration rules)
import data from JSON objects
expect all entities in input file
Acknowledgement , Badge , CategoryOfOffer , CategoryOfOfferToProvider , Certificate , CommonTrainingCompletionProof , CompetenceL1, CompetenceL2, CompetenceL2half , CompL1ToRelease , CompL2ToRelease , CompL2halfToRelease , Corporation , Country , CorporationRole , DeliveryMethod , DeliveryMethodType , JobRole , JobRoleCategory , JobRoleToWorkingGroup , Language , LearningOutcome , LearningPath , Logs , LPathElement , LPathSCK , LPathToPersonProgress , MegaTrend , ModuleActivation , OrganisationType , Person , PersonAchievedSCK , PersonCompletedTraining , PersonFavouriteJobRole , PersonFavouriteTraining , PersonGeneratedLPath , TrainingProviderGuaranteedLPath, PersonSavedLPath , PersonToCorporationRole , PersonToTrainingProvider , PersonToWorkingGroup , PriceType , ProviderGroup , SCK , SCKLevel , SCKToJobRole , SCKToTraining , SCKToTrend , SCKToWorkingGroup , SkillDatasetRelease , SCKToRelease , TargetGroup , TargetGroupToTraining , Training , TrainingCategory , TrainingCompletionProof , TrainingMetricsEnroll , TrainingMetricsRating , TrainingMetricsView , TrainingProvider , TrainingProviderToTrend , TrainingType , Trend , WikiPage , WorkingGroup SCKLevelToTraining
original table for data migration:
DRIVE_AreaToTraining , DRIVES_TrainingElement, DRIVES_TrainingToTE,
DRIVES_SCKLevelToSCKToJobRole , DRIVES_SCKLevelToSCKToTE
DRIVES_TrainingToProvider , DRIVES_SCKToExternalSystem
User_, Users_Roles, Role_
ENTITY MAPPING TYPES:
reduction - some attributes are going to be omitted
extension - some attributes must be delivered
adjusted - some attributes are added some are removed
special adjusted - some attributes are added some are removed; special - some attributes could be recalculated or delivered
1:1 - direct attribute migration
special 1:1 - direct attribute migration; special - some attributes could be recalculated or delivered
new - specific migration or creation
NewPlatformTable (OriginalPlatformTable) (entity mapping type)
Identified entities with reset IDs (PK)
TrainingMetricsView, TrainingMetricsEnroll, TrainingMetricsRating, LearningOutcome, DeliveryMethod, PersonAchievedSCK, PersonCompletedTraining, TargetGroup, Certificate, PriceType, DeliveryMethodType
Lookup tables
Country (Country) (reduction)Language (DRIVES_Language) (extension)
code - delivered from internet
Users
User (User_)
will be new users generated using Keycloack API
exported - userId, emailAddress, firstName, lastName, createdAt, roles
if user has middleName it is attached to firstName
roles is list of user roles filtered by selected roles to use
DRIVES_Person is exported and the records contain both personId and userId data so it is possible to assign Keycloack user to Person
password will be randomly generated and users will be required to set it later
Role
defined in Keycloack
selected roles to use: User, Provider, Administrator, Content Manager
others can be added later
Main entities
PERSON
Person (DRIVES_Person) (extension)
keycloakUserId - null
origUserId - user ID
jobTitle - delivered from User_
TRAINING COMPLETION
TrainingCompletionProof (new table, 1:1)
this table will be new in original platform (during summer)
data will be then migrated as 1:1
PersonCompletedTraining (DRIVES_PersonToTraining) (adjusted)
removing - id, status
adding - trainingCompletionProofId
PersonAchievedSCK (DRIVES_SCKToPersonToTraining, DRIVES_SCKToPerson) (adjusted)
removing - amount TODO (what to do with this value?)
adding
achievedDate
sckLevelId
trainingId
Badge (DRIVES_Badge, DRIVES_SCKToPersonToTraining) (adjusted)
removing
sckId
sckLevelId,
jobRoleId - ever
PersonCompletedTrainingToProof (DRIVES_PersonTrainingProof) (1:1)
personToTrainingId → personCompletedTrainingId (just renaming)
CompletionProof (DRIVES_CompletionProof) (1:1)
PROVIDER
look ups
CategoryOfOffer (DRIVES_CategoryOfOffer) (1:1)OrganisationType (DRIVES_OrganisationType) (1:1)
TrainingProvider (DRIVES_TrainingProvider) (extension)
createdAt, updatedAt, deletedAt - set as null
PersonToTrainingProvider (DRIVES_PersonTrainingProv)
original id is omitted
creating PFK (trainingProvId, personId)
keep first find personId
CategoryOfOfferToProvider (DRIVES_CategoryOfOfferToProvider ) (1:1)
TRAINING
look ups
TrainingType (DRIVES_TrainingType) (extension)
TrainingCategory (DRIVES_CourseCategory) (1:1)Certificate (DRIVES_Certificate) (reduction)
iso attribute is removed
new value list is created (`NONE`, `ATTENDANCE_CONFIRMATION`, `COMPLETION_CONFIRMATION`, `CERTIFICATE`, `ACCREDITED_CERTIFICATE`, `EXAM_CERTIFICATE`, `DIGITAL_BADGE`, `CUSTOM`, `BY_AGREEMENT`)
TargetGroup (DRIVES_TargetGroup) (1:1)
new value list was created (1 - General Public, 2 - Students, 3 - Secondary School Students, 4 - Higher Education Students, 5 - Vocational Education and Training Students, 6 - Educators and Trainers, 7 - School Teachers, 8 - VET Teachers, 9 - University Lecturers, 10 - Professionals, 11 - Early Career Professionals, 12 - Experienced Professionals, 13 - Upskilling Professionals, 14 - Reskilling Learners, 15 - Managers and Supervisors, 16 - Engineers and Technicians, 17 - Industry Specialists, 18 - New Employees / New Hires, 19 - Job Seekers, 20 - Future Workforce, 21 - Researchers and Academics, 22 - Entrepreneurs and Business Owners)
delivery method look ups
DeliveryMethodType (new)
delivery source - name and description are hardcoded texts
DRIVES_OrganisationArea (type_) → DeliveryMethod (typeId)
PriceType (new)
delivery source - training string attribute → name is hardcoded text
DRIVES_Training (priceType) [0, 1, 2, 3] → PriceType (name) [“Free”, “Fee”, “Freemium”, “ContactUs”]
LearningOutcome (DRIVES_LearningOutcomes) (special 1:1)
DRIVES_LearningOutcomes (outcomesText) → splitted by “\n” to LearningOutcome (outcomeText)
DeliveryMethod (DRIVES_OrganisationArea) (special 1:1)
id, .. (1:1)
countryId (from country varchar attribute)
trainingId (select all AreaToTraining of given organisationAreaId, if there is no training omit organisationArea in export)
Acknowledgement (DRIVES_Acknowledgement) (1:1)
SCKToTraining (DRIVES_SCKToTrainingElement and DRIVES_TrainingToTE)
sckLevelId - delivered from DRIVES_SCKLevelToSCKToTE using ID of DRIVES_TrainingToTE (which includes trainingId)
SCKLevelToTraining (DRIVES_SCKLevelToTraining) (reduction)
original id is omitted
creating PFK (trainingProvId, personId)
TargetGroupToTraining (new)
trainingId
Training has groupTarget text attribute - it is necessary to do deterministic mapping from this attribute values to new TargetGroup list
targetGroupId
this is result of deterministic mapping
Training (DRIVES_Training) (adjusted)
removing - cost, domainGroupId, priceTypeId
adding - updatedAt, deletedAt
createdAt - from dateAdded
publishedAt - from datePublished
certificateId - from text attribute certificate (deterministic mapping - check method - private static long resolveCertificateId(String certificate))
JOB_ROLE
JobRole (DRIVES_JobRole) (adjusted)
mapping is 1:1
removing - mainWorkingGroupId, mainProjectId, inGroup, conceptUri
adding - jrCategory (all null)
adding - originKey
delivery:
at ESCO job roles - from removed attribute conceptUri
at DRIVES job roles - “DRIVES_{id}”
at MSK job roles - job role ID attribute - it is included in XLSX file (this is not going to be exported from SH production but from MSK)
SCKToJobRole (DRIVES_SCKToJobRole) (extension)
sckLevelId - delivered from DRIVES_SCKLevelToSCKToJobRole using ID of SCKToJobRole
SCK
SCK (DRIVES_SCK) (adjusted)
mapping is 1:1
removing - mainWorkingGroupId, mainProjectId, conceptType
adding - originKey
delivery:
at ESCO skills - from DRIVES_SCKToExternalSystem
at DRIVES skills - “DRIVES_{id}”
at MSK skills - skill code attribute (this is not going to be exported from SH production but from MSK)
SCKLevel (DRIVES_SCKLevel) (1:1)
attribute type was renamed to origin
TRENDS
MegaTrend (DRIVES_MegaTrend) (1:1)Trend (DRIVES_Trend) (1:1)TrainingProviderToTrend (DRIVES_TrainingProviderToTrend) (1:1 with removing PK)SCKToTrend (DRIVES_SCKToTrend) (1:1 with removing PK)
WORKING GROUP
WorkingGroup (DRIVES_WorkingGroup)SCKToWorkingGroup (DRIVES_SCKToWorkingGroup ) (1:1 with removing PK)
mainGroup - delivered by searching the SCK, check workingGroupId and mainProjectId attributes, set mainGroup as true if there is non zero FK
JobRoleToWorkingGroup (DRIVES_JobRoleToWorkingGroup ) (1:1 with removing PK)
mainGroup - delivered by searching the SCK, check workingGroupId and mainProjectId attributes, set mainGroup as true if there is non zero FK
PersonToWorkingGroup (DRIVES_PersonToWorkingGroup ) (1:1 with removing PK)
mainGroup - delivered by searching the SCK, check workingGroupId and mainProjectId attributes, set mainGroup as true if there is non zero FK
LEARNING PATH
LearningPath (DRIVES_LearningPath) (reduction)
migrating only predefined paths
LPathElement (DRIVES_LPathElement) (1:1)
migrating only from predefined paths
LPathSCK (DRIVES_LearningPath.jobRoleId → JR skill list) (new)
migrating only from predefined paths
learningPathId - from predefined LP
sckId, sckLevelId - load JR from predefined LP and then load JR's skills with levels
TrainingProviderGuaranteedLPath (DRIVES_LPathToPerson) (adjusted)
personId replaced by trainingProviderId
GENERAL
WikiPage (DRIVES_WikiPage) (1:1)TrainingMetricsView , TrainingMetricsEnroll , TrainingMetricsRating (DRIVES_CourseMetrics) (new)
view and enroll
iterate through value attribute (the quantity of views/enrolls) and in each iteration create record for view/enroll
timestamp, trainingId and personId - set as empty because production data do not have this information
rating
all timestamp, value, trainingId and personId are set up
No data entities
PersonFavouriteJobRole PersonFavouriteTraining CompetenceL1 CompetenceL2 CompetenceL2half JobRoleCategory ModuleActivation ProviderGroup SkillDatasetRelease SCKToRelease CompL1ToRelease CompL2ToRelease CompL2halfToRelease
Omitting migration
Corporation CorporationRole (new)PersonToCorporationRole (new)PersonGeneratedLPath PersonSavedLPath LPathToPersonProgress Logs
MIGRATION LOGS ANALYSIS
Training not found is OK
SCK not found is OK
Person not found
MySQL Data Type PostgreSQL Equivalent Notes
TINYINT(1)
BOOLEAN
MySQL uses TINYINT for booleans
DATETIME
TIMESTAMP or TIMESTAMPTZ
Adjust for timezone handling
TEXT
TEXT
Similar usage in both systems
ENUM
CHECK constraint or TEXT
ENUM is not native in PostgreSQL
BLOB
BYTEA
Use BYTEA for binary data
DOUBLE
DOUBLE PRECISION
Ensure correct precision settings
JSON
JSONB
JSONB offers better indexing and performance
source: https://skyvia.com/blog/mysql-to-postgresql/
ideas: https://chatgpt.com/c/6a1e9ada-ca20-8329-9ca0-44f3cf8862ed
Logging Structural and referential Integrity
a ll entities are present, including empty onesrequired fields exist
data types match
each FK points to an existing ID
there are no orphaned records
This should be largely validated by the new platform importers
Migration log based validation
Migration validation based on business rules
Sampling manual validation
going through new platform and compare old and new system using frontend interface
help to investigate
the preservation of original IDs works
the relationships match those in the old database after import
Exporters logs
info OrganisationArea: 2 references non-existing Training ID: 42
info OrganisationArea: 2 references non-existing Training ID: 97
info OrganisationArea: 2 references non-existing Training ID: 181
info OrganisationArea: 2 references non-existing Training ID: 401
info OrganisationArea: 2 references non-existing Training ID: 501
info OrganisationArea: 2 references non-existing Training ID: 601
info OrganisationArea: 2 references non-existing Training ID: 801
info OrganisationArea: 602 references non-existing Training ID: 2202
info OrganisationArea: 803 references non-existing Training ID: 2305
info OrganisationArea: 2808 references non-existing Training ID: 4601
info OrganisationArea: 4923 references non-existing Training ID: 6007
info OrganisationArea: 4924 references non-existing Training ID: 6007
info OrganisationArea: 11644 references non-existing Training ID: 11116
info OrganisationArea: 11946 references non-existing Training ID: 11405
info OrganisationArea: 11946 references non-existing Training ID: 11405
info OrganisationArea: 11946 references non-existing Training ID: 11405
info OrganisationArea: 11946 references non-existing Training ID: 11405
info OrganisationArea: 11946 references non-existing Training ID: 11405
info OrganisationArea: 11946 references non-existing Training ID: 11405
info OrganisationArea: 11946 references non-existing Training ID: 11405
info OrganisationArea: 11946 references non-existing Training ID: 11405
info OrganisationArea: 11946 references non-existing Training ID: 11405
info OrganisationArea: 12018 references non-existing Training ID: 11505
info OrganisationArea: 12018 references non-existing Training ID: 11505
info OrganisationArea: 12127 references non-existing Training ID: 11616
info OrganisationArea: 12127 references non-existing Training ID: 11616
info OrganisationArea: 12127 references non-existing Training ID: 11616
info OrganisationArea: 29301 references non-existing Training ID: 22201
info OrganisationArea: 29301 references non-existing Training ID: 22201
info OrganisationArea: 29301 references non-existing Training ID: 22201
info OrganisationArea: 29301 references non-existing Training ID: 22201
info OrganisationArea: 29301 references non-existing Training ID: 22201
info OrganisationArea: 29301 references non-existing Training ID: 22201
info OrganisationArea: 30804 references non-existing Training ID: 23004
info OrganisationArea: 30804 references non-existing Training ID: 23004
info OrganisationArea: 34502 references non-existing Training ID: 24801
Importers logs
Training not found is OK
"LearningOutcome: skipped row {id=2} — trainingId=20601 not found in Training",
"SCKLevelToTraining: skipped row {sckLevelId=7, trainingId=6007} — trainingId=6007 not found in Training",
"SCKLevelToTraining: skipped row {sckLevelId=8, trainingId=6007} — trainingId=6007 not found in Training",
"SCKLevelToTraining: skipped row {sckLevelId=9, trainingId=6007} — trainingId=6007 not found in Training",
"SCKLevelToTraining: skipped row {sckLevelId=8, trainingId=11116} — trainingId=11116 not found in Training",
"SCKLevelToTraining: skipped row {sckLevelId=7, trainingId=11405} — trainingId=11405 not found in Training",
"SCKLevelToTraining: skipped row {sckLevelId=8, trainingId=11405} — trainingId=11405 not found in Training",
"SCKLevelToTraining: skipped row {sckLevelId=4, trainingId=11505} — trainingId=11505 not found in Training",
"SCKLevelToTraining: skipped row {sckLevelId=5, trainingId=11505} — trainingId=11505 not found in Training",
"SCKLevelToTraining: skipped row {sckLevelId=4, trainingId=11616} — trainingId=11616 not found in Training",
"SCKLevelToTraining: skipped row {sckLevelId=5, trainingId=11616} — trainingId=11616 not found in Training",
"SCKLevelToTraining: skipped row {sckLevelId=6, trainingId=11616} — trainingId=11616 not found in Training",
"SCKLevelToTraining: skipped row {sckLevelId=5, trainingId=22201} — trainingId=22201 not found in Training",
"SCKLevelToTraining: skipped row {sckLevelId=6, trainingId=23004} — trainingId=23004 not found in Training",
"SCKToTraining: skipped row {sckId=4559, sckLevelId=1, trainingId=11116} — trainingId=11116 not found in Training",
"SCKToTraining: skipped row {sckId=10376, sckLevelId=1, trainingId=11116} — trainingId=11116 not found in Training",
"SCKToTraining: skipped row {sckId=25218, sckLevelId=2, trainingId=11505} — trainingId=11505 not found in Training",
"SCKToTraining: skipped row {sckId=25219, sckLevelId=2, trainingId=11505} — trainingId=11505 not found in Training",
"SCKToTraining: skipped row {sckId=25220, sckLevelId=1, trainingId=11505} — trainingId=11505 not found in Training",
"SCKToTraining: skipped row {sckId=25319, sckLevelId=1, trainingId=11616} — trainingId=11616 not found in Training",
"SCKToTraining: skipped row {sckId=11966, sckLevelId=1, trainingId=23004} — trainingId=23004 not found in Training",
"SCKToTraining: skipped row {sckId=4884, sckLevelId=1, trainingId=23004} — trainingId=23004 not found in Training",
"SCKToTraining: skipped row {sckId=796, sckLevelId=2, trainingId=23004} — trainingId=23004 not found in Training",
"SCKToTraining: skipped row {sckId=7002, sckLevelId=2, trainingId=23004} — trainingId=23004 not found in Training",
"SCKToTraining: skipped row {sckId=481, sckLevelId=2, trainingId=23004} — trainingId=23004 not found in Training",
"SCKToTraining: skipped row {sckId=9451, sckLevelId=3, trainingId=23004} — trainingId=23004 not found in Training",
"SCKToTraining: skipped row {sckId=9860, sckLevelId=2, trainingId=23004} — trainingId=23004 not found in Training",
"SCKToTraining: skipped row {sckId=10610, sckLevelId=2, trainingId=23004} — trainingId=23004 not found in Training"
SCK not found is OK
"SCKToWorkingGroup: skipped row {sckId=169255, workingGroupId=5} — sckId=169255 not found in SCK",
Person not found - these are testing accounts
"PersonToTrainingProvider: skipped row {personId=37255, trainingProvId=2001} — personId=37255 not found in Person",
"PersonToWorkingGroup: skipped row {personId=40125, workingGroupId=8} — personId=40125 not found in Person",
"PersonToWorkingGroup: skipped row {personId=40276, workingGroupId=8} — personId=40276 not found in Person",
"PersonCompletedTraining: skipped row {id=2805} — personId=37255 not found in Person",
"PersonAchievedSCK: skipped row {id=15279} — personId=37255 not found in Person"