NAME

awe::Db - Accurate database interface

SYNOPSIS

  use awe::Db;

  my $table='accounts';

  my $data ={date =>'now',
             summa=>123};

  $rv = dbInsert($table, $data);

  $rv = dbUpdate($table, $data, {date=>'02/03/02'});

  dbTransaction {
    $sth = dbSelect($table, '*', $where);
    $row = dbFetch($sth);
    $row = modifieFields($row);
    dbUpdate($table, $row, $where);
  };

DESCRIPTION

This module allows you to work with default framework's database in simple manier. It provides useful, but low-level interface. I recommend to use awe::Table instead. You can look into this module for more examples of using awe::Db also.

The module uses Error for throw errors.

Notation and Conventions

The following conventions are used in this document:

  $where   Specifies C<WHERE> part of SQL statement
  $table   Table name
  $data    Data fields and values for C<UPDATE> or C<INSERT>
  $rv      General Return Value (typically an integer)
  $extra   Extra string added to end of SQL statement
  $fields  Fields to select
  $sth     Statement handle object

The detailed descriptions of the $where, $extra, $data and $fields input parameters see bellow.

PROCEDURAL INTERFACE

The interface is not object-oriented.

dbSelect ($table, $fields, $where, $extra)

It returns the statement handle object if there is no errors. Otherwise it throws a error.

The $fields input parameter can be a string or a reference to the array of field's name you wan to be selected. In case it is the arrayref it is transformed to the string joining by the comma. If it is not specified, the * symbol is inserted to the SQL statement.

The $where input parameter is used in the other functions and have the same syntax. It can be just a string, an array reference or a hash reference. It is processed recursively, if it is the reference.

In case the processed parameter is a array reference, it is joined into a single string with elements separeted by the current logic operator. The default logic operator is and. If the processed element is a hashref then it is processed as hashref.

In case the processed parameter is a hash reference, it is joined into a single string with portions like this $key = ?. Where $key is the key of hash pair and the question-mark is a placeholder for bind values. The value of this hash pair is pushed to the array of bind values. If the value of hash pair is a array reference then it is processed as described above and the key name of this pair is used as logic to join. The logic can be any string, but there is no reasone to use other then and or or. If the logic is - (minus sign) then the value of this hash pair is pushed to the bind array as is.

The $extra parameter is just a string that is added to the tail of the resulted SQL statement. It is the place for the SQL operators like order by, group by etc.

The examples:

The function:

  dbSelect('users','',"login_date >= '03/02/01'",'order by login');

executes the SQL statement:

  SELECT * FROM users WHERE (login_data >= '03/02/01') order by login;

Next function illustrates using of bind values:

  dbSelect('users','login_date',{login=>'vasya'});

generates and executes:

  SELECT login_date FROM users WHERE (login = ?);

and the bind array contains one element: 'vasya';

This one illustrates the recursion:

  dbSelect('users','',{or =>{
                            name  => 'vasya',
                            login => 'petya',
                            },
                       and => [
                              "login_date >= '01/02/02'",
                              "login_date <= '01/10/02'"
                              ]
                       });

generates the SQL statement:

  SELECT * FROM users WHERE ((login_date >= '01/02/02'
  and login_date <= '01/10/02') and (login=? or name=?))

the bind array contains two elements: 'vasya','petya';

The similar SQL statement can be generated by these function:

Reverse array and hash:

  dbSelect('users','',["login_date >= '01/02/02'",
                       "login_date <= '01/10/02'",
                       or =>{
                            name  => 'vasya',
                            login => 'petya',
                            }
                       ]);

  SELECT * FROM users WHERE (login_date >= '01/02/02'
  and login_date <= '01/10/02' and (login=? or name=?))

Directly use bind values:

  dbSelect('users','',["login_date >= '01/02/02'",
                       "login_date <= '01/10/02'",
                       "(name = ? or login = ?)",
                        {'-' =>{
                            name  => 'vasya',
                            login => 'petya',
                            }
                        }
                       ]);

The $where parameter is processed by the prepareData function. Look at the its source code for more details or experiment with it.

dbInsert ($table, $data)

It returns the general return value (returned by DBI's execute()). Otherwise it throws a error. The $data input parameters work is similar to the $where but it use , (comma) as default logic parameter.

dbUpdate ($table, $data, $where)

It returns the general return value (returned by DBI's execute()). Otherwise it throws a error. For syntax of the $where input parameter look at the dbSelect function. The work of the $data input parameter is similar to the $where but it uses , (comma) as default logic parameter.

dbDelete ($table, $where)

It returns the general return value (returned by DBI's do()). Otherwise it throws a error. For syntax of the $where input parameter look at the dbSelect function.

dbConnect

Connects to the database specified by the sql.database config and using user name, password and attributes from the sql.login, sql.password and sql.attr. Then it sets the default database handle. No input and returened parameters. It throws a error if it is not connected to the database.

dbDisconnect

Disconnects form the database. It undefines the database handle. No input or returned parameters. It does nothing if there is no connection to the database.

dbRollback

Just executes the DBI's rollback.

dbCommit

Just executes the DBI's commit.

dbFetch ($sth)

Executes DBI's fetchrow_hashref for the specified statement handle with the parameter specfied by the sql.fieldsName config parameter and returns the value returned by DBI. As sad in DBI you must fetch all the data or call the finish method for the unfinished statement handle. There is no the analog of the finish in the awe::Db module.

dbGenerateId ($table)

Executes the SQL statement to select the ID for the new record in the specified table and returns fetched ID (just one numeric value). It uses the sql.generator config parameter to create the statement. See "CONFIG PARAMETERS" for information about the syntax of this parameter and other details.

dbTransaction CODE

Evaluates the CODE. If the awe::Db::Error error was thrown then it does rollback and the code is evaluated again. The number of evaluations is specified in the sql.transaction config parameter. If there is finished evaluation that has no the awe::Db::Error error thrown then it does commit and exit. The transaction doesn't catch not awe::Db::Error errors.

INTERNAL FUNCTIONS

These functions are not exported, but I think you would like to use their in som situations.

error ($code,..)

Throws awe::Error::Db error. It just executes awe::Log::fatal.

logQuery

It just executes log_notice('[SQL]',@_) if the debug log is allowed.

prepareData ($where,$separator,\@bind,$type);

$separator='and' or ','

$type = 0,1,2

getDbh

Returns database handle

setDbh

Sets database handle. This handle is set in the dbConnect function.

CONFIG PARAMETERS

There are some config parameters that are used by this module.

sql.datasource

The data source with DBI syntax.

Eg. DBI:InterBase:dbname=/var/db/test

sql.user

User name to connect to the database

sql.password

The password to connect to the database

sql.generator

The SQL statement that is used to generate the ID key for the table. The statemet's question-mark is replaced with the table name. The example of this parameter for the InterBase SQL server is:

  SELECT GEN_ID(?_seq,1) FROM table_gen

Of course, you must create special table_gen before. Something like this:

  CREATE TABLE table_gen (id INTEGER);
  INSERT INTO table_gen VALUES (1);

And you must create the generators (sequencor) for every tables you want to use the generator for.

  CREATE GENERATOR users_seq;

debug.sql

Log the statements and other database operations for debug. Default value is 1 (yes)

sql.fieldsName

This attribute is used to specify which attribute name the dbFetch() method should use to get the field names for the hash keys. Allowed values are low, up and none.

sql.transaction

The number of tries in the transaction. The default value is 5.

sql.attr

The text hash of DBI's attributes are used by connection. Look to DBI for details. The default value is:

  C<RaiseError:0 ShowErrorStatement:0>

EXAMPLES

Look for more examples at the awe::Table module.

ERRORS

All thrown errors are based on the awe::Error::Db abstract exception class.

It is the list of possible errors and the functions that can throw their:

13 => Can't connect to database

dbConnect()

28 => SQL: prepare/execute generator..

dbGenerateId()

28 => SQL: prepare/execute select..

dbSelect()

28 => SQL: do update..

dbUpdate()

28 => SQL: prepare/execute insert..

dbInsert()

28 => SQL: do delete ..

dbDelete()

29 => Call fetch until STH is not defined

dbFetch()

34 => No data to update or insert..

dbInsert(), dbUpdate()

36 => WARNING! You want to update/delete whole table

dbUpdate(), dbDelete()

SEE ALSO

awe(3), awe::Table(3), awe::Conf(3), DBI(3)

AUTHOR

Danil Pismenny <dapi@mail.ru>

cpanm awe::Db

perl -MCPAN -e shell
install awe::Db