view release on metacpan or  search on metacpan

Puzzle/DBTable.pm  view on Meta::CPAN

     
my @fields_req	= qw/dbh name/;
my $DEBUG_FH;     

sub new
{   
	my $proto = shift;
    my $class = ref($proto) || $proto;
    my $self = {};
    bless $self,$class;
    $self->_init(@_);
    return $self;
}							

sub _init {
	my $self = shift;
	my (%options) = @_;
	# Assign default options
	while (my ($key,$value) = each(%fields)) {
		$self->{$key} = $self->{$key} || $value;
    }
    # Assign options
    while (my ($key,$value) = each(%options)) {
    	$self->{$key} = $value
    }
    # Check required params
    foreach (@fields_req) {
		croak "You must declare '$_' in " . ref($self) . "::new"
				if (!defined $self->{$_});
	}
	$DEBUG_FH = new FileHandle ">>$DEBUG_FILE_PATH" if ($DEBUG);										
}

sub DESTROY {
	$DEBUG_FH->close if ($DEBUG);
}

sub add {
	my $self 	= shift;
	my $values 	= shift;
	my %values	= %{$values};
	if (!exists $values{date}) {
		# built default date
		my @lt = localtime;
		my $lt = $lt[5]+1900; $lt .= &_f2($lt[4]+1); $lt.=&_f2($lt[3]);
		$lt .= &_f2($lt[2]); $lt .= &_f2($lt[1]); $lt.=&_f2($lt[0]);
		$values{date} = $lt;
	}
	# costruisco la stringa chiave1,chiave2,...
	my $keys	= join(',',keys(%values));
	# array dei valori
	my @values  = values(%values);
	# costruisco la stringa ?,?,?...
	my $jolly	= join(',',map('?',@values));
	my $sql 	= qq/Insert into $self->{name} ($keys) values ($jolly)/;
	debug($sql);
	$self->{dbh}->do($sql,undef,@values) or 
		die "Unable to execute $sql with params " . join(';',@values);
}

sub update {
	my $self 	= shift;
	my $fields	= shift;
	my $filter	= shift;
	# create sql string
	my $sql 	= qq/Update $self->{name} set /;
	# built section field1 = ? for update
	my $set = join(',',map("$_ = ? ",keys(%{$fields})));
	# built where section
	my $where = join(',',map("$_ = ? ",keys(%{$filter})));
	# complete sql is...
	$sql .= qq/$set where $where/;
	# get values
	my @values = (values(%{$fields}),values(%{$filter}));
	$self->{dbh}->do($sql,undef,@values) or 
		die "Unable to execute $sql with params: " . join(@values);
}

sub delete {
	my $self	= shift;
	my $id		= shift;
	$id = [$id] unless (ref($id)) ;
	my $in = join(', ',map('?',@{$id}));
	my $sql		= qq/delete from $self->{name} where id in ($in)/;
	$self->{dbh}->do($sql,undef,@{$id}) or 
		die "Unable to execute $sql with id $id ";
}

sub array_items {
	my $self 					= shift;
	my (undef,undef,$filter) 	= @_;
	my $sql						= $self->_item_sql(@_);
	return $self->_rec_as_array($sql,values(%{$filter}));
}

sub hash_items {
	my $self 					= shift;
	my (undef,undef,$filter) 	= @_;
	my $sql						= $self->_item_sql(@_);
	return $self->_rec_as_hash($sql,values(%{$filter}));
}


sub create {
	my $self	= shift;
	my $dbh; my $name;
	if (defined $self) {
		$dbh	= $self->{dbh};
		$name	= $self->{name};
	} else {
		my @dbInfo	= &_prompt_db_info;
		$dbh		= &_local_dbh(@dbInfo);
		$name		= $dbInfo[-1];
	}
	my $sql		=
		qq/
			CREATE TABLE $name (
	  		  id smallint(1) unsigned NOT NULL auto_increment,
			  title varchar(255) NOT NULL default '',
			  txt_short varchar(255) default NULL,
			  txt_long text,