New DBQuery addon submitted

Add-ons Bindings
1

Introduction

I submitted a new database query addon named dbquery that allows creating items from native database queries.
The initial submission only supports InfluxDB 2.0 but I expect to publish another version in the near future with support for JDBC databases that are currently supported as persistence services.
The addon targets OH 3.0 and it’s only usable on that version.

I open that thread for further discussion and comments about the addon.

I know it’s difficult by now, because you need InfluxDB 2.0 and OH 3.0, that both are not released, but both also started to work in a release.
However, if you are able to install both and test the addon it will be heavily appreciated. If not, but you are interested in the addon any suggestion or comment will be well received.
The addon is in submission state and until is approved if you want to try you can download from here. Remember you need OH 3.0 to install it.

The initial submission is made in this PR:

Documentation

Detailed documentation about features and usage of the addon can be found here:

You can also find previously discussion about the addon in that message.

Some use cases

  • Integrate a device that stores it’s data in a database
  • Query derived data from you OpenHAB persistence, for example with Influx2 tasks you can process your data to create a new one
  • Bypass limitations of current OpenHAb persistence queries if you need to do complex filters or aggregations or you are more comfortable using native queries

A complex use case

One sample use case, that is the origin of this addon and a contribution I did in the InfluxDb persistence service to support InfluxDB 2.0, is the following that I like to explain because I think that there are some very nice synergies between this addon and InfluxDB 2.0:

I have an Iotawatt device that I use to get readings of my energy consumption, with the device I have different readings for instant consumption of all major home appliances: light, plugs, heating, fridge, washing machine, …

Those readings are continuously integrated to InfluxDB 1 and actually using the HTTP binding addon I launch some queries to InfluxDB to integrate the power consumption and obtain instant power usage or daily consumptions for each device.

But it has several limitations:

  • Due to escaping rules of HTTP binding and query syntax the queries are very difficult to read and maintain, for example one looks like this:

Number AerotermiaPower "Aerotermia [%d W]" <"energy"> { http="<[http://localhost:8086/query?db=openhab_db&q=SELECT%%20mean(%%22value%%22)%%20AS%%20%%22mean_value%%22%%20FROM%%20%%22openhab_db%%22.%%22origin%%22.%%22IOT_Aerotermia%%22%%20where%%20time%%20%%3E%%20now()%%20-1m:10000:JSONPATH($.results[0].series[0].values[0].[1])]"

  • The Influx 1 query language misses several features like obtaining the hour of the day of a given time, and that way, for example you are not able to do calculations of electricity cost if you have a per hour tariff.
  • Influx 1 has not as good features as 2 to process data and create derived measures.

With the combinations of the addon, I expect to have a lot more readable and maintainable queries, and using InfluxDB 2.0 and it’s new tasks feature I will be able to create derived measures like electricity cost for each device applying hourly tariff.
And that new measures will be easily queried and integrated into items with the query addon.

I think that the synergy with Influx2 tasks and dbquery addon it’s a good tool to do complex processing of your data or a new way to integrate newer devices that use supported databases.

Thanks

Finally, I like to give thanks to the community, specially @rossko57 and @hilbrand that help me answering some questions during addon development and improving it’s design and @5iver that helped me to get rules working for OH 3.0 and to be able to test the addon actions.

3 Likes
2

Hi there, any news about this add-on?
I have a similar use case and I wasn’t able to resolve it through http binding even if I run InfluxDB 1.8.
I’m trying to execute the following query

select (21*Studio+6*BathroomFF+17*Bedroom+10*Marco+12*Jordi+50*LivingRoom+6*BathroomGF)/122*100 as AvgHeatingIndex from his.heatingIndex where time>now()-1d

and if I escape the URL in the state URL extension I get an error from java format string, and if I do not escape it I think they way the binding escapes it is not liked by InfluxDB.
Thx

3

Hi llegovich,

Actually, it’s in process of review [dbquery] Initial contribution by lujop · Pull Request #8780 · openhab/openhab-addons · GitHub
It has been there for a lot of time, first waiting for maintainers review, and then I had been also not available for some time to correct what was asked.

Now in theory asked changes had been done and it can be approved at any moment, or be asked for more changes.

If you want I can provide with a binary version, but take note that initially the addon only supports Influx2 and my plans are to support JDBC in the near future.
Influx1 it’s not in my plans by now, but help will be appreciated.

4

Thank you for this binding, it’s really useful for getting aggregated values for my electric prices, to help save money!

However, when trying to configure it, I noticed an error either in the documentation or the code for the execute channel. The documentation states:

Send ON to execute the query manually. It also indicates if query is currently running (ON ) or not running (OFF )
But the code that handles the commands look like this:

    public void handleCommand(ChannelUID channelUID, Command command) {
        logger.trace("handleCommand for channel {} with command {}", channelUID, command);

        if (command instanceof RefreshType) {
            if (CHANNEL_EXECUTE.equals(channelUID.getId())) {
                executeQuery();
            }
        } else {
            logger.warn("Query Thing can only handle RefreshType commands as the thing is read-only");
        }
    }

I.e. It says to send ON to the Item, but only accepts a REFRESH command. IMO, the best would be if the code is changed to match the documentation, that way it’s easy to manually execute the query from the UI if needed.