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_query n" ($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 time execute
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 functions swiped 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, 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?

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