# 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.                                    |

{% hint style="warning" %}
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. 
{% endhint %}

{% hint style="info" %}
Use the EventDefinition.xml (Data Routing Definition) to define in which tables as well as which columns the data should be inserted.
{% endhint %}

### Sample configuration

A sample MQTT plugin configuration is illustrated below

```markup
<?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 | <p>Messages arriving from ingress plugins are batched until the <code>NumberOfMessagesToCombine</code> is reached or the <code>MessageCombineCounterMs</code> (milliseconds) are elapsed. Then they are sent. Setting higher values reduces bandwidth if the transmitted messages are compressed. </p><p><strong>Note:</strong> SQLite is very fast when inserting huge batches of data. </p> |
| RingBufferLength                                  | Defines the size of the ringbuffer implementation                                                                                                                                                                                                                                                                                                                                             |

### Sample file

{% file src="<https://1533098984-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-Lj6tqYfpNT_59dZ_Z3Z%2F-Lj7UoXi3TptdKErRE2w%2F-Lj7V-U-w6VyvzKA3fOR%2FSqliteSettings.xml?alt=media&token=5a413bfb-bd8d-451e-a3e8-c80186765cb1>" %}
SqliteSettings.xml
{% endfile %}