Migrate qualifications
The instruction describes how to migrate business processes with manual tasks that use qualifications from the WorkFusion platform v10.1.x to v10.2.1 and higher. For more information, refer to Migrating users and qualifications from previous platform versions | Migrating qualifications.
Migration check
Before executing the migration script, you are able to see records that the script will pick. Actually, this SQL snippet is a part of the migration script.
note
To see the records that will be ignored, change conditions from <= to > in the following expression:
HAVING SUM(rwq.qualifications_per_question) <= COUNT(rwq.question_count));
to
HAVING SUM(rwq.qualifications_per_question) > COUNT(rwq.question_count));
By default, the migration script skips all records from an entire Run even if even one manual task is non-applicable. To see all applicable or non-applicable records, change the condition:
WHERE EXISTS(SELECT rwq.run_id ...... <= COUNT(rwq.question_count));
to
WHERE run_with_qualifications.qualifications_per_question = 1; where = 1—applicable; > 1—non-applicable
WITH run_with_qualifications AS (
SELECT run.id AS run_id,
question.id AS question_id,
run.description_id,
run.executingtype,
COUNT(question.id) AS qualifications_per_question,
COUNT(run.id) OVER (PARTITION BY run.id) AS question_count
FROM ct.run AS run
LEFT JOIN ct.campaignmap AS cm ON (
(run.status IN ('DELETED', 'DRAFT') AND cm.parent = run.campaign_id) OR cm.id = run.campaignmap_id)
INNER JOIN ct.campaign AS campaign ON (
campaign.id = cm.campaign OR campaign.id = run.campaign_id
) --AND campaign.executingtype = 'HUMAN'
INNER JOIN ct.question AS question ON question.id = campaign.question_id
INNER JOIN ct.qualificationrequirement AS qualification_requirement
ON qualification_requirement.question_id = question.id
INNER JOIN ct.qualification AS qualification ON qualification.id = qualification_requirement.qualification_id
WHERE run.executingtype IN ('HUMAN', 'COMPOSITE')
GROUP BY run.id, run.description_id, run.executingtype, question.id
)
SELECT run_with_qualifications.run_id,
run_with_qualifications.description_id,
CAST(IIF(run_with_qualifications.executingtype = 'HUMAN', 1, 0) AS BIT) AS is_manual,
question.id AS question_id,
question.workspacepreviewscheme_id,
question.workspacepreviewdata,
campaign.uuid AS campaign_uuid,
campaign.title AS title
FROM ct.question AS question
INNER JOIN run_with_qualifications ON question.id = run_with_qualifications.question_id
INNER JOIN ct.campaign AS campaign ON campaign.question_id = question.id
WHERE EXISTS(SELECT rwq.run_id
FROM run_with_qualifications rwq
WHERE rwq.run_id = run_with_qualifications.run_id
GROUP BY rwq.run_id
HAVING SUM(rwq.qualifications_per_question) <= COUNT(rwq.question_count));
Migration script
To perform qualification migrations, execute the following script.
caution
Execute the script under the user who can create tables or procedures, for example, wf_dba.
Expand to view the script
USE workfusion;
IF NOT EXISTS(SELECT *
FROM sys.sysobjects
WHERE name = 'migration_log'
AND xtype = 'U')
CREATE TABLE ct.migration_log
(
id BIGINT IDENTITY
CONSTRAINT PK_migration_log PRIMARY KEY,
question_id BIGINT,
campaign_id BIGINT,
run_description_id BIGINT,
old_question_preview_schema_id BIGINT,
new_question_preview_schema_id BIGINT,
old_question_preview_data VARCHAR(MAX),
new_question_preview_data VARCHAR(MAX),
old_run_description_preview_schema_id BIGINT,
new_run_description_preview_schema_id BIGINT,
old_run_description_preview_data VARCHAR(MAX),
new_run_description_preview_data VARCHAR(MAX),
old_run_step_description VARCHAR(MAX),
new_run_step_description VARCHAR(MAX),
old_campaign_step_description VARCHAR(MAX),
new_campaign_step_description VARCHAR(MAX)
)
IF NOT EXISTS(SELECT *
FROM sys.sysobjects
WHERE name = 'migration_log_nested_updates'
AND xtype = 'U')
CREATE TABLE ct.migration_log_nested_updates
(
id BIGINT IDENTITY
CONSTRAINT PK_migration_log_nstd_updts PRIMARY KEY,
run_description_id BIGINT,
old_run_description_preview_schema_id BIGINT,
new_run_description_preview_schema_id BIGINT,
old_run_description_preview_data VARCHAR(MAX),
new_run_description_preview_data VARCHAR(MAX),
old_run_step_description VARCHAR(MAX),
new_run_step_description VARCHAR(MAX),
actions VARCHAR(MAX),
migration_log_id BIGINT
CONSTRAINT fk_migration_log_nstd_upds_id REFERENCES ct.migration_log
)
go
IF NOT EXISTS(SELECT *
FROM sys.sysobjects
WHERE name = 'migration_log_qualification_requirement'
AND xtype = 'U')
CREATE TABLE ct.migration_log_qualification_requirement
(
id BIGINT IDENTITY
CONSTRAINT PK_migration_log_qualification_requirement PRIMARY KEY,
comparatortype NVARCHAR(40) NOT NULL,
localevalue NVARCHAR(255),
value INT,
qualification_id BIGINT,
question_id BIGINT,
version BIGINT,
preset_id BIGINT,
crowd_id BIGINT,
migration_log_id BIGINT
CONSTRAINT fk_migration_log_q_r_id REFERENCES ct.migration_log
)
IF NOT EXISTS(SELECT *
FROM sys.sysobjects
WHERE name = 'migration_log_field_schema'
AND xtype = 'U')
CREATE TABLE ct.migration_log_field_schema
(
id BIGINT IDENTITY
CONSTRAINT PK_migration_log_field_schema PRIMARY KEY,
uuid NVARCHAR(36) NOT NULL UNIQUE,
name NVARCHAR(255) NOT NULL,
description NVARCHAR(1000),
status NVARCHAR(255) NOT NULL,
origination NVARCHAR(50) NOT NULL,
inserted_answer_id BIGINT,
migration_log_id BIGINT
CONSTRAINT fk_migration_log_f_s_id references ct.migration_log
)
DROP PROCEDURE IF EXISTS ct.ExtendsWSPreviewScheme;
GO
CREATE PROCEDURE ct.ExtendsWSPreviewScheme @field_schema_id BIGINT, @migration_log_id BIGINT, @answer_type_id BIGINT
AS
BEGIN
DECLARE
@field_scheme_name_to_rename VARCHAR(MAX) = (SELECT fs.name
FROM ct.fieldschema AS fs
WHERE fs.id = @field_schema_id
AND name NOT LIKE '% (Qualifications Migrated)');
IF @field_scheme_name_to_rename IS NOT NULL
BEGIN
DECLARE @sequence_number INT = (SELECT MAX(a.sequencenumber) FROM ct.answer AS a WHERE a.fieldschemaid = @field_schema_id);
INSERT INTO ct.answer (fieldschemaid, answer_type_id, answer, answercode, sequencenumber, lastModified, allowna, required)
VALUES (@field_schema_id, @answer_type_id, '_sys_user_groups', '_sys_user_groups', @sequence_number + 1, GETDATE(), 0, 0);
PRINT 'Inserted into ANSWER with id: ' + CAST(SCOPE_IDENTITY() AS VARCHAR);
--------------------------LOG----------------------------------
INSERT INTO ct.migration_log_field_schema(uuid, name, description, status, origination, inserted_answer_id, migration_log_id)
SELECT fs.uuid, fs.name, fs.description, fs.status, fs.origination, SCOPE_IDENTITY(), @migration_log_id
FROM ct.fieldschema AS fs
WHERE fs.id = @field_schema_id;
------------------------LOG_END--------------------------------
PRINT 'Updating FIELD_SCHEMA name: ' + @field_scheme_name_to_rename + ' with id: ' + CAST(@field_schema_id AS VARCHAR)
UPDATE ct.fieldschema
SET name = name + ' (Qualifications Migrated)',
origination = 'CREATED_AUTOMATICALLY'
WHERE id = @field_schema_id;
END
END
GO
BEGIN TRANSACTION
BEGIN TRY
DECLARE @run_cursor CURSOR;
SET @run_cursor = CURSOR LOCAL FAST_FORWARD FOR
WITH run_with_qualifications AS (
SELECT run.id AS run_id,
question.id AS question_id,
run.description_id,
run.executingtype,
COUNT(question.id) AS qualifications_per_question,
COUNT(run.id) OVER (PARTITION BY run.id) AS question_count
FROM ct.run AS run
LEFT JOIN ct.campaignmap AS cm ON (
(run.status IN ('DELETED', 'DRAFT') AND cm.parent = run.campaign_id) OR cm.id = run.campaignmap_id)
INNER JOIN ct.campaign AS campaign ON (
campaign.id = cm.campaign OR campaign.id = run.campaign_id
) --AND campaign.executingtype = 'HUMAN'
INNER JOIN ct.question AS question ON question.id = campaign.question_id
INNER JOIN ct.qualificationrequirement AS qualification_requirement
ON qualification_requirement.question_id = question.id
INNER JOIN ct.qualification AS qualification ON qualification.id = qualification_requirement.qualification_id
WHERE run.executingtype IN ('HUMAN', 'COMPOSITE')
GROUP BY run.id, run.description_id, run.executingtype, question.id
)
SELECT run_with_qualifications.run_id,
run_with_qualifications.description_id AS run_description_id,
run_description.stepdescription AS run_step_description,
run_description.workspacepreviewscheme_id AS run_ws_preview_id,
run_description.workspacepreviewdata AS run_ws_preview_data,
run.campaign_id AS run_campaign_id,
CAST(IIF(run_with_qualifications.executingtype = 'HUMAN', 1, 0) AS BIT) AS is_manual,
question.id AS question_id,
question.workspacepreviewscheme_id,
question.workspacepreviewdata,
campaign.uuid AS campaign_uuid,
campaign.id AS campaign_id
FROM ct.question AS question
INNER JOIN run_with_qualifications ON question.id = run_with_qualifications.question_id
INNER JOIN ct.campaign AS campaign ON campaign.question_id = question.id
INNER JOIN ct.run on run.id = run_with_qualifications.run_id
INNER JOIN ct.rundescription AS run_description ON run_description.id = run.description_id
WHERE EXISTS(SELECT rwq.run_id
FROM run_with_qualifications rwq
WHERE rwq.run_id = run_with_qualifications.run_id
GROUP BY rwq.run_id
HAVING SUM(rwq.qualifications_per_question) <= COUNT(rwq.question_count));
DECLARE @run_id BIGINT;
DECLARE @run_description_id BIGINT;
DECLARE @is_manual_run BIT;
DECLARE @question_id BIGINT;
DECLARE @question_ws_preview_data VARCHAR(MAX);
DECLARE @question_ws_preview_scheme_id BIGINT;
DECLARE @run_step_description VARCHAR(MAX);
DECLARE @run_ws_preview_scheme_id BIGINT;
DECLARE @run_ws_preview_data VARCHAR(MAX);
DECLARE @campaign_uuid VARCHAR(MAX);
DECLARE @campaign_id BIGINT;
DECLARE @run_campaign_id BIGINT;
CREATE TABLE #tmp_data_to_update
(
run_step VARCHAR(MAX),
campaign_id BIGINT primary key,
campaign_step VARCHAR(MAX)
);
OPEN @run_cursor;
FETCH NEXT FROM @run_cursor
INTO @run_id, @run_description_id, @run_step_description,
@run_ws_preview_scheme_id, @run_ws_preview_data, @run_campaign_id,
@is_manual_run,
@question_id, @question_ws_preview_scheme_id, @question_ws_preview_data,
@campaign_uuid, @campaign_id;
DECLARE @answer_type_id BIGINT = (SELECT id
FROM ct.answertype
WHERE code = 'FREE_TEXT');
DECLARE @user_groups_field_schema_id BIGINT = (SELECT fs.id
FROM ct.fieldschema fs
WHERE fs.name = 'User Groups (Qualifications Migrated)')
DECLARE @user_groups_answer_id BIGINT = (SELECT a.id
FROM ct.answer AS a
WHERE a.fieldschemaid = @user_groups_field_schema_id);
IF @@fetch_status = 0 AND @user_groups_field_schema_id IS NULL
BEGIN
INSERT INTO ct.fieldschema (uuid, name, description, status, origination)
VALUES (NEWID(),
'User Groups (Qualifications Migrated)',
'User groups scheme based on qualifications',
'ACTIVE',
'CREATED_AUTOMATICALLY');
SET @user_groups_field_schema_id = SCOPE_IDENTITY();
PRINT 'Inserted into field_scheme: ' + CAST(@user_groups_field_schema_id AS VARCHAR)
INSERT INTO ct.answer (fieldschemaid, answer_type_id, answer, answercode, sequencenumber, lastModified, allowna, required)
VALUES (@user_groups_field_schema_id, @answer_type_id, '_sys_user_groups', '_sys_user_groups', 1, GETDATE(), 0, 0);
SET @user_groups_answer_id = SCOPE_IDENTITY();
PRINT 'Inserted into answer: ' + CAST(@user_groups_answer_id AS VARCHAR);
END
WHILE @@fetch_status = 0
BEGIN
PRINT '---------------------------------------------------------'
PRINT 'Processing RUN table with id: ' + CAST(@run_id AS VARCHAR);
PRINT 'Processing RUN_DESCRIPTION table with id: ' + CAST(@run_description_id AS VARCHAR);
PRINT 'Processing QUESTION table with id: ' + CAST(@question_id AS VARCHAR);
PRINT 'Processing CAMPAIGN table with id: ' + CAST(@campaign_id AS VARCHAR);
IF @run_step_description IS NOT NULL AND
(SELECT COUNT(*) FROM #tmp_data_to_update AS tmp WHERE tmp.campaign_id = @run_campaign_id) = 0
BEGIN
INSERT INTO #tmp_data_to_update(run_step, campaign_step, campaign_id)
SELECT @run_step_description, campaign.stepdescription, @run_campaign_id
FROM ct.campaign
WHERE campaign.id = @run_campaign_id;
END
DECLARE @qualification_id BIGINT;
DECLARE @qualification_name VARCHAR(MAX);
SELECT @qualification_id = q.id, @qualification_name = q.name
FROM ct.qualification AS q
INNER JOIN ct.qualificationrequirement AS qm ON qm.qualification_id = q.id
WHERE qm.question_id = @question_id;
DECLARE @new_question_ws_preview_data VARCHAR(MAX);
DECLARE @new_run_ws_preview_data VARCHAR(MAX);
DECLARE @new_question_preview_schema_id BIGINT;
DECLARE @new_run_preview_schema_id BIGINT;
DECLARE @migration_log_id BIGINT;
DECLARE @is_qualification_processed BIT = (
SELECT CAST(IIF(COUNT(*) > 0, 0, 1) AS BIT)
FROM ct.qualificationrequirement
WHERE question_id = @question_id
AND qualification_id = @qualification_id);
--log old data--
DECLARE @campaign_descr VARCHAR(MAX) = (SELECT campaign.stepdescription
FROM ct.campaign AS campaign
WHERE campaign.id = @run_campaign_id);
INSERT INTO ct.migration_log(question_id, run_description_id, old_run_step_description,
old_run_description_preview_schema_id, old_run_description_preview_data,
old_question_preview_schema_id, old_question_preview_data,
old_campaign_step_description)
VALUES (@question_id, @run_description_id, @run_step_description,
@run_ws_preview_scheme_id, @run_ws_preview_data,
@question_ws_preview_scheme_id, @question_ws_preview_data,
@campaign_descr);
SET @migration_log_id = SCOPE_IDENTITY();
--end log old data--
--already removed link between question and qualifications?--
IF @is_qualification_processed = 0
BEGIN
--------------------------LOG----------------------------------
INSERT INTO ct.migration_log_qualification_requirement(comparatortype, localevalue, value, qualification_id,
question_id,
version, preset_id, crowd_id, migration_log_id)
SELECT qr.comparatortype,
qr.localevalue,
qr.value,
qr.qualification_id,
qr.question_id,
qr.version,
qr.preset_id,
qr.crowd_id,
@migration_log_id
FROM ct.qualificationrequirement AS qr
WHERE qr.question_id = @question_id
AND qr.qualification_id = @qualification_id;
------------------------LOG_END--------------------------------
DELETE FROM ct.qualificationrequirement WHERE question_id = @question_id AND qualification_id = @qualification_id;
PRINT 'Deleted from QUALIFICATION_REQUIREMENT with question_id: ' + CAST(@question_id AS VARCHAR) + '' +
' and qualification_id: ' + CAST(@qualification_id AS VARCHAR);
END
--end--
DECLARE @is_mt_from_bp BIT = IIF(@is_manual_run = 1 AND @run_step_description IS NOT NULL, 1, 0);
--if task is from bp, it will required updates another runs. just log old data
IF @is_mt_from_bp = 1
BEGIN
print 'insert migration log id: ' + cast(@migration_log_id as varchar);
INSERT INTO ct.migration_log_nested_updates(run_description_id, migration_log_id, old_run_step_description,
old_run_description_preview_schema_id, old_run_description_preview_data,
actions)
SELECT rd.id, @migration_log_id, rd.stepdescription, rd.workspacepreviewscheme_id, rd.workspacepreviewdata, 'BACKUP'
FROM ct.run
INNER JOIN ct.rundescription AS rd ON run.description_id = rd.id
LEFT JOIN ct.migration_log_nested_updates AS nested ON rd.id = nested.run_description_id
WHERE run.campaign_id = @run_campaign_id
AND nested.id IS NULL
AND run.status <> 'DELETED';
END
--end--
DECLARE @qualification_ws_data VARCHAR(MAX) = '/qualifications/' + @qualification_name;
DECLARE @ws_qualification_preview_data VARCHAR(MAX) = '{"name": "_sys_user_groups", "value": "' + @qualification_ws_data + '"}';
IF @question_ws_preview_data IS NULL
BEGIN
PRINT 'Adding QUESTION ws preview data';
SET @new_question_ws_preview_data = '[' + @ws_qualification_preview_data + ']';
SET @new_run_ws_preview_data = @new_question_ws_preview_data;
UPDATE ct.question SET workspacepreviewdata = @new_question_ws_preview_data WHERE id = @question_id;
IF @is_manual_run = 1
BEGIN
PRINT 'Add RUN_DESCRIPTION ws preview data';
UPDATE ct.rundescription
SET workspacepreviewdata = @new_run_ws_preview_data
WHERE id = @run_description_id;
END
PRINT 'Added ws preview data: ' + @new_question_ws_preview_data;
END
ELSE
BEGIN
SET @new_question_ws_preview_data =
JSON_MODIFY(@question_ws_preview_data, 'append $', JSON_QUERY(@ws_qualification_preview_data, '$'));
SET @new_run_ws_preview_data =
JSON_MODIFY(@run_ws_preview_data, 'append $', JSON_QUERY(@ws_qualification_preview_data, '$'));
PRINT 'Update existing QUESTION ws preview data: ' + @question_ws_preview_data;
UPDATE ct.question SET workspacepreviewdata = @new_question_ws_preview_data WHERE id = @question_id;
PRINT 'Updated QUESTION ws preview data with: ' + @new_question_ws_preview_data;
IF @is_manual_run = 1
BEGIN
PRINT 'Update existing RUN_DESCRIPTION ws preview data: ' + @run_ws_preview_data;
UPDATE ct.rundescription
SET workspacepreviewdata = @new_run_ws_preview_data
WHERE id = @run_description_id;
PRINT 'Updated RUN_DESCRIPTION ws preview data with: ' + @new_run_ws_preview_data;
END
END
IF @question_ws_preview_scheme_id IS NULL
BEGIN
PRINT 'No ws preview scheme id'
SET @new_question_preview_schema_id = @user_groups_field_schema_id;
SET @new_run_preview_schema_id = @user_groups_field_schema_id;
UPDATE ct.question
SET workspacepreviewscheme_id = @new_question_preview_schema_id
WHERE id = @question_id;
PRINT 'Updated QUESTION ws preview scheme id: ' + CAST(@new_question_preview_schema_id AS VARCHAR);
IF @is_manual_run = 1
BEGIN
UPDATE ct.rundescription
SET workspacepreviewscheme_id = @new_run_preview_schema_id
WHERE id = @run_description_id;
PRINT 'Updated RUN_DESCRIPTION ws preview scheme id: ' + CAST(@new_run_preview_schema_id AS VARCHAR);
END
END
ELSE
BEGIN
SET @new_question_preview_schema_id = @question_ws_preview_scheme_id;
SET @new_run_preview_schema_id = @question_ws_preview_scheme_id;
IF @is_qualification_processed = 0
BEGIN
PRINT 'WS preview scheme exists';
EXECUTE ct.ExtendsWSPreviewScheme @question_ws_preview_scheme_id, @migration_log_id, @answer_type_id;
IF @is_manual_run = 1
BEGIN
EXECUTE ct.ExtendsWSPreviewScheme @run_ws_preview_scheme_id, @migration_log_id, @answer_type_id;
END
END
END
-- updating step descriptions --
DECLARE @new_campaign_step_description VARCHAR(MAX);
DECLARE @new_run_step_description VARCHAR(MAX);
IF @is_manual_run = 0 OR @is_mt_from_bp = 1
BEGIN
DECLARE @sql NVARCHAR(MAX);
DECLARE @parameter_definitions NVARCHAR(100) = N'@json nvarchar(max) output,@json_data nvarchar(max)';
DECLARE @json NVARCHAR(MAX);
DECLARE @json_data NVARCHAR(MAX);
DECLARE @run_step VARCHAR(MAX);
DECLARE @campaign_step VARCHAR(MAX);
SELECT @campaign_step = tmp.campaign_step,
@run_step = tmp.run_step
FROM #tmp_data_to_update AS tmp
WHERE tmp.campaign_id = @run_campaign_id;
-- if not qualifications is not processed (configurations is shared between runs Deleted and etc)
-- update step_descriptions
IF @is_qualification_processed = 0
BEGIN
SET @json = @campaign_step
SET @json_data = @new_question_ws_preview_data
PRINT 'Processing root CAMPAIGN with id: ' + CAST(@run_campaign_id AS VARCHAR);
PRINT 'Adjusting root CAMPAIGN.step_description for campaign_uuid: ' + @campaign_uuid;
PRINT 'Updating root CAMPAIGN.step_description: ' + @campaign_descr;
SET @sql = N'set @json = JSON_MODIFY(@json, ''$."' + @campaign_uuid + '".workSpacePreviewData'', @json_data);';
EXEC sp_executesql @sql, @parameter_definitions, @json output, @json_data;
SET @json_data = IIF(@question_ws_preview_scheme_id IS NULL, @user_groups_field_schema_id,
@question_ws_preview_scheme_id);
SET @sql = N'set @json = JSON_MODIFY(@json, ''$."' + @campaign_uuid +
'".workSpacePreviewSchemeId'', @json_data);';
EXEC sp_executesql @sql, @parameter_definitions, @json output, @json_data;
SET @new_campaign_step_description = @json;
UPDATE #tmp_data_to_update
SET campaign_step = @new_campaign_step_description
WHERE campaign_id = @run_campaign_id;
END
-- end update campaign step description --
-- start update run descriptions --
-- always need to update run step descriptions because it is unique
SET @json = @run_step
SET @json_data = @new_question_ws_preview_data
SET @sql = N'set @json = JSON_MODIFY(@json, ''$."' + @campaign_uuid + '".workSpacePreviewData'', @json_data);';
EXEC sp_executesql @sql, @parameter_definitions, @json output, @json_data;
SET @json_data =
IIF(@question_ws_preview_scheme_id IS NULL, @user_groups_field_schema_id,
@question_ws_preview_scheme_id);
SET @sql = N'set @json = JSON_MODIFY(@json, ''$."' + @campaign_uuid +
'".workSpacePreviewSchemeId'', @json_data);';
EXEC sp_executesql @sql, @parameter_definitions, @json output, @json_data;
SET @new_run_step_description = @json;
PRINT 'Processing RUN_DESCRIPTION id: ' + CAST(@run_description_id AS VARCHAR)
PRINT 'Adjusting RUN_DESCRIPTION.step_description for campaign_uuid: ' + @campaign_uuid;
PRINT 'Updating RUN_DESCRIPTION.step_description: ' + @run_step_description;
SET @new_run_step_description = @json;
UPDATE #tmp_data_to_update
SET run_step = @new_run_step_description
WHERE campaign_id = @run_campaign_id;
------------end update run descriptions--------------
-- if mt in bp, it require update another runs.
IF @is_mt_from_bp = 1
BEGIN
DECLARE @root_id AS BIGINT = @run_id;
PRINT 'Updating nested RUNs for RUN root id: ' + CAST(@root_id AS VARCHAR);
CREATE TABLE #tmp_nested_runs_to_update
(
tmp_rd_id BIGINT
);
WITH NestedRunsToUpdate (id, uuid, parent_uuid, children_uuid, rd_id, Level) AS (
-- Anchor member definition
SELECT e.id,
e.uuid,
e.parentrunuuid,
e.childrenrunuuid,
rd.id AS rd_id,
0 AS Level
FROM ct.run AS e
INNER JOIN ct.rundescription AS rd ON e.description_id = rd.id
WHERE e.id = @root_id
UNION ALL
-- Recursive member definition
SELECT e.id, e.uuid, e.parentrunuuid, e.childrenrunuuid, rd.id AS rd_id, Level + 1
FROM ct.run AS e
INNER JOIN NestedRunsToUpdate AS d ON e.uuid = d.children_uuid
INNER JOIN ct.rundescription AS rd ON e.description_id = rd.id
WHERE e.executingtype <> 'HUMAN'
AND e.status <> 'DELETED'
)
INSERT
INTO #tmp_nested_runs_to_update(tmp_rd_id)
SELECT rd_id
FROM NestedRunsToUpdate
WHERE rd_id <> @run_description_id;
--------------------------LOG--------------------
DECLARE @nested_to_update NVARCHAR(MAX);
SET @nested_to_update =
(SELECT a.tmp_rd_id AS run_description_id FROM #tmp_nested_runs_to_update AS a FOR JSON AUTO)
PRINT 'Nested RUN_DESCRIPTION updates for run_description_id: ' + @nested_to_update
PRINT 'with ws preview id/data: ' + cast(@new_run_preview_schema_id AS VARCHAR) + ', ' +
@new_run_ws_preview_data
print 'migration log id update: ' + cast(@migration_log_id as varchar)
UPDATE ct.migration_log_nested_updates
SET new_run_description_preview_schema_id = @new_run_preview_schema_id,
new_run_description_preview_data = @new_run_ws_preview_data,
migration_log_id = @migration_log_id,
actions = actions + ', PREVIEW_UPDATE'
WHERE run_description_id IN (SELECT tmp_rd_id FROM #tmp_nested_runs_to_update);
-----------------------END LOG-------------------
UPDATE ct.rundescription
SET workspacepreviewscheme_id = @new_run_preview_schema_id,
workspacepreviewdata = @new_run_ws_preview_data
WHERE id IN (SELECT tmp_rd_id FROM #tmp_nested_runs_to_update);
DROP TABLE #tmp_nested_runs_to_update;
END
-- end updating nested runs --
END
--------------------------LOG----------------------------------
UPDATE ct.migration_log
SET new_question_preview_data = @new_question_ws_preview_data,
new_run_description_preview_data = @new_run_ws_preview_data,
new_question_preview_schema_id = @new_question_preview_schema_id,
new_run_description_preview_schema_id = @new_run_preview_schema_id,
campaign_id = @campaign_id,
run_description_id = @run_description_id,
question_id = @question_id
WHERE id = @migration_log_id;
------------------------LOG_END--------------------------------
FETCH NEXT FROM @run_cursor
INTO @run_id, @run_description_id, @run_step_description,
@run_ws_preview_scheme_id, @run_ws_preview_data, @run_campaign_id,
@is_manual_run,
@question_id, @question_ws_preview_scheme_id, @question_ws_preview_data,
@campaign_uuid, @campaign_id;
END
-- finally update step descriptions --
DECLARE @data_to_update NVARCHAR(MAX) =
(SELECT run.description_id AS description_id, run.campaign_id AS root_campaign_id
FROM #tmp_data_to_update AS tmp
INNER JOIN ct.run AS run ON tmp.campaign_id = run.campaign_id
FOR JSON AUTO)
IF @data_to_update IS NOT NULL
BEGIN
PRINT '---------------------------------------------------------'
-- updating description steps --
PRINT 'Updating step descriptions for: ' + @data_to_update
UPDATE ct.migration_log
SET new_run_step_description = tmp.run_step,
new_campaign_step_description = tmp.campaign_step
FROM #tmp_data_to_update AS tmp
INNER JOIN ct.run AS run ON tmp.campaign_id = run.campaign_id
WHERE migration_log.run_description_id = run.description_id;
UPDATE ct.migration_log_nested_updates
SET new_run_step_description = tmp.run_step,
actions = actions + ', STEP_UPDATE'
FROM #tmp_data_to_update AS tmp
INNER JOIN ct.run AS run ON tmp.campaign_id = run.campaign_id
WHERE migration_log_nested_updates.run_description_id = run.description_id;
UPDATE ct.campaign
SET stepdescription = tmp.campaign_step
FROM #tmp_data_to_update AS tmp
WHERE campaign.id = tmp.campaign_id;
UPDATE ct.rundescription
SET stepdescription = tmp.run_step
FROM #tmp_data_to_update AS tmp
INNER JOIN ct.run AS run ON tmp.campaign_id = run.campaign_id
WHERE run.description_id = rundescription.id;
END
DROP TABLE #tmp_data_to_update;
CLOSE @run_cursor;
DEALLOCATE @run_cursor;
PRINT '---------------------------------------------------------'
PRINT 'DONE';
-- ROLLBACK TRANSACTION
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT error_message()
PRINT error_line()
PRINT '--------------- Migrations FAILED ---------------';
ROLLBACK TRANSACTION
END CATCH
DROP PROCEDURE ct.ExtendsWSPreviewScheme;
You can see how migrations will go without applying changes. For that, uncomment ROLLBACK TRANSACTION and comment COMMIT TRANSACTION instead.
All changes (remove, rename, insert, update) log into special tables:
ct.migration_log_qualification_requirementct.migration_log_field_schemact.migration_log_nested_updatesct.migration_log
After you check that all qualifications migrated successfully, you can remove these tables:
DROP TABLE ct.migration_log_qualification_requirement;
DROP TABLE ct.migration_log_nested_updates;
DROP TABLE ct.migration_log_field_schema;
DROP TABLE ct.migration_log;