Article 5003 of comp.lang.perl: Xref: feenix.metronet.com comp.lang.perl:5003 comp.databases:7094 comp.databases.oracle:3830 comp.databases.ingres:1830 comp.databases.sybase:2215 comp.databases.informix:2764 Newsgroups: comp.lang.perl,comp.databases,comp.databases.oracle,comp.databases.ingres,comp.databases.sybase,comp.databases.informix From: timbo@ig.co.uk (Tim Bunce) Path: feenix.metronet.com!news.utdallas.edu!wupost!howland.reston.ans.net!agate!doc.ic.ac.uk!warwick!qmw-dcs!qmw!demon!ig.co.uk!timbo Subject: DBperl. Database Independent API for Perl. Summary: Partial draft of DBperl API for review and comment Keywords: Perl, DBperl Organisation: Paul Ingram Group, Software Systems, +44 483 424424 Date: Fri, 13 Aug 1993 19:33:48 +0000 Message-ID: Sender: usenet@demon.co.uk Lines: 60 The following two messages contain Section 1 and Section 2 of the current draft DBperl API Specification. Extract from Section 1... =========================================================================== 1. INTRODUCTION TO DBperl =========================================================================== 1.1 Purpose of DBperl The purpose of the DBperl API (Application Perl-script Interface) is to create a common set of extensions (usubs) to perl to enable interaction between applications and various database engines. DBperl (yes this is the officially recognized and approved capitalization and spelling) will allow the creation of database-manipulation scripts without regard for the engine being used to service the SQL requests. 1.2 Background A number of independent efforts have been made to connect database engines to perl. Interfaces currently exist for Oracle, Sybase, Ingres, Interbase, Informix, Rdb, Unify and perhaps others that we are not aware of. All of these interfaces have APIs that are atuned to the peculiarities of their engine back-end and thus a perl script must be changed to work with different engines. 1.3 Current Work Since around October 1992, a group of interested parties, including the authors of some of the interfaces named above, have been working on (thrashing out) an engine-independent interface specification for what we are calling DBperl. The specification is currently at the 0.5 revision level and is nearing completion. It's around 2000 lines and 80Kb long. The first two sections are now being distributed to comp.lang.perl and the comp.databases.* newsgroups to gather some feedback and encourage interested parties to join in the (hopefully) final review process. --- If you are a user of an existing perl database interface then DBperl should be of interest because it will eventually replace your current interface. Anyone unfamiliar with the perl language is strongly recommended to fetch the perl FAQ files from convex.com and join comp.lang.perl. Section 1 contains details of how you can comment on or contribute to this work by posting to or joining our mailing list. Anyone with an interest is very welcome to join in, especially if you have experience working with database interfaces. Thankyou. Regards, Tim Bunce. Article 5004 of comp.lang.perl: Xref: feenix.metronet.com comp.lang.perl:5004 comp.databases:7096 comp.databases.oracle:3831 comp.databases.ingres:1831 comp.databases.sybase:2216 comp.databases.informix:2765 Newsgroups: comp.lang.perl,comp.databases,comp.databases.oracle,comp.databases.ingres,comp.databases.sybase,comp.databases.informix From: timbo@ig.co.uk (Tim Bunce) Path: feenix.metronet.com!news.utdallas.edu!tamsun.tamu.edu!cs.utexas.edu!uunet!pipex!uknet!warwick!qmw-dcs!qmw!demon!ig.co.uk!timbo Subject: Re: DBperl. Database Independent API for Perl. Keywords: Perl, DBperl References: Organisation: Paul Ingram Group, Software Systems, +44 483 424424 Date: Fri, 13 Aug 1993 19:44:57 +0000 Message-ID: Sender: usenet@demon.co.uk Lines: 161 DBperl API Specification. Version 0.5 DRAFT Previous editor: Kurt Andersen <*> Current editor: Tim Bunce (Version 0.5 onwards) Editors notes: lines of text that begin with an exclamation: ! are editors notes to the reviewers and not part of the spec. ! I have changed elements of the format and style since taking ! over from Kurt. Mainly to simplify the editing task. Generally ! there is less numbering, less indentation and more blank lines. ! Any comments/complaints etc gladly received. This section was last modified on $Date: 1993/08/13 19:04:50 $ =========================================================================== 1. INTRODUCTION TO DBperl =========================================================================== 1.1 Purpose of DBperl The purpose of the DBperl API (Application Perl-script Interface) is to create a common set of extensions (usubs) to perl to enable interaction between applications and various database engines. DBperl (yes this is the officially recognized and approved capitalization and spelling) will allow the creation of database-manipulation scripts without regard for the engine being used to service the SQL requests. 1.2 Background A number of independent efforts have been made to connect database engines to perl. Interfaces currently exist for Oracle, Sybase, Ingres, Interbase, Informix, Rdb, Unify and perhaps others that we are not aware of. All of these interfaces have APIs that are atuned to the peculiarities of their engine back-end and thus a perl script must be changed to work with different engines. 1.3 Current Work Since around October 1992, a group of interested parties, including the authors of some of the interfaces named above, have been working on (thrashing out) an engine-independent interface specification for what we are calling DBperl. The specification is currently at the 0.5 revision level and is nearing completion. It's around 2000 lines and 80Kb long. The first two sections are now being distributed to comp.lang.perl and the comp.databases.* newsgroups to gather some feedback and encourage interested parties to join in the (hopefully) final review process. 1.4 Guiding Principles It is hoped that DBperl will: o Provide an interface with sufficient capability to be useful. o Be simple to use for simple applications. o Have sufficient flexibility to accommodate unusual or proprietary functionality (events etc) and even non-sql databases. o Conform to or anticipate applicable standards where practical. Especially the X/Open & SQL Access Group SQL and CLI standards. o Enable the creation of database-independent perl scripts but not limit you to the lowest common functionality. o Be freely available. See below. o More exotic goals include: support for concurrent access to multiple database engines, dynamic loading of database interface modules, easy to implement database modules in C or perl code. 1.5 Contributors To This Specification & Database Engine Of Interest - Kurt Andersen <*> Informix - Kevin Stock <*> Oracle - Buzz Moschetti Interbase - Michael Peppler Sybase - Tim Bunce DBperl Switch & futures - Ted Lemon Ingres ... and a whole lot more people who have put up with the discussions and contributed from time to time on the mailing list (see below). <*> Unfortunately Kevin (a major contributor) and Kurt (the original specification editor) have both recently lost their access to the net. We always need more active contributors. Please join in this effort. 1.6 Intellectual Property Our collective intention is that all of the DBperl materials (DBperl API specifications, switch and modules) will be distributed under the same terms and mechanisms as perl itself (e.g., can be used under the GNU _or_ Artistic License). Thus individual Module implementors and DBperl application developers are free to exploit their work commercially. They are not required to release their work or source code, but we hope that they will. 1.7 For More Information Or To Contribute Constructive Feedback Very little of this specification has been arbitrarily chosen. If you are interested in the reasoning behind any particular portion of the API, please send your queries to the address below and you will probably get one (or more) versions of the discussions that have led up to this current formulation. Be warned that more than 9 months and over 250 messages of more than 800Kb have transpired, so the succinct version you receive will not preserve the nuances of the give-and-take that has happened. For feedback on this specification, please send that to the DBperl mailing list from whence it will be echoed to the interested parties: perldb-interest@vix.com If you would like to be added to (or removed from) the list of interested parties, please send your request to: perldb-interest-request@vix.com Archives of the perldb-interest mail and copies of the DBperl API specification are kept at ftp.demon.co.uk:/pub/perl/db/dbperl by kind permission of demon.net (Demon Internet Services Limited, UK). This archive also holds other database/perl related software (oraperl, sybperl, ingperl/sqlperl, uniperl, rdb, shql etc). Please contact one of the people listed above if you have specific questions about the handling of database engine peculiarities. 1.7 Structure Of The Specification Distribution Because the specification is fairly long it is divided into several parts (trying to keep each part < 250 lines for maximum mailer compatibility): 1) Introduction to DBperl (what you're reading now). 2) Structure, Conventions and Open Issues 3) Database Interaction Functions 4) Error and Event Handling 5) Data Type Conversion Functions 6) Data Dictionary Functions 7) The Switch and Modules 8) Variables 9) Standard Attributes 10) Examples 11) Appendix ! The order for sections 3 to 9 has yet to be decided. Eventually this specification will become a man page for DBperl (hopefully somewhat shorter then perl's man page :-). Each Module implementor will also supply a man page for their module in a standard format and style. End of DBperl API Section 1. Article 5005 of comp.lang.perl: Xref: feenix.metronet.com comp.lang.perl:5005 comp.databases:7097 comp.databases.oracle:3832 comp.databases.ingres:1832 comp.databases.sybase:2217 comp.databases.informix:2766 Newsgroups: comp.lang.perl,comp.databases,comp.databases.oracle,comp.databases.ingres,comp.databases.sybase,comp.databases.informix From: timbo@ig.co.uk (Tim Bunce) Path: feenix.metronet.com!news.utdallas.edu!tamsun.tamu.edu!cs.utexas.edu!uunet!tcsi.tcs.com!agate!doc.ic.ac.uk!warwick!qmw-dcs!qmw!demon!ig.co.uk!timbo Subject: Re: DBperl. Database Independent API for Perl. Keywords: Perl, DBperl References: Organisation: Paul Ingram Group, Software Systems, +44 483 424424 Date: Fri, 13 Aug 1993 19:45:40 +0000 Message-ID: Sender: usenet@demon.co.uk Lines: 241 This section was last modified on $Date: 1993/08/13 19:04:50 $ ============================================================ 2. CONVENTIONS, STRUCTURE and OPEN ISSUES ============================================================ 2.1 Architecture of a DBperl Application |<-- Scope of DBperl --->| .-. .-------------. .-------------. .------. | |---| XYZ Module |---| XYZ Engine | |Perl | |S| `-------------' `-------------' |script| |A| |w| .-------------. .-------------. |using |--|P|--|i|---|Oracle Module|---|Oracle Engine| |DBperl| |I| |t| `-------------' `-------------' | API | |c|... |funcs | |h|... Other modules `------' | |... `-' 2.2 Terms API -- Application Perl-script Interface The call interface and variables provided by DBperl to perl scripts. The API is implemented via the perl usub mechanism. Switch The code that provides the DBperl API and re-directs the DBperl function calls to the appropriate Module for actual execution. The Switch is also responsible for the dynamic loading of Modules, error checking/handling and other general duties. Module A Module implements support for a given type of Engine (database). Modules contain implementations of the DBperl functions written using the private interface functions of the corresponding Engine. Only authors of sophisticated/multi-database applications or generic library functions need be concerned with Modules. The Switch itself has a Module interface and can be conveniently treated as a Module by applications. Engine The actual "engine" that is being communicated with by a Module, e.g., Oracle, Ingres, Sybase etc. Note that Engines need not be databases, consider DNS, X.500, SNMP for example. MPI -- Module Programmer's Interface This is the interface provided by the Switch which is used to communicate with Modules. Only module implementors need be concerned with this interface. The Switch and MPI will take most of the work out of writing Modules for DBperl. Hopefully this will encourage people to implement Modules for their favourite database. Work on the MPI specification is unlikely to start until the DBperl specification is stable and implementation of the Switch has begun. The MPI is likely to provide both perl usub and C style interfaces so Modules can be implemented with whichever is the most appropriate. It should be possible to implement DBperl Modules in perl code! Handle Handles are opaque values returned by some functions which can be passed back to DBperl functions to refer to objects such as a Module, a database connection or a prepared SQL query. Library A perl package of utility functions to aid the DBperl user in performing common activities. Within the DBperl spec these functions are not separated out but are located in their appropriate sections and identified by the label 'Library Function'. Bundle The actual collection of DBperl Switch and DBperl Modules which are built and installed on the system where the perl script is being executed. TBD To Be Decided. Parts of the specification that have not yet been completed. These are generally section cross references. 2.3 Notation a) "Handles", if defined, are assumed to be active: $modh - designates a handle for a database module and is interchangeable (except in exotic cases) with the name of the module to which it connects. Typically users need not be concerned with module handles. $dbh - designates a handle for an open database connection and presumes a module designation. $qh - designates a query handle for a single prepared SQL statement and presumes a $dbh to which it is connected. There is no separate cursor handle in DBperl. The $qh serves as a handle for both the prepared statement and it's cursor. Multi-statements tied to a single handle are not supported. $hh - designates either a $qh, $dbh or $modh. Note that $hh is sometimes specified where a $modh would not be acceptable. Handle overloading is allowed, e.g., any function specified to accept a $modh parameter can also accept a $dbh or an $qh to indirectly specify the module. Similarly, any function that calls for a $dbh parameter can accept a $qh to specify the $dbh indirectly. This applies everywhere. b) Return values are noted as: $rc - return condition (or a handle), evaluates (in Perl) to boolean false on an error condition to facilitate short-cut chaining. E.g., &db_something() || die "db_something failed" $rv - return value, does not necessarily evaluate as false on an error condition. @ary - returns an array of values. Typically an error condition will result in an empty array being returned. 2.4 General Interface Rules & Caveats a) All data is returned to the perl script as perl strings. This allows arbitrary precision representations within the scope of the engine/module to be handled without loss of accuracy. Beware that perl conversions and other engines/modules may not preserve the same accuracy. b) Dates and times are returned as character strings in the native format of the corresponding Engine. Functions are provided to convert the native format values into integers representing the number of seconds since 1 January 1970, e.g., 'unix time'. Other conversion functions may be defined later. Timezone effects are engine/module dependent and can be deduced from (and in some cases affected by) parameters available from &db_getvalue(), q.v. c) Binary data types are not yet explicitly supported by DBperl. Perl itself supports binary data in perl strings and the Switch will pass binary data to the Modules without change. It is up to the Module implementors to decide how they wish to handle such binary data. d) Multiple SQL statements may not be combined in a single execution handle, e.g., a single $qh. This restriction has most impact upon Sybase users. An alternative approach may be possible. e) Positioned updates and deletes are not currently supported by DBperl. E.g., it is not possible to execute a select query, fetch a number of rows and then update or delete the 'current row' before fetching more rows. All cursors are assumed to be read-only and unnamed. This may change in the future. Individual Modules may provide private mechanisms but it's not currently part of this specification. 2.5 Naming Conventions a) The name space 'db_*' is reserved for DBperl functions, variables, packages etc. E.g., scripts using identifiers which begin with db_ may experience compatibility problems with future versions of the DBperl API. The package name 'db' is also reserved for future use. b) All environment variables used by the DBperl Switch or Modules begin with 'DBPERL_'. c) Attribute name-value pairs are used in many places throughout the DBperl specification. They provide a very simple and flexible way to determine or alter the behaviour of a Module or a particular database query. Attribute names and their values are described in detail in section TBD. For now it is only important to note that the case of the attribute name is used to signify who defined the meaning of that name and it's values. Case of name Meaning defined by ------------ ------------------ UPPER_CASE Standards, e.g., X/Open, SQL92 etc (portable) MixedCase DBperl API (portable) lower_case Module or engine specific (non-portable) 2.6 Each function is documented with the following general structure: 5.4.3 db_something $rv = &db_something($dbh, $arg [,%attr]); $dbh -- database handle $arg -- description of argument %attr -- associative array of additional attributes that can be passed to this function to affect it's behaviour $rv -- description of returned value Several sentences describing the purpose and behaviour of the function. Events: description of events/errors that may occur (if any) NO_MORE_ROWS -- ... Attributes: description of optional attributes (where appropriate) name, value -- description of attribute and it's possible values Events and errors are described in section TBD. Standard events and errors such as ERROR and WARN are also described in that section. Only events that are specific to a function are listed in that functions description. 2.7 Open Issues a) The contents of the DBperl Library are subject to expansion. Please let me know what other common patterns of SQL execution you think would be useful to DBperl application developers. b) Suggestions for additional attribute names and values to be accessible via functions &db_getvalue() and &db_setvalue() are requested. See section TBD. c) How to handle date and time intervals in a distinct manner from absolute dates and times is yet to be defined. d) Data typing issues in general have been put to one side for now (other than the absolute vs interval date/time issue noted above). It seems best to ignore them until implementations of the spec actually exist. e) Should we use a 'db' package or the db_* namespace? either &db'connect() or &db_connect() This may have an impact once we can implement modules in perl, e.g., Module 'xbase' could be implemented in "package db_xbase" and would have a function &db_xbase'connect() which would be called by the Switch as required. This would still work for db_xbase'db_connect but db_xbase'connect seems cleaner to me. Packages may have a greater role to play in Perl 5 than they have done in Perl 4. I have added the 'db' package name to the reserved name space so we can leave the issue open for now. End of DBperl API Section 2.