Extension Documentation

Chapter 3. Database Design
Prev	Part I. Macrotone Issue Tracker	Next

Chapter 3. Database Design

In this section we specifically concentrate upon the underlying database objects.

Designing the Database Objects

Once the database requirements are defined, the next step is to turn these into a database design and an outline that describes how the user interface accepts and presents data. Given the requirements described above the three main tables can be created:

Projects tracks all current projects
People contains information about who can be assigned to handle issues
Issues tracks all information about an issue, including the project to which it is related and the person assigned to the issue

Further detail notes upon the main tables is provided later in this document.

Subsidiary tables are created to provide reference data. Sample reference data is supplied on installation. The provide samples should be tailored by the site administrator to reflect actual ‘real’ life terms used by the organisation. Once obvious example is for foreign language sites to be able to tailor the codes for their own use.

Status provides the base issue status codes. i.e. Open Closed etc.
Priority provides the list of priorities an issue may take. i.e. Low, High etc.
Role contains the list of business roles individuals may have. i.e. Manger, Lead etc.
Types define the ‘issue type’. i.e. Whether the issue is a ‘Defect’ or perhaps and ‘Enhancement Request’, or related to product ‘Documentation’. The list is extensible and should be tailed for the specific site requirements.
Emails defines the templates used for the notification messages.
Triggers defines the database triggers used to populate the enhanced auditing (change history) feature.
Change History holds the data populated by the database triggers that comprise the enhanced audit/change history feature.

In addition to the tables, we also need to create additional database objects, such as procedures and triggers, to support the tables. System generated primary keys will be used for all tables so that all the data can be edited without executing a cascade update.

Database Objects - Procedures, Views, Triggers

Our experience tends to indicate that very few (if any) Joomla components make use of the underlying database to its full advantage, preferring instead to implement features at the application layer. This is in our opinion missing a very important 'trick', since the database is 'closest' to the data itself. We have used our extensive database expertise to therefore make use of some of the database features with this component to provide the change data.

	Important
This component makes use of database features to provide enhanced functionality specifically the use of database routines, database views and database triggers. Not all host providers allow their clients to create these types of database objects. It is advisable to check your specific privileges before installing the component otherwise you will not be able to use the component if you cannot create database triggers. Attempts to install the component upon a system without the permissions are handled gracefully with a message indicating that certain features will be unavailable when the component is installed.

Important

This component makes use of database features to provide enhanced functionality specifically the use of database routines, database views and database triggers. Not all host providers allow their clients to create these types of database objects. It is advisable to check your specific privileges before installing the component otherwise you will not be able to use the component if you cannot create database triggers.

Attempts to install the component upon a system without the permissions are handled gracefully with a message indicating that certain features will be unavailable when the component is installed.

Database Triggers

The component has a basic check upon record changes inbuilt using PHP code within the component. This is backed up with some database triggers to capture situations where changes occur 'outside' of the component. If the Joomla database connection user has not been granted the database privilege to create database triggers, then this 'back up' ability will not be available. This should not be a disadvantage to most installations.

The enhance auditing feature requires and makes use of database triggers to provide the component with 'audit/change history' data. If the Joomla database connection user has not been granted the database privilege to create database triggers, then this feature will not be available within the component.

The 'basic' Joomla installation provides some tables with a record of who made the last change to an item (i.e. an article or a web link etc.) and when they made the change. Unfortunately it doesn't inform one of what was changed. It might have been something as simple as a correction of a typological error, or it may have been some thing much more extensive. Some sites require much more information about any given change, especially sites that may house data that could be considered 'sensitive'. The enhanced auditing feature addresses this requirement and provides a more comprehensive and extensive audit over all of the changes that have occurred upon table records.

This feature makes use of the underlying features of the database and creates 'database triggers' that record the changes after they are made in the database. In this way there is no need to change any of the Joomla libraries or core code, which avoids any problems if/when the core code is changed between releases.

The following description is taken from Wikipedia:

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database. For example, when a new record (representing a new worker) is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries.

Triggers are commonly used to:

audit changes (e.g. keep a log of the users and roles involved in changes)
enhance changes (e.g. ensure that every change to a record is time-stamped by the server's clock)
enforce business rules (e.g. require that every invoice have at least one line item)
execute business rules (e.g. notify a manager every time an employee's bank account number changes)
replicate data (e.g. store a record of every change, to be shipped to another database later)
enhance performance (e.g. update the account balance after every detail transaction, for faster queries)

The examples above are called Data Manipulation Language (DML) triggers because the triggers are defined as part of the Data Manipulation Language and are executed at the time the data is manipulated. Some systems also support non-data triggers, which fire in response to Data Definition Language (DDL) events such as creating tables, or runtime or and events such as logon, commit and rollback. Such DDL triggers can be used for database auditing purposes.

The following are major features of database triggers and their effects:

triggers do not accept parameters or arguments (but may store affected-data in temporary tables)
triggers cannot perform commit or rollback operations because they are part of the triggering SQL statement (only through autonomous transactions)

MySQL

MySQL 5.0.2 introduced support for triggers. MySQL supports these trigger types:

Insert Trigger
Update Trigger
Delete Trigger

	Note
	MySQL allows only one trigger of each type on each table (i.e. one before insert, one after insert, one before update, one after update, one before delete and one after delete).

	Note
	MySQL does NOT fire triggers outside of a statement (i.e. API's, foreign key cascades)

Whether a specific database user can create these triggers will depend upon the permissions granted to the database user. The database user that has to have the correct permission in the Joomla environment is the user that is used to perform all database connects as defined in the Joomla installation itself.

	Note
	Different databases implement database features such as database triggers in different way, and often with slightly different syntax. For that reason the current version only supports MySQL databases, the most commonly used database for Joomla systems.

Database Routines (Procedures)

A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure is actually stored in the database data dictionary.

Typical use for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures can consolidate and centralise logic that was originally implemented in applications. Extensive or complex processing that requires execution of several SQL statements is moved into stored procedures, and all applications call the procedures. One can use nested stored procedures by executing one stored procedure from within another. This is more efficient that handling the actions at the application layer, since the database is 'closer' to the actual data being manipulated.

Stored procedures are similar to database functions. The major difference is that functions can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement.

The component uses MySQL procedures to perform the installation (and uninstall) of the sample data used to demonstrate initial component usage. If the Joomla database connection user has not been granted the database privilege to create database routines then the 'sample data' will not be available within the component. This should not be a disadvantage to most installations since the sample database is not important to the functioning of the component.

Database Views

A view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object. This pre-established query command is kept in the database dictionary. Unlike ordinary base tables in a relational database, a view does not form part of the physical schema: as a result set, it is a virtual table computed or collated from data within the database, dynamically when access to that view is requested. Changes applied to the data in a relevant underlying table are reflected in the data shown in subsequent invocations of the view.

Views can provide advantages over tables:

Views can represent a subset of the data contained in a table; consequently, a view can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table.
Views can join and simplify multiple tables into a single virtual table
Views can act as aggregated tables, where the database engine aggregates data (sum, average etc.) and presents the calculated results as part of the data
Views can hide the complexity of data; for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table
Views take very little space to store; the database contains only the definition of a view, not a copy of all the data which it presents
Depending on the SQL engine used, views can provide extra security

Issue Tracker uses views to simplify the query used by the Finder (Smart Search) plugin. If the Joomla database connection user has not been granted the database privilege to create database views, then the finder plugin uses a longer, and slightly more complex query to create 'index' entries. This though slightly slower, should not be a disadvantage to most installations.

Data Model

The data model designed is graphically shown in Figure 3.1, “Data model for Issue Tracker 1.6”.

Figure 3.1. Data model for Issue Tracker 1.6

Projects Table

Each project must include project name, project start date, target date, and actual end date columns. These date columns help determine if any outstanding issues are jeopardising the project end date.

Table 3.1. Projects table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Number	n/a	Yes	Primary key	A unique numeric identification for each project (or sub-project). Populated by an auto-sequence.
asset_id	Number	n/a	No	None	FK to the #__assets table.
parent_id	Number	n/a	No	None	Key to parent project.
title	Varchar	255	Yes	Unique key	A unique alphanumeric name for the project. Formally named project_name.
alias	Varchar	10	Yes	None	Project Alias. Used to mask primary key of issue from random selection.
description	Varchar	4000	No	None	Description of the project. Formally named project_description.
lft	Integer	11	Yes	None	Nested table left.
rgt	Integer	11	Yes	None	Nested table right.
level	Integer	10	Yes	None	Nested table level.
access	TinyInt	3	Yes	None	Required for nested table.
path	Varchar	255	Yes	None	Required for nested table.
state	Number	4	No	None	Indicates that project is visible to front end.
assignee	Integer	11	No	None	A specified default assignee for the project. Default 0.
itypes	Text	4000	No	None	A JSON encoded string containing the list of the 'types' for this project. The default is all published types. Added in 1.6.5,
customfieldsgroup	Integer	11	Yes	None	Custom field group associated with this project.
ordering	Number	n/a	No	None	Order in which projects are displayed
checked_out	Number	11	Yes	None	Joomla field record locking
checked_out_time	Date	n/a	Yes	None	Joomla field record locking
start_date	Date	n/a	Yes	None	The project start date.
target_end_date	Date	n/a	Yes	None	The targeted project end date.
actual_end_date	Date	n/a	No	None	The actual end date.
metadata	Varchar	n/a	Yes	None	Associated metadata for the record.
created_on	Date	n/a	Yes	None	Date the record was created.
created_by	Varchar	255	Yes	None	The user who created the record
modified_on	Date	n/a	Yes	None	The date the record was last modified.
modified_by	Varchar	255	Yes	None	The user who last modified the record.

Note

1) It is possible to define sub-projects as well. Each sub-project is linked through the parent_id field. In the Joomla displays the full project name is displayed for every sub-project. i.e. For a project with three levels of sub-projects the project name is displayed as:

Project Name Level 1 - Level 2 - Level 3

Where ‘Level 1’, ‘Level 2’ and ‘Level 3’ are the defined project_name values for the sub projects. This means that the displayed ‘combined project name may become quite long, and for that reason it is suggested that the ‘project name’ is kept to a minimum length sufficient to uniquely identify it.

2) The ordering column permits the easy display of sub-projects below the main project in the list displays, using standard Joomla functionality.

People Table

Each person will have a defined name and role. Project leads and team members will also have an assigned project.

In order to associate the current user to a person, a username column is added to the people table. This allows flexibility when deciding on the authentication mechanism and also allows for an authorisation scheme that can determine who the person is that has logged on and if they have access to the application.

As a standard, audit columns are present on each table. They do not need to be identified during analysis because they are added consistently to each table just before implementation.

Table 3.2. People table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Number	n/a	Yes	Primary key	A numeric ID that identifies each user. Populated by an auto incremental sequence.
user_id	Number	n/a	No	Unique Key	Value obtained from the ‘#__users’ tables.
alias	Varchar	10	Yes	None	Not currently used
person_name	Varchar	255	Yes	Unique key	A unique name that identifies each user.
person_email	Varchar	100	Yes	None	User email address.
person_role	Integer	n/a	Yes	Check constraint (MySQL)	The role assigned to each user. Foreign key to the it_roles table.
username	Varchar	150	Yes	Unique Key	The username of this person. Used to link login to person's details.
phone_number	Varchar	300	No	Unique Key	User phone number for receiving SMS messages if configured. Added in 1.6.5.
assigned_project	Number	n/a	No	None	The project this person is assigned to.
issues_admin	Number	1	No	None	Whether the person is an administrator.
staff	Number	1	No	None	Whether the person is a company staff member.
email_notifications	Number	1	No	None	Whether the person has requested email notifications.
sms_notify	Number	1	No	None	Whether the person has requested SMS notifications. Added in 1.6.5.
registered	Number	1	No	None	Whether the person is registered in Joomla.
published	Number	1	No	None	Indicates issue is visible in front end.
ordering	Number	n/a	No	None	Specifies ordering of issues.
checked_out	Number	11	Yes	None	Joomla field record locking
checked_out_time	Date	n/a	Yes	None	Joomla field record locking
created_on	Date	n/a	Yes	None	Date the record was created.
created_by	Varchar	255	Yes	None	The user who created the record.
modified_on	Date	n/a	Yes	None	The date the record was last modified.
modified_by	Varchar	255	Yes	None	The user who last modified the record.

Note:

For the current implementation the design has been simplified. User data is usually much more elaborate and in our implementation the user information is populated automatically from the Joomla users table. Also, users typically work on more than one project at a time.

The minimum amount of information is kept about the user, sufficient only to be able to notify them if they request to be kept informed.

The roles that are assigned to a user are dynamic, A separate role table with a foreign key that relates to the people table.

The user information is automatically inserted into the table when a new user is registered upon the Joomla site. This is achieved via a Joomla plug-in, installed when the component is installed. The plug-in also handles the deletion of users when they are removed from the Joomla users table. Only issues assigned or raised by the deleted user are handled as specified in the ‘options: delete mode’ setting. See below for more details of options.

In release 1.1.0 and earlier no facility existed to create new users in the component. This was deliberate since the users are expected to be ‘registered Joomla users’ and there is no concept of ‘Issue Tracker users’ as a distinct entity outside of Joomla. In release 2.0 the ability to create users who are known to Issue Tracker but not registered is introduced. This means that it is possible to identify a non-registered user with an issue that they may view in the front end. The registered and user_id fields were introduced to meet this change.

The presence of the published field may seem strange since there is a security question about whether people's details should be made available to the wider public. However the web site might not be available on the web and instead be resident on an internal company intranet. In this situation the ability to control what is displayed becomes more obvious.

The staff field is used to define users who can be assigned to ‘work’ an issue.

Issues Table

It was decided to track separate issues assigned to each person. Issues will also provide a simple audit trail. The audit trail will track who created the issue, when it was created, as well as who modified the issue last and on what date that modification was made.

Table 3.3. Issues table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Number	n/a	Yes	primary key	A unique numeric ID that identifies an issue. Populated by an auto sequence.
asset_id	Int	10	No		ACL asset reference field.
alias	Varchar	10	No	None	Issue Alias used to mask primary key of the issue from random selection.
issue_summary	Varchar	255	Yes	None	A brief summary of the issue.
issue_description	Varchar	4000	No	None	A detailed description of the issue.
identified_by_person_id	Number	11	Yes	foreign key to People ‘id’	The user who identified the issue.
identified_date	Date	n/a	Yes	None	The date the issue was identified.
related_project_id	Number	n/a	Yes	foreign key to Projects table	Project related to the issue.
assigned_to_person_id	Integer	11	No	foreign key to the People table ‘user_id’	The person who is assigned to ‘work’ the issue.
issue_type	Integer	11	Yes	Foreign key to the it_types table.	The type of issue. i.e. Defect, Enhancement etc.
status	Integer	11	Yes	Check constraint (MySQL)	The issue status. Automatically set to Open when new and set to Closed when actual resolution date entered. Foreign key to the it_status table.
public	TidyInt	3	No	None	Privacy flag to indicate the issue contains private information.
state	Number	4	No	None	Indicates issue is visible in front end.
ordering	Number	11	No	None	Specifies ordering of issues.
checked_out	Number	11	Yes	None	Joomla field record locking
checked_out_time	Date	n/a	Yes	None	Joomla field record locking
priority	Integer	n/a	to	Check constraint (MySQL)	The priority of the issue. Foreign key to the it_priority table.
target_resolution_date	Date	n/a	No	None	The target resolution date.
progress	Mediumtext	-	No	None	The progress of the issue. Varchar(4000) pre 1.3.0 release.
actual_resolution_date	Date	n/a	No	None	Actual resolution date of the issue. Will also ensure that the issue status is set to ‘Closed’.
resolution_summary	Varchar	4000	No	None	Resolution summary
access	Integer	10	Yes	None	Access group rules for the record.
custom_fields	Text	N/A	Yes	None	Custom field values.
metadata	Varchar	N/A	Yes	None	Associated metadata for the record.
created_on	Date	n/a	Yes	None	Date the record was created.
created_by	Varchar	255	Yes	None	The user who created the record.
modified_on	Date	n/a	Yes	None	The date the record was last modified.
modified_by	Varchar	255	Yes	None	The user who last modified the record.

Note

To be more robust there should be more comprehensive auditing carried out and recorded. This is to enable the tracking of each change to the data rather than just the last change. Tracking each change to the data would require an additional table, linked to the issues table. If the valid priorities assigned to issues need to be dynamic, we would be required to add a separate table with a foreign key that relates to the issues table.
The progress field is also used to store some basic information if the issue is created in the front end of the site. For unregistered users, the username, the email address and website are stored in this field. For all users the Joomla version, Database type, and Product version are stored if the option is chosen from the component options.
In release 1.2.0 the identified_by_person_id fields is reference to the id field of a person in the it_people table. The assigned_to_person_id is reference to the user_id field in the it_people table. This means that any person identified in the it_people table can be used as the ‘identifier’ of an issue, but that only registered users can have issues assigned to them. A further restriction is also used such that the assigned user must be a ‘staff’ member.

Progress Table

The structure of the progress table is as shown below. This table is used to store the progress records for All issues. The records each have a unique number to record the consecutive number within each issue as well as individual control over the display and access for each record.

Table 3.4. Progress table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Number	n/a	Yes	Primary key	A unique numeric identification for each progress record. Populated by an auto-sequence.
asset_id	Number	n/a	No	None	FK to the #__assets table. Note: Not currently implemented.
issue_id	Integer	11	Yes	None	The system generated unique identifier for the specific issue. FK to issue table.
alias	Varchar	10	No	None	Issue Alias. Used to mask primary key of issue from random selection. From the issue record.
public	TinyInt	3	Yes	None	Again from the issue record, whether the issue is private or public applies to the progress records.
state	Number	4	No	None	Indicates that progress record is visible to front end.
checked_out	Number	11	Yes	None	Joomla field record locking
checked_out_time	Date	n/a	Yes	None	Joomla field record locking
ordering	Number	n/a	No	None	Order in which progress records are displayed. Not currently used as the order of creation is used instead. Possibility of future usage/expansion.
progress	MediumText	n/a	Yes	None	Any progress notes on the issue resolution. The field is required to be this size since on creation any existing information in the Issues table has to be transferred over to this table, and making it any smaller may truncate and hence remove important progress data for existing issues. In reality it probably does not need to be any larger than a 'Text' field.
lineno	Integer	11	Yes	None	Unique value defining the default order of the various progress updates for a specific issue.
access	TinyInt	3	Yes	None	Required for controlled group access.
created_on	Date	n/a	Yes	None	Date the record was created.
created_by	Varchar	255	Yes	None	The user who created the record
modified_on	Date	n/a	Yes	None	The date the record was last modified.
modified_by	Varchar	255	Yes	None	The user who last modified the record.

Attachments Table

The attachments table contains the details of all attachments (images or files) associated with raised issues.

Table 3.5. Attachments table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Number	n/a	Yes	primary key	A unique numeric ID that identifies a priority. Populated by an auto sequence.
issue_id	Varchar	10	Yes	Foreign key to associated issue.	Associated issue.
uid	Int	11	No	None	User id of the user attaching the file.
title	Varchar	255	Yes	None	Title for attachment.
description	Mediumtext	-	Yes	None	Description of the file attachment.
filepath	Mediumtext	1024	Yes	None	Path to the file in the system.
filename	Varchar	255	No	None	Original name of the file attachment.
hashname	text	-	No	None	Hash of file name and date string.
filetype	Varchar	255	No	None	Type of file attachment. Default `application/octet-stream`
size	Integer	10	Yes	None	Size of file attachment.
state	Number	4	No	None	Indicates issue is visible in front end.
ordering	Number	11	No	None	Specifies ordering of issues.
checked_out	Number	11	Yes	None	Joomla field record locking
checked_out_time	Date	n/a	Yes	None	Joomla field record locking
created_on	Date	n/a	Yes	None	Date the record was created.
created_by	Varchar	255	Yes	None	The user who created the record.
modified_on	Date	n/a	Yes	None	The date the record was last modified.
modified_by	Varchar	255	Yes	None	The user who last modified the record.

Priorities Table

The priorities table is a basic structure with future expansion in mind. In the release 1.1.0 only the priority_id, priority_type, state and description are used. Release 1.3.0 uses the priority ranking to implement colour coding for the associated issues. The additional fields will be implemented in a future version.

Table 3.6. Priorities table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Number	n/a	Yes	primary key	A unique numeric ID that identifies a priority. Populated by an auto sequence.
priority_name	Varchar	60	Yes	None	The name used to display on the screens.
response_time	Decimal	(11,2)	Yes	None	The target response time expressed in hours.
ranking	Int	11	Yes	None	The ranking of the priority expressed as a value between 0 and 100. Higher numbers indicate higher priority.
resolution_time	Decimal	(11,2)	Yes	None	The target resolution time expressed in hours.
description	Varchar	1024	No	None	A brief description of the issue type.
state	Number	4	No	None	Indicates issue is visible in front end.
ordering	Number	11	No	None	Specifies ordering of issues.
checked_out	Number	11	Yes	None	Joomla field record locking
checked_out_time	Date	n/a	Yes	None	Joomla field record locking
created_on	Date	n/a	Yes	None	Date the record was created.
created_by	Varchar	255	Yes	None	The user who created the record.
modified_on	Date	n/a	Yes	None	The date the record was last modified.
modified_by	Varchar	255	Yes	None	The user who last modified the record.

	Note
	Priority records may not be deleted whilst they are in use by any issue.

Roles Table

The roles table is used to assign different roles to the people registered upon the site. The supplied sample roles are expandable and cover the basic type of activities performed by people.

Table 3.7. Roles table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Number	n/a	Yes	Primary key	A unique numeric ID that identifies a role. Populated by an auto sequence.
role_name	Varchar	60	Yes	None	The name used to display on the screens.
description	Varchar	1024	No	None	A brief description of the role.
state	Number	4	No	None	Indicates issue is visible in front end.
ordering	Number	11	No	None	Specifies ordering of issues.
checked_out	Number	11	Yes	None	Joomla field record l1cking
checked_out_time	Date	n/a	Yes	None	Joomla field record locking
created_on	Date	n/a	Yes	None	Date the record was created.
created_by	Varchar	255	Yes	None	The user who created the record.
modified_on	Date	n/a	Yes	None	The date the record was last modified.
modified_by	Varchar	255	Yes	None	The user who last modified the record.

	Note
	Issue types may not be deleted whilst they are being used by any issue.

Status Table

The status table contains a list of different statuses that an issues may be in. For example an issue is often Opened, then goes through being ‘Assigned’, then in being worked upon it in ‘In-Progress’, before being resolved, and undergoing ‘QA Testing’, released to the client, when it goes into a ‘Waiting for Customer’ before being ‘Closed’. Such a mechanism is easy to create in Issue Tracker.

Table 3.8. Status table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Number	n/a	Yes	primary key	A unique numeric ID that identifies a status. Populated by an auto sequence.
status_name	Varchar	60	Yes	None	The name used to display on the screens.
description	Varchar	1024	No	None	A brief description of the status.
state	Number	4	No	None	Indicates issue is visible in front end.
ordering	Number	11	No	None	Specifies ordering of issues.
checked_out	Number	11	Yes	None	Joomla field record locking
checked_out_time	Date	n/a	Yes	None	Joomla field record locking
created_on	Date	n/a	Yes	None	Date the record was created.
created_by	Varchar	255	Yes	None	The user who created the record.
modified_on	Date	n/a	Yes	None	The date the record was last modified.
modified_by	Varchar	255	Yes	None	The user who last modified the record.

	Note
	Status records may not be deleted whilst they are being used by any issue.

Issue Types Table

The Issue Types relate to the different types of issues that may be raised. For example an Issue may be upon a ‘Defect’ of a product that is produced, or may reflect a ‘Documentation’ feature, or a ‘Suggestion’ for product improvement.

Table 3.9. Issue Types table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Number	n/a	Yes	primary key	A unique numeric ID that identifies an issue type. Populated by an auto sequence.
type_name	Varchar	60	Yes	None	The name used to display on the screens.
description	Varchar	1024	No	None	A brief description of the issue type.
state	Number	4	No	None	Indicates issue is visible in front end.
ordering	Number	11	No	None	Specifies ordering of issues.
checked_out	Number	11	Yes	None	Joomla field record locking
checked_out_time	Date	n/a	Yes	None	Joomla field record locking
created_on	Date	n/a	Yes	None	Date the record was created.
created_by	Varchar	255	Yes	None	The user who created the record.
modified_on	Date	n/a	Yes	None	The date the record was last modified.
modified_by	Varchar	255	Yes	None	The user who last modified the record.

Email Types Table

More specifically this is really a Message Template table. This table relate to the different types of messages, emails and SMS that may be send from the Issue Tracker component. For example an email may be send when an issue is opened, updated or closed. Likewise an SMS mesage may be despatched if the AcySMS component is installed, which is used for the SMS sending interface.

Messages can be sent to issue administrators, assignees who would work the issue, or the user who raised the issue in the first place. Global options control the send of the specific message types, and a global notification override, along with optional notifications for individual users. Administrators and assignee will receive messages unless the global options are set not to do so.

The stored text format is HTML.

Table 3.10. Email types table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Number	n/a	Yes	primary key	A unique numeric ID that identifies an issue email type. Populated by an auto sequence.
type	Varchar	60	Yes	None	The messagel type name used to display to control the email sending. These are hard coded into the component and should not be changed.
description	Varchar	1024	No	None	A brief description of the issue email type.
subject	Varchar	60	Yes	None	The text to be inserted into the email header.
body	Varchar	2048	Yes	None	The text that is used for the email body. May also include tags which are substituted by the details from the issue itself with which the email is concerned.
state	Number	4	No	None	Indicates issue is visible in front end.
ordering	Number	11	No	None	Specifies ordering of issues email types.
checked_out	Number	11	Yes	None	Joomla field record locking
checked_out_time	Date	n/a	Yes	None	Joomla field record locking
created_on	Date	n/a	Yes	None	Date the record was created.
created_by	Varchar	255	Yes	None	The user who created the record.
modified_on	Date	n/a	Yes	None	The date the record was last modified.
modified_by	Varchar	255	Yes	None	The user who last modified the record.

When installed a number of message types are automatically created in thedatabase table and these are detailed below:

Table 3.11. Message types description

Template name	Type	Description
admin_close	Email	Used when an Email message is sent to the issue administatrors for an Issue closure.
admin_new	Email	Used when an Email message is sent to the issue administartors when a new Issue is created.
admin_update	Email	Used when an Email message is sent to the issue administartors for an Issue update.
ass_close	Email	Used when an Email message is sent to the issue assignee for an Issue closure.
ass_new	Email	Used when an Email message is sent to the issue assignee when a new Issue is created.
ass_update	Email	Used when an Email message is sent to the issue assignee for an Issue update.
sms_close	SMS	Used when a SMS message is sent to a user, an assignee or the issue administatrors for an Issue closure.
sms_new	SMS	Used when a SMS message is sent to a user, an assignee or the issue administartors when a new Issue is created.
sms_update	SMS	Used when a SMS message is sent to a user, an assignee or the issue administartors for an Issue update.
user_close	Email	Used when an Email message is sent to the user (issue creator) for an Issue closure.
user_new	Email	Used when an Email message is sent to the user (issue creator) when a new Issue is created.
user_update	Email	Used when an Email message is sent to the user (issue creator) for an Issue update.

Issue Log Table

The Issue Log table is used by the logging routines to store information, of different priorities that are used to provide additional information upon the operation of the component. It is built on top of the standard Joomla JLog libraries.

Table 3.12. Log table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Number	n/a	Yes	primary key	A unique numeric ID that identifies an issue type. Populated by an auto sequence.
priority	Number	11	Yes	n/a	The priority of the message. i.e. Alert, Info, Notice, Warning etc.
message	Varchar	512	No	n/a	The text message to store.
date	Date		No	n/a	The date the message was generated.
category	Varchar	255	No	n/a	The message category. Default ‘com_issuetracker’

Table 11 - Issues Log Table

There is a compound index upon the category, date and priority columns in that order.

Change History Table

The Change History table contains the change records and is populated by the database triggers created and enabled in the MySQL database.

Table 3.13. Change History table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Bigint	20	Yes	Primary key	The system generated unique identifier for the record. Populated by an auto-sequence.
table_name	Varchar	256	Yes	None	Name of the table for which the change is recorded.
component	Varchar	255	Yes	None	The component to which the change record applies. The default value is 'Issue Tracker'.
state	TinyInt	4	Yes	None	State of the specific record. i.e. Published, archived, trashed etc.
row_key	Int	11	No	None	The value of the primary key for the record.
row_key_link	Text	255	No	None	The value of the alias for the record if it exists, the value of the Primary key otherwise.
column_name	Varchar	255	Yes	None	The name of the table column being recorded.
column_type	Varchar	12	Yes	Varchar	The type of column for the record. i.e. Integer, Varchar etc.
old_value	MediumText		No	None	For an DELETE or UPDATE action the former field value.
new_value	MediumText		No	None	For an UPDATE or INSERT action the new field value.
action	Varchar	12	No	None	The action of the change record. i.e.INSERT, UPDATE or DELETE.
change_date	DateTime	n/a	No	None	The date when the change was made.
change_by	Int	11	No	None	The Joomla id of the person who made the change where it can be determined otherwise the super user id.

Triggers Table

The triggers table contains details of the triggers created by the Issue Tracker component.

Table 3.14. Triggers table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Int	11	Yes	Primary key	The system generated unique identifier for the record. Populated by an auto-sequence.
table_name	Varchar	255	Yes	None	Name of the table for which the trigger applies.
trigger_schema	Varchar	255	Yes	None	The database schema to which the trigger applies. MySQL databases may have multiple schemas.
trigger_name	Varchar	255	Yes	None	The name of the trigger. This is automatically generated based upon the name of the table upon which it is apples.
trigger_event	Varchar	255	Yes	AFTER	The type of trigger. i.e. BEFORE or AFTER. Some databases may contain other trigger events.
trigger_event	Varchar	255	Yes	INSERT	The type of trigger, or more accurately when the trigger fires. i.e. INSERT, UPDATE or DELETE.
trigger_text	Medium Text		Yes	None	The actual trigger text from the specified criteria.
columns	Text	255	Yes	All	The columns to which the trigger applies.
action_orientation	Varchar	10	No	ROW	How the trigger is applied.
applied	TinyInt	4	Yes	Inactive	State of the specific trigger. i.e. Active or Inactive.
created_by	Integer	11	Yes	None	The Joomla id of the use who created the trigger text. Automatically populated.
created_by_alias	Varchar	255	No	None	The name of the person who created the trigger text.
created_on	DateTime	n/a	No	None	Date the record was changed.

Custom Field Table

This table stores the define custom fields required by a site to capture additional information that will assist in resolving raised issues.

Table 3.15. Custom Field table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Number	n/a	Yes	Primary key	A unique numeric identification for each project (or sub-project). Populated by an auto-sequence.
name	Varchar	255	Yes	None	Name for the custom field.
value	Text	n/a	Yes	None	Default value for the field.
type	Varchar	255	Yes	None	Type of the custom field.
tooltip	MediumText	n/a	No	None	Text for the tooltip of the field, if any. Probably too large a field, perhaps Varchar(255) might be more suitable.
validation	MediumText	n/a	No	None	Validation rules for the field. Probably too large a field, perhaps Varchar(255) might be more suitable.
access	TinyInt	3	Yes	None	Required for controlled user group access.
group	Integer	11	Yes	None	Name of the group for which this field is part. Or to be precise a numeric value that relates to the entry in the Custom Field Group table. FK to the Custom Field Group.
state	Number	4	No	None	Indicates that field is visible to front end.
ordering	Number	n/a	No	None	Order in which fields are displayed
checked_out	Number	11	Yes	None	Joomla field record locking
checked_out_time	Date	n/a	Yes	None	Joomla field record locking
created_on	Date	n/a	Yes	None	Date the record was created.
created_by	Varchar	255	Yes	None	The user who created the record
modified_on	Date	n/a	Yes	None	The date the record was last modified.
modified_by	Varchar	255	Yes	None	The user who last modified the record.

Custom Field Group Table

The custom field group stores the details of all of the defined custom groups. A custom group is associated with one or more projects.

Table 3.16. Custom Field Group table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Number	n/a	Yes	Primary key	A unique numeric identification for each custom field group. Populated by an auto-sequence.
name	Varchar	255	Yes	None	Name of the custom field group.
state	Number	4	No	None	Indicates that group is visible to front end.
checked_out	Number	11	Yes	None	Joomla field record locking
checked_out_time	Date	n/a	Yes	None	Joomla field record locking
created_on	Date	n/a	Yes	None	Date the record was created.
created_by	Varchar	255	Yes	None	The user who created the record
modified_on	Date	n/a	Yes	None	The date the record was last modified.
modified_by	Varchar	255	Yes	None	The user who last modified the record.

Meta Data Table

The Meta table is used by the component itself to record version numbers of its component parts. i.e. Modules, plugins etc.

Table 3.17. Meta data component table description

Column Name	Type	Size	Not Null?	Constraints	Description
id	Int	11	Yes	Primary key	The system generated unique identifier for the record. Populated by an auto-sequence.
version	Varchar	100	Yes	None	Version number of the installed component.
type	Varchar	20	Yes	None	Type of extension.

Implementing Database Objects

This first step in building an application was to create the database objects. The tables have been defined above but there are a few other database objects we require.

Additional Database Objects Needed

To populate the primary key values of the tables needed for this application, an auto incrementing sequence is used in the MySQL implementation.

Triggers are present upon the tables for insert and update. These triggers are used to set the values of the audit fields (if they are not set by the application) and also to set curtain defined business criteria. These database triggers act as a secondary mechanism in case data is entered into the database outside of the application. (The addition of the sample data is performed in this way.)

Certain database procedures are used to encapsulate mechanisms into the database. i.e. The sample data is provided in the form of a database procedure, and the application provides a call to the procedures.

About Building Database Objects

There are several tools for creating objects in MySQL. You can:

Use phpAdmin to create tables, views, indexes, procedures, functions, triggers etc.
Use a product such as Toad, which provides the same abilities as phpAdmin with a cleaner interface. This is downloadable from the Quest website.
Create a script in a standard text editor and then upload the script and use the MySQL command interface, Toad, or phpAdmin to execute it.

Default Base Data

There are a few provided records inserted into the tables upon installation.

The projects table has a Root entry created with an id value of 1, which acts as the base project from which all other projects derive. This particular 'Root' project is 'hidden' from viewing or changing within the component.

There is also a default 'Unspecified Project' created with an id value of 10. This projects acts as the project to which all issues are assigned if another project is not specified. There is a component option to change this if desired.

The people table is populated with a default 'Anonymous' user, usually with an id value of 1. In rare instances, such as where the id value is already in use, as would be the situation were the root id have such a value, the 'Anonymous' id value would then become 2. This entry is used to populate any new issues with the identifying user, unless another is chosen upon issue creation.

The default project and default person may be edited and altered in any way, and once the component options are set, can if not used as ‘default settings’ be safely removed, although this is not necessarily recommended.

There are also sample reasons, priorities, issue types, statuses, email templates etc provided that the site administrator can edit and modify to reflect site design. These may be changed and will not be generally be updated as a result of upgrading the component. See the release notes for the particular upgrade where there would be a specific mention were this to change.

Prev	Up	Next
Front End Download of Attachments	Home	Chapter 4. CSS Styling