Difference between revisions of "WebIssues/DB Schema"

Latest revision as of 19:10, 26 February 2013

This is a short description of the most important database tables and columns, focusing on those which store information about issues, their details and history.

The stamps table is used to generate auto-incremented identifiers which are used as issue IDs, change IDs and stub IDs. Each operation starts with inserting a row in the stamps table and ends with updating the stamp_id field of both the affected issue and the folder in which the issue is located. This allows the incremental updates used in the WebIssues protocol to work with both issue lists and issue details. It also allows to mark the issue as modified.

stamps

• stamp_id - unique, auto-incremented identifier
• user_id - user who performed the operation
• stamp_time - UNIX time stamp of the operation

The issues table is the main table that stores information about existing issues.

issues

• issue_id - issue identifier (corresponds to a stamp ID generated when creating the issue)
• folder_id - the folder where the issue is located (indirectly determines the issue type)
• stamp_id - the stamp identifier of the last modification of the issue (equal to issue_id just after creating the issue)
• stub_id - the identifier of the last stub associated with the issue (used when the issue is moved to another folder; initially NULL)

The attr_values table stores the current values of all attributes of all issues. Values are always stored as text. Numbers and dates are formatted according to user's language and preferences before displaying. When an attribute is set to an empty value, the corresponding row is deleted from this table. All changes of attribute values are tracked in the changes table.

attr_values

• issue_id - identifier of the issue
• attr_id - identifier of the attribute

The changes table stores the entire history of the issue, including creating the issue, changing the issue name and attribute values, adding comments and attachments and moving between folders. The issue created record contains the initial name of the issue; its change_id is equal to the issue_id. The issue renamed record contain both the original and new name. The value changed record contains the old and new value and the ID of the changed attribute. The issue moved record contains the identifiers of the source and target folders.

changes

• change_id - the stamp identifier of the change
• issue_id - the issue associated with the change
• change_type - the type of the change, corresponding to the constants defined in System_Const
• stamp_id - the stamp identifier of the last modification of the record (usually equal to change_id, except when a comment or file was modified)

The comments table stores the text of the comments associated with the comment records in the changes table.

comments

• comment_id - identifier of the comment, corresponding to the change_id

The files table stores information about file attachments associated with the file records in the changes table.

files

• file_id - identifier of the attachment, corresponding to the change_id

Whenever an issue is deleted or moved to another folder, a "stub" is created to make it possible to incrementally update issue lists in the client. Note that multiple stubs can exist for a given issue if it is moved between folders more than once.

issue_stubs

• stub_id - the stamp identifier of the stub
• prev_id - identifier of the previous stub associated with the issue or issue_id if this is the first stub
• issue_id - identifier of the deleted or moved issue
• folder_id - the folder from which the issue was deleted or moved

Whenever a comment or attachment is deleted, a "stub" is created to make it possible to perform incrementally update issue details in the client.

change_stubs

• stub_id - the stamp identifier of the stub
• change_id - identifier of the deleted comment or attachment
• issue_id - the issue from which the comment or attachment was deleted