Implementing the Model

In the rest of this chapter, we’ll lay out the code that implements a robust Model for a class we’ll call Publisher . For the sake of simplicity, each publisher has just two attributes: an ID and a name. The id field is the primary key and uniquely identifies each row of the table. The Name field is the name of the publisher.

In this class, you will recognize all the methods we discussed in the previous section. Of the 13 methods, 4 correspond directly to SQL activities:

create( )

An instance method that inserts a new row into the table to hold the data from this object. The primary key for this table is a MySQL AUTO_INCREMENT field that automatically creates a new value for that field. Therefore, this method inserts only the value of the name field. The id field is passed in by this method as NULL, set in the database by MySQL, and then retrieved and assigned to the object’s id attribute by this method.

get( )

A static method that creates an SQL SELECT statement based on WHERE parameters passed to the function. For each row of the result set, a new object is created and an array of these objects is returned. This method represents the Generic Where method described in the previous section.

remove( )

An instance method that removes the row of data corresponding to this object in the database. It issues an SQL DELETE command to accomplish the removal. After this method is called, the program should destroy the object, because its underlying data is gone.

update( )

An instance method that updates the data in the database with the attribute data in the object. In effect, this method “saves” the current state of the object into the database. For this method to work, the object must already have a row in the database, because the method uses the SQL UPDATE statement.

Some of these calls invoke lower-level functions to handle SQL WHERE clauses. These WHERE clauses can be very complex, especially when multiple tables are involved. Clauses may even be nested. The information stored within an SQL WHERE clause can also be stored in a Perl multilevel hash without losing any information. We allow a WHERE clause of any complexity by encapsulating the processing of the clause in the following two functions:

make_where( )

A method that flattens the multilevel hash into a regular SQL WHERE clause that can be used in a SQL query.

bind_where( )

A method that inserts the WHERE clause into a statement handle. While the make_where( )method creates the actual SQL WHERE clause, the values of the parameters still need to be bound to the statement once the statement is prepared. This method calls the bind_param( ) method shown earlier in this chapter to insert each parameter value into the statement handle. After this method is called, the SELECT statement can be executed.

The following functions get and set the two attributes:

getId( )

An accessor method that retrieves the current value of the id attribute.

setId( )

An accessor method that sets the value of the id attribute. Because the id field of the table is the primary key, this method will rarely be called.

getName( )

An accessor method that retrieves the current value of the name attribute.

setName( )

An accessor method that sets the value of the name attribute.

The following is the Primary Key select function described in the previous section:

getByPrimaryKey( )

A static method that creates a single object based on a primary key. This method calls the generic get method to perform the actual query.

The following are constructors:

new( )

A generic constructor that simply creates an empty object.

populate_publisher( )

A static method that creates a new Publisher object based on data from a result set. This is a utility method used by get. The advantage of making it a separate method is that it can be used externally by other Model classes that need to create new Publisher objects.

This Publisher.pm module must be located in a directory we’ve called CBDB. In addition to this class, which you can generalize and apply to other applications, we use three helper classes. These are entirely generic and can be reused in any application empoying our Model.

DB

This class handles the creation of the connection and a couple of other low-level operations on the database that are required by some Publisher methods. The code must be placed in a module named DB.pm in the CBDB directory.

mysql

This class implements other low-level functions that must be implemented by functions specific to MySQL. These functions are isolated here so that you can easily port the Model code to another database and just replace this class. The mysql.pm module must be placed in a directory named BM that is a sibling of the CBDB directory.

Cache

This class provides a very simple mechanism for storing objects in memory and retrieving them later, to minimize the amount of traffic to and from the database. The Cache.pm module must be placed in the CBDB directory.

If you like to learn code from the bottom up, read the sections on the helper classes, then return to read the code for the Publisher class.

Finally, you may understand the wealth of functions better by seeing them organized into a hierarchy and seeing which functions are called by others. Figure 9-1 shows the main ways functions are called during major database operations.

Function hierarchy
Figure 9-1. Function hierarchy

The Publisher class

In the following subsections, we’ll introduce each method of this class in the order in which we’ve previously described it. The file begins with the following initializations. 

package CBDB::Publisher;
  
our $VERSION = '1.0';
  
use strict;
use DBI qw(:sql_types);
use CBDB::DB;
use CBDB::Cache;
  
our @ISA = qw( CBDB::DB );

Methods that build and execute SQL

The create( ), get( ), remove( ), and update( ) methods are fairly short and simple. They check the types of their input arguments, build SQL queries or updates in the ways shown earlier in this chapter, and execute the SQL. They also interact with the cache. Example 9-6 demonstrates this.

Example 9-6. Methods that build and execute SQL 
#################################################
# create() - Inserts the object into the database.
# Parameters: None.
# Returns: A Publisher object (redundantly, because
# this method is called on that same object).
sub create {
    my $self = shift;
    my $dbh = CBDB::DB::getDB(  );
    my $query = "INSERT INTO publisher ( name, id ) VALUES ( ?, ? )";
    my $sth = $dbh->prepare($query);

    my $pk_id = undef;
  
    $sth->bind_param(1, $self->getName(  ), {TYPE=>1});
    $sth->bind_param(2, undef, {TYPE=>4});
    $sth->execute;
    $sth->finish;
  
    $pk_id = CBDB::DB::get_pk_value($dbh, 'publisher_id'),
    $self->setId( $pk_id);
  
    $dbh->disconnect;
    CBDB::Cache::set('publisher', $self->getId(  ), $self);
    return $self;
}
  
#################################################
# get() - Retrieves objects from the database.
# Parameters: Optional WHERE clause.
# Returns: Array of Publisher objects.
sub get {
    my $wheres = undef;
    my $do_all = 1;
    if (ref($_[0]) eq 'ARRAY') { $wheres = shift; $do_all = shift if @_; }
    else { $do_all = shift; }
  
    my $dbh = CBDB::DB::getDB(  );
    my $where .= ' WHERE  ' . make_where( $wheres );
    my $query = qq{
    SELECT publisher.name as publisher_name, 
    publisher.id as publisher_id 
        FROM publisher 
    $where
    };
    my $sth = $dbh->prepare($query);
    bind_where( $sth, $wheres );
    $sth->execute;
    my @publishers;
    while (my $Ref = $sth->fetchrow_hashref) {
    my $publisher = undef;
    if (CBDB::Cache::has('publisher', $Ref->{publisher_id})) {
        $publisher = CBDB::Cache::get('publisher', $Ref->{publisher_id});
    } else { 
        $publisher = CBDB::Publisher::populate_publisher( $Ref );
  
    CBDB::Cache::set('publisher', 
        $Ref->{publisher_id}, $publisher);
    }
    push(@publishers, $publisher);
    }
    $sth->finish;
    $dbh->disconnect;
    return @publishers;
}
  
#################################################
# remove(  ) - Removes an object from the database.
# This method can be called on an object to delete
# that object, or statically, with a WHERE clause,
# to delete multiple objects.  
# Parameters: An optional where clause.
# Returns: Nothing.
sub remove {
    my $self = undef;
    my $where = undef;
    my $is_static = undef;
    if ( ref($_[0]) and $_[0]->isa("CBDB::Publisher") ) {
    $self = shift;
    $where = "WHERE id = ?";
    } elsif (ref($_[0]) eq 'HASH') {
    $is_static = 1;
    $where = 'WHERE ' . make_where($_[0]);
    } else {
    die "CBDB::Publisher::remove: Unknown parameters: " . join(' ', @_);
    }
  
    my $dbh = CBDB::DB::getDB(  );
    my $query = "DELETE FROM publisher $where";
  
    my $sth = $dbh->prepare($query);
  
    if ($is_static) {
    bind_where($sth, $_[0]);
    } else {
    $sth->bind_param(1, $self->getId(  ), {TYPE=>4});
    }
    $sth->execute;
    $sth->finish;
    $dbh->disconnect;
}
  
#################################################
# update(  ) - Updates this object in the database.
# Parameters: None.
# Returns: Nothing.
sub update {
    my $self = shift;
    my $dbh = CBDB::DB::getDB(  );
    my $query = "UPDATE publisher SET name = ?, id = ? WHERE id = ?";
    my $sth = $dbh->prepare($query);
  
    $sth->bind_param(1, $self->getName(  ), {TYPE=>1});
    $sth->bind_param(2, $self->getId(  ), {TYPE=>4});
    $sth->bind_param(3, $self->getId(  ), {TYPE=>4});
    $sth->execute;
    $sth->finish;
    $dbh->disconnect;
    CBDB::Cache::set('publisher', $self->getId(  ), $self);
  
}

Methods that handle WHERE clauses

The make_where() and bind_where( ) methods are the most complex in our Model, because they must unpack and process complex data structures: Perl hashes, sometimes containing nested hashes. The make_where( ) method takes the Perl hash as input and converts its contents to a string containing a valid WHERE clause. The bind_where( ) method is even more complicated. It takes a statement handle and an array (sometimes containing nested arrays) of bind variables. It issues bind_param( ) calls to bind values to the proper places in the statement handle.

The WHERE clause used by the get and remove methods is in the form of a array reference. Each element of the array is either a single WHERE element or a reference to another array. If it is a reference to another array, the elements in that array are recursively embedded into the WHERE clause to allow clauses such as: 

                     element AND (element OR (element AND element))

A single WHERE element is a hash reference that has at least the keys column and value. These contain the column name and value of the WHERE element. Other optional keys include type, which is the SQL operator used to join this element with the next element (it defaults to AND) and operator, which is the SQL operator used between the column name and the value (it defaults to an equals sign). Example 9-7 shows methods that handle WHERE clauses.

Example 9-7. Methods that handle WHERE clauses 
#################################################
# make_where(  ) - Construct a WHERE clause from a well-defined hash ref.
# Parameters: WHERE clause reference.
# Returns: WHERE clause string.
sub make_where {
    my $where_ref = shift;
    if ( ref($where_ref) ne 'ARRAY' ) { 
    die "CBDB::Publisher::make_where: Unknown parameters: " . 
    join(' ', @_);
    }
    my @wheres = @$where_ref;
    my $element_counter = 0;
    my $where = "";
    for my $element_ref (@wheres) {
    if (ref($element_ref) eq 'ARRAY') { 
        $where .= make_where($element_ref);
    } elsif (ref($element_ref) ne 'HASH') { 
        die "CBDB::Publisher::make_where: malformed WHERE parameter: " 
        . $element_ref; 
    }
    my %element = %$element_ref;
    my $type = 'AND';
    if (not $element_counter and scalar keys %element == 1 and 
        exists($element{'TYPE'})) {
        $type = $element{'TYPE'};
    } else {
        my $table = "publisher";
        my $operator = "=";
        if (exists($element{'table'})) { $table = $element{'table'}; }
        if (exists($element{'operator'})) 
        { $operator = $element{'operator'}; }
        if ($element_counter) { $where .= " $type "; } else 
        { $element_counter = 1; }
        for my $term ( grep !/^(table|operator)$/, keys %element ) {
            $where .= "$table.$term $operator ?";
        }
    }
  }
  return $where;
}
  
#################################################
# bind_where(  ) - Executes the handle->bind method that binds the
# where element.
# Parameters: WHERE clause array ref and a scalar
# ref to a counter number that tells the method
# which parameter to bind to.
# Returns: Nothing.
sub bind_where {
    my $sth = shift;
    my $where_ref = shift;
    my $counter_ref = shift || undef;
    my $counter = (ref($counter_ref) eq 'Scalar')?  $$counter_ref : 1;
    if ( not $sth->isa('DBI::st') or ref($where_ref) ne 'ARRAY' ) { 
    die "CBDB::Publisher::make_where: Unknown parameters: " 
        . join(' ', @_);
    }
    my @wheres = @$where_ref;
    for my $element_ref (@wheres) {
    if (ref($element_ref) eq 'ARRAY') { 
        bind_where($sth, $element_ref, $counter);
    } elsif (ref($element_ref) ne 'HASH') {
        die "CBDB::Publisher::make_where: malformed WHERE parameter: " 
            . $_;
    }
    my %element = %$element_ref;
    unless (not $counter and scalar keys %element == 1 and
            exists($element{'TYPE'})) {
        my $table = "publisher";
        if (exists($element{'table'})) {
            $table = $element{'table'};
        }
        for my $term ( grep !/^(table|operator)$/, keys %element ) {
            $sth->bind_param($counter, $element{$term}, 
                {TYPE=>CBDB::DB::getType($table,$term)});
            $counter++;
        }
    }
  }

}

Getter/setter methods

The getId( ), setId( ), getName( ), and setName( ) methods are typical object-oriented methods for accessing properties of the object. Example 9-8 demonstrates this.

Example 9-8. Getter/setter methods 
#################################################
# getId(  ) - Return Id for this publisher.
# Parameters: None.
# Returns: ID.
sub getId {
    my $self = shift;
    return $self->{Id};
}
  
#################################################
# setId(  ) - Set Id for this publisher.
# Parameters: An Id number.
# Returns: Nothing.
sub setId {
    my $self = shift;
    my $pId = shift or die "publisher.setId( Id ) requires a value.";
    $self->{Id} = $pId;
}
  
#################################################
# getName(  ) - Return Name for this publisher.
# Parameters: None.
# Returns: Name.
sub getName {
    my $self = shift;
    return $self->{Name};
}
  
#################################################
# setName(  ) - Set Name for this publisher.
# Parameters: A name.
# Returns: Nothing.
sub setName {
    my $self = shift;
    my $pName = shift || undef;
    $self->{Name} = $pName;
}

Primary key select method

The getByPrimaryKey( ) method retrieves data by its primary key, invoking the get( ) method previously shown. This is shown in Example 9-9.

Example 9-9. Primary Key Select Method 
#################################################
# getByPrimaryKey() - Retrieves a single object from
# the database based on a primary key.
# Parameters: An Id.
# Returns: A Publisher object.
sub getByPrimaryKey {
    my $pId = shift or die "publisher.get(  )";
    my $where = [ {'id' => $pId } ];
    return ( get( $where, 1 ) )[0];
}

Constructors

The new( ) method is a typical, generic constructor, while the populate_publisher( ) method is used by the get( ) method to create a Publisher object. Example 9-10 shows constructors.

Example 9-10. Constructors 
#################################################
# new(  ) - Constructor.
# Example: CBDB::Publisher->new(  );
# Returns: blessed hash.
sub new {
    my $proto = shift;
  
    my $class = ref($proto) || $proto;
    my $self = {};
    bless($self, $class);
  
    return $self;
}
  
#################################################
# populate_publisher() - Return a publisher object
# populated from a result set.
# Parameters: Data from a DBI fetch.
# Returns: A Publisher object.
sub populate_publisher {
    my $Ref = shift;
    my $publisher = CBDB::Publisher->new(  );
    $publisher->setName($Ref->{publisher_name});
    $publisher->setId($Ref->{publisher_id});
  
    return $publisher;
}
  
1; # This always terminates a class definition.

The DB class

This class provides utility functions related to databases. It also invokes a database-specific class, mysql. It contains, as a static variable, a hash that lists all the columns in all the application’s tables along with their data types.

getDB( )

This method creates a connection to the database and returns the database handle. This method is used by the Publisher methods that build and execute SQL.

get_pk_value( )

This method returns the most recent value assigned to a primary key through the auto-increment feature of the database. It invokes a method by the same name from mysql.

get_type( )

This method returns the SQL type of a column within a table, using the class’s hash of columns.

Example 9-11 demonstrates the DB class.

Example 9-11. The DB class 
package CBDB::DB;
  
use strict;
use BM::mysql;
  
my $VERSION = '0.1';
use constant DSN => "dbi:mysql:database=Books;host=localhost";
use constant USER => "andy";
use constant PASSWORD => "ALpswd";
  
my $types = {
    'creator' => { 'name' => 1, 'id' => 4 },
    'book' => { 'title' => 1, 'publisher_id' => 4, 'date' => 11, 'id' => 4 },
    'book_creator' => { 'book_id' => 4, 'creator_id' => 4, 'role_id' => 4 },
    'publisher' => { 'name' => 1, 'id' => 4 },
    'role' => { 'name' => 1, 'id' => 4 },
};
  
#####################################################################
# getDB() - Returns a database handle connection for the database.
# Parameters: None.
# Returns: DBH Connection Handle.
sub getDB {
    my $dbh = DBI->connect(DSN,USER,PASSWORD,{PrintError => 1,RaiseError => 1});
    return $dbh;
}
  
#####################################################################
# get_pk_value() - Returns the most recent auto_increment value for a PK.
# Parameters: Database Handle.
# Returns: Primary key value.
sub get_pk_value {
    my $dbh = shift or die "DB::get_pk_value needs a Database Handle...";
  
    my $dbd = BM::mysql->new(  );
    return $dbd->get_pk_value( $dbh );
}
  
#####################################################################
# getType() - Returns the type of a column within a table.
# Parameters: Table name and column name.
# Returns: DBI Type code.
sub getType {
    my $table = shift;
    my $col = shift;
    return $types->{$table}{$col};
}
  
1;

The mysql class

In this class, we have tried to extract and isolate calls to methods that are specific to a particular database server.

new( )

This is the generic object constructor.

is_pk( )

This method determines whether a field is part of the primary key of the table.

is_auto_increment( )

This method determines whether the primary key of the table is an AUTO_INCREMENT field.

get_pk_value( )

This method returns the value of the most recently inserted AUTO_INCREMENT field.

Example 9-12 demonstrates the mysql class.

Example 9-12. The mysql class 
package BM::mysql;
  
use strict;
  
###############
# CONSTRUCTOR #
###############
sub new {
    my $proto = shift;
    my $class = ref($proto) || $proto;
    my $self = { };
    bless($self, $class);
    return $self;
}
  
##################################################
# is_pk() - Determines if a column is a primary key.
# Parameters: DBI statement handle and a column
# number from that handle.
# Returns: true or false.
  
sub is_pk ($$$) {
    my $self = shift;
    my $sth = shift;
    my $i = shift;
  
    return 1 if $$sth->{mysql_is_pri_key}->[$i];
    return 0;
}
  
###################################################
# is_auto_increment() - Determines if a column is an
# AUTO_INCREMENT column.
# Parameters: DBI statement.
# handle and a column number from that handle.
# Returns: true or false.
sub is_auto_increment($$$) {
    my $self = shift;
    my $sth = shift;
    my $i = shift;
  
    return 1 if $$sth->{mysql_is_auto_increment}->[$i];
    return 0;
}
  
################################################
# get_pk_value() - Returns the last AUTO_INCREMENT
# value for this connection.
# Paramaters: DBI database handle.
# Returns: PK value.
sub get_pk_value {
    my $self = shift;
    my $dbh = shift or die "mysql::get_pk needs a Database Handle...";
    my $mysqlPk = "Select last_insert_id(  ) as pk";
    my $mysqlSth = $dbh->prepare($mysqlPk);
    $mysqlSth->execute(  );
    my $mysqlHR = $mysqlSth->fetchrow_hashref;
    my $pk = $mysqlHR->{"pk"};
    $mysqlSth->finish;
    return $pk;
}
  
1;

The Cache class

This class contains a static hash of all the Model objects used by the application. The cache is organized by class name (the first-order hash) and primary key (the second-order, or nested, hash).

Besides increasing performance, the cache also allows multiple objects to exist that represent the same row of data in the underlying table. Because each object’s code uses references to the cached objects, the object automatically reflects changes made to other objects.

set( )

Adds an object to the cache.

get( )

Retrieves an object from the cache.

has( )

Checks to see whether the object already exists in the cache.

Example 9-13 demonstrates the Cache class.

Example 9-13. The Cache class 
package CBDB::Cache;
  
# This file keeps a copy of all active objects,
#s with records of their primary keys.
use strict;
  
my %cache = (  );
  
sub set {
    $cache{$_[0]}{$_[1]} = $_[2];
}
sub get {
    return $cache{$_[0]}{$_[1]};
}
sub has {
    return exists $cache{$_[0]}{$_[1]};
}
  
1;
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset