aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--migrations/20151030181628-types-values.js30
-rw-r--r--migrations/sqls/20151030181628-types-values-down.sql1
-rw-r--r--migrations/sqls/20151030181628-types-values-up.sql5
-rw-r--r--src/Diller.js2
-rw-r--r--src/DillerDao.js19
-rw-r--r--web/static/app/templates/property.html6
6 files changed, 47 insertions, 16 deletions
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 @@
<td>{{v.value}}</td>
</tr>
</tbody>
- <tbody>
- <tr ng-repeat="v in ctrl.values">
- <td>{{v.timestamp | date:'medium'}}</td>
- <td>{{v.value}}</td>
- </tr>
- </tbody>
</table>
</div>
generated by cgit v1.2.3 (git 2.46.0) at 2024-12-23 03:00:24 +0000