Perl Module for Simplifying SQL Query

Question

Over the years, I've noticed a few issues with SQL queries in Perl scripts:

They're hard to read
Most developers aren't really that good with SQL.

Let's say I have the following tables:

PET          PET_TYPE     COLOR
========     ==========   ============
PET_ID       PET_TYPE     COLOR_ID
Name         Description  Description
Owner
PET_TYPE
COLOR_ID

And, I want to find all the red dogs, I'll have to produce the following SQL query.

FIND PET_ID, Name, Owner
FROM PET, PET_TYPE, COLOR
WHERE COLOR_ID.Description = 'RED'
      AND PET_TYPE.Description = 'DOG'
      AND PET.COLOR_ID = COLOR.COLOR_ID
      AND PET.PET_TYPE = PET_TYPE.PET_TYPE

I would like a Perl module that would simplify producing the querying. Something that will allow me to create the database connection, then predefine how the tables are linked. Once that is done, all developer have to do is a simplified query.

I imagine an interface as something like this:

  #
  # Create a new Database Connection
  #

  my $db = Some:Module->new(\%options);

  #
  # Now describe how these tables relate to each other.
  # It should be possible to reuse this information for
  # multiple queries. Maybe even make this a subroutine
  # or company wide Perl module that will do this for
  # the developer.
  #
  $db->link_tables (
       PET   => "COLOR_ID",
       COLOR => "COLOR_ID
  );

  $db->link_tables (
      PET      => "PET_TYPE",
      PET_TYPE => "PET_TYPE",
  );

  #
  # Now that you've created a link to the database,
  # and you've describe how the tables relate to
  # each other, let's create a query. I'm not 100%
  # sure of the interface, but it'll be something
  # like this. There might be multiple `query`
  # statements.
  #

  my $query = $db->query (
     PET_TYPE => "Description = DOG",
     COLOR    => "Description = RED"
  );

  #
  # Now, execute this query
  # 
  $db->execute_query;

  while (%row = $db->fetch) {
     say "Dog's name is $row{Pet.Name}";
     say "The owner is $row{Pet.Owner}";
  }
  #
  # Let's find all cats named Wiskers.
  # We've already described the links,
  # so that doesn't have to be redone.
  #
  my $query = $db->query (
     PET_TYPE => "Description = CAT",
     PET      => "Name = Wiskers"
  );

I know my description is incomplete, and no matter how complete the module, there has to be some emergency escape hatch where you can do a raw SQL query. However, the idea is to concentrate the program on what you want (I want red dogs or cats named whiskers) instead of the various fields and spending most of the Where clause on describing the links between tables. That would make it easier to program and easier to understand the program.

If there is no such module that does this, I'll produce one. However, before spending a few weeks on this endeavor, I want to make sure I'm not duplicating any work.

Try looking through google.com/search?q=Perl+ORM; see if anything catches your fancy. — ruakh
– ruakh, Commented Jan 23, 2012 at 18:59

jloper3 · Accepted Answer · 2012-01-23 19:00:12Z

7

I think what you are looking for is an ORM, and there are plenty of those. While not exactly what you are describing, it comes pretty close in functional terms. Have a look at Class::DBI and DBIx::Class, or just google for perl ORMs.

answered Jan 23, 2012 at 19:00

jloper3

3841 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David W. Over a year ago

At least I now know what I'm looking for: ORM. Thanks

Cornel Ghiban · Accepted Answer · 2012-01-23 19:21:11Z

2

There are quite a few modules: Class::DBI, Rose::DB, DBIx::Class. While DBIx::Class is more powerful and heavily updated, Class::DBI is simpler to use. Later on, if you want to switch to DBIx::Class, you can do so without much effort.

edited Jan 23, 2012 at 19:21

answered Jan 23, 2012 at 19:00

Cornel Ghiban

9024 silver badges6 bronze badges

Collectives™ on Stack Overflow

Perl Module for Simplifying SQL Query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related