This page constitutes random notes from my work day as an Atlassian product consultant, put up in the vague hope they might benefit others. Expect rambling, reference to unsolved problems, and plenty of stacktraces. Check the date as any information given is likely to be stale.
I spend plenty of time poking around in Jira databases. Something that has always mildy annoyed me is the proliferation of the AO_ tables that plugins create:
redradish_jira=#
List of relations
┌────────┬─────────────────────────────────────────────┬──────────┬────────────────┐
│ Schema │ Name │ Type │ Owner │
├────────┼─────────────────────────────────────────────┼──────────┼────────────────┤
│ public │ AO_013613_ACTIVITY_SOURCE │ table │ redradish_jira │
│ public │ AO_013613_ACTIVITY_SOURCE_ID_seq │ sequence │ redradish_jira │
│ public │ AO_013613_EXPENSE │ table │ redradish_jira │
│ public │ AO_013613_EXPENSE_ID_seq │ sequence │ redradish_jira │
│ public │ AO_013613_EXP_CATEGORY │ table │ redradish_jira │
│ public │ AO_013613_EXP_CATEGORY_ID_seq │ sequence │ redradish_jira │
....
└────────┴─────────────────────────────────────────────┴──────────┴────────────────┘
(1318 rows)
Of those 1318 tables (!?), 1116 begin with AO_.
I know these AO_ tables are associated with plugins, but I have no idea (short of Google searching) which plugin generated which tables.
Furthermore every search involving these tables requires quoting the table name and column names, because they inexplicably needed to be uppercase.
To make life easier, I've create a project that automatically creates nicely named views on top of the AO tables:
https://github.com/redradishtech/activeobject_views
I can now see my Jira database contains tables from 56 plugins:
redradish_jira=# \dn
List of schemas
┌────────────────────┬────────────────┐
│ Name │ Owner │
├────────────────────┼────────────────┤
│ agile │ redradish_jira │
│ agilepoker │ redradish_jira │
│ api │ redradish_jira │
│ atlnotifications │ redradish_jira │
│ automation │ redradish_jira │
│ backbonesync │ redradish_jira │
│ betterpdf │ redradish_jira │
│ configmanagercore │ redradish_jira │
│ dvcs │ redradish_jira │
│ dynaforms │ redradish_jira │
│ groovy │ redradish_jira │
│ hipchat │ redradish_jira │
│ inform │ redradish_jira │
│ issueactionstodo │ redradish_jira │
│ jeditor │ redradish_jira │
│ jeti │ redradish_jira │
│ jiradevint │ redradish_jira │
│ jiradiagnostics │ redradish_jira │
│ jiraemailprocessor │ redradish_jira │
│ jirainvite │ redradish_jira │
│ jiramail │ redradish_jira │
│ jiramobile │ redradish_jira │
│ jiraoptimizer │ redradish_jira │
│ jiraprojects │ redradish_jira │
│ jiratranstrigger │ redradish_jira │
│ jirawebhooks │ redradish_jira │
│ jmcf │ redradish_jira │
│ jqlt │ redradish_jira │
│ jsd │ redradish_jira │
│ jsu │ redradish_jira │
│ kepler │ redradish_jira │
│ labelmanager │ redradish_jira │
│ navlinks │ redradish_jira │
│ portfolio │ redradish_jira │
│ portfolioteam │ redradish_jira │
│ projtemplates │ redradish_jira │
│ public │ postgres │
│ queries │ redradish_jira │
│ saml │ redradish_jira │
│ securelogin │ redradish_jira │
│ servicerocket │ redradish_jira │
│ sil │ redradish_jira │
│ simpletasklists │ redradish_jira │
│ simplifiedplanner │ redradish_jira │
│ startwork │ redradish_jira │
│ streams │ redradish_jira │
│ structure │ redradish_jira │
│ support │ redradish_jira │
│ tempo │ redradish_jira │
│ tempo2 │ redradish_jira │
│ tempoplanner │ redradish_jira │
│ timedpromise │ redradish_jira │
│ webhooks │ redradish_jira │
│ whitelist │ redradish_jira │
│ workhours │ redradish_jira │
│ xporter │ redradish_jira │
└────────────────────┴────────────────┘
(56 rows)
If I want to see tables for a specific plugin, I can limit psql to just the plugin's schema:
redradish_jira=# set search_path=tempo;
SET
redradish_jira=# \d
List of relations
┌────────┬─────────────────────┬──────┬────────────────┐
│ Schema │ Name │ Type │ Owner │
├────────┼─────────────────────┼──────┼────────────────┤
│ tempo │ account_v1 │ view │ redradish_jira │
│ tempo │ activity_source │ view │ redradish_jira │
│ tempo │ budget │ view │ redradish_jira │
│ tempo │ category_type │ view │ redradish_jira │
│ tempo │ category_v1 │ view │ redradish_jira │
│ tempo │ customer_permission │ view │ redradish_jira │
│ tempo │ customer_v1 │ view │ redradish_jira │
│ tempo │ exp_category │ view │ redradish_jira │
│ tempo │ expense │ view │ redradish_jira │
│ tempo │ favorites │ view │ redradish_jira │
│ tempo │ hd_scheme │ view │ redradish_jira │
│ tempo │ hd_scheme_day │ view │ redradish_jira │
│ tempo │ hd_scheme_member │ view │ redradish_jira │
│ tempo │ internal_issue │ view │ redradish_jira │
│ tempo │ link_v1 │ view │ redradish_jira │
│ tempo │ location │ view │ redradish_jira │
│ tempo │ membership │ view │ redradish_jira │
│ tempo │ permission_group │ view │ redradish_jira │
│ tempo │ pgp_group │ view │ redradish_jira │
│ tempo │ pgp_group_to_team │ view │ redradish_jira │
│ tempo │ pgp_group_v2 │ view │ redradish_jira │
│ tempo │ pgp_member │ view │ redradish_jira │
│ tempo │ pgp_member_v2 │ view │ redradish_jira │
│ tempo │ pgp_permission │ view │ redradish_jira │
│ tempo │ pgp_permission_v2 │ view │ redradish_jira │
│ tempo │ program │ view │ redradish_jira │
│ tempo │ project_config │ view │ redradish_jira │
│ tempo │ rate │ view │ redradish_jira │
│ tempo │ rate_table │ view │ redradish_jira │
│ tempo │ saved_report │ view │ redradish_jira │
│ tempo │ saved_report_v2 │ view │ redradish_jira │
│ tempo │ team │ view │ redradish_jira │
│ tempo │ team_link │ view │ redradish_jira │
│ tempo │ team_member │ view │ redradish_jira │
│ tempo │ team_member_v2 │ view │ redradish_jira │
│ tempo │ team_permission │ view │ redradish_jira │
│ tempo │ team_role │ view │ redradish_jira │
│ tempo │ team_to_member │ view │ redradish_jira │
│ tempo │ team_v2 │ view │ redradish_jira │
│ tempo │ user_index │ view │ redradish_jira │
│ tempo │ user_location │ view │ redradish_jira │
│ tempo │ wa_sl_value │ view │ redradish_jira │
│ tempo │ wa_value │ view │ redradish_jira │
│ tempo │ wl_scheme │ view │ redradish_jira │
│ tempo │ wl_scheme_day │ view │ redradish_jira │
│ tempo │ wl_scheme_member │ view │ redradish_jira │
│ tempo │ work_attribute │ view │ redradish_jira │
└────────┴─────────────────────┴──────┴────────────────┘
(47 rows)
I hope this helps fellow SQL hackers out there!