def store(self):
database = Database()
database.open()
if self.id:
query = "SELECT COUNT(id) FROM appliances_network WHERE id = '@@id@@';"
query = self.__replaceSqlQueryToken(query)
queryResult = database.executeReadQuery(query)
if int(queryResult[0][0]) > 0:
query = "UPDATE appliances_network SET name = '@@name@@', label = '@@label@@', brand = '@@brand@@', model = '@@model@@', smart_appliance = '@@smart_appliance@@', protocol = '@@protocol@@', address = '@@address@@', timeslots = '@@timeslots@@' WHERE id = '@@id@@';"
else:
query = "SELECT COUNT(id) FROM appliances_network WHERE name = '@@name@@';"
query = self.__replaceSqlQueryToken(query)
queryResult = database.executeReadQuery(query)
if int(queryResult[0][0]) > 0:
query = "UPDATE appliances_network SET name = '@@name@@', label = '@@label@@', brand = '@@brand@@', model = '@@model@@', smart_appliance = '@@smart_appliance@@', protocol = '@@protocol@@', address = '@@address@@', timeslots = '@@timeslots@@' WHERE name = '@@name@@';"
else:
query = "INSERT INTO appliances_network (name, label, brand, model, smart_appliance, protocol, address, timeslots) VALUES ('@@name@@', '@@label@@', '@@brand@@', '@@model@@', '@@smart_appliance@@', '@@protocol@@', '@@address@@', '@@timeslots@@');"
query = self.__replaceSqlQueryToken(query)
database.executeWriteQuery(query)
database.close()
def delete(self): database = Database() database.open() query = "DELETE FROM settings WHERE s_key = '@@s_key@@';" query = self.__replaceSqlQueryToken(query) database.executeWriteQuery(query) database.close()
def delete(self):
database = Database()
database.open()
query = "DELETE FROM feedbacks WHERE id = '@@id@@';"
query = self.__replaceSqlQueryToken(query)
database.executeWriteQuery(query)
database.close()
def deleteRoom(self, room):
database = Database()
database.open()
query = "DELETE FROM rooms_groups WHERE room_name = '@@room_name@@' AND building_name = '@@building_name@@' AND group_id = '@@id@@';"
query = self.__replaceSqlQueryToken(query)
query = query.replace("@@room_name@@", str(room.roomName))
database.executeWriteQuery(query)
database.close()
def store(self):
database = Database()
database.open()
query = "INSERT INTO sessions (session_key, user_uuid, expire_timestamp) VALUES ('@@session_key@@', '@@user_uuid@@', '@@expire_timestamp@@');"
query = self.__replaceSqlQueryToken(query)
database.executeWriteQuery(query)
database.close()
def addRoom(self, room):
database = Database()
database.open()
query = "INSERT INTO rooms_groups (group_id, building_name, room_name) VALUES ('@@id@@', '@@building_name@@', '@@room_name@@');"
query = self.__replaceSqlQueryToken(query)
query = query.replace("@@room_name@@", str(room.roomName))
database.executeWriteQuery(query)
database.close()
def delete(self):
database = Database()
database.open()
query = "DELETE FROM appliances_network WHERE id = '@@id@@';"
query = self.__replaceSqlQueryToken(query)
database.executeWriteQuery(query)
database.close()
def delete(self):
print "\t\t\t\t\t\t\t\tTODO (" + self.__class__.__name__ + ":" + sys._getframe().f_code.co_name + ") : Consistency check not performed - class Building"
database = Database()
database.open()
query = "DELETE FROM buildings WHERE building_name = '@@building_name@@';"
query = self.__replaceSqlQueryToken(query)
database.executeWriteQuery(query)
database.close()
def delete(self):
print "\t\t\t\t\t\t\t\tTODO (" + self.__class__.__name__ + ":" + sys._getframe().f_code.co_name + ") : Consistency check not performed - Trigger class"
database = Database()
database.open()
query = "DELETE FROM notifications WHERE id = '@@id@@';"
query = self.__replaceSqlQueryToken(query)
database.executeWriteQuery(query)
database.close()
def addUser(self, user):
self.isClassInitialized()
database = Database()
database.open()
query = "INSERT INTO users_rooms (room_name, building_name, user_uuid) VALUES ('@@room_name@@', '@@building_name@@', '@@user_uuid@@');"
query = self.__replaceSqlQueryToken(query)
query = query.replace("@@user_uuid@@", str(user.uuid))
database.executeWriteQuery(query)
database.close()
def delete(self):
print "\t\t\t\t\t\t\t\tTODO (" + self.__class__.__name__ + ":" + sys._getframe().f_code.co_name + ") : Consistency check not performed into table users_rooms"
database = Database()
database.open()
query = "DELETE FROM users WHERE uuid = '@@uuid@@'"
query = self.__replaceSqlQueryToken(query)
database.executeWriteQuery(query)
database.close()
def deleteUser(self, user):
self.isClassInitialized()
database = Database()
database.open()
query = "DELETE FROM users_rooms WHERE room_name = '@@room_name@@' AND building_name = '@@building_name@@' AND user_uuid = '@@user_uuid@@';"
query = self.__replaceSqlQueryToken(query)
query = query.replace("@@user_uuid@@", str(user.uuid))
database.executeWriteQuery(query)
database.close()
def delete(self):
if not (self.buildingName and self.roomName and self.ruleId): raise MissingInputDataError("Missing input to get a rule priority")
print "\t\t\t\t\t\t\t\tTODO (" + self.__class__.__name__ + ":" + sys._getframe().f_code.co_name + ") : Consistency check not performed - RulePriority class"
database = Database()
database.open()
query = "DELETE FROM rules_priority WHERE building_name = '@@building_name@@' AND room_name = '@@room_name@@' AND rule_id = '@@rule_id@@';"
query = self.__replaceSqlQueryToken(query)
database.executeWriteQuery(query)
database.close()
def addTrigger(self, trigger):
print "\t\t\t\t\t\t\t\tTODO (" + self.__class__.__name__ + ":" + sys._getframe().f_code.co_name + ") : non yet tested"
self.isClassInitialized()
database = Database()
database.open()
query = "INSERT INTO rooms_triggers (room_name, building_name, trigger_id) VALUES ('@@room_name@@', '@@building_name@@', '@@trigger_id@@');"
query = self.__replaceSqlQueryToken(query)
query = query.replace("@@trigger_id@@", str(trigger.id))
database.executeWriteQuery(query)
database.close()
def delete(self):
print "\t\t\t\t\t\t\t\tTODO (" + self.__class__.__name__ + ":" + sys._getframe().f_code.co_name + ") : Consistency check not performed into table users_rooms"
self.isClassInitialized()
database = Database()
database.open()
query = "DELETE FROM rooms WHERE room_name = '@@room_name@@' AND building_name = '@@building_name@@';"
query = self.__replaceSqlQueryToken(query)
database.executeWriteQuery(query)
database.close()
def deleteAction(self, action):
print "\t\t\t\t\t\t\t\tTODO (" + self.__class__.__name__ + ":" + sys._getframe().f_code.co_name + ") : non yet tested"
self.isClassInitialized()
database = Database()
database.open()
query = "DELETE FROM rooms_actions WHERE room_name = '@@room_name@@' AND building_name = '@@building_name@@' AND action_id = '@@action_id@@';"
query = self.__replaceSqlQueryToken(query)
query = query.replace("@@action_id@@", str(action.id))
database.executeWriteQuery(query)
database.close()
def store(self):
database = Database()
database.open()
query = "SELECT COUNT(building_name) FROM buildings WHERE building_name = '@@building_name@@';"
query = self.__replaceSqlQueryToken(query)
queryResult = database.executeReadQuery(query)
if int(queryResult[0][0]) > 0:
query = "UPDATE buildings SET label = '@@label@@', description = '@@description@@' WHERE building_name = '@@building_name@@';"
else:
query = "INSERT INTO buildings (building_name, label, description) VALUES ('@@building_name@@', '@@label@@', '@@description@@');"
query = self.__replaceSqlQueryToken(query)
database.executeWriteQuery(query)
database.close()
def store(self):
database = Database()
database.open()
query = "SELECT COUNT(id) FROM triggers WHERE id = '@@id@@';"
query = self.__replaceSqlQueryToken(query)
queryResult = database.executeReadQuery(query)
if int(queryResult[0][0]) > 0:
query = "UPDATE triggers SET category = '@@category@@', trigger_name = '@@trigger_name@@', rule_consequent = '@@rule_consequent@@', description = '@@description@@' WHERE id = '@@id@@';"
else:
query = "INSERT INTO triggers (category, trigger_name, rule_consequent, description) VALUES ('@@category@@', '@@trigger_name@@', '@@rule_consequent@@', '@@description@@');"
query = self.__replaceSqlQueryToken(query)
database.executeWriteQuery(query)
database.close()
def store(self):
database = Database()
database.open()
query = "SELECT COUNT(id) FROM mturk WHERE id = '@@id@@';"
query = self.__replaceSqlQueryToken(query)
queryResult = database.executeReadQuery(query)
if int(queryResult[0][0]) > 0:
query = "UPDATE mturk SET day = '@@day@@', user_uuid = '@@user_uuid@@', token = '@@token@@' WHERE id = '@@id@@';"
else:
query = "INSERT INTO mturk (day, user_uuid, token) VALUES ('@@day@@', '@@user_uuid@@', '@@token@@');"
query = self.__replaceSqlQueryToken(query)
database.executeWriteQuery(query)
database.close()
def store(self): updateQuery = False database = Database() database.open() query = "SELECT COUNT(id) FROM rules WHERE id = '@@id@@';" query = self.__replaceSqlQueryToken(query) queryResult = database.executeReadQuery(query) if self.lastEditTimestamp == None: self.lastEditTimestamp = datetime.datetime.now() if self.creationTimestamp == None: self.creationTimestamp = datetime.datetime.now() if int(queryResult[0][0]) > 0: self.lastEditTimestamp = datetime.datetime.now() query = """UPDATE rules SET priority = '@@priority@@', category = '@@category@@', building_name = '@@building_name@@', group_id = '@@group_id@@', room_name = '@@room_name@@', author_uuid = '@@author_uuid@@', antecedent = '@@antecedent@@', consequent = '@@consequent@@', enabled = '@@enabled@@', deleted = '@@deleted@@', creation_timestamp = '@@creation_timestamp@@', last_edit_timestamp = '@@last_edit_timestamp@@' WHERE id = '@@id@@';""" updateQuery = True else: query = """INSERT INTO rules (priority, category, building_name, group_id, room_name, author_uuid, antecedent, consequent, enabled, deleted, creation_timestamp, last_edit_timestamp) VALUES ( '@@priority@@', '@@category@@', '@@building_name@@', '@@group_id@@', '@@room_name@@', '@@author_uuid@@', '@@antecedent@@', '@@consequent@@', '@@enabled@@', '@@deleted@@', '@@creation_timestamp@@', '@@last_edit_timestamp@@');""" query = self.__replaceSqlQueryToken(query) database.executeWriteQuery(query) self.id = int(database.getLastInsertedId()) if not self.id else self.id database.close() if not self.groupId and not updateQuery: from app.backend.model.rulePriority import RulePriority rulePriority = RulePriority(buildingName = self.buildingName, roomName = self.roomName, ruleId = self.id, rulePriority = self.__priority) rulePriority.store()
def setActiveRule(self, buildingName, roomName, ruleId):
if not (buildingName and roomName and ruleId):
MissingInputDataError("Impossible to set an active rule without buildingName roomName or ruleId")
query = "INSERT INTO `active_rules` (`building_name`, `room_name`, `rule_id`) VALUES ('@@building_name@@', '@@room_name@@', @@rule_id@@);"
query = query.replace("@@building_name@@", buildingName)
query = query.replace("@@room_name@@", roomName)
query = query.replace("@@rule_id@@", str(ruleId))
database = Database()
database.open()
database.executeWriteQuery(query)
database.close()
def store(self):
database = Database()
database.open()
query = "SELECT COUNT(id) FROM groups WHERE id = '@@id@@' AND building_name = '@@building_name@@';"
query = self.__replaceSqlQueryToken(query)
queryResult = database.executeReadQuery(query)
if int(queryResult[0][0]) > 0:
query = "UPDATE groups SET description = '@@description@@', cross_rooms_validation = '@@cross_rooms_validation@@', cross_rooms_validation_categories = '@@cross_rooms_validation_categories@@' WHERE id = '@@id@@' AND building_name = '@@building_name@@';"
else:
query = "INSERT INTO groups (building_name, description, cross_rooms_validation, cross_rooms_validation_categories) VALUES ('@@building_name@@', '@@description@@', '@@cross_rooms_validation@@', '@@cross_rooms_validation_categories@@');"
query = self.__replaceSqlQueryToken(query)
database.executeWriteQuery(query)
self.id = int(database.getLastInsertedId()) if not self.id else self.id
database.close()
def store(self):
database = Database()
database.open()
query = "SELECT COUNT(building_name) FROM rules_priority WHERE building_name = '@@building_name@@' AND room_name = '@@room_name@@' AND rule_id = '@@rule_id@@';"
query = self.__replaceSqlQueryToken(query)
queryResult = database.executeReadQuery(query)
if int(queryResult[0][0]) > 0:
query = "UPDATE rules_priority SET rule_priority = '@@rule_priority@@' WHERE building_name = '@@building_name@@' AND room_name = '@@room_name@@' AND rule_id = '@@rule_id@@';"
else:
query = "INSERT INTO rules_priority (building_name, room_name, rule_id, rule_priority) VALUES ('@@building_name@@', '@@room_name@@', '@@rule_id@@', '@@rule_priority@@');"
query = self.__replaceSqlQueryToken(query)
database.executeWriteQuery(query)
database.close()
def store(self):
self.isClassInitialized()
database = Database()
database.open()
query = "SELECT COUNT(room_name) FROM rooms WHERE room_name = '@@room_name@@' AND building_name = '@@building_name@@';"
query = self.__replaceSqlQueryToken(query)
queryResult = database.executeReadQuery(query)
if int(queryResult[0][0]) > 0:
query = "UPDATE rooms SET description = '@@description@@' WHERE room_name = '@@room_name@@' AND building_name = '@@building_name@@';"
else:
query = "INSERT INTO rooms (room_name, building_name, description) VALUES ('@@room_name@@', '@@building_name@@', '@@description@@');"
query = self.__replaceSqlQueryToken(query)
database.executeWriteQuery(query)
database.close()
def store(self):
database = Database()
database.open()
query = "SELECT COUNT(s_key) FROM settings WHERE s_key = '@@s_key@@';"
query = self.__replaceSqlQueryToken(query)
queryResult = database.executeReadQuery(query)
if int(queryResult[0][0]) > 0:
query = "UPDATE settings SET s_value = '@@s_value@@' WHERE s_key = '@@s_key@@';"
else:
query = "INSERT INTO settings (s_key, s_value) VALUES ('@@s_key@@', '@@s_value@@');"
query = self.__replaceSqlQueryToken(query)
database.executeWriteQuery(query)
database.close()
def store(self):
if not self.feedbackTimestamp:
self.feedbackTimestamp = datetime.datetime.now()
database = Database()
database.open()
query = "SELECT COUNT(id) FROM feedbacks WHERE id = '@@id@@';"
query = self.__replaceSqlQueryToken(query)
queryResult = database.executeReadQuery(query)
if int(queryResult[0][0]) > 0:
query = "UPDATE feedbacks SET author_uuid = '@@author_uuid@@', alternative_contact = '@@alternative_contact@@', score = '@@score@@', message = '@@message@@', feedback_timestamp = '@@feedback_timestamp@@' WHERE id = '@@id@@';"
else:
query = "INSERT INTO feedbacks (author_uuid, alternative_contact, score, message, feedback_timestamp) VALUES ('@@author_uuid@@', '@@alternative_contact@@', '@@score@@', '@@message@@', '@@feedback_timestamp@@');"
query = self.__replaceSqlQueryToken(query)
database.executeWriteQuery(query)
self.id = int(database.getLastInsertedId()) if not self.id else self.id
database.close()
def store(self):
if not self.registrationTimestamp:
self.registrationTimestamp = datetime.datetime.now()
database = Database()
database.open()
query = "SELECT COUNT(uuid) FROM users WHERE uuid = '@@uuid@@';"
query = self.__replaceSqlQueryToken(query)
queryResult = database.executeReadQuery(query)
if int(queryResult[0][0]) > 0:
query = "UPDATE users SET username = '******', email = '@@email@@', password = '******', person_name = '@@person_name@@', level = '@@level@@', registration_timestamp = '@@registration_timestamp@@' WHERE uuid = '@@uuid@@';"
else:
query = "INSERT INTO users (username, email, password, person_name, level, registration_timestamp) VALUES ('@@username@@','@@email@@', '@@password@@', '@@person_name@@', '@@level@@', '@@registration_timestamp@@');"
query = self.__replaceSqlQueryToken(query)
database.executeWriteQuery(query)
self.uuid = int(database.getLastInsertedId()) if not self.uuid else self.uuid
database.close()
def store(self):
if not self.sendTimestamp:
self.sendTimestamp = datetime.datetime.now()
if not self.messageRead:
self.messageRead = 0
database = Database()
database.open()
query = "SELECT COUNT(id) FROM notifications WHERE id = '@@id@@';"
query = self.__replaceSqlQueryToken(query)
queryResult = database.executeReadQuery(query)
if int(queryResult[0][0]) > 0:
query = "UPDATE notifications SET send_timestamp = '@@send_timestamp@@', message_subject = '@@message_subject@@', message_text = '@@message_text@@', recipient_uuid = '@@recipient_uuid@@', message_read = '@@message_read@@' WHERE id = '@@id@@';"
else:
query = "INSERT INTO notifications (send_timestamp, message_subject, message_text, recipient_uuid, message_read) VALUES ('@@send_timestamp@@', '@@message_subject@@', '@@message_text@@', '@@recipient_uuid@@', '@@message_read@@');"
query = self.__replaceSqlQueryToken(query)
database.executeWriteQuery(query)
database.close()
def resetActiveRules(self):
database = Database()
database.open()
database.executeWriteQuery("TRUNCATE TABLE active_rules;")
database.close()
def resetNetwork(self):
database = Database()
database.open()
database.executeWriteQuery("TRUNCATE TABLE appliances_network;")
database.close()
