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

Dear Monks

I have been writing many objects for a while and most of them fall into the general pattern of simply wrapping a mySQL table. They load a record from the table put the variables into the object hash and then get and set functions manipulate the data and provide input correctness and output formatting.

I did this previously by every field having a get and set method which got the data from the database or wrote directly to the database. It worked but it is not very efficient.

Basically my code consisted of many many verions of this: 

sub get_user_name( $ )
{
   my $data;
   my $query;   

   my $self = shift;

   my $dbh  = $self->{'dbh'};   
    

   my $sqlQuery = "SELECT UserName 
                     FROM USERINFORMATION                     
                    WHERE UserID = \'$self->{user_id}\'";
                        
    $query = $dbh->prepare( $sqlQuery );
      
    $query-> execute() || die $dbh->errstr;

    if( $data = $query->fetchrow_array() )
    {       
        $self->{'error_type'}   = "";   
        $self->{'error_string'} = "";   
        return( $data );
                            
     }
     else
     {
         $self->{'error_type'}   = "variable returned no value";   
         $self->{'error_string'} = "variable returned no value";    
         return( undef ); 
     }  
   
}  





sub update_user_name( $ )
{
   my @data;
   my $query;   

   my $self = shift;

   my $dbh  = $self->{'dbh'};   
    
   # Test for zero length input and 
   if( $_[0] eq "" )
   {
      
      $self->{'error_type'}   = "blank";   
      $self->{'error_string'} = "This variable must be filled in."; 
      return( undef );  
   
   }
    
   # test for input string being too long
   elsif( length( $_[0] ) > 20 )
   {
      
    $self->{'error_type'}   = "overlength";   
      $self->{'error_string'} = "This variable may only be 20 characte
+rs."; 
      return( undef );  
   
   } 
    
   # test for chracter correctness
   elsif( $_[0] =~ /[^A-Za-z0-9_]/  )
   {
         
    $self->{'error_type'}   = "illegal characters";   
      $self->{'error_string'} = "Only the chracters A-Z a-z 0-9 _ are 
+allowed.";    
      return( undef );  
   
   }
   
   # test for variable specific cases here

   # username must be unique
   
   
   my $sqlQuery = "SELECT UserID, UserName
                     FROM USERINFORMATION
                    WHERE UserName = \'$_[0]\'";

   my $query = $dbh->prepare( $sqlQuery );

   $query->execute() || die $dbh->errstr;

   @data = $query->fetchrow_array();
   
   #if the userid of the duplicate groupname
   #matches the current userid of the person
   #registering the system then proceed 
   if( $data[0] != $self->{'user_id'} && $data[1] ne "" )  
   { 
   
        $self->{'error_type'}   = "not unique";   
        $self->{'error_string'} = "User Name already in use please cho
+ose another.";    
        return( undef );        
   }
   

   else
   {
        my $update = &format_for_mysql( $_[0] );
       
        my $sqlQuery = "UPDATE USERINFORMATION
                          SET UserName = \'$update\'
                          WHERE UserID = $self->{user_id}";
                        
        $query  = $dbh->do( $sqlQuery );
      
         return( 1 );
      
    
    }

}

[download]

Now I wonder since 90 percent of my objects simply wrap a table like the above code is there a better way?

Edit by tye, add READMORE tag

Replies are listed 'Best First'.
Re: Writing Better Objects
by particle (Vicar) on Jun 09, 2003 at 21:41 UTC

    see Class::DBI. also, a Super Search around here will yield tutorials, questions, and answers on usage and limitations.

    ~Particle *accelerates*

[reply]
[d/l]
Re: Writing Better Objects
by perrin (Chancellor) on Jun 09, 2003 at 21:56 UTC
[reply]
      I believe there's also one called Alzabo. Class::DBI and to a lesser extent Tangram are the best known. I have had bad experiences with SPOPS but YMMV.

      andramoiennepemousapolutropon

[reply]
        Alzabo is different. It is more like an OO replacement for SQL than a tool for making objects persistent.
[reply]
Re: Writing Better Objects
by nite_man (Deacon) on Jun 10, 2003 at 07:56 UTC
    If your objects interact with database, in my mind, using a DBIx-SearchBuilder abstaction classes: 
      package MyClass;

  use DBIx::SearchBuilder::Record::Cachable;
  @ISA = (DBIx::SearchBuilder::Record::Cachable);
  
  # Object initialize method 
  sub _Init {
      my $self = shift;
      my $DBIxHandle = shift; # A DBIx::SearchBuilder::Handle object f
+or your database

      $self->_Handle($DBIxHandle);
      $self->Table("YourTable");

      # Do some object initialization
  }
 
   # Object properties and access modes
   sub _Accessible  {                                              
       my $self = shift;                                             
       my %Cols = ( Name      => 'read/write/admin',
                    Password  => 'read/write/admin',                  
+   
                    Comments  => 'read/write',          
                    Signature => 'read/write', 
                   );
   
       return($self->SUPER::_Accessible(@_, %Cols));
 . . .

    [download]

    Thise set of modules allowes you to develop an abstact interface between your classes and database and spares you from routine things (like create a constuctor, object properties access methods etc).

    I hope that it will be useful for you :-)

          
--------------------------------
SV* sv_bless(SV* sv, HV* stash);
[reply]
[d/l]
Re: Writing Better Objects
by shotgunefx (Parson) on Jun 10, 2003 at 02:03 UTC
    Just FYI, method calls are not influenced by prototypes. see perlsub.

    -Lee

    "To be civilized is to deny one's nature."
[reply]
Re: Writing Better Objects
by clscott (Friar) on Jun 10, 2003 at 17:35 UTC
    I am in love with Class::DBI and Class::DBI is in love with me. 
    package User;
# read the docs for why not to sub-class exactly like this
use base Class::DBI::mysql; 

__PACKAGE__->set_db('Main', 'dbi:mysql:dbname', 'user', 'password');

__PACKAGE__->set_up_table( 'USERINFORMATION');

1;

# In your program
package main;

use User;

my $howmany = User->count;

my $user = User->search(username => $_[0]);
print $user->userid;
print $user->username;

#update username
$user->username('Biff');
$user->update;

    [download]
    --
    Clayton aka "Tex"
[reply]
[d/l]
      Ive got a possible solution, dbClassBuilder

      It's a small project i coded up a while ago, that ive started looking at again.

      It has a perl script that parses a simple sql schema, and feeds the hashref representation to an HTML::Template driven template to create the Class modules.

      It's fairly easy to create your own templates, in your own favoured style for whatever language you like.

      The advantage of preproducing the code is that you can delete unneeded functions and tweak code for specific needs.

      I have plans to create a more complex meta language, that can handle foreign keys and other relationships.

[reply]

        It sounds like you're recreating Alzabo except that its already written. It does exactly that.

[reply]
Re: Writing Better Objects
by dirt (Acolyte) on Jun 10, 2003 at 15:43 UTC
    If you continue to use plain vanilla DBI, you should use parameter binding, e.g. $sql="SELECT foo FROM bar WHERE id=? ..."; $sth = $dbh->prepare($sql); $sth->bind_param(1,$id), but of course, TMTOWTDI with DBI.

    Not only could you reuse your prepared statements (e.g. $sth->execute(@params) again and again), it'll protect against SQL injection.

[reply]

Back to Seekers of Perl Wisdom