Worklog Attribute Related Database Tables

Owned by Deniz Oğuz
Last updated: Apr 09, 2020
2 min read

Since some of our customers are writing various integration scripts we want to explain a little bit our database table structure, so that they can use this information to debug their scripts.

 

AO_7B9828_ATTR_TYPE: Definition of worklog attribute types in the system

  • ID primary key. Referred from other tables like AO_7B9828_ATTR_WORK_LOG and AO_7B9828_ATTR_VALUE.

  • NAME is the name of attribute type such as “Billable”, “Work Type” etc.

  • TYPE is of one SINGLE_SELECT, NUMBER, BOOL, DURATION. If it is SINGLE_SELECT possible values are defined in AO_7B9828_ATTR_VALUE table.

  • DESCRIPTION is the description of the attribute type.

  • WIDTH is the pixel width provided for the entry of attribute value on the UI.

  • REQUIRED if TRUE user should provide a value when entering a worklog.

  • FONT_CLASS is a font icon value from Font Awesome.

AO_7B9828_ATTR_WORK_LOG : This table stores which worklog has which attributes.

  • ID primary key.

  • WORK_LOG_ID is ID of the worklog from WORKLOG table.

  • ATTR_TYPE_ID is ID of attribute type from AO_7B9828_ATTR_TYPE table.

  • ATTR_VALUE is value of the attribute. For BOOLEAN attributes it can be “yes” or “no”. For NUMERIC attributes it stores numeric value of the attribute. For DURATION attribute, it stores duration value as seconds so a numeric value again. For SINGLE_SELECT attributes it stores ID of the selected attribute from AO_7B9828_ATTR_VALUE table.

  • ISSUE_ID, PROJECT_ID can be accessed through WORKLOG table using WORK_LOG_ID column but we are denormalizing them for various reasons. You should not need this information.

AO_7B9828_ATTR_VALUE: This table stores possible attribute values for a SINGLE_SELECT attribute.

  • ID primary key. Other tables may refer to this table through this ID column.

  • ATTR_TYPE_ID is ID of attribute type from AO_7B9828_ATTR_TYPE table.

  • ORDER is only meaningful for SINGLE_SELECT attribute types. Shows relative ordering of each attribute value within the same attribute type. If value is lower it is displayed first in the select combobox.

  • Other columns are self explanatory and mostly not needed for scripting.

WORKLOG: This table is standard Jira worklog table. This table does NOT belong to WorklogPRO.

  • ID, primary key. Various WorklogPRO tables may reference a worklog through this ID column.

  • ISSUEID is ID of the Jira issue.

  • AUTHOR is the key of the user who created the worklog entry.

  • UPDATEAUTHOR is the key of the user who worked on the issue.

  • TIMEWORKED is the time spent value in seconds.

  • STARTDATE is the start date of the worklog.

  • CREATED is the time at which a worklog is created. It can be different than STARTDATE because you can register worklogs in the past or future.

  • UPDATED is the time worklog is updated. If worklog is not updated it has the same value CREATED.

  • WORKLOGBODY is the comment of worklog.

  • GROUPLEVEL and ROLELEVEL are for visibility of worklog comment.