Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Handling near-redundant SQL queries

by Anonymous Monk
on Aug 31, 2004 at 22:59 UTC ( #387352=perlquestion: print w/ replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Lets say we're working on a database driven mod_perl application. We've got some tables in our database:

person: id, name, telephone_number, address, ...
person_jobs: person_id, company_id
companies: id, name, address, ...
company_products: product_id, company_id
products: id, name, description

Now, lets say on our main page we want to display a list of all the people in our database, and the company they work for. So, we create a sub called get_person_companies() (or something like that), which performs this task for us.

Then, later on, we need to display that same information on another page of our site, except we also want to display the persons phone number. Then we want a list of people, companies, and company products (contrived example, just to add to the complexity of generating the neccessary SQL).

How do you handle this? Create a new, nearly identical, sub for each set of differing data you need? Grab all the data and discard that which you don't need? Something better, I hope?

In the past I've taken the ad hoc route of just recycling the same sub to grab more and more data. I know that I'm unhappy with that approach, but I haven't been able to come up with an appealing alternative. I don't like the approach of writing a new sub for each set of data because:

  1. You duplicate a lot of code (although, I suppose, a lot of that is SQL),
  2. The thought of having two subs which perform such identical tasks bothers me, and
  3. I never know what to name the new subs (get_data_for_so_and_so_section() sounds wrong, but what is your alternative?).

Maybe I'm just being picky or something.

Anyway, Thanks for reading, and hopefully helping :-)

20040908 Edit by ysth: change title from Managing complexity

Comment on Handling near-redundant SQL queries
Re: Handling near-redundant SQL queries
by NetWallah (Abbot) on Aug 31, 2004 at 23:51 UTC
    Check out Class::DBI - It's OO structure is just the ticket!

        Earth first! (We'll rob the other planets later)

Re: Handling near-redundant SQL queries
by blokhead (Monsignor) on Sep 01, 2004 at 01:08 UTC
    I wrote Class::Tables just for this kind of situation. You have a schema sitting there in the database, and you want a nice interface with minimal effort. In this case, the schema might need a minor change -- unless you're using SQLite, you'll also need an 'id' column for each of the many-to-many mapping tables. Apart from that little change, the module will automatically do the right thing here... Even match up the singular "company_id" column with the plural "companies" table, a feature I'm quite proud of. All it takes is 2 extra lines of code, and you have the proper object persistence classes available. Check out the pod.

    The newest development version also supports many-to-many accessors directly (thanks to contributions from elusion) which your schema will greatly benefit from.

    If you need more complex functionality, Class::DBI is the way to go, so check them both out to see which is a better fit for your goals.

    Either way, with object-persistence modules like this, you can perform a query and get back a collection of objects. Then you should really be sending the results to an HTML templating system. It should be the current template that decides what fields from the objects should be shown (phone number, etc). The core application logic shouldn't have to be burdened with that. Certainly you wouldn't want a separate function in your code for each possible combination of columns to fetch/show.

    blokhead

Re: Handling near-redundant SQL queries
by dragonchild (Archbishop) on Sep 01, 2004 at 01:31 UTC
    I have a module that will construct an SQL query given the following:
    • A list of select clauses
    • A list of where clauses (including all the bind parameters)
    • A list of groupby clauses
    • A list of parameters to pass to $dbh->execute()

    It does so by doing the following:

    • It extracts all the table names from the select, where, and groupby clauses
    • It takes a list of all the tables in your database and how they're connected (you supply this ahead of time.)
    • Using the list of connections, it builds the SQL query for you and returns it for usage by the caller.

    Usage is something like:

    my $sql = $builder->build_sql( select => [ "foo.column AS blah", "foo.column2 AS blah2", "bar.column AS bar", ], where => [ "foo.column3 = ?", "bar.column2 = ?", ], params => [ 3, 5, ], ); ## This would generate something like: SELECT foo.column AS blah ,foo.column2 AS blah2 ,bar.column AS bar FROM foo JOIN table1 ON (foo.foo = table1.foo) JOIN bar ON (table1.bar = bar.bar) WHERE foo.column3 = ? AND bar.column2 = ?

    Additionally, if you have selectively denormalized your schema for speed purposes, this algorithm would take that into account. (I have, because it's a readonly reporting database. Other possibilities would be for an OLTP database that uses triggers to maintain certain denormalizations.)

    Is this something that would solve your problem? I haven't released it yet, but could in a week or two. (I planned on doing so anyways, but could do so as early as the end of next week ...)

    Note: This module doesn't do anything with the database. It's actually a text manipulation module that has some graph theory in the backend. So, it's completely RDBMS-agnostic. And, as you can tell, it produces SQL99-compliant queries. It also supports OUTER JOINs, if needed. I use this module to construct queries for both Oracle9i and MySQL 4.1.x.

    ------
    We are the carpenters and bricklayers of the Information Age.

    Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

    I shouldn't have to say this, but any code, unless otherwise stated, is untested

      I have a module that will construct an SQL query given the following...

      I took a similar approach in DBIx::SQLEngine, which accepts a very flexible set of arguments and can both generate SQL or optionally execute it for you.

      When working at this higher level, the OP may find that they don't need as many subroutines -- for example, rather than having subroutines for each version of a select query based on which columns you need, your get_person_companies() subroutine could take a list of additional columns to include in the results.

Re: Handling near-redundant SQL queries
by bronto (Priest) on Sep 01, 2004 at 08:41 UTC
    Lets say we're working on a database driven mod_perl application...what is your alternative?

    Mine? Well, fire up AxKit, create some XSP pages using AxKit::XSP::ESQL, and an XPathScript stylesheet.

    You may also want to read ubu's excellent book on AxKit.

    Ciao!
    --bronto


    The very nature of Perl to be like natural language--inconsistant and full of dwim and special cases--makes it impossible to know it all without simply memorizing the documentation (which is not complete or totally correct anyway).
    --John M. Dlugosz
Re: Handling near-redundant SQL queries
by Anonymous Monk on Sep 02, 2004 at 14:48 UTC

    Thanks to all for the advice.

    I will be taking a closer look at all the solutions mentioned throughout the week.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://387352]
Approved by BrowserUk
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (14)
As of 2014-07-10 22:38 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (217 votes), past polls