• Rough draft

    • Data storage in the database using a datasource

    Owned by Anh Phuong Duong (Deactivated)
    Last updated: Mar 15, 2024 by Dam
    4 min read

    In this documentation you will learn how data are stored on Jira database, especially for string and date column type using TGNG app.

    You will also learn how data are stored on Jira database or on an external database using Data Mirror.

    In these use cases and examples we are using PostgreSQL Database for Jira Software 9.4.X but the SQL commands should run on almost all supported database systems.

    You can find all supported database systems in this Atlassian documentation: Supported platforms | Administering Jira applications Data Center 9.4 | Atlassian Documentation

    1st Use case: 1 row storage format in Jira Database

    What is the value of Test-1 issue who has a Dates custom field where the "Date Name" = Feature Done, "Date Value" = 15.07.2023 ?

    1. first create a test project with key = DEMO

    2. second create a grid with 2 column only:

      • Date name = string column type

      • Date value = date column type

    3. create an issue and grid data : 1 row =
      "Date Name" = Feature Done, "Date Value" = 15.07.2023

    4. On the Jira issue you should have something like that:

      image-2023-10-12-16-21-24-688.png

    5. connect to PostgreSQL database system, using Jira database, and run this SQL command:
      - issuenum=1 is the number of your issue

      SELECT id, customfield, textvalue FROM customfieldvalue WHERE issue= (SELECT id FROM jiraissue WHERE issuenum=1 AND project= (SELECT id FROM project WHERE pkey='DEMO'));

    6. Here is the result of this PostgreSQL command to show how data are stored in the database:

    { "ducketId":"8", "rows":[ { "rowId":"row_288c5098-de10-4257-9ed5-599dec6f497c", "columns":{ "datevalue":1689379200000, "order":0, "datename":"Feature Done" }, "order":0 } ] }

    2nd Use case: 2 rows storage format in Jira Database

    What is the value of Test-2 issue who has a Dates custom filed where the "Dates Name" = Feature Start Date and "Date Value" = 03.10.2023 and has another row with "Date Name" = Feature End Date and "Date Value" = 31.10.2023 ?

    1. Following the first example, this is the issue with 2 rows:

      image-2023-10-03-19-07-33-084.png

    2. connect to PostgreSQL database system, using Jira database, and run this SQL command:
      - issuenum=2 is the number of your issue

      SELECT id, customfield, textvalue FROM customfieldvalue WHERE issue= (SELECT id FROM jiraissue WHERE issuenum=2 AND project= (SELECT id FROM project WHERE pkey='DEMO'));

    3. Here is the result of this PostgreSQL command to show how data are stored in the database:

    3rd Use case: 1 row and 2 rows storage format using Data Mirror feature

    You can learn more about Data Mirror feature here: Data Mirror

    All data are stored in a table called testdate in the DB of your choice (here i'm using testtge Database), using column name from the grid config.

    To display the results, it a simple SQL command: