{"type":"doc","content":[{"type":"heading","attrs":{"level":2},"content":[{"text":"Hosting","type":"text"}]},{"type":"paragraph","content":[{"type":"status","attrs":{"color":"neutral","style":"bold","text":"DATA CENTER","localId":"594ab0ba-03aa-410c-85bb-56b2e84c338f"}}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Problems and solutions","type":"text"}]},{"type":"paragraph","content":[{"text":"Here are some scripts that address common Projectrak Data Center (DC) problems. These scripts should be executed in the Projectrak console, which can be accessed from the Manage Apps section and is only accessible to Jira Administrator.","type":"text"},{"type":"hardBreak"},{"text":"Click ","type":"text"},{"text":"here","type":"text","marks":[{"type":"link","attrs":{"href":"/wiki/spaces/PROJECTRAKDC/pages/1279206"}}]},{"text":" to learn more about the Projectrak Script Console console.","type":"text"}]},{"type":"panel","attrs":{"panelType":"warning"},"content":[{"type":"paragraph","content":[{"text":"Most of the scripts make irreversible changes that can impact the proper functioning of the application. They should be used with caution. ","type":"text"}]},{"type":"paragraph","content":[{"text":"Please contact our support team before using them.","type":"text"}]}]},{"type":"paragraph"},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"toc","parameters":{"macroParams":{"minLevel":{"value":"2"},"maxLevel":{"value":"5"},"include":{"value":"Scripts.*"},"outline":{"value":"false"},"type":{"value":"list"},"separator":{"value":"pipe"},"printable":{"value":"false"}},"macroMetadata":{"macroId":{"value":"ac07589c-b8f3-4ca1-814d-f9bbcb4b5fbd"},"schemaVersion":{"value":"1"},"title":"Table of Contents"}},"localId":"a076ee54-450b-4b10-9c3e-c12faa2c285f"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Scripts for values","type":"text"}]},{"type":"paragraph","content":[{"text":"Problem: ","type":"text","marks":[{"type":"strong"}]},{"text":"You need to delete values by their identifiers","type":"text"}]},{"type":"paragraph","content":[{"text":"Solution: ","type":"text","marks":[{"type":"strong"}]},{"text":"The introduced identifiers are being traversed, and if they exist, they are deleted from the database.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"import com.atlassian.jira.component.ComponentAccessor\nimport com.deiser.jira.profields.ao.v2.projectvalue.ProjectValueMgr\n \n \ndef VALUES_IDS = [x,x]\ndef projectValueMgr = ComponentAccessor.getOSGiComponentInstanceOfType(ProjectValueMgr.class)\ndef value = null\ndef println = \"\"\n \nVALUES_IDS.each {\n \n println += \"*** *** ***
\"\n println += \"Value ID: \" + it + \"
\"\n \n value = projectValueMgr.get(it)\n \n if(value != null){\n projectValueMgr.delete(value)\n println += \"Deleted!!
\"\n }\n else{\n println += \"Can not found this Value
\"\n }\n \n println += \"

\"\n}\n \n \nreturn println","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Problem: ","type":"text","marks":[{"type":"strong"}]},{"text":"Delete all values from Projectrak within a set of projects.","type":"text"}]},{"type":"paragraph","content":[{"text":"Solution:","type":"text","marks":[{"type":"strong"}]},{"text":" All the introduced project identifiers are being traversed, and for each of them, all their values are being iterated to subsequently delete them.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"import com.atlassian.jira.component.ComponentAccessor\nimport com.deiser.jira.profields.ao.v2.projectvalue.ProjectValueMgr\n \n \ndef PROJECTS_IDS = [x, x]\ndef projectValueMgr = ComponentAccessor.getOSGiComponentInstanceOfType(ProjectValueMgr.class)\ndef value = null\ndef println = \"\"\n \nPROJECTS_IDS.each {\n \n println += \"*** *** ***
\"\n println += \"Project Value for Project: \" + it + \"
\"\n \n value = projectValueMgr.getByProject((Long) it)\n \n if(value != null){\n projectValueMgr.delete(value)\n println += \"Deleted!!
\"\n }\n else{\n println += \"Can not found this Value
\"\n }\n \n println += \"

\"\n}\n \n \nreturn println","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Problem:","type":"text","marks":[{"type":"strong"}]},{"text":" The values in the columns where the value is stored in lowercase do not have the same equivalent value as those where the value is stored as raw data.","type":"text"}]},{"type":"paragraph","content":[{"text":"Solution: ","type":"text","marks":[{"type":"strong"}]},{"text":"All existing values are verified. Each field type has a different validation check.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"import com.atlassian.jira.component.ComponentAccessor\nimport com.deiser.jira.profields.ao.v2.projectvalue.ProjectValueMgr\nimport com.deiser.jira.profields.api.field.FieldService\nimport com.deiser.jira.profields.api.field.FieldType\n \n \ndef projectValueMgr = ComponentAccessor.getOSGiComponentInstanceOfType(ProjectValueMgr.class)\ndef fieldService = ComponentAccessor.getOSGiComponentInstanceOfType(FieldService.class)\ndef VALUES_IDS = projectValueMgr.get()\ndef println = \"*** Values different ***
\"\nVALUES_IDS.each {\n def change = false\n \n def field = fieldService.get(it.field.ID)\n \n if(!(field.type.isCalculatedType() || field.type.isEquals(\"number\") || field.type.isEquals(\"date\") ||field.type.isEquals(\"duration\"))) {\n \n if (it.value != null && !it.value.toLowerCase().equals(it.lowerValue)) {\n println += \"VALUE: \" + it.ID + \"
\"\n println += \"Changing LowerValue
\"\n it.setLowerValue(it.value.toLowerCase())\n change = true\n }\n \n if (it.display != null && !it.display.toLowerCase().equals(it.lowerDisplay)) {\n println += \"VALUE: \" + it.ID + \"
\"\n println += \"Changing LowerDisplay
\"\n it.setLowerDisplay(it.display.toLowerCase())\n change = true\n } else if (it.display == null && it.value != null) {\n println += \"VALUE: \" + it.ID + \"
\"\n println += \"Set Display equals to Value
\"\n it.setDisplay(it.value)\n it.setLowerDisplay(it.value.toLowerCase())\n change = true\n }\n \n if(change){\n projectValueMgr.save(it)\n println += \"Change it!
\"\n }\n }\n \n \n}\n \n \nreturn println","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Problem","type":"text","marks":[{"type":"strong"}]},{"text":": Information of values still exists in Projectrak for projects that no longer exist in Jira.","type":"text"}]},{"type":"paragraph","content":[{"text":"Solution","type":"text","marks":[{"type":"strong"}]},{"text":": All value records are being traversed, and those where the project does not exist are being deleted.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"import com.atlassian.jira.bc.project.ProjectService\nimport com.atlassian.jira.component.ComponentAccessor\nimport com.deiser.jira.profields.ao.v2.projectvalue.AOProjectValue\nimport com.deiser.jira.profields.ao.v2.projectvalue.ProjectValueMgr\n \n \ndef projectValueMgr = ComponentAccessor.getOSGiComponentInstanceOfType(ProjectValueMgr.class)\ndef projectManager = ComponentAccessor.getProjectManager()\ndef println = \"\"\n \ndef allProjectValues = projectValueMgr.get()\nif(allProjectValues != null){\n allProjectValues.each {\n \n def haveToDelete = it == null || projectManager.getProjectObj(it.getProjectId()) == null\n if(haveToDelete){\n \n println += \"*** *** ***
\"\n println += (it == null || it.getProjectId() == null) ? \"Project Value is empty\" : \"Project Value for Project: \" + it.getProjectId() + \"
\"\n \n projectValueMgr.delete(it)\n println += \"
\"\n }\n }\n \n}\n \nreturn println","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Problem","type":"text","marks":[{"type":"strong"}]},{"text":": There are projects with duplicate values for the same field.","type":"text"}]},{"type":"paragraph","content":[{"text":"Solution","type":"text","marks":[{"type":"strong"}]},{"text":": Traverse all values and delete the duplicates, leaving the oldest value in the database.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"\nimport com.atlassian.jira.component.ComponentAccessor\nimport com.deiser.jira.profields.ao.v2.projectvalue.AOProjectValue\nimport com.deiser.jira.profields.ao.v2.projectvalue.ProjectValueMgr\nimport com.deiser.jira.profields.api.configuration.ConfigurationService\nimport com.deiser.jira.profields.api.field.FieldService\nimport com.deiser.jira.profields.api.field.FieldType\nimport groovy.transform.Field\nimport org.apache.commons.collections.CollectionUtils\n \n@Field ProjectValueMgr projectValueMgr = ComponentAccessor.getOSGiComponentInstanceOfType(ProjectValueMgr.class)\n \n/* ***** execution ***** */\n@Field HashMap> allValuesByHash = new HashMap<>()\n@Field List println = [\"*** REMOVED ***
\"]\nremoveAllDuplicatedValues()\nreturn println\n \n/* ***** FUNCTIONS ***** */\ndef removeAllDuplicatedValues(){\n projectValueMgr.get().each{addInMap(it)}\n getAllDuplicatedValues().each {list -> removeDuplicated(list)}\n}\n \ndef getAllDuplicatedValues(){\n return allValuesByHash.entrySet().stream()\n .filter{it -> it.value.size() > 1}\n .map{it -> it.value}\n .collect()\n}\n \ndef addInMap(AOProjectValue value){\n String hash = getHash(value)\n List currentValues = allValuesByHash.remove(hash)\n currentValues = currentValues == null ? new ArrayList() : currentValues;\n currentValues.add(value)\n allValuesByHash.put(hash, currentValues)\n}\n \ndef getHash(AOProjectValue value){\n def fieldType = FieldType.getFieldTypeByKey(value.getField().getType())\n return fieldType.isMultiple() ? getHashForMultipleField(value) : getHashForSimpleField(value)\n}\n \ndef getHashForMultipleField(AOProjectValue value){\n return \"PROJECT(\"+ value.projectId +\"); FIELD_ID(\"+ value.field.ID +\"); VALUE(\"+ value.value +\"); DISPLAY(\"+ value.display +\"); PARENT_ID(\"+ value.parentId +\");\".toLowerCase()\n}\n \ndef getHashForSimpleField(AOProjectValue value){\n return \"PROJECT(\"+ value.projectId +\"); FIELD_ID(\"+ value.field.ID +\");\".toLowerCase()\n}\n \ndef getValueInString(AOProjectValue value){\n return \"
ID(\"+ value.ID +\"); PROJECT(\"+ value.projectId +\"); FIELD_ID(\"+ value.field.ID +\"); FIELD_TYPE(\"+ value.field.type +\"); VALUE(\"+ value.value +\"); DISPLAY(\"+ value.display +\"); PARENT-VALUE(\"+ value.parentValue +\") PARENT-ID(\"+ value.parentId +\"); CHILDREN(\"+ getNumberOfChildren(value) +\");\"\n}\n \ndef removeDuplicated(List duplicated){\n List withChildren = new ArrayList<>();\n List withoutChildren = new ArrayList<>();\n duplicated.sort{a,b -> (a.ID <=> b.ID) }\n duplicated.each{ fillByNumberOfChildren(it, withChildren, withoutChildren)}\n def listToRemove = getListToRemove(withChildren, withoutChildren)\n listToRemove.each{\n println.add(getValueInString(it))\n projectValueMgr.delete(it)\n }\n}\n \ndef fillByNumberOfChildren(AOProjectValue value, List withChildren, List withoutChildren){\n def numberOfChildren = getNumberOfChildren(value)\n if(numberOfChildren == 0) withoutChildren.add(value)\n else withChildren.add(value)\n}\n \ndef getNumberOfChildren(AOProjectValue value){\n def parent = projectValueMgr.getByParentCustom(value.value)\n return parent == null ? null : parent.size();\n}\n \ndef getListToRemove(List withChildren, List withoutChildren){\n if(withChildren.isEmpty()) return withoutChildren.subList(1, withoutChildren.size())\n if(withChildren.size() == 1) return withoutChildren\n List listToRemove = withChildren.size(1, withChildren.size())\n listToRemove.addAll(withoutChildren)\n return listToRemove\n}","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Scripts for layouts","type":"text"}]},{"type":"paragraph","content":[{"text":"Problem","type":"text","marks":[{"type":"strong"}]},{"text":": We have found non-existent fields in the structure of a layout.","type":"text"}]},{"type":"paragraph","content":[{"text":"Solution","type":"text","marks":[{"type":"strong"}]},{"text":": Traverse the structure of all layouts to check if their fields exist, and if not, we delete them.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"import com.atlassian.jira.component.ComponentAccessor\nimport com.deiser.jira.profields.ao.v2.viewitem.AOViewItem\nimport com.deiser.jira.profields.ao.v2.viewitem.ViewItemMgr\nimport com.deiser.jira.profields.api.field.FieldService\n\ndef viewItemMgr = ComponentAccessor.getOSGiComponentInstanceOfType(ViewItemMgr.class)\ndef fieldService = ComponentAccessor.getOSGiComponentInstanceOfType(FieldService.class)\ndef allViewItems = viewItemMgr.get()\n\ndef println = \"\"\n\nallViewItems.each {\n\n if (it.fieldId != null) {\n\n def field = fieldService.get(it.fieldId)\n if(field == null){\n println += \"Delete Field (\" + it.fieldId + \") in Layout (\" + it.layout.name + \")
\"\n viewItemMgr.delete(it)\n }\n }\n}\n\nprintln","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Problem","type":"text","marks":[{"type":"strong"}]},{"text":": Within a layout, there is duplicated information and orphaned structures.","type":"text"}]},{"type":"paragraph","content":[{"text":"Solution","type":"text","marks":[{"type":"strong"}]},{"text":": Traverse all the layouts and verify their internal structure. If they have duplicated or orphaned structures, we delete them.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"import com.atlassian.jira.component.ComponentAccessor\nimport com.deiser.jira.profields.ao.v2.layout.LayoutMgr\nimport com.deiser.jira.profields.ao.v2.viewitem.AOViewItem\nimport com.deiser.jira.profields.ao.v2.viewitem.ViewItemMgr\nimport com.deiser.jira.profields.api.layout.view.SectionView\nimport com.deiser.jira.profields.api.layout.view.ViewItem\nimport com.google.common.collect.Lists\nimport groovy.transform.Field\nimport org.apache.commons.collections.ListUtils\n\nimport java.util.stream.Collectors\n\n@Field ViewItemMgr viewItemMgr = ComponentAccessor.getOSGiComponentInstanceOfType(ViewItemMgr.class)\n@Field LayoutMgr layoutMgr = ComponentAccessor.getOSGiComponentInstanceOfType(LayoutMgr.class)\n@Field List println = []\n\nexecute()\n\n\n//EXECUTE\ndef execute(){\n def layouts = layoutMgr.get()\n layouts.each {\n iterateByLayout(it)\n }\n return println\n}\n\n//LAYOUTS\ndef iterateByLayout(layout){\n def sourceItems = viewItemMgr.getByLayout(layout)\n checkAndFix(layout, sourceItems)\n}\n\ndef checkAndFix(layout, sourceItems){\n //Get all structure\n def structure = getAllSections(sourceItems)\n addToPrint(layout, structure)\n\n //Remove all duplicated sections\n def duplicatedSections = removeAllDuplicatedSections(structure)\n addToPrintDeletedDuplicatedSections(duplicatedSections)\n\n //Remove all orphan\n def deletedOrphans = removeUntilThereAreNoOrphans()\n addToPrintDeletedOrphans(deletedOrphans)\n\n //Split\n addToPrintSplit()\n}\n\n//SECTIONS\ndef getAllSections(sourceItems){\n return sourceItems.stream()\n .filter{it -> isSection(it)}\n .map{it -> new ViewNode(item: it, children: getAllColumns(it, sourceItems), type: \"Section\")}\n .collect(Collectors.toList())\n}\n\nboolean isSection(viewItem){\n return viewItem.parentId == null\n}\n\nint compareSection(viewNode1, viewNode2){\n boolean comparative = viewNode1.item.name == viewNode2.item.name &&\n viewNode1.item.position == viewNode2.item.position &&\n ((viewNode1.children == null && viewNode2.children == null) ||\n (viewNode1.children.size() == viewNode2.children.size()))\n return comparative ? 0 : 1\n}\n\ndef removeAllDuplicatedSections(structure){\n def sectionsUniques = structure.toUnique{ sect1, sect2 -> compareSection(sect1, sect2) }\n def thereAreDuplicatedSections = structure.size() != sectionsUniques.size();\n if(thereAreDuplicatedSections){\n def repeatedSections = ListUtils.removeAll(structure, sectionsUniques)\n def repeatedViewItemSections = repeatedSections.stream().map { it -> it.item }.collect(Collectors.toList())\n\n this.viewItemMgr.delete(repeatedViewItemSections)\n return repeatedViewItemSections\n }\n return [];\n}\n\n\n\n//COLUMNS\ndef getAllColumns(section, sourceItems){\n return columns = sourceItems.stream()\n .filter{ it -> isColumn(section, it) }\n .map{it-> new ViewNode(item: it, children: getAllContainer(it, sourceItems), type: \"Column\")}\n .collect(Collectors.toList())\n .sort{ a -> a.item.position}\n}\n\nboolean isColumn(section, viewItem){\n return viewItem.parentId != null &&\n viewItem.parentId == section.ID &&\n viewItem.name == null &&\n viewItem.fieldId == null\n}\n\n\n\n//CONTAINERS\ndef getAllContainer(column, sourceItems){\n return sourceItems.stream()\n .filter{it -> isContainer(column, it)}\n .map{it -> new ViewNode(item: it, children: getAllFields(it, sourceItems), type: \"Container\")}\n .collect(Collectors.toList())\n .sort{ a -> a.item.position}\n}\n\nboolean isContainer(column, viewItem){\n return viewItem.parentId != null &&\n viewItem.parentId == column.ID &&\n viewItem.name\n}\n\n//FIELDS\ndef getAllFields(container, sourceItems){\n return sourceItems.stream()\n .filter{it -> isField(container, it)}\n .map{it -> new ViewNode(item: it, children: null, type: \"Field\")}\n .collect(Collectors.toList())\n .sort{ a -> a.item.position}\n}\n\ndef isField(container, viewItem){\n return viewItem.parentId != null &&\n viewItem.parentId == container.ID &&\n viewItem.getFieldId() != null\n}\n\n\nclass ViewNode {\n AOViewItem item\n List children;\n String type;\n\n def compare(ViewNode node){\n return item.name == node.item.name &&\n item.position == node.item.position &&\n item.fieldId == node.item.fieldId &&\n item.required == node.item.required &&\n item.layout.ID == node.item.layout.ID &&\n compareChildren(node.children)\n }\n\n def compareChildren(List nodeChildren){\n if(children == null && nodeChildren == null) return true;\n\n if(children.size() != nodeChildren.size()) return false;\n\n def compareResult = true\n children.eachWithIndex { ViewNode item, int index ->\n compareResult &= item.compare(nodeChildren.get(index))\n if(compareResult == false) return false\n }\n return compareResult\n }\n\n def printToString(){\n def itemString = item.position + \" ID[\"+ item.ID +\"] NAME[\"+ item.name +\"] PARENT-ID[\"+ item.parentId +\"] FIELD-ID[\"+ item.fieldId +\"]
\"\n def childrenString = this.children == null\n ? \"\"\n : this.children.stream()\n .map{it -> it.printToString()}\n .collect(Collectors.joining(\"\"))\n\n def tab = \"\"\n switch(type){\n case \"Section\":\n break;\n case \"Column\":\n tab += \" \"\n break;\n case \"Container\":\n tab += \" \" + \" \"\n break;\n case \"Field\":\n tab += \" \" + \" \" + \" \" + \" \"\n break;\n }\n\n return tab + type + \" --> \" + itemString + childrenString\n }\n}\n\n//REMOVE ORPHAN\ndef removeUntilThereAreNoOrphans(){\n def hasOrphan = true\n def printlnToOrphans = []\n\n while(hasOrphan){\n hasOrphan = removeOrphan(printlnToOrphans)\n }\n\n return printlnToOrphans\n}\n\ndef removeOrphan(println){\n def hasRemoved = false\n viewItemMgr.get().each {\n def parentId = it.getParentId()\n if(parentId != null && parentId >= 0){\n if(viewItemMgr.get(parentId) == null){\n println.add( \"ID: \"+ it.ID +\", Name: \"+ it.name +\" / \")\n viewItemMgr.delete(it)\n hasRemoved = true\n }\n }\n }\n return hasRemoved\n}\n\n\n//PRINTS\ndef addToPrint(layout, structure){\n println.add(\"Layout (\"+ layout.ID +\") \"+ layout.name +\"
Structure:
\")\n println.add(structure.stream().map{it -> it.printToString()}.collect(Collectors.toList()))\n}\n\ndef addToPrintDeletedDuplicatedSections(duplicatedSections){\n if(duplicatedSections){\n def prefix = \"
Deleted Sections:\";\n def duplicatedString = duplicatedSections.stream().map{it -> \"ID: \"+ it.ID +\", Name: \"+ it.name +\" / \"}.collect(Collectors.joining(\" / \"))\n println.add(prefix + duplicatedString + \"
\")\n }\n}\n\ndef addToPrintSplit(){\n println.add(\" ------------ ------------ ------------ ------------ ------------ ------------
\")\n}\n\ndef addToPrintDeletedOrphans(deletedOrphans){\n if(!deletedOrphans.isEmpty()){\n println.add(\"Orphans Deleted: \" + deletedOrphans + \"
\")\n }\n}","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Problem","type":"text","marks":[{"type":"strong"}]},{"text":": We have found duplicate associations between layouts and projects.","type":"text"}]},{"type":"paragraph","content":[{"text":"Solution","type":"text","marks":[{"type":"strong"}]},{"text":": Traverse all existing associations and remove the duplicates.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"import com.atlassian.jira.component.ComponentAccessor\nimport com.deiser.jira.profields.ao.v2.projectlayout.AOProjectLayout\nimport com.deiser.jira.profields.ao.v2.projectlayout.ProjectLayoutMgr\n\n\ndef projectLayoutMgr = ComponentAccessor.getOSGiComponentInstanceOfType(ProjectLayoutMgr.class)\n\ndef store = [:]\ndef listToDelete = []\n\n\ndef allProjectLayout = projectLayoutMgr.get()\nallProjectLayout.sort(true){a,b -> a.ID <=> b.ID}\nif(allProjectLayout != null && allProjectLayout.size() > 0){\n allProjectLayout.each {\n\n def key = \"LAYOUT: \" + it.layout.ID + \"; PROJECT: \" + it.projectId + \";\"\n def value = store.get(key)\n if(value == null){\n store.put(key, it)\n }\n else{\n listToDelete.add(it)\n }\n }\n}\n\n\ndef println = \"\"\nlistToDelete.each {\n println += \"ID: \"+ it.ID +\"LAYOUT: \" + it.layout.ID + \"; PROJECT: \" + it.projectId + \";\"\n projectLayoutMgr.delete(it)\n println += \"Deleted!
\"\n}\nprintln","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Problem","type":"text","marks":[{"type":"strong"}]},{"text":": We have found associations of layouts with non-existent projects.","type":"text"}]},{"type":"paragraph","content":[{"text":"Solution","type":"text","marks":[{"type":"strong"}]},{"text":": Traverse all associations and delete those that are associated with non-existent projects.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"import com.atlassian.jira.component.ComponentAccessor\nimport com.deiser.jira.profields.ao.v2.projectlayout.AOProjectLayout\nimport com.deiser.jira.profields.ao.v2.projectlayout.ProjectLayoutMgr\n\n\ndef projectLayoutMgr = ComponentAccessor.getOSGiComponentInstanceOfType(ProjectLayoutMgr.class)\ndef projectManager = ComponentAccessor.getProjectManager()\ndef println = \"\"\n\ndef allProjectLayout = projectLayoutMgr.get()\nif(allProjectLayout != null && allProjectLayout.length > 0){\n allProjectLayout.each {\n def haveToDelete = it == null || projectManager.getProjectObj(it.getProjectId()) == null\n if(haveToDelete){\n\n println += \"
\"\n println += (it == null || it.getProjectId() == null) ? \"Layout-Project is empty. \" : \"Layout-Project for Project: \" + it.getProjectId() + \" \"\n\n projectLayoutMgr.delete(it)\n println += \"Deleted!!\"\n }\n }\n}\n\nreturn println","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Scripts for items of list fields","type":"text"}]},{"type":"paragraph","content":[{"text":"Problem","type":"text","marks":[{"type":"strong"}]},{"text":": We are experiencing issues with the items in the list fields, but we are uncertain about the root cause. We need to identify where the problem originates.","type":"text"}]},{"type":"paragraph","content":[{"text":"Solution","type":"text","marks":[{"type":"strong"}]},{"text":": Display the information of the problematic items.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"import com.atlassian.jira.component.ComponentAccessor\nimport com.deiser.jira.profields.ao.v2.field.FieldMgr\nimport com.deiser.jira.profields.ao.v2.listitem.AOListItem\nimport com.deiser.jira.profields.ao.v2.listitem.ListItemMgr\nimport com.deiser.jira.profields.ao.v2.listitem.ListItemType\nimport groovy.transform.Field;\n\n@Field ListItemMgr listItemMgr = ComponentAccessor.getOSGiComponentInstanceOfType(ListItemMgr.class)\n@Field FieldMgr fieldMgr = ComponentAccessor.getOSGiComponentInstanceOfType(FieldMgr.class)\n\n\n\n\nString getMessageError(AOListItem listItem){\n String error = \"\"\n error += getValidField(listItem)\n error += getValidType(listItem)\n error += getValidCustomId(listItem)\n error += getValidData(listItem)\n error += getValidOrder(listItem)\n return error\n}\n\nString getValidField(AOListItem listItem){\n String error = \"\"\n error += listItem.getFieldId() == null ? \"FieldId NULL; \" : \"\"\n error += (error == \"\") && listItem.getFieldId() <= 0 ? \"FieldId-0; \" : \"\"\n error += (error == \"\") && fieldMgr.get(listItem.getFieldId()) == null ? \"Field Not Exist; \" : \"\"\n return error\n}\n\nString getValidType(AOListItem listItem){\n String error = \"\"\n error += listItem.getListItemType() == null ? \"Type NULL; \" : \"\"\n error += (error == \"\") && !ListItemType.isValidKey(listItem.getListItemType()) ? \"Type Not Valid Value; \" : \"\"\n return error\n}\n\nString getValidCustomId(AOListItem listItem){\n String error = \"\"\n error += listItem.getCustomId() == null ? \"CustomId NULL\" : \"\"\n error += (error == \"\") && listItem.getCustomId() == \"\" ? \"CustomId is EMPTY;\" : \"\"\n return error\n}\n\nString getValidData(AOListItem listItem){\n String error = \"\"\n error += listItem.getData() == null ? \"Data NULL; \" : \"\"\n error += (error == \"\") && listItem.getData() == \"\" ? \"Data is EMPTY;\" : \"\"\n return error\n}\n\nString getValidOrder(AOListItem listItem){\n String error = \"\"\n error += listItem.getOrder() == null ? \"Order NULL; \" : \"\"\n error += (error == \"\") && listItem.getOrder() <= 0 ? \"Order-0\" : \"\"\n return error\n}\n\n\n\ndef print = \"\"\nlistItemMgr.get().each {\n def error = getMessageError(it)\n if(error != \"\"){\n print += \"ID \" + it.ID + \": \" + error + \"
\"\n }\n}\nreturn \"List Items With ERRORS:
\" + print","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Problem","type":"text","marks":[{"type":"strong"}]},{"text":": There are issues with the sequential order of items in list fields. They maintain their order, but it is not sequential.","type":"text"}]},{"type":"paragraph","content":[{"text":"Solution","type":"text","marks":[{"type":"strong"}]},{"text":": Establish a new sequential order through the script.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"import com.atlassian.jira.component.ComponentAccessor\nimport com.deiser.jira.profields.ao.v2.field.FieldMgr\nimport com.deiser.jira.profields.ao.v2.listitem.AOListItem\nimport com.deiser.jira.profields.ao.v2.listitem.ListItemMgr\nimport com.deiser.jira.profields.api.configuration.ConfigurationService\nimport groovy.transform.Field\n@Field ListItemMgr listItemMgr = ComponentAccessor.getOSGiComponentInstanceOfType(ListItemMgr.class)\n@Field boolean UPDATE = true\nmain()\n/* **** METHODS **** */\ndef main(){\n def itemsByFieldID = getAllItemsByFieldID()\n def itemsToChange = checkOrder(itemsByFieldID)\n def println = printItems(itemsToChange)\n if(UPDATE){\n listItemMgr.save(itemsToChange)\n println.add(\" UPDATED ALL
\")\n }\n return println\n}\ndef getAllItemsByFieldID(){\n def fieldWithItems = [:]\n listItemMgr.get().each {\n def values = fieldWithItems[it.fieldId]\n if(values == null){\n fieldWithItems[it.fieldId] = [it]\n }\n else{\n values.add(it)\n fieldWithItems[it.fieldId] = values\n }\n }\n return fieldWithItems\n}\ndef checkOrder(HashMap map){\n def itemsToChange = []\n def keys = map.keySet()\n keys.each{\n def values = map[it]\n orderItems(values, itemsToChange)\n }\n return itemsToChange\n}\ndef printItems(List items){\n def println = [\" *** ITEMS TO UPDATE ***
\"]\n items.each { println.add(itemToString(it))}\n return println\n}\ndef orderItems(List items, List itemsToChange){\n def sortedItems = items.sort{ a, b -> a.order <=> b.order}\n sortedItems.eachWithIndex { AOListItem entry, int i ->\n if(entry.order != (i +1)){\n entry.order = i + 1\n itemsToChange.add(entry)\n }\n }\n}\nString itemToString(AOListItem item){\n return String.format(\"ID(%d); ORDER(%d) FIELD_ID(%d); TYPE(%s);
\",\n item.ID,\n item.order,\n item.fieldId,\n item.listItemType\n )\n}","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Scripts for connections","type":"text"}]},{"type":"paragraph","content":[{"text":"Problem","type":"text","marks":[{"type":"strong"}]},{"text":": Establishing a connection to a pre-existing database is required for certain fields of Projectrak's external list.","type":"text"}]},{"type":"paragraph","content":[{"text":"Solution","type":"text","marks":[{"type":"strong"}]},{"text":": Through a set of IDs of external list fields, they are traversed to establish the selected connection using their respective ID.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"\nimport com.atlassian.jira.component.ComponentAccessor\nimport com.deiser.jira.profields.ao.v2.field.AOField\nimport com.deiser.jira.profields.ao.v2.field.FieldMgr\nimport groovy.json.JsonOutput\nimport groovy.json.JsonSlurper\n \ndef fieldMgr = ComponentAccessor.getOSGiComponentInstanceOfType(FieldMgr.class)\n \ndef FIELD_IDs = [X, X];\ndef CONNECTION_ID = Y\n \ndef println = \"Start
\"\n \n \n \nFIELD_IDs.each {\n \n def fieldList = fieldMgr.getById(it)\n \n if(fieldList.length == 1){\n \n def field = fieldList[0]\n def data = field.getData()\n \n println += \" Field: Id(\" + field.ID + \") Name(\" + field.name + \") Data(\" + field.data + \")
\"\n \n def jsonSlurper = new JsonSlurper()\n def objectJson = jsonSlurper.parseText(data)\n \n objectJson.connectionId = CONNECTION_ID\n def newData = JsonOutput.toJson(objectJson)\n \n field.setData(newData)\n \n println += \" Before Save: Id(\" + field.ID + \") Name(\" + field.name + \") Data(\" + field.data + \")
\"\n \n fieldMgr.save(field)\n \n println += \" Saved!
\"\n }\n}\n \nprintln","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Scripts for subscriptions","type":"text"}]},{"type":"paragraph","content":[{"text":"Problem","type":"text","marks":[{"type":"strong"}]},{"text":": Know which subscriptions the instance has and who their owner is.","type":"text"}]},{"type":"paragraph","content":[{"text":"Solution","type":"text","marks":[{"type":"strong"}]},{"text":": Retrieve all subscriptions stored in Projectrak along with their respective users.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"import com.deiser.jira.profields.api.subscription.SubscriptionService\nimport com.atlassian.jira.component.ComponentAccessor\n\nComponentAccessor.getOSGiComponentInstanceOfType(SubscriptionService.class)\n .get()\n .collect{\" - ${it.user}: ${it.name}\"}\n .join(\"
\")","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Scripts for mapping to Jira custom fields","type":"text"}]},{"type":"paragraph","content":[{"text":"Problem","type":"text","marks":[{"type":"strong"}]},{"text":": We have encountered Jira custom fields that have lost their association with Projectrak fields.","type":"text"}]},{"type":"paragraph","content":[{"text":"Solution","type":"text","marks":[{"type":"strong"}]},{"text":": All Jira custom fields that belong to Projectrak are traversed to verify if they are related to a Projectrak field. If not, the Jira custom field is deleted.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"import com.atlassian.jira.component.ComponentAccessor\nimport com.atlassian.jira.config.managedconfiguration.ManagedConfigurationItem\nimport com.atlassian.jira.config.managedconfiguration.ManagedConfigurationItemService\nimport com.atlassian.jira.issue.CustomFieldManager\nimport com.atlassian.jira.issue.fields.CustomField\nimport com.deiser.jira.profields.ao.v2.field.FieldMgr\n \nCustomFieldManager customFieldManager = ComponentAccessor.getCustomFieldManager()\nManagedConfigurationItemService managedConfigurationItemService = ComponentAccessor.getComponent(ManagedConfigurationItemService.class);\n \nFieldMgr fieldMgr = ComponentAccessor.getOSGiComponentInstanceOfType(FieldMgr.class)\n \ndef objects = customFieldManager.getCustomFieldObjects()\nprint = \"*** CustomFields of Profields ***
\"\n \ndef each = objects.each {\n def customType = it.getCustomFieldType().toString()\n if (customType.contains(\"com.deiser.jira.profields\")) {\n \n print += \"CustomField ID: \" + it.idAsLong + \"
\"\n print += \"Type: \" + it.getCustomFieldType().toString() + \"
\"\n print += \"Name: \" + it.name + \"
\"\n \n def field = fieldMgr.getFieldByCustomField(it.idAsLong)\n if(field != null){\n print += \"Oh Yeah! It's correct!
\"\n }\n else{\n print += \"This customfield doesn't exist in Profields
\"\n \n ManagedConfigurationItem managedCreatedCustomField = managedConfigurationItemService.getManagedCustomField(it);\n managedConfigurationItemService.removeManagedConfigurationItem(managedCreatedCustomField)\n customFieldManager.removeCustomField(it)\n \n print += \"Deleted!!
\"\n }\n \n print += \"

\"\n }\n}\n \nprint","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Problem","type":"text","marks":[{"type":"strong"}]},{"text":": The Jira custom fields related to Projectrak cannot be edited or deleted as they are currently locked.","type":"text"}]},{"type":"paragraph","content":[{"text":"Solution","type":"text","marks":[{"type":"strong"}]},{"text":": All Jira custom fields that belong to Projectrak are traversed to unlock.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"import com.atlassian.jira.component.ComponentAccessor\nimport com.atlassian.jira.config.managedconfiguration.ConfigurationItemAccessLevel\nimport com.atlassian.jira.config.managedconfiguration.ManagedConfigurationItem\nimport com.atlassian.jira.config.managedconfiguration.ManagedConfigurationItemBuilder\nimport com.atlassian.jira.config.managedconfiguration.ManagedConfigurationItemService\nimport com.atlassian.jira.issue.CustomFieldManager\nimport com.atlassian.jira.issue.fields.CustomField\nimport com.deiser.jira.profields.ao.v2.field.FieldMgr\n \n \nString PLUGIN_NAME = \"com.deiser.jira.profields\";\n \nCustomFieldManager customFieldManager = ComponentAccessor.getCustomFieldManager()\nManagedConfigurationItemService managedConfigurationItemService = ComponentAccessor.getComponent(ManagedConfigurationItemService.class);\n \ndef objects = customFieldManager.getCustomFieldObjects()\nprint = \"*** CustomFields of Profields ***
\"\n \nobjects.each {\n def customType = it.getCustomFieldType().toString()\n if (customType.contains(PLUGIN_NAME)) {\n \n print += \"CustomField ID: \" + it.idAsLong + \"
\"\n print += \"Type: \" + it.getCustomFieldType().toString() + \"
\"\n print += \"Name: \" + it.name + \"
\"\n \n ManagedConfigurationItem managedCreatedCustomField = managedConfigurationItemService.getManagedCustomField(it);\n ManagedConfigurationItemBuilder managedConfigurationItemBuilder = managedCreatedCustomField.newBuilder();\n ManagedConfigurationItem managedConfigurationItem = managedConfigurationItemBuilder\n .setManaged(false)\n .setConfigurationItemAccessLevel(ConfigurationItemAccessLevel.ADMIN)\n .build();\n \n managedConfigurationItemService.updateManagedConfigurationItem(managedConfigurationItem);\n print += 'Unlock CustomField

'\n }\n}\n \nprint","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Problem","type":"text","marks":[{"type":"strong"}]},{"text":": We want to regenerate the mapping of all Projectrak fields with the Jira custom fields.","type":"text"}]},{"type":"paragraph","content":[{"text":"Solution","type":"text","marks":[{"type":"strong"}]},{"text":": Traverse the Projectrak fields that have mapping enabled to remove the current Jira custom field and generate a new one.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"import com.atlassian.jira.component.ComponentAccessor\nimport com.atlassian.jira.issue.CustomFieldManager\nimport com.deiser.jira.profields.ao.v2.field.FieldMgr\nimport com.deiser.jira.profields.api.field.FieldService\n \nCustomFieldManager customFieldManager = ComponentAccessor.getCustomFieldManager()\nFieldMgr fieldMgr = ComponentAccessor.getOSGiComponentInstanceOfType(FieldMgr.class)\nFieldService fieldService = ComponentAccessor.getOSGiComponentInstanceOfType(FieldService.class)\n \ndef println = \"Delete ID CustomField:
\"\n \nfieldMgr.get().each {\n def customFieldId = it.getCustomFieldId()\n if(customFieldId != null){\n \n def customField = customFieldManager.getCustomFieldObject(customFieldId)\n if(customField == null){\n println += \"customField(\" + it.customFieldId + \") in the field(\" + it.ID + \"): \" + it.name + \" \"\n it.customFieldId = null\n fieldMgr.save(it)\n \n def field = fieldService.get(it.ID)\n field.customField = true\n fieldService.update(field)\n println += \"-> setted new custom field;
\"\n }\n \n }\n}\n \nprintln","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Scripts for gadgets","type":"text"}]},{"type":"paragraph","content":[{"text":"Problem","type":"text","marks":[{"type":"strong"}]},{"text":": There are issues with data migrations related to the gadgets, and we want to identify the problematic gadget.","type":"text"}]},{"type":"paragraph","content":[{"text":"Solution","type":"text","marks":[{"type":"strong"}]},{"text":": All users of the instance are being traversed to check the Projectrak gadgets. Both correct and erroneous gadgets are displayed on the screen.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"import org.apache.commons.codec.binary.Hex\nimport java.util.stream.*\nimport com.atlassian.jira.component.ComponentAccessor\nimport com.atlassian.jira.portal.PortalPage\nimport com.atlassian.jira.portal.PortletConfiguration\nimport com.atlassian.jira.portal.PortletConfigurationManager\nimport com.atlassian.jira.user.ApplicationUser\nimport com.atlassian.jira.bc.portal.PortalPageService\nimport org.apache.commons.lang.StringUtils\n \ndef GADGET_ONEFIELD_XML = \"profields_gadget_onefield.xml\"\ndef GADGET_SUMMARY_XML = \"profields_gadget_summary.xml\"\ndef GADGET_TIMESHEET_XML = \"profields_gadget_timesheet.xml\"\ndef GADGET_TWOFIELDS_XML = \"profields_gadget_twofields.xml\"\n \ndef GADGETS_PROFIELDS = [GADGET_ONEFIELD_XML, GADGET_SUMMARY_XML, GADGET_TIMESHEET_XML, GADGET_TWOFIELDS_XML]\n \ndef userManager = ComponentAccessor.getUserManager()\ndef portalPageService = ComponentAccessor.getOSGiComponentInstanceOfType(PortalPageService.class)\ndef portletConfigurationManager = ComponentAccessor.getOSGiComponentInstanceOfType(PortletConfigurationManager.class)\n \ndef errorPortalPage = \"\"\ndef printValue = \"\"\ndef amountOfGadgetsMigrated = 0\n \ndef applicationUserCollection = userManager.getAllApplicationUsers()\nfor(ApplicationUser user: applicationUserCollection){\n \n def portalPageCollection = portalPageService.getOwnedPortalPages(user)\n for(PortalPage portalPage: portalPageCollection){\n \n try {\n \n def listPortletConfiguration = portletConfigurationManager.getByPortalPage(portalPage.getId())\n for (PortletConfiguration portletConfiguration : listPortletConfiguration) {\n \n def uri = portletConfiguration.getOpenSocialSpecUri().getOrNull()\n if (uri != null) {\n \n GADGETS_PROFIELDS.each {\n \n if (uri.toString().contains(it)) {\n printValue += \" ***************************
\"\n printValue += \" ID: \" + portalPage.id\n printValue += \"
\"\n printValue += \" UserName: \" + user.getName()\n printValue += \"
\"\n printValue += \" Dashboard: \" + portalPage.getName()\n printValue += \"
\"\n printValue += \"Configuration:\"\n printValue += \"
\"\n \n def filter = false\n portletConfiguration.getUserPrefs()\n .each {\n key, value ->\n \n if (key == \"overrideTitle\") {\n printValue += \"Gadget Name: ${value}\"\n printValue += \"
\"\n } else if (key == \"pqlPickerSource\") {\n if (value == \"filter\") {\n filter = true\n printValue += \"Has FILTER\"\n printValue += \"
\"\n } else if (value == \"pql\") {\n filter = false\n printValue += \"Has PQL\"\n printValue += \"
\"\n }\n } else if (key == \"pqlPickerData\") {\n if (filter) {\n printValue += \"ID FILTER: ${value}\"\n printValue += \"
\"\n } else {\n if (value != null) {\n value = new String(Hex.decodeHex(value.toCharArray()))\n }\n printValue += \"PQL: ${value}\"\n printValue += \"
\"\n }\n }\n \n }\n \n printValue += \" ***************************
\"\n amountOfGadgetsMigrated += 1\n }\n }\n \n }\n }\n }\n catch(Exception ex){\n printValue += \"
-- ERROR --> amount before: \" + amountOfGadgetsMigrated + \"
\"\n errorPortalPage += \"
**** **** ****
\"\n errorPortalPage += \"ID: \" + portalPage.id + \"/ Name: \" + portalPage.name + \"/ Owner: \" + portalPage.ownerUserName + \"
\"\n errorPortalPage += \"Before: \" + amountOfGadgetsMigrated + \"
\"\n errorPortalPage += \"ERROR: \" + ex.getMessage() + \"

\"\n }\n }\n}\n \nreturn printValue + \"
\" + errorPortalPage","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Script for Projectrak cache","type":"text"}]},{"type":"paragraph","content":[{"text":"Problem","type":"text","marks":[{"type":"strong"}]},{"text":": Need to enable or disable the Projectrak cache for testing purposes.","type":"text"}]},{"type":"paragraph","content":[{"text":"Solution","type":"text","marks":[{"type":"strong"}]},{"text":": Set the desired value in the Projectrak configuration related to the cache.","type":"text"}]},{"type":"paragraph","content":[{"text":"Enable cache:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"import com.atlassian.jira.component.ComponentAccessor\nimport com.deiser.jira.profields.api.configuration.ConfigurationService\nimport static com.atlassian.jira.component.ComponentAccessor.getOSGiComponentInstanceOfType\n\ndef configurationService = ComponentAccessor.getOSGiComponentInstanceOfType(ConfigurationService.class)\nconfigurationService.setCache(true)","type":"text"}]},{"type":"paragraph","content":[{"text":"Disable cache:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"import com.atlassian.jira.component.ComponentAccessor\nimport com.deiser.jira.profields.api.configuration.ConfigurationService\nimport static com.atlassian.jira.component.ComponentAccessor.getOSGiComponentInstanceOfType\n\ndef configurationService = ComponentAccessor.getOSGiComponentInstanceOfType(ConfigurationService.class)\nconfigurationService.setCache(false)","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Scripts for Projectrak database version","type":"text"}]},{"type":"paragraph","content":[{"text":"Problem","type":"text","marks":[{"type":"strong"}]},{"text":": We want to know the latest Projectrak migration registered in Jira that has been performed. Please note that there may be other types of migrations that are not recorded in Jira.","type":"text"}]},{"type":"paragraph","content":[{"text":"Solution","type":"text","marks":[{"type":"strong"}]},{"text":": Accessing the Jira tables where the migration code is stored.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"import com.atlassian.jira.component.ComponentAccessor\nimport org.apache.commons.lang3.exception.ExceptionUtils\nimport org.ofbiz.core.entity.ConnectionFactory\nimport org.ofbiz.core.entity.DelegatorInterface\n \nimport java.sql.ResultSet\nimport java.sql.Statement\n \ndef delegator = ComponentAccessor.getComponentOfType(DelegatorInterface.class)\ndef helperName = delegator.getGroupHelperName(\"default\")\ndef connection = ConnectionFactory.getConnection(helperName)\n \ndef result = \"\"\ntry {\n \n def stmt = connection.createStatement()\n \n ResultSet rs = stmt.executeQuery(\n \"select propertystring.id, propertystring.propertyvalue \" +\n \"from propertyentry, propertystring \" +\n \"where propertyentry.id = propertystring.id \" +\n \"and propertyentry.property_key = 'AO_00B950_#'\"\n );\n \n while (rs.next()) {\n def id = rs.getString(\"id\");\n def value = rs.getString(\"propertyvalue\");\n \n result += \"Version of Profields Database : \" + value + \"
\"\n }\n \n} catch (def e) {\n result += \"
Error:
\" + ExceptionUtils.getStackTrace(e)\n}\n \nreturn result","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Problem","type":"text","marks":[{"type":"strong"}]},{"text":": We want to reset the latest Projectrak migration registered in Jira that has been performed. Please note that there may be other types of migrations that are not recorded in Jira.","type":"text"}]},{"type":"paragraph","content":[{"text":"Solution","type":"text","marks":[{"type":"strong"}]},{"text":": Reset the record in the Jira tables where the migration code is stored.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"import com.atlassian.jira.component.ComponentAccessor\nimport org.apache.commons.lang3.exception.ExceptionUtils\nimport org.ofbiz.core.entity.ConnectionFactory\nimport org.ofbiz.core.entity.DelegatorInterface\n \nimport java.sql.ResultSet\nimport java.sql.Statement\n \ndef delegator = ComponentAccessor.getComponentOfType(DelegatorInterface.class)\ndef helperName = delegator.getGroupHelperName(\"default\")\ndef connection = ConnectionFactory.getConnection(helperName)\n \ndef result = \"\"\ntry {\n def NEW_VERSION = 26\n \n def stmt = connection.createStatement()\n def isOk = stmt.execute(\n \"update propertystring \" +\n \"set propertyvalue = ('\"+ NEW_VERSION +\"') \" +\n \"where id = ( \" +\n \"select propertystring.id \" +\n \" from propertyentry, propertystring \" +\n \" where propertyentry.id = propertystring.id \" +\n \" and propertyentry.property_key = 'AO_00B950_#'\" +\n \")\"\n )\n \n if(isOk != true){\n result += \" Update the database version of Profields to (\" + NEW_VERSION + \")\"\n }\n else{\n result += \" Error: Can not update the database version of Profields to (\" + NEW_VERSION + \")\"\n }\n \n} catch (def e) {\n result += \"
Error:
\" + ExceptionUtils.getStackTrace(e)\n}\n \nreturn result","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Scripts for Oracle database","type":"text"}]},{"type":"paragraph","content":[{"text":"Problem","type":"text","marks":[{"type":"strong"}]},{"text":": When making a change in the database properties, we encountered Clob-type columns in the Projectrak tables, which is causing errors in certain functionalities.","type":"text"}]},{"type":"paragraph","content":[{"text":"Solution","type":"text","marks":[{"type":"strong"}]},{"text":": Execute three scripts to modify the column type. This involves creating a new column for data dumping and subsequently deleting the erroneous column. No information is lost during the execution of the scripts.","type":"text"}]},{"type":"paragraph","content":[{"text":"Project Values (Table: AO_00B950_AOPROJECT_VALUE)","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"import com.atlassian.jira.component.ComponentAccessor\nimport org.apache.commons.lang3.StringUtils\nimport org.apache.commons.lang3.exception.ExceptionUtils\nimport org.ofbiz.core.entity.ConnectionFactory\nimport org.ofbiz.core.entity.DelegatorInterface\nimport java.sql.Statement\n \ndef delegator = ComponentAccessor.getComponentOfType(DelegatorInterface.class)\ndef helperName = delegator.getGroupHelperName(\"default\")\ndef connection = ConnectionFactory.getConnection(helperName)\n \ndef arrayColumns = [\"DISPLAY\", \"LOWER_DISPLAY\", \"VALUE\", \"LOWER_VALUE\", \"PARENT_VALUE\"] as String[]\n \ndef result = \"\"\ntry {\n def statement = connection.createStatement()\n \n arrayColumns.each {\n \n statement.execute(\"alter table AO_00B950_AOPROJECT_VALUE add \" + it +\"2 clob\")\n statement.execute(\"update AO_00B950_AOPROJECT_VALUE set \" + it +\"2 = \" + it)\n statement.execute(\"update AO_00B950_AOPROJECT_VALUE set \" + it +\" = null\")\n statement.execute(\"alter table AO_00B950_AOPROJECT_VALUE drop column \" + it)\n \n statement.execute(\"alter table AO_00B950_AOPROJECT_VALUE add \" + it +\" varchar2(4000)\")\n statement.execute(\"update AO_00B950_AOPROJECT_VALUE set \" + it +\" = \" + it +\"2\")\n statement.execute(\"alter table AO_00B950_AOPROJECT_VALUE drop column \" + it +\"2\")\n \n result += \"Completed \" + it + \"!
\"\n \n }\n \n \n} catch (def e) {\n result += \"
Error:
\" + ExceptionUtils.getStackTrace(e)\n}\n \nreturn result","type":"text"}]},{"type":"paragraph","content":[{"text":"Historical (Table: AO_00B950_AOHISTORICAL_FIELD)","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"import com.atlassian.jira.component.ComponentAccessor\nimport org.apache.commons.lang3.StringUtils\nimport org.apache.commons.lang3.exception.ExceptionUtils\nimport org.ofbiz.core.entity.ConnectionFactory\nimport org.ofbiz.core.entity.DelegatorInterface\nimport java.sql.Statement\n \ndef delegator = ComponentAccessor.getComponentOfType(DelegatorInterface.class)\ndef helperName = delegator.getGroupHelperName(\"default\")\ndef connection = ConnectionFactory.getConnection(helperName)\n \ndef arrayColumns = [\"NEW_VALUE\", \"OLD_VALUE\", \"NEW_DISPLAY\", \"OLD_DISPLAY\"] as String[]\n \n \ndef result = \"\"\ntry {\n def statement = connection.createStatement()\n \n arrayColumns.each {\n \n statement.execute(\"alter table AO_00B950_AOHISTORICAL_FIELD add \" + it +\"2 clob\")\n statement.execute(\"update AO_00B950_AOHISTORICAL_FIELD set \" + it +\"2 = \" + it)\n statement.execute(\"update AO_00B950_AOHISTORICAL_FIELD set \" + it +\" = null\")\n statement.execute(\"alter table AO_00B950_AOHISTORICAL_FIELD drop column \" + it)\n \n statement.execute(\"alter table AO_00B950_AOHISTORICAL_FIELD add \" + it +\" varchar2(4000)\")\n statement.execute(\"update AO_00B950_AOHISTORICAL_FIELD set \" + it +\" = \" + it +\"2\")\n statement.execute(\"alter table AO_00B950_AOHISTORICAL_FIELD drop column \" + it +\"2\")\n \n result += \"Completed \" + it + \"!
\"\n \n }\n \n \n} catch (def e) {\n result += \"
Error:
\" + ExceptionUtils.getStackTrace(e)\n}\n \nreturn result","type":"text"}]},{"type":"paragraph","content":[{"text":"List items (Table: AO_00B950_AOLIST_ITEM)","type":"text"}]},{"type":"codeBlock","attrs":{"language":"groovy"},"content":[{"text":"import com.atlassian.jira.component.ComponentAccessor\nimport org.apache.commons.lang3.StringUtils\nimport org.apache.commons.lang3.exception.ExceptionUtils\nimport org.ofbiz.core.entity.ConnectionFactory\nimport org.ofbiz.core.entity.DelegatorInterface\nimport java.sql.Statement\n \ndef delegator = ComponentAccessor.getComponentOfType(DelegatorInterface.class)\ndef helperName = delegator.getGroupHelperName(\"default\")\ndef connection = ConnectionFactory.getConnection(helperName)\n \ndef arrayColumns = [\"DATA\", \"LOWER_DATA\"] as String[]\n \n \ndef result = \"\"\ntry {\n def statement = connection.createStatement()\n \n statement.execute(\"alter table AO_00B950_AOLIST_ITEM modify DATA null\")\n \n arrayColumns.each {\n \n statement.execute(\"alter table AO_00B950_AOLIST_ITEM add \" + it +\"2 clob\")\n statement.execute(\"update AO_00B950_AOLIST_ITEM set \" + it +\"2 = \" + it)\n statement.execute(\"update AO_00B950_AOLIST_ITEM set \" + it +\" = null\")\n statement.execute(\"alter table AO_00B950_AOLIST_ITEM drop column \" + it)\n \n statement.execute(\"alter table AO_00B950_AOLIST_ITEM add \" + it +\" varchar2(4000)\")\n statement.execute(\"update AO_00B950_AOLIST_ITEM set \" + it +\" = \" + it +\"2\")\n statement.execute(\"alter table AO_00B950_AOLIST_ITEM drop column \" + it +\"2\")\n \n result += \"Completed \" + it + \"!
\"\n \n }\n \n statement.execute(\"alter table AO_00B950_AOLIST_ITEM modify DATA not null\")\n \n} catch (def e) {\n result += \"
Error:
\" + ExceptionUtils.getStackTrace(e)\n}\n \nreturn result","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":2},"content":[{"text":"Related articles","type":"text"}]},{"type":"paragraph","content":[{"type":"placeholder","attrs":{"text":"Related articles appear here based on the labels you select. Click to edit the macro and add or change labels."}}]},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"contentbylabel","parameters":{"macroParams":{"showLabels":{"value":"false"},"max":{"value":"5"},"spaces":{"value":"com.atlassian.confluence.content.render.xhtml.model.resource.identifiers.SpaceResourceIdentifier@140ea"},"showSpace":{"value":"false"},"sort":{"value":"modified"},"reverse":{"value":"true"},"type":{"value":"page"},"cql":{"value":"label in ( \"script\" , \"profields\" , \"scriptrunner\" , \"exporter\" ) and type = \"page\" and space = \"SKB\""},"labels":{"value":"profields script scriptrunner"}},"macroMetadata":{"macroId":{"value":"639d9282-5ee7-444a-afff-16104f725629"},"schemaVersion":{"value":"4"},"title":"Content by Label"}},"localId":"27644793-b8cf-445c-ad7a-2ec148fc34c9"}},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"bodiedExtension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"details","parameters":{"macroParams":{"hidden":{"value":"true"}},"macroMetadata":{"macroId":{"value":"d39d3764-f8d2-4e1f-9848-04be1664616c"},"schemaVersion":{"value":"1"},"title":"Page Properties"}},"localId":"6007bf77-436c-40dd-b364-eb98cb78ac1c"},"content":[{"type":"table","attrs":{"layout":"default","localId":"105058f8-92df-4c6d-b710-0a14366cd26c"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[509.0]},"content":[{"type":"paragraph","content":[{"text":"Related issues","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[217.0]},"content":[{"type":"paragraph","content":[{"type":"hardBreak"}]}]}]}]}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"paragraph","content":[{"type":"hardBreak"}]}],"version":1}

Browser not supported