Database Structure

The design of the Ketura database schema and the descriptive technical information provided here is © 1998-2008 Araxis Ltd. All rights reserved. Araxis grants you a licence to view and use this information only for the sole purpose of accessing the data stored by an Araxis Ketura system. Do not read this material if these terms are not acceptable to you.

Introduction

The structure of a database is determined by its schema. Users and applications that wish to directly access the data in a Ketura database (for example, by odbc) need to understand the database schema (or, at least, the relevant parts of it) if they are to extract the information of interest. The document describes the schema of the Ketura database, as well as providing additional guidance notes on how that schema is used by Ketura.

Accessing a Ketura database directly is an advanced operation and should only be attempted by suitably knowledgeable people. Incorrect access may result in the loss of data. Any attempted access should be read-only: attempts to modify the data in a Ketura database directly are likely to result in corrupt or inconsistent databases. Note too that, in the current release of Ketura, direct access to the database is not possible when Ketura is using the default, built-in database engine. Such access is currently only possible when using an external database engine, such as Microsoft SQL Server, although this restriction might be lifted in a future release.

History

This document describes the Ketura database schema version 35. The schema version is incremented each time a Ketura release is made that has a different database schema from the previous release. The schema version is stored in the DatabaseSchemaVersion property of the tblGlobalProperties table. Applications that access the Ketura database directly can use this property to determine whether they are compatible with the database schema in use.

The database schema history is described in the table below.

Overview

There are several principal entities in the Ketura database. The tables relating to each entity are described in a corresponding section below. In addition there are some tables that do not conveniently fit into any of the preceding categories. Such tables are described in the System section.

The following information is provided for each described database table:

• Column – the database column name.
• Type – the column type. Certain conventions have been used in the interests of presenting a dbms-independent description. Where a ‘national’ text type is encountered, it should be understood that Unicode-capable character equivalents are intended (in Microsoft SQL Server, these include nchar and nvarchar). Where timestamp is used, an appropriate type capable of holding a timestamp is meant. For Microsoft SQL Server a suitable type is datetime.
• Constraints – not null if the column must not be null, and the table.column referenced by a column if it has a foreign key constraint.
• Notes – any additional notes appropriate to the column description.

A key symbol is used to mark columns that form primary keys of the tables in which they are found.

The database structure is presented in sections according to its general functionality:

• Topics
• Workflow
• Issues
• History
• Searching
• Contacts
• Users
• System
• Tasks
• Work Journal
• Milestones
• Projects
• Favourites
• Alerts

Ids

A number of tables have id columns: the column name will then usually end with Id (for example: ComponentId, IssueId). The allocation of values to fields of this kind is automatic. For Microsoft SQL Server, this is accomplished using the IDENTITY construct. For other databases, Ketura may use similar constructs or sequences, as appropriate.

Ids with negative values correspond to reserved items for use by the Ketura system, whereas ids with non-negative values correspond to items created by users. Table entries with negative ids should not be removed from the database. For example, the predefined issue state type New has an id of -1, whereas user-created issue state types would have positive ids.

Issue Topics

tblComponents

Column	Type	Constraints	Notes
ComponentId	int	Not null	A unique identifier for this issue topic
Name	national character varying	Not null	A short name for the issue topic
Description	national character varying	Can be null	A descriptive name for the issue topic
MilestoneIdForNewIssues	int	Can be null	The milestone id for new issues associated with this issue topic
DisplayReportedIn	bit	Not null	True if Reported-in field should be shown for issues of this issue topic, otherwise false
DisplayFixedIn	bit	Not null	True if Fixed-in field should be shown for issues of this issue topic, otherwise false

The tblComponents table contains an entry for each issue topic. Within the database schema, the term ‘component’ is used interchangeably with ‘issue topic’. This is for historical reasons.

An issue topic cannot be deleted if it contains issues.

When an issue topic is deleted, rows are deleted from the following tables where the ComponentId field matches that the issue topic to be deleted:

• tblIssueStateTransitions
• tblComponentVersions
• tblEvents
• tblEventNotifications
• tblIssueStateTransitions

When a subtopic or version is created, entries are made in the following tables:

• tblComponentVersions – a version with name ‘0.0’ and description ‘Initial’ is created.
• tblIssueStateTransitions – state transitions are created from each issue state type to every other issue state type.

tblComponentVersions

Column	Type	Constraints	Notes
ComponentVersionId	int	Not null	A unique identifier for this subtopic or version
ComponentId	int	Not null, tblComponents > ComponentId	The issue topic of which this version is a member
Description	national character varying	Can be null	A descriptive name for the issue topic version
Version	national character varying	Not null	A short name for the issue topic version

The tblComponentVersions table contains an entry for each subtopic or version.

When a subtopic or version is deleted, the ComponentVersionId or FixedInComponentVersionId fields are set to NULL, if necessary

Workflow

One of the roles of Ketura issue topics is to administer workflow. By establishing permitted state transitions, Ketura administrators can control the states to which an issue can be changed. Only if a state transition is defined is an issue allowed to be changed from one state to another.

tblIssueStateTransitions

The tblIssueStateTransitions table contains an entry for each permitted state transition.

Issues

tblIssues

Column	Type	Constraints	Notes
IssueId	int	Not null	A unique identifier for this issue
IssueType	int	Not null, tblIssueTypes > IssueType	The type of the issue
Description	national text	Not null	The description of the issue
FixedInBuild	national character varying	Not null	The build in which the issue was fixed
FixedInComponentVersionId	int	Can be null, tblComponentVersions > ComponentVersionId	The subtopic or version in which the issue was fixed
ComponentBuild	national character varying	Not null	The build in which the issue was reported
ComponentId	int	Not null, tblComponents > ComponentId	The issue topic that this issue is about
ComponentVersionId	int	Can be null, tblComponentVersions > ComponentVersionId	The subtopic or version that this issue is about
Severity	int	Not null, tblIssueSeverityTypes > Severity	The severity of the issue
State	int	Not null, tblIssueStateTypes > State	The state of the issue
Category	national character varying	Can be null	The category to which a task set belongs
Summary	national character varying	Not null	A short summary of the issue
MustStartByDate	timestamp	Can be null	Optional date to specify when work on this issue must start
MustEndByDate	timestamp	Can be null	Optional date to specify when work on this issue must end

The tblIssues table contains an entry for each issue.

When an issue is deleted, rows are deleted from the following tables where the IssueId field matches that the issue to be deleted:

• tblSearchDicionary
• tblIssueAttachments
• tblIssueContacts
• tblIssueRelations
• tblMilestoneIssues

tblIssueTypes

Column	Type	Constraints	Notes
IssueType	int	Not null	A unique identifier for this issue type
Name	national character varying	Not null
Description	national character varying	Can be null	A descriptive name for the issue type
IssueDescriptionDefault	national character varying	Can be null	The default issue description used when a new issue of this type is created

The tblIssueTypes table contains an entry for each issue type. This allows the Ketura administrator to localize issue types.

An issue type cannot be deleted if it still being used for one or more issues.

tblIssueSeverityTypes

Column	Type	Constraints	Notes
Severity	int	Not null	A unique identifier for this issue severity type
Name	national character varying	Not null	A short name for the issue severity type
Description	national character varying	Can be null	A descriptive name for the severity type
Weight	int	Not null	The weight of this severity type

The tblIssueSeverityTypes table contains an entry for each issue severity type. This allows the Ketura administrator to localize issue severity types.

tblIssueStateTypes

Column	Type	Constraints	Notes
State	int	Not null	A unique identifier for this issue state type
Name	national character varying	Not null	A short name for the issue state type
Description	national character varying	Can be null	A descriptive name for the state type
IsActive	bit	Not null	1 if the state is active, otherwise 0

The tblIssueStateTypes table contains an entry for each issue state type. This allows the Ketura administrator to localize issue state types.

An issue state type cannot be deleted if it still being used for one or more issues.

The issue state type whose id is -1 (the ‘New’ state) cannot be deleted

When an issue state type is created, entries are made in the following table:

• tblIssueStateTransitions – entries are created from every other state type to the newly created state type, and vice versa.

tblIssueContacts

The tblIssueContacts table contains an entry for each contact added to an issue.

tblIssueAttachments

Column	Type	Constraints	Notes
AttachmentId	int	Not null	A unique identifier for this attachment
ContentType	national character varying	Can be null	The MIME type of the attachment
DateTimestamp	timestamp	Not null	The date and time at which the attachment was added
Description	national character varying	Not null	A descriptive name for the issue attachment
Filename	national character varying	Not null	The filename with which the attachment was originally associated
IssueId	int	Not null, tblIssues > IssueId	The issue to which the attachment was added
Len	int	Not null	The length of the attachment, in bytes
Object	longvarbinary	Can be null	The binary image of the attachment
UserId	int	Not null	The user that added the attachment

The tblIssueAttachments table contains an entry for each attachment added to an issue.

tblIssueRelations

The tblIssueRelations table contains an entry for each related issue. Note that issue relations are symmetrical – if issue A is related to issue B then the reverse is equally true.

History

The history of an issue is recorded to allow the display of issue audit trails and to support the generation of reports. The tblEvents maintains the issue's history.

Searching

A dictionary is maintained to improve the performance of issue searches. The dictionary indexes the issues table by words found in issue comments, descriptions and summaries. Common words may be excluded from the dictionary by means of a stop list.

tblSearchDictionary

The tblSearchDictionary table contains an entry for each dictionary word.

tblSearchStopWords

Column	Type	Constraints	Notes
WordId	int	Not null
Word	national character varying	Not null	The text of the word

The tblStopList table contains an entry for each entry in the stop list.

Contacts

tblContacts

Column	Type	Constraints	Notes
ContactId	int	Not null	A unique identifier for this contact
ContactTypeId	int	Not null, tblContactTypes > ContactTypeId	Type of the contact
Address1	national character varying	Can be null	Part of contact’s address
Address2	national character varying	Can be null	Part of contact’s address
BusinessFax	national character varying	Can be null	Business fax number
BusinessPhone	national character varying	Can be null	Business telephone number
City	national character varying	Can be null	Part of contact’s address
Company	national character varying	Can be null	Company with which the contact is associated
CountryId	int	Can be null, tblCountries > CountryId	Part of contact’s address
Email	national character varying	Can be null	Contact’s email address
FirstName	national character varying	Not null	Contact’s first name
JobTitle	national character varying	Can be null	Contact’s job title
LastName	national character varying	Not null	Contact’s last name
Notes	national text	Not null	Description to be associated with this contact
PostCode	national character varying	Can be null	Contact’s zip, area or post code
State	national character varying	Can be null	Contact’s state, region or county

The tblContacts table contains an entry for each contact.

A contact cannot be deleted if it is used for a user of the database.

tblContactTypes

Column	Type	Constraints	Notes
ContactTypeId	int	Not null	A unique identifier for this contact type
ContactTypeName	national character varying	Not null	A short name for the contact type
Description	national character varying	Can be null	A descriptive name for the contact type

The tblContactTypes table contains an entry for each contact type. This allows the Ketura administrator to localize contact types.

The contact type whose id is -1 (the ‘User’ contact type) cannot be deleted

tblCountries

Column	Type	Constraints	Notes
CountryId	int	Not null	A unique identifier for this country
Name	national character varying	Not null	The name of the country

The tblCountries table contains an entry for each country. This allows the Ketura administrator to localize country names and/or update them as changes occur.

A country name cannot be deleted if it is used for a contact in the database.

Users

tblGroups

Column	Type	Constraints	Notes
GroupId	int	Not null	A unique identifier for this group
Name	national character varying	Not null	A short name for the group
Description	national character varying	Can be null	A descriptive name for the group

The tblGroups table contains an entry for each group.

A group cannot be deleted if it contains users.

The group whose id is -1 (the ‘Administrators’ group) cannot be deleted

tblUsers

Column	Type	Constraints	Notes
UserId	int	Not null	A unique identifier for this user
UserName	national character varying	Not null
PasswordHash	national character varying	Not null	The MD5 hash of the user’s password
ContactId	int	Not null, tblContacts > ContactId	The contact recording the user’s personal details
GroupId	int	Not null, tblGroups > GroupId	The group of which the user is a member
HourlyCost	int	Not null	The hourly cost of the user
Active	bit	Not null	The user’s active flag
WorkJournalLockDate	timestamp	Not null	All work journal entries before this UTC date should be treated as locked

The tblUsers table contains an entry for each user.

A user cannot delete his or her own user name

When a user is deleted, rows are deleted from the following tables where the UserId field matches that of the user to be deleted:

• tblEventNotifications

tblPermissions

The tblPermissions table contains an entry for each granted permission.

The tblPermissionTypes table contains an entry for each permission type. This allows the administrator to localize permission types (but note the Name and PermissionType field may not be updated (their values are defined by the Ketura system)).

tblEventNotifications

Column	Type	Constraints	Notes
NotificationId	int	Not null	A unique identifier for this event notification
UserId	int	Not null, tblUsers > UserId	The user to whom notifications are to be sent
EventTypeId	int	Can be null	The event for which notifications are to be sent, or NULL of all issue events are to trigger notifications
ComponentId	int	Can be null, tblComponents > ComponentId	The issue topic to which issues must be assigned to trigger a notification, or NULL if there is no such restriction

The tblEventNotifications table contains an entry for each event notification.

Availability Templates

This table holds the availability templates. Initially, it will only contain one row which will represent the default for all users template (whose id will be -1). In future releases of Ketura we may allow users to define their own availability templates.

tblAvailabilityTemplates

Column	Type	Constraints	Notes
AvailabilityTemplateId	int	Not null	The unique id of the availability template
Day1Work	int	Not null	The available time to work in seconds on Sundays
Day2Work	int	Not null	The available time to work in seconds on Mondays
Day3Work	int	Not null	The available time to work in seconds on Tuesdays
Day4Work	int	Not null	The available time to work in seconds on Wednesdays
Day5Work	int	Not null	The available time to work in seconds on Thursdays
Day6Work	int	Not null	The available time to work in seconds on Fridays
Day7Work	int	Not null	The available time to work in seconds on Saturdays

User Availabilities

There is a row in this table for each user. For each day that a user’s availability deviates, the appropriate work value (in seconds) is entered into the appropriate DayXWork column. For those days on which availability does not deviate, the appropriate column value is set to null to indicate no change from the following template. It should be noted that in the current implementation, either all of the DayNWork columns are null or all are non-null.

tblUserAvailabilities

Column	Type	Constraints	Notes
UserId	int	Not null, tblUsers > UserId	The user id
AvailabilityTemplateId	int	Not null, tblAvailabilityTemplates > AvailabilityTemplateId	The availability template followed by this user
Day1Work	int	Can be null	The available time to work in seconds on Sundays, if different from the followed template, otherwise null
Day2Work	int	Can be null	The available time to work in seconds on Mondays, if different from the followed template, otherwise null
Day3Work	int	Can be null	The available time to work in seconds on Tuesdays, if different from the followed template, otherwise null
Day4Work	int	Can be null	The available time to work in seconds on Wednesdays, if different from the followed template, otherwise null
Day5Work	int	Can be null	The available time to work in seconds on Thursdays, if different from the followed template, otherwise null
Day6Work	int	Can be null	The available time to work in seconds on Fridays, if different from the followed template, otherwise null
Day7Work	int	Can be null	The available time to work in seconds on Saturdays, if different from the followed template, otherwise null

User Availability Exceptions

A row exists in the table for each day a user’s availability deviates from the followed template.

We record NonWork as a percentage of a day to avoid changes in meaning if the number of working hours in a day is changed.

tblUserAvailabilityExceptions

The entry for the exception day type represents one of the non working day types. The defined types are Public holiday, Employee vacation, Unpaid leave, Sick leave, Training, Maternity/paternity leave, Company/organisation holiday, Suspended with pay, Suspended without pay, and Other non-working time.

User Allocations

This table records the proportion (in 2²⁰ths) of a user’s available time on a particular day that is allocated to a particular project.

Available time is recorded as a proportion, rather than a length of time, to avoid over and under allocation issues if a user’s availability were to change in the future.

tblUserAllocations

User Issue Values Cache

If an issue does not have any tasks (and has never had any tasks), there will not be an entry in this table for the issue. There will be an entry in the table for deleted tasks. UserId will only be null if the entry is for the unassigned user.

System

The state and history of the system is held in tblEvents. System history is used to construct the audit trail.

tblEvents

Column	Type	Constraints	Notes
EventId	int	Not null	A unique identifier for this event
DateTimestamp	timestamp	Not null	The date and time at which the event occurred
EventTypeId	int	Not null	The type of the event
ObjectId	int	Can be null	The id of the object (e.g. issue topic) that has changed
ObjectName	national character varying	Can be null	The human readable name of the object referenced by ObjectId
FieldName	national character varying	Can be null	The name of the field that has changed
NewFieldValue	national text	Can be null	The new value of the field
OldFieldValue	national text	Can be null	The old value of the field
UserId	int	Can be null, tblUsers > UserId	The user making the change. NULL if the user has been deleted since the event was created.
UserName	national character varying	Can be null	The log on name of the user whose action caused the event. Valid even if the user who created the event has been deleted
ForUserId	int	Can be null, tblUsers > UserId	The id of the user for whom the notification is for
Cleared	bit	Can be null	If false, indicates that this alert notification should be shown in the alert list of ForUserId; if true, the alert notification should not be shown. Null if this row is not an alert notification.
Parameter1	national character varying	Can be null	Holds the first alert specific parameter value, or null if no parameter value exists
Parameter2	national character varying	Can be null	Holds the second alert specific parameter value, or null if no parameter value exists
ObjectId2	int	Can be null	The id of an optional secondary object
Info1	national text	Can be null	May be used on an alert by alert, or event by event basis
Info2	national text	Can be null	May be used on an alert by alert, or event by event basis

The tblEvents table contains an entry for each event.

tblGlobalProperties

Column	Type	Constraints	Notes
Name	national character varying	Not null	A unique identifier for this property
Value	national character varying	Can be null

The tblGlobalProperties table contains an entry for each instance property.

Tasks

Tasks are system entities that enable users to estimate and record the amount of effort required to complete a unit of work. Each task belong to exactly one issue, although an issue can have any number of tasks.

Tasks with work done recorded against them will be marked as deleted and their work baseline, work planned and work remaining values will be set to zero so that they don’t contribute to the overall cache values of issues when re-calculated.

tblTasks

Column	Type	Constraints	Notes
TaskId	int	Not null
IssueId	int	Not null, tblIssues > IssueId	The id of the issue to which the task belongs
AssignedUserId	int	Can be null, tblUsers > UserId	The id of the user to whom the task is assigned
CreatedBy	int	Can be null, tblUsers > UserId	The id of the user that created the task
InProgress	bit	Not null	1 if the task is part of the user’s work in progress, 0 if pending. This flag is used to determine whether to show a task in a user’s Pending or In Progress lists. It is not an indicator of the state of a task (i.e. work is in progress).
Summary	national character varying	Not null	A summary description of the task
TaskOrder	int	Not null	The project manager’s intended order for this task (relative to other tasks of the same issue). A task with a lower order will (typically) be displayed higher in lists than one with a higher order. The relative ordering of tasks within an issue will usually indicate the project manager’s desired implementation order
WorkPlanned	bigint	Not null	The project manager’s current planned estimate of work effort (in seconds) required to complete the task. Note: this is work effort, and says nothing about task duration
WorkRemaining	bigint	Not null	The task assignee’s estimate of remaining work effort (in seconds) required to complete the task. A task is considered complete if this field is set to 0. Note: this is work effort, and says nothing about task duration.
Complete	bit	Not null	1 if the task is complete and not to be shown in task lists, 0 if it is to be shown
Description	national text	Not null	The optional full description of the task

Work Journal

Each user has a work journal where they can log work against a task.

tblWorkEntries

Column	Type	Constraints	Notes
WorkEntryId	int	Not null	The unique id of the work entry
UserId	int	Not null, tblUsers > UserId	The id of the user to which to work entry belongs
TaskId	int	Can be null, tblTasks > TaskId	The id of the task to which the work entry belongs
StartDateTimeMJD	double precision	Not null	Modified Julian Date (MJD) representation of the start date.
StartDateTime	timestamp	Not null	The date and time when the work entry was started
EndDateTime	timestamp	Can be null	The date and time when the work entry was completed
Duration	bigint	Can be null	The duration of time taken to complete the work entry
Timed	bit	Not null	1 indicates work entry is being timed. 0 indicates work entry is not being timed

Milestones

tblMilestones

Column	Type	Constraints	Notes
MilestoneId	int	Not null	The milestone’s unique id
Name	national character varying	Not null	The milestone’s name
DesiredCompletionDateTime	timestamp	Not null	The milestone’s desired completion date
ProjectPlanId	int	Not null, tblProjectPlans > ProjectPlanId	The milestone’s project id
MilestoneOrder	int	Not null	The milestone’s order
UsersBeginTogether	bit	Not null	A flag to indicate that when calculating end dates the system will assume that users will wait until all work from the previous milestones is complete before commencing work on this milestone.
IsCurrent	bit	Not null	Indicates if the milestone is current

tblMilestoneIssues

Projects

tblProjectPlans

Column	Type	Constraints	Notes
ProjectPlanId	int	Not null	The project’s unique id
Name	national character varying	Not null	The project’s name
StartDateTime	timestamp	Not null	The project’s start date as defined by a project manager
Active	bit	Not null	The project’s active flag
AlternativeForProjectPlanId	int	Can be null	The id for the project for which this is an alternative

Favourites

tblFavourites

Column	Type	Constraints	Notes
FavouriteId	int	Not null	A unique id of the favourite
ObjectId	int	Not null	The id of the object for which the favourite exists
ObjectTypeId	int	Not null	The id of the type of object: 1 for components, 2 for issues, 3 for tasks, 4 for projects, 5 for milestones.
UserId	int	Not null, tblUsers > UserId	The id of the user for whom the favourite exists

Alerts

tblAlertSubscriptions

SCM Systems

The SCM systems currently monitored by Ketura.

tblScms

Column	Type	Constraints	Notes
ScmId	int	Not null	A unique identifier for this SCM
Type	national character varying	Not null	Classname of the plug-in class used to access this SCM system
Name	national character varying	Not null	Friendly name of the SCM configuration settings
Username	national character varying	Not null	Name of the user that will be used by the thread to access this SCM system
Password	national character varying	Not null	Password phrase that will be used by the thread to access this SCM system
ServerAddress	national character varying	Not null	Network address of the SCM system
ExecutableLocation	national character varying	Not null	Specifies the location of the executable on the server that the plug-in will use to issue commands to the SCM system
LastChecked	bigint	Can be null	Big integer representing the point to which processing of change sets has been completed. Depending on plugin implemetation this will be a change set id or the millisecond value of a timestamp.
WebInterfaceId	national character varying	Not null	SCM web interface id
WebInterfaceField1	national character varying	Not null	Field to hold optional configuration string for use with currently selected web interface
WebInterfaceField2	national character varying	Not null	Field to hold optional configuration string for use with currently selected web interface
WebInterfaceField3	national character varying	Not null	Field to hold optional configuration string for use with currently selected web interface
WebInterfaceField4	national character varying	Not null	Field to hold optional configuration string for use with currently selected web interface

Change Sets

The change sets found in the SCM systems currently being monitored.

tblChangeSets

Column	Type	Constraints	Notes
ChangeSetId	int	Not null	A unique identifier for this change set
ScmId	int	Not null, tblScms > ScmId	Id of the SCM that contains the change set
ScmChangeSetId	national character varying	Not null	Id of the change set in the SCM system
Comment	national text	Not null	Text of change set’s comment
Submitter	national character varying	Not null	Id/username within the SCM that was used to submit the change set
SubmissionTimestamp	timestamp	Not null	Date and time that the change set was submitted

Changes

The details of the files of each change set.

tblChanges

Column	Type	Constraints	Notes
ChangeId	int	Not null	A unique identifier for this change
ChangeSetId	int	Not null, tblChangeSets > ChangeSetId	Id of the change set to which this change belongs
ScmPath	national character varying	Not null	Path of the change within the SCM system, as returned by the SCM system

Issue to ChangeSet associations

A record of the issues that are associated with change sets.

tblIssueChangeSets