Versions Compared

Version Old Version 3 New Version Current
Changes made by

Hung Techlead

Hung Techlead

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Requirements:

  1. Be able to assign an SQL DataSource Table as the primary source of truth (source of most up-to-date TGNG data)

    1. Update the Jira CUSTOMFIELDTABLE DB value according to the DB Table value (On Issue load and API access event)

      1. check if JQL search and Jira Issue Export trigger any of our methods (pull data periodically to keep the Jira version fresh?)

  2. The feature should be an extension of the Data Mirror functionality

    1. Add a button to allow users to pick whether they want the Data Mirror table to be the source of truth

    2. Add a checkbox labelled “Treat DataSource value as primary during sync (overwrites Jira value)” (or other wording that would sound better)

Use Case (Data Flow):

  1. The user defines an SQL data source

  2. In the Grid config, the user sets up Data Mirror and checks the box to make it the primary storage

    1. An info panel with table handling restrictions is displayed (Do not change the table schema or it will break)

    2. A checkbox is presented labelled something like “Treat DataSource value as primary during sync (overwrites Jira value)”

  3. Any pre-existing data is mirrored to the DB Table (as in Data Mirror)

  4. Any changes in the DB Table after that are reflected on Issue View and when REST API is called

    1. If an error occurs because of DB Table data type incompatibility (or any other SQL-related data), the error message should include that information

  5. Any changes by the user (on issue or via REST/Java API) are first reflected in the table and then saved to the default Jira storage

    1. If the save is not possible due to the change in the DB Table, an error should be displayed detailing the cause

  6. The JQL search is using the most up-to-date values that are taken from the DB Table

Plan of action:

  1. Check how TGE handles a similar functionality

  2. Implement a naїve version of the functionality (always overwrites on sync, doesn’t handle getCustomfieldValue, etc.)

  3. Validate whether all of the points in the Questions are relevant to the actual behaviour of the Grid in that basic implementation version.

  4. Write potential answers/suggestions to the questions with some detail on the implementation.

  5. Get approval on the clarifications from Questions

  6. Agree and document the timescale of the full implementation, and add it to the overall roadmap.

  7. Implement a more thorough version of the functionality

Questions:

...

Question

...

Does this functionality need to be present on Cloud as well as on Server

...

Hung: There is no restriction so I think we can do for both

...

How the TGE manages the getCustomfieldValue call from Jira’s own API? Do we provide the data pulled from the table on that call? Is it a Service that TGE exports?

...

Hung: We keep auto_increment ID on both side, and use another field “UUID” to manage the data to synchonize.

With the data save from FE, we can use library like uuidv4

WIth the data save from database, we can use function UUID() from mysql

Code Block
CREATE TABLE MyTable (
    id INT AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) NOT NULL DEFAULT (UUID()),
    name VARCHAR(255),
    age INT
);

...

If the DB Table sourced rows contain non-standard Row IDs, do they get transformed and re-inserted? Can we make the correct format of the Row ID be populated on the SQL side?

...

Hung: the same answer as above

...

Should we use a different format for Row IDs that are primarily stored in a remote DB?

...

Hung: UUID is an option that I suggest

...

Should the case of initialising TGNG from a pre-existing table be supported?

...

Hung: Yes, that is also a case that Francis suggest when we talk about bulk import

...

Do we handle Attachment columns by just storing the Attachment ID?

...

HUng: Tested on current Data Mirror function, it will save the Attachment ID with the value property

...

Do wrong/non-existing Attachment ID values get ignored or do we add a custom error? Does the same behaviour apply to wrong/non-existing values on User/Multi/Single-select?

...

Hung: With the current behaviour, it will ignore, so do User/Multi/Single-select. But to make it more effective, we can display a #error like Excel

...

Info

This document outlines the bidirectional synchronization feature in TGNG, explaining its purpose, functionality, and key considerations for users.

Table of Contents
minLevel1
maxLevel6
outlinefalse
stylenone
typelist
printabletrue

Overview

The Bidirectional Data Synchronization feature enables seamless two-way data exchange between Table Grid Next Generation (TGNG) and an external database. This allows users to maintain up-to-date information across both systems efficiently.

Key Benefits

  • Automatic Data Synchronization: Keep your grid data and database in sync without manual intervention.

  • Customizable Sync Rules: Choose whether database values should overwrite grid values.

  • Improved Data Integrity: Minimize discrepancies by ensuring data consistency.

How It Works

  1. Enable Bidirectional Synchronization in the grid settings.

  2. Configure the Data Source to specify which database table will sync with the grid.

  3. Select Sync Mode:

    • Grid as Primary: Changes in the grid update the database.

    • Database as Primary: Database updates override grid values.

  4. Automatic or Manual Sync: Choose between real-time updates or scheduled batch synchronization.

Flowcharts

1. Synchronize Bidirectionally Data Mirror (ODBC)

Drawio
mVer2
zoom1
simple0
inComment0
custContentId293634077
pageId291340289
lbox1
diagramDisplayNameUntitled Diagram-1736765866887.drawio
contentVer2
revision2
baseUrlhttps://tablegrid.atlassian.net/wiki
diagramNameUntitled Diagram-1736765866887.drawio
pCenter0
width601
links
tbstyle
height561

2. Disconnect Synchronize Bidirectionally Data Mirror (ODBC) → Data Mirror function as normally

Drawio
mVer2
zoom1
simple0
inComment0
custContentId292848080
pageId291340289
lbox1
diagramDisplayNameUntitled Diagram-1736766899314.drawio
contentVer1
revision1
baseUrlhttps://tablegrid.atlassian.net/wiki
diagramNameUntitled Diagram-1736766899314.drawio
pCenter0
width601.0000000000003
links
tbstyle
height440.99999999999994

Configuration Steps

  1. Navigate to Grid Settings → Data Mirror.

  2. Enable Bidirectional Sync and select the preferred sync mode.

Image Added

  1. Define Sync Rules:

    • Map grid columns to database fields.

  2. Save Settings and start synchronization.

Common Questions

1. How is it different from the current Data Mirror function?

The current Data Mirror function only allows saving grid data to an external database for viewing purposes.

With Data Mirror Synchronization, you can edit data directly in the external database, and the changes will be reflected in the Grid UI.

2. What databases are supported?

Most major SQL databases (PostgreSQL, MySQL, SQL Server, Oracle.) are supported.

3. What are the limitations?

  • Currently, if data is saved from the Grid UI and later deleted from the external database, it will not be automatically removed from the Grid.

  • Each time you update data in the external database, you must reload the grid in the UI to see the latest changes.

With this feature, users will gain enhanced flexibility in managing data synchronization between Jira and external databases. By ensuring data integrity and providing a configurable source of truth, TGNG aims to streamline workflows and improve efficiency. Future updates will refine and expand upon this functionality based on user feedback and evolving needs.

For further assistance, refer to our Support Portal.