+- MyTFLS社区 (https://community.mytfls.com)
+-- 论坛： 电脑区 (https://community.mytfls.com/forum-19.html)
+--- 论坛： 系统+网络+硬件 (https://community.mytfls.com/forum-20.html)
+--- 主题： 一个方便使用DBI (/thread-8920.html)
一个方便使用DBI - Metmet - 2005-8-18
#!/usr/bin/perl -w
# Last modify Time-stamp: <2005-08-16 22:18:10 user>
#===========================================================
# File: MyDBI.pm
# Version: v 0.0 2005/08/16 14:17:48
# Author: Ye Wenbin <wenbinye@163.com>
#
# Description: A simple improved DBI
#===========================================================
use strict;
use warnings;
package MyDBI;
use DBI;
sub new
{
my $class = shift;
my $self = {};
if (@_)
{
my @cp = @_;
$self->{dbh} = DBI->connect(@cp)
|| die "Cannot connect to db: @cp! $!";
}
bless $self, $class;
return $self;
}
sub set_db
{
my $self = shift;
my @cp = @_;
$self->new(@_);
}
sub set_sql
{
my $self = shift;
my ($sql, $name) = @_;
if (defined $name)
{
if (exists $self->{$name})
{
die "The name has exists. Set another name!";
}
}
else {
$name = 'TEMP';
}
$self->{$name} = $self->{dbh}->prepare($sql);
return $self->{$name};
}
#=============================================================
# Function: search
# Parameters: Query, Name of sql, 'TEMP' if not given
# Returns: undef if None, all array ref
#
# Description: search in the db
#============================================================
sub search
{
my $self = shift;
my $query = shift;
my $name = shift;
unless (defined $name)
{
$name = 'TEMP';
}
unless ( exists $self->{$name} )
{
die "Set the sql before search.";
}
my $sth = $self->{$name};
$sth->execute(@$query);
my $rec = $sth->fetchall_arrayref;
if (@$rec)
{
return $rec;
}
else {
return undef;
}
}
#=============================================================
# Function: find_or_create
# Parameters: find sth, insert sth, primary key fields value,
# record
# Returns: undef if parameter wrong, return the auto_increment
# id
#
# Description: use for a table has auto_increment id. make sure
# the primary_key_value is uniq in the db
#============================================================
sub find_or_create
{
my $self = shift;
my ($fname, $iname, $primary_key_value, $record) = @_;
my ($fsth, $isth);
unless ( exists $self->{$fname} )
{
die "The sql $fname is not exists!";
}
$fsth = $self->{$fname};
unless ( exists $self->{$iname} )
{
die "The sql $iname is not exists!";
}
$isth = $self->{$iname};
# foreach ([$fname, $fsth], [$iname, $isth])
# {
# unless ( exists $self->{$_->[0]} )
# {
# die "The sql $_->[0] is not exists!";
# }
# $_->[1] = $self->{$_->[0]};
# }
unless (defined $primary_key_value->[0])
{
return undef;
}
unless ( _is_subset($primary_key_value, $record))
{
return undef;
}
$fsth->execute(@$primary_key_value);
my $r = $fsth->fetchall_arrayref;
if (@$r)
{
return $r->[0][0];
}
else {
$isth->execute(@$record) || die "Insert failed: $!";
return $self->{dbh}->last_insert_id(undef, undef, undef, undef);
}
}
#=============================================================
# Function: _is_subset
# Parameters: small array ref, big array ref
# Returns: 0 if small array is not a subset of big array ref,
# otherwise return 1
#
# Description: Judge an array is or not a subset of another
# array
#============================================================
sub _is_subset
{
my ($small, $big) = @_;
my %count;
foreach (@$big)
{
$count{$_}++;
}
foreach ( @$small )
{
unless ( exists( $count{$_} ) )
{
return 0;
}
}
return 1;
}
1;