Jira | 事务与项目跟踪软件，敏捷团队的首先软件开发工具。
Database – Change history
Jira将每个Issue的变更历史记录存储在 changegroup 和 changeitem 表中。每条changegroup 表记录，描述了它关联的Issue、变更的时间以及执行变更的用户（对于未登录的用户为null）。每条changegroup 表记录关联一条或多条 changeitem 记录。每条 changeitem 记录描述已更新的Issue字段及其新旧值。 OLDVALUE 列记录更改的实体的ID（例如，状态），而 OLDSTRING 记录实体的名称，因此，如果从系统中删除实体，则仍可以显示Issue的变更历史记录。 NEWVALUE 和 NEWSTRING 列在本质上是相似的。
mysql> select * from changegroup; +-------+---------+--------+---------------------+ | ID | issueid | AUTHOR | CREATED | +-------+---------+--------+---------------------+ | 10000 | 10000 | admin | 2005-06-09 15:16:39 | | 10751 | 10000 | admin | 2005-06-10 00:00:00 | +-------+---------+--------+---------------------+
mysql> select * from changeitem; +-------+---------+-----------+------------+----------+-----------+----------+-----------+ | ID | groupid | FIELDTYPE | FIELD | OLDVALUE | OLDSTRING | NEWVALUE | NEWSTRING | +-------+---------+-----------+------------+----------+-----------+----------+-----------+ | 10000 | 10000 | jira | status | 1 | Open | 6 | Closed | | 10001 | 10000 | jira | resolution | NULL | NULL | 1 | Fixed | | 11404 | 10751 | jira | status | 1 | Open | 6 | Closed | +-------+---------+-----------+------------+----------+-----------+----------+-----------+
SEQUENCE_VALUE_ITEM表：记录每个Jira数据库表中使用的最大ID
mysql> select * from SEQUENCE_VALUE_ITEM; +-----------------------------+--------+ | SEQ_NAME | SEQ_ID | +-----------------------------+--------+ | Action | 10310 | | ChangeGroup | 11050 | | ChangeItem | 11320 | | ColumnLayout | 10040 | | ColumnLayoutItem | 10120 | | Component | 10110 | | ConfigurationContext | 10170 | | SchemeIssueSecurities | 10040 | ...
Database - Configuration properties
propertytype字段：1-propertynumber表，5-propertystring表，6-propertytext表
Database – Custom fields
customfield 存储自定义字段， customfieldvalue 存储自定义字段的值， genericconfiguration 存储自定义字段的默认值
mysql> desc customfieldvalue; +-------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+-------+ | ID | decimal(18,0) | NO | PRI | | | | ISSUE | decimal(18,0) | YES | MUL | NULL | | | CUSTOMFIELD | decimal(18,0) | YES | | NULL | | | PARENTKEY | varchar(255) | YES | | NULL | | | STRINGVALUE | varchar(255) | YES | | NULL | | | NUMBERVALUE | decimal(18,6) | YES | | NULL | | | TEXTVALUE | longtext | YES | | NULL | | | DATEVALUE | datetime | YES | | NULL | | | VALUETYPE | varchar(255) | YES | | NULL | | +-------------+---------------+------+-----+---------+-------+
Database – Issue fields
mysql> desc jiraissue; +----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+---------------+------+-----+---------+-------+ | ID | decimal(18,0) | NO | PRI | NULL | | | pkey | varchar(255) | YES | | NULL | | | issuenum | decimal(18,0) | YES | MUL | NULL | | | PROJECT | decimal(18,0) | YES | MUL | NULL | | | REPORTER | varchar(255) | YES | MUL | NULL | | | ASSIGNEE | varchar(255) | YES | MUL | NULL | | | CREATOR | varchar(255) | YES | | NULL | | | issuetype | varchar(255) | YES | | NULL | | | SUMMARY | varchar(255) | YES | | NULL | | | DESCRIPTION | longtext | YES | | NULL | | | ENVIRONMENT | longtext | YES | | NULL | | | PRIORITY | varchar(255) | YES | | NULL | | | RESOLUTION | varchar(255) | YES | | NULL | | | issuestatus | varchar(255) | YES | | NULL | | | CREATED | datetime | YES | MUL | NULL | | | UPDATED | datetime | YES | MUL | NULL | | | DUEDATE | datetime | YES | MUL | NULL | | | RESOLUTIONDATE | datetime | YES | MUL | NULL | | | VOTES | decimal(18,0) | YES | MUL | NULL | | | WATCHES | decimal(18,0) | YES | MUL | NULL | | | TIMEORIGINALESTIMATE | decimal(18,0) | YES | | NULL | | | TIMEESTIMATE | decimal(18,0) | YES | | NULL | | | TIMESPENT | decimal(18,0) | YES | | NULL | | | WORKFLOW_ID | decimal(18,0) | YES | MUL | NULL | | | SECURITY | decimal(18,0) | YES | | NULL | | | FIXFOR | decimal(18,0) | YES | | NULL | | | COMPONENT | decimal(18,0) | YES | | NULL | | +----------------------+---------------+------+-----+---------+-------+
因为每个Issue可以有多个组件或版本，因此在 jiraissue 和 version/component 之间有一个连接表 nodeassociation 。如影响的版本IssueVersion
mysql> desc nodeassociation; +--------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+---------------+------+-----+---------+-------+ | SOURCE_NODE_ID | decimal(18,0) | NO | PRI | | | | SOURCE_NODE_ENTITY | varchar(60) | NO | PRI | | | | SINK_NODE_ID | decimal(18,0) | NO | PRI | | | | SINK_NODE_ENTITY | varchar(60) | NO | PRI | | | | ASSOCIATION_TYPE | varchar(60) | NO | PRI | | | | SEQUENCE | decimal(9,0) | YES | | NULL | | +--------------------+---------------+------+-----+---------+-------+
Database – Issue status and workflow
每条 jiraissue 都有对应的一条 OS_CURRENTSTEP 和 OS_WFENTRY 。OS_WFENTRY 指定适用的工作流， OS_CURRENTSTEP 指定该工作流的步骤 STEP_ID ，对应的是 atlassian-jira/WEB-INF/classes/jira-workflow.xml 的
jiraissue.WORKFLOW_ID == OS_WFENTRY.IDjiraissue.WORKFLOW_ID == OS_CURRENTSTEP.ENTRY_ID
Issue状态和工作流步骤始终是保持同步的。如果步骤与状态不同步，Issue页面上将会显示不正确的（或没有）工作流操作。
Database – User and Group tables
APP_USER 存储User
COLUMN_NAME
DATA_TYPE
COMMENTS
ID
NUMBER(18,0)
USER_KEY
VARCHAR(255)
Unique user key
LOWER_USER_NAME
VARCHAR(255)
Links to CWD_USER.LOWER_USER_NAME
CWD_USER
COLUMN_NAME
DATA_TYPE
COMMENTS
ID
NUMBER(18,0)
DIRECTORY_ID
NUMBER(18,0)
Links to CWD_DIRECTORY
USER_NAME
VARCHAR(255)
LOWER_USER_NAME
VARCHAR(255)
used for case-insensitive search
ACTIVE
NUMBER(9,0)
CREATED_DATE
DATE
UPDATED_DATE
DATE
FIRST_NAME
VARCHAR(255)
Not used
LOWER_FIRST_NAME
VARCHAR(255)
Not used
LAST_NAME
VARCHAR(255)
Not used
LOWER_LAST_NAME
VARCHAR(255)
Not used
DISPLAY_NAME
VARCHAR(255)
LOWER_DISPLAY_NAME
VARCHAR(255)
EMAIL_ADDRESS
VARCHAR(255)
LOWER_EMAIL_ADDRESS
VARCHAR(255)
CREDENTIAL
VARCHAR(255)
CWD_USER_ATTRIBUTES 存储User的任意属性
CWD_GROUP 存储分组信息
CWD_MEMBERSHIP 存储哪个用户属于哪个分组
USERASSOCIATION 存储用户关注和投票
