search

Vantiq / vantiq-extension-sources

Contains source code for VANTIQ Extension sources as well as SDKs for building these sources.
Other
13 stars 6 forks source link

Make the JDBC really event-driven #138

Open liu-xiao-guo opened 5 years ago

liu-xiao-guo commented 5 years ago

Currently, the connector can ONLY set a polltimer to poll the data from the database. For most of the time, the data could be the same without any change if there is no data change in the database.

However, we may make the connector event driven. If there is a data change (INSERT/UPDATE/DELETE) in the database (MySQL or Oracle), it can send the notifications to the Vantiq system. This is useful for a lot existing database-based IoT applications. They normally firstly save into the database, and database could be the live stream. Connector only sends the data when there is an insertion. As such, it may save a lot of bandwidth instead of polling all of the data again and again.

namirfa commented 5 years ago

@liu-xiao-guo

After looking into this, there does not seem to be any universal JDBC method that can be used to check for changes in a database. Oracle has offered a "Database Change Notification", which is specific to the Oracle database. Since this feature is not applicable to any other databases, it does not make sense to add this feature as part of the general JDBC Enterprise Connector.

For now, all that we can offer is the "pollTime" and "pollQuery", which can be used to accomplish similar functionality (with the corresponding Vail code).

liu-xiao-guo commented 5 years ago

The problem with VAIL is that it wastes the bandwidth if there is no update of data in the database. It keeps polling data from the database, and the data could be the same for the two consecutive pollings. If the database is big, potentially, there is a big waste of network bandwidth.

markejfmunro commented 5 years ago

Hi XiaoGue,

But as pointed out there is no generic JDBC based approach to this solution. I know Oracle for instance has some extension to thier JDBC driver to do this but it is not generic and only works with Oracle. This would have to be an Oracle specific extension source rather than a generic JDBC source.

fhcarter commented 4 years ago

Possible alternative: What if there was a notion of a cached (set of) values instead of pollQuery. The configuration of this would take a list of fields that would be the fields that determine if something changed. Then, the connector would cache the results and, at periodically (maybe using poll time), compare the current results to the old results. If different, send. Otherwise, skip it.

This is probably a bit simplistic in that it assumes that the poll query returns a single row (which isn't required), etc. So we make have to specify (or determine) a key to key the cache on, etc. And we'd have to figure out the semantics w/r/t what happens if we're caching 6 values & 2 change. Do we re-report all 6 (the set changed) or only the change. It could get complicated fast.

But maybe there's something there. Not sure where this would be on the priority list -- just noting a thought.

namirfa commented 4 years ago

@fhcarter

This idea definitely seems like something that we could work out and implement.

Are there any requests from the field to add this type of feature? If so, maybe we could consult with them to see what scenarios they'd need this feature for, and use that to better understand what we would do in different scenarios (variable numbers of rows returned, different field values changing, etc..)