SQL language for different databases has some differences, which could cause problems after migrations between these databases. Here, we collected some hints, problems could appear during migration from MySQL to PostgreSQL. We focus on the CakePHP model layer and query generation layer.
Tables join in where expression
Often we want to join two tables using a condition like $query->where([‘Author.id = Article.author_id’]) which works fine till we dont need field alias quotes. In the case of migration to postgres, we might want to enable autoQuotes. In this case, we can use $query->newExpr()->equalFields(‘Author.id, Article.author_id’).
Case sensitivity in like expressions
By default mysql does case insensitive search. Switching to postgres, you can note that some functionality works differently. Hopefully you have tests, which covers your code, and this will be detected during migration. Postgres uses custom syntax for such queries named ILIKE.The case of old style conditions where method arrays straight forward, you’d just go with ILIKE instead of LIKE. But what if we want to use ILIKE in builder methods... Here is that example:
return $query->where(
function (QueryExpression $exp) use ($field, $value): QueryExpression {
return $exp->add(new \Cake\Database\Expression\Comparison($field, $value, 'string', 'ILIKE'));
});
Type casing
As postgres is much more strict with types, type casing is not a rare operation and may be needed.
Here is an example of how to perform it using FunctionExpression: $expr = (new FunctionExpression('CAST'))->setConjunction(' AS ')->add([$id, 'varchar' => 'literal']); which generates expression like :id AS varchar there :id is the placeholder for variable $id. This trick, used with literal, allows you to cast to any postgres type.
Quotes of tables and fields
Sometimes it is critical to inform CakePHP that the field should be quoted. One recommendation is to avoid using plain strings in case of table joins, or using IS NULL as string. So if array syntax is used, all CakePHP conventions must be followed. However, sometimes we should help the ORM and obviously wrap a field name with IdentifiedExpression. Let's take a look back to the previous example, but now we want to type cast not value, but a field. The only solution to quote field name correctly is using this code:
$id = new IdentifierExpression($this->aliasField($field));
$expr = (new FunctionExpression('CAST'))->setConjunction(' AS ')->add([$id, 'varchar' => 'literal']);
Building complex arithmetic expressions
In case we want to generate expressions in query fields, and we don’t want to overcomplicate logic, we could use these next tricks. Here, I have created ListExpression, which could be used as a collection of expressions. Each of these are corrected, and generates a query with correct handling of each element. See: https://gist.github.com/skie/f6e4f1a1b61e0f902a507f7907c3bbf2
So, say we want to generate expressions like this: “Events”.”time_to” - “Events”.”time_from”...
With ListExpression, it can be done quite easy:
$diff = new ListExpression([new IdentifierExpression('Events.time_to'), '-', new IdentifierExpression('Events.time_from')]);
Hopefully these tricks will be as useful for your baking as they have been for mine!
Once the database has been created, bake models and controllers as normal using:
