Re: Prepare and prepare ? - Mailing list pgsql-interfaces

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@kineticode.com                              ICQ: 15726394                                               Yahoo!:
dew7e                                              Jabber: Theory@jabber.org
 
Kineticode. Setting knowledge in motion.[sm]