File Coverage

blib/lib/Relations/Abstract.pm

Criterion	Covered	Total	%
statement	9	116	7.7
branch	0	36	0.0
condition			n/a
subroutine	3	16	18.7
pod	12	12	100.0
total	24	180	13.3

# This module is for saving development time and
# programming space with the Perl DBI.

package Relations::Abstract;

require Exporter;
require DBI;
require 5.004;
require Relations;
require Relations::Query;

use Relations;
use Relations::Query;

# You can run this file through either pod2man or pod2html to produce pretty
# documentation in manual or html file format (these utilities are part of the
# Perl 5 distribution).

# Copyright 2001 GAF-3 Industries, Inc. All rights reserved.
# Written by George A. Fitch III (aka Gaffer), [e-mail]

# This program is free software, you can redistribute it and/or modify it under
# the same terms as Perl itself

$Relations::Abstract::VERSION='0.94';

@ISA = qw(Exporter);

@EXPORT = qw(
  new
);

@EXPORT_OK = qw(
  new
  set_dbh
  run_query
  select_field
  select_row
  select_column
  select_matrix
  insert_row
  insert_id
  select_insert_id
  update_rows
  delete_rows
  report_error
);

%EXPORT_TAGS = ();

# From here on out, be strict and clean.

use strict;



### Creates a new Relations::Abstract object.

sub new {

  my ($type) = shift;

  # Get all the arguments passed

  my ($dbh) = shift;

  # Create the hash to hold all the vars
  # for this object.

  my $self = {};

  # Bless it with the type sent (I think this
  # makes it a full fledged object)

  bless $self, $type;

  # Add the info into the hash only if it was sent

  $self->{dbh} = $dbh if $dbh;

  # Give thyself

  return $self;

}



### Sets the default database handle to use.

sub set_dbh {

  # Know thyself

  my $self = shift;

  # Get the DBH sent

  my ($dbh) = shift;

  # Set the database handle.

  $self->{dbh} = $dbh;

}



### Runs a query.

sub run_query {

  ### What we're doing here is sending the query to
  ### the dbh, and reporting an error if the execute
  ### failed. The query can be sent as a string, hash
  ### or Relations::Query object.

  # Know thyself

  my $self = shift;

  # Get the query sent

  my ($query) = shift;

  # Convert whatever we were sent to a query string.

  $query = to_string($query);

  # Declare a statement handle and prepare the query.

  my ($sth) = $self->{dbh}->prepare($query);

  # Execute it.

  $sth->execute() or return $self->report_error("run_query failed: $query\n");

  # Finish it off.

  $sth->finish();

  # Return 1 cuz it worked

  return 1;

}



### Select a field's data.

sub select_field {

  ### What we're doing here is creating and sending the query
  ### string to the dbh, retreiving the requested item and
  ### reporting an error if the execute failed. We can take
  ### simple info like the table and where clause, or complex
  ### info like a full query.

  # Know thyself

  my $self = shift;

  # Get the field, table, where clause sent

  my ($field,$table,$where,$query) = rearrange(['FIELD','TABLE','WHERE','QUERY'],@_);

  # Unless we were sent query info, make some.

  $query = "select $field from $table where " . equals_clause($where) unless $query;

  # Convert whatever we were sent to a query string.

  $query = to_string($query);

  # Declare a statement handle and prepare the query.

  my ($sth) = $self->{dbh}->prepare($query);

  # Execute it.

  $sth->execute() or return $self->report_error("select_field failed: $query\n");

  # Declare hash for retrieving value, and variable to hold value.

  my ($hash_ref,$value);

  # If we got something returned

  if ($hash_ref = $sth->fetchrow_hashref()) {

    # Get the value returned.

    $value = $hash_ref->{$field};

  }

  # Finish it off.

  $sth->finish();

  # Return the value.

  return $value;

}



### Selects a row of data.

sub select_row {

  ### What we're doing here is creating and sending the query
  ### string to the dbh, retreiving the requested item and
  ### reporting an error if the execute failed. We can take
  ### simple info like the table and where clause, or complex
  ### info like a full query.

  # Know thyself

  my $self = shift;

  # Get the table and where clause sent

  my ($table,$where,$query) = rearrange(['TABLE','WHERE','QUERY'],@_);

  # Unless we were sent query info, make some.

  $query = "select * from $table where " . equals_clause($where) unless $query;

  # Convert whatever we were sent to a query string.

  $query = to_string($query);

  # Declare a statement handle and prepare the query.

  my ($sth) = $self->{dbh}->prepare($query);

  # Execute it.

  $sth->execute() or return $self->report_error("select_row failed: $query\n");

  # Get the value returned.

  my ($hash_ref) = $sth->fetchrow_hashref();

  # Finish it off.

  $sth->finish();

  # Return the value.

  return $hash_ref;

}



### Selects a column of data.

sub select_column {

  ### What we're doing here is creating and sending the query
  ### string to the dbh, retreiving the requested items and
  ### reporting an error if the execute failed. We can take
  ### simple info like the table and where clause, or complex
  ### info like a full query.

  # Know thyself

  my $self = shift;

  # Get the field table and where clause sent

  my ($field,$table,$where,$query) = rearrange(['FIELD','TABLE','WHERE','QUERY'],@_);

  # Unless we were sent query info, make some.

  $query = "select $field from $table where " . equals_clause($where) unless $query;

  # Convert whatever we were sent to a query string.

  $query = to_string($query);

  # Declare a statement handle and prepare the query.

  my ($sth) = $self->{dbh}->prepare($query);

  # Execute it.

  $sth->execute() or return $self->report_error("select_colum failed: $query\n");

  # Create an array to hold the data

  my (@column) = ();

  # Get the values returned.

  my ($hash_ref);

  while ($hash_ref = $sth->fetchrow_hashref()) {

    # Add them to the column array.

    push @column,$hash_ref->{$field};

  }

  # Finish it off.

  $sth->finish();

  # Return the value.

  return \@column;

}



### Selects a matrix (rows of columns) of data.

sub select_matrix {

  ### What we're doing here is creating the query string
  ### and sending it to the dbh, retreiving the rows of hashes,
  ### returning them unless there's an error. If so we'll report
  ### the error. We can take simple info like the table and where
  ### clause, or complex info like a full query.

  # Know thyself

  my $self = shift;

  # Get the table and where clause sent

  my ($table,$where,$query) = rearrange(['TABLE','WHERE','QUERY'],@_);

  # Unless we were sent query info, make some.

  $query = "select * from $table where " . equals_clause($where) unless $query;

  # Convert whatever we were sent to a query string.

  $query = to_string($query);

  # Declare a statement handle and prepare the query.

  my ($sth) = $self->{dbh}->prepare($query);

  # Execute it.

  $sth->execute() or return $self->report_error("select_matrix failed: $query\n");

  # Create an array to hold the data

  my (@matrix) = ();

  # Get the values returned.

  my ($hash_ref);

  while ($hash_ref = $sth->fetchrow_hashref()) {

    # Create a hash to hold the data

    my (%matrix) = %$hash_ref;

    # Add them to the column array.

    push @matrix,\%matrix;

  }

  # Finish it off.

  $sth->finish();

  # Return the value.

  return \@matrix;

}



### Inserts a row of data into a table.

sub insert_row {

  ### What we're doing here is sending the query string to the dbh, and
  ### returning the number of rows affected, unless there's an error. If
  ### there's an error, we'll send back a 0.

  # Know thyself

  my $self = shift;

  # Get the table and set clause sent

  my ($table,$set) = rearrange(['TABLE','SET'],@_);

  # Get the info for the where clause;

  $set = assign_clause($set);

  # Form query

  my ($query) = "insert into $table set $set";

  # Declare a statement handle and prepare the query.

  my ($sth) = $self->{dbh}->prepare($query);

  # Execute it.

  $sth->execute() or return $self->report_error("insert_row failed: $query\n");

  # Finish it off.

  $sth->finish();

  # Return the number of rows affected

  return $sth->rows();

}



### Inserts a row data into a table and returns the new id.

sub insert_id {

  ### What we're doing here is sending the query string to the dbh, retreiving
  ### the new id, and sending it back, unless there's an error. If there's an
  ### error, we'll send back a zero.

  # Know thyself

  my $self = shift;

  # Get the table and set clause sent

  my ($table,$set) = rearrange(['TABLE','SET'],@_);

  # Get the info for the where clause;

  $set = assign_clause($set);

  # Form query

  my ($query) = "insert into $table set $set";

  # Declare a statement handle and prepare the query.

  my ($sth) = $self->{dbh}->prepare($query);

  # Execute it.

  $sth->execute() or return $self->report_error("insert_id failed: $query\n");

  # Finish it off.

  $sth->finish();

  # Return the new id using and old friend of ours.

  return $self->select_field(-field => 'id', -query => 'select last_insert_id() as id');

}



### Selects or inserts data and returns the id.

sub select_insert_id {

  ### What we're doing here trying select_item and returning the id if
  ### succesful. Else, trying insert_id, returning the new if successful.
  ### Else, returning zero.

  # Know thyself

  my $self = shift;

  # Get the table, where clause, and set clause sent

  my ($id,$table,$where,$set) = rearrange(['ID','TABLE','WHERE','SET'],@_);

  # Declare the id variables

  my ($old_id,$new_id);

  # If the data's already there.

  if ($old_id = $self->select_field($id,$table,$where)) {

    # Return the old id

    return $old_id;

  }

  # If we could add the data

  if ($new_id = $self->insert_id($table,$set)) {

    # Return the new id.

    return $new_id;

  }

  # If we've come this far, then neither was successful. Indicate this.

  return $self->report_error("select_insert_id failed: id: $id table: $table where: $where set: $set\n");


}



### Updates rows of data in a table and returns the number of updated rows.

sub update_rows {

  ### What we're doing here is sending the query string to the dbh, and
  ### returning the number of rows affected, unless there's an error. If
  ### there's an error, we'll send back a 0.

  # Know thyself

  my $self = shift;

  # Get the table where clause, and set clause sent

  my ($table,$where,$set) = rearrange(['TABLE','WHERE','SET'],@_);

  # Get the info for the set and where clause;

  $set = assign_clause($set);
  $where = equals_clause($where);

  # Form query

  my ($query) = "update $table set $set where $where";

  # Declare a statement handle and prepare the query.

  my ($sth) = $self->{dbh}->prepare($query);

  # Execute it.

  $sth->execute() or return $self->report_error("update_rows failed: $query\n");

  # Finish it off.

  $sth->finish();

  # Return the number of rows affected

  return $sth->rows();

}



### Deletes rows from a table and returns the number of deleted rows.

sub delete_rows {

  ### What we're doing here is sending the query string to the dbh, and
  ### returning the number of rows affected, unless there's an error. If
  ### there's an error, we'll send back a 0.

  # Know thyself

  my $self = shift;

  # Get the table and where clause criteria sent

  my ($table,$where) = rearrange(['TABLE','WHERE'],@_);

  # Get the info where clause;

  $where = equals_clause($where);

  # Form query

  my ($query) = "delete from $table where $where";

  # Declare a statement handle and prepare the query.

  my ($sth) = $self->{dbh}->prepare($query);

  # Execute it.

  $sth->execute() or return $self->report_error("delete_rows failed: $query\n");

  # Finish it off.

  $sth->finish();

  # Return the number of rows affected

  return $sth->rows();

}



### Reports a failed routine if PrintError is enabled in the dbh.

sub report_error {

  # Know thyself

  my $self = shift;

  # If DBI isn't printing errors neither are we.

  return unless $self->{dbh}->{PrintError};

  # Get the failure message

  my $message = shift;

  # Tell the user what's up.

  print STDERR $message;

  return;

}

$Relations::Abstract::VERSION;

__END__