NAME DBIx::PDlib - DBI SQL abstraction and convenience methods SYNOPSIS use DBIx::PDlib; my $db = DBIx::PDlib->connect({ driver => 'mydriver', host => 'myhost.com', dbname => 'mydb', user => 'myuser', password => 'mypassword', }); my ($name) = $db->select('name','table1',"id = '10'"); my $dbi_sth = $db->iterated_select('name','table1', "id > 2",'ORDER BY name'); while (my ($name) = $dbi_sth->fetchrow_array) { ...do stuff... } my $rv = $db->insert('table1',['id','name'],['11','Bob']); my $rv = $db->update('table1',['name'],['Bob Jr.'],"id = '11'"); my $rv = $db->delete('table1',"id = '11'"); my @quoted = $db->quote( "something", $foo, $bar, @moredata ); my $rv = $db->raw_query("CREATE TABLE table1 (id int, name char)"); if ($db->connected) { ...we're connected... } $db->disconnect; ABSTRACT DBIx::PDlib provides a simplified way to interact with DBI. It provides methods for SELECT, INSERT, UPDATE, and DELETE which result in having to type less code to do the DBI queries. It does as little as possible to make things easier. What it doesn't do... It isn't trying to replace DBI. It's not trying to completely abstract SQL statement building into some 100% perllike syntax (though that is REALLY cool, and what I liked about DBIx::Abstract), but it does abstract it some. REQUIRES DBI INSTALLATION Download the gzipped tar file from: http://search.cpan.org/search?dist=DBIx-PDlib Unzip the module as follows or use winzip: tar -zxvf DBIx-PDlib-1.xxx.tar.gz For "make test" to work, you need to setup some parameters for the build. perl Makefile.PL --help The rest is done the standard Perl way: make make test make install # you need to be root Windows users without a working "make" can get nmake from: ftp://ftp.microsoft.com/Softlib/MSLFILES/nmake15.exe METHODS MAIN METHODS "$db = DBIx::PDlib->connect( $connect_config | $dbihandle )" *CONSTRUCTOR* Open a connection to a database as configured by $connect_config. $connect_config can either be a scalar, in which case it is a DBI data source, or a referance to a hash with the following keys: dsn -- The data source to connect to your database OR, DBIx::PDlib will try to generate it if you give these instead: driver -- DBD driver to use (defaults to mysql) host -- Host of database server port -- Port of database server dbname -- Name of database Username and password are always valid. user -- Username to connect as password -- Password for user Alternatively you can pass in a DBI handle directly. This will disable the methods "reconnect" and "ensure_connection" as they rely on connection info not available on a DBI handle. "$sth = $db->iterated_select( 't.field1,t2.field2','table t, table2 t2','t.id = t2.id','ORDER BY t.field1')" This builds an SQL query, executes it, and returns the DBI statement handle if execute succeeds. It will return undef if execute fails. The above query would build the following SQL statement: SELECT t.field1, t2.field2 FROM table t, table2 t2 WHERE t.id = t2.id ORDER BY t.field1 The first two options (fields and table) are required. The third option is the WHERE statement, which you can leave blank or undef to exclude using a where statement. The fourth option is any additional raw SQL to append to the query (ORDER BY, GROUP BY, etc type stuff can be put here). "$field = $db->select( 'field1','table','id > 10','ORDER BY field1')" This will return the first row of data, and call DBI's finish() on the handle. If called in array context, an array of the fields will be returned. If called in scalar context, the first field in the first row returned will be returned. 'undef' will be returned if the call fails. This is very useful if you just need to grab one row of data. The statement fields have the same requirements as iterated_select. "$arrayref = $db->select_all( 'field1','table','id > 10','ORDER BY field1')" This will execute the statement (same requirements as iterated_select), and call DBI's fetchall_arrayref on the handle, finish() the handle, and return the resulting arrayref. The $arrayref will contain an array representing all rows returned, of arrayrefs containing the columns for each row (an array of arrays). "$rv = $db->insert('table1',['id','name'],['11','Bob']);" Inserts a row into the database. The first option is the table to insert into. The second option is the list of field names. The third option is a list of values. Use the perl 'undef' value to insert a NULL. This format was chosen to allow Insert's and Update's to use the same calling semantics. "$rv = $db->update('table1',['name'],['Bob Jr.'],'id = 11');" Updates a row in the database. The first three options have the same requirements as insert(). The last option is the WHERE statement, and is optional (though recommended). Use the perl 'undef' value to update a field to NULL. "$rv = $db->delete('table1','id = 11');" Deletes rows matching the where statement in the second option from the database table 'table1'. The where statement is required as a safety precaution. If you really want to delete everything in the table, pass in a "1" as the where statement. "@quoted = $db->quote('something', $foo, $bar, @moredata );" Takes in an array of values, and returns an array of those same values quoted using DIB's quote(). If called in scalar context, it will return the first item in the list. ACCESSOR METHODS "$db->raw_query($sql)" This executes a DBI do() on whatever you pass to it. This is useful for CREATE, DROP, ALTER, etc type SQL commands. "$db->connected" Check to see if this object is connected to a database. It will do a DBI ping on the current DBI database handle that is inside the DBIx::PDlib object, returning 1 if it is successful. "$db->disconnect" You don't need to call this, but if you really want to disconnect from the database for some reason, this will do the job. It just calls DBI's disconnect() on the current DBI handle in the object. QUOTING An attempt has been made to provide automatic quoting where appropriate, but there are some areas normally used that you will need to do your own value quoting. In areas where you will need to do your own quoting, the quote() method is the recommended way to do it. insert() - The values passed to insert will automatically be quoted by use of DBI's placeholders (?). To pass a NULL, simply pass an undef value. You should NOT manually quote values passed to insert(), as DBI's quote will be called on those values, resulting in the actual quotes being entered into the database. update() - The values portion will be automatically quoted, the same way as insert(). However, the WHERE statement will simply be appended to the query string that is built, so you MUST quote your own values. select(), iterated_select(), select_all(), delete() - No quoting is done by these methods. Any fields that need quoted will need to be handled by your program. TODO not sure yet. SEE ALSO DBI DBIx::Abstract (From which connect(), Makefile.PL, and t/1.pl borrow heavily) AUTHOR Josh I. Miller, COPYRIGHT AND LICENSE Portions copyright 2003 by Josh I. Miller Portions copyright 2001-2002 by Andrew Turner Portions copyright 2000-2001 by Adelphia Business Solutions Portions copyright 1998-2000 by the Maine Internetworks (MINT) This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.