Overview of DB Client

When accessing the DB Client page, you will see 3 fields ...

Apps

Even if we are aware about the Database model of different deployed plugins, like me, you should not know exactly the the AO Prefix for a plugin (AO_123456_).

Generally, to find a table of a plugin, we have to find the correct AO prefix by accessing the System page Database Tables (from Plugin Data Storage menu item with the URL is <jiraHost>/plugins/servlet/active-objects/tables/list). And you know that more you have plugins and large instance, the load of this page is quite long since for each found tables, a SELECT COUNT(*) is done ... A pleasure !


With Apps Select Box will help you to filter Tables per Apps ...

You see listed different entries :

  • Core (Core) will filter all Tables issued from Jira Core
  • AO Plugins (AO_) will filter all Tables issued from all plugins with Active Objects
  • One entries for each plugin with Active Objects : Plugin Name (AO_123456_)  .


You will also able to perform some filtering by entering a part of plugin name.

Depending on the retained filtering on Apps Select Box, you will see in Tables Select Box only Tables of selected Apps.


Note that the MetaData is loaded and kept in cache. If you deploy a new plugin with new Active Object tables, you will have to force the Refresh Meta Data.

Tables

Now that your able to filter Tables based on Apps and also find more quickly the expected table.

But, if you may opt for another alternative by switch to the advanced search mode ...

It will allow to enter a term and see the tables filtered by Table Name and/or Columns Name.

Below, we can identify table where the term user_name is used ...

SQL Statement

If you are aware with the Database Model, you switch to SQL Statement field directly without selecting Apps and/or Table, and write the expected SQL statement.

If you have selected a specific Table, an additional section appears : SELECT and WHERE Clauses Select box, where all columns of selected Table are displayed.

You select columns in each of these lists to help you to write more quickly your SQL Statement ...

     

You have just to adapt you query and execute it.

 

You see in this sample, that the WHERE Clause has been corrected in order to have a valid SQL Statement since the settings Quote Object's name is not yet applied on WHERE Clause, but only on SELECT Clause (future correction slightly smiling face )

fromto
NAME = '?'


"NAME" like 'SSD%"



Note also that all executed SQL Statements are memorized ! See this details by clicking on SQL History ... 

You will be able to see :

  • Executed SQL Statement,
  • Row count of its execution (for any query Select, Insert, Update or Delete)
  • Related Status (Blue in case of Success, Red in case of Error) 

By clicking on the relevant row, you will see your SQL Statement copied to the SQL Statement field, ready for a new execution.

Now you are ready to discover Jira Database Model and enhance your support capacities.


  • No labels