CHAPTER 12 ■ ENTERPRISE PATTERNS
values(? )";
static $add_space = "INSERT INTO space
( name, venue )
values( ?,? )";
static $check_slot = "SELECT id, name
FROM event
WHERE space =?
AND (start+duration) >?
AND start < ?";
static $add_event = "INSERT INTO event
( name, space, start, duration )
values( ?, ?, ?,? )";
//...
Not much new here. These are the SQL statements that the transaction scripts will use. They are
constructed in a format accepted by the PDO class’s prepare() method. The question marks are
placeholders for the values that will be passed to execute().
Now to define the first method designed to fulfill a specific business need:
function addVenue( $name, $space_array ) {
$ret = array();
$ret['venue'] = array( $name );
$this->doStatement( self::$add_venue, $ret['venue']);
$v_id = self::$DB->lastInsertId();
$ret['spaces'] = array();
foreach ( $space_array as $space_name ) {
$values = array( $space_name, $v_id );
$this->doStatement( self::$add_space, $values);
$s_id = self::$DB->lastInsertId();
array_unshift( $values, $s_id );
$ret['spaces'][] = $values;
}
return $ret;
}
As you can see, addVenue() requires a venue name and an array of space names. It uses these to
populate the venue and space tables. It also creates a data structure that contains this information, along
with the newly generated ID values for each row.
This method is spared lots of tedious database work by the superclass. I pass the venue name
provided by the caller to doStatement(). If there’s an error with this, remember, an exception is thrown. I
don’t catch any exceptions here, so anything thrown by doStatement() or (by extension)
prepareStatement() will also be thrown by this method. This is the result I want, although I should to
make it clear that this method throws exceptions in my documentation.
Having created the venue row, I loop through $space_array, adding a row in the space table for each
element. Notice that I include the venue ID as a foreign key in each of the space rows I create, associating
the row with the venue.
The second transaction script is similarly straightforward:
function bookEvent( $space_id, $name, $time, $duration ) {
$values = array( $space_id, $time, ($time+$duration) );
$stmt = $this->doStatement( self::$check_slot, $values, false ) ;