Raleigh.pm 2004-01-15

Class::DBI

# 1

    Brad Oaks

Introduction to Class::DBI

Class::DBI provides a convenient abstraction layer to a database.

    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.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 2

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/

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 3

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 4

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 5

Reasons NOT to use Class::DBI over regular DBI

  • you've decided not to use Objects

    • cdbi isn't much use without object inheritance

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 6

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 )

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 7

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;
    

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 8

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;

continued...

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 8

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 9

DonorMgmt/Donation.pm

    package DonorMgmt::Donation;
    use base 'DonorMgmt::DBI';
    use strict;
    __PACKAGE__->set_up_table( "donation" );
    __PACKAGE__->has_a( donor => "DonorMgmt::Donor" );
    1;

continued...

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 9

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 10

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;
    

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 11

DonorMgmt/t/main.t

    #!/usr/bin/perl -w
    use Test::More 'no_plan';
    use strict;
    use_ok( "DonorMgmt::Donor" );
    use_ok( "DonorMgmt::Donation" );

continued...

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 11

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

continued...

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 11

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

continued...

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 11

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;

continued...

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 11

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 12

Updating a Record

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 13

Deleting Records

individual records

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

continued...

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 13

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 14

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'} 
            );
    

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 15

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 16

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)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Raleigh.pm 2004-01-15

Class::DBI

# 17

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;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Table of Contents

1. Introduction to Class::DBI - Class::DBI provides a convenient abstraction layer to a database.
2. Disclaimer and Sources
3. Where to get more Info
4. Reasons to use Class::DBI over regular DBI
5. Reasons NOT to use Class::DBI over regular DBI
6. Installing Class::DBI and Class::DBI::mysql
7. DonorMgmt/DBI.pm
8. DonorMgmt/Donor.pm
9. DonorMgmt/Donation.pm
10. DonorMgmt/Setup.pm
11. DonorMgmt/t/main.t
12. Updating a Record
13. Deleting Records - individual records
14. Searching for Records - see also Class::DBI::AbstractSearch
15. Other Class Methods
16. Triggers
17. If we're feeling lucky (with enough time) - A Group Exercise