aws-icons.png

User Rating: 0 / 5

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive

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.

roundcube-calendar.png

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active

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.

User Rating: 0 / 5

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive

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!

About

Read about IT, Migration, Business, Money, Marketing and other subjects.

Some subjects: FusionPBX, FreeSWITCH, Linux, Security, Canada, Cryptocurrency, Trading.