diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/DillerDao.js | 46 | ||||
| -rw-r--r-- | src/DillerTx.js | 2 | ||||
| -rw-r--r-- | src/types.js | 1 | ||||
| -rw-r--r-- | src/web/DillerWeb.js | 38 |
4 files changed, 78 insertions, 9 deletions
diff --git a/src/DillerDao.js b/src/DillerDao.js index cf4c75b..7620541 100644 --- a/src/DillerDao.js +++ b/src/DillerDao.js @@ -4,7 +4,7 @@ var _ = require('lodash'); * @param tx {PgTx} * @class */ -function DillerDao(tx) { +function DillerDao(tx, as) { var deviceColumns = ['id', 'created_timestamp', 'key', 'name', 'description']; var deviceStatusColumns = ['device', 'online', 'timestamp', 'host']; @@ -176,8 +176,47 @@ function DillerDao(tx) { // ------------------------------------------------------------------------------------------------------------------- function valuesByPropertyId(propertyId, limit) { - limit = limit || 10; - return tx.manyOrNone('SELECT timestamp, coalesce(value_numeric::text, value_text) AS value FROM value WHERE property=$1 ORDER BY timestamp DESC LIMIT $2', [propertyId, limit]); + var sql = 'SELECT timestamp, coalesce(value_numeric::text, value_text) AS value FROM value WHERE property=$1'; + var args = [propertyId]; + + sql += ' ORDER BY timestamp DESC'; + + if (limit) { + args.push(limit); + sql += ' LIMIT $' + args.length; + } + + return tx.manyOrNone(sql, args); + } + + function aggregateValuesByPropertyId(propertyId, level, from, to) { + var sql, args = [level, as.date(from), as.date(to), propertyId]; + + if (level == 'hour') { + // TODO: use correct table instead of querying raw table + } else { + throw 'Unsupported level: ' + level; + } + + sql = 'with g as (select * from generate_series($2::timestamp, $3::timestamp, (\'1 \' || $1)::interval) as times(ts)),\n' + + 'v as (select\n' + + ' date_trunc($1, timestamp) as ts,\n' + + ' count(timestamp)::real as count,\n' + + ' min(value_numeric)::real as min,\n' + + ' max(value_numeric)::real as max,\n' + + ' avg(value_numeric)::real as avg\n' + + 'FROM value\n' + + 'WHERE timestamp >= $2::timestamp\n' + + ' AND timestamp < $3::timestamp\n' + + ' AND property=$4\n' + + ' AND value_numeric is not null\n' + + ' GROUP BY date_trunc($1, timestamp)\n' + + ')\n' + + 'select g.ts as timestamp, v.count, v.min, v.max, v.avg\n' + + 'from g left outer join v on g.ts = v.ts\n' + + 'order by 1'; + + return tx.manyOrNone(sql, args); } function insertValue(propertyId, timestamp, value) { @@ -234,6 +273,7 @@ function DillerDao(tx) { updateProperty: updateProperty, valuesByPropertyId: valuesByPropertyId, + aggregateValuesByPropertyId: aggregateValuesByPropertyId, insertValue: insertValue, updateHourAggregatesForProperty: updateHourAggregatesForProperty, updateMinuteAggregatesForProperty: updateMinuteAggregatesForProperty diff --git a/src/DillerTx.js b/src/DillerTx.js index 0f6b2ea..f1caa09 100644 --- a/src/DillerTx.js +++ b/src/DillerTx.js @@ -46,7 +46,7 @@ function _DillerTx(config) { var con = pgp(config.postgresqlConfig); return con.tx(function (pg) { - var dao = new _DillerDao(con); + var dao = new _DillerDao(con, pgp.as); var diller = new _Diller(config, con, dao); return action(pg, dao, diller) }); diff --git a/src/types.js b/src/types.js index 8c6300c..9856036 100644 --- a/src/types.js +++ b/src/types.js @@ -74,6 +74,7 @@ var HttpReq = {}; HttpReq.prototype.body = {}; HttpReq.prototype.headers = {}; HttpReq.prototype.params = {}; +HttpReq.prototype.query = {}; var HttpRes = {}; /** diff --git a/src/web/DillerWeb.js b/src/web/DillerWeb.js index 396c2f8..b42e47e 100644 --- a/src/web/DillerWeb.js +++ b/src/web/DillerWeb.js @@ -1,8 +1,11 @@ var express = require('express'); +var moment = require('moment'); var bodyParser = require('body-parser'); var _ = require('lodash'); var di = require('di'); +var isoFormat = 'YYYY-MM-DDTHH:mm:ss'; + /** * @param {DillerConfig} config * @param {DillerMqttClient} mqttClient @@ -105,14 +108,33 @@ function DillerWeb(config, mqttClient, tx) { * @param {HttpRes} res */ function getValues(req, res) { + var from = req.query.from && moment(req.query.from, isoFormat); + var to = req.query.to && moment(req.query.to, isoFormat); + var limit = req.query.limit || 10; + tx(function (tx, dao) { - var propertyId = req.params.propertyId; - return dao.valuesByPropertyId(propertyId, 10); + var propertyId = Number(req.params.propertyId); + + if (from || to) { + from = (from || moment()).startOf('hour'); + to = (to || moment()).startOf('hour'); + + if (typeof propertyId !== 'number' || !from.isValid() || !to.isValid()) { + log.info('getValues: Invalid parameters: propertyId', propertyId, 'from', from.toISOString(), 'to', to.toISOString()); + return Promise.reject('Invalid parameters: ' + typeof propertyId); + } else { + log.info('getValues: propertyId', propertyId, 'from', from.toISOString(), 'to', to.toISOString()); + + return dao.aggregateValuesByPropertyId(propertyId, 'hour', from.toDate(), to.toDate()); + } + } else { + return dao.valuesByPropertyId(propertyId, limit); + } }).then(function (values) { res.json({values: values}); }, function (err) { log.warn('fail', err); - res.status(500).json({message: 'fail'}); + res.status(500).json({message: typeof err === 'string' ? err : 'fail'}); }); } @@ -254,7 +276,7 @@ function DillerWeb(config, mqttClient, tx) { s += _.map(calls, function (call) { - var s = ' function ' + call.name + '(' + call.keys.join(', ') + ') {\n' + + var s = ' function ' + call.name + 'Req(' + call.keys.join(', ') + ') {\n' + ' var req = {};\n' + ' req.method = \'' + call.method + '\';\n' + ' req.url = baseUrl + \'/api' + call.path + '\';\n'; @@ -268,6 +290,12 @@ function DillerWeb(config, mqttClient, tx) { } s += + ' return req;\n' + + ' }\n' + + '\n'; + + s += ' function ' + call.name + '(' + call.keys.join(', ') + ') {\n' + + ' var req = ' + call.name + 'Req(' + call.keys.join(', ') + ');\n' + ' return $http(req);\n' + ' }\n'; @@ -276,7 +304,7 @@ function DillerWeb(config, mqttClient, tx) { s += '\n'; s += ' return {\n'; s += _.map(calls, function (call) { - return ' ' + call.name + ': ' + call.name + return ' ' + call.name + 'Req: ' + call.name + 'Req,\n ' + call.name + ': ' + call.name }).join(',\n'); s += '\n'; s += ' };\n'; |