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