Fixing the awful SQL-level usability of AO_* tables

To those of us who are not Java developers, but Jira administrators, the Jira database has become a bit of a nightmare thanks to ActiveObjects. My local Jira database contains 1116 tables with inscrutable names like AO_013613_WOMBLES_V2 whose origin and purpose I cannot easily tell. The sheer quantity, lack of grouping, unhelpful names and need to quote everything due to UPPERCASE NAMES makes direct SQL querying painful.

To help those of us writing direct SQL queries, I’ve written some SQL-generating SQL which generates SQL views over AO_* tables:

First, a user-friendly name for each hash is looked up (e.g. AO_013613 -> ‘tempo’) from a giant csv table. A schema is created per plugin (e.g. ‘tempo’). For each each plugin table, a view is created with lowercased names and columns.

So now instead of writing:

select "ID", "LEAD" from public."AO_AEFED0_TEAM_V2";

one can write:

select id, lead from tempo.team_v2;

These plugin views can be refreshed as new plugins are added, and dropped too.

I hope others find this useful.

8 Likes

Not an administrator but can see how this could be useful. Thanks for sharing this here Jeff! :smiley:

My heart is filled with pride for the fact that you have included AO_687793 :heart_eyes:

3 Likes