Database – Custom fields
Custom fields defined in the system are stored in the customfield table and instances of custom fields are
stored in customfieldvalue:
1 2mysql> desc customfieldvalue; +-------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+-------+ | ID | decimal(18,0) | NO | PRI | | | | ISSUE | decimal(18,0) | YES | MUL | NULL | | | CUSTOMFIELD | decimal(18,0) | YES | | NULL | | | PARENTKEY | varchar(255) | YES | | NULL | | | STRINGVALUE | varchar(255) | YES | | NULL | | | NUMBERVALUE | decimal(18,6) | YES | | NULL | | | TEXTVALUE | longtext | YES | | NULL | | | DATEVALUE | datetime | YES | | NULL | | | VALUETYPE | varchar(255) | YES | | NULL | | +-------------+---------------+------+-----+---------+-------+
You can print all custom field values for an issue with:
1 2mysql> select * from customfieldvalue where issue=(select id from jiraissue where issuenum=5448 and project=(select id from project where pkey='JRA')); +-------+-------+-------------+-----------+-------------+-------------+-----------+---------------------+-----------+ | ID | ISSUE | CUSTOMFIELD | PARENTKEY | STRINGVALUE | NUMBERVALUE | TEXTVALUE | DATEVALUE | VALUETYPE | +-------+-------+-------------+-----------+-------------+-------------+-----------+---------------------+-----------+ | 23276 | 22160 | 10190 | NULL | NULL | NULL | NULL | 2004-12-07 17:25:58 | NULL | +-------+-------+-------------+-----------+-------------+-------------+-----------+---------------------+-----------+
You can see what type of custom field this (10190) is with:
1 2mysql> select * from customfield where id=10190; +-------+-------+------------------------------------------------+--------------------------------------------------------+-----------------+-------------+--------------+-----------+---------+-----------+ | ID | cfkey | CUSTOMFIELDTYPEKEY | CUSTOMFIELDSEARCHERKEY | cfname | DESCRIPTION | defaultvalue | FIELDTYPE | PROJECT | ISSUETYPE | +-------+-------+------------------------------------------------+--------------------------------------------------------+-----------------+-------------+--------------+-----------+---------+-----------+ | 10190 | NULL | com.atlassian.jira.ext.charting:resolutiondate | com.atlassian.jira.ext.charting:resolutiondatesearcher | Resolution Date | NULL | NULL | NULL | NULL | NULL | +-------+--------------------------------------------------------+--------------------------------------------------------+-----------------+-------------+--------------+-----------+---------+-----------+
(That is, it's a "Resolution Date".)
This query identifies a particular custom field value in a particular issue:
1 2mysql> select stringvalue from customfieldvalue where customfield=(select id from customfield where cfname='Urgency') and issue=(select id from jiraissue where issuenum=845 and project=(select id from project where pkey='FOR')); +-------------+ | stringvalue | +-------------+ | Low | +-------------+ 1 row in set (0.33 sec)
If the custom field has multiple values (multi-select or multi-user picker), each issue can have
multiple customfieldvalue rows:
1 2mysql> select * from customfieldvalue where customfield=(select ID from customfield where cfname='MultiUser'); +-------+-------+-------------+-----------+-------------+-------------+-----------+-----------+-----------+ | ID | ISSUE | CUSTOMFIELD | PARENTKEY | STRINGVALUE | NUMBERVALUE | TEXTVALUE | DATEVALUE | VALUETYPE | +-------+-------+-------------+-----------+-------------+-------------+-----------+-----------+-----------+ | 10002 | 10060 | 10000 | NULL | bob | NULL | NULL | NULL | NULL | | 10003 | 10060 | 10000 | NULL | jeff | NULL | NULL | NULL | NULL | +-------+-------+-------------+-----------+-------------+-------------+-----------+-----------+-----------+ 2 rows in set (0.00 sec)
Here issue 10060 has bob and jeff users in its MultiUser custom field.
Custom field configuration options
The option sets (1, 2, 3 and A, B, C) are stored in the customfieldoption table:
1 2mysql> select * from customfieldoption where customfieldconfig=10031; +-------+-------------+-------------------+----------------+----------+-------------+------------+----------+ | ID | CUSTOMFIELD | CUSTOMFIELDCONFIG | PARENTOPTIONID | SEQUENCE | customvalue | optiontype | disabled | +-------+-------------+-------------------+----------------+----------+-------------+------------+----------+ | 10000 | 10001 | 10031 | NULL | 0 | 1 | NULL | false | | 10001 | 10001 | 10031 | NULL | 1 | 2 | NULL | false | | 10002 | 10001 | 10031 | NULL | 2 | 3 | NULL | false | +-------+-------------+-------------------+----------------+----------+-------------+------------+----------+
1 2mysql> select * from customfieldoption where customfieldconfig=10032; +-------+-------------+-------------------+----------------+----------+-------------+------------+----------+ | ID | CUSTOMFIELD | CUSTOMFIELDCONFIG | PARENTOPTIONID | SEQUENCE | customvalue | optiontype | disabled | +-------+-------------+-------------------+----------------+----------+-------------+------------+----------+ | 10003 | 10001 | 10032 | NULL | 0 | A | NULL | false | | 10004 | 10001 | 10032 | NULL | 1 | B | NULL | false | | 10005 | 10001 | 10032 | NULL | 2 | C | NULL | false | +-------+-------------+-------------------+----------------+----------+-------------+------------+----------+
Custom field configuration default value
The custom field default value is stored in the genericconfiguration table. Because this table must store a
value for any custom field type (cascading selects, multi-selects, and so on), the value is encoded as XML.
If we were to set a default value of "2" for our "Default Configuration Scheme for SelectCF", it would be recorded as:
1 2mysql> select * from genericconfiguration where ID=10031; +-------+--------------+---------+--------------------+ | ID | DATATYPE | DATAKEY | XMLVALUE | +-------+--------------+---------+--------------------+ | 10031 | DefaultValue | 10030 | <string>2</string> | +-------+--------------+---------+--------------------+
Custom field configuration schemes
Jira custom fields can have different default values and possible values for each project or issue type. This is set up by clicking Configure in the custom field definition.
For instance, in this screenshot the "SelectCF" select-list field will have values 1, 2, 3 for all projects except bugs and sub-tasks in "NewProj" and "Test Project", which will have values A, B, and C:
Custom field configuration scopes
In the database, these custom field configuration schemes are stored in the fieldconfigscheme table:
1 2mysql> select * from fieldconfigscheme where id in (10031,10032); +-------+-------------------------------------------+------------------------------------------------+-------------------+-------------+ | ID | configname | DESCRIPTION | FIELDID | CUSTOMFIELD | +-------+-------------------------------------------+------------------------------------------------+-------------------+-------------+ | 10031 | Default Configuration Scheme for SelectCF | Default configuration scheme generated by JIRA | customfield_10001 | NULL | | 10032 | NewProj scheme | | customfield_10001 | NULL | +-------+-------------------------------------------+------------------------------------------------+-------------------+-------------+
The projects in scope for each of these schemes is listed as records (one per project)
in the configurationcontext table:
1 2mysql> select * from configurationcontext where fieldconfigscheme=10031; +-------+-----------------+---------+----------------------+-------------------+ | ID | PROJECTCATEGORY | PROJECT | customfield | FIELDCONFIGSCHEME | +-------+-----------------+---------+----------------------+-------------------+ | 10053 | NULL | NULL | customfield_10001 | 10031 | +-------+-----------------+---------+----------------------+-------------------+
(Here showing that the "Default Configuration Scheme for SelectCF" applies to all projects.)
1 2mysql> select * from configurationcontext where fieldconfigscheme=10032; +-------+-----------------+---------+----------------------+-------------------+ | ID | PROJECTCATEGORY | PROJECT | customfield | FIELDCONFIGSCHEME | +-------+-----------------+---------+----------------------+-------------------+ | 10054 | NULL | 10000 | customfield_10001 | 10032 | | 10055 | NULL | 10010 | customfield_10001 | 10032 | +-------+-----------------+---------+----------------------+-------------------+
(Here showing that "NewProj scheme" is restricted to projects with IDs 10000 and 10010 ("Test Project" and "NewProj").)
Finally, the issue types in scope for each scheme is listed as records (one per issue type) in
the fieldconfigschemeissuetype table:
1 2mysql> select * from fieldconfigschemeissuetype where fieldconfigscheme = 10031; +-------+-----------------+-------------------+----------------------+ | ID | ISSUETYPE | FIELDCONFIGSCHEME | FIELDCONFIGURATION | +-------+-----------------+-------------------+----------------------+ | 10051 | NULL | 10031 | 10031 | +-------+-----------------+-------------------+----------------------+
(Here showing that "Default Configuration Scheme for SelectCF" is not limited to any issue types.)
1 2mysql> select * from fieldconfigschemeissuetype where fieldconfigscheme = 10032; +-------+-----------------+-------------------+----------------------+ | ID | ISSUETYPE | FIELDCONFIGSCHEME | FIELDCONFIGURATION | +-------+-----------------+-------------------+----------------------+ | 10052 | 1 | 10032 | 10032 | | 10053 | 4 | 10032 | 10032 | +-------+-----------------+-------------------+----------------------+
(Here showing that "Newproj scheme" is limited to issue types with IDs 1 and 4.)
Note that there should always be a record in configurationcontext and fieldconfigschemeissuetype for each
issue type configuration scheme. If the scheme isn't restricted to any projects or issue types, the project and
issuetype columns of the respective tables should be NULL.
Rate this page: