CODEWITHSUNDEEP
sql-serverperldbi
David W.
David W.

Reputation: 107040

Perl Module for Simplifying SQL Query

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

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.

Upvotes: 2

Views: 204

Answers (2)

Cornel Ghiban
Cornel Ghiban

Reputation: 902

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.

Upvotes: 2

jloper3
jloper3

Reputation: 384

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.

Upvotes: 7

Related Questions