Storage & Performance
Introduction
Since app version 2.0.0 the app's data storage is optimized for Jira Data Center to work in a clustered environment. We make use of Scheduled Jobs, Caches and Database Search Tables to achieve best performance in a clustered environment during high load and with a lot of data. Data is stored in two different ways:
Maintenance Data such as internal app settings are stored via PluginSettingsFactory.
Permissions and search tables are stored via ActiveObjects in database tables.
Storing Lightweight App Data
App settings are stored via the Shared Access Layer (PluginSettingsFactory) which is a Jira API providing simple save and load methods for storing small chunks of data. This is useful for internal App Settings. This data should usually not be accessed via a database query tool.
DataMigrationTask Information stores a date and the migration-task id per migration task.
GlobalSettings stores a boolean for the Navigation Links Setting.
The data is stored by Jira in a table called propertyentry
with foreignkey references to the table propertystring
. The data can be accessed by using property keys.
DataMigrationTask Information
jiracustomfieldeditorplugin.<migrationTaskId>.migrationtasks
DataMigrationTask Information stores a date and the migration-task id per migration task. The format is a timestamp in seconds. If the migrationtask did not run, there is no entry.
Example entries look like this:
> SELECT * FROM "propertyentry" WHERE property_key = "jiracustomfieldeditorplugin.200001.migrationtasks" OR property_key = "jiracustomfieldeditorplugin.121001.migrationtasks" | id | entity_name | entity_id | property_key | propertytype | |-------|-----------------|-----------|----------------------------------------------------|--------------| | 10521 | jira.properties | 1 | jiracustomfieldeditorplugin.200001.migrationtasks | 5 | | 10515 | jira.properties | 1 | jiracustomfieldeditorplugin.121001.migrationtasks | 5 |
> SELECT * FROM "propertystring" WHERE id = 10523 | id | propertyvalue | |-------|---------------| | 10515 | 1556833017635 | | 10521 | 1556833169626 |
GlobalSettings
jiracustomfieldeditorplugin.admin.settings
GlobalSettings are stored as stringified json. The format is shown in the example below.
Example entries look like this:
> SELECT * FROM "propertyentry" WHERE property_key = "jiracustomfieldeditorplugin.admin.settings" | id | entity_name | entity_id | property_key | propertytype | |-------|-----------------|-----------|---------------------------------------------|--------------| | 10523 | jira.properties | 1 | jiracustomfieldeditorplugin.admin.settings | 5 |
> SELECT * FROM "propertystring" WHERE id = 10523 | id | propertyvalue | |-------|--------------------------------------------------| | 10523 | {"hideAdminLinksFromNonAdminUsers":false} |
Storing Complex App Data
App Permissions and Search Tables are stored via ActiveObjects which is a Jira API providing real database access to database tables. This is useful for complex data that needs to be queried by SQL Statements. This data can be accessed via a database query tool.
Search Table: AO_4811F4_CUSTOM_FIELDS
This table stores customfields to be able to provide filtering and pagination. The table contains customfield id, name, description and has multiple indexes on the columns, to provide fast sql queries during filtering.
ID | The primary key of the table with auto_increment. |
FIELD_ID | The customfield id. |
FIELD_ID_AS_STRING | The customfield id for fulltext search. |
NAME | The customfield name cut off at 255 characters. |
NAME_LOWER | The customfield name cut off at 255 characters and lower-cased for fulltext search. |
DESCRIPTION | The customfield description cut off at 255 characters. |
DESCRIPTION_LOWER | The customfield description cut off at 255 characters and lower-cased for fulltext search. |
FIELD_TYPE | The customfield type in short form such as |
An example entry looks like this:
> SELECT * FROM "AO_4811F4_CUSTOM_FIELDS" | ID | FIELD_ID | FIELD_ID_AS_STRING | NAME | NAME_LOWER | DESCRIPTION | DESCRIPTION_LOWER | FIELD_TYPE | |----|----------|--------------------|------------------|------------------|-------------------------------------------|-------------------------------------------|-----------------| | 2 | 10201 | 10201 | Coffee varieties | coffee varieties | Coffee varieties according to its origins | coffee varieties according to its origins | multicheckboxes |
App Permissions: AO_4811F4_PERMISSIONS
This table stores the app permissions used to enforce authorization on customfields and contexts. The table contains customfield id, context id, permission type, userkey and groupname and has multiple indexes on the columns, to provide fast sql queries.
ID | The primary key of the table with auto_increment. |
FIELD_ID | The customfield id. |
CONTEXT_ID | The customfield's context id or null if global permission type. |
TYPE | 1 = global permission, 2 = context permission. |
USER | The userKey of the user granted permission to. Note that userKey is not userName. |
GROUP | The groupname of the group granted permission to. |
Note: Starting with v2.0.0 the user permissions are stored as userKeys (not userNames). You can read this GDPR BlogPost to see why.
Example entries look like this:
> SELECT * FROM "AO_4811F4_PERMISSIONS" | ID | FIELD_ID | CONTEXT_ID | TYPE | USER | GROUP | |-----|----------|------------|------|----------|---------------------| | 316 | 10203 | 10406 | 2 | admin | | | 317 | 10203 | 10406 | 2 | | jira-user | | 318 | 10203 | | 1 | john | | | 319 | 10203 | | 1 | | jira-administrators |
App Settings: AO_4811F4_JOB
This table stores information about internal app jobs. The table contains job id, name, status, scheduled time, finished time and has multiple indexes on the columns, to provide fast sql queries.
ID | The primary key of the table with auto_increment. |
NAME | The job name. |
JOB_STATUS | The job status as: 1 = SCHEDULED, 2 = RUNNING, 3 = SUCCESS, 4 = FAILED. |
SCHEDULED_TIME | The UTC timestamp when the job was scheduled for execution. |
FINISHED_TIME | The UTC timestamp when the job finished its execution. |
Example entries look like this:
> SELECT * FROM "AO_4811F4_JOB" | ID | NAME | JOB_STATUS | SCHEDULED_TIME | FINISHED_TIME | |----|--------------------------------|------------|-------------------------|-------------------------| | 1 | JobRunDataMigrationTasks | 3 | 2019-05-16 13:03:27.106 | 2019-05-16 13:03:27.113 | | 2 | JobIndexCustomFieldSearchTable | 3 | 2019-05-16 13:03:27.106 | 2019-05-16 13:03:27.16 |
App Settings: AO_4811F4_EVENT_LOG
This table stores information about internal app events. The table contains job id, name, status, scheduled time, finished time and has multiple indexes on the columns, to provide fast sql queries.
ID | The primary key of the table with auto_increment. |
NAME | The event name. |
NAME_LOWER | The event name cut off at 255 characters and lower-cased for fulltext search. |
DESCRIPTION | The event description. |
DESCRIPTION_LOWER | The event description cut off at 255 characters and lower-cased for fulltext search. |
DATE_TIME | The UTC timestamp when the event occured. |
EVENT_HASH | An unique auto-generated hash to identify the event. |
Example entries look like this:
> SELECT * FROM "AO_4811F4_JOB" | ID | NAME | NAME_LOWER | DETAILS | DETAILS_LOWER | DATE_TIME | EVENT_HASH | |----|-------------------------|-------------------------|-------------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------|-------------------------|------------------------------------------------------------------| | 1 | PluginEnabledEvent | pluginenabledevent | on plugin install/update → triggered: JobRunDataMigrationTasks, JobIndexCustomFieldSearchTable | on plugin install/update → triggered: jobrundatamigrationtasks, jobindexcustomfieldsearchtable | 2019-05-02 21:39:29.324 | 1f7c9db29aa93491fe434acb1a9a23e70352e5ffc872c583718232520d06b190 | | 23 | CustomFieldCreatedEvent | customfieldcreatedevent | ID 10206, name Seasonal Fruit Choice, type cascadingselect → triggered: update row in customfield search table | id 10206, name seasonal fruit choice, type cascadingselect → triggered: update row in customfield search table | 2019-05-11 13:24:51.452 | 129d8d0deef00752b1b4fc9189b2348a4992bffb5a0584abd304a2b603f3f42b | | 37 | CustomFieldUpdatedEvent | customfieldupdatedevent | ID 10204, name Z Special Character 1 öäüÆæØ type cascadingselect → triggered: update row in customfield search table | id 10204, name z special character 1 öäüÆæØ type cascadingselect → triggered: update row in customfield search table | 2019-05-13 09:32:25.635 | 68e84993276ed6d9d674ddca828c7db69e34c5b42a69a2ea6d713dc80305477e |
Events and Background Tasks
The app needs to react to certain events in order to keep permissions and search-indexes up to date. Therefore depending on the event different background tasks are performed. The table below shows all events that the app listens to and which tasks are triggered. Since app version 2.0.0 the app listens to these events.
PluginEnabledEvent | When: Once during Jira startup. Or right after app is installed. |
CustomFieldCreatedEvent | When: Every time somone creates a customfield. |
CustomFieldUpdatedEvent | When: Every time somone updates a customfield. For example renaming it, or changing the description. |
CustomFieldDeletedEvent | When: Every time somone deletes a customfield. |
UserDeletedEvent | When: Every time somone deletes a Jira user. |
GroupDeletedEvent | When: Every time somone deletes a Jira group. |
UserKeyChangeHandler#update | When: Every time somone uses the GDPR Anonymize User Functionality to anonymize a user (userName, userKey, email are changed to e.g. JIRAUSER123). |
UserAnonymizationHandler#update | When: Every time somone uses the GDPR Anonymize User Functionality to anonymize a user (userName, userKey, email are changed to e.g. JIRAUSER123). |
Caches
We are using the Atlassian Cache 2 implementation.Global Settings Cache
This cache exists since app version 2.3.0. We have chosen cache type cluster that is limited to one entry and expires after 15 minutes. It is using a CacheLoader to load the setting from the database, if not present. We have chosen this cluster type, since the change of a global setting has to be present across the cluster immediately.
new CacheSettingsBuilder()
.maxEntries(1)
.remote()
.replicateSynchronously()
.replicateViaCopy()
.expireAfterWrite(15, TimeUnit.MINUTES)
Scheduled Jobs
We are using the Atlassian SchedulerService implementation.Event-Log Auto-Truncate Job
This job exists since app version 2.5.4. We have chosen cache runMode RUN_ONCE_PER_CLUSTER
. The user can set the cronExpression via the GUI. By default this Job is deactivated. The user has to opt-in and activate this feature if needed. If activated the job will truncate the Customfield Editor EventLog Table at the given times.
Schedule jobSchedule = Schedule.forCronExpression(schedule.getCronExpression()); JobConfig jobConfig = JobConfig .forJobRunnerKey(JobRunnerKey.of(MaintenanceEventLogTruncateJob.JOBRUNNERKEY)) .withSchedule(jobSchedule) .withRunMode(RunMode.RUN_ONCE_PER_CLUSTER); schedulerService.scheduleJob(jobId, jobConfig);
Deprecated App Data Storage
Prior to version 2.0.0 the app stored all settings via the Shared Access Layer (PluginSettingsFactory) which is a Jira API providing simple save and load methods for storing data. This is useful for internal App Settings. This data should usually not be accessed via a database query tool.
Note: Prior to v2.0.0 the userlist contained userNames. Starting with v2.0.0 the user permissions store the userKey. You can read the GDPR BlogPost to see why.
The data is stored by Jira in a table called propertyentry
with foreignkey references to the table propertystring
. The data can be accessed by using property keys.
Deprecated Global Permissions
jiracustomfieldeditorplugin.<customFieldId>.globalpermissions
For each customfield that has global permissions set, this setting exists. The format is stringified JSON as shown in the example below.
Example entries look like this:
> SELECT * FROM "propertyentry" WHERE property_key = "jiracustomfieldeditorplugin.customfield_10200.globalpermissions" | id | entity_name | entity_id | property_key | propertytype | |-------|-----------------|-----------|-----------------------------------------------------------------|--------------| | 10519 | jira.properties | 1 | jiracustomfieldeditorplugin.customfield_10200.globalpermissions | 5 |
> SELECT * FROM "propertystring" WHERE id = 10519 | id | propertyvalue | |-------|--------------------------------------------------| | 10519 | {"userlist":["admin","foo"],"grouplist":[]} |
Deprecated Context Permissions
jiracustomfieldeditorplugin.<customFieldId>.contextpermissions
For each customfield that has context permissions set, this setting exists. The format is stringified JSON as shown in the example below.
Example entries look like this:
> SELECT * FROM "propertyentry" WHERE property_key = "jiracustomfieldeditorplugin.customfield_10200.contextpermissions" | id | entity_name | entity_id | property_key | propertytype | |-------|-----------------|-----------|------------------------------------------------------------------|--------------| | 10518 | jira.properties | 1 | jiracustomfieldeditorplugin.customfield_10200.contextpermissions | 5 |
> SELECT * FROM "propertystring" WHERE id = 10518 | id | propertyvalue | |-------|------------------------------------------------------------------| | 10518 | [{"contextId": 123, "userlist":["admin","foo"],"grouplist":[]}] |
Manual Deprecated Permission Migration
If for some reason the migration job fails to migrate your permissions even though it shows SUCCESS, please contact the support.
It can be fixed by extracting the old permissions via an SQL Query and creating an migration script that uses the REST API.
> SELECT "propertyentry"."id", "propertyentry"."property_key", "propertystring".* FROM "propertyentry" LEFT JOIN "propertystring" ON "propertystring"."id" = "propertyentry"."id" WHERE property_key LIKE 'jiracustomfieldeditorplugin%' ; | id | property_key | id | propertyvalue | |-------+------------------------------------------------------------------+-------+---------------------------------------------------------------| | 10519 | jiracustomfieldeditorplugin.customfield_10200.globalpermissions | 10519 | {"userlist":["admin","foo"],"grouplist":["team-a"]} | | 10520 | jiracustomfieldeditorplugin.customfield_10200.contextpermissions | 10520 | {"contextId": 123,"userlist":["admin","foo"],"grouplist":[]} |
You will get a lot of these lines and you can transform them (or ask the support to transform them for you) into a simple migration script.
For the above query it can look like this:
#!/bin/bash
# jiracustomfieldeditorplugin.customfield_10200.globalpermissions {"userlist":["admin","foo"],"grouplist":["team-a"]}
curl -X PATCH -u admin:adminPass -H "Content-Type: application/json" \
"https://jira.foo.io/rest/jiracustomfieldeditorplugin/1/admin/customfields/10200"
-d '{"user": "admin", "action": "GRANT", "type": "GLOBAL"}'
curl -X PATCH -u admin:adminPass -H "Content-Type: application/json" \
"https://jira.foo.io/rest/jiracustomfieldeditorplugin/1/admin/customfields/10200"
-d '{"user": "foo", "action": "GRANT", "type": "GLOBAL"}'
curl -X PATCH -u admin:adminPass -H "Content-Type: application/json" \
"https://jira.foo.io/rest/jiracustomfieldeditorplugin/1/admin/customfields/10200"
-d '{"group": "team-a", "action": "GRANT", "type": "GLOBAL"}'
# jiracustomfieldeditorplugin.customfield_10200.contextpermissions {"contextId": 123,"userlist":["admin","foo"],"grouplist":[]}
curl -X PATCH -u admin:adminPass -H "Content-Type: application/json" \
"https://jira.foo.io/rest/jiracustomfieldeditorplugin/1/admin/customfields/10200"
-d '{"contextId": 123, "user": "admin", "action": "GRANT", "type": "CONTEXT"}'
curl -X PATCH -u admin:adminPass -H "Content-Type: application/json" \
"https://jira.foo.io/rest/jiracustomfieldeditorplugin/1/admin/customfields/10200"
-d '{"contextId": 123, "user": "foo", "action": "GRANT", "type": "CONTEXT"}'
You can execute this on Linux via your terminal and it will patch in the missing permissions that were not migrated.