Database Values JIRA Plugin

Description/Features

This plugin for JIRA is a custom field that allows you to connect to an external database and fetch some values from there. After adding this custom field to an issue edit screen, you can select one of those values so it will be associated with the current issue.
You can choose how the values are rendered for viewing, editing and searching independently. For the editing, you can use a combobox or an AJAX-style input field.
Internally, the plugin will store only the primary key of the item you have selected, so you can safely edit things in your database, as long as you keep the primary key constant.

Usage

The custom field must be configured using a properties file. E.g.:

# The database connection parameters
database.driver=org.hsqldb.jdbcDriver
database.user=sa
database.password=
database.connection.url=jdbc:hsqldb:mem:plugintestdb

# Cache Timeout (= 15 minutes by default). The actual db is queried only once and then the results are kept in the cache for the given timeout. Uncomment the line below to change it.
#cache.timeout=900000 

# The SQL Query that will be executed on the database
sql.query=select id, firstname, lastname, city, country from customer
# The column number (starting from 0) that contains the primary key of the returned data.
primarykey.column.number=0
# The pattern that should be used to render the data in view mode. Use {column_number} as placeholders.
rendering.viewpattern={1} {2} from <a href="http://maps.google.com/maps?f=q&hl=nl&geocode=&q={3}, {4}">{3}, {4}</a>
# The pattern that should be used to render the data in edit mode. Use {column_number} as placeholders.
rendering.editpattern={2}, {1}
# The pattern that should be used to render the data in searcher. Use {column_number} as placeholders.
rendering.searchpattern={1} {2} ({0})
# This is used when sorting in the issue navigator. When not defined, the 'rendering.viewpattern' is used.
rendering.sortpattern={1} {2}
# Use 0 to have a combobox for editing, 1 to have AJAX-style input field, 2 for cascading select
edit.type=0

The name of the properties file must be jira-database-values-plugin-10000.properties where 10000 should be replaced with the id of the custom field. This allows you to define multiple custom fields with different queries or even pointing to completely different databases.

As you can see, you can even include some html into the viewing. In the above example, clicking the link will open a window of google maps with the address.

Database parameters

The first 4 parameters (database.driver, database.user, database.password, database.connection.url) are needed to be able to connect to the database. They define what database should be used, where it is and what user should be used to read from it.

The sql.query parameter contains the SQL statement that will be used to query the database. Note that I do not do any checking on the query to see if you are not doing anything wrong with it. You are advised to only use SELECT statements

Rendering parameters

The sql.query will return a number of database rows with a number of columns. The order of the column is important for the rendering parameters (rendering.viewpattern, rendering.editpattern, rendering.searchpattern and rendering.sortpattern) and the primarykey.column.number parameter.
This last one defines what value the plugin will store in the JIRA database to know what value was selected by the user. You are strongly adviced to use the column that contains your primary key there.

In the rendering patterns, you must use MessageFormat-alike formatting to define where the values should come. If we look at the following example:

rendering.editpattern={2}, {1}

Here we define that the pattern for editing should contain the 3rd column, then a comma and a space and then the 2nd column (Note that we work zero-based!)

Sorting

You can control how the custom field is sorted in the issue navigator via rendering.sortpattern.

The pattern is optional. When not defined, the rendering.viewpattern is used. This pattern can be useful if the rendering.viewpattern uses some HTML elements and you don't want those to interfere with proper sorting. The sorting pattern is only used internaly to ensure proper sorting. The actual value you see in the issue navigator is defined by the rendering.viewpattern. The sorting is not case sensitive.

By using this separate property, we can display the names as:

Wim Deblauwe from Heule, Belgium

but sort on last name for example by setting the rendering.sortpattern={2}.

Screenshot of sorting in the issue navigator

If you want to sort in the drop-down menu that is used when selecting a value, then you need to use ORDER BY in you SQL query.

Depending on the project

You can make the results of the custom field depend on the actual project that is being used if you use the following SQL query syntax:

sql.query=select id, firstname, lastname, city, country from customer where jira_key like '${jira.project.key}'

The above example assumes that you have a 'jira_key' column in your table containing the JIRA project key.

You need to use like in your where clause otherwise, things will not work properly.

You also need to specify the primarykey.column.name property with the exact name of your primary key column. This is needed because the cache is bypassed for the issue navigator when depending on the project.

To avoid the cache growing too big, you need to configure cache.maximum.projects when using this functionality. This parameter defines how many different projects are being kept in the cache at the same time. By default this is 1. If you have only a small number of rows but many projects, you can increase this without problem to the number of projects you have. If you have a large number of rows per project, you need to keep this low.

Also know that depending on the project has an impact on performance in the issue navigator. Since issues from multiple projects can be shown in the issue navigator, I bypass the cache there (since otherwise, I still need to put everything in the cache).

Configuring AJAX-style input field

By default, a combobox is used to allow the user to input a value. However, you can configure the plugin to use an AJAX-style input field. For that, you need to set edit.type in the properties file to 1.
It is also possible to add additional information with the AJAX-style input field that will be displayed when the user is making his choice, but will not appear in the input box once he has made his selection.
In editing-ajax-example.png, the rendering.editpattern is defined as follows:

rendering.editpattern={2}, {1}<span class="informal" style="color: grey;"><br/>{3}, {4}</span>

You will notice that the additional information about each choice (a customer name in the example) needs to be included in a span html element with the class attribute set to 'informal'. If you want to style using inline CSS, then do it after the class attribute. It is important the if you use the 'informal' part, you start with <span class="informal", otherwise, you will not get the results you would expect.

Screenshot of AJAX-style edit

Configurating cascading select comboboxes

You can use a cascading select (like the build-in custom field) also with this plugin. If you have a lot of entries, it might be easier for the user to first select some kind of category and then make this final selection. For this you need the following configuration:

edit.type=2
rendering.editpattern.group.column.number=4

An edit.type of 2 means you want a cascading select. The rendering.editpattern.group.column.number identifies the column you want to group by. In our customer example, we group by the country. So a user can first select the country and then the 2nd combobox will only contain the customers from that country.

Screenshot of cascading select edit

Examples

Suppose you already have a database with customer information. Your JIRA is not open to customers, but you would like to track which customer has put in which request. With this custom field, you point to that database, put in an SQL script that extracts the customers and you can start choosing the correct customer in your issues.

See also Screenshots

Installation

1. Copy the database-values-plugin-xxx.jar to WEB-INF/lib
2. Create a properties file jira-database-values-plugin-customfieldid.properties
3. Copy the properties file to WEB-INF/classes
4. If you use a different database then the one JIRA uses, then make sure you also add the database driver jar to JIRA.
5. Add a new custom field of type 'Database Values Selection Field' (You might want to do this before creating the properties file so you can know the custom field id)

FAQ

Where can I find the custom field id?

1. Go to the administration section of JIRA
2. Locate the "Issue Fields" section and select "Custom Fields"
3. Click on the "Configure" link for the custom field
4. Look at the url, it will end in customFieldId=10000, this is the custom field id

Version History

0.1 - Initial Version
0.1.1 - Bugfixing + i18n support
0.2 - Added cascading select edit type
0.2.1 - Add proper sorting in issue navigator.
0.2.2 - Add support for ${jira.project.key}. Support for making custom field required.
0.2.3 - fixes for cascading select
0.2.4 - fixed for caching when using project key dependency
0.2.5 - NPE fixed

Screenshots

Screenshots
Searching on the database values	Editing the database value	Viewing the database value
Editing the value with AJAX-style	Editing the value with a cascading select	Editing the value with a cascading select
Sorting the values in the issue navigator