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 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 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.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 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.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 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 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 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 delete(self): database = Database() database.open() query = "DELETE FROM appliances_network 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 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 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 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 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 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 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 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 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 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 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 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 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 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): 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()