Why SQL+JQL?

The SQL+JQL Driver is a convenient way to access to Jira data for reporting. SQL is the second language most widely used in the World and it is powerful and friendy at time.

It's a secure and reliable solution to access to Jira data at scale: it is used by thousands of companies for reporting and more.

SQL+JQL is not native SQL: whereas native SQL access to the Jira legacy database (Oracle, MSSQL, PostgreSQL, MySQL), SQL+JQL runs in the context of the calling user respecting Jira permissions in real-time and all the queries are resolved against the Atlassian's Jira Java API instead. Therefore SQL+JQL queries are independent if the Jira legacy database vendor and they will continue working with no changes if the Jira legacy database is migrated to another vendor or the data model changed by Atlassian.

It is not only a secure and long term solution: SQL+JQL is 100% standard SQL supporting JQL to search for issues. it combines the best of both languages with no drawbacks.

Furthermore, SQL+JQL supports all the Jira editions (Core, Software and Service Desk) for reporting avoiding users to corrupt Jira data as it is read-only: INSERT, UPDATE and DELETE are aborted. Only SELECT is supported. 

Therefore is possible to create almost any Jira report with SQL+JQL.

Subversion ALM plugs Subversion into SQL+JQL. In other words, it is possible to make any Jira report including essential Subversion data like commits and items related to issues.

As SQL+JQL is standard SQL you can create Subversion-based reports and run them remotely from any custom or third-party reporting tool compatible with standard JDBC.

There are extensions available on the Marketplace for powerful data visualizations based on SQL+JQL, so it is possible to create reports relating Jira and Subversion data and add them to Jira DAshboards and Confluence pages.

What kind of reports could you do?

With SQL+JQL you can create most powerful Subversion reports integrated with Jira. The SQL+JQL Driver is packed within the Subversion ALM plugin and installed automatically by Jira behind the scene, requiring zero configuration.

Now it is possible making reports for (examples):

• Subversion contributions to issues: Who, when and what changed in Subversion due to the Jira issue → analyze work logs, status, etc.
• Release scopes: What commits are in a version and what items have been changed in the release
• Board analysis: get commits in sprints, versions, epics or the entire board and their story points, etc
• Analysis of commits by Jira projects and their components
• Create Subversion reports and add them to Jira Dashboards and Confluence pages via the built-in SQL+JQL table viewer gadget, BIRT reports or to your custom JDBC-based applications.
• Share tables with your colleagues to view data always up to date
• And much, much, more!

The SVN Database Schema in SQL+JQL

Subversion ALM contributes to the SQL+JQL data model with its own schema, named SVN. It is created by Subversion ALM right with the SQL+JQL Driver when the plugin starts.

Tables

REPOSITORIES

This table is intended for checking the Subversion repositories' health in real-time.

It allows to search for registered Subversion Repositories by ID and check their status: The ERROR column displays the latest error message happening during the last indexing process, or NULL value if no error happened.

The COMMITS column displays the amount of commits already indexed (might be different from the real amount of commits in the repository).

Every Jira user (logged in) can read this table, so it does not displays the repository URL.

COMMITSWITHISSUES

It allows to get the issues related to any commit and the commits with issues for Subversion-based reporting on Jira.

• REPOSITORYID: The ID of the registered repository (required in most cases)
• REVISION: The revision number
• AUTHOR: the author of the commit
• TIMESTAMP: date+time of the commit
• COMMENT: The comment of the commit
• ISSUEID: The ID of the issue related to the commit

The SQL+JQL Driver checks whether the user has permissions to read the issue and Subversion commits (View Development Tools permission) if enabled on Subversion ALM. before return any result to the user.

ITEMSWITHISSUES

It returns items (file and directory paths) having some Jira issue related

• REPOSITORYID: The ID of the registered repository (required in most cases)
• REVISION: The revision number
• ACTION: A capitalized character for the action on the item in the revision: A(dded), M(odified), D(eleted) or R(replaced)
• ITEM: The file or directory path in the Subversion repository
• MAXITEMS: The max records (items) to return. It's a sort of SQL LIMIT clause useful to avoid OutputMemoryException  when for large commits with a thousand of items versioned.

SQL+JQL example for Subversion

This query will list all commits related to current assignee's issues not resolved yet:

SELECT 
 iconType(jql.issue) AS "Type", 
 link(jql.issue, '_blank') AS "Issue", 
 iconPriority(jql.issue) AS "Priority", 
 issueStatus(jql.issue) AS "Status",  
 LEFT(summary(jql.issue),25) AS "Summary",
 R.NAME as "SVN Repository",
 anchor(absoluteurl('/secure/SWCTopMenuAction!default.jspa?jsp=revisionDetails&repoId=' || CI.REPOSITORYID || '&location=/&rev=' || CI.REVISION), CI.REVISION, CI.COMMENT, '_blank') as "SVN Revision",
 CI.AUTHOR as "SVN author",
FORMATDURATIONNATURAL(DATEDIFF('MILLISECOND',CI.TIMESTAMP, CURRENT_TIMESTAMP() )) as "SVN Date"
FROM 
 JQL  
  JOIN
 SVN.COMMITSWITHISSUES CI ON CI.ISSUEID = JQL.ISSUEID
  JOIN
 SVN.REPOSITORIES R ON R.ID = CI.REPOSITORYID
WHERE 
 JQL.QUERY = 'assignee = currentUser() AND resolution is EMPTY '