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:
1
configs/configurations/OutputPlugins/Sqlite/SqliteSettings.xml
Copied!

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
1
<?xml version="1.0" encoding="utf-8" ?>
2
<SqliteConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
3
xsi:schemaLocation="https://senseforce.io/ ..\..\Schemas\sqliteoutsettings.xsd" >
4
<SqliteServer>
5
<!--Path to db or ":memory:"-->
6
<DataSource>C:\Sqlite\testdb.db</DataSource>
7
<!--Represents the connection modes that can be used when opening a connection.
8
Valid values: ReadWriteCreate, ReadWrite, ReadOnly, Memory-->
9
<Mode>ReadWrite</Mode>
10
<!-- Represents the caching modes that can be used when creating a new Connection.
11
Valid values: Default, Private, Shared-->
12
<Cache>Shared</Cache>
13
</SqliteServer>
14
15
<!--Messages arriving from ingress plugins are batched until the NumberOfMessagesToCombine is reached or the MessageCombineCounterMs are elapsed. Then they are sent.-->
16
<NumberOfMessagesToCombine>40000</NumberOfMessagesToCombine>
17
<MessageCombineCounterMs>60000</MessageCombineCounterMs>
18
19
<!-- Ringbuffer configuration-->
20
<!-- Defines, which column is used as autoincremental column. Mandatory for ringbuffer to work-->
21
<AutoIncrementColumn>rId</AutoIncrementColumn>
22
<!-- Total length (number of rows) of ringbuffer -->
23
<RingBufferLength>60000</RingBufferLength>
24
<!-- Defines, which table is used as ringbuffer. Only one table can be configured as ringbuffer-->
25
<RingBufferTableName>MDE100</RingBufferTableName>
26
27
<!--Specify file for logging-->
28
<Logger>
29
<FilePath>$(LunaAppDataPath)Logs</FilePath>
30
<FileName>SQLiteOut.log</FileName>
31
<!--LogLevel: Debug,Information,Warning,Error,Critical,None-->
32
<LogLevel>Debug</LogLevel>
33
</Logger>
34
</SqliteConfiguration>
Copied!

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

SqliteSettings.xml
2KB
Text
SqliteSettings.xml
Last modified 7mo ago