NAME
Rosetta::Framework - Documentation on the Rosetta framework at large
SYNOPSIS
None present at the moment. Meanwhile, see the EXAMPLE PROGRAM below.
ABSTRACT
Rosetta is a comprehensive framework for database-using applications of any size or function that allows them to be easily portable across multiple database implementations because any proprietary details of each are abstracted away. At the same time, it is designed to be fast and efficient. Applications use Rosetta as a virtual embedded database, whose API is called the "Rosetta Native Interface" or "RNI", and whose feature set is an opaque normalized superset of all common database feature sets. The feature superset includes both data manipulation (with multi-table selects and updates plus subqueries or stored procedure calls) and schema manipulation (tables, views, procedures). Rosetta is designed to work equally well with both embedded and client-server databases; in the latter case, it is the client.
The RNI is implemented using mainly a "Command" design pattern, meaning that it has few real functions or methods, but those use objects as input and output, which are flexible enough to define any task or result. The RNI is verbose and intended to provide non-ambiguous structured definitions of all tasks, so that the results of executing them are easy to predict; the definitions are multi-dimensional data structures (or objects) having atomic values (which also have native data type formats). Rosetta has this as an advantage over other database abstractions that use serialized strings like SQL (such as ODBC/JDBC), because each database has its own SQL dialect, and applications using them must be coded differently for each one. Rosetta is especially suited for data-driven applications, since the composite scalar values in their data dictionaries can often be copied directly to RNI structures, saving applications the tedious work of generating SQL themselves. Rosetta also provides native internationalization support, for example allowing system messages to be in multiple user languages simultaneously.
Rosetta makes it easy to layer alternative APIs on top of RNI, so that you can simplify or customize it to your specific needs. As practical examples of this, there are several emulators provided for common existing database APIs (such as ODBC/JDBC), so that most applications can simply use Rosetta as a hot-swappable replacement for them; you do not have to "learn yet another language" or re-code your application in order for it to just work with more databases. While the Rosetta core must always be embedded in an application to be used, an extension is available that will allow it to be used in a client-server arrangement instead (as ODBC does), where the server is a proxy for the client; the client is embedded in the main application, and it talks to the server in network-serialized RNI, which then translates the request into native database actions. Some utilities built on Rosetta are also available, for such tasks as cloning or backing up a database (schema and/or data; this includes scanning the database to make a data dictionary), or editing one through a web interface (like PHPPgAdmin or PHPMyAdmin but for any RDBMS).
Rosetta is not a complete database by itself and you need to separately have an actual database to use it with. Rosetta does not usually implement features that are missing in a database being abstracted (such as foreign key constraints, transactional integrity, or geographical data types), in order to give it an identical feature set to a more capable database; Rosetta just allows for the features that do exist to be called in an identical way. A consequence of this is that your choice of database implementation will indeed affect what features you have available; your application will port without changes only to databases which support the features that you use. The RNI may not interface to every single feature of a particular database (neglecting esoteric ones), so you can't use those features with Rosetta (but support can be added). Rosetta does not automate installation of any separate database software or configure it (like a package manager); you will have to do that yourself.
Note: see also the SQL::Routine library, which is the foundation for the Rosetta API; see the Language.pod file in particular.
DESCRIPTION
The Rosetta framework is intended to support complex (or simple) database-using applications that are easily portable across databases because common product-specific details are abstracted away. These include the RDBMS product and vendor name, what dialect of SQL its scripting or query interface uses, whether the product uses SQL at all or some other method of querying, how query results are returned, what features the RDBMS supports, how to manage connections, how to manage schema, how to manage stored procedures, and perhaps how to manage users. The main thing that this framework will not be doing in the forseeable future is managing the installation and configuration of the RDBMS itself, which may be on the same machine or a different one.
There are two main types of functionality that the Rosetta framework is designed to implement; this functionality may be better described in different groupings.
The first functionality type is the management (creation, modification, deletion) of the schema in a database, including: tables, keys, constraints, relations, sequences, views, stored procedures, triggers, and users. This type of functionality typically is used infrequently and sets things up for the main functionality of your database-using application(s). In some cases, typically with single-user desktop applications, the application may install its own schema, and/or create new database files, when it starts up or upon the user's prompting; this can be analogous to the result of a "New..." (or "Save As...") command in a desktop financial management or file archiving application; the application would then carry on to use the schema as its personal working space. In other cases, typically with multiple-user client-server applications, one "Installer" or "Manager" type application or process with exclusive access will be run once to create the schema, and then a separate application or process will be run to make use of it as a shared working space.
The second functionality type is the management (creation, modification, deletion) of the data in a database, including such operations as: direct selects from single or multiple tables or views, direct inserts or updates or deletes of records, calling stored procedures, using sequences, managing temporary tables, managing transactions, managing data integrity. This type of functionality typically is used frequently and comprises the main functionality of your database-using application(s). In some cases, typically with public-accessible websites or services, all or most users will just be viewing data and not changing anything; everyone would use the same database user and they would not be prompted for passwords or other security credentials. In other cases, typically with private or restricted-access websites or services, all or most users will also be changing data; everyone would have their own real or application-simulated database user, whom they log in as with a password or other credentials; as the application implements, these users can have different activity privileges, and their actions can be audited.
The Rosetta framework can be considered a low-level service because it allows a fine level of granularity or detail for the commands you can make of it and the results you get back; you get a detailed level of control. But it is not low-level in the way that you would be entering any raw SQL, or even small fragments of raw SQL; that is expressly avoided because it would expose implementation details that aren't true on all databases. Rather, this framework provides the means for you to specify in an RDBMS-generic fashion exactly what it is you want to happen, and your request is mapped to native or emulated functionality for the actual RDBMS that is being used, to do the work. The implementation or mapping is different for each RDBMS being abstracted away, and makes maximum use of that database's built-in functionality. Thereby, the Rosetta framework achieves the greatest performance possible while still being 100% RDBMS-generic.
This differs from other database abstraction modules or frameworks that I am aware of on CPAN, since the others tend to either work towards the lowest-common-denominator database while emulating more complex functionality, which is very slow, or more often they provide a much more limited number of abstracted functions and expect you to do things manually (which is specific to single databases or non-portable) with any other functionality you need. With many modules, even the abstracted functions tend to accept sql fragments as part of their input, which in the broadest sense makes those non-portable as well. With my framework I am attempting the "holy grail" of maximum portability with maximum features and maximum speed, which to my knowledge none of the existing solutions on CPAN are doing, or would be able to do short of a full rewrite. This is largely why I am starting a new module framework rather than trying to help patch an existing solution; I believe a rewrite is needed.
The Rosetta framework is best used through its native interface (RNI), which accepts and returns only atomic values (or multi-dimensional data structures containing them); no "parsing" or such analysis is done such as with SQL statements. The main reason is that this framework is intended primarily for a data-driven application programming model, where the applications use a "data dictionary" to control what work it is doing; the applications can simply copy the composite scalar values of the data dictionary, without having to encode them into a single string. The RNI is designed to allow entry of a non-ambiguous structured definition of any task that you would want a database to do. Rosetta is intended to support a superset of features from all common generic RDBMS products, so it should have a native way of expressing any task that you can do now. For cases where you don't already have a data dictionary, Rosetta can scan your existing database to create one.
One would think that, despite all the advantages that Rosetta can bring to a new application that is designed around RNI (or a simplifying wrapper of it), it wouldn't be very helpful to an existing older application that is built around "a different way of doing things". From the latter perspective, there looks to be just as much work involved in porting their application to use Rosetta as there would be to port it to a new database or other interface framework. The problem would be the all-too-common having to "learn yet another language", and then port the application to it (for that matter, it would be a new language for new app builders as well, although that may not be the same problem). Either transition could be a significant cost and the hurdle can deter upgrades to making apps portable.
But to help with this situation, Rosetta also includes several emulators (each of which is a higher-level layer that translates its input into RNI calls) for common existing database interfaces, so that most applications can simply use Rosetta as a hot-swappable replacement for them; you do not have to "learn yet another language" or re-code your application in order for it to just work with more databases. It should be possible to emulate any existing interface, and if a new one comes along with features that Rosetta can't handle (interface to or implement), then this is a reasonable excuse to update the core so that it is possible. That said, the success of an emulator depends largely on whether code that was using the original module is using the original the way it was designed or not; code that was hacking the internals of a module (as Perl makes so easy) is less likely to work (sort of like how an app used to using un-documented APIs on an operating sytem, or doing direct OS data structure access on a non-memory-protected OS, would break if the implementation of that OS changed).
Included in a Rosetta distribution will be several applications which serve as examples of Rosetta in use, but in some cases are useful themselves. One example will be a utility for copying one database to another, such as for backup or restore, or just migration. Another example will be a web app that works sort of like "PHPPgAdmin" or "PHPMyAdmin" (letting users manually edit schema and data) except that it works with many RDBMS products. Some code porting utilities could also be available, to help makers of old applications migrate to RNI, for better control and performance than an emulator would provide.
STRUCTURE
The modules composing the Rosetta framework are grouped into several main categories, which can be called: "Locale" (Locale::KeyedText), "Model" (SQL::Routine), "Interface", "Engine", "Proxy" (Server/Client), "Validator", "Emulator", and others.
Note: the file Language.pod should be read next, after Framework.pod.
These classes do 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 any class for your own modules, then that often means something like $self->method().
This briefly illustrates the relationship of the module groups:
DATABASE
|
|
Proxy |
Client;Engine--------------\
called by | uses used by \
| \
invokes | \
Interface--------------Model;Locale (both used by other 7 groups)
called by | \ uses used by / |
| \ / |
invokes/| \ / /
//| Emulator------/ /
Validator--// | | uses used by /
/ | | /
Proxy/ | | /
Server APPLICATION-------/
uses used byThe Locale modules function as constant data resources, mainly holding user-readable text such as database error messages and so forth, so that this text is separate from normal module code and easy to edit. These are part of the Rosetta core, and are used by the Interface and Engine modules. There are multiple Locale files rather than just one partly so that each file can have a different localization; adding a new user language is as easy as adding another file. The set of text strings being stored should be comprehensive enough that any Engine module can use them, and therefore not need their own Locale files for displaying error messages. All strings are mapped to short codes (eg: 'R001372'); these codes are what the normal module code uses to display a particular message.
The "Model" and "Interface" modules are collectively referred to as the "Rosetta Native Interface" or "RNI", and they are the "Core" of the Rosetta framework to which all else is attached. The distinction between the two is that Model modules are purely container objects which hold descriptions of "things" (such as data types or tables or views), while Interface modules typically are not containers and represent "actions" (such as connections or cursors). Model objects are complete on their own and can be serialized or stored indefinately off site for later retrieval and use, such as with a "data dictionary" describing a table or view. Interface objects only make sense within the context where they were created and often contain time-sensitive data; it wouldn't make sense to store them except during the short term, such as with a pool of active and reusable database connections. It is common for Interface objects to hold Model objects as properties, to help them know how to do their actions, such as how to create a table or select from it.
The Engine modules are what enable Rosetta to use each type of database. They are what handle (interface to or implement) all the details of using a specific RDBMS product; you need at least one for each unique RDBMS product you plan to run your application on; they are "action" modules. They take Interface/Model objects as input and convert them into the actual SQL or other method calls that the RDBMS products use, then they invoke the generated, and then interpret the results into other Interface/Model objects to return as output. The Engine modules also deal with extracting any existing schema stored in an RDBMS so they can generate an RNI data dictionary from it when desired. Nothing talks to an RDBMS product except a Engine module, and nothing talks to an Engine module except the Interface modules.
Note that application specific "stored procedures" which are implemented in Perl, and that are RDBMS product specific, would sit beside "Engine" in the above diagram, since they would have direct access to the product-specific code that the Engine uses to talk to the DATABASE. Those are not shown in the above diagram for simplicity. That said, all calls to such "stored procedures" from the main APPLICATION will still always go through Interface.
The Proxy modules comprise an extension that allows the normally embedded-only Rosetta to be used in a client-server situation (as ODBC is). But this extension is implemented in such a way that neither the Engine modules nor the APPLICATION need to know the difference of whether the extension is being used or not. The Proxy modules correspond to both a "Engine" (network client) and an "APPLICATION" (network server); they involve two applications that have the Rosetta core embedded talking to each other, with one handing all the database details on behalf of the other. See the following diagram:
DATABASE
|
Engine
|
Interface::::::
| =
Proxy Server =
| =
NETWORK = (Engine and APP think these are the same single Interface)
| =
Proxy Client =
| =
Interface::::::
|
APPLICATIONThe Validator modules are a common comprehensive test suite to run against all Engines in order to tell that they are implementing the Rosetta interface properly or not. This also saves each Engine maker from having to write their own tests. These would be used similarly to how Sun has an official validation suite for Java Virtual Machines to make sure they implement the official Java specification. Since not all databases are alike, each Engine is expected to report programmatically which kinds of features it supports, and the validator suite will only test interface compliance for those features; this way, a missing feature does not mean the Engine will fail its tests unless it explicitly says it has said feature.
The Emulator modules are extensions that are purely optional for use, and they are intended to facilitate rapid adaption (hot-swapping) of Rosetta into an existing application that is already built around a different database interface (such as DBI or ODBC or OCI). Each Emulator module is a higher-level layer which translates its input into pure RNI calls and translates the output correspondingly; each should have an identical public interface to what it emulates. So applications often won't need to be changed to use Rosetta, but they still just become more portable. Many Emulator modules would include SQL parsing functionality (as SQL is their input), and would convert SQL statements from known dialects into Interface/Model objects
All of the Rosetta components are implemented using pure Perl 5 code for now and during the near future (although some Engines or extensions may have prerequisites that are not). Once Rosetta is feature complete in this form, it will be rewritten as a C library which has bindings for multiple languages, including Perl 5 and Parrot. Advantages of this change are not only greater speed and memory efficiency but also compatability; it will allow Rosetta (and through it Perl) to use databases which previously had no Perl bindings or DBI Engines before. Moreover, the potential user base would increase sharply because users of other languages like PHP or Python would be able to use it. The C version of Rosetta will probably have the "Model" (SQL::Routine) and "Interface" modules being mostly reduced to struct definitions, macros for manipulating the structs, header files, and a small amount of implementation code. Most of the normal implementation C code would be derived from the other modules, particularly the "Engine", "Proxy", "Validator", "Emulator" ones. The pure Perl version will probably stay around after that conversion should it be useful, but it won't have as large of a database support. I have no plans to make a pure Java version, but rather, the C version would bind to Java environments using a Java Bean.
AN ALTERNATIVE SECOND VERSION
Inspired by the recent (February 2004) formation of dbdi-dev@perl.org, a group which aims to make a Parrot-native database access framework for all Parrot hosted languages, and that can talk to native database drivers through Parrot's Native Call Interface, meaning that the Parrot database project doesn't need any parts written in C, I may decide to take the same approach with the next major version of Rosetta and related projects, implementing it in IMC instead of C.
More recently (March 2005), considering that a working Perl 6 engine is already taking shape separately from Parrot (see Autrijus Tang's "Pugs" distribution), I will probably just make the second version in Perl 6 instead. If an IMC version is made, it will be done later for the sake of better performance.
BRIEF MODULE LIST
The following module names and descriptions are undergoing a state of transition, mainly in the 'Engine' arena. See below for details. Many of these modules don't exist yet, but are being planned to exist, as they are implemented over time.
Note: As an analogy to the DBI framework, 'Rosetta' is akin to 'DBI', 'SQL-Routine' is akin to 'SQL-Statement', 'Rosetta-Engine-*' is akin to 'DBD-*', and 'Rosetta-Emulator-*' is akin to 'Oraperl' etc; the Builder/Parser are optional utilities that can be used by Engine/Emulator/etc.
Any indented package names represent subordinate classes of what they are beneath.
In distribution "Locale-KeyedText"
Locale::KeyedText (Refer to user messages in programs by keys)
Locale::KeyedText::Message (stores details of a machine-readable message)
Locale::KeyedText::Translator (converts a m-r message to a human readable form)In distribution "SQL-Routine"
SQL::Routine (Specify all database tasks with SQL routines)
SQL::Routine::Container (a context for Nodes, one of these per parent)
SQL::Routine::Node (a semi-atomic model component)
SQL::Routine::L::en (Localization of SQL::Routine for English)
SQL::Routine::L::fr (Localization of SQL::Routine for French)
SQL::Routine::L::* ... like German, Spanish, Dutch, Chinese, etc
SQL::Routine::Details (Main structural and API documentation for SQL::Routine)
SQL::Routine::Language (What language or grammar SQL::Routine speaks)
SQL::Routine::EnumTypes (What enumerated types are part of SQL::Routine's language or grammar)
SQL::Routine::NodeTypes (What Node types are part of SQL::Routine's language or grammar)In distribution "Rosetta"
Rosetta (Framework for RDBMS-generic apps and schemas)
Rosetta::Interface (defines the whole Rosetta public API, is largely a shim)
Rosetta::Interface::Application
Rosetta::Interface::Environment
Rosetta::Interface::Connection
Rosetta::Interface::Cursor
Rosetta::Interface::Literal
Rosetta::Interface::Success
Rosetta::Interface::Preparation
Rosetta::Interface::Error
Rosetta::Engine (common base class for Engine modules that implement the Rosetta API)
Rosetta::Dispatcher (handle requests not directed at any particular Engine)
Rosetta::L::en (Localization of Rosetta core for English)
(Also provides a set of generic database error strings for Engines, in English)
Rosetta::L::fr (Localization of Rosetta core for French)
Rosetta::L::* ... like German, Spanish, Dutch, Chinese, etc
Rosetta::Details (Main structural and API documentation for Rosetta)
Rosetta::Features (What RNI features a Rosetta Engine can possibly implement)
Rosetta::Framework (Documentation on the Rosetta framework at large)
Rosetta::Validator (A common comprehensive test suite to run against all Engines)
Rosetta::Validator::L::en (Localization of Rosetta::Validator for English)
Rosetta::Validator::L::fr (Localization of Rosetta::Validator for French)
Rosetta::Validator::L::* ... like German, Spanish, Dutch, Chinese, etcIn distribution "SQL-Routine-SQLBuilder"
SQL::Routine::SQLBuilder (Generate ANSI/ISO SQL:2003 and other SQL variants)
SQL::Routine::SQLBuilder::L::en (Localization of SQL::Routine::SQLBuilder for English)In distribution "SQL-Routine-SQLParser"
SQL::Routine::SQLParser (Parse ANSI/ISO SQL:2003 and other SQL variants)
SQL::Routine::SQLParser::L::en (Localization of SQL::Routine::SQLParser for English)In distribution "Rosetta-Utilities" or a multiplicity of similarly named
Rosetta::Utility::RWViews (convert IUD against views into IUD against tables)
Rosetta::Utility::* ... other non-core utility modules usable by several non-core distrosIn distribution "Rosetta-Engine-Generic"
Rosetta::Engine::Generic (A catch-all Engine for any DBI-supported SQL database)
Rosetta::Engine::Generic::L::en (Localization of Rosetta::Engine::Generic for English)In a multiplicity of "Rosetta-Engine-*" distributions
These Engine names are examples which I probably won't implement myself any time soon, if at all. I leave these open for anyone who wants to make an Engine for one or more databases, either that does a more "proper" job than my own Generic catch-all, or to support a database or service my catch-all doesn't support at all, such as a non-SQL based one, or who for example want to use something not DBI based like Win32::ODBC.
Note: more databases (and data access protocols) can be found listed at "http://freshmeat.net/articles/view/305/" and "http://freshmeat.net/articles/view/307/"
Rosetta::Engine::SQLite (binding to public domain SQLite embedded db 3.x, 2.8.x)
Rosetta::Engine::MySQL (interface/binding to MySQL cli-serv/embed db 5.x, 4.x, 3.23.x)
Rosetta::Engine::PostgreSQL (interface to PostgreSQL db 8.x, 7.4.x, 7.3.x, 7.2.x)
Rosetta::Engine::Oracle (interface to Oracle db 10.x, 9.x, 8.x)
Rosetta::Engine::Sybase (interface to Sybase db)
Rosetta::Engine::Informix (interface to IBM Informix db)
Rosetta::Engine::DB2 (interface to IBM's DB/2 db and its DB/2 Express db)
Rosetta::Engine::SQLServer (interface to MS SQLServer db)
Rosetta::Engine::Adabas (interface to Software AG's Adabas db)
Rosetta::Engine::SAPdb (interface to the SAP open source db; forked from Adabas apparently)
Rosetta::Engine::Unify (interface to Unify db)
Rosetta::Engine::Empress (interface to the Empress db)
Rosetta::Engine::PrimeBase (interface to PrimeBase db)
Rosetta::Engine::OpenBase (interface to OpenBase db 9.x, 8.x)
Rosetta::Engine::FrontBase (interface to FrontBase db)
Rosetta::Engine::InterBase (binding to Borland InterBase embedded db)
Rosetta::Engine::Firebird (interface to Firebird db)
Rosetta::Engine::Derby (interface to Apache Derby db, if linking to Java is possible)
Rosetta::Engine::Solid (interface to Solid db)
Rosetta::Engine::Cache (interface to InterSystems Cache db)
Rosetta::Engine::xBase (binding to XBase db accessor library)
Rosetta::Engine::Ingres (interface to Ingres db)
Rosetta::Engine::Illustra (interface to Illustra database)
Rosetta::Engine::Valentina (binding to Paradigma Software's Valentina embedded db)
Rosetta::Engine::RelX (binding to relX' embedded db)
Rosetta::Engine::4D (interface to 4D db)
Rosetta::Engine::mSQL (interface to mSQL db)
Rosetta::Engine::Helix (interface to Helix db)
Rosetta::Engine::Pick (interface to Pick Systems' db)
Rosetta::Engine::PointBase (interface to PointBase db, if linking to Java is possible)
Rosetta::Engine::HSQLDB (interface to HSQLDB db, if linking to Java is possible)
Rosetta::Engine::FileMaker (interface to FileMaker Pro db)
Rosetta::Engine::Panorama (interface to Provue Panorama db)
Rosetta::Engine::FoxPro (interface to FoxPro db)
Rosetta::Engine::Access (interface to MS Access db)
Rosetta::Engine::Paradox (interface to Borland Paradox db)
Rosetta::Engine::dBase (interface to dBase db)
Rosetta::Engine::CSV (a database Engine using CSV files for storage)
Rosetta::Engine::* ... any of the 100+ other databases or services not listed aboveIn distribution "Rosetta-Proxy"
Some terms seen here are out of date.
Rosetta::Proxy::Server (an application on top of RNI implementing a network server)
Rosetta::Proxy::Client (a "Engine" implementing a network client of the server)
Rosetta::Proxy::Command (network-safe packed repr of ::Interface::Command obj)
Rosetta::Proxy::Result (network-safe packed repr of ::Interface::Result obj)In distribution "Rosetta-Emulator-DBI"
Rosetta::Emulator::DBI (emulates DBI/DBD::*, but result is more portable)
Rosetta::Emulator::DBI::L::en (Localization of Rosetta::Emulator::DBI for English)In a multiplicity of "Rosetta-Emulator-*" distributions
Rosetta::Emulator::ODBC (emulates an ODBC module, result more portable)
Rosetta::Emulator::OCI (emulates an OCI module, result more portable)
Rosetta::Emulator::* ... like some of the other abstraction modules
Rosetta::Emulator::*::* (helpers for emulated frameworks of several modules)In a multiplicity of "Rosetta-Application-*" distributions
Rosetta::Application::* ... higher level functionality like: Backup, Restore, Copy, ...In unknown distributions
Rosetta::* ... who knows ...
Rosetta::Adapter::* ... I like the name so it might be used for something
RosettaX::* ... unofficial extensions or wrappers to Rosetta are allowed here ...OLDER DOCUMENTATION
These paragraphs were more or less the original documentation for high-level Rosetta concepts, back when each concept was planned to be a separate class of its own. They were originally published in Framework.pod since the first release of the Rosetta distribution (2003-01-05). Some details you see here shouldn't be considered accurate. One portion that was here as late as 2004-02-04, 'Model Modules', has been moved to Language.pod, as of 2004-02-12.
Interface Modules
Rosetta::Interface - This Interface class is inherited by all other Interface Interface classes, and it provides functionality to talk to or manage Engine modules. Its main task is to define the execute_command() method, which takes a Command object saying what should be done next and returns or throws a Result object saying what actually was done (or what errors there were). For some command types, execute_command() may only start the process that needs doing (eg: get a select cursor), and invoking execute_command() again on the Result object (which is a subclass) will continue or finish the process (eg: fetch a row). Instantiated by itself, this class stores globals that are shared by all Engines or connections. Subclasses include: Result, Connection.
Rosetta::Command - This Interface class describes an action that needs to be done against a database; its objects encapsulate SQL::Routine::Command objects and also encapsulates the resolved host params, or live objects like 'Cursor'. Code using these objects should be doing something like both calling DBI->prepare() and DBI->execute(). See also SQL::Routine::Command.
Rosetta::Result - This Interface class is inherited by all Interface classes that would be returned from or thrown by an execute_command() method, and it contains the return values or errors of a Command. Its main task is to implement the is_error() and get_error() methods, which say whether the Command failed or not, and if so then why. Some commands (eg: 'database_close') have no other meta-data or data to return, while others do (eg: 'data_select'). Subclasses include: Connection.
Rosetta::Connection - This Interface class represents a connection to a database instance, and the simplest database applications use only one. You instantiate a Connection object by executing a Command of type 'database_open'; that command usually takes 4 arguments, the first of which is mandatory: 'engine' is a string having the name of the Engine module to use, which also defines what RDBMS product is being used; 'server' is the name of the specific database instance to use; 'user' is the username to authenticate yourself against a multi-user database as; 'pass' is the associated password.
Rosetta::Cursor - This Interface class represents a cursor over a rowset that is being selected from a database. You instantiate a Cursor object by executing a command of type 'data_select'; that command usually takes 1 argument, which is mandatory: 'view' is a View object that describes the select statement being run, including what columns it has and their datatypes, what the source tables are, how they are joined, what the row filters are, sort order, and row limiting or paging.
Utility Modules
Rosetta::Utility::RWViews - This is a utility class containing common functionality for multiple Engines. Given that most, if not all, existing RDBMS systems don't support updateable views, meaning treating a view like a table in every way, this class will implement that Rosetta feature, translating selects, inserts, updates, or deletes against a view into one or more corresponding actions against the underlying tables that the view interfaces.
SQL::Routine::SQLBuilder - This is a utility class containing common functionality for multiple Engines. Given that a majority of commonly used RDBMS systems use SQL as their primary command language, this class will take care of translating RNI commands into SQL commands. While a large part of SQL is implemented in a common way on most SQL-using databases, this class will take arguments that instruct it how to vary the SQL output to the dialect that a particular RDBMS uses. Note that the default SQL being produced will be compliant with the ANSI/ISO SQL:2003 standard.
Engine Modules
Rosetta::Engine::Generic - This Engine is a "default catch-all" that I am providing to get Rosetta up and working quickly with a wide variety of databases, specifically all those that have a DBI driver (eg, DBD::Oracle) and are "normal" SQL databases (or are the likes of DBD::ODBC who front "normal" databases). This distribution has a dependency in the SQL-Routine-SQLBuilder distribution, which handles the meat of the SQL generation, and the SQL-Routine-SQLParser distribution, which handles parsing in the case of reverse-engineering some schemas; it also, obviously, has a dependency on DBI and one or more DBD modules.
Rosetta::Engine::SQLite - This class embeds the public domain SQLite RDBMS, and will serve as a default (though separately distributed) Engine for Rosetta. It is small, powerful, easy to install and use, and implements the most important parts of the RNI, including Unicode, portability, transactions, and subselects. This Engine is intended mainly for programs that are used by one person at a time, as locks will grab the whole database. Given that the actual database is embedded into the Engine, we simply use the latest version available at the time of each Engine update, so mention of version ranges being supported or not is not applicable. That said, given that SQLite recently went through a major revision that involves a file format change, where the current version can't read files of the older version, this class will temporarily embed both main versions, v3.2.5 and v2.8.16 (both of which are declared stable); the 2.x support is deprecated.
Rosetta::Engine::MySQL - This class implements an Engine for talking to MySQL databases. It is recommended for use only with MySQL versions 5.0.12 (beta) and greater. Support for MySQL versions 4.1.14 and 4.0.26 and 3.23.58 (all 3 stable) is present but deprecated. v3.22.x and earlier will not be supported at all, without great demand.
Rosetta::Engine::PostgreSQL - This class implements an Engine for talking to PostgreSQL databases. It is recommended for use only with PostgreSQL versions 8.0.3 (stable) and greater. Support for PostgreSQL versions 7.4.8 and 7.3.10 and 7.2.8 (all 3 stable) is present but deprecated. v7.1.x and earlier will not be supported at all, without great demand.
Rosetta::Engine::Oracle - This class implements an Engine for talking to Oracle databases. It is recommended for use only with Oracle versions 9.x and greater, including v10.x. Support for Oracle versions 8.x is present but deprecated. v7.3 and earlier will not be supported at all, without great demand.
Rosetta::Engine::OpenBase - This class implements an Engine for talking to OpenBase databases. It is recommended for use only with OpenBase versions 8.0.4 and greater, including v9.x. v7.x and earlier will not be supported without great demand.
All other databases in common use should be supported as well; some in the above module list are products that I have used personally; I need to research others to know what versions exist or are stable or are in common use. Other RDBMS products include: Sybase, DB2, SQL-Server, FrontBase, Valentina, Informix, and others.
Miscellaneous Modules From Afar
In this context, "Wrapper" is a type of Rosetta extension that sits between the application and the RNI, such as Emulators. But Wrappers take many forms, most of which will not be included with this distribution.
One form of Wrapper is a value-added extension, possibly more application-specific, such as an interpreter for data dictionaries. For example, a data dictionary could say that an application is composed of screens or forms that are related in a certain way; each screen would contain several controls of various types, and some controls may correspond to specific columns in database tables. The module in question would determine from the data dictionary what needs to be retrieved from the database to support a particular screen, and ask the Interface modules to go get it. Similarly, if the application user edits data on the screens that should then be saved back to the database, the Wrapper module would ask the Interface modules to save it. On the other side of things, it is quite possible that the data dictionary for the application is itself stored in the database, and so the Interface modules can be asked to fetch portions of it as the Wrapper module requires.
Another form of Wrapper is an interface customizer or simplifier. if you know that certain details of your commands to Interface will always be the same, or you just like to express your needs in a different way, you can take care of the default values in a wrapper module, so that the rest of your application simply has to provide inputs that aren't always the same.
Another form of Wrapper is a data parser or serializer. For example, to convert database output to XML or convert XML to a database command (although, certain kinds of XML processing may be better implemented in the Interface/Engine layers for performance reasons, but if so it would still be an extension).
Another form of Wrapper is a command parser for various SQL dialects. For example, if you want to quickly port an application, which already includes SQL statements that are tailored to a specific database product, to a different database for which it is incompatible, a Wrapper module could parse that statement into the object representation that Interface uses. This is effectively an SQL-to-SQL translator. I would expect that, citing reasons of performance or application code simplicity, one wouldn't want to use this functionality long-term, but replace the SQL with Interface object definitions later.
Finally, one could also make Wrappers which emulate other database abstraction solutions for similar reasons to the above, which is a different type of quick porting. Since the intended feature set of Rosetta should be a superset of existing solutions' feature sets, it should be possible to emulate them with it.
OLDER EXAMPLE PROGRAM
The following sample code is more or less the original SYNOPSIS documentation in Framework.pod since the first release of the Rosetta distribution (2003-01-05); it was moved down here as of 2004-02-23. Some details you see here shouldn't be considered accurate; the new SYNOPSIS in Rosetta.pm is loosely derived from this, and that is up to date.
Content of settings file "survey_prefs.pl", used by script below:
my $rh_prefs = {
database_engine => 'Rosetta::Engine::MySQL',
database_open_args => {
server => 'survey1',
user => 'joebloe',
pass => 'fdDF9X0sd7zy',
},
question_list => [
{
visible_title => "What's your name?",
type => 'str',
name => 'name',
is_required => 1,
}, {
visible_title => "What's the combination?",
type => 'int',
name => 'words',
}, {
visible_title => "What's your favorite colour?",
type => 'str',
name => 'color',
},
],
};Content of a simple CGI script for implementing a web survey:
#!/usr/bin/perl
use strict;
use warnings;
&script_main();
sub script_main {
my $base_url = 'http://'.($ENV{'HTTP_HOST'} || '127.0.0.1').$ENV{'SCRIPT_NAME'};
my ($curr_mode) = $ENV{'QUERY_STRING'} =~ m/mode=([^&]*)/;
my $form_data_str = '';
read( STDIN, $form_data_str, $ENV{'CONTENT_LENGTH'} );
chomp( $form_data_str );
my %form_values = ();
foreach my $pair (split( '&', $form_data_str )) {
my ($key, $value) = split( '=', $pair, 2 );
next if( $key eq "" );
$key =~ tr/+/ /;
$key =~ s/%([0-9a-fA-F]{2})/pack("c",hex($1))/ge;
$value =~ tr/+/ /;
$value =~ s/%([0-9a-fA-F]{2})/pack("c",hex($1))/ge;
$form_values{$key} = $value;
}
my $fn_prefs = 'survey_prefs.pl';
print
"Status: 200 OK\n",
"Content-type: text/html\n\n",
"<html><head>\n",
"<title>Simple Web Survey</title>\n",
"</head><body>\n",
"<p><a href=\"$base_url?mode=install\">Install Schema</a>\n",
" | <a href=\"$base_url?mode=remove\">Remove Schema</a>\n",
" | <a href=\"$base_url?mode=fillin\">Fill In Form</a>\n",
" | <a href=\"$base_url?mode=report\">See Report</a></p>\n",
"<hr />\n",
"<form method=\"POST\" action=\"$base_url?mode=$curr_mode\">\n",
"<p>\n",
(&script_make_screen( $fn_prefs, $curr_mode, \%form_values )),
"</p>\n",
"<p><input type=\"submit\" name=\"OK\" value=\"Do It Now\" /></p>\n",
"</form>\n",
"</body></html>\n";
}
sub script_make_screen {
my ($fn_prefs, $curr_mode, $form_values) = @_;
my $prefs = do $fn_prefs;
unless( ref( $prefs ) eq 'HASH' ) {
return "Error: can't obtain required preferences hash from '$fn_prefs': ".
(defined( $prefs ) ? "result not a hash ref, but '$prefs'" :
$@ ? "compilation or runtime error of '$@'" : $!);
}
eval {
require Rosetta; # also compiles SQL::Routine
};
if( $@ ) {
return "Error: can't compile Rosetta modules: $@";
}
my $interface = Rosetta->new_interface();
$interface->throw_error( 0 ); # on error, ret result obj, do not throw exception
my $dbh = $interface->execute_command( {
'type' => 'database_open',
'engine' => $prefs->{database_engine},
'args' => $prefs->{database_open_args}, # includes server name, user/pass
} );
if( $dbh->is_error() ) {
return "Error: can't open database: ".$dbh->get_error();
}
my $html_output = &script_while_opened( $prefs, $dbh, $curr_mode, $form_values );
my $rv = $dbh->execute_command( {
'type' => 'database_close',
} );
if( $rv->is_error() ) {
return "Error: can't close database: ".$rv->get_error();
}
return $html_output;
}
sub script_while_opened {
my ($prefs, $dbh, $curr_mode, $form_values) = @_;
my $questions = $prefs->{question_list};
unless( ref( $questions ) eq 'ARRAY' and scalar( @{$questions} ) > 0 ) {
return "Error: no survey question list defined in prefs file";
}
my $dd_table = SQL::Routine->new_table( 'survey_data' );
foreach my $question (@{$questions}) {
unless( ref( $question ) eq 'HASH' and $question->{visible_title} ) {
return "Error: invalid question defined in prefs file";
}
$dd_table->add_column( {
'name' => $question->{name},
'data_type' => { 'base_type' => $question->{type}, },
'is_req' => $question->{is_required},
} ) or return "Error: invalid question defined in prefs file";
}
if( $curr_mode eq 'install' ) {
return &script_do_install( $dbh, $dd_table, $questions, $form_values );
}
if( $curr_mode eq 'remove' ) {
return &script_do_remove( $dbh, $dd_table, $questions, $form_values );
}
if( $curr_mode eq 'fillin' ) {
return &script_do_fillin( $dbh, $dd_table, $questions, $form_values );
}
if( $curr_mode eq 'report' ) {
return &script_do_report( $dbh, $dd_table, $questions, $form_values );
}
return "This is a simple demo. Click on the menu items to do them.";
}
sub script_to_install {
my ($dbh, $dd_table, $questions, $form_values) = @_;
unless( $form_values->{OK} ) {
# user is seeing screen for first time (did not click 'OK' button)
return join( "",
"<h1>Install Schema</h1>\n",
"<p>Do you want to install new schema to store answers for ",
"the following questions?</p>\n",
"<ol>\n",
(map { "<li>".$_->{visible_title}."</li>\n" } @{$questions}),
"</ol>\n",
);
}
# user saw screen and clicked the 'OK' button, so try to install;
# the following makes a Command of type 'table_create' and executes it
my $rv = $dbh->execute_command( $dd_table->new_command_create() );
if( $rv->is_error() ) {
return "Error: can't create survey table: ".$rv->get_error();
}
return "The new schema was successfully created.";
}
sub script_to_remove {
my ($dbh, $dd_table, $questions, $form_values) = @_;
unless( $form_values->{OK} ) {
# user is seeing screen for first time (did not click 'OK' button)
return join( "",
"<h1>Remove Schema</h1>\n",
"<p>Do you want to remove existing schema to store answers for ",
"the following questions?</p>\n",
"<ol>\n",
(map { "<li>".$_->{visible_title}."</li>\n" } @{$questions}),
"</ol>\n",
);
}
# user saw screen and clicked the 'OK' button, so try to destroy;
# the following makes a Command of type 'table_destroy' and executes it
my $rv = $dbh->execute_command( $dd_table->new_command_destroy() );
if( $rv->is_error() ) {
return "Error: can't remove survey table: ".$rv->get_error();
}
return "The new schema was successfully removed.";
}
sub script_to_fillin {
my ($dbh, $dd_table, $questions, $form_values) = @_;
unless( $form_values->{OK} ) {
# user is seeing screen for first time (did not click 'OK' button)
return join( "",
"<h1>Fill In Form</h1>\n",
"<p>Please answer the following questions. ",
"Those marked with a '*' are required.</p>\n",
(map {
'<p>'.($_->{is_required} ? '*' : '').$_->{visible_title}.":".
'<input type="text" name="'.$_->{name}.'" /></p>'."\n"
} @{$questions}),
);
}
# user saw screen and clicked the 'OK' button, so try to fillin;
# the following makes a Command of type 'data_insert' and executes it
my $dd_view = SQL::Routine->new_view( $dd_table );
my $rv = $dbh->execute_command( $dd_view->new_command_insert( $form_values ) );
if( $rv->is_error() ) {
return "Error: can't save form values in database: ".$rv->get_error();
}
return "Your form submission was saved successfully.";
}
sub script_to_report {
my ($dbh, $dd_table, $questions, $form_values) = @_;
# the following makes a Command of type 'data_select' and executes it
my $dd_view = SQL::Routine->new_view( $dd_table );
my $cursor = $dbh->execute_command( $dd_view->new_command_select() );
if( $cursor->is_error() ) {
return "Error: can't fetch form values from database: ".$cursor->get_error();
}
my $rowset = $cursor->get_all_rows();
my @html_output = (
"<h1>See Report</h1>\n",
"<p>Here are the answers that previous visitors gave:</p>\n",
"<table>\n",
"<tr>\n",
(map { "<th>".$_->{visible_title}."</th>\n" } @{$questions}),
"</tr>\n",
);
my @question_names = map { $_->{name} } @{$questions};
foreach my $row (@{$rowset}) {
push( @html_output,
"<tr>\n",
(map { "<td>".$row->{$_}."</td>\n" } @question_names),
"</tr>\n",
);
}
push( @html_output, "</table>\n" );
return join( "", @html_output );
}
1;SEE ALSO
Rosetta and the various other modules mentioned in its SEE ALSO.
AUTHOR
Darren R. Duncan (perl@DarrenDuncan.net)
LICENCE AND COPYRIGHT
This file is part of the Rosetta database portability library.
Rosetta 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 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 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 statically or dynamically with other modules is making a combined work based on Rosetta. Thus, the terms and conditions of the GPL cover the whole combination. As a special exception, the copyright holders of Rosetta give you permission to link Rosetta 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 (the version of Rosetta 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, and which is fully useable when not linked to Rosetta in any form.
Any versions of Rosetta 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 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 would appreciate being informed any time you create a modified version of Rosetta that you are willing to distribute, because that is a practical way of suggesting improvements to the standard version.