diff options
author | Trygve Laugstøl <trygvis@inamo.no> | 2015-10-19 21:53:49 +0200 |
---|---|---|
committer | Trygve Laugstøl <trygvis@inamo.no> | 2015-10-19 21:53:49 +0200 |
commit | 73d272ffe8954b3169901eda74428bad3d2740fe (patch) | |
tree | 1f1cca7f57809e05d0c1fea7363555a5521f8bcb /src/DillerDao.js | |
parent | 52eb8072664a61ea61dbdbef7485d6c81dbbcfe9 (diff) | |
download | diller-server-73d272ffe8954b3169901eda74428bad3d2740fe.tar.gz diller-server-73d272ffe8954b3169901eda74428bad3d2740fe.tar.bz2 diller-server-73d272ffe8954b3169901eda74428bad3d2740fe.tar.xz diller-server-73d272ffe8954b3169901eda74428bad3d2740fe.zip |
o Adding aggregation tables.
o Adding migration scripts for the schema.
Diffstat (limited to 'src/DillerDao.js')
-rw-r--r-- | src/DillerDao.js | 31 |
1 files changed, 29 insertions, 2 deletions
diff --git a/src/DillerDao.js b/src/DillerDao.js index ed6fcf0..e0c69d3 100644 --- a/src/DillerDao.js +++ b/src/DillerDao.js @@ -28,7 +28,32 @@ function DillerDao(client) { } function insertValue(propertyId, value) { - return client.none('INSERT INTO value(property, timestamp, value) VALUES($1, CURRENT_TIMESTAMP, $2)', [propertyId, value]); + return client.one('INSERT INTO value(property, timestamp, value) VALUES($1, CURRENT_TIMESTAMP, $2) RETURNING timestamp', [propertyId, value]); + } + + function updateHourAggregatesForProperty(propertyId, timestamp) { + return client.none('DELETE FROM value_by_hour WHERE property=$1 AND timestamp=DATE_TRUNC(\'hour\', $2::TIMESTAMPTZ)', [propertyId, timestamp]) + .then(function() { + return client.one('INSERT INTO value_by_hour(property, timestamp, count, max, min, avg) ' + + 'SELECT property, DATE_TRUNC(\'hour\', timestamp) AS timestamp, COUNT(value) AS count, MAX(value::NUMERIC) AS max, MIN(value::NUMERIC) AS min, AVG(value::NUMERIC) AS avg ' + + 'FROM value WHERE property=$1 AND DATE_TRUNC(\'hour\', timestamp)=DATE_TRUNC(\'hour\', $2::TIMESTAMPTZ) ' + + 'GROUP BY property, DATE_TRUNC(\'hour\', timestamp) ' + + 'ORDER BY 2, 1 ' + + 'RETURNING *;', [propertyId, timestamp]); + }); + } + + function updateMinuteAggregatesForProperty(propertyId, timestamp) { + return client.none('DELETE FROM value_by_minute WHERE property=$1 AND timestamp=DATE_TRUNC(\'minute\', $2::TIMESTAMPTZ)', [propertyId, timestamp]) + .then(function() { + return client.one('INSERT INTO value_by_minute(property, timestamp, count, max, min, avg) ' + + 'SELECT property, DATE_TRUNC(\'minute\', timestamp) AS timestamp, COUNT(value) AS count, MAX(value::NUMERIC) AS max, MIN(value::NUMERIC) AS min, AVG(value::NUMERIC) AS avg ' + + 'FROM value ' + + 'WHERE property=$1 AND DATE_TRUNC(\'minute\', timestamp)=DATE_TRUNC(\'minute\', $2::TIMESTAMPTZ) ' + + 'GROUP BY property, DATE_TRUNC(\'minute\', timestamp) ' + + 'ORDER BY 2, 1 ' + + 'RETURNING *;', [propertyId, timestamp]); + }); } return { @@ -40,7 +65,9 @@ function DillerDao(client) { updatePropertyName: updatePropertyName, updatePropertyDescription: updatePropertyDescription, - insertValue: insertValue + insertValue: insertValue, + updateHourAggregatesForProperty: updateHourAggregatesForProperty, + updateMinuteAggregatesForProperty: updateMinuteAggregatesForProperty } } |