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: <CBpqBC.K4n@ig.co.uk>
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: <CBpqBC.K4n@ig.co.uk>
Organisation: Paul Ingram Group, Software Systems, +44 483 424424
Date: Fri, 13 Aug 1993 19:44:57 +0000
Message-ID: <CBpqty.K7H@ig.co.uk>
Sender: usenet@demon.co.uk
Lines: 161
DBperl API Specification. Version 0.5 DRAFT
Previous editor: Kurt Andersen <*>
Current editor: Tim Bunce <timbo@ig.co.uk> (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 <buzz@bear.com> Interbase
- Michael Peppler <mpeppler@itf.ch> Sybase
- Tim Bunce <tbunce@ig.co.uk> DBperl Switch & futures
- Ted Lemon <mellon@hemlock.ncd.com> 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: <CBpqBC.K4n@ig.co.uk>
Organisation: Paul Ingram Group, Software Systems, +44 483 424424
Date: Fri, 13 Aug 1993 19:45:40 +0000
Message-ID: <CBpqv4.K7K@ig.co.uk>
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.