NAME

Data::Toolkit::Connector::DBI

DESCRIPTION

Connector for relational databases accessed through Perl's DBI methods

SYNOPSIS

   $dbiConn = Data::Toolkit::Connector::DBI->new();

   $dbi = DBI->connect( $data_source, $username, $auth, \%attr ) or die "$@";

   $dbiConn->server( $dbi );

   $spec = $dbiConn->filterspec( "SELECT joinkey,name FROM people WHERE joinkey = '%mykey%'" );
   $msg = $dbiConn->search( $entry );
   while ( $entry = $dbiConn->next() ) {
        process $entry.....
   }

   $msg = $dbiConn->search( $entry );
   $entry = $dbiConn->allrows();

   $spec = $dbiConn->addspec( "INSERT INTO people (joinkey,name) VALUES (%mykey%,%myname%)" );
   $dbiConn->add( $entry );

   $spec = $dbiConn->updatespec( "UPDATE people set name = %myname% WHERE joinkey = %mykey%" );
   $dbiConn->update( $entry );

   $spec = $dbiConn->deletespec( "DELETE FROM people WHERE joinkey = %mykey%" );
   $msg = $dbiConn->delete( $entry );

Data::Toolkit::Connector::DBI does not do any commits or rollbacks. If you need transactions, you should call the DBI commit and rollback methods directly.

Note that all data is supplied via placeholders rather than being interpolated into the SQL strings. Thus for example, this addspec:

   INSERT INTO people (joinkey,name) VALUES (%mykey%,%myname%)

is translated before passing to the database engine, becoming:

   INSERT INTO people (joinkey,name) VALUES (?,?)

and the actual values of the 'mykey' and 'myname' attributes are passed as parameters. This avoids all problems with quoting and SQL-injection attacks. It does make some SELECT statements a bit harder to compose, particularly when you want to use LIKE to do substring searches. The solution is to use CONCAT():

   SELECT joinkey,sn FROM people WHERE sn LIKE CONCAT(%firstletter%, '%%')

The value of the 'firstletter' attribute will become a parameter when the select operation is executed.

Non-SQL databases

Not using SQL? No problem: Data::Toolkit::Connector::DBI does not attempt to understand the strings that you give it. All it does is attribute-name substitution, so provided your database query language understands the '?' placeholder convention it will all work.

DEPENDENCIES

   Carp
   Clone
   DBI
   DBD::CSV (for testing)

Constructor

new

   my $dbiConn = Data::Toolkit::Connector::DBI->new();

Creates an object of type Data::Toolkit::Connector::DBI

Methods

server

Define the database server for the connector to use. This should be an object of type DBI

   my $res = $dbiConn->server( DBI->connect($data_source, $username, $auth) );

Returns the object that it is passed.

filterspec

Supply or fetch filterspec

   $hashref = $ldapConn->filterspec();
   $hashref = $ldapConn->filterspec( "SELECT key,name FROM people WHERE key = '%mykey'" );

Parameters are indicated thus: %name% - this will result in a '?'-style placeholder in the SQL statement and the named attribute will be extracted from the supplied entry by the search() method.

Search the database. If an entry is supplied, attributes from it may be used in the search.

   $msg = $dbiConn->search();
   $msg = $dbiConn->search( $entry );

Returns the result of the DBI execute() operation. This will be false if an error occurred.

next

Return the next entry from the SQL search as a Data::Toolkit::Entry object. Optionally apply a map to the data.

Updates the "current" entry (see "current" method description below).

   my $entry = $dbConn->next();
   my $entry = $dbConn->next( $map );

The result is a Data::Toolkit::Entry object if there is data left to be read, otherwise it is undef.

allrows

Merges the data from all rows returned by the SQL search into a single Data::Toolkit::Entry object, so that each attribute has multiple values. Optionally apply a map to the data.

After this method if called, the "current" entry will be empty (see "current" method description below).

   my $entry = $dbConn->allrows();
   my $entry = $dbConn->allrows( $map );

The result is a Data::Toolkit::Entry object if there is data left to be read, otherwise it is undef.

current

Return the current entry in the list of search results. The current entry is not defined until the "next" method has been called after a search.

   $entry = $dbConn->current();

addspec

Supply or fetch spec for add

   $spec = $dbiConn->addspec();
   $spec = $dbiConn->addspec( "INSERT INTO people (joinkey,name) VALUES (%key%, %myname%)" );

Parameters are indicated thus: %name% - this will result in a '?'-style placeholder in the SQL statement and the named attribute will be extracted from the supplied entry by the add() method. Note that these parameters should not be quoted - they are not passed as text to the database engine.

add

Update a row in the database using data from a source entry and an optional map. If a map is supplied, it is used to transform data from the source entry before it is applied to the database operation.

Returns the result of the DBI execute operation.

   $msg = $dbConn->add($sourceEntry);
   $msg = $dbConn->add($sourceEntry, $addMap);

A suitable add operation must have been defined using the addspec() method before add() is called:

   $spec = $dbiConn->addspec( "INSERT INTO people (joinkey,name) VALUES (%key%, %myname%)" );
   $msg = $dbiConn->add( $entry );

NOTE that only the first value of a given attribute is used, as relational databases expect a single value for each column in a given row.

updatespec

Supply or fetch spec for update

   $spec = $dbiConn->updatespec();
   $spec = $dbiConn->updatespec( "UPDATE people set name = %myname% WHERE joinkey = %mykey%" );

Parameters are indicated thus: %name% - this will result in a '?'-style placeholder in the SQL statement and the named attribute will be extracted from the supplied entry by the update() method.

update

Update a row in the database using data from a source entry and an optional map. If a map is supplied, it is used to transform data from the source entry before it is applied to the database operation.

Returns the result of the DBI execute operation.

   $msg = $dbConn->update($sourceEntry);
   $msg = $dbConn->update($sourceEntry, $updateMap);

A suitable update operation must have been defined using the updatespec() method before update() is called:

   $spec = $dbiConn->updatespec( "UPDATE people set name = %myname% WHERE key = %mykey%" );
   $msg = $dbiConn->update( $entry );

NOTE that only the first value of a given attribute is used, as relational databases expect a single value for each column in a given row.

Note also that multiple rows could be affected by a single call to this method, depending on how the updatespec has been defined.

deletespec

Supply or fetch spec for delete

   $spec = $dbiConn->deletespec();
   $spec = $dbiConn->deletespec( "DELETE from people WHERE joinkey = %mykey%" );

Parameters are indicated thus: %name% - this will result in a '?'-style placeholder in the SQL statement and the named attribute will be extracted from the supplied entry by the delete() method.

delete

Delete a row from the database using data from a source entry and an optional map. If a map is supplied, it is used to transform data from the source entry before it is applied to the database operation.

Returns the result of the DBI execute operation.

   $msg = $dbConn->delete($sourceEntry);
   $msg = $dbConn->delete($sourceEntry, $deleteMap);

A suitable delete operation must have been defined using the deletespec() method before delete() is called:

   $spec = $dbiConn->deletespec( "DELETE FROM people WHERE joinkey = %mykey%" );
   $msg = $dbiConn->delete( $entry );

NOTE that only the first value of a given attribute is used.

Note also that multiple rows could be affected by a single call to this method, depending on how the deletespec has been defined.

Debugging methods

debug

Set and/or get the debug level for Data::Toolkit::Connector

   my $currentDebugLevel = Data::Toolkit::Connector::LDAP->debug();
   my $newDebugLevel = Data::Toolkit::Connector::LDAP->debug(1);

Any non-zero debug level causes the module to print copious debugging information.

Note that this is a package method, not an object method. It should always be called exactly as shown above.

All debug information is reported using "carp" from the Carp module, so if you want a full stack backtrace included you can run your program like this:

   perl -MCarp=verbose myProg

Author

Andrew Findlay

Skills 1st Ltd

andrew.findlay@skills-1st.co.uk

http://www.skills-1st.co.uk/