blog.dotcs.me

Blending Databases with FastAPI and SQLAlchemy

2024-12-18T18:00:00Z

In this post, I will show how to blend multiple databases in a FastAPI application using SQLAlchemy. This is a common use case when multiple databases are being used and one wants to interact with them transparently through a single database session.

An example where this could make sense is having a common database for user data and separate databases with sensitive, maybe immutable, customer data that should not be mixed and where access should be restricted based on the tenant.

# Introduction

FastAPI is a modern web framework for building APIs with Python 3.6+ based on standard Python type hints. SQLAlchemy is a SQL toolkit and Object-Relational Mapping (ORM) library for Python.

The goal is to create a FastAPI application that connects to two databases: a SQLite database and another SQLite database. But the same principles can be applied to any other database supported by SQLAlchemy. SQLAlchemy will be used to interact with the databases and FastAPI is used to expose the data through an API. Two base classes will be created to handle the database sessions and models. The FastAPI application will be created with two endpoints to query the data from each database.

# Code

You can find the complete code for this post in my GitHub repository.

The magic happens in the VirtualSession class, which configures two database sessions, which are created through FastAPI's sessionmaker function. We mimic the behavior of a single session by creating a virtual session that can be used to interact with both databases. By proxying the session methods, such as query or add, we can interact with both databases transparently.

By making use of FastAPI's dependency injection system, we can inject the virtual session into our endpoints and interact with both databases through a single session.

Integration tests are included to demonstrate how to interact with the databases through the session using the FastAPI test client.

# Conclusion

In this post, we have seen how to blend multiple databases in a FastAPI application using SQLAlchemy. This is a powerful feature that allows you to interact with multiple databases through a single session transparently. It is flexible and can be extended to support more databases or different database types by adding more database sessions and models.

Combining FastAPI Dependency Injection with Service and Repository Layers

2024-11-11T21:50:16+01:00

FastAPI has a wonderful Dependency Injection (DI) system that works reasonably well at the controller level. However, to build a robust and testable application, it is helpful to make use of a dependency injection chain that spans multiple layers of the application. This approach not only promotes a clean architecture but also significantly improves the test situation.

# Starting Point

Typically, a FastAPI application consists of a controller layer (the FastAPI endpoints), a service layer and often a repository layer. While FastAPI provides a way to inject dependencies into the controller layer, it does not provide a way to inject dependencies into the service or repository layers.

But this does not mean that we cannot use dependency injection across multiple layers. By building a dependency injection chain that spans multiple layers, we can inject dependencies into the service and repository layers as well.

Let's see how we can do this and get our hands dirty with some code.

A typical application might look like this:

graph LR A[Controller] --> B[Service] B --> C[Repository] C --> D[Database]

But often we have application settings that might be used across the application in all layers, which let the application look like this:

graph TD subgraph Z[" "] direction LR A[Controller] --> B[Service] B --> C[Repository] C --> D[Database] end E[Settings] E --> A E --> B E --> C

Sometimes we also have a logger that we want to inject into all layers:

graph TD subgraph Z[" "] direction LR A[Controller] --> B[Service] B --> C[Repository] C --> D[Database] end E[Settings] E --> A E --> B E --> C F[Logger] F --> A F --> B F --> C

And many more dependencies might be possible. Some of them could be shared across all layers, some of them might be shared only between some layers, and some of them might be specific to a single layer.

# Building the Dependency Injection Chain

To build a dependency injection chain that spans multiple layers, we can use Python's Annotated type and the Depends class from FastAPI.

Let's start by defining a Settings class that holds our application settings by inheriting from Pydantic BaseSettings class. This helps to ensure that the settings are correctly typed and validated and can be easily injected into other classes. (Also it helps to load the settings from environment variables or configuration files, which is typically why I like to use it in my applications.) 😉

# file: settings.py
from pydantic import BaseSettings

class Settings(BaseSettings):
    app_name: str = "My App"
    db_connection_string: str = "sqlite:///:memory:"
    debug: bool = False

Next, let us define the three layers of our application: the controller, service, and repository layers.

The repository layer is responsible for fetching data from the database. In this example, we will keep it simple and return some sample data. In a real-world application, this would be a database query, often by using an ORM like SQLAlchemy.

# file: repository.py
class Repository:
    def __init__(self, settings: Settings, db: Database):
        self.settings = settings
        self.db = db   # just to show that we can inject multiple dependencies

    def get_sample_data(self):
        # usually this would be a database query
        return [{"name": "Alice"}, {"name": "Bob"}]

Note that we have not used FastAPI's Depends here. This is because the repository layer is not controlled by FastAPI. Instead we will inject the Settings and Database objects into the repository layer by using the normal dependency injection pattern, meaning we will pass them as arguments to the constructor.

The service layer is responsible for business logic. In this example, we will keep it simple and return the application name in uppercase.

# file: service.py
from fastapi import Depends

class Service:
    def __init__(self, settings: Settings, repository: Repository):
        self.settings = settings
        self.repository = repository

    def app_name_upper(self):
        return self.settings.app_name.upper()

    def get_sample_data_with_upper_names(self):
        data = self.repository.get_sample_data()
        return [{"name": item["name"].upper()} for item in data]

Before we define the controller layer, let us write the dependency injection functions which FastAPI will use to inject the dependencies into the service and repository layers. These functions are needed by the FastAPI dependency injection system to resolve the dependencies. Their purpose is to instantiate the objects. FastAPI allows to let dependencies depend on other dependencies, which allows to build a dependency chain.

# file: dependencies.py
from typing import Annotated

from myapp.settings import Settings
from myapp.database import Database
from myapp.repository import Repository

def get_settings():
    """Returns the application settings."""
    return Settings()

def get_db(settings: Annotated[Settings, Depends(get_settings)]):
    """Returns the database connection."""
    return Database(connection_string=settings.db_connection_string).connect()

def get_repository(settings: Annotated[Settings, Depends(get_settings)], db: Annotated[Database, Depends(get_db)]):
    return Repository(settings, db)

def get_service(settings: Annotated[Settings, Depends(get_settings), repository: Annotated[Repository, Depends(get_repository)]):
    """Returns the service layer."""
    return Service(settings, repository)

Finally, we define the controller layer, which consists of the FastAPI endpoints. We inject the Service class into the controller layer using the Depends class from FastAPI.

Note that we do not need to inject the Repository class into the controller layer because it is already injected into the Service class, which is then injected into the controller layer. Also there is no need to retrieve and pass the database connection to the service, because the repository is responsible for that.

FastAPI will automatically resolve the dependency chain and resolve the dependencies in the correct order.

# app.py
from fastapi import FastAPI
from typing import Annotated

from myapp.dependencies import get_service

app = FastAPI()

@app.get("/")
def read_root(service: Annotated[Service, Depends(get_service)]):
    return {"app_name": service.app_name_upper()}

@app.get("/sample_data")
def read_sample_data(service: Annotated[Service, Depends(get_service)]):
    return service.get_sample_data_with_upper_names()

Note that from the controller layer, we only inject the Service class. If needed, we could also inject the Settings class or any other dependencies that are meant to be used in all layers. But there is no need to inject the Repository class or the Database class into the controller layer!

# Testing

This significantly simplifies testing.

For example, to test the Service class, we can easily mock the Repository class and the Settings class. This is generally possible because of the dependency injection pattern we have used for initializing the Service class.

# test_service.py
from myapp.service import Service
from myapp.repository import Repository
from myapp.settings import Settings

class MockSettings(Settings):
    app_name = "My App under test"

class MockRepository(Repository):
    def get_sample_data(self):
        return [{"name": "Foo"}, {"name": "Bar"}]

def test_app_name_upper():
    service = Service(MockSettings(), MockRepository())
    assert service.app_name_upper() == "MY APP UNDER TEST"

def test_get_sample_data_with_upper_names():
    service = Service(MockSettings(), MockRepository())
    assert service.get_sample_data_with_upper_names() == [{"name": "FOO"}, {"name": "BAR"}]

But the real power of this approach becomes apparent when testing the controller layer.

Because we have used the Depends class from FastAPI to inject the Service class into the controller layer and have configured everything as a dependency chain, we can easily override the dependency injection functions to inject mock objects at any level.

The following example shows how to override the get_repository function to inject a MockRepository object into the controller layer.

# test_controller.py
from fastapi.testclient import TestClient

from myapp.app import app
from myapp.repository import Repository
from myapp.dependencies import get_settings, get_repository

class MockRepository(Repository):
    def get_sample_data(self):
        return [{"name": "Foo"}, {"name": "Bar"}]

def mock_get_repository(settings: Annotations[Settings, Depends(get_settings)]):
    # Return a mock repository instead of the real one.
    # Also, we don't need the database connection here, so we can pass None.
    return MockRepository(settings, None)

def test_read_root():
    # Arrange
    # Override any dependencies that need to be mocked
    app.dependency_overrides[get_repository] = mock_get_repository

    client = TestClient(app)

    # Act
    response = client.get("/sample_data")

    # Assert
    assert response.status_code == 200
    assert response.json() == [{"name": "FOO"}, {"name": "BAR"}]

This approach allows us to easily test the controller layer by injecting mock objects at any level of the dependency chain.

Note that we only need to override the dependency injection functions that we want to mock. This makes the tests more focused and easier to understand. In this case the full application logic can be tested without the need to start a real database or to mock the database connection by just mocking the repository layer.

# Vizualizing the Dependency Injection Chain

If the application is built like this and gets more complex over time because of many dependencies, it can be helpful to visualize the dependency injection chain. Unfortunately I was not able to find a tool that can automatically generate a diagram from the code, so I wrote a Python library that can do this and published it on PyPI as an open-source project.

The library works by inspecting the application code and generating a graph in the DOT language, which can then be rendered to an image using Graphviz. Alternatively the library provides to output mermaid diagrams, which can be used in markdown files, e.g., on GitHub.

The library is called fastapi-di-viz and can be installed into a project with pip install fastapi-di-viz. It provides a CLI tool that allows to inspect any FastAPI application. See the README for more details on how this works.

Crawler for German Bank Insitutes

2023-01-23T20:33:00+01:00

Recently I stumbled across the bank.green project, which aims to add some transparency on how banks invest the money of their investors. I really like the project idea since following the money typically seems to have a high chance of success. I do not think I fully understood how they want to generate those insights for most bank institutes world-wide, but still it is worth trying in my opinion.

A quick search showed that a lot German banks are missing in their list. While I was able to find the GLS bank, the bank of my trust, I missed a lot other institues - especially the Sparkasse and Volksbank institutes which are very popular in Germany.

To help the project, I wanted to contribute the necessary core data for German bank institutes. So I started writing a web-crawler based on scrapy, which crawls all the institutes and generates a rather large JSON file, with the extracted data.

My crawling tests have shown that the structure of the Sparkasse is much easier to crawl than the Volksbank institutions. It seems that Sparkasse institutes use a common website engine, while Volksbank take a freer approach and give their institutes more freedom in structuring their imprint pages.

Nevertheless I found a way to cover the most relevant data and provide it to the bank.green project.

The result looks like this for Sparkasse institutes

[
{"url": "https://www.sparkasse-nienburg.de", "imprint_url": "https://www.sparkasse-nienburg.de/de/home/toolbar/impressum.html", "name": "Sparkasse Nienburg", "address": "Sparkasse Nienburg\nAnstalt des \u00f6ffentlichen Rechts\nGoetheplatz 4\n31582\u00a0Nienburg", "routing_number": "25650106", "bic": "NOLADE21NIB", "company_register_number": "HR A 21724 beim Amtsgericht Walsrode", "vat_id": "DE116159984", "phone": "+4950219690", "telefax": "+4950219696969", "email": "info@sparkasse-nienburg.de"},
{"url": "https://www.sparkasse-vorderpfalz.de", "imprint_url": "https://www.sparkasse-vorderpfalz.de/de/home/toolbar/impressum.html", "name": "Sparkasse Vorderpfalz", "address": "Sparkasse Vorderpfalz\nAnstalt des \u00f6ffentlichen Rechts\nLudwigstra\u00dfe 52\n67059\u00a0Ludwigshafen", "routing_number": "54550010", "bic": "LUHSDE6AXXX", "company_register_number": "HRA 3647 beim Amtsgericht Ludwigshafen", "vat_id": "DE149138080", "phone": "+4962159920", "telefax": "+496215992865992", "email": "kontakt@sparkasse-vorderpfalz.de"},
{"url": "https://www.ksk-tuebingen.de", "imprint_url": "https://www.ksk-tuebingen.de/de/home/toolbar/impressum.html", "name": "Kreissparkasse T\u00fcbingen", "address": "Kreissparkasse T\u00fcbingen\nAnstalt des \u00f6ffentlichen Rechts\nM\u00fchlbach\u00e4ckerstra\u00dfe 2\n72072\u00a0T\u00fcbingen", "routing_number": "64150020", "bic": "SOLADES1TUB", "company_register_number": "HRA 381312 beim Registergericht Stuttgart", "vat_id": "DE146889408", "phone": "+4970712050", "telefax": "+497071205105", "email": "info@ksk-tuebingen.de"},
{"url": "https://www.kreissparkasse-schwalm-eder.de", "imprint_url": "https://www.kreissparkasse-schwalm-eder.de/de/home/toolbar/impressum.html", "name": "Kreissparkasse Schwalm-Eder", "address": "Kreissparkasse Schwalm-Eder\nAnstalt des \u00f6ffentlichen Rechts\nSparkassenplatz 1\n34212\u00a0Melsungen", "routing_number": "52052154", "bic": "HELADEF1MEG", "company_register_number": "HR A 14161 beim Amtsgericht Fritzlar", "vat_id": "DE113056386", "phone": "+4956617070", "telefax": "+4956617073100", "email": "info@kskse.de"},
{"url": "https://www.ssk-cuxhaven.de", "imprint_url": "https://www.ssk-cuxhaven.de/de/home/toolbar/impressum.html", "name": "Stadtsparkasse Cuxhaven", "address": "Stadtsparkasse Cuxhaven\nAnstalt des \u00f6ffentlichen Rechts\nRohdestra\u00dfe 6\n27472\u00a0Cuxhaven", "routing_number": "24150001", "bic": "BRLADE21CUX", "company_register_number": "HRA 110595 beim Amtsgericht Tostedt", "vat_id": "DE115168565", "phone": "+4947211090", "telefax": "+494721109276", "email": "rechnungseingang@ssk-cuxhaven.de"},
{"url": "https://www.spk-vorpommern.de", "imprint_url": "https://www.spk-vorpommern.de/de/home/toolbar/impressum.html", "name": "Sparkasse Vorpommern", "address": "Sparkasse Vorpommern\nAnstalt \u00d6ffentlichen Rechts\nAn der Sparkasse 1\n17489\u00a0Greifswald", "routing_number": "15050500", "bic": "NOLADE21GRW", "company_register_number": "Handelsregister Stralsund HRA 1291", "vat_id": "DE811671292", "phone": "+4938345577888", "telefax": "+4938345577239", "email": "info@spk-vorpommern.de"},
{"url": "https://www.ostsaechsische-sparkasse-dresden.de", "imprint_url": "https://www.ostsaechsische-sparkasse-dresden.de/de/home/toolbar/impressum.html", "name": "Osts\u00e4chsische Sparkasse Dresden", "address": "Osts\u00e4chsische Sparkasse Dresden\nAnstalt des \u00d6ffentlichen Rechts\nG\u00fcntzplatz 5\n01307\u00a0Dresden", "routing_number": "85050300", "bic": "OSDDDE81XXX", "company_register_number": "HRA 4000  beim Amtsgericht Dresden", "vat_id": "DE140135071", "phone": "+493514550", "telefax": null, "email": "e-mail@sparkasse-dresden.de"},
{"url": "https://www.mbs.de", "imprint_url": "https://www.mbs.de/de/home/toolbar/impressum.html", "name": "Mittelbrandenburgische Sparkasse in Potsdam", "address": "Mittelbrandenburgische Sparkasse in Potsdam\nAnstalt des \u00f6ffentlichen Rechts\nSaarmunder Str. 61\n14478\u00a0Potsdam", "routing_number": "16050000", "bic": "WELADED1PMB", "company_register_number": "HRA 2432 P beim Amtsgericht Potsdam", "vat_id": "DE138408302", "phone": "+49331898989", "telefax": "+49331898985", "email": "kontakt@mbs.de"},
{"url": "https://www.sparkasse-opr.de", "imprint_url": "https://www.sparkasse-opr.de/de/home/toolbar/impressum.html", "name": "Sparkasse Ostprignitz-Ruppin", "address": "Sparkasse Ostprignitz-Ruppin\nAnstalt des \u00f6ffentlichen Rechts\nFontaneplatz 1\n16816\u00a0Neuruppin", "routing_number": "16050202", "bic": "WELADED1OPR", "company_register_number": "A 1037 beim Amtsgericht Neuruppin", "vat_id": "DE138672917", "phone": "+493391810", "telefax": "+49339181292222", "email": "info@sparkasse-opr.de"},
...
]

and like this for Volskbank institutes

[
{"url": "https://www.vb-isun.de", "imprint_url": "https://www.vb-isun.de/service/rechtliche-hinweise/impressum_OSOGS.html", "name": "Volksbank in Schaumburg und Nienburg eG", "address": "Klosterstr. 30\n31737 Rinteln", "routing_number": "25591413", "bic": "GENODEF1BCK", "company_register_number": null, "vat_id": "DE116160038", "phone": "+49572495145300", "telefax": "+49575140589", "email": "info@vb-isun.de"},
{"url": "https://www.volksbank-kleverland.de", "imprint_url": "https://www.volksbank-kleverland.de/service/rechtliche-hinweise/impressum.html", "name": "Volksbank Kleverland eG", "address": "Minoritenstr. 2\n47533 Kleve", "routing_number": "32460422", "bic": "GENODED1KLL", "company_register_number": null, "vat_id": "DE120050936", "phone": "+4928218080", "telefax": null, "email": "info@volksbank-kleverland.de"},
{"url": "https://www.leipziger-volksbank.de", "imprint_url": "https://www.leipziger-volksbank.de/service/impressum.html", "name": "Leipziger Volksbank eG", "address": "Schillerstr. 3\n04109 Leipzig", "routing_number": "86095604", "bic": "GENODEF1LVB", "company_register_number": null, "vat_id": "DE141508765", "phone": "+4934169790", "telefax": "+493416979106", "email": "Kontakt@leipziger-volksbank.de"},
{"url": "https://www.vr-genobank.de", "imprint_url": "https://www.vr-genobank.de/service/rechtliche-hinweise/impressum_OSOGS.nolayer.html", "name": "VR GenoBank DonauWald eG", "address": "Raiffeisenstrasse 1\n94234 Viechtach", "routing_number": "74190000", "bic": "GENODEF1DGV", "company_register_number": null, "vat_id": "DE131459282", "phone": "+49992284010", "telefax": "+499942944966", "email": "online@vr-genobank.de"},
{"url": "https://www.volksbank-plochingen.de", "imprint_url": "https://www.volksbank-plochingen.de/service/rechtliche-hinweise/impressum_OSOGS.nolayer.html", "name": "Volksbank Plochingen eG", "address": "Am Fischbrunnen 8 \n73207 Plochingen", "routing_number": "61191310", "bic": "GENODES1VBP", "company_register_number": null, "vat_id": "DE145341772", "phone": "+49715398250", "telefax": "+497153706146", "email": "ezv@volksbank-plochingen.de"},
{"url": "https://www.volksbank-eg.de", "imprint_url": "https://www.volksbank-eg.de/service/rechtliche-hinweise/impressum_OSOGS.nolayer.html", "name": "Volksbank eG", "address": "M\u00fcnsterstr. 34\n48231 Warendorf", "routing_number": "41262501", "bic": "GENODEM1AHL", "company_register_number": null, "vat_id": "DE126731251", "phone": "+492581570", "telefax": "+49258157122", "email": "kundenservicecenter@volksbank-eg.de"},
{"url": "https://www.vbidr.de", "imprint_url": "https://www.vbidr.de/service/rechtliche-hinweise/impressum.html", "name": "Volksbank in der Region eG", "address": "Herrenberger Str. 1-5\n72070 T\u00fcbingen", "routing_number": "60391310", "bic": "GENODES1VBH", "company_register_number": null, "vat_id": "DE145047512", "phone": "+4970329400", "telefax": "+4970329401193", "email": "info@vbidr.de"},
{"url": "https://www.volksbank-stuttgart.de", "imprint_url": "https://www.volksbank-stuttgart.de/service/rechtliche-hinweise/impressum.html", "name": "Volksbank Stuttgart eG", "address": "Daimlerstra\u00dfe 129\n70372 Stuttgart", "routing_number": "60090100", "bic": "VOBADESS", "company_register_number": null, "vat_id": "DE147325720", "phone": "+497111810", "telefax": "+497111812497", "email": "info@volksbank-stuttgart.de"},
{"url": "https://www.volksbank-syke.de", "imprint_url": "https://www.volksbank-syke.de/service/rechtliche-hinweise/impressum.html", "name": "Volksbank eG, Syke", "address": "Bremer Str. 28\n27211 Bassum", "routing_number": "29167624", "bic": "GENODEF1SHR", "company_register_number": null, "vat_id": "DE116638071", "phone": "+49424185858", "telefax": "+49424185859", "email": "kundenservice@volksbank-syke.de"},
...
]

At the moment the code is not yet published. I am considering doing so later on but for the moment I only plan to release the full blown JSON files through some static file serving, e.g., a GitHub gist or so. Stay tuned. If you are interested in the data set or any questions related to it, please reach out to me.

Learnings along the way:

Sometimes it helps to play with the User Agent string. Using curl/7.72.0 opened some doors that otherwise would be closed.
Sometimes it makes sense to slow down crawling in order to not DOS servers. Still pages might put measurements in place to prevent too many requests from one IP address. In my case this could be mitigated by writing a custom middleware that replaced the original RetryMiddleware and forced a 2min break if a 503 Service Unavailable error was found.
Writing web-crawlers to extract data based on the DOM nodes paths and CSS classes or xpaths is brittle. Maybe it would help to train a ML model to extract the data on its own. The model might be much more stable against changes on the website that might occur at any time.

Copy large files to Nextcloud

2021-01-02T16:49:22+01:00

I'm running my own Nextcloud instance in a docker container with the data directory mounted from a local folder on my disk. Unfortuantely it's not very satisfying to use the web UI or using a davfs2 mount of the disk to copy large files to the Nextcloud instance. This is because files must be passed through the full LAMP stack which involves a lot overhead and slows down any large file uploads.

An alternative to upload files via the browser or WebDAV is to copy them directly into the user data folder and then force Nextcloud to sync the content with its internal file cache and database entries.

Say our Nextcloud instance data lives in /opt/nextcloud/data and our source files live in /tmp/stage on the server. Further let the Nextcloud username be my-user and say the source data should be copied to ~/my-folder in Nextcloud.

Copying the data would then look like this:

# copy files
rsync -avP /tmp/stage/ /opt/nextcloud/data/my-user/files/my-folder/
# fix permissions
chmod www-data:www-data /opt/nextcloud/data/my-user/files/my-folder

It's necessary to fix the permissions since the default user in the official Nextcloud docker images is the www-data user. See also the Nextcloud documentation. Details:

$ docker-compose exec nextcloud id www-data
uid=33(www-data) gid=33(www-data) groups=33(www-data)

After the files have been copied, Nextcloud must be forced to re-index all files within this directory:

$ docker-compose exec -u www-data nextcloud ./occ files:scan --path="/my-user/files/my-folder"
Starting scan for user 1 out of 1 (my-user)
+---------+-------+--------------+
| Folders | Files | Elapsed time |
+---------+-------+--------------+
| 6       | 354   | 00:00:01     |
+---------+-------+--------------+

The internal database and caches have been updated and your files will pop up in the web UI afterwards.

Privacy and Audible Audiobooks

2020-12-30T22:46:05+01:00

Audible has a lot books available as audiobooks and I think a price of about 10 EUR per book is more than justified. But unfortunately Audible locks people into their own ecosystem. Users must use the official Audible app(s) to download and listen to the purchased books. To my knowledge third party software is not supported. This has a lot to do with digital right managment (DRM) of course, but the situation also plays into Amazon's cards. By locking the people into a certain app they can analyze how people listen to the audiobooks. For example they could answer the following questions about you:

Which are the audiobooks that you listen to?
Which audiobooks have you started to hear? Which of them have you finished? Which have you rejected?
Which chapters have you re-read? Which parts have you skipped?
and so on

From their privacy information details:

Automatic information we collect and analyze include:

[...]

content interaction information, such as content downloads, streams, playback details, listening behavior like start and stop points, listening duration, reading behavior on your Kindle devices and apps (so we can provide our WhisperSync for Voice functionality and award badges based on your listening and also calculate royalties), and network details for streaming and download quality, including information about your internet service provider;

Audible Privacy Information, relevant text has been highlighted by me

I'm wondering if those award badges are mainly there so that Amazon can argue about any privacy concerns that people might have. At least for me they are not important at all, I would prefer a tracking-free Audible variant and would gladly waive the badges for this.

While it's not possible to prevent Amazon from tracking which Audiobooks are bought, it is possible to stop them from tracking HOW you read them. Let's see how this can be done.

Disclaimer: This approach breaks the DRM protection of the Audiobooks. Please make sure that this is legal in your country for the intended purpose.

# Downloading Audiobooks

Audiobooks that you have bought from Audible can be downloaded from your library. Execute this script in your browser's console to copy all relevant download links into the clipboard.

let links = Array.from(document.querySelectorAll('.adbl-lib-action-download > a[href^="https://cds.audible.de/download"]'))
    .map(el => el.attributes['href'].nodeValue);
let titles = Array.from(document.querySelectorAll('#adbl-library-content-main .bc-list a.bc-link[href^="/pd"]'))
    .map(el => el.innerText);

let data = links.map((href, i) => ({ href, title: titles[i]}));
copy(data);

This creates a valid JSON object in your clipboard which contains a list of records with the keys href and title for each audiobook. Don't share the document with anyone as the download links contain your private key.

wl-paste > /tmp/audible.json

Here I'm using the wl-paste (wayland only) command to get the content from the system's clipboard. If you're using X11 you might want to replace wl-paste with xsel or whatever you have installed. In a second I will also use jq to filter the JSON file, make sure to install it also.

If everything worked out, the temporary file you have created should look like this:

[
  {
    "href": "https://cds.audible.de/download?asin=B08KQJ469F&cust_id=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx&codec=LC_128_44100_Stereo&source=Audible&type=AUDI",
    "title": "Stille Nacht in der Provence"
  },
  // ....
]

Note that the param asin in the link refers to the book and cust_id is a customer specific identifier that gives access to the book and should not be shared with anyone.

Now let's kick off wget and download our audiobooks. Please note that audiobooks are large (~1-2GB per file). Make sure that you have enough storage available.

mkdir -p /tmp/audible && cd $_

jq -c '.[]' /tmp/audible.json | while read i; do
    href=$(echo $i | jq -r '.href')
    title=$(echo $i | jq -r '.title')
    wget -O "$title.aax" "$href"
done

In case you want to download the audiobooks in chunks you can also use jq ranges, e.g. jq -c '.[:3]'.

This will place all your audiobooks as *.aax files in the /tmp/audible folder.

Note: If you have a lot audiobooks you might want to consider a different approach that parallelizes the download queries.

# Convert from aax to m4a

The files contain a DRM protection which prevents users from sharing the files with others. In this step you will remove the DRM protection. Please make sure that this step is allowed by law in your country before proceeding.

To remove the DRM protection this respository can be used. It contains a docker file that comes pre-installed with all dependencies to break the DRM encryption and decrypt the files. Make sure to have docker pre-installed to be able to follow the next steps.

mkdir -p /tmp/inaudible && $_
git clone https://github.com/ryanfb/docker_inaudible_rainbowcrack .
docker build -t inaudible .

cd /tmp/audible
docker run --rm -v $(pwd):/data inaudible

This will read in all aax files, restore the encryption key via brute-force and then decrypt all aax files. The result will be one m4a and one png artwork file for each aax file.

Note that this will take additional storage on your disk - approximately the same amount that is needed for the aax files.

# Listen on Android

To listen to m4a files from an Android device I can highly recommend the Voice app from the F-Droid store. The interface is super clean, the application remembers the playback location and simply does its job - without tracking the user's behavior. Kudos to Paul Woitaschek for this piece of software!

Upload both, the m4a and png artwork file, to a folder on your device and add this folder to the list of folders that are scanned by Voice and you're done.

# Mission completed 🥳

So these few steps allow you to create a private copy of the audiobooks that you own. By using m4a files it's up to you to choose what player you want to use. Using an open source player such as Voice helps to be sure that noone is tracking how you read your books. Let's take our privacy back - step by step.

Oh and since award badges seem to be relevant these days, here is your nerds-love-privacy badge

🤓

Have fun with it or throw it away, I don't care.

Update: Oh, and if you're interested in an ETL pipeline that integrates well with a Nextcloud instance to serve the m4a files, have a look into this repository.

Pi-hole User Group Management

2020-11-18T08:10:34+01:00

At home I have a Pi-hole in service which acts as my central DNS provider for all devices in my network. This means that by default all devices share the same filter lists, so effectively ads are blocked on all devices.

This setup is most likely the setup most people need and prefer. For our home it's not working, because some devices should see clear and unfiltered DNS entries. Let's see how we can exclude certain devices from the filter lists.

# Pi-hole Group Management

Since Pi-hole v5.0, which has been released in 2020-05, groups can be configured. Known devices can be put into groups which can have different filter lists assigned to them. The group management can be found under http(s)://my-pi.hole/admin/groups.php.

In my setup I created a group for all my devices which I named dotcs.

Pi-hole Admin: Group management

By default this group does nothing. First devices need to be assigned to the group. Those settings can be found under http(s)://my-pi.hole/admin/groups-clients.php.

Pi-hole Admin: Device management

After that the adlists need to be configured and attached to the group. In my case I removed all of the lists from the default group and attached them only to the dotcs group.

Pi-hole Admin: Adlist management

With this configuration all devices listed in the dotcs group see filtered DNS entries whereas all other devices see unfiltered DNS entries.

Thanks to the Pi-hole developers for this awesome feature and please consider donating to the project if you like it.

How to deploy a Next.js application to GitHub pages

2020-11-16T22:56:02+01:00

Next.js is becoming very popular - for good reasons. To me it's one of the best frameworks out there to create all kinds of web pages that are based on React. Since Next.js does allow for full server-side rendering and static html exports, pages can be rendered completely on the server and delivered to the client as dump static HTML pages with a little bit of CSS and JavaScript where needed. This makes them perfectly suitable to be hosted on GitHub pages.

My first thought was: Yeah, easy let's do this quickly. But not so fast! I've leared that there are a few things that require tweaks.

# Use GitHub Actions to build and export Next.js pages

I use GitHub actions to build the static pages. The corresponding project setting is set to deliver pages from the gh-pages branch.

GitHub Pages settings to deliver from branch gh-pages

The corresponding .github/workflows/gh-pages.yml is simple. Note that the npm run export script has an enviroment variable DEPLOY_TARGET: gh-pages attached to it. This env variable will be used in the second step. In the deploy step the branch is set to gh-pages and we deliver results from the out folder, which is the default target folder for the next export command.

Also note that the pipelie creates the (empty) file out/.nojekyll. This is necessary to bypass Jekyll processing on GitHub pages as mentioned here. Otherwise folders that start with an underscore are ignored, but Next.js puts merged assets, e.g. CSS and JS files, into a folder _next. 😵

name: Build and Deploy
on:
  push:
    branches:
      - master
jobs:
  build-and-deploy:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout
        uses: actions/checkout@v2.3.1
        with:
          persist-credentials: false

      - name: Install and Build
        uses: actions/setup-node@v1
      - run: npm install
      - run: npm run build     # runs `next build`
      - run: npm run export    # runs `next export`
        env:
          CI: true
          DEPLOY_TARGET: gh-pages
      - run: touch out/.nojekyll

      - name: Deploy
        uses: JamesIves/github-pages-deploy-action@3.7.1
        with:
          GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}
          BRANCH: gh-pages  # The branch the action should deploy to.
          FOLDER: out       # The folder the action should deploy.
          CLEAN: true       # Automatically remove deleted files from the deploy branch

# Rewrite paths to static files

When serving websites via <username>.github.io any static assets must point to <username>.github.io/<projectname> with <projectname> being the name of the repository (typically again <username>.github.io). GitHub Pages rewrites some paths internally, so that they appear to come from <username>.github.io, but things will not work out if the <projectname> part is missing.

next export allows to set a assetPrefix, which tweaking the URL paths and re-write them from / to /<assetPrefix>/. This can be configured in the project's next.config.js:

const ghPages = process.env.DEPLOY_TARGET === 'gh-pages';

module.exports = {
  assetPrefix: ghPages ? '/dotcs.github.io/' : ''   // customize this value
};

With this line in place we detect if we're executed by the GitHub Action runner and re-write paths accordingly.

# Success

With those tweaks Next.js pages can be hosted via GitHub pages easily. You just need to be aware of them. 😉

Migrate from i3 to sway

2020-11-15T09:52:00Z

I recently changed from the Linux display server protocol X.org Server to Wayland on one of my machines. My favorite window manager i3 is not compatible with Wayland though, so I had to search for an alternative. Luckily there is a drop-in replacement for i3 available which is named Sway.

Sway is very similar to i3, it even uses the same syntax for the config, so the change required only a few tweaks which I will now talk about. I can also recommend to read the Sway article on the Arch Wiki.

At this let's take a brief moment to say "Thank you" to all contributors of Sway and Wayland and maintainers of the corresponding packages in the various distros out there. ♥

# Installation

Installation of sway under Arch Linux is easy. It's as simple as:

yay -S sway

# Copy configuration

Sway searches it's configuration in ~/.config/sway/config so I copied my i3 config there. All changes go into this config file.

# Adjust configuration

After the configuration has been copied use a separate TTY to try our sway and its configuration. Remember that <SUPER> + SHIFT + c does reload your configuration which is quite handy for trying things out. Also swaymsg can be used to send messages to sway, so for example the following command would set the scaling 2 for all output devices.

swaymsg output "*" scale 2

# Screen output resolution

I'm working with a HiDPI screen which was detected correctly in Sway. But I found the scaling factor of 2, which is the default in HiDPI screens, too much. Although it's not recommended to use float values here, I found a scaling factor of 1.3 to fit my needs, so I added this line to my config:

# Screen scaling (default is 2)
output eDP-1 scale 1.3

To list connected displays use swaymsg -t get_outputs which in my case now (after the change) shows as:

$ swaymsg -t get_outputs   
Output eDP-1 'Apple Computer Inc Color LCD 0x00000000' (focused)
  Current mode: 2560x1600 @ 59.972000 Hz
  Position: 0,0
  Scale factor: 1.300000
  Scale filter: linear
  Subpixel hinting: unknown
  Transform: normal
  Workspace: 4
  Max render time: off
  Adaptive sync: disabled
  Available modes:
    2560x1600 @ 59.972000 Hz

# Background images with automatic rotation

I like to have randomly chosen background images that change every now and then. Sway makes it easy to implement this. I added the following line to my config which executes a script during start.

exec --no-startup-id ~/.local/scripts/sway-rotate-bg-image.sh

The script itself doesn't do much. It lists all files in a certain folder, shuffles the names and selects one of them. It updates the background on all attached displays image via swaymsg and then pauses for 300 seconds (=5min) before the next iteration.

#!/usr/bin/env bash
IMG_DIR=~/wallpapers
while true;
do
    IMG=`ls -t1 $IMG_DIR/* | shuf | head -n 1`
    swaymsg output "*" bg $IMG fill; sleep 300
done

# Detect idle state and lock screen

To automatically lock the screen there are luckily two programs available that do the heavy-lifting. They are called swayidle and swaylock and can be installed in Arch via:

yay -S swayidle swaylock

The following script is taken from the swayidle manpage:

This will lock your screen after 300 seconds of inactivity, then turn off your displays after another 300 seconds, and turn your screens back on when resumed. It will also lock your screen before your computer goes to sleep.

To make sure swayidle waits for swaylock to lock the screen before it releases the inhibition lock, the -w options is used in swayidle, and -f in swaylock.

exec --no-startup-id swayidle -w \
    timeout 300 'swaylock -f -c 000000' \
    timeout 600 'swaymsg "output * dpms off"' \
    resume 'swaymsg "output * dpms on"' \
    before-sleep 'swaylock -f -c 000000'

# Screen backlight keys

In my i3 setup I used xbacklight to change the backlight of my screen.

bindsym XF86MonBrightnessUp exec xbacklight +10
bindsym XF86MonBrightnessDown exec xbacklight -10

This of course won't work anymore in Wayland, so I replaced the old configuration with brightnessctl which needs to be installed first:

yay -S brightnessctl

The syntax of brightnessctrl differs slightly:

bindsym XF86MonBrightnessDown exec brightnessctl set 5%-
bindsym XF86MonBrightnessUp exec brightnessctl set +5%

# Keyboard bindings

For me one key on the keyboard is completely superfluous: capslock. In my systems I either deactivate this key or map it to ESC if possible. Having ESC closer to the home row is benefitial to me - especially when working a lot with vim. This can done with xkb_options.

Another interesting setting is the key repeat delay and rate, which can be tuned also quite easily.

input "type:keyboard" {
    # Capslock key should work as escape key
    xkb_options caps:escape

    repeat_delay 350
    repeat_rate 45
}

# SSH Agent

To automatically start the SSH agent I use a systemd service which is placed in ~/.config/systemd/user/ssh-agent.service:

[Unit]
Description=SSH key agent

[Service]
Type=simple
Environment=SSH_AUTH_SOCK=%t/ssh-agent.socket
# DISPLAY required for ssh-askpass to work
Environment=DISPLAY=:0
ExecStart=/usr/bin/ssh-agent -D -a $SSH_AUTH_SOCK

[Install]
WantedBy=default.target

Also configure the pam environment in ~/.pam_environment:

SSH_AUTH_SOCK DEFAULT="${XDG_RUNTIME_DIR}/ssh-agent.socket"

Then start the service and enable it for future system starts: systemctl --user enable --now ssh-agent.service.

# Exit script

Finally I updated my exit script that allows me to lock my screen, suspend, shutdown or reboot my machine, etc.

The Sway configuration looks as this

set $mode_system System (1) lock, (e) logout, (s) suspend, (h) hibernate, (r) reboot, (d) shutdown
mode "$mode_system" {
    bindsym l exec --no-startup-id ~/.local/scripts/sway-exit.sh lock, mode "default"
    bindsym e exec --no-startup-id ~/.local/scripts/sway-exit.sh logout, mode "default"
    bindsym s exec --no-startup-id ~/.local/scripts/sway-exit.sh suspend, mode "default"
    bindsym h exec --no-startup-id ~/.local/scripts/sway-exit.sh hibernate, mode "default"
    bindsym r exec --no-startup-id ~/.local/scripts/sway-exit.sh reboot, mode "default"
    bindsym d exec --no-startup-id ~/.local/scripts/sway-exit.sh shutdown, mode "default"
    # back to normal: Enter or Escape
    bindsym Return mode "default"
    bindsym Escape mode "default"
}

which utilizes the script located in ~/.local/scripts/sway-exit.sh

#!/bin/sh
lock() {
    swaylock -f -c 000000
}

case "$1" in
    lock)
        lock
        ;;
    logout)
        swaymsg exit
        ;;
    suspend)
        lock && systemctl suspend
        ;;
    hibernate)
        lock && systemctl hibernate
        ;;
    reboot)
        systemctl reboot
        ;;
    shutdown)
        systemctl poweroff
        ;;
    *)
        echo "Usage: $0 {lock|logout|suspend|hibernate|reboot|shutdown}"
        exit 2
esac

exit 0

# Notifications

In i3 I used dunst to handle notifications. Unfortunately it's not yet ready to work with wayland. I found mako to be a good replacement.
Using mako is dead simple. After installation an additional line exec mako in the sway config file does the job.

# Printer

CUPS was not properly started after the switch. What helped was to create the following configuration in /etc/systemd/system/cups.socket:

[Unit]
Description=CUPS Printing Service Sockets

[Socket]
ListenStream=/var/run/cups/cups.sock
ListenStream=0.0.0.0:631
ListenDatagram=0.0.0.0:631
BindIPv6Only=ipv6-only

[Install]
WantedBy=multi-user.target

Then enable/start the service with systemctl enable --now cups.service and make sure it started properly:

$ systemctl status cups.service
● cups.service - CUPS Scheduler
     Loaded: loaded (/usr/lib/systemd/system/cups.service; enabled; vendor preset: disabled)
     Active: active (running) since Sat 2020-11-28 21:21:45 CET; 4min 31s ago
TriggeredBy: ● cups.socket
             ● cups.path
       Docs: man:cupsd(8)
   Main PID: 55543 (cupsd)
     Status: "Scheduler is running..."
      Tasks: 1 (limit: 19070)
     Memory: 5.2M
     CGroup: /system.slice/cups.service
             └─55543 /usr/bin/cupsd -l

Nov 28 21:21:45 mali systemd[1]: Starting CUPS Scheduler...
Nov 28 21:21:45 mali systemd[1]: Started CUPS Scheduler.

Automate tasks with docker-compose and systemd

2020-05-22T15:57:00Z

I have several tasks encapsulated in Docker containers which I need to trigger on a regular basis. The scheduling can be done with systemd. Both tools together have proven to be a quite powerful combination.

# Example: Backup service

Let's assume we have a backup service that lives in /opt/backup. This folder contains a docker-compose.yaml file which contains the configuration of a backup service.

We need one systemd unit for the service and one for the timer. Both are shown below. The setup is pretty straight forward.

# /etc/systemd/system/backup.service
[Unit]
Description=Backup service
Requires=docker.service
After=docker.service

# Send mail in case of an error
OnFailure=status-email-user@%n.service

[Service]
WorkingDirectory=/opt/backup

ExecStart=/usr/local/bin/docker-compose up

[Install]
WantedBy=multi-user.target

# /etc/systemd/system/backup.timer
[Unit]
Description=Timer for backup service
Requires=backup.service

[Timer]
Unit=backup.service

# Time to wait after booting before we run first time
OnBootSec=10min

# Define a calendar event (see `man systemd.time`)
OnCalendar=*-*-* 03:00:00 Europe/Berlin

[Install]
WantedBy=multi-user.target

The service unit has a OnFailure hook which runs a separate systemd service in case the exit code of the service is non-zero. This idea comes from the Arch Wiki.
The status email service can be configured in a generic way such that it can be re-used in other systemd services as well.

# /etc/systemd/system/status-email-user@.service
[Unit]
Description=Status Email for %i to user

[Service]
Type=oneshot
ExecStart=/usr/local/bin/systemd-email email@example.com %i
User=nobody
Group=systemd-journal

/usr/local/bin/systemd-email:

#!/bin/sh

/usr/bin/mail -Ssendwait -t <<ERRMAIL
To: $1
From: Monitor (systemd) <alert@example.tld>
Subject: $2
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=UTF-8

$(systemctl status --full "$2")
ERRMAIL

Finally the timer needs to be started/enabled as usual:

systemctl start backup.timer
systemctl enable backup.timer

# Show logs

Logs can be read with journalctl:

journalctl -b -u backup.service

Nextcloud: Migrate from SQLite to PostgreSQL

2020-05-11T11:28:00Z

I recently changed the database in my personal Nextcloud instance. Migrating databases is quite simple and took about an hour in my instance (~70GB of data). I noticed a huge improvement in speed when using the PostgreSQL database – especially when it comes to concurrent access of multiple users.

I'm running Nextcloud in a Docker container, so this post describes the procedure when using Docker containers. It might be slightly different in cases where Nextcloud has been directly installed to the server.

I have done the migration in Nextcloud 18.0.4. Older or newer versions might differ, so please be careful.

# Step 0: Preparations

Make sure to backup old data – especially the SQLite database file. In case the migration will fail your original database will be left untouched, but still it's good to have a backup in case things are messed up accidentally.

# Step 1: Stop routing traffic to the Nextcloud instance

Make sure to stop any traffic to the instance. In my case I disabled the rules in the reverse proxy which sits in front of my Nextcloud application, so that the instance does not receive any traffic and thus updates/modifications during the migration phase.

# Step 2: Add database to `docker-compose.yaml`

Let's first add a database to our docker-compose.yaml. The sample below shows the absolute minimum configuration to run a Nextcloud instance with a PostgreSQL database.

services:
  nextcloud:
    image: "nextcloud:18.0.4"
    ports:
      - "8080:80"
    volumes:
      - ./data:/var/www/html
  db:
    image: postgres:9.6
    environment:
      - POSTGRES_USER=ncuser
      - POSTGRES_PASSWORD=secret
      - POSTGRES_DB=ncdb
    volumes:
      - ./db:/var/lib/postgresql/data

After the db service has been added make sure to start the database by running docker-compose up.

# Step 3: Run migration

Now comes the database migration. Fortunately the Nextcloud developers provide a tool for that which can be used to essentially make this a one-liner.

First connect to the running nextcloud instance as user www-data

$ docker-compose exec -u www-data nextcloud bash

then run the database conversion script

$ ./occ db:convert-type --port 5432 --all-apps --clear-schema pgsql ncuser db ncdb

The arguments ncuser (database user) and ncdb (database name) might differ depending on your setup. The tool will interactively ask for your password and once it has access start the migration. Both flags --all-apps and --clear-schema are optional. The first one define if database tables of deactivated apps should be migrated or not. The second one drops tables in the target database in case they have been existing before the migration.

This might take a while. For me it took under an hour for a Nextcloud instance that has a size of about 70GB.

# Step 4: Confirm that config has been updated

After the database has been migrated successfully check that the config file has been updated properly. It should list dbtype as pgsql and have values for the properties dbname, dbhost, dbuser and dbpassword. This can be checked with the following command:

$ docker-compose exec -u www-data nextcloud bash -c "cat /var/www/html/config/config.php | grep db[a-z]"

Restart the service once so that the new config is read and applied.

$ docker-compose restart

# Step 5: Start routing traffic to the Nextcloud instance

You can now start to route traffic again to the instance. Now the PostgreSQL database will be used instead of the SQLite database file. Well done! :)

Webcam Support in Arch Linux on Macbooks

2020-04-01T18:07:00Z

I'm running Manjaro Linux (an Arch Linux derivative) on my MacbookPro12,1 (Retina, 13-inch, Early 2015).

Image source: support.apple.com, 2020/04/01

I was astonished how many things just worked out-of-the-box. Kudos to the Arch and Manjaro teams for their great work!

Unfortunately one thing that did not work out-of-the-box is the webcam. It requires a driver that is not part of the official kernel but must be installed separately.
The code is in the Arch User Repository (AUR). I use yay to install packages from AUR which I can highly recommend and which I will use in this article.

Let's get our hands dirty!

First let's get information about all installed kernels and see if the headers are installed for all of them:

$ pacman -Q | grep -E 'linux[0-9]+'  # see which kernels are installed

For each installed linux kernel there should be a -headers file next to it, e.g.

linux419 4.19.113-1
linux419-headers 4.19.113-1
linux54 5.4.28-1
linux54-headers 5.4.28-1
linux55 5.5.13-1
linux55-headers 5.5.13-1

If the headers are missing, make sure to install them:

$ yay -S linux-headers   # select the headers that are missing

Then let's get the driver and install it from AUR:

$ yay -S bcwc-pcie-git

After the installation we need to load the kernel module:

$ modprobe facetimehd

You can test if the video source has been identified correctly by executing v4l2-ctl --list-devices. If everything worked out, the output should be:

Apple Facetime HD (PCI:0000:02:00.0):
	/dev/video0

If the output is instead No /dev/video0 device, then make sure to unload the kernel module bdc_pci as described in the wiki. This can be done by running the following commands:

modprobe -r facetimehd  # temporary remove facetimehd module
modprobe -r bdc_pci     # remove disturbing kernel module

# make sure that the disturbing kernel module is blacklisted
# and cannot be loaded as a dependency of another module.
echo "blacklist bdc_pci\ninstall bdc_pci /bin/false" > /etc/modprobe.d/bcwc-pcie.conf

modprobe facetimehd     # load the facetimehd module again

The webcam should work now. You can test it on any website that requires a webcam, such as meet.jit.si. Have fun!

OfflineIMAP + systemd timer

2020-04-01T14:38:00Z

I use mutt to manage my emails and access them from the terminal. To sync the mails between the IMAP server and mutt I use OfflineIMAP.

To regularly check for new mails I use a systemd timer. The necessary files can be found in the official repository.

Let's clone the repository first and copy all necessary files to /etc/systemd/user.

cd /tmp
git clone --depth 1 git@github.com:OfflineIMAP/offlineimap.git  # a shallow copy is enough
cd offlineimap/contrib/systemd
sudo cp *.{service,timer} /etc/systemd/user/
systemctl --user daemon-reload
cd /tmp && rm -rf /tmp/offlineimap

By default the update interval of the timer is set to 15min. I preferred an update interval of 5 min, which can be easily accomplished.

$ systemctl --user edit offlineimap-oneshot.timer

In this file values can be overwritten as such:

[Timer]
OnUnitInactiveSec=5min

Finally it's time to enable and start the timer.

$ systemctl --user enable offlineimap-oneshot.timer # autostart at boot
$ systemctl --user start offlineimap-oneshot.timer  # start now

Let's check if the timer is up and running. systemctl --user status offlineimap-oneshot.timer should show that it is up and running and tell how many time is left until the next run:

● offlineimap-oneshot.timer - Offlineimap Query Timer
     Loaded: loaded (/usr/lib/systemd/user/offlineimap-oneshot.timer; enabled; vendor preset: enabled)
    Drop-In: /home/dotcs/.config/systemd/user/offlineimap-oneshot.timer.d
             └─override.conf
     Active: active (waiting) since Wed 2020-04-01 16:27:20 CEST; 8min ago
    Trigger: Wed 2020-04-01 16:37:34 CEST; 1min 55s left
   Triggers: ● offlineimap-oneshot.service

i3 + termite + custom themes = <3

2020-04-01T09:36:00Z

I love the sun – especially in Spring when temperatures are not too hot and taking a sunbath is a real pleasure. And I love working in the sun, which is often a challenge because of the poor contrast - especially if, like me, you like to use dark themes.

Since I recently changed to i3 as my window manager, I use termite as my default terminal which has by default no simple way to switch themes – at least not to my knowledge. But working in the sun requires a light theme, so I decided to work on a simple theme changer that is also integrated in i3.

So let's get to work.

First we need custom color themes. I have taken the two themes from this GitHub repository and put them in ~/.config/termite/themes. Of course you can also define custom themes and put them there. The theme files should have the following format:

[colors]
foreground = #000000
background = #ffffff
# and so on

Then copy your current ~/.config/termite/config to ~/.config/termite/config.base and remove the colors section.

We will now need a way to append a theme file to the config.base file. I've created a simple script that does this job:

#!/usr/bin/env bash

USAGE="$0 light|dark"

TERMITE_CONF_FOLDER=~/.config/termite
THEME=$1

change_theme() {
    echo "# THIS FILE HAS BEEN AUTOGENERATED. DO NOT CHANGE MANUALLY!" | \
        cat - /tmp/termite-switch-theme.hint $TERMITE_CONF_FOLDER/config.base $TERMITE_CONF_FOLDER/themes/$1 > $TERMITE_CONF_FOLDER/config
    killall -USR1 termite || true
}

case $THEME in
    light|solarized-light) change_theme solarized-light ;;
    dark|solarized-dark) change_theme solarized-dark ;;
    *) echo $USAGE ;;
esac

Put this script somewhere and make it executable. In my case I've saved the file in ~/.local/scripts/termite-switch-theme.sh. You should be able to switch themes now by calling the script, e.g. ~/.local/scripts/termite-switch-theme.sh light.

This is already great, but for a perfect integration with i3 put these lines into your ~/.config/i3/config:

set $mode_term_theme Terminal theme (l) light, (d) dark
mode "$mode_term_theme" {
    # choose which theme should be applied by pressing l (light) or d (dark)
    bindsym l exec --no-startup-id "~/.local/scripts/termite-switch-theme.sh light", mode "default"
    bindsym d exec --no-startup-id "~/.local/scripts/termite-switch-theme.sh dark", mode "default"
    # back to normal: Enter or Escape
    bindsym Return mode "default"
    bindsym Escape mode "default"
}
bindsym $mod+t mode "$mode_term_theme"

Finally reload the i3 config with mod+shift+c. Now you can change themes with mod+t and choose which theme should be applied (light or dark). All running terminals will immediately reflect your theme changes. Have fun!

# Base16 Themes

If you want to have even more themes to choose from take a look at the wonderful repository khamer/base16-termite. Those themes are working out-of-the-box just as described above.

# Acknowledgements

Thanks to @BarbUk for helping me find the correct signal to send to running termite instances.
Thanks to @nils-braun for reviewing this article.

Spotify Desktop App with HiDPI Scaling in GNOME 3

2019-11-12T16:45:00Z

When using a HiDPI screen, such as the MacBook Pro with Retina display, Spotify Desktop may not automatically detect the correct scaling factor. I have noticed issues in my Manjaro installation that runs with GNOME 3 Desktop. Spotify has been installed from the AUR package repository.
The wrong scaling factor leads to very small font size that is hard to read.

$ spotify --version
Spotify version 1.1.10.546.ge08ef575, Copyright (c) 2019, Spotify Ltd

Fortunately there is an option to force Spotify Desktop to use a (hardcoded) scaling factor. Use the following command and vary the scaling factor in order to find the correct factor first:

$ spotify --force-device-scale-factor=2

After the correct value has been found, adjust the desktop entry that GNOME uses to when starting the application via its launcher. The file lives in /usr/share/applications/spotify.desktop:

[Desktop Entry]
Type=Application
Name=Spotify
GenericName=Music Player
Icon=spotify-client
TryExec=spotify
Exec=spotify --force-device-scale-factor=2 %U
Terminal=false
MimeType=x-scheme-handler/spotify;
Categories=Audio;Music;Player;AudioVideo;
StartupWMClass=spotify

An "open" command in Linux

2019-11-11T21:03:00Z

As a previous macOS user I am used to call open <anything> in a terminal to open the current file or folder with the standard tool. So open . would open the current folder while open file.pdf would open the PDF in the default PDF viewer.

Today I learned that xdg-open serves the same purpose on the Linux desktop. So I configured an alias, to have the same behavior on both systems:

alias open="xdg-open &>/dev/null"

# Query and set defaults

xdg-open is part of xdg-utils, which describes itself as

xdg-utils is a set of tools that allows applications to easily integrate with the desktop environment of the user, regardless of the specific desktop environment that the user runs.

It's super convenient to change the default application for a given file, say a PDF. Let's change the default program:

First let's assume we don't know the mime-type of the PDF, which is application/pdf. We can find this type by query all known filetypes:

$ xdg-mime query filetype /path/to/a/file.pdf 
application/pdf

Let's see which default application is currently configured for this filetype:

$ xdg-mime query default application/pdf
chromium.desktop

If we don't want to use chromium to read PDFs, but for example zathura we can change the default application as follows:

$ find /usr/share/applications/ -name '*zathura*'
/usr/share/applications/org.pwmt.zathura-pdf-mupdf.desktop
/usr/share/applications/org.pwmt.zathura.desktop

$ xdg-mime default org.pwmt.zathura.desktop application/pdf

$ xdg-mime query default application/pdf
org.pwmt.zathura.desktop

Now PDF files are opened with zathura when using our new open command as defined above.

A lot programs bring their own .desktop file(s). Files are listed in /usr/share/appliations and can be overridden or suplemented with files located in ~/.local/share/applications. The specification of those files can be found here.

GNOME 3: Change wallpaper periodically

2019-11-10T18:52:00Z

If you are using GNOME 3 and you want to periodically change your desktop background using images in a folder, this is for you.

Put this script somewhere and make it executable.

#!/usr/bin/env bash

# You need to adjust this path
WALLPAPER_FOLDER=/path/to/your/wallpaper/folder

# https://askubuntu.com/a/1073769/277761
PID=$(pgrep gnome-session | tail -n1)
export DBUS_SESSION_BUS_ADDRESS=$(grep -z DBUS_SESSION_BUS_ADDRESS /proc/$PID/environ|cut -d= -f2-)

FILE_PATH=`ls $WALLPAPER_FOLDER | shuf -n 1`
FILE_URI="file://$WALLPAPER_FOLDER/$FILE_PATH"
/usr/bin/gsettings set org.gnome.desktop.background picture-uri $FILE_URI

Then create a cronjob that executes this script periodically, e.g. by using this line:

*/10 * * * * /path/to/your/file.sh

And you're done. Have fun!

Leaving Google's Mail Services behind

2019-11-01T13:55:00Z

I decided to leave Google's e-mail services behind. This post describes how I changed to another provider and how I use my domain at the same time so that I can switch between e-mail providers more flexibly in the future.

A year ago I decided to leave Google and their e-mail services behind. The reason that lead to this decision was that Google decided to kill Inbox - my absolute favorite e-mail service. They did it although many people loved Inbox and after operating the service for about 4 years.
Inbox was simply fabulous. It had a clean web based UI, came with powerful features, such as auto-grouping related e-mails into a thread, marking e-mails as done, so that one could establish a zero-inbox pattern, a very well working Android app, and so on. Thinking about the shutdown of Google Inbox still makes me sad. Google killed the better of their e-mail services and forced users migrate back to GMail, the much more bloated and annoying variant of the two services. In my opinion Google should have killed GMail instead and migrate their users to Inbox. But they have decided differently any many people followed their decision - many, but not me.

Since last year I have had quite a journey to switch e-mail providers. I decided to never use an e-mail address again that is not tied so a domain I do own – simply because it's a mess to change the e-mail address for hundreds of accounts.
Let me explain my steps to migrate to another e-mail service.

# Step 1: Choose an alternative

There are countless alternatives on the market – many of those belong to larger companies, such as Microsoft. I did not want to move from one web-giant to another, so I decided to use a smaller provider. First I thought about hosting e-mails on my own server, but I quickly got rid of this idea because I cannot guarantee uptime and hosting an own e-mail server comes with its own difficulties.
And I wanted a mail provider that does cost money. Why you ask? Because hosting e-mails and providing a proper e-mail service is serious business. E-mails contain a lot of personal and sensitive information. If the service is free then users are the product – Inbox was no exception to this, GMail is neither. In my opinion it is worthwhile to think very carefully about this topic, because most mails are not encrypted, often they contain sensitive information, such as information about accounts, bank credits, shopping information that helps to create an exact profile of the user. E-mails are a paradise for data scientists.

What I needed instead was an e-mail provider that fulfills these requirements:

Reliability: The service should have a history in which it worked flawlessly. No profile building, no ads: I want to have a service that does not scan my e-mails (automatically) and display ads based on the content. I don't want to see any ads at all. I do not want the e-mail provider to use my data for a kind of profile which is then sold to third parties – even if this is only done via an advertising network.
Secure authentication: I want the service to provide a two-factor authentication. This way an attacker does not only need my username and password combination, but also needs to have physical access to my second device. This should be standard nowadays.
Custom domain: I want to use a custom domain, so the mail provider must provide support for e-mail aliases.
GDPR compliance: Since we have this wonderful EU General Data Protection Regulation (GDPR) in place, I want the service to be hosted in such a way that it is compliant with GDPR. Actually most services are compliant, but I favor services that are also hosted in the EU.

In the end I came up with two providers that looked very interesting: mailbox.org and posteo.de. In my opinion both providers are absolutely worth their money. One of the main differences is how custom domains are handled. While Posteo does not allow for custom domains by default, Mailbox is fine with it. Posteo has a good point of not allowing it. Their FAQ explains why they have decided against custom domains:

Can I use Posteo with my own domains?

No. We are an email provider with a particular, privacy-oriented model – and this is not compatible with incorporating own domains. [...] Even if only the MX record pointed to us, we would still need to store the assignment of the domain in your Posteo account as user information. Thus we would possess your user information and be required to give it out. For this reason, we have decided not to offer this possibility and instead to use data economy. [...] In order to be able to read replies to these messages, you need to set up forwarding to Posteo for the external address.

Source: https://posteo.de/en/site/faq

A year ago I have decided to use Posteo. Nowadays I think it would have been better to go with Mailbox, because of the custom domain problem. I am not so sure if Mailbox allowed setting up custom domains a year ago, but since Mailbox natively allows for custom domains nowadays, I guess it makes much more sense to use their offering. I will outline below why this is important.

Image of the posteo landing page

Creating an account is straight-forward, I will not explain how to do that. Maybe it is worth mentioning that Posteo does provide a large amount of anonymity. It does allow to create new accounts without the need to enter any personal details. Also the payment does work anonymously.

# Step 2: Setup with custom domain

As mentioned in the requirements I want to use a custom domain for my mails. Doing so has several advantages and one major disadvantage.
Advantages are that you can have as many e-mail addresses as you like. @yourdomain.com is possible, so you have a lot of freedom here. Another advantage is that those mail addresses are independent of the mail provider, which means that I can, at some point in time, if I am no longer satisfied with Posteo, simply change the e-mail provider and do not have to inform others that my mail address has changed. Isn't that great?! I mean changing e-mail addresses is a tedious task – it took me several days to update my e-mail address in all the services that I use.
Instead of changing mail addresses one could also forward e-mails from one provider to the other. Although it would be simple and can be configured in most e-mail services, including GMail. Why? Because it would still route mails via Google – all my mails. And Google will for sure analyze those mails. So I would not have won anything. Instead I take the burden once in my life, change my mail address and then stick to it. This is possible – but only if the domain is yours. Which brings me to the major disadvantage: You have to own this domain. Forever – or at least as long as somebody sends mail to this domain that should be kept private. You must make sure, that this domain never gets lost. Most domain providers do provide options here, so please check carefully. If you lose the domain, it could be registered by someone else who sets their own MX records and receives mails intended only for your eyes. So be warned!

# Step 3: Set up e-mail forwarding

This step is only needed if the mail provider does not allow for custom domains (e.g. Posteo). In case the mail provider does allow for custom domains (e.g. Mailbox) this step is not necessary.

I have bought my domain at GoDaddy, one of the very large domain registrars out there. GoDaddy offers up to 100 e-mail forwarding configurations per domain for free.

GoDaddy offers 100 e-mail forwarding configurations for free.

Configuration is simple. Just configure the e-mail address, that the service should listen to, e.g. foobar@domain.tld and enter the mail address that it should be forwarded to, e.g. mymailaddress@posteo.de. This way you can configure up to 100 different e-mail addresses that can later be used as aliases for your one e-mail address at Posteo.

Configuration dialog at GoDaddy

DNS records are typically managed by the registrar itself – in my case GoDaddy. I manage my DNS records via Cloudflare, but this is absolutely not necessary. The following is still valid for GoDaddy or any other service that is used to manage DNS entries. The UI might look different though.

# Step 4: Set up MX, SPF and DMARC records

The missing pieces are now setting up MX records, SPF and DMARC. The first one is necessary so that other e-mail servers know which inbox server they should use to deliver the messages to. SPF and DMARC are necessary, so that sent mails can pass the SPAM rules of various mail providers.

Let's talk about the MX records first. Each e-mail provider does have some servers that are responsible for receiving mails from other services. Since Posteo does not allow for custom domains I have to use the inbox servers from GoDaddy and forward mails to Posteo. If you go with Mailbox, please enter the Mailbox MX records instead.

To find out GoDaddy's MX records go to the Workspace Control Center, this is where you can configure your e-mail records for your domain. Then go to Tools → Server Settings. Here you can see the MX records and their priorities that are to be set up.

MX records as needed to work with GoDaddy.

Finally use these MX records to change the DNS records of your domain:

DNS settings: MX records and SPF and DMARC settings.

The image above also shows the SPF record, which I set to v=spf1 include:posteo.de -all. We basically inherit the SPF record of posteo.de and advice that other mail providers should strictly reject messages that do not pass the SPF test (-all flag).
In case you are interested in SPF record configuration and want to learn more about SPF records, I can recommend spfwizard.net. Posteo recommends at least to have the include:posteo.de part, their recommendation can be found here.

Configuration as shown by spfwizard.net.

Please note that if you use Mailbox, you might want to use the SPF record v=spf1 include:mailbox.org as described in their help pages.

I also configured DMARC with the value v=DMARC1; p=quarantine; rua=mailto:DMARC-report@dotcs.me; ruf=mailto:DMARC-forensic@dotcs.me; pct=100. A generator for the DMARC format can be found here.

I have not configured DKIM, because to my knowledge it is not possible in combination with Posteo as the mail provider.

# Step 5: Final checks

Last but not least, I want to make sure that our mails are delivered properly. What I found quite useful is to send a mail to GMail and check the mail headers there. The good news first: mails are delivered properly. Neither are they rejected, nor marked as SPAM. Our configuration seems to work – strike! Let's check in more detail if SPF and DMARC worked as expected.

The relevant part of the header is this one:

Authentication-Results: mx.google.com;
    spf=pass (google.com: domain of fantasymail@dotcs.me designates 185.67.36.142 as permitted sender) smtp.mailfrom=fantasymail@dotcs.me;
    dmarc=pass (p=QUARANTINE sp=QUARANTINE dis=NONE) header.from=example.com

We see that both SPF as well as DMARC tests passed. I have used that setup for quite some time now and I had never experienced any issues so far.

Evaluate data from Komoot with Elasticsearch and Kibana

2017-06-11T12:00:00Z

In my spare time I like to travel by bike. As a data enthusiast it's no question to track my trips using services such as Komoot. Besides the data analysis methods that these companies provide on their websites or apps, they are always limited in what kind of analysis they provide to their customers. Most of the time it is not enough tough. Having access to the raw data allows to do further analysis and implement highly customized charts.

# Table of content

For convenience this post is divided in five sections:

Preconditons
In this section the necessary tools are explained and installed on your developer machine.
Data mining
In this section the raw data is accessed which will be further analyzed in later steps. No advanced techniques are used for data mining as the focus in this post lies on the next two steps.
Data preprocessing
Using the raw data it's possible to pre-process them in order to simplify the data analysis in the next step. In this step the data will also be imported into the database.
Data analysis and visualization
Custom analysis and charts are implemented in this section.
Sumary and outlook
Having all the data available in Elasticsearch it's possible to do a lot more. This outlook will give a few more ideas what to do next.

Let's start!

# Preconditions

To analyze the data the so called ELK-Stack or Elastic Stack (Elasticsearch, Logstash, Kibana) is used.

An instance of Elasticsearch and Kibana is required to run on the machine in order to analyze the data later on. To simplify the setup process it's best to not install them directly on the development machine but use docker to manage the tools in a virtual environment. By doing so environments are separated, which means that it doesn't matter what operating system your working machine runs - Windows, macOS or Linux - and what versions of tools you have installed. The containers are isolated and describe their own system and dependencies. In case you haven't used docker yet, make sure to install docker first. To get started quickly I have created a dockerized environment that can be used directly.

# clone the Docker Elastic Stack description into the folder named komoot-analysis
git clone https://github.com/dotcs/komoot-elk-jupyter.git

cd komoot-elk-jupyter    # change to this directory
docker-compose up        # spin up the ELK stack and jupyter

docker-compose will spin up a network in which one instance of each, Elasticsearch, Logstash, Kibana and Jupyter, is running. They share the same network configuration so that they can talk to each other.

If everything went fine the following links should work on your developer machine:

Kibana: http://localhost:5601
Jupyter: http://localhost:8888

Note: On a Mac Docker runs inside VirtualBox. Typically the IP of this machine is not bound to localhost, so the links might not work. Make sure to use the correct one by running docker-machine ip default, where default is the name of the machine in my case.

Alright, we've finished the first step in which we spun up a complex development environment that allows for intensive data analysis. Now everything is set up to continue gathering the data. Let's move on!

# Data mining

While Komoot provides an experimental API, it seems impossible to create an application token without participating in some kind of beta program. My issue in the corresponding github project has not been answered yet, so a workaround is necessary to get access to the data.

Fortunately it's easy to call the official API endpoints from the browser when logged in into their web application, because it uses the very same API. For the necessary API call you must be aware of your personal USER_ID. It can be extracted from the link to the profile in the web application, which has the following schema: https://www.komoot.de/user/{USER_ID}. In my case it is 320477127324.

In order to fetch your latest data in JSON format, use the following URL: https://www.komoot.de/api/v007/users/{USER_ID}/tours/. Save the content of this file to jupyter-notebooks/data/komoot.json, it will be needed in the next step.

# Data sample

A single entry does look like this. Note that some information was omitted to save space and it's not used in this blog post anyway.

{
  _embedded: {
    tours: [
      {
        status: "public",
        type: "tour_recorded",
        date: "2017-06-03T14:14:37.000+02:00",
        name: "Runde an der Würm entlang",
        distance: 42980.88913041058,
        duration: 7660,
        sport: "touringbicycle",
        _links: {
          creator: { href: "http://api.komoot.de/v007/users/320477127324/profile_embedded" },
          self: { href: "http://api.komoot.de/v007/tours/17503590?_embedded=" },
          coordinates: { href: "http://api.komoot.de/v007/tours/17503590/coordinates" }
        },
        id: 17503590,
        changed_at: "2017-06-03T15:06:51.000Z",
        kcal_active: 880,
        kcal_resting: 154,
        start_point: {
          lat: 48.795956,
          lng: 8.85,
          alt: 426
        },
        elevation_up: 548.1543636580948,
        elevation_down: 544.0455472560726,
        time_in_motion: 7236,
        map_image: { /* ... */ },
        map_image_preview: { /* ... */ },
        _embedded: {
          creator: { /* ... */ },
          _links: { /* ... */ } },
          display_name: "Fabian Müller"
          }
        }
      },
      /* ... other tour entries ... */
    ]
  }
}

# Data preprocessing

To analyze the data it needs to be imported into Elasticsearch first. To do so I'll preprocess the data using Python with Pandas.

Hint: All steps in this section can be found in the Jupyter notebook located in jupyter-notebooks/komoot-elk-preprocessing.ipynb. I'll explain step by step what each step does. The repo comes with some sample data which is located in jupyter-notebooks/data/komoot-sample-data.json. In case you haven't dowwnloaded your own data you can also use this data to get started - although it's boring because it only comes with three entries. ;-)

Create a new notebook by opening Jupyter and click on New > Notebook > Python 3.

First all necessary modules need to be imported.

import json
import pandas as pd
from pandas import DataFrame, Series
from elasticsearch import Elasticsearch

Next a connection to Elasticsearch is established.

es = Elasticsearch(hosts=['localhost'], http_auth="elastic:changeme")
es.info()

Output:

{
    "cluster_name": "docker-cluster",
    "cluster_uuid": "Dvit14MhRgiEoTxfIkuzYA",
    "name": "osb6xs4",
    "tagline": "You Know, for Search",
    "version": {
        "build_date": "2017-04-28T17:43:27.229Z",
        "build_hash": "780f8c4",
        "build_snapshot": False,
        "lucene_version": '6.5.0',
        "number": '5.4.0'
    }
}

Now that the notebook and Elasticsearch are connected it's time to read the data in Python.

# Read data into python dictionary.
# Use `data/komoot-sample-data.json` in this call to read the sample data.
with open('data/komoot-sample-data.json') as data_file:
    data = json.load(data_file)
df = DataFrame(data['_embedded']['tours'])

First data is loaded into the data variable, then only the list of tours is loaded into a pandas DataFrame.

Before filling the data into the database it's worth to have a closer look into the data structure. Especially the start point of each tour seems to not match what Elasticsearch can work with. Elasticsearch defines so called geo_points, which are required to have a special format. To make use of geo_points the format needs to be transformed a bit. Let's first tell Elasticsearch that it should expect a geo_point here:

mapping = '''
{
  "mappings": {
    "tour": {
      "properties": {
        "start_point": {
          "type": "geo_point"
        }
      }
    }
  }
}'''
es.indices.create(index='komoot', ignore=400, body=mapping)

This piece of code creates an index called komoot in which it tells Elasticsearch to expect start_point to be of type geo_point. The document type is called tour in the index. It's necessary to transform the data before sending it to Elasticsearch:

df['start_point'] = df['start_point']\
    .map(lambda item: [item['lng'], item['lat']])

This transforms the start_point column from a dictionary into a list with two entries, longitude and latitude of the starting point of the tour.

Next the data can be send to Elasticsearch which will create the rest of the search index automatically based on the types of the input values.

for i, row in df.iterrows():
    res = es.index(index='komoot', doc_type='tour', id=item['id'], body=row.to_json())
    print(row['id'], res['result'])

# output
# 17342318 created
# 17140311 created
# 17069942 created

In the next steps Kibana is used to query data and generate some nice plots from it. The remaining thing to do is to tell Kibana that komoot is the preferred index. To do so go to Kibana, log in by using the default username (elastic) and password (changeme) which should bring you right to the Management tab in which a default index must be selected. In this screen use komoot as the index name, check that it contains time-based events and set the time-field name to be date. Click on create to configure the index pattern.

Kibana: Interface to configure an index pattern

# Data analysis and visualization

Now comes the most interesting part - the data analysis. In this section charts will be built using Kibana which accesses Elasticsearch to provide real-time charts. In our case the data is quite sparse compared to server logs, for which Kibana is developed and where you have up to hundreds of logs per second, but nevertheless we will create charts that will change over time, so it's nice to have them encapulated in Kibana. After creating such charts it's only necessary to push new data to Elasticsearch to get them updated. It's as easy as that. Okay, let's see how this works.

We'll create three types of visualizations in this tutorial:

A tile map of all recorded tours
A heatmap of distance over time
A bar chart of time in motion and elevation

Also a Dashboard containing all the previous charts will be created.

# Tile map of all recorded tours

Create a new visualization via Visualize > Plus Icon (Create new visualization) and choose Tile Map. On the next page select komoot as the index. This will create a map with no content yet.

To show a heatmap based on all recorded tours, choose Geo Coordinates as the bucket. This will automatically set up Geohash as the aggregation type and the field start_point since it is the only entry of type geo_point in the schema of the index. Click tab Options and choose Heatmap as the map type. Depending on the data it might be useful to fine-tune the parameters in this tab later on. To only select the recorded tours and ignore the planned ones, type type:tour_recorded into the search bar at the top of the screen. Make sure to choose a proper time span in the upper right corner (e.g. use the last two years or so). I cannot say how often I have forgotten to do so and wondered why there is no data to display. ;-)

Press the primary button ► (Apply Changes) to see your result. You might need to zoom in into the region where your data points are located. Et voilà there it is, enjoy your first heatmap!

Geo-Heatmap of recorded tours

Once you're satisfied with the result save the map by clicking on Save at the top of the screen and choose a proper name, e.g. "Komoot: Geo-Heatmap of recorded tours". This allows to add the chart to a dashboard later on.

# Heatmap of distance over time

Next let's see how well we are performing. I'd like to know how often I went for a ride and how long my rides are.

Go to Visualize > Plus Icon (Create new visualization) > Heat Map. Again select komoot as the index, and define a Y-Axis first. Choose Histogram as the aggregation type and choose distance as the field. Define a proper interval, for me 10000 or 10km per bucket worked fine. Click on Advanced and set {"order" : { "_key" : "desc" }} as the JSON input. This tells Kibana that the Histogram should be sorted by the key of each bucket. Click on Add sub-buckets to add a X-Axis and choose Date Histogram as the sub-aggregation type. Set date as the field and Monthly as the interval.

Again set type:tour_recorded into the search bar at the top of the screen to limit the results to the recorded tours.

Heat map: Distance (buckets) over the time (buckets)

Once you're satisfied with the result save the map by clicking on Save at the top of the screen and choose a proper name, e.g. "Komoot: Heatmap distance over time". This allows to add the chart to a dashboard later on.

# Bar chart of time in motion and elevation

Now let's see how the average time in motion varies over time and combine that with the average elevation in a given time interval.

Go to Visualize > Plus Icon (Create new visualization) > Vertical Bar. Select komoot as the index and set the X-Axis to be of type Date Histogram. Choose a Weekly interval. Then define two Y-Axis, where one has aggregation type Average and field time_in_motion. The other one is also of aggregation type Average but has set elevation_up as its field.

Then go to tab Metrics & Axes and add a second Y-Axis. Afterwards in section Metrics set both axis to type normal.

And once again do not forget to set type:tour_recorded in the search bar at the top of the screen to limit the results to the recorded tours.

This gives a nice bar chart that shows the average time in motion as well as the average upwards elevation. Seems like I have climbed a lot more in average in the last year than nowadays. Do I get old? ;-)

Bar chart showing average time in motion and average upwards elevation

# Dashboard

To sum up the visualization part let's create a dashboard. It will be used to display all charts side by side to each other. The main purpose is to interactively explore data. Since all visualizations are connected in the dashboard changing the search query or time interval will update all visualizations at once. Let's get started!

Click on Dashboard > Plus Icon (Create new dashboard) to create a new dashboard. Then click Add at the top of the screen and select the visualizations we have created before. You can re-arrange them as you like, also the size and position can be changed. To save the dashboard click Save on the top of the screen and give it a proper name, e.g. Komoot. I'd recommend to store the time by checking Store time with dashboard. This will update the currently selected time span each time the dashboard is opened.

Custom dashboard with custom visualizations in Kibana

Note that you can use the search query to further reduce the number of results. This will automatically update all charts which is super useful if you have a bunch of charts that you want to keep in sync.

Changing search queries will automatically update the visuals

# Summary and Outlook

Let's recap what we have done on our journey to create a dashboard with customized charts.

We used a dockerized environment to spin up containers which isolate Elasticsearch, Kibana and Jupyter. This technique in itself is very helpful to isolate environments and get rid of any dependency hell issues. I highly recommend to work in dockerized environments as these enviroments can be shared easily, such as I have done it by providing you a git repository that defines all the needed tools.

Data mining has been done by calling an API endpoint by hand and copying the data manually. Although it was simple to do, this is no good solution as it requires to manually log into a website to generate the necessary authentication credentials. I'd like to do better here, which means using the OAuth2 layer of the API, but currently this seems not possible. In case you can use an OAuth layer I'd recommend to do so, because by doing so the data can be requested automatically and therefore the database can be updated regularly with the latest and freshest data available.

For data preprocessing we have used Python and Pandas which I like to work with. Of course other tools are available and there is no need to use the tools I've used. Feel free to use whatever you like to work with. This section also gives a lot of freedom what to do next. Besides using Python only to preprocess data also analytics could be done here. As we have connected Python with the Elasticsearch database, it's easy to do queries here against the database. The elasticsearch module comes with a large API and you should definitively have a look what else it can do. We could also either visualize data directly in Python using matplotlib or other libraries or maybe do some kind of Machine learning. Sky is the limit at this point.

After we've put the data into the database we have analyzed it using Kibana, a powerful frontend for Elasticsearch. We have generated our own visualizations which are easy to create and highly dynamical. We have created a dashboard to group multiple charts and make them dependent on the same query and time interval. This can be a large time saver when digging into the data to gain more and more insights. If you want to show your work to others, note that dashboards can be shared. But note that sharing requires to have the stack running on a server, not only on your local dev machine.

I hope you enjoyed this article. Let me know if you have any questions or remarks.

Howto train a CNN with TensorFlow on FloydHub

2017-06-11T12:00:00Z

Nowadays artificial intelligence (AI) is a big thing. Computational power increased a lot within the last years which makes it possible to build and run large Neural Networks (NN) for Deep Learning (DL) that model various problems extremely well. Unfortunately huge networks consume large amounts of memory and computational power. Often developers do not have the necessary hardware available to solve larger problems in a suitable time. One solution to this problem is to shift the heavy computational task to the some cloud provider.

This article demonstrates how to solve the MNIST Digit Recognizer task by using a Convolutional Neural Net (CNN). It will be trained with TensorFlow, Google's open-source software library for Machine Intelligence. For the heavy-lifting the cloud provider FloydHub will be used.

This article uses the Kaggle Dataset for MNIST Digit Recognizer.

# Table of content

Why FloydHub
Quick overview of what FloydHub is and why I've chosen this provider.
CNN Architecture
Anwers how the CNN architecture does look like in more detail.
Get the code
Explains how to get the code to run get your own instance up and running.
Upload data to FloydHub
Explains how data can be transferred to FloydHub by using their CLI tool.
Run a Jupyter notebook
A Jupyter notebook will be started on FloydHub.
Train the CNN
Explains how the CNN can be trained in the cloud.

# Why FloydHub?

The aim of Floydhub is to be the "Heroku for Deep Learning". Given how easy it is to use Heroku for deploying websites, it seems a perfect candidate to focus on the things we really want to do - not to much on how to handle the infrastructure.

Obviously another reason is that if we need huge amounts of GPU power, FloydHub has us covered. They offer access to one Nvidia Tesla K80 graphic card with 12GB RAM per instance. And this GPU is a beast! It gives us the option to run even very deep and memory intensive networks. Also this provider offers a per second billing and auto-termination of jobs after they run. I think this setup is quite good and something I wanted definitively to explore.

This article assumes that you have created an account at FloydHub and installed their CLI tool. If you haven't please do so first and get 100 hours free GPU, which is more than enough to follow this article.

# CNN Architecture

Let's first talk about how our CNN will look like. We'll use a CNN with four convolution (CONV), two max-pooling (POOL) and two dense (or fully connected, FC) layers. The basic structure looks like this:

LAYER TYPE	F	S	PAD	FILTERS	X	Y	Z	MEM (COUNT)	MEM (SIZE IN KB)	WEIGHTS (COUNT)	USAGE OF WEIGHTS IN THIS LAYER
INPUT					28	28	1	784	0.7656	0	0.0000
CONV	3	1	1	64	28	28	64	50176	49.0000	576	0.0001
CONV	3	1	1	64	28	28	64	50176	49.0000	36864	0.0055
POOL	2	2			14	14	64	12544	12.2500	0	0.0000
CONV	3	1	1	128	14	14	128	25088	24.5000	73728	0.0110
CONV	3	1	1	128	14	14	128	25088	24.5000	147456	0.0220
POOL	2	2			7	7	128	6272	6.1250	0	0.0000
FC					1	1	1024	1024	1.0000	6422528	0.9598
DROPOUT
FC					1	1	10	10	0.0098	10240	0.0015
SOFTMAX

In total this CNN has around 6.7 million weights. As it's quite common in CNNs nearby all parameters (ca. 96%) are located towards the end of the network in the first FC layer. Note that after the first FC layer a dropout layer is used for regularization of weights. After the last FC layer a softmax function is used to squash values between 0 and 1 which allows to treat them as predictions.

The following meta parameters are used to train the CNN:

learning_rate = 0.001       # learning rate of the network
training_iters = 100000     # number of total iterations used for training
batch_size = 56             # number of samples in each training step
keep_prob = 0.75            # probability to keep nodes in DROPOUT layer

Alright, let's have some fun with this network and FloydHub.

# Get the code

To get the code please checkout this git repository. It comes with a dockerized environment which installs all necessary requirements, such as NumPy, SciPy, Jupyter and tensorflow. To get started make sure to have docker installed.

If you don't want to use the dockerized setup, feel free to use the notebooks directly from the notebooks directory. But be aware that in this case you have to adjust the INPUT_ROOT and OUTPUT_ROOT paths in the floydhub-simple-cnn.ipynb file. You then also have to install all requirements on your own.

git clone https://github.com/dotcs/mnist-cnn-floydhub  # clone git repository to local machine
cd mnist-cnn-floydhub      # change into the cloned directory
sh extract-zip-locally.sh  # extract zipped training and test data to ./input folder
docker-compose up          # start the dockerized environment

Now a local instance of Jupyter should run on localhost:8888. You can use that to discover how the CNN is written using TensorFlow. The corresponding notebook is called floydhub-simple-cnn.ipynb and will be listed in Jupyter. But beware, training could take forever depending on your machine. This is exactly why we need to do computation in the cloud. Let's go ahead!

# Upload data to FloydHub

To access data in a VM in FloydHub it must be transferred to the cloud first. FloydHub provides commands via their CLI to upload large datasets from our machine to their cloud. A zipped version of the train and test datasets is placed in the floydhub-zip folder.

cd floydhub-zip                # change into folder that contains the zipped data
floyd data init mnist.zipped   # create a new data resource on FloydHub
floyd data upload              # upload the data to that resource

You should now the following output in your terminal, which tells you that 14.7 MB of data are transferred to FloydHub. It also gives you access to the DATA_ID after the upload has been finished.

Compressing data ...
Creating data source. Total upload size: 14.7MiB
Uploading compressed data ...
Done=============================] 15378159/15378159 - 00:00:00
Upload finished
DATA ID                 NAME                    VERSION
----------------------  --------------------  ---------
P3WVS2Vswo2o66pMsbPMTC  dotcs/mnist.zipped:1          1

In the summary table the DATA_ID is listed. In my case is P3WVS2Vswo2o66pMsbPMTC but is different in your case. Make sure to copy this ID as we'll need it in a second.

What we have transferred to FloydHub is a zipped copy of our data. This is not exactly what we want to work with, so let's unzip it first on the remote machine. We do so by switching to the floydhub-unzip folder. Since unzipping is a one-time-step I suggest to create a new project for this task only. This keeps the logs and version control of other projects that you have on FloydHub clean.

By running a shell command with the --data flag, we specify the data-container that should be mounted to the /input path of the container in which the shell command (or later the tensorflow environment) runs.

cd floydhub-unzip
floyd init mnist.unzipped
floyd run --data P3WVS2Vswo2o66pMsbPMTC "unzip /input/train-test.zip -d /output"

You should now see again some output that looks like this:

Creating project run. Total upload size: 282.0B
Syncing code ...
Done=============================] 1027/1027 - 00:00:00
RUN ID                  NAME                      VERSION
----------------------  ----------------------  ---------
b8yRAXUGMnhoTbrVvPMfCX  dotcs/mnist.unzipped:1          1


To view logs enter:
    floyd logs b8yRAXUGMnhoTbrVvPMfCX

This time our task runs asynchronously - again on a fresh VM. At some point in the future the command will finish its work and the container will automatically be shut down by FloydHub. To see what happens behind the scene it's possible to either check the dashboard or run the floyd logs <RUN_ID> command that was referenced in the output.

In my case the output is:

2017-06-11 06:25:12,073 INFO - Preparing to run TaskInstance <TaskInstance: dotcs/mnist.unzipped:1 (id: NQpRp4cU3srGWtMK33voLJ) (checksum: 9d9d607a8649c3ab0d9d69489f2d8fc4) (last update: 2017-06-11 06:25:12.065267) [queued]>
2017-06-11 06:25:12,085 INFO - Starting attempt 1 at 2017-06-11 06:25:12.077828
2017-06-11 06:25:12,322 INFO - Executing command in container: stdbuf -o0 sh command.sh
2017-06-11 06:25:12,322 INFO - Pulling Docker image: floydhub/tensorflow:1.1.0-py3
2017-06-11 06:25:13,506 INFO - Starting container...
2017-06-11 06:25:13,740 INFO -
################################################################################

2017-06-11 06:25:13,741 INFO - Run Output:
2017-06-11 06:25:13,792 INFO - Archive:  /input/train-test.zip
2017-06-11 06:25:15,027 INFO - inflating: /output/train.csv
2017-06-11 06:25:15,896 INFO - inflating: /output/test.csv
2017-06-11 06:25:15,942 INFO -
################################################################################

2017-06-11 06:25:15,943 INFO - Waiting for container to complete...
2017-06-11 06:25:16,287 INFO - [success] Finishing execution in 4 seconds for TaskInstance <TaskInstance: dotcs/mnist.unzipped:1 (id: NQpRp4cU3srGWtMK33voLJ) (checksum: 9d9d607a8649c3ab0d9d69489f2d8fc4) (last update: 2017-06-11 06:25:16.278860) [success]>

Here we see that two files have been written to the /output folder: train.csv and test.csv. And again this data-container gets an ID assigned which can be used as the input container for another runtime container.

To get the ID of all containers there run the command floyd data status. This gives us a list of all data-container, where the unzipped one has the DATA_ID NQpRp4cU3srGWtMK33voLJ in my case.

DATA ID                 CREATED        DISK USAGE    NAME                                                VERSION
----------------------  -------------  ------------  ------------------------------------------------  ---------
NQpRp4cU3srGWtMK33voLJ  2 minutes ago  121.97 MB     dotcs/mnist.unzipped:1/output                             1
P3WVS2Vswo2o66pMsbPMTC  5 minutes ago  14.66 MB      dotcs/mnist.zipped:1                                      1

Finally we do have transferred and extracted our data completely. Since we don't need the zipped file anymore we can get rid of it: floyd data delete P3WVS2Vswo2o66pMsbPMTC.

Now it's time to move to the more interesting tasks. Let's get the CNN up and running.

# Run a Jupyter notebook

Multiple options exist how to run code at FloydHub using the CLI. Here I have written code as a Jupyter notebook which makes it necessary to run floyd with the flag --mode jupyter. To run a Jupyter notebook we first have to create another project which will be solely used for Jupyter. This spins up a fresh new VM for us. By using the --gpu flag we get access to an Nvidia Tesla K80 GPU with 12 GB of RAM. Can you already feel the power? ☺

cd notebooks
floyd init mnist-cnn-floydhub
floyd run --gpu --mode jupyter --env tensorflow --data NQpRp4cU3srGWtMK33voLJ

It might take a while to spin up the VM and the output should look like this. Note that in the first step around 21kB of data is uploaded to the VM. This is our Jupyter notebook, so we can access it directly in the upcoming VM. Because files that are uploaded in this step cannot exceed a given file size limit is is necessary to upload larger files with the floyd data CLI command as we have seen before.

Creating project run. Total upload size: 21.4KiB
Syncing code ...
Done=============================] 22993/22993 - 00:00:00
RUN ID                  NAME                          VERSION
----------------------  --------------------------  ---------
bViegecgc6cWncp9vmJRjG  dotcs/mnist-cnn-floydhub:1          1

Setting up your instance and waiting for Jupyter notebook to become available ......................

Path to jupyter notebook: https://www.floydhub.com:8000/XP3WqMBjV3meJejcmMhW66

To view logs enter:
    floyd logs bViegecgc6cWncp9vmJRjG

# Train the CNN

We have waited long enough to train our CNN, now we can do so. Go to the URL provided in the command above, in my case https://www.floydhub.com:8000/XP3WqMBjV3meJejcmMhW66, and select the notebook floydhub-simple-cnn.

Select Cell > Run All to run the notebook and wait for the CNN to be trained. After is has finished make sure to close the machine via the dashboard. Jupyter notebooks run infinitely and are not shut down automatically so make sure to shut it down yourself when you're finished. It should like this after shutdown:

Correctly shutdown Jupyter notebook on FloydHub

While the notebook is executed several files are written to the /output directory of this VM. After training the trained model is written to /output/model.ckpt. Predictions on the Kaggle test dataset are written to /output/prediction.csv.

To download the data go to floydhub.com/data and click on the arrow in the View column. This opens a detail view on the right which allows you to either browse the data (and download only parts of it) by clicking on the open button or download all the data as a tar-archive by clicking on the download button.

Data export on FloydHub

And by the way, predictions of this CNN yield an accuracy of 0.95071 on the Kaggle Leaderboard, which means that about 5 out of 100 images are classified wrongly. This is a not too good result actually, but also not too bad. Now it's up to you to use the power of this setup to yield a better result! Have fun!

blog.dotcs.me

Blending Databases with FastAPI and SQLAlchemy

# Introduction

# Code

# Conclusion

Combining FastAPI Dependency Injection with Service and Repository Layers

# Starting Point

# Building the Dependency Injection Chain

# Testing

# Vizualizing the Dependency Injection Chain

Crawler for German Bank Insitutes

Copy large files to Nextcloud

Privacy and Audible Audiobooks

# Downloading Audiobooks

# Convert from aax to m4a

# Listen on Android

# Mission completed 🥳

Pi-hole User Group Management

# Pi-hole Group Management

How to deploy a Next.js application to GitHub pages

# Use GitHub Actions to build and export Next.js pages

# Rewrite paths to static files

# Success

Migrate from i3 to sway

# Installation

# Copy configuration

# Adjust configuration

# Screen output resolution

# Background images with automatic rotation

# Detect idle state and lock screen

# Screen backlight keys

# Keyboard bindings

# SSH Agent

# Exit script

# Notifications

# Printer

Automate tasks with docker-compose and systemd

# Example: Backup service

# Show logs

Nextcloud: Migrate from SQLite to PostgreSQL

# Step 0: Preparations

# Step 1: Stop routing traffic to the Nextcloud instance

# Step 2: Add database to docker-compose.yaml

# Step 3: Run migration

# Step 4: Confirm that config has been updated

# Step 5: Start routing traffic to the Nextcloud instance

Webcam Support in Arch Linux on Macbooks

OfflineIMAP + systemd timer

i3 + termite + custom themes = <3

# Base16 Themes

# Acknowledgements

Spotify Desktop App with HiDPI Scaling in GNOME 3

An "open" command in Linux

# Query and set defaults

GNOME 3: Change wallpaper periodically

Leaving Google's Mail Services behind

# Step 1: Choose an alternative

# Step 2: Setup with custom domain

# Step 3: Set up e-mail forwarding

# Step 4: Set up MX, SPF and DMARC records

# Step 5: Final checks

Evaluate data from Komoot with Elasticsearch and Kibana

# Table of content

# Preconditions

# Data mining

# Data sample

# Data preprocessing

# Data analysis and visualization

# Tile map of all recorded tours

# Heatmap of distance over time

# Bar chart of time in motion and elevation

# Dashboard

# Summary and Outlook

Howto train a CNN with TensorFlow on FloydHub

# Table of content

# Why FloydHub?

# CNN Architecture

# Get the code

# Upload data to FloydHub

# Run a Jupyter notebook

# Step 2: Add database to `docker-compose.yaml`