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 namedILIKE.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 usingFunctionExpression: $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 usingIS 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!
Anyway, I am not going to talk about remote work because these days there are thousands of articles about it (even in one of our previous posts). I want to focus today on writing about our experience with different communication tools and the pros and cons for each of them.
Back in 2011 when I started working at CakeDC, we had an IRC server in place with a couple of plugins to retain messages for our day-to-day communication. Everyone used different clients like Colloquy on mac or Pidgin on Linux. Additionally, we used Skype for peer/team calls and also for client communication. In my opinion, the linux experience was awful until they improved the client a few years later. This setup was implemented in 2007 when the company was started, and in 2012, we decided to shut it down because it was easier just using Skype for everything, messages and calls.
After several years using - suffering - Skype, with new options in the market, we decided to move away to a more reliable and modern approach. The main reason was the lack of updates for Skype linux client, and the message retention limits. In 2016 we started utilizing the more than popular Slack and its open source alternative, Rocket Chat; always keeping Skype for client communication.
Some months later the team concluded that Rocket Chat was the right choice, mainly because we wanted to have control over the messages and the information transmitted. Since the features we were using were available in both solutions, we installed our own Rocket Chat server. At this point I have to say that we did try the calls solution (Jitsy) inside Rocket Chat, but the experience was not good at all, issues with missing calls, poor call quality, etc, made us keep Skype for calls.
On the other hand CakePHP training was provided using Join.me and even when it worked very well in most situations; our trainer Jorge, always had to use a Windows machine instead of his usual Linux one. And then, Zoom emerged.
The year was 2018, when Zoom became so popular even though it started back in 2011 (yes, Zoom existed before COVID-19 crisis). We started using it and it quickly replaced Skype for our team calls. It allowed multiple people calls, screen share, etc. I must say, however, the Zoom chat is one of the worst things I have ever seen.
Going back to Jorge, as you can imagine he was very happy when he saw Zoom had a good Linux client. Unfortunately, he was quickly disappointed because the client screen crashed randomly, went black, and the computer ran out of memory when he tried to share his screen to more than 10 attendees. Happily he didn't throw his windows machine out of the window yet so he could continue giving the training with that machine.
It's 2020, COVID-19 is around us, and Zoom is probably the most popular telecommuting tool. For both adults and children, it is an essential tool to keep working and studying. However, fame never comes alone and threats, rumors and comments are making us move away (again) from Zoom to Google Meet. Also, it didn't make sense to pay for Zoom if we were already paying for GSuite.
I didn't mention it before, but we have been using GSuite (former Google Apps) since the beginning. Google - late as usual - detected a market niche and decided to pump out its meeting tool. In my opinion, I am a standalone app person. This means that I will always prefer having 20 apps instead of 20 browser tabs. So, I don't like Google Meet a lot because of this, but I must say the call quality is superb.
I am not sure how or when we will move to other tools, but right now we are very happy with our Rocket Chat installation and we are still getting used to Google Meet, but it fits our needs.
As a side note we are still using skype to communicate with clients because it is the app everyone has, and sometimes people simply don't want to install something else or use something they are not used to.
To conclude I must say that each team and person should try all of these different tools before choosing one, because one tool that may fits my needs may not fit yours.
It’s time to beat out the competition, are you ready? I first recommend seeing where you stand. There are a few tools you can use to test your SEO score. One example is: 
To keep up with the planning process or any changes, make sure you follow us on 
