Re: [INTERFACES] Prepare and prepare ?

[David Wheeler]

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
[EMAIL PROTECTED] ICQ: 15726394
Yahoo!: dew7e
Jabber: [EMAIL PROTECTED]
Kineticode. Setting knowledge in motion.[sm]