Databasemanager

Purpose

Act as interface between the databases and the rest of the program.

Main features

• Connect to MySQL, MariaDB and MSSQL servers
• Connect & create SQLite databases and the tables
• Combine queries in PreparedStatements (but also accepts plain queries) and execute them in batches

Usage

To get a list of all available commands use the command dbm:? or databasemanager:?. A less typical xml section will look like this

    <databases>
    <sqlite id="sensordata" path="db\sensordata.sqlite">
        <flush age="30s" batchsize="30"/>
        <idleclose>-1</idleclose>
        <table name="data">
            <timestamp alias="">timestamp</timestamp>
            <integer alias="@macro_serial">serial</integer>
            <real alias="@macro_temperature">temperature</real>
            <integer alias="@macro_humidity">humidity</integer>
            <integer alias="@macro_pressure">pressure</integer>
        </table>
    </sqlite>
</databases>

Timestamp

This might change but for now this column type means that the system time will be used.

Alias

By default, when building the query for a table, the code will look for tablename_columnname in the rtval/rttext map. If this is not wanted, the alias can be filled in and then that will be used to look for a corresponding value.

Why would you want this?

• If multiple tables (across different databases perhaps) use the same value then this might not match tablename_columnname.
• If there are duplicate sensors (eg. a temperature sensor grid) and the data string contains an unique identifier than this identifier can be used with @macro, so this only works with generics. Then the alias would look like @macro_temperature.
• If there are duplicate sensors but no unique identifier, this can't be covered in the generics.

Macro

By default, the trigger for a write is done by a generic as seen in that section a generic can have a macro value. This value is passed along when the insert trigger is received, so it can be used to fill in the alias. Because of this, it's possible to insert data from multiple sensors in the same table as long as there's a unique identifier.

So in the above xml, if the macro value received from the generic is '1234' then for the temperature value the code will look for the rtval with name 1234_temperature and the same thing is done for the other ones.