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!