[Vtigercrm-developers] [jens: Patches required for PostgreSQL 8.1.4]

[Jens Hamisch]

Hi Allan,
hi *,

most changes I did seem not to be related to postgres only.
So it would help a lot (and remove dbType dependencies) from
the code if smb. could check if my changes will also be valid
for mySQL ...

Jens


On Tue, Jul 18, 2006 at 07:14:27AM -0700, Allan Bush wrote:
> I have given up on getting postgres support into 5.0 because I got
> tired of having my changes reverted and the database changing on me.
> The last straw was when all the database table names were changed
> without warning.
> 
> If someone else wants to continue the effect feel free to take a look
> at this patch and take over the effort.
> 
> On 7/18/06, Jens Hamisch <jens at strawberry.com> wrote:
> >
> > --
> >
> > --------------------------------------------------------------------------------
> >      /
> >  +##+|##+   STRAWBERRY                     Jens Hamisch
> > +v#+v v##+  EDV-Systeme GmbH               Managing director
> > / v    v\v
> > | . .  . |  Waldeckstr. 9a                 Car (Voice):  (+49 172) 81 04 162
> > |     .  |  D-82515 Wolfratshausen         Voice:        (+49 8171) 41805-0
> >  | .     |                                 Fax:          (+49 8171) 41805-59
> >  \   .  /   Tel.: (+49 8171) 41805-0       Email:        jens at Strawberry.COM
> >   \____/    Strawberry at Strawberry.COM
> >
> >
> >
> >
> > ---------- Forwarded message ----------
> > From: Jens Hamisch <jens>
> > To: vtigercrm-developers at lists.vtigercrm.com
> > Date: Tue, 18 Jul 2006 09:02:01 +0200
> > Subject: Patches required for PostgreSQL 8.1.4
> > Hi,
> >
> > I'm currently running vtigercrm5 (revision 8057) using a Postgres 8.1.4
> > database. Compared to mySQL this database seems to require some code
> > changes. I've located and fixed the following problems:
> >
> >     1.  SELECT count(*) count FROM ...  won't work.
> >         The AS clause is missing: SELECT count(*) AS count FROM ...
> >
> >     2.  Referring table columns in case of joined tables in the
> >         SELECT or ORDER BY clause requires the columns also to be
> >         listed in a GROUP BY clause.
> >
> >     3.  tablename.* won't work in a GROUP BY clause
> >
> >     4.  LIMIT #,# is not supported. PostgreSQL requires a single
> >         LIMIT and OFFSET clause.
> >
> >     5.  PostgreSQL supports transactions. However the current coding
> >         results in transaction failures.
> >
> > I've attached my patches to this mail. Those patches at a first glance
> > address the problems 1-4. The transaction problem is not yet fixed.
> > I want to clean up the "simple" SQL statements first, before analyzing
> > such complex things.
> >
> > I'm not a 100% satisfied with the patches, because they introduce some
> > dbType dependencies into the "high level" vtiger code. Also structural
> > information is required in the function which expands the queries by
> > the required GROUP BY clause. I was thinking of moving those things
> > to a more abstract layer, but stopped doing this, because a generic
> > solution would either result in parsing and fixing each entire SQL statement
> > (including all its features and possibilities) or a redesign of the
> > affected SQL queries itsself.
> >
> > In most cases (especially the LIMIT changes) my patches might also work
> > for mySQL, so the database dependency possibly could be removed. This
> > might also apply to some of the GROUP BY patches.
> >
> >
> > Hope those changes will find their way to the vtigercrm5 mainline.
> >
> > Kind regards
> > -- Jens
> >
> > --------------------------------------------------------------------------------
> >      /
> >  +##+|##+   STRAWBERRY                     Jens Hamisch
> > +v#+v v##+  EDV-Systeme GmbH               Managing director
> > / v    v\v
> > | . .  . |  Waldeckstr. 9a                 Car (Voice):  (+49 172) 81 04 162
> > |     .  |  D-82515 Wolfratshausen         Voice:        (+49 8171) 41805-0
> >  | .     |                                 Fax:          (+49 8171) 41805-59
> >  \   .  /   Tel.: (+49 8171) 41805-0       Email:        jens at Strawberry.COM
> >   \____/    Strawberry at Strawberry.COM
> >
> >
> >
> > _______________________________________________
> > Get started with creating presentations online - http://zohoshow.com?vt
> >
> >
> >
> _______________________________________________
> Get started with creating presentations online - http://zohoshow.com?vt 

-- 

--------------------------------------------------------------------------------
     /
 +##+|##+   STRAWBERRY                     Jens Hamisch
+v#+v v##+  EDV-Systeme GmbH               Managing director
/ v    v\v
| . .  . |  Waldeckstr. 9a                 Car (Voice):  (+49 172) 81 04 162
|     .  |  D-82515 Wolfratshausen         Voice:        (+49 8171) 41805-0
 | .     |                                 Fax:          (+49 8171) 41805-59
 \   .  /   Tel.: (+49 8171) 41805-0       Email:        jens at Strawberry.COM
  \____/    Strawberry at Strawberry.COM