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

Version 1 Next »

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.

Understanding the basics of our schema model

Table prefix

  • Prefix in Confluence: AO_32F7CE

  • Prefix in Jira: AO_42D05A

  • Prefix for PSEA in Confluence: AO_B895AB

All our tables are stored with the prefix “AO_32F7CE” corresponding to the plugin key in md5.

Columns are uppercase (and may require quotes)

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

Our unique keys are SPACEKEY - KEY - BASELINE

Despite a technical ID available in all tables, which is a Long, we almost always use the 3 values above to reference requirements. Don’t omit to include all 3 parameters, even if the baseline is often null!

Example: 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