SQL+JQL is built on top of the popular H2 database engine. H2 is a high-performance database, however, SQL+JQL resolves all the queries against the Jira Java API, checking users' permissions, etc.
Strings:
SELECT KEY(JQL.ISSUE) FROM JQL WHERE JQL.QUERY = ' status = "Open" ' |
Pay attention to the condition:
JQL.QUERY = ' status = "Open" '
In the query above:
"Open"
is a string in a JQL that will be parsed by Jira, whereas:
' status = "Open" '
is a string that will be parsed by the SQL+JQL engine.
Please ensure that you understood this section properly as this feature is essential to write SQL queries efficiently in many cases |
JQL performs much better than SQL+JQL. So, let Jira do as much work as possible → Move conditions into JQL as much as possible, instead of SQL.
Example:
SELECT * FROM issues i WHERE i.jql = ' reporter = currentUser() ' AND i.assignee = 'John Smith' |
The query above will get all the current user's issues. Then, the engine will check whether the assignee is John Smith for all the fetched issues. If the currentUser() reported N issues, checking the assignee by the SQL+JQL engine will occur N times.
However, the query below the SQL+JQL engine will rely on the Jira JQL engine to search for all the current user's issues assigned to John Smith which will return M records where M <= N.
SELECT * FROM issues i WHERE i.jql = ' reporter = currentUser() AND assignee = "John Smith" ' |
Please ensure that you understood this section properly as this feature is essential to write SQL queries efficiently in many cases |
I.e: how to get all the issues fixing any version of a project?
SELECT key(jql.issue) as "Issue" FROM PROJECTVERSIONS pv JOIN JQL ON query = 'fixVersion = ' || pv.id WHERE pv.projectid = 10001 |
Note that the JQL query is built dynamically:
'fixVersion = ' || pv.id
taken the project verion id as input.
The result of the above sentence is a text string that will be executed and resolved by the JQL table returning all the issues in that project version.
Read about the Performance