#6536: multi-line PostgreSQL calculated field names being escaped improperly
----------------------------+-----------------------------------------------
Reporter: mikesmullin | Type: Bug
Status: new | Priority: Medium
Milestone: 1.3.x.x | Component: PostgreSQL
Version: 1.2 Final | Severity: Normal
Keywords: | Php_version: n/a
Cake_version: |
----------------------------+-----------------------------------------------
== Synopsis ==
To prevent accidental escape of PL/PGSQL calculated fields in
`Model::find('all', array('fields' => array( /* ... */ ))` the field name
in question needs to have an opening and closing parenthesis on the first
line and just begin with something other than the opening parenthesis
(e.g. `COUNT(*)`, `SUM(fieldname)`, etc.)
== Proposal ==
However, in my case this was too restrictive. Consider the following
calculated field:
{{{
--- Pythagorean theorem based straight-line distance calculation
CASE
WHEN "ZipCodeB".zip_code_longitude < "ZipCodeA".zip_code_longitude THEN
CASE
WHEN "ZipCodeB".zip_code_latitude < "ZipCodeA".zip_code_latitude THEN
'NW'
WHEN "ZipCodeB".zip_code_latitude > "ZipCodeA".zip_code_latitude THEN
'NE'
ELSE 'N'
END
WHEN "ZipCodeB".zip_code_longitude > "ZipCodeA".zip_code_longitude THEN
CASE
WHEN "ZipCodeB".zip_code_latitude < "ZipCodeA".zip_code_latitude THEN
'SW'
WHEN "ZipCodeB".zip_code_latitude > "ZipCodeA".zip_code_latitude THEN
'SE'
ELSE 'S'
END
ELSE
CASE
WHEN "ZipCodeB".zip_code_latitude < "ZipCodeA".zip_code_latitude THEN
'W'
WHEN "ZipCodeB".zip_code_latitude > "ZipCodeA".zip_code_latitude THEN
'E'
ELSE NULL
END
END as direction
}}}
This will get way screwed up as DboPostgres::fields() attempts to rename
and escape this as if it were an ordinary (e.g. non-calculated) fields.
Of course, you can always run all these types of fields through a function
like preg_replace('/[\r\n]+/', '', $plpgsql_calculated_field) but its much
easier to just have DboPostgres understand these by modifying its regex on
line 432 of ./cake/libs/model/datasources/dbo/dbo_postgres.php from:
{{{
'/^.+\\(.*\\)/'
}}}
to:
{{{
'/\\(.*\\)/s'
}}}
Which I believe was the author's true intent, without considering the
possibility that calculated fields may be longer than just a brief one-
line call to a single aggregate function (e.g. `COUNT`, `MIN`, `MAX`,
`SUM`, `AVG`, etc.) but in fact useful for more intricate things, as well.
--
Ticket URL: <https://trac.cakephp.org/ticket/6536>
CakePHP : The Rapid Development Framework for PHP <https://trac.cakephp.org/>
Cake is a rapid development framework for PHP which uses commonly known design
patterns like ActiveRecord, Association Data Mapping, Front Controller and MVC.
Our primary goal is to provide a structured framework that enables PHP users at
all levels to rapidly develop robust web applications, without any loss to
flexibility.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"tickets cakephp" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/tickets-cakephp?hl=en
-~----------~----~----~----~------~----~------~--~---
