[Slashcode-development] RFC: SQL Placeholders

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

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

As of late I have been working on getting Slash2 to work with Oracle (going
ahead on my own since there wasn't much interest (read: none) a while back when
I asked).  So far it's been quite successful -- the initial database creates and
loads without error.  Now it's down to messing with the DB interface 
itself.  The biggest show-stopper so far is while loading the templates in:

DBD::Oracle::db do failed: ORA-01704: string literal too long

because it's trying to embed the entire string into the SQL statement, which you
can't do in Oracle if it's over 4000 bytes (the boundary between VARCHAR and
LOB).  The variable has to be explicitly typed as a LOB when preparing the
statement, and that requires the use of DBI placeholders for bind 
values.  However, there isn't a single placeholder in the current code.  I found
this rather surprising.

Aside from the obvious technical issue above, there's a performance one as
well.  Oracle isn't able to cache any of the SQL with the variables embedded
inside, so performance will suffer when I finally do manage to get it
working.  This is actually an issue with MySQL as well, since Slash2 currently
makes quite a bit of use of prepare_cached, a use that's practically worthless
(and a large waste of memory) without using placeholders.

My proposal is to get the SQL internals using placeholders and bind values to
facilitate current and future porting, as well as making the current MySQL core
substantially cleaner and more memory-efficient.  I've spent all day looking
over the Slash::DB::Utility interface and the sql* methods and I've devised an
extension to the current interface that would make the changes to the current
code minimal (an additional argument to sql* calls with embedded variables, and
some fairly minor recoding where such calls are).  The conversion would almost
be painless.

Even so, this is a pretty radical change to the code, and I wouldn't go ahead
with it without some kind of "blessing" from the core developers and ensuring
that this is a direction they want to head.  However, my main goal is seeing 
this thing run on Oracle, and I don't see any way of getting it there without
A) these changes to allow Oracle more seamless plugging into the existing code,
or B) a substantial rewrite Slash::DB::* for Oracle, which would almost be a
code fork since the interface would have to be radically different.  That's the
last place I want to head.

There are other porting issues I've come across, but they're pathetic compared
to this.  Comments are welcome.

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

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

iQA+AwUBOs/Lt91EXk7JbKbMEQKX8gCYsdgaJOPlKxDvE+XB3g7EBLielACeLYzY
vleJuQHrdCaBJn4gtYttfow=
=y90I
-----END PGP SIGNATURE-----