In the Administration
Requirement Yogi has a few usage metrics in the administration:
However, the API of Confluence doesn’t allow us to perform proper aggregations. So, if you are a database administrator, you may want to understand how the data is stored, to be able to extract information yourself.
Direct SQL access is not supported
Accessing the database directly is neither supported nor covered by our agreement, since:
Any upgrade may require that you change the way to extract data,
You may extract wrong data,
SQL is hard and you may make mistakes that erase or corrupt data.
Accessing the Requirement Yogi database in Confluence
All our tables are stored with the prefix “AO_32F7CE” (“AO_42D07A” in Jira, “AO_B895AB” for PSEA, it’s easy, it is the md5 of the plugin key).
Tip: Most databases assume that table names are lowercase, even when you type SELECT SPACEKEY, KEY, BASELINE FROM AO_32F7CE_DBREQUIREMENT. So you have to put double-quotes ("), or backticks in weird databases (`) : SELECT "SPACEKEY", "KEY", "BASELINE" FROM "AO_32F7CE_DBREQUIREMENT".
ID: Technical primary key
SPACEKEY*: The space key
KEY*: The key of the requirement
BASELINE*: The baseline (for ARCHIVED requirements only), or null (for ACTIVE only).
STATUS: ACTIVE, ARCHIVED, MOVED, DELETED.
Get the count of requirements by space and status:
select "SPACEKEY", SUM(CASE "STATUS" WHEN 'ACTIVE' THEN 1 END) "COUNT_CURRENT", SUM(CASE "STATUS" WHEN 'ARCHIVED' THEN 1 END) "COUNT_BASELINED", SUM(CASE "STATUS" WHEN 'DELETED' THEN 1 END) "COUNT_DELETED" from "AO_32F7CE_DBREQUIREMENT" group by "SPACEKEY" order by "COUNT_CURRENT" DESC ;
We’ll write more queries here, as customers ask.
Primary key: SPACEKEY, KEY, BASELINE,
STATUS: ACTIVE, ARCHIVED, MOVED, DELETED,
Primary key: RELATIONSHIP, PARENT_ID, CHILD_ID
PARENT_ID: ID of the DBREQUIREMENT,
If ORIGIN=true, this is the page where the requirement was created,
If ORIGIN=false, this is a page that cites the requirement, or a Jira issue that cites it, since it depends on the type.
The primary key for this table is … almost all columns, since metadata varies per type of link.
The list of saved traceability matrices,
Primary key: SPACEKEY, ORIGINALID
The queue (messages to Jira, indexation jobs or background jobs),
AO_32F7CE_DBBACKUPITEM and AO_32F7CE_DBBACKUPMAPPING:
Those are used as temporary storage tables when you export requirements to another instance of Confluence.
Descriptor of Jira apps registered in Confluence, and opposite.
Accessing the Requirement Yogi database in Jira
All our tables are stored with the prefix “AO_42D05A” in Jira.