This page last changed on Oct 24, 2007 by jeff.

Custom fields defined in the system are stored in the customfield table, and instances of custom fields are stored in customfieldvalue:

mysql> 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    |       |
+-------------+---------------+------+-----+---------+-------+

We can print all custom field values for an issue with:

mysql> select * from customfieldvalue where issue=(select id from jiraissue where pkey='JRA-5448');
+-------+-------+-------------+-----------+-------------+-------------+-----------+---------------------+-----------+
| ID    | ISSUE | CUSTOMFIELD | PARENTKEY | STRINGVALUE | NUMBERVALUE | TEXTVALUE | DATEVALUE           | VALUETYPE |
+-------+-------+-------------+-----------+-------------+-------------+-----------+---------------------+-----------+
| 23276 | 22160 |       10190 | NULL      | NULL        |        NULL | NULL      | 2004-12-07 17:25:58 | NULL      |
+-------+-------+-------------+-----------+-------------+-------------+-----------+---------------------+-----------+

and we can see what type of custom field this (10190) is with:

mysql> select * from customfield where id=10190;
+-------+------------------------------------------------+--------------------------------------------------------+-----------------+-------------+--------------+-----------+---------+-----------+
| ID    | CUSTOMFIELDTYPEKEY                             | CUSTOMFIELDSEARCHERKEY                                 | cfname          | DESCRIPTION | defaultvalue | FIELDTYPE | PROJECT | ISSUETYPE |
+-------+------------------------------------------------+--------------------------------------------------------+-----------------+-------------+--------------+-----------+---------+-----------+
| 10190 | com.atlassian.jira.ext.charting:resolutiondate | com.atlassian.jira.ext.charting:resolutiondatesearcher | Resolution Date | NULL        | NULL         |      NULL |    NULL | NULL      |
+-------+------------------------------------------------+--------------------------------------------------------+-----------------+-------------+--------------+-----------+---------+-----------+

(ie. it's a "Resolution Date").

This query identifies a particular custom field value in a particular issue:

mysql> select stringvalue from customfieldvalue where customfield=(select id from customfield where cfname='Urgency') and issue=(select id from jiraissue where pkey='FOR-845');
+-------------+
| 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:

mysql> 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 two users, bob and jeff 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:

mysql> select * from customfieldoption where customfieldconfig=10031;

ID CUSTOMFIELD CUSTOMFIELDCONFIG PARENTOPTIONID SEQUENCE customvalue optiontype
10000 10001 10031 NULL 0 1 NULL
10001 10001 10031 NULL 1 2 NULL
10002 10001 10031 NULL 2 3 NULL

mysql> select * from customfieldoption where customfieldconfig=10032;

ID CUSTOMFIELD CUSTOMFIELDCONFIG PARENTOPTIONID SEQUENCE customvalue optiontype
10003 10001 10032 NULL 0 A NULL
10004 10001 10032 NULL 1 B NULL
10005 10001 10032 NULL 2 C NULL

Custom field configuration default value

The custom field default value is stored in the genericconfiguration table. Since this table must store a value for any custom field type (cascading selects, multi-selects, etc) 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:

mysql> 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 and/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 improvements 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

mysql> 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:

mysql> select * from configurationcontext where fieldconfigscheme=10031;

ID PROJECTCATEGORY PROJECT customfield FIELDCONFIGSCHEME
10053 NULL NULL customfield_10001 10031

(Here showing that that the "Default Configuration Scheme for SelectCF" applies to all projects)

mysql> 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:

mysql> 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)

mysql> 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. Incidentally JIRA has/had a bug where it didn't leave an entry when deleting an issue type (JRA-10461), so if you are making changes manually, don't make the same mistake.


cf_configschemes.png (image/png)
Document generated by Confluence on Mar 27, 2011 18:54