SQLite Plugin -Output 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 |
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 Note: SQLite is very fast when inserting huge batches of data. |
RingBufferLength | Defines the size of the ringbuffer implementation |
Sample file