When using Value Streams to log historical data, there's a service to purge the ValueStream entries from the Thing itself.
But, what to do when a Thing that once logged values into a ValueStream was deleted? Currently, there's no OOTB way to delete these entries if they're not being used anymore.
Currently, I was asked this question and wanted to share this with the entire community. I created a utility application that queries directly the TWX DB for Things that are present in the ValueStream but don't exist anymore and allows a user to purge it.
These services are considering PostgreSQLServer as persistence provider for the ValueStreams. The services can be modified if you're using SQLServer. Do not apply for InfluxDB persistence providers
The twxDBConnector thing is based on the PostgreSqlServer template, that is present in the Relational Databases extension. It has 4 main services:
getEntriesToPurge: Queries the TWX DB for all the entries related to a Thing. It does not consider the ValueStream id, so it will purge all the entries across all value streams. Requires a Thing name as an input;
getMissingThings: Queries Things that are present in the ValueStream DB table that are not present in the Things table, meaning that they were deleted;
purgeThingEntries: Purges the entries related to a Thing. It does not consider the ValueStream id, so it will purge all the entries across all value streams;
purgeAllEntries: Purge all the entries related to Things that were deleted.
The queries can be modified to allow the selection of the value stream to be cleaned.
I also added a sample mashup that leverages the services.
The twxDBConnector has a configuration table that requires the DB Connection string, user and password.
You can also do it directly from the DB using PGAdmin and purge it all.
DELETE FROM value_stream
WHERE value_stream.entry_id IN
--Queries all entries in the value stream table that belong to an inexistent thing
(SELECT entry_id FROM
value_stream LEFT JOIN thing_model ON
value_stream.source_id = thing_model.name
WHERE thing_model.name IS NULL)
Attention: These services are changing directly the TWX DB, so use it carefully.
To use it:
- Import the PostgresSQLServer Extension (you might need to change the JAR in the extension depending on the TWX version you're using);
- Import the entities from the purgeVSEntries.xml
Thanks @dsantos for the help on optimizing the queries.
Hope it helps.
Ewerton
