Description

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() ' 

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:

SELECT 
 *
FROM
 JQL
WHERE
 JQL.ISSUEID = 11022

Built-in HTML functions

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

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

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
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:

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.

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

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

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

SELECT 
  * 
FROM 
  ISSUEFIELDDEFINITIONS 
ORDER BY    NAME