Re: Function update_data

[mikeh@minivend.com]

Quoting Stefan Hornburg (racke@linuxia.de):
> 
> 
> Hello, MiniVend hackers !
> 
> I'm looking at the MiniVend source code, especially the function
> update_data in minivend. On updating or inserting an row, every field is
> updated separately by Vend::Data::set_field. This seems 
> 1) quite inefficient to me

I don't worry much about this one -- I never spend much time looking for
efficiency in rare operations. The time is better spent optimizing the
repetitive ones.

> 2) conflicts with constraints like "NOT NULL" on SQL databases.
> 

This is much more important. The original version of update_data did
aggregate the values and do an UPDATE .... SET ...., which caused
problems of its own. I took it out to make the database interface more
uniform.

There are many SQL issues anyway, such as limiting length. I really wish 
Oracle would allow autotruncation, for example!

Probably the best way to do this is to create a "row_settor" subroutine in
Vend::Table::DBI, similar to field_settor. These routines yield a closure
which allows a one-call row update. (The only example of its use in the
current stuff is the journal update routine in MiniMate.) The advantage
of this compared to putting SQL special-cases is that the row_settor
routine can be done for both DBM and DBI.

Even if you don't like DBM, there are memory databases and the new
session-oriented databases to think about.

WARNING: UNTESTED CODE -- SQL not verified!

=head1 EXAMPLE 

	my @cols = qw/code description price/;
	my $db = Vend::Data::database_exists_ref();
	$db = $db->ref();
	my $update = $db->row_settor(@cols);
	my @rows = (
					[ '00-0011', 'Mona Lisa', 100_000 ],
					[ '99-103', 'T-Shirt', 9.95 ],
				);
	for (@rows) {
		$update->(@$_);
	}

=cut

sub row_settor {
	my ($s, @columns) = @_;
	my ($i, @quote);
	for ($i = 0; $i < @columns; $i++) {
		push @quote, $i 
			unless $s->[$CONFIG]{NUMERIC}{$columns[$i]};
	}
	return sub {
		my(@values) = @_;
		my $query;
		my $update = $s->row_exists($values[0]) ? 1 : 0;
		for(@quote) {
			$values[$_] = $s->[$DBI]->quote($values[$_]);
		}
		my $key = $values[0];
		if($update) {
			$query = "update $s->[$TABLE] ("	.
					join (",\n", @columns)		.
					") SET ("					.
					join (",\n", @values)		.
					") where $s->[$KEY] = $key"
					;
		}
		else {
			$query = "insert $s->[$TABLE] ("	.
					join (",\n", @columns)		.
					") VALUES ("					.
					join (",\n", @values)		.
					")"
					;
		}
		my $sth = $s->[$DBI]->prepare($query);
		if(!$sth) {
			::logError("DBI prepare row_settor for table=$s->[$TABLE]: $DBI::errstr");
			return undef;
		}
		my $rc = $sth->execute();
		if(! defined $rc) {
			::logError("DBI execute row_settor for table=$s->[$TABLE]: $DBI::errstr");
			return undef;
		}
		return $rc;
	};
}

But as I have demonstrated before, I am not really SQL savvy so
suggestions are appreciated.

-- 
Mike Heins                          http://www.minivend.com/  ___ 
                                    Internet Robotics        |_ _|____
If you think nobody cares if        131 Willow Lane, Floor 2  | ||  _ \
you're alive, try missing a couple  Oxford, OH  45056         | || |_) |
of car payments.                    <mikeh@minivend.com>     |___|  _ <
 -- Earl Wilson                     513.523.7621 FAX 7501        |_| \_\