/ 
NoSQL-PL2SQL-1.14
River stage one • 2 direct dependents • 2 total dependents
/ NoSQL::PL2SQL::DBI

NAME

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

SYNOPSIS

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

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

## Primary User Methods

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

## Internally Used Methods

my @nvp = ( [ $name, $value, $isstring ], ... ) ;
my $perldata = $dsn->fetch( @nvp )->perldata ;
my %results = $dsn->insert( @nvp ) ;
my %results = $dsn->update( $recordid, @nvp ) ;
$dsn->delete( $recordid ) ;
$dsn->delete( @nvp ) ;

my $encoded = $dsn->encodestring( $text ) ;
my $recno = $dsn->lastinsert ;
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 ;
$tablename = $dsn->table ;

DESCRIPTION

NoSQL::PL2SQL::DBI provides an abstraction for connecting to an external database. Subclass definitions should be used for specific implementations. These methods may be generally useful, but are intended for use with NoSQL::PL2SQL, an object backing mechanism.

An end user implementing NoSQL::PL2SQL, or any class which implements this package, will access this driver using the constructor and the 3 methods listed above as "Primary User Methods".

Anyone implementing a subclass needs to understand the "Internally Used Methods" above.

Developers who are comfortable with RDB can design a thin object interface using any number of tools, such as DBIx::Class. NoSQL::PL2SQL is designed for developers of thicker objects that may be more logical and require data flexibility. For these developers, where the database is merely a mechanism for object persistance, NoSQL::PL2SQL provides a simple abstraction with a trivial interface, and great portability.

One of NoSQL::PL2SQL's features is a "universal" table definition that can accomodate arbitrary and indeterminate data structures. This flexibility means that a single table can be used for heterogeneous instantiations of different classes. In many cases, a single table can serve the data needs of an entire application. Consequently, a NoSQL::PL2SQL::DBI object is primarily defined by the tablename using a constructor argument.

A NoSQL::PL2SQL:DBI instance consists of one other property, a database handle. This handle is defined using the connect() method with the same arguments as the default DBI->connect() method. Otherwise, the default handle is a NoSQL::PL2SQL::DBI::Null object that simply reflects statement arguments, and can be useful for debugging.

The NoSQL::PL2SQL::DBI AUTOLOAD overrides any DBI method. Because the RDB table is abstracted within, SQL statements do not need to specify a table. The sprintf() notation is used instead- replacing '%s' in any SQL construction with the table name first. The sqlstatement() method is always used for this translation.

Additionally, NoSQL::PL2SQL::DBI provides versions of DBI->fetchrow_arrayref() and DBI->fetchrow_hashref- rows_array() and rows_hash() respectively. These methods take an SQL statement as an argument, perform preparation and execution, and return the same output as their counterparts.

rows_array() and rows_hash() may be used as a convenience. However, these methods required syntactically appropriate SQL instead of something independent of the underlying database. The fetch() method should be used instead of rows_hash().

If the output is piped into the perldata() method, fetch()->perldata, the results are a set of NVP's keyed in the recordid. All NoSQL::PL2SQL data structures are implemented as a tree of nodes. And each NVP (originally blessed as NoSQL::PL2SQL::Perldata) represents a node. In the NoSQL::PL2SQL::Perldata class, unblessed nodes are passed to static methods.

To ensure SQL independence, NoSQL::PL2SQL::DBI methods are called using a set of nvp arguments: Each arguments is an arrayref consisting of a string name, a scalar value, and a boolean to distinguish string values. The boolean argument controls the SQL construction and triggers encoding, via stringencode().

delete( $id ) shows a single scalar argument which is understood to mean delete( [ id => $id ] )>>.

The insert() method is trivial. Implementations only need to override the update() method. insert() needs to return a recordid value, which is determined by the underlying RDB application. Both insert() and update() return NVP's as a hash reference containing an element named "id". The other element, "sqlresults", contains the only useful output when the connected database is the default "NoSQL::PL2SQL::DBI::Null".

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

fetch()
update()
sqlupdate()
delete()
lastinsertid()
indexschema()
stringencode()

GENERAL USE

As of version 0.10, features have been added to make the DBI more useful for schemas other than the default. For example, delete now takes conditional arguments similar to fetch():

$dsn->delete( [ textkey => 20 ] ) ;

A more complicated example involves an update request, which requires two sets of nvp's: The first defines the values and the second defines a conditional. Since update() is a variation of insert() and accepts no conditional arguments, use sqlupdate() instead:

my @values = ( [ refto => 20 ] ) ;
my @conditions = ( [ objectid => 1 ] ) ;

my $dsn = NoSQL::PL2SQL::DBI->new('mytable') ;	## unconnected
print $dsn->fetch( @conditions ) ;		## pass conditional nvp's
print $dsn->insert( @values )->{sqlresults} ;	## pass value nvp's

## Generate the values clause as a separate string
my $nvp = NoSQL::PL2SQL::DBI->new('')->insert( @values )->{nvp} ;

## Pass that values clause into the sqlupdate() method
print $dsn->sqlupdate( $nvp, @conditions ) ;

## Prints: UPDATE mytable SET refto=20 WHERE objectid=1

Implementations of PL2SQL::DBI handle slight variations of SQL. And the translation instructions are distributed among various methods somewhat arbitrarily: The command syntax is defined in sqlupdate(); the values clause syntax is defined in update(); and the conditional clause is defined in fetch().

SCHEMA

The purpose of the schema is to build a data source that conforms to the NVP arguments of the above methods. The loadschema() method triggers the build. So implementations that override loadschema() can ignore the specification below. However, database applications that use SQL as an interface should be implemented consistently.

In NoSQL::PL2SQL::DBI and its implementations, the schema() method should return one or more SQL directives. The default loadschema() feeds each into NoSQL::PL2SQL::DBI->do(). Consequently, the SQL statements should always refer to the table name as '%s'. NoSQL::PL2SQL::DBI->schema() takes no argument. Instead, it uses an internal definition. The default definition, designed for MySQL, has an XML format using an ad-hoc XML definition. This definition may be replaced with a more universal standard, or hopefully prove to be suitably extensible.

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 output. 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 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.

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.

1 POD Error

The following errors were encountered while parsing the POD:

Around line 496:

Unterminated C< ... > sequence