view release on metacpan or  search on metacpan

lib/EAI/DB.pm  view on Meta::CPAN

						} elsif (($DBI::errstr =~ /cannot insert duplicate key/i and !$ignoreDuplicateErrs) or $DBI::errstr !~ /cannot insert duplicate key/i) {
							my $errRec;
							for (my $j=0; $j < scalar(@{columns}); $j++) {
								my $datatype = $coldefs->{$columns[$j]}{"TYPE_NAME"}."(".$coldefs->{$columns[$j]}{"COLUMN_SIZE"}.")";
								$errRec.= $columns[$j]."[".$datatype."]:".$dataArray[$j].", ";
							}
							confess $DBI::errstr." when updating data: $errRec, executed statement: $dostring";
						}
					};
				}
				print "EAI::DB::storeInDB stored ".($i+1)." of $lines\r" if $countPercent and (($i+1) % (int($lines * ($countPercent / 100)) == 0 ? 1 : int($lines * ($countPercent / 100))) == 0);
			}
		} else {
			confess "no data to store";
		}
	};
	if ($@) {
		$logger->error($@);
		return 0;
	} else {
		$logger->trace("returned data:".Dumper($data)) if $logger->is_trace;
		return 1;
	}
}

# delete data identified by key-data in database
sub deleteFromDB ($$) {
	my ($DB,$data) = @_;
	my $logger = get_logger();
	my $tableName = $DB->{tablename};
	my @keycolumns = @{$DB->{keyfields}} if $DB->{keyfields};
	eval {
		confess "no tablename given" if !$tableName;
		confess "no keyfields given" if !@keycolumns;
		confess "no valid dbh connection available" if !defined($dbh);
		if ((@{$data}) > 0) {
			# prepare statement
			my $whereclause; map {$whereclause = "[$_] = ? AND "} @keycolumns;
			substr($whereclause, -5)  = ""; # delete last " AND "
			my $sth = $dbh->prepare("DELETE FROM ".$tableName." WHERE $whereclause");
			# execute with data
			for my $primkey (@{$data}) {
				$logger->trace("deleting data for $primkey") if $logger->is_trace;
				$sth->execute(($primkey)) or confess "couldn't delete data for $primkey:".$DBI::errstr;
			}
		} else {
			$logger->error("no data to delete".longmess());
			return 0;
		}
	};
	if ($@) {
		$logger->error($@);
		return 0;
	} else {
		$logger->trace("returned data:".Dumper($data)) if $logger->is_trace;
		return 1;
	}
}

# update data in database
sub updateInDB ($$) {
	my ($DB,$data) = @_;
	my $logger = get_logger();
	my $tableName = $DB->{tablename};
	my @keycolumns = @{$DB->{keyfields}} if $DB->{keyfields};
	eval {
		confess "no tablename given" if !$tableName;
		confess "no keyfields given" if !@keycolumns;
		confess "no valid dbh connection available" if !defined($dbh);
		my $firstrecordID = (keys %{$data})[0];
		confess "no valid data passed (couldn't find keys in data hashes)" if !$firstrecordID;
		$logger->trace("passed data:\n".Dumper($data)) if $logger->is_trace;
		my @columns = sort keys %{$data->{$firstrecordID}}; # sort to ensure deterministic ordering of fieldnames !
		my $schemaName = $DB->{schemaName};
		if ($tableName =~ /\./) {
			$logger->debug("getting schema from $tableName (contains dot)");
			($schemaName, $tableName) = ($tableName =~ /(.*)\.(.*)/);
		}
		confess "no schemaName available (neither from tablename containing schema nor from parameter schemaName" if !$schemaName;
		my $colh = $dbh->column_info ('', $schemaName, $tableName, "%");
		my $coldefs = $colh->fetchall_hashref ("COLUMN_NAME");
		
		if ((keys %{$data}) > 0) {
			# prepare statement
			my %keycolumns; map {$keycolumns{$_}=1} @keycolumns;
			my $whereclause; map {$whereclause = "[$_] = ? AND "} @keycolumns;
			substr($whereclause, -5)  = ""; # delete last " AND "
			my @dataCols;
			my $cols; 
			# build columns for set statement and start ordered columns with non key fields (key fields are appended to this below in the order of the WHERE clause)
			map {if (!$keycolumns{$_}) {$cols.="[$_] = ?,"; push @dataCols, $_}} @columns;
			substr($cols, -1)  = ""; # delete last ","
			my $prepUpdStmt = "UPDATE ".$tableName." SET $cols WHERE $whereclause";
			$logger->trace("prepared update statement: $prepUpdStmt") if $logger->is_trace;
			my $sth = $dbh->prepare($prepUpdStmt);
			# execute with data from data columns and key columns
			for my $primkey (keys %{$data}) {
				my @dataArray;
				for (my $i=0; $i <= $#columns; $i++) {
					if ($i <= $#dataCols) {
						$dataArray[$i] = $data->{$primkey}{$dataCols[$i]};
					} else {
						$dataArray[$i] = $data->{$primkey}{$keycolumns[$i-$#dataCols-1]};
					}
				}
				$logger->trace("executed data: @dataArray ") if $logger->is_trace;
				$sth->execute(@dataArray) or do {
					my $errRec;
					for (my $j=0; $j < scalar(@{columns}); $j++) {
						my $datatype = $coldefs->{$columns[$j]}{"TYPE_NAME"}."(".$coldefs->{$columns[$j]}{"COLUMN_SIZE"}.")";
						$errRec.= $columns[$j]."[".$datatype."]:".$dataArray[$j].", ";
					}
					confess $DBI::errstr." with record: $errRec";
				}
			}
		} else {
			confess "no data to update";
		}
	};
	if ($@) {
		$logger->error($@);