DBIx::Abstract - DBI SQL abstraction

  use DBIx::Abstract;
  $db = DBIx::Abstract->connect({
    driver=>'mydriver',
    host=>'myhost.org',
    dbname=>'mydb',
    user=>'myuser',
    password=>'mypassword',
    });

  if ($db->select('*','table')->rows) {
    while ($data = $db->fetchrow_hashref) {
      ...
    }
  }

  ($name) = $db->select('name','table',{id=>$id})->fetchrow_array;

  ***

  $db = DBIx::Abstract->connect( { driver=>'csv', f_name=>'foo/' } );

  ***

  $db = DBIx::Abstract->connect({
    dsn=>'dbi:someotherdb:so_db_name=mydb',
    user=>'myuser',
    password=>'mypassword',
    });

This module provides methods for doing manipulating database tables This module provides methods retrieving and storing data in SQL databases. It provides methods for all of the more important SQL commands (like SELECT, INSERT, REPLACE, UPDATE, DELETE).

It endeavors to produce an interface that will be intuitive to those already familiar with SQL.

Notable features include:

  * data_source generation for some DBD drivers.
  * Can check to make sure the connection is not stale and reconnect
    if it is.
  * Controls statement handles for you.
  * Can delay writes.
  * Generates complex where clauses from hashes and arrays.
  * Shortcuts (convenience functions) for some common cases. (Like
    select_all_to_hashref.)

Unless otherwise mentioned all methods return the database handle.

CWconnect($connect_config | $dbihandle [,$options]) CONSTRUCTOR Open a connection to a database as configured by CW$connect_config. CW$connect_config can either be a scalar, in which case it is a DBI data source, or a reference to a hash with the following keys:

 dsn      -- The data source to connect to your database

 OR, DBIx::Abstract 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

This clones the object. For those times when you need a second connection to the same DB. If you need a second connection to a different DB, create a new object with 'connect'. This operation is logged at level 5 with the message Cloned.

Check to see if this object is connected to a database. It checks to see if it has a database handle and if that handle's Active attribute is true.

If the object is not connected to a database it will reconnect using the same parameters connect was originally called with.

Makes sure that the object is connect to a database. Makes sure that the connect is active (by sending a SELECT 1). If there is no connection, or the connection is not active then it tries to reconnect. If it fails to reconnect then it dies.

($key[,$value]) ({key=>$key[,value=>$value]) Set option CW$key to CW$value. Available keys are:

  loglevel (default 0)
      0 -- Fatal errors only
      1 -- Modifications
      2 -- And selects
      3 -- And user created queries
      4 -- And results of queries
      5 -- And other misc commands
      6 -- Internals of commands

  logfile (default undef)
    Log file

  delaymods (default false)
    Delay making modifications to the database until
    run_delayed is run.

  useCached
    If this is true then prepare_cached is used instead of prepare.
    Checkout the DBI documentation on this feature before using this
    feature.

  saveSQL
    If this is true then with each query DBIx::Abstract will stuff the generated
    SQL into the 'lastsql' key in the self payload.

  Additionally you may use any valid DBI attribute.  So, for instance, you
  can pass AutoCommit or LongReadLen.

($sql,@bind_params) ({sql=>$sql,bind_params=>[@bind_params]}) This sends CW$sql to the database object's query method. This should be used for applications where the existing methods are not able to generate flexible enough SQL for you. If you find yourself using this very often with things other then table manipulation (eg 'create table','alter table','drop table') then please let me know so I can extend DBIx::Abstract to include the functionality you are using. This operation is logged at level 3

Execute delayed update/insert/delete queries. This operation is logged at level 5 with the message Run delayed.

($table[,$where]) ({table=>$table[,where=>$where]}) Deletes records from CW$table. See also the documentation on DBIx::Abstract Where Clauses.

($table,$fields) ({table=>$table,fields=>$fields}) $table is the name of the table to insert into. $fields is either a reference to a hash of field name/value or a scalar containing the SQL to insert after the SET portion of the statement. These all produce functionally equivalent SQL.

  $db->insert('foo',{bar=>'baz'});
  $db->insert('foo',q|bar='baz'|);
  $db->insert({table=>'foo',fields=>{bar=>'baz'}});
  $db->insert({table=>'foo',fields=>q|bar='baz'|});

  $db->insert('foo',{name=>'bar',date=>['substring(now(),1,10)']});

  INSERT INTO foo (name,date) VALUES (?,substring(now(),1,10))

($table,$fields) ({table=>$table,fields=>$fields}) $table is the name of the table to replace into. $fields is either a reference to a hash of field name/value or a scalar containing the SQL to insert after the SET portion of the statement. Replace works just like insert, except that if a record with the same primary key already exists then the existing record is replaced, instead of producing an error.

($table,$fields[,$where]) ({table=>$table,fields=>$fields[,where=>$where]}) $table is the table to update. $fields is a reference to a hash keyed on field name/new value. See also the documentation on DBIx::Abstract Where Clauses.

CWselect ($fields,[$table,[$where[,$order]]]) ({fields=>$fields,table=>$table[,where=>$where][,order=>$order][,join=>$join][,group=>$group]}) The select method returns the DBIx::Abstract object it was invoked with. This allows you to chain commands. $fields can be either an array reference or a scalar. If it is an array reference then it should be a list of fields to include. If it is a scalar then it should be a literal to be inserted into the generated SQL after SELECT. $table can be either an array reference or a scalar. If it is an array reference then it should be a list of tables to use. If it is a scalar then it should be a literal to be inserted into the generated SQL after FROM. See also the documentation on DBIx::Abstract Where Clauses. $order is the output order. If it is a scalar then it is inserted literally after ORDER BY. If it is an arrayref then it is join'd with a comma and inserted. $join is there to make joining tables more convenient. It will takes one or more (as an arrayref) sets of statements to use when joining. For instance:

  $dbh->select({
    fields=>'*',
    table=>'foo,bar',
    join=>'foo.id=bar.foo_id',
    where=>{'foo.dollars',['>',30]}
    });

  SELECT * FROM foo,bar WHERE (foo.dollars > ?) and (foo.id=foo_id)

($fields,$table[,$where]) ({fields=>$fields,table=>$table[,where=>$where]}) This returns a hashref to the first record returned by the select. Typically this should be used for cases when your where clause limits you to one record anyway. $fields is can be either a array reference or a scalar. If it is an array reference then it should be a list of fields to include. If it is a scalar then it should be a literal to be inserted into the generated SQL. $table is the table to select from. See also the documentation on DBIx::Abstract Where Clauses.

($fields,$table[,$where]) ({fields=>$fields,table=>$table[,where=>$where]}) This returns a arrayref to the first record returned by the select. Typically this should be used for cases when your where clause limits you to one record anyway. $fields is can be either a array reference or a scalar. If it is an array reference then it should be a list of fields to include. If it is a scalar then it should be a literal to be inserted into the generated SQL. $table is the table to select from. See also the documentation on DBIx::Abstract Where Clauses.

($fields,$table[,$where]) ({fields=>$fields,table=>$table[,where=>$where]}) This returns a array to the first record returned by the select. Typically this should be used for cases when your where clause limits you to one record anyway. $fields is can be either a array reference or a scalar. If it is an array reference then it should be a list of fields to include. If it is a scalar then it should be a literal to be inserted into the generated SQL. $table is the table to select from. See also the documentation on DBIx::Abstract Where Clauses.

($fields,$table[,$where]) ({fields=>$fields,table=>$table[,where=>$where]}) This returns a hashref to all of the results of the select. It is keyed on the first field. If there are only two fields then the value is just the second field. If there are more then two fields then the value is set to an arrayref that contains all of the fields. $fields is can be either a array reference or a scalar. If it is an array reference then it should be a list of fields to include. If it is a scalar then it should be a literal to be inserted into the generated SQL. $table is the table to select from. See also the documentation on DBIx::Abstract Where Clauses.

This is just a call to the DBI method.

This calls fetchrow_hashref and dereferences it for you.

This method calls the database handle's method of the same name.

This method calls the database handle's method of the same name.

This method calls the database handle's method of the same name.

This method is passed to the database handle via AUTOLOAD.

This method is passed to the database handle via AUTOLOAD.

This method is passed to the database handle via AUTOLOAD.

This method is passed to the database handle via AUTOLOAD.

This method is passed to the database handle via AUTOLOAD.

This method is passed to the statement handle via AUTOLOAD.

This method is passed to the statement handle via AUTOLOAD.

This method is passed to the statement handle via AUTOLOAD.

Where clauses in DBIx::Abstract can either be very simple, or highly complex. They are designed to be easy to use if you are just typing in a hard coded statement or have to build a complex query from data. Wheres are either a scalar, hash-ref or array-ref: If it is a scalar, then it is used as the literal where. If it is a hash-ref then the key is the field to check, the value is either a literal value to compare equality to, or an array-ref to an array of operator and value.

  {
   first=>'joe',
   age=>['>',26],
   last=>['like',q|b'%|]
  }

 WHERE first = ? AND last like ? AND age > ?

  [
    {
      first=>'joe',
      age=>['>',26]
    },
    'OR',
    {
      last=>['like',q|b'%|]
    }
  ]

 WHERE (first = ? AND age > ?) OR (last like ?)

  [
    {
      first=>'joe',
      last=>['like','%foo%'],
    },
    'AND',
    [
      {age=>['>',26]},
      'OR',
      {age=>['<',30]}
    ]
  ]

  WHERE (first = ? AND last like ?) AND ((age > ?) OR (age < ?))

These drivers have been reported to work:

* mysql (development environment)

* Pg (development environment)

* Oracle

* XBase

Any driver that uses ODBC syntax should work using the hash ref method. With other drivers you should pass the DBI data source instead (this method will work with all drivers.)

* Fixed a bug discovered by Jan Martin Mathiassen <reaper@mindriot.as>. If you pass in a password but no username it's supposed to ignore the password. Instead it was clearing it in the hash that you passed in but still using it.

* Removed Test::More and Test::Builder from the distribution put Test::Simple in the prerequisites.

Andrew Turner <turnera@cpan.org>

Portions copyright 2001-2002 by Andrew Turner

Portions copyright 2000-2001 by Adelphia Business Solutions

Copyright 1998-2000 by the Maine Internetworks (MINT)

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

http://sourceforge.net/projects/dbix-abstract/

DBI(3)