def recoverSubtopic():
subtopic_id = int(sys.argv[1])
atn_db = DBHandler('./database/Memex.db')
atn_db.cur.execute('UPDATE subtopic SET state=0 WHERE subtopic_id=?', [subtopic_id])
atn_db.cur.execute(
'''
UPDATE filter_list SET state=1
WHERE topic_id = (SELECT topic_id FROM subtopic WHERE subtopic_id=?)
AND docno IN (
SELECT DISTINCT passage.docno FROM passage
WHERE passage.subtopic_id=?
AND passage.state=0) AND state!=1
''',[subtopic_id, subtopic_id])
atn_db.cur.execute(
'''
INSERT INTO filter_list (topic_id, docno, state)
SELECT DISTINCT subtopic.topic_id, passage.docno, 1 FROM subtopic, passage
WHERE subtopic.subtopic_id = passage.subtopic_id
AND subtopic.subtopic_id=?
AND passage.state = 0
AND passage.docno NOT in (SELECT docno FROM filter_list WHERE topic_id = subtopic.topic_id);
''', [subtopic_id])
atn_db.commit()
atn_db.close()
def recoverSubtopic():
subtopic_id = int(sys.argv[1])
atn_db = DBHandler('./database/test.db')
atn_db.cur.execute('UPDATE subtopic SET state=0 WHERE subtopic_id=?', [subtopic_id])
atn_db.cur.execute(
'''
UPDATE filter_list SET state=1
WHERE topic_id = (SELECT topic_id FROM subtopic WHERE subtopic_id=?)
AND docno IN (
SELECT DISTINCT passage.docno FROM passage
WHERE passage.subtopic_id=?
AND passage.state=0) AND state!=1
''',[subtopic_id, subtopic_id])
atn_db.cur.execute(
'''
INSERT INTO filter_list (topic_id, docno, state)
SELECT DISTINCT subtopic.topic_id, passage.docno, 1 FROM subtopic, passage
WHERE subtopic.subtopic_id = passage.subtopic_id
AND subtopic.subtopic_id=?
AND passage.state = 0
AND passage.docno NOT in (SELECT docno FROM filter_list WHERE topic_id = subtopic.topic_id);
''', [subtopic_id])
atn_db.commit()
atn_db.close()
def userAuthentication(username, password):
user_db = DBHandler(db_path.user)
result = None
user_db.cur.execute(
'SELECT userid, username, usercookie FROM user WHERE username = ? AND password = ?',[username, password])
result = user_db.cur.fetchone()
user_db.close()
return result
def userAuthentication(username, password):
user_db = DBHandler(db_path.user)
result = None
user_db.cur.execute(
'SELECT userid, username, usercookie FROM user WHERE username = ? AND password = ?',
[username, password])
result = user_db.cur.fetchone()
user_db.close()
return result
def cookieAuthentication(env):
user_db = DBHandler(db_path.user)
result = None
if 'HTTP_COOKIE' in env:
for pair in env['HTTP_COOKIE'].split(';'):
cookie = pair.strip()
if cookie.startswith('usercookie'):
key, value = cookie.split('=')
user_db.cur.execute('SELECT userid, username, usercookie FROM user WHERE usercookie = ?',[value,])
result = user_db.cur.fetchone()
break
user_db.close()
return result
def cookieAuthentication(env):
user_db = DBHandler(db_path.user)
result = None
if 'HTTP_COOKIE' in env:
for pair in env['HTTP_COOKIE'].split(';'):
cookie = pair.strip()
if cookie.startswith('usercookie'):
key, value = cookie.split('=')
user_db.cur.execute(
'SELECT userid, username, usercookie FROM user WHERE usercookie = ?',
[
value,
])
result = user_db.cur.fetchone()
break
user_db.close()
return result
def dupTopic():
userid = 30
topic_id = 391
# copy this topic to this userid
atn_db = DBHandler('./database/test.db')
atn_db.insert('topic', [
None, "slums and orphans _ debug", None, userid, 1, 'L', 'L', '', '', 0
])
new_tid = atn_db.cur.lastrowid
atn_db.cur.execute('SELECT * FROM subtopic WHERE topic_id=? AND state=0',
[topic_id])
subtopics = atn_db.cur.fetchall()
for subtopic in subtopics:
atn_db.insert('subtopic',
[None, subtopic[1] + ' _ debug', new_tid, 0, 0])
new_sid = atn_db.cur.lastrowid
atn_db.cur.execute(
'SELECT * FROM passage WHERE subtopic_id=? AND state=0',
[subtopic[0]])
passages = atn_db.cur.fetchall()
for passage in passages:
atn_db.insert(
'passage',
[None, passage[1], passage[2], 0, 0, passage[5], new_sid, 0])
atn_db.cur.execute('SELECT docno, state FROM filter_list WHERE topic_id=?',
[topic_id])
fdocs = atn_db.cur.fetchall()
for fdoc in fdocs:
docno, state = fdoc
atn_db.insert('filter_list', [new_tid, docno, state])
atn_db.commit()
atn_db.close()
def main():
atn_db = DBHandler("../../../database/Memex.db") #database connection
topicId = int(sys.argv[1]) #topic id
must_list = []
should_list = []
query_dic = getQuery(atn_db,topicId)
age_min = 0
age_max = 0
height_min = 0
height_max = 0
query_body = {"size":500,"query":{"bool":{"must":[],"should":[]}} }
feature_should_search_map = {"name":"name","hairColor":"hair","eyeColor":"eye","nationality":"nationality","ethnicity":"ethnicity","reviewSite":"review","reviewSiteId":"review","email":"email","phone":"phone","state":"","city":"","price":"","multiple_providers":"","socialMedia":"","socialMediaId":"","services":"","height":"height","weight":"weight","post_date":"posted"}
for key in query_dic:
if key in ["phone","age","height","hairColor","eyeColor"]: #field search
pass
else:
must_list.append(query_dic[key])
if "age" in query_dic:
age_min = int(query_dic["age"][:2])
age_max = int(query_dic["age"][2:])
should_list.append("age")
if "height" in query_dic:
height_min = int(query_dic["height"][:3])
height_max = int(query_dic["height"][3:])
should_list.append("height")
if must_list: #plain text search fields
query_body["query"]["bool"]["must"].append({"match":{"raw_content":" ".join(must_list)}})
else: #field search
query_list = []
if "age" in query_dic:
query_list.append("age")
if "height" in query_dic:
query_list.append("height")
query_body["query"]["bool"]["must"].append({"match":{"raw_content":" ".join(query_list)}})
#should_arr = []
# for word in should_list:
# dic = {}
# dic["match"] = {}
# dic["match"]["raw_content"] = word
# should_arr.append(dic)
#query_body["query"]["bool"]["should"] = should_arr
if "phone" in query_dic:
phone_number = re.sub("\D","",query_dic["phone"])
query_body["query"]["bool"]["must"].append({"match":{"phone":phone_number }})
if "age" in query_dic:
query_body["query"]["bool"]["must"].append({"range" : {"age" : {"gte" : age_min,"lte" : age_max}}})
if "height" in query_dic:
query_body["query"]["bool"]["must"].append({"range" : {"height" : {"gte" : height_min,"lte" : height_max}}})
if "hairColor" in query_dic:
query_body["query"]["bool"]["must"].append({"match":{"hairColor":" ".join(query_dic["hairColor"].split(","))}})
if "eyeColor" in query_dic:
query_body["query"]["bool"]["must"].append({"match":{"eyeColor":" ".join(query_dic["eyeColor"].split(","))}})
raw_content_str = query_body["query"]["bool"]["must"][0]
if not raw_content_str["match"]["raw_content"]: #occurs when field search(phone,hairColor,eyeColor) is the only field involved
query_body["query"]["bool"]["must"].pop(0)
a = open("test.txt","w")
a.write(str(query_body))
a.close()
es = Elasticsearch(["localhost:9200/positiongt"],request_timeout=60)
response = es.search(body=query_body,request_timeout=60)
documents = response["hits"]["hits"]
results = []
if not documents:
hypoFields = []
if "hairColor" in query_dic:
hypoFields.append("hairColor")
if "eyeColor" in query_dic:
hypoFields.append("eyeColor")
is_raw_content = False
if hypoFields: #if there is no results and hairColor or eyeColor included, transfer field search(originally hairColro and eyeColor are field search) to plain text search
for term in hypoFields:
j = -1
for i in range(len(query_body["query"]["bool"]["must"])):
if "raw_content" in query_body["query"]["bool"]["must"][i]["match"]:
query_body["query"]["bool"]["must"][i]["match"]["raw_content"] += " "+" ".join(query_dic[term].split(","))
is_raw_content = True
if term in query_body["query"]["bool"]["must"][i]["match"]:
j = i
if j>=0:
query_body["query"]["bool"]["must"].pop(j) #remove the field search
if not is_raw_content: #this case occurs when field search are the only fields involved.
query_body["query"]["bool"]["must"].insert(0,{"match":{"raw_content":" ".join(map(lambda x:" ".join(query_dic[x].split(",")),hypoFields))}})
response = es.search(body=query_body,request_timeout=60)
documents = response["hits"]["hits"]
if "ethnicity" in query_dic:
f = open("nation_continent.txt")
ethnicity_dic = yaml.load(f)
candidate_countries = ethnicity_dic[query_dic["ethnicity"].lower()]+[query_dic["ethnicity"].capitalize()]
for document in documents:
if "ethnicity" in document["_source"] and document["_source"]["ethnicity"]:
ethnicities = map(lambda x:x.lower(),document["_source"]["ethnicity"])
#print(ethnicities)
if query_dic["ethnicity"].capitalize() in ethnicities:
print(document["_id"])
results.append(document["_id"])
else:
isMatch = False
for eth_candi in ethnicities:
if isMatch:
break
for coun_candi in candidate_countries:
if fuzz.ratio(eth_candi,coun_candi.lower())>=80:
print(document["_id"])
results.append(document["_id"])
isMatch = True
break
else:
for document in documents:
print document["_id"]
results.append(document["_id"])
atn_db.cur.execute("SELECT round from search_list where topic_id=? ORDER BY round DESC LIMIT 1",[topicId])
res = atn_db.cur.fetchone()
round = 0
if res:
round, = res
round += 1
for documentId in results:
#print((None,topicId,round,documentId))
atn_db.cur.execute('INSERT INTO %s VALUES(%s)' %("search_list", "?,?,?,?"), (None,topicId,round,documentId))
atn_db.commit()
atn_db.close()
# initialize the image descriptor
cd = ColorDescriptor((8, 12, 3))
# load the query image and describe it
query = cv2.imread("query")
features = cd.describe(query)
# perform the search
searcher = Searcher("a.csv")
#start = datetime.now()
results = searcher.search(features)
#print((datetime.now()-start).seconds)
# display the query
#cv2.imshow("Query", query)
results = map(lambda k:k[1].split(".")[0].split("_")[0],results)
cur.execute("SELECT round from search_list where topic_id=? ORDER BY round DESC LIMIT 1",[topicId])
res = cur.fetchone()
round = 0
if res:
round, = res
round += 1
# loop over the results
for resultID in list(set(results)):
#load the result image and display it
print(resultID)
cur.execute('INSERT INTO %s VALUES(%s)' %("search_list", "?,?,?,?"), (None,topicId,round,resultID))
# result = cv2.imread(args["result_path"] + "/" + resultID)
# cv2.imshow("Result", result)
# cv2.waitKey(0)
atn_db.commit()
atn_db.close()
os.system("rm -f query")
