JIRA 4.0 : User and Group Tables
This page last changed on Nov 01, 2006 by terry.ooi.
User and Group TablesUser TablesJIRA uses an open source framework called OSUser to manage its users. The user records are stored in the userbase database table. The table holds little information: +---------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------+------+-----+---------+-------+ | ID | decimal(18,0) | | PRI | 0 | | | username | varchar(255) | YES | MUL | NULL | | | PASSWORD_HASH | varchar(255) | YES | | NULL | | +---------------+---------------+------+-----+---------+-------+ OSUser also stores some properties for the user. The properties are: full name and e-mail. These properties are stored in the propertyentry and propertystring tables. For each user the propertyentry table: +--------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------+------+-----+---------+-------+ | ID | decimal(18,0) | | PRI | 0 | | | ENTITY_NAME | varchar(255) | YES | MUL | NULL | | | ENTITY_ID | decimal(18,0) | YES | | NULL | | | PROPERTY_KEY | varchar(255) | YES | | NULL | | | propertytype | decimal(9,0) | YES | | NULL | | +--------------+---------------+------+-----+---------+-------+ stores 'OSUser' in the ENTITY_NAME column, the id of the userbase record in the ENTITY_ID column, and 'fullName' or 'email' in the PROPERTY_KEY column. The propertystring table: +---------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------+------+-----+---------+-------+ | ID | decimal(18,0) | | PRI | 0 | | | propertyvalue | text | YES | | NULL | | +---------------+---------------+------+-----+---------+-------+ stores the actual values of the properties. The ID column is the same as the ID of the propertyentry record and the propertyvalue column would store the full name or e-mail of the user. Group TablesThe groups are stored in the groupbase table: +-----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------+------+-----+---------+-------+ | ID | decimal(18,0) | | PRI | 0 | | | groupname | varchar(255) | YES | MUL | NULL | | +-----------+---------------+------+-----+---------+-------+ Group MembershipThe membershipbase table records which users belong to which groups: +------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------+------+-----+---------+-------+ | ID | decimal(18,0) | | PRI | 0 | | | USER_NAME | varchar(255) | YES | MUL | NULL | | | GROUP_NAME | varchar(255) | YES | MUL | NULL | | +------------+---------------+------+-----+---------+-------+ The USER_NAME column is set to the username column in the userbase table. The GROUP_NAME is set to the groupname record in the groupbase table. Watches and VotesWatches and votes are recorded in the userassociation table: +------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------------+------+-----+---------+-------+ | SOURCE_NAME | 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 | | +------------------+---------------+------+-----+---------+-------+ For example: mysql> select * from userassociation; +---------------+--------------+------------------+------------------+----------+ | SOURCE_NAME | SINK_NODE_ID | SINK_NODE_ENTITY | ASSOCIATION_TYPE | SEQUENCE | +---------------+--------------+------------------+------------------+----------+ | asmith | 108433 | Issue | WatchIssue | NULL | | droberts | 100915 | Issue | WatchIssue | NULL | | dfernandez | 106387 | Issue | VoteIssue | NULL | ... For example, here user 'asmith' is watching issue with id 108433. |
![]() |
Document generated by Confluence on Oct 06, 2009 00:31 |