NAME

XML::Object::DBI - Methodical DBI Abstraction

SYNOPSIS

perl

    package Foo;

    use base qw(XML::Object::DBI);

    sub new {
	my $class = shift;
	my $self = $class->SUPER::new(@_);

	# ...

	return $self;
    }

    sub method1 {
	my $self = shift;

	# simple API for data source accessors: no args
	for my $hashref ($self->get()->hashrefs) {
	    $self->do_something_with($hashref);
	}
    }

    sub method2 {
	my $self = shift;

	# positional sprintf-like bindings
	for my $hashref ($self->get([ 'foo', 'bar' ])->hashrefs) {
	    $self->do_something_with($hashref);
	}
    }

    sub method3 {
	my $self = shift;
	my $mutator = $self->setIter(); # similar API for mutators 

	# named template-style bindings
	my $bind = { 'alpha' => 'beta', 'gamma' => 'vlissides'};
	my $get = $self->get($bind);

	# row-at-a-time
	while ($arrayref = $get->arrayref) {
	    $mutator->(...);
	}
    }

    sub method4 {
	my $self = shift;
	my $get = $self->get([ 42 ]);
	my $scalar = $get->scalar();
    }

XML

    <config>
	<new>
	    <dsn>dbi:Pg:dbname=whatever;host=wherever</dsn>
	    <user>foo</user>
	    <password>bar</password>
	</new>

	<method1>
	    <!-- no args -->
	    <get>SELECT * FROM foo WHERE bar = 'baz';</get>
	</method1>

	<method2>
	    <!-- positional -->
	    <get>SELECT * FROM bar WHERE %s = '%s';</get>
	</method2>

	<method3>
	    <!-- named -->
	    <get>SELECT * FROM baz WHERE %alpha = '%beta';</get>
	    <!-- SQL can be inline or external -->
	    <set path="example.sql" />
	</method3>

	<method4>
	    <!-- single row, single column -->
	    <get>SELECT foo FROM bar WHERE unique_id = %d;</get>
	</method4>

    <config>

DESCRIPTION

    XML::Object::DBI is designed to make it easy:
    
	a) to work smoothly and conveniently with SQL without getting
	   tangled in DBI duct tape
	b) to move SQL out of Perl and into XML or SQL files

    XML::Object::DBI adds get() and set() methods to the base XML::Object class.

    These provide a DWIMish interface to the usual relational accessors
    and mutators.

    In addition, various utility functions and exceptions are provided.

METHODS

new

usage

my $dbi = XML::Object::DBI->new($config_file, %optional_xml_simple_options);

description

Constructs a new XML::Object::DBI object.

Takes the path to a configuration file as an argument, and optionally a HASH of args to be passed to XML::Simple's XMLin() method - which is used to convert the specified XML into the object's configuration HASH/ARRAY ref.

Typically this method is not called directly, but rather invoked on the superclass by the user-supplied subclass of XML::Object::DBI. However, there are circumstances under which a direct invocation is preferable: for instance, the manipulation of multiple data sources can easily be achieved through aggregation:

    package Migrator;

    use XML::Object::DBI;

    sub new {
	my class = shift;
	my $self = {};
	$self->{SOURCE} = XML::Object::DBI->new($source_config);
	$self->{DESTINATION} = XML::Object::DBI->new($destination_config);
	bless $self, ref $class || $class;
    }

    sub migrate {
	my ($self, $arg1, $arg2) = @_;
	my $source = $self->source();
	my $destination = $self->destination();
	my $get = $source->get([ $arg1, $arg2 ]);
	my $row;

	while ($row = $get->hashref) {
	    $destination->set({ foo => $row->{foo}, bar => $row->{bar} });
	}
    }

For subclasses of XML::Object::DBI, the constructor can be invoked via the SUPER() builtin:

    use base qw(XML::Object::DBI);

    sub new {
	my $class = shift;
	my $self = $class->SUPER::new(@_);

	# ... 
    }

XML::Object::DBI::new returns a blessed HASH ref whose members are stored under the key '_XML_OBJECT_' e.g.

bless { _XML_OBJECT_ => ... }, 'XML::Object::DBI'

Subclasses will need to bear this in mind if adding new members.

The XML file whose path is specified by $config_file can define a data source and optionally a username and password in its <new> ... </new> section.

e.g.

    <config>
	<new>
	    <dsn>dbi:Pg:dbname=whatever;host=wherever</dsn>
	    <user>foo</user>
	    <pass>bar</pass>
	</new>
	...
    </config>

The contents of the <dsn> ... </dsn> tag should be a valid DBI data source name. Valid synonyms are:

<datasource> ... </datasource>

and:

<data_source> ... </data_source>

The username and password tags are both optional, but should be empty strings where the connection requires it:

    <new>
	<data_source>dbi:ODBC:FMPro</data_source>
	<username></username>
	<password></password>
    </new>

Valid synonym for user:

<username> .. </username>

Valid synonym for password:

<pass> .. </pass>

If these connection details are not provided in the XML, the dbh() method will need to be called manually before using the object's accessors/mutators:

my $dbi = XML::Object::DBI->new($config_file);
$dbi->dbh($dsn, $user, $password);
# ...

By default, the top-level tag - <config> .. </config> in the example above - is not of any significance, although any attributes defined for it will end up as top-level keys in the configuration HASH:

    <foo bar="baz">
	...
    </foo>

XML::Simpl-ifies to:

    {
	bar => baz,
	...
    }

get

key_get

usage

    my $accessor = $self->get($optional_config, @optional_path, $optional_bindings);
	# or
    my $accessor = $self->key_get($optional_config, @optional_path, $optional_bindings);


    my $scalar = $accessor->scalar(); # single row, single column
    my @scalars = $accessor->scalars(); # single column, list context
    my $scalars = $accessor->scalars(); # single column, scalar context

	# or

    while ($arrayref = $accessor->arrayref) { ... }
	# AKA
    while (@array = $accessor->arrayref) { ... }

	# or

    for my $arrayref ($accessor->arrayrefs) { ... }
	# AKA
    my $array_of_arrayrefs = $accessor->arrayrefs();

	# or

    while ($hashref = $accessor->hashref) { ... }
	# AKA
    while (%hash = $accessor->hashref) { ... }

	# or

    for my $hashref ($accessor->hashrefs) { ... }
	# AKA
    my $array_of_hashrefs = $accessor->hashrefs();

	# or

    my $fields = $accessor->fields();
	# AKA
    my @fields = $accessor->fields();

description

get() evaluates the SQL (typically a SELECT statement) defined under the element name corresponding to the name of the current function, and returns a new XML::Object::DBI::Accessor object as a result. This object implements the methods outlined above: arrayref(), arrayrefs(), hashref(), hashrefs(), scalar(), scalars() and fields().

key_get() works the same, but takes an absolute path instead of deriving it from the name of the current method. Thus:

    sub foo {
	my $self = shift;
	my $get = $self->get();
    }

is equivalent to:

    sub foo {
	my $self = shift;
	# use 'foo' as key into configuration data structure
	my $get = $self->get($self->self);
    }

If debug="1" (or some such value of debug that is non-false) is specified in the XML, the executed SQL is processed via the XML::Object::DBI::debug() method (which, by default, prints to STDERR). Subclasses can override this to provide other behaviour.

e.g.

XML

    <config>
	<foo>
	    <get debug="1">
		SELECT * FROM wherever;
	    </get>
	</foo>
    </config>

As an alternative to inlined SQL, the path to an external SQL file can be specified using the path attribute:

    <config>
	<foo>
	    <get path="foo.sql" />
	</foo>
    </config>

Arguments to get(), key_get(), set(), key_set(), setIter() and key_setIter() are processed in the same manner as args to demand(), test() &c. in XML::Object. i.e. a data structure can be passed as the configuration 'context':

    sub init {
	my $self = shift;
	my $init = $self->demand(); # get the init data structure

	# ignore  
	try {
	    # e.g. DROP TABLE whatever;
	    $self->set($init, 'drop');
	} catch XOE::BadSet with {
	    # ignore error if table doesn't exist 
	}

	$self->set($init, 'create');

	my $insert1 = $self->set($init, 'insert1');
	my $insert2 = $self->set($init, 'insert2');

	printf "rows affected: %d%s", $insert1 + $insert2, $/;
    }

Alternatively (or in addition) a 'path' into the configuration data structure can be supplied either relative to the current path (determined by the name of the current sub for the 'standard' accessors and mutators) or absolutely (for key_get and key_set*). Note that: however the path is derived, 'get' or 'set' are always appended to the path expression:

    sub example1 {
	my $self = shift;
	# SQL is looked up under $self->config()->{example1}->{foo}->{bar}->{baz}->{get}
	my $arrayrefs = $self->getArray('foo', 'bar', 'baz');
    }

    sub example2 {
	my $self = shift;
	# SQL is looked up under $self->config()->{foo}->{bar}->{baz}->{get}
	my @arrayrefs = $self->key_getArray('foo', 'bar', 'baz');
    }

An ARRAY ref or HASH ref can be supplied as the final argument to all get() and set() methods. The mechanism by which these arguments are bound into the generated SQL are described in the set() section below.

An ARRAY or HASH ref is considered to be a collection of template bindings (either positional or named) if it appears at the end of a parameter list containing two or more arguments; conversely, an ARRAY or HASH ref appearing at the beginning of a parameter list (again: with two or more arguments) is considered to be a configuration 'context' i.e. the data structure against which lookups are performed.

In the ambiguous case where only one argument is supplied and that argument is a reference, it is taken to be a collection of template bindings rather than a configuration context. i.e.

    sub foo {
	my $self = shift;
	my $get = $self->get($hashref);
    }

resolves to:

    sub foo {
	my $self = shift;
	# look up $self->config()->{foo} and perform template substitutions contained in $hashref
	my $get = $self->key_get($self->self, $hashref);
    }

rather than:

    sub foo {
	my $self = shift;
	# look up $hashref->{foo}->{get}
	my $get = $self->key_get($hashref, $self->self);
    }

To perform the latter query, simply write it out as shown with at least one other argument to provide explicit disambiguation.

In list context, get() and key_get() return an ARRAY ref of the fields associated with the query - this corresponds to the value returned by the XML::Object::DBI::Accessor::fields() method:

    sub whatever {
	my $get = $self->get(...);
	my $fields = $get->fields();

	# or

	my ($get, $fields) = $self->get(...);
    }

set

key_set

usage

    sub example {
	my $self = shift;


	my $rows_affected = $self->set($optional_context, @optional_path, $optional_bindings);

	    # i.e.

	$self->set();

	    # or

	$self->set([ $arg1, $arg2 ]);

	    # or

	$self->set({ arg1 => $arg1, arg2 => $arg2 });

	    # or

	$self->set('foo', 1, 'bar');

	    # or

	$self->set('foo', 1, 'bar', { arg1 => $arg1, arg2 => $arg2 });

	    # or

	$self->set($config, 'foo', 1, 'bar', { arg1 => $arg1, arg2 => $arg2 });
    }

XML

    <config>
	<example>
	    <set>
		UPDATE foo SET bar = %arg1 WHERE baz = %arg2;
	    </set>
	</example>

	<!-- or -->

	<example>
	    <set>
		INSERT INTO foo (bar, baz) VALUES ('%s', %d);
	    </set>
	</example>
    </config>

description

set() provides a simple API for the implementation of database operations that 'mutate' rather than 'access' the data source. Typically, this refers to: INSERT, UPDATE and DELETE.

If an ARRAY ref of args is passed as the last argument to set(), it is processed the same way as the get* methods: i.e. each argument is sprintf()'d into the SQL (whether external or inline) - which is taken to be a printf-style template:

    <config>
	<example>
	    <set debug="1">DELETE FROM foo where %s = '%s';</set>
	</example>
    </config>

    sub example {
	my $self = shift;
	$self->set([ 'bar', 'baz' ]);
    }

- would result in the following query being performed and (thanks to the debug="1") printed to STDERR:

DELETE FROM foo WHERE bar = 'baz';

If passed a HASH ref as the last argument, variable names (formed by prepending a '%' to the hashtable keys) are replaced with their corresponding hashtable values: thus

    <config>
	<example>
	    <set debug="1">DELETE FROM foo WHERE %bar = '%baz';</set>
	</example>
    </config>

    sub example {
	my $self = shift;
	$self->set({ bar => 'fly', baz => 'luhrmann' });
    }

- would result in:

DELETE FROM foo WHERE fly = 'luhrmann';

All invocations of set() and key_set() and of the iterators returned by setIter() and key_setIter() return an integer representing the number of rows affected by the operation, or -1 if the number is not known.

This number will always evaluate to true in a boolean context, even though its arithmentic value may be equivalent to 0 (i.e. 0E0).

Because get* and set* use XML::Object's key_demand() to locate their 'set' and 'get' sections, the @optional_path part of both these families of methods can supply their own context. e.g. :

    sub example {
	my $self = shift;
	my $cfg = { set => { path => 'wherever.sql' } };
	my $rows_affected = $self->key_set($cfg, [ 'foo', 'bar' ]);
    }

key_set() has the same relationship to set() that key_get() has to get() i.e. it allows one to supply an explicit lookup path rather than one automatically derived from the name of the current method.

setIter

key_setIter

usage

    sub example {
	my $accessor = $self->get(...);
	my $mutator = $self->setIter(...);
	while ($row = $get->hashref) {
	    $mutator->({ alpha => $row->{alpha}, beta => $row->{beta} }); 
	}
    }

description

Returns a closure that can be used to perform multiple mutations of a data source. Each invocation of the closure performs the operation specified in the inline or external SQL; any arguments passed to the closure are interpolated into the SQL in accordance with the ARRAY ref and HASH ref conventions outlined in the set() section above.

Note that: arguments passed to setIter() are treated as default bindings that can optionally be overridden by arguments passed to the iterator itself. e.g:

my $default = $self->setIter([ 'foo' , 'bar' ]);
# foo and bar bind into the SQL by default 

&$default;
# use the default bindings 

$default->([ 'alpha', 'beta' ]);
# override the bindings for this call only

dbh

usage

    my $dbh = $self->dbh();

	# or

    $self->dbh($dsn)
   
	# or

    $self->dbh($dsn, $user)

	# or

    $self->dbh($dsn, $user, $password)

description

When called with no arguments dbh() returns the DBI database handle.

If passed one or more arguments, those values are assigned as the object's data source name, username and password respectively.

array2hash

usage

my $array = $self->array2hash($fields, $hash);

description

Converts an ARRAY ref into an a HASH ref using the key provided by the $fields ARRAY ref to attach keys to the supplied values.

This, in conjunction with hash2array(), facilitates interchange between the HASH and ARRAY ref types returned by the various accessors.

hash2array

usage

my $array = $self->hash2array($fields, $hash);

description

Converts a HASH ref into an ARRAY ref using the key provided by the $fields ARRAY ref to order the fields.

This, in conjunction with array2hash(), facilitates interchange between the HASH and ARRAY ref types returned by the various accessors.

EXCEPTIONS

XOE::BadArrayRef

Thrown if an ARRAY ref argument is expected but not supplied.

XOE::BadConnect

Thrown if the initial DBI connection fails.

XOE::BadExecute

Thrown if the DBI $sth->execute method fails.

XOE::BadFetch

Thrown if a retrieve operation on a DBI statement handle fails.

XOE::BadHashRef

Thrown if a HASH ref argument is expected but not supplied.

XOE::BadPrepare

Thrown if the DBI $sth->prepare method fails.

XOE::BadScalarFields

Thrown if the scalar() or scalars() is invoked on an accessor whose n=head2 XOE::BadScalar

XOE::BadScalarRows

Thrown if the scalar() is invoked on an accessor whose number of rows is not equal to one

XOE::BadSet

Thrown if an error occurs while DBI->do() is performed on the SQL supplied to the set() mutator.

XOE::BadSQLPath

Thrown if the <get path="external.sql">...</get> usage refers to a SQL file that can't be read.

EXPORT

None by default.

AUTHOR

chocolateboy <chocolate.boy@email.com>

COPYRIGHT AND LICENSE

Copyright 2003 by chocolateboy

This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself.

To install XOE, copy and paste the appropriate command in to your terminal.

cpanm

cpanm XOE

CPAN shell

perl -MCPAN -e shell
install XOE

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

perl

XML

DESCRIPTION

METHODS

new

usage

description

get

key_get

usage

description

XML

set

key_set

usage

XML

description

setIter

key_setIter

usage

description

dbh

usage

description

array2hash

usage

description

hash2array

usage

description

EXCEPTIONS

XOE::BadArrayRef

XOE::BadConnect

XOE::BadExecute

XOE::BadFetch

XOE::BadHashRef

XOE::BadPrepare

XOE::BadScalarFields

XOE::BadScalarRows

XOE::BadSet

XOE::BadSQLPath

EXPORT

SEE ALSO

AUTHOR

COPYRIGHT AND LICENSE

Module Install Instructions