File Coverage

blib/lib/Fey.pm
Criterion Covered Total %
statement 6 6 100.0
branch n/a
condition n/a
subroutine 2 2 100.0
pod n/a
total 8 8 100.0


line stmt bran cond sub pod time code
1             package Fey;
2              
3 23     23   1016166 use strict;
  23         47  
  23         833  
4 23     23   106 use warnings;
  23         36  
  23         1104  
5              
6             our $VERSION = '0.43';
7              
8             1;
9              
10             # ABSTRACT: Better SQL Generation Through Perl
11              
12             __END__
13              
14             =pod
15              
16             =head1 NAME
17              
18             Fey - Better SQL Generation Through Perl
19              
20             =head1 VERSION
21              
22             version 0.43
23              
24             =head1 SYNOPSIS
25              
26             use Fey::Literal::Function;
27             use Fey::Placeholder;
28             use Fey::Schema;
29             use Fey::SQL;
30              
31              
32             my $schema = hand_waving();
33              
34             my $user = $schema->table('User');
35             my $group = $schema->table('Group')
36              
37             my $select = Fey::SQL->new_select();
38              
39             my $func = Fey::Literal::Function->new( 'LCASE', $user->column('username') );
40              
41             $select->select( $user->columns( 'user_id', 'username' ) )
42             ->from( $user, $group )
43             ->where( $group->group_id, 'IN', 1, 2, 3 )
44             ->and ( $func, 'LIKE', 'smith%' );
45              
46             print $select->sql($dbh);
47              
48             =head1 DESCRIPTION
49              
50             The C<Fey> distribution contains a set of modules for representing the
51             components of a DBMS schema, and for dynamically generating SQL
52             queries based on that schema.
53              
54             =head1 USAGE
55              
56             Loading this module does nothing. It's just here to provide docs and a
57             version number for the distro.
58              
59             You'll want to take a look at L<Fey::Schema>, L<Fey::Table>, and other
60             modules in the distro for more details.
61              
62             =head1 WHAT IS Fey?
63              
64             The goal of the core C<Fey> distro is to provide a simple, flexible
65             way of I<dynamically> generating complex SQL queries in Perl. Other
66             packages build on top of this functionality to create a complete ORM
67             (C<Fey::ORM>).
68              
69             =head1 GETTING STARTED
70              
71             If you're interested in an ORM, take a look at the C<Fey::ORM> distro.
72              
73             To generate SQL with Fey, you first need to create a set of objects
74             representing the tables and foreign keys in your schema. The simplest
75             way to do this is to use the C<Fey-Loader> distro, which will connect
76             to an existing schema and generate a set of objects for you.
77              
78             Alternatively, you can create these objects via Fey's API. You would
79             first create a L<Fey::Schema> object. This object will hold all of
80             your tables and foreign keys. If you want to create your schema this
81             way, you should start with the L<Fey::Schema>, L<Fey::Table>, and
82             L<Fey::FK> APIs. You'll also want to use the L<Fey::Column> API.
83              
84             Once you have a schema, you can generate SQL using L<Fey::SQL>, or a
85             DBMS-specific subclass of L<Fey::SQL>.
86              
87             =head1 THE CORE Fey DISTRO
88              
89             The emphasis in the core Fey distro is on dynamic queries,
90             particularly on the tables/columns/etc involved in the query, not just
91             the bound parameters.
92              
93             This is I<not> what I mean by a dynamic query ...
94              
95             SELECT user_id FROM User where username = ?
96              
97             While this is dynamic in the sense that the username is parameterized
98             and may change on each invocation, it is still easily handled by a
99             phrasebook class. If that is all you need, I suggest checking out any
100             of C<Class::Phrasebook::SQL>, C<Data::Phrasebook>, or C<SQL::Library>
101             on CPAN.
102              
103             Imagine that we have a database with a User table and a Message table,
104             where each message has a user who is that message's creator. We might
105             want to grab all the users in the database, in which case we would do
106             a simple C<SELECT> against the User table ...
107              
108             SELECT * FROM User
109              
110             But maybe we want to get all the users who have created a message in
111             the last week:
112              
113             SELECT User.*
114             FROM User JOIN Message
115             USING (user_id)
116             WHERE Message.creation_date >= ?
117              
118             The resultset for our query is still the same (0+ users) but the
119             constraints of the query are more complex. Now imagine another dozen
120             or so permutations on how we search for users. This is what I mean by
121             "dynamically" generating queries.
122              
123             =head1 RATIONALE
124              
125             You probably don't need to read this if you just wanted to know how to
126             use Fey.
127              
128             =head2 Why Not Use a Phrasebook?
129              
130             Let's assume we have a simple User table with the following columns:
131              
132             username
133             state
134             first_name
135             last_name
136             access_level
137              
138             Limiting ourselves to queries of equality ("username = ?", "state =
139             ?"), we would still need 32 (1 + 5 + 10 + 10 + 5 + 1) entries to
140             handle all the possible combinations of columns. Now imagine adding in
141             variants like allowing for wildcard searches using LIKE or regexes, or
142             more complex variants involving an "OR" in a subclause.
143              
144             This gets even more complicated if you start adding in joins, outer
145             joins, and so on. It's plain to see that a phrasebook gets too large
146             to be usable at this point. You'd probably have to write a program
147             just to generate the phrasebook and keep it up to date!
148              
149             =head2 Why Not String Manipulation?
150              
151             The next idea that might come to mind is to dump the phrasebook in
152             favor of string manipulation. This is simple enough at first, but
153             quickly gets ugly. Handling all of the possible options correctly
154             requires lots of fiddly code that has to concatenate bits of SQL in
155             the correct order, taking into account where to put in commas,
156             C<WHERE> vs C<AND>, and so on and so forth. I've been there, and trust
157             me, it's madness.
158              
159             =head2 The Solution
160              
161             The core Fey modules provide a solution to the dynamic SQL
162             problem. Using Fey, you can specify queries in the form of I<Perl
163             methods and objects>. Fey provides a set of objects to represent the
164             parts of a schema, specifically tables, columns, and foreign
165             keys. Using these objects along with L<Fey::SQL>, you can easily
166             generate very complex queries.
167              
168             This core distro is also intended to be the foundation for building
169             higher-level tools like an ORM. See C<Fey::ORM> for just such a thing.
170              
171             =head1 HISTORY AND GOALS
172              
173             This module comes from my experience writing and using Alzabo. Alzabo
174             does everything this module does, and a lot more. The fact that Alzabo
175             does so many things has become a fairly problematic in its
176             maintenance, and Alzabo was over 6 years old at the time this project
177             was begun (August of 2006).
178              
179             =head2 Goals
180              
181             Rather than coming up with a very smart solution that allows us to use
182             80% of a DBMS's functionality, I'd rather come up with a solution
183             that's dumber but supports all (or at least 99%) of the DBMS's
184             features. It's easy to add smarts on top of a dumb layer, but it can
185             be terribly hard to add that last 20% once you've got something really
186             smart.
187              
188             The goals for Fey, based on my experience with Alzabo, are the
189             following:
190              
191             =over 4
192              
193             =item *
194              
195             Provide a simple way to generate queries dynamically. I really like
196             how this works with Alzabo conceptually, but Alzabo is not as flexible
197             as I'd like and it's "build a data structure" approach to query
198             building can become very cumbersome.
199              
200             Rather than complex data structures, with Fey you call methods on a
201             C<Fey::SQL> object to build up a query. This turns out to be simpler
202             to work with.
203              
204             Fey, unlike Alzabo, can be used to generate multi-row updates and
205             deletes, and it supports sub-selects, unions, etc. and all that other
206             good stuff.
207              
208             =item *
209              
210             Fey supports complex query creation with less fiddliness than
211             Alzabo. This means that the class to represent queries is a little
212             smarter and more flexible about the order in which bits are added.
213              
214             For example, in using Alzabo I often came across cases where I wanted
215             to add a table to a query's join I<if it hasn't already been
216             added>. With Alzabo, there's no nice clean way to do this. Simply
217             adding the table to the join parameter twice will cause an error. It
218             would be nice to simply be able to do this
219              
220             $select->join( $foo_table => $bar_table );
221              
222             and have it do the right thing if that join already exists (where the
223             right thing is just do nothing). C<Fey::SQL> does exactly that.
224              
225             =item *
226              
227             Provide the core for an RDBMS-OO mapper similar to a combination of
228             C<Alzabo::Runtime::Row> and C<Class::AlzaboWrapper>.
229              
230             At the same time, query generation and the ORM are decoupled. You can
231             use L<Fey::SQL> to generate queries without having to every use the
232             C<Fey::ORM> ORM.
233              
234             =item *
235              
236             Be declarative like Moose. In particular, the C<Fey::ORM> ORM is as
237             declarative as possible, and aims to emulate Moose's declarative sugar
238             style where possible.
239              
240             =item *
241              
242             Leverage the API user's SQL knowledge. Building up queries with Fey
243             looks enough like SQL that you shouldn't have to think I<too> hard
244             about it. This means join support is baked in at a core level, as are
245             subselects and ideally anything else you can do in SQL.
246              
247             =back
248              
249             =head2 Problems with Alzabo
250              
251             Here are some of the problems I've had with Alzabo over the years
252             which inspired me to create Fey ...
253              
254             =over 4
255              
256             =item *
257              
258             Adding support for a new DBMS to Alzabo is a lot of work, so it only
259             supports MySQL and Postgres. Alzabo tries to be really smart about
260             preventing users from shooting themselves in the foot, and requires a
261             lot of DBMS-specific code to achieve this.
262              
263             In retrospect, being a lot dumber and allowing for foot-shooting makes
264             supporting a new DBMS much easier. People generally know how their
265             DBMS works, and if they generate an invalid query or table name, it
266             will throw an error.
267              
268             For example, while Fey can accommodate per-DBMS query (sub)classes, it does
269             not include any by default, and is capable of supporting many DBMS-specific
270             features without per-DBMS classes.
271              
272             =item *
273              
274             Alzabo has too much DBMS-specific knowledge. If you want to use a SQL
275             function in a query, you have to import a corresponding Perl function
276             from the appropriate C<Alzabo::SQLMaker>, which limits you to what's
277             already defined, or forces you to go through a cumbersome API to
278             define a new SQL function for use in your Perl code.
279              
280             By contrast, Fey has simple generic support for arbitrary functions
281             via the C<Fey::Literal::Function> class. If you need more flexibility
282             you can use the C<Fey::Literal::Term> subclass to generate an
283             arbitrary snippet to insert into your SQL.
284              
285             A related problem is that Alzabo doesn't support multiple versions of
286             a DBMS very well. Either it doesn't work with an older version at all,
287             or it doesn't support some enhanced capability of a newer version. It
288             mostly supports whatever version I happened to be using when I wrote a
289             specific piece of functionality.
290              
291             =item *
292              
293             There are now free GUI design tools for specific databases that do a
294             better job of supporting the database in question than Alzabo ever
295             has.
296              
297             =item *
298              
299             Alzabo separates its classes into Create (for generation of DDL) and
300             Runtime (for DML) subclasses, which might have been worth the memory
301             savings six years ago, but just makes for an extra hassle now.
302              
303             =item *
304              
305             When I originally developed Alzabo, I included a feature for
306             generating high-level application object classes which subclass the
307             Alzabo classes and add "business logic" methods. This is what is
308             provided by C<Alzabo::MethodMaker>.
309              
310             Nowadays, I prefer to have my business logic classes simply use the
311             Alzabo classes. In other words, I now prefer "has-a" and "uses-a"
312             versus "is-a" object design for this case.
313              
314             Method auto-generation based on a specific schema can be quite handy,
315             but it should be done in the domain-specific application classes, not
316             as a subclass of the core functionality.
317              
318             =item *
319              
320             Storing schemas in an Alzabo-specific format is problematic for many
321             reasons. It's simpler to simply get the schema definition from an
322             existing schema, or to allow users to define it in code.
323              
324             =item *
325              
326             Alzabo's referential integrity checking code was really cool back when
327             I mostly used MySQL with MYISAM tables. Now it's just a maintenance
328             burden and a barrier for new features.
329              
330             =item *
331              
332             I didn't catch the testing bug until quite a while after I'd started
333             working on Alzabo. Alzabo's test suite is nasty. Fey is built with
334             testability in mind, and high test coverage is part of my ongoing
335             goals for the project.
336              
337             =item *
338              
339             Alzabo does too many things, which makes it hard to explain and
340             document.
341              
342             =back
343              
344             =head1 WHY IS IT NAMED Fey?
345              
346             When I first started working on Fey, it was named "Q". This was a nice
347             short name to type, but obviously unsuitable for releasing on CPAN. I
348             wanted a nice short name that could be used in multiple distributions,
349             like John Siracusa's "Rose" modules.
350              
351             I was standing in the shower one day and had the following series of
352             thoughts leading to Fey. Reading this will may give you an unpleasant
353             insight into my mind. You have been warned.
354              
355             =over 4
356              
357             =item * SQLy
358              
359             This module is "SQL-y", as in "related to SQL". However, this name is
360             bad for a number of reasons. First, it's not clear how to pronounce
361             it. It may make you think of a YACC grammar ("SQL.y"). It's a weird
362             combo of upper- and lower-case letters.
363              
364             =item * SQLy => Squall
365              
366             "SQLy" and "Squall" share a number of letters, obviously.
367              
368             Squall is a single short word, which is good. However, it's a bit
369             awkward to type and has a somewhat negative meaning to me, because a
370             storm can mean trouble.
371              
372             =item * Squall => Lionheart => Faye
373              
374             Squall Lionheart is a character in Final Fantasy VIII, which IMO is
375             the best Final Fantasy game before the PS2.
376              
377             The inimitable Faye Wong sang the theme song for FF VIII. I love Faye
378             Wong.
379              
380             =item * Faye => Fey
381              
382             And thus we arrive at "Fey". It's nice and short, easy to type, and
383             easy to say.
384              
385             Some of its meanings are "otherworldly" or "magical". Attempting to
386             combine SQL and OO in any way is certainly unnatural, and if done
387             right, perhaps magical. Fey can also mean "appearing slightly
388             crazy". This project is certainly that.
389              
390             =back
391              
392             Yes, I'm a nerd, I know.
393              
394             =head1 BUGS
395              
396             Please report any bugs or feature requests to C<bug-fey@rt.cpan.org>,
397             or through the web interface at L<http://rt.cpan.org>. I will be
398             notified, and then you'll automatically be notified of progress on
399             your bug as I make changes.
400              
401             =head1 AUTHOR
402              
403             Dave Rolsky <autarch@urth.org>
404              
405             =head1 CONTRIBUTORS
406              
407             =for stopwords Aristotle Pagaltzis hdp@glaive.weftsoar.net hdp@localhost hdp@rook.opensourcery.com Oliver Charles
408              
409             =over 4
410              
411             =item *
412              
413             Aristotle Pagaltzis <pagaltzis@gmx.de>
414              
415             =item *
416              
417             hdp@glaive.weftsoar.net <hdp@glaive.weftsoar.net>
418              
419             =item *
420              
421             hdp@localhost <hdp@localhost>
422              
423             =item *
424              
425             hdp@rook.opensourcery.com <hdp@rook.opensourcery.com>
426              
427             =item *
428              
429             Oliver Charles <oliver@ocharles.org.uk>
430              
431             =back
432              
433             =head1 COPYRIGHT AND LICENSE
434              
435             This software is Copyright (c) 2011 - 2015 by Dave Rolsky.
436              
437             This is free software, licensed under:
438              
439             The Artistic License 2.0 (GPL Compatible)
440              
441             =cut