time.png

Lately, I have got many doubts from people trying to understand the logic behind time conditions. So, I will try in this article to explain it. Let us start with some basic ideas: a) FusionPBX is only a frontend of FreeSWITCH, do not forget this, as all the dial plan magic happens inside FreeSWITCH; b) as for FreeSWITCH eyes', the concept of time condition do not exist, it is just another dial plan with some condition based on the time.

With all this said, I will start explaining.

It seems that the FILTER() clause was introduced in SQL 2003 revision. The FILTER clause allows you to have a better control when doing statistics functions in SLQL such as COUNT(), MAX(), MIN(), SUM() and others. Sadly, so far only Postgresql has implemented it. According to my readings on the net, other big players such as MySQL, MariaDB, Oracle, Microsoft SQL, SQLite (and maybe others) do not support it. Lucky us, there is a way to translate those queries.

Translating SUM(field) FILTER(WHERE) to SUM(CASE WHEN)

Please note this approach works with any other SQL function that returns a statistic calculation (MAX, MIN, AVERAGE). There is an alternative for COUNT(), I will write about it bellow.

Let's have this query as an example:

SELECT SUM(balance) FILTER (WHERE expired = 0) AS total FROM mytable

This could be translated as

SELECT SUM(CASE WHEN expired = 0 THEN balance ELSE 0 END) AS total FROM mytable

It is very important to put the ELSE 0, otherwise, the result could it be null (which it is different than zero). I know this example could be written in a very simple way. But trust me, there are many other SQL sentences that can hardly be translated into JOINS.

Translating COUNT(*) FILTER(WHERE) to SUM(CASE WHEN)

There is no a direct way to support COUNT(CASE WHEN). At least, I tried on MariaDB 10.1 and I got a syntax error. There is another approach. Let us say we have this SQL sentence:

SELECT COUNT(*) FILTER (WHERE expired = 0) AS count FROM mytable

This could be translated as

SELECT SUM(CASE WHEN expired = 0 THEN 1 ELSE 0 END) AS count FROM mytable

We are changing the COUNT() into a SUM(), but instead of adding the value in the variable, we are fixing it with a 1. 

Good luck!

roundcube-calendar.png

Roundcube is an awesome webmail. The most complete I have ever seen so far. It's fancy, look and feel combined with its IMAP access makes it not only very intuitive but to be synchronized with others clients. Well, if you are familiar with IMAP protocol you will understand why. Anyway, you can go to the Roundcube official website to read about the multiple capabilities this software has.

One thing that Roundcube lacks is a Calendar, similar to the Google Calendar. Lucky us, Kolab the open source project, has published a calendar plugin for Roundcube. I will talk here how I manage to install it under my ISPConfig3 web hosting server.

aws-icons.png

Not a fan of Amazon AWS service but I have someone who is. Although the cluster installation process it is almost the same, there are some notes to keep in mind. These notes are done thinking you are familiar with FusionPBX, FreeSWITCH, and AWS.

Rsyslog is the Syslog server I have chosen to use. I could get Syslog-ng, but while reviewing the configuration files and having a lot of experience using the vanilla Syslog, Rsyslog seems to be the best option and the faster to work with.

By default, Rsyslog will always do the reverse resolution. This could be a double knife weapon, while sometimes it is nice having it, it makes the process slow (because the DNS takes time) and the output could be more confusing. 24-140-225-46.cpe.teksavvy.com. is harder to read than 24.140.255.46.

I will describe how I manage to disable it.