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 | |||||
Column | JQL (deprecated) | ||||
Type | VARCHAR | ||||
Indexed | x |
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:
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:
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. | 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
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.
More about performance
Despite the Atlassian's API performance is good, fetching data and checking permissions might require some fractions of a millisecond. This might look like very few time, but when you are dealing with thousand of records, this fractions of time becomes noticeable and the query might require some seconds to be performed.
Since the 7.2 version the JQL table is supported to increase performance and simplify the queries a lot by reducing the number of joins required.
The ISSUES table has more than 20 columns that are always populated for each record, regardless they are read or not. This requires fractions of milliseconds, but remember that those small portions of time have a significant impact dealing with thousand of issues.
2 (milliseconds / issue) x 1000 (issues) = 2 (seconds)!
Furthermore, the database model is strongly normalized. Therefore it is required joining a lot of tables to write useful queries. These joined tables also invoke the Atlassian's API to fetch issues and their data summarizing more and more fractions of milliseconds. As result, you might get useful (it works) but annoying (it takes a very long time) query.
Example:
SELECT p.name as "Project", i.key as "Issue", cfv.value as "Custom field value" FROM ISSUES i JOIN ISSUECUSTOMFIELDVALUES cfv ON cfv.issueid = i.id AND cfv.customfieldid = 10000 //(2) JOIN PROJECTS p ON p.id = i.projectid //(3) WHERE i.JQL = ' reporter = currentUser() ' //(1)
The SQL+JQL query above will:
- Perform the JQL query. Imagine it returns 3000 issues.
- for each issue in (1) the value of the custom field with id = 10000 will be fetched by invoking the Jira API.
- for each issue in (1) its project object will be fetched from the API to get the project name.
Therefore, the Jira API will be invoked:
1 time to perform the JQL (~0.5 seconds)
3000 times to get the custom field values (~ 5 seconds). It requires build the issue Java object again for each id (which as done in (1) and the ask for their custom fields values).
3000 times to get the project name (~ 2.5 seconds). Just the same above here.
TOTAL ~ 8 seconds to be performed.
Too much slow? The API has been invoked ~ 6000 times, therefore it took 8000 ms / 6000 call = 1.3 ms per cal. It is not a bad performance, but fractions of milliseconds are important when dealing with thousands of records and join.
Now, have a look at the equivalent query using the JQL table
SELECT PROJECTNAME(JQL.ISSUE) "Project", KEY(JQL.ISSUE) as "Issue", CF(JQL.ISSUE, 10000) as "Custom field value" //(3) FROM JQL //(2) WHERE JQL.QUERY = ' reporter = currentUser() ' //(1)
The query above will take about 1.5 seconds only to be performed: 5 times faster compared with the previous using the ISSUES table.
What did it change?
- The JQL string is just the same: ' reporter = currentUser() '
- No joins. Only one table is invoked: it reduces the number of combinations performed by the database engine.
- the KEY() and CF() auxiliary functions extract the data from the JQL.ISSUE (Issue Java object). These objects have a lot of cached data about the issue, therefore fetching columns is very effective as the Jira API does not have to be invoked.
The metrics above have been provided by an organization with about one million issues in their Jira instance.