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

[Allan Bush]

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