Database

Owned by Łukasz Krupa (Unlicensed)
Last updated: Oct 11, 2018 by Marcin Kokoszka (Unlicensed)
5 min read


IssueSYNC provides new tables to the database schema:

Queues can be browsed directly in database (use your favorite DB client):

  • AO_6D516B_QUEUE_IN
  • AO_6D516B_QUEUE_OUT

There is also a log queue that contains more details about communication:

  • AO_6D516B_QUEUE_LOG

AO_6D516B_CONNECTION

This tabel contains information about connections to remote JIRA instances you are using. Please refer to Connection.


Schema:

  • ID
  • CONNECTION_NAME
  • LAST_TEST
  • OUT_OF_SYNC
  • PASSIVE - 
  • PROXY
  • LOCAL_AUTH_KEY - local JIRA instance authentication key
  • REMOTE_AUTH_KEY - remote JIRA instance's local authorization key
  • REMOTE_JIRA_TYPE - specifies if remote JIRA instance is server or cloud type
  • REMOTE_JIRA_URL - URL of remote JIRA instance you want to synchronize with
  • ALTER_BASE_URL - alternative URL of remote JIRA instance you want to synchronize with
  • USERNAME - username of technical user
  • PASSWORD - encrypted technical user password

AO_6D516B_CONTRACT

This table contains information about your contracts. Please refer to Contract.


Schema:

  • ID
  • CONNECTION_ID - specifies connection to which this contract belongs
  • CONTRACT_NAME
  • CONTRACT_TYPE - depreciated
  • PROJECT_TYPE - specifies project type e.g. "software" or "service_desk" (since IssueSYNC v1.6)
  • ISSUE_TYPE - id of issue type, which this contract affects
  • JQL_CONSTRAINTS - additional JQL constraints provided for contract
  • PROJECT_ID
  • REMOTE_CONTEXT_NAME - remote contract name
  • STATUS - specifies if the contract is enabled or disabled
  • ATTACHMENTS - specifies if attachment synchronization has been enabled
  • ADD_PREFIX_TO_ATTACHMENTS
  • SYNCHRONIZE_ALL_ATTACHMENTS
  • COMMENTS - stores information about comments synchronization preferences (on, off, external tab, service desks internal, service desks shared)
  • ALL_COMMENT_RESTRICTIONS
  • ENABLE_EXTERNAL_COMMENT - specifies if you want to synchronize comments only from IssueSYNCs dedicated tab (depreciated since IssueSYNC v1.6, logic moved to COMMENTS)
  • SYNCHRONIZE_ALL_COMMENTS
  • FIELD_MAPPING - depreciated
  • WORKFLOW_MAPPING - id of workflow mapping provided in IssueSYNC configuration
  • WORKLOGS - specifies if you want to synchronize worklogs (only for IssueSYNC server version)

AO_6D516B_CONTRACT_FM

This table stores information about field mapping you have provided for contract. Please refer to Field Mapping.


Schema:

  • ID
  • CONTRACT_ID - specifies contract, which this field mapping associates with
  • LOCAL_FIELD_ID - JIRAs name of field
  • LOCAL_FIELD_NAME - field name/alias, which exposes to remote JIRA instance
  • REMOTE_FIELD_NAME - remote JIRA exposed field name

AO_6D516B_CONTR_EVENTS

This table stores information about field mapping you have provided for contract. Please refer to Triggers.


Schema:

  • ID
  • CONTRACT_ID - specifies contract, which this field mapping associates with
  • EVENT_ID - JIRAs id of event
  • EVENT_TYPE - specifies which action IssueSYNC should perform when event (form EVENT_ID) occurs e.g. CREATE, UPDATE

AO_6D516B_FIELD_MAPPING

This table stores field mapping schemas. Please refer to Field Mapping Templates.


Schema:

  • ID
  • NAME - name of field mapping schema

AO_6D516B_FIELD_MAPPING_ENTRY

This table stores details of field mapping schemas. Please refer to Field Mapping Templates.


Schema:

  • ID
  • FIELD_MAPPING_ID - relation to field mapping schema
  • LOCAL_FIELD_ID - JIRAs name of field
  • LOCAL_FIELD_NAME - field name/alias, which exposes to remote JIRA instance
  • REMOTE_FIELD_NAME - depreciated

AO_6D516B_COMMENTS

This table stores information about comments synchronization both build-in and from dedicated tab. Please refer to Comments & Attachments and Comments.


Schema:

  • ID
  • AUTHOR
  • BUILD_IN_COMMENT_ID - id of built-in comment id (if it wasn't created in IssueSYNCs dedicated tab)
  • COMMENT - comment body/ user input
  • COMMENT_TYPE
  • CONTRACT_ID - local contract id
  • DATE_INTERNAL - time of original creation in issue (by user)
  • DATE_EXTERNAL - time of creation in issue after synchronization (by technical user)
  • ISSUE_ID
  • REMOTE_COMMENT_ID
  • SYNC_ISSUE_ID

AO_6D516B_REMOTE_CONTRACT

This table stores information about remote contract configuration. Please refer to Field Mapping Templates.


Schema:

  • ID
  • CONNECTION - local connection id, to which this remote contract belongs
  • CONTRACT - remote contract name
  • ATTACHMENTS_ENABLED - specifies if remote contract enables attachment synchronization
  • COMMENTS_ENABLED - specifies if remote contract enables comment synchronization
  • WORKLOGS_ENABLED - specifies if remote contract enables worklogs synchronization
  • CREATE_ENABLED - specifies if remote contract enables issue creation
  • UPDATE_ENABLED - specifies if remote contract enables issue updates
  • DELETE_ENABLED - specifies if remote contract enables issue updates
  • HASH - this value defines when something has changed in remote contract configuration
  • UPDATE_DATE

AO_6D516B_REMOTE_FIELD_MAPPING

This table stores information about remote contract configuration. Please refer to Contract.


Schema:

  • ID
  • CONNECTION - local connection id
  • CONTRACT_NAME - remote contract name
  • FIELD_NAME - remote exposed field name/alias
  • FIELD_TYPE - type of remote field e.g. text field, textarea, user picker
  • UPDATE_DATE

AO_6D516B_REMOTE_WORKFLOW

This table stores information about remote workflow configuration. Please refer to Workflows and Workflow Synchronization.


Schema:

  • ID
  • CONNECTION - local connection id
  • IN_TRANSITION_TEXT- transition code exposed to remote JIRA instance
  • RESOLUTION - specifies if resolution should be set on transition (incoming from remote JIRA instance)
  • WORKFLOW_MAPPING_DISPLAY_NAME - name of workflow mapping schema available to choose on contract configuration
  • WORKFLOW_MAPPING_ID
  • UPDATE_DATE

AO_6D516B_SYNC_ATTACHMENT

This table stores information about attachments sycnhronziation. Please refer to Attachments and Comments & Attachments.


Schema:

  • ID
  • LOCAL_ATTACHMENT_ID - local attachment id
  • REMOTE_ATTACHMENT_ID - remote attachment id (created during synchronization process)
  • RESPONSE_MESSAGE
  • SYNC_ISSUE - relation to SYNC_ISSUE

AO_6D516B_SYNC_ISSUE

This table stores information about links between local and remote issue. Please refer to Synchronized Issues View.


Schema:

  • ID
  • CONTRACT_ID - local contract id
  • ISSUE_ID - local issue id
  • REMOTE_ISSUE_ID
  • REMOTE_ISSUE_KEY

AO_6D516B_QUEUE_IN:

This table stores IssueSYNC's messages that comes to JIRA. Please refer to Incoming configuration and Queues & Services.


Schema:

  • ID
  • CONNECTION_ID - local connection id related to the massage
  • CONTRACT_ID - local contract id related to the massage
  • CREATE_DATE
  • ISSUE_ID - id of local issue, which was/will be affected by he massage
  • ISSUE_KEY - key of issue, which was/will be affected by he massage
  • JSON_MSG - core information for IssueSYNC, contains change informations
  • MATCH_QUEUE_ID - id of remote QUEUE_OUT
  • MSG_TYPE
  • STATUS
  • UPDATE_DATE - when update has been performed

QueueIn Statuses:

  • NEW (0) 
    Message has arrived.
  • PROCESSING (1)
    Update after create scenario - waits until response with created issue id will return.
    Message is awaiting some other dependent messages. Status very rare to happen.
  • DONE (5)
  • ERROR (6)

  • RETRY (4) <- This one can be set manually (SQL Update Statement) to force resend of ERROR messages. *


AO_6D516B_QUEUE_OUT

This table stores IssueSYNC's messages that are send to remote JIRA instance. Please refer to Outgoing configuration and Queues & Services.


Schema:

  • ID
  • CONNECTION_ID - local connection id related to the massage
  • CONTRACT_ID - local contract id related to the massage
  • CREATE_DATE
  • ISSUE_ID - id of local issue, which was/will be affected by he massage
  • JSON_MSG - core information for IssueSYNC, contains change informations
  • MATCH_QUEUE_ID - id of remote QUEUE_IN (until outgoing job runs it will remain empty)
  • MSG_TYPE
  • STATUS
  • UPDATE_DATE
  • EVENT_DATE - when event provoked synchronization

QueueOut Statuses:

  • NEW (0) 
    Message has arrived.
  • SENT (2)
    Message sent to (or pulled by) the remote.
  • DONE (5)
  • ERROR (6)
  • RETRY (4) *


AO_6D516B_QUEUE_ARCHIVE

This table stores information about processed messages, which have been archived. Please refer to Archivization.


Schema:

  • ID
  • ISSUE_ID
  • ARCHIVED_DATE
  • CONNECTION_ID
  • CONTRACT_ID
  • CREATE_DATE
  • JSON_MSG
  • MATCH_QUEUE_ID
  • MSG_TYPE
  • QUEUE_ID
  • QUEUE_TYPE
  • STATUS
  • UPDATE_DATE

AO_6D516B_QUEUE_LOG

This table stores information about message processing and communication with remote JIRA instance. Please refer to Log Browser.


Schema:

  • ID
  • CONTRACT_ID - local contract id related to the log
  • ISSUE_ID - id of local issue related to the log
  • ISSUE_KEY - key of local issue related to the log
  • LOG_DATA - information about issue modifications
  • LOG_LEVEL
  • LOG_MESSAGE - message displayed to the user/administrator
  • MSG_ID
  • QUEUE_TYPE - defines if it was incoming or outgoing message processing
  • RESPONSE_STATUS - HTTP response code
  • CREATE_DATE

AO_6D516B_QUEUE_IN_DATA:

As well as AO_6D516B_QUEUE_OUT_DATA, AO_6D516B_QUEUE_LOG_DATA, AO_6D516B_QUEUE_ARCHIVE_DATA, AO_6D516B_COMMENTS.

It's present since IssueSYNC 2.5.0


Schema:

  • ID
  • JSON_CHUNK - piece of json string, which contains synchronization data
  • POSITION - position of JSON_CHUNK
  • REFERENCE TO original table - can be one of (QUEUE_OUT_ID, QUEUE_IN_ID, QUEUE_LOG_ID, QUEUE_ARCHIVE_ID, COMMENTS_ID)

AO_6D516B_JOB_STATISTIC

This table stores information about Services/Jobs executions. IssueSYNC uses it to execute jobs in configured interval. Please refer to Services.


Schema:

  • ID
  • LAST - timestamp of last job run
  • NAME - name/package of job

AO_6D516B_STATISTIC

This table stores information about statistics of IssueSYNC processes. Please refer to Queues & Services and Performance Dashboard.


Schema:

  • ID
  • CONNECTION - local connection id
  • CONTRACT_ID - local contract id
  • ATTACHMENT_SIZE - 0 if this statistic log was not about attachment
  • DATE
  • IN_OUT - specifies if it belonged to outgoing or incoming job.
  • MESSAGE_TYPE
  • MSG_SIZE - size of a message that was send to/ received from remote JIRA
  • PROCESSING_TIME

AO_6D516B_ALERT_HISTORY

This table stores information about IssueSYNC alerts. Please refer to Alerts.


Schema:

  • ID
  • LAST - time of last alert occurrence 
  • MESSAGE



Few words about the sync flow

Everything starts when event selected in contract configuration occurs. Changes are filter by field mapping and synchronization string is created. This data are saved in database and are waiting for Outgoing Job to be send. If you want to see what data will be sent look into database table(A0_6D516B_QUEUE_OUT).

  1. Outgoing Job send synchronization data stored in A0_6D516B_QUEUE_OUT to remote JIRA instance where data are saved in databse A0_6D516B_QUEUE_IN table. 
  2. Incoming Job fetch data in status NEW / RETRY and build internal rest request to JIRA REST API. Depends on result update status and send response to JIRA which started conversation(more technically save data in A0_6D516B_QUEUE_OUT table).
  3. Outgoing Response Job fetch response related data from A0_6D516B_QUEUE_OUT  and sends response to JIRA which started synchronization.
  4. Incoming Response Job fetch data from A0_6D516B_QUEUE_IN table in database related to responses and update status of synchronization data from initial A0_6D516B_QUEUE_OUT records from point 1.