DBMS support for persistent event log

Starting from late spring of 2021, we’ve been working on support for relational databases in Viinex for storing events coming from ONVIF cameras, other kinds of sensors, as well as from video analytics modules, scripts and so on. First of all, the integration with PostgreSQL was added; then we thought that sometimes users would prefer to not bother with maintaining an instance of PostgreSQL, so we implemented support for SQLite. Today we’ve released Viinex build containing an experimental support for Microsoft SQL Server. All three of currently integrated DBMS-es are available on all architectures where Viinex runs.

The schema for storing events consists of just one table. The table contains columns for common information on events, and the JSON column for the data which is specific to the particular event type. The schema is created/updated automatically at Viinex startup, and it’s actually not a part of public Viinex interface: users do not need to access the database directly. For events retrieval from perspective of clients, there are a few simple HTTP API calls implemented by Viinex, as described in section 3.15 of the documentation.

As a basic example of how this functionality can be used, we added the support for persistent event storage to the user interface of Railcar ID number recognition application. Now, the railcar ID number recognition results are by default stored to the SQLite database. The demo UI (which source code is now open and available at https://github.com/viinex/viinex-rlw-ui) queries the database for railcar number recognition results based on date and railway track name where the results were captured.