var fs = require("fs")
|
|
const mysql = require('mysql2/promise');
|
|
|
|
var db;
|
|
|
|
class Database {
|
|
connect() {
|
|
return new Promise(
|
|
(resolve, reject) => {
|
|
return mysql.createConnection({
|
|
host: 'localhost',
|
|
user: 'ledcontroller',
|
|
database: 'led'
|
|
}).then(res => {
|
|
db = res;
|
|
resolve()
|
|
})
|
|
.catch(err => {
|
|
reject(err)
|
|
})
|
|
})
|
|
}
|
|
|
|
getLamps() {
|
|
return db.execute("SELECT `light`.`PK_light` as id, `light`.`name`, `light`.`description`, `type`.`key` as type FROM `type`, `light` WHERE `light`.`FK_type` = `type`.`PK_type`;")
|
|
.then(res => {
|
|
return res[0]
|
|
});
|
|
}
|
|
|
|
getLampChannelConfig(id) {
|
|
|
|
console.log("SELECT `mapping-light`.`channel`, `mapping-light`.`key` \
|
|
FROM `light`, `mapping-light`, `board` \
|
|
WHERE `light`.`PK_light` = "+ id + " AND `mapping-light`.`FK_light` = " + id + " AND `mapping-light`.`FK_board` = `board`.`PK_board`")
|
|
|
|
return db.execute("SELECT `mapping-light`.`channel`, `mapping-light`.`key` \
|
|
FROM `light`, `mapping-light`, `board` \
|
|
WHERE `light`.`PK_light` = "+ id + " AND `mapping-light`.`FK_light` = " + id + " AND `mapping-light`.`FK_board` = `board`.`PK_board`")
|
|
.then(res => {
|
|
return res[0]
|
|
})
|
|
.catch(err => {
|
|
console.log("error fetching Lampchannelconfig from db")
|
|
})
|
|
}
|
|
|
|
getPresets() {
|
|
return db.execute("SELECT PK_preset as id, title, description from `preset`")
|
|
.then(res => {
|
|
return res[0]
|
|
})
|
|
}
|
|
|
|
setPreset(preset) {
|
|
return db.execute("INSERT INTO `led`.`preset` VALUES (NUll,'" + preset.name + "','" + preset.description + "');")
|
|
.then(res => {
|
|
let temp = ""
|
|
preset.settings.forEach((channel, index) => {
|
|
if (index != 0) {
|
|
temp += ","
|
|
}
|
|
temp += "(NULL," + channel.id + "," + res[0].insertId + "," + channel.value + ",'" + channel.key + "')"
|
|
})
|
|
return db.execute("INSERT INTO `led`.`mapping-preset` VALUES " + temp + ";")
|
|
.then(res => { return 200 })
|
|
.catch(err => {
|
|
console.log(err)
|
|
console.log("Inserting channels for preset failed, cleaning up preset...")
|
|
db.execute("DELETE FROM `led`.`preset` WHERE PK_preset=" + res[0].insertId)
|
|
.then(res => { console.log("Cleaning up was successfull!") })
|
|
.catch(err => {
|
|
console.log("Cleaning up failed: ")
|
|
console.log(err)
|
|
})
|
|
return 500
|
|
})
|
|
})
|
|
}
|
|
|
|
getPreset(preset) {
|
|
return db.execute("SELECT board.address, `mapping-light`.channel, `mapping-preset`.value \
|
|
FROM `led`.`mapping-light`, `led`.`mapping-preset`, `board` \
|
|
WHERE `mapping-preset`.`FK_preset` = " + preset + " \
|
|
AND `mapping-preset`.channel = `mapping-light`.key \
|
|
AND `mapping-preset`.`FK_mapping-light` = `mapping-light`.`FK_light`;")
|
|
.then(res => {
|
|
return res[0]
|
|
})
|
|
}
|
|
|
|
deletePreset(id) {
|
|
console.log(id)
|
|
return db.execute("DELETE FROM `led`.`preset` WHERE PK_preset = " + id)
|
|
.then(res => {
|
|
|
|
})
|
|
.catch(err => {
|
|
console.log(err)
|
|
})
|
|
|
|
//return db.execute("DELETE FROM ")
|
|
}
|
|
|
|
getBoards() {
|
|
return db.execute("SELECT PK_board as boardID, address from `led`.`board`")
|
|
.then(res => {
|
|
return res[0]
|
|
})
|
|
}
|
|
|
|
getLampTypes() {
|
|
return db.execute("SELECT PK_type as typeID, name, channel from `led`.`type`")
|
|
.then(res => {
|
|
return res[0]
|
|
})
|
|
}
|
|
|
|
deleteLamp(lampID) {
|
|
return db.execute("DELETE FROM `mapping-preset` WHERE `FK_mapping-light`=" + lampID + ";")
|
|
.then(() => {
|
|
db.execute("DELETE FROM `mapping-light` WHERE `FK_light`=" + lampID + ";")
|
|
.then(() => {
|
|
db.execute("DELETE FROM light WHERE PK_light=" + lampID + ";")
|
|
})
|
|
})
|
|
}
|
|
|
|
createLamp(light) {
|
|
let dbRequests = []
|
|
|
|
console.log("+++ createLamp")
|
|
console.log(light)
|
|
console.log("SELECT PK_type FROM `led`.`type` WHERE `led`.`type`.`PK_type` = " + light.typeID + ";")
|
|
return db.execute("SELECT PK_type FROM `led`.`type` WHERE `led`.`type`.`PK_type` = " + light.typeID + ";")
|
|
.then(res => {
|
|
db.execute("INSERT INTO `led`.`light` (`FK_type`,`name`,`description`) VALUES ("+res[0][0]['PK_type'] + ",\"" + light.title + "\",\"" + light.description + "\");")
|
|
.then((res2) => {
|
|
switch (light.typeID) {
|
|
case 1:
|
|
this.addRGBChannels(light.channels, res2[0]['insertId'], light.boardID)
|
|
break;
|
|
default:
|
|
console.log("Unknown lighttype")
|
|
return reject()
|
|
}
|
|
})
|
|
.catch(err => {
|
|
console.log("failed to add Lamp inner: " + err)
|
|
})
|
|
})
|
|
.catch(err => {
|
|
console.log("failed to add lamp: " + err)
|
|
})
|
|
}
|
|
|
|
addRGBChannels(channels, insertID, boardID) {
|
|
|
|
let command = "INSERT INTO `led`.`mapping-light` (`FK_light`,`FK_board`,`channel`,`key`, `description`) VALUES"
|
|
|
|
Object.keys(channels).forEach(channel => {
|
|
command += "(" + insertID + "," + boardID + "," + channels[channel] + ",\"" + channel + "\",\".\"),"
|
|
})
|
|
command = command.substr(0, command.length - 1)
|
|
console.log(command)
|
|
return db.execute(command)
|
|
.then(res=> {
|
|
console.log("Created light")
|
|
})
|
|
.catch(err => {
|
|
console.log(err)
|
|
})
|
|
}
|
|
}
|
|
module.exports = Database
|