JIRA SQL Plugin

The SQL Plugin lets users run SQL predefined queries against the JIRA database, the results are rendered using velocity templates customised by an administrator.

The same template can be used as a portlet/gadget or a report, although generally additional information will be shown in the report, for instance the data table driving the chart.

img4d.jpg

Users cannot write their own arbitrary SQL queries as in a similar plugin for confluence, the risk of users retrieving hashed passwords etc or other restricted content is too great, and if you restrict access to solely administrators you'd defeat the purpose. Besides, the jira schema is complex enough that very few normal users are cluey enough to get the information they wanted.

A SQL report is not always the correct solution to your problem… certain types of query are better handled by the API. However for a quick report this is ideal as you do not need to write any code. Compared to using the API, direct SQL is particularly suited to aggregating large amounts of data.

For example the following two reports/portlets/gadgets show a treemap of issues by projects by components, and a history of votes satisfied by releases over time… both are quite easy with SQL but would be a hassle to create using the API, and database or Lucene-intensive.

img60.jpg 

Installation

Download the jar: sqlquery-11.jar and copy it to atlassian-jira/WEB-INF/lib (this is a plugins 1 plugin). Tested on 3.13 – 4.1.

Alternatively build the jar from source using "atlas-mvn package". 

All databases are supported, however not all the sample reports may work with all databases. If you want it to work you may have to modify the SQL. However the samples use very generic SQL, and have been tested with SQL Server, Postgres, MySQL and HSQL.

Restart JIRA. 

Usage

Sample reports are available so try them out before creating your own.

Rather than requiring lots of configuration, all reports must be under the path /templates/reports, on your classpath. So these can either be in a jar, as in the examples, or in the WEB-INF/classes /templates/reports directory.

Each report should be in its own directory, and the main velocity file must be called report.vm. The directory name is what is displayed in the report viewer so should be appropriately named. Example:

classes/templates/reports/
My Report/
		report.vm
		sql.vm 

My Report will be display in the report picker. The SQL file need not be called sql.vm. You can use as may templates as you like and #parse them in, but there must be one file called report.vm.

The Most Active Users Report is very simple containing no javascript or charts, so it's worth having a look at it.

The following variables and methods are available in the velocity context:

Variable / Method  Usage
$sqlUtil.getResults("sql statement") Runs the SQL and returns a list of Maps that you can iterate over to tabulate or chart the results.

To deal with differences between databases, the keys are always converted to upper case. Example:

#set ($results = $sqlUtil.getResults("select pname from project))
## this is the first result: $results.get(0).PNAME
$sqlUtil.getResults("sql statement", limitRows)  Same as above but only returns the top limitRows rows.

Normally you would do this in SQL with TOP or LIMIT which will be more efficient. This is only useful if you are targetting multiple DBMS types, as I am with the examples.

$sqlUtil.setSqlParams(listOfParams)   Set the parameters for any SQL parameter markers, for instance if you want the user to select a Project. The parameters are retrieved from the http request, so you pass the parameter names, not the actual values. Example:
$!sqlUtil.setSqlParams(["categoryId", "projectId"]) 
$reportName   Name of the report, eg "My Report"
$reportDir
Name of the report directory, eg "templates/classes/My Report
$reportPath
Full path to report.vm, eg "templates/classes/My Report/report.vm
$schemaName   Name of the schema as defined in entityengine.xml. You may need this in the SQL depending on DBMS type, eg:

select * from ${schemaName}.project
$dbTypeName   Name of the DBMS as defined in entityengine.xml, eg mssql, postgres72 etc.  Only useful if you are targetting multple DBMS types.
$isReport True when viewing as a report, false when as a portlet/gadget.
$isPortlet Converse of the above. 
$log Instance of logger, to log interesting things from the templates, eg failures

$log.error("Validation query failed for $reportName")
$componentManager   Instance of com.atlassian.jira.ComponentManager. Used for getting lists of projects and so on, to populate select lists for report parameters. See Project Category Breakdown report for an example.

 

InFAQ

Why are you using raw SQL and not the API to retrieve data?

As mentioned above, it's faster, easier, and more suited for particular tasks. In theory, using the API is supposed to insulate you against changes to the underly database schema. In practice the API changes dramatically and often, and the schema rarely. Additional tables or columns won't break an existing query, and if something needs to be rewritten it's a lot easier to change one SQL template than find the sources of a plugin, modify, compile, test and deploy.

Will SQL queries respect the permission or issue security schemes?

No. The intention is that you display aggregated data which would not reveal information about particular issues. This is another reason users cannot write their own SQL queries. If this is a problem you could create a view based on the jiraissue table that filters out sensitive projects, and use that in your queries rather than jiraissue.

If you are using this and security is a desperate problem contact me, and I may add report-level security.

What about SQL-injection attacks?

If you are accepting parameters from the user, as in the Project Category Breakdown example, you need to use parameter markers. Parameters are placed in the SQL using using ? markers, then set using

$!sqlUtil.setSqlParams(["categoryId"])

This will retrieve this parameter (categoryId) from the http request and use it in the query. Again, see the example.

Unfortunately at the moment you cannot set report parameters in dashboard portlets/gadgets.

You're not supposed to mix velocity and javascript.

Meh. Ideally not, but the mixing is minimal. The charts and tables should probably retrieve their data by getting JSON from a REST service, however I am trying to get something that works on both JIRA 3.13 and 4. Therefore I was avoiding 4.0 specific extensions.

Using object literal notation would certainly help with the fact that characters like a single-quote in results will produce invalid javascript unless you take care to escape them.

Some of these templates look a bit complex

Mostly because I was trying to write SQL that would work on SQL Server, Postgres, MySql and HSQL, plus demonstrate different possibilities, and provide demo data if you don't have any. In practice yours should be much simpler.

How can I make writing reports quicker?

Edit atlassian/WEB-INF/classes/velocity.properties on your development instance, and follow the instructions therein about velocity reloading. Then make a change to your report velocity, and reload in the browser.

And start with a very simple report, and build it up slowly. When you are happy with dataset choose a pretty visualisation from the Google gallery and plug it in. 

Why are you using Google Charts and not JFreechart?

It's much quicker to create a report, and they are far more versatile than JFreechart. Some of them are also interactive, e.g. you can pop up a sub-chart when a data slice is selected.

It also seems to fit with the JIRA 4 philosophy of moving as much work to the client as possible. 

31 comments to JIRA SQL Plugin

  • Jamie,

    Nice work!

    > In practice the API changes dramatically and often, and the schema rarely.

    Ouch.

    ~Matt

  • asaintprix

    I’ll have to give this a go. Looks interesting. As Matt said…nice work.

    -Adam

  • This was also useful as a starting point for seeing how to use Google Charts for my Timecharts plugin – ta.

    ~Matt

  • imario

    Hi!

    I tried your plugin and managed to make it work with a custom teplate/sql as report, but the plugin does not show up as portlet in my JIRA 4.1.1 installation.

    Does the portlet stuff not yet work with this JIRA version or is there something I am missing?

    Thanks!
    Mario

  • Hi Mario,

    It has been tested with 4.1.1 and it does work. Are you saying the reports work but portlets do not? Or that nothing works?

    Either way, are there any exceptions in the log?

    cheers, jamie

  • masterrwong

    Wicked reports! Will try the plugin. Thanks.

  • ohingardail

    We use this plugin extensively in my QA department to aggregate test coverage data in JIRA; it’s just exactly what we need – many thanks.

    However, I’ve never been able to get sqlUtil.setSqlParams to work properly; the example ‘Project Category Breakdown’ report, as well as any report I write myself, returns ‘Parameter metadata not available for the given statement’ in the JIRA logfile.

    I’m using JIRA 4.0.1 on MySQL 5.0.77. I don’t want to clutter up this blog with further corroborative data, but I have heaps of it…

    Any help appreciated – I’m more of a SQL person than a Java one so debugging the SqlUtil.java source file is a little beyond me, I’m afraid.

  • Hi ohingardail,

    This is undoubtedly due to either MySQL or the JDBC driver failing to provide parameter metadata. Can you let me know what driver you are using, I will try to reproduce. It may be that the db or driver just doesn’t support that and we’ll have to think of a workaround.

    cheers, jamie

  • Jamie,

    Thanks for your response. I don’t know exactly what version of JDBC I’m using (and don’t know how to find out!), although I know I am using sun-java 10.5.3 and JDK 1.6.

    I have already found a workaround; I think the problem was that I was using a multi-select HTML box and needed to store an array of multi-select values. Instead of using sqlUtil.setSqlParams to munge such data (which I think can only cope with one value – am I right?), I use $req.getParameterValues(“parameter”) which works just fine. $req is the velocity context instantiation of HttpServletRequest (http://jira.atlassian.com/browse/JRA-10222 , http://confluence.atlassian.com/pages/viewpage.action?pageId=157481 ).

    Thanks for your help,

    Adam.

  • davos29

    Hi Jamie,

    thanks for sharing this great plugin. We already created some custom reports, they look great.

    But now we are trying to use SQL parameter markers, but it doesn’t seem to work, we must be missing something.

    It seems that the parameter from the form doesn’t seem to be substituted for ‘?’ in the SQL.
    The SQL returns no result set.

    In the SQL at the top I have this code:
    #if ($!textutils.stringSet($!monthId))
    $!sqlUtil.setSqlParams(["monthId"])
    #end

    and later in the sql:
    #if ($!textutils.stringSet($!monthId))
    and extract(month from a.CREATED) = ? ## this doesn’t work, the value from $monthId is not substituted for ‘?’
    #else
    and extract(month from a.CREATED) between 5 and extract(month from curdate()) – 1 ## this is default and works
    #end

    I could also send you both files (report.vm, sql.vm) if you would have time to take a quick look at it.

    Also what I don’t know is how the substitution for 2 parameters works.
    In the documentation on this page, you have an example:
    $!sqlUtil.setSqlParams(["categoryId", "projectId"])
    how are these 2 values substituted in the SQL when you use ‘?’ in both places,
    how does the plugin know which parameter/variable value belongs to which ‘?’ ?

    David

  • acapin

    Hi!
    We’re currently looking at this plugin. It’s really great! However, we’ve encountered this error on the portlet when we try to run a report:

    “A portlet with the key {0} does not exist. ”

    However, if we try to run from the projects>reports>sql report, we didn’t encounter this problem.

    Did we do something wrong with our templates?

    Thanks.

  • gineer01

    I wonder if you have load-tested this plugin with SQL query that returns thousands of rows? With lots of users concurrently open the report/portlet?

    I’ve looked into the source code and I’m not very comfortable with using SQL directly. I tried using SQL for reporting once in the past. During development, it worked fine because I was the only user. Once deployed, it gave me SQLException all over the place. In particular, querying took forever if the query returned thousands of rows.

    If you have encountered that error and have fixed that, I’d love to learn the trick.

    Thanks,

  • Hello gineer01,

    A prerequisite of using this plugin is that you are happy to run SQL directly… if not it’s probably not for you. But I have tested it on a production database, if you run the query first in your SQL editor and that works, the plugin should work. It doesn’t do anything different. If you’re return 0000′s of rows and processing them in javascript then yeah, that could take time but you shouldn’t get sql exceptions. Maybe if you post more detail someone will help.

    cheers, jamie

  • danyx

    Hi Jamie,

    First of all it is a really nice plugin, congratulation for that and I absulutely agree with your motivation when you choosed SQL instead of API!

    I faced with a little annoying problem when I installed it under Jira 4.1.1, to be more concrete when I would like to use the dashboard portlet it seems that the portlet size is fixed and some contents are not viewable. It is not a big problem, but my customers love this dashboard thing… Do you have an idea how can I solve this problem? (I am not a plugin expert at all, but I have some knowledge about that)

    Thank you,
    daniel

  • gaston

    Hi!

    Great Plugin!, but we’re having some troubles:

    The report is working great but the portlet option does not appear at the gadget library, we’re working on two Jira instances, both 4.1.2, in one works and in the other no.

  • justin

    I love this plugin — especially the way you incorporate the Google Charts.

    My only problem I’ve had is the gadget output — the charts are getting cut off. You can see more here: http://lh3.ggpht.com/_kNgEiC_bnXk/TUCLA9oXpBI/AAAAAAAAAA0/IQv3w1wc-kg/s800/dashboard.PNG. Any ideas??

  • VtxDavid

    This is a great plug-in. It made it possible for users to run certain sql reports without having to actually write a report from scratch. It was very user friendly!

    I currently have upgraded Jira to v4.2 which is not yet supported. I am anxiously awaiting this plug-in being made compatible with the latest version so we can begin using it again.

  • h259bws

    Hi Jamie,

    Love this plugin. Great job on it!

    I’ve written a report that just does some complicated SQL (beyond what JQL can support) and presents the results in a grid. It appears fine when run as a report, but doesn’t render well when done as a portlet.

    The problems with the portlet are:
    1. Column width appears to be restricted to the size of the column heading. (Adding “width=xx%” to the or didn’t change this.)
    2. The height of the portlet doesn’t expand to either show all rows or accommodate paging. In my test environment, I had about 10 rows and it only showed 2 on the portlet.

    I’m using v1.1 under JIRA 4.0#466, and can send you screenshots and the report.vm/sql.vm if you’d like.

    Thanks,
    Betsy

  • Dave Maheshwari

    Hi Jamie,

    I see empty reports/portlets when running against Oracle 11g on a JIRA 402 server. I tried on a local box (HSQL) and things seems to be working fine but when i try on a pre-production server I see blank portlets and reports. Debug statement shows no exceptions as such

    Any idea whatz going wrong?

    Thanks
    Dave

    11-04-16 22:57:32,550 http-8080-Processor22 DEBUG dave 82652x167x1 9kmhw0 http://localhost:8080/secure/RunPortlet.jspa [onresolve.jira.sql.SqlUtil] sqlStmt: SELECT
    COUNT(*) AS KOUNT, userbase.username AS USERNAME
    FROM
    .changegroup
    INNER JOIN .userbase ON changegroup.AUTHOR = userbase.username
    GROUP BY userbase.username
    ORDER BY KOUNT DESC
    2011-04-16 22:57:32,551 http-8080-Processor22 DEBUG dave 82652x167x1 9kmhw0 http://localhost:8080/secure/RunPortlet.jspa [onresolve.jira.sql.SqlUtil] sqlParams: []

  • Dave, it’s likely to be that the schemename is required for oracle… have a look for $schemaName above.
    cheers, jamie

  • Dave Maheshwari

    For Oracle the schemename attribute is removed as per http://confluence.atlassian.com/display/JIRA040/Connecting+JIRA+to+Oracle#ConnectingJIRAtoOracle-4.ConfiguretheJIRAEntityEngine

    Sid would not work as well. Any other route?

  • Hrm, I use oracle 10g at my client, and we have schema-name=”name_of_jira_owner”… I take it the queries run properly in sqlplus or dbartisan or TOAD or whatever you use?

  • Dave Maheshwari

    yup, giving the user directly worked, … Thanks!!

  • Who IS John Galt?

    Love the plugin- it works great. But there are a couple of things I am just too new to know how to do:

    How do I use it in “portlet” mode?

    Also, not related to your plugin but the Google Charts API: for some reason I cannot get the API to show a stacked bar chart–can someone help debug the following- It shouws hte data in a single bar chart(not stacked) Thanks!

    function drawChart() {

    // Create our data table.
    var data = new google.visualization.DataTable();
    data.addColumn(‘string’, ‘Request Type’);
    data.addColumn(‘string’, ‘Key Drivers’);
    data.addColumn(‘number’, ‘Issue Count’);

    #foreach ($row in $results)
    data.addRow(['$row.REQUEST_TYPE','$row.KEYDRIVERS',$row.KOUNT]);
    #end

    // Instantiate and draw our chart, passing in some options.
    var chart = new google.visualization.ImageBarChart(document.getElementById(‘chart_div’));
    var chartTitle = ‘Key Drivers by Request Type’;
    chart.draw(data, {width: 400, height: 240, isVertical: true, is3D: true, title: chartTitle});

    var table = new google.visualization.Table(document.getElementById(‘table_div’));
    table.draw(data, {showRowNumber: false});
    }

  • Who IS John Galt?

    Update to previous post: I have been able to figure out the challenges I had with the Google stacked bar charts. It had to do with how I structured the data-I now have a really cool interactive chart.

    Still have the question about portlet mode- not sure what that means and how to use it. I’d like to make the sqlQuery chart available as a Gadget on a dashboard. Is this do-able?

  • Who IS John Galt?

    you can ignore my comment/questions above- I found the gadget- didn’t realize you had named it “SQL Portlet (Legacy)” – doh.

    I’m still having trouble getting it to actually launch any of the charts from the portlet- and not seeing any erros in the browser or in JIRA logs. BUt I’m sure it will be some blatently obvious solution once I get in and debug.

    THanks again!

  • Who IS John Galt?

    I was able to resolve the issue others reported with the auto-resize thing not working.

    In the \templates\core\portlet-parser.vm, I removed the function call to autoresize(). Like magic, the portlet version of my charts shows up perfectly.

  • Hi John/Who,

    I didn’t actually call it “… Legacy portlet”, jira must do that because it’s not a gadget. This is not really under active development atm, but it sounds like you have sorted out all the issues.

    cheers, jamie

  • h259bws

    Hi Jamie,

    Under JIRA v4.0 when trying to add the SQL portlet to a dashboard in IE8, it lets me add it but when I select a report and click Save, nothing happens.

    It appears the workaround cited here: http://www.minyaa.com/jira/browse/MYAA-519?focusedCommentId=18821&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#action_18821 resolves it, but I’m wondering whether you’ll be updating the plugin to fix this as well as Who is John Galt’s fix re: removing autoresize() function. Both of these changes make the plugin more feasible to use in a Production environment.

    Also, when will you be setting up a bug reporting project for this plugin?

    Thanks!
    Betsy

  • Hi Betsy,

    I plan to give this some TLC at some point. I’m interested in what people are using this plugin for, as I find it difficult to gauge interest.

    Issues can be reported here: https://studio.plugins.atlassian.com/browse/JSQL

    cheers, jamie

  • Who IS John Galt?

    I have just upgraded to JIRA 4.3 on my dev box and portlets that worked under 4.2 now fail and show the text “gadget.common.error.500″. Anyone finding this to be true?

Leave a Reply