def Create(self): query = """INSERT INTO Person(Name, LastName, FirstName, CVTag, Birthday, LastSeen, LastSpokenTo, Gender, Language) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}');""" db().Execute( query.format(self.Name, self.LastName, self.FirstName, self.CVTag, self.Birthday, self.LastSeen, self.LastSpokenTo, self.Gender, self.Language))
def Update(self): if self.DBID < 1: return query = "UPDATE Person SET Name='{0}', LastName='{1}', FirstName='{2}', CVTag='{3}', Birthday='{4}', LastSeen='{5}', LastSpokenTo='{6}', Gender='{7}', Language='{8}' WHERE ID = '{9}'" db().Execute( query.format(self.Name, self.LastName, self.FirstName, self.CVTag, self.Birthday, self.LastSeen, self.LastSpokenTo, self.Gender, self.Language, self.DBID)) self.SaveObject()
def SaveCategory(self, Name): query = "INSERT or IGNORE INTO Conversation_Category ('Name') Values ('{0}')".format( Name) db().Execute(query) query = "SELECT ID FROM Conversation_Category WHERE Name = '{0}'".format( Name) categoryID = db().Fetchall(query)[0][0] return categoryID
def SaveRequirement(self, Name): query = "INSERT or IGNORE INTO Conversation_Requirement ('Name') Values ('{0}')".format( Name) db().Execute(query) query = "SELECT ID FROM Conversation_Requirement WHERE Name = '{0}'".format( Name) requirementID = db().Fetchall(query)[0][0] return requirementID
def LinkKeywordAndSentence(self, KeywordList, Language, SentenceID): for keyword in KeywordList: if (type(keyword) == int): keywordID = keyword else: keywordID = self.SaveKeyword(keyword, Language) query = "INSERT or IGNORE INTO Conversation_Sentence_Keyword ('KeywordID', 'SentenceID') Values ('{0}', '{1}')".format( keywordID, SentenceID) db().Execute(query)
def AddWifiToLocation(self, location, wifi): if not isinstance(location, (int, long)): location = self.GetLocationID(location) wifientry = self.GetWiFiID(wifi.BSSID) if (wifientry is None): wifientry = self.CreateWiFi(wifi) query = "INSERT INTO Location_WiFi_Fingerprint_Name ('LocationID', 'WiFiID', 'RSSI', 'Noise', 'Indicator') VALUES ('{0}','{1}','{2}','{3}','{4}')".format( location, wifientry, wifi.RSSI, wifi.Noise, wifi.Indicator) db().Execute(query)
def SaveKeyword(self, Word, Language): isStopword = 0 if NLP.IsStopword(Word, Language): isStopword = 1 query = "INSERT or IGNORE INTO Conversation_Keyword ('Keyword', 'Normalized', 'Language', 'Stopword') Values ('{0}', '{1}', '{2}', '{3}')".format( Word, NLP.Normalize(Word, Language), Language, isStopword) db().Execute(query) query = "SELECT ID FROM Conversation_Keyword WHERE Normalized = '{0}'".format( NLP.Normalize(Word, Language)) keywordID = db().Fetchall(query)[0][0] return keywordID
def SaveAction(self, Name, Module, Class, Function, Language, ErrorSentence): query = "INSERT or IGNORE INTO Conversation_Action ('Name', 'Module', 'Class', 'Function') Values ('{0}', '{1}', '{2}', '{3}')".format( Name, Module, Class, Function) db().Execute(query) query = "SELECT ID FROM Conversation_Action WHERE Name = '{0}'".format( Name) actionID = db().Fetchall(query)[0][0] query = "INSERT or IGNORE INTO Conversation_Action_Error (ActionID, Sentence, Language) Values ('{0}', '{1}', '{2}')" db().Execute(query.format(actionID, ErrorSentence, Language)) return actionID
def SaveSentence(self, Sentence, Language, UserName, Animation=None, Approved=0): query = "INSERT or IGNORE INTO Conversation_Sentence ('Sentence', 'Language', 'Source', 'Approved', 'Animation') Values ('{0}', '{1}', '{2}', '{3}', '{4}')".format( Sentence, Language, UserName, Approved, Animation) db().Execute(query) query = "SELECT ID FROM Conversation_Sentence WHERE Sentence = '{0}'".format( Sentence) sentenceID = db().Fetchall(query)[0][0] return sentenceID
def PredictLocation(self): wifiList = self.GetWiFiList() prediction = {} for wifi in wifiList: query = """SELECT Location_WiFi_Fingerprint_Name.Indicator, ABS({1}-Location_WiFi_Fingerprint_Name.Indicator) as diff, Location_Name.ID FROM Location_WiFi_Fingerprint, Location_WiFi_Fingerprint_Name, Location_Name WHERE BSSID = '{0}' AND Location_WiFi_Fingerprint.ID = Location_WiFi_Fingerprint_Name.WiFiID AND Location_WiFi_Fingerprint_Name.LocationID = Location_Name.ID ORDER BY ABS({1}-Indicator) LIMIT 5""" result = db().Fetchall(query.format(wifi.BSSID, wifi.Indicator)) for r in result: distance = r[1] + 1 if r[2] in prediction: prediction[r[2]] += (r[0] / distance) else: prediction[r[2]] = (r[0] / distance) prediction[r[2]] += 10 return prediction
def __init__(self): query = """SELECT ID FROM Memory WHERE ParentID = '1' AND Key = 'STT'""" sqlResult = db().Fetchall(query) parentID = -1 for r in sqlResult: parentID = r[0] Base.__init__(self, parentID)
def GetSentencesByKeyword(self, sentenceList, word, baseWord, language, isSynonym, isTrainer): query = """SELECT DISTINCT Conversation_Sentence_Keyword.SentenceID, Conversation_Keyword.Stopword, Conversation_Sentence_Keyword.Priority, Conversation_Keyword.Priority, Conversation_Keyword.Normalized FROM Conversation_Keyword, Conversation_Sentence_Keyword, Conversation_Sentence WHERE Conversation_Keyword.ID = Conversation_Sentence_Keyword.KeywordID AND Conversation_Sentence_Keyword.SentenceID = Conversation_Sentence.ID AND Conversation_Sentence.Approved >= {0} AND Conversation_Sentence.Disabled = 0 AND Conversation_Keyword.Normalized IN ({1}) AND Conversation_Keyword.Language = '{2}'""" trainer = 0 if isTrainer else 1 sqlResult = db().Fetchall(query.format(trainer, word, language)) for r in sqlResult: stopwordFactor = self.__stopwordFactor if r[1] == 1 else 1 isStopword = True if r[1] == 1 else False synonymFactor = self.__synonymFactor if isSynonym else 1 synonymTag = "(* {0})".format(baseWord) if isSynonym else "" if r[0] in sentenceList: sentenceList[r[0]].AddKeyword( (r[3] + synonymFactor * stopwordFactor * r[2]), r[4], isStopword) else: sentenceList[r[0]] = Sentence( r[0], (r[3] + synonymFactor * stopwordFactor * r[2]), r[4], isStopword) sentenceList[r[0]].AddBaseword("{0}{1}".format(r[4], synonymTag)) return sentenceList
def GetWiFiBSSID(self, id): location = db().Fetchall( "SELECT BSSID FROM Location_WiFi_Fingerprint WHERE ID = '{0}'". format(id)) if len(location) > 0: return location[0][0] return None
def CreateLocation(self, name): returnID = self.GetLocationID(name) if (returnID is None): returnID = db().Execute( "INSERT INTO Location_Name ('Name') Values ('{0}')".format( name)) return returnID
def __setUser(self, query, name): sqlResult = db().FetchallCacheBreaker(query.format(name)) for r in sqlResult: self.DBID = r[0] self.Name = r[1] self.LastName = r[2] self.FirstName = r[3] self.FullName = "{0} {1}".format(r[3], r[2]) self.CVTag = r[4] self.Birthday = r[5] self.LastSeenPrevious = r[6] self.LastSeen = datetime.now().strftime("%Y-%m-%d %H:%M:%S") self.LastSpokenTo = r[7] self.Gender = r[8] if self.Gender.lower() == "female": self.NameTitle = Config().Get( "DEFAULT", "FormalFormOfAddressFemale").format("") else: self.NameTitle = Config().Get( "DEFAULT", "FormalFormOfAddressMale").format("") self.Formal = r[9].lower() == "formal" self.Trainer = r[10] == 1 self.Admin = r[11] == 1 self.Language = r[12] self.Update() continue self.Updated = datetime.now().strftime("%H%M")
def AddSentencePriority(self, sentenceList): query = """SELECT Priority FROM Conversation_Sentence WHERE ID = '{0}'""" for sentenceID in sentenceList.iterkeys(): sqlResult = db().Fetchall(query.format(sentenceID)) for r in sqlResult: if not sentenceList[sentenceID].OnlyStopwords: sentenceList[sentenceID].AddPriority(r[0]) return sentenceList
def Set(self, key, value, parentID = None): if(parentID is None): parentID = self.ParentID query = """SELECT ID FROM Memory WHERE ParentID = '{0}' AND lower(Key) = '{1}'""" sqlResult = db().FetchallCacheBreaker(query.format(parentID, key.lower())) storedId = -1 for r in sqlResult: storedId = r[0] if(storedId > 1): query = "UPDATE Memory SET Key = '{1}', Value = '{2}', ParentID = '{3}', Timestamp = CURRENT_TIMESTAMP WHERE ID = {0}".format(storedId, key, value, parentID) else: query = "INSERT INTO Memory (Key, Value, ParentID) VALUES ('{0}', '{1}', '{2}')".format(key, value, parentID) return db().Execute(query)
def GetActionErrorResponse(self, language, formal=True): query = """SELECT Sentence, Formal, Informal FROM Conversation_Action_Error WHERE ActionID = '{0}' and Language = '{1}'""" sqlResult = db().Fetchall(query.format(self.ActionID, language)) for r in sqlResult: data = self.__formalOrOther(r, formal) if data is not None: return data return None
def GetSentenceString(self, formal=True): query = "SELECT Sentence, Formal, Informal FROM Conversation_Sentence WHERE ID = '{0}'" sqlResult = db().Fetchall(query.format(self.ID)) for r in sqlResult: data = self.__formalOrOther(r, formal) if data is not None: return data return None
def CreatePosition(self, pose, ignoreOrientation=False): p = pose.position o = pose.orientation if (ignoreOrientation): query = "INSERT INTO Location_Map_Position ('PointX', 'PointY', 'PointZ') Values ('{0}', '{1}', '{2}')".format( p.x, p.y, p.z) else: query = "INSERT INTO Location_Map_Position ('PointX', 'PointY', 'PointZ', 'OrientationX', 'OrientationY', 'OrientationZ', 'OrientationW') Values ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}')".format( p.x, p.y, p.z, o.x, o.y, o.z, o.w) return db().Execute(query)
def HasInteraction(self): query = """SELECT Conversation_Interaction.Name FROM Conversation_Interaction, Conversation_Interaction_Sentence WHERE Conversation_Interaction_Sentence.SentenceID = '{0}' AND Conversation_Interaction.ID = Conversation_Interaction_Sentence.InteractionID""" sqlResult = db().Fetchall(query.format(self.ID)) for r in sqlResult: self.InteractionName = r[0] return True return False
def AddActionBonus(self, sentenceList): query = """SELECT Conversation_Action.ID FROM Conversation_Sentence_Action, Conversation_Action WHERE Conversation_Sentence_Action.ActionID = Conversation_Action.ID AND Conversation_Sentence_Action.SentenceID = '{0}' LIMIT 1""" for sentenceID in sentenceList.iterkeys(): sqlResult = db().Fetchall(query.format(sentenceID)) for _ in sqlResult: sentenceList[sentenceID].AddPriority(self.__ActionBonus) return sentenceList
def CalculateCategory(self, sentenceList, category): query = """SELECT Conversation_Category.Name FROM Conversation_Category, {0} WHERE Conversation_Category.ID = {0}.CategoryID AND {0}.SentenceID = '{1}'""" for sentenceID in sentenceList.iterkeys(): sqlResult = db().Fetchall( query.format("Conversation_Sentence_Category_Has", sentenceID)) for r in sqlResult: sentenceList[sentenceID].HasCategory.append(r[0]) sqlResult = db().Fetchall( query.format("Conversation_Sentence_Category_Set", sentenceID)) for r in sqlResult: sentenceList[sentenceID].SetsCategory.append(r[0]) if category in sentenceList[sentenceID].HasCategory: sentenceList[sentenceID].AddPriority(self.__categoryBonus) return sentenceList
def Get(self, key, maxAge = None, parentID = None): if(parentID is None): parentID = self.ParentID maxAgeQuery = "" if(maxAge is not None): maxAgeQuery = """AND Timestamp > datetime(CURRENT_TIMESTAMP, '-{0} seconds') """.format(maxAge) query = """SELECT Value FROM Memory WHERE ParentID = '{0}' AND lower(Key) = '{1}' {2}""" sqlResult = db().FetchallCacheBreaker(query.format(parentID, key.lower(), maxAgeQuery)) for r in sqlResult: return r[0] return None
def GetDatabasePosition(self, pose, range=0.01): p = pose.position query = """SELECT * FROM Location_Map_Position Where PointX BETWEEN {0} and {1} AND PointY BETWEEN {2} and {3} AND PointZ BETWEEN {4} and {5}""" position = db().Fetchall( query.format((p.x - range), (p.x + range), (p.y - range), (p.y + range), (p.z - range), (p.z + range))) if len(position) > 0: return position return None
def GetAction(self): query = """SELECT Conversation_Action.Name, Conversation_Action.Module, Conversation_Action.Class, Conversation_Action.Function, Conversation_Action.ID FROM Conversation_Sentence_Action, Conversation_Action WHERE Conversation_Sentence_Action.ActionID = Conversation_Action.ID AND Conversation_Sentence_Action.SentenceID = '{0}'""" sqlResult = db().Fetchall(query.format(self.ID)) for r in sqlResult: self.ActionID = int(r[4]) return { 'Name': r[0], 'Module': r[1], 'Class': r[2], 'Function': r[3] } return None
def GetSentenceByCategory(self, category, language, isTrainer): sentenceDictionary = {} query = """SELECT cs.ID FROM Conversation_Category as cc , Conversation_Sentence_Category_Has as csch, Conversation_Sentence as cs WHERE cc.Name = '{0}' AND cc.ID = csch.CategoryID AND csch.SentenceID = cs.ID AND cs.Approved >= {1} AND cs.Language = '{2}' AND cs.Disabled = 0 """ trainer = 0 if isTrainer else 1 sqlResult = db().Fetchall(query.format(category, trainer, language)) for r in sqlResult: sentenceDictionary[r[0]] = Sentence(r[0], 0, category, False) return sentenceDictionary
def GetSentenceByInteraction(self, sentenceList, interaction, language, isTrainer): query = """SELECT Conversation_Sentence.ID, Conversation_Sentence.Priority FROM Conversation_Interaction, Conversation_Interaction_Sentence, Conversation_Sentence WHERE Conversation_Interaction.Name = '{0}' AND Conversation_Interaction.ID = Conversation_Interaction_Sentence.InteractionID AND Conversation_Sentence.ID = Conversation_Interaction_Sentence.SentenceID AND Conversation_Sentence.Approved >= {1} AND Conversation_Sentence.Disabled = 0 AND Conversation_Sentence.Language = '{2}'""" trainer = 0 if isTrainer else 1 sqlResult = db().Fetchall(query.format(interaction, trainer, language)) for r in sqlResult: sentenceList[r[0]] = Sentence(r[0], r[1], interaction, False) sentenceList[r[0]].InteractionName = interaction sentenceList[r[0]].AddPriority(self.__InteractionBonus) return sentenceList
def GetSentencesByParameter(self, sentenceList, wordParameterList, language, isTrainer): query = """SELECT DISTINCT Conversation_Sentence_Keyword.SentenceID, Conversation_Keyword.Priority, Conversation_Sentence_Keyword.Priority, Conversation_Keyword.Normalized FROM Conversation_Keyword, Conversation_Sentence_Keyword, Conversation_Sentence WHERE Conversation_Keyword.ID = Conversation_Sentence_Keyword.KeywordID AND Conversation_Sentence_Keyword.SentenceID = Conversation_Sentence.ID AND Conversation_Sentence.Approved >= {0} AND Conversation_Sentence.Disabled = 0 AND Conversation_Keyword.Normalized IN ({1}) AND Conversation_Keyword.Language = '{2}'""" trainer = 0 if isTrainer else 1 sqlResult = db().Fetchall( query.format(trainer, "'{" + "}', '{".join(wordParameterList) + "}'", language)) for r in sqlResult: word = "{{{0}}}".format(r[3]) if r[0] in sentenceList: # if only stop-keywords present and threshold reached if sentenceList[r[0]].OnlyStopwords and sentenceList[ r[0]].Rating >= self.__parameterStopwordThreshold: sentenceList[r[0]].AddKeyword( (r[1] + self.__parameterFactor * r[2] * self.__stopwordFactor), word) # if there are normal keywords present if not sentenceList[r[0]].OnlyStopwords: sentenceList[r[0]].AddKeyword( (r[1] + self.__parameterFactor * r[2]), word) # sentence not in list by now else: sentenceList[r[0]] = Sentence( r[0], (self.__parameterFactor * r[2] * self.__parameterFactorNoKeyword), word) return sentenceList
def __executeQuery(self, query, word): return db().Fetchall(query.format(lowerword=word.lower()))