diff options
Diffstat (limited to 'src/DillerDao.js')
| -rw-r--r-- | src/DillerDao.js | 63 |
1 files changed, 51 insertions, 12 deletions
diff --git a/src/DillerDao.js b/src/DillerDao.js index e0c69d3..c4d0e67 100644 --- a/src/DillerDao.js +++ b/src/DillerDao.js @@ -1,40 +1,74 @@ -function DillerDao(client) { +function DillerDao(tx) { var deviceColumns = 'id, key, created_timestamp'; var propertyColumns = 'id, device, key, created_timestamp'; + var valueColumns = 'property, timestamp, value'; + + // ------------------------------------------------------------------------------------------------------------------- + // Device + // ------------------------------------------------------------------------------------------------------------------- + + function devices() { + return tx.many("SELECT " + deviceColumns + " FROM device"); + } + + function deviceById(id) { + return tx.oneOrNone("SELECT " + deviceColumns + " FROM device WHERE id=$1", id); + } function deviceByKey(key) { - return client.oneOrNone("SELECT " + deviceColumns + " FROM device WHERE key=$1", key); + return tx.oneOrNone("SELECT " + deviceColumns + " FROM device WHERE key=$1", key); } function insertDevice(key) { - return client.one("INSERT INTO device(id, key, created_timestamp) VALUES(DEFAULT, $1, CURRENT_TIMESTAMP) RETURNING " + deviceColumns, key); + return tx.one("INSERT INTO device(id, key, created_timestamp) VALUES(DEFAULT, $1, CURRENT_TIMESTAMP) RETURNING " + deviceColumns, key); + } + + // ------------------------------------------------------------------------------------------------------------------- + // Device Property + // ------------------------------------------------------------------------------------------------------------------- + + function devicePropertyById(id) { + return tx.one('SELECT ' + propertyColumns + ' FROM device_property WHERE id=$1', [id]); } function devicePropertyByDeviceIdAndKey(deviceId, key) { - return client.oneOrNone('SELECT id FROM device_property WHERE device=$1 AND key=$2', [deviceId, key]); + return tx.oneOrNone('SELECT id FROM device_property WHERE device=$1 AND key=$2', [deviceId, key]); + } + + function devicePropertiesByDeviceId(deviceId) { + return tx.many('SELECT ' + propertyColumns + ' FROM device_property WHERE device=$1', [deviceId]); } function insertDeviceProperty(deviceId, key) { - return client.oneOrNone('INSERT INTO device_property(id, device, key, created_timestamp) VALUES(DEFAULT, $1, $2, CURRENT_TIMESTAMP) RETURNING ' + propertyColumns, [deviceId, key]); + return tx.oneOrNone('INSERT INTO device_property(id, device, key, created_timestamp) VALUES(DEFAULT, $1, $2, CURRENT_TIMESTAMP) RETURNING ' + propertyColumns, [deviceId, key]); } function updatePropertyName(id, name) { - return client.none('UPDATE device_property SET name=$1 WHERE id=$2', name, id); + return tx.none('UPDATE device_property SET name=$1 WHERE id=$2', name, id); } function updatePropertyDescription(id, description) { - return client.none('UPDATE device_property SET description=$1 WHERE id=$2', description, id); + return tx.none('UPDATE device_property SET description=$1 WHERE id=$2', description, id); + } + + // ------------------------------------------------------------------------------------------------------------------- + // Value + // ------------------------------------------------------------------------------------------------------------------- + + function valuesByPropertyId(propertyId, limit) { + limit = limit || 10; + return tx.many('SELECT timestamp, value FROM value WHERE property=$1 LIMIT $2', [propertyId, limit]); } function insertValue(propertyId, value) { - return client.one('INSERT INTO value(property, timestamp, value) VALUES($1, CURRENT_TIMESTAMP, $2) RETURNING timestamp', [propertyId, value]); + return tx.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]) + return tx.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) ' + + 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) ' + 'GROUP BY property, DATE_TRUNC(\'hour\', timestamp) ' + @@ -44,9 +78,9 @@ function DillerDao(client) { } function updateMinuteAggregatesForProperty(propertyId, timestamp) { - return client.none('DELETE FROM value_by_minute WHERE property=$1 AND timestamp=DATE_TRUNC(\'minute\', $2::TIMESTAMPTZ)', [propertyId, timestamp]) + return tx.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) ' + + 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 ' + 'FROM value ' + 'WHERE property=$1 AND DATE_TRUNC(\'minute\', timestamp)=DATE_TRUNC(\'minute\', $2::TIMESTAMPTZ) ' + @@ -57,14 +91,19 @@ function DillerDao(client) { } return { + devices: devices, + deviceById: deviceById, deviceByKey: deviceByKey, insertDevice: insertDevice, + devicePropertyById: devicePropertyById, devicePropertyByDeviceIdAndKey: devicePropertyByDeviceIdAndKey, + devicePropertiesByDeviceId: devicePropertiesByDeviceId, insertDeviceProperty: insertDeviceProperty, updatePropertyName: updatePropertyName, updatePropertyDescription: updatePropertyDescription, + valuesByPropertyId: valuesByPropertyId, insertValue: insertValue, updateHourAggregatesForProperty: updateHourAggregatesForProperty, updateMinuteAggregatesForProperty: updateMinuteAggregatesForProperty |