[Trac] PostgreSQL Conversion

[Brad Anderson]

<snip>

> > 1/ Changes like using COALESCE instead of IFNULL could be folded into
> > trunk immediately. I.e. anything that moves to more universal SQL and
> > works well with SQLite (obviously). It'd be nice to have separate
> > patches for such changes.
>
> Sounds good to me.

I can't commit changes to the repos, and rather than maintain and sync/merge 
another change branch on my local machine, I'm just going to include them in 
the pgsql patch.

I'll be sync/merging that one with the main trunk, so if you do it (like I saw 
in Query.py recently), I'll pick it up then, and they'll disappear from my 
patch.

<snip>

> > This is bad because you are now using string formatting to insert the
> > parameter, instead of using the DB API. The DB API will escape the
> > value for you, and quote it if necessary etc. It should be used
> > wherever possible. When it absolutely isn't possible to use the DB
> > API,  parameters *need* to be escaped with the util.sql_escape()
> > function.
> >
> > Now you probably made those changes because you got errors, but maybe
> > the following FAQ entry helps: "Using pyPgSQL, you use %s for all
> > parameters, no matter which type they have"
> > <http://pypgsql.sourceforge.net/pypgsql-faq.html#id2836440>. It would
> > be okay to use %s everywhere IMHO, and *much* better than using
> > string  formatting for parameter insertion.
>
> I actually agree with you.  I went through three different drivers, and
> in the end decided on pypgsql.  I have read that FAQ that you cited, and
> now that I'm using pypgsql, I can't remember on which driver that
> specific query and others like it broke.  It may have been pygresql or
> psycopg.  I got pretty far with pygresql and then found out you had to
> integer-index your resultset calls.  I.e. row['ticket'] was not allowed,
> and you had to know row['3'].  There are times where the results are
> dynamic, so this wasn't going to work.
>
> Now that I have trac-admin and Environment.py done, I will probably svn
> revert some of the modules and try again...

I've reverted some of the changes that I made like this, and went to using 
tuples inside of the cursor.execute() calls.  It was indeed working with the 
pygresql module that caused me to do that, but pyPgSQL worked fine with it, 
and I believe is the preferred module.

I also got trac-admin working with 'upgrade' as well as 'initenv'.  'wiki 
upgrade' and others await.

And I've gone through most of the modules and finished up some changes to make 
queries work in both.

I now feel okay about the patch, but more people need to test this with psgql 
as well as sqlite to see if the cross-db changes are working okay or not, and 
if I missed any queries.

BA