1 Description
- 1.1 Example 2
2 Built-in HTML functions
- 2.1 Example: HTML
3 Embedding HTML
- 3.1 Embedding HTML
4 Security
- 4.1 Code injection
- 4.2 Escaping HTML code
5 Built-in field functions

Description

JQL

JQL
Column	ISSUEID	QUERY	ISSUE
Type	BIGINT	VARCHAR	OTHER
Indexed	x	x

The JQL table is the most convenient way to perform JQL queries and transforms JQL results into SQL records.

SELECT 
 JQL.ISSUEID AS "Id", 
 KEY(JQL.ISSUE) as "Key", 
 SUMMARY(JQL.ISSUE) as "Summary"
FROM
 JQL
WHERE
 JQL.QUERY = ' reporter = currentUser() '

Id	Key	Summary

Id	Key	Summary
11022	SSPA-23	As a user, I'd like a historical story to show in reports

The engine takes the JQL.QUERY string (VARCHAR) from the WHERE condition and passes it to the JQL table which resolves it against the Atlassian's public Java API for Jira. Then the JQL is populated with the records, one per returned issue id along with the Issue Lucene document in the JQ.ISSUE column.

The JQL table is dual. It is also able to get the issues from the JQL.ISSUEID column:

Example 2

SELECT 
 *
FROM
 JQL
WHERE
 JQL.ISSUEID = 11022

ISSUEID	QUERY	ISSUE

ISSUEID	QUERY	ISSUE
11022	null	[Issue object]

Built-in HTML functions

Some functions are provided to extract data from issues and build HTML elements:

Example: HTML

SELECT 
 LINK(JQL.ISSUE) as "Issue", 
 SUMMARY(JQL.ISSUE) as "Summary"
FROM
 JQL
WHERE
 JQL.ISSUEID = 11022

Issue	Summary

Issue	Summary
SSPA-23	As a user, I'd like a historical story to show in reports

Note that the LINK() function builds an HTML anchor element (link) to open the issue on the Jira Issue Detail View.

The LINK() function also admits optional parameters: LINK(JQL.ISSUE, TARGET) where the target param corresponds to the HTML anchor "<a>" element target and supports the same values: '_top' (default), '_blank', '_self', etc.

There are more useful HTML functions:

ICONTYPE(ISSUE)
ICONPRIORITY(ISSUE)
ISSUESTATUS(ISSUE)
LINKPROJECT(ISSUE, TARGET)

Example:

SELECT 
 linkproject(jql.issue, '_blank') AS "Project", 
 iconType(jql.issue) AS "Type", 
 link(jql.issue, '_blank') AS "Issue", 
 iconPriority(jql.issue) AS "Priority", 
 issueStatus(jql.issue) AS "Status"
FROM 
 JQL  
WHERE 
 JQL.QUERY = 'id = 11022'

Project	Type	Issue	Priority	Status

Project	Type	Issue	Priority	Status
Sample Scrum Project A		SSPA-21		Done

These functions allow displaying data with the same look&feel than Jira (AUI),

Embedding HTML

SQL+JQL supports HTML and Javascript in the output. This allows creating powerful (even interactive) GUIs:

Embedding HTML

SELECT
  '<span style="border-style:solid; border-color:lightgray; border-radius: 6px; ">' || ICONTYPE(JQL.ISSUE) || ' ' || KEY(JQL.ISSUE) || '</span>' as "Issue"
FROM
 JQL
WHERE
  JQL.QUERY = ' id = 11022'

In the example above, a rectangle has been added to around the issue with HTML tags.

Security

It's important to sanitize the users' texts to avoid script injection (XSS attacks!) The built-in function escape() does the job: issue summaries, descriptions, comments, etc. are subject to contain malicious code.

Code injection

SELECT 
  '<script>alert("XSS attack!")</script>' 
FROM DUAL

Escaping HTML code

SELECT 
  ESCAPE('<script>alert("XSS attack!")</script>') as "Escaped code"
FROM DUAL

Escaped code

Escaped code
<script>alert("XSS attack!")</script>

Built-in field functions

There is a bunch of built-in functions helping to extract fields from the JQL.ISSUE object, All those functions can be listed querying the ISSUEFIELDEFINITIONS table

ISSUEFIELDDEFINITIONS

ISSUEFIELDDEFINITIONS
Column	NAME	TYPE	DESCRIPTION
Type	VARCHAR	VARCHAR	VARCHAR
Indexed

SELECT 
  * 
FROM 
  ISSUEFIELDDEFINITIONS 
ORDER BY    NAME

NAME	TYPE	DESCRIPTION

NAME	TYPE	DESCRIPTION
archived	BOOLEAN	The issue archived status (Data Center)
assignee	VARCHAR	The issue assignee
cf	VARCHAR	The custom field value. Usage: cf(ISSUE, custom field id [,delimiter])
created	TIMESTAMP	The issue creation date
description	VARCHAR	The issue description
due	TIMESTAMP	The issue due date
environment	VARCHAR	The issue environment
estimated	BIGINT	The issue original estimate time
key	VARCHAR	The issue key
logged	BIGINT	The issue time logged (same as "spent")
parentid	BIGINT	The issue parent id (for subtasks)
priorityid	BIGINT	The issue priority id
priorityname	VARCHAR	The issue priority name
priorityurl	VARCHAR	The issue priority url
progress	BIGINT	The issue work progress
projectid	BIGINT	The issue project id
projectkey	VARCHAR	The issue project key
projectname	VARCHAR	The issue project name
remaining	BIGINT	The issue remaining estimate time
reporter	VARCHAR	The issue reporter
resolutionId	BIGINT	The issue resolution id
resolutionName	VARCHAR	The issue resolution name
resolved	TIMESTAMP	The issue resolved date
securitylevelid	BIGINT	The issue security level
spent	BIGINT	The issue time spent (same as "logged")
statusName	VARCHAR	The issue field status name
statusid	BIGINT	The issue field status id
summary	VARCHAR	The issue summary
typeid	BIGINT	The issue type id
typename	VARCHAR	The issue type name
typeurl	BIGINT	The issue type url
updated	VARCHAR	The issue updated date
votes	BIGINT	The issue votes
watches	BIGINT	The issue watches