Somewhere at the end of 2018, the Baïkal calendar synchronization on my iPhone started to show some problems. Continuous synchronization in the background did not seem to work.

By changing some of the Baïkal account settings on the iPhone, the synchronization could be made to work again. Intentionally, I create invalid settings, let the validation pass and save the settings. Then, I restore the original, correct settings again. As a result, the iPhone starts a full synchronization, which takes a few minutes in my case.

Further analysis showed that probably iOS does not like to synchronize using a self-signed certificate on the Baïkal-server. There is no error reported. Other users with comparable issues have reported this in discussion groups.

Some research led me to trying LetsEncrypt. For my server setup, there is a script available for using Certbot, a tool that automates issuing and installation of LetsEncrypt-certificates. Worked like a charm. The Baïkal-server now uses a real certificate and iOS synchronization works again.

Calculations involving dates in database selections can cause headaches. For aggregation into “week buckets”, we were looking for a way to calculate the date of the first day of the week, given an arbitrary date and using the ISO 8601 definiton of a week.

On SAP HANA, we have some convenient standard functions at our disposal: ISOWEEK, WEEKDAY, ADD_DAYS. The ISOWEEK-function calculates the ISO week number for a given date. The WEEKDAY-function returns an integer for the day of the week of a given date: 0 for Monday, 6 for Sunday. In a sense, it follows the logic of ISO 8601, that says that Monday is the first day of the week.

A SELECT-statement calculating the date of the first day of the week for any given date is:
SELECT null AS DateFirst, TO_DATE('2017-01-01', 'YYYY-MM-DD') AS Date, ISOWEEK(TO_DATE('2017-01-01', 'YYYY-MM-DD')) AS IsoWeek, ADD_DAYS(TO_DATE('2017-01-01', 'YYYY-MM-DD'), 0 - WEEKDAY ( TO_DATE('2017-01-01', 'YYYY-MM-DD'))) AS IsoWeekFirstDay FROM DUMMY;

On Microsoft SQL Server, there are comparable, convenient standard functions: CAST, DATEPART, WEEKDAY. The DATEPART-function is versatile, and can be used to calculate the ISO 8601 week number from a given date.

SQL Server has a few features that complicate things. The WEEKDAY-function returns an integer value between 1 and 7 for a given date, representing the day of the week. Which day it is, depends on the value of an environment variable DATEFIRST. This is outside the realm of ISO 8601, but we need the functionality to get the date of the first day of th ISO week:
SELECT @@DATEFIRST AS DateFirst, '2017-01-01' AS Date, DATEPART(isowk, '2017-01-01') AS IsoWeek, CAST(CAST('2017-01-01' AS datetime) - DATEPART(WEEKDAY, CAST('2017-01-01' as datetime) + @@DATEFIRST + 5 ) % 7 AS date) AS IsoWeekFirstDay;

On my new iPhone, I switched on the most important business functions: email (IMAP), and synchronization of contacts and en calendar. These services are being provided by my own servers.

For the synchronization of contacts, the CardDAV-protocol is used. For the calendar, a similar protocol called CalDav is in use. Baïkal is the server side software that facilitates this, and it uses the Nginx web server. Recent versions of iOS support IMAP, CardDAV and CalDAV. My laptop synchronizes this data with the same server, using the same protocols.

Setup of email and contacts was quickly done on the iPhone. Calendar synchronization did not work at the first attempt. Reason for a bit of investigation.

When searching the web for keywords iOS and Baïkal, a number of specific problem reports came up. To enable access to a properly functioning Baïkal-server from iOS, a few specific settings have to be made in the web server. When contacting the Baïkal-server, iOS uses a default path, something like https://my.server.com/baikal/.well-known/caldav. For this, a redirect-rule must be set up in the web server, causing the iPhone’s request to be redirected to the correct address.

Most of the documentation for Baïkal does not assume Nginx is the web server. A separate issue is that it is not always clear where exactly the redirect rules must be implemented. Options:

• In the root folder of the website controlled by Nginx, a folder called .well-known must exist. In that folder, a file named .htaccess must exist which contains (Nginx specific) redirect rules. This did not cause a redirect upon request by iOS.
• In the base configuration of Nginx. Changing this session led to a redirect of the iPhone’s request to the right url.

Example of redirect rules for Nginx in the .htaccess file:

rewrite ^/.well-known/carddav$ /card.php;
rewrite ^/.well-known/caldav$ /cal.php;

Example of redirect rules in the server block of file /etc/nginx/nginx.conf:

server { listen 443; server_name my.server.com; root /var/www/baikal/html; index index.php; rewrite ^/.well-known/caldav /dav.php redirect; rewrite ^/.well-known/carddav /dav.php redirect; charset utf-8;

After making this change, the iPhone’s calendar still did not synchronize. When entering the Baïkal server address, iOS validates a number of things, and decides that no calendar can be found. In the access logs of the Nginx server, the incoming requests are visible. The last request all cause a http 405 error (method not allowed).

iOS asks the CalDAV server many different questions, some of which Baïkal appears not to support. Errors like this can therefore not completely be avoided, even when synchronization works. However, from the iPhone, the calendar could still not be synchronized.

I noticed that configuration of synchronization of contacts on the iPhone had not been problematic at all. An analysis:

• The default url expected by the Baïkal-server for the contacts is https://my.server.com/baikal/card.php/addressbooks/erwin/default/
• iOS had changed it (and still working) into https://my.server.com/baikal/card.php/principals/erwin
• When setting up calendar synchronization, iOS expects https://my.server.com, and asks for more information at https://my.server.com/baikal/.well-known/caldav.
• This leads to a redirect to https://my.server.com/baikal/cal.php/addressbooks/erwin/default/ which causes only error messages.
• This is why I entered as the address for calendar synchronization on iOS: https://my.server.com/baikal/cal.php/principals/erwin. And this works.

So, for synchronization of contacts and calendar on iOS using a Baïkal-server running on Nginx:

• The redirect rules (for iOS) must be correctly applied in Nginx, so in its base configuration.
• The url’s that must be entered into iOS for connecting with Baïkal are different from what other Baïkal-clients use.

Since a few weeks, I am using an iPhone as business smartphone.

This has a long history. For quite some time, I have been using Nokia smartphones with Symbian. First, the N95, and later some of its successors. Their specifications were never best in class, and configuring them required quite a lot of work. But, they generally met my requirements for a business smartphone. Then came a robust Samsung Galaxy S4 Active and later an S6. Many of their functions work perfectly, as long as you want to use the Google prepared solutions. Until recently, I have been using a Jolla, using SailfishOS, which can be considered a descendant of Symbian. Its specifications were mediocre. Still, email and the calendar used to work fine. Later, the device was showing a number of issues, which were difficult to grasp, but recurring. This became so elaborate that I decided to look for another smartphone.

For business use, my requirements are modest. Email, calendar and contacts need to be available. The smartphone is only one of the devices, so everything must be kept synchronized, using various accounts. The most important central functions (email, synchronization) is hosted on my own servers.

Additionally, it would be convenient to have some other functions available when on the road: taking pictures, social media, etc. For this, I considered two alternatives: a separate tablet next to the (simple) smartphone. Or, a single smartphone that can do all. I decided for a large iPhone 7 Plus, which can do it all. Android is not my preferred smartphone operating system. Windows smartphones provide value for money, but Windows appears to become marginal. A smartphone running something different from Android, Windows or Apple’s iOS is not available, currently.

So, what is the verdict after a few weeks of using? The device is easy to use. It is surprising how quick and easy configuration is done. Having a powerful smartphone with a nice display and a good camera is of course very nice. After a few weeks, the last issue regarding the business functions has been resolved: calendar synchronization.

HANA is many things. It is an own, in-memory database manager with columnar store by SAP AG. Including a number of newly-developed components for ERP, advanced planning, Business Intelligence, Analytics, etcetera.

What is special about SAP HANA’s database management system? In earlier articles, the key challenge of designing contemporary database management software has been described, as well as the column oriented store used by HANA.

All column oriented tables are also made available in row oriented form by SAP HANA. HANA takes care that the row and column oriented versions of a table are kept in sync. Changes to the data require extra work to update both the row and the column store.

Now: The SAP HANA delta-stores.

In order to avoid impacting the run time of data changing operations too much, SAP HANA does not immediately update the row and column versions of a columnar tabular upon posting the changes (for example an SQL UPDATE-statement). Every change to the table’s content is initially saved to a delta-store for that table, after which the change statement is complete for the requester. In the background, SAP HANA processes the delta-store and updates row and column versions of the table. This is the delta merge process.

When another change to the data has to be processed at the moment the delta merge process is active, the delta store is not available. The change is then stored in a second delta store for the same table, so that it can be processed into the row and table versions at a later stage.

Next to additional storage space used, another consequence of using delta stores is that upon every request for table content, SAP HANA has to consult both delta stores as well as the row and column versions of the table.

Complicated? Using a column store makes dramatic improvements to the speed of queries on large data sets possible. The delta-stores prevent the cost of extra updates from slowing down the data changing operations. This is about large databases which are used intensively. For that, an efficient solution is required where reading and writing operations on the same data at any moment. Multi version concurrency control.