NAME

SQL::Routine - Specify all database tasks with SQL routines

DEPENDENCIES

Perl Version: 5.006

Standard Modules: none

Nonstandard Modules:

Locale::KeyedText 1.00 (for error messages)

COPYRIGHT AND LICENSE

This file is part of the SQL::Routine library (libSQLRT).

SQL::Routine is Copyright (c) 1999-2004, Darren R. Duncan. All rights reserved. Address comments, suggestions, and bug reports to perl@DarrenDuncan.net, or visit "http://www.DarrenDuncan.net" for more information.

SQL::Routine is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License (GPL) version 2 as published by the Free Software Foundation (http://www.fsf.org/). You should have received a copy of the GPL as part of the SQL::Routine distribution, in the file named "LICENSE"; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.

Linking 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

Trivial Perl Code Example

This module's native API is highly verbose / detailed and so a realistically complex example of its use would be too large to show here. However, here is a trivial example that contains everything needed to define a table with two columns, plus two domains used by it, plus the necessary CREATE instruction:

use SQL::Routine;

sub make_a_node {
	my ($node_type, $model) = @_;
	my $node = $model->new_node( $node_type );
	$node->set_node_id( $model->get_next_free_node_id( $node_type ) );
	$node->put_in_container( $model );
	return( $node );
}

sub make_a_child_node {
	my ($node_type, $pp_node, $pp_attr) = @_;
	my $container = $pp_node->get_container();
	my $node = $pp_node->new_node( $node_type );
	$node->set_node_id( $container->get_next_free_node_id( $node_type ) );
	$node->put_in_container( $container );
	$node->set_node_ref_attribute( $pp_attr, $pp_node );
	$node->set_pp_node_attribute_name( $pp_attr );
	return( $node );
}

eval {
	my $model = SQL::Routine->new_container();

	##### NEXT SET CATALOG ELEMENT-TYPE DETAILS #####

	# Create user-defined scalar data type that our database record primary keys are:
	my $sdt_entity_id = make_a_node( 'scalar_data_type', $model );
	$sdt_entity_id->set_literal_attribute( 'si_name', 'entity_id' );
	$sdt_entity_id->set_enumerated_attribute( 'base_type', 'NUM_INT' );
	$sdt_entity_id->set_literal_attribute( 'num_precision', 9 );

	# Create user-defined scalar data type that our person names are:
	my $sdt_pers_name = make_a_node( 'scalar_data_type', $model );
	$sdt_pers_name->set_literal_attribute( 'si_name', 'person_name' );
	$sdt_pers_name->set_enumerated_attribute( 'base_type', 'STR_CHAR' );
	$sdt_pers_name->set_literal_attribute( 'max_chars', 100 );
	$sdt_pers_name->set_enumerated_attribute( 'char_enc', 'UTF8' );

	# Create u-d row data type that describes the columns of the table that holds our data:
	my $rdt_person = make_a_node( 'row_data_type', $model );
	$rdt_person->set_literal_attribute( 'si_name', 'person' );

	# Define the 'person id' field/column of that row/table:
	my $rdtf_person_id = make_a_child_node( 'row_data_type_field', $rdt_person, 'pp_row_data_type' );
	$rdtf_person_id->set_literal_attribute( 'si_name', 'person_id' );
	$rdtf_person_id->set_node_ref_attribute( 'scalar_data_type', $sdt_entity_id );

	# Define the 'person si_name' field/column of that row/table:
	my $rdtf_person_name = make_a_child_node( 'row_data_type_field', $rdt_person, 'pp_row_data_type' );
	$rdtf_person_name->set_literal_attribute( 'si_name', 'si_name' );
	$rdtf_person_name->set_node_ref_attribute( 'scalar_data_type', $sdt_pers_name );

	##### NEXT SET APPLICATION ELEMENT-TYPE DETAILS #####

	# Create user-defined data type for generic boolean literals:
	my $sdt_boolean = make_a_node( 'scalar_data_type', $model );
	$sdt_boolean->set_literal_attribute( 'si_name', 'boolean' );
	$sdt_boolean->set_enumerated_attribute( 'base_type', 'BOOLEAN' );

	##### NEXT SET CATALOG BLUEPRINT-TYPE DETAILS #####

	# Describe the database catalog blueprint that we will store our data in:
	my $catalog_bp = make_a_node( 'catalog', $model );
	$catalog_bp->set_literal_attribute( 'si_name', 'The Catalog Blueprint' );

	# Define the unrealized database user that owns our primary schema:
	my $owner = make_a_child_node( 'owner', $catalog_bp, 'pp_catalog' );

	# Define the primary schema that holds our data:
	my $schema = make_a_child_node( 'schema', $catalog_bp, 'pp_catalog' );
	$schema->set_literal_attribute( 'si_name', 'gene' );
	$schema->set_node_ref_attribute( 'owner', $owner );

	# Define the table that holds our data:
	my $tb_person = make_a_child_node( 'table', $schema, 'pp_schema' );
	$tb_person->set_literal_attribute( 'si_name', 'person' );
	$tb_person->set_node_ref_attribute( 'row_data_type', $rdt_person );

	# Add more attributes to the 'person id' column of that table:
	my $tbf_person_id = make_a_child_node( 'table_field', $tb_person, 'pp_table' );
	$tbf_person_id->set_node_ref_attribute( 'si_row_field', $rdtf_person_id );
	$tbf_person_id->set_literal_attribute( 'mandatory', 1 );
	$tbf_person_id->set_literal_attribute( 'default_val', 1 );
	$tbf_person_id->set_literal_attribute( 'auto_inc', 1 );

	# Add more attributes to the 'person si_name' column of that table:
	my $tbf_person_name = make_a_child_node( 'table_field', $tb_person, 'pp_table' );
	$tbf_person_name->set_node_ref_attribute( 'si_row_field', $rdtf_person_name );
	$tbf_person_name->set_literal_attribute( 'mandatory', 1 );

	##### NEXT SET APPLICATION BLUEPRINT-TYPE DETAILS #####

	# Describe a utility application for managing our database schema:
	my $setup_app = make_a_node( 'application', $model );
	$setup_app->set_literal_attribute( 'si_name', 'Setup' );

	# Describe the data link that the utility app will use to talk to the database:
	my $setup_app_cl = make_a_child_node( 'catalog_link', $setup_app, 'pp_application' );
	$setup_app_cl->set_literal_attribute( 'si_name', 'admin_link' );
	$setup_app_cl->set_node_ref_attribute( 'target', $catalog_bp );

	# Describe a routine for setting up a database with our schema:
	my $rt_install = make_a_child_node( 'routine', $setup_app, 'pp_application' );
	$rt_install->set_literal_attribute( 'si_name', 'install_app_schema' );
	$rt_install->set_enumerated_attribute( 'routine_type', 'PROCEDURE' );
	my $rts_install = make_a_child_node( 'routine_stmt', $rt_install, 'pp_routine' );
	$rts_install->set_enumerated_attribute( 'call_sroutine', 'CATALOG_CREATE' );
	my $rte_install_a1 = make_a_child_node( 'routine_expr', $rts_install, 'pp_stmt' );
	$rte_install_a1->set_enumerated_attribute( 'call_sroutine_arg', 'LINK_BP' );
	$rte_install_a1->set_enumerated_attribute( 'cont_type', 'SRT_NODE' );
	$rte_install_a1->set_node_ref_attribute( 'actn_catalog_link', $setup_app_cl );
	my $rte_install_a2 = make_a_child_node( 'routine_expr', $rts_install, 'pp_stmt' );
	$rte_install_a2->set_enumerated_attribute( 'call_sroutine_arg', 'RECURSIVE' );
	$rte_install_a2->set_enumerated_attribute( 'cont_type', 'SCALAR' );
	$rte_install_a2->set_literal_attribute( 'valf_literal', 1 );
	$rte_install_a2->set_node_ref_attribute( 'scalar_data_type', $sdt_boolean );

	##### NEXT SET PRODUCT-TYPE DETAILS #####

	# ... TODO ...

	##### NEXT SET INSTANCE-TYPE DETAILS #####

	# ... TODO ...

	##### END OF DETAILS SETTING #####

	# Now check that we didn't omit something important:
	$model->assert_deferrable_constraints();

	# Now serialize all our Nodes to see if we stored what we expected:
	print $model->get_all_properties_as_xml_str();

	# Now explicitly destroy our Container so we don't leak memory:
	$model->destroy();
};

if( my $message = $@ ) {
	my $translator = Locale::KeyedText->new_translator( ['SQL::Routine::L::'], ['en'] );
	my $user_text = $translator->translate_message( $message );
	unless( $user_text ) {
		$user_text = ref($message) ? "internal error: can't find user text for a message: ".
			$message->as_string()." ".$translator->as_string() : $message;
	}
	print "SOMETHING'S WRONG: $user_text\n";
}

Real-life code would probably be more terse than the above example because it makes use of more wrapper functions (not provided here).

This is the serialization of the model that the above code sample makes:

<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="person_name" base_type="STR_CHAR" max_chars="100" char_enc="UTF8" />
		<row_data_type id="1" si_name="person">
			<row_data_type_field id="1" pp_row_data_type="1" si_name="person_id" scalar_data_type="1" />
			<row_data_type_field id="2" pp_row_data_type="1" si_name="si_name" scalar_data_type="2" />
		</row_data_type>
		<scalar_data_type id="3" si_name="boolean" base_type="BOOLEAN" />
	</elements>
	<blueprints>
		<catalog id="1" si_name="The Catalog Blueprint">
			<owner id="1" pp_catalog="1" />
			<schema id="1" pp_catalog="1" si_name="gene" owner="1">
				<table id="1" pp_schema="1" si_name="person" row_data_type="1">
					<table_field id="1" pp_table="1" si_row_field="1" mandatory="1" default_val="1" auto_inc="1" />
					<table_field id="2" pp_table="1" si_row_field="2" mandatory="1" />
				</table>
			</schema>
		</catalog>
		<application id="1" si_name="Setup">
			<catalog_link id="1" pp_application="1" si_name="admin_link" target="1" />
			<routine id="1" pp_application="1" si_name="install_app_schema" routine_type="PROCEDURE">
				<routine_stmt id="1" pp_routine="1" call_sroutine="CATALOG_CREATE">
					<routine_expr id="1" pp_stmt="1" call_sroutine_arg="LINK_BP" cont_type="SRT_NODE" actn_catalog_link="1" />
					<routine_expr id="2" pp_stmt="1" call_sroutine_arg="RECURSIVE" cont_type="SCALAR" valf_literal="1" scalar_data_type="3" />
				</routine_stmt>
			</routine>
		</application>
	</blueprints>
	<tools />
	<sites />
	<circumventions />
</root>

For a much larger and semi-complete example of SQL::Routine model-building code, and a serialization of the same, see the file t/lib/t_SQL_Routine.pm, which implements this distribution's main test suite.

For more additional code samples, try looking at the various modules that sub-class or use SQL::Routine. They tend to implement or use wrappers that make for much more compact code.

Comparative SQL Code Examples Generated From a Model

SQL::Routine works like an XML DOM except that it is restricted to holding specific kinds of data, which resemble SQL statements. This part of the SYNOPSIS shows some actual SQL statements that can be generated from selected portions of the model that is built by t/lib/t_SQL_Routine.pm .

This first set of Nodes describes 3 data types, 1 domain and 1 table, the latter 2 of which are conceptually named schema objects.

<scalar_data_type id="1" si_name="entity_id" base_type="NUM_INT" num_precision="9" />
<scalar_data_type id="2" si_name="person_name" base_type="STR_CHAR" max_chars="100" char_enc="UTF8" />
<row_data_type id="1" si_name="person">
	<row_data_type_field id="1" pp_row_data_type="1" si_name="person_id" scalar_data_type="1" />
	<row_data_type_field id="2" pp_row_data_type="1" si_name="name" scalar_data_type="2" />
	<row_data_type_field id="3" pp_row_data_type="1" si_name="father_id" scalar_data_type="1" />
	<row_data_type_field id="4" pp_row_data_type="1" si_name="mother_id" scalar_data_type="1" />
</row_data_type>

<row_domain id="1" pp_schema="1" si_name="person_type" data_type="1" />
<table id="1" pp_schema="1" si_name="person" row_domain="1">
	<table_field id="1" pp_table="1" si_row_field="1" mandatory="1" default_val="1" auto_inc="1" />
	<table_field id="2" pp_table="1" si_row_field="2" mandatory="1" />
	<table_index id="1" pp_table="1" si_name="primary" index_type="UNIQUE">
		<table_index_field id="1" pp_table_index="1" si_field="1" />
	</table_index>
	<table_index id="2" pp_table="1" si_name="fk_father" index_type="FOREIGN" f_table="1">
		<table_index_field id="2" pp_table_index="2" si_field="3" f_field="1" />
	</table_index>
	<table_index id="3" pp_table="1" si_name="fk_mother" index_type="FOREIGN" f_table="1">
		<table_index_field id="3" pp_table_index="3" si_field="4" f_field="1" />
	</table_index>
</table>

The above Node group has all the necessary details needed by external code to generate the following SQL statements. There are two versions of SQL given for the same task; the first one is for SQL:2003 compliant databases, that support DOMAIN schema objects; the second example is for older databases that do not. (Both of them use a MySQL extension AUTO_INCREMENT, but SQL generated for other databases would do the same thing in a different way.)

CREATE DOMAIN entity_id AS INTEGER(9);
CREATE DOMAIN person_name AS VARCHAR(100);
CREATE TABLE person (
	person_id entity_id NOT NULL DEFAULT 1 AUTO_INCREMENT,
	name person_name NOT NULL,
	father_id entity_id NULL,
	mother_id entity_id NULL,
	CONSTRAINT PRIMARY KEY (person_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,
	name VARCHAR(100) NOT NULL,
	father_id INTEGER(9) NULL,
	mother_id INTEGER(9) NULL,
	CONSTRAINT PRIMARY KEY (person_id),
	CONSTRAINT fk_father FOREIGN KEY (father_id) REFERENCES person (person_id),
	CONSTRAINT fk_mother FOREIGN KEY (mother_id) REFERENCES person (person_id)
);

Note that, regardless of which type of SQL is generated, the details for each data type, including its name, only need to be declared once, in 'scalar_domain' Nodes; if this one copy is changed, everything using it updates automatically.

This second set of Nodes describes a routine that takes 4 arguments (each of which is an actual argument if a named stored procedure is generated, or a named host parameter if un-named client-side SQL is generated) and performs an UPDATE query against one table record; the query takes 4 arguments, using one to match a record and 3 as new record column values to set.

<routine id="8" pp_application="2" si_name="update_a_person" routine_type="PROCEDURE">
	<routine_context id="5" pp_routine="8" si_name="conn_cx" cont_type="CONN" conn_link="2" />
	<routine_arg id="4" pp_routine="8" si_name="arg_person_id" cont_type="SCALAR" scalar_data_type="1" />
	<routine_arg id="5" pp_routine="8" si_name="arg_person_name" cont_type="SCALAR" scalar_data_type="2" />
	<routine_arg id="6" pp_routine="8" si_name="arg_father_id" cont_type="SCALAR" scalar_data_type="1" />
	<routine_arg id="7" pp_routine="8" si_name="arg_mother_id" cont_type="SCALAR" scalar_data_type="1" />
	<view id="3" pp_routine="8" si_name="update_a_person" view_type="UPDATE">
		<view_src id="3" pp_view="3" si_name="person" match_table="1">
			<view_src_field id="1" pp_src="3" si_match_field="1" />
			<view_src_field id="2" pp_src="3" si_match_field="2" />
			<view_src_field id="3" pp_src="3" si_match_field="3" />
			<view_src_field id="4" pp_src="3" si_match_field="4" />
		</view_src>
		<view_expr id="1" pp_view="3" view_part="SET" set_src_field="2" cont_type="SCALAR" valf_p_routine_arg="5" />
		<view_expr id="2" pp_view="3" view_part="SET" set_src_field="3" cont_type="SCALAR" valf_p_routine_arg="6" />
		<view_expr id="3" pp_view="3" view_part="SET" set_src_field="4" cont_type="SCALAR" valf_p_routine_arg="7" />
		<view_expr id="4" pp_view="3" view_part="WHERE" cont_type="SCALAR" valf_call_sroutine="EQ">
			<view_expr id="5" pp_expr="4" cont_type="SCALAR" valf_src_field="1" />
			<view_expr id="6" pp_expr="4" cont_type="SCALAR" valf_p_routine_arg="4" />
		</view_expr>
	</view>
	<routine_stmt id="9" pp_routine="8" call_sroutine="UPDATE">
		<routine_expr id="14" pp_stmt="9" call_sroutine_cxt="CONN_CX" cont_type="CONN" valf_p_routine_cxt="5" />
		<routine_expr id="15" pp_stmt="9" call_sroutine_arg="UPDATE_DEFN" cont_type="SRT_NODE" actn_view="3" />
	</routine_stmt>
</routine>

The above Node group, *together* with the previous Node group, has details to generate the following SQL statements. There are two versions of SQL given for the same task; the first one is for databases that support named bind variables, illustrated using the Oracle style of ':foo'; the second one is for those that require positional host parameters, illustrated with the DBI style of '?'. These two SQL variants are intended to be run by the SQL client.

UPDATE person
SET name = :arg_person_name, father_id = :arg_father_id, mother_id = :arg_mother_id
WHERE person_id = :arg_person_id;

UPDATE person
SET name = ?, father_id = ?, mother_id = ?
WHERE person_id = ?;

Alternately, if the primary-parent Node for above routine was a 'schema' rather than an 'application', then a server-side stored procedure (and calls to it) can be generated from the same SRT Node set, instead of a client-side anonymous routine. The two SQL variants are for new or old databases respectively, like the first example.

CREATE PROCEDURE update_a_person
(arg_person_id entity_id, arg_person_name person_name, arg_father_id entity_id, arg_mother_id entity_id)
BEGIN
	UPDATE person
	SET name = arg_person_name, father_id = arg_father_id, mother_id = arg_mother_id
	WHERE person_id = arg_person_id;
END;

CREATE PROCEDURE update_a_person
(arg_person_id INTEGER(9), arg_person_name VARCHAR(100), arg_father_id INTEGER(9), arg_mother_id INTEGER(9))
BEGIN
	UPDATE person
	SET name = arg_person_name, father_id = arg_father_id, mother_id = arg_mother_id
	WHERE person_id = arg_person_id;
END;

To go with those, here are SQL statements to invoke the server-side stored procedures from the client side, with the two variants being named-vs-positional host parameters.

CALL update_a_person (:arg_person_id, :arg_person_name, :arg_father_id, :arg_mother_id);

CALL update_a_person (?, ?, ?, ?);

Finally, all DROP statements can be generated from the same Nodes as CREATE.

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. So if you wanted to change the name of a table column, such as 'person_name' to 'the_name', then you make the change in exactly one place and all SQL generated from the model will update, both the CREATE and UPDATE statements. Alternately, if you wanted to change the data type of person ids, then you only have to make a single change, such as by setting num_precision to 6. Alternately, if you wanted to change the order of the arguments for 'update_a_person', you only have to change the order the 'routine_arg' Nodes appear, and any calls to the procedure will automatically re-order any passed values in the generated SQL.

See also the separately distributed Rosetta::Utility::SQLBuilder module, which is a reference implementation of a SQL 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.

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, 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.

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, 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.

MATTERS OF PORTABILITY AND FEATURES

SQL::Routines are intended to represent all kinds of SQL, both DML and DDL, both ANSI standard and RDBMS vendor extensions. Unlike basically all of the other SQL generating/parsing modules I know about, which are limited to basic DML and only support table definition DDL, this class supports arbitrarily complex select statements, with composite keys and unions, and calls to stored functions; this class can also define views and stored procedures and triggers. Some of the existing modules, even though they construct complete SQL, will take/require fragments of SQL as input (such as "where" clauses) By contrast, SQL::Routine takes no SQL fragments. All of its inputs are atomic, which means it is also easier to analyse the objects for implementing a wider range of functionality than previously expected; for example, it is much easier to analyse any select statement and generate update/insert/delete statements for the virtual rows fetched with it (a process known as updateable views).

Considering that each database product has its own dialect of SQL which it implements, you would have to code SQL differently depending on which database you are using. One common difference is the syntax for specifying an outer join in a select query. Another common difference is how to specify that a table column is an integer or a boolean or a character string. Moreover, each database has a distinct feature set, so you may be able to do tasks with one database that you can't do with another. In fact, some databases don't support SQL at all, but have similar features that are accessible thorough alternate interfaces. SQL::Routine is designed to represent a normalized superset of all database features that one may reasonably want to use. "Superset" means that if even one database supports a feature, you will be able to invoke it with this class. You can also reference some features which no database currently implements, but it would be reasonable for one to do so later. "Normalized" means that if multiple databases support the same feature but have different syntax for referencing it, there will be exactly one way of referring to it with SQL::Routine. So by using this class, you will never have to change your database-using code when moving between databases, as long as both of them support the features you are using (or they are emulated). That said, it is generally expected that if a database is missing a specific feature that is easy to emulate, then code which evaluates SQL::Routines will emulate it (for example, emulating "left()" with "substr()"); in such cases, it is expected that when you use such features they will work with any database. For example, if you want a model-specified BOOLEAN data type, you will always get it, whether it is implemented on a per-database-basis as a "boolean" or an "int(1)" or a "number(1,0)". Or a model-specified "STR_CHAR" data type you will always get it, whether it is called "text" or "varchar2" or "sql_varchar".

SQL::Routine is intended to be just a stateless container for database query or schema information. It does not talk to any databases by itself and it does not generate or parse any SQL; rather, it is intended that other third party modules or code of your choice will handle this task. In fact, SQL::Routine is designed so that many existing database related modules could be updated to use it internally for storing state information, including SQL generating or translating modules, and schema management modules, and modules which implement object persistence in a database. Conceptually speaking, the DBI module itself could be updated to take SQL::Routine objects as arguments to its "prepare" method, as an alternative (optional) to the SQL strings it currently takes. Code which implements the things that SQL::Routine describes can do this in any way that they want, which can mean either generating and executing SQL, or generating Perl code that does the same task and evaling it, should they want to (the latter can be a means of emulation). This class should make all of that easy.

SQL::Routine is especially suited for use with applications or modules that make use of data dictionaries to control what they do. It is common in applications that they interpret their data dictionaries and generate SQL to accomplish some of their work, which means making sure generated SQL is in the right dialect or syntax, and making sure literal values are escaped correctly. By using this module, applications can simply copy appropriate individual elements in their data dictionaries to SQL::Routine properties, including column names, table names, function names, literal values, host parameter names, and they don't have to do any string parsing or assembling.

Now, I can only imagine why all of the other SQL generating/parsing modules that I know about have excluded privileged support for more advanced database features like stored procedures. Either the authors didn't have a need for it, or they figured that any other prospective users wouldn't need it, or they found it too difficult to implement so far and maybe planned to do it later. As for me, I can see tremendous value in various advanced features, and so I have included privileged support for them in SQL::Routine. You simply have to work on projects of a significant size to get an idea that these features would provide a large speed, reliability, and security savings for you. Look at many large corporate or government systems, such as those which have hundreds of tables or millions of records, and that may have complicated business logic which governs whether data is consistent/valid or not. Within reasonable limits, the more work you can get the database to do internally, the better. I believe that if these features can also be represented in a database-neutral format, such as what SQL::Routine attempts to do, then users can get the full power of a database without being locked into a single vendor due to all their investment in vendor-specific SQL stored procedure code. If customers can move a lot more easily, it will help encourage database vendors to keep improving their products or lower prices to keep their customers, and users in general would benefit. So I do have reasons for trying to tackle the advanced database features in SQL::Routine.

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), and SQL::Routine::Node (aka Node).

While all 3 of the above classes are implemented in one module for convenience, you should consider all 3 names as being "in use"; do not create any modules or packages yourself that have the same names.

The Container and Node 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.

STRUCTURE

The internal structure of a SQL::Routine object is conceptually a cross between an XML DOM and an object-relational database, with a specific schema. This module is implemented with two main classes that work together, Containers and Nodes. The Container object is an environment or context in which Node objects usually live. A typical application will only need to create one Container object (returned by the module's 'new_container' function), and then a set of Nodes which live within that Container. The Nodes are related sometimes with single or multiple cardinality to each other.

SQL::Routine is expressly designed so that its data is easy to convert between different representations, mainly in-memory data structures linked by references, and multi-table record sets stored in relational databases, and node sets in XML documents. A Container corresponds to an XML document or a complete database, and each Node corresponds to an XML node or a database record. Each Node has a specific node_type (a case-sensitive string), which corresponds to a database table or an XML tag name. See the SQL::Routine::Language documentation file to see which ones exist. The node_type is set when the Node is created and it can not be changed later.

A Node has a specific set of allowed attributes that are determined by the node_type, each of which corresponds to a database table column or an XML node attribute. Every Node of a common node_type has a unique 'id' attribute (a positive integer) by which it is referenced; that attribute corresponds to the database table's single-column primary key. Each other Node attribute is either a scalar value of some data type, or an enumerated value, or a reference to another Node of a specific node_type, which has a foreign-key constraint on it. Foreign-key constraints are enforced by this module, so you will have to add Nodes in the appropriate order, just as when adding records to a database. Any Node which is referenced in an attribute (cited in a foreign-key constraint) of another is a parent of the other; as a corollary, the second Node is a child of the first. The order of child Nodes under a parent is the same as that in which the parent-child relationship was assigned, unless you have afterwards used the move_before_sibling() method to change this.

The order of child Nodes under a parent is often significant, so it is important to preserve this sequence explicitly if you store a Node set in an RDBMS, since databases do not consider record order to be significant or worth remembering; you would add extra columns to store sequence numbers. You do not have to do any extra work when storing Nodes in XML, however, because XML does consider node order to be significant and will preserve it.

When SQL::Routines are converted to XML, one referencing attribute is given higher precedence than the others and becomes the single parent XML node. For example, the XML parent of a 'routine_var' Node is always a 'routine' Node, even though a 'scalar_domain' Node may also be referenced. While Nodes of most types always have Nodes of a single other type as their parents, there are some exceptions. Nodes of certain types, such as [view|routine]_expr, may have either another Node of the same type as itself, or of a specific other type as its parent, depending on the context; these Nodes form trees of their own type, and it is the root Node of each tree which has a different Node type as its parent.

Finally, any Node of certain types will always have a specific pseudo-Node as its single parent, which it does not reference in an attribute, and which can not be changed. All 6 pseudo-Nodes have no attributes, even 'id', and only one of each exists; they are created by default with the Container they are part of, forming the top 2 levels of the Node tree, and can not be removed. They are: 'root' (the single level-1 Node which is parent to the other pseudo-Nodes but no normal Nodes), 'elements' (parent to 'scalar_data_type' and 'row_data_type' Nodes), 'blueprints' (parent to 'catalog' and 'application' Nodes), 'tools' (parent to 'data_storage_product' and 'data_link_product' Nodes), 'sites' (parent to 'catalog_instance' and 'application_instance' Nodes), and 'circumventions' (parent to 'sql_fragment' nodes). All other Node types have normal Nodes as parents.

You should look at the POD-only file named SQL::Routine::Language, which comes with this distribution. It serves to document all of the possible Node types, with attributes, constraints, and allowed relationships with other Node types. As the SQL::Routine class itself has very few properties and methods, all being highly generic (much akin to an XML DOM), the POD of this PM file will only describe how to use said methods, and will not list all the allowed inputs or constraints to said methods. With only simple guidance in Routine.pm, you should be able to interpret Language.pod to get all the nitty gritty details. You should also look at the tutorial or example files which will be in the distribution when ready. You could also learn something from the code in or with other modules which sub-class or use this one.

FAULT TOLERANCE AND MULTI-THREADING SUPPORT

Disclaimer: The following claims assume that only this module's published API is used, and that you do not set object properties directly or call private methods, which Perl does not prevent. It also assumes that the module is bug free, and that any errors or warnings which appear while the code is running are thrown explicitly by this module as part of its normal functioning.

SQL::Routine is designed to ensure that the objects it produces are always internally consistant, and that the data they contain is always well-formed, regardless of the circumstances in which it is used. You should be able to fetch data from the objects at any time and that data will be self-consistant and well-formed.

This will not change regardless of what kind of bad input data you provide to object methods or module functions. Providing bad input data will cause the module to throw an exception; if you catch this and the program continues running (such as to chide the user and have them try entering correct input), then the objects will remain un-corrupted and able to accept new input or give proper output. In most cases, the object will be in the same state as it was before the public method was called with the bad input.

This module does not use package variables at all, besides constants like $VERSION, and all symbols ($@%) declared at file level are strictly constant value declarations. No object should ever step on another.

This module will allow a Node to be created piecemeal, such as when it is storing details gathered one at a time from the user, and during this time some mandatory Node properties may not be set, or pending links from this node to others may not be validated. However, until a Node has its required properties set and/or its Node links are validated, no references will be made to this Node from other Nodes; from their point of view it doesn't exist, and hence the other Nodes are all consistant.

SQL::Routine is explicitly not thread-aware (thread-safe); it contains no code to synchronize access to its objects' properties, such as semaphores or locks or mutexes. To internalize such things in an effective manner would have made the code a lot more complex than it is now, without any clear benefits. However, this module can (and should) be used in multi-threaded environments where the application/caller code takes care of synchronizing access to its objects, especially if the application uses coarse-grained read or write locks.

The author's expectation is that this module will be mainly used in circumstances where the majority of actions are reads, and there are very few writes, such as with a data dictionary; perhaps all the writes on an object may be when it is first created. An application thread would obtain a read lock/semaphore on a Container object during the period for which it needs to ensure read consistency; it would block write lock attempts but not other read locks. It would obtain a write lock during the (usually short) period it needs to change something, which blocks all other lock attempts (for read or write).

An example of this is a web server environment where each page request is being handled by a distinct thread, and all the threads share one SQL::Routine object; normally the object is instantiated when the server starts, and the worker threads then read from it for guidance in using a common database. Occasionally a thread will want to change the object, such as to correspond to a simultaneous change to the database schema, or to the web application's data dictionary that maps the database to application screens. Under this situation, the application's definitive data dictionary (stored partly or wholly in a SQL::Routine) can occupy one place in RAM visible to all threads, and each thread won't have to keep looking somewhere else such as in the database or a file to keep up with the definitive copy. (Of course, any *changes* to the in-memory data dictionary should see a corresponding update to a non-volatile copy, like in an on-disk database or file.)

Note that, while a nice thing to do may be to manage a course-grained lock in SQL::Routine, with the caller invoking lock_to_read() or lock_to_write() or unlock() methods on it, Perl's thread->lock() mechanism is purely context based; the moment lock_to_...() returns, the object has unlocked again. Of course, if you know a clean way around this, I would be happy to hear it.

NODE EVOLUTION STATES

A SQL::Routine Node object always exists in one of 2 official ordered states (which can conceptually be divided further into more states). For now we can call them "Alone" (1) and "Well Known" (2). The set of legal operations you can perform on a Node are different depending on its state, and a Node can only transition between adjacent-numbered states one at a time.

Note: Up to 2004.09.13 there was a third state, "At Home", that was part way between "Alone" and "Well Known", but it was removed on that date.

When a new Node is created, using new_node(), it starts out "Alone"; it does *not* live in a Container, and it is illegal to have any actual (Perl) references between it and any other Node. Nodes in this state can be built (have their Node Id and other attributes set or changed) piecemeal with the least processing overhead, and can be moved or exist independently of anything else that SQL::Routine manages. An "Alone" Node does not need to have its Node Id set. Any Node attributes which are conceptually references to other Nodes are stored and read as Id numbers when the Node is "Alone"; also, no confirmation has yet taken place that the referenced Nodes actually exist yet. A Node may only be individually deleted when it is "Alone"; in this state it will be garbage collected like any Perl variable when your own reference to it goes away.

When you invoke the put_in_container() method on an "Alone" Node, giving it a Container object as an argument, the Node will transition to the "Well Known" state; you can move from "Well Known" to "Alone" using the complementary take_from_container() method. An "Well Known" Node lives in a Container, and any attributes which refer to other Nodes now must be actual references, where the existence of the other Node in the same Container is confirmed. If any conceptual references are set in a Node while it is "Alone", these will be converted into actual references by put_in_container(), which will fail if any can't be found; any other Nodes that this one references will now link back to it in their own child lists. The method take_from_container() will replace references with Node Ids, and remove the parent-to-child references. A Node can only link to a Node in the same Container as itself.

Testing for the existence of mandatory Node attribute values is separate from the official Node state and can be invoked on a Node at any time. None of the official Node states themselves will assert that any mandatory attributes are populated. This testing is separate partly to make it easy for you to build Nodes piecemeal, though there are other practical reasons for it.

Note that all typical Node attributes can be read, set, replaced, or cleared at any time regardless of the Node state; you can set them all either when the Node is "Alone" or when it is "Well Known", as is your choice. However, the Node Id must always have a value when the Node is in a Container; if you want to make a Node "Well Known" as early as possible, you simply have to set its Node Id first.

SYNTAX

This class does not export any functions or methods, so you need to call them using object notation. This means using Class->function() for functions and $object->method() for methods. If you are inheriting this class for your own modules, then that often means something like $self->method().

All SQL::Routine functions and methods are either "getters" (which read and return or generate values but do not change the state of anything) or "setters" (which change the state of something but do not return anything on success); none do getting or setting conditionally based on their arguments. While this means there are more methods in total, I see this arrangement as being more stable and reliable, plus each method is simpler and easier to understand or use; argument lists and possible return values are also less variable and more predictable.

All "setter" functions or methods which are supposed to change the state of something will throw an exception on failure (usually from being given bad arguments); on success, they officially have no return values. A thrown exception will always include details of what went wrong (and where and how) in a machine-readable (and generally human readable) format, so that calling code which catches them can recover gracefully. The methods are all structured so that they check all preconditions prior to changing any state information, and so one can assume that upon throwing an exception, the Node and Container objects are in a consistent or recoverable state at worst, and are completely unchanged at best.

All "getter" functions or methods will officially return the value or construct that was asked for; if said value doesn't (yet or ever) exist, then this means the Perl "undefined" value. When given bad arguments, generally this module's "information" functions will return the undefined value, and all the other functions/methods will throw an exception like the "setter" functions do.

Generally speaking, if SQL::Routine throws an exception, it means one of two things: 1. Your own code is not invoking it correctly, meaning you have something to fix; 2. You have decided to let it validate some of your input data for you (which is quite appropriate).

Note also that SQL::Routine is quite strict in its own argument checking, both for internal simplicity and robustness, and so that code which *reads* data from it can be simpler. If you want your own program to be more liberal in what input it accepts, then you will have to bear the burden of cleaning up or interpreting that input, or delegating such work elsewhere. (Or perhaps someone may want to make a wrapper module to do this?)

CONSTRUCTOR WRAPPER FUNCTIONS

These functions are stateless and can be invoked off of either the module name, or any package name in this module, or any object created by this module; they are thin wrappers over other methods and exist strictly for convenience.

new_container()

my $model = SQL::Routine->new_container();
my $model2 = SQL::Routine::Container->new_container();
my $model3 = SQL::Routine::Node->new_container();
my $model4 = $model->new_container();
my $model5 = $node->new_container();

This function wraps SQL::Routine::Container->new().

new_node( NODE_TYPE )

my $node = SQL::Routine->new_node( 'table' );
my $node2 = SQL::Routine::Container->new_node( 'table' );
my $node3 = SQL::Routine::Node->new_node( 'table' );
my $node4 = $model->new_node( 'table' );
my $node5 = $node->new_node( 'table' );

This function wraps SQL::Routine::Node->new( NODE_TYPE ).

CONTAINER CONSTRUCTOR FUNCTIONS AND METHODS

This function/method is stateless and can be invoked off of either the Container class name or an existing Container object, with the same result.

new()

my $model = SQL::Routine::Container->new();
my $model2 = $model->new();

This "getter" function/method will create and return a single SQL::Routine::Container (or subclass) object.

CONTAINER OBJECT METHODS

These methods are stateful and may only be invoked off of Container objects.

destroy()

$model->destroy();

This "setter" method will destroy the Container object that it is invoked from, and it will also destroy all of the Nodes inside that Container. This method exists because all Container objects (having 1 or more Node) contain circular references between the Container and all of its Nodes. You need to invoke this method when you are done with a Container, or you will leak the memory it uses when your external references to it go out of scope. This method can be invoked at any time and will not throw any exceptions. When it has completed, all external references to the Container or any of its Nodes will each point to an empty (but still blessed) Perl hash. See the CAVEATS documentation.

auto_assert_deferrable_constraints([ NEW_VALUE ])

This method returns this Container's "auto assert deferrable constraints" boolean property; if NEW_VALUE is defined, it will first set that property to it. When this flag is true, SQL::Routine's build_*() methods will automatically invoke assert_deferrable_constraints() on the newly created Node, if it is in this Container, prior to returning it. The use of this method helps isolate bad input bugs faster by flagging them closer to when they were created; it is especially useful with the build*tree() methods.

auto_set_node_ids([ NEW_VALUE ])

This method returns this Container's "auto set node ids" boolean property; if NEW_VALUE is defined, it will first set that property to it. When this flag is true, SQL::Routine will automatically generate and set a Node Id for a Node that lacks one as soon as there is an attempt to put that Node in this Container. When this flag is false, a missing Node Id will cause an exception to be raised instead.

use_abstract_interface([ NEW_VALUE ])

This method returns this Container's "use abstracts" boolean property; if NEW_VALUE is defined, it will first set that property to it. When this flag is true, SQL::Routine will accept a wider range of input values when setting Node ref attribute values, beyond Node object references and integers representing Node ids to look up; if other types of values are provided, SQL::Routine will try to look up Nodes based on other attributes than the Id, usually 'si_name', before giving up on finding a Node to link.

get_node( NODE_TYPE, NODE_ID )

my $catalog_node = $model->get_node( 'catalog', 1 );

This "getter" method returns a reference to one of this Container's member Nodes, which has a Node Type of NODE_TYPE, and a Node Id of NODE_ID. You may not request a pseudo-Node (it doesn't actually exist).

get_child_nodes([ NODE_TYPE ])

my $ra_node_list = $model->get_child_nodes();
my $ra_node_list = $model->get_child_nodes( 'catalog' );

This "getter" method returns a list of this Container's child Nodes, in a new array ref. A Container's child Nodes are defined as being all Nodes in the Container whose Node Type defines them as always having a pseudo-Node parent. If the optional argument NODE_TYPE is defined, then only child Nodes of that Node Type are returned; otherwise, all child Nodes are returned. All Nodes are returned in the same order they were added.

get_next_free_node_id( NODE_TYPE )

my $node_id = $model->get_next_free_node_id( 'catalog' );

This "getter" method returns an integer which is valid for use as the Node ID of a new Node, which has a Node Type of NODE_TYPE, that is going to be put in this Container. Its value is 1 higher than the highest Node ID for the same Node Type that is already in the Container, or had been before. You can use this method like a sequence generator to produce Node Ids for you rather than you producing them in some other way. An example situation when this method would be useful is if you are building a SQL::Routine by scanning the schema of an existing database.

deferrable_constraints_are_tested()

my $is_all_ok = $model->deferrable_constraints_are_tested();

This "getter" method will return the boolean "deferrable constraints are tested" property of this Container. This property is true when all "Well Known" Nodes in this Container are known to be free of all data errors, both individually and collectively. This property is initially set to true when a Container is new and empty; it is also set to true by Container.assert_deferrable_constraints() when all of its tests complete without finding any problems. This property is set to false when any changes are made to a "Well Known" Node in this Container, which includes moving the Node in to or out of "Well Known" status.

assert_deferrable_constraints()

$model->assert_deferrable_constraints();

This "getter" method implements several types of deferrable data validation, to make sure that every "Well Known" Node in this Container is ready to be used, both individually and collectively; it throws an exception if it can find anything wrong. Note that a failure with any one Node will cause the testing of the whole set to abort, as the offending Node throws an exception which this method doesn't catch; any untested Nodes could also have failed, so you will have to re-run this method after fixing the problem. This method will short-circuit and not perform any tests if this Container's "deferrable constraints are tested" property is true, so to avoid unnecessary repeated tests due to redundant external invocations; this allows you to put validation checks for safety everywhere in your program while avoiding a corresponding performance hit.

NODE CONSTRUCTOR FUNCTIONS AND METHODS

This function/method is stateless and can be invoked off of either the Node class name or an existing Node object, with the same result.

new( NODE_TYPE )

my $node = SQL::Routine::Node->new( 'table' );
my $node2 = $node->new( 'table' );

This "getter" function/method will create and return a single SQL::Routine::Node (or subclass) object whose Node Type is given in the NODE_TYPE (enum) argument, and all of whose other properties are defaulted to an "empty" state. A Node's type can only be set on instantiation and can not be changed afterwards; only specific values are allowed, which you can see in the SQL::Routine::Language documentation file. This new Node does not yet live in a Container, and will have to be put in one later before you can make full use of it. However, you can read or set or clear any or all of this new Node's attributes (including the Node Id) prior to putting it in a Container, making it easy to build one piecemeal before it is actually "used". A Node can not have any actual Perl references between it and other Nodes until it is in a Container, and as such you can delete it simply by letting your own reference to it be garbage collected.

NODE OBJECT METHODS

These methods are stateful and may only be invoked off of Node objects. For some of these, it doesn't matter whether the Node is in a Container or not. For others, this condition must be true or false for the method to be invoked, or it will throw an exception (like for bad input).

delete_node()

This "setter" method will destroy the Node object that it is invoked from, if it can. You are only allowed to delete Nodes that are not inside Containers, and which don't have child Nodes; failing this, you must remove the children and then take this Node from its Container first. Technically, this method doesn't actually do anything (pure-Perl version) other than validate that you are allowed to delete; when said conditions are met, the Node will be garbage collected as soon as you lose your reference to it.

get_node_type()

my $type = $node->get_node_type();

This "getter" method returns the Node Type scalar (enum) property of this Node. You can not change this property on an existing Node, but you can set it on a new one.

get_node_id()

This "getter" method will return the integral Node Id property of this Node, if it has one.

clear_node_id()

This "setter" method will erase this Node's Id property if it can. A Node's Id may only be cleared if the Node is not in a Container.

set_node_id( NEW_ID )

This "setter" method will set or replace this Node's Id property if it can. If this Node is in a Container, then the replacement will fail if some other Node with the same Node Type and Node Id already exists in the same Container.

expected_literal_attribute_type( ATTR_NAME )

This "getter" method will return an enumerated value that explains which literal data type that values for this Node's literal attribute named in the ATTR_NAME argument must be.

get_literal_attribute( ATTR_NAME )

This "getter" method will return the value for this Node's literal attribute named in the ATTR_NAME argument.

get_literal_attributes()

This "getter" method will fetch all of this Node's literal attributes, returning them in a Hash ref.

clear_literal_attribute( ATTR_NAME )

This "setter" method will clear this Node's literal attribute named in the ATTR_NAME argument.

clear_literal_attributes()

This "setter" method will clear all of this Node's literal attributes.

set_literal_attribute( ATTR_NAME, ATTR_VALUE )

This "setter" method will set or replace this Node's literal attribute named in the ATTR_NAME argument, giving it the new value specified in ATTR_VALUE.

set_literal_attributes( ATTRS )

This "setter" method will set or replace multiple Node literal attributes, whose names and values are specified by keys and values of the ATTRS hash ref argument; this method will invoke set_literal_attribute() for each key/value pair.

expected_enumerated_attribute_type( ATTR_NAME )

This "getter" method will return an enumerated value that explains which enumerated data type that values for this Node's enumerated attribute named in the ATTR_NAME argument must be.

get_enumerated_attribute( ATTR_NAME )

This "getter" method will return the value for this Node's enumerated attribute named in the ATTR_NAME argument.

get_enumerated_attributes()

This "getter" method will fetch all of this Node's enumerated attributes, returning them in a Hash ref.

clear_enumerated_attribute( ATTR_NAME )

This "setter" method will clear this Node's enumerated attribute named in the ATTR_NAME argument.

clear_enumerated_attributes()

This "setter" method will clear all of this Node's enumerated attributes.

set_enumerated_attribute( ATTR_NAME, ATTR_VALUE )

This "setter" method will set or replace this Node's enumerated attribute named in the ATTR_NAME argument, giving it the new value specified in ATTR_VALUE.

set_enumerated_attributes( ATTRS )

This "setter" method will set or replace multiple Node enumerated attributes, whose names and values are specified by keys and values of the ATTRS hash ref argument; this method will invoke set_enumerated_attribute() for each key/value pair.

expected_node_ref_attribute_type( ATTR_NAME )

This "getter" method will return an enumerated value that explains which Node Type that values for this Node's node attribute named in the ATTR_NAME argument must be.

get_node_ref_attribute( ATTR_NAME )

This "getter" method will return the value for this Node's node attribute named in the ATTR_NAME argument. The value will be a Node ref if the current Node is in a Container, and an Id number if it isn't.

get_node_ref_attributes()

This "getter" method will fetch all of this Node's node attributes, returning them in a Hash ref. The values will be Node refs if the current Node is in a Container, and Id numbers if it isn't.

clear_node_ref_attribute( ATTR_NAME )

This "setter" method will clear this Node's node attribute named in the ATTR_NAME argument; the other Node being referred to will also have its child list reciprocal link to the current Node cleared.

clear_node_ref_attributes()

This "setter" method will clear all of this Node's node attributes; see the clear_node_ref_attribute() documentation for the semantics.

set_node_ref_attribute( ATTR_NAME, ATTR_VALUE )

This "setter" method will set or replace this Node's node attribute named in the ATTR_NAME argument, giving it the new value specified in ATTR_VALUE (if it is different). If the attribute was previously valued, this method will first invoke clear_node_ref_attribute() on it. When setting a new value, if the current Node is in a Container, then it will also add the current Node to the other Node's child list.

set_node_ref_attributes( ATTRS )

This "setter" method will set or replace multiple Node node attributes, whose names and values are specified by keys and values of the ATTRS hash ref argument; this method will invoke set_node_ref_attribute() for each key/value pair.

expected_attribute_major_type( ATTR_NAME )

This "getter" method will return an enumerated value that explains which major data type that values for this Node's attribute named in the ATTR_NAME argument must be. There are 4 possible return values: 'ID' (the Node Id), 'LITERAL' (a literal attribute), 'ENUM' (an enumerated attribute), and 'NODE' (a node ref attribute).

get_attribute( ATTR_NAME )

my $curr_val = $node->get_attribute( 'si_name' );

This "getter" method will return the value for this Node's attribute named in the ATTR_NAME argument.

get_attributes()

my $rh_attrs = $node->get_attributes();

This "getter" method will fetch all of this Node's attributes, returning them in a Hash ref.

clear_attribute( ATTR_NAME )

This "setter" method will clear this Node's attribute named in the ATTR_NAME argument.

clear_attributes()

This "setter" method will clear all of this Node's attributes.

set_attribute( ATTR_NAME, ATTR_VALUE )

This "setter" method will set or replace this Node's attribute named in the ATTR_NAME argument, giving it the new value specified in ATTR_VALUE.

set_attributes( ATTRS )

$node->set_attributes( $rh_attrs );

This "setter" method will set or replace multiple Node attributes, whose names and values are specified by keys and values of the ATTRS hash ref argument; this method will invoke set_attribute() for each key/value pair.

get_pp_node_attribute_name()

This "getter" method returns the name of this Node's node attribute which is designated to reference this Node's primary parent Node, if there is one.

get_pp_node()

my $parent = $node->get_pp_node();

This "getter" method returns the primary parent Node of the current Node, if there is one. The semantics are like "if the current Node is in a Container and its 'parent node attribute name' is defined, then return the Node ref value of the named node attribute, if it has one".

clear_pp_node_attribute_name()

This "setter" method will clear this Node's 'primary pparent node attribute name' property, if it has one. The actual node attribute being referred to is not affected.

set_pp_node_attribute_name( ATTR_NAME )

This "setter" method will set or replace this Node's 'primary parent node attribute name' property, giving it the new value specified in ATTR_NAME. No actual node attribute is affected. Note that only a subset (usually one) of a Node's node attributes may be named as the holder of its primary parent.

get_first_candidate_pp_node_attribute_name()

This "getter" method will look at each primary-parent candidate attribute in the current Node and return the name of the first one that is valued; it will return the undefined value if no candidates are valued.

estimate_pp_node_attribute_name( NEW_PARENT[, ONLY_NOT_VALUED] )

This "getter" method will try to find a way to make the Node given in its NEW_PARENT argument into the primary parent of the current Node. It returns the name of the first appropriate Node attribute which takes a Node of the same Node Type as NEW_PARENT; if one can not be found, the undefined value is returned. By default, the current value of the found attribute is ignored; but if the optional argument ONLY_NOT_VALUED is true, then an otherwise acceptible attribute name will not be returned if it already has a value.

get_container()

my $model = $node->get_container();

This "getter" method returns the Container object which this Node lives in, if any.

put_in_container( NEW_CONTAINER )

This "setter" method will put the current Node into the Container given as the NEW_CONTAINER argument if it can, which moves the Node from "Alone" to "Well Known" status.

take_from_container()

This "setter" method will take the current Node from its Container if it can, which moves the Node from "Well Known" to "Alone" status.

move_before_sibling( SIBLING[, PARENT] )

This "setter" method allows you to change the order of child Nodes under a common parent Node; specifically, it moves the current Node to a position just above/before the sibling Node specified in the SIBLING Node ref argument, if it can. You can only invoke it on a Node that is "Well Known", since that is the only time it exists in its parent's child list at all. Since a Node can have multiple parent Nodes (and the sibling likewise), the optional PARENT argument lets you specify which parent's child list you want to move in; if you do not provide an PARENT value, then the current Node's primary parent Node is used, if possible. This method will throw an exception if the current Node and the specified sibling or parent Nodes are not appropriately related to each other (parent <-> child). If you want to move the current Node to follow the sibling instead, then invoke this method on the sibling.

get_child_nodes([ NODE_TYPE ])

my $ra_node_list = $node->get_child_nodes();
my $ra_node_list = $node->get_child_nodes( 'table' );

This "getter" method returns a list of this object's child Nodes, in a new array ref. If the optional argument NODE_TYPE is defined, then only child Nodes of that Node Type are returned; otherwise, all child Nodes are returned. All Nodes are returned in the same order they were added.

add_child_node( NEW_CHILD )

$node->add_child_node( $child );

This "setter" method allows you to add a new child Node to this object, which is provided as the single NEW_CHILD Node ref argument. The new child Node is appended to the list of existing child Nodes, and the current Node becomes the new or first primary parent Node of NEW_CHILD.

add_child_nodes( LIST )

$model->add_child_nodes( [$child1,$child2] );
$model->add_child_nodes( $child );

This "setter" method takes an array ref in its single LIST argument, and calls add_child_node() for each element found in it.

assert_deferrable_constraints()

This "getter" method implements several types of deferrable data validation, to make sure that this Node is ready to be used; it throws an exception if it can find anything wrong. This method can be used on any Node regardless of its current node evolution state, but that state does affect which tests are performed; "Well Known" Nodes get all the tests, while "Alone" Nodes skip some.

CONTAINER OR NODE METHODS FOR DEBUGGING

The following 3 "getter" methods can be invoked either on Container or Node objects, and will return a tree-arranged structure having the contents of a Node and all its children (to the Nth generation). The previous statement assumes that all the 'children' are in the same Container, which means that a Node's parent is aware of it; if a child is not in the Container, the assumption is that said Node is still being constructed, and neither it nor its children will be included in the output. If you invoke the 3 methods on a Node, then that Node will be the root of the returned structure. If you invoke them on a Container, then a few pseudo-Nodes will be output with all the normal Nodes in the Container as their children.

get_all_properties()

$rh_node_properties = $node->get_all_properties();
$rh_node_properties = $container->get_all_properties();

This method returns a deep copy of all of the properties of this object as non-blessed Perl data structures. These data structures are also arranged in a tree, but they do not have any circular references. The main purpose, currently, of get_all_properties() is to make it easier to debug or test this class; it makes it easier to see at a glance whether the other class methods are doing what you expect. The output of this method should also be easy to serialize or unserialize to strings of Perl code or xml or other things, should you want to compare your results easily by string compare (see "get_all_properties_as_perl_str()" and "get_all_properties_as_xml_str()").

get_all_properties_as_perl_str([ NO_INDENTS ])

$perl_code_str = $container->get_all_properties_as_perl_str();
$perl_code_str = $container->get_all_properties_as_perl_str( 1 );
$perl_code_str = $node->get_all_properties_as_perl_str();
$perl_code_str = $node->get_all_properties_as_perl_str( 1 );

This method is a wrapper for get_all_properties() that serializes its output into a pretty-printed string of Perl code, suitable for humans to read. You should be able to eval this string and produce the original structure. By default, contents of lists are indented under the lists they are in (easier to read); if the optional boolean argument NO_INDENTS is true, then all output lines will be flush with the left, saving a fair amount of memory in what the resulting string consumes. (That said, even the indents are tabs, which take up much less space than multiple spaces per indent level.)

get_all_properties_as_xml_str([ NO_INDENTS ])

$xml_doc_str = $container->get_all_properties_as_xml_str();
$xml_doc_str = $container->get_all_properties_as_xml_str( 1 );
$xml_doc_str = $node->get_all_properties_as_xml_str();
$xml_doc_str = $node->get_all_properties_as_xml_str( 1 );

This method is a wrapper for get_all_properties() that serializes its output into a pretty-printed string of XML, suitable for humans to read. By default, child nodes are indented under their parent nodes (easier to read); if the optional boolean argument NO_INDENTS is true, then all output lines will be flush with the left, saving a fair amount of memory in what the resulting string consumes. (That said, even the indents are tabs, which take up much less space than multiple spaces per indent level.)

CONTAINER OR NODE FUNCTIONS AND METHODS FOR RAPID DEVELOPMENT

The following 7 "setter" functions and methods should assist more rapid development of code that uses SQL::Routine, at the cost that the code would run a bit slower (SQL::Routine has to search for info behind the scenes that it would otherwise get from you). These methods are implemented as wrappers over other SQL::Routine methods, and allow you to accomplish with one method call what otherwise requires about 4-10 method calls, meaning your code base is significantly smaller (unless you implement your own simplifying wrapper functions, which is recommended in some situations).

For convenience, these methods can take both positional and named arguments; if the first actual positional argument is a Perl hash-ref, then the method assumes it contains all the arguments in named format; otherwise, the method assumes all of the actual positional arguments are the arguments. All of the argument names are uppercased strings that are identical to what is shown in the positional-oriented argument list documentation.

Note that when a subroutine is referred to as a "function", it is stateless and can be invoked off of either a class name or class object; when a subroutine is called a "method", it can only be invoked off of Container or Node objects.

build_lonely_node( NODE_TYPE[, ATTRS][, PP_ATNM] )

my $nodeP = SQL::Routine->build_lonely_node( 'catalog', { 'id' => 1, } ); 
my $nodeN = SQL::Routine->build_lonely_node( 
	{ 'NODE_TYPE' => 'catalog', 'ATTRS' => { 'id' => 1, } } ); 

This function will create and return a new Node that is "Alone" (not in a Container), whose type is specified in NODE_TYPE, and also set its attributes. The ATTRS argument is processed by Node.set_attributes() if it is provided; a Node id can also be provided this way, or the Node id won't be set. The PP_ATNM is processed by Node.set_pp_node_attribute_name() if provided; if not, then Node.get_first_candidate_pp_node_attribute_name() will be called to find a suitable parent from the newly set ATTRS, and then set the PP if one is found.

build_node( NODE_TYPE[, ATTRS][, PP_ATNM] )

This method is like build_lonely_node() except that it must be invoked off of a Container, or a Node that is in a Container, and the newly created Node will be put in that Container. This method will throw an exception if it is invoked on a Node that is not in a Container.

build_child_node( NODE_TYPE[, ATTRS] )

This method is like build_node() except that it will set the new Node's primary parent to be the Node that this method was invoked on, using add_child_node(); if this method was invoked on a Container, then it will work only for new Nodes that would have a pseudo-Node as their primary parent. When creating a Node with this method, you do not set any PP candidates in ATTRS.

build_child_nodes( LIST )

This method takes an array ref in its single LIST argument, and calls build_child_node() for each element found in it; if LIST is not an array ref, then one is constructed with LIST as its single element. This method does not return anything.

build_child_node_tree( NODE_TYPE[, ATTRS][, CHILDREN] )

This method is like build_child_node() except that it will recursively create all of the child Nodes of the new Node as well; CHILDREN is a Perl array-ref (or, if defined, it will become the single element of a new array-ref), and build_child_node_tree() will be called for each of its elements after their parent has been fully created. In the context of SQL::Routine, a "Node tree" or "tree" consists of one arbitrary Node and all of its "descendants". If invoked on a Container object, this method will recognize any pseudo-Node names given in 'NODE_TYPE' and simply move on to creating the child Nodes of that pseudo-Node, rather than throwing an error exception for an invalid Node type. Therefore, you can populate a whole Container with one call to this method. This method returns the root Node that it creates, if NODE_TYPE was a valid Node type; it returns the Container instead if NODE_TYPE is a pseudo-Node name.

build_child_node_trees( LIST )

This method takes an array ref in its single LIST argument, and calls build_child_node_tree() for each element found in it; if LIST is not an array ref, then one is constructed with LIST as its single element. This method does not return anything.

build_container( NODE_TYPE[, ATTRS][, CHILDREN] )

This function is like build_child_node_tree() except that it will create a new Container object, which is returned, and populate it with new Node objects that are generated from its arguments. This function is the exact opposite of Container.get_all_properties(); you should be able to take the Hash-ref output of Container.get_all_properties(), give it to build_container(), and end up with a clone of the original Container.

INFORMATION FUNCTIONS AND METHODS

These "getter" functions/methods are all intended for use by programs that want to dynamically interface with SQL::Routine, especially those programs that will generate a user interface for manual editing of data stored in or accessed through SQL::Routine constructs. It will allow such programs to continue working without many changes while SQL::Routine itself continues to evolve. In a manner of speaking, these functions/methods let a caller program query as to what 'schema' or 'business logic' drive this class. These functions/methods are all deterministic and stateless; they can be used in any context and will always give the same answers from the same arguments, and no object properties are used. You can invoke them from any kind of object that SQL::Routine implements, or straight off of the class name itself, like a 'static' method. All of these functions return the undefined value if they match nothing.

valid_enumerated_types([ ENUM_TYPE ])

This function by default returns a list of the valid enumerated types that SQL::Routine recognizes; if the optional ENUM_TYPE argument is given, it just returns true if that matches a valid type, and false otherwise.

valid_enumerated_type_values( ENUM_TYPE[, ENUM_VALUE] )

This function by default returns a list of the values that SQL::Routine recognizes for the enumerated type given in the ENUM_TYPE argument; if the optional ENUM_VALUE argument is given, it just returns true if that matches an allowed value, and false otherwise.

valid_node_types([ NODE_TYPE ])

This function by default returns a list of the valid Node Types that SQL::Routine recognizes; if the optional NODE_TYPE argument is given, it just returns true if that matches a valid type, and false otherwise.

valid_node_type_literal_attributes( NODE_TYPE[, ATTR_NAME] )

This function by default returns a Hash ref where the keys are the names of the literal attributes that SQL::Routine recognizes for the Node Type given in the NODE_TYPE argument, and where the values are the literal data types that values for those attributes must be; if the optional ATTR_NAME argument is given, it just returns the literal data type for the named attribute.

valid_node_type_enumerated_attributes( NODE_TYPE[, ATTR_NAME] )

This function by default returns a Hash ref where the keys are the names of the enumerated attributes that SQL::Routine recognizes for the Node Type given in the NODE_TYPE argument, and where the values are the enumerated data types that values for those attributes must be; if the optional ATTR_NAME argument is given, it just returns the enumerated data type for the named attribute.

valid_node_type_node_ref_attributes( NODE_TYPE[, ATTR_NAME] )

This function by default returns a Hash ref where the keys are the names of the node attributes that SQL::Routine recognizes for the Node Type given in the NODE_TYPE argument, and where the values are the Node Types that values for those attributes must be; if the optional ATTR_NAME argument is given, it just returns the Node Type for the named attribute.

major_type_of_node_type_attribute( NODE_TYPE, ATTR_NAME )

This "getter" function returns the major type for the attribute of NODE_TYPE Nodes named ATTR_NAME, which is one of 'ID', 'LITERAL', 'ENUM' or 'NODE'.

valid_node_type_parent_attribute_names( NODE_TYPE[, ATTR_NAME] )

This function by default returns an Array ref which lists the names of the node attributes that are allowed to reference the primary parent of a Node whose type is specified in the NODE_TYPE argument; if the optional ATTR_NAME argument is given, it just returns true the named attribute may reference the primary parent of a NODE_TYPE Node.

node_types_with_pseudonode_parents([ NODE_TYPE ])

This function by default returns a Hash ref where the keys are the names of the Node Types whose primary parents can only be pseudo-Nodes, and where the values name the pseudo-Nodes they are the children of; if the optional NODE_TYPE argument is given, it just returns the pseudo-Node for that Node Type.

ABOUT THE OPTIONAL ABSTRACT INTERFACE

If you set certain boolean properties on a Container object to true (they all default to false), then subsequently that Container and its Nodes will be less strict with regards to what input data formats their methods accept, in some specific ways. The following paragraphs outline that further.

This feature set is strictly an extension, meaning that if you provide it input which would be acceptable to the stricter default interface, then you will get the same behaviour. Where you will see the difference is when you provide certain kinds of input which would cause the parent class to return an error and/or throw an exception.

If you set Container.auto_set_node_ids() to true, then this module will automatically generate (by serial number) a new Node's "id" attribute when your input doesn't provide one. If you set Container.use_abstract_interface() to true, then, when you want to refer to an earlier created Node by a later one, for purposes of linking them, you can refer to the earlier Node by a more human-readable attribute than the Node's "id" (or Node ref), such as its 'si_name' (which is also what actual SQL uses). Between these two features, it is possible to use SQL::Routine without ever having to explicitly see a Node's "id" attribute.

Note that, for the sake of avoiding conflicts, you should not be explicitly setting ids for some Nodes of a type, and having others auto-generated, unless you take extra precautions. This is because while auto-generated Node ids will not conflict with prior explicit ones, later provided explicit ones may conflict with auto-generated ones. How you can resolve this is to use the parent class' get_node() method to see if the id you want is already in use. The same caveats apply as if the auto-generator was a second concurrent user editing the object. This said, you can mix references from one Node to another between id and non-id ref types without further consequence, because they don't change the id of a Node.

This module's added features can make it "easier to use" in some circumstances than the bare-bones SQL::Routine, including an appearance more like actual SQL strings, because matching descriptive terms can be used in multiple places.

However, the functionality has its added cost in code complexity and reliability; for example, since non-id attributes are not unique, the module can "guess wrong" about what you wanted to do, and it won't work at all in some circumstances. Additionally, since your code, by using this module, would use descriptive attributes to link Nodes together, you will have to update every place you use the attribute value in your module-building source code when you change the original, so they continue to match; this is unlike the default interface, which always uses non-descriptive attributes for links, which you are unlikely to ever change. The added logic also makes the code slower and use more memory.

BUGS

This module is currently in pre-alpha development status, meaning that some parts of it will be changed in the near future, perhaps in incompatible ways; however, I believe that any further incompatible changes will be small. The current state is analagous 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. All of this said, I plan to move this module into alpha development status within the next few releases, once I start using it in a production environment myself.

The abstract interface code and related data-dictionary has not been rewritten after its insertion from another merged-in module. While it should be rewritten soon, in the mean time its features will not work for many Node types; mainly it just works for data types, tables, and views.

CAVEATS

All SQL::Routine::Container objects contain circular references by design (or more specifically, when 1 or more Node is in one). When you are done with a Container object, you should explicitly call its "destroy()" method prior to letting your references to it go out of scope, or you will leak the memory it used. Note that some early versions of SQL::Routine had wrapped the actual Container object in a second object that was auto-destroyed when it went out of scope, but this cludge was later removed due to adding worse problems than it solved, such as Containers being destroyed too early.

SEE ALSO

perl(1), SQL::Routine::L::en, SQL::Routine::Language, SQL::Routine::API_C, Locale::KeyedText, Rosetta, Rosetta::Engine::Generic, Rosetta::Utility::SQLBuilder, Rosetta::Utility::SQLParser, DBI, SQL::Statement, 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.

cpanm SQL::Routine

perl -MCPAN -e shell
install SQL::Routine