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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 getRules(self, includeDisabled = False, includeDeleted = False): from app.backend.model.rule import Rule query = "SELECT * FROM rules WHERE building_name = '@@building_name@@'" 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] priority = int(record[1]) category = record[2] buildingName = record[3] authorUuid = int(record[6]) antecedent = record[7] consequent = record[8] enabled = bool(int(record[9])) deleted = bool(int(record[10])) creationTimestamp = record[11] lastEditTimestamp = record[12] groupId = int(record[4]) roomName = record[5] groupId = groupId if groupId != -1 else None roomName = roomName if roomName != "None" else None rule = Rule(id = ruleId, priority = priority, category = category, buildingName = buildingName, groupId = groupId, roomName = roomName, authorUuid = authorUuid, antecedent = antecedent, consequent = consequent, enabled = enabled, deleted = deleted, creationTimestamp = creationTimestamp, lastEditTimestamp = lastEditTimestamp) ruleList.append(rule) return ruleList
def retrieve(self): database = Database() database.open() query = "SELECT * FROM buildings WHERE building_name = '@@building_name@@';" query = self.__replaceSqlQueryToken(query) queryResult = database.executeReadQuery(query) if len(queryResult) > 0: self.buildingName = queryResult[0][0] self.label = queryResult[0][1] self.description = queryResult[0][2] else: database.close() raise BuildingNotFoundError("Impossibile to find any building with the provided values") database.close()
def checkIfUnique(self): if self.groupId: query = "SELECT count(id) FROM rules WHERE building_name = '@@building_name@@' AND group_id = '@@group_id@@' AND antecedent = '@@antecedent@@' AND consequent = '@@consequent@@';" elif self.roomName: query = "SELECT count(id) FROM rules WHERE building_name = '@@building_name@@' AND room_name = '@@room_name@@' AND antecedent = '@@antecedent@@' AND consequent = '@@consequent@@';" else: raise MissingInputDataError("To check if a rule is unique, or groupId or roomName is needed.") database = Database() database.open() query = self.__replaceSqlQueryToken(query) queryResult = database.executeReadQuery(query) if int(queryResult[0][0]) > 0: return False return True
def getCrossRoomValidationGroups(self, roomName = None, validationCategories = None): print "\t\t\t\t\t\t\t\tTODO (" + self.__class__.__name__ + ":" + sys._getframe().f_code.co_name + ") : non yet tested" from app.backend.model.group import Group query = "SELECT id FROM groups WHERE building_name = '@@building_name@@' AND cross_rooms_validation = '1';" database = Database() database.open() query = self.__replaceSqlQueryToken(query) queryResult = database.executeReadQuery(query) database.close() groupList = [] for groupRecord in queryResult: group = Group(id = groupRecord[0], buildingName = self.buildingName) group.retrieve() if validationCategories: queriedValidationCategories = set(validationCategories) groupValidationCategories = set(group.crossRoomsValidationCategories) if queriedValidationCategories.issubset(groupValidationCategories): groupList.append(group) else: groupList.append(group) if not roomName and not validationCategories: return groupList if roomName: filteredGroupList = [] for group in groupList: for room in group.getRooms(): if room.roomName == roomName: filteredGroupList.append(group) return filteredGroupList raise UnknownError()
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 getUnassignedRooms(self): from app.backend.model.room import Room query = "SELECT * FROM rooms where (room_name,building_name) NOT IN (SELECT room_name,building_name FROM users_rooms) AND building_name='@@building_name@@'" database = Database() database.open() query = self.__replaceSqlQueryToken(query) queryResult = database.executeReadQuery(query) database.close() roomList = [] for roomRecord in queryResult: room = Room(roomName = roomRecord[0], buildingName = self.buildingName) room.retrieve() roomList.append(room) return roomList
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 retrieve(self): if not (self.buildingName and self.roomName and self.ruleId): raise MissingInputDataError("Missing input to get a rule priority") database = Database() database.open() query = "SELECT * 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 len(queryResult) > 0: self.rulePriority = queryResult[0][3] else: database.close() raise RoomRulePriorityNotFoundError("Impossibile to find the requested rule priority record") 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 getAllBuildings(self): buildingList = [] database = Database() query = "SELECT * FROM buildings;" database.open() queryResult = database.executeReadQuery(query) database.close() for record in queryResult: buildingName = record[0] label = record[1] description = record[2] building = Building(buildingName = buildingName, label = label, description = description) buildingList.append(building) return buildingList
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 getGroups(self): print "\t\t\t\t\t\t\t\tTODO (" + self.__class__.__name__ + ":" + sys._getframe().f_code.co_name + ") : non yet tested" from app.backend.model.group import Group query = "SELECT id FROM groups WHERE building_name = '@@building_name@@';" database = Database() database.open() query = self.__replaceSqlQueryToken(query) queryResult = database.executeReadQuery(query) database.close() groupList = [] for groupRecord in queryResult: group = Group(id = groupRecord[0], buildingName = self.buildingName) group.retrieve() groupList.append(group) return groupList
def getRules(self, excludedRuleId = False, includeDisabled = False, includeDeleted = False, categoriesFilter = None): from app.backend.model.rule import Rule query = "SELECT id FROM rules WHERE group_id = '@@id@@' @@__EXCLUDED_RULE_ID__@@" query += " AND enabled='1'" if not includeDisabled else "" query += " AND deleted='0'" if not includeDeleted else "" if categoriesFilter: query += " AND (@@__CATEGORY_FILTERS__@@)" categoryFilterQuery = "" for category in json.loads(categoriesFilter): categoryFilterQuery += "category = '" + category + "' OR " query = query.replace("@@__CATEGORY_FILTERS__@@", categoryFilterQuery) query = query.replace(" OR )", ")") query += ";" if excludedRuleId: query = query.replace("@@__EXCLUDED_RULE_ID__@@", "AND NOT id = " + str(excludedRuleId)) else: query = query.replace("@@__EXCLUDED_RULE_ID__@@", "") database = Database() database.open() query = self.__replaceSqlQueryToken(query) queryResult = database.executeReadQuery(query) database.close() ruleList = [] for ruleRecord in queryResult: rule = Rule(ruleRecord[0]) rule.retrieve() ruleList.append(rule) return ruleList