Aan het eind va 2018 kreeg mijn iPhone problemen met het synchroniseren van de agenda met Baïkal. Het automatisch synchroniseren op de achtergrond leek niet meer te werken.

Door een paar Baïkal account-instellingen op de iPhone aan te passen, kon ik de synchronisatie weer werkend krijgen. Eerst maakte ik de Baïkal-instellingen op de iPhone ongeldig en sloeg ze op. Daarna terugveranderen in de juiste instellingen. Hierdoor gaat de iPhone de hele agenda opnieuw synhroniseren.

Uit wat zoekwerk bleek dat iOS vermoedelijk weigert om (herhaald) te synchroniseren met een “self-signed” certificaat op de Baïkal-server. Er worden geen foutmeldingen getoond. Andere iOS-gebruikers met vergelijkbare problemen meldden dit op discussiefora.

Verder onderzoek leidde me naar LetsEncrypt. Voor mijn server-setup bestaat een script voor het gebruik van Certbot, een tool waarmee genereren en installeren van LetsEncrypt-certificaten grotendeels automatisch verloopt. Dat werkte prima. De Baïkal-server heeft nu een “echt” certificaat, en automatische synchronisatie van de agenda werkt nu weer op iOS.

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.

Berekeningen met datum-waarden in selecties op de database zijn lastig. Voor aggregatie naar weeknummers, waren we op zoek naar een manier om de datum van de eerste dag van de week te berekenen, uitgaande van een willekeurige datum en op basis van ISO 8601 weeknummers.

In SAP HANA is een aantal handige standaardfuncties beschikbaar: ISOWEEK, WEEKDAY, ADD_DAYS. Functie ISOWEEK berekent het ISO weeknummer voor een opgegeven datum. WEEKDAY berekent een integer die hoort bij de dag van de week voor een opgegeven datum: 0 voor maandag, 6 voor zondag. Dit past bij de regels van ISO 8601 die zeggen dat maandag de eerste dag van de week is.

Een SELECT-statement dat de datum van de eerste dag van de week berekent voor een willekeurige datum:
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;

Microsoft SQL Server heeft vergelijkbare, handige standaardfuncties: CAST, DATEPART, WEEKDAY. De DATEPART-functie is veelzijdig en kan ook worden gebruikt om het ISO 8601 weeknummer te berekenen voor een gegeven datum.

Sommige features van SQL Server zorgen voor complicaties. De WEEKDAY-functie berekent een integer tussen 1 en 7 voor een opgegeven datum: de dag van de week. Welke dag dat is hangt af van de waarde van een omgevingsvariabele DATEFIRST. Dit gaat veel verder dan ISO 8601, maar we hebben toch de kennis nodig om de datum van de eerste dag van de ISO week te kunnen berekenen:
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;

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.