NAME
SQL::Routine - Specify all database tasks with SQL routines
DEPENDENCIES
Perl Version: 5.008001
Core Modules:
Scalar::Util 1.11 (for weak refs)Non-Core Modules:
Locale::KeyedText 1.05 (for error messages)COPYRIGHT AND LICENSE
This file is part of the SQL::Routine database portability library.
SQL::Routine 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.
SQL::Routine 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 SQL::Routine 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 SQL::Routine statically or dynamically with other modules is making a combined work based on SQL::Routine. Thus, the terms and conditions of the GPL cover the whole combination. As a special exception, the copyright holders of SQL::Routine give you permission to link SQL::Routine 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 SQL::Routine (the version of SQL::Routine 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 SQL::Routine, and which is fully useable when not linked to SQL::Routine in any form.
Any versions of SQL::Routine 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. SQL::Routine 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 SQL::Routine would appreciate being informed any time you create a modified version of SQL::Routine that you are willing to distribute, because that is a practical way of suggesting improvements to the standard version.
SYNOPSIS
Perl Code That Builds A SQL::Routine Model
This executable code example shows how to define some simple database tasks with SQL::Routine; it only shows a tiny fraction of what the module is capable of, since more advanced features are not shown for brevity.
use SQL::Routine;
eval {
# Create a model/container in which all SQL details are to be stored.
# The two boolean options being set true here permit all the subsequent code to be as concise,
# easy to read, and most SQL-string-like as possible, at the cost of being slower to execute.
my $model = SQL::Routine->new_container();
$model->auto_set_node_ids( 1 );
$model->may_match_surrogate_node_ids( 1 );
# This defines 4 scalar/column/field data types (1 number, 2 char strings, 1 enumerated value type)
# and 2 row/table data types; the former are atomic and the latter are composite.
# The former can describe individual columns of a base table (table) or viewed table (view),
# while the latter can describe an entire table or view.
# Any of these can describe a 'domain' schema object or a stored procedure's variable's data type.
# See also the 'person' and 'person_with_parents' table+view defs further below; these data types help describe them.
$model->build_child_node_trees( [
[ 'scalar_data_type', { 'si_name' => 'entity_id' , 'base_type' => 'NUM_INT' , 'num_precision' => 9, }, ],
[ 'scalar_data_type', { 'si_name' => 'alt_id' , 'base_type' => 'STR_CHAR', 'max_chars' => 20, 'char_enc' => 'UTF8', }, ],
[ 'scalar_data_type', { 'si_name' => 'person_name', 'base_type' => 'STR_CHAR', 'max_chars' => 100, 'char_enc' => 'UTF8', }, ],
[ 'scalar_data_type', { 'si_name' => 'person_sex' , 'base_type' => 'STR_CHAR', 'max_chars' => 1, 'char_enc' => 'UTF8', }, [
[ 'scalar_data_type_opt', 'M', ],
[ 'scalar_data_type_opt', 'F', ],
], ],
[ 'row_data_type', 'person', [
[ 'row_data_type_field', { 'si_name' => 'person_id' , 'scalar_data_type' => 'entity_id' , }, ],
[ 'row_data_type_field', { 'si_name' => 'alternate_id', 'scalar_data_type' => 'alt_id' , }, ],
[ 'row_data_type_field', { 'si_name' => 'name' , 'scalar_data_type' => 'person_name', }, ],
[ 'row_data_type_field', { 'si_name' => 'sex' , 'scalar_data_type' => 'person_sex' , }, ],
[ 'row_data_type_field', { 'si_name' => 'father_id' , 'scalar_data_type' => 'entity_id' , }, ],
[ 'row_data_type_field', { 'si_name' => 'mother_id' , 'scalar_data_type' => 'entity_id' , }, ],
], ],
[ 'row_data_type', 'person_with_parents', [
[ 'row_data_type_field', { 'si_name' => 'self_id' , 'scalar_data_type' => 'entity_id' , }, ],
[ 'row_data_type_field', { 'si_name' => 'self_name' , 'scalar_data_type' => 'person_name', }, ],
[ 'row_data_type_field', { 'si_name' => 'father_id' , 'scalar_data_type' => 'entity_id' , }, ],
[ 'row_data_type_field', { 'si_name' => 'father_name', 'scalar_data_type' => 'person_name', }, ],
[ 'row_data_type_field', { 'si_name' => 'mother_id' , 'scalar_data_type' => 'entity_id' , }, ],
[ 'row_data_type_field', { 'si_name' => 'mother_name', 'scalar_data_type' => 'person_name', }, ],
], ],
] );
# This defines the blueprint of a database catalog that contains a single schema and a single virtual user which owns the schema.
my $catalog_bp = $model->build_child_node_tree( 'catalog', 'Gene Database', [
[ 'owner', 'Lord of the Root', ],
[ 'schema', { 'si_name' => 'Gene Schema', 'owner' => 'Lord of the Root', }, ],
] );
my $schema = $catalog_bp->find_child_node_by_surrogate_id( 'Gene Schema' );
# This defines a base table (table) schema object that lives in the aforementioned database catalog.
# It contains 6 columns, including a not-null primary key (having a trivial sequence generator to give it
# default values), another not-null field, a surrogate key, and 2 self-referencing foreign keys.
# Each row represents a single 'person', for each storing up to 2 unique identifiers, name, sex, and the parents' unique ids.
my $tb_person = $schema->build_child_node_tree( 'table', { 'si_name' => 'person', 'row_data_type' => 'person', }, [
[ 'table_field', { 'si_row_field' => 'person_id', 'mandatory' => 1, 'default_val' => 1, 'auto_inc' => 1, }, ],
[ 'table_field', { 'si_row_field' => 'name' , 'mandatory' => 1, }, ],
[ 'table_index', { 'si_name' => 'primary' , 'index_type' => 'UNIQUE', }, [
[ 'table_index_field', 'person_id', ],
], ],
[ 'table_index', { 'si_name' => 'ak_alternate_id', 'index_type' => 'UNIQUE', }, [
[ 'table_index_field', 'alternate_id', ],
], ],
[ 'table_index', { 'si_name' => 'fk_father', 'index_type' => 'FOREIGN', 'f_table' => 'person', }, [
[ 'table_index_field', { 'si_field' => 'father_id', 'f_field' => 'person_id' } ],
], ],
[ 'table_index', { 'si_name' => 'fk_mother', 'index_type' => 'FOREIGN', 'f_table' => 'person', }, [
[ 'table_index_field', { 'si_field' => 'mother_id', 'f_field' => 'person_id' } ],
], ],
] );
# This defines a viewed table (view) schema object that lives in the aforementioned database catalog.
# It left-outer-joins the 'person' table to itself twice and returns 2 columns from each constituent, for 6 total.
# Each row gives the unique id and name each for 3 people, a given person and that person's 2 parents.
my $vw_pwp = $schema->build_child_node_tree( 'view', { 'si_name' => 'person_with_parents',
'view_type' => 'JOINED', 'row_data_type' => 'person_with_parents', }, [
( map { [ 'view_src', { 'si_name' => $_, 'match' => 'person', }, [
map { [ 'view_src_field', $_, ], } ( 'person_id', 'name', 'father_id', 'mother_id', ),
], ], } ('self') ),
( map { [ 'view_src', { 'si_name' => $_, 'match' => 'person', }, [
map { [ 'view_src_field', $_, ], } ( 'person_id', 'name', ),
], ], } ( 'father', 'mother', ) ),
[ 'view_field', { 'si_row_field' => 'self_id' , 'src_field' => ['person_id','self' ], }, ],
[ 'view_field', { 'si_row_field' => 'self_name' , 'src_field' => ['name' ,'self' ], }, ],
[ 'view_field', { 'si_row_field' => 'father_id' , 'src_field' => ['person_id','father'], }, ],
[ 'view_field', { 'si_row_field' => 'father_name', 'src_field' => ['name' ,'father'], }, ],
[ 'view_field', { 'si_row_field' => 'mother_id' , 'src_field' => ['person_id','mother'], }, ],
[ 'view_field', { 'si_row_field' => 'mother_name', 'src_field' => ['name' ,'mother'], }, ],
[ 'view_join', { 'lhs_src' => 'self', 'rhs_src' => 'father', 'join_op' => 'LEFT', }, [
[ 'view_join_field', { 'lhs_src_field' => 'father_id', 'rhs_src_field' => 'person_id' } ],
], ],
[ 'view_join', { 'lhs_src' => 'self', 'rhs_src' => 'mother', 'join_op' => 'LEFT', }, [
[ 'view_join_field', { 'lhs_src_field' => 'mother_id', 'rhs_src_field' => 'person_id' } ],
], ],
] );
# This defines the blueprint of an application that has a single virtual connection descriptor to the above database.
my $application_bp = $model->build_child_node_tree( 'application', 'Gene App', [
[ 'catalog_link', { 'si_name' => 'editor_link', 'target' => $catalog_bp, }, ],
] );
# This defines another scalar data type, which is used by some routines that follow below.
my $sdt_login_auth = $model->build_child_node( 'scalar_data_type', { 'si_name' => 'login_auth',
'base_type' => 'STR_CHAR', 'max_chars' => 20, 'char_enc' => 'UTF8', } );
# This defines an application-side routine/function that connects to the 'Gene Database', fetches all
# the records from the 'person_with_parents' view, disconnects the database, and returns the fetched records.
# It takes run-time arguments for a user login name and password that are used when connecting.
my $rt_fetch_pwp = $application_bp->build_child_node_tree( 'routine', { 'si_name' => 'fetch_pwp',
'routine_type' => 'FUNCTION', 'return_cont_type' => 'RW_ARY', 'return_row_data_type' => 'person_with_parents', }, [
[ 'routine_arg', { 'si_name' => 'login_name', 'cont_type' => 'SCALAR', 'scalar_data_type' => $sdt_login_auth }, ],
[ 'routine_arg', { 'si_name' => 'login_pass', 'cont_type' => 'SCALAR', 'scalar_data_type' => $sdt_login_auth }, ],
[ 'routine_var', { 'si_name' => 'conn_cx', 'cont_type' => 'CONN', 'conn_link' => 'editor_link', }, ],
[ 'routine_stmt', { 'call_sroutine' => 'CATALOG_OPEN', }, [
[ 'routine_expr', { 'call_sroutine_cxt' => 'CONN_CX', 'cont_type' => 'CONN', 'valf_p_routine_item' => 'conn_cx', }, ],
[ 'routine_expr', { 'call_sroutine_arg' => 'LOGIN_NAME', 'cont_type' => 'SCALAR', 'valf_p_routine_item' => 'login_name', }, ],
[ 'routine_expr', { 'call_sroutine_arg' => 'LOGIN_PASS', 'cont_type' => 'SCALAR', 'valf_p_routine_item' => 'login_pass', }, ],
], ],
[ 'routine_var', { 'si_name' => 'pwp_ary', 'cont_type' => 'RW_ARY', 'row_data_type' => 'person_with_parents', }, ],
[ 'routine_stmt', { 'call_sroutine' => 'SELECT', }, [
[ 'view', { 'si_name' => 'query_pwp', 'view_type' => 'ALIAS', 'row_data_type' => 'person_with_parents', }, [
[ 'view_src', { 'si_name' => 's', 'match' => $vw_pwp, }, ],
], ],
[ 'routine_expr', { 'call_sroutine_cxt' => 'CONN_CX', 'cont_type' => 'CONN', 'valf_p_routine_item' => 'conn_cx', }, ],
[ 'routine_expr', { 'call_sroutine_arg' => 'SELECT_DEFN', 'cont_type' => 'SRT_NODE', 'act_on' => 'query_pwp', }, ],
[ 'routine_expr', { 'call_sroutine_arg' => 'INTO', 'query_dest' => 'pwp_ary', 'cont_type' => 'RW_ARY', }, ],
], ],
[ 'routine_stmt', { 'call_sroutine' => 'CATALOG_CLOSE', }, [
[ 'routine_expr', { 'call_sroutine_cxt' => 'CONN_CX', 'cont_type' => 'CONN', 'valf_p_routine_item', 'conn_cx', }, ],
], ],
[ 'routine_stmt', { 'call_sroutine' => 'RETURN', }, [
[ 'routine_expr', { 'call_sroutine_arg' => 'RETURN_VALUE', 'cont_type' => 'RW_ARY', 'valf_p_routine_item' => 'pwp_ary', }, ],
], ],
] );
# This defines an application-side routine/procedure that inserts a set of records, given in an argument,
# into the 'person' table. It takes an already opened db connection handle to operate through as a
# 'context' argument (which would represent the invocant if this routine was wrapped in an object-oriented interface).
my $rt_add_people = $application_bp->build_child_node_tree( 'routine', { 'si_name' => 'add_people', 'routine_type' => 'PROCEDURE', }, [
[ 'routine_context', { 'si_name' => 'conn_cx', 'cont_type' => 'CONN', 'conn_link' => 'editor_link', }, ],
[ 'routine_arg', { 'si_name' => 'person_ary', 'cont_type' => 'RW_ARY', 'row_data_type' => 'person', }, ],
[ 'routine_stmt', { 'call_sroutine' => 'INSERT', }, [
[ 'view', { 'si_name' => 'insert_people', 'view_type' => 'INSERT', 'row_data_type' => 'person', 'ins_p_routine_item' => 'person_ary', }, [
[ 'view_src', { 'si_name' => 's', 'match' => $tb_person, }, ],
], ],
[ 'routine_expr', { 'call_sroutine_cxt' => 'CONN_CX', 'cont_type' => 'CONN', 'valf_p_routine_item' => 'conn_cx', }, ],
[ 'routine_expr', { 'call_sroutine_arg' => 'INSERT_DEFN', 'cont_type' => 'SRT_NODE', 'act_on' => 'insert_people', }, ],
], ],
] );
# This defines an application-side routine/function that fetches one record
# from the 'person' table which matches its argument.
my $rt_get_person = $application_bp->build_child_node_tree( 'routine', { 'si_name' => 'get_person',
'routine_type' => 'FUNCTION', 'return_cont_type' => 'ROW', 'return_row_data_type' => 'person', }, [
[ 'routine_context', { 'si_name' => 'conn_cx', 'cont_type' => 'CONN', 'conn_link' => 'editor_link', }, ],
[ 'routine_arg', { 'si_name' => 'arg_person_id', 'cont_type' => 'SCALAR', 'scalar_data_type' => 'entity_id', }, ],
[ 'routine_var', { 'si_name' => 'person_row', 'cont_type' => 'ROW', 'row_data_type' => 'person', }, ],
[ 'routine_stmt', { 'call_sroutine' => 'SELECT', }, [
[ 'view', { 'si_name' => 'query_person', 'view_type' => 'JOINED', 'row_data_type' => 'person', }, [
[ 'view_src', { 'si_name' => 's', 'match' => $tb_person, }, [
[ 'view_src_field', 'person_id', ],
], ],
[ 'view_expr', { 'view_part' => 'WHERE', 'cont_type' => 'SCALAR', 'valf_call_sroutine' => 'EQ', }, [
[ 'view_expr', { 'call_sroutine_arg' => 'LHS', 'cont_type' => 'SCALAR', 'valf_src_field' => 'person_id', }, ],
[ 'view_expr', { 'call_sroutine_arg' => 'RHS', 'cont_type' => 'SCALAR', 'valf_p_routine_item' => 'arg_person_id', }, ],
], ],
], ],
[ 'routine_expr', { 'call_sroutine_cxt' => 'CONN_CX', 'cont_type' => 'CONN', 'valf_p_routine_item' => 'conn_cx', }, ],
[ 'routine_expr', { 'call_sroutine_arg' => 'SELECT_DEFN', 'cont_type' => 'SRT_NODE', 'act_on' => 'query_person', }, ],
[ 'routine_expr', { 'call_sroutine_arg' => 'INTO', 'query_dest' => 'person_row', 'cont_type' => 'RW_ARY', }, ],
], ],
[ 'routine_stmt', { 'call_sroutine' => 'RETURN', }, [
[ 'routine_expr', { 'call_sroutine_arg' => 'RETURN_VALUE', 'cont_type' => 'ROW', 'valf_p_routine_item' => 'person_row', }, ],
], ],
] );
# This defines 6 database engine descriptors and 2 database bridge descriptors that we may be using.
# These details can help external code determine such things as what string-SQL flavors should be
# generated from the model, as well as which database features can be used natively or have to be emulated.
# The 'si_name' has no meaning to code and is for users; the other attribute values should have meaning to said external code.
$model->build_child_node_trees( [
[ 'data_storage_product', { 'si_name' => 'SQLite v3.2' , 'product_code' => 'SQLite_3_2' , 'is_file_based' => 1, }, ],
[ 'data_storage_product', { 'si_name' => 'MySQL v5.0' , 'product_code' => 'MySQL_5_0' , 'is_network_svc' => 1, }, ],
[ 'data_storage_product', { 'si_name' => 'PostgreSQL v8', 'product_code' => 'PostgreSQL_8', 'is_network_svc' => 1, }, ],
[ 'data_storage_product', { 'si_name' => 'Oracle v10g' , 'product_code' => 'Oracle_10_g' , 'is_network_svc' => 1, }, ],
[ 'data_storage_product', { 'si_name' => 'Sybase' , 'product_code' => 'Sybase' , 'is_network_svc' => 1, }, ],
[ 'data_storage_product', { 'si_name' => 'CSV' , 'product_code' => 'CSV' , 'is_file_based' => 1, }, ],
[ 'data_link_product', { 'si_name' => 'Microsoft ODBC v3', 'product_code' => 'ODBC_3', }, ],
[ 'data_link_product', { 'si_name' => 'Oracle OCI*8', 'product_code' => 'OCI_8', }, ],
[ 'data_link_product', { 'si_name' => 'Generic Rosetta Engine', 'product_code' => 'Rosetta::Engine::Generic', }, ],
] );
# This defines one concrete instance each of the database catalog and an application using it.
# This concrete database instance includes two concrete user definitions, one that can owns
# the schema and one that can only edit data. The concrete application instance includes
# a concrete connection descriptor going to this concrete database instance.
# Note that 'user' descriptions are only stored in a SQL::Routine model when that model is being used to create
# database catalogs and/or create or modify database users; otherwise 'user' should not be kept for security sake.
$model->build_child_node_trees( [
[ 'catalog_instance', { 'si_name' => 'test', 'blueprint' => $catalog_bp, 'product' => 'PostgreSQL v8', }, [
[ 'user', { 'si_name' => 'ronsealy', 'user_type' => 'SCHEMA_OWNER', 'match_owner' => 'Lord of the Root', 'password' => 'K34dsD', }, ],
[ 'user', { 'si_name' => 'joesmith', 'user_type' => 'DATA_EDITOR', 'password' => 'fdsKJ4', }, ],
], ],
[ 'application_instance', { 'si_name' => 'test app', 'blueprint' => $application_bp, }, [
[ 'catalog_link_instance', { 'blueprint' => 'editor_link', 'product' => 'Microsoft ODBC v3', 'target' => 'test', 'local_dsn' => 'keep_it', }, ],
], ],
] );
# This defines another concrete instance each of the database catalog and an application using it.
$model->build_child_node_trees( [
[ 'catalog_instance', { 'si_name' => 'production', 'blueprint' => $catalog_bp, 'product' => 'Oracle v10g', }, [
[ 'user', { 'si_name' => 'florence', 'user_type' => 'SCHEMA_OWNER', 'match_owner' => 'Lord of the Root', 'password' => '0sfs8G', }, ],
[ 'user', { 'si_name' => 'thainuff', 'user_type' => 'DATA_EDITOR', 'password' => '9340sd', }, ],
], ],
[ 'application_instance', { 'si_name' => 'production app', 'blueprint' => $application_bp, }, [
[ 'catalog_link_instance', { 'blueprint' => 'editor_link', 'product' => 'Oracle OCI*8', 'target' => 'production', 'local_dsn' => 'ship_it', }, ],
], ],
] );
# This defines a third concrete instance each of the database catalog and an application using it.
$model->build_child_node_trees( [
[ 'catalog_instance', { 'si_name' => 'laptop demo', 'blueprint' => $catalog_bp, 'product' => 'SQLite v3.2', 'file_path' => 'Move It', }, ],
[ 'application_instance', { 'si_name' => 'laptop demo app', 'blueprint' => $application_bp, }, [
[ 'catalog_link_instance', { 'blueprint' => 'editor_link', 'product' => 'Generic Rosetta Engine', 'target' => 'laptop demo', }, ],
], ],
] );
# This line will run some correctness tests on the model that were not done
# when the model was being populated for execution speed efficiency.
$model->assert_deferrable_constraints();
# This line will dump the contents of the model in pretty-printed XML format.
# It can be helpful when debugging your programs that use SQL::Routine.
print $model->get_all_properties_as_xml_str( 1 );
};
$@ and print error_to_string($@);
# SQL::Routine throws object exceptions when it encounters bad input; this function
# will convert those into human readable text for display by the try/catch block.
sub error_to_string {
my ($message) = @_;
if( ref($message) and UNIVERSAL::isa( $message, 'Locale::KeyedText::Message' ) ) {
my $translator = Locale::KeyedText->new_translator( ['SQL::Routine::L::'], ['en'] );
my $user_text = $translator->translate_message( $message );
unless( $user_text ) {
return 'internal error: can\'t find user text for a message: '.
$message->as_string().' '.$translator->as_string();
}
return $user_text;
}
return $message; # if this isn't the right kind of object
}Note that one key feature of SQL::Routine is that all of a model's pieces are linked by references rather than by name as in SQL itself. For example, the name of the 'person' table is only stored once internally; if, after executing all of the above code, you were to run "$tb_person->set_attribute( 'si_name', 'The Huddled Masses' );", then all of the other parts of the model that referred to the table would not break, and an XML dump would show that all the references now say 'The Huddled Masses'.
For some more (older) examples of SQL::Routine in use, see its test suite code.
An XML Representation of That Model
This is the XML that the above get_all_properties_as_xml_str() prints out:
<?xml version="1.0" encoding="UTF-8"?>
<root>
<elements>
<scalar_data_type id="1" si_name="entity_id" base_type="NUM_INT" num_precision="9" />
<scalar_data_type id="2" si_name="alt_id" base_type="STR_CHAR" max_chars="20" char_enc="UTF8" />
<scalar_data_type id="3" si_name="person_name" base_type="STR_CHAR" max_chars="100" char_enc="UTF8" />
<scalar_data_type id="4" si_name="person_sex" base_type="STR_CHAR" max_chars="1" char_enc="UTF8">
<scalar_data_type_opt id="5" si_value="M" />
<scalar_data_type_opt id="6" si_value="F" />
</scalar_data_type>
<row_data_type id="7" si_name="person">
<row_data_type_field id="8" si_name="person_id" scalar_data_type="entity_id" />
<row_data_type_field id="9" si_name="alternate_id" scalar_data_type="alt_id" />
<row_data_type_field id="10" si_name="name" scalar_data_type="person_name" />
<row_data_type_field id="11" si_name="sex" scalar_data_type="person_sex" />
<row_data_type_field id="12" si_name="father_id" scalar_data_type="entity_id" />
<row_data_type_field id="13" si_name="mother_id" scalar_data_type="entity_id" />
</row_data_type>
<row_data_type id="14" si_name="person_with_parents">
<row_data_type_field id="15" si_name="self_id" scalar_data_type="entity_id" />
<row_data_type_field id="16" si_name="self_name" scalar_data_type="person_name" />
<row_data_type_field id="17" si_name="father_id" scalar_data_type="entity_id" />
<row_data_type_field id="18" si_name="father_name" scalar_data_type="person_name" />
<row_data_type_field id="19" si_name="mother_id" scalar_data_type="entity_id" />
<row_data_type_field id="20" si_name="mother_name" scalar_data_type="person_name" />
</row_data_type>
<scalar_data_type id="59" si_name="login_auth" base_type="STR_CHAR" max_chars="20" char_enc="UTF8" />
</elements>
<blueprints>
<catalog id="21" si_name="Gene Database">
<owner id="22" si_name="Lord of the Root" />
<schema id="23" si_name="Gene Schema" owner="Lord of the Root">
<table id="24" si_name="person" row_data_type="person">
<table_field id="25" si_row_field="person_id" mandatory="1" default_val="1" auto_inc="1" />
<table_field id="26" si_row_field="name" mandatory="1" />
<table_index id="27" si_name="primary" index_type="UNIQUE">
<table_index_field id="28" si_field="person_id" />
</table_index>
<table_index id="29" si_name="ak_alternate_id" index_type="UNIQUE">
<table_index_field id="30" si_field="alternate_id" />
</table_index>
<table_index id="31" si_name="fk_father" index_type="FOREIGN" f_table="person">
<table_index_field id="32" si_field="father_id" f_field="person_id" />
</table_index>
<table_index id="33" si_name="fk_mother" index_type="FOREIGN" f_table="person">
<table_index_field id="34" si_field="mother_id" f_field="person_id" />
</table_index>
</table>
<view id="35" si_name="person_with_parents" view_type="JOINED" row_data_type="person_with_parents">
<view_src id="36" si_name="self" match="person">
<view_src_field id="37" si_match_field="person_id" />
<view_src_field id="38" si_match_field="name" />
<view_src_field id="39" si_match_field="father_id" />
<view_src_field id="40" si_match_field="mother_id" />
</view_src>
<view_src id="41" si_name="father" match="person">
<view_src_field id="42" si_match_field="person_id" />
<view_src_field id="43" si_match_field="name" />
</view_src>
<view_src id="44" si_name="mother" match="person">
<view_src_field id="45" si_match_field="person_id" />
<view_src_field id="46" si_match_field="name" />
</view_src>
<view_field id="47" si_row_field="self_id" src_field="[person_id,self]" />
<view_field id="48" si_row_field="self_name" src_field="[name,self]" />
<view_field id="49" si_row_field="father_id" src_field="[person_id,father]" />
<view_field id="50" si_row_field="father_name" src_field="[name,father]" />
<view_field id="51" si_row_field="mother_id" src_field="[person_id,mother]" />
<view_field id="52" si_row_field="mother_name" src_field="[name,mother]" />
<view_join id="53" lhs_src="self" rhs_src="father" join_op="LEFT">
<view_join_field id="54" lhs_src_field="father_id" rhs_src_field="person_id" />
</view_join>
<view_join id="55" lhs_src="self" rhs_src="mother" join_op="LEFT">
<view_join_field id="56" lhs_src_field="mother_id" rhs_src_field="person_id" />
</view_join>
</view>
</schema>
</catalog>
<application id="57" si_name="Gene App">
<catalog_link id="58" si_name="editor_link" target="Gene Database" />
<routine id="60" si_name="fetch_pwp" routine_type="FUNCTION" return_cont_type="RW_ARY" return_row_data_type="person_with_parents">
<routine_arg id="61" si_name="login_name" cont_type="SCALAR" scalar_data_type="login_auth" />
<routine_arg id="62" si_name="login_pass" cont_type="SCALAR" scalar_data_type="login_auth" />
<routine_var id="63" si_name="conn_cx" cont_type="CONN" conn_link="editor_link" />
<routine_stmt id="64" call_sroutine="CATALOG_OPEN">
<routine_expr id="65" call_sroutine_cxt="CONN_CX" cont_type="CONN" valf_p_routine_item="conn_cx" />
<routine_expr id="66" call_sroutine_arg="LOGIN_NAME" cont_type="SCALAR" valf_p_routine_item="login_name" />
<routine_expr id="67" call_sroutine_arg="LOGIN_PASS" cont_type="SCALAR" valf_p_routine_item="login_pass" />
</routine_stmt>
<routine_var id="68" si_name="pwp_ary" cont_type="RW_ARY" row_data_type="person_with_parents" />
<routine_stmt id="69" call_sroutine="SELECT">
<view id="70" si_name="query_pwp" view_type="ALIAS" row_data_type="person_with_parents">
<view_src id="71" si_name="s" match="[person_with_parents,Gene Schema,Gene Database]" />
</view>
<routine_expr id="72" call_sroutine_cxt="CONN_CX" cont_type="CONN" valf_p_routine_item="conn_cx" />
<routine_expr id="73" call_sroutine_arg="SELECT_DEFN" cont_type="SRT_NODE" act_on="query_pwp" />
<routine_expr id="74" call_sroutine_arg="INTO" query_dest="pwp_ary" cont_type="RW_ARY" />
</routine_stmt>
<routine_stmt id="75" call_sroutine="CATALOG_CLOSE">
<routine_expr id="76" call_sroutine_cxt="CONN_CX" cont_type="CONN" valf_p_routine_item="conn_cx" />
</routine_stmt>
<routine_stmt id="77" call_sroutine="RETURN">
<routine_expr id="78" call_sroutine_arg="RETURN_VALUE" cont_type="RW_ARY" valf_p_routine_item="pwp_ary" />
</routine_stmt>
</routine>
<routine id="79" si_name="add_people" routine_type="PROCEDURE">
<routine_context id="80" si_name="conn_cx" cont_type="CONN" conn_link="editor_link" />
<routine_arg id="81" si_name="person_ary" cont_type="RW_ARY" row_data_type="person" />
<routine_stmt id="82" call_sroutine="INSERT">
<view id="83" si_name="insert_people" view_type="INSERT" row_data_type="person" ins_p_routine_item="person_ary">
<view_src id="84" si_name="s" match="[person,Gene Schema,Gene Database]" />
</view>
<routine_expr id="85" call_sroutine_cxt="CONN_CX" cont_type="CONN" valf_p_routine_item="conn_cx" />
<routine_expr id="86" call_sroutine_arg="INSERT_DEFN" cont_type="SRT_NODE" act_on="insert_people" />
</routine_stmt>
</routine>
<routine id="87" si_name="get_person" routine_type="FUNCTION" return_cont_type="ROW" return_row_data_type="person">
<routine_context id="88" si_name="conn_cx" cont_type="CONN" conn_link="editor_link" />
<routine_arg id="89" si_name="arg_person_id" cont_type="SCALAR" scalar_data_type="entity_id" />
<routine_var id="90" si_name="person_row" cont_type="ROW" row_data_type="person" />
<routine_stmt id="91" call_sroutine="SELECT">
<view id="92" si_name="query_person" view_type="JOINED" row_data_type="person">
<view_src id="93" si_name="s" match="[person,Gene Schema,Gene Database]">
<view_src_field id="94" si_match_field="person_id" />
</view_src>
<view_expr id="95" view_part="WHERE" cont_type="SCALAR" valf_call_sroutine="EQ">
<view_expr id="96" call_sroutine_arg="LHS" cont_type="SCALAR" valf_src_field="[person_id,s]" />
<view_expr id="97" call_sroutine_arg="RHS" cont_type="SCALAR" valf_p_routine_item="arg_person_id" />
</view_expr>
</view>
<routine_expr id="98" call_sroutine_cxt="CONN_CX" cont_type="CONN" valf_p_routine_item="conn_cx" />
<routine_expr id="99" call_sroutine_arg="SELECT_DEFN" cont_type="SRT_NODE" act_on="query_person" />
<routine_expr id="100" call_sroutine_arg="INTO" query_dest="person_row" cont_type="RW_ARY" />
</routine_stmt>
<routine_stmt id="101" call_sroutine="RETURN">
<routine_expr id="102" call_sroutine_arg="RETURN_VALUE" cont_type="ROW" valf_p_routine_item="person_row" />
</routine_stmt>
</routine>
</application>
</blueprints>
<tools>
<data_storage_product id="103" si_name="SQLite v3.2" product_code="SQLite_3_2" is_file_based="1" />
<data_storage_product id="104" si_name="MySQL v5.0" product_code="MySQL_5_0" is_network_svc="1" />
<data_storage_product id="105" si_name="PostgreSQL v8" product_code="PostgreSQL_8" is_network_svc="1" />
<data_storage_product id="106" si_name="Oracle v10g" product_code="Oracle_10_g" is_network_svc="1" />
<data_storage_product id="107" si_name="Sybase" product_code="Sybase" is_network_svc="1" />
<data_storage_product id="108" si_name="CSV" product_code="CSV" is_file_based="1" />
<data_link_product id="109" si_name="Microsoft ODBC v3" product_code="ODBC_3" />
<data_link_product id="110" si_name="Oracle OCI*8" product_code="OCI_8" />
<data_link_product id="111" si_name="Generic Rosetta Engine" product_code="Rosetta::Engine::Generic" />
</tools>
<sites>
<catalog_instance id="112" si_name="test" blueprint="Gene Database" product="PostgreSQL v8">
<user id="113" si_name="ronsealy" user_type="SCHEMA_OWNER" match_owner="Lord of the Root" password="K34dsD" />
<user id="114" si_name="joesmith" user_type="DATA_EDITOR" password="fdsKJ4" />
</catalog_instance>
<application_instance id="115" si_name="test app" blueprint="Gene App">
<catalog_link_instance id="116" blueprint="editor_link" product="Microsoft ODBC v3" target="test" local_dsn="keep_it" />
</application_instance>
<catalog_instance id="117" si_name="production" blueprint="Gene Database" product="Oracle v10g">
<user id="118" si_name="florence" user_type="SCHEMA_OWNER" match_owner="Lord of the Root" password="0sfs8G" />
<user id="119" si_name="thainuff" user_type="DATA_EDITOR" password="9340sd" />
</catalog_instance>
<application_instance id="120" si_name="production app" blueprint="Gene App">
<catalog_link_instance id="121" blueprint="editor_link" product="Oracle OCI*8" target="production" local_dsn="ship_it" />
</application_instance>
<catalog_instance id="122" si_name="laptop demo" blueprint="Gene Database" product="SQLite v3.2" file_path="Move It" />
<application_instance id="123" si_name="laptop demo app" blueprint="Gene App">
<catalog_link_instance id="124" blueprint="editor_link" product="Generic Rosetta Engine" target="laptop demo" />
</application_instance>
</sites>
<circumventions />
</root>String SQL That Can Be Made From the Model
This section has examples of string-SQL that can be generated from the above model. The examples are conformant by default to the SQL:2003 standard flavor, but will vary from there to make illustration simpler; some examples may contain a hodge-podge of database vendor extensions and as a whole won't execute as is on some database products.
These two examples for creating the same TABLE schema object, separated by a blank line, demonstrate SQL for a database that supports DOMAIN schema objects and SQL for a database that does not. They both assume that uniqueness and foreign key constraints are only enforced on not-null values.
CREATE DOMAIN entity_id AS INTEGER(9);
CREATE DOMAIN alt_id AS VARCHAR(20);
CREATE DOMAIN person_name AS VARCHAR(100);
CREATE DOMAIN person_sex AS ENUM('M','F');
CREATE TABLE person (
person_id entity_id NOT NULL DEFAULT 1 AUTO_INCREMENT,
alternate_id alt_id NULL,
name person_name NOT NULL,
sex person_sex NULL,
father_id entity_id NULL,
mother_id entity_id NULL,
CONSTRAINT PRIMARY KEY (person_id),
CONSTRAINT UNIQUE (alternate_id),
CONSTRAINT fk_father FOREIGN KEY (father_id) REFERENCES person (person_id),
CONSTRAINT fk_mother FOREIGN KEY (mother_id) REFERENCES person (person_id)
);
CREATE TABLE person (
person_id INTEGER(9) NOT NULL DEFAULT 1 AUTO_INCREMENT,
alternate_id VARCHAR(20) NULL,
name VARCHAR(100) NOT NULL,
sex ENUM('M','F') NULL,
father_id INTEGER(9) NULL,
mother_id INTEGER(9) NULL,
CONSTRAINT PRIMARY KEY (person_id),
CONSTRAINT UNIQUE (alternate_id),
CONSTRAINT fk_father FOREIGN KEY (father_id) REFERENCES person (person_id),
CONSTRAINT fk_mother FOREIGN KEY (mother_id) REFERENCES person (person_id)
);This example is for creating the VIEW schema object:
CREATE VIEW person_with_parents AS
SELECT self.person_id AS self_id, self.name AS self_name,
father.person_id AS father_id, father.name AS father_name,
mother.person_id AS mother_id, mother.name AS mother_name
FROM person AS self
LEFT OUTER JOIN person AS father ON father.person_id = self.father_id
LEFT OUTER JOIN person AS mother ON mother.person_id = self.father_id;If the 'get_person' routine were implemented as a database schema object, this is what it might look like:
CREATE FUNCTION get_person (arg_person_id INTEGER(9)) RETURNS ROW(...) AS
BEGIN
DECLARE person_row ROW(...);
SELECT * INTO person_row FROM person AS s WHERE s.person_id = arg_person_id;
RETURN person_row;
END;Then it could be invoked elsewhere like this:
my_rec = get_person( '3' );If the same routine were implemented as an application-side routine, then it might look like this (not actual DBI syntax):
my $sth = $dbh->prepare( "SELECT * FROM person AS s WHERE s.person_id = :arg_person_id" );
$sth->bind_param( 'arg_person_id', 'INTEGER(9)' );
$sth->execute( { 'arg_person_id' => '3' } );
my $my_rec = $sth->fetchrow_hashref();And finally, corresponding DROP statements can be made for any of the above database schema objects:
DROP DOMAIN entity_id;
DROP DOMAIN alt_id;
DROP DOMAIN person_name;
DROP DOMAIN person_sex;
DROP TABLE person;
DROP VIEW person_with_parents;
DROP FUNCTION get_person;See also the separately distributed SQL::Routine::SQLBuilder module, which is a reference implementation of a SQL:2003 (and more) generator for SQL::Routine.
DESCRIPTION
The SQL::Routine (SRT) Perl 5 module provides a container object that allows you to create specifications for any type of database task or activity (eg: queries, DML, DDL, connection management) that look like ordinary routines (procedures or functions) to your programs; all routine arguments are named.
SQL::Routine is trivially easy to install, since it is written in pure Perl and its whole non-core dependency chain consists of just 1 other pure Perl module.
Typical usage of this module involves creating or loading a single SQL::Routine::Container object when your program starts up; this Container would hold a complete representation of each database catalog that your program uses (including details of all schema objects), plus complete representations of all database invocations by your program; your program then typically just reads from the Container while active to help determine its actions.
SQL::Routine can broadly represent, as an abstract syntax tree (a cross-referenced hierarchy of nodes), code for any programming language, but many of its concepts are only applicable to relational databases, particularly SQL understanding databases. It is reasonable to expect that a SQL:2003 compliant database should be able to implement nearly all SQL::Routine concepts in its SQL stored procedures and functions, though SQL:2003 specifies some of these concepts as optional features rather than core features.
This module has a multi-layered API that lets you choose between writing fairly verbose code that performs faster, or fairly terse code that performs slower.
SQL::Routine is intended to be used by an application in place of using actual SQL strings (including support for placeholders). You define any desired actions by stuffing atomic values into SQL::Routine objects, and then pass those objects to a compatible bridging engine that will compile and execute those objects against one or more actual databases. Said bridge would be responsible for generating any SQL or Perl code necessary to implement the given SRT routine specification, and returning the result of its execution.
The 'Rosetta' database portability library (a Perl 5 module) is a database bridge that takes its instructions as SQL::Routine objects. There may be other modules that use SQL::Routine for that or other purposes.
SQL::Routine is also intended to be used as an intermediate representation of schema definitions or other SQL that is being translated from one database product to another.
This module is loosely similar to SQL::Statement, and is intended to be used in all of the same ways. But SQL::Routine is a lot more powerful and capable than that module, as I most recently understand it, and is suitable for many uses that the other module isn't.
SQL::Routine does not parse or generate any code on its own, nor does it talk to any databases; it is up to external code that uses it to do this.
To cut down on the size of the SQL::Routine module itself, most of the POD documentation is in these other files: SQL::Routine::Details, SQL::Routine::Language, SQL::Routine::EnumTypes, SQL::Routine::NodeTypes.
CLASSES IN THIS MODULE
This module is implemented by several object-oriented Perl 5 packages, each of which is referred to as a class. They are: SQL::Routine (the module's name-sake), SQL::Routine::Container (aka Container, aka Model), SQL::Routine::Node (aka Node), and SQL::Routine::Group (aka Group). This module also has 2 private classes named SQL::Routine::ContainerStorage and SQL::Routine::NodeStorage, which help to implement Container and Node respectively; each of the latter is a wrapper for one of the former.
While all 6 of the above classes are implemented in one module for convenience, you should consider all 6 names as being "in use"; do not create any modules or packages yourself that have the same names.
The Container and Node and Group classes do most of the work and are what you mainly use. The name-sake class mainly exists to guide CPAN in indexing the whole module, but it also provides a set of stateless utility methods and constants that the other two classes inherit, and it provides a few wrapper functions over the other classes for your convenience; you never instantiate an object of SQL::Routine itself.
Most of the SQL::Routine documentation you will see simply uses the terms 'Container' and 'Node' to refer to the pair of classes or objects which implements each as a single unit, even if said documentation is specific to the 'Storage' variants thereof, because someone using this module shouldn't need to know the difference. This said, some documentation will specify a pair member by appending the terms 'interface' and 'Storage'; "Container interface" refers to ::Container, "ContainerStorage" refers to ::ContainerStorage, "Node interface" refers to ::Node, "NodeStorage" refers to ::NodeStorage.
BRIEF FUNCTION AND METHOD LIST
Here is a compact list of this module's functions and methods along with their arguments. For full details on each one, please see SQL::Routine::Details.
CONSTRUCTOR WRAPPER FUNCTIONS:
new_container()
new_node( CONTAINER, NODE_TYPE[, NODE_ID] )
new_group( CONTAINER )CONTAINER CONSTRUCTOR FUNCTIONS:
new()CONTAINER OBJECT METHODS:
new_interface()
get_self_id()
auto_assert_deferrable_constraints([ NEW_VALUE ])
auto_set_node_ids([ NEW_VALUE ])
may_match_surrogate_node_ids([ NEW_VALUE ])
delete_node_tree()
get_child_nodes([ NODE_TYPE ])
find_node_by_id( NODE_ID )
find_child_node_by_surrogate_id( TARGET_ATTR_VALUE )
get_next_free_node_id()
get_edit_count()
deferrable_constraints_are_tested()
assert_deferrable_constraints()NODE CONSTRUCTOR FUNCTIONS:
new( CONTAINER, NODE_TYPE[, NODE_ID] )NODE OBJECT METHODS:
new_interface()
get_self_id()
delete_node()
delete_node_tree()
get_container()
get_node_type()
get_node_id()
set_node_id( NEW_ID )
get_primary_parent_attribute()
clear_primary_parent_attribute()
set_primary_parent_attribute( ATTR_VALUE )
get_surrogate_id_attribute([ GET_TARGET_SI ])
clear_surrogate_id_attribute()
set_surrogate_id_attribute( ATTR_VALUE )
get_attribute( ATTR_NAME[, GET_TARGET_SI] )
get_attributes([ GET_TARGET_SI ])
clear_attribute( ATTR_NAME )
clear_attributes()
set_attribute( ATTR_NAME, ATTR_VALUE )
set_attributes( ATTRS )
move_before_sibling( SIBLING[, PARENT] )
get_child_nodes([ NODE_TYPE ])
add_child_node( CHILD )
add_child_nodes( CHILDREN )
get_referencing_nodes([ NODE_TYPE ])
get_surrogate_id_chain()
find_node_by_surrogate_id( SELF_ATTR_NAME, TARGET_ATTR_VALUE )
find_child_node_by_surrogate_id( TARGET_ATTR_VALUE )
get_relative_surrogate_id( SELF_ATTR_NAME )
assert_deferrable_constraints()GROUP CONSTRUCTOR FUNCTIONS:
new( CONTAINER )GROUP OBJECT METHODS: (TODO)
CONTAINER OR NODE METHODS FOR DEBUGGING:
get_all_properties([ LINKS_AS_SI ])
get_all_properties_as_perl_str([ LINKS_AS_SI ])
get_all_properties_as_xml_str([ LINKS_AS_SI ])CONTAINER OR NODE FUNCTIONS AND METHODS FOR RAPID DEVELOPMENT:
build_node( NODE_TYPE[, ATTRS] )
build_child_node( NODE_TYPE[, ATTRS] )
build_child_nodes( CHILDREN )
build_child_node_tree( NODE_TYPE[, ATTRS][, CHILDREN] )
build_child_node_trees( CHILDREN )
build_container([ CHILDREN[, AUTO_ASSERT[, AUTO_IDS[, MATCH_SURR_IDS]]] ])INFORMATION FUNCTIONS:
valid_enumerated_types([ ENUM_TYPE ])
valid_enumerated_type_values( ENUM_TYPE[, ENUM_VALUE] )
valid_node_types([ NODE_TYPE ])
node_types_with_pseudonode_parents([ NODE_TYPE ])
node_types_with_primary_parent_attributes([ NODE_TYPE ])
valid_node_type_literal_attributes( NODE_TYPE[, ATTR_NAME] )
valid_node_type_enumerated_attributes( NODE_TYPE[, ATTR_NAME] )
valid_node_type_node_ref_attributes( NODE_TYPE[, ATTR_NAME] )
valid_node_type_surrogate_id_attributes([ NODE_TYPE ])BUGS
This module is currently in alpha development status, meaning that some parts of it will be changed in the near future, some perhaps in incompatible ways; however, I believe that any further incompatible changes will be small. The current state is analogous to 'developer releases' of operating systems; it is reasonable to being writing code that uses this module now, but you should be prepared to maintain it later in keeping with API changes. This module also does not yet have full code coverage in its tests, though the most commonly used areas are covered.
CAVEATS
You can not use surrogate id values that look like valid Node ids (that are positive integers) since some methods won't do what you expect when given such values. Nodes having such surrogate id values won't be matched by values passed to set_attribute(), directly or indirectly. That method only tries to lookup a Node by its surrogate id if its argument doesn't look like a Node ref or a Node id. Similarly, the build*() methods will decide whether to interpret a defined but non-Node-ref ATTRS argument as a Node id or a surrogate id based on its looking like a valid Node id or not. You should rarely encounter this caveat, though, since you would never use a number as a "SQL identifier" in normal cases, and that is only technically possible with a "delimited SQL identifier".
CREDITS
Besides myself as the creator ...
* 2004.05.20 - Thanks to Jarrell Dunson (jarrell_dunson@asburyseminary.edu) for inspiring me to add some concrete SYNOPSIS documentation examples to this module, which demonstrate actual SQL statements that can be generated from parts of a model, when he wrote me asking for examples of how to use this module.
* 2005.03.21 - Thanks to Stevan Little (stevan@iinteractive.com) for feedback towards improving this module's documentation, particularly towards using a much shorter SYNOPSIS, so that it is easier for newcomers to understand the module at a glance, and not be intimidated by large amounts of detailed information. Also thanks to Stevan for introducing me to Scalar::Util::weaken(); by using it, SQL::Routine objects can be garbage collected normally despite containing circular references, and users no longer need to invoke destructor methods.
SEE ALSO
perl(1), SQL::Routine::L::en, SQL::Routine::Details, SQL::Routine::Language, SQL::Routine::EnumTypes, SQL::Routine::NodeTypes, Locale::KeyedText, Rosetta, SQL::Routine::SQLBuilder, SQL::Routine::SQLParser, Rosetta::Engine::Generic, Rosetta::Emulator::DBI, DBI, SQL::Statement, SQL::Parser, SQL::Translator, SQL::YASP, SQL::Generator, SQL::Schema, SQL::Abstract, SQL::Snippet, SQL::Catalog, DB::Ent, DBIx::Abstract, DBIx::AnyDBD, DBIx::DBSchema, DBIx::Namespace, DBIx::SearchBuilder, TripleStore, Data::Table, and various other modules.