The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
NoSQL-PL2SQL-1.21
River stage one • 2 direct dependents • 2 total dependents
++
/ NoSQL::PL2SQL::DBI

NAME

NoSQL::PL2SQL::DBI - Base Perl RDB driver for NoSQL::PL2SQL

SYNOPSIS

  ## Typical usage for PL2SQL

    package MyArbitraryClass ;
    use base qw( NoSQL::PL2SQL ) ;
    use NoSQL::PL2SQL::DBI::123SQL ;

  ## Primary PL2SQL operations

    my $dsn = new NoSQL::PL2SQL::DBI::123SQL $tablename ;
    $dsn->connect( $data_source, $username, $auth, \%attr ) ;
    $dsn->do('DROP TABLE %s') ;
    $dsn->loadschema ;

  ## Definition of 123SQL

    package NoSQL::PL2SQL::DBI::123SQL ;
    use base qw( NoSQL::PL2SQL::DBI ) ;

  ## Construction operation methods 

    $dsn->loadschema( $xmlschema ) ;

    ## These two statements are nearly equivalent.  Except the schema
    ## method never performs table name substitution.
    $dsn->schema( $xmlschema ) ;
    $dsn->new->loadschema( $xmlschema ) ;

  ## Conditional operation methods

    my @conditions = ( [ $name, $value, $isstring ], ... ) ;
    my @exclusions = $dsn->exclude( [ $name, $value, $isstring ], ... ) ;

    my @rows = $dsn->fetch( @conditions, @exclusions ) ;
    my $perldata = $dsn->fetch( @conditions, @exclusions ) ;
    $dsn->delete( @conditions, @exclusions ) ;

    $dsn->delete( $id ) ;       ## Equivalent to the statement below
    $dsn->delete( [ id => $id ] ) ;

  ## Data assignment operation methods

    my @nvp = ( [ $name, $value, $isstring ], ... ) ;

    my $results = $dsn->insert( @nvp ) ;
    my $results = $dsn->update( $recordid, @nvp ) ;

  ## Combined operation methods

    $dsn->sqlupdate( 
                $dsn->new->update( undef => @nvp )->{nvp},
                @conditions ) ;

  ## Internally used methods
  
    my $response = $dsn->do( $sqltext ) ;
    my $encoded = $dsn->encodestring( $text ) ;
    my $recno = $dsn->lastinsertid ;
    my @sql = $dsn->schema ;
  
  ## Utilities and debugging

    $dsn->sqldump( $reset = 1 ) ;
    $dsn->debug( $arbitrarystring ) ;
    print join "\n", $dsn->sqldump() ;

    my @fetchrows = $dsn->rows_hash('SELECT * FROM %s WHERE objectid=1') ;
    my @fetchrows = $dsn->rows_array('SELECT * FROM %s WHERE objectid=1') ;

    my $sql = $dsn->sqlstatement( $sqlarg ) ;
    my $db = $dsn->db ;
    my $tablename = $dsn->table ;

DESCRIPTION

NoSQL::PL2SQL::DBI was developed as part of NoSQL::PL2SQL to provide an abstract representation of an RDB table. Database specific implementations should be defined as subclasses to account for differences in SQL syntax, data definitions and features.

NoSQL::PL2SQL users only need the constructor and the 3 methods listed above as "Primary PL2SQL operations". In the PL2SQL interface, a NoSQL::PL2SQL::DBI instance is used to represent a data source.

A NoSQL::PL2SQL::DBI instance represents a single table in an RDB, with two properties: A database handle and a string to reference a particular table. Thus, there are a couple accessor methods, but mostly this class is responsible for the SQL translation.

PL2SQL simply provides a persistance mechanism for Perl objects that's tied to an RDB table. Its interface is implemented primarily as declarations and definitions, with only a handful of procedural operations- there's no correspondence between an SQL operation (eg SELECT) and a PL2SQL function call (eg fetch). Internally, of course, PL2SQL needs some SQL interface. This functionality is consolidated in the NoSQL::PL2SQL::DBI package. Consquently, users who want a translator for low-level database access can use NoSQL::PL2SQL::DBI directly.

There are basically four types of operations:

Construction Operations

Operations such as SQL CREATE that are not called as part of normal operation require a bit more preparation. The PL2SQL::DBI method loadschema() performs these functions. Its single argument contains all the definitions as block of XML text.

Conditional Operations

Operations such as SQL SELECT and DELETE use defined conditionals. The corresponding PL2SQL::DBI methods are fetch() and delete().

Data Assignment Operations

Operations such as SQL INSERT use only NVP's (name value pairs) to correspond to the data assignments. SQL UPDATE also falls into this category when the changes are to be applied to a single row record. The corresponding PL2SQL::DBI methods are insert() and update().

Combined Operations

When a SQL UPDATE request needs to be applied to multiple row records, both NVP and Conditional definitions are required. This operation is seldom required, and is a bit more cumbersome. The corresponding PL2SQL::method is sqlupdate().

The methods listed above require arguments to define the conditionals and NVP's. Each conditional or NVP term is represented by an array consisting of a name, value, and optional boolean that explicity identifies the value as a string. An undefined value corresponds to an SQL NULL. Multiple terms can be passed to these methods.

A conditional term can be defined as an exclusion using the exclude() command. Exclusion conditionals are treated as logical inversions.

The update() method has a special requirement: The first argument must be a scalar that identifies a particular table row record or the resulting SQL has no conditional clause.

The fetch() method returns data instead of a confirmation count. In an array context, fetch() returns records in the same format as DBI::fetchrow_hashref(). The row data is nearly the same when fetch() is called in a scalar context. Each row is a value in a hash reference, keyed on the row id.

The insert() and update() methods have been overloaded to return a complex value:

  $results->{id}        record id resulting from an insert operation
  $results->{sql}       sql results of an operation
  $results->{nvp}       the name/values clause of the SQL translation

The sqlupdate() method requires both conditional and data assignment arguments. To distinguish between them, the data assignment arguments are first fed into the update() method, the name/values clause is extracted via the result's nvp property, and that string is passed as the first scalar argument to the sqlresults() method.

The ability to extract literal SQL translations is a feature built into every method. Since the $dsn data source instance needs an active database handle to be effective, a literal SQL translation is returned on every method of an unconnected instance. This feature can be useful for debugging:

  ## This statement performs a database operation
  $results = $dsn->update( 5, [ stringdata => 20, 1 ] ) ;

  ## This statement does not perform a database operation
  $results = $dsn->new->update( 5, [ stringdata => 20, 1 ] ) ;

  ## The return value may vary in this context
  $response = $dsn->new->fetch( 5 ) ;

In the first two update() examples, the return value has the same structure regardless of whether a database operation has been performed. $results->{sql} contains the results of the operation. In the first, this value reflects the number of affected table rows. In the second, this value contains translated SQL. The difference between the two is that the second example applies the update() method to a transient instance created with the new() constructor.

In fetch(), delete() and the other conditional operation methods, the sql translation is returned directly. With no database handle, the fetch() results are the same in scalar or array context.

The do() method performs the second half of these operations and applies the translated SQL as a database operation. Extending the examples above:

  $dsn->do( $results->{sql} ) ;
  $dsn->do( $response ) ;

The do() method always takes a string SQL representation. Because the table name is embedded as an instance property, the SQL argument may use '%s' in place of the table name. The do() method automatically performs the substitution.

NoSQL::PL2SQL::DBI acts like a subclass of DBI in that AUTOLOAD() attempts to apply any undefined methods to the internal database handle. Every invocation is called like the do() method: AUTOLOAD assumes a single string argument, and applies the '%s' table name substitution describe above.

SCHEMA

Most SQL operations can be easily implemented as simple methods, but we have not yet acknowledged the 800 pound gorilla required to map the SQL Create operations to a function.

The first challenge is that the Create function is fairly complex and implementation dependent. However, since XML and SQL have many similar properties, XML is used to define generic schemas. Specific methods are used to translate XML nodes into SQL clauses. Parent node translations roll all the clauses into a single SQL statement. NoSQL::PL2SQL::DBI::Schema and its subclasses provide the engine recursing through structured XML.

Overloaded methods can be used for specific translation requirements. So ideally, the XML definition should be independent of the database implementations. Unfortunately, loadschmema() only performs a syntactical translation. Any differences in the definition arising from implementation specific data type definitions or features need to be explicit in the XML definition.

Consequently, the inelegant solution is to use a different XML schema in each of NoSQL::PL2SQL::DBI's implementation subclasses. Two are actually defined: In addition to $xmlschema, $indexschema is used by NoSQL::PL2SQL::Simple. Ultimately, others need to be able to extend PL2SQL with their own data definitions, and also use the features independently of PL2SQL. However, this problem is currently overshadowed by the need for more DBI subclass implementations.

IMPLEMENTATION SUBCLASSES

Currently, only MySQL and SQLite implementation subclasses are included in the PL2SQL package. The remaining discussion is for developers who wish to build new implementation subclasses.

The following methods are implemented, by default, to use an SQL syntax compatible with MySQL and SQLite. Other RDB implementations may require overriding these methods:

fetch()
update()
sqlupdate()
delete()
lastinsertid()
stringencode()
Schema definitions

The loadschema() should not be overridden. The schema() method is responsible for the XML to SQL translation. Through a subclassing system, each XML node calls a distinct schema() method for translation.

There are two default schema() definitions. The first, NoSQL::PL2SQL::DBI->schema(), converts the XML definition into an XML::Parser::Nodes tree. This tree is reblessed into another package as follows:

  return bless( $nodes, ref( $dsn ) .'::Schema' )->schema() ;

Consequently, there is a second default schema called NoSQL::PL2SQL::DBI::Schema->schema(), (For convenience, these two will be distinguished as schema() and Schema->schema().) An implementation must be defined as follows, using 123SQL as an example implementation.

  package NoSQL::PL2SQL::DBI::123SQL ;
  use base qw( NoSQL::PL2SQL::DBI ) ;

  package NoSQL::PL2SQL::DBI::123SQL::Schema ;
  use base qw( NoSQL::PL2SQL::DBI::Schema ) ;

By default, Schema->schema() calls the schema method on its child nodes. For example, each SQL statement is represented by an <sql> node. In order to return an SQL statement, the following must be defined (using the same example):

  package NoSQL::PL2SQL::DBI::123SQL::Schema::sql ;
  use base qw( NoSQL::PL2SQL::DBI::Schema ) ;

This definition, however, is only required for explict SQL translation. Otherwise, the default Schema->schema() method is called in recursion on the next level of child nodes. The nodes below are shown as XML and with defined methods:

  ## <table command="CREATE" ...>
  ##   <column ... />
  ## </table>

  package NoSQL::PL2SQL::DBI::123SQL::Schema::table ;
  use base qw( NoSQL::PL2SQL::DBI::Schema ) ;

  sub schema {
        my $self = shift ;
        return $self->command ;
        }

  sub CREATE {
        my $self = shift ;
        my @columns = NoSQL::PL2SQL::DBI::Schema->schema( $self ) ;
        ## combine columns into a single SQL directive
        }

  package NoSQL::PL2SQL::DBI::123SQL::Schema::table::column ;
  use base qw( NoSQL::PL2SQL::DBI::Schema ) ;

  sub schema {
        my $self = shift ;
        ## return column definition
        }

The XML node shown above, named table, is processed by Schema->schema(), and its explicitly defined Schema::table->schema() method is called. That method punts to another method, defined by the "command" attribute of the node, and the Schema::table->CREATE() method is called in turn. That method gets its child schemas by calling the default Schema->schema() method. At this point, the package names of the child schemas start accumulating, and each of those schema() methods return substrings that are combined into a single SQL directive.

To summarize, a schema definition requires the definition of a number of package classes. The package names correlate to the structure of the node tree (see XML::Parser::Nodes::tree()). Each package class needs to extend NoSQL::PL2SQL::DBI::Schema, and may or may not override the schema() method. Output can be varied by defining methods that correspond to the "command" attribute.

In general, there's probably no need to define a package unless the schema() method will be overridden. But consider the following definitions:

  package NoSQL::PL2SQL::DBI::MySQL::Schema ;   ## The Schema
  use base qw( NoSQL::PL2SQL::DBI::Schema ) ;   ## The Base Schema

  package NoSQL::PL2SQL::DBI::MySQL::Schema::table ;    ## A Node Schema
  use base qw( NoSQL::PL2SQL::DBI::Schema ) ;   ## The Base Schema

  ## Not defined but part of the model
  package NoSQL::PL2SQL::DBI::Schema::table ;   ## A Base Node Schema

For undefined packages, the inheritance order is:

Base Node Schema >> Schema >> Base Schema

A package may be defined without an overriding schema() definition in order to define a different inheritance.

EXPORT

None by default.

HISTORY

0.01

Original version; created by h2xs 1.23 with options

  -AXCO
        NoSQL::PL2SQL
0.02

Cleaned perldoc formatting issues

Added optional arg to schema() method

0.03

Added optional arg to schema() method

0.04

Added debug() method

0.05

Generalized fetch() and perldata() methods to handle arbitrary schemas.

perldata() arguments are now explicitly defined.

delete() now accepts the same arguments as fetch().

With an argument table() creates a second DSN instance chained via db().

0.10

Added sqlupdate().

Added nvp element to update()'s return value.

Fixed a bug in the $xmlschema "CREATE INDEX" node.

Modified sqlstatement()

Added indexschema() for NoSQL::PL2SQL:Simple.

0.11

perldata() now always returns a hash ref and fetch() always returns an array. In order to combine duplicated functionality, perldata() is now invoked as $dsn->fetch()->perldata.

0.12

Added exclude() method.

Removed the pesky perldata() method- this output is now returned by fetch() in scalar context. NO BACKWARDS COMPATIBILITY.

Rewrote the documentation to reflect less dependence on PL2SQL.

SEE ALSO

NoSQL::PL2SQL
XML::Parser::Nodes
http://pl2sql.tqis.com/

AUTHOR

Jim Schueler, <jim@tqis.com>

COPYRIGHT AND LICENSE

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.