PROJECTS |
|
|
|
|
|
|
|
| |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Column | ID | KEY | NAME | DESCRIPTION | CATEGORY | LEAD | URL | COUNTER | ISARCHIVED | ARCHIVEDBY | ARCHIVEDON | ||||||||
Type | BIGINT | VARCHAR | VARCHAR | VARCHAR | VARCHAR | VARCHAR | VARCHAR | BIGINTIndexed | BOOLEAN | VARCHAR | TIMESTAMP | ||||||||
Indexed | x | x | x |
|
| ||||||||||||||
...
x |
How to get all the project issues? Given a JIRA project, there is not a straightforward way to get all its issues by using the JIRA Java API: there is not a Java method on the project object to get all its issues. The only way to get the project issues via the PROJECTS table joined with the ISSUE table
select * from projects p inner join issues i on API is by performing a JQL similar to:
project = SSPSQL for JIRA is pure and strict wrap of the JIRA API: it follows the same pattern and it has the same constraints than the API. Therefore there is no way to get all the JIRA issues at once by using SQL as well as there is no way to achieve it by using JQL!.
If you try the SQL query below a full scan exception will be raised:
select * from projects p inner join issues i on p.id=i.projectid
The It he query above is correct from the semantic point of view and most JIRA users would expect it shows would be allowed, it would fetch all the issues on from JIRA. Therefore, all the relational database systems (Oracle, MySQL, H2, PostgreSQL, SQLServer, etc) would execute it.
However, SQL for JIRA is different. The SQL query above might compromise the JIRA Server performance (CPU and memory resources).Therefore, it is aborted by the SQL for JIRA plugin by raising a FULL SCAN exception. Please, read the ISSUES table documentation in order to understand wayThis would work fine with small JIRA instances but it would probably crash large instances with lot of thousands issues.
This constraint makes SQL for JIRA ready for the masses as it cares of the JIRA users' mistakes aborting inefficient queries.
Even if you restrict the project to a single one:
select * from projects p inner join issues i on p.id=i.projectid where p.name='SSP'It still will raise a full scan exception because according to JIRA API pattern, the right way would be use a JQL for that:
select * from issues where JQL='project = SSP'As you see, SQL for JIRA respects the same constraints than JIRA in order to avoid to compromise the JIRA performance and by keeping the same good balance among features and performance.