NAME
SQL::Routine - Specify all database tasks with SQL routines
DEPENDENCIES
Perl Version: 5.008001
Core Modules: none
Non-Core Modules:
Locale::KeyedText 1.02 (for error messages)COPYRIGHT AND LICENSE
This file is part of the SQL::Routine library (libSQLRT).
SQL::Routine is Copyright (c) 1999-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) 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
The previous SYNOPSIS was removed; a new one will be written later.
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 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), 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.
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( NODE_TYPE )CONTAINER CONSTRUCTOR FUNCTIONS AND METHODS:
new()CONTAINER OBJECT METHODS:
destroy()
auto_assert_deferrable_constraints([ NEW_VALUE ])
auto_set_node_ids([ NEW_VALUE ])
may_match_surrogate_node_ids([ NEW_VALUE ])
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()
deferrable_constraints_are_tested()
assert_deferrable_constraints()NODE CONSTRUCTOR FUNCTIONS AND METHODS:
new( NODE_TYPE )NODE OBJECT METHODS:
delete_node()
get_node_type()
get_node_id()
clear_node_id()
set_node_id( NEW_ID )
get_primary_parent_attribute()
clear_primary_parent_attribute()
set_primary_parent_attribute( ATTR_VALUE )
get_literal_attribute( ATTR_NAME )
get_literal_attributes()
clear_literal_attribute( ATTR_NAME )
clear_literal_attributes()
set_literal_attribute( ATTR_NAME, ATTR_VALUE )
set_literal_attributes( ATTRS )
get_enumerated_attribute( ATTR_NAME )
get_enumerated_attributes()
clear_enumerated_attribute( ATTR_NAME )
clear_enumerated_attributes()
set_enumerated_attribute( ATTR_NAME, ATTR_VALUE )
set_enumerated_attributes( ATTRS )
get_node_ref_attribute( ATTR_NAME[, GET_TARGET_SI] )
get_node_ref_attributes([ GET_TARGET_SI ])
clear_node_ref_attribute( ATTR_NAME )
clear_node_ref_attributes()
set_node_ref_attribute( ATTR_NAME, ATTR_VALUE )
set_node_ref_attributes( ATTRS )
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 )
get_container()
put_in_container( NEW_CONTAINER )
take_from_container()
move_before_sibling( SIBLING[, PARENT] )
get_child_nodes([ NODE_TYPE ])
add_child_node( NEW_CHILD )
add_child_nodes( LIST )
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()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_lonely_node( NODE_TYPE[, ATTRS] )
build_node( NODE_TYPE[, ATTRS] )
build_child_node( NODE_TYPE[, ATTRS] )
build_child_nodes( LIST )
build_child_node_tree( NODE_TYPE[, ATTRS][, CHILDREN] )
build_child_node_trees( LIST )
build_container( LIST[, AUTO_ASSERT[, AUTO_IDS[, MATCH_SURR_IDS]]] )INFORMATION FUNCTIONS AND METHODS:
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
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. In the future, we may try to use a "weak reference" adjunct feature to Perl to remove the need for explicit destruction in a more elegant fashion, but not necessarily.
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_node_ref_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".
SEE ALSO
perl(1), SQL::Routine::L::en, SQL::Routine::Details, SQL::Routine::Language, SQL::Routine::EnumTypes, SQL::Routine::NodeTypes, SQL::Routine::API_C, Locale::KeyedText, Rosetta, Rosetta::Engine::Generic, SQL::Routine::SQLBuilder, SQL::Routine::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.