Direct SQL access is not supported Accessing the database directly is neither supported nor covered by our agreement, because:
Please perform a backup of your data before editing. |
See also: Database schema for Confluence backups, Database schema for Jira backups
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.
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".
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
In Jira, the primary key has 4 identifiers, by necessity of being a remote app: APPLINKID, SPACEKEY, KEY, BASELINE.
AO_32F7CE_DBREQUIREMENT
Primary key: SPACEKEY, KEY, BASELINE,
STATUS: One of { ACTIVE, ARCHIVED, MOVED, DELETED },
STORAGETYPE, HTMLEXCERPT: The storage type indicates the indexing engine. 1 or null means LEGACY_JS, 2 means it’s the 2023 engine, 3 means XHTML (not rendered yet), 4 means it’s pure text. The HTMLEXCERPT is the description of the requirement.
NEWKEY, NEWSPACEKEY: If the status is MOVED, then this contains the target.
UPPERKEY: Predefined field for case-insensitive searches.
AO_32F7CE_DBPROPERTY:
TYPE can be INLINE (belongs to the requirement, since it’s the page) or EXTERNAL (losely associated IDs).
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.
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
A lot of tables have the following fields:
SCHEMAVERSION: An number corresponding to our ModelVersion, always ascending. Whenever we upgrade records to a new format, we update this field, so that we don’t work on the same entities twice. Not all records will have the latest SCHEMAVERSION, because upgrade tasks can ignore subsets of records depending on various criteria.
ID: Will be of type Long (64 bits) for all our modern entities, which start with “DB” (“DBREQUIREMENT”). Our old entities didn’t start with “DB” and only had shorter Integer (32 bits) identifiers, which was too short.
CREATEDUSER, LASTUPDATEDUSER: The userKey of the last person who created/updated the entity.
CREATEDDATE, LASTUPDATEDDATE: The date of creation and edition.