DBIx::SimplePerl - Perlish access to DBI

ABSTRACT

This module provides DBIx::SimplePerl which is a highly (over)simplified interface to DBI. The point of DBIx::SimplePerl is that end programmers who want to write their programs which access simple databases in Perl, should be able to write their programs in Perl and not Perl + SQL. This is a different approach as compared to the Tie::DBI. This module is not what high end or midrange database programmers would like or care to use. It works great for really simple stuff, like SADU (search, add, delete, update) on existing tables. It follows a basic Keep It Simple(tm) philosophy, in that the programmer ought to be able to use a database table with very little effort. Other modules attempt to make SQL access simple, but in the end they rely on the user emitting SQL at some point. This module hides the SQL from the end user by automatically generating it in flight. The idea being that Perl programmers who need very simple database access do not necessarily need to write SQL.

SYNOPSIS

  use DBIx::SimplePerl;
  my $sice = DBIx::SimplePerl->new;
				 
  ...
  # all methods return a hash with two possible keys. 
  # On success, the return is
  #	{ success => true }
  # On failure, the return is
  #	{ failed  => { 
  #                    error => "error_message_from_call", 
  #                    code  => "error_return_code_from_call" 
  #                  } 
  #     }
  
  # sets internal $sice->{_dbh} to open database handle
  $sice->db_open(dsn => $dsn, dbuser => $dbuser, dbpass => $dbpass);
  
  $sice->db_add(table => $table_name,columns => {field1=>$data1,...});
  $sice->db_search(table => $table_name [,search => {field=>$data1,...}]);
  $sice->db_update(table => $table_name,search => {field=>$data1,...},
                                                   columns=>
						     {field1=>$data1,...
						  }
		  );
  $sice->db_delete(table => $table_name,search => {field=>$data1,...});
  
  $sice->close; 

The session handle is available under the object as $sice->{_sth}

Methods

db_open(dsn => $dsn, dbuser => $dbuser, dbpass => $dbpass )

The db_open method returns a database handle attached to $self->{_dbh}. RaiseError is set to 1. This function attaches the object to a database. As long as the DBD/DBI supports it, you may have multiple independent objects connected to the same database.

db_add(table => $table_name,columns => {field1=>$data1,...})

The db_add method will take a record (the hash pointed to by the columns field, generate the necessary SQL, and do an insert into the table indicated. That is, if we have a table named "users", and we want to add a record with a username, password, home directory, and shell, we can do something like this:

    use DBIx::SimplePerl;
    my $sice = DBIx::SimplePerl->new;        
    $sice->db_open(
                    'dsn'    => "dbi:SQLite:dbname=/etc/cluster/cluster.db",
                    'dbuser' => "",
                    'dbpass' => ""
                  );
  
    $sice->db_add(
    		   table   =>"users",
		   columns => {
		   		username	=> $username,
				password	=> $password,
				homedir		=> $homedir,
				shell		=> $shell
		   	      }
		 );

and the method will generate the appropriate SQL to insert this record:

insert into "users" ("username","password","homedir","shell") \
values ("$username","$password","$homedir","$shell");

If the insert operation failed or generated errors or warnings, you will be able to check for the existance of and inspect $sice->{failed}. As each DBD is different, no two different DBDs will generate the same error messages or error codes.

If you would like to see the SQL the method generates, then set the debug attribute to a non-zero value

$sice->{debug} = 1;

and it will emit the SQL it generates on the STDERR.

db_search(table => $table_name,search => {field1=>$data1,...})

The db_search method will perform a select with an appropriate where clause, generated by the hash pointed to by the search field. That is, if we have a table named "users", and we want to find a set of one or more records with a particular username, we can do something like this:

    use DBIx::SimplePerl;
    my $sice = DBIx::SimplePerl->new;        
    $sice->db_open(
                    'dsn'    => "dbi:SQLite:dbname=/etc/cluster/cluster.db",
                    'dbuser' => "",
                    'dbpass' => ""
                  );
  
    $sice->db_search(
    		   table   =>"users",
		   search => {
		   		username => $username
		   	      }
		 );

and the method will generate the appropriate SQL to perform this select:

select from "users" where "username"="$username";

If the select operation failed or generated errors or warnings, you will be able to check for the existance of and inspect $sice->{error}. As each DBD is different, no two different DBDs will generate the same error messages or error codes. As many fields as are relevant in the particular table may be used. The search=> may be completely omitted to give a "SELECT * from table" effect. The results are returned as a DBI session handle, and any of the DBI methods may be used to extract the data at this point.

foreach (sort keys %{$sice->{_sth}->fetchall_hashref('username')} )
  {
    printf "name = \'%s\'\n",$_;
  }

We recommend using something like this:

$q=($sice->{_sth}->fetchall_hashref('username'));
foreach (sort keys %{$q} )
  {
    printf "name = \'%s\'\n",$_;
  }

as it will be simple to access the records fields as

$q->{$_}->{field_name}

If the select succeeds, then the $sice->db_search... will return an anonymous hash with a key named "success". Testing for the existence of this key is sufficent for determining if the method call succeeded. Error messages (if generated) would be stored in the anonymous hash's "failed" key. Lack of existence of this key is another indicator of success. There is more than one way to do it, and these should always be consistent.

db_update(table => $table_name, search => {field1=>$data1,...}, columns=> {field1=>$data1,...})

The db_update method will perform an update with an appropriate where clause, generated by the hash pointed to by the search field, using the column hash to insert the updated values. Fields not specified in the column hash will not be changed. That is, if we have a table named "users", and we want to update a set of one or more records with a particular username, we can do something like this:

    use DBIx::SimplePerl;
    my $sice = DBIx::SimplePerl->new;        
    $sice->db_open(
                    'dsn'    => "dbi:SQLite:dbname=/etc/cluster/cluster.db",
                    'dbuser' => "",
                    'dbpass' => ""
                  );
  
    $sice->db_update(
    		   table   =>"users",
		   search  => {
		   		username => $username		
		   	      },
		   columns => {
		   		homedir => $new_homedir
		   	      }
		 );

and the method will generate the appropriate SQL to perform this update.:

update  "users" set "homedir"="$new_homedir" \
                where "username"="$username";

If the update operation failed or generated errors or warnings, you will be able to check for the existance of and inspect $sice->{error}. As many fields as are relevant in the particular table may be used in the search hash or the column hash. The results are returned as a DBI session handle, and any of the DBI methods may be used to extract the data at this point.

If the update succeeds, then the $sice->db_update... will return an anonymous hash with a key named "success". Testing for the existence of this key is sufficent for determining if the method call succeeded. Error messages (if generated) would be stored in the anonymous hash's "failed" key. Lack of existence of this key is another indicator of success.

db_delete(table => $table_name, search => {field1=>$data1,...})

The db_delete method will perform a record delete with an appropriate where clause, generated by the hash pointed to by the search field. This will not delete the table itself, just the record. That is, if we have a table named "users", and we want to delete a set of one or more records with a particular username, we can do something like this:

    use DBIx::SimplePerl;
    my $sice = DBIx::SimplePerl->new;        
    $sice->db_open(
                    'dsn'    => "dbi:SQLite:dbname=/etc/cluster/cluster.db",
                    'dbuser' => "",
                    'dbpass' => ""
                  );
  
    $sice->db_delete(
    		   table   =>"users",
		   search  => {
		   		username => $username		
		   	      }
		 );

and the method will generate the appropriate SQL to perform this update.:

delete from "users" where "username"="$username";

If the delete operation failed or generated errors or warnings, you will be able to check for the existance of and inspect $sice->{error}. As many fields as are relevant in the particular table may be used in the search hash. The results are returned as a DBI session handle, and any of the DBI methods may be used to extract the data at this point.

If the delete succeeds, then the $sice->db_update... will return an anonymous hash with a key named "success". Testing for the existence of this key is sufficent for determining if the method call succeeded. Error messages (if generated) would be stored in the anonymous hash's "failed" key. Lack of existence of this key is another indicator of success.

db_create_table(table => $table_name,columns => {field1=>"type1",...})

The db_create_table method will take a record (the hash pointed to by the columns field, generate the necessary SQL, and do an create table into the attached database handle. That is, if we want to create a table named "users", with columns of username, password, home directory, uid, shell, and date we can do something like this:

    use DBIx::SimplePerl;
    my $sice = DBIx::SimplePerl->new;        
    $sice->db_open(
                    'dsn'    => "dbi:SQLite:dbname=/etc/cluster/cluster.db",
                    'dbuser' => "",
                    'dbpass' => ""
                  );
  
    $sice->db_create_table(
    		   table   =>"users",
		   columns => {
		   		username	=> "varchar(30)",
				password	=> "varchar(30)",
				homedir		=> "varchar(255)",
				shell		=> "varchar(30)",
				uid		=> "integer",
				date		=> "datetime"
		   	      }
		 );

and the method will generate the appropriate SQL to create this table:

   create table "users" 
   	(
	  "username"	varchar(30),
	  "password"	varchar(30),
	  "homedir"	varchar(255) ,
	  "shell"	varchar(30),
	  "uid"		integer,
	  "date"	datetime
	);

If the create operation failed or generated errors or warnings, you will be able to check for the existance of and inspect $sice->{failed}. As each DBD is different, no two different DBDs will generate the same error messages or error codes.

If you would like to see the SQL the method generates, then set the debug attribute to a non-zero value

$sice->{debug} = 1;

and it will emit the SQL it generates on the STDERR.

EXAMPLE

Suppose you have a nice database, a SQLite in this case, though it will work perfectly well with Mysql, Postgres, and anything else DBI supports. This database has a list of host names and MAC addresses, and you want to list them from the database.

The table has been created using:

CREATE TABLE hosts (
       mac_address      text,
       ip_address       text,
       dhcp_ipaddress   text,
       host_name        text,
       host_domain      text,
       net_device       text,
       gateway          text,
       netmask          text,
       mtu              text,
       options          text
       );

and the script looks like this

 #!/usr/bin/perl

 use strict;
 use DBIx::SimplePerl;
 my ($dbh,$err,$sice);
 my ($rc,$debug,$q);

 $debug	= 1;
 $sice   = SICE->new( { debug=>$debug } );
 $sice->db_open(
                 'dsn'           => "dbi:SQLite:dbname=/etc/cluster/cluster.db",
                 'dbuser'        => "",
                 'dbpass'        => ""
               );

 printf "Machines in cluster.db\n" ;
 $rc     = $sice->db_search('table' => 'hosts');
 if (defined($rc->{success}))
    {
       printf "mac\t\t\tip\t\thostname\n" ;
       $q=($sice->{_sth}->fetchall_hashref('mac_address'));
       foreach (sort keys %{$q})
	{
         printf "%s\t%s\t%s\n", $_,
	 			$q->{$_}->{ip_address},
				$q->{$_}->{host_name} ;
	}
    }
   else
    {
       printf "WARNING: the search did not succeed.\n
       	       DB returned the following error:\n\n%s\n\n",
	       $rc->{failed};
    }
 $sice->db_close;

The db_search does the query, and stores the result in a session handle stored as $object_name->{_sth}. You can then use your favorite DBI method to pull out the records. What DBI Simple saves you is writing SQL. It will do that portion for you. If you turn debugging by creating the object with debug=>1, then you can watch the SQL that is generated.

WHY

Why hide SQL? That question should answer itself, especially in programs not requiring the full firepower of a Class::DBI or most of the DBI methods. It is fairly easy to make a mistake in the SQL you generate, and debugging it can be annoying. This was the driving force behind this particular module. The SQL that is generated is fairly simple minded. It is executed, and results returned. If it fails, this is also caught and what DBI thinks is the reason it failed is returned as the $object->{failed} message.

This module is not for the folks who need the full firepower of most of the rest of DBI. This module is for simple programs. If you exceed the capabilities of this module, then please look to one of the other modules that do DBs.

The approach to this module is simplicity. It is intended to be robust for basic applications, and it is used in a commercial product.

AUTHOR

Joe Landman (landman@scalableinformatics.com)

COPYRIGHT

Copyright (c) 2003-2005 Scalable Informatics LLC. All rights reserved.

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.6 or, at your option, any later version of Perl 5 you may have available.

SEE ALSO

perl(1), DBI, Class::Accessor

BUGS

Well, quite likely. SQL is a standard, and standards are open to interpretation. This means that some things may not work as expected. We have run into issues in quoting fields and values, where DBD::Mysql happily accepted input that DBD::Pg croaked on. This module is known to work without noticable issues on DBD::SQLite, DBD::Mysql, DBD::Pg. Others may or may not work, depending upon how compatible they are with the specs in DBI for DBD module writers.