Page Comparison

...

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 (3 required items)

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!

...

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

...

In Jira, the primary key has 4 identifiers, by necessity of being a remote app: APPLINKID, SPACEKEY, KEY, BASELINE.

Confluence 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

Jira database schema

In Jira, there are 4 identifiers for requirements, since we add the APPLINKID.

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

• AO_42D05A_DBREMOTEREQUIREMENT:

  • The list of requirements.

  • Identified by APPLINKID, SPACEKEY, KEY, BASELINE,

  • FLAGDELETED: Boolean, the requirement is not shown if true.

• AO_42D05A_DBISSUELINK

  • ISSUEID: The primary identifier of issues

  • ISSUEKEY: Text only, FYI and not necessarily up to date (e.g. if the issue was moved),

  • RELATIONSHIP: A free verb to characterize the link to Confluence,

  • REQUIREMENT_ID: The ID of the table AO_42D05A_DBREMOTEREQUIREMENT.

• AO_42D05A_DBAUDITTRAIL:

  • A sequential list of changes on issue links,

  • They are serialized and stored in the JSON column,

  • This table is regularly purged depending on the options chosen in the administration.

• AO_42D05A_DBQUEUE: Queue messages

• AO_42D05A_DBBACKUPITEM: Import/export feature

• AO_42D05A_DBBACKUPMAPPING: Import/export feature

• AO_42D05A_DBAPPLINK: Configuration details of this applink