def show_persons():
query = "SELECT Persons.Name FROM Persons"
all_rows = db.query_db(query)
list_string = ''
for row in all_rows:
list_string = list_string + "\n" + row['Name']
return util.get_simple_response("debugQuery Persons", list_string)
def get_tags_for_email(entryID):
query = "SELECT Emails.ID from Emails WHERE Emails.EntryID='{0}';".format(entryID)
all_rows = db.query_db(query)
if not(all_rows):
return get_empty_tags_response()
row = all_rows[0]
emailID = row['ID']
query = "SELECT Tags.Name FROM Tags INNER Join EmailTags on EmailTags.tagID=Tags.ID WHERE EmailID='{0}';".format(emailID)
tagList = []
for tag_row in db.query_db(query):
tag = tag_row['Name']
tagDict = { "Name" : tag }
tagList.append(tagDict)
result = {}
result["Tags"] = tagList
response = jsonify(result)
return response
def get_email_tag_id(entryID, tag):
print("person is {0}, tag is {1}".format(entryID, tag))
emailID = emailTable.get_email_id_for_email(entryID)
tagID = tagTable.get_tag_id_for_tag(tag)
if (emailID=='?' or tagID=='?'):
return '?'
query="SELECT EmailTags.ID FROM EmailTags WHERE EmailID='{0}' and TagID='{1}'".format(emailID, tagID)
all_rows = db.query_db(query)
return util.get_id_from_rows(all_rows)
def get_person_tag_id(person, tag):
print("person is {0}, tag is {1}".format(person, tag))
personID = personTable.get_person_id_for_person(person)
tagID = tagTable.get_tag_id_for_tag(tag)
if (personID == '?' or tagID == '?'):
return '?'
query = "SELECT PersonTags.ID FROM PersonTags WHERE PersonID='{0}' and TagID='{1}'".format(
personID, tagID)
all_rows = db.query_db(query)
return util.get_id_from_rows(all_rows)
def get_resource_tag_id(type, name, tag):
print("resource is {0}, tag is {1}".format(name, tag))
resourceID = resourceTable.get_resource_id(type, name)
tagID = tagTable.get_tag_id_for_tag(tag)
if (resourceID == '?' or tagID == '?'):
return '?'
query = "SELECT ResourceTags.ID FROM ResourceTags WHERE ResourceID='{0}' and TagID='{1}'".format(
resourceID, tagID)
all_rows = db.query_db(query)
return util.get_id_from_rows(all_rows)
def get_all_tags():
query = "SELECT Tags.Name from Tags"
tagList = []
for tag_row in db.query_db(query):
tag = tag_row['Name']
tagDict = { "Name" : tag }
tagList.append(tagDict)
result = {}
result["Tags"] = tagList
response = jsonify(result)
return response
def get_doc_tree_for_tag_id(tagID):
print("tag was {0}".format(tagID))
mru_query = "SELECT Resources.Name FROM Resources INNER JOIN ResourceTags ON Resources.ID=ResourceTags.ResourceID WHERE Resources.Type='FILE' AND ResourceTags.TagID='{0}' ORDER BY Resources.LastUse DESC LIMIT 2;".format(
tagID)
mruDocList = []
for doc_row in db.query_db(mru_query):
doc = doc_row['Name']
docDict = {"Name": doc}
mruDocList.append(docDict)
rel_query = "SELECT Resources.Name FROM Resources INNER JOIN ResourceTags ON Resources.ID=ResourceTags.ResourceID WHERE Resources.Type='FILE' AND ResourceTags.TagID='{0}' ORDER BY Resources.Name;".format(
tagID)
relDocList = []
for doc_row in db.query_db(rel_query):
doc = doc_row['Name']
docDict = {"Name": doc}
relDocList.append(docDict)
result = {}
result["RelevantDocuments"] = relDocList
result["MruDocuments"] = mruDocList
response = jsonify(result)
return response
def get_person_id_for_person(person):
query = "SELECT Persons.ID from Persons WHERE Persons.Name='{0}';".format(
person)
print(query)
all_rows = db.query_db(query)
return util.get_id_from_rows(all_rows)
def get_email_id_for_email(entryID):
query = "SELECT Emails.ID from Emails WHERE Emails.EntryID='{0}';".format(
entryID)
all_rows = db.query_db(query)
return util.get_id_from_rows(all_rows)
def get_email_id_for_these(conversationID, entryID):
query = "SELECT Emails.ID from Emails WHERE Emails.ConversationID='{0}' and Emails.EntryID='{1}';".format(
conversationID, entryID)
all_rows = db.query_db(query)
return util.get_id_from_rows(all_rows)
def get_tag_id_for_tag(tag):
query = "SELECT Tags.ID from Tags WHERE Tags.Name='{0}';".format(tag)
all_rows = db.query_db(query)
return util.get_id_from_rows(all_rows)
def get_resource_id(type, name):
query = "SELECT Resources.ID from Resources WHERE Resources.Type='{0}' and Resources.Name='{1}';".format(
type, name)
all_rows = db.query_db(query)
return util.get_id_from_rows(all_rows)