Installation

From Subversion ALM 9.0.0 version the Driver must be installed manually from the Marketplace. It is FREE for Subversion ALM customers, therefore ignore the UMP message requiring a license for the Driver. In other words, now is optional.

In previous versions (8.x) the Driver was packed within Subversion ALM and automatically deployed behind the scene. It could not be uninstalled.

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.

The 7.9 version of SQL+JQL supports custom workflow conditions and validators, so it is possible to customize your Jira workflows by using Subversion data

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

REPOSITORIES
Column	ID	NAME	COMMITS	ERROR
Type	BIGINT	VARCHAR	BIGINT	VARCHAR
Indexed	x

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

COMMITSWITHISSUES
Column	REPOSITORYID	REVISION	AUTHOR	TIMESTAMP	COMMENT	ISSUEID
Type	BIGINT	BIGINT	VARCHAR	TIMESTAMP	VARCHAR	BIGINT
Indexed	x	x				x

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

ITEMSWITHISSUES
Column	REPOSITORYID	REVISION	ACTION	ITEM	MAXITEMS
Type	BIGINT	BIGINT	VARCHAR	TIMESTAMP	BIGINT
Indexed	x	x

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:

Example

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 '

Browser not supported