Introduction
...
- Scrum board — for teams that plan their work in sprints
- Kanban board — for teams that focus on managing and constraining their work-in-progress
(View What is a board?)
Columns
| BOARDS |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| ID | BIGINT | X | The id of the board. |
| NAME | VARCHAR |
| The name of the board. |
| TYPE | VARCHAR |
| The type of the board. It could be either SCRUM or KANBAN. |
| SAVEDFILTERID | BIGINT |
| The id of the filter that determines which issues belong to board. |
| ISSPRINTSUPPORTENABLED | BOOLEAN |
| 'true' if the board supports sprints. |
| SHOWDAYSINCOLUMN | BOOLEAN |
| 'true' if the board shows days in column. |
| CARDCOLORSTRATEGYNAME | VARCHAR |
| The card color strategy name of the board. |
| SWIMLANESTRATEGYNAME | VARCHAR |
| The swimlane strategy name of the board. |
| BOARDFILTERS |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| ID | BIGINT | X | The id of the entity. |
| NAME | VARCHAR |
| The name of the entity. Must not be null or empty. |
| DESCRIPTION | VARCHAR |
| General description of the entity. May be null or empty. |
| OWNER | VARCHAR |
| Filter owner |
| QUERY | VARCHAR |
| Gets the query that defines the search that will be performed for this filter. |
| ISLOADED | BOOLEAN |
| 'true' if the filter has been loaded. |
| ISMODIFIED | BOOLEAN |
| 'true' if the filter has been modified. |
| FAVOURITECOUNT | BIGINT |
| The number of users who have marked this entity as one of their favourites. |
Examples
| Code Block |
|---|
| language | sql |
|---|
| title | Get boards |
|---|
|
SELECT *
FROM SOFTWARE.BOARDS
|
...
A board displays issues from one or more projects. You can know what projects are the issues your board shows.
(View Starting a new project, Leading an agile project)
Columns
| BOARDPROJECTS |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| PROJECTID | BIGINT | X | Project identifier |
| BOARDID | BIGINT | X | Board identifier |
Examples
| Code Block |
|---|
| language | sql |
|---|
| title | Get the boards for a project |
|---|
|
SELECT p.id as "Project Id.", p.key, p.name, b.id as "Board Id.", b.name, b.type
FROM SOFTWARE.BOARDPROJECTS bp
INNER JOIN PROJECTS p on p.id=bp.PROJECTID
INNER JOIN SOFTWARE.BOARDS b on b.id=bp.boardid
WHERE projectid = 10100 |
...
Versions are points-in-time for a project. They help you schedule and organize your releases.
Once a version is created, and issues are assigned to it, a Releases link will be available in your project navigation sidebar.
Assigning issues to versions helps you plan the order in which new features (stories) for your application will be released to your customers.
(View Configuring versions in a Kanban project, Configuring versions in a Scrum project)
Columns
| BOARDVERSIONS |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| BOARDID | BIGINT | X | Board identifier. |
| VERSIONID | BIGINT |
| Version identifier |
Examples
| Code Block |
|---|
| language | sql |
|---|
| title | Get versions for a board |
|---|
|
SELECT bv.boardid, pv.*
FROM SOFTWARE.BOARDVERSIONS bv
INNER JOIN PROJECTVERSIONS pv on bv.versionid=pv.id
where boardid=2 |
...
The vertical columns in both the Active sprints of a Scrum board and the Kanban board represent the workflow of your board's project.
The default columns in the Active Sprints of a Scrum board are To Do, In Progress, and Done.
The default columns on a Kanban board are Backlog, Selected for Development, In Progress, and Done.
(View Configuring columns, Configuring a board)
Columns
| BOARDCOLUMNS |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| BOARDID | BIGINT | X | Board identifier. |
| ID | BIGINT |
| Column Identifier. |
| NAME | VARCHAR |
| Column name. |
| POSITION | BIGINT |
| Column position. |
| MINIMUM | BIGINT |
| Minimum number of issues in column. |
| MAXIMUM | BIGINT |
| Maximum number of issues in column. |
| ISVALID | BOOLEAN |
| 'true' if column is valid. |
| ISVISIBLE | BOOLEAN |
| 'true' if column is visible. |
...
| BOARDCOLUMNPROGRESS |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| BOARDID | BIGINT | X | Board identifier. |
| STATUSID | BIGINT |
| Status identifier. |
| PROGRESS | VARCHAR |
| The breakdown of status mapped in the view to their respective Column Progress. |
Examples
| Code Block |
|---|
| language | sql |
|---|
| title | Get columns for a board |
|---|
|
SELECT *
FROM SOFTWARE.BOARDCOLUMNS
WHERE boardid=2 |
...
- Scrum board — the epics are not part of the set of issues.
- Kanban board — the epics are part of the set of issues.
Columns
| BOARDISSUES |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| BOARDID | BIGINT | X | Board identifier. |
| EPICID | BIGINT |
| Epic identifier. |
| ISSUEID | BIGINT |
| Issue identifier. |
Examples
| Code Block |
|---|
| language | sql |
|---|
| title | Get issues for Scrum board |
|---|
|
SELECT bi.boardid, b.name as "Boar Name", b.type, bi.epicid, i.id as "Issue Id.", i.key as "Issue Key", itd.name as "Issue Type", p.id as "Project Id.", p.name as "Project Name"
FROM SOFTWARE.BOARDISSUES bi
INNER JOIN SOFTWARE.BOARDS b on b.id=bi.boardid
INNER JOIN ISSUES i ON i.id=bi.issueid
INNER JOIN ISSUETYPEDEFINITIONS itd on itd.id=i.typeid
INNER JOIN PROJECTS p on p.id=i.projectid
WHERE boardid=2 order by i.key |
...
| Warning |
|---|
| title | Only for Scrum boards. |
|---|
|
The backlog is only functional for Scrum boards.
On Kanbam boards, the backlog is simply a board column that is mapped as a Backlog. So the table will not return results for Kanbam boards. |
Columns
| BOARDBACKLOGISSUES |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| BOARDID | BIGINT | X | Board identifier. |
| EPICID | BIGINT |
| Epic identifier. |
| ISSUEID | BIGINT |
| Issue identifier |
Examples
| Code Block |
|---|
| language | sql |
|---|
| title | Get the backlog issues of a board |
|---|
|
SELECT bbi.boardid, bbi.epicid, i.id as "Issue Id.", i.key as "Issue Key"
FROM SOFTWARE.BOARDBACKLOGISSUES bbi
INNER JOIN ISSUES i ON i.id=bbi.issueid
INNER JOIN ISSUETYPEDEFINITIONS itd on itd.id=i.typeid
WHERE boardid=2 |
...
A sprint — also known as an iteration — is a short period in which the development team implements and delivers a discrete and potentially shippable application increment.
Note, sprints do not apply to Kanban projects.
(View Running sprints in a Scrum project, Planning sprints)
Columns
| BOARDSPRINTS |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| ID | BIGINT | X | Sprint identifier. |
| BOARDID | BIGINT | X | Board identifier. |
| NAME | VARCHAR |
| The name of the sprint. |
| SEQUENCE | BIGINT |
| The number which determine sprints order in the backlog. Initially is equal to the sprint id. |
| STATE | VARCHAR |
| The state of the sprint. |
| ISACTIVE | BOOLEAN |
| 'true' if sprint is active. |
| ISFUTURE | BOOLEAN |
| 'true' if sprint hasn't been started yet. |
| ISCLOSED | BOOLEAN |
| 'true' if sprint has been completed. |
| STARTDATE | TIMESTAMP |
| The date when the sprint suppose to start. May be null if sprint hasn't been started. |
| COMPLETEDATE | TIMESTAMP |
| The date when sprint was completed. May be null if sprint hasn't been closed. |
| ENDDATE | TIMESTAMP |
| The date when the sprint suppose to end. May be null if sprint hasn't been started. |
| BOARDSPRINTISSUES |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| BOARDID | BIGINT | X | Board identifier. |
| SPRINTID | BIGINT | X | Sprint identifier. |
| EPICID | BIGINT |
| Epic identifier. |
| ISSUEID | BIGINT |
| Issue identifier. |
|
|
|
|
|
| X | Double index |
Examples
| Code Block |
|---|
| language | sql |
|---|
| title | Get all the sprints of a board |
|---|
|
SELECT *
FROM SOFTWARE.BOARDSPRINTS
WHERE boardid=2 |
...
Unlike sprints, epics often change in scope over time as a natural aspect of agile development. Epics are almost always delivered over a set of sprints.
As a team learns more about an epic through development and customer feedback, user stories will be added and removed to optimize the team's release time.
(View Epics, stories, versions, and sprints, Working with epics)
Columns
| BOARDEPICS |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| ID | BIGINT |
| The issue id of epic. |
| BOARDID | BIGINT | X | Board identifier. |
| KEY | VARCHAR |
| The issue key of epic. |
| NAME | VARCHAR |
| The epic's name. |
| SUMMARY | VARCHAR |
| the issue summary of epic. |
| ISDONE | BOOLEAN |
| Done status of the epic. If the epic is done it will not be displayed on board. |
| BOARDEPICISSUES |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| BOARDID | BIGINT | X | Board identifier. |
| EPICID | BIGINT | X | Epic identifier. |
| ISSUEID | BIGINT |
| Issue identifier. |
|
|
|
|
|
| X | Double index |
Example with dynamic JQL joining
| Info |
|---|
Some users have reported that the BOARDEPICISSUES table does not perform well. In this case, use dynamic JQL joining instead, to achieve the dame result: |
...
The example above returns all the issues belonging to an Epic in a given board.
Examples
| Code Block |
|---|
| language | sql |
|---|
| title | Get the epics from a board |
|---|
|
SELECT *
FROM SOFTWARE.BOARDEPICS
WHERE boardid=2 |
...
The "INFO*" tables return information about the own elements used by JIRA Software, such as custom fields and issue types.
These tables are useful when we want to extract only information from JIRA Software's own elements.
Columns
| INFOCUSTOMFIELDS |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| ID | VARCHAR |
| Element identifier text. |
| NAME | VARCHAR |
| Custom field name. |
| CUSTOMFIELDID | BIGINT |
| Custom field identifier. |
...
| INFOISSUETYPES |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| ID | VARCHAR |
| Element identifier text. |
| NAME | VARCHAR |
| Issue type name. |
| ISSUETYPEID | BIGINT |
| Issue type identifier. |
Examples
| Code Block |
|---|
| language | sql |
|---|
| title | Get JIRA Software's custom fields |
|---|
|
SELECT icf.customfieldid, icfd.name, icfd.type
FROM SOFTWARE.INFOCUSTOMFIELDS icf
INNER JOIN ISSUECUSTOMFIELDDEFINITIONS icfd on icfd.id = icf.customfieldid |
Image Modified
...
| Code Block |
|---|
| language | sql |
|---|
| title | Get the values of JIRA Software's custom fields for an issue |
|---|
|
SELECT icfv.issueid, icfv.CUSTOMFIELDID, icf.name, icfv.value
FROM SOFTWARE.INFOCUSTOMFIELDS icf
INNER JOIN ISSUECUSTOMFIELDVALUES icfv on icfv.CUSTOMFIELDID = icf.customfieldid
WHERE icfv.issueid=10200 |
...