Check Test Step Reuse at Codebeamer Upgrade
Since Codebeamer release 22.10-LTS, the performance is improved at test step reuse. To achieve better performance, a data update might be required before upgrading the system for this or newer versions. For the data upgrade, a Test Steps table field must exist in the test case type trackers.
Application Configuration
The following section in the application configuration enables the renaming of table fields:
"testManagement": {
"disableEditingBuiltInFieldsName" : false }
"disableEditingBuiltInFieldsName" : false }
This option must be set to true in the future to avoid issues with the Test Steps features.
Database Check
Because of the above configuration option, Test Steps table fields must be checked before Codebeamer upgrade. If the name of the field has been changed, it must be restored to Test Steps.
Find the Tracker and Project IDs Before Starting the Upgrade to 22.04-SP5 Configuration Export
To check if the database contains a renamed Test Steps table field, run the following query. The query returns the IDs, name and Project IDs of the trackers where the field has been renamed.
There are three queries which need to be executed before continuing the upgrade.
Process the query results in the following order:
1. Query to check the Test Case Template:
WITH TEST_STEPS_FIELDS as (SELECT FIELD.parent_id, FIELD.reference_id
FROM object FIELD
INNER JOIN object_revision FIELD_REV
ON FIELD.id = FIELD_REV.object_id AND FIELD.revision = FIELD_REV.revision
WHERE FIELD.type_id = 25
AND FIELD_REV.name = 'Test Steps')
SELECT TEST_CASE_TRACKER.id as tracker_id,
TRACKER_REV_ART.name as tracker_name,
TEST_CASE_TRACKER.proj_id as project_id
FROM task_type TEST_CASE_TRACKER
INNER JOIN object TRACKER_ARTIFACT
on TEST_CASE_TRACKER.id = TRACKER_ARTIFACT.id
INNER JOIN object_revision TRACKER_REV_ART ON TRACKER_ARTIFACT.id = TRACKER_REV_ART.object_id AND TRACKER_ARTIFACT.revision = TRACKER_REV_ART.revision
LEFT OUTER JOIN TEST_STEPS_FIELDS ON TEST_CASE_TRACKER.id = TEST_STEPS_FIELDS.parent_id
WHERE TEST_CASE_TRACKER.desc_id = 102
AND TEST_CASE_TRACKER.template_id is NULL
AND TRACKER_ARTIFACT.deleted IN (0, 1)
AND TEST_STEPS_FIELDS.reference_id IS NULL
FROM object FIELD
INNER JOIN object_revision FIELD_REV
ON FIELD.id = FIELD_REV.object_id AND FIELD.revision = FIELD_REV.revision
WHERE FIELD.type_id = 25
AND FIELD_REV.name = 'Test Steps')
SELECT TEST_CASE_TRACKER.id as tracker_id,
TRACKER_REV_ART.name as tracker_name,
TEST_CASE_TRACKER.proj_id as project_id
FROM task_type TEST_CASE_TRACKER
INNER JOIN object TRACKER_ARTIFACT
on TEST_CASE_TRACKER.id = TRACKER_ARTIFACT.id
INNER JOIN object_revision TRACKER_REV_ART ON TRACKER_ARTIFACT.id = TRACKER_REV_ART.object_id AND TRACKER_ARTIFACT.revision = TRACKER_REV_ART.revision
LEFT OUTER JOIN TEST_STEPS_FIELDS ON TEST_CASE_TRACKER.id = TEST_STEPS_FIELDS.parent_id
WHERE TEST_CASE_TRACKER.desc_id = 102
AND TEST_CASE_TRACKER.template_id is NULL
AND TRACKER_ARTIFACT.deleted IN (0, 1)
AND TEST_STEPS_FIELDS.reference_id IS NULL
2. Query to check the renamed children Test Case tracker:
 |
The following queries are database-specific. Ensure you run only the query that corresponds to your specific database system. Running a query designed for a different database can result in errors or unexpected behavior. Verify your database type before executing any queries.
|
◦ Query for Oracle database:
WITH TEST_STEPS_FIELDS as (SELECT FIELD.parent_id, FIELD.reference_id
FROM object FIELD
INNER JOIN object_revision FIELD_REV
ON FIELD.id = FIELD_REV.object_id AND FIELD.revision = FIELD_REV.revision
WHERE FIELD.type_id = 25
AND FIELD_REV.name = 'Test Steps'),
TEMPLATE_TRACKERS_WITH_TEST_STEPS as (SELECT TEST_CASE_TRACKER.id as tracker_id,
TEST_STEPS_FIELDS.reference_id
FROM task_type TEST_CASE_TRACKER
INNER JOIN object TRACKER_ARTIFACT
on TEST_CASE_TRACKER.id = TRACKER_ARTIFACT.id
INNER JOIN object_revision TRACKER_REV_ART
ON TRACKER_ARTIFACT.id = TRACKER_REV_ART.object_id AND
TRACKER_ARTIFACT.revision = TRACKER_REV_ART.revision
LEFT OUTER JOIN TEST_STEPS_FIELDS ON TEST_CASE_TRACKER.id = TEST_STEPS_FIELDS.parent_id
WHERE TEST_CASE_TRACKER.desc_id = 102
AND TEST_CASE_TRACKER.template_id is NULL
AND TRACKER_ARTIFACT.deleted IN (0, 1))
,
CHILD_TRACKERS (tracker_id, proj_id, reference_id) as (SELECT child.id as tracker_id,
child.proj_id,
TEMPLATE_TRACKERS_WITH_TEST_STEPS.reference_id
FROM task_type child
INNER JOIN TEMPLATE_TRACKERS_WITH_TEST_STEPS
ON TEMPLATE_TRACKERS_WITH_TEST_STEPS.tracker_id = child.template_id
INNER JOIN object o on child.id = o.parent_id and
TEMPLATE_TRACKERS_WITH_TEST_STEPS.reference_id =
o.reference_id
INNER join object_revision r on o.id = r.object_id and o.revision = r.revision
UNION ALL
SELECT child_x.id as tracker_id,
child_x.proj_id,
CHILD_TRACKERS.reference_id
FROM task_type child_x
INNER JOIN CHILD_TRACKERS ON child_x.template_id = CHILD_TRACKERS.tracker_id
INNER JOIN object o
on child_x.id = o.parent_id and CHILD_TRACKERS.reference_id = o.reference_id
INNER join object_revision r on o.id = r.object_id and o.revision = r.revision)
SELECT CHILD_TRACKERS.tracker_id, CHILD_TRACKERS.proj_id, CHILD_TRACKERS.reference_id, TEST_STEPS_FIELD_REV.name
FROM CHILD_TRACKERS
LEFT OUTER JOIN object TEST_STEPS_FIELD ON CHILD_TRACKERS.tracker_id = TEST_STEPS_FIELD.parent_id and
CHILD_TRACKERS.reference_id = TEST_STEPS_FIELD.reference_id
INNER JOIN object_revision TEST_STEPS_FIELD_REV on TEST_STEPS_FIELD.id = TEST_STEPS_FIELD_REV.object_id AND
TEST_STEPS_FIELD.revision = TEST_STEPS_FIELD_REV.revision
WHERE TEST_STEPS_FIELD_REV.name NOT IN ('Test Steps', '..');
FROM object FIELD
INNER JOIN object_revision FIELD_REV
ON FIELD.id = FIELD_REV.object_id AND FIELD.revision = FIELD_REV.revision
WHERE FIELD.type_id = 25
AND FIELD_REV.name = 'Test Steps'),
TEMPLATE_TRACKERS_WITH_TEST_STEPS as (SELECT TEST_CASE_TRACKER.id as tracker_id,
TEST_STEPS_FIELDS.reference_id
FROM task_type TEST_CASE_TRACKER
INNER JOIN object TRACKER_ARTIFACT
on TEST_CASE_TRACKER.id = TRACKER_ARTIFACT.id
INNER JOIN object_revision TRACKER_REV_ART
ON TRACKER_ARTIFACT.id = TRACKER_REV_ART.object_id AND
TRACKER_ARTIFACT.revision = TRACKER_REV_ART.revision
LEFT OUTER JOIN TEST_STEPS_FIELDS ON TEST_CASE_TRACKER.id = TEST_STEPS_FIELDS.parent_id
WHERE TEST_CASE_TRACKER.desc_id = 102
AND TEST_CASE_TRACKER.template_id is NULL
AND TRACKER_ARTIFACT.deleted IN (0, 1))
,
CHILD_TRACKERS (tracker_id, proj_id, reference_id) as (SELECT child.id as tracker_id,
child.proj_id,
TEMPLATE_TRACKERS_WITH_TEST_STEPS.reference_id
FROM task_type child
INNER JOIN TEMPLATE_TRACKERS_WITH_TEST_STEPS
ON TEMPLATE_TRACKERS_WITH_TEST_STEPS.tracker_id = child.template_id
INNER JOIN object o on child.id = o.parent_id and
TEMPLATE_TRACKERS_WITH_TEST_STEPS.reference_id =
o.reference_id
INNER join object_revision r on o.id = r.object_id and o.revision = r.revision
UNION ALL
SELECT child_x.id as tracker_id,
child_x.proj_id,
CHILD_TRACKERS.reference_id
FROM task_type child_x
INNER JOIN CHILD_TRACKERS ON child_x.template_id = CHILD_TRACKERS.tracker_id
INNER JOIN object o
on child_x.id = o.parent_id and CHILD_TRACKERS.reference_id = o.reference_id
INNER join object_revision r on o.id = r.object_id and o.revision = r.revision)
SELECT CHILD_TRACKERS.tracker_id, CHILD_TRACKERS.proj_id, CHILD_TRACKERS.reference_id, TEST_STEPS_FIELD_REV.name
FROM CHILD_TRACKERS
LEFT OUTER JOIN object TEST_STEPS_FIELD ON CHILD_TRACKERS.tracker_id = TEST_STEPS_FIELD.parent_id and
CHILD_TRACKERS.reference_id = TEST_STEPS_FIELD.reference_id
INNER JOIN object_revision TEST_STEPS_FIELD_REV on TEST_STEPS_FIELD.id = TEST_STEPS_FIELD_REV.object_id AND
TEST_STEPS_FIELD.revision = TEST_STEPS_FIELD_REV.revision
WHERE TEST_STEPS_FIELD_REV.name NOT IN ('Test Steps', '..');
◦ Query for PostgreSQL or MySQL database:
WITH RECURSIVE
TEST_STEPS_FIELDS as (SELECT FIELD.parent_id, FIELD.reference_id
FROM object FIELD
INNER JOIN object_revision FIELD_REV
ON FIELD.id = FIELD_REV.object_id AND FIELD.revision = FIELD_REV.revision
WHERE FIELD.type_id = 25
AND FIELD_REV.name = 'Test Steps'),
TEMPLATE_TRACKERS_WITH_TEST_STEPS as (SELECT TEST_CASE_TRACKER.id as tracker_id,
TEST_STEPS_FIELDS.reference_id
FROM task_type TEST_CASE_TRACKER
INNER JOIN object TRACKER_ARTIFACT
on TEST_CASE_TRACKER.id = TRACKER_ARTIFACT.id
INNER JOIN object_revision TRACKER_REV_ART
ON TRACKER_ARTIFACT.id = TRACKER_REV_ART.object_id AND
TRACKER_ARTIFACT.revision = TRACKER_REV_ART.revision
LEFT OUTER JOIN TEST_STEPS_FIELDS ON TEST_CASE_TRACKER.id = TEST_STEPS_FIELDS.parent_id
WHERE TEST_CASE_TRACKER.desc_id = 102
AND TEST_CASE_TRACKER.template_id is NULL
AND TRACKER_ARTIFACT.deleted IN (0, 1))
,
CHILD_TRACKERS as (SELECT child.id as tracker_id, child.proj_id, TEMPLATE_TRACKERS_WITH_TEST_STEPS.reference_id
FROM task_type child
INNER JOIN TEMPLATE_TRACKERS_WITH_TEST_STEPS
ON TEMPLATE_TRACKERS_WITH_TEST_STEPS.tracker_id = child.template_id
INNER JOIN object o on child.id = o.parent_id and
TEMPLATE_TRACKERS_WITH_TEST_STEPS.reference_id = o.reference_id
INNER join object_revision r on o.id = r.object_id and o.revision = r.revision
UNION ALL
SELECT child_x.id as tracker_id, child_x.proj_id, CHILD_TRACKERS.reference_id
FROM task_type child_x
INNER JOIN CHILD_TRACKERS ON child_x.template_id = CHILD_TRACKERS.tracker_id
INNER JOIN object o on child_x.id = o.parent_id and CHILD_TRACKERS.reference_id = o.reference_id
INNER join object_revision r on o.id = r.object_id and o.revision = r.revision)
SELECT CHILD_TRACKERS.tracker_id, CHILD_TRACKERS.proj_id, CHILD_TRACKERS.reference_id, TEST_STEPS_FIELD_REV.name
FROM CHILD_TRACKERS
LEFT OUTER JOIN object TEST_STEPS_FIELD ON CHILD_TRACKERS.tracker_id = TEST_STEPS_FIELD.parent_id and
CHILD_TRACKERS.reference_id = TEST_STEPS_FIELD.reference_id
INNER JOIN object_revision TEST_STEPS_FIELD_REV on TEST_STEPS_FIELD.id = TEST_STEPS_FIELD_REV.object_id AND
TEST_STEPS_FIELD.revision = TEST_STEPS_FIELD_REV.revision
WHERE TEST_STEPS_FIELD_REV.name NOT IN ('Test Steps', '..');
TEST_STEPS_FIELDS as (SELECT FIELD.parent_id, FIELD.reference_id
FROM object FIELD
INNER JOIN object_revision FIELD_REV
ON FIELD.id = FIELD_REV.object_id AND FIELD.revision = FIELD_REV.revision
WHERE FIELD.type_id = 25
AND FIELD_REV.name = 'Test Steps'),
TEMPLATE_TRACKERS_WITH_TEST_STEPS as (SELECT TEST_CASE_TRACKER.id as tracker_id,
TEST_STEPS_FIELDS.reference_id
FROM task_type TEST_CASE_TRACKER
INNER JOIN object TRACKER_ARTIFACT
on TEST_CASE_TRACKER.id = TRACKER_ARTIFACT.id
INNER JOIN object_revision TRACKER_REV_ART
ON TRACKER_ARTIFACT.id = TRACKER_REV_ART.object_id AND
TRACKER_ARTIFACT.revision = TRACKER_REV_ART.revision
LEFT OUTER JOIN TEST_STEPS_FIELDS ON TEST_CASE_TRACKER.id = TEST_STEPS_FIELDS.parent_id
WHERE TEST_CASE_TRACKER.desc_id = 102
AND TEST_CASE_TRACKER.template_id is NULL
AND TRACKER_ARTIFACT.deleted IN (0, 1))
,
CHILD_TRACKERS as (SELECT child.id as tracker_id, child.proj_id, TEMPLATE_TRACKERS_WITH_TEST_STEPS.reference_id
FROM task_type child
INNER JOIN TEMPLATE_TRACKERS_WITH_TEST_STEPS
ON TEMPLATE_TRACKERS_WITH_TEST_STEPS.tracker_id = child.template_id
INNER JOIN object o on child.id = o.parent_id and
TEMPLATE_TRACKERS_WITH_TEST_STEPS.reference_id = o.reference_id
INNER join object_revision r on o.id = r.object_id and o.revision = r.revision
UNION ALL
SELECT child_x.id as tracker_id, child_x.proj_id, CHILD_TRACKERS.reference_id
FROM task_type child_x
INNER JOIN CHILD_TRACKERS ON child_x.template_id = CHILD_TRACKERS.tracker_id
INNER JOIN object o on child_x.id = o.parent_id and CHILD_TRACKERS.reference_id = o.reference_id
INNER join object_revision r on o.id = r.object_id and o.revision = r.revision)
SELECT CHILD_TRACKERS.tracker_id, CHILD_TRACKERS.proj_id, CHILD_TRACKERS.reference_id, TEST_STEPS_FIELD_REV.name
FROM CHILD_TRACKERS
LEFT OUTER JOIN object TEST_STEPS_FIELD ON CHILD_TRACKERS.tracker_id = TEST_STEPS_FIELD.parent_id and
CHILD_TRACKERS.reference_id = TEST_STEPS_FIELD.reference_id
INNER JOIN object_revision TEST_STEPS_FIELD_REV on TEST_STEPS_FIELD.id = TEST_STEPS_FIELD_REV.object_id AND
TEST_STEPS_FIELD.revision = TEST_STEPS_FIELD_REV.revision
WHERE TEST_STEPS_FIELD_REV.name NOT IN ('Test Steps', '..');
3. Check Test Steps Field which are not Table Fields
These fields must be renamed before upgrade.
select orev.name, tt.id as tracker_id, tt.proj_id as project_id, o.reference_id
from object_revision orev
inner join object o on orev.object_id = o.id and orev.revision = o.revision
inner join task_type tt on o.parent_id = tt.id and tt.desc_id = 102
where o.reference_id < 1000000
and orev.name = 'Test Steps';
from object_revision orev
inner join object o on orev.object_id = o.id and orev.revision = o.revision
inner join task_type tt on o.parent_id = tt.id and tt.desc_id = 102
where o.reference_id < 1000000
and orev.name = 'Test Steps';
4. Check Working-Set Branches (only if you use working-set)
WITH BRANCHES AS (SELECT BRANCH_ARTIFACT.id,
BRANCH.proj_id,
BRANCH_ARTIFACT_REVISON.name
FROM object BRANCH_ARTIFACT
INNER JOIN object_revision BRANCH_ARTIFACT_REVISON
ON BRANCH_ARTIFACT.id = BRANCH_ARTIFACT_REVISON.object_id AND
BRANCH_ARTIFACT.revision = BRANCH_ARTIFACT_REVISON.revision
INNER JOIN task_type BRANCH on BRANCH_ARTIFACT.id = BRANCH.id
WHERE BRANCH_ARTIFACT.type_id = 36
AND BRANCH.desc_id = 102)
SELECT BRANCHES.*
FROM BRANCHES
WHERE BRANCHES.id NOT IN (SELECT BRANCHES.id
FROM BRANCHES
INNER JOIN object TEST_STEP_FIELD ON TEST_STEP_FIELD.parent_id = BRANCHES.id
INNER JOIN object_revision TEST_STEP_FIELD_REVISION
on TEST_STEP_FIELD.id = TEST_STEP_FIELD_REVISION.object_id AND
TEST_STEP_FIELD.revision = TEST_STEP_FIELD_REVISION.revision
WHERE TEST_STEP_FIELD_REVISION.name = 'Test Steps');
BRANCH.proj_id,
BRANCH_ARTIFACT_REVISON.name
FROM object BRANCH_ARTIFACT
INNER JOIN object_revision BRANCH_ARTIFACT_REVISON
ON BRANCH_ARTIFACT.id = BRANCH_ARTIFACT_REVISON.object_id AND
BRANCH_ARTIFACT.revision = BRANCH_ARTIFACT_REVISON.revision
INNER JOIN task_type BRANCH on BRANCH_ARTIFACT.id = BRANCH.id
WHERE BRANCH_ARTIFACT.type_id = 36
AND BRANCH.desc_id = 102)
SELECT BRANCHES.*
FROM BRANCHES
WHERE BRANCHES.id NOT IN (SELECT BRANCHES.id
FROM BRANCHES
INNER JOIN object TEST_STEP_FIELD ON TEST_STEP_FIELD.parent_id = BRANCHES.id
INNER JOIN object_revision TEST_STEP_FIELD_REVISION
on TEST_STEP_FIELD.id = TEST_STEP_FIELD_REVISION.object_id AND
TEST_STEP_FIELD.revision = TEST_STEP_FIELD_REVISION.revision
WHERE TEST_STEP_FIELD_REVISION.name = 'Test Steps');
The tracker_id and its project_id which has missing Test Steps table field can be retrieved by running the above queries.
 |
In case a tracker cannot be found in the relevant project, the trash should also be checked.
|
Find the Project Admin of a Project
To find the project admin of a project, run the following query. Make sure to replace <PROJECT_IDS_TO_CHECK> with the project ids returned by the above query.
WITH PROJECT_ADMIN AS (SELECT id
from ACL_ROLE
WHERE NAME = 'Project Admin'),
PROJECT_ADMIN_USER_IDS AS (SELECT DISTINCT MBR.to_id AS user_id, PRL.proj_id
FROM object PRL
INNER JOIN object_reference MBR
ON MBR.from_type_id = 5 AND MBR.from_id = PRL.id AND MBR.field_id = 1 AND
MBR.to_type_id = 1
INNER JOIN object PMA ON PMA.id = MBR.assoc_id AND PMA.type_id = 21
INNER JOIN PROJECT_ADMIN ON PRL.REFERENCE_ID = PROJECT_ADMIN.id
WHERE PRL.proj_id IN (<PROJECT_IDS_TO_CHECK>)
AND PRL.type_id = 19
AND PRL.deleted = 0
AND MBR.status_id = 3
AND MBR.deleted = 0)
SELECT PROJECT_ADMIN_USER_IDS.proj_id, USER_TABLE.*
FROM PROJECT_ADMIN_USER_IDS
INNER JOIN users USER_TABLE ON PROJECT_ADMIN_USER_IDS.user_id = USER_TABLE.id
ORDER BY PROJECT_ADMIN_USER_IDS.proj_id, USER_TABLE.id;
from ACL_ROLE
WHERE NAME = 'Project Admin'),
PROJECT_ADMIN_USER_IDS AS (SELECT DISTINCT MBR.to_id AS user_id, PRL.proj_id
FROM object PRL
INNER JOIN object_reference MBR
ON MBR.from_type_id = 5 AND MBR.from_id = PRL.id AND MBR.field_id = 1 AND
MBR.to_type_id = 1
INNER JOIN object PMA ON PMA.id = MBR.assoc_id AND PMA.type_id = 21
INNER JOIN PROJECT_ADMIN ON PRL.REFERENCE_ID = PROJECT_ADMIN.id
WHERE PRL.proj_id IN (<PROJECT_IDS_TO_CHECK>)
AND PRL.type_id = 19
AND PRL.deleted = 0
AND MBR.status_id = 3
AND MBR.deleted = 0)
SELECT PROJECT_ADMIN_USER_IDS.proj_id, USER_TABLE.*
FROM PROJECT_ADMIN_USER_IDS
INNER JOIN users USER_TABLE ON PROJECT_ADMIN_USER_IDS.user_id = USER_TABLE.id
ORDER BY PROJECT_ADMIN_USER_IDS.proj_id, USER_TABLE.id;
Restore the Name of Renamed Test Steps Fields
Recommendation on how to modify them before the upgrade:
• Restoring the name of the renamed Test Steps field must be performed in the tracker > before (re-)trying the upgrade of Codebeamer to the latest version.
• If the "disableEditingBuiltInFieldsName" property is set to false in the Application Configuration, the table field can be modified by clicking on the name of the table field in the tracker's configuration.
Before the Codebeamer upgrade starts, a validation is performed that checks the same query. This means that the upgrade cannot start until the Test Steps table field is restored.
If the Test Steps field is missing, for example, renamed or deleted, it is highly recommended to restore it through the Field section of the tracker configuration.
The default test steps table configuration is the following:
Test Steps table field is the crucial part for the test step reuse upgrade, but it is also highly recommended to use the default names of the other columns as well.
Identify Test Step IDs Exceeding 255 Characters for Validation and Delete if Needed
Due to the above mentioned "disableEditingBuiltInFieldsName" application configuration, using the test step Id column in the Test Steps table for different purposes was possible. It could mean that an Id can be longer than 255 character which is not permitted for the Test step reuse feature.
| | This issue is officially fixed from 22.10-SP8. |
There are two queries which can be executed to discover test cases with test step Ids exceeding 255 characters.
| | The following queries are database-specific. Ensure you run only the query that corresponds to your specific database system. Running a query designed for a different database can result in errors or unexpected behavior. Verify your database type before executing any queries. |
• Query for Oracle database:
WITH TEST_STEP_TABLE AS (SELECT DISTINCT FIELD.reference_id, FIELD.parent_id
FROM task TEST_CASE
INNER JOIN task_type TEST_CASE_TRACKER ON TEST_CASE.type_id = TEST_CASE_TRACKER.id
INNER JOIN object FIELD ON TEST_CASE_TRACKER.id = FIELD.parent_id
INNER JOIN object_revision FIELD_REV
ON FIELD.id = FIELD_REV.object_id AND FIELD.revision = FIELD_REV.revision
WHERE TEST_CASE_TRACKER.desc_id = 102
AND FIELD.type_id = 25
AND FIELD_REV.name = 'Test Steps'),
TEST_TABLE_FIELDS AS (SELECT DISTINCT FIELD_REV.name, FIELD.reference_id, FIELD.parent_id
FROM TEST_STEP_TABLE
INNER JOIN object FIELD ON TEST_STEP_TABLE.parent_id = FIELD.parent_id
INNER JOIN object_revision FIELD_REV
ON FIELD.id = FIELD_REV.object_id AND FIELD.revision = FIELD_REV.revision
WHERE FIELD.reference_id BETWEEN TEST_STEP_TABLE.reference_id + 1 AND TEST_STEP_TABLE.reference_id + 999999
AND FIELD.deleted IN (0, 1))
SELECT LISTAGG(TFV.field_value, '|') WITHIN GROUP (ORDER BY TFV.field_value) as step_id_regexp
FROM task_field_value TFV
INNER JOIN task TEST_CASE ON TFV.task_id = TEST_CASE.id
INNER JOIN TEST_TABLE_FIELDS ON TEST_CASE.type_id = TEST_TABLE_FIELDS.parent_id
INNER JOIN TEST_STEP_TABLE ON TEST_CASE.type_id = TEST_STEP_TABLE.parent_id
WHERE TFV.label_id BETWEEN TEST_STEP_TABLE.reference_id + 1 AND TEST_STEP_TABLE.reference_id + 999999
AND FLOOR(MOD(MOD(TFV.label_id, 1000000), 100)) = FLOOR(MOD(MOD(TEST_TABLE_FIELDS.reference_id, 1000000), 100))
AND TEST_TABLE_FIELDS.name = 'Id'
AND DBMS_LOB.GETLENGTH(TFV.field_value) > 255;
FROM task TEST_CASE
INNER JOIN task_type TEST_CASE_TRACKER ON TEST_CASE.type_id = TEST_CASE_TRACKER.id
INNER JOIN object FIELD ON TEST_CASE_TRACKER.id = FIELD.parent_id
INNER JOIN object_revision FIELD_REV
ON FIELD.id = FIELD_REV.object_id AND FIELD.revision = FIELD_REV.revision
WHERE TEST_CASE_TRACKER.desc_id = 102
AND FIELD.type_id = 25
AND FIELD_REV.name = 'Test Steps'),
TEST_TABLE_FIELDS AS (SELECT DISTINCT FIELD_REV.name, FIELD.reference_id, FIELD.parent_id
FROM TEST_STEP_TABLE
INNER JOIN object FIELD ON TEST_STEP_TABLE.parent_id = FIELD.parent_id
INNER JOIN object_revision FIELD_REV
ON FIELD.id = FIELD_REV.object_id AND FIELD.revision = FIELD_REV.revision
WHERE FIELD.reference_id BETWEEN TEST_STEP_TABLE.reference_id + 1 AND TEST_STEP_TABLE.reference_id + 999999
AND FIELD.deleted IN (0, 1))
SELECT LISTAGG(TFV.field_value, '|') WITHIN GROUP (ORDER BY TFV.field_value) as step_id_regexp
FROM task_field_value TFV
INNER JOIN task TEST_CASE ON TFV.task_id = TEST_CASE.id
INNER JOIN TEST_TABLE_FIELDS ON TEST_CASE.type_id = TEST_TABLE_FIELDS.parent_id
INNER JOIN TEST_STEP_TABLE ON TEST_CASE.type_id = TEST_STEP_TABLE.parent_id
WHERE TFV.label_id BETWEEN TEST_STEP_TABLE.reference_id + 1 AND TEST_STEP_TABLE.reference_id + 999999
AND FLOOR(MOD(MOD(TFV.label_id, 1000000), 100)) = FLOOR(MOD(MOD(TEST_TABLE_FIELDS.reference_id, 1000000), 100))
AND TEST_TABLE_FIELDS.name = 'Id'
AND DBMS_LOB.GETLENGTH(TFV.field_value) > 255;
SELECT TEST_CASE.id as test_case_id,
TEST_CASE_TRACKER.id as tracker_id,
TEST_CASE_TRACKER.proj_id as proj_id
FROM object ASSOC
INNER JOIN object_revision LATEST_ASSOC ON ASSOC.id = LATEST_ASSOC.object_id AND ASSOC.revision = LATEST_ASSOC.revision
INNER JOIN object_reference ASSOC_REF ON ASSOC.id = ASSOC_REF.assoc_id
INNER JOIN task TEST_CASE ON ASSOC_REF.from_id = TEST_CASE.id
INNER JOIN task_type TEST_CASE_TRACKER ON TEST_CASE.type_id = TEST_CASE_TRACKER.id
INNER JOIN object TRACKER_ARTIFACT on TEST_CASE_TRACKER.id = TRACKER_ARTIFACT.id
WHERE LATEST_ASSOC.deleted IN (0)
AND LATEST_ASSOC.name = 'derived'
AND LATEST_ASSOC.description LIKE '{"TestStepReferenceMap"%'
AND ASSOC_REF.field_id = 0
AND ASSOC_REF.assoc_id IS NOT NULL
AND ASSOC_REF.from_type_id = 9
AND ASSOC_REF.to_type_id = 9
AND TEST_CASE_TRACKER.desc_id = 102
AND TRACKER_ARTIFACT.deleted IN (0, 1)
AND REGEXP_LIKE(LATEST_ASSOC.description, ' < step_id_regexp_from_the_first_query > ');
TEST_CASE_TRACKER.id as tracker_id,
TEST_CASE_TRACKER.proj_id as proj_id
FROM object ASSOC
INNER JOIN object_revision LATEST_ASSOC ON ASSOC.id = LATEST_ASSOC.object_id AND ASSOC.revision = LATEST_ASSOC.revision
INNER JOIN object_reference ASSOC_REF ON ASSOC.id = ASSOC_REF.assoc_id
INNER JOIN task TEST_CASE ON ASSOC_REF.from_id = TEST_CASE.id
INNER JOIN task_type TEST_CASE_TRACKER ON TEST_CASE.type_id = TEST_CASE_TRACKER.id
INNER JOIN object TRACKER_ARTIFACT on TEST_CASE_TRACKER.id = TRACKER_ARTIFACT.id
WHERE LATEST_ASSOC.deleted IN (0)
AND LATEST_ASSOC.name = 'derived'
AND LATEST_ASSOC.description LIKE '{"TestStepReferenceMap"%'
AND ASSOC_REF.field_id = 0
AND ASSOC_REF.assoc_id IS NOT NULL
AND ASSOC_REF.from_type_id = 9
AND ASSOC_REF.to_type_id = 9
AND TEST_CASE_TRACKER.desc_id = 102
AND TRACKER_ARTIFACT.deleted IN (0, 1)
AND REGEXP_LIKE(LATEST_ASSOC.description, ' < step_id_regexp_from_the_first_query > ');
• Query for PostgreSQL database:
WITH TEST_STEP_TABLE AS (SELECT DISTINCT FIELD.reference_id, FIELD.parent_id
FROM task TEST_CASE
INNER JOIN task_type TEST_CASE_TRACKER ON TEST_CASE.type_id = TEST_CASE_TRACKER.id
INNER JOIN object FIELD ON TEST_CASE_TRACKER.id = FIELD.parent_id
INNER JOIN object_revision FIELD_REV
ON FIELD.id = FIELD_REV.object_id AND FIELD.revision = FIELD_REV.revision
WHERE TEST_CASE_TRACKER.desc_id = 102
AND FIELD.type_id = 25
AND FIELD_REV.name = 'Test Steps'),
TEST_TABLE_FIELDS AS (SELECT DISTINCT FIELD_REV.name, FIELD.reference_id, FIELD.parent_id
FROM TEST_STEP_TABLE
INNER JOIN object FIELD ON TEST_STEP_TABLE.parent_id = FIELD.parent_id
INNER JOIN object_revision FIELD_REV
ON FIELD.id = FIELD_REV.object_id AND FIELD.revision = FIELD_REV.revision
WHERE FIELD.reference_id BETWEEN TEST_STEP_TABLE.reference_id + 1 AND TEST_STEP_TABLE.reference_id + 999999
AND FIELD.deleted IN (0, 1))
SELECT string_agg(DISTINCT '%' || TFV.field_value || '%', '|') as step_id_regexp
FROM task_field_value TFV
INNER JOIN task TEST_CASE ON TFV.task_id = TEST_CASE.id
INNER JOIN TEST_TABLE_FIELDS ON TEST_CASE.type_id = TEST_TABLE_FIELDS.parent_id
INNER JOIN TEST_STEP_TABLE ON TEST_CASE.type_id = TEST_STEP_TABLE.parent_id
WHERE TFV.label_id BETWEEN TEST_STEP_TABLE.reference_id + 1 AND TEST_STEP_TABLE.reference_id + 999999
AND MOD(MOD(TFV.label_id, 1000000), 100) = MOD(MOD(TEST_TABLE_FIELDS.reference_id, 1000000), 100)
AND TEST_TABLE_FIELDS.name = 'Id'
AND LENGTH(TFV.field_value) > 255;
FROM task TEST_CASE
INNER JOIN task_type TEST_CASE_TRACKER ON TEST_CASE.type_id = TEST_CASE_TRACKER.id
INNER JOIN object FIELD ON TEST_CASE_TRACKER.id = FIELD.parent_id
INNER JOIN object_revision FIELD_REV
ON FIELD.id = FIELD_REV.object_id AND FIELD.revision = FIELD_REV.revision
WHERE TEST_CASE_TRACKER.desc_id = 102
AND FIELD.type_id = 25
AND FIELD_REV.name = 'Test Steps'),
TEST_TABLE_FIELDS AS (SELECT DISTINCT FIELD_REV.name, FIELD.reference_id, FIELD.parent_id
FROM TEST_STEP_TABLE
INNER JOIN object FIELD ON TEST_STEP_TABLE.parent_id = FIELD.parent_id
INNER JOIN object_revision FIELD_REV
ON FIELD.id = FIELD_REV.object_id AND FIELD.revision = FIELD_REV.revision
WHERE FIELD.reference_id BETWEEN TEST_STEP_TABLE.reference_id + 1 AND TEST_STEP_TABLE.reference_id + 999999
AND FIELD.deleted IN (0, 1))
SELECT string_agg(DISTINCT '%' || TFV.field_value || '%', '|') as step_id_regexp
FROM task_field_value TFV
INNER JOIN task TEST_CASE ON TFV.task_id = TEST_CASE.id
INNER JOIN TEST_TABLE_FIELDS ON TEST_CASE.type_id = TEST_TABLE_FIELDS.parent_id
INNER JOIN TEST_STEP_TABLE ON TEST_CASE.type_id = TEST_STEP_TABLE.parent_id
WHERE TFV.label_id BETWEEN TEST_STEP_TABLE.reference_id + 1 AND TEST_STEP_TABLE.reference_id + 999999
AND MOD(MOD(TFV.label_id, 1000000), 100) = MOD(MOD(TEST_TABLE_FIELDS.reference_id, 1000000), 100)
AND TEST_TABLE_FIELDS.name = 'Id'
AND LENGTH(TFV.field_value) > 255;
SELECT TEST_CASE.id as test_case_id,
TEST_CASE_TRACKER.id as tracker_id,
TEST_CASE_TRACKER.proj_id as proj_id
FROM object ASSOC
INNER JOIN object_revision LATEST_ASSOC ON ASSOC.id = LATEST_ASSOC.object_id AND ASSOC.revision = LATEST_ASSOC.revision
INNER JOIN object_reference ASSOC_REF ON ASSOC.id = ASSOC_REF.assoc_id
INNER JOIN task TEST_CASE ON ASSOC_REF.from_id = TEST_CASE.id
INNER JOIN task_type TEST_CASE_TRACKER ON TEST_CASE.type_id = TEST_CASE_TRACKER.id
INNER JOIN object TRACKER_ARTIFACT on TEST_CASE_TRACKER.id = TRACKER_ARTIFACT.id
WHERE LATEST_ASSOC.deleted IN (0)
AND LATEST_ASSOC.name = 'derived'
AND LATEST_ASSOC.description LIKE '{"TestStepReferenceMap"%'
AND ASSOC_REF.field_id = 0
AND ASSOC_REF.assoc_id IS NOT NULL
AND ASSOC_REF.from_type_id = 9
AND ASSOC_REF.to_type_id = 9
AND TEST_CASE_TRACKER.desc_id = 102
AND TRACKER_ARTIFACT.deleted IN (0, 1)
AND LATEST_ASSOC.description SIMILAR TO ' ( < step_id_regexp_from_the_first_query > ) ';
TEST_CASE_TRACKER.id as tracker_id,
TEST_CASE_TRACKER.proj_id as proj_id
FROM object ASSOC
INNER JOIN object_revision LATEST_ASSOC ON ASSOC.id = LATEST_ASSOC.object_id AND ASSOC.revision = LATEST_ASSOC.revision
INNER JOIN object_reference ASSOC_REF ON ASSOC.id = ASSOC_REF.assoc_id
INNER JOIN task TEST_CASE ON ASSOC_REF.from_id = TEST_CASE.id
INNER JOIN task_type TEST_CASE_TRACKER ON TEST_CASE.type_id = TEST_CASE_TRACKER.id
INNER JOIN object TRACKER_ARTIFACT on TEST_CASE_TRACKER.id = TRACKER_ARTIFACT.id
WHERE LATEST_ASSOC.deleted IN (0)
AND LATEST_ASSOC.name = 'derived'
AND LATEST_ASSOC.description LIKE '{"TestStepReferenceMap"%'
AND ASSOC_REF.field_id = 0
AND ASSOC_REF.assoc_id IS NOT NULL
AND ASSOC_REF.from_type_id = 9
AND ASSOC_REF.to_type_id = 9
AND TEST_CASE_TRACKER.desc_id = 102
AND TRACKER_ARTIFACT.deleted IN (0, 1)
AND LATEST_ASSOC.description SIMILAR TO ' ( < step_id_regexp_from_the_first_query > ) ';
• Query for MySQL database:
In MySQL, GROUP_CONCAT has a 1kb limit on it, so in case of big data, this limit should be increased. The following statement is only effective in the used session:
SET SESSION group_concat_max_len = 2048; -- e.g. set maximum length to 2 kilobytes in bytes
WITH TEST_STEP_TABLE AS (SELECT DISTINCT FIELD.reference_id, FIELD.parent_id
FROM task TEST_CASE
INNER JOIN task_type TEST_CASE_TRACKER ON TEST_CASE.type_id = TEST_CASE_TRACKER.id
INNER JOIN object FIELD ON TEST_CASE_TRACKER.id = FIELD.parent_id
INNER JOIN object_revision FIELD_REV
ON FIELD.id = FIELD_REV.object_id AND FIELD.revision = FIELD_REV.revision
WHERE TEST_CASE_TRACKER.desc_id = 102
AND FIELD.type_id = 25
AND FIELD_REV.name = 'Test Steps'),
TEST_TABLE_FIELDS AS (SELECT DISTINCT FIELD_REV.name, FIELD.reference_id, FIELD.parent_id
FROM TEST_STEP_TABLE
INNER JOIN object FIELD ON TEST_STEP_TABLE.parent_id = FIELD.parent_id
INNER JOIN object_revision FIELD_REV
ON FIELD.id = FIELD_REV.object_id AND FIELD.revision = FIELD_REV.revision
WHERE FIELD.reference_id BETWEEN TEST_STEP_TABLE.reference_id + 1 AND TEST_STEP_TABLE.reference_id + 999999
AND FIELD.deleted IN (0, 1))
SELECT GROUP_CONCAT(DISTINCT TFV.field_value SEPARATOR '|') as step_id_regexp
FROM task_field_value TFV
INNER JOIN task TEST_CASE ON TFV.task_id = TEST_CASE.id
INNER JOIN TEST_TABLE_FIELDS ON TEST_CASE.type_id = TEST_TABLE_FIELDS.parent_id
INNER JOIN TEST_STEP_TABLE ON TEST_CASE.type_id = TEST_STEP_TABLE.parent_id
WHERE TFV.label_id BETWEEN TEST_STEP_TABLE.reference_id + 1 AND TEST_STEP_TABLE.reference_id + 999999
AND FLOOR(MOD(MOD(TFV.label_id, 1000000), 100)) = FLOOR(MOD(MOD(TEST_TABLE_FIELDS.reference_id, 1000000), 100))
AND TEST_TABLE_FIELDS.name = 'Id'
AND CHAR_LENGTH(TFV.field_value) > 255;
FROM task TEST_CASE
INNER JOIN task_type TEST_CASE_TRACKER ON TEST_CASE.type_id = TEST_CASE_TRACKER.id
INNER JOIN object FIELD ON TEST_CASE_TRACKER.id = FIELD.parent_id
INNER JOIN object_revision FIELD_REV
ON FIELD.id = FIELD_REV.object_id AND FIELD.revision = FIELD_REV.revision
WHERE TEST_CASE_TRACKER.desc_id = 102
AND FIELD.type_id = 25
AND FIELD_REV.name = 'Test Steps'),
TEST_TABLE_FIELDS AS (SELECT DISTINCT FIELD_REV.name, FIELD.reference_id, FIELD.parent_id
FROM TEST_STEP_TABLE
INNER JOIN object FIELD ON TEST_STEP_TABLE.parent_id = FIELD.parent_id
INNER JOIN object_revision FIELD_REV
ON FIELD.id = FIELD_REV.object_id AND FIELD.revision = FIELD_REV.revision
WHERE FIELD.reference_id BETWEEN TEST_STEP_TABLE.reference_id + 1 AND TEST_STEP_TABLE.reference_id + 999999
AND FIELD.deleted IN (0, 1))
SELECT GROUP_CONCAT(DISTINCT TFV.field_value SEPARATOR '|') as step_id_regexp
FROM task_field_value TFV
INNER JOIN task TEST_CASE ON TFV.task_id = TEST_CASE.id
INNER JOIN TEST_TABLE_FIELDS ON TEST_CASE.type_id = TEST_TABLE_FIELDS.parent_id
INNER JOIN TEST_STEP_TABLE ON TEST_CASE.type_id = TEST_STEP_TABLE.parent_id
WHERE TFV.label_id BETWEEN TEST_STEP_TABLE.reference_id + 1 AND TEST_STEP_TABLE.reference_id + 999999
AND FLOOR(MOD(MOD(TFV.label_id, 1000000), 100)) = FLOOR(MOD(MOD(TEST_TABLE_FIELDS.reference_id, 1000000), 100))
AND TEST_TABLE_FIELDS.name = 'Id'
AND CHAR_LENGTH(TFV.field_value) > 255;
SELECT TEST_CASE.id as test_case_id,
TEST_CASE_TRACKER.id as tracker_id,
TEST_CASE_TRACKER.proj_id as proj_id
FROM object ASSOC
INNER JOIN object_revision LATEST_ASSOC ON ASSOC.id = LATEST_ASSOC.object_id AND ASSOC.revision = LATEST_ASSOC.revision
INNER JOIN object_reference ASSOC_REF ON ASSOC.id = ASSOC_REF.assoc_id
INNER JOIN task TEST_CASE ON ASSOC_REF.from_id = TEST_CASE.id
INNER JOIN task_type TEST_CASE_TRACKER ON TEST_CASE.type_id = TEST_CASE_TRACKER.id
INNER JOIN object TRACKER_ARTIFACT on TEST_CASE_TRACKER.id = TRACKER_ARTIFACT.id
WHERE LATEST_ASSOC.deleted IN (0)
AND LATEST_ASSOC.name = 'derived'
AND LATEST_ASSOC.description LIKE '{"TestStepReferenceMap"%'
AND ASSOC_REF.field_id = 0
AND ASSOC_REF.assoc_id IS NOT NULL
AND ASSOC_REF.from_type_id = 9
AND ASSOC_REF.to_type_id = 9
AND TEST_CASE_TRACKER.desc_id = 102
AND TRACKER_ARTIFACT.deleted IN (0, 1)
AND LATEST_ASSOC.description REGEXP ' < step_id_regexp_from_the_first_query > ';
TEST_CASE_TRACKER.id as tracker_id,
TEST_CASE_TRACKER.proj_id as proj_id
FROM object ASSOC
INNER JOIN object_revision LATEST_ASSOC ON ASSOC.id = LATEST_ASSOC.object_id AND ASSOC.revision = LATEST_ASSOC.revision
INNER JOIN object_reference ASSOC_REF ON ASSOC.id = ASSOC_REF.assoc_id
INNER JOIN task TEST_CASE ON ASSOC_REF.from_id = TEST_CASE.id
INNER JOIN task_type TEST_CASE_TRACKER ON TEST_CASE.type_id = TEST_CASE_TRACKER.id
INNER JOIN object TRACKER_ARTIFACT on TEST_CASE_TRACKER.id = TRACKER_ARTIFACT.id
WHERE LATEST_ASSOC.deleted IN (0)
AND LATEST_ASSOC.name = 'derived'
AND LATEST_ASSOC.description LIKE '{"TestStepReferenceMap"%'
AND ASSOC_REF.field_id = 0
AND ASSOC_REF.assoc_id IS NOT NULL
AND ASSOC_REF.from_type_id = 9
AND ASSOC_REF.to_type_id = 9
AND TEST_CASE_TRACKER.desc_id = 102
AND TRACKER_ARTIFACT.deleted IN (0, 1)
AND LATEST_ASSOC.description REGEXP ' < step_id_regexp_from_the_first_query > ';
Possible Solutions:
• Solution 1 - In the test cases returned by the query, recreate the test steps with the test step reuses.
• Solution 2 - Rename the current Id column of Test Steps table in tracker configuration to something else than 'Id'. Then, recreate the test step reuses in the test cases returned by query. It might mean that some of the test step reuses are going to be lost in the process, however, these trackers were most probably not used for test step reuses since the Id column was used for other purposes. The Id column of Test Steps table is hidden by default, therefore, it is also an indicator of an invalid Id column if it is not hidden in the tracker configuration.
Guidelines for Fixing Invalid Transitive Test Step Reuses
Before Codebeamer 22.10-LTS, making transitive test step reuses were possible through Excel import. From this version such reuses are not possible, however, previously-created transitive test step reuses can still exist in Codebeamer.
Possible examples:
There are two test cases which have test step reuses between them.
Test Case 3 is referring three test steps from Test Case 2, which is not possible since those steps are already reused from Test Case 1.
In order to fix it, these steps from Test Case 3 must be deleted and reused from Test Case 1 manually.
Modifying items such as Test Case 3 is not possible until the invalid reuses are fixed.
It is also possible that Test Case 2 looks like the following:
The Test Case 3 does not have an error message, but the modification of Test Case 3 is also not permitted in this case until the transitive reuse is fixed.