Database Schema

TimeCollect stores the time record in three tables. The suffix "_6" in the table names indicates the schema version. When upgrading to a new TimeCollect version with a modified database schema new tables with an incremented schema version are allocated.

Table Activity

mysql> describe activity_6;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| start       | datetime      | NO   | PRI | NULL    |       |
| stop        | datetime      | NO   |     | NULL    |       |
| ticket      | int(11)       | YES  |     | NULL    |       |
| description | varchar(1024) | NO   |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+

Description of Columns

start

Time and date when the activity started.

stop

Time and date when the activity ended.

ticket

Internal identifier for the ticket (foreign key into the ticket table further describing the activity.

description

Detailed description of the activity not covered by the ticket itself.

Table Ticket

The ticket table stores all tickets that are either managed locally within TimeCollect, or fetched from external ticket systems.

mysql> describe ticket_6;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id        | int(11)       | NO   | PRI | NULL    |       |
| system    | smallint(6)   | YES  |     | NULL    |       |
| rid       | int(11)       | YES  |     | NULL    |       |
| mnemonic  | varchar(254)  | NO   |     | NULL    |       |
| type      | varchar(254)  | NO   |     | NULL    |       |
| component | varchar(254)  | NO   |     | NULL    |       |
| state     | varchar(254)  | NO   |     | NULL    |       |
| title     | varchar(1024) | NO   |     | NULL    |       |
| lc        | smallint(6)   | NO   |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+

Description of Columns

id

Internal identifier for the ticket. Used to reference the ticket from the activity table.

system

Internal identifier for the ticket system from which the ticket was fetched. The value NULL represents a ticket locally managed within TimeCollect. The system identifier references the ticket system within the TimeCollect configuration where the access settings are stored. Currently only a single external ticket system is supported. Therefore the value is either NULL, or 1.

rid

Remote identifier of the ticket within an external ticket system. NULL for a ticket manages locally within TimeCollect.

mnemonic

Short-cut for the ticket to select the ticket by keyboard from within the "fetch ticket" dialog.

type

The ticket type. E.g. "bug", "task", or "enhancement".

component

The ticket component.

state

The ticket state in the external ticket system. E.g. "new", "assigned", "accepted", "closed". Empty for local tickets.

title

The ticket title.

lc

The internal life-cycle state of the ticket (see below).

Life-Cycle State

The internal life-cycle state of the ticket may have one of the following values:

INACTIVE

The ticket is no loger displayed in the main context menu, but potentially referenced from an activity in the activity table.

DROPPED

The ticket is matched by the current query, but should not be displayed in the list of active tickets in the main context menu.

MATCHED

The ticket is matched by the current query, and should be displayed in the list of active tickets in the main context menu.

SELECTED

The ticket was fetched explicitly from the "fetch ticket" dialog. It is displayed in the main context menu, even if it is not matched by the current query.

Table Property

The table property stores the current activity state and the TimeCollect schema version.

mysql> describe property_6;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| name    | varchar(255)  | NO   | PRI | NULL    |       |
| vint    | bigint(20)    | YES  |     | NULL    |       |
| vstring | varchar(4096) | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+

Description of Columns

name

The name of the property.

vint

For integer valued properties the property value, NULL otherwise.

name

For string valued properties the property value, NULL otherwise.

Description of Properties

mysql> select * from property_6;
+----------------+------+---------+
| name           | vint | vstring |
+----------------+------+---------+
| active         |    1 | NULL    |
| schema.version |    6 | NULL    |
+----------------+------+---------+

active

Whether TimeCollect is currently active and collecting time.

schema.version

The schema version of the TimeCollect version that set up the tables.