[Vtigercrm-developers] Patches required for PostgreSQL 8.1.4

[Jens Hamisch]

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      

-------------- next part --------------
A non-text attachment was scrubbed...
Name: postgres-patches.tgz
Type: application/octet-stream
Size: 18385 bytes
Desc: not available
Url : http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20060718/e9b482c8/attachment-0003.obj