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 ) ;
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.
perldata() is nearly the same as rows_hash(), except the output is a hash reference that keys each record on its recordid. Originally, the hashref was blessed as a NoSQL::PL2SQL::Perldata object, hence the name. All NoSQL::PL2SQL data structures are implemented as a tree of nodes. And the static methods in NoSQL::PL2SQL::Perldata are used to access the RDB records as though they were tree nodes.
All RDB inquiries made by NoSQL::PL2SQL expect a hashref structure similar to perldata()'s. As of v1.0, only the fetch() method is used. Additionally, NoSQL::PL2SQL passes its requests as a list of NVP's (name value pairs). The nvp arguments are arrayrefs consisting of a string name, a scalar value, and a boolean that identifies the value as a string. The boolean argument controls the SQL construction and triggers encoding, via stringencode(). delete(), however, optionally takes a recordid as a single argument.
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:
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 SchemaFor undefined packages, the inheritance order is:
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()andperldata()methods to handle arbitrary schemasperldata()arguments are explicitly defineddelete()now accepts the same arguments asfetch()With an argument
table()creates a second DSN instance chained viadb().
SEE ALSO
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.