A Universal Database Definition

PL2SQL Overview

PL2SQL was inspired by a meeting last summer when someone recommended Drupal because the underlying MySQL RDB platform is so much more robust.  I have no idea if that assertion is true.  But terrified that all future software would be as bloated and unwieldy as Drupal, I starting working on a lightweight and more universal alternative.
PL2SQL is a package that marshals any arbitrary data structure/object into records of an RDB table, and fulfills 4 additional objectives:
  • Has a simple API
  • Stores data of arbitrary complexity
  • Performs partial updates of large object data
  • Is independent of the underlying database
PL2SQL's API is accessible with a single statement, SQLObject, which is a replacement for Perl's built-in bless operator.  Perl's bless operator is used to declare an object. PL2SQL->SQLObject is nearly identical, but requires an additional argument that identifies an RDB table as the data source.
Perl consists of 3 primitive data structures: scalars, arrays, and associative arrays. This limited set, as well as some advanced features, led to Perl's selection for my reference implementation.  In addition to handling any combination of primitives and class associations, PL2SQL also supports internal references.
PL2SQL uses a universal table schema so that a single table can store hetero-
geneous objects.  Consequently, an object implementing PL2SQL is not constrained by any external table definition.  Ultimately, all project data can be consolidated within a single RDB table.
A database application such as MySQL performs numerous functions.  PL2SQL performs only one of them: storing and retrieving structured data.  Ideally, the other features should be defined in a PL2SQL subclass.  This subclass, PL2SQL::Simple, provides a complete API that is more functionally consistent with SQL.
Traditional RDB table data is represented as an NVP set.  As an NVP (Name Value Pair), any data element in a record can be accessed by its name, e.g. CreationTime. If all records contain the same names, the data can be represented in a two dimension table: Each record corresponds to a row, each name corresponds to a column, and each value corresponds to the intersecting cell.  Values are always scalars (in Perl terminology, a number or text, but not a set).
Since PL2SQL::Simple extends PL2SQL, queries return objects (and sets of objects) instead of RDB records.  Generally, Perl programmers use database interfaces to convert records into objects or primitive data structures, so this feature reduces a bit of coding overhead.  At any rate, in order to conform with common practice, PL2SQL::Simple objects must have an NVP structure.  Or in Perl terms, the object must be a blessed associative array.
From there, PL2SQL::Simple is designed to provide an optimal combination of RDB/SQL and NoSQL features.  Primarily, PL2SQL::Simple is independent of the database (it uses two universally schemed tables instead of one). And the record objects can be arbitrarily complex, provided they are structured as associative arrays.  Moreover, the API includes data definition methods and explicit reading, writing, and querying operations that are consistent with traditional database interfaces.
In order to support querying, the PL2SQL::Simple record objects must conform to a data definition, like a RDB/SQL record, with these differences:
  • Data definitions are only required for queries.
  • Each Record object can contain other arbitrary element names for data that will not be queried.  These elements do not need to be scalar.
  • The data definition is established as part of the class definition- that is, independently of the database table- allowing a single table to contain a variety of separately defined objects.
By virtue of this last feature, a fully normalized data schema can be implemented using only the two tables that underlie PL2SQL::Simple.
One of Perl's best features is its OO implementation.  Objects in Perl 5 are merely one of the primitive data structures declared (blessed) as a defined class.  Class methods are helpful, because they can embed assumptions about their data component.  But the underlying data can always be accessed independently of the class definition.  This model minimizes data preparation and transformation (for example, no operations to slurp database records into objects). And PL2SQL specifically takes advantage of this feature.
Perl's philosophy of hubris, laziness, and impatience once set the gold standard for Rapid Application Development.  But the language no longer enjoys widespread use.  And although PL2SQL is designed with the same philosophy, I am hoping for a more universal appeal.  So currently, I am seeking collabatorators to help me port these applications to other languages.
Another new CPAN module today, Contextual::Return::Wrapper. If nothing else, this initiative represents an attempt to formalize my code development.
Contextual::Return::Wrapper is the result of a more topical initiative, NoSQL::PL2SQL::DBIxClass.  This new module is basically complete, tested, and ready to go.  Probably in a week or so.
Alternatively named NoSQL::PL2SQL::DBI::DBIxClass because this new package is modelled on the NoSQL::PL2SQL::DBI interface.  Both DBIxClass.pm and DBI.pm create an abstract database interface.  So DBIxClass is both an implementation and replacement of the original NoSQL::PL2SQL::DBI.
Self-consciously, I'm aware that DBI may have sunk significant resources to re-invent the wheel.  Yes and no.  At the outset, it wasn't obvious that the constraints of DBIx::Class wouldn't compromise the design objectives of NoSQL::PL2SQL. In fact, NoSQL::PL2SQL::DBI (and consequently DBIxClass) is a bit more abstract than DBIx::Class.  Undoubtedly, some problems were avoided as a result of this extra effort.
Comments Leave a comment
  • Comments Script
    Nov 3 2012
    TQIS Jim
    0 Replies
  • Requested NoSQL::PL2SQL from CPAN
    Oct 8 2012
    TQIS Jim
    1 Replies
  • groups.google.com NoSQL Discussion
    Oct 2 2012
    Konstantin Osipov
    1 Replies