Changes in Pomm2's projection system

Posted 3 years ago.

The better: the change

Programmers books call that «the difference between theory and real life». The projection as proposed by Pomm 2 until now was powerful but yet simplistic. By example if one wants to extend a projection with the information of the age of students in the table is odd or even:

$projection
    ->setField(´is_even´, ´cast(extract(year from age(%birthdate)) as int) % 2 = 0´, ´bool´)
    ;

$sql = strtr( "select :projection from student s", [ ´:projection´ => $projection->formatFieldsWithFieldAlias(´s´), ] );

The SQL statement is expanded as:

select
  s.first_name as first_name,
  …,
  cast(extract(year from age(s.birthdate)) as int) % 2 = 0 as is_even
from
  student s

Notice the nice expansion of the %birthdate field with the table alias in the expression. As long as it was that simple, the projection system was working. Real life came in the form of this bug ticket. To be short: a column was using a SQL reserved keyword (limit) hence should be quoted. To be able to detect and quote fields correctly in complex expressions as the one above, the % sign was not enough. So the notation changed to %:field:%. The projection must now be rewritten as:

$projection
    ->setField(´is_even´, ´cast(extract(year from age(%:birthdate:%)) as int) % 2 = 0´, ´bool´)
        ;

The generated SQL expression is now

select
  s."first_name" as "first_name",
  …,
  cast(extract(year from age(s."birthdate")) as int) % 2 = 0 as "is_even"
from
  student s

The hard part: the change

Changing the projection syntax means a project using Pomm 2 must be fixed if it uses projection expressions. The change is now commited in a branch named projection. It will be merged into master the Friday 6th of March. That´s the problem of running beta software, the good news behind this is that Pomm will turn stable soon.