def retrieve(self): if self.id: query = "SELECT * FROM mturk WHERE id = '@@id@@';" if self.day != None and self.userUuid != None: query = "SELECT * FROM mturk WHERE day = '@@day@@' AND user_uuid = '@@user_uuid@@' ;" else: raise MissingInputDataError("Impossibile to query any Mturk with missing parameters") database = Database() database.open() query = self.__replaceSqlQueryToken(query) queryResult = database.executeReadQuery(query) if len(queryResult) > 0: self.id = queryResult[0][0] self.day = queryResult[0][1] self.userUuid = queryResult[0][2] self.token = queryResult[0][3] else: database.close() raise MturkNotFoundError("Impossibile to find any Mturk with the provided values") database.close()
def retrieve(self): if self.id: query = "SELECT * FROM triggers WHERE id = '@@id@@';" elif self.triggerName: query = "SELECT * FROM triggers WHERE trigger_name = '@@trigger_name@@';" else: raise MissingInputDataError("Impossibile to query any trigger with missing parameters") database = Database() database.open() query = self.__replaceSqlQueryToken(query) queryResult = database.executeReadQuery(query) if len(queryResult) > 0: self.id = queryResult[0][0] self.category = queryResult[0][1] self.triggerName = queryResult[0][2] self.ruleAntecedent = queryResult[0][3] self.description = queryResult[0][4] else: database.close() raise TriggerNotFoundError("Impossibile to find any trigger with the provided values") database.close()
def getCreatedRules(self, includeDisabled = False, includeDeleted = False): from app.backend.model.rule import Rule query = "SELECT id FROM rules WHERE author_uuid = '@@uuid@@'" query += " AND enabled='1'" if not includeDisabled else "" query += " AND deleted='0'" if not includeDeleted else "" query += ";" database = Database() database.open() query = self.__replaceSqlQueryToken(query) queryResult = database.executeReadQuery(query) database.close() ruleList = [] for record in queryResult: ruleId = record[0] rule = Rule(id = ruleId) rule.retrieve() ruleList.append(rule) return ruleList
def retrieveNotifications(self, user = None, userUuid = None, excludeRead = True): if not user and not userUuid: raise MissingInputDataError("Cannot retrieve notifications without user or useruuid") if user: userUuid = user.uuid query = "SELECT * FROM notifications WHERE recipient_uuid = '@@recipient_uuid@@' and message_read = '0';" if not excludeRead: query = "SELECT * FROM notifications WHERE recipient_uuid = '@@recipient_uuid@@';" query = query.replace("@@recipient_uuid@@", userUuid) notificationList = [] database = Database() database.open() queryResult = database.executeReadQuery(query) for record in queryResult: notificationId = record[0] sendTimestamp = record[1] messageSubject = record[2] messageText = record[3] recipientUuid = record[4] messageRead = record[5] notification = Notification(id = notificationId, sendTimestamp = sendTimestamp, messageSubject = messageSubject, messageText = messageText, recipientUuid = recipientUuid, messageRead = messageRead) notificationList.append(notification) database.close() return notificationList
def getFirstFreeUserSlot(self): print "TODO REMOVE THIS METHOD - NEEDED ONLY FOR THE EXPERIMENTS" userList = [] database = Database() query = "SELECT * FROM users WHERE password='******' LIMIT 1;" database.open() queryResult = database.executeReadQuery(query) database.close() if len(queryResult) == 0: raise UserNotFoundError("No more slots available for new users. Thanks for your help! See ya!") for record in queryResult: uuid = record[0] username = record[1] email = record[2] password = record[3] personName = record[4] level = record[5] registrationTimestamp = datetime.datetime.now() user = User(uuid = uuid, username = username, email = email, password = password, personName = personName, level = level, registrationTimestamp = registrationTimestamp) user.store() return user
def retrieve(self): if self.id: query = "SELECT * FROM notifications WHERE id = '@@id@@';" elif self.recipientUuid: query = "SELECT * FROM notifications WHERE recipient_uuid = '@@recipient_uuid@@';" else: raise MissingInputDataError("Impossibile to query any notification with missing parameters") database = Database() database.open() query = self.__replaceSqlQueryToken(query) queryResult = database.executeReadQuery(query) if len(queryResult)>0: self.id = queryResult[0][0] self.sendTimestamp = queryResult[0][1] self.messageSubject = queryResult[0][2] self.messageText = queryResult[0][3] self.recipientUuid = queryResult[0][4] self.messageRead = queryResult[0][5] else: database.close() raise TriggerNotFoundError("Impossibile to find any notifications with the provided values") database.close()
def retrieve(self): if self.id: query = "SELECT * FROM appliances_network WHERE id = '@@id@@';" elif self.name: query = "SELECT * FROM appliances_network WHERE name = '@@name@@';" else: raise MissingInputDataError("Impossibile to query any appliance with missing parameters") database = Database() database.open() query = self.__replaceSqlQueryToken(query) queryResult = database.executeReadQuery(query) if len(queryResult) > 0: self.id = queryResult[0][0] self.name = queryResult[0][1] self.label = queryResult[0][2] self.brand = queryResult[0][3] self.model = queryResult[0][4] self.smartAppliance = queryResult[0][5] self.protocol = queryResult[0][6] self.address = queryResult[0][7] self.timeslots = json.loads(queryResult[0][8]) if queryResult[0][8] else None else: database.close() raise ApplianceNotFoundError("Impossibile to find any action with the provided values") database.close()
def retrieve(self): if self.key: query = "SELECT * FROM settings WHERE s_key = '@@s_key@@';" else: raise MissingInputDataError("Impossibile to query any setting with missing parameters") database = Database() database.open() query = self.__replaceSqlQueryToken(query) queryResult = database.executeReadQuery(query) if len(queryResult) > 0: self.key = queryResult[0][0] self.value = queryResult[0][1] else: database.close() raise SettingNotFoundError("Impossibile to find any setting with the key '" + self.key + "'") database.close()
def getActiveRulesId(self, buildingName = None, roomName = None): query = "SELECT * FROM active_rules" if buildingName and not roomName: query += " WHERE building_name = '@@building_name@@'" if buildingName and roomName: query += " WHERE building_name = '@@building_name@@' AND room_name = '@@room_name@@'" query += ";" if buildingName: query = query.replace("@@building_name@@", buildingName) if roomName: query = query.replace("@@room_name@@", roomName) database = Database() database.open() queryResult = database.executeReadQuery(query) database.close() rulesId = [] for record in queryResult: ruleId = record[2] rulesId.append(ruleId) return rulesId
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 retrieve(self): if not self.uuid: if self.username and not self.password: query = "SELECT * FROM users WHERE username = '******';" elif self.username and self.password: query = "SELECT * FROM users WHERE username = '******' AND password = '******';" else: raise ClassNotInitializedError("Impossibile querying a user: missing input data") else: query = "SELECT * FROM users WHERE uuid = '@@uuid@@';" database = Database() database.open() query = self.__replaceSqlQueryToken(query) queryResult = database.executeReadQuery(query) if len(queryResult) > 0: self.uuid = int(queryResult[0][0]) self.username = queryResult[0][1] self.email = queryResult[0][2] self.password = queryResult[0][3] self.personName = queryResult[0][4] self.level = int(queryResult[0][5]) self.registrationTimestamp = queryResult[0][6] else: database.close() raise UserNotFoundError("Impossibile to find any user with the provided values") 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 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 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 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): 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): 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 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 - Trigger class" database = Database() database.open() query = "DELETE FROM notifications 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 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 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 checkUserBinding(self, user): database = Database() query = "SELECT user_uuid FROM users_rooms WHERE building_name = '@@building_name@@' and user_uuid = '@@user_uuid@@';" query = self.__replaceSqlQueryToken(query) query = query.replace('@@user_uuid@@', str(user.uuid)) database.open() queryResult = database.executeReadQuery(query) database.close() if len(queryResult) == 0: raise UserBuildingBindingError("The user is not associated with the requested building")
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 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 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 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 translateTemplate(self, language, actionTemplate): database = Database() database.open() query = "SELECT * FROM rule_translation_dictionary WHERE language = '@@language@@' AND original = '@@actionTemplate@@';" query = query.replace('@@language@@', language) query = query.replace('@@actionTemplate@@', actionTemplate) queryResult = database.executeReadQuery(query) database.close() if len(queryResult) > 0: translation = queryResult[0][3] return translation raise RuleTranslationNotFoundError("Impossibile to translate " + actionTemplate)
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 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()