Unlike many database systems, a Tracker database has no explicit schema or rigid structure, which lets it adapt readily to the changes that occur as an application matures.

Data in a Tracker database is stored in fields. Each field has a name, a value, and a type. The database field types are the same as the PDL field data types.

Fields are organized into entities or records. Each entity represents something from the real world, such as a bug report, project list, or project team, and the fields represent that item's properties or attributes. Every entity has a field named $ENTITY_ID that contains a permanent integer value that is assigned automatically by the database upon creation.

Entity Fields

Although the entities within a class tend to have the same set of fields, this is not required by the Tracker database. An entity cannot have more than one field with a given name. However, different entities can have fields with the same name; fields with the same name can be of the same or different types.

As an example, the RTS database has two entity classes: one named tracker_request (the default) and the other named project (see Figure 3-1). Entities in the tracker_request entity class represent requests in the database. Potentially, they can use all of the fields defined in the PDL.

Figure 3-1. RTS Database with Entity Classes

The actual information depends on which field values were entered. The entities in the project entity class are used to keep track of project names and managers.

When you enter a new entity into a database, you can enter a value for any field declared in the PDL file. You can also enter new fields if you have a special requirement.

Queries from the graphical user interface are transmitted in DML to the database and can access only fields defined in the associated PDL file. From the DML interface, you can use PDL field definitions or you can define your own fields.

Figure 3-2 illustrates the relation of the dml program to the two interfaces.

Figure 3-2. Relation of DML to Tracker and PDL

The database designer or system administrator can control who is able to make changes to a database through DML. This is done by means of the UNIX file permissions on a file called Tracker.sec in the database directory. The Tracker.sec file is the control point for access to the database. Its permissions, which match those on the database, provide security for the information in the database.

When a database is created, the creator can set the file permissions on Tracker.sec to give read and/or write permission to selected people or groups. Tracker sets the initial permissions to give ownership to the person who is running tvgen.

Each database has its own Tracker.sec file, which may allow different users access. For example, database A may have file permissions which give read/write permission to the owner, but read permission only to everyone else (-rw-r--r--), and database B may give read/write access to the owner and specified group, but no access to others (-rw-rw----). A user without read permission for a particular database cannot even start the DML interpreter program, dml.

---

Note: If you use databases created with an earlier version of Tracker (version 1.0 or 1.0.1), you can add a Tracker.sec file to these databases if you wish.

---

All DML statements are terminated by a semi-colon character.

It is important when using DML to specify the names of the fields exactly, since incorrect field names can be interpreted as non-existent or new. If you don't have a copy of the PDL field declarations handy, you can select a single entity designating * as the field list and get the proper spellings. Refer to “Select Statement” for more information.

For your convenience, DML provides alternatives for entering literal values in statements:

dml> value typing on;
dml> select $ENTITY_ID from tracker_request where $ENTITY_ID <3;
$ENTITY_ID: int '1'

$ENTITY_ID: 1

The select statement queries the Tracker database and returns field values. It includes an optional order by statement that lets you sort the items within each field in ascending or descending order.

The general form is:

select field-list from entity-class;

field-list names the Tracker fields returned by the query for each selected entity. This form selects all entities of class entity-class. The optional additions to this statement, shown in square brackets, are:

select field-list from entity-class
            [order by field1 descending, field2, ...];
select field-list from entity-class [where condition];

The items in the fields listed in order by without a modifier will be sorted in the default order, which is ascending. To sort in descending order, add the keyword descending after the field name. The condition expression in the second form determines which entities are selected by the query. For example, the statement:

select $ENTITY_ID, Customer, Submit_date from bug where
Engineers_pri = 1;
order by Submit_date descending;

returns a table containing the $ENTITY_ID, Customer, and Submit_date fields for all bugs with Engineers_pri equal to 1, and listed according to date of submission. For example:

$ENTITY_ID: 40274
customer: acme
submit_date:Wed Jun 10 21:57:33 1992 
$ENTITY_ID: 39567
customer: xyzco
submit_date: Fri May 29 10:09:00 1992

You can substitute an asterisk (*) for field-list in a select statement, which then retrieves all fields for qualifying entities:

select * from bug where Engineers_pri = 1;

Typically, entities do not have data in all available fields. When performing a query, the DML retrieves those fields named in the field list that do exist and ignores the entity's empty fields.

The condition expression can contain multiple field comparisons combined using the and, or, and not operators and is not limited to equality comparisons.

For example,

select $ENTITY_ID, Customer, Submit_date from bug where
(Engineers_pri > 1 and Type = BUG) or 
(Engineers_pri < 4 and Type = RFE) or 
(Type <> BUG and Type <> RFE and Priority = P1);

Table 3-4 lists the available comparison operators.

The following example further illustrates comparison operators:

select $ENTITY_ID from bug where 
Engineers_pri = null and  //unset fields match 
Type = [BUG, TAKEN] and   //Type=BUG or Type=TAKEN
Severity = [1, 5:10];     //Severity=1 or is 
                          //between 5 and 10

Value ranges take the form

[..., value:value, ...]

They are recognized only within value lists, that is, inside square brackets ([]). For example,

Severity = 5:10

is not legal; rather, you must use the following form:

Severity = [5:10]

The match comparison operator allows regular expression matching in text fields. The regular expression must be supplied as a quoted string literal in the form described by the regcmp(3X) man page. For example, the query:

select owner from bug where summary match '[Ww]indow';

retrieves all entities whose summary fields contain the word window or Window.

The contains operator determines if a list-of field contains a specified list element. For example, the query:

select * from project where engineers contains 'billy';

retrieves all project entities where the engineers field includes billy.

The contains any operator is similar to contains; it is used to specify multiple list elements to be contained within the list-of field. Thus, the supplied literal value must be a list itself. It is equivalent to Ored contains statements. For example,

select * from project where engineers contains any
('billy', 'bob');

is equivalent to:

select * from project where engineers contains 'billy' or
engineers contains 'bob';

The contains only operator determines if the list-of field contains some subset of elements from the supplied list. If a field contains any elements not found in the supplied list, the entity is not selected. For example, the following statement:

select * from project where engineers contains only
('fred', 'bob');

will select only entities whose engineers field is one of the following (ignoring duplicate elements):

()           // empty list 
('fred') 
('bob') 
('fred', 'bob') 
('bob', 'fred')

Both contains any and contains only can substitute a nested select statement for a list. The nested select builds an integer list from the $ENTITY_ID fields of the selected entities and uses that list in the enclosing expression.

For example, consider the clause

... where project_ids contains any (select $ENTITY_ID from
projects where name match 'TV';

The $ENTITY_ID values for those projects matching the string TV are substituted into the where clause.

Nested select statements offer an alternative to specifying lists of integers in DML statements. The select statement return value must be of type list-of int.

In evaluating a nested select, DML first executes the nested select statement, then builds an integer list from the $ENTITY_ID fields of the selected entities, and uses the list in the enclosing expression.

Currently, only the $ENTITY_ID fields of the selected entities can be used in the enclosing expression. Even if the field list in the nested select selects another field or fields, the $ENTITY_ID fields always construct the list that evaluates the enclosing expression.

For example, consider the clause

... where project_ids = (select $ENTITY_ID from projects
where name match 'TV';

The $ENTITY_ID values for those projects matching the string TV are substituted into the where clause. The where clause is true if the variable project_ids equals the list of qualifying projects.

The insert statement is used to add new entities to the database. The two general forms are:

insert into entity-class set field1 = value1, ... fieldN = valueN;
insert into entity-class set field1 = value1, ... fieldN = valueN
where condition;

Each field = value pair creates a field in the database and assigns it a value. The insert statement automatically creates a $ENTITY_ID field and assigns it a unique integer value within the class; it cannot appear explicitly in the list of field assignments. For example, consider the statement:

insert into bug set Id = 45799, System = SCR, 
Customer = 'John Doe, Inc.', Description = long-text
'Description with embedded newlines.';

It creates a new entity with five fields: the four specified explicitly and the $ENTITY_ID field.

The second form of the insert statement adds a new entity to the database if no entity matching the where clause already exists. If one or more entities matching the where clause do exist, they are updated as if an update statement had been executed and no new entity is created.

The update statement is used to modify existing entities. The general form is:

update entity-class set field1 = value1, ... fieldN = value1
where condition;

Each field assignment in the list is applied to all existing entities selected by the condition expression. The condition expression is identical to that described for the select statement. For example, the statement:

update bug set Engineers_pri = 3
where Engineers_pri = 2;

modifies all bugs with Engineers_pri equal to 2, changing the Engineers_pri field to 3.

The delete statement removes entities and their fields from the database. The general form is:

delete entity-class where condition;

The where clause holds the condition necessary to delete the request from the database. Use the delete statement with caution so that you don't inadvertently remove good data.

The lock and unlock statements obtain shared, non-exclusive locks on the specified entities. A locked entity can be modified only by the holder of the lock, although other users can read its field values. Any attempt to update or delete a locked entity will generate an error from the DML processor.

The general form of the statement is:

lock entity_class1 where condition1
[, entity_class2 where condition2 ...];

or

unlock entity_class1 where condition1
[, entity_class2 where condition2 ...];

The Tracker GUI automatically locks and unlocks entities as needed to guarantee that the edits made by the user can be committed to the database. All locks are released when the holder disconnects from a Tracker database.

The begin and end transaction statements are used to combine a series of database modifications into an atomic operation; if any part of a transaction cannot be performed, then none of it is performed. They are used as follows:

begin transaction; 
       dml statement;
       dml statement;
   ...
end transaction;

You cannot nest transactions.

The use of transactions can have a dramatic effect on the performance of certain database operations. For example, in creating a script to import a large number of entities, say more than 100, performance can be improved by grouping the insert statements into transactions with several insert statements per transaction. Without grouping into explicit transactions, each statement that modifies the database is treated as a transaction. By reducing the total number of transactions through grouping, the resources needed to accomplish a large task can be reduced.