Database
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 Setup Connection URL & User.
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 (IssueSYNC 1.x.x).
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 Fields Types & 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 Track 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 (IssueSYNC 1.x.x).
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 Copy all vs only new 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 Problems / 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 Manage Background 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 /wiki/spaces/SYNTWO/pages/771424442.
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).
- 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.
- 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).
- Outgoing Response Job fetch response related data from A0_6D516B_QUEUE_OUT and sends response to JIRA which started synchronization.
- 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.