Workaround found
Moin all together,
we have now found a workaround for us, with which you can rename the keys of the object schemas.
Why is this important?
As long as an object schema has an underscore in the key, neither the name of the schema can be changed, nor the object schema can be deleted (e.g. after an export and re-import under a new name).
How did we proceed?
1. Determine affected DB fields
Since it was not clear to us from the Insight DB documentation in which fields object schema keys can occur everywhere, we first pulled a DB dump and searched it for all affected keys. Since the DB file is huge, a suitable editor is recommended for this, e.g. UltraEdit Portable. With some regex in the search query, you can surf through the file and find the affected DB fields without having to look at every single occurrence in the database.
In our case, the object schema keys occurred in the following fields:
- AO_54307E_GOAL.JQL_QUERY (JQL in SLA targets, search key with and without ID)
- AO_54307E_SERIES.JQL (JQL in JSD reports, search key with and without ID)
- AO_54307E_VIEWPORTFIELDVALUE.VALUE (User defined Fields)
- AO_733371_EVENT_PARAMETER.VALUE
- AO_8542F1_IFJ_OBJ_ATTR_VAL.TEXT_VALUE (Object references of one object to another)
- AO_8542F1_IFJ_OBJ_SCHEMA.OBJECT_SCHEMA_KEY (Object schemas, search only key without ID)
- AO_8542F1_IFJ_ON_FILTER.CRITERIA (JQL in filter, search key with and without ID)
- AO_8542F1_IFJ_PRG_OT_RES.OBJECT_TYPE_RESULT (JSON, probably event log of object schema imports)
- AO_8542F1_IFJ_WIDGET.PARAMETERS (JQL, search key with and without ID)
- changeitem.OLDVALUE
- changeitem.NEWVALUE
- customfieldvalue.STRINGVALUE
- gadgetuserpreference.USERPREFVALUE (JQL in HTML, search key with and without ID)
- propertystring.propertyvalue
Since we certainly don't have every conceivable link and function in use with Insight, it is probably advisable in your case to search the DB for other occurrences of the affected keys in other DB fields. Potentially these keys could also appear in all possible JQL fields, but in our case I have only listed the ones that affect us.
In most of the above fields, it is an object ID, which means you could search for the key with a hyphen appended. In the deviating cases, I have labeled it. But I think you can also do a blanket search and replace for just the key, without the hyphen.
2. Build SQL statements for affected DB fields and object schema keys
For each of the determined DB fields we now have to build a SQL statement according to the following scheme (here at the example AO_54307E_GOAL.JQL_QUERY ):
UPDATE AO_54307E_GOAL
SET JQL_QUERY = REPLACE(JQL_QUERY, 'OLD_KEY1', 'NEWKEY1')
WHERE JQL_QUERY LIKE '%OLD_KEY1%'
In case you need to change the key of multiple object schemas, you can do it for all schemas in one statement per DB field by nesting it like this:
UPDATE AO_54307E_GOAL
SET JQL_QUERY = REPLACE(REPLACE(REPLACE(JQL_QUERY, 'OLD_KEY1', 'NEWKEY1'), 'OLD_KEY2', 'NEWKEY2'), 'OLD_KEY3', 'NEWKEY3')
WHERE JQL_QUERY LIKE '%OLD_KEY1%' OR JQL_QUERY LIKE '%OLD_KEY2%' OR JQL_QUERY LIKE '%OLD_KEY3%'
3. Renaming the object scheme keys in the stage
After finding all the tools, we came up with the following plan:
- Build SQL statements for all necessary fields
- Pull current DB dump of Stage system and perform Jira backup.
- Execute statements in Stage DB
- If immediate errors occur, directly reimport DB dump and troubleshoot
- Check the effects of the changes and observe them over several weeks
- If errors occur, readjust, restore Jira instance if necessary, repeat steps 1-6.
- Plan rollout of these changes for production.
4. Making the changes in the production system.
After importing the SQL statements in the stage, the system ran stable, all links worked. We observed this for a few weeks and then implemented it analogously for the production system.
I hope to be able to help you with this and I am looking forward to your feedback!
Contemplative Christmas
Johannes Nazarov
Translated with www.DeepL.com/Translator (free version)
Suggestion
Workaround found
Moin all together,
we have now found a workaround for us, with which you can rename the keys of the object schemas.
Why is this important?
As long as an object schema has an underscore in the key, neither the name of the schema can be changed, nor the object schema can be deleted (e.g. after an export and re-import under a new name).
How did we proceed?
1. Determine affected DB fields
Since it was not clear to us from the Insight DB documentation in which fields object schema keys can occur everywhere, we first pulled a DB dump and searched it for all affected keys. Since the DB file is huge, a suitable editor is recommended for this, e.g. UltraEdit Portable. With some regex in the search query, you can surf through the file and find the affected DB fields without having to look at every single occurrence in the database.
In our case, the object schema keys occurred in the following fields:
Since we certainly don't have every conceivable link and function in use with Insight, it is probably advisable in your case to search the DB for other occurrences of the affected keys in other DB fields. Potentially these keys could also appear in all possible JQL fields, but in our case I have only listed the ones that affect us.
In most of the above fields, it is an object ID, which means you could search for the key with a hyphen appended. In the deviating cases, I have labeled it. But I think you can also do a blanket search and replace for just the key, without the hyphen.
2. Build SQL statements for affected DB fields and object schema keys
For each of the determined DB fields we now have to build a SQL statement according to the following scheme (here at the example AO_54307E_GOAL.JQL_QUERY ):
In case you need to change the key of multiple object schemas, you can do it for all schemas in one statement per DB field by nesting it like this:
3. Renaming the object scheme keys in the stage
After finding all the tools, we came up with the following plan:
4. Making the changes in the production system.
After importing the SQL statements in the stage, the system ran stable, all links worked. We observed this for a few weeks and then implemented it analogously for the production system.
I hope to be able to help you with this and I am looking forward to your feedback!
Contemplative Christmas
Johannes Nazarov
Translated with www.DeepL.com/Translator (free version)