From ecb664a8550ee787a593db6cf45907100a875a54 Mon Sep 17 00:00:00 2001 From: Trygve Laugstøl Date: Fri, 30 Oct 2015 19:45:22 +0100 Subject: o Splitting the value field into value_numeric and value_text. --- migrations/20151030181628-types-values.js | 30 ++++++++++++++++++++++ .../sqls/20151030181628-types-values-down.sql | 1 + migrations/sqls/20151030181628-types-values-up.sql | 5 ++++ src/Diller.js | 2 +- src/DillerDao.js | 19 +++++++------- web/static/app/templates/property.html | 6 ----- 6 files changed, 47 insertions(+), 16 deletions(-) create mode 100644 migrations/20151030181628-types-values.js create mode 100644 migrations/sqls/20151030181628-types-values-down.sql create mode 100644 migrations/sqls/20151030181628-types-values-up.sql diff --git a/migrations/20151030181628-types-values.js b/migrations/20151030181628-types-values.js new file mode 100644 index 0000000..2adec04 --- /dev/null +++ b/migrations/20151030181628-types-values.js @@ -0,0 +1,30 @@ +var dbm = global.dbm || require('db-migrate'); +var type = dbm.dataType; +var fs = require('fs'); +var path = require('path'); + +exports.up = function(db, callback) { + var filePath = path.join(__dirname + '/sqls/20151030181628-types-values-up.sql'); + fs.readFile(filePath, {encoding: 'utf-8'}, function(err,data){ + if (err) return callback(err); + console.log('received data: ' + data); + + db.runSql(data, function(err) { + if (err) return callback(err); + callback(); + }); + }); +}; + +exports.down = function(db, callback) { + var filePath = path.join(__dirname + '/sqls/20151030181628-types-values-down.sql'); + fs.readFile(filePath, {encoding: 'utf-8'}, function(err,data){ + if (err) return callback(err); + console.log('received data: ' + data); + + db.runSql(data, function(err) { + if (err) return callback(err); + callback(); + }); + }); +}; diff --git a/migrations/sqls/20151030181628-types-values-down.sql b/migrations/sqls/20151030181628-types-values-down.sql new file mode 100644 index 0000000..44f074e --- /dev/null +++ b/migrations/sqls/20151030181628-types-values-down.sql @@ -0,0 +1 @@ +/* Replace with your SQL commands */ \ No newline at end of file diff --git a/migrations/sqls/20151030181628-types-values-up.sql b/migrations/sqls/20151030181628-types-values-up.sql new file mode 100644 index 0000000..e0b5e61 --- /dev/null +++ b/migrations/sqls/20151030181628-types-values-up.sql @@ -0,0 +1,5 @@ +ALTER TABLE value +ADD COLUMN value_numeric NUMERIC; + +ALTER TABLE value +RENAME COLUMN value TO value_text; diff --git a/src/Diller.js b/src/Diller.js index fa2e308..e73bf46 100644 --- a/src/Diller.js +++ b/src/Diller.js @@ -12,7 +12,7 @@ function Diller(config, db) { return dao.insertValue(property.id, value) .then(function (res) { - log.info('typeof', typeof res.timestamp, res.timestamp, res.timestamp.getTime()); + // ignore the result updateAggregates(property.id, res.timestamp); return res; }); diff --git a/src/DillerDao.js b/src/DillerDao.js index b46e734..98b6e6e 100644 --- a/src/DillerDao.js +++ b/src/DillerDao.js @@ -2,7 +2,7 @@ function DillerDao(tx) { var deviceColumns = 'id, key, created_timestamp'; var propertyColumns = 'id, device, key, created_timestamp'; - var valueColumns = 'property, timestamp, value'; + var valueColumns = 'property, timestamp, value_text, value_numeric'; // ------------------------------------------------------------------------------------------------------------------- // Device @@ -58,21 +58,23 @@ function DillerDao(tx) { function valuesByPropertyId(propertyId, limit) { limit = limit || 10; - return tx.many('SELECT timestamp, value FROM value WHERE property=$1 ORDER BY timestamp DESC LIMIT $2', [propertyId, limit]); + return tx.many('SELECT timestamp, coalesce(value_numeric::text, value_text) AS value FROM value WHERE property=$1 ORDER BY timestamp DESC LIMIT $2', [propertyId, limit]); } function insertValue(propertyId, value) { - return tx.one('INSERT INTO value(property, timestamp, value) VALUES($1, CURRENT_TIMESTAMP, $2) RETURNING timestamp', [propertyId, value]); + var value_numeric = parseFloat(value) || undefined, + value_text = value_numeric ? null : value; + + return tx.one('INSERT INTO value(property, timestamp, value_text, value_numeric) VALUES($1, CURRENT_TIMESTAMP, $2, $3::NUMERIC) RETURNING timestamp', [propertyId, value_text, value_numeric]); } function updateHourAggregatesForProperty(propertyId, timestamp) { return tx.none('DELETE FROM value_by_hour WHERE property=$1 AND timestamp=DATE_TRUNC(\'hour\', $2::TIMESTAMPTZ)', [propertyId, timestamp]) .then(function() { return tx.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) ' + + 'SELECT property, DATE_TRUNC(\'hour\', timestamp) AS timestamp, COUNT(value_numeric) 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) AND value_numeric IS NOT NULL ' + 'GROUP BY property, DATE_TRUNC(\'hour\', timestamp) ' + - 'ORDER BY 2, 1 ' + 'RETURNING *;', [propertyId, timestamp]); }); } @@ -81,11 +83,10 @@ function DillerDao(tx) { return tx.none('DELETE FROM value_by_minute WHERE property=$1 AND timestamp=DATE_TRUNC(\'minute\', $2::TIMESTAMPTZ)', [propertyId, timestamp]) .then(function() { return tx.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 ' + + 'SELECT property, DATE_TRUNC(\'minute\', timestamp) AS timestamp, COUNT(value_numeric) 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) ' + + 'WHERE property=$1 AND DATE_TRUNC(\'minute\', timestamp)=DATE_TRUNC(\'minute\', $2::TIMESTAMPTZ) AND value_numeric IS NOT NULL ' + 'GROUP BY property, DATE_TRUNC(\'minute\', timestamp) ' + - 'ORDER BY 2, 1 ' + 'RETURNING *;', [propertyId, timestamp]); }); } diff --git a/web/static/app/templates/property.html b/web/static/app/templates/property.html index 5f925e7..65a66e8 100644 --- a/web/static/app/templates/property.html +++ b/web/static/app/templates/property.html @@ -31,12 +31,6 @@ {{v.value}} - - - {{v.timestamp | date:'medium'}} - {{v.value}} - - -- cgit v1.2.3