Issues

Overview

ISSUES

Column

ID

KEY

ASSIGNEE

CREATED

DESCRIPTION

Type

BIGINT

VARCHAR

VARCHAR

TIMESTAMP

VARCHAR

Indexed

x

x






Column

DUE

ENVIRONMENT

ORIGINALESTIMATE

PARENTID

PRIORITYID

Type

TIMESTAMP

VARCHAR

BIGINT

BIGINT

BIGINT

Indexed








Column

PROJECTID

REMAININGESTIMATE

REPORTER

RESOLUTIONID

RESOLVED

Type

BIGINT

BIGINT

VARCHAR

BIGINT

TIMESTAMP

Indexed








Column

SECURITYLEVEL

STATUSID

SUMMARY

TYPEID

TIMESPENT

Type

BIGINT

BIGINT

VARCHAR

BIGINT

BIGINT

Indexed








Column

UPDATED

VOTES

WATCHES

WORKRATIO

ISARCHIVED

Type

TIMESTAMP

BIGINT

BIGINT

BIGINT

BOOLEAN

Indexed








ColumnJQL (deprecated)



TypeVARCHAR



Indexedx




The ISSUES table is empty. It works in a similar way as the JQL table, but most of the ISSUE fields are available as columns, It accepts a JQL query in the ISSUES.JQL column to populate the table in real-time.

Performance

We are going to start with a common mistake. Most of the users might want to perform the query below for quick testing of the app:

Example 1
SELECT 
 *
FROM
 ISSUES 

BUMP!. You will get the following error message:

Why? SQL+JQL driver is ready for large Jira instance and Data Center having millions of issues. The query above simply have does not sense. We name it as a user's reckless query.

This kind of queries can compromise the Jira reliability and cause serious performance problems in production. Fortunately, SQL+JQL Driver is enough smart and analyzes how the table indexes are used by the query.


An index in SQL+JQL means that the column is supported by the underlying Atlassian's API in a straightforward way. Without those input data, it is not possible to invoke the Atlassian's  API, then the query is aborted showing the expected data by the Atlassian's API.

The error message above means that one of these singe columns (ID, KEY or JQL) must be provided to populate the table. Usually, they are provided via conditions in the WHERE clause or table JOINs conditions:

Example 2
SELECT
 * 
FROM
 issues 
WHERE 
 id = 10000 

This query above will show the fields of the issue with id = 10000.

ID

KEY

ASSIGNEE

CREATED

DESCRIPTION

DUE

ENVIRONMENT

ORIGINALESTIMATE

PARENTID

PRIORITYID

PROGRESS

PROJECTID

REMAININGESTIMATE

REPORTER

RESOLUTIONID

RESOLVED

SECURITYLEVEL

STATUSID

SUMMARY

TYPEID

TIMESPENT

UPDATED

VOTES

WATCHES

WORKRATIO

ISARCHIVED

JQL

10000

DEMO-1

admin

2019-11-17 19:18:55

This is your first task.

Issues are the things you do in a project. In business projects, issues are called tasks.

Types of tasks

A task can represent a document, a creative asset, a purchase and even a person.


Learn more

2019-12-20 00:00:00

null

0

null

3

50

10000

57600

admin

10000

2019-12-17 10:07:58

null

10001

This is your first task

10000

57600

2020-03-31 11:02:08

0

0

9223372036854775807

FALSE

null







The id condition does not have quotes because it is a numeric (integer) value.

Internally, the SQL+JQL Driver will get the issue id from the WHERE condition and will use it to invoke the Atlassian's API. This query will be performed very quickly taking very few milliseconds. 

Security


Example 3
SELECT
 * 
FROM
 issues 
WHERE 
 jql = 'reporter = currentUser()' 

The query above is really very interesting.

The JQL string (note that is enclosed with single quotes as expected by the standard SQL language) will be performed by the app by invoking the Atlassian's API and the Jira results will be coveted into the SQL record format with strong support for data types (string, numeric, dates, ...)

You are seeing the transformation of Jira JQL query into SQL records!.

Another interesting issue: Who is the currentUser()? this is very subtle concept. Of course, it is the logged in Jira user as you supposed. But, the currentUser() is a Jira concept and databases are not aware of it. However, SQL+JQL Driver is always able to get the logged user in Jira  (it runs under a sort of automatic and transparent Single Sign-On) and the query is performed against the Atlassina's API taking it in consideration to preserve the Jira data privacy and respecting Jira permissions under any circumstance: SQL+JQL runs in the context of the calling user.... always!

This is a feature supported in the core of the app regardless of the tool used to run the query and there is no way to bypass it. The app will always return data checking the logged in Jira user's permissions. If no logged-in user could be found, the Driver will return empty no records. Anonymous access is not supported. 

XSS code

It is important to sanitize the users' texts like summaries, descriptions, comments, etc. to avoid code injection. Use the built-in escape() function for that.