...
But for the purposes of this tutorial, worklog information is just a nice example of something in the Jira database which you'd like to query in an interactive manner.
Implementation
Here we assume you are using Postgres.
Which Confluence SQL plugin?
is an overview of what we're going to od:
- Install a Confluence plugin capable of rendering SQL query results in a page
- Configure a read-only Postgres account to query database with
- Configure our plugin
- Display sample table results in Confluence
- Create our real timesheet query, and render that.
Choosing a Confluence SQL plugin
For this tutorial we are using the free Play SQL Base plugin. You could alternatively use PocketQuery or SQL for Confluence, which are in fact better plugins overall - in particular, they let you restrict who can run SQL queries, whereas Play SQL can't. This tutorial uses Play SQL Base because it's tall the free Play SQL Base plugin. You could alternatively use PocketQuery or SQL for Confluence, which are in fact better plugins overall - in particular, they let you restrict who can run SQL queries, whereas Play SQL can't. This tutorial uses Play SQL Base because it's what I had available. We will restrict SQL queries at the Postgres layer, which is a good thing to do anyway.
...
Configure Play SQL Base
In Confluence, type 'gg', 'Find new apps' and install the free Play SQL Base plugin.
In Confluence spaces you will now see a new 'Tables' menu item. Here is the page from a live Confluence instance, with various queries already defined (there's one from the Automatically deactivating inactive Jira users report):
...
Click 'Manage Connections and Permissions' and set up the database connection. In my instance my space's connection delegates use a Global datasource:
...
Clicking 'General Admin' shows the global config:
Creating a Postgres read-only account
At this point we're about to tell Play SQL how to connect to our database. We should probably do it with a dedicated read-only Postgres user. Also, since Jira might contain sensitive information irrelevant to our reports, we don't want to grant access to all Jira tables.
...
Code Block |
---|
root@jturner-desktop:~# PGUSER=confluence_reports PGPASSWORD=confluence_reports PGHOST=localhost PGDATABASE=redradish_jira psql -tAc "select count(*) from queries.sample;" 5 root@jturner-desktop:~# PGUSER=confluence_reports PGPASSWORD=confluence_reports PGHOST=localhost PGDATABASE=redradish_jira psql -tAc "select count(*) from public.jiraissue;" ERROR: permission denied for table jiraissue |
Define a Datasource in Confluence
There are two ways to tell Play SQL (and other SQL plugins) how to connect to a database:
...
Expand | ||
---|---|---|
| ||
|
Configure PlaySQL with the Datasource
To recap, we've just been on a detour to create a read-only Postgres account, and edited Confluence's conf/server.xml
file to define our QueriesDS
datasource. Now configure Play SQL to use the Datasource. Here I've configured QueriesDS as our 'global connection', which Play SQL uses by default:
Create a test Play SQL Table
Now return to the 'Tables' tab in a space:
...
Warning |
---|
Did we mention Play SQL Base is free? It is free, but also buggy, and at this point the bugs are very evident. The best we can say is that if you power on, it does work in the end.:
If you power on, it does work in the end. Don't complain - the Play SQL author makes his money from Play SQL Spreadsheets, not Play SQL Base - we're fortunate to have a free, roughly functional plugin at all. |
Create the real timesheets query.
We're not going to dwell too much on the specifics of our query. Here it is:
Include Code | ||||
---|---|---|---|---|
|
I suggest creating a directory in your Confluence app dir for SQL queries like this:
Code Block |
---|
/opt/atlassian/jira/current # mkdir SQL_QUERIES
/opt/atlassian/jira/current # cd SQL_QUERIES/
/opt/atlassian/jira/current/SQL_QUERIES # |
Then you can fetch the SQL directly using curl
:
Code Block |
---|
/opt/atlassian/jira/current/SQL_QUERIES # curl -sLOJ 'https://github.com/redradishtech/jira-interesting-sql-queries/raw/master/worklog_monthly.sql'
/opt/atlassian/jira/current/SQL_QUERIES # head worklog_monthly.sql
-- A giant table of worklog hours per day, for each day of the month, selectable by user, year and month
-- https://www.redradishtech.com/display/~jturner/2019/12/19/A+monthly+worklog+report+within+Confluence?moved=true
create schema if not exists queries;
create or replace view queries.worklog_monthly AS
select * from (
select user_name, email_address, year, month
, round(sum(sum),2) AS month_total
,case sum("1") when 0 then 0 else round(sum("1"),2) end AS "1"
,case sum("2") when 0 then 0 else round(sum("2"),2) end AS "2"
,case sum("3") when 0 then 0 else round(sum("3"),2) end AS "3"
/opt/atlassian/jira/current/SQL_QUERIES # |
and run it in your database:
Code Block |
---|
root@jturner-desktop:~# PGUSER=jira_ro PGPASSWORD=jira_ro PGHOST=localhost PGDATABASE=redradish_jira psql psql (12.2 (Ubuntu 12.2-4)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. redradish_jira=> \d redradish_jira=> select^C redradish_jira=> \conninfo You are connected to database "redradish_jira" as user "jira_ro" on host "localhost" (address "127.0.0.1") at port "5432". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) redradish_jira=> select * from jiraissue ; ERROR: permission denied for table jiraissue redradish_jira=> \q root@jturner-desktop:~# PGUSER=jira_ro PGPASSWORD=jira_ro PGHOST=localhost PGDATABASE=redradish_jira psql psql (12.2 (Ubuntu 12.2-4)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. redradish_jira=> \conninfo You are connected to database "redradish_jira" as user "jira_ro" on host "localhost" (address "127.0.0.1") at port "5432". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) redradish_jira=> select * from jiraissue; ERROR: permission denied for table jiraissue redradish_jira=> \q root@jturner-desktop:~# |
...