diff options
Diffstat (limited to 'src/DillerDao.js')
| -rw-r--r-- | src/DillerDao.js | 86 |
1 files changed, 81 insertions, 5 deletions
diff --git a/src/DillerDao.js b/src/DillerDao.js index 34f4205..cf4c75b 100644 --- a/src/DillerDao.js +++ b/src/DillerDao.js @@ -6,9 +6,70 @@ var _ = require('lodash'); */ function DillerDao(tx) { - var deviceColumns = 'id, created_timestamp, key, name, description'; + var deviceColumns = ['id', 'created_timestamp', 'key', 'name', 'description']; + var deviceStatusColumns = ['device', 'online', 'timestamp', 'host']; var propertyColumns = 'id, created_timestamp, device, key, name, description, last_value, last_timestamp'; + function cols(columns, select_prefix, as_prefix) { + var s; + if (select_prefix) { + as_prefix = as_prefix || select_prefix; + for (var i = 0; i < columns.length; i++) { + var c = columns[i]; + if (!s) { + s = ''; + } else { + s += ', '; + } + + s += select_prefix + '.' + c + ' AS ' + as_prefix + '_' + c; + } + return s; + } + + return columns.join(', '); + } + + function unjoin(main) { + var children = Array.prototype.slice.call(arguments).slice(1); + + function convertRow(row) { + if (!row) { + return row; + } + var r = {}; + for (var field in row) { + var f = '' + field; + if (f.startsWith(main + '_')) { + r[f.substring(main.length + 1)] = row[f]; + } else { + for (var i = 0; i < children.length; i++) { + var child = children[i]; + var prefix = child + '_'; + + if (f.startsWith(prefix)) { + var o = r[child]; + if (!o) { + o = r[child] = {}; + } + + o[f.substr(prefix.length)] = row[f]; + } + } + } + } + return r; + } + + return function (res) { + if (Array.isArray(res)) { + return res.map(convertRow); + } else { + return convertRow(res); + } + } + } + // ------------------------------------------------------------------------------------------------------------------- // Device // ------------------------------------------------------------------------------------------------------------------- @@ -17,19 +78,26 @@ function DillerDao(tx) { * @returns {Promise} */ function devices() { - return tx.manyOrNone("SELECT " + deviceColumns + " FROM device"); + var sql = 'SELECT ' + cols(deviceColumns, 'd') + ', ' + cols(deviceStatusColumns, 'ds', 'status') + " FROM device d LEFT JOIN device_status ds on (d.id = ds.device)"; + return tx.manyOrNone(sql).then(unjoin('d', 'status')); } function deviceById(id) { - return tx.one("SELECT " + deviceColumns + " FROM device WHERE id=$1", id); + var sql = 'SELECT ' + cols(deviceColumns, 'd') + ', ' + cols(deviceStatusColumns, 'ds', 'status') + " FROM device d LEFT JOIN device_status ds on (d.id = ds.device) WHERE d.id=$1"; + return tx.one(sql, [id]).then(unjoin('d', 'status')); } function deviceByKey(key) { - return tx.oneOrNone("SELECT " + deviceColumns + " FROM device WHERE key=$1", key); + var sql = 'SELECT ' + cols(deviceColumns, 'd') + ', ' + cols(deviceStatusColumns, 'ds', 'status') + " FROM device d LEFT JOIN device_status ds on (d.id = ds.device) WHERE d.key=$1"; + return tx.oneOrNone(sql, [key]).then(unjoin('d', 'status')); } function insertDevice(key) { - return tx.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 " + cols(deviceColumns), key); + } + + function insertDeviceStatus(device, online, timestamp, host) { + return tx.none("INSERT INTO device_status(device, online, timestamp, host) VALUES($1, $2, $3, $4)", [device, online, timestamp, host]); } function updateDevice(id, attributes) { @@ -55,6 +123,11 @@ function DillerDao(tx) { return tx.none(sql, values); } + function updateDeviceStatus(device, timestamp, online, host) { + var sql = 'UPDATE device_status SET timestamp=$2, online=$3, host=$4 WHERE device=$1'; + return tx.none(sql, [device, timestamp, online, host]); + } + // ------------------------------------------------------------------------------------------------------------------- // Device Property // ------------------------------------------------------------------------------------------------------------------- @@ -151,6 +224,9 @@ function DillerDao(tx) { insertDevice: insertDevice, updateDevice: updateDevice, + insertDeviceStatus: insertDeviceStatus, + updateDeviceStatus: updateDeviceStatus, + devicePropertyById: devicePropertyById, devicePropertyByDeviceIdAndKey: devicePropertyByDeviceIdAndKey, devicePropertiesByDeviceId: devicePropertiesByDeviceId, |