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
