Re: Prepare and prepare ?

On Tuesday, February 11, 2003, at 10:51 PM, Rudy Lippan wrote:

> Since David is too busy to work on DBD::Pg right now, I have ported the
> changes that I made against 1.13 to CVS HEAD from gborg.postgresql.org.
> The patch can be found here:
> http://www.remotelinux.com/rlippan/dbd_pg.patch (I did not include it
> in
> this email because I did not want to spam everyone with a 100K diff).

Wow. Reading your description below, I'm really impressed. That's a lot
of work! Good thing I stepped aside! ;-)

> I'd like to start putting together some developer releases (set up a
> cvs
> branch for this?), and see about getting together a stable release
> version
> that addresses the all of the outstanding problems with DBD::Pg.
>
> So, if people are interested, where do I go from here to get things set
> up for doing dev releases &c.?

No need for a separate branch, I think. Just change the version number
to have an undersore, e.g., 1.30_01. That will automatically make it a
beta release number when it's put on CPAN.

> What the patch does:
>
> 1. $dbh->prepare() rewrites the SQL statement into an internal for
> striping out comments and whitespace, and if PostgreSQL > 7.3 takes
> the
> stripped statement
> and passes that to postgress' PREPARE statement, then rewrites the
> statement as 'EXECUTE "DBD::PG::cached_query n" ($1, $2, ... $n,
> $n+1)'
> for DBD::Pg's execute.

Cool.

> 2. Allows the use of :n and :foo bind params. So: (SELECT * FROM foo
> where
> 1 = :this and 2 = :that) will now work.

Oh, was that broken?

> 3. Complains on execute when unbound bind params are submitted
> (instead of
> defaulting to NULL)

Didn't know about that one.

> 4. Switched over to use driver.xst.

Yay!

> 5. pg_error() only removes \n's don't truncate message on first \n

Oh, good. I think it should be changed to do_error, too, with renaming
in dbdimp.h, no?

> 6. fixed statement scan problem where the preparse of
> "SELECT foo[3:33] from bar" was scanning :33 as a placeholder
>
> 7. moved the quoting of bind values out of execute() and into
> bind -- as there is no need to requote the value every time execute
> is called.

Cool.

> 8. :veryverylongplaceholdername == Long walk. Sort pier -- fixed.
>
> 9. quote() is now in C and uses same code as bind_param.

Nice. Faster, t hen.

> 10. quoting and dequoting now use libpq quoting functions where
> available
> (I still need
> to take the libpq functions swiped out of quote.c and move it into
> libpqswip.c
> with license info &c., and switch ifndefs to ifdefs)

Excellent.

> 11. bind_param() will convert from 1,0 to TRUE/FALSE when pg_type is
> PGBOOLOID.

Smart.

> 12. fixed many heap buffer overruns.

Do you include tests for all of these things, too? That'd be great.

> Known problems with patch:
>
> 1. Broke new pg_bool_tf attribute.

Ah. Can be fixed, I assume?

> 2. Broke rows() method (Just needs a define in makefile)
>
> 3. quote("stuff", {typeinfo }) is broken (only when hashref is passed
> in)

Probably is never used, anyway. Or shouldn't be. Is it fixable?

> 4. broke chopblanks.
>
> 5. has some // style comments around debug statements that need to be
> converted into logging statements
>
> 6. PREPARE is not bullet-proof casts in RSH of equality and functions
> LHS of equality can break serverside prepare, so decide exactly how
> to
> do this and how to get prepares of INSERT statements to work? Move
> prepqre to execute and build list column list in bind_param? Add
> attributes to prepare to identify the columns and serverside
> prepare if
> they exist?

Not sure I understand the problems here. Is it weaknesses in
PostgreSQL's PREPARE?

> 7. Code is not as clean as it should be -- some kludgeness in there.

Oh, well *that's* not new! ;-)

> 8. Documentation has not been updated.
>
> 9. Needs more tests.
>
>
>
> On my immediate todo list:
>
> 1. Change quote and dequote functions to take Sv instead of string so
> that
> things like arrays can be seralized by the quote function. This
> will
> take care of broken chopblanks and pg_bool_tf (pass the
> quote/dequote
> options struct to function quote/dequote functions).

Cool.

> 2. Export the full list of supported OID types.

For constants? I think I added a partial list in 1.20.

> 3. finish all type mappings in types.c
>
> 4. supoort for begin_work() method.
>
> 5. clean logging and trace levels.
>
> 6. dblogin to handle user names, table table names and other
> names that containg ';'s
>
> 7. add attributes to control preparing of statements.
>
> 8. rewrite hacked version detection code (and get format for string so
> it is done correctly with patch level &c.).

You'll want to borrow code from PostgreSQL's ODBC implementation for
this, I think. Tim sent some links a while ago.

> 9. various code cleanups and polishing.
>
> 10. Copy over external test cases from DBD::Churlpg and write more
> tests.
>
> 11. Update documentation.
>
> There are probably many other things that I cannot remember off the
> top of
> my head since I have not done much (other than copy code over to CVS
> head)
> with this code since late Nov/ early Dec.

Still, it's a lot of work. I had no idea you'd done this, Rudy. I
suspect that once you get this committed, others will contribute, as it
represents the first real advances in DBD::Pg in years. I'll help out
where I can, too.

David

--
David Wheeler AIM: dwTheory
david(at)kineticode(dot)com ICQ: 15726394
Yahoo!: dew7e
Jabber: Theory(at)jabber(dot)org
Kineticode. Setting knowledge in motion.[sm]