Database items

This page explains the operating principle and the configuration of Connected items.

Principle

As explained before, the values of a Connected item are generated in 2 steps:

1. A query in the external datasource, the output is a result set
2. A template is applied on each row of the result set, the output is
   
   • a list of options - in case of a Connected item of type "Text - Select list"
   • a text string - in case of a Connected item of type "Text - Read only"

Performance

• For performance purposes, Elements Connect will only read the first 1,000 rows of a result set. To access the subsequent row, use the userInput parameter
• A 5-minute, non-configurable, cache for the datasource exists. Identical queries will fetch the Connected item values directly in the cache

Different ways to configure query

In Connected item configuration page, you can either configure your query manually, or use our visual tool to navigate among your datasource structure and create your query more easily.

SQL mode

Visual mode

Configuration

We'll illustrate the configuration of a Connected item with the "Printer" example presented below:

In the example above, we have 3 Connected items connected to a Configuration Management Database (CMDB):

• Printer: all the active printers owned by the company, listed in a select list
• Office: office location of the selected printer, displayed as read only text
• Printer details: details of the printer selected, displayed as read only text

Printers are listed in a DB table: PrintersView. Here is an abstract:

Printer

We want to display all the printers ID in a select list.

We create a Connected item of type "Text - Select list" with the following configuration:

Query

The SQL query is pretty basic, we get all the "ID" from the "PrintersView" table ordered alphabetically.

SELECT "ID"
FROM "PrintersView"
ORDER BY "ID"

The output of this query is a single-column table - here is an abstract:

The next step is to execute the template on each row of the result to get the item options.

Template

The template is written using the Apache FreeMarker™ templating language.
FreeMarker is an advanced templating language that offers both a rapid way to write simple templates along with powerful features that can be used to implement more advanced use cases.

The template is called once for every row of the result set. In other words, if the query returns 10 results the template will be called 10 times.
In the template, the variable row gives us access to the current row. Columns are accessible through their name.

In our example, the result set contains one column: "ID", our template is:

${row.ID}

Office

"Office" is an item of type "Text - Read only" that displays the Office where is located the selected printer.

The associated configuration is:

Query

The value of this item depends on the value of the item "Printer", this dependency is visible in the query:

SELECT "Office"
FROM "PrintersView"
WHERE "ID" = $issue.connected_item_10001

The "Printer" item value is referenced with the variable $issue.connected_item_10001  where 10001  is the ID of the Printer item.

If no printer is selected, the variable is replaced by an empty text, and the query executed is:

SELECT "Office"
FROM "PrintersView"
WHERE "ID" = ''

When the printer HK-SALES-MS is selected, the query is evaluated to:

SELECT "Office"
FROM "PrintersView"
WHERE "ID" = 'HK-SALES-MS'

Do you want to learn more about dynamic queries? Read Writing dynamic queries with dependencies

Template

Used when the query returns something, this template is mandatory.

The result set is available with the variable resultSet. It's an array that contains all rows of the result set. In our example, the query returns 1 row with 1 column. Our template is:

${resultSet[0].Office}

References to variables start with $ and are enclosed by brackets: { and }.
We want to access the first item of the resultSet array, because the numbering starts at 0 we use this notation: resultSet[0].

Do you want to learn more about templates? Read Writing templates with Apache FreeMarker

No result template

Used when the query returns an empty result set, this template is optional.

In our example we want to ask the user to select a printer, our template is:

<span style="color:#999;">Please, select a printer</span>

Fine tune your templates with HTML

You can add more impact to your Connected item by using HTML and CSS code in your templates.
For example, you can emphasize part of the text with bold text, attract reader attention with red text, or even use all the components provided by Atlassian through the Atlassian User Interface framework (AUI).

No Javascript
For security reasons, it's not allowed to use Javascript in a template. Any javascript code will be removed and won't be executed.

XSS attack prevention
To prevent from XSS attack any HTML, CSS, or Javascript code that may be returned by the datasource is escaped when used in a template.

Printer details

This item is very similar to the "Office" item, except that its template is more advanced.

The associated configuration is:

Query

The query of "Printer details" is very close to the query of "Office", except that it returns more columns.
It has a dependency on the "Printer" item.

SELECT "Brand", "Location", "MonthlyDutyCycle", "Image", "Support", "MaintenanceEnd", "MaintenanceStart"
FROM "PrintersView"
WHERE "ID" = '$issue.connected_item_10001'

Do you want to learn more about dynamic queries? Read Writing dynamic queries with dependencies

Template

This template is more advanced than the previous one: it contains HTML and CSS.

We also take advantage of the Apache FreeMarker™ templating language to generate a dynamic "Maintenance flag". A different message with a different style if the maintenance end date is reached or not.

<#-- The result set can return only one row -->
<#assign printer = resultSet[0]>

<#-- Custom style  -->
<style>
  div.printer {
    display: flex;
    margin-top: 10px;
  }
  
  div.printer table.aui td:first-child {
    font-weight:bold;
  }
  
  img.printer-image {
    max-width: 180px;
    max-height: 250px;
  }
</style>
<div class="printer">
  <span>
      <img src="${printer.Image}" class="printer-image"/>
  </span>
  <span>
    <table class="aui">
      <tbody>
        <tr>
          <td>Brand</td>
          <td>${printer.Brand}</td>
        </tr>
        <tr>
          <td>Location</td>
          <td>${printer.Location}</td>
        </tr>
        <tr>
          <td>Monthly duty cycle</td>
          <td>${printer.MonthlyDutyCycle}</td>
        </tr>
        <tr>
          <td>Maintenance</td>
          <td>
            From ${printer.MaintenanceStart} to ${printer.MaintenanceEnd}
            <#-- Maintenance end date: is it reached or not? -->
            <#if (.now?date gte printer.MaintenanceEnd?date)>
              <br/><span class="aui-lozenge aui-lozenge-error">Out of maintenance</span>
            <#else>
              <br/><span class="aui-lozenge aui-lozenge-success">Under maintenance</span>
            </#if>
          </td>
        <tr>
          <td>Support</td>
          <td><a href="tel:+${printer.Support}">+${printer.Support}</a></td>
        </tr>
      </tbody>
    </table>
  </span>
</div>

We can pay particular attention to:

• line 2: <#assign printer = resultSet[0]> to assign the first element of the result set to a local variable: printer.
• line 44: <#if (.now?date gte printer.MaintenanceEnd?date)> to compare the value of the maintenance end date to the current date and thus display the appropriate maintenance status

Do you want to learn more about templates? Read Writing templates with Apache FreeMarker

No result template

We use the same template as for "Office".

Request with more than 1000 results

Our select lists can display a maximum of 1000 results. If your query retrieves more than 1000 options, you have two solutions to filter your results:

• Writing dynamic queries with dependencies
• Use the $userInput variable

$userInput variable

Let's take the example of a datasource retrieving a list of cities in the United States. There are more than 1000 cities, and we only have access to their name

Without the $userInput variable, the query would be:

SELECT "NAME" 
FROM "Cities"

With this query, you would get all the cities from Abbeville to Flowood. If in your ticket, you wanted to select Tallahassee, it wouldn't be possible.

If we add a condition using the $userInput, the query would be:

SELECT "NAME" 
FROM "Cities"
WHERE "NAME" ILIKE '$userInput%'

Directly on the Connected item select list, what you type will be taken into account in the query and will retrieve the wanted data.

For example, if you start by typing 'tal', the query would be:

SELECT "NAME" 
FROM "Cities"
WHERE "NAME" ILIKE 'tal%'

And the results associated

You would now be able to select "Tallahassee"

What's next

• Writing dynamic queries with dependencies
• Writing templates with Apache FreeMarker