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.

JIRA Groups and JIRA Project Roles

Summary: JIRA groups are made up of JIRA users and can only be changed by JIRA administrators. JIRA project roles are made up of JIRA users and JIRA groups and can be changed per project by project administrators.

The difference between groups and project roles seems to confuse many JIRA administrators. This article explains the differences and what each one is good for. Up until a few years ago JIRA had users and groups of users, but no project roles. Groups were pretty powerful - wherever you could do something with a user, you could probably use a group instead.

For instance, if you wanted to allow a user John.Smith to change the reporter field in your issues, you could

1. Create a new permission scheme named something like "John.Smith can change reporter"
2. Add the John.Smith user to the appropriate Modify Reporter permission entry in the new permission scheme
3. Change the appropriate projects to use the new permission scheme

You could also do the same thing with a group

1. Define a new JIRA group "Can Modify Reporters"
2. Add the user John.Smith to the new group
3. Create a new permission scheme named something like "My new group can change reporter"
4. Add the group (instead of the user) to the appropriate Modify Reporter permission entry in the new permission scheme.
5. Change the appropriate projects to use the new permission scheme

All good so far, but there are two main problems with groups: scaling and updating

Scaling

If I want John.Smith to be able to edit the reporter in some of my projects, and also allow a different user Jane.Bloggs do the same in some other projects, then I have to create two permission schemes, one for each user. If I then decide that they are both allowed to edit the reporter in some shared projects, then I need a third permission scheme. With lots of users this leads to an explosion in the number of permission schemes (or any other JIRA scheme that supports groups). Keeping track of the difference between each of these schemes is tedious and error-prone, even with the scheme comparison tools.

Updating

Only JIRA administrators can change the membership of groups, which means extra work for them that could be better handled by JIRA project leads.

Project Roles

What was needed was another level of indirection and that's what exactly JIRA project roles are. Every JIRA project has three default project roles - Administrators, Developers and Users. The default roles can be changed, but for now let's stick with those three. For each role in every project, you can define who plays that role by adding a user or a group to the role.

Viewing a project's roles

A project's default roles

Once you've chosen who plays each role for each project, we can use the roles in our schemes. For instance, when you look at a permission scheme, you'll see that all of the permissions are granted to project roles, not to users or groups. The key thing about roles is that they can be changed per project by people who aren't JIRA administrators.

Creating a New Role

Another way to understand what's going on here is to create a new role. Let's say for some reason we want to allow a Technical Publications user assigned to each project to modify the reporter of an issue.

The default permission scheme allows users in the Administrator role for a project to modify the reporter of an issue. We don't want to allow tech pubs to administer the project, just give them that one specific permission.

We can create a new role "Technical Publications" in Admin, "Users, Groups & Roles", Project Role Browser. We can also add our Tech Pubs lead Bobby.Jo as a default member of the new role so that she will be in the Tech Pubs role for all projects by default.

Adding the new "Technical Publications" project role

Now every JIRA project has this new role. We can add the appropriate tech pubs user (or group) to the Tech Pubs role for each project. Once the users for this role have been added, we can edit the permission scheme and add the Tech Publications role to the Modify Reporter permission entry. The permission scheme now checks which users are in the role for each project, rather than looking at a fixed list of users or groups of users.

If the tech pubs person changes for the project, then the people with the project Administrator role can change the members of the Technical Publications role for their project. And there is no need to ask the JIRA administrator to make the changes.


Should I use a group or a project role?

If you want to refer to the same set of users across multiple projects, use a group. If you want to refer to a set of users that is different per project, use a role.


JIRA Versions

All

Documentation

http://confluence.atlassian.com/display/JIRA/Managing+Groups

http://confluence.atlassian.com/display/JIRA/Managing+Project+Roles

Signs that a new version of JIRA is imminent

Atlassian is a more open company than most and they're also smart enough not to provide release dates and then have to change them later on. Atlassian partners usually get a week's notice that the next release is coming up, but I have my own set of signs. Once all these have happened, the next JIRA release is imminent.

• Cut and paste text appears from Atlassian PM in a few issues with lots of votes,  explaining why each issue is not going to be fixed in the near future.
• A larger number of other issues were recently resolved (use the Resolution Time chart with a filter for the next version)
• The most reliable indicator seems to be when the upgrade documentation changes.

Based on these inputs, I expect to see JIRA 4.2 before the end of this month, certainly before AtlasCamp in mid-October when some of the key JIRA developers might be out of the office for a few days.

~Matt

Older articles about JIRA

I run a separate blog for my company Consulting Toolsmiths where I 've published various articles related to JIRA over the past three years. Future JIRA articles will appear on this blog instead, but some of the more technical other ones include:

• One Bug, Multiple Branches - a presentation to SF Atlassian User Group about tracking lots of builds in JIRA
• Handling large numbers of JIRA Projects - a 3.13 jsp modification to let you select a Category when creating an issue and then only have the projects in that category appear
• Making it easier to maintain JIRA Workflows - a 3.13 jsp modification to show the name of a transition's screen in the list of transitions
• JIRA Customization Guidelines - a presentation from AtlasCamp 2009
• Finally, a use for those JIRA user properties - how to search for users with a given property, e.g. location="San Jose"
• Field-level permissions in JIRA - via .vm modifications. Superseded by the Behaviors plugin.
• Using JIRA for Test Tracking - an early description, largely outdated now.

All posts labeled with JIRA are here.

~Matt

Why isn't there a JIRA book?

People occasionally ask me why I haven't written a book about JIRA. I wrote Practical Development Environments for O'Reilly a few years ago and enjoyed doing so. I have lots of ideas for what would make a good book about JIRA. So what's the problem? Well, here's the gist of a reply I sent to a publisher recently:

I'd love to write that book! My problem is that I don't want to spend a year working 8pm to 2am for minimal pay.  That's horribly destructive to family life. Nowadays I'm in a consulting position where I could probably do it as a day job in around 6 months, but who's going to pay me to do that? Atlassian might consider it taking it on as marketing expense but my understanding is that what it would cost them is far larger than what they might ever expect to make.

My sense is that technical books are created because their authors felt "it just needed doing" or "because it's there", but not for rational financial reasons. Yet if this blog ends up containing a useful number of articles I wouldn't object to publishing them as a book.

Who's using your JIRA?

Sometimes it's interesting to see who is actually using a JIRA instance. The Participants field in the JIRA Toolkit is a custom field that shows the issues that you've reported, are assigned to, or have commented on. The useful thing is that you can create an Issue Statistics gadget to summarise the participants by user name.

1. Create a filter to select the issues to be examined. This could be all issues, or just a few select projects
2. Create an Issue Statistics gadget on a dashboard
3. Select the Participants field for Statistic Type
4. Select Total for Sort By
5. Select Descending for Sort Direction
6. Select Yes for Show Resolved Issue Statistics
7. Select Never for Refresh Interval

You should get a list of users as in the screenshot below.

Last year I posted a summary of who had reported issues with JIRA using only the Reporters field. This time I looked at who were the participants in JIRA issues in the past year. As expected most participants in JIRA issues work for Atlassian. Neal Applebaum and Sergiy Lizenko (ILS-Ukraine) are still the most active and I still come in a distant third. Greg Brauer of DreamWorks is the newest user in the list.

Write what you want to read

This blog contains articles about JIRA, the issue tracker from Atlassian. Most of the articles are technical in nature. If you have an article you would like to see appear here, please submit it to the editor, Matt Doar.