NAME

SqlObject - Sql module for wrappers around DBI

SYNOPSIS

    use SQL::SqlObject;

    $dbh = new SQL::SqlObject('my_db','dbi::pg','user','passwd');

    $dbh->db_dsn      = $dsn;    $dsn    = $dbh->db_dsn;
    $dbh->db_name     = $name;   $name   = $dbh->db_name;
    $dbh->db_user     = $user;   $usr    = $dbh->db_user;
    $dbh->db_password = $passwd; $passwd = $dbh->db_password;

    $dbh->connect([$SCALAR]); # Defaults to 'cezb-html'
    $dbh->disconnect();

    $SCALAR          = $dbh->value($SCALAR);
    [@LIST|$LISTREF] = $dbh->list($SCALAR);
    [@LIST|$LISTREF] = $dbh->array($SCALAR);
    [%HASH|$HASHREF] = $dbh->hash($SCALAR);
    [@AOH|$LISTREF]  = $dbh->hashes($SCALAR);

    $SCALAR = $dbh->insert_select($SCALAR,$HASHREF,[$SCALAR]);
    $dbh->insert($SCALAR,$HASHREF);
    $dbh->cond_insert($SCALAR,$HASHREF,[$HASHREF]);
    $SCALAR = $dbh->cond_insert_select($SCALAR,$HASHREF,[$HASHREF],[$SCALAR]);
    $dbh->delete($SCALAR,$HASHREF);
    $dbh->update($SCALAR,$HASHREF,[$HASHREF|$SCALAR]);

DESCRIPTION

The SQL::SqlObject module allows you to use the DBI module with a hashref-based interface to the data.

Additionaly, as a wrapper module, calls on the SQL::SqlObject instance object which refer to a native DBI method are passed through to the underlying DBI object.

Basicly, this module provides several functions to the underlying DBI object which are of great practical convience, particularly when use DBI under CGI.

ABSTRACT

This program provides a set of utility functions to extend the functionality of an underlying DBI object around which the SQL::SqlObject is 'wrapped'.

This is intended to ease the delevopment of SQL intensive applications.

This is not intended as a replacement for Tim Bunce's DBI module, nor is it intended to surplant a full understanding of that module, which the authors of the program consider a critical must for database programing in perl.

If you have not read the documentation for Tim Bunce's DBI module, or are not very familiar with that document please take take time to read it now.

Each SQL::SqlObject instance object relies on an underly DBI object, the full functionality of which is available through the SQL::SqlObject wrapper.

EXAMPLES

    use SQL::SqlObject;

    # create an instance object connected to my_db
    $dbh = new SQL::SqlObject ("my_db");

    # print the number of rows in the table 'name'.
    print $dbh->value("select count(*) from name");

    # Print all of the first names in the table 'name' separated by
    # HTML line breaks.
    print join '<br>',$dbh->list("select first_name from name");

    # Print a list of all of the columns followed by the appropriate
    # data for a specific last name separated by HTML line breaks.
    %h = $dbh->hash("select * from name where last_name is 'Goff'");
    for (keys %h)) {
      print "$_: $h{$_}<br>";
    }

    # Print all of the names in the table 'name' separated by
    # HTML line breaks.
    for ($dbh->hashes("select first_name,last_name from name")) {
      print "$_->{first_name} $_->{last_name}<br>";
    }
    sub callback { print join '', map { "$_ => $h->{$_}\n" } shift }
    $dbh->hashes('select first_name,last_name from name',\&callback);

    # Insert data into the 'name' table
    %h = (first_name => 'Jeff', last_name => 'Goff');
    $dbh->insert('name',\%h);

    # Insert data into the 'name' table where an exactly
    # matching record doesn't already exist
    %h = (first_name => 'Corwin', last_name => 'Brust');
    if ($dbh->cond_insert('name',\%h)) {
      print "record was inserted"
    } else {
      print "record already existed"
    }

    # don't insert if a partial match is found
    %oh = (first_name => 'Corwin');
    $dbh->cond_insert('name',\%h, \%oh)

    # Insert data into the 'name' table and return
    # the 'name_id' field for the new row
    %h = (first_name => 'Someone' => last_name => 'New');
    $id = $dbh->insert_select('name',\%h);

    # same thing
    $id = $dbh->insert_select('name',\%h, 'name_id');

    # Insert data into the name table unless a matching
    # record is found.  Return 'name_id' for the existing
    # or inserted record
    %h = (first_name => 'Another' last_name => 'Newbie');
    $id = $dbh->cond_insert_select('name',\%h);

    # same thing
    $id = $dbh->cond_insert_select('name',\%h,'name_id');

    # partial match
    %oh = { first_name => 'Another' };
    $id = $dbh->cond_insert_select('name',\%h, \%oh);

    # same thing
    $id = $dbh->cond_insert_select('name',\%h, \%oh, 'name_id');

    # Delete a record from the 'name' table
    %h = (first_name => 'John', last_name => 'Public');
    $dbh->delete('name',\%h);

    # Update the 'Jeff Goff' record with a new business phone number
    %old = (first_name => 'Jeff', last_name => 'Goff');
    %new = (first_name => 'Jeff', last_name => 'Goff', bus_phone => '786-9601');
    $dbh->update('name',\%new,\%old);

CONSTRUCTOR

  $dbh = new SQL::SqlObject( [ SCALAR, [ SCALAR, [ SCALAR , [ SCALAR ] ] ]);

Creates an instance object of SQL::SqlObject class, connected to a database.

Arguments are as follows, each having a corrisponding accessor method.

database name

The name of the database to which a connection should be made.
driver name (dsn)

The name of database driver to be used, in the format specified by the BDI module.

e.g. dbi::pg
user name

The name of the user as which SQL::SqlObject will attempt make it's database connection.

NOTE: This is for the database's purposes only. This does not attempt any change in the effective user id under which a program using SQL::SqlObject is run.
password

The password for user name, above, to be used in establishing a database connection.

NOTE: Again, this is the password for the database user, not a system password.

ACCESSORS

These methods provide access to the internal data stored by SQL::SqlObject instance objects.

dbh

  $dbh->dbh

Provides access to the underlying DBI object.

The database handle (DBI) is created the first time it is used, so don't try somthing like:

  # This always works, if the module is 
  # properly installed and configured
  print "Connected" if $dbh->dbh;

To test if your SQL::SqlObject instance object is connected the DBI which it is configured to wrap (eg: it has been used, since the instance object was created) use this:

  print "Connected" if $dbh->is_connected;

NOTE: This is provided for the sake of completeness, and should not be assigned to except possably by a program sub-classing this module (See sub-classing).

db_name

  $name = $dbh->db_name;
  $dbh->db_name = 'my_database';

The name of database to which we'll be connecting.

db_user

  $user = $dbh->db_user;
  $dbh->db_user = 'perl_db_user';

The name of the database user for us to connect as.

db_password

  $passwd = $dbh->db_password;
  $dbh->db_password = 'perl_db_user_password';

The database password for the user as which we are connecting.

db_dsn

  $dsn = $dbh->db_dsn;
  $dbh->db_dsn = 'dbi::Sybase';

METHODS

The following subrutines are public methods available to instance objects of the SQL::SqlObject class.

is_connected

  $bool = $dbh->is_connected

Returns $bool will contain the value 1 (one) if the database handle has been invoked since the SQL::SqlObject instance object was created.

value

  SCALAR = $dbh->value( SCALAR )

Given a SQL SELECT statement return the first value of the result set returned by the database after running that SQL.

  $sql = 'select first_name from name order by first_name limit 1';
  $firstfirst = $dbh->value($sql);

array

  LIST | LISTREF = $dbh->array( SCALAR )

Given a SQL SELECT statement return all values returned by that statement after running that SQL, as a list or list reference.

This effectivly provides one step access to the fetchrow_arrayref method provided to DBI's statement handles. See Statement Handle Methods in the DBI documentation for more information on the fetchrow_arrayref method.

list

  LIST | LISTREF = $dbh->list( SCALAR )

Given a SQL statement return the first value from all rows returned by the database after running that SQL.

  $sql = 'select first_name from name order by first_name';
  @list = $dbh->list($sql);
  $listref = $dbh->list($sql);

hash

  HASH | HASHREF = $dbh->hash( SCALAR )

Given a SQL SELECT statement return all field names and values returned by the database after running that SQL as a hash or hash reference.

This effectivly provides one step access to the fetchrow_hashref method provided to DBI's statement handles. See Statement Handle Methods in the DBI documentation for more information on the fetchrow_hashref method.

  my $sql = "select * from names where name_id = 1";
  %hash = $dbh->hash($sql);
  while my ($k, $v) (each %hash) {
    print "Field: $k\t";
    print "Value: $v\n";
  }

  $hashref = $dbh->hash($sql);
  printf "Field: %-20s\t%%s\n" $_, $hashref->{$_} for keys %$hashref;

hashes

  LIST | LISTREF = $dbh->hashes( SCALAR )

Given a SQL SELECT statement return all field names and values returned by the database after running that SQL as a list of hash references, or reference to a list of hash references.

Like the hash method, above, but returns all database rows (where hash will return data from -at most- one row).

  my $sql = "select * from names order by name_id";
  @AoH = $dbh->hashes($sql);
  for my ($href) (@AoH) {
    print "$href->{last_name}, $href->{first_name}\n";
  }

  $listref = $dbh->hashes($sql);
  print map {
    "$listref->[$_]->{first_name} $listref->[$_]->{last_name}"
  } for 0..$#  $listref;

insert

  $dbh->insert( SCALAR, HASHREF);

Given a table name and a reference to an hash of field names and values, perform a SQL INSERT query.

  %data = (first_name => 'Larry', last_name => 'Wall');
  $dbh->insert('name',\%data);

cond_insert

  SCALAR = $dbh->cond_insert( SCALAR, HASHREF [, HASHREF ])

Given a table name and a reference to an hash of field names and values, perform a SQL INSERT query unless a record exists already exists in the specified table matching all values given in the hash reference.

  %data = (first_name => 'Tim', last_name => 'Bunce');
  $bool_did_insert = $dbh->cond_insert('name',\%data);

If a second hash reference is provided, no INSERT QUERY is performed if a record can be found which exactly matches the values provided for the fields listed therein.

  %data = (first_name => 'Tim', last_name => 'Bunce');
  %check = (last_name => 'Bunce');
  $bool_did_insert = $dbh->cond_insert('name',\%data, \%check);

If an INSERT QUERY was performed, cond_insert returns 1, otherwise no return value is defined.

insert_select

  SCALAR = $dbh->insert_select( SCALAR, %HREF [, SCALAR] );

Given a table name; a reference to an hash of field names and values; and (optionaly) a field name to select on success: Perform a SQL INSERT QUERY and select field name from the newly inserted row.

If no field name is provided a value of "<table>_id" is assumed.

  %data = (first_name => 'Bruce', last_name => 'Banner');
  $name_id = $dbh->insert_select('name',\%data);

  # get the 'create_time' field, after insert...
  %data = (first_name => 'Peter' last_name => 'Parker');
  $ctime = $dbh->insert_select('name',\%data,'create_time');

cond_insert_select

  SCALAR = $dbh->cond_insert_select( SCALAR, HASHREF [, HASHREF] [, SCALAR ])

Given a table name; a reference to an hash of field names and values; (optionaly) a reference to a second hash of field names and values; and (optionaly) a field name: Perform a SQL INSERT QUERY and select field name from the newly inserted row only if a row matching all values from the second hash reference (or the first, if only one was provided) cannot be found within the given table, then return field name.

As with cond_insert, if no field name is provided a value of "<table>_id" is assumed.

cond_insert_select returns field name

  %data = (first_name => 'Pappa', last_name => 'Smurf');
  $name_id = $dbh->cond_insert_select('name',\%data);


  # get name.name_id for a record where last_name = 'Ock'
  # or insert a new record for Doc Ock and get the name_id
  # for the new row.
  %data = (first_name => 'Doc', last_name => 'Ock');
  %where = (last_name => 'Ock');
  $name_id = $dbh->cond_insert_select('name',\%data,\%where);

  # insert a record for marilyn mason (unless one already
  # exists).  In either case, get the create_date for
  # marilyn's record in the 'name' table.
  %data = (first_name => 'Marilyn', last_name => 'Mason');
  $cdate = $dbh->cond_insert_select('name',\%data,'create_date');

  # insert a record for Doc Smith (unless there is already a record
  # where first_name = 'Doc').  Return the last_name field of the
  # record matched or inserted.
  %data = (first_name => 'Doc', last_name => 'Smith');
  %where = (first_name => 'Doc');
  $last = $dbh->cond_insert_select('name',\%data,\%where,'last_name');
  if ($last eq 'Smith') { print "inserted Doc Smith !" }
  else                  { print "found Doc $last !"    }

update

  $dbh->update( SCALAR, HASHREF,  SCALAR | HASHREF)

Given a table name; a reference to a hash of field names and values; and either a SQL where clause or a reference to a second hash of field names and values: Update table name.

  # set last_name = 'Smith' where first_name = 'Doc'
  %data = { last_name => 'Smith' };
  $dbh->update('name', \%data, q/where first_name = 'Doc'/);

  # set first_name = 'Bob' where last_name = 'Smith'
  %data = ( first_name => 'Bob' );
  %where = { last_name => 'Smith' };
  $dbh->update('name',\%data, \%where);

delete

  $dbh->delete (SCALAR, HASHREF)

Given a table name and a reference to a hash of field names and values, delete records from table name which match hold values currisponding to those in the provided hash reference for fields specified by the keys of that hash reference.

  %data = (first_name => 'Bob', last_name => 'Smith');
  $dbh->delete('name',\%data);

SUB-CLASSING

In using this module, for our own nefarious purposes, we have found that providing the various server/project specific data is often most easily accomplished by creating a per server/project subclass of the SQL::SqlObject module.

This is quite easy to accomplish, and though TMTOWTDI certialy rules our universe, the following should provide you with a good start to doing this for your own needs.

  package MySqlProject;

  use strict;
  use warnings;
  use SQL::SqlObject;
  use base 'SQL::SqlObject';

  # post constructor processing
  sub _init {
    my ($self) = @_;
    $self->db_dsn      = 'dbi:mysql';     # DBD drive
    $self->db_name     = 'mysqldatabase'; # db name
    $self->db_name     = 'myclient';      # db username
    $self->db_password = 'mypassword';    # db password
  }

  __END__

  =head1 SEE ALSO

  L<SQL::SqlObject> - our base class

This allows you to write a script like

  #/bin/perl -w
  #
  # List of sometable entries, seperated by a blank line
  #
  use strict;
  use MySqlProject;
  my $dbh = new MySqlProject;
  for my $hashref ($dbh->hashes("select * from sometable"))
  {
      while (my ($col_name, $value) = each %$hashref)
      {
          print $col_name, '.' x 20 - length $col_name, $value, "\n";
      }
      print "\n";
  }

Compare that to the following exactly equilivent example which doesn't make use of the subclass.

  #/bin/perl -w
  #
  # List of sometable entries, seperated by a blank line
  #
  use strict;
  use MySqlProject;
  my $dbh = new MySqlProject(
                              --name     => 'mysqldatabase',
                              --user     => 'myclient',
                              --password => 'mypassword'
                             );
  $dbh->db_dsn = 'dbi::mysql';
  for my $hashref ($dbh->hashes("select * from sometable"))
  {
    ...

As you can see, there are only a few lines of difference, however, consider the need to reapeat this in every script which makes a database connection the convience of the former approach becomes clear.

NOTE

SQL::SqlObject may be redistributed under the same terms as Perl.

AUTHOR

The SqlObject interface was written by Jeff Goff (<jgoff@hargray.com>) and Corwin Brust (<cbrust@mpls.cx>)

To install SQL::SqlObject, copy and paste the appropriate command in to your terminal.

cpanm

cpanm SQL::SqlObject

CPAN shell

perl -MCPAN -e shell
install SQL::SqlObject

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)