Re: [Slashcode-development] RFC: SQL Placeholders

[Stephen C. <the...@wa...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The extension I've come up with to implement placeholders is described
below.  The goal was not so much elegance as it was to keep from recoding half
of the Slash::DB tree, something I know you're not wanting to do so close 
to release.  Even so I think it meshes quite nicely, and gives a pretty good
illusion of elegance, assuming you don't think it is :)

Basically, instead of passing SQL with prefilled values to the generator
functions in Slash::DB::Utility, you would pass the statement elements with
placeholders, followed by an array reference containing the bind values.  So
something like this (from Slash::DB::MySQL::createComment):

	my($maxCid) = $self->sqlSelect(
		"max(cid)", "comments", "sid=$sid_db"
	);

becomes:

	my($maxCid) = $self->sqlSelect(
		"max(cid)", "comments", "sid=?", [$sid_db]
	);

Meanwhile, Slash::DB::Utility::sqlSelect looks like this:

sub sqlSelect {
	my $self = shift;
	my $sql = '';
	foreach my $elem ('SELECT ',' FROM ',' WHERE ',' ') {
		last if !@_ or ref($_[0]);
		$sql .= $elem . shift;
	}
	my $sth = $self->{_dbh}->prepare_cached($sql);
	$self->sqlConnect();
	if (!$sth->execute($_[0] ? @$_[0] : ()) {
		errorLog($sql);
		$self->sqlConnect;
		return undef;
	}
	my @r = $sth->fetchrow;
	$sth->finish;
	return @r;
}

which builds the SQL statement assuming the old parameter format until it
encounters the bind value arrayref, then passes that to execute as the bind
value set.

I picked a very simplistic example, but the syntax savings are in more complex
statements farther down that entail heavy parameter quoting.  Since bind values
don't need quoting, the excessive variable copying going on now can be
completely eliminated and the parameters passed verbatim in the arrayref.  That
alone is a huge cleanup to the MySQL code.  In the case of Oracle and PG (and
other databases that support bind values natively), any necessary quoting is
done in the server's internal API, making them work even faster.

The real necessary extension comes when binding values for insert.  Inserts
already use a hashref to carry the values over, so nothing needs to change there
(other than adjusting the internals of sqlInsert to generate a placeholder-based
statement instead).  The extension here is a second hashref containing the extra
database-dependent information.  So my current bane (the template insert):

	$self->sqlInsert('templates', $hash);

can become:

	$self->sqlInsert('templates', $hash,
		{ template => { ora_type => ORA_CLOB } }
	);

And that extra pragma for the template column would go into a bind_param call to
properly set up the Oracle statement.  The hashref extension should also be
available to the select function; although I've never run into a select
statement that needs the extra parameters, other databases might need
them for something.  And it doesn't change anything if it's not there anyway.

Admittedly this isn't the best product pitch I've done :)  But hopefully you can
extrapolate the benefits.  I've freed up my evenings this week for this task
(changing Slash::DB::Utility and Slash::DB::MySQL) if you approve of it.  I
don't expect it to take long, and the old MySQL code should keep working
anyway, since the parameters to sql* haven't really changed (just optional
ones tacked on).  I could even have Oracle doing something quasi-functional by 
this weekend, which would be a huge accomplishment, IMO.

In case you're curious about my drive for this, I'm wanting Slash2 to power my
soon-to-reactivate site, which has been floundering for well over a year and is
now beckoning me to give it a new drive core :)  Corporate politics and personal
SQL bigotry keep me from running MySQL on our main server cluster, so Oracle it
is.  I think support is close, though, once these minor design hurdles are
cleared.

- -- 
Stephen Clouse <the...@wa...>
warpcore.org Founder, Chief Megalomaniac, and Evil Overlord

-----BEGIN PGP SIGNATURE-----
Version: PGP 6.5.8

iQA/AwUBOtJLod1EXk7JbKbMEQI9HQCfaEybkJ4sYs2hk8uvR+wYjQX3igYAoPQt
UwSJ9PWWYM6DxDzjv8WaFRJ9
=nWpz
-----END PGP SIGNATURE-----