Author image Jim Schueler
and 1 contributors


NoSQL::PL2SQL - Relational Database Persistence for Perl Objects


NoSQL::PL2SQL is intended for class designers. An example of a class that implements NoSQL::PL2SQL, MyArbitraryClass, is defined below.

  package MyArbitraryClass ;
  use base qw( NoSQL::PL2SQL ) ;

  ## define a data source
  use NoSQL::PL2SQL::DBI::SQLite ;
  my $tablename = 'objectdata' ;
  my $dsn = new NoSQL::PL2SQL::DBI::SQLite $tablename ;
  $dsn->connect( 'dbi:SQLite:dbname=:memory:', '', '') ;

  # Preloaded methods go here.

  sub new {
        my $package = shift ;
        my $object = @_? shift( @_ ): {} ;
        my $self = $package->SQLObject( $dsn, $object ) ;
        printf "userid: %d\n", $self->SQLObjectID if ref $object ;
        return $self ;

  sub error {
        my $self = shift ;
        $self->SQLRollback ;
        warn "unrecoverable error" ;

  sub clone {
        my $self = shift ;
        return $self->SQLClone ;

  ## Should not be necessary
  END {
        ## Destroy all instantiations

The main requirement is that the class inherit NoSQL::PL2SQL methods. Second, a data source needs to be defined. One of NoSQL::PL2SQL's features is a "universal" data structure that can accomodate heterogenous, complex data structures. As a result the table that defines the data source can be shared among different classes.

NoSQL::PL2SQL::DBI contains access methods used by NoSQL::PL2SQL. In addition to the shown constructor and connector, $dsn->loadschema is used to build the datasource table when an application is installed.

NoSQL::PL2SQL's interface is intended to be built into an implementing class's constructor, generally in place of the bless statement. In this example, the AnyArbitraryClass->new() constructor can be invoked three ways. First, with no arguments, the constructor returns an empty persistent blessed hash reference. Second, if the single argument is a hash reference, the constructor converts the structure into a persistent blessed object. Third, if the argument is valid, numeric ObjectID, the constructor returns the stored object that exactly matches the state when it was previously destroyed.

In this example, when a persistent object is initialized, its ObjectID is printed out. Naturally, this is a clumsy way to maintain object references, although personally, I am not above hardcoding a reference into an HTML document.

Another option is to use a fixed object as an index to other objects. Unfortunately, as of this writing, NoSQL::PL2SQL has no features for object locking, and this strategy would quickly foul up in a multi-user environment. A third option is to define another data source to map the ObjectID to another key, such as a user's email address. A fourth, more complicated example is shown below.

Objects are automatically written when they are destroyed. This feature can be disabled by calling SQLRollback(). Another solution is to create an untied cloned object, using SQLClone(). Modifications to the clone are destroyed along with the object.

Results have been erratic and unsatisfactory when object destruction is postponed until global destruction- although experienced programmers will always scope the objects they use. If a particularly robust solution is required, maintain a univeral list of all instantiations and explicitly destroy each one using an END{} clause, as shown.


Apparently, many programmers, when envisioning a new project, think about database schemas and imagine the SQL statements that join everything together. Well into the design, they implement a thin object, using one of the numerous solutions available, to create an OO interface for their application.

I say "apparently" and "many" because I am not among them. When I envision a new project, I think about classes, interfaces, and imagine lines of inheritance. Well into the prototype, I am still using native marshalling mechanisms, and when the time comes to add robust and portable persistence, I consider the database design a nuisance.

There are fewer tools for programmers like me- Although some of the NoSQL initiatives are starting to attract attention. This design started with some specific objectives to meet my needs, and a few additional features added along the way.

1. Most importantly, the interface needs to be simple and unobtrusive.
2. The data implementation needs to be flexible and portable- intended to be rolled out on any shared server that provides MySQL.
3. The implementation should be relatively lightweight, fast, and minimize resources.

The interface is intended to be a drop-in replacement for Perl's bless operator. The SQLObject() method returns a blessed object that is automatically tied to NoSQL::PL2SQL's RDB persistance.


If you're comfortable writing closures or anonymous subroutines, V1.03 uses error handlers. An application or implementing class can now resolve problems during run-time. Most errors are thrown for the SQLObject() method- although SQLClone() can be invoked as an alias for SQLObject(). The following errors are defined, the triggering method is shown in parentheses.

BlessedCaller (SQLObject)

SQLObject() should be called as a constructor. Its first argument must be a classname string. Otherwise, this error is thrown. SQLClone() is intended to be called as an object method.

InvalidDataSource (SQLObject)

If the first argument does not instantiate NoSQL::PL2SQL::DBI, this error is thrown.

InvalidObjectID (SQLObject)

If the first argument is valid, this error is thrown if the second argument is missing.

UnconnectedDataSource (SQLObject)

The data source must be connected using the DBI::Connect() method or this error is thrown.

DuplicateObject (SQLObject)

If thrown with 3 valid arguments, SQLObject() will try to assign the second argument as an ObjectID. If that ObjectID has already been assigned, this error is thrown.

ObjectNotFound (SQLObject)

If the second argument is a scalar it is understood to be an ObjectID. SQLObject() will either retrieve the mapped object or throw this error.

CorruptData (SQLObject)

An ObjectID assigned by SQLObject() matches the record number of the node tree header. The header record is identified where reftype == 'perldata'. Otherwise, this error is thrown.

TableLockFailure (DESTROY)

As of V1.2, PL2SQL attempts a lock operation on the table before writing updates. Instead of blocking indefinitely, the lock attempt fails after 10 seconds. This handler should be overridden to safely dump updates and notify an administrator.

By default, SQLObject() errors carp an English text message and return undefined. A custom error handler could translate the message, return a custom error value, or recurse with different arguments. Since SQLObject() always returns a non-scalar on success, any scalar can be used to identify an error.

To assign a handler, use the following format:

  ## $function is an anonymous function or closure
  my $function = sub {
        my $package = shift ;   ## First argument to SQLObject
        my $errorid = shift ;   ## One of the error keys listed above
        my $errorobj = shift ;  ## A hash reference containing useful state
                                ## data.  Usually keyed on $errorid
        my $textmessage = pop ; ## The default English error message
        my @args = @_ ;         ## The remaining arguments passed to SQLObject

        ## Handler code goes here
        } ;

  my @keys = SQLError( $key => $function ) ;
  ## $key is one of the keys listed above

For convenience, SQLError() returns a list of all the active error keys.


As part of the design objectives, my applications need to be able to migrate their data to NoSQL::PL2SQL. Primarily, I need to specify the object ids that key each object. (Did I mention these are hardcoded in my HTML?) In order to specify an object id, see the example below.

When specifying object ids, care should be taken to ensure that each id is unique. For that reason, an application should either consistently assign ids or always use automatic assignments. For practicality, this uniqueness constraint only applies to objects in a given class. In other words, the key definition is a combination of the class name and object id. Since the class name is a string, the schema can be manipulated to use string keys instead of numerics. The MyArbitraryClass is redefined below to illustrate this approach.

  ## SQLObject creation using a specified object id
  # MyArbitraryClass->SQLObject( $dsn, $objectid => $dataobject ) ;

  package MyArbitraryClass ;
  use base qw( NoSQL::PL2SQL ) ;

  sub new {
        my $package = shift ;

        return warn unless @_ ;
        my $object = shift @_ ; ## a user record or email scalar

        my $self = ref $object? 
                          $object->{email}, $dsn, 0, $object ):
                        NoSQL::PL2SQL::SQLObject( $object, $dsn, 0 ) ;
        return bless $self, $package ;

The constructor is called with a single argument that is either the email address as scalar string, or a user record where the email address is an element called "email". With this approach, the email address masquerades as a class name. The object id is not accessible to the user, so it should consistently be set to a value such as 0. Using this strategy, a user record is accessed whenever a user enters an email address.


XML::Dumper::pl2xml provides the basic mechanism for converting an arbitrary complex Perl data structure into an XML tree. XML::Parser::Nodes treats this tree as a set of identical nodes. Originally, NoSQL::PL2SQL::Node simply extended this module by adding a method to write itself as a RDB table record. The name PL2SQL is a reference to this legacy.

Hopefully, users will infer PL2SQL also includes SQL2PL functionality, so implementing objects can move back and forth between persistent storage and active use. The approach is to retain the node tree structure, and use Perl TIE magic to make the node containers appear as the original object members. The SQL2PL functionality is embodied in the NoSQL::PL2SQL::Object package, which defines the TIE constructors and all overloading methods.

NoSQL::PL2SQL::DBI defines a data abstraction for accessing the data sources. The raw RDB record data is accessed using methods in the NoSQL::PL2SQL::Perldata package. Each of the modules contains detailed information about their architecture and internal operations.


None by default.




Jim Schueler, <>


Copyright (C) 2012 by Jim Schueler

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.8.9 or, at your option, any later version of Perl 5 you may have available.