Brad Oaks

    from the module's documentation:

    It not only provides a simple database to object mapping layer,
    but can be used to implement several higher order database functions
    (triggers, referential integrity, cascading delete etc.), at the 
    application level, rather than at the database.  

• Class::DBI and Class::DBI::mysql are currently maintained by Tony Bowden

• Class::DBI is built on top of Ima::DBI, Class::Accessor and Class::Data::Inheritable.

Disclaimer and Sources

• I am an OO Perl beginner

• My playing started with Tony Bowden's article titled Class::DBI

  http://www.perl.com/pub/a/2002/11/27/nocode.html

• My playing continued with Kake Pugh's article titled How to Avoid Writing Code

  http://www.perl.com/pub/a/2003/07/15/nocode.html

• Many thanks go to Michael Graham for creating a comprehensive set of slides on Class::DBI

  http://www.occamstoothbrush.com/perl/talks/

Where to get more Info

• From CPAN directly (good POD)

  http://search.cpan.org/~tmtm/Class-DBI-0.95/lib/Class/DBI.pm

• if XML is useful to you, try Class::DBI::ToSax

  http://search.cpan.org/~semantico/Class-DBI-ToSax-0.10/lib/Class/DBI/ToSax.pm

Reasons to use Class::DBI over regular DBI

• provides more clean isolation of SQL code into a few files

• don't have to pass around database handles within your object instances

Reasons NOT to use Class::DBI over regular DBI

• you've decided not to use Objects

  • cdbi isn't much use without object inheritance

Installing Class::DBI and Class::DBI::mysql

• use CPAN and follow prerequisite modules

• Class::DBI::mysql gave me a problem during make test

  • The table create in the test has a default value defined for a TIMESTAMP field; this fails to create. Removing that default designation allowed the tests to pass. (file: /.cpan/build/Class-DBI-mysql-0.22/t/mysql.t )

DonorMgmt/DBI.pm

• Our base class inherits from Class::DBI::mysql instead of plain Class::DBI, so we can save ourselves the trouble of directly specifying the table columns for each of our database tables--the database-specific base classes will auto-create a set_up_table method to handle all this for you.

      package DonorMgmt::DBI;
      use base "Class::DBI::mysql";
  

      __PACKAGE__->set_db( "Main", "dbi:mysql:donormgmt", "donormgmtuser", "donormgmtsekret" );
  

      1;
  

DonorMgmt/Donor.pm

    package DonorMgmt::Donor;
    use base 'DonorMgmt::DBI';
    use strict;
    use Carp;$
    $
    sub name { &read_only; $_[0]->firstname . " " . $_[0]->lastname; }$
    $
    sub read_only {$
        croak "Can't change the value of read-only attribute " . (caller 1)[3]$
            if @_ > 1;$
        }$

    __PACKAGE__->set_up_table( "donor" ); 
    __PACKAGE__->has_many( "donations", "DonorMgmt::Donation" => "donor" );

    1;

DonorMgmt/Donor.pm

    package DonorMgmt::Donor;
    use base 'DonorMgmt::DBI';
    use strict;
    use Carp;$
    $
    sub name { &read_only; $_[0]->firstname . " " . $_[0]->lastname; }$
    $
    sub read_only {$
        croak "Can't change the value of read-only attribute " . (caller 1)[3]$
            if @_ > 1;$
        }$

    __PACKAGE__->set_up_table( "donor" ); 
    __PACKAGE__->has_many( "donations", "DonorMgmt::Donation" => "donor" );

    1;

. . .

    CREATE TABLE IF NOT EXISTS donor (
        uid           MEDIUMINT unsigned NOT NULL auto_increment,
        firstname     varchar(50),
        lastname      varchar(50),
        ts_update     timestamp NOT NULL,
        PRIMARY KEY (uid)
    )

DonorMgmt/Donation.pm

    package DonorMgmt::Donation;
    use base 'DonorMgmt::DBI';
    use strict;

    __PACKAGE__->set_up_table( "donation" );
    __PACKAGE__->has_a( donor => "DonorMgmt::Donor" );

    1;

DonorMgmt/Donation.pm

    package DonorMgmt::Donation;
    use base 'DonorMgmt::DBI';
    use strict;

    __PACKAGE__->set_up_table( "donation" );
    __PACKAGE__->has_a( donor => "DonorMgmt::Donor" );

    1;

. . .

    CREATE TABLE IF NOT EXISTS donation (
        uid           MEDIUMINT unsigned NOT NULL auto_increment,
        donor         MEDIUMINT unsigned, # references donor.uid
        amount        integer,
        type          ENUM('check','cash','credit','money order'),
        date          DATE,
        notes         MEDIUMTEXT,
        PRIMARY KEY (uid)
    )

DonorMgmt/Setup.pm

• now that DonorMgmt::DBI is accessible via DonorMgmt::Donor and DonorMgmt::Donation we can create remake the tables using the two classes.

• Class::DBI cannot create the SQL tables alone; however it can modify them once they exist.

• The tables have to exist with a primary key, and any foreign keys.

• Without this our class modules will fail to load during the execution of DonorMgmt::Setup.

• This Setup.pm module was useful for me in tweaking my schema during development

      package DonorMgmt::Setup;
      use strict;
      use DonorMgmt::Donor;
      use DonorMgmt::Donation;
      my $force_clear = 1;
  

      if ($force_clear == 1) {
          DonorMgmt::Donor->drop_table;
          DonorMgmt::Donation->drop_table;
          }
  

      DonorMgmt::Donor->table('donor');
      DonorMgmt::Donor->create_table(q{
        uid           MEDIUMINT unsigned NOT NULL auto_increment,
        firstname     varchar(50),
        lastname      varchar(50),
        ts_update     timestamp NOT NULL,
        PRIMARY KEY (uid)
      });
  

      DonorMgmt::Donation->table('donation');
      DonorMgmt::Donation->create_table(q{
        uid           MEDIUMINT unsigned NOT NULL auto_increment,
        donor         MEDIUMINT unsigned, # references donor.uid
        amount        integer,
        type          ENUM('check','cash','credit','money order'),
        date          DATE,
        notes         MEDIUMTEXT,
        PRIMARY KEY (uid)
      });
  

      1;
  

DonorMgmt/t/main.t

    #!/usr/bin/perl -w
    use Test::More 'no_plan';
    use strict;

    use_ok( "DonorMgmt::Donor" );
    use_ok( "DonorMgmt::Donation" );

DonorMgmt/t/main.t

    #!/usr/bin/perl -w
    use Test::More 'no_plan';
    use strict;

    use_ok( "DonorMgmt::Donor" );
    use_ok( "DonorMgmt::Donation" );

    my $donor = DonorMgmt::Donor->create({ firstname => "Brad", lastname => "Oaks" });
    isa_ok( $donor, "DonorMgmt::Donor" );

DonorMgmt/t/main.t

    #!/usr/bin/perl -w
    use Test::More 'no_plan';
    use strict;

    use_ok( "DonorMgmt::Donor" );
    use_ok( "DonorMgmt::Donation" );

    my $donor = DonorMgmt::Donor->create({ firstname => "Brad", lastname => "Oaks" });
    isa_ok( $donor, "DonorMgmt::Donor" );

    my $donation = DonorMgmt::Donation->create({ amount  => 25.00 , donor => $donor });
    isa_ok( $donation, "DonorMgmt::Donation" );

DonorMgmt/t/main.t

    #!/usr/bin/perl -w
    use Test::More 'no_plan';
    use strict;

    use_ok( "DonorMgmt::Donor" );
    use_ok( "DonorMgmt::Donation" );

    my $donor = DonorMgmt::Donor->create({ firstname => "Brad", lastname => "Oaks" });
    isa_ok( $donor, "DonorMgmt::Donor" );

    my $donation = DonorMgmt::Donation->create({ amount  => 25.00 , donor => $donor });
    isa_ok( $donation, "DonorMgmt::Donation" );

    is( $donation->donor->firstname, "Brad", "correct donor" );
    is( $donation->donor->name, "Brad Oaks", "'name' method works" );

    $donation->delete;
    $donor->delete;

DonorMgmt/t/main.t

    #!/usr/bin/perl -w
    use Test::More 'no_plan';
    use strict;

    use_ok( "DonorMgmt::Donor" );
    use_ok( "DonorMgmt::Donation" );

    my $donor = DonorMgmt::Donor->create({ firstname => "Brad", lastname => "Oaks" });
    isa_ok( $donor, "DonorMgmt::Donor" );

    my $donation = DonorMgmt::Donation->create({ amount  => 25.00 , donor => $donor });
    isa_ok( $donation, "DonorMgmt::Donation" );

    is( $donation->donor->firstname, "Brad", "correct donor" );
    is( $donation->donor->name, "Brad Oaks", "'name' method works" );

    $donation->delete;
    $donor->delete;

. . .

    perl DonorMgmt/t/main.t
    ok 1 - use DonorMgmt::Donor;
    ok 2 - use DonorMgmt::Donation;
    ok 3 - The object isa DonorMgmt::Donor
    ok 4 - The object isa DonorMgmt::Donation
    ok 5 - right donor
    ok 6 - name method works 
    1..6

Updating a Record

    my $donor = DonorMgmt::Donor->retreive(4);
    $donor->lastname('Oaks, Sr.'); 
    $donor->update;

Deleting Records

individual records

    my $donor = DonorMgmt::Donor->retreive(8);
    $donor->delete;

Deleting Records

individual records

    my $donor = DonorMgmt::Donor->retreive(8);
    $donor->delete;

a range of records

    DonorMgmt::Donation->delete( date => '2004-01-15', type => 'credit' );

Searching for Records

    my @smiths = DonorMgmt::Donor->search(lastname => 'Smith');

see also Class::DBI::AbstractSearch

    Class::DBI::AbstractSearch allows "arbitrarily complex searches using perl data structures, rather than SQL."

• add 'use Class::DBI::AbstractSearch;' to class module.

      my @donors3 = DonorMgmt::Donor->search_where(
          {
              lastname => ['Oaks', 'Oaks, Sr.'], 
              uid => {'<','100'} 
          }, 
          { order => 'firstname DESC'}
          );
  

      my @brads = DonorMgmt::Donor->search_where( 
          firstname => ['Brad','Bradley','Bradford'],
          active => {'!=','No'} 
          );
  

Other Class Methods

    #!/usr/bin/perl -w
    use strict; 
    use DonorMgmt::Donor;
    use DonorMgmt::Donation;

    my $type = DonorMgmt::Donor->column_type('uid');
    my @allowed = DonorMgmt::Donation->enum_vals('type');

    print "uid type: $type\n";
    print "allowed: \n\t", join("\n\t",@allowed), "\n";
    __END__

    uidtype: mediumint(8) unsigned
    allowed:
        check
        cash
        credit
        money order

Triggers

    __PACKAGE__->add_trigger(after_create  => \&call_after_create);

    before_create       (also used for deflation)
    after_create
    before_set_$column  (also used by add_constraint)
    after_set_$column   (also used for inflation and by has_a)
    before_update       (also used for deflation and by might_have)
    after_update
    before_delete
    after_delete
    select              (also used for inflation and by construct and _flesh)

If we're feeling lucky (with enough time)

A Group Exercise

Multi Table Relationships

• create a table 'events' which will refer to many donations.

• modify the schema in DonorMgmt::Setup before adding the 'has_many' and 'has_a' statements;