view release on metacpan or  search on metacpan

lib/DBIx/Raw.pm  view on Meta::CPAN

If you need to specify more constraints for the row that you are updating instead of just the id, you can pass in a where clause:

    my $num_rows_updated = $db->update(href => \%updated_person, table => 'people', where => 'name=? AND favorite_color=? AND age=?', vals => ['Joe', 'green', 61]);

Note that any bind values used in a where clause can just be passed into the C<vals> as usual. It is possible to use a where clause and an id or pk together:

    my $num_rows_updated = $db->update(href => \%updated_person, table => 'people', where => 'name=? AND favorite_color=? AND age=?', vals => ['Joe', 'green', 61], id => 1);

Alternatively, you could just put the C<id> or C<pk> in your where clause.

If you need to have literal SQL into your update query, then you just need to pass in a scalar reference. For example:

    "UPDATE people SET name='Billy', update_time=NOW() WHERE id=1"

If we had this:

    my %updated_person = (
        name => 'Billy',
        update_time => 'NOW()',
    );

    my $num_rows_updated = $db->update(href => \%updated_person, table => 'people', id => 1);

This would effectively evaluate to:

    my $num_rows_updated = $db->raw(query => "UPDATE people SET name=?, update_time=? WHERE id=?", vals => ['Billy', 'NOW()', 1]);

However, this will not work. Instead, we need to do:

    my %updated_person = (
        name => 'Billy',
        update_time => \'NOW()',
    );

    my $num_rows_updated = $db->update(href => \%updated_person, table => 'people', id => 1);

Which evaluates to:

    my $num_rows_updated = $db->raw(query => "UPDATE people SET name=?, update_time=NOW() WHERE id=?", vals => ['Billy', 1]);

And this is what we want.

=head3 update encrypt

When encrypting for update, because a hash is passed in you need to have the encrypt array reference contain the names of the columns that you want to encrypt
instead of the indices for the order in which the columns are listed:

    my %updated_person = (
        name => 'Billy',
        age => '32',
        favorite_color => 'blue',
    );

    my $num_rows_updated = $db->update(href => \%updated_person, table => 'people', id => 1, encrypt => ['name', 'favorite_color']);

Note we do not ecnrypt age because it is most likely stored as an integer in the database.

=cut

sub update {
	my $self = shift;
	my $params = $self->_params(@_);

	croak "href and table are required for update" unless $params->{href} and $params->{table};

	my @vals;
	my $string = '';
    my @encrypt;
	while(my ($key,$val) = each %{$params->{href}}) {
		my $append = '?';
		if (ref $val eq 'SCALAR') {
			$append = $$val;
		}
		else {
            # TODO: write update encrypt tests
            if ((defined $params->{encrypt} and $params->{encrypt} eq '*')
                    or ($params->{encrypt} and first { $_ eq $key } @{$params->{encrypt}})) {
                push @encrypt, scalar(@vals);
            }

			push @vals, $val;
		}

		$string .= "$key=$append,";
	}

	$string = substr $string, 0, -1;

	$params->{vals} = [] unless $params->{vals};
	my $where = '';
	if($params->{where}) {
		$where = " WHERE $params->{where}";
		push @vals, @{$params->{vals}};
	}

	if($params->{id}) {
		if($where eq '') {
			$where = " WHERE id=? ";
		}
		else {
			$where .= " AND id=? ";
		}

		push @vals, $params->{id};
	}
	elsif($params->{pk}) {
		my $name = $params->{pk}->{name};
		my $val = $params->{pk}->{val};
		if($where eq '') {
			$where = " WHERE $name=? ";
		}
		else {
			$where .= " AND $name=? ";
		}

		push @vals, $val;
	}

	$params->{query} = "UPDATE $params->{table} SET $string $where";
	$params->{vals} = \@vals;