RT Essentials

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.

9.1.1. DBIx::SearchBuilder::Handle

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-04

Although 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;

9.1.2. DBIx::SearchBuilder::Record

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); 12

The 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"; 16

The current subject line for reference follows the failed output message:

Update: Immutable field Subject: My frobnitz is broken

When 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.

9.1.3. DBIx::SearchBuilder

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"; 8

It 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 } 12

The 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: 81

Printing the next one produces the second object ID from our original list:

15 print "Next Id: ".$recs->Next->id."\n"; 16   Next Id: 44

Simply 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: 61

We 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: 81

From 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: 44

Want 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: 61

Because 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); 30

After 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); 32

Now 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: 5

This 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%'); 40

Now we can take another look at our current count:

41 print "Updated Count: ".$recs->Count."\n"; 42   Updated Count: 6

That 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, 61

This 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.