Re: Prepare and prepare ?

On Thu, 6 Feb 2003, David Wheeler wrote:

> Unfortunately I have no time to work on DBD::Pg stuff right now. :-( 
> I'll have to rely on others to evaluate your approach. Post to the 
> dbi-dev list, too -- those folks have a better idea on how to implement 
> DBI drivers, I think.
> 
> I hope I can find time to get back to this stuff one day, but I'm 
> (happily) way too busy right now.
> 
> Regards,
> 
> David
> 

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).   

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.?


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_queryn"  ($1, $2, ... $n, $n+1)'  for DBD::Pg's execute.
 

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

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

4. Switched over to use driver.xst.

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

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
timeexecute    is called. 
 

8.  :veryverylongplaceholdername ==  Long walk. Sort pier -- fixed.

9.  quote() is now in C and uses same code as bind_param.

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

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

12. fixed many heap buffer overruns.



Known problems with patch:

1. Broke new pg_bool_tf attribute.

2. Broke rows() method (Just needs a define in makefile)

3. quote("stuff", {typeinfo }) is broken (only when hashref is passed in)

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,
sodecide exactly how to   do this and how to get prepares of INSERT statements to work?  Move  prepqre to execute and
buildlist column list in bind_param? Add   attributes to prepare to identify the columns and serverside prepare if
theyexist?
 

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

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
quotefunction.  This will  take care of broken chopblanks and pg_bool_tf (pass the  quote/dequote   options struct to
functionquote/dequote functions).
 

2. Export the full list of supported OID types.

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.).

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.



-r