SQLite Plugin

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:

[Edge Main Folder]/configs/configurations/InputPlugins/SQLite

These configs are only used for the Edge instance Luna.Console, executed directly from the [Edge Main Folder]. Click the "Service Application" tab above to check the location for Edge services.

C:/ProgramData/Senseforce/Luna Data/[YourServiceName]/configurations/InputPlugins/SQLite

You can install several SF Edge service instances onto one device - for example to collect data of several machines from a local data collection device. Each service instance needs it's own set of configurations. When using the Senseforce Edge Service Installer, the default configurations are automatically installed for any service.

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

<?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).

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

<?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>

PreviousSQL Plugin NextOPC UA Plugin

Last updated 3 years ago