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()
