Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Next »

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.

Disclaimer

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

Prefix: AO_32F7CE

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".

Table 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.

*Primary key

Usual queries

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.

Database schema

  • AO_32F7CE_DBREQUIREMENT

    • Primary key: SPACEKEY, KEY, BASELINE,

    • STATUS: ACTIVE, ARCHIVED, MOVED, DELETED,

    • HTMLEXCERPT, PROPERTIES

  • AO_32F7CE_DBDEPENDENCY

    • Primary key: RELATIONSHIP, PARENT_ID, CHILD_ID

  • AO_32F7CE_DBLINK

    • 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.

  • AO_32F7CE_DBTRACEABILITYMATRIX

    • The list of saved traceability matrices,

    • Primary key: SPACEKEY, ORIGINALID

  • AO_32F7CE_DBQUEUE:

    • 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.

  • AO_32F7CE_DBAPPLINK:

    • Descriptor of Jira apps registered in Confluence, and opposite.

Accessing the Requirement Yogi database in Jira

Prefix: AO_42D05A

All our tables are stored with the prefix “AO_42D05A” in Jira.

  • AO_42D05A_DBREMOTEREQUIREMENT

  • AO_42D05A_DBISSUELINK

  • No labels