Monday, September 27, 2010

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.

12 comments:

  1. Does anyone know how to format source code and pre elements better in HTML for blogspot blogs?

    ReplyDelete
  2. I use html-fontify with Emacs and customized CSS. You can see it in action here: http://uint32t.blogspot.com/2009/05/using-c0x-lambda-to-replace-boost-bind.html

    I'd try and switch to wordpress as soon as possible. It has a lot more options for non-trivial formatting.

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Thanks for Sharing, this is just what i was looking for!

    ReplyDelete
  6. Extra note: the entity id is unique per address and is created by you for each address. JIRA has a table (SEQUENCE_VALUE_ITEM) that it uses for its own purposes for some entity ids (OSPropertyEntry), increasing them as necessary so they are always unique. If you are storing your own data, you need to create a unique entity id value for each object that you are storing.

    ReplyDelete
  7. i have a question: i`d like to save some additional info about group in PropertySet, so, how can i retrieve Id of jira group? I can`t find such a method. Thx!

    ReplyDelete
  8. I have created the propertyset using following code. I am using JIRA 4.3.4.

    PropertySet propertySetjiraPropertySetFactory.buildNoncachingPropertySet("SomeService");
    propertySet.setString("pollingtime", "2011-11-12 4:23");
    String lastpolledTime = propertySet.getString("pollingtime");

    When I run this code, It creates the entity and stored the polling time in the propertyString database. However when I tried to read the value using propertySet.getString("pollingtime"), it is throwing an error as below.
    Do you know how to read value from table?

    com.opensymphony.module.propertyset.InvalidPropertyTypeException
    at com.opensymphony.module.propertyset.ofbiz.OFBizPropertySet.get(OFBizPropertySet.java:273)
    at com.opensymphony.module.propertyset.AbstractPropertySet.getString(AbstractPropertySet.java:305)

    ReplyDelete
  9. I'm not sure about buildNoncachingPropertySet. Maybe it doesn't set something that is needed? I usually use something more like:

    private PropertySet getPS(String projectId) {
    HashMap ofbizArgs = new HashMap();
    ofbizArgs.put("delegator.name", "default");
    ofbizArgs.put("entityName", "jenkins_environments");
    ofbizArgs.put("entityId", new Long(projectId));
    PropertySet ofbizPs = PropertySetManager.getInstance("ofbiz", ofbizArgs);
    return ofbizPs;
    }

    ReplyDelete
  10. Matt, it would be nice to mention Active Objects(since jira 4.3) as a great mechanism for storing in JIRA.

    ReplyDelete
  11. Absolutely! My plugin book "Practical JIRA Plugins" describes both but for all development in JIRA 4.3+ I recommend Active Objects.

    ReplyDelete
  12. Hi Matt,

    Thanks for the great book. It was really useful. Just one question regarding "Configurable custom filed". How to generate the ENTITY_ID for storing the configurations. There should be someway to generate this id. Can you please tell me what is the correct way to populate ENTITY_ID.

    ReplyDelete