Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses

OO Perl: classes and database access

by fx (Pilgrim)
on Sep 27, 2001 at 19:57 UTC ( #115130=perlquestion: print w/replies, xml ) Need Help??
fx has asked for the wisdom of the Perl Monks concerning the following question:

Hi there,

First of all thanks to all those who replied to my previous posts of Using DBI: handlers or references to handlers? and OO Perl: calling a constructor within a class. After taking your advice my application is now maturing well.

I have come across a new dilemma however. My application has a number of users, and each user can control several projects. So I have a User class and a Project class.

There is a backend database to all this with user and project tables. I am struggling to find the best way to get the data out of the database and into the objects.

An example: users log into the system using This takes a username, retrieves the user's details from the database and makes a new User object.

I have tried the following methods for populating the User object with the details:

- hardcode the SQL into, retrieve an array, and pass that array to the new() constructor of User

- write a class method for User which retrieves an array of data and calls the constructor

- write a new() constructor that queries the database

- write a Database package/class which contains several functions for retrieving values, which are then fed to a new() constructor for User

- write a User::Database class for specific user-type queries on the database

However, I'm confused as to the 'best' way to do this. Everytime I come up with a solution I seem to find a big problem with it. Another consideration I have is that as each user has several projects, then each User object has to have the capability to store an array of Project objects. Should I just do one big retrieve when I create a User object, or do I create the array of Project objects when needed? This confused me even more when writing the constructors.

Any pointers are most welcome.



Replies are listed 'Best First'.
Re: OO Perl: classes and database access
by MZSanford (Curate) on Sep 27, 2001 at 20:19 UTC
    I did a simlar project when making a system for a company to track employee's and related skills. I also had two tables and the login gave me trouble. I think this all came down to object design. As much code as i have written line-by-line as i think of it, Object design is once of the things were it pays to spend a bit of time before the code begins to flow. With what you have said, i don't think i could say the best way, as i do not presume to know everything there is about OO design or Perl. But, what i would do would probably look something like this :

    my assumption: Assuming the user table has something like uid, username, passwd (crypt()'d) ... the project table, i would imagine, has project info, including something like project_id ... and would lastly guess that there is a table with something like uid and project_id which is used to pull it all together.
    if this is not correct, please bear in mind this is just to show what an object might look like
    $user_object = { 'uid' => 0, 'user_name' => 'root', 'projects' => [ 1,2,3,4 ], # project_id's };
    Now, where to initilaize this data. I would guess that you would want to do it in the new() constructor. Assuming new() is provided a user name (or id), it should be some simple SQL to get the remaining data into the object.

    You could also use a method like load() to get the data, and it may not be a bad idea to also provide that (and just have new() call it too) because the second your query for project_id's has finished, it will be incorrect (possibly). If you have stuck to OO priciples (and all info from the object is via methods), you could not hold the project data at all, and simply have the projects() method run the SQL based on the uid/user_name. That would prevent stale project data.

    Once again, this is mostly opinion, but this is the general aproach i have used.
    "They shall not overcome. Whoever told them that the truth shall set them free was obviously and grossly unfamiliar with federal law."
        -- John Ashcroft
Re: OO Perl: classes and database access
by jeroenes (Priest) on Sep 27, 2001 at 20:41 UTC
    Have you read "Code Complete" by McConnell? Read all about abstraction layers and independent components.

    You have already had some suggestions about switching from database to flat file etc. Separation also holds for your user-interaction interface, whatever it is.

    E.g., is clearly an interface thingy, so make it independent from your User-objects. That means: calls the user-class, and the user-class calls the database-interaction class, where holds the interface (web/CLI/GUI), user holds your (temporal) user data and the database class is left with calling whatever the database is.

    You just apply this kind of reasoning to every relation you encounter while making up the program, and I think you will get around to some nice stuff pretty soon.

    I'm not a real experienced OO'er or whatever, so listen to everybody else here for more advice. Browse tilly's nodes for example....


    "We are not alone"(FZ)

Re: OO Perl: classes and database access
by runrig (Abbot) on Sep 27, 2001 at 20:42 UTC
    If you have the time, you might try investigating existing solutions to OO database access and/or persistence, e.g. Tangram, DBIx::Recordset, Alzabo.

    I've never used any of these, but you can find information on these and other modules through Super Search or CPAN.

    FYI, here's a post from the DBI list:

    I just thought I'd let this list know that there's a comparison of persistence tools for Perl at POOP stands for Perl Object-Oriented Persistence. There's also a mailing list for discussion of such things ( The document as it stands compares a number of RDBMS-OO mappers as well as some OO-RDBMS mappers, including Class::DBI, Alzabo, and DBIx::RecordSet in the former category and Tangram and SPOPS in the latter.

    And there's an article in this month's Linux World here:

Re: OO Perl: classes and database access
by thpfft (Chaplain) on Sep 27, 2001 at 21:57 UTC

    Your question is addressed exactly by Class::DBI. It is particularly good at translating database relationships into object methods, so your users and projects question all but disappears. Assuming you have a Class::DBI class for each of user and project, put this in the My::User class:

    __PACKAGE__->hasa_list( 'My::Project', ['name_of_field_in_project_table'], 'projects', );

    And then to retrieve the projects all you have to say is:

    my $user = My::User->retrieve($id); my @projects = $user->projects();

    I use it all the time and i can't recommend it highly enough.

      Thanks for the suggestion, Class::DBI is working well for me now.


Re: OO Perl: classes and database access
by TGI (Parson) on Sep 27, 2001 at 21:10 UTC

    Great question!

    Two approaches seem attractive to me. First, you could code a method that loads data from the db into an object, and provide a way to call it from the constructor. Or you could make a User::Database class to handle communications with the database.

    The first option is more compact and would be quite servicable. If you have an object method that loads data from the db that is separate from the constructor, you gain some flexibility--you may want to reload a User object's data at some point. For convenience, it would be nice to be able to load data into an object when calling it. Since the load method would need to know what object to load data into, it makes sense to make load an object method.

    Class methods are good for things that operate on the all the instance of a class, like an instance counter. A method that flushes all user objects to the database is a good example. You could have a class array that stores a ref to each object. Your class method User_flush_all could loop through the array and call a flush oject method on each user.

    Making a User::Database class to handle db connections is a great idea. It allows you to abstract the issue of persistance. This way, if your company falls on hard times and you can't afford that Oracle server anymore, you can easily modify User::Database to use DBM files or mySQL or whatever. Of course there are the excellent DBI modules that already some of this abstraction, so it may not be worth it to separate out the persistance code. It's all a tradeoff of time/cost vs. flexibility.

    As to the loading of Projects for Users, I would favor an approach where project data was loaded only when needed. Perhaps each User should have attributes for Project_IDs => [1, 2, 3, 4,]; and for Open_Projects => [Project(0x884204),Project(0x883204),Project(0x886504),]. So when you want to look at a project, you call $project = $user->open_project($project_id) which creates a new Project and adds the project to the Open_Projects array for that user. In this way you can avoid the memory bloat and startup lag of loading all a user's projects.

    TGI says moo

Re: OO Perl: classes and database access
by CubicSpline (Friar) on Sep 27, 2001 at 21:15 UTC
    Sometimes it helps to look at these kinds of interactions in a class interaction diagram of sorts. Given that you have a User object, one of it's actions is going to be LogIn. I would propose instantiating a User with as small an amount of data as possible (could it just be a username or id?) and let the methods take care of populating the rest of members for you. In example, I would look at doing something like this:

    $U = User->new( $user_name ); # at this point there is a minimal amount of data for the User object, + but it should still be functional $U->LogIn(); # handles all of the database connection activity, loads in user info +as well as populates project list, etc.
    HTH ... actually, HTII (Hope This Isn't Incoherent)
Re: OO Perl: classes and database access
by perrin (Chancellor) on Sep 27, 2001 at 21:59 UTC
    I like to do this by putting the database stuff in the constructor of the data object. However, I usually make three methods: load, save, and new. You call new when making an object that isn't in the database yet (a new one!), load - with an ID - to load one from the database, and save to save changes to an object.

    I'm wary of object/relational mapping tools for various reasons, but a lot of people seem to like SPOPS and Class::DBI for automating these steps. If your objects map to database tables in a simple way, these could save you some coding.

Re: OO Perl: classes and database access
by cfreak (Chaplain) on Sep 27, 2001 at 22:04 UTC

    I'm not really sure what your user object looks like so this make or may not work:

    Instead of trying to populate the user object with an array, why not try passing the database output directly to it with a hashref? I've used this method on several projects:

    my $query = "SELECT * FROM users WHERE user=?"; my $sth = $dbh->prepare($query); $sth->execute($user) or die $sth->errstr(); # Here you have all the user's info in a hashref. The # keys are the same as the database column names. my $row = $sth->fetchrow_hashref(); # Then you could call the new method: my $user_info = User->new($row);

    Then your user info new method basically could just bless the input

    package User; sub new { my($class,$user_info) = @_; return bless $user_info,$class; }

    Hope that helps

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://115130]
Approved by root
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (8)
As of 2018-04-23 17:36 GMT
Find Nodes?
    Voting Booth?