The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
Rosetta-Engine-Generic-0.20
River stage zero No dependents
/ Rosetta::Engine::Generic

NAME

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

DEPENDENCIES

Perl Version: 5.008001

Core Modules: none

Non-Core Modules:

        Rosetta 0.47
        SQL::Routine::SQLBuilder 0.20
        SQL::Routine::SQLParser 0.01
        DBI 1.48 (highest version recommended)

COPYRIGHT AND LICENSE

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.

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 SQL::Routine::* 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
                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 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 analogous 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. 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.

These options are explicitly defined by SQL::Routine 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 SQL::Routine 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.

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, SQL::Routine::SQLBuilder, SQL::Routine::SQLParser, DBI.