NAME
Rosetta::Utility::SQLBuilder - Generate ANSI/ISO SQL-2003 and other SQL variants
DEPENDENCIES
Perl Version: 5.006
Standard Modules: none
Nonstandard Modules:
Locale::KeyedText 0.03 (for error messages)
SQL::SyntaxModel 0.17COPYRIGHT 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
This module is a reference implementation of fundamental Rosetta features.
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-2003 (or 1999 or 1992) 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.
CONSTRUCTOR FUNCTIONS AND METHODS
This function/method is stateless and can be invoked off of either this module's name or an existing module object, with the same result.
new()
my $builder = Rosetta::Utility::SQLBuilder->new();
my $builder2 = $builder->new();This "getter" function/method will create and return a single Rosetta::Utility::SQLBuilder (or subclass) object. All of this object's properties are set to default values that should cause the object to generate SQL in a SQL-2003 standard conforming manner.
PROPERTY ACCESSOR METHODS
These methods are stateful and can only be invoked from this module's objects.
positional_bind_vars([ NEW_VALUE ])
my $old_val = $builder->positional_bind_vars();
$builder->positional_bind_vars( 1 );This getter/setter method returns this object's "positional bind vars" boolean property; if the optional NEW_VALUE argument is defined, this property is first set to that value. If this property is false (the default), then any SQL this object makes will include bind variable declarations in named format; eg: ":FOO" and ":BAR". If this property is true, then bind variables are declared in positional format; they will all be "?" (as the DBI module specifies), and the SQL-making method will also return an array ref with maps bind variable names to the positional "?" in the new SQL.
delimited_identifiers([ NEW_VALUE ])
my $old_val = $builder->identifier_delimiting_char();
$builder->identifier_delimiting_char( 1 );This getter/setter method returns this object's "delimited identifiers" boolean property; if the optional NEW_VALUE argument is defined, this property is first set to that value. If this property is false (the default), then this object will generate SQL identifiers (such as table or column or schema names) that are non-delimited, case-insensitive (and uppercase), and contain only a limited range of characters such as: letters, underscore, numbers (non-leading). If this property is true, then generated SQL identifiers will be delimited, case-sensitive, and able to contain any characters (including whitespace). Note that both formats are supported by the SQL standard, and many database products, though the non-delimited version is considered more "normal".
identifier_delimiting_char([ NEW_VALUE ])
my $old_val = $builder->identifier_delimiting_char();
$builder->identifier_delimiting_char( '`' );This getter/setter method returns this object's "identifier delimiting char" scalar property; if the optional NEW_VALUE argument is defined, this property is first set to that value. When the "delimited identifiers" property is true, then "identifier delimiting char" defines what character to delimit identifiers with. The double-quote (") is used by default, as it is given by example in the SQL standard and many databases such as Oracle support it; however, a commonly used alternative is the back-tick (`), such as MySQL supports. You may use any delimiter you want by setting this property to it. Note that any occurance of your chosen delimiter in the actual identifier name will be escaped in generated SQL by way of a double occurance (eg: '"' becomes '""').
get_data_type_customizations()
my $rh_old_values = $builder->get_data_type_customizations();This "getter" method returns this object's "data type customizations" family of properties in a new hash ref. The family has 46 members with more likely to be added later; see the source code for a list. Most of the members are used to map SQL::SyntaxModel qualified data types or domains to RDBMS native data types. As data types is one of the places that RDBMS products are more likely to differ from each other, the customization related to them is fine grained in SQLBuilder. The current values either match the 2003 SQL standard or are as close to it as possible; often, many members can be kept the same for use with particular database products, but often many members will also have to be changed for each product. The next 2 methods are for changing these members.
set_data_type_customizations( NEW_VALUES )
$builder->set_data_type_customizations( { 'NUM_INT_8' => 'TINYINT' } );This "setter" method lets you change one or more member of this object's "data type customizations" family of properties; you provide replacements in the NEW_VALUES hash ref argument, where the keys match the member name and the values are the new values. Invalid keys will also be added to the member list, but the SQL generating code will ignore them.
reset_default_data_type_customizations()
$builder->reset_default_data_type_customizations();This "setter" method lets you reset all of this object's "data type customizations" family of properties to their default values, such as they were when the SQLBuilder object was first created.
ora_style_seq_usage([ NEW_VALUE ])
my $old_val = $builder->ora_style_seq_usage();
$builder->ora_style_seq_usage( 1 );This getter/setter method returns this object's "ora style seq usage" boolean property; if the optional NEW_VALUE argument is defined, this property is first set to that value. If this property is false (the default), then sequence next-value expressions will have the format 'NEXT VALUE FOR seq-name'; if this property is true, they will be 'seq-name.NEXTVAL' instead, as Oracle likes.
named_subqueries([ NEW_VALUE ])
my $old_val = $builder->named_subqueries();
$builder->named_subqueries( 0 );This getter/setter method returns this object's "named subqueries" boolean property; if the optional NEW_VALUE argument is defined, this property is first set to that value. If this property is true (the default), then query expressions will be generated having a "with" clause when any sub-queries have names; if this property is false then all sub-queries will be in-lined whether they have names or not (since the database engine doesn't support "with").
SQL LEXICAL ELEMENT CONSTRUCTION METHODS
These "getter" methods each do trivial SQL construction; each one returns what amounts to a single 'token', such as a formatted identifier name or a quoted literal value. Typically these are only called by other SQL making functions. See the subsections of SQL-2003 Foundation section 5 "Lexical elements" (p131).
quote_literal( LITERAL, BASE_TYPE )
my $quoted = $builder->quote_literal( "can't you come?", 'STR_CHAR' );
# Function returns "'can''t you come?'".This method takes a literal scalar value in the argument LITERAL and returns a quoted and/or escaped version of it, according to the rules of the SSM simple data type specified in BASE_TYPE. This method is a wrapper for the other quote_*_literal( LITERAL ) methods, with BASE_TYPE determining which to call.
quote_char_string_literal( LITERAL )
my $quoted = $builder->quote_char_string_literal( "Perl" );
# Function returns "'Perl'".This method takes a literal scalar value in the argument LITERAL and returns a quoted and/or escaped version of it, as a character string.
quote_bin_string_literal( LITERAL )
my $quoted = $builder->quote_char_string_literal( "Perl" );
# Function returns "B'01010000011001010111001001101100'".This method takes a literal scalar value in the argument LITERAL and returns a quoted and/or escaped version of it, as a binary-digit string. Note that quote_literal() never calls this for binary literals, but rather 'hex'.
quote_hex_string_literal( LITERAL )
my $quoted = $builder->quote_char_string_literal( "Perl" );
# Function returns "X'5065726C'".This method takes a literal scalar value in the argument LITERAL and returns a quoted and/or escaped version of it, as a hex-digit (or hexit) string.
quote_integer_literal( LITERAL )
my $quoted = $builder->quote_integer_literal( 54 );This method takes a literal scalar value in the argument LITERAL and returns a quoted and/or escaped version of it, as an integer.
quote_numeric_literal( LITERAL )
my $quoted = $builder->quote_numeric_literal( 7.53 );This method takes a literal scalar value in the argument LITERAL and returns a quoted and/or escaped version of it, as a numeric of arbitrary scale.
quote_boolean_literal( LITERAL )
my $true = $builder->quote_boolean_literal( 1 );
my $false = $builder->quote_boolean_literal( 0 );
my $unknown = $builder->quote_boolean_literal( undef );This method takes a literal scalar value in the argument LITERAL and returns a quoted and/or escaped version of it, as a boolean value. By default the returned values are bare-words of either [TRUE, FALSE, UNKNOWN] in accordance with the SQL-2003 standard; however, if the "data type customizations" element called 'BOOL_USE_NUMS' is set to true, then [1, 0, NULL] are returned instead.
quote_identifier( NAME )
my $quoted = $builder->quote_identifier( 'my_data' );
my $quoted2 = $builder->quote_identifier( 'My Data' );This method takes a raw SQL identifier (such as a table or column name) in NAME and returns an appropriately formatted version, taking into account the current object's "delimited identifiers" and "identifier delimiting char" properties. This function only works on un-qualified identifiers; to quote a qualified identifier, pass each piece here separately, and join with "." afterwards.
SCALAR EXPRESSION SQL CONSTRUCTION METHODS
These "getter" methods build SQL expressions and correspond to the subsections of SQL-2003 Foundation section 6 "Scalar expressions" (p161).
build_expr_data_type_spec( DOMAIN_NODE )
my $sql = $builder->build_expr_data_type_spec( $domain_node );This method takes a 'domain' SSM Node and builds a corresponding SQL fragment such as would be used in the "data type" reference of a table column definition. Example return values are "VARCHAR(40)", "DECIMAL(7,2)", "BOOLEAN" "INTEGER UNSIGNED". Most of the "data type customizations" property elements are used to customize this method. See SQL-2003 6.1 "<data type>".
build_expr_identifier_element( OBJECT_NODE )
my $quoted = $builder->build_expr_identifier_element( $object_node );This method takes a SQL::SyntaxModel::Node object in OBJECT_NODE, extracts its 'name' attribute, and returns that after passing it to quote_identifier(). The result is an "unqualified identifier". Note that SQL::SyntaxModel will throw an exception if the Node is of the wrong type.
build_expr_identifier_chain( OBJECT_NODE )
my $quoted = $builder->build_expr_identifier_chain( $object_node );This method is like build_expr_identifier_element() except that it will also trace all of the relevant parent Nodes of the given OBJECT_NODE, extracting and quoting their 'name' also. Then all of the quoted 'name' are stitched together with ".", as with the SQL standard, with the combined string returned. The result is a "qualified identifier". For example, passing a 'table' node will usually return 'schema_name.table_name'. See SQL-2003 6.6 "<identifier chain>".
QUERY EXPRESSION SQL CONSTRUCTION METHODS
These "getter" methods build SQL expressions and correspond to the subsections of SQL-2003 Foundation section 7 "Query expressions" (p293).
SCHEMA DEFINITION SQL CONSTRUCTION METHODS
These "getter" methods build SQL strings or fragments thereof that are used mainly when declaring or defining (or removing) database schema constructs.
build_schema_sequence_create( SEQUENCE_NODE )
my $sql = $builder->build_schema_sequence_create( $sequence_node );This method takes a 'sequence' SSM Node and builds a corresponding "CREATE SEQUENCE" DDL SQL statement, which it returns.
build_schema_sequence_delete( SEQUENCE_NODE )
my $sql = $builder->build_schema_sequence_delete( $sequence_node );This method takes a 'sequence' SSM Node and builds a corresponding "DROP SEQUENCE" DDL SQL statement, which it returns.
build_schema_table_create( TABLE_NODE )
my $sql = $builder->build_schema_table_create( $table_node );This method takes a 'table' SSM Node and builds a corresponding "CREATE TABLE" DDL SQL statement, which it returns.
Incorporates 6.2 "<field definition>".
build_schema_table_delete( TABLE_NODE )
my $sql = $builder->build_schema_sequence_delete( $table_node );This method takes a 'table' SSM Node and builds a corresponding "DROP TABLE" DDL SQL statement, which it returns.
build_schema_view_create( VIEW_NODE )
my $sql = $builder->build_schema_view_create( $view_node );This method takes a 'view' SSM Node and builds a corresponding "CREATE VIEW" DDL SQL statement, which it returns.
build_schema_view_delete( VIEW_NODE )
my $sql = $builder->build_schema_view_delete( $view_node );This method takes a 'view' SSM Node and builds a corresponding "DROP VIEW" DDL SQL statement, which it returns.
build_schema_routine_create( ROUTINE_NODE )
my $sql = $builder->build_schema_routine_create( $routine_node );This method takes a 'routine' SSM Node and builds a corresponding "CREATE ROUTINE/PROCEDURE/FUNCTION" DDL SQL statement, which it returns.
build_schema_routine_delete( ROUTINE_NODE )
my $sql = $builder->build_schema_routine_delete( $routine_node );This method takes a 'routine' SSM Node and builds a corresponding "DROP ROUTINE/PROCEDURE/FUNCTION" DDL SQL statement, which it returns.
SQL CONSTRUCTION METHODS
This documentation isn't written yet. Meanwhile, look at the source code for this module. The property getters/setters appear first, and the SQL building methods appear below them. Also see source of Rosetta::Engine::Generic, which uses this module.
UTILITY METHODS
substitute_macros( STR, SUBS )
my $result = $builder->substitute_macros( 'NUMBER({p},{s})', { 'p' => 7, 's' => 2 } )This method takes a string in STR which contains brace-delimited tokens and returns a version of that string having the tokens replaced by corresponding values provided in the hash ref SUBS. This method is used mainly by build_expr_data_type_spec() at the moment.
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.