Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

Object Oriented Pattern help

by thefid (Friar)
on May 12, 2001 at 03:14 UTC ( #79866=perlquestion: print w/replies, xml ) Need Help??

thefid has asked for the wisdom of the Perl Monks concerning the following question:

I wanted to tackle my next project using Objects since I'm fairly inexperienced in OO, it seems like a good chance to learn.

I have created a guest object where the methods are simple add/delete/update. Using DBI, the program posts the information to a MySQL database. A simple application where the users can record information pertaining to their guests on-line.

Question 1-> There has to be a more efficient way to store the queries because I am defining the queries each time the method is called... but where should they be stored?

Question 2-> As you can see I am opening the database handle in the controller program and then passing the dbh to the method. I really don't want to open the connection to the database is there a better way to do this? when should open the connection to the database?

Here's the

package Guest; sub new { my ($pkg, $last_name, $first_names, $address, $city, $state, $zip, $est_count) = @_; bless { _last_name => $last_name, _first_names => $first_names, _address => $address, _city => $city, _state => $state, _zip => $zip, _est_count => $est_count }, $pkg; } # Database methods: add/update/delete # - All database objects requre a database handle parameter # and will use DBI to talk to database. sub add { my ($obj,$dbh) = @_; # Insert row into database my $sth = $dbh->prepare( "INSERT INTO guest_list (last_name, first_names, address, city, state, zip, est_count) VALUES (?,?,?,?,?,?,?)" ); $sth->execute($obj->{_last_name}, $obj->{_first_names}, $obj->{_address}, $obj->{_city}, $obj->{_state}, $obj->{_zip}, $obj->{_est_count}); return 1; } # Delete sub delete { my ($obj,$dbh) = @_; # Prep delete statement my $sth = $dbh->prepare( "DELETE FROM guest_list WHERE last_name = ? and first_names = ?" ); $sth->execute($obj->{_last_name}, $obj->{_first_names}); # Don't forget to delete the object return 1; } # Update sub update { my ($obj,$dbh) = @_; # Insert row into database my $sth = $dbh->prepare( "UPDATE guest_list SET address = ?, city = ?, state = ?, zip = ?, est_count = ? WHERE last_name = ? and first_names = ?" ); $sth->execute($obj->{_address}, $obj->{_city}, $obj->{_state}, $obj->{_zip}, $obj->{_est_count}, $obj->{_last_name}, $obj->{_first_names}); return 1; } #Accessor methods sub last_name {my $obj = shift; @_ ? $obj->{_last_name} = shift : $obj->{_last_name}} sub first_names {my $obj = shift; @_ ? $obj->{_first_names} = shift : $obj->{_first_names}} sub address {my $obj = shift; @_ ? $obj->{_address} = shift : $obj->{_address}} sub city {my $obj = shift; @_ ? $obj->{_city} = shift : $obj->{_city}} sub state {my $obj = shift; @_ ? $obj->{_state} = shift : $obj->{_state}} sub zip {my $obj = shift; @_ ? $obj->{_zip} = shift : $obj->{_zip}} sub est_count {my $obj = shift; @_ ? $obj->{_est_count} = shift : $obj->{_est_count}} 1;
#!/usr/bin/perl -w use DBI; use Guest; # Connect to database my $dbh = DBI->connect( "dbi:mysql:xxx", 'xxx', 'xxx' ) or die "Can't connect to MySQL database: $DBI::errstr\n"; my $last_name = "Rodriguez"; my $q_last_name = $dbh->quote($last_name); my $first_names = "Alex"; my $q_first_names = $dbh->quote($first_names); # Fetch One my ($add,$city,$state,$zip,$cnt) = $dbh->selectrow_array( "SELECT address, city, state, zip, est_count FROM guest WHERE last_name = $q_last_name AND first_names = $q_first_names" ); my $guest = new Guest ($last_name,$first_names,$add, $city,$state,$zip,$cnt); #### Test Update print "Testing Update...\n"; $guest->state("TX"); $guest->update($dbh); $dbh->disconnect(); exit;
Any suggestions and/or comment would be much appreciated. thanks, mike

Replies are listed 'Best First'.
Re: Object Oriented Pattern help
by Masem (Monsignor) on May 12, 2001 at 03:25 UTC
    For 1), you can store the $sth after you create it once; when you next use that to execute() a SQL statement, the query still remains, though the placeholder values will be replaced with the appropriate values for the new statement. So simply create these as private variables in your Guest pacakge.

    For 2), you can create a function "dbi()", which when called, either creates the $dbi connection if none exists and returns it, or returns the existing dbi connection. Then in your various classes in Guest, call something like:

    my $sth = Controller->dbi()->prepare( "SQL STATEMENT" ) or die DBI->errstr;
    You no longer need to pass the $dbi to each method.

    Dr. Michael K. Neylon - || "You've left the lens cap of your mind on again, Pinky" - The Brain
      For 2), you can create a function "dbi()", which when called, either creates the $dbi connection if none exists and returns it, or returns the existing dbi connection. Then in your various classes in Guest, call something like:

      This is a nice pattern to use, I am sure you realise that it is a variant of the singleton object. *REACHES for DP*

      Singleton Intent Ensure a class only has one instance and provide a global point of access to it.

      A singleton is a powerful tool BUT I would not use it in this case. The key issue here is why do you need a singleton? Is it to preserve some design decision to force a single instance of an object? .. no there will be many database connections .. is it to create a global variable by stealth, and so to extend the scope of an object? .. in this case I would say yes it is. Wilst a singleton offers many advantages over global variables and the associated namespace cluttering, giving a variable global access is indicitive of an incorrect design decision. An object VERY rarely needs global scope, you need to define clearly the scope and life of an object then enforce those rules.



        I believe this is bad advice.

        Having a variable available from anywhere through a function has two advantages over a global. First it gains some protection from being modified anywhere you want (though not enough in this case because anyone can close the connection). Secondly it allows you to put off the decision over whether to initialize the variable as long as possible. If you need it, it will be there, but if you never needed it you didn't want to do the work. The third win is that if the variable has a complex initialization then every place it is used you document where to look for its initialization. That can be nice in a complex system.

        The second is the real win here.

        Connecting to a database takes a lot of work. For many useful programs the database connection takes more time than the entire program does! This is not the kind of operation that you want to possibly wind up doing repeatedly in a loop.

        Furthermore this is an operation that is common to want to do from many places for many reasons. Which is why the appropriate life of a database connection is often longer than the life of your script! In fact that is one of the main wins that make people go to mod_perl, perlex, and friends. That you can have database connections that be reused across multiple web pages.

        Incidentally the above point is why I think it is important to know why things are good design decisions. Zigster's advice as abstract advice is along a line that is generally recognized as the right thing to do. Were it practically any other example I would (probably silently) be in agreement with him.

        But the cost of this advice is that wind up creating and destroying things a lot. Usually the overhead of creation and destruction is justified from reduced conflicts and easier to follow code. But in this case the excess creations can quickly turn into an..interesting..stress test for your database...

Re: Object Oriented Pattern help
by repson (Chaplain) on May 12, 2001 at 05:27 UTC
    I'd say that the $dbh connection should remain open for the lifetime of the object, which means it should be created during the constructor. You will also want to add the DBI->connect string to the items passed to the constructor in that case, and maybe give the option for an already created dbi session to be passed in instead. When you have the session, you can store it in $self->{dbh}.

    As for the individual queries they should be either created with $dbh->prepare_cached('select...');, or stored in the object. This code may give an idea of what I mean.

    sub delete { my $obj = shift; my $sth = $obj->{queries}{delete} || $obj->{dbh}->prepare( "DELETE FROM guest_list WHERE last_name = ? and first_names = ?" ); $sth->execute($obj->{_last_name}, $obj->{_first_names}); $obj->{queries}{delete} = $sth; return 1; }
      I agree here, I open a connection to the database in the constructor and disconnect in the destructor. If your running mod_perl you'll most likely have cached open handles which can be used and you might not need the disconnect, but I do it out of habit. I use code something like this:
      use vars qw( %conn ); sub new { my $class = shift; $self = bless { _dbh => undef, }, $class; $conn{'dbname'} ||= DBI->connect( ... ); $self->dbh( $conn{'dbname'} ); return $self; } sub DESTROY { $_[0]->dbh->disconnect() if $_[0]->dbh; } sub dbh { @_ > 0 ? $_[0]->{_dbh} = $_[1] : $_[0]->{_dbh} }

        This may not be so good of an idea if using multiple instances of the class in a single program. Even with persistent connections, it seems too cumbersome to pass in database info to the constructor each time. If you're not using persistent connections and you create 5 objects in a script, each object will create a new connection to the database.

        This also doesn't scale very well if using another similar class in the program. Again, multiple handles to the same database for each guest object and each XYZ object and you have some pretty greedy perl code.

        If you're only using one class to access the database, then maybe use a class variable:

        package Guest; use vars qw($DBH); sub new { $DBH ||= DBI->connect(...); } END { $DBH->disconnect(); }

        The original code showed passing in a reference to the database handle to each method and that would work fine. A better way may be to pass that in to the constructor and have each object store that instead. But if you do that, make sure you don't have the destructor close the database handle either. Doing database opens/closes in a central location (in this case, the main program) seems like the cleanest way.

      You probably don't want your objects creating the dbh handles for you. This doesn't scale and is not portable; you have to edit your perl modules if you change your database or use on a different server.

      What if you already have the database connection open in your code? Do you want your objects to open a new connection per object?

      But creating a $self->{dbh} is probably a good idea. I would do something like:

      my $person = People->new(dbh => $dbh, %other_data);
Re: Object Oriented Pattern help
by bjelli (Pilgrim) on May 12, 2001 at 13:57 UTC

    I know you want to program the stuff for yourself, but you might get some ideas by reading code:

    There is a module Class::DBI that helps you create a Class that is closly linked to one Table in a Database.

    It reuses the DB connection, and there is a function set_sql to generate reusable sql-statements (and statement handles).

    Brigitte    'I never met a chocolate I didnt like'    Jellinek
      The module Class::DBI was exactly what I was looking for... thanks!

      Like koolade suggested, it may not be a good idea to open a database connection for every object, since I may have the case where a query for guests may return up to 100 objects. It looks like this module is efficient in that respects using the module Ima::DBI where it states:

      Holds off opening a database connection until necessary. While Ima::DBI is informed of all your database connections and SQL statements at compile-time, it will not connect to the database until you actually prepare a statement on that connection. This is obviously very good for programs that sometimes never touch the database. It's also good for code that has lots of possible connections and statements, but which typically only use a few. Kinda like an autoloader.

Re: Object Oriented Pattern help
by thefid (Friar) on May 14, 2001 at 05:45 UTC
    Just a little update on my progress in case anyone is still interested ;)

    I installed Class::DBI , and created an "Event" object to test with. The savings in the amount of code that I had to write was tremendous:

    package Event; use base qw(Class::DBI); # Tell Class::DBI a little about yourself Event->table('events'); Event->columns('All', qw( name given_by location date time ) ); Event->columns('Primary', 'name'); Event->set_db('Main', 'dbi:mysql:xxx', 'xxx', 'xxx' ); 1;
    But what I gain in simplicity, I lose a bit in performance:
    #!/usr/bin/perl -w use DBI; use Guest; use Event; use Benchmark; # Connect to database my $dbh = DBI->connect( "dbi:mysql:web_stuff", 'xxx', 'xxx' ) or die "Can't connect to MySQL database: $DBI::errstr\n"; # Need to benchmark these solutions sub modify_event { ### Test (query) the Event object my $this_event = Event->retrieve('Party'); ### Test (modify) the Event object $this_event->time('9:00 PM'); $this_event->commit; } sub modify_guest { # Fetch One my $last_name = "Rodriguez"; my $q_last_name = $dbh->quote($last_name); my $first_names = "Alex"; my $q_first_names = $dbh->quote($first_names); my ($add,$city,$state,$zip,$cnt) = $dbh->selectrow_array( "SELECT address, city, state, zip, est_count FROM guest_list WHERE last_name = $q_last_name AND first_names = $q_first_names" ); my $guest = new Guest ($last_name,$first_names, $add,$city,$state,$zip,$cnt); #### Test Update $guest->state("TX"); $guest->update($dbh); } timethese(1000, {modify_event => 'modify_event()', modify_guest => 'modify_guest()' }); $dbh->disconnect(); exit;
    Benchmark: timing 1000 iterations of modify_event, modify_guest... modify_event: 7 wallclock secs ( 5.25 usr + 0.26 sys = 5.51 CPU) modify_guest: 5 wallclock secs ( 2.89 usr + 0.15 sys = 3.04 CPU)
Re: Object Oriented Pattern help
by blue_cowdawg (Monsignor) on May 14, 2001 at 00:41 UTC
    *Question 1-> There has to be a more efficient way to 
    *store the queries because I am defining the queries 
    *each time the method is called... but where should 
    *they be stored? 

    Create an .PM file called (or some such name as that.) In that file you start off with:

    package Queries; use DBI; use strict; #we really don't care about this as we are not #going to instantiate a Queries object. Just its #children sub new { my $proto=shift; my $class=ref($proto) || $proto; my $self={}; bless $self,$class; # We'll just put this here for clarity $self -> init(); return $self; } sub init { my $self=shift; #modify this to suit your situation my $self->{dbh}=DBI->connect('DBI:mysql',"username" ,"password" ); } sub execute { my $self=shift; my @parms=@_; return unless $self->{dbh}; return unless $self->{sql}; $self->{sth}=$self->{dbh}->prepare($self->{sql}); } sub fetchrow_hashref { my $self=shift; return unless $self->{sth}; return $self->{sth}->fetchrow_hashref; }

    Now that you have the base class you can add more classes to the same .pm file as follows:

    package loadusertable; use Queries; use strict; use vars qw( @ISA ); @ISA=qw(Queries); sub new { my $proto=shift; my $class=ref($proto) || $proto; my $self={}; bless $self,$class; $self->init; # we get that from the base class $self->{sql}="select * from users where id=?"; return $self; }

    Now we have a class that when we instatiate it it connects to the database and can be used as follows:

    use Queries; use strict; my $q=new loadusertable; $q->execute(15); my $row=$q->fetchrow_hashref;

    There is more that can be done with this. This was just a quick "something" I threw together to answer your question.

    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Peter L. Berghold --- Peter@Berghold.Net "Those who fail to learn from history are condemned to repeat it."

      I have never, ever benchmarked this, but prepare_cached1 should get you increased performance, especially if you're using your queries a lot.

      In other words, what repson said =)

      1"the statement handle returned will be stored in a hash associated with the `$dbh'. If another call is made to `prepare_cached' with the same `$statement' and `%attr' values, then the corresponding cached `$sth' will be returned without contacting the database server" from the DBI documentation.

      perl -e 'print "How sweet does a rose smell? "; chomp $n = <STDIN>; $r +ose = "smells sweet to degree $n"; *other_name = *rose; print "$other +_name\n"'

        As I said in my write up there is a lot more that can be done with the code that I presented. For instance, how about having just one DBH for the module as a whole? This way the first instantiation of an object declared in the .PM would make a connection and that connection would be used until nobody cared any more.

        I am not always convinced that fetchrow_hashref is all that efficient. In fact in the DBI man page it says that it isn't.

        One cat. Many ways to skin it and only that cat objects to how it is skinned...

        ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Peter L. Berghold --- Peter@Berghold.Net "Those who fail to learn from history are condemned to repeat it."

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://79866]
Approved by root
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (4)
As of 2022-10-02 12:35 GMT
Find Nodes?
    Voting Booth?
    My preferred way to holiday/vacation is:

    Results (8 votes). Check out past polls.