I think it makes a good addition. But it might be even better to add support for such features to the openhab-core persistence APIs since I think it may also be useful functionality for other peristence add-ons. WDYT @Kai?
The restoreOnStartup persistence strategy is a special instance of such functionality where the items are restored on startup only. Whereas it might be useful to periodically restore the item state when the state is persisted by other applications/users using the database.
Then it could also be possible to transform data to item state within the database by using a database view or some database triggers.
Yes, it will be nice to have it globally for all addons.
Because it will allow to query derived data in the persistence addon or use it as an integration point for external systems.
For me, a very important point to go for InfluxDB 2.0 was the flux task to create derivative data that will be very useful for me.
For me a possible implementation could be something simple and flexible like: nativeQuery(String query, Map<String,Object> params)
Because it provides flexibility to query data that is not tied to OpenHab persistence rules.
I am not fully clear on what the suggestion is here, I have the impression that different things are discussed.
If we are talking about introducing a binding that is able to interact with databases in general (because there might be data that is stored by devices like iotawatt or calculated/derived in some other way), then I could imagine having a “Database Binding”, which is generic enough to make use of any queryable persistence service that is available.
If we want to add querying features to rules, we could easily extend PersistenceExtensions by a query method, which integrates with the existing QueryablePersistenceService.query method, which is generic and not specific for any database.
Adding a nativeQuery function as suggested by @lujop looks to me as the least attractive option from an architectural point of view.
This is exactly my use case. But it will need to have some native query support from the persistence services to do the queries.
Because each persistence service use its clients and very different queries, from SQL for MySQL to flux for InfluxDB 2.0
As mentioned above, I would hope that such a binding can be implemented to work with any QueryablePersistenceService. The db-specific queries are then already implemented within the persistence service and there should be no need to have anything specific in the binding.
@Kai, any update on my proposal?
Or at least will be accepted to do the addon only for InfluxDB.
Adding a NativeQuery interface to the InfluxDB persistence service and develop a new addon with a binding that uses it?
To meet my needs I’ve to be able to do flux queries in the binding, using just FilterCriteria is not a solution.
I am still not very fond of this idea as it somewhat breaks the abstraction layer of the persistence service. But if you think it makes a lot of sense, feel free to create an issue at openhab-core, where it can be discussed. Decision is not taken by me, but by the core maintainers, so those are the ones you need to convince .