EasyDBAccess - Perl Database Access Interface
use EasyDBAccess; if(defined(&EasyDBAccess::foo)){ print "lib is included"; }else{ print "lib is not included"; } my $dba=EasyDBAccess->new({host=>'127.0.0.1',usr=>'root',pass=>'passwd',database=>'test_db'}); my $dba=EasyDBAccess->new({socket=>'/tmp/mysql.sock',usr=>'root',pass=>'passwd',database=>'test_db'}); my $dba=EasyDBAccess->new({host=>'127.0.0.1',usr=>'root',pass=>'passwd',database=>'test_db',encoding=>'gbk'}); #disable die in next operation EasyDBAccess->once(); my ($dba,$err_code)=EasyDBAccess->new({host=>'127.0.0.1',usr=>'root',pass=>'passwd',database=>'test_db'}); if($err_code==3){ print "Connect Error"; }elsif($err_code==0){ print "Connect Succ"; }else{ CORE::die 'BUG'; } my $dba=EasyDBAccess->new({host=>'127.0.0.1',usr=>'root',pass=>'passwd',database=>'test_db'},{err_file=>'\var\log\logfile'}); my $die_handler=EasyHandler->new(\&die_to_file,['\var\log\logfile']); my $dba=EasyDBAccess->new({host=>'127.0.0.1',usr=>'root',pass=>'passwd',database=>'test_db'},{die_handler=>$die_handler}); my $dbh=$dba->dbh(); EasyDBAccess->once(); $dba->once(); $re=$dba->select_one('select id from person limit 0,1'); #$re=1 ($re,$err_code)=$dba->select_one('select id from person limit 0,1'); #$re=1,$err_code=0 ($re,$err_code)=$dba->select_one('select id from person2 limit 0,1'); #table person2 doesn't exist, will die $dba->once(); ($re,$err_code,$err_detail)=$dba->select_one('select id from person2 limit %start_pos,%count',{start_pos=>0,count=>1}); #won't die, because we have do "$dba->once()" before it if($err_code==0){ print "no error, id is $re"; }elsif($err_code==5){ #execute error if($dba->err_code()==1146){ print 'table not exist'; }else{ #other error CORE::die $err_detail; } } ($re,$err_code)=$dba->execute('insert into person values (?,?)',[3,'Bob']); #1, affected_rows ($re,$err_code)=$dba->select('select * from person'); #[{id=>1,name=>'tom'},{id=>2,name=>'gates'}] ($re,$err_code)=$dba->select_array('select * from person'); #[[1,'tom'],[2,'gates']] ($re,$err_code)=$dba->select_row('select * from person'); #{id=>1,name=>'tom'} ($re,$err_code)=$dba->select_col('select id from person'); #[1,2] ($re,$err_code)=$dba->select_one('select id from person'); #1, first line first column ($re,$err_code)=$dba->select_one('select name from person where id=3'); #select_row, select_one can cause NO_LINE error if($err_code==0){ print "no error, name is $re"; }elsif($err_code==1){ #no line print "there is 0 row in result set"; }else{ print "other error"; } $id=$dba->id('key1');#1 $id=$dba->id('key2');#1 $id=$dba->id('key1');#2 $id=$dba->id('key1');#3 $sid=$dba->sid(); #446d40ffd9890184 $sid_info=$dba->sid_info('446d40ffd9890184'); #{"sid" => 3649634692, "comment" => undef, "record_time" => 1148010751} #will insert a record to note table $sid->note('hello world');
The synopsis above only lists the major methods and parameters.
use this table as sample table in document test_db.person +----+-------+ | id | name | +----+-------+ | 1 | tom | | 2 | gates | +----+-------+
you can edit begin part of code of this file(EasyDBAccess.pm) to set some globe option you can also overload in runtime
e.g EasyDBAccess::$_DEBUG=0;
default is true
if you set $_DEBUG=false then no "DIE" (not recommend)
do "SET NAMES" when dbi connected
if you set $_SETNAMES=false then not do "SET NAMES" when dbi connected
when "DIE", this module will throw out param infomation, this is dangerous when connect DB fail, it may throw out user name as password
default is false
if you set $_HIDE_CONN_PARAM=false, then don't throw connection param infomarion
if you ser $_HIDE_CONN_PARAM=true, then throw connection param infomarion if connect to DB fail (strongly not recommend)
return value can be in scalar mode and array mode
#scalar mode $re=$dba->select_one('select 1'); #$re=1 #array mode ($re,$err_code,$err_detail,$_pkg_name)=$dba->select_one('select 1'); #$re=1,$err_code=0,$err_detail=undef,$_pkg_name=EasyDBAccess #if you don't need all result ($re,$err_code)=$dba->select_one('select 1');
Extra Rule: if $err_code!=0 then $re=undef
nearly all function use this design of return value
when you use this module, you will cause some error, for example, db connect failed and some other error
so in summary, there are 5 kind of runtime error (we don't discuss error cause by mistake usage of module)
some error will will triger "DIE"(we name it) to happen, and some will not
"DIE" is a action will do when error happen, the default behavior of "DIE" is CORE::die $err_detail
and you can overload this behavior by set "die_handler" in "new" function
$err_code=0 when there is no error
when you assume there will be at least one line in result, for example, when you call $dba->serlect_row or $dba->select_one, but there is no record in record set, then will cause NO_LINE error
u have some error in param value, for example sql string is null
connect to db fail
prepare sql error, in fact, this error is impossible
execute sql error
we use var "$err_code" to store ERR_CODE
when error (2,3,5) happens , by default, module will triger "DIE", you need to use "once" function to temporary disable it if you want to handler it
other error (1) won't triger "DIE" ( refer to "error handling & "DIE" & "once"" )
you can overload "DIE" behavior by set "die_handler" in "new" function,
you can assign an instance of EasyHandler(recommend, but you need use EasyHandler Module),
or an instance of _EasyDBAccess_EasyHandler(no need use extra module) to key "die_handler" in construct param
refer to "error handling & "DIE" & "once""
$dba->select($sql_str,$bind_param,$inline_param); e.g $dba->select('select * from person where id=?,name=? limit %start_pos,%count',[10,'qian'],{start_pos=>10,count=>20});
sql string may contain some symbol like '?' and '%'
? will function with $bind_param % will do string replace on string start with "%"
an array_ref
internal implement:
$sth->execute(@$bind_param)
an hash_ref
internal implemnt:
while(my($k,$v)=each %{$inline_param}){ if(!defined($v)){return 0;} $_[0]=~s/\Q%$k\E/$v/g; }
if(defined(&EasyDBAccess::foo)){ print "lib is included"; }else{ print "lib is not included"; }
$dba/($dba,$err_code)=EasyDBAccess->new($conn_param,$ext_option); $dba/($dba,$err_code)=EasyDBAccess->new($param);
$param = $conn_param + $ext_option
$param= merge of $conn_param & $ext_option, sometimes you need to put param in separate hash_ref,so do this design
$param is a hash_ref has below option
type: only support 'mysql' yet, default 'mysql' host: mysql server address, default'127.0.0.1' port: mysql server service port, default is 3306 socket: use socket to connect mysql, set socket path to this usr : mysql user name, default 'root' pass: mysql auth password, default '' encoding: mysql charset (when mysql ver>=4.1), default 'UTF8', please check ther reference of 'SET NAMES' version: mysql database version, default auto detect, if set, then please at least specify one digit after '.', e.g. '3.23','4.1' database: default database, if not set, then no default database
the below option is to set what should do when error occur(default is CORE::die)
die_handler: set a EasyHandler to this to handler error, will do this handler when err_file: an internal die_handler, set file name to this, then will log into file when die
e.g
#normal use my $dba=EasyDBAccess->new({host=>'127.0.0.1',usr=>'root',pass=>'passwd',database=>'test_db'}); #use socket my $dba=EasyDBAccess->new({socket=>'/tmp/mysql.sock',usr=>'root',pass=>'passwd',database=>'test_db'}); #use other encoding than utf8 my $dba=EasyDBAccess->new({host=>'127.0.0.1',usr=>'root',pass=>'passwd',database=>'test_db',encoding=>'gbk'}); #do something when connect fail EasyDBAccess->once(); #disable die in next operation my ($dba,$err_code)=EasyDBAccess->new({host=>'127.0.0.1',usr=>'root',pass=>'passwd',database=>'test_db'}); if($err_code==3){ print "Connect Error"; }elsif($err_code==0){ print "Connect Succ"; }else{ CORE::die 'BUG'; } #write err log to file my $dba=EasyDBAccess->new({host=>'127.0.0.1',usr=>'root',pass=>'passwd',database=>'test_db'},{err_file=>'\var\log\logfile'}); #costomer die my $die_handler=EasyHandler->new(\&die_to_file,['\var\log\logfile']); my $dba=EasyDBAccess->new({host=>'127.0.0.1',usr=>'root',pass=>'passwd',database=>'test_db'},{die_handler=>$die_handler});
disconnect db $dba->close();
get the dbh my $dbh=$dba->dbh();
return database type,always 'mysql' up to now print $dba->type();
EasyDBAccess->once(); $dba->once();
EasyDBAccess->once(); #disable die in next operation my ($dba,$err_code)=EasyDBAccess->new({host=>'127.0.0.1',usr=>'root',pass=>'passwd',database=>'test_db'}); if($err_code==3){ print "Connect Error"; }elsif($err_code==0){ print "Connect Succ"; }else{ CORE::die 'BUG'; }
$dba->err_code();#1146,if no error, return undef $dba->err_str(); $dba->once(); ($rc,$err_code,$err_detail)=$dba->execute('insert into hello values(1,2,3)'); if($err_code==5){#execute error if($dba->err_code()==1146){ print 'table not exist'; print $dba->err_str(); }else{ CORE::die $dba->err_str(); } }
$rc/($rc,$err_code,$err_detail,$_pkg_name)=$dba->execute($sql_str,$bind_param,$inline_param); return result of $dbh->do if succ, in most case ,this will be "affected rows" if execute error, $rc return undef
$rc/($rc,$err_code,$err_detail,$_pkg_name)=$dba->select($sql_str,$bind_param,$inline_param); return result as array_ref of hash_ref ([{id=>1,name=>'hello'},...])
$rc/($rc,$err_code,$err_detail,$_pkg_name)=$dba->select_select_array($sql_str,$bind_param,$inline_param); return result as array_ref of array_ref ([[1,'hello'],...])
$rc/($rc,$err_code,$err_detail,$_pkg_name)=$dba->select_row($sql_str,$bind_param,$inline_param); return first row of result set as hash ({id=>1,name=>'hello'}) if no row in result set, then $rc=undef, $err_code=1 but won't cause a die
$rc/($rc,$err_code,$err_detail,$_pkg_name)=$dba->select_row($sql_str,$bind_param,$inline_param); return first column of result set as array_ref( [1,2,3,...] )
$rc/($rc,$err_code,$err_detail,$_pkg_name)=$dba->select_one($sql_str,$bind_param,$inline_param); return first row first column of result set scalar( 1 ) if no row in result set, then $rc=undef, $err_code=1 but won't cause a die
insert many record into table at once $rc/($rc,$err_code,$err_detail,$_pkg_name)=$dba->batch_insert($sql_str,$values_tmpl,$values,$max_count); $max_count: max record insert per time, the default value for $max_count is 1 return result of $dbh->do if succ, in most case ,this will be "affected rows" if execute error, $rc return undef
$dba->batch_insert('insert into person values %V','(?,?)',[[1,'tom'],[2,'gates'],[3,'bush']],100);
build data array, always for insert ($ra,$err_code)/$ra = EasyDBAccess::build_array($filter,$rh,$ra); when you want to find a value from hash, but there is no such key in hash, then will use value undef instead, and will set $err_code=1
my $param={name=>'tom',age=>23,other_key=>'hello'}; my $filter=[qw/? name age/]; my $id=$dba->id('person'); my $record=&EasyDBAccess::build_array($filter,$param,[$id]); $dba->execute('insert into person values (?,?,?)',$record);
build data for update, update some items on record ($str2,$ra_bind_param,$count,$str)/$str=EasyDBAccess::build_update($filter,$hash);
my $param={name=>'jack',other_key=>'hello'}; my $filter=[qw/name age/]; my ($str2,$ra_bind_param,$count,$str)=EasyDBAccess::build_update($filter,$param); # $str2='name=?',$ra_bind_param=['jack'],$count=1,$str='name=?,' if($count>0){ $dba->execute("update person set $str2 where id=?",[@$ra_bind_param,3]); } my $param={name=>'jack', age=>23, other_key=>'hello'}; my $filter=[qw/name age/]; my ($str2,$ra_bind_param,$count,$str)=EasyDBAccess::build_update($filter,$param); # $str2='name=?,age=?',$ra_bind_param=['jack',23],$count=1,$str='name=?,age=?,' if($count>0){ $dba->execute("update person set $strgender=1 where id=?",[@$ra_bind_param,3]); }
execute command from binding data, always for insert $rc/($rc,$err_code,$err_detail,$_pkg_name)==EasyDBAccess::insert_one_row($sql, $filter, $rh_data, $ra);
my $sql='insert into person values (?,?)'; my $filter=[qw/name ?/]; my $param={name=>'tom',age=>23,other_key=>'hello'}; my $ra=[23] my $record=&EasyDBAccess::insert_one_row($sql,$filter,$record,$ra);
execute command from binding data, always for update $rc/($rc,$err_code,$err_detail,$_pkg_name)==EasyDBAccess::update($sql, $filter, $rh_data, $ra_param);
my $sql='update person set %ITEM, SCORE=\'A\' where name=?'; #my $sql='update person set %COMMAITEM SCORE=\'A\' where name=?'; #COMMAITEM is ITEM with a comma my $filter=[qw/age/]; my $rh_data={name=>'tom',age=>24}; my $ra_param=['tom']; my $record=&EasyDBAccess::update($sql, $filter, $rh_data, $ra_param);
to use this function, you must create table RES CREATE TABLE RES(ATTRIB VARCHAR(255) NOT NULL,ID INT NOT NULL ,PRIMARY KEY (ATTRIB)) $id=$dba->id('key1');#1 $id=$dba->id('key2');#1 $id=$dba->id('key1');#2 $id=$dba->id('key1');#3
to use this function, you must create table SID CREATE TABLE SID(RECORD_TIME INT UNSIGNED NOT NULL, SID INT UNSIGNED NOT NULL,COMMENT VARCHAR(255) DEFAULT NULL, PRIMARY KEY(RECORD_TIME,SID)) $sid=$dba->sid(); #446d40ffd9890184 $sid_info=$dba->sid_info('446d40ffd9890184'); #{"sid" => 3649634692, "comment" => undef, "record_time" => 1148010751} internal of sid: sid_string=hex(2^16*record_time+ ramdon_number) sid_string: return value of $dba->sid() record_time: record_time in $sid_info ramdon_number: sid in $sid_info
to use this function, you must create table NOTE CREATE TABLE NOTE(TEXT TEXT NOT NULL, RECORD_TIME INT UNSIGNED NOT NULL) $dba->note('something to note'); you can read this note via database
this will make an CONN_ERR, it will triger "DIE"
EasyDBAccess->new({host=>'127.0.0.1',usr=>'root',pass=>'wrong passwd',database=>'test_db'});
if you don't want to triger "DIE", you can use "once" to temporary disable it
EasyDBAccess->once(); #disable "DIE" in next operation my ($dba,$err_code)=EasyDBAccess->new({host=>'127.0.0.1',usr=>'root',pass=>'passwd',database=>'test_db'});
another example to show usage of "once"
$dba->once(); $dba->select(undef);
if you want handler this error
EasyDBAccess->once(); #disable "DIE" in next operation my ($dba,$err_code)=EasyDBAccess->new({host=>'127.0.0.1',usr=>'root',pass=>'passwd',database=>'test_db'}); if($err_code==3){ print "Connect Error"; }elsif($err_code==0){ print "Connect Succ"; }else{ CORE::die 'BUG'; }
this will make an PARAM_ERR, it will triger "DIE"
$dba->select(undef);
this will make an EXEC_ERR, it will triger "DIE"
$dba->execute('wrong sql'); $dba->execute('insert into hello values(1,2,3)'); #table hello doesn't exist $dba->execute('insert into person values (1,'Buffett');#ER_DUP_ENTRY
this will make an NO_LINE, not all error will triger "DIE", NO_LINE won't triger "DIE"
($re,$err_code)=$dba->select_one('select name from person where id=3'); #select_row, select_one can cause NO_LINE error if($err_code==0){ print "no error, name is $re"; }elsif($err_code==1){#no line print "there is 0 row in result set"; }else{ print "other error"; }
if you want handler error by database error code
$dba->once(); ($rc,$err_code,$err_detail)=$dba->execute('insert into hello values(1,2,3)'); if($err_code==5){#execute error if($dba->err_code()==1146){ print 'table not exist'; }else{ CORE::die $err_detail;#other error } }
customize die_handler
sub die_to_file{ my $file_path= shift; my ($err_pkg,$err_code,$err_detail,$record_time)=(undef,undef,undef,CORE::time); my $param_count=scalar(@_); if($param_count==1){ ($err_detail)=@_; }elsif($param_count==3){ ($err_code,$err_detail,$err_pkg)=@_; }elsif($param_count==4){ ($err_code,$err_detail,$err_pkg,$record_time)=@_; }else{ CORE::die($_pkg_name.'::die_to_file: param error'); } $_=[localtime($record_time)]; my $prefix="#####".sprintf('%04s-%02s-%02s %02s:%02s:%02s',$_->[5]+1900,$_->[4]+1,$_->[3],$_->[2],$_->[1],$_->[0])."\n"; my $result=append_file($file_path,$prefix.$err_detail."\n"); if($result){ #log succ CORE::die $err_detail; }else{ CORE::die($_pkg_name.'::die_to_file: append to file failed'); } } my $die_handler=EasyHandler->new(\&die_to_file,['\var\log\logfile']); #my $die_handler=_EasyDBAccess_EasyHandler->new(\&die_to_file,['\var\log\logfile']);#this one is OK, too my $dba=EasyDBAccess->new({host=>'127.0.0.1',usr=>'root',pass=>'passwd',database=>'test_db'},{die_handler=>$die_handler});
system integrated an alternative die_handler beside "CORE::die", this die_handler called "die_to_file", it write $err_detail to log_file before "CORE::die"
sample code show how to use it
my $dba=EasyDBAccess->new({host=>'127.0.0.1',usr=>'root',pass=>'passwd',database=>'test_db'},{err_file=>'\var\log\logfile'});
The EasyDBAccess module is Copyright (c) 2003-2005 QIAN YU. All rights reserved.
You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.
To install EasyDBAccess, copy and paste the appropriate command in to your terminal.
cpanm
cpanm EasyDBAccess
CPAN shell
perl -MCPAN -e shell install EasyDBAccess
For more information on module installation, please visit the detailed CPAN module installation guide.