NAME
Rosetta::Engine::Generic - A catch-all Engine for any DBI-supported SQL database
DEPENDENCIES
Perl Version: 5.006
Standard Modules: none
Nonstandard Modules:
Rosetta 0.38
DBI 1.45 (highest version recommended)
Rosetta::Utility::SQLBuilder 0.12COPYRIGHT AND LICENSE
This file is part of the Rosetta::Extensions collection of Rosetta feature reference implementations.
Rosetta::Extensions is Copyright (c) 1999-2004, Darren R. Duncan. All rights reserved. Address comments, suggestions, and bug reports to perl@DarrenDuncan.net, or visit "http://www.DarrenDuncan.net" for more information.
Rosetta::Extensions is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License (GPL) version 2 as published by the Free Software Foundation (http://www.fsf.org/). You should have received a copy of the GPL as part of the Rosetta::Extensions distribution, in the file named "LICENSE"; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
Linking Rosetta::Extensions statically or dynamically with other modules is making a combined work based on Rosetta::Extensions. Thus, the terms and conditions of the GPL cover the whole combination. As a special exception, the copyright holders of Rosetta::Extensions give you permission to link Rosetta::Extensions with independent modules, regardless of the license terms of these independent modules, and to copy and distribute the resulting combined work under terms of your choice, provided that every copy of the combined work is accompanied by a complete copy of the source code of Rosetta::Extensions (the version of Rosetta::Extensions used to produce the combined work), being distributed under the terms of the GPL plus this exception. An independent module is a module which is not derived from or based on Rosetta::Extensions, and which is fully useable when not linked to Rosetta::Extensions in any form.
Any versions of Rosetta::Extensions that you modify and distribute must carry prominent notices stating that you changed the files and the date of any changes, in addition to preserving this original copyright notice and other credits. Rosetta::Extensions is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
While it is by no means required, the copyright holders of Rosetta::Extensions would appreciate being informed any time you create a modified version of Rosetta::Extensions that you are willing to distribute, because that is a practical way of suggesting improvements to the standard version.
SYNOPSIS
Note: This SYNOPSIS is already out of date. It will be fixed later.
This is an example showing about the minimum amount of code required to go from nothing to an open database connection using Rosetta::Engine::Generic, or any standard Rosetta Engine module for that matter (about 40 lines of code).
# First load our basic requirements, Rosetta and SQL::Routine.
use Rosetta;
eval {
# Now create a schema model we will use everywhere.
my $model = SQL::Routine->new_container();
# Now create the model root node representing the app running right now / that we are.
my $app_bp = make_a_node( 'application', $model );
# Define an application instance, for testers, which is the app running right now.
my $test_app = make_a_node( 'application_instance', $model );
$test_app->set_node_ref_attribute( 'blueprint', $app_bp );
# Now create a new Rosetta Interface tree that will mediate db access for us.
my $application = Rosetta->new_application( $test_app );
# Now create the model root node that represents a database/catalog we will
# be using (may be several), and a node belonging to said app representing a
# yet-unrealized data connection from the app to the db.
my $catalog_bp = make_a_node( 'catalog', $model );
my $app_cl = make_a_child_node( 'catalog_link', $app_bp, 'pp_application' );
$app_cl->set_literal_attribute( 'name', 'big_data' );
$app_cl->set_node_ref_attribute( 'target', $catalog_bp );
# ... Next, probably (or you can do it later), make and stuff a whole bunch
# of nodes in the model that describe the database/catalog schema and any
# application-stored queries or routines that would run against the db.
# As an example of the above, a command to 'connect' to the database.
my $open_cmd = make_a_child_node( 'command', $app_bp, 'pp_application' );
$open_cmd->set_enumerated_attribute( 'standard_routine', 'CATALOG_OPEN' );
my $open_cmd_a1 = make_a_child_node( 'command_arg', $open_cmd, 'pp_command' );
$open_cmd_a1->set_node_ref_attribute( 'catalog_link', $app_cl );
# Now create the node that says we will use Rosetta::Engine::Generic as
# our data link product to talk to some database.
my $dlp = make_a_node( 'data_link_product', $model );
$dlp->set_literal_attribute( 'product_code', 'Rosetta::Engine::Generic' );
# Now create the model node that says what data storage product we will
# use to implement/host the catalog/database, Oracle 9i in this case.
# The string 'Oracle_9_i' is something that Rosetta::Engine::Generic
# and/or its supporting modules specifically recognize.
my $dsp = make_a_node( 'data_storage_product', $model );
$dsp->set_literal_attribute( 'product_code', 'Oracle_9_i' );
$dsp->set_literal_attribute( 'is_network_svc', 1 );
# Define a database catalog instance, in this case, an Oracle db for testers.
my $test_db = make_a_node( 'catalog_instance', $model );
$test_db->set_node_ref_attribute( 'product', $dsp );
$test_db->set_node_ref_attribute( 'blueprint', $catalog_bp );
# Now realize the data connection to run from the app instance to the catalog instance.
# This is where we say that the app running right now (we) will use Rosetta::Engine::Generic.
# Each application instance may only have 1 realization of the same unrealized link.
my $test_app_cl = make_a_child_node( 'catalog_link_instance', $test_app, 'pp_application' );
$test_app_cl->set_node_ref_attribute( 'product', $dlp );
$test_app_cl->set_node_ref_attribute( 'unrealized', $app_cl );
$test_app_cl->set_node_ref_attribute( 'target', $test_db );
$test_app_cl->set_literal_attribute( 'local_dsn', 'test' );
# Now tell Rosetta to get ready to open a connection to the catalog/database.
# This is when Perl will actually try to "require" Rosetta::Engine::Generic,
# compiling it and DBI, and load the DBD module if possible.
my $prepared_open_cmd = $application->prepare( $open_cmd );
# Now we will actually call DBI->connect() and related stuff; this will cause a new
# Rosetta Interface to be returned that fronts the resulting DBI database handle.
# The host params 'login_name/pass' are specifically recognized by the CATALOG_OPEN command.
my $db_conn = $prepared_open_cmd->execute( { 'login_name' => 'jane', 'login_pass' => 'pawd' } );
# ... Now we do whatever else we wanted to do with the database, such as running queries.
};
if( my $message = $@ ) {
if( ref($message) and UNIVERSAL::isa( $message, 'Rosetta::Interface' ) ) {
$message = $message->get_error_message();
}
my $translator = Locale::KeyedText->new_translator(
['Rosetta::Engine::Generic::L::','Rosetta::L::','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";
}
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 );
}If $model were dumped right now as an XML string, after running the actual code above, it would look like this:
<root>
<blueprints>
<application id="1">
<catalog_link id="1" application="1" name="big_data" target="1" />
<command id="1" application="1" standard_routine="CATALOG_OPEN">
<command_arg id="1" command="1" catalog_link="1" />
</command>
</application>
<catalog id="1" />
</blueprints>
<tools>
<data_link_product id="1" product_code="Rosetta::Engine::Generic" />
<data_storage_product id="1" product_code="Oracle_9_i" is_network_svc="1" />
</tools>
<sites>
<application_instance id="1" blueprint="1">
<catalog_link_instance id="1" product="1" application="1" unrealized="1" target="1" local_dsn="test" />
</application_instance>
<catalog_instance id="1" product="1" blueprint="1" />
</sites>
<circumventions />
</root>DESCRIPTION
This module is a reference implementation of fundamental Rosetta features.
The Rosetta::Engine::Generic Perl 5 module is a functional but quickly built Rosetta Engine that interfaces with a wide variety of SQL databases. Mainly this is all databases that have a DBI driver module for them and that support SQL natively; multi-database DBD modules like DBD::ODBC are supported on equal terms as single-database ones like DBD::Oracle. I created this module to be a "first line of support" so that Rosetta works with a variety of databases as soon as possible.
While a better long term solution would probably be to make a separate Engine for each database, I will leave this up to other people that have the expertise and desire to make "better" support for each database; likewise, I leave it up to others to make Engines that don't use a DBI module, such as one built on Win32::ODBC, or Engines that talk to non-SQL databases like dBase (?), FoxPro (?) or FileMaker.
Rosetta::Engine::Generic has an external dependency in several Rosetta::Utility::* modules, which do most of the actual work in SQL generating (usual task) or parsing; the latter is for some types of schema reverse engineering. However, reverse engineering from "information schemas" will likely be done in Generic itself or a third module, as those are not SQL based.
As with all Rosetta::Engine::* modules, you are not supposed to instantiate objects of Rosetta::Engine::Generic directly; rather, you use this module indirectly through the Rosetta::Interface class. Following this logic, there is no class function or method documentation here.
CAVEAT: THIS ENGINE IS "UNDER CONSTRUCTION" AND MANY FEATURES DESCRIBED BY SQL::Routine::Language AND Rosetta::Features ARE NOT YET IMPLEMENTED.
ROSETTA FEATURES SUPPORTED BY ENVIRONMENT
Rosetta::Engine::Generic explicitly declares the support levels for certain Rosetta Native Interface features at the Environment level, listed below. Those with 'yes' are always available regardless of any Connection circumstances; those with 'no' are never available.
CATALOG_LIST
yes
CATALOG_INFO
no
CONN_BASIC
yes
CONN_MULTI_SAME
yes
CONN_MULTI_DIFF
yes
CONN_PING
no
TRAN_MULTI_SIB
no
TRAN_MULTI_CHILD
no
USER_LIST
no
USER_INFO
no
SCHEMA_LIST
no
SCHEMA_INFO
no
DOMAIN_LIST
no
DOMAIN_INFO
no
DOMAIN_DEFN_VERIFY
no
DOMAIN_DEFN_BASIC
no
TABLE_LIST
no
TABLE_INFO
no
TABLE_DEFN_VERIFY
no
TABLE_DEFN_BASIC
no
TABLE_UKEY_BASIC
no
TABLE_UKEY_MULTI
no
TABLE_FKEY_BASIC
no
TABLE_FKEY_MULTI
no
QUERY_BASIC
no
QUERY_SCHEMA_VIEW
no
QUERY_RETURN_SPEC_COLS
no
QUERY_RETURN_COL_EXPRS
no
QUERY_WHERE
no
QUERY_COMPARE_PRED
no
QUERY_BOOLEAN_EXPR
no
QUERY_NUMERIC_EXPR
no
QUERY_STRING_EXPR
no
QUERY_LIKE_PRED
no
QUERY_JOIN_BASIC
no
QUERY_JOIN_OUTER_LEFT
no
QUERY_JOIN_ALL
no
QUERY_GROUP_BY_NONE
no
QUERY_GROUP_BY_SOME
no
QUERY_AGG_CONCAT
no
QUERY_AGG_EXIST
no
QUERY_OLAP
no
QUERY_HAVING
no
QUERY_WINDOW_ORDER
no
QUERY_WINDOW_LIMIT
no
QUERY_COMPOUND
no
QUERY_SUBQUERY
noThis Engine may contain code that supports additional features, but these have not been tested at all and so are not yet declared.
ROSETTA FEATURES SUPPORTED PER CONNECTION
Rosetta::Engine::Generic explicitly declares the support levels for certain Rosetta Native Interface features at the Connection level, listed below. Whether or not each is available depends on what Connection you have. The conditions for each feature are listed with them, below and indented.
TRAN_BASIC
- If "auto_commit" ECO is true then:
no
- Else
don't know yet
TRAN_ROLLBACK_ON_DEATH
don't know yetENGINE CONFIGURATION OPTIONS
The SQL::Routine objects that comprise Rosetta's inputs have special compartments for passing configuration options that are only recognizable to the chosen "data link product", which in Rosetta terms is an Engine. At the moment, all Engine Configuration Options are conceptually passed in at "catalog link realization time", which is usually when or before a Connection Interface is about to be made (by a prepare(CATALOG_OPEN)/execute() combination), or it can be when or before an analagous operation (such as a CATALOG_INFO). When a catalog link is realized, a short chain of related SRT Nodes is consulted for their attributes or associated child *_opt Nodes, one each of: catalog_link_instance, catalog_instance, data_link_product, data_storage_product. The last two may be removed from consultation. Option values declared later in this list are increasingly global, and those declared earlier are increasingly local; any time there are name collisions, the most global values have precedence. The SRT Nodes are read at prepare() time. At execute() time, any ROUTINE_ARGS values can fill in blanks, but they can not override any any SRT Node option values. Once a Connection is created, the configuration settings for it can not be changed. Rosetta::Engine::Generic recognizes these options:
local_dsn - cstr - This is the locally recognized "data source name" of the database/catalog that you want to connect to. Details or other related Engine options to be worked out.
login_name - cstr - This is a database natively recognized "authorization identifier" or "user name" that your application wants to log-in to the database as every time it connects. You typically only set this if the user-name is hidden from the application user such as if it is stored in a application configuration file, and the user would not be prompted for a different one if it fails to work. If the database user name is provided by the user, then you typically pass it as a host parameter value at execute() time instead of storing it in the model. If you do both, then the execute() argument will be used instead of the model-provided value. If you do not provide this value either in the model or at execute() time, we will assume the database doesn't require authentication, or we will try to log in anonymously.
login_pass - cstr - This is the database natively recognized "password" that you provide along with the login_name. All parts of the above description for the "user" apply to the "pass" also.
dbi_driver - cstr - Seeing as Rosetta::Engine::Generic is meant to sit on top of DBI and any of its drivers, this option lets you explicitely pick which one to use. If this is not set, then Generic will make an educated guess for which DBD module to use based on the "data_storage_product" you chose for the database catalog you are connecting to, or it will fall back to DBD::ODBC.
auto_commit - bool - If this option is false (the default), Rosetta::Engine::Generic will always use transactions and require explicit commits for database actions to be saved; if this option is true, then it will instead auto-commit every database action, so separate commits are not necessary. When this option is true, then this module should behave as expected with every kind of data storage product; automatic explicit commits will be issued for transaction supporting databases, and this behaviour will just happen anyway on non-supporting ones. When this option is false, then you must make sure to only use database products with it that have native support for transactions; Generic won't even try to emulate transactions since that is too difficult to do properly; this module simply won't work properly with databases that lack native transaction support, even though it will incorrectly declare support for said activity.
delim_ident - bool - The SQL standard defines 2 main formats for naming identifiers (eg table, column names) which we will call "delimited identifiers" and "bareword identifiers". Delimited identifiers are case-sensitive and can contain any character (probably) like a literal string; however, all references to them must be quoted/delimited, which looks unusual considering their conceptual equivalence to variable or function or class names in programming languages. Bareword identifiers are case-insensitive (and practically fully uppercase) and can contain only letters, underscores, and partly numbers; SQL using this format tends to look much cleaner, and this format is also considered the "normal" way of doing things in SQL, with most databases supporting it only, if not both. As delimited identifiers carry more information (a full superset), that is what Rosetta and SQL::Routine support internally. Movement from a delimited format to a bareword one will render all alpha characters uppercase and strip the non-allowed characters, and both steps discard information; movement the other way will keep all information and match as uppercase. Rosetta::Engine::Generic will generate SQL in either format, as determined either by a database product's abilities, or according to this Engine configuration option. True (the default) says to use delimited identifiers, where false says to use bareword ones. Identifiers are usually delimited by double-quotes ('"', as distinct from string delimiting single-quotes), or back-ticks ('`'). Feature details subject to change.
More options will be added, or some will be changed, over time.
BUGS
This module is currently in pre-alpha development status, meaning that some parts of it will be changed in the near future, perhaps in incompatible ways.
SEE ALSO
perl(1), Rosetta, SQL::Routine, Locale::KeyedText, Rosetta::Utility::SQLBuilder, Rosetta::Utility::SQLParser, DBI.