The SQLite input plugin allows continually polling an SQLITE database. The plugin is able to remember it's last position based on an increasing numeric column.
Configuration location:
Console Application | Service Application | ||||
|---|---|---|---|---|---|
|
|
| Info |
|---|
Use the default DataMappings.xml for most use cases. |
SqliteSettings.xml
The SqliteSettings.xml define where to find the SQLITE database file and how to open it.
Example configuration
Description | |
DataSource (line 6) | Defines where to find the database file |
Mode (line 9) | Defines the opening mode. Set to ReadOnly for most use cases |
Cache (line 12) | Defines the SQLITE caching mode. Set to default for most use cases |
| Code Block |
|---|
<?xml version="1.0" encoding="utf-8" ?>
<SqliteConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="https://senseforce.io/ ..\..\Schemas\sqlitesettings.xsd" >
<SqliteServer>
<!--Path to db or ":memory:"-->
<DataSource>S:\SQLiteLuna.db</DataSource>
<!--Represents the connection modes that can be used when opening a connection.
Valid values: ReadWriteCreate, ReadWrite, ReadOnly, Memory-->
<Mode>ReadOnly</Mode>
<!-- Represents the caching modes that can be used when creating a new Connection.
Valid values: Default, Private, Shared-->
<Cache>Default</Cache>
</SqliteServer>
<!--Specify file for message mappaing-->
<Mappings>
<FilePath>$(LunaAppDataPath)configurations/InputPlugins/SQLite</FilePath>
<FileName>DataMappings.xml</FileName>
</Mappings>
<!--Specify file where the data blocks to read are defined-->
<DataConnections>
<FilePath>$(LunaAppDataPath)configurations/InputPlugins/SQLite</FilePath>
<FileName>DataDefinition.xml</FileName>
</DataConnections>
<!--Specify file for logging-->
<Logger>
<!--LogLevel: Debug,Information,Warning,Error,Critical,None-->
<LogLevel>Information</LogLevel>
</Logger>
</SqliteConfiguration> |
DataDefinition.xml
The DataDefinition.xml defines which table and which columns to read.
Example configuration
The following exemplary configuration polls the table CounterTest (line 6) every second (line 10) and reads the columns Id, Timestamp and Counter (lines 13 to 27). A file called PersistId.json (line 47) is used to remember, which line with which Id was last read (line 16).
| Note |
|---|
Make sure that the column marked with IsLastReadId = true (line 16) is implemented as incrementing number in the SQLITE database table. |
Settings
Setting | Description |
Name (line 6) | Name of table to read |
OrderBy (line 7) | Which column to order the table by. Use the column marked as IsLastReadId for most use cases |
OrderDir (line 8) | Use ASC in all cases |
NumberToRead (line 9) | Maximum number of rows to read in a single reading operation |
PollingTimeS (line 10) | Polling interval in seconds |
OnlySendChanges (line 11) | Defines, whether only changes in columns should be transmitted. For most SQLITE use cases, false is appropriate |
Name (line 14) | Defines the name of which column to read |
FilterOnly (line 15) | Flag indicating whether this column is used only for filtering |
IsLastReadId (line 16) | If set to true, this column is used to remember, which row was last read. Make sure that this column is used in the OrderBy setting (line 7) and it is implemented as incrementing number or incrementing timestamp in the SQLITE table to read. |
IsTimestamp (line 20) | If set to true, this column is handled as internal timestamp datapoint. Must be unix timestamp in milliseconds. It is highly recommended to have one column marked as timestamp. |
Filter (line 27) | The filter block allows to define where clauses in the SQLITE select statement |
Condition (line 28) | Whether to connect this filter logically AND or OR with other filters. Not relevant, if only one filter is defined. Allowed values: OR, AND |
Operator (line 29) | Allowed values: Greater, Less, GreaterOrEqual, LessOrEqual, Equal or NotEqual |
FilterName (line 30) | Value to compare the column. In the example, only rows where column Counter equals 1 are read |
MandatoryCondition | Optional. Filter marked with MandatoryCondition = true must evaluate to true, otherwise the reading process is halted, until the filter evaluates to true. If the condition in a row is not met, all the entries are read until the row which does not meet the condition. |
MandatoryConditionSkipTimeMs | Optional: If a filter with MandatoryCondition = true is set for longer than MandatoryConditionSkipTimeMs (in milliseconds), the mandatory condition is skipped and the halting line is read anyhow. Note: The column definitions must contain a column with flag IsTimestamp =true |
MathOperation | Optional. One or more MathOperation-blocks may be defined in a wrapping MathOperations-block (see line 34ff.). |
Operation (line 38) | Defines the mathematical operation to perform. Allowed values: Add, Subtract, Multiply, Divide, Power If several operations are defined, the operations are applied consecutively, starting with the top-most defined operation. |
Value (line 40) | Right-hand-side value of the mathematical operation. In the example, the read value of column Counter is multiplied by 1000 |
| Code Block |
|---|
<?xml version="1.0" encoding="utf-8" ?>
<DataConnectionsConfig xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="https://senseforce.io/ ..\..\Schemas\dataConnections_sqlite.xsd" >
<Datas>
<Table>
<Name>CounterTest</Name>
<OrderBy>Id</OrderBy>
<OrderDir>ASC</OrderDir>
<NumberToRead>100</NumberToRead>
<PollingTimeS>1</PollingTimeS>
<OnlySendChanges>false</OnlySendChanges>
<Columns>
<Column>
<Name>Id</Name>
<FilterOnly>false</FilterOnly>
<IsLastReadId>true</IsLastReadId>
</Column>
<Column>
<Name>Timestamp</Name>
<IsTimestamp>true</IsTimestamp>
<FilterOnly>false</FilterOnly>
</Column>
<Column>
<Name>Counter</Name>
<IsTimestamp>false</IsTimestamp>
<FilterOnly>false</FilterOnly>
<!-- You can eigher use a single filter -->
<Filter>
<Condition>OR</Condition>
<Operator>Equal</Operator>
<FilterName>1</FilterName>
</Filter>
<!-- .. or many filters as well as in the example below -->
<!-- <Filters>
<Filter>
<Condition>AND</Condition>
<Operator>Greater</Operator>
<FilterName>8</FilterName>
</Filter>
<Filter>
<Condition>OR</Condition>
<Operator>Equal</Operator>
<FilterName>5</FilterName>
</Filter>
</Filters>-->
<!-- Define which mathematical operations you want to perform with the data -->
<!-- List of Mathematical operations -->
<MathOperations>
<!-- Definition of mathematical operation -->
<MathOperation>
<!-- Definition of operation type (valid: Add, Subtract, Divide, Multiply, Power) -->
<Operation>Multiply</Operation>
<!-- Define the second mathematical operand (first one is the variable itself) -->
<Value>1000</Value>
</MathOperation>
</MathOperations>
</Column>
</Columns>
<PersistLastId>
<FilePath>$(LunaAppDataPath)configurations/InputPlugins/SQLite</FilePath>
<FileName>PersistId.json</FileName>
</PersistLastId>
</Table>
</Datas>
</DataConnectionsConfig> |
Example SqliteSettings.xml
| View file | ||
|---|---|---|
|
Example DataDefinition.xml
| View file | ||
|---|---|---|
|