Egg::Mod::EasyDBI - DBI is easily made available.
use Egg::Mod::EasyDBI { debug => 1, trace => 1, upgrade_ok => 1, clear_ok => 1, alias => { members => 'member_management_master', ... }, sql_abstract=> { logic => 'and', ...... }, }; use DBI; my $dbh= DBI->connect( ...... ); my $es= Egg::Mod::EasyDBI->new($dbh); $ed->trace(1); my $db= $es->db; my $members= $db->members; # SELECT * FROM members WHERE id = ? my $hoge= $members->hashref('id = ?', $id) || die q{ Data is not found. }; # SELECT * FROM members WHERE age > ? my $list= $members->arrayref('age > ?', 20) || die q{ Data is not found. }; # SELECT id FROM members WHERE user = ? my $id= $members->scalar(\'id', 'user = ?', 'boo') || die q{ Data is not found. }; # The processed list is acquired. my $list= $members->arrayref('age > ?', [20], sub { my($array, %hash)= @_; push @$array, "$hash{id} : $hash{user} : $hash{age}"; }) || die q{ Data is not found. }; # The data that can be immediately used is acquired. my $text; $members->arrayref('age > ?', [20], sub { my($array, %hash)= @_; $text.= <<END_DATA; --------------------------- ID : $hash{id} NAME : $hash{user} AGE : $hash{age} END_DATA }) || ""; # INSERT INTO members (id, user, age) VALUES (?, ?, ?); $members->insert( id=> 1, user=> 'zoo', age=> 20 ) || die q{ Fails in regist of data. }; # UPDATE members SET other = ?, age = age + 1 WHERE id = ? $members->update( id=> 1, other=> 'gao', age=> \1 ) || die q{ Fails in update of data. }; or $members->update(\'id = ?', { id=> [1], other=> 'gao', age=> \1 }) || die q{ Fails in update of data. }; # The record is added when failing in the update. $members->update_insert( user=> 'zaza', age=> 22 ); # It adds it if there is no record. $members->find_insert( user=> 'zaza', age=> 22 ); # UPDATE members SET age = ? $members->upgrade( age=> 20 ); # DELETE FROM members WHERE user = ? $members->delete('user = ?', 'zaza'); # DELETE FROM members; $members->clear; # SQL statement is used as it is. my $hash = $es->hashref(q{SELECT * FROM members WHERE id = ?}, $id); my $list = $es->arrayref(q{SELECT * FROM members WHERE age > ?}, $age); my $scalar= $es->scalar(q{SELECT user FROM members WHERE id = ?}, $id); $es->do(q{INSERT INTO members (id, user, age) VALUES (?, ?, ?)}, $id, $user, $age); # [[SQL::Abstract]] support. my $hash = $members->abs_hashref(\@fields, \%where, \@order); my $array= $members->abs_arrayref(\@fields, \%where, \@order); $members->abs_insert(\%fieldvals || \@values); $members->abs_update(\%fieldvals, \%where); $members->abs_delete(\%where); # Table uniting. # # SELECT a.user, a.message, b.id, b.age, c.email_addr # FROM messages a JOIN members b ON a.user = b.user # LEFT JOIN profiles c ON b.id = c.id # WHERE a.message_id = ? # my $jdb= $es->db(qw/ messages = members:a.user=b.user < profiles:b.id=c.id /); my $list= $jdb->arrayref( \'a.user, a.message, b.id, b.age, c.email_addr', 'a.message_id = ?', $msgid ); # If you process the transaction # If commit_ok is undefined, it is always rollback. $es->commit_ok(1); $es->close; $dbh->disconnect;
The processing of DBI by which the code tends to become complex is made available by an easy code.
The use of SQL::Abstract is enabled by the thing set to 'sql_abstract' by the start option.
use Egg::Mod::EasyDBI { sql_abstract => { logic => 'and', ...... }, };
This module doesn't correspond to CLOB of Oracle and bind_param to the BLOB field.
This module is not only for Egg. It is possible to use it also with the unit.
The start option can be defined by the HASH reference.
use Egg::Mod::EasyDBI { debug => 1, ...... };
Debug mode is made effective.
SQL sentence issued to STDERR is output when keeping effective.
It passes it to the trace method of the data base handler.
To pass TRACE_FILE, it defines it by the ARRAY reference.
trace => [3, '/path/to/trace_log'],
The upgrade method can be used with the table object. When the upgrade method is called, the exception is generated when this is invalid.
The clear method can be used with the table object. When the clear method is called, the exception is generated when this is invalid.
The alias to the table name is defined.
Using it when a very long table name is used or multi byte character is used for the table name is convenient.
alias=> { members => 'member_management_master', profiles=> 'member_profile_master', },
The table object is used with a set key.
my $table= $ed->db->profiles;
SQL::Abstract can be used.
The content of HASH is an option to pass everything to SQL::Abstract.
Especially, empty HASH is set if there is no option to specify.
sql_abstract => {},
Constructor.
The data base handler made beforehand is passed to DBH. OPTIONS is accepted among the start options excluding debug, sql_abstract, and alias. This OPTIONS overwrites a set value of the start option.
my $dbh= DBI->connect( .... ); my $es= Egg::Mod::EasyDBI->new($dbh);
A set value of the start option is returned.
The data base handler passed to the constructor is returned.
When the method of no support with this object is called, the method of the data base handler is called.
$es->trace(1); $es->commit; $es->rollback; # The reference to the value of the data base handler is impossible. # The data base handler is seen directly. if ($es->dbh->{Active}) { ....... }
When ARGS is not passed, the object of Egg::Mod::EasyDBI::db is returned. When ARGS is passed, the object of Egg::Mod::EasyDBI::joindb is returned.
my $db= $es->db; my $jdb= $es->db(qw/ members = profiles:a.id=b.id /);
When the close method is called, commit is done if this value is effective. This is convenient to settle at the end of processing and to issue committing.
This method functions only when the transaction is effective.
$es->commit_ok(1);
When the close method is called, rollback is done if this value is effective. If it is not necessary to commit it, the method always does rollback. Therefore, this method need not usually be considered. It is good to make this method effective when the error etc. occur during processing. Rollback is done regardless of the state of commit_ok when this value is effective.
$es->rollback_ok(1);
The alias data of the table set by the start option etc. is returned.
The upgrade method can keep effective and be invalidated temporarily. If the upgrade method is called when this value returns false, the exception is generated.
$es->upgrade_ok(1);
The clear method can keep effective and be invalidated temporarily. If the clear method is called when this value returns false, the exception is generated.
$es->clear_ok(1);
If 'sql_abstract' is set by the start option, the object of SQL::Abstract is returned. If 'sql_abstract' is invalid, the exception is generated.
my $abs= $es->sql_abstract;
The result of SELECT is returned by the HASH reference. Therefore, SQL_STATEMENT is SELECT always sentence. Moreover, because it is data that is returned for one record, it is necessary to make it to SQL_STATEMENT of corresponding.
When the result is not obtained, anything returns undefined.
There is no EXECUTE_ARGS needing if Prasfolda is not included in SQL_STATEMENT.
if (my $hash= $es->hashref(q{SELECT * FROM members WHERE id = ?}, $id)) { ....... } else { die q{ Data is not found }; }
The result of SELECT is returned by the ARRAY reference. Therefore, SQL_STATEMENT is SELECT always sentence.
CODE_REF executed at the same time by the loop in the method can be passed. The list received by this is looped further and it comes do not to have to process it. HASH with the ARRAY reference for the return value of the method and the column of each record is passed to this CODE_REF.
$es->arrayref('SELECT ...', [ ... ], sub { my($array, %hash)= @_; ........ push @$array, \%hash; });
Undefined returns when nothing is put in $array.
Please pass EXECUTE_ARGS by the ARRAY reference when you pass CODE_REF. An empty ARRAY reference is passed if there is no something given to EXECUTE_ARGS.
Alias = list
The result of SELECT is returned by the SCALAR reference. Therefore, SQL_STATEMENT is SELECT sentence that specifies one column received without fail.
my $scalar_ref= $es->scalarref(q{SELECT user_name FROM ...... }, $id);
The result of the scalarref method is returned with usual scalar.
my $scalar= $es->scalarref(q{SELECT user_name FROM ...... }, $id);
SQL_STATEMENT that doesn't need the result is executed.
Please note that some hows to pass the argument are different from the do method of the data base handler.
$es->do(q{INSERT INTO members (id, user, age) VALUES (1, 'user_name', 20)});
If the transaction is effective, commit or rollback is done. And, it makes it to unavailability annulling the data base handler.
Please disconnect is not done.
$es->close; $dbh->disconnect;
I think it is good only to do disconnect if the transaction is invalid.
$dbh->disconnect;
This object doesn't have the method that can be especially used.
A peculiar table object is returned considering the character string passed as a method to be a table name.
$members= $es->db->members;
Constructor. The Egg::Mod::EasyDBI::db object is returned.
my $db= $es->db;
The method of this object becomes possible the shape succeeded to by the table class generated with Egg::Mod::EasyDBI::db use.
Constructor. The Egg::Mod::EasyDBI::table object is returned. When Egg::Mod::EasyDBI::db returns the table object, this constructor is called. It is not necessary to call it from the application.
my $table= $es->db->table_name;
The data for one acquired record is returned by the HASH reference.
Undefined returns when the record was not able to be acquired.
COLUMN is SCALAR reference for which the column that wants to be acquired is delimited by ','.
It is treated so that '*' is specified when COLUMN is omitted. Moreover, please make STATEMENT the first argument when omitting it.
STATEMENT is SQL sentence following WHERE. It ties by switching off the half angle space district when passing it by the ARRAY reference if it doesn't want to insert 'WHERE' and it is treated as STATEMENT.
There is no EXECUTE_ARGS needing if Prasfolda is not included in STATEMENT.
my $hash= $table->hashref(\'id, user, age', 'id = ?', $id); my $hash= $table->hashref('id = ?', $id); my $hash= $table->hashref(q{ user = '$user' }); my $hash= $table->hashref([qw/ ORDER BY age OFFSET 0 LIMIT 1 /]);
The acquired data is returned by the ARRAY reference.
The way to give the argument is almost the same as the hashref method.
The treatment of CODE_REF is the same as arrayref of the Egg::Mod::EasyDBI object.
my $list= $table->arrayref(\'id, user, age', 'age = ?', $age); my $list= $table->arrayref('age = ?', $age); my $list= $table->arrayref(qq{ age = $age }); my $list= $table->arrayref([qw/ ORDER BY age DESC /]); my $list= $table->arrayref(\'id, user', [qw/ ORDER BY age DESC /], [], sub { my($array, %hash)= @_; push @$array, "ID=$hash{id}, USER=$hash{user}"; }); my $text; $table->arrayref('age > ?', [$age], sub { my($array, %hash)= @_; $test .= <<END_DATA; --------------------------- ID : $hash{id} USER : $hash{user} AGE : $hash{age} END_DATA });
list
The acquired data is returned by the SCALAR reference.
It is necessary to specify one acquired column for COLUMN.
STATEMENT and EXECUTE_ARGS become hows to pass other similar methods.
my $scalar_ref= $table->scalarref(\'count(id)'); my $scalar_ref= $table->scalarref(\'id', 'user = ?', $user);
The result of the scalarref method is returned with usual SCALAR.
my $scala= $table->scalarref(\'count(id)'); my $scalar= $table->scalarref(\'id', 'user = ?', $user);
The record is added.
HASH cares about neither HASH reference nor usual HASH.
When failing in the addition, undefined is returned.
$table->insert( id=> 1, user=> 'hoge', age=> 20 ); $table->insert({ id=> 1, user=> 'hoge', age=> 20 });
Alias = in
It updates record.
STATEMENT is passed by the SCALAR reference in SQL sentence following WHERE.
The update data is passed to HASH by the HASH reference.
If Prasfolda is included in STATEMENT, the value of the key that HASH corresponds is specified by the ARRAY reference. The first element is passed and it is passed that the value for the retrieval and the value of the second element for the update. At this time, when the second element is omitted, it becomes data used only for the retrieval. If the same value as Prasfolda is referred, the first element is made ARRAY reference further. It sequentially extends to Prasfolda by this.
When STATEMENT is omitted, HASH is made the first argument and it passes it with usual HASH. At this time, the column corresponding to the first key is retrieved most.
The numerical value is passed to the value of HASH by the SCALAR reference at the update concerning the addition and subtraction of the numerical value.
# UPDATE $table SET user = 'zoo', age = age + 1 WHERE user = 'hoge' $table->update(\'user = ?', { user=> [qw/ hoge zoo /], age=> \1 }); or $table->update( user=> [qw/ hoge zoo /], age=> \1 ); # UPDATE $table SET age = age + 1 WHERE user = 'hoge' $table->update(\'user = ?', { user=> ['hoge'], age=> \1 }); or $table->update( user=> ['hoge'], age=> \1 ); # UPDATE $table SET age = age + -1 WHERE user = 'hoge' or user = 'zoo' $table->update(\'user = ? or user = ?', { user=> [[qw/ hoge zoo /]], age=> \"-1" });
Alias = up
The record is added if the update is tested and it fails.
When succeeding in the update, 0E0 is restored. Undefined returns when failing in the addition.
The way to give STATEMENT and HASH is the same as update. However, the data used to add and subtract the numerical value is excluded from an additional object. * A still clear specification is not decided about how to treat this numerical value.
# UPDATE $table SET user = 'zoo', age = age + 1 WHERE user = 'hoge' # INSERT INTO $table (user) VALUES ('zoo') $table->update_insert(\'user = ?', { user=> [qw/ hoge zoo /], age=> \1 }); or $table->update_insert( user=> [qw/ hoge zoo /], age=> \1 ); # UPDATE $table SET user = 'zoo', age = 20 WHERE id = 1 # INSERT INTO $table (user, age) VALUES ('zoo', 20) $table->update_insert(\'id => ?', { id=> [1], user=> 'zoo', age=> 20 }); or $table->update_insert( id=> [1], user=> 'zoo', age=> 20 );
If the record doesn't exist, the record is added.
If the record already exists, 0E0 is restored. Undefined returns when failing in the addition.
COLUMN specifies only one column used for the retrieval. When the value of this column cannot be received from HASH, it becomes an error.
When COLUMN is omitted, HASH is made the first argument, and it passes it as usual HASH. The first key to HASH is handled by this as a column for the retrieval.
Please omit the second element specifying the value for the retrieval of HASH by the ARRAY reference when there is a column that doesn't want to be included when adding it.
# SELECT user FROM $table WHERE user = 'banban' # INSERT INTO $table (user, age) VALUES ('baban', 20) $table->find_insert(\'user', { user=> 'banban', age=> 20 }); or $table->find_insert( user=> 'banban', age=> 20 ); # SELECT id FROM $table WHERE id = 1 # INSERT INTO $table (user, age) VALUES ('banban', 20) $table->find_insert(\'id', { id=> [1], user=> 'banban', age=> 20 }); or $table->find_insert( id=> [1], user=> 'banban', age=> 20 );
The record by FOR UPDATE is locked.
0 returns when failing.
# SELECT * FROM $table WHERE id = ? FOR UPDATE $table->for_update( id=> 1 );
The record is deleted.
# DELETE FROM $table WHERE id = ? $table->delete('id = ?', $id);
UPDATE is done to all the records.
If upgrade_ok is not effective, this method returns the exception.
# UPDATE $table SET age = 20 $table->upgrade( age => 20 );
All the records are deleted.
If clear_ok is not effective, this method returns the exception.
# DELETE FROM $table $table->clear;
* I think that it is more efficient to do TRUNCATE by the do method.
After SQL Statement is received from SQL::Abstract, hashref is done.
Please set 'sql_abstract' to enable this method use.
All the arguments extend to SQL::Abstract. There is no table name needing.
Please see at the document of SQL::Abstract of how to give the argument in detail.
# SELECT * FROM $table WHERE id = 1 AND user = 'hooo' my $hash= $table->abs_hashref({ id=> 1, user=> 'hooo' });
After SQL Statement is received from SQL::Abstract, arrayref is done.
As for the argument, it is the same as abs_hashref accepting CODE_REF.
The treatment of CODE_REF is the same as arrayref. Because it is picked up as CODE_REF if the last element of the argument is CODE reference, the argument without the necessity need not be buried with undef etc.
# SELECT * FROM $table WHERE age > 18 my $list= $table->abs_arrayref({ age => { '>', 18 } }); # SELECT * FROM $table WHERE age > 18 my $list= $table->abs_arrayref({ age => { '>', 18 } }, sub { my($array, %hash)= @_; push @$array, "ID=$hash{id}, AGE=$hash{age}"; });
Alias = abs_list
After SQL Statement is received from SQL::Abstract, scalarref is done.
To acquire it only by one column, the argument gives all FIELDS without fail though it is the same as hashref.
# SELECT user FROM $table WHERE id = 1; my $scalar_ref= $table->abs_scalarref(['user'], { id=> 1 });
The result of abs_scalarref is returned with SCALAR receiving usual.
# SELECT user FROM $table WHERE id = 1; my $scalar= $table->abs_scalar(['user'], { id=> 1 });
After SQL Statement is received from SQL::Abstract, insert is done.
The argument extends to the insert method of SQL::Abstract.
# INSERT INTO $table (id, user, age) VALUES (2, 'banban', 20) $table->abs_insert({ id=> 2, user=> 'banban', age=> 20 });
After SQL Statement is received from SQL::Abstract, update is done.
The argument extends to the update method of SQL::Abstract.
# UPDATE $table SET age = 22 WHERE user = 'banban' $table->abs_update({ age=> 22 }, { user=> 'banban' });
After SQL Statement is received from SQL::Abstract, delete is done.
# DELETE FROM $table WHERE user = 'banban' $table->delete({ user=> 'banban' });
This class supports the table uniting.
When the argument is given to the db method of Egg::Mod::EasyDBI, the constructor here is called.
ARRAY is a list of the table name to unite tables. It cares about neither usual ARRAY nor the ARRA reference.
The first element of this ARRAY is a table name of the main. And, HASH is developed since the second element in this method. The key to this HASH is handled by the following signs under the uniting condition. * Do not pass the second element by the HASH reference.
= or join ..... JOIN < or left ..... LEFT OUTER JOIN > or right ..... RIGHT OUTER JOIN @ or full ..... FULL OUTER JOIN _ or inner ..... INNER JOIN
The value of the key specifies the uniting table names, and and, the relation is delimited by ':' and specified the condition.
The affixing character of the table name is sequentially allocated from the table name of the main by a, b, c.., and the automatic operation.
Alias to which the table name is set by the start option is effective.
# table1 a JOIN table2 b ON a.id = b.id LEFT OUTER JOIN table3 c ON b.id = c.id my $jdb= $es->db(qw/ table1 = table2:a.id=bid < table3:b.id=c.id /); or my $jdb= $es->db('table1', join=> 'table2 : a.id = bid', left=> 'table3 : b.id = c.id', ); # table1 a RIGHT OUTER JOIN table2 b ON a.id = b.id my $jdb= $es->db(qw/ table1 > table2:a.id=b.id /); or my $jdb= $es->db('table1' right=> 'table2 : a.id = b.id');
It is functionally the same as hashref of 'TABLE OBJECT METHODS'.
It is necessary to put the affixing character of the table name in COLUMN and STATEMENT.
# SELECT a.id, a.user FROM members a JOIN profiles b ON a.id = b.id WHERE b.email = ? my $hash= $es->db(qw/ members = profiles:a.id=b.id /) ->hashref(\'a.id, a.user', 'b.email = ?', 'hoge@mydomain');
It is functionally the same as arrayref of 'TABLE OBJECT METHODS'.
# SELECT a.id, a.user FROM members a JOIN profiles b ON a.id = b.id WHERE b.addr = ? my $hash= $es->db(qw/ members = profiles:a.id=b.id /) ->arrayref(\'a.id, a.user', 'b.addr = ?', 'Nagoya', sub { my($array, %hash)= @_; push @$array, "ID=$hash{id} USER=$hash{user}"; });
It is functionally the same as scalarref of 'TABLE OBJECT METHODS'.
# SELECT count(a.id) FROM members a JOIN profiles b ON a.id = b.id WHERE b.addr = ? my $scalarref= $es->db(qw/ members = profiles:a.id=b.id /) ->scalarref(\'count(a.id)', 'b.addr = ?', 'Nagoya');
The result of scalarref is returned with usual SCALAR.
# SELECT count(a.id) FROM members a JOIN profiles b ON a.id = b.id WHERE b.addr = ? my $scalar= $es->db(qw/ members = profiles:a.id=b.id /) ->scalar(\'count(a.id)', 'b.addr = ?', 'Nagoya');
DBI, SQL::Abstract, Egg::Plugin::EasyDBI, Class::Accessor::Fast, Class::Data::Inheritable, Tie::Hash::Indexed,
Masatoshi Mizuno <lushe@cpan.org>
Copyright (C) 2008 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.
To install Egg::Release::DBI, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Egg::Release::DBI
CPAN shell
perl -MCPAN -e shell install Egg::Release::DBI
For more information on module installation, please visit the detailed CPAN module installation guide.