NAME
Rosetta::Utility::SQLBuilder - Generate ANSI/ISO SQL-1999 and other SQL variants
DEPENDENCIES
Perl Version: 5.006
Standard Modules: none
Nonstandard Modules:
Locale::KeyedText 0.03 (for error messages)
SQL::SyntaxModel 0.16COPYRIGHT AND LICENSE
This file is part of the Rosetta database abstraction framework.
Rosetta is Copyright (c) 1999-2004, Darren R. Duncan. All rights reserved. Address comments, suggestions, and bug reports to perl@DarrenDuncan.net, or visit "http://www.DarrenDuncan.net" for more information.
Rosetta is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License (GPL) version 2 as published by the Free Software Foundation (http://www.fsf.org/). You should have received a copy of the GPL as part of the Rosetta distribution, in the file named "LICENSE"; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
Linking Rosetta statically or dynamically with other modules is making a combined work based on Rosetta. Thus, the terms and conditions of the GPL cover the whole combination. As a special exception, the copyright holders of Rosetta give you permission to link Rosetta with independent modules, regardless of the license terms of these independent modules, and to copy and distribute the resulting combined work under terms of your choice, provided that every copy of the combined work is accompanied by a complete copy of the source code of Rosetta (the version of Rosetta used to produce the combined work), being distributed under the terms of the GPL plus this exception. An independent module is a module which is not derived from or based on Rosetta, and which is fully useable when not linked to Rosetta in any form.
Any versions of Rosetta that you modify and distribute must carry prominent notices stating that you changed the files and the date of any changes, in addition to preserving this original copyright notice and other credits. Rosetta is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
While it is by no means required, the copyright holders of Rosetta would appreciate being informed any time you create a modified version of Rosetta that you are willing to distribute, because that is a practical way of suggesting improvements to the standard version.
SYNOPSIS
use Rosetta::Utility::SQLBuilder; # also loads SQL::SyntaxModel
use DBI;
my $model = SQL::SyntaxModel->new_container();
# ... Probably around this time you would stuff $model full of nodes that
# describe the schema or action concepts you want to derive SQL from.
# In this case, define a table, and a command to create it, and a routine
# to select from it; the command and routine nodes each have an id of 1.
my $builder = Rosetta::Utility::SQLBuilder->new();
my $dbh = DBI->connect( 'driver:db', 'user', 'pass' );
my $cr_tbl_cmd_node = $model->get_node( 'command', 1 ); # TABLE_CREATE cmd def earlier
my $create_sql = $builder->build_sql_routine( $cr_tbl_cmd_node );
my $sth1 = $dbh->prepare( $create_sql );
my $rv1 = $sth1->execute(); # creates a table in the database
my %named_arg_values = ( 'foo' => 'abc', 'bar' => 7 ); # to use in select where clause
my $select_from_tbl_rtn_node = $model->get_node( 'routine', 1 );
my ($select_sql, $arg_map) = $builder->build_sql_routine( $select_from_tbl_rtn_node );
my @ordered_arg_values = map { $named_arg_values{$_} } @{$arg_map};
my $sth2 = $dbh->prepare( $select_sql );
my $rv2 = $sth2->execute( @ordered_arg_values ); # opens a select cursor/query
my $rowset = $sth2->fetchall_arrayref({}); # get array of hashes
$dbh->close();DESCRIPTION
The Rosetta::Utility::SQLBuilder Perl 5 module is a functional but quickly built Rosetta utility class that converts a set of related SQL::SyntaxModel Nodes into one or more SQL strings that are ready to give as input to a particular SQL relational database management system. This class will by default produce SQL that is compliant with the ANSI/ISO SQL-1999 (or 1992 or 2003) standard, which should be useable as-is with most database products. In addition, this class takes arguments that let you vary the SQL output to an alternate SQL dialect that particular database products either require or prefer for use.
Rosetta::Utility::SQLBuilder is designed to implement common functionality for multiple Rosetta Engine classes (such as Rosetta::Engine::Generic) allowing them to focus more on the non-SQL specific aspects of their work. A Rosetta Engine would typically invoke this class within its prepare() method. This class can also be used by code on the application-side of a Rosetta::Interface tree; for example, a module that emulates an older database interface which wants to return schema dumps as SQL strings ('create' statements usually) can use this module to generate those. (For your reference, see also the Rosetta::Utility::SQLParser module, which implements the inverse functionality to SQLBuilder, and is used in both of the same places.)
Rosetta::Utility::SQLBuilder has no dependence on any database link products or libraries. You would, for example, use it in exactly the same way (probably) when generating SQL for an Oracle database regardless of whether the Engine is employing ODBC or SQL*Net as the pipe over which the SQL is sent. That said, it does have specific support for the DBI module's standard way of indicating run-time SQL bind variables (using a '?' for each instance); since DBI's arguments are positional and SQL::SyntaxModel's are named, this class will also return a map for the SQL that says what order to give the named values to DBI.
CAVEAT: THIS MODULE IS "UNDER CONSTRUCTION" AND MANY FEATURES DESCRIBED BY SQL::SyntaxModel ARE NOT YET IMPLEMENTED.
BUGS
This module is currently in pre-alpha development status, meaning that some parts of it will be changed in the near future, perhaps in incompatible ways.
SEE ALSO
perl(1), Rosetta, SQL::SyntaxModel, Rosetta::Engine::Generic, Rosetta::Utility::SQLParser.