[persistence] Add a native query interface that services can implement

[lujop]

Background

It would be a good feature to be able to create Items from arbitrary database data using native queries not tied to Openhab persisted data.
It will allow integration with other products, using the database as the interface, i.e. a way to integrate with Iotawatt is to make queries to InfluxDB and create items with the results of that queries to have information about your power usage/consumption.
Another use case is if you want to query derived data from Openhab persisted one using some type of background process, for example InfluxDB 2 offers flux tasks. Or in general if you want to create an item from some database data that doesn't follow Openhab structure.

More about the background in original forum discussion [1]

Need justification

To cover prior use cases described in the background section my implementation idea is to write a Native Query add-on (outside core) that allows you to define Items from an arbitrary query in the native query language that uses the persistence service ( SQL, Flux,...)

Need for a query interface

As the intend of the addon is to be generic an useful for several persistence services as debated on [1], it's not scalable to have all the persistence connectors in the addon.
And the best way would be to restrict its responsibilities to define items and update them making queries to existing persistence services.

Need for the query interface to be native

As the addon must be capable to make items from arbitrary queries the current FilterCriteria doesn't cover what is needed because it's very limited and tied to Openhab persistence format.

One possibility will be to extend FilterCriteria adding more functionality. But I think that it's not the way to go for this issue use cases because it will be very difficult to cover all the needed cases that users can have:

• Queries can be very complex and some will could not be possible to be done with a hypothetic improvement of FilterCriteria and then limiting user experience going that way.
• Several persistence services use very different query capabilities and formats. For example, SQL and Flux are very different. And can have specific capabilities that can be very useful, for example being able to do integrals in FluxDB to calculate power consumption from last hour from instant power readings
• It can be hard to implement the new interface in all persistence services and to define a query syntax and parser to allow the user to express the queries.

In contrast, adding a native persistence interface has the following benefits:

• User can do everything that it's possible in the persistence service it uses.
• Easy to implement for the persistence services that want to have such functionality.
• Easy for the user to define queries, as it doesn't need to learn a new query syntax to make its queries, and it can use a query syntax that he could already knows or is easier for him to learn, test and experiment with persistence service tooling and documentation

Architecture considerations

I think that improving FilterCriteria to cover more use cases can be useful for some use cases like addons that offer generic functionality about Openhab own persisted data without the user having to write any query.
But for external data, that for the definition of external, the query would need to be manually given by the user, I think that is too much work to offer an abstraction or genericity that it's possible that offers less and more complex functionality for the user.

I think that the native interface doesn't break persistence service if a rule is used:
That interface should only be used for queries completely created from the end-user through configurations files and never by dynamically generated queries from the core or other addons.
That way it doesn't compromise interoperability of addons with a generic persistence service, and allow end-users to create powerful queries in an easier way for them.

Also, I think that other generic persistence services offer that option. For example in Java, the most famous generic and standard API is possibly JPA that offers a nativeQuery interface in case you need it, and it doesn't considers that breaks the abstraction. That option's not used by libraries or generic features that build on top of JPA, but allow the end-user to make use of them for uses cases than cant solve with standard API.

Proposed Interface

The proposed interface would be something like

interface NativeQuery {
   List<Object[]> query(Map<String,Object> params)
}

References
[1] Original forum discussion

[lujop]

I updated the last section about Architecture considerations.
I also want to say that I will happily collaborate in developing the query addon and implementing the interface in major persistence services.

And if it's not possible or accepted I will implement the query addon tied to InfluxDB2 that it's what I need to cover my uses cases. But I will really like to be able to provide that also for the community.

[splatch]

Your proposal makes a lot of sense, so I will leave my 0.2€ here.

Additionally I will bring my insights. The present interface of QueryablePersistenceService is fairly limited since FilterCriteria can ship just single item and single condition. I haven't seen any reasonable use of filtering which was based on just one condition. Even simplest data retrieval from given time period will usually involve two bounds. By design the query method can return multiple HistoricItem instances which represent different Items of certain category, group or passing some threshold, however input for the method does not permit anything like that. In order to make Queryable persistence service fit reality the input should be rather a list of conditions and not just one.

Additionally OH persistence services can return ONLY basic output. They are never asked directly or indirectly about aggregation, even if underlying storage mechanism allows that. Such functionality is built on top via PersistenceExtensions, which glorify usage of rules. This castrates user interface options. This also promoted use of rrd4j to host any charts. It did for far too long.
I am aware that new UI will ship new graphing options, however making multiple requests to read multiple items from persistence services will not improve overall speed of openhab-cloud which is very sensitive to network latency and roundtrips!

[lujop]

In the end, to be able to advance in the binding that I want, and originated my need for that issue I've started creating a new binding named DbQuery.

My initial plans for the binding are to provide an Influx2Bridge an MySQLBridge, and if someday that issue is a reality an OpenHabQueryablePersistenceServiceBridge will be added.

Thinking it after time, the possibility to query an external database even not related to any persistence service can be a plus for some requirements.