Database plugin: Provide pack mechanism to keep database small

[ohinckel]

The sqlite plugin have a pack mechanism which keeps the database small (like RRD files does). The database plugin does not have this mechanism and keeps all the values in the database.

To be able to have small databases (e.g. for embedded systems and maybe completely replace sqlite plugin) the database plugin should also have such a mechanism.

Different ideas exist

1. Reduce by aggregation: aggregate and pack values for different timespans to different details
2. Reduce by dumping/exporting: automatic export/dump function which exports data (e.g. into CSV files) and removes them from database after export completed
3. Reduce by moving to history tables: make use of history tables (e.g. move everything older than one year into a log_YYYY table)
4. Reduce by removing "duplicates": duplicated values, eg same value in multiple records can be aggregated to one record
5. Reduce by remvoing: Simply delete data older the given amount of time
6. Reduce by max. amount of records: Only given amountof records will be kept in database, other ones will be removed
7. Reduce by kepping database size: calculate usage of each item and remove given percentage frkm all of them
8. Reduce by replacing details with inaccurates: replace details by calculating an inaccurate value for a set of values (e.g. only store changes for temperature without changes in floating point)
9. ... other ideas?

Can be configured:

1. Globally in plugin configuration
2. On a per item basis
3. ... other ideas

(leftover from #165)

[bmxp]

Brainstorming upon the motivation and reasons to reduce data

Why are we collecting the data?

• compare different chapters in time with the same series (e.g. scoring of a heating)
• have a series of temperatures to control the regulating?
• check presence in point of time (did the room service tidy 2 hours as ordered)
• check an occurence in time (door bell)
• cross link two data series to check for an event indicator (door bell and lights on a hallway)

What is the intention of a pack mechanism?

• prevent storage space from running full (Look at Raspi SD-Cards)? --> Would need to cut down to a max database size regardless of the entry or discard some data first to keep db size below a threshold
• getting rid of old data? (we just don't need it any more)
• personal data protection? (yes, personal data can be collected, think of bluetooth id from smartphone connected to SmartHomeNG, you can track persons this way)

Ideas to pack data

• Pack data with same value but repeating time stamps into the one with the first time stamp
  (e.g. 10 times value light on in kitchen every 5 minutes since 11:00 --> drop the last 9 values from database)
• Compatability to old SQLite behaviour --> aggregate values as done before
• Keep only x values for one item in database
• Keep only history of specific timespan for one item
• Keep database at a specific size (count the datasets per item to get a usage of % per item and then drop proportional amount of datasets), maybe declare some items as untouchable.
• remove details (temperature goes up and down some centi grades, pack values according to a threshold)
• export data to somewhere else and free the space in database

Please extend further!

[onkelandy]

I think it would be very important to configure the pack mechanism in the plugin.yaml file. At least to disable it. Other idea would be to delete all data older than x days. Or a smarter method like the older the data the more of it gets deleted.
So the pack mechanism can be turned off, adaptive, on
Additional attribute defines max. amount of days

[msinn]

With SmartHomeNG it is possible to restrict the age of history data kept for an item. Older history entries are removed periodically. For Items that should use this removal, an the item attribute database_maxage must specify the number of days for which history should be kept.

[msinn]

This issue has been moved to the plugin repo: smarthomeNG/plugins#566