Dave Rolsky > Fey > Fey

Download:
Fey-0.40.tar.gz

Dependencies

Annotate this POD

CPAN RT

New  3
Open  2
View/Report Bugs
Module Version: 0.40   Source  

NAME ^

Fey - Better SQL Generation Through Perl

VERSION ^

version 0.40

SYNOPSIS ^

  use Fey::Literal::Function;
  use Fey::Placeholder;
  use Fey::Schema;
  use Fey::SQL;


  my $schema = hand_waving();

  my $user  = $schema->table('User');
  my $group = $schema->table('Group')

  my $select = Fey::SQL->new_select();

  my $func = Fey::Literal::Function->new( 'LCASE', $user->column('username') );

  $select->select( $user->columns( 'user_id', 'username' ) )
         ->from( $user, $group )
         ->where( $group->group_id, 'IN', 1, 2, 3 )
         ->and  ( $func, 'LIKE', 'smith%' );

  print $select->sql($dbh);

DESCRIPTION ^

The Fey distribution contains a set of modules for representing the components of a DBMS schema, and for dynamically generating SQL queries based on that schema.

USAGE ^

Loading this module does nothing. It's just here to provide docs and a version number for the distro.

You'll want to take a look at Fey::Schema, Fey::Table, and other modules in the distro for more details.

WHAT IS Fey? ^

The goal of the core Fey distro is to provide a simple, flexible way of dynamically generating complex SQL queries in Perl. Other packages build on top of this functionality to create a complete ORM (Fey::ORM).

GETTING STARTED ^

If you're interested in an ORM, take a look at the Fey::ORM distro.

To generate SQL with Fey, you first need to create a set of objects representing the tables and foreign keys in your schema. The simplest way to do this is to use the Fey-Loader distro, which will connect to an existing schema and generate a set of objects for you.

Alternatively, you can create these objects via Fey's API. You would first create a Fey::Schema object. This object will hold all of your tables and foreign keys. If you want to create your schema this way, you should start with the Fey::Schema, Fey::Table, and Fey::FK APIs. You'll also want to use the Fey::Column API.

Once you have a schema, you can generate SQL using Fey::SQL, or a DBMS-specific subclass of Fey::SQL.

THE CORE Fey DISTRO ^

The emphasis in the core Fey distro is on dynamic queries, particularly on the tables/columns/etc involved in the query, not just the bound parameters.

This is not what I mean by a dynamic query ...

 SELECT user_id FROM User where username = ?

While this is dynamic in the sense that the username is parameterized and may change on each invocation, it is still easily handled by a phrasebook class. If that is all you need, I suggest checking out any of Class::Phrasebook::SQL, Data::Phrasebook, or SQL::Library on CPAN.

Imagine that we have a database with a User table and a Message table, where each message has a user who is that message's creator. We might want to grab all the users in the database, in which case we would do a simple SELECT against the User table ...

 SELECT * FROM User

But maybe we want to get all the users who have created a message in the last week:

 SELECT User.*
   FROM User JOIN Message
        USING (user_id)
  WHERE Message.creation_date >= ?

The resultset for our query is still the same (0+ users) but the constraints of the query are more complex. Now imagine another dozen or so permutations on how we search for users. This is what I mean by "dynamically" generating queries.

RATIONALE ^

You probably don't need to read this if you just wanted to know how to use Fey.

Why Not Use a Phrasebook?

Let's assume we have a simple User table with the following columns:

 username
 state
 first_name
 last_name
 access_level

Limiting ourselves to queries of equality ("username = ?", "state = ?"), we would still need 32 (1 + 5 + 10 + 10 + 5 + 1) entries to handle all the possible combinations of columns. Now imagine adding in variants like allowing for wildcard searches using LIKE or regexes, or more complex variants involving an "OR" in a subclause.

This gets even more complicated if you start adding in joins, outer joins, and so on. It's plain to see that a phrasebook gets too large to be usable at this point. You'd probably have to write a program just to generate the phrasebook and keep it up to date!

Why Not String Manipulation?

The next idea that might come to mind is to dump the phrasebook in favor of string manipulation. This is simple enough at first, but quickly gets ugly. Handling all of the possible options correctly requires lots of fiddly code that has to concatenate bits of SQL in the correct order, taking into account where to put in commas, WHERE vs AND, and so on and so forth. I've been there, and trust me, it's madness.

The Solution

The core Fey modules provide a solution to the dynamic SQL problem. Using Fey, you can specify queries in the form of Perl methods and objects. Fey provides a set of objects to represent the parts of a schema, specifically tables, columns, and foreign keys. Using these objects along with Fey::SQL, you can easily generate very complex queries.

This core distro is also intended to be the foundation for building higher-level tools like an ORM. See Fey::ORM for just such a thing.

HISTORY AND GOALS ^

This module comes from my experience writing and using Alzabo. Alzabo does everything this module does, and a lot more. The fact that Alzabo does so many things has become a fairly problematic in its maintenance, and Alzabo was over 6 years old at the time this project was begun (August of 2006).

Goals

Rather than coming up with a very smart solution that allows us to use 80% of a DBMS's functionality, I'd rather come up with a solution that's dumber but supports all (or at least 99%) of the DBMS's features. It's easy to add smarts on top of a dumb layer, but it can be terribly hard to add that last 20% once you've got something really smart.

The goals for Fey, based on my experience with Alzabo, are the following:

Problems with Alzabo

Here are some of the problems I've had with Alzabo over the years which inspired me to create Fey ...

WHY IS IT NAMED Fey? ^

When I first started working on Fey, it was named "Q". This was a nice short name to type, but obviously unsuitable for releasing on CPAN. I wanted a nice short name that could be used in multiple distributions, like John Siracusa's "Rose" modules.

I was standing in the shower one day and had the following series of thoughts leading to Fey. Reading this will may give you an unpleasant insight into my mind. You have been warned.

Yes, I'm a nerd, I know.

BUGS ^

Please report any bugs or feature requests to bug-fey@rt.cpan.org, or through the web interface at http://rt.cpan.org. I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.

AUTHOR ^

Dave Rolsky <autarch@urth.org>

COPYRIGHT AND LICENSE ^

This software is Copyright (c) 2011 by Dave Rolsky.

This is free software, licensed under:

  The Artistic License 2.0 (GPL Compatible)
syntax highlighting: