9.3. Database Considerations

Because each database implements the SQL standard slightly differently, each database needs a slightly different schema for RT. One of the primary concerns is to ensure each object has a unique id field. To make things simple, RT handles the various compatability issues internally on your behalf. We can see how some of this is achieved by taking a look at several examples contained in the SQL that creates the Tickets table.

For MySQL, RT specifies an auto-incrementing id field directly within the CREATE TABLE statement. The Subject field takes a default value, and the Started field takes the current datetime if the value is NULL.

     CREATE TABLE Tickets (       id INTEGER NOT NULL AUTO_INCREMENT,       Subject varchar(200) NULL DEFAULT '[no subject]' ,        Started DATETIME NULL,       ...     ); 

For Oracle, RT defines an automatically incrementing sequence at installation time and uses it when the RT::Record class creates a new object. The Subject field takes a default value, and the Started field must be filled in manually. Again the RT::Record class takes care of this for us internally.

     CREATE TABLE Tickets (       id NUMBER(11, 0),       Subject VARCHAR2(200) DEFAULT '[no subject]',       Started DATE,       ...     ); 

As you can see, RT accommodates the vagaries of each database type. It not only ensures a unique identifying integer for the id field, but it also adapts the datatypes: VARCHAR2 for Oracle, VARCHAR for Informix, and so on. RT also accommodates Postgres, SQLite, and Sybase seamlessly.

9.3.1. Id

Because RT already has taken care of all these things internally, probably the most important thing to remember when making any changes to the default schema is to make sure it's always easy to determine the row in the database to which an object relates. DBIx::SearchBuilder expects every database table it works with to have a single auto-incrementing integer primary key called id. Other than that, you are more or less free to design your own schema or modify RTs, so long as the code reflects the tables you create.

9.3.2. JoinsJoins

Internally, RT generates its own SQL statements from the criteria specified in the various user interfaces and the currently excuting tasks.

9.3.2.1. Inner joins

Normally when RT joins one table to another table, the type of join generated is an inner join. This rejects rows that do not have a matching row in both tables with an identical value in a particular (usually unique) column as not satisfying the requirements of the query.

For example, you might ask RT to return a list of users and their tickets constrained by ticket status. In this case you would only want to see the users with tickets whose status matched to the criteria given.

9.3.2.2. Outer joins

Occasionally RT is required to perform a left (outer) join. This accepts records from the left-hand table even if they do not have matching rows in the right-hand table. In this case RT still returns the rows in the left-hand table with null rows (no data) for the right-hand table.

For example, you might ask RT to return a list of users and their tickets. Some users in the database might not have any tickets yet. You would still want to see the entire list of users, both those with tickets and those without tickets.

9.3.3. Transactions and Data Integrity

Transactions are handled on a per action basis. In other words, when a new ticket is created, all the elements that make up the new ticket are handled as a single transaction. For example, creating a ticket might involve the following actions:

• Create a new ticket.

• Create a new user if the user has not been seen before, and ACL controls allow anyone to create a new ticket.

• Assign the ticket to an existing group (optional).

• Assign the ticket to an existing queue (optional).

If any step along the way fails, the ticket is not created. You always have the opportunity to fix the errant behavior and to retry the transaction.