Storing data in JIRA properties tables

Summary: store your own data in existing JIRA tables using the same classes that JIRA uses.

One of the most common questions when adding new functionality to JIRA is "where do I store my own configuration data?". The brute-force way is to add new tables or new columns to the database by modifying entityde fs/entitymode.xml, but then your future upgrades will need the same changes.

A better approach that's easier to maintain is to store your data where JIRA stores this sort of data itself, the propertyentry table. This also has the advantage of using the same classes that JIRA uses for accessing the data.

The propertyentry table is documented in the JIRA database schema as being where JIRA stores the user information, but JIRA also stores its own licenses and other configuration values in this table.

The way that JIRA uses the propertyentry table is based on the PropertySet interface from OpenSymphony. This interface lets you store unique "key=value" pairs with various get and set methods.

Let's take a look at the propertyentry table and its fields.

mysql> describe propertyentry;
+--------------+---------------+
| Field        | Type          |
+--------------+---------------+
| ID           | decimal(18,0) |
| ENTITY_NAME  | varchar(255)  |
| ENTITY_ID    | decimal(18,0) |
| PROPERTY_KEY | varchar(255)  |
| propertytype | decimal(9,0)  |
+--------------+---------------+

The documentation is a bit terse about what each of these fields is for: "each property has a record in the propertyentry table specifying its name and type, and a record in one of propertystring, propertydecimal, propertydate, propertytext, propert ydata or propertynumber, depending on the type."

In more detail, the purposes of each of the fields in the propertyentry table are:

• ID - a unique identifier for every piece of data
• ENTITY_NAME - class name of the data object
• ENTITY_ID - identifies an instance of the data object
• PROPERTY_KEY - the key of the key=value pair
• propertytype - the data type of the value, e.g. 5 for a string

For each propertytype the corresponding table for the data value is:

1  propertynumber
5  propertystring
6  propertytext

One Example

Let's say I want to store two street addresses. In XML they might look something like this:

<address id="10010" housenumber="10" street="Oak Avenue" city="San Jose">
<address id="10200" housenumber="32" street="Long Street" city="London">

In the propertyentry table they would look like this

ID  ENTITY_NAME  ENTITY_ID  PROPERTY_KEY    PROPERTYTYPE

100 Address      10010      House_Number    5
101 Address      10010      Street          5
102 Address      10010      City            5

103 Address      10200      House_Number    5
104 Address      10200      Street          5
105 Address      10200      City            5

First, the ID is a unique identifier for every piece of data in this database.

Then the entity name "Address" is the kind of data we want to store. In an Object Relational Model (ORM) this is the class name of the object being stored.

Then comes the entity id to used distinguish multiple addresses. In ORM this is a unique identifier for each instance of an object.

Then property_key contains the name of a data field within each address. This is the "key" of the "key=value" pair that is being stored.

There's one last field in the propertyentry table - propertytype. The most common value is 5, which means that the value is a string stored in the propertystring table. 1 is for boolean settings such as enabling or disabling voting and its value is stored in the propertynumber table. 6 is for blocks of text such as the license data and is in propertytext.

The values in propertystring for our example are:

ID   PROPERTYVALUE

100  10
101  Oak Avenue
102  San Jose

103  32
104  Long Street
105  London

Accessing the Data

We first have to create a PropertySet object propertySet that knows how to refer to just one address, say the first one with has an entity id value of 10010.

This can be done with code such as

private static PropertySet getPS() {
        if (ofbizPs == null) {
            HashMap ofbizArgs = new HashMap();
            ofbizArgs.put("delegator.name", "default");
            ofbizArgs.put("entityName", "Address");
            ofbizArgs.put("entityId", new Long(10010));
            ofbizPs = PropertySetManager.getInstance("ofbiz", ofbizArgs);
        }
        return ofbizPs;
    }

Then we can use that to call

propertySet.setString("Street", "Pine Avenue");

to update the row in propertystring so that it looks like

ID   PROPERTYVALUE
101  Pine Avenue

A similar method can be used to get the data:

String currentStreet = propertySet.getString("Street");

More Examples

User Properties are stored with a property key prefixed by "jira.meta.". So if you store a property "hair_color=brown", you will see an entry for the user with id 10000 in propertyentry such as

mysql> select * from propertyentry where property_key like 'jira.meta%';
+-------+-------------+-----------+-----------------------+--------------+
| ID    | ENTITY_NAME | ENTITY_ID | PROPERTY_KEY          | propertytype |
+-------+-------------+-----------+-----------------------+--------------+
| 10111 | OSUser      |     10000 | jira.meta.hair_color  |            5 | 
+-------+-------------+-----------+-----------------------+--------------+

and an entry in propertystring such as

mysql> select * from propertystring where id=10111;
+-------+---------------+
| ID    | propertyvalue |
+-------+---------------+
| 10111 | brown         | 
+-------+---------------+

JCLP

The JIRA Create and Link plugin stores its configuration as an XML string in the propertyentry table and is an example of storing general data for a plugin. The class that does this in a generalized way is Persister.java.

JIRA Versions

All

Documentation

http://confluence.atlassian.com/display/JIRA/Database+Schema

The "User Details" section of the [Issue Fields|http://confluence.atlassian.com/display/JIRA/Issue+fields] part of the database schema documentation is more helpful after reviewing the example in this article.