NAME

Rosetta::Engine::Generic - A catch-all Engine for any DBI-supported SQL database

VERSION

This document describes Rosetta::Engine::Generic version 0.22.0.

SYNOPSIS

The previous SYNOPSIS was removed; a new one will be written later.

DESCRIPTION

This module is a reference implementation of fundamental Rosetta 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::Model::* 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 Rosetta::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
        no

This 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 yet

ENGINE CONFIGURATION OPTIONS

The Rosetta::Model 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 analogous operation (such as a CATALOG_INFO). When a catalog link is realized, a short chain of related ROS M 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. 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 ROS M Nodes are read at prepare() time. At execute() time, any ROUTINE_ARGS values can fill in blanks, but they can not override any any ROS M Node option values. Once a Connection is created, the configuration settings for it can not be changed.

These options are explicitly defined by Rosetta::Model and have their own dedicated Node attributes; the options listed here have the same names (lower-case) as the attribute names in question. You can provide each of these options either in the dedicated attribute or in a *_opt Node having a same-named si_key; if both are set, the attribute takes precedence:

• product_code - cstr - Corresponds to "data_storage_product.product_code".
• is_memory_based - cstr - Corresponds to "data_storage_product.is_memory_based".
• is_file_based - cstr - Corresponds to "data_storage_product.is_file_based".
• is_local_proc - cstr - Corresponds to "data_storage_product.is_local_proc".
• is_network_svc - cstr - Corresponds to "data_storage_product.is_network_svc".
• file_path - cstr - Corresponds to "catalog_instance.file_path". When using a data storage product that is file based, this config option is required; it contains the file path for the data storage file. TODO: server_ip, server_domain, server_port.
• local_dsn - cstr - Corresponds to "catalog_link_instance.local_dsn". This is the locally recognized "data source name" of the database/catalog that you want to connect to.
• login_name - cstr - Corresponds to "catalog_link_instance.login_name". 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 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 - Corresponds to "catalog_link_instance.login_pass". This is the database natively recognized "password" that you provide along with the login_name. All parts of the above description for the "name" apply to the "pass" also.

Rosetta::Engine::Generic recognizes these options:

• 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 product_code engine configuration option, or it will fall back to DBD::ODBC if possible.
• 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.
• ident_style - enum - If this "identifier style" option is 'YD_CS' (the default), then Rosetta::Engine::Generic will generate SQL identifiers (such as table or column or schema names) that are delimited, case-sensitive, and able to contain any characters (including whitespace). If this option is 'ND_CI_UP', then generated SQL identifiers will be non-delimited, case-insensitive, with latin characters folded to uppercase, and contain only a limited range of characters such as: letters, underscore, numbers (non-leading); these are "bare-word" identifiers. The 'ND_CI_DN' style is the same as 'ND_CI_UP' except that the identifier is folded to lowercase. Note that all of these formats are supported by the SQL standard but that the standard specifies all non-delimited identifiers will match as uppercase when compared to delimited identifiers. SQL using the bare-word format may look cleaner than the delimited format, and some databases support it only, if not both. As delimited identifiers carry more information (a full superset), that is what Rosetta and Rosetta::Model support internally. Movement from a delimited format to a bare-word one will fold the case of all alpha characters and strip the non-allowed characters, and both steps discard information; movement the other way will keep all information. 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. Identifiers are usually delimited by double-quotes ('"', as distinct from string delimiting single-quotes), or back-ticks ('`').

More options will be added, or some will be changed, over time.

DEPENDENCIES

This module requires any version of Perl 5.x.y that is at least 5.8.1.

It also requires the Perl modules version and only, which would conceptually be built-in to Perl, but aren't, so they are on CPAN instead.

It also requires the Perl module List::Util, which would conceptually be built-in to Perl, but is bundled with it instead.

It also requires these modules that are on CPAN: Rosetta '0.71.0-', Rosetta::Utility::SQLBuilder '0.22.0-', Rosetta::Utility::SQLParser '0.3.0-', DBI '1.48-' (highest version recommended).

INCOMPATIBILITIES

None reported.

SEE ALSO

perl(1), Rosetta, Rosetta::Model, Locale::KeyedText, Rosetta::Utility::SQLBuilder, Rosetta::Utility::SQLParser, DBI.

BUGS AND LIMITATIONS

This module is currently in pre-alpha development status, meaning that some parts of it will be changed in the near future, perhaps in incompatible ways.

AUTHOR

Darren R. Duncan (perl@DarrenDuncan.net)

LICENCE AND COPYRIGHT

This file is part of the Rosetta::Engine::Generic feature reference implementation of the Rosetta database portability library.

Rosetta::Engine::Generic is Copyright (c) 2002-2005, Darren R. Duncan. All rights reserved. Address comments, suggestions, and bug reports to perl@DarrenDuncan.net, or visit http://www.DarrenDuncan.net/ for more information.

Rosetta::Engine::Generic is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License (GPL) as published by the Free Software Foundation (http://www.fsf.org/); either version 2 of the License, or (at your option) any later version. You should have received a copy of the GPL as part of the Rosetta::Engine::Generic distribution, in the file named "GPL"; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301, USA.

Linking Rosetta::Engine::Generic statically or dynamically with other modules is making a combined work based on Rosetta::Engine::Generic. Thus, the terms and conditions of the GPL cover the whole combination. As a special exception, the copyright holders of Rosetta::Engine::Generic give you permission to link Rosetta::Engine::Generic 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::Engine::Generic (the version of Rosetta::Engine::Generic 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::Engine::Generic, and which is fully useable when not linked to Rosetta::Engine::Generic in any form.

Any versions of Rosetta::Engine::Generic 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::Engine::Generic 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::Engine::Generic would appreciate being informed any time you create a modified version of Rosetta::Engine::Generic that you are willing to distribute, because that is a practical way of suggesting improvements to the standard version.