SQLite Plugin

With our SQLite output plugin, data can be stored in a local or remote SQLite database. This allows for implementing high-frequency and edge-only data collection use-cases as well as storing the data locally only without connecting to the internet. Transferring the SQLite database to a remote computer with an internet connection allows transmiting the data to our cloud afterward.

A ring-buffer setting allows limiting the maximum amount of data stored locally to prevent disc overflow.

File location:

configs/configurations/OutputPlugins/Sqlite/SqliteSettings.xml

Configuration

Mandatory settings

The following settings are mandatory for running the SQLite output plugin

Setting	Description
DataSource	Filepath (with filename) pointing to the SQLite database file
RingBufferTableName	Specify the table which is used for implementing the ring-buffer. Remove this setting, if no ring-buffer behavior is desired.
AutoIncrementColumn	Name of a column in the SQLITE ringbuffer table. Use an INT autoincrement column for this.

The SQLite database as well as the desired table need to be available on startup. Senseforce Edge does not create a new database or a new table.

Use the EventDefinition.xml (Data Routing Definition) to define in which tables as well as which columns the data should be inserted.

Sample configuration

A sample MQTT plugin configuration is illustrated below

<?xml version="1.0" encoding="utf-8" ?>
<SqliteConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                     xsi:schemaLocation="https://senseforce.io/ ..\..\Schemas\sqliteoutsettings.xsd" >
  <SqliteServer>
    <!--Path to db or ":memory:"-->
    <DataSource>C:\Sqlite\testdb.db</DataSource>
    <!--Represents the connection modes that can be used when opening a connection.
    Valid values: ReadWriteCreate, ReadWrite, ReadOnly, Memory-->
    <Mode>ReadWrite</Mode>
    <!-- Represents the caching modes that can be used when creating a new Connection.
    Valid values: Default, Private, Shared-->
    <Cache>Shared</Cache>
  </SqliteServer>

  <!--Messages arriving from ingress plugins are batched until the NumberOfMessagesToCombine is reached or the MessageCombineCounterMs are elapsed. Then they are sent.-->
  <NumberOfMessagesToCombine>40000</NumberOfMessagesToCombine>
  <MessageCombineCounterMs>60000</MessageCombineCounterMs>

  <!-- Ringbuffer configuration-->
  <!-- Defines, which column is used as autoincremental column. Mandatory for ringbuffer to work-->
  <AutoIncrementColumn>rId</AutoIncrementColumn>
  <!-- Total length (number of rows) of ringbuffer -->
  <RingBufferLength>60000</RingBufferLength>
  <!-- Defines, which table is used as ringbuffer. Only one table can be configured as ringbuffer-->
  <RingBufferTableName>MDE100</RingBufferTableName>

  <!--Specify file for logging-->
  <Logger>
    <FilePath>$(LunaAppDataPath)Logs</FilePath>
    <FileName>SQLiteOut.log</FileName>
    <!--LogLevel: Debug,Information,Warning,Error,Critical,None-->
    <LogLevel>Debug</LogLevel>
  </Logger>
</SqliteConfiguration>

Advanced Settings

The following settings are available: (Note: the corresponding line in the above sample file is provided in brackets).

Setting	Description
Mode	File opening mode. Use ReadWrite or ReadWriteCreate
Cache	Represents the caching modes that can be used when creating a new Connection. Valid values: Default, Private, Shared
NumberOfMessagesToCombine MessageCombineCounterMs	Messages arriving from ingress plugins are batched until the NumberOfMessagesToCombine is reached or the MessageCombineCounterMs (milliseconds) are elapsed. Then they are sent. Setting higher values reduces bandwidth if the transmitted messages are compressed. Note: SQLite is very fast when inserting huge batches of data.
RingBufferLength	Defines the size of the ringbuffer implementation

Sample file

2KB

SqliteSettings.xml

SqliteSettings.xml