/ 
Egg-Release-2.21
River stage two • 17 direct dependents • 17 total dependents
/ Egg::Plugin::DBI::Easy

NAME

Egg::Plugin::DBI::Easy - Plugin of an easy doing as for treatment of DBI.

SYNOPSIS

use Egg qw/ DBI::Easy /;

# The result of SELECT is received with HASH.
my $hash= $e->dbh_hashref( 'id',
   q{ SLECT id, name, email FROM myapp_table WHERE id = ? }, $id)
   || return 0;

# The data table object is used.
my $hash= $e->db->myapp_table->hashref
   ([qw/ id name email /], ' id = ? ', $id) || return 0;

# Data is added.
$e->db->myapp_table->insert
   ({ id => $id, name => 'myname', emai => 'myname@domain.name' });

# Specified data is deleted.
$e->db->myapp_table->delete(' id = ? ', $id);

# All data is deleted.
$e->db->myapp_table->clear(1);

DESCRIPTION

It is a plug-in to be able to write the description of the DBI processing that tends to become tedious easily.

* Complex SQL cannot be treated. Please examine the introduction of "Egg::Model::DBIC " wanting the easy treatment of complex SQL.

CONFIGURATION

It setup it with 'plugin_dbi_easy'.

table_alias = [HASH_REF]

Alias for the table object is setup.

table_alias => {
  any_name=> 'real_table_name',
  },

# It comes to be able to operate 'real_table_name' table by $e->db->any_name.

METHODS

dbh

When the DBI::Transaction plug-in is not loaded, the dbh method can be used.

sql_datetime ( [TIME] )

Time::Piece::MySQL->mysql_datetime is returned.

An arbitrary date can be acquired by passing TIME the value of time.

# Time of the day before of the seventh is acquired.
$e->sql_datetime( time- (7* 24* 60* 60) );

* Please look at the document of Time::Piece::MySQL.

dbh_hashref ( [TRUE_CHECK_COLUMN], [SQL], [EXECUTE_ARGS] )

It is dbh->prepare( [SQL] ), and sth->execute( [EXECUTE_ARGS] ).

If the result is preserved in HASH and the value of TRUE_CHECK_COLUMN is effective, the HASH reference is returned.

* The result returns only one record.
* When two or more records become a hit, the first data is returned.

 my $hash= $e->dbh_hashref
           ('id', 'SELECT * FROM myapp_table WHERE id = ?', $id)
           || return 0;
 
 print $hash->{id};

dbh_arrayref ( [SQL], [EXECUTE_ARGS], [CODE_REF] )

The result is returned by the ARRAY reference.

* Each data of the ARRAY inside becomes HASH.

my $array= $e->dbh_arrayref
         ('SELECT * FROM myapp_table WHERE email like ?', '%name%')
         || return 0;

for my $db (@$array) {
  print "$db->{id} = $db->{name} : $db->{email} \n";
}

If EXECUTE_ARGS is ARRAY reference, CODE_REF can be passed.

my $code= sub {
	my($array, %hash)= @_;
	return 0 unless ($hash{name} and $hash{email});
	push @$array, "$hash{id} = $hash{name}:$hash{email}\n";
  };
my $array= $e->dbh_arrayref
    ('SELECT * FROM myapp_table WHERE email like ?', ['%name%'], $code)
    || return 0;
print join('', @$array);

Or

my $output;
my $code= sub {
	my($array, %hash)= @_;
	return 0 unless ($hash{name} and $hash{email});
	$output.= "$hash{id} = $hash{name}:$hash{email} \n";
  };
$e->dbh_arrayref
   ('SELECT * FROM myapp_table WHERE email like ?', ['%name%'], $code);
$output || return 0;
print $output;

dbh_scalarref ( [SQL], [EXECUTE_ARGS] )

The result is returned by the SCALAR reference.

* When two or more records become a hit, the first data is returned.

my $scalar= $e->dbh_scalarref
          ('SELECT email FROM myapp_table WHERE id = ?', $id)
          || return 0;

print $$scalar;

db

The object that generates the data table object is returned.

 $e->db->table_name->...

* The table name is used and the comeback call is used for the method
  putting it.
* When the table name is improper as the method name of Perl, it is not
  possible to use it.

DB METHODS

new

Constructor who returns DB object.

AUTOLOAD

The method of returning the handler object recognizing the table name the method of no existence is dynamically generated.

DESTROY

It doesn't do at all.

HANDLER METHODS

new

Constructor who returns handler object.

hashref ( [GET_COLUMN], [WHERE_SQL], [EXECUTE_ARGS] )

The result of dbh_hashref is returned.

If GET_COLUMN is ARRAY, the first column is treated most as TRUE_CHECK_COLUMN.

If GET_COLUMN is usual SCALAR, it is treated as TRUE_CHECK_COLUMN, and '*' is continued to SELECT.

* Please write ORDER BY etc. following WHERE in WHERE_SQL.

my $hash= $e->db->myapp_table->hashref('id', 'id = ?', $id)
       || return 0;

print $hash->{id};

arrayref ( [GET_COLUMN], [WHERE_SQL], [EXECUTE_ARGS], [CODE_REF] )

The result of dbh_arrayref is returned.

GET_COLUMN accepts the ARRAY reference.

When GET_COLUMN is omitted, '*' is used.

my $array= $e->db->myapp_table->arrayref(0, 'email like ?', '%name%')
        || return 0;

for my $db (@$array) {
  print "$db->{id} = $db->{name} : $db->{email} \n";
}

If EXECUTE_ARGS is ARRAY reference, CODE_REF can be passed. * Please refer to 'dbh_array_ref' for the example of the code.

scalarref ( [GET_COLUMN], [WHERE_SQL], [EXECUTE_ARGS] )

The result of dbh_scalarref is returned.

my $scalar= $e->db->myapp_table->scalarref('email', 'id = ?', $id)
         || return 0;

print $$scalar;

scalar ( [GET_COLUMN], [WHERE_SQL], [EXECUTE_ARGS] )

print $e->db->myapp_table->scalar('email', 'id = ?', $id);

insert ( [DATA_HASH] || [PKEY_ARRAY], [DATA_HASH])

The data record is added.

$e->db->myapp_table->insert(
  user_name => 'hoge',
  email_addr=> 'hoge@domain',
  age       => 20,
  );

When an unpalatable column exists, it is specified for PKEY_ARRAY that it is included in the INSERT sentence as the column of the serial form is included in DATA_HASH by a double ARRAY reference.

$e->db->myapp_table->insert( [['id']], {
  id        => 1, 
  user_name => 'hoge',
  email_addr=> 'hoge@domain',
  });

# When this form is used, DATA_HASH is a thing passed without fail
# by the HASH reference.

* The IGNOR_COLUMN specification by the second argument was abolished.

update ( [WHERE_COLUMNS], [DATA_HASH] )

The data record is updated.

WHERE_COLUMN is good at the thing specified by the ARRAY reference in the column name to retrieve data.

Moreover, it develops with following WHERE_COLUMN and the WHERE phrase is generated.

  • When you give one column by a usual variable.

    $e->db->my_table->update('id', { id=> 1, myname=> 'hoge' });

WHERE key_name = ?

    * This is the same as giving usual HASH to DATA_HASH.

    $e->db->my_table->update( id=> 1, myname=> 'hoge' );

  • When you delimit all columns with 'and'.

    $e->db->my_table->update(
  [qw/ myname age sex /],
  { myname=> 'hoge', age=> 20, sex=> 'man', email=> 'myname@email' }
  );

WHERE myname = ? and age = ? and sex and ?

  • When you want to specify inequitable value, like, and '>', etc.

    $e->db->my_table->update(
  [qw/ myname age:>= sex:like /],
  { myname=> 'hoge', age=> 18, sex=> '%man%', email=> 'myname@email' }
  );

WHERE myname = ? and age != ? and sex like ?

  • When you want to place 'or' etc.

    $e->db->my_table->update(
  ['myname', { or => 'age:>=' }, 'sex'],
  { myname=> 'hoge', age=> 20, sex=> 'man', email=> 'myname@email' }
  );

WHERE myname = ? or age >= ? and sex = ?

* Only the above-mentioned WHERE phrase is generable.

* The column given to WHERE_COLUMN is not reflected in the SET phrase.

The addition and subtraction of a numeric column is set in the value of DATA_HASH by the SCALAR reference.

# One value of 'age' is added.
$e->db->myapp_table->update('id', { id => 1, age=> \'1' });

or

# One value of 'age' is subtracted.
$e->db->myapp_table->update('id', { id => 1, age=> \'-1' });

update_insert ( [WHERE_COLUMNS], [DATA_HASH] )

Update and insert are distributed by the existence of the data of WHERE_COLUMNS.

The way to pass WHERE_COLUMNS is similar to update.

$e->db->my_table->update_insert(
  [qw/ id user_name /],
  { id=> 1, user_name=> 'hoge', email=> 'hoge@domain' },
  );

It is specified that it is included in the VALUES phrase if insert is done by using a double ARRAY reference when there is an unpalatable column.

$e->db->my_table->update_insert(
  [[qw/ id /], qw/ user_name /]
  { id=> 1, user_name=> 'hoge', email=> 'hoge@domain' },
  );

upgrade ( [DATA_HASH] )

Update that doesn't need WHERE is done. For two or more data update in a word.

$e->db->myapp_table->upgrade({ age => 18 });

* Addition and subtraction of numeric column.

my $num= -1;
$e->db->myapp_table->update({ age => \$num });

delete ( [WHERE_SQL] )

The data deletion with the condition is done.

$e->db->myapp_table->delete('age < ?', 18);

clear ( [TRUE] )

All the data of the table inside is deleted.

* If TRUE is not given, the exception is generated.

$e->db->myapp_table->clear(1);

SEE ALSO

Egg::Model::DBI, Egg::Plugin::DBI::Transaction, Egg::Release, Tie::Hash::Indexed,

AUTHOR

Masatoshi Mizuno <lushe@cpan.org>

COPYRIGHT

Copyright (C) 2007 by Bee Flag, Corp. <http://egg.bomcity.com/>, 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.

1 POD Error

The following errors were encountered while parsing the POD:

Around line 45:

L<> starts or ends with whitespace