NAME
Rosetta::Utility::SQLBuilder - Generate ANSI/ISO SQL:2003 and other SQL variants
VERSION
This document describes Rosetta::Utility::SQLBuilder version 0.22.0.
SYNOPSIS
The previous SYNOPSIS was removed; a new one will be written later.
DESCRIPTION
This module is a reference implementation of fundamental Rosetta::Model features.
The Rosetta::Utility::SQLBuilder Perl 5 module is a functional but quickly built Rosetta::Model utility class that converts a set of related Rosetta::Model 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() implementation methods. This class can also be used by code on the application-side of a Rosetta::Interface tree (such as Rosetta::Emulator::DBI); 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 host parameters / bind variables (using a '?' for each instance); since DBI's arguments are positional and Rosetta::Model'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: SIGNIFICANT PORTIONS OF THIS MODULE ARE NOT WRITTEN YET. MOREOVER, MOST PARTS OF THIS MODULE HAVE NOT BEEN EXECUTED YET AND THEY PROBABLY CONTAIN MANY ERRORS. ALL THAT IS KNOWN FOR SURE WITH THOSE PARTS IS THAT THEY COMPILE.
CONSTRUCTOR FUNCTIONS
This function 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.
STATIC CONFIGURATION PROPERTY ACCESSOR METHODS
These methods are stateful and can only be invoked from this module's objects. This set of properties are generally set once at the start of a SQLBuilder object's life and aren't changed later, since they are generally static configuration data.
positional_host_params([ NEW_VALUE ])
my $old_val = $builder->positional_host_params();
$builder->positional_host_params( 1 );This getter/setter method returns this object's "positional host params" 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 host parameter declarations in named format; eg: ":FOO" and ":BAR". If this property is true, then host parameters 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 host parameter names to the positional "?" in the new SQL. This property simply indicates the database engine's capability; it does not say "act now". The "positional host param map array" property can be set regardless of the engine's capability, but SQLBuilder code will only do something with it if "positional host params" is true.
identifier_style([ NEW_VALUE ])
my $old_val = $builder->identifier_style();
$builder->identifier_style( 'YD_CS' );
$builder->identifier_style( 'ND_CI_UP' );
$builder->identifier_style( 'ND_CI_DN' );This getter/setter method returns this object's "identifier style" string property; if the optional NEW_VALUE argument is defined, this property is first set to that value. If this property is 'YD_CS' (the default), then this object will generate SQL identifiers (such as table or column or schema names) that are delimited, case-sensitive, and able to contain any characters (including whitespace). If this property is 'ND_CI_UP', then generated SQL identifiers will be non-delimited, case-insensitive, with latin characters folded to uppercase, and contain only a limited range of characters such as: letters, underscore, numbers (non-leading); these are "bare-word" identifiers. The 'ND_CI_DN' style is the same as 'ND_CI_UP' except that the identifier is folded to lowercase. Note that all of these formats are supported by the SQL standard but that the standard specifies all non-delimited identifiers will match as uppercase when compared to delimited identifiers.
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 "identifier style" property is 'YD_CS', 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 Rosetta::Model 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.
ora_style_routines([ NEW_VALUE ])
my $old_val = $builder->ora_style_routines();
$builder->ora_style_routines( 1 );This getter/setter method returns this object's "ora style routines" 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 generated routine layouts will follow the SQL:2003 standard, meaning that local variables are declared inside BEGIN/END blocks using 'DECLARE var-name ...', and value assignments take the form 'SET dest-var = src-expr'. If this property is true, then routine layouts will follow the Oracle 8 style where variable declarations are above BEGIN/END blocks and assignments are of the form 'dest-var := src-expr'.
inlined_subqueries([ NEW_VALUE ])
my $old_val = $builder->inlined_subqueries();
$builder->inlined_subqueries( 1 );This getter/setter method returns this object's "inlined subqueries" 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 query expressions will be generated having a "with" clause when any sub-queries have names; if this property is true then all sub-queries will be in-lined whether they have names or not (since the database engine doesn't support "with").
inlined_domains([ NEW_VALUE ])
my $old_val = $builder->inlined_domains();
$builder->inlined_domains( 1 );This getter/setter method returns this object's "inlined domains" 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 data container declarations like table columns will refer to named domain schema objects as their data type; if this property is true then data type definitions will always be inlined such as for table column declarations (since the database engine doesn't support named domains).
flatten_to_single_schema([ NEW_VALUE ])
my $old_val = $builder->flatten_to_single_schema();
$builder->flatten_to_single_schema( 1 );This getter/setter method returns this object's "flatten to single schema" 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 we assume that the database engine in use supports multiple named schemas in a single catalog, and we are using them; if this property is true then we assume the database only supports a single schema per catalog, or the current user is only allowed to use a single schema (effectively the same problem), so we will flatten all of our schema objects into a single namespace where each object name includes the schema name prefixed to it, then emulating multiple schemas.
single_schema_join_chars([ NEW_VALUE ])
my $old_val = $builder->single_schema_join_chars();
$builder->single_schema_join_chars( '___' );This getter/setter method returns this object's "single schema join chars" scalar property; if the optional NEW_VALUE argument is defined, this property is first set to that value. When the "flatten to single schema" property is true, then "single schema join chars" defines what short character string to concatenate the ROS M schema name and schema object name with when flattening them from two levels to one. The default value of this property is '__' (a double underscore). It should have a value that is guaranteed to never appear in either the schema names or schema object names being joined, so that reverse-engineering such a flattened schema into a ROS M can be trivial.
emulate_subqueries([ NEW_VALUE ])
my $old_val = $builder->emulate_subqueries();
$builder->emulate_subqueries( 1 );This getter/setter method returns this object's "emulate subqueries" 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 we assume that the database engine in use supports sub-queries of some kind, either named or inlined, and we are using them; if this property is true then we assume the database does not support subqueries at all, and so we will need to emulate them using multiple simple or joining queries as well as temporary tables to hold intermediate values. Note that the emulator will only support static sub-queries for the near future, meaning those that don't take any view_args, and that are evaluated exactly once prior to the invoking query. For that matter, some database engines only have native support for static sub-queries also, such as SQLite 2.8.13.
emulate_compound_queries([ NEW_VALUE ])
my $old_val = $builder->emulate_compound_queries();
$builder->emulate_compound_queries( 1 );This getter/setter method returns this object's "emulate compound queries" 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 we assume that the database engine in use supports the various compound queries (union, intersect, except), and we are using them; if this property is true then we assume the database does not support compound queries at all, and so we will need to emulate them using multiple simple or joining queries as well as temporary tables to hold intermediate values.
emulated_query_temp_table_join_chars([ NEW_VALUE ])
my $old_val = $builder->emulated_query_temp_table_join_chars();
$builder->emulated_query_temp_table_join_chars( '___' );This getter/setter method returns this object's "emulated query temp table join chars" scalar property; if the optional NEW_VALUE argument is defined, this property is first set to that value. When either or both of the "emulate subqueries" or "emulate compound queries" properties are true, then "emulated query temp table join chars" defines what short character string to concatenate the parts of the names of each temporary table used by the emulation to hold intermediate values. Given that "inner views" used in subquery or compound query definitions are declared inside the main view and/or each other, each temporary table name is the concatenation of the names of the inner view being invoked and each of its parent views, parent-most on the left. The default value of this property is '__' (a double underscore). So, for example, a parent view named 'foo' having an inner view named 'bar' will produce a temporary table named 'foo__bar' when an emulation happens.
DYNAMIC STATE MAINTENANCE PROPERTY ACCESSOR METHODS
These methods are stateful and can only be invoked from this module's objects. Each of these contains either very short term configuration options (meant to have the life of about one external build* method call) that are only set externally as usual, or some may also be set or changed by SQLBuilder code, and can be used effectively as extra output from the build* method; they maintain state for a build* invocation.
make_host_params([ NEW_VALUE ])
my $old_val = $builder->make_host_params();
$builder->make_host_params( 1 );This getter/setter method returns this object's "make host params" boolean property; if the optional NEW_VALUE argument is defined, this property is first set to that value. This property helps manage the fact that routine_arg ROS M Nodes can have dual purposes when being converted to SQL. With an ordinary stored routine, they turn into normal argument declarations and are used by SQL routine code by name as usual. With an application-side routine, or BLOCKs inside those, they instead represent application host parameters, which are formatted differently when put in SQL. This property stores the current state as to whether any referenced routine_arg should be turned into host params or not. This property should be set false (the default) when we are in an ordinary routine, and it should be set true when we are in an application-side routine.
get_positional_host_param_map_array()
This "getter" method returns a new array ref having a copy of this object's "positional host param map array" array property. This property is explicitely emptied by external code, by invoking the clear_positional_host_param_map_array() method, prior to that code requesting that we build SQL which contains positional host parameters. When we build said SQL, each time we are to insert a host parameter, we simply put a "?" in the SQL, and we add to this array the name of the routine_arg whose value is supposed to substitute at exec time. As soon as said SQL is made and returned, external code reads this array's values using the get_positional_host_param_map_array() method.
clear_positional_host_param_map_array()
This "setter" method empties this object's "positional host param map array" array property. See the previous method's documentation for when to use it.
unwrap_views([ NEW_VALUE ])
my $old_val = $builder->unwrap_views();
$builder->unwrap_views( 1 );This getter/setter method returns this object's "unwrap views" boolean property; if the optional NEW_VALUE argument is defined, this property is first set to that value. This property helps manage the fact that when we are making INSERT or UPDATE or DELETE statements, these operate on a single table or named view by its original (not correlation) name. The outer view that stores the details of the I|U|D is "unwrapped". This property stores the current state as to whether any view_src_field should be referenced by their original names or not; false (the default) means to use the correlation name, and true means to use the original. External code which is working with I|U|D statements, or several functions in this module, would set this true before calling this module's generic SQL making functions, and then set it false just afterwards.
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 ROS M 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.
build_identifier_element( OBJECT_NODE )
my $sql = $builder->build_identifier_element( $object_node );This method takes a Rosetta::Model::Node object in OBJECT_NODE, extracts its 'si_name' attribute, and returns that after passing it to quote_identifier(). The result is an "unqualified identifier". This is the most widely used, at least internally, build_identifier_*() method; for example, it is used for table/view column names in table/view definitions, and for all declaration or use of routine variables, or for routine/view arguments. Note that Rosetta::Model will throw an exception if the Node argument is of the wrong type.
build_identifier_host_parameter_name( ROUTINE_ARG_NODE )
my $sql = $builder->build_identifier_host_parameter_name( $routine_arg_node );This method takes a routine_arg ROS M Node and generates either a named or positional SQL identifier (depending on this object's "positional host params" property) based on the "name" of the routine_arg. This function is used for application-side or application-invoked ROS M routines. Named host params look according to the SQL:2003 standard, like ":foo", and positional host params follow the DBI-style '?' (and also SQL:2003 standard, apparently). When making a positional parameter, this method adds an element to the 'map array' property, so it can be externally mapped to a named value later.
build_identifier_schema_or_app_obj( OBJECT_NODE[, FOR_DEFN] )
my $sql = $builder->build_identifier_schema_or_app_obj( $object_node );
my $sql = $builder->build_identifier_schema_or_app_obj( $object_node, 1 );This method is like build_identifier_element() except that it will generate a "qualified identifier", by following parent Nodes of the given Node, and that it is specifically for either a permanent schema object (domain, sequence generator, table, view, routine) name or a temporary application/connection-specific object; which of those two forms gets generated is determined soley by whether OBJECT_NODE's parent is a 'schema' or 'application'. For example, passing a 'table' Node under a 'schema' will usually return '<schema_name>.<table_name>', or '<schema_name>__<table_name>' if the "flatten to single schema" property is true. When under an 'application' Node, the special SQL:2003 prefix "MODULE." is used in place of a schema name. This method is used mostly when referencing an existing schema object, such as in a query's FROM clause, or a table's foreign key constraint definition, or a trigger's ON declaration. See SQL:2003 6.6 "<identifier chain>". If the optional boolean argument FOR_DEFN is true, then it will create a (possibly identical) variant of the object name that is to be used in statements that create or drop the same object; this is in case there is a requirement for names to be a different format on definition.
build_identifier_view_src_field( VIEW_SRC_FIELD_NODE )
my $sql = $builder->build_identifier_view_src_field( $view_src_field_node );This method makes an identifier chain that is used within a query/view expression to refer to a source table/view column that we are taking the value of. Assuming that all view sources have local correlation names, the identifier chain will usually look like '<correlation name>.<original table/view column name>'. (As an exception, this method will output just the unqualified column name when this object's "unwrap views" property is true, as that format works best in WHERE/etc clauses of INSERT|UPDATE|DELETE.)
build_identifier_temp_table_for_emul( INNER_VIEW_NODE )
my $sql = $builder->build_identifier_temp_table_for_emul( $inner_view_node );This method makes an identifier for a temporary table that is intended to be used by code that is emulating sub-queries and/or compound queries, such that the table would hold intermediate values. See the emulated_query_temp_table_join_chars() method documentation for more details on what the new identifier name looks like.
SCALAR EXPRESSION AND PREDICATE SQL CONSTRUCTION METHODS
These "getter" methods build SQL expressions and correspond to the subsections of SQL:2003 Foundation section 6 "Scalar expressions" (p161) and section 8 "Predicates" (p373).
build_expr( EXPR_NODE )
my $sql = $builder->build_expr( $expr_node );This method takes any kind of "expression" Node ("view_expr" or "routine_expr") and builds the corresponding SQL fragment. Sometimes this method is simply a wrapper for other build_expr_*() methods, which are called for specific 'expr_type' values, but other times this method does the work by itself.
build_expr_scalar_data_type_defn( SCALAR_DATA_TYPE_NODE )
my $sql = $builder->build_expr_scalar_data_type_defn( $scalar_data_type_node );This method takes a 'scalar_data_type' ROS M 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>" (p161).
build_expr_row_data_type_defn( ROW_DATA_TYPE_NODE )
my $sql = $builder->build_expr_row_data_type_defn( $row_data_type_node );This method takes a 'row_data_type' ROS M Node and builds a corresponding SQL fragment such as would be used in the "data type" reference of a routine variable definition. See SQL:2003 6.1 "<data type>" (p161) and SQL:2003, 6.2 "<field definition>" (p173).
build_expr_scalar_data_type_or_domain_name( SCALAR_DT_OR_DOM_NODE )
my $data_type_sql = $builder->build_expr_scalar_data_type_or_domain_name( $data_type_node );
my $domain_sql = $builder->build_expr_scalar_data_type_or_domain_name( $domain_node );This method takes a 'scalar_data_type' or 'scalar_domain' ROS M Node and returns one of two kinds of SQL fragments, depending partly on whether or not the current database engine supports "domain" schema objects (and we are using them). If it does then this method returns the identifier of the domain schema object to refer to, if the argument was a 'scalar_domain' Node; if it does not, or the argument is a 'scalar_data_type' Node, then this method instead returns the data type definition to use. See SQL:2003, 11.4 "<column definition>" (p536).
build_expr_row_data_type_or_domain_name( SCALAR_DT_OR_DOM_NODE )
my $data_type_sql = $builder->build_expr_row_data_type_or_domain_name( $data_type_node );
my $domain_sql = $builder->build_expr_row_data_type_or_domain_name( $domain_node );This method takes a 'row_data_type' or 'row_domain' ROS M Node and returns one of two kinds of SQL fragments, depending partly on whether or not the current database engine supports "domain" schema objects (and we are using them). If it does then this method returns the identifier of the domain schema object to refer to, if the argument was a 'row_domain' Node; if it does not, or the argument is a 'row_data_type' Node, then this method instead returns the data type definition to use. See SQL:2003, 11.4 "<column definition>" (p536).
build_expr_cast_spec( EXPR_NODE )
my $sql = $builder->build_expr_cast_spec( $expr_node );This method takes an "*_expr" Node whose 'expr_type' is 'CAST' and generates the corresponding "CAST( <operand> AS <target> )" SQL fragment, if the database engine supports the syntax; it generates alternative casting expressions otherwise, such as Oracle 8's "TO_*(...)" functions. See SQL:2003, 6.12 "<cast specification>" (p201).
build_expr_seq_next( SEQUENCE_NODE )
my $sql = $builder->build_expr_seq_next( $sequence_node );This method takes a "sequence" Node and generates the appropriate SQL expression for reading the next value from the corresponding schema object. See SQL:2003, 6.13 "<next value expression>" (p217).
build_expr_call_sroutine( EXPR_NODE )
my $sql = $builder->build_expr_call_sroutine( $expr_node );This method takes an "*_expr" Node whose 'expr_type' is 'ROSMN' and generates the corresponding "built-in function" call, which includes basic predicates (comparison or assertion), math operations, string operations, logic gates and switches, aggregate functions, and olap functions. Child "*_expr" Nodes provide the argument values to give said "built-in function), if there are any.
build_expr_call_uroutine( EXPR_NODE )
my $sql = $builder->build_expr_call_uroutine( $expr_node );This method takes an "*_expr" Node whose 'expr_type' is 'URTN' and generates a call to a named FUNCTION routine schema object. Child "*_expr" Nodes provide the argument values to give said FUNCTION, if there are any.
QUERY EXPRESSION SQL CONSTRUCTION METHODS
These "getter" methods build SQL query expressions and correspond to the subsections of SQL:2003 Foundation section 7 "Query expressions" (p293).
build_query_table_expr( VIEW_NODE )
my $sql = $builder->build_query_table_expr( $view_node );This method takes a "view" Node and generates the main body of a query, namely the concatenation of these 5 clauses in order: FROM, WHERE, GROUP BY, HAVING, and the window clause. All of these clauses are optional in a query (except FROM in most cases); whether or not they are generated is determined by whether the given VIEW_NODE comes with definitions for them. This method is a shim that calls 5 separate build_query_*_clause() methods which do the actual work, and concatenates the results. See SQL:2003, 7.4 "<table expression>" (p300).
build_query_from_clause( VIEW_NODE )
my $sql = $builder->build_query_from_clause( $view_node );This method takes a "view" Node and generates the FROM clause in the corresponding query, if the query has a FROM clause (most do); it returns the empty string otherwise. The query has a FROM clause if it has at least one child "view_src" Node; if it has multiple "view_src", then they are all joined together based on this view's child "view_join" Nodes, to form a single "joined table". Each "view_src" can be either a table or view schema object, or a call to a named subquery (with optional arguments), or an embedded anonymous subquery; it is rendered into SQL, by the build_query_table_factor() method, with a unique "correlation name" (expression AS name) that every other part of this view's query references it with. Note that this method should never be invoked on "COMPOUND" views. See SQL:2003, 7.5 "<from clause>" (p301) and SQL:2003, 7.6 "<table reference>" (p303) and SQL:2003, 7.7 "<joined table>" (p312).
build_query_table_factor( VIEW_SRC_NODE )
my $sql = $builder->build_query_table_factor( $view_src_node );This method is invoked by build_query_from_clause() for each "table factor" that needs to be generated in a FROM clause; see that method for context information. See SQL:2003, 7.6 "<table reference>" (p303).
build_query_where_clause( VIEW_NODE )
my $sql = $builder->build_query_where_clause( $view_node );This method takes a "view" Node and generates the WHERE clause in the corresponding query, if the query has a WHERE clause; it returns the empty string otherwise. See SQL:2003, 7.8 "<where clause>" (p319).
build_query_group_clause( VIEW_NODE )
my $sql = $builder->build_query_group_clause( $view_node );This method takes a "view" Node and generates the GROUP BY clause in the corresponding query, if the query has a GROUP BY clause; it returns the empty string otherwise. See SQL:2003, 7.9 "<group by clause>" (p320).
build_query_having_clause( VIEW_NODE )
my $sql = $builder->build_query_having_clause( $view_node );This method takes a "view" Node and generates the HAVING clause in the corresponding query, if the query has a HAVING clause; it returns the empty string otherwise. See SQL:2003, 7.10 "<having clause>" (p329).
build_query_window_clause( VIEW_NODE )
my $sql = $builder->build_query_window_clause( $view_node );This method takes a "view" Node and generates the window clause in the corresponding query, if the query has a window clause; it returns the empty string otherwise. The window clause includes things like "ORDER BY", "LIMIT", "OFFSET". See SQL:2003, 7.11 "<window clause>" (p331).
build_query_query_spec( VIEW_NODE[, INTO_DEST_NODE] )
my $sql = $builder->build_query_query_spec( $view_node );
my $sql2 = $builder->build_query_query_spec( $view_node, $rtn_var_node );This method takes a "view" Node and generates the main body of a query plus a SELECT list. The output looks like "SELECT <set quantifier> <select list> <into clause> <table expression>", where "<set quantifier>" is [DISTINCT|ALL], and the other three parts are built respectively by: build_query_select_list(), internally, build_query_table_expr(). This method should not be called for a COMPOUND view. The <into clause> is only made if the Node ref argument INTO_DEST_NODE is set; that is only intended to happen for root SELECT statements inside routines. See SQL:2003, 7.12 "<query specification>" (p341) and SQL:2003, 14.5 "<select statement: single row>" (p824).
build_query_select_list( VIEW_NODE )
my $sql = $builder->build_query_select_list( $view_node );This method takes a "view" Node and generates the "select list" portion of the corresponding query, which defines the output columns of the query. This method returns a comma-delimited list expression where each list item is a "<derived column> ::= <value expression> AS <column name>". See SQL:2003, 7.12 "<query specification>" (p341).
build_query_query_expr( VIEW_NODE )
my $sql = $builder->build_query_query_expr( $view_node );This method takes a "view" Node and outputs the definitions of the named subqueries for the current query, if the database engine supports named subqueries (and we are using them), plus the definition of the main query itself, which may be a compound query. The output may look like "WITH ... SELECT ...". See SQL:2003, 7.13 "<query expression>" (p351).
build_query_query_expr_body( VIEW_NODE )
my $sql = $builder->build_query_query_expr_body( $view_node );This method takes a "view" Node and produces the SQL for a compound query where this view's child views are the queries being compounded together, if the current view is a COMPOUND view. If the current view is not a COMPOUND view, then this method is simply a shim for build_query_query_spec(). See SQL:2003, 7.13 "<query expression>" (p351).
build_query_subquery( EXPR_NODE )
my $sql = $builder->build_query_subquery( $expr_node );This method takes a "view_expr" Node whose 'expr_type' is 'CVIEW' and generates either a call to a named subquery, or inlines the definition of an anonymous subquery, depending on what the database engine supports (and we are using). View schema or table objects are not invoked directly here, but can be indirectly via a subquery. Child "*_expr" Nodes provide the argument values to give said subquery, if it takes arguments. See SQL:2003, 7.15 "<subquery>" (p370).
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. They correspond to the subsections of SQL:2003 Foundation section 11 "Schema definition and manipulation" (p519).
build_schema_create( SCHEMA_NODE )
my $sql = $builder->build_schema_create( $schema_node );This method takes a 'schema' ROS M Node and builds a corresponding "CREATE SCHEMA" DDL SQL statement, which it returns. See SQL:2003, 11.1 "<schema definition>" (p519).
build_schema_delete( SCHEMA_NODE )
my $sql = $builder->build_schema_delete( $schema_node );This method takes a 'schema' ROS M Node and builds a corresponding "DROP SCHEMA" DDL SQL statement, which it returns. See SQL:2003, 11.2 "<drop schema statement>" (p522).
build_schema_or_app_scalar_domain_create( DOMAIN_NODE )
my $sql = $builder->build_schema_or_app_scalar_domain_create( $domain_node );This method takes a 'scalar domain' ROS M Node and builds a corresponding "CREATE DOMAIN" DDL SQL statement, which it returns. See SQL:2003, 11.24 "<domain definition>" (p603).
build_schema_or_app_scalar_domain_delete( DOMAIN_NODE )
my $sql = $builder->build_schema_or_app_scalar_domain_delete( $domain_node );This method takes a 'scalar domain' ROS M Node and builds a corresponding "DROP DOMAIN" DDL SQL statement, which it returns. See SQL:2003, 11.30 "<drop domain statement>" (p610).
build_schema_or_app_row_domain_create( DOMAIN_NODE )
my $sql = $builder->build_schema_or_app_row_domain_create( $domain_node );This method takes a 'domain' ROS M Node and builds a corresponding "CREATE DOMAIN" DDL SQL statement, which it returns. See SQL:2003, 11.24 "<domain definition>" (p603).
build_schema_or_app_row_domain_delete( DOMAIN_NODE )
my $sql = $builder->build_schema_or_app_row_domain_delete( $domain_node );This method takes a 'row domain' ROS M Node and builds a corresponding "DROP DOMAIN" DDL SQL statement, which it returns. See SQL:2003, 11.30 "<drop domain statement>" (p610).
build_schema_or_app_sequence_create( SEQUENCE_NODE )
my $sql = $builder->build_schema_or_app_sequence_create( $sequence_node );This method takes a 'sequence' ROS M Node and builds a corresponding "CREATE SEQUENCE" DDL SQL statement, which it returns. See SQL:2003, 11.62 "<sequence generator definition>" (p726).
build_schema_or_app_sequence_delete( SEQUENCE_NODE )
my $sql = $builder->build_schema_or_app_sequence_delete( $sequence_node );This method takes a 'sequence' ROS M Node and builds a corresponding "DROP SEQUENCE" DDL SQL statement, which it returns. See SQL:2003, 11.64 "<drop sequence generator statement>" (p729).
build_schema_or_app_table_create( TABLE_NODE )
my $sql = $builder->build_schema_or_app_table_create( $table_node );This method takes a 'table' ROS M Node and builds a corresponding "CREATE TABLE" DDL SQL statement, which it returns. See SQL:2003, 6.2 "<field definition>" (p173) and SQL:2003, 11.3 "<table definition>" (p525) and SQL:2003, 11.4 "<column definition>" (p536) and SQL:2003, 11.5 "<default clause>" (p541) and SQL:2003, 11.6 "<table constraint definition>" (p545) and SQL:2003, 11.7 "<unique constraint definition>" (p547) and SQL:2003, 11.8 "<referential constraint definition>" (p549) and SQL:2003, 11.9 "<check constraint definition>" (p569).
build_schema_or_app_table_delete( TABLE_NODE )
my $sql = $builder->build_schema_or_app_table_delete( $table_node );This method takes a 'table' ROS M Node and builds a corresponding "DROP TABLE" DDL SQL statement, which it returns. See SQL:2003, 11.21 "<drop table statement>" (p587).
build_schema_or_app_view_create( VIEW_NODE )
my $sql = $builder->build_schema_or_app_view_create( $view_node );This method takes a 'view' ROS M Node and builds a corresponding "CREATE VIEW" DDL SQL statement, which it returns. See SQL:2003, 11.22 "<view definition>" (p590).
build_schema_or_app_view_delete( VIEW_NODE )
my $sql = $builder->build_schema_or_app_view_delete( $view_node );This method takes a 'view' ROS M Node and builds a corresponding "DROP VIEW" DDL SQL statement, which it returns. See SQL:2003, 11.23 "<drop view statement>" (p600).
build_schema_or_app_routine_create( ROUTINE_NODE )
my $sql = $builder->build_schema_or_app_routine_create( $routine_node );This method takes a 'routine' ROS M Node and builds a corresponding "CREATE ROUTINE/PROCEDURE/FUNCTION" DDL SQL statement, which it returns. See SQL:2003, 11.39 "<trigger definition>" (p629) and SQL:2003, 11.50 "<SQL-invoked routine>" (p675).
build_schema_or_app_routine_delete( ROUTINE_NODE )
my $sql = $builder->build_schema_or_app_routine_delete( $routine_node );This method takes a 'routine' ROS M Node and builds a corresponding "DROP ROUTINE/PROCEDURE/FUNCTION" DDL SQL statement, which it returns. See SQL:2003, 11.40 "<drop trigger statement>" (p633) and SQL:2003, 11.52 "<drop routine statement>" (p703).
ACCESS CONTROL SQL CONSTRUCTION METHODS
These "getter" methods build SQL statements that are used mainly when declaring users or roles and their permissions. They correspond to the subsections of SQL:2003 Foundation section 12 "Access control" (p731). Note that Rosetta::Model assigns privileges to roles, and roles to users; privileges are not assigned to users directly.
build_access_role_create( ROLE_NODE )
my $sql = $builder->build_access_role_create( $role_node );This method takes a 'role' ROS M Node and builds a corresponding "CREATE ROLE" SQL statement, which it returns. See SQL:2003, 12.4 "<role definition>" (p743).
build_access_role_delete( ROLE_NODE )
my $sql = $builder->build_access_role_delete( $role_node );This method takes a 'role' ROS M Node and builds a corresponding "DROP ROLE" SQL statement, which it returns. See SQL:2003, 12.6 "<drop role statement>" (p746).
build_access_grant( GRANTEE_NODE )
my $sql = $builder->build_access_grant( $grantee_node );This method takes a "grantee" ("role" or "user") Node and builds a list of "GRANT ... TO ..." SQL statements, which it returns as a string. If the grantee is a 'role', then grant statements for all of the privileges assigned to that role are created. If the grantee is a 'user', then grant statements for all the roles assigned to that user are created. This method returns an empty string if the grantee has no privileges or roles. See SQL:2003, 12.1 "<grant statement>" (p731) and SQL:2003, 12.2 "<grant privilege statement>" (p736) and SQL:2003, 12.3 "<privileges>" (p739) and SQL:2003, 12.5 "<grant role statement>" (p744).
build_access_revoke( GRANTEE_NODE )
my $sql = $builder->build_access_revoke( $grantee_node );This method takes a "grantee" ("role" or "user") Node and builds a list of "REVOKE ... FROM ..." SQL statements, which it returns as a string. If the grantee is a 'role', then revoke statements for all of the privileges assigned to that role are created. If the grantee is a 'user', then revoke statements for all the roles assigned to that user are created. This method returns an empty string if the grantee has no privileges or roles. See SQL:2003, 12.7 "<revoke statement>" (p747) and SQL:2003, 12.3 "<privileges>" (p739).
DATA MANIPULATION SQL CONSTRUCTION METHODS
These "getter" methods build SQL statements that are used mainly with cursors or routine statements, or when manipulating data, such as insert/update/delete commands. They correspond to the subsections of SQL:2003 Foundation sections: 14 "Data manipulation" (p809), 15 "Control statements", plus part of "SQL-client modules" (p765).
build_dmanip_routine_args( ROUTINE_NODE )
my $sql = $builder->build_dmanip_routine_args( $routine_node );This method takes a 'routine' ROS M Node and constructs the argument list declaration for it, returning that as a string (or the empty string if there are no arguments). The string includes bounding parenthesis. See SQL:2003, 11.50 "<SQL-invoked routine>" (p675), particularly "<SQL parameter declaration list>".
build_dmanip_routine_body( ROUTINE_NODE[, IS_ATOMIC] )
my $sql = $builder->build_dmanip_routine_body( $routine_node, 1 );This method takes a 'routine' ROS M Node and constructs the main body SQL of that routine, which is the BEGIN...END compound statement, all the contained routine statements, and the variable declarations; these are all returned as a string. This method does not construct a method name or argument list. It is suitable for both named/stored routines and anonymous/application routines. If the optional boolean argument IS_ATOMIC is true, then "BEGIN ATOMIC" is generated instead of "BEGIN"; it is used for trigger bodies. See SQL:2003, 14.1 "<declare cursor>" (p809) plus other relevant sections of SQL:2003.
build_dmanip_routine_stmt( STMT_NODE )
my $sql = $builder->build_dmanip_routine_stmt( $stmt_node );This method takes a "routine_stmt" Node and builds the corresponding SQL statement. Sometimes this method is simply a wrapper for other build_dmanip_*() methods, which are called for specific 'stmt_type' values, but other times this method does the work by itself. See SQL:2003, 13.5 "<SQL procedure statement>" (p790) and SQL:2003, 15.2 "<return statement>" (p886).
build_dmanip_call_sroutine( STMT_NODE )
my $sql = $builder->build_dmanip_call_sroutine( $stmt_node );This method takes a "routine_stmt" Node whose 'stmt_type' is 'ROSMN' and generates the corresponding "built-in procedure" call, which includes creation and use of cursors, selects, inserts, updates, deletes, commit, rollback, etc. Child "*_expr" Nodes provide the argument values to give said "built-in function), if there are any. See SQL:2003, 14.2 "<open statement>" (p815) and SQL:2003, 14.3 "<fetch statement>" (p817) and SQL:2003, 14.4 "<close statement>" (p822).
build_dmanip_src_schema_object_name( VIEW_NODE )
my $sql = $builder->build_dmanip_src_schema_object_name( $view_node );This method takes a "view" Node and returns the schema-qualified name of its single source, if it has a single source and that is a schema object (table or a named view); it returns the undefined value otherwise. This function is used by the methods which generate INSERT|UPDATE|DELETE statements.
build_dmanip_insert_stmt( VIEW_NODE )
my $sql = $builder->build_dmanip_insert_stmt( $view_node );This method takes a "view" Node and returns the corresponding INSERT SQL statement, assuming the view has details for one. See SQL:2003, 7.3 "<table value constructor>" (p298) and SQL:2003, 14.8 "<insert statement>" (p834).
build_dmanip_update_stmt( VIEW_NODE )
my $sql = $builder->build_dmanip_update_stmt( $view_node );This method takes a "view" Node and returns the corresponding UPDATE SQL statement, assuming the view has details for one. See SQL:2003, 14.11 "<update statement: searched>" (p849) and SQL:2003, 14.12 "<set clause list>" (p853).
build_dmanip_delete_stmt( VIEW_NODE )
my $sql = $builder->build_dmanip_delete_stmt( $view_node );This method takes a "view" Node and returns the corresponding DELETE SQL statement, assuming the view has details for one. See SQL:2003, 14.7 "<delete statement: searched>" (p831).
build_dmanip_call_uroutine( STMT_NODE )
my $sql = $builder->build_dmanip_call_uroutine( $stmt_node );This method takes a "routine_stmt" Node whose 'stmt_type' is 'URTN' and generates a call to a named PROCEDURE routine schema object. Child "*_expr" Nodes provide the argument values to give said PROCEDURE, if there are any. See SQL:2003, 15.1 "<call statement>" (p885).
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_scalar_data_type_defn() at the moment.
find_scalar_domain_for_row_domain_field( SCALAR_DT_NODE, ROW_DOM_NODE )
This method takes a 'scalar_data_type' ROS M Node in SCALAR_DT_NODE and tries to find a 'scalar_domain' schema object that corresponds to it; the method returns the 'scalar_domain' Node if one is found; otherwise, it returns the original 'scalar_data_type' Node. The ROW_DOM_NODE argument, a 'row_domain' ROS M Node, is used to determine where to search; this method currently only searches for 'scalar_domain' Nodes that have the same primary-parent Node as the 'row_domain' Node, meaning they are declared in the same context. This method is used mainly by build_schema_or_app_table_create() at the moment.
DEPENDENCIES
This module requires any version of Perl 5.x.y that is at least 5.8.1.
It also requires the Perl modules version and only, which would conceptually be built-in to Perl, but aren't, so they are on CPAN instead.
It also requires the Perl module List::MoreUtils '0.12-', which would conceptually be built-in to Perl, but isn't, so it is on CPAN instead.
It also requires these modules that are on CPAN: Locale::KeyedText '1.6.0-' (for error messages), Rosetta::Model '0.71.0-'.
INCOMPATIBILITIES
None reported.
SEE ALSO
perl(1), Rosetta::Utility::SQLBuilder::L::en, Locale::KeyedText, Rosetta::Model, Rosetta::Utility::SQLParser, Rosetta::Engine::Generic, Rosetta::Emulator::DBI.
BUGS AND LIMITATIONS
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.
AUTHOR
Darren R. Duncan (perl@DarrenDuncan.net)
LICENCE AND COPYRIGHT
This file is part of the Rosetta::Utility::SQLBuilder reference implementation of a SQL:2003 string builder that uses the Rosetta::Model database portability library.
Rosetta::Utility::SQLBuilder is Copyright (c) 2002-2005, 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::Utility::SQLBuilder is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License (GPL) as published by the Free Software Foundation (http://www.fsf.org/); either version 2 of the License, or (at your option) any later version. You should have received a copy of the GPL as part of the Rosetta::Utility::SQLBuilder distribution, in the file named "GPL"; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301, USA.
Linking Rosetta::Utility::SQLBuilder statically or dynamically with other modules is making a combined work based on Rosetta::Utility::SQLBuilder. Thus, the terms and conditions of the GPL cover the whole combination. As a special exception, the copyright holders of Rosetta::Utility::SQLBuilder give you permission to link Rosetta::Utility::SQLBuilder 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::Utility::SQLBuilder (the version of Rosetta::Utility::SQLBuilder 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::Utility::SQLBuilder, and which is fully useable when not linked to Rosetta::Utility::SQLBuilder in any form.
Any versions of Rosetta::Utility::SQLBuilder 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::Utility::SQLBuilder 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::Utility::SQLBuilder would appreciate being informed any time you create a modified version of Rosetta::Utility::SQLBuilder that you are willing to distribute, because that is a practical way of suggesting improvements to the standard version.