In RT, the DBIx::SearchBuilder
module is responsible for connecting to the database, building the SQL required—whether searching, creating, or editing data—and returning the data to the RT interface. It supplies the API (Application Programming Interface), which provides access to the data stored in the database and is an essential component of RT.
More than that, because this module has been built to execute SQL in a generic manner and to run against multiple different types of databases, your own programs also can use it. In this chapter we discuss DBIx::SearchBuilder in some detail, which will enable you to understand what it does behind the scenes for RT and how to leverage its functionality for your own nefarious purposes.
DBIx::SearchBuilder is not supposed to be used directly for each record. It is designed to act as a base for other modules that add the appropriate behavior. Any application that uses the DBIx::SearchBuilder suite is likely to start by using the DBIx::SearchBuilder::Record modules as a wrapper for a particular object.
Because the easiest way to understand something is to use it, we’ll write some simple code to interface with DBIx::SearchBuilder. Note that the code here is minimal so that you can concentrate on the interface. For more complex examples, simply browse the RT source code libraries themselves.
To show how this fits together, let’s begin with a database handle, which every object needs if it wants to talk to the database.
The first thing to do is to load the appropriate module.
use DBIx::SearchBuilder::Handle;
Followed by a call to its constructor:
my $handle = DBIx::SearchBuilder::Handle->new();
This is a generic database handle object that still needs to connect to a database before it can do anything useful. The Connect method connects to a database:
$handle->Connect(
Driver => 'mysql',
Database => 'dbname',
Host => 'hostname',
User => 'dbuser',
Password => 'dbpassword'
);The following example is a simple command sent to an Oracle database to return and print the current date:
print $handle->FetchResult("SELECT sysdate FROM dual");
30-DEC-04Although the $handle is a generic database handle, it is not a DBI handle. However, if you want to ping the database, or use any other standard DBI handle method, you can retrieve the DBI handle itself with the dbh method and use it directly:
$dbh = $handle->dbh;
die "Couldn't talk to the database" unless $dbh->ping;
To create some code to handle queries and retrieve data from the database, we need to write a small module wrapper around DBIx::SearchBuilder::Record. The following short chunk of code creates a small package called Tkt as a limited window on the tickets table in our database.
1 package Tkt;
2
3 use DBIx::SearchBuilder::Handle;
4 use base qw(DBIx::SearchBuilder::Record);
5
6 sub _Init {
7 my $self = shift;
8 my $handle = DBIx::SearchBuilder::Handle->new;
9 $handle->Connect(Driver => 'Oracle', User=>'rt', Password=>'rt');
10 $self->_Handle($handle);
11 $self->Table('Tickets');
12 }
13
14 sub _ClassAccessible {
15 {
16 Id => { read => 1 },
17 Status => { read => 1, write => 1 },
18 Subject => { read => 1 },
19 };
20 }
21
22 1;First, we inherit all of DBIx::SearchBuilder::Records methods (line 4). The _Init subroutine creates the necessary database handle (line 8) and connects to the database (line 9). Next, the Table method registers the Ticket table as our data source (line 11). Finally the _ClassAccessible subroutine (line 14 to line 20) specifies certain fields and their access rights.
Using our new module is as simple as the following few lines of code:
1 use Tkt;
2
3 my $rec = Tkt->new;
4
5 $rec->Load(21);
6
7 printf("%s: %s (%s)\n", $rec->id, $rec->Subject, $rec->Status);We instantiate the object (line 3), then query the database and load the complete record into it, identified by the unique ID 21 (line 5). At this point we simply print out the relevant fields (line 7) using the accessor methods from the _ClassAccessible subroutine we declared in our module earlier. The output displays the requested information:
21: My frobnitz is broken (open)
We also can update this record in the database using automatically created mutator methods. To change the status we pass the SetStatus method an appropriate argument (in this case resolved) and print the new values for reference. Repeating the command on line 7 prints out and confirms the change of status for our ticket:
8
9 $rec->SetStatus('resolved');
10
11 printf("%s: %s (%s)\n", $rec->id, $rec->Subject, $rec->Status);
12The output displays the now modified information:
21: My frobnitz is broken (resolved)
Note that if you attempt to modify or write to a field that is not declared in the _ClassAccessible subroutine as write => '1', the column is not updated and the method returns an appropriate “Immutable field” message. We should then print out the subject line to reassure ourselves of the true value of the field (line 14):
13 print "Update: ".$rec->SetSubject('a new subject line')."\n";
14
15 print "Subject: ".$rec->Subject."\n";
16The current subject line for reference follows the failed output message:
Update: Immutable field
Subject: My frobnitz is brokenWhen we’re working with the code, it might be nice to know what values are readable and writable, without having to search through the code for the relevant definitions. Fortunately DBIx::SearchBuilder::Record helps us out here, too, by providing a pair of handy methods that print out an array of the actual attributes for both cases. The following example demonstrates the ReadableAttributes method:
17 print "Readable attributes: ".join(', ', sort $rec->ReadableAttributes)."\n";The output correctly prints out the readable attributes for this object.
Readable attributes: Id, Status, Subject
The following example is the matching WritableAttributes method:
18 print "Writable attributes: ".join(', ', sort $rec->WritableAttributes)."\n";The output again confirms that the only field of this object that we may modify is the status field.
Writable attributes: Status
DBIx::SearchBuilder provides several other useful utility methods. One, called Table(), prints out the name of the table where the object gets its data.
19 print "Table: ".$rec->Table."\n";
The output confirms the table name for our object.
Table: Tickets
Another useful method is PrimaryKeys(). This helpfully tells us not only what the table’s primary keys are called, but it also returns the values for the current object, if it has any.
20 print "Primary keys: ".join('=', $rec->PrimaryKeys)."\n";The output confirms that we have a single primary key with a name of id, and that for the instance of the object which we are currently working with, it has the value of 21.
Primary keys: id=21
This API isn’t only suitable for searching existing data records, it also creates and deletes them. When using the Create() method, the important thing to bear in mind is that you are responsible for checking the validity of the primary keys and any mandatory fields. Indeed, this is what RT itself handles when it wraps DBIx::SearchBuilder::Record methods.
First, we Create() an object with an appropriate primary key, and several attribute values.
21 $rec->Create(Id => 12345, subject=>'This is a new Ticket', status=>'fresh');
Next, we use the Load method we saw earlier to read this object cleanly from the database.
22 $rec->Load(12345);
Now we reuse the code from line 7 again, to print out the relevant values, and take a look at our new object.
23 printf("Ticket %s: %s (%s)\n", $rec->id, $rec->Subject, $rec->Status);The resulting line confirms the new record was created satisfactorily.
Ticket 12345: This is a new Ticket (fresh)
The matching method is Delete() and is even simpler to use. It requires no arguments and simply deletes the current object from the database.
24 $rec->Delete;
Although this is a small example, you can see this is both simple and powerful. The code is generic and adaptable for many purposes. RT uses this flexibility to create a core of code that can manage a complicated application with consummate ease.
DBIx::SearchBuilder::Record offers a number of other useful methods. To read more about the available functionality that Tkt inherits, read the documentation for DBIx::SearchBuilder::Record.
We’ve seen how to use DBIx::SearchBuilder::Record to work with a single record or object. A similar approach applies to collections of records. This section uses DBIx::SearchBuilder to handle many records at once.
The following code is a wrapper around DBIx::SearchBuilder. It is remarkably similar to the example in the previous section. The main differences are that we inherit from DBIx::SearchBuilder directly (line 4), and we use the Tkt class from the previous section to return each object from the database (line 17) in turn.
1 package Tkts;
2
3 use Tkt;
4 use base qw(DBIx::SearchBuilder);
5
6 sub _Init {
7 my $self = shift;
8 my $handle = DBIx::SearchBuilder::Handle->new;
9 $handle->Connect(Driver => 'Oracle', User=>'rt', Password=>'rt');
10 $self->_Handle($handle);
11 $self->Table('Tickets');
12 return ( $self->SUPER::_Init(Handle => $handle) );
13 }
14
15 sub NewItem {
16 my $self = shift;
17 return(Tkt->new);
18 }
19
20 1;Now we use a short piece of code to get a handle (line 3) to define our search criteria (line 5). We print out the number of records found (line 7). Note that an alternative to using the Limit method is to use the UnLimit method, which returns all records.
1 use Tkts;
2
3 my $recs = Tkts->new;
4
5 $recs->Limit( FIELD => 'Subject', OPERATOR => 'LIKE', VALUE => '%urgent%');
6
7 print "Tickets: ".$recs->Count()."\n";
8It prints something like:
Tickets: 5
At this point we loop through each object, printing out both the subject and status of each ticket to receive feedback on each issue. Note that each object returned from the Next() method is an object of the Tkt class. This means it is Loaded by DBIx::SearchBuilder and has all the power of a Tkt object available in the automatically created accessor and mutator methods described above.
9 while (my $rec = $recs->Next) {
10 printf("%s: %s (%s)\n", $rec->id, $rec->Subject, $rec->Status);
11 }
12The output would look something like this:
81: an urgent contact request (new)
44: another very urgent issue ( open )
45: urgent contact request ( open )
22: an urgent problem (rejected )
61: extremely urgent disaster (new)This is just skimming the surface of the DBIx::SearchBuilder's functionality. It has many other useful methods for working with record collections. Let’s have a look at a couple that help to control the while loop shown above.
As you’d expect, calling the Next() method on our $recs collection object iterates through the list of objects returned from the database. What you may not expect is the handy feature that if you come to the end of the list—indicated by Next() returning undef which signals the end of the while loop—the following next Next() call starts at the first object again.
The next several lines of code use the object indirectly on the end of the print statement, rather than explicitly retrieving each object and then using it to print out one of its attributes. This saves paper, contributes to world peace, and makes a logical line of code all in one shot.
From the end of the while loop above, we simply print the Next() ID. Because we’re starting at the beginning of the loop again, the ID should be 81.
13 print "ReStart loop Id: ".$recs->Next->id."\n";
14
ReStart loop Id: 81Printing the next one produces the second object ID from our original list:
15 print "Next Id: ".$recs->Next->id."\n";
16
Next Id: 44Simply iterating through the list in a round-robin fashion is probably the most common use of this sort of construct, but there’s more. You also can go directly to any particular element of the current list using the GotoItem() method, passing it an integer to identify which item to go to.
Let’s go directly to item 4 and print out the Next() ID. Item 4 has an ID of 22, and item 5 has an ID of 61:
17 $recs->GotoItem(4);
18
19 print "Fifth Id: ".$recs->Next->id."\n";
20
Fifth Id: 61We also can go to the First() item in our list and print out its ID, which as we already know is 81:
21 print "First Id: ".$recs->First->id."\n";
22
First Id: 81From looking at the list returned earlier, we can see that the Next() ID should be 44, but we’re going to check this:
23 print "Next Id: ".$recs->Next->id."\n";
24
Next Id: 44Want to know what the Last() ID was? Use the Last() method to retrieve that object.
25 print "Last Id: ".$recs->Last->id."\n";
26
Last Id: 61Because DBIx::SearchBuilder uses data that it has stored internally, the working data may get out of sync with the actual data in the database while you iterate over various objects. As a demonstration, first instantiate a new Tkt object, then use the Create() method to store the record in the database:
27 my $newrec = new Tkt;
28
29 $newrec->Create(Id => 66, Subject => 'yet another urgent issue', queue => 1);
30After you create the new record in the database, it is important to Load() the record into the object, otherwise you will be working with a bare Tkt object with no data.
31 $newrec->Load(66);
32Now print out the familiar fields to check the results:
33 printf("%s: %s (%s)\n", $newrec->id, $newrec->Subject, $newrec->Status);
34
66: yet another urgent issue ()Although the database knows there is an extra record in the table, DBIx::SearchBuilder does not. Let’s use the Count() method to see how many objects we have currently.
35 print "Current Count: ".$recs->Count."\n";
36
Current Count: 5This count is correct for our current data but wrong from the perspective of the database. To get the true data collection, we need to use the CleanSlate() method and rerun our Limit() query.
37 $recs->CleanSlate;
38
39 $recs->Limit(FIELD => 'Subject', OPERATOR => 'LIKE', VALUE => '%urgent%');
40Now we can take another look at our current count:
41 print "Updated Count: ".$recs->Count."\n";
42
Updated Count: 6That looks much better. We also can use the handy ItemsArrayRef() method to return a reference to an array of all the items found by the search. Looping through this with a join() and a map(), we print out a list of all object IDs we have at our disposal to check that our 66 ID is actually in there, too.
43 print join(', ', map { $_->id } @{$recs->ItemsArrayRef})
44
81, 44, 66, 45, 22, 61This last command deletes the object we created in this small run of code, both to demonstrate that the Delete() method works here too, and so that you can use this code repeatedly without violating unique database constraints for our extra object while testing.
45 $newrec->Delete;
46
DBIx::SearchBuilder offers a number of other useful methods. To read more about the functionality the Tkts object inherits, read the documentation for DBIx::SearchBuilder.
RT’s object-oriented code is arranged in several distinct groups of modules. Each group generally depends on a specific core or essential module that provides methods other modules can inherit or overload as appropriate: RT::Base
, RT::Record
, and RT::SearchBuilder
.
RT::Base is a relatively minor but central utility module, which sets the current user for ACL decisions and the like. The following classes inherit this functionality:
RT::Date
RT::Record
RT::SearchBuilder
RT::System
RT::URI
RT::Record inherits from DBIx::SearchBuilder::Record and is responsible for handling a single object corresponding to a single record in the database. The following classes inherit from RT::Record:
RT::ACE
RT::Attachment
RT::Attribute
RT::CurrentUser
RT::CustomField
RT::Group
RT::GroupMember
RT::Link
RT::Principal
RT::Queue
RT::Scrip
RT::ScripAction
RT::ScripCondition
RT::Template
RT::Ticket
RT::Transaction
RT::User
RT::SearchBuilder also inherits from DBIx::SearchBuilder. It provides methods and functionality for collections of objects. The following collection objects inherit from RT::SearchBuilder:
RT::ACL
RT::Attachments
RT::Attributes
RT::CustomFields
RT::GroupMemebers
RT::Groups
RT::Links
RT::Principals
RT::Queues
RT::ScripActions
RT::ScripConditions
RT::Scrips
RT::Templates
RT::Tickets
RT::Transactions
RT::UsersYou saw earlier that DBIx::SearchBuilder records and collections always take a database handle as a parameter to their _Init methods. The RT::Record and RT::SearchBuilder objects take care of this for you. But there’s an added wrinkle. Within RT, every object needs to know which user is working with it so that it can make the right access control decisions and keep good records of who does what. With few exceptions, every RT object’s new method takes only a single paremeter, an RT::CurrentUser object. (For bootstrapping reasons RT::CurrentUser can take a username or id number instead of an RT::CurrentUser object.) RT doesn’t pass this information back to DBIx::SearchBuilder but uses it internally to make access control decisions.
RT implements ACLs (Access Control Lists) as the control mechanism for authorization for ticket and object creation. If you set the ACLs to permit the Everyone group to create tickets, RT automatically creates accounts for every user who ever submits a ticket. You can read more about ACLs and how RT uses them in Chapters 2 and 5.
Other modules are worth browsing to discover the functionality provided by the standard RT distribution. Action::Generic, Condition::Generic, Search::Generic, and URI::base are just a few ideas for further research and exploration. RT has a rich code base. Explore further to find your own ideas and solutions for your own particular application requirements.
Errors are handled by cancelling (or rolling back) the current transaction at the database level and sending the error to the current interface—whether that’s the command-line interface, email, or the web frontend—and to syslog. The /etc/syslog.conf configuration file can specify different responses to generate for the various RT errors and logging messages.
To get at the SQL, look at the relevant database logs. Alternatively you might try setting the DBI_TRACE variable to something suitably high to dump the SQL to STDERR.
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.
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.
Internally, RT generates its own SQL statements from the criteria specified in the various user interfaces and the currently excuting tasks.
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.
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.
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.