Plugin: backend_odbc

• infos = Information about the backendOdbc plugin is in the keys below
• infos/author = Florian Lindner flori.nosp@m.an.l.nosp@m.indne.nosp@m.r@st.nosp@m.udent.nosp@m..tuw.nosp@m.ien.a.nosp@m.c.at
• infos/licence = BSD
• infos/provides = backend
• infos/needs =
• infos/recommends =
• infos/placements = backend
• infos/status = reviewed unittest experimental
• infos/description = Plugin implementing full backend functionality for ODBC data sources

Introduction

This plugin is a backend plugin that stores and retrieves data using ODBC (Open Database Connectivity) data sources. It was tested with unixODBC on Linux, but should also work with iODBC and Microsoft ODBC (on Windows).

If you want to use it with one of the latter two ODBC implementations, feel free to update this documentation with your experiences!

Required database scheme

The minimum requirement is a table with at least two columns:

• Key-name (string, primary key (PK))
• Key-value (string)

Additionally, a second table with at least three columns is required.

• Key-name (string, foreign key (FK) to the first table)
• Metakey-name (string)
• Metakey-value (string)

The primary key of this table consists of two columns: the key-name and the metakey-name. In the language of ER-modelling, the metatable can therefore by considered a weak-entity.

Currently, only data sources with tables for metadata are supported! So you have to define a meta-table. Data sources without a table for metadata will probably be supported in the future. If you want to use metadata, the ODBC driver for your data source has to support outer joins. This implies that currently, only ODBC drivers with support for outer joins are supported by the ODBC backend.

The tables may also contain other columns, but they are not processed by this plugin and must support NULL- or DEFAULT-values, if you want to add tuples to the table via Elektra (e.g. by calling kdb set <key> <value>).

If the column for the key-name is not defined as a primary key and multiple rows contain the same key-name, they are treated as one key, where the value is taken from the first row and the metadata is combined. If multiple metakeys with the same metakey-name exist for a key, the last metavalue is used. This behavior is part of the internal algorithm of the plugin and could change in the future. So if you want a predictable and expectable behavior, make sure that the described primary- and foreign-key constraints are respected.

Mounting

The mountpoint definition for ODBC mountpoints, which is stored at system:/elektra/mountpoints/<MP>/definition is defined as follows. The placeholder <MP> is the path in the KDB where the root of the defined mountpoint is located. This is the path you specify when creating the mountpoint.

The key-names for an ODBC mountpoint-definition are:

system:/elektra/mountpoints/<mp>
system:/elektra/mountpoints/<mp>/plugins/backend
system:/elektra/mountpoints/<mp>/plugins/backend/name
system:/elektra/mountpoints/<mp>/definition/dataSourceName
system:/elektra/mountpoints/<mp>/definition/userName
system:/elektra/mountpoints/<mp>/definition/password
system:/elektra/mountpoints/<mp>/definition/timeout
system:/elektra/mountpoints/<mp>/definition/table/name
system:/elektra/mountpoints/<mp>/definition/table/keyColName
system:/elektra/mountpoints/<mp>/definition/table/valColName
system:/elektra/mountpoints/<mp>/definition/metaTable/name
system:/elektra/mountpoints/<mp>/definition/metaTable/keyColName
system:/elektra/mountpoints/<mp>/definition/metaTable/metaKeyColName
system:/elektra/mountpoints/<mp>/definition/metaTable/metaValColName

The key-values for the keys under definition are defined as follows:

• dataSourceName: The name of the ODBC data source (as defined in the odbc.ini file)
• userName: Name of the user that should be used to connect to the data source
  • can be empty if no username is required or if the value should be read from the odbc.ini file
• password: Password for the user who wants to connect
  • can be empty if no password is required or if the value should be read from the odbc.ini file
• timeout: The number of seconds to wait after a connection attempt is considered as failed (0 = wait indefinitely)
  • if empty, a default value is used
• table/name: Name of the table where the data is stored
• table/keyColName: The name of the column where the keynames are stored
• table/valColName: The name of the column where the key-values (strings) are stored
• metaTable/name: The name of the table where the metadata is stored
• metaTable/keyColName: The name of the column in the MetaTable where the name of the key the metakey belongs to is stored
• metaTable/metaKeyColName: The name of the column where the name of the metakeys is stored
• metaTable/metaValColName: The name of the column where the value (string) of the metakeys is stored

There is a new command for the kdb-tool: kdb mountOdbc. Please be aware that the ODBC backend, in contrast to the classic file-based backend, currently does not support adding other plugins to the mountpoint.

The kdb mountOdbc command accepts arguments for the previously described values of the mountpoint definition. For further details, please refer to the man page for that command.

For unmounting, there is no new command necessary. Just use the well-known kdb umount <mountpoint>, exactly like for the file-based backend.

For more information about the ODBC backend, there is a tutorial available at /doc/tutorials/odbc-backend.md.

Testing and Benchmarking

There are no dedicated unit tests that actually store, read or delete data from an ODBC data source. The reason is, that with the current concept, a valid ODBC data source definition (for unixODBC in /etc/unixODBC/odbc.ini) must be present. We don't want to write to such an important system file that can influence other applications. It would also be cumbersome if we require the user to set up a specific ODBC configuration before running the tests .

However, for basic functionality testing and performance evaluation, you can create a valid ODBC mountpoint yourself using kdb mountOdbc and then run the benchmark which is implemented in /benchmarks/mountpoint.c for the created mountpoint. This approach makes it possible to test and benchmark any valid mountpoint in the KDB. So different backends and plugins with different configurations can be tested and benchmarked. Just create a mountpoint with the backends, plugins and configuration that you want and run the benchmark with e.g. bin/benchmark_mountpoint <MP path>/benchmark for this mountpoint.

A short example that shows creating a new ODBC mountpoint and running benchmarks for KeySets and metadata:

bin/kdb mountOdbc Selektra "" "" elektraKeys keyName keyValue metaKeys keyName metaKeyName metaKeyValue "" user:/odbcSqlite
bin/benchmark_mountpoint -c 1000 user:/odbcSqlite
bin/benchmark_mountpoint -Mc 1000 user:/odbcSqlite

More information about benchmarking is available at /benchmarks/README.md.