[jens: Patches required for PostgreSQL 8.1.4]

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

[jens: Patches required for PostgreSQL 8.1.4]

Jens Hamisch

--

--------------------------------------------------------------------------------
     /
 +##+|##+   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:        [hidden email]
  \____/    [hidden email]      


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:        [hidden email]
  \____/    [hidden email]      


_______________________________________________
Get started with creating presentations online - http://zohoshow.com?vt 

postgres-patches.tgz (24K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [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 <[hidden email]> 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:        [hidden email]
>   \____/    [hidden email]
>
>
>
>
> ---------- Forwarded message ----------
> From: Jens Hamisch <jens>
> To: [hidden email]
> 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:        [hidden email]
>   \____/    [hidden email]
>
>
>
> _______________________________________________
> Get started with creating presentations online - http://zohoshow.com?vt
>
>
>
_______________________________________________
Get started with creating presentations online - http://zohoshow.com?vt 
Reply | Threaded
Open this post in threaded view
|

Re: [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 <[hidden email]> 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:        [hidden email]
> >   \____/    [hidden email]
> >
> >
> >
> >
> > ---------- Forwarded message ----------
> > From: Jens Hamisch <jens>
> > To: [hidden email]
> > 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:        [hidden email]
> >   \____/    [hidden email]
> >
> >
> >
> > _______________________________________________
> > 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:        [hidden email]
  \____/    [hidden email]      

_______________________________________________
Get started with creating presentations online - http://zohoshow.com?vt