NAME

LEOCHARRE::Database - common database methods for oo

SYNOPSIS

Mymod.pm:

   package Mymod;
   use base 'LEOCHARRE::Database';

   1;

script.pl:

   use Mymod;

   my $m = new Mymod({ 
      DBHOST => 'localhost' , 
      DBUSER => 'username',
      DBPASSWORD => 'passwerd',
      DBNAME => 'superdb',
   });

   $m->dbh;

   my $a = $m->dbh_sth('select * from avocado where provider = ?');
   $a->execute(3);

   $m->dbh_sth('INSERT INTO fruit (name,provider) values(?,?)')->execute('grape','joe'); # gets prepared and cached
   
   $m->dbh_sth('INSERT INTO fruit (name,provider) values(?,?)')->execute('orange','joe'); # uses the previous cached prepared handle

DESCRIPTION

This is meant to be used as base by another oo module.

This can be passed an open database handle via DBH or it will attempt to open a new connection. By default it attempts to open a mysql connection. It can also open a sqlite connection.

Basically, if you provide the argument DBABSPATH, will attempt to open a mysql connection instead.

PLEASE NOTE autocommit is off. DESTROY calls commit and finishes and closes handles.

These and any other modules under my name as namespace base (LEOCHARRE) are parts of code that I use for doing repetitive tasks. They are not bringing anything really new to the table, and I wouldn't assume that 'my way' is the way to attack these problems by any stretch of the imagination. This is why I place them under my namespace, as a gesture of deference.

To open a mysql connection

   new ({ 
      DBHOST => $host,
      DBNAME => $dbname,
      DBUSER => $dbuser,
      DBPASSWORD => $pw,
   });

To open a sqlite connection

   new ({ 
      DBABSPATH => '/home/myself/great.db',
   });

To use existing connection

   new ({ 
      DBH => $dbh,
   });

METHODS

These are meant to be inherited by your module with

   use base 'LEOCHARRE::Database';

dbh()

returns database handle

dbh_selectcol()

argument is statement will select and return array ref

   my $users = $self->dbh_selectcol("SELECT user FROM users WHERE type = 'm'");

Now users has ['joe','rita','carl']

This is useful sometimes.

dbh_do()

argis hash ref with mysql and sqlite keys

will select one or the other with dbh_is_mysql etc

   $self->dbh_do({
      sqlite => 'CREATE TABLE bla( name varchar(25) )',
      mysql  => 'CREATE TABLE bla( name varchar(25) )',  
   });

dbh_count()

argument is statement returns count number you MUST have a COUNT(*) in the select statement

   my $matches = $self->dbh_count('select count(*) from files');

dbh_sth()

argument is a statment, returns handle it will cache in the object, subsequent calls are not re-prepared

   my $delete = $self->dbh_sth('DELETE FROM files WHERE id = ?');
   $delete->execute(4);
   
   # or..
   for (@ids){
      $self->dbh_sth('DELETE FROM files WHERE id = ?')->execute($_);
   } 

If the prepare fails, confess is called.

dbh_is_mysql()

returns boolean

dbh_is_sqlite()

returns boolean

dbh_driver()

returns name of DBI Driver, sqlite, mysql, etc. Currently mysql is used, sqlite is used for testing. For testing the package, you don't need to have mysqld running.

dbh_table_exists()

argument is table name, returns boolean

dbh_table_dump()

argument is table name returns string of dump of table suitable for print to STDERR

dbh_droptable()

arg is table name, drops the table. returns boolean will drop IF EXISTS

dbh_lid()

arg is table name, returns last insert id. returns undef if not there

dbh_close_active_handles()

closes ChildHandles that are active, finishes and undefines them. returns true + number of active handles were finished and undefined here

DESTROY()

If the database handle was created in this object and not passed via constructor, then we close all handles, commit, and disconnect.

finishes active database handles etc, makes a commit to the database. Note, this method is called automatically.

CAVEATS

IMPORTANT NOTE AUTOCOMMIT

Autocommit is set to 0 by default. That means you should commit after indexing_lock(), indexing_lock_release(), delete_record()

DESTROY will finish and commit if there are open handles created by the object

DEBUG

To turn on debug,

   $LEOCHARRE::Database::DEBUG = 1;

SEE ALSO

LEOCHARRE::CLI LEOCHARRE::Dev DBI

AUTHOR

Leo Charre leocharre at cpan dot org http://leocharre.com

COPYRIGHT

Copyright (c) 2008 Leo Charre. All rights reserved.

LICENSE

This package is free software; you can redistribute it and/or modify it under the same terms as Perl itself, i.e., under the terms of the "Artistic License" or the "GNU General Public License".

DISCLAIMER

This package is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

See the "GNU General Public License" for more details.

cpanm LEOCHARRE::Database

perl -MCPAN -e shell
install LEOCHARRE::Database