What follows is a longer, worked example of using the database picker in case any concepts need reinforcing.
We will attempt to implement a country picker, using the various customizations outlined on the Database Picker Customizations page. For our source of countries, we can use https://github.com/ghusta/docker-postgres-world-db - which is a docker image containing both PostgreSql and a sample database that contains a list of countries of the world.
The sample database represents any external database - possibly your sales, CRM database, configuration management database, etc. But using this container should allow you to follow along with this tutorial using any non-production Jira instance.
First of all run the docker container:
docker run -d -p 5432:5432 ghusta/postgres-world-db:2.4
This should give you a postgres instance listening on
localhost:5432. For Windows users, this will be
<docker-ip>:5432. See the documentation for running docker on Windows.
Next, in your Jira instance, set up a Resource pointing to this database. The username and password are taken from the instructions at https://github.com/ghusta/docker-postgres-world-db.
Click Preview to make sure that you can connect. If there are problems, check the hostname in the JDBC URL, and validate that the docker container is working properly.
There is only a single table that we will make use of initially -
country. Get a feel for the shape of the data by entering the following SQL in the
SQL field and pressing Preview.
select * from country
We’re only going to be interested in a few of the columns here, but there are many other columns you could use for interesting exercises.
Save the resource so that it’s available to be used in scripts and picker fields.
Country Picker Field
Let’s start with a basic Country Picker field.
As a reminder, for a database picker, we need to provide two queries.
One specifying what search to do based on what the user has typed in the field, and a unique identifier to be saved in the Jira database.
A second one that, given the unique identifier, will retrieve the record to display to the end-user. It also serves to validate the ID (particular required when using the REST API to create issues, where the picker is not shown).
So our first decision is what will uniquely identify a country using this table. Let’s go for the 2-letter country code which is an ISO standard - this is in our table as
code2. Meaning, if the country name changes (it happens), it won’t affect our stored data. Normally, a database table would be expected to have an auto-generated numeric counter, but this one doesn’t. Where possible, always use a unique counter, as customers and products etc undergo name changes over time.
The attribute that we will search on according to what the user types will be the country name.
Given the above, our two queries will be:
select code2, name from country where code2 = ?
select code2, name from country where lower(name) like lower(?) || '%'
Both the country and search query are lower-cased to avoid the end-user having to worry about the capitalisation of country names.
You should end up with the field configured like the following. You can test it before creation by entering any valid issue key and clicking Preview.
Now, add this field to a screen so you can test it. Verify basic operations such as searching, and updating an issue:
Customizing the Appearance
Setting a drop-down icon
This is a perfectly serviceable country picker, with a data source that can be modified outside of Jira. However, we can jazz it up a bit by showing the country’s flag, which might aid selection and recognition.
As discussed in Database Picker Customizations, we can modify picker field behaviour using the Configuration Script.
Browsing the internet, we find that there is a site where you can get any flag by the two-letter country code - for example Zimbabwe - https://flagpedia.net/data/flags/small/zw.png.
Let’s set the icon in the drop-down:
Here we just return a URL that extracts the
code2 field from the object representing the database row for each record.
Enter this in the Configuration Script, either inline, or into a file that you point to:
Update the field and verify we see the icons:
Using a file rather than an inline script is far faster to develop with, as any changes will be reflected instantly. There is no need to keep updating the field configuration, and if you have set it up properly, you could also attach a debugger.
The Snippets drop-down contains a number of templates for commonly-used customisations, as a starting point.
Customising the view
When viewing the issue, we could make the selected value a bit more informative.
We specify the HTML to be displayed by implementing the
renderViewHtml closure. We want it to display as shown:
Note, that will we will show the continent, which we can get from the database table. So we will adjust the retrieval query to also return the
select code2, name, continent from country where code2 = ?
We can put any number of other columns after the first two (which have special significance), that we can use in our customizations.
See the complete listing at the end of this page for the necessary
OutputFormatter.markupBuilder avoids the possibility that a bad actor could modify the linked database, insert
<script> tags into the
continent column, and conduct an XSS attack. Read more on avoiding XSS attacks.
You may have data which would be unmanageable to search on using a single attribute. For instance, customers and their orders. In this case, the end-user might find it easier to use if they could first select the Customer, then select from the Orders relevant only to that Customer.
In our example, we will allow the user to choose the continent, then the country, showing only relevant countries. (This is purely for the purposes of an example, if we did want a Country Picker field, choosing the continent would just be an irritation)
Let’s start by creating a standard select list containing options for each continent, which we can get from our database using:
select distinct continent from country order by continent
Now, we will modify our code to implement the
getSearchSql closure. We’ll get the value of the Continent custom field, and use it in the SQL. If it hasn’t been set, then no countries will be available. Optionally you could just ignore it in this case so all countries are shown.
Optionally, you could include validation that both fields match. Whether you implement validation is really dependent on whether the other fields are just intended to be a guide, or whether they really must match.
To validate the continent matches use:
To experiment with disabled values, add a column to the
country table representing whether the country is disabled or not:
ALTER TABLE country ADD COLUMN "disabled" BOOLEAN DEFAULT FALSE;
Then as discussed in Disabled Values, modify your SQL to take our additional column into account:
Full Configuration Script Listing