def get_user_interests(userid, maxrecs):
# Expects http://127.0.0.1:5000/user_interests/2/1000 where the 2 is user id, 10 is record limit.
# Userid of 0 means all users.
c: sqlite3.Connection = mydb.open_db()
cur = c.cursor()
# The JSON serializer alphabetizes by key so force this to the first record location.
ret = {
'000_metadata': {
'userid': userid,
'maxrecs': maxrecs,
'columns': ['uid', 'user_handle', 'interest_tag', 'date_followed'],
'key is': 'uid'
}
}
# Yikes! No input error checking, highly hackable! THough there is some protection with Flask's
# routing parser insisting on integer above.
if userid == 0:
for row in cur.execute('SELECT * FROM user_interests LIMIT ?',
[maxrecs]).fetchall():
# row like (2, 1, '"mvc2"', '"2017-06-27 16:26:52"')
ret[str(row[0])] = row
else:
for row in cur.execute(
'SELECT * FROM user_interests WHERE user_handle=? LIMIT ?',
[userid, maxrecs]).fetchall():
# row like (2, 1, '"mvc2"', '"2017-06-27 16:26:52"')
ret[str(row[0])] = row
mydb.close_db(c)
return ret
def delete_pattern(pattern_id):
mydb.open()
nodes = mydb.query_db('SELECT * FROM node WHERE pid = ?',[pattern_id])
if len(nodes) > 1:
return False
prohibited_node = []
results = mydb.query_db('SELECT * FROM pattern')
for p in results:
pid = p['pid']
re1 = mydb.query_db('SELECT * FROM edge WHERE _from = ?',[pid])
re2 = mydb.query_db('SELECT * FROM edge WHERE _to = ?',[pid])
if len(re1)+len(re2) == 1:
if len(re1) == 1:
pro = re1[0]['_to']
else:
pro = re2[0]['_from']
prohibited_node.append(pro)
print prohibited_node
if pid in prohibited_node:
mydb.close_db()
return False
else:
mydb.delete_db('delete from edge where _from = ? or _to = ?',[pattern_id, pattern_id])
mydb.delete_db('delete from pattern where pid=?', [pattern_id])
mydb.delete_db('delete from node where pid=? and node_index = 1', [pattern_id])
mydb.close_db()
return True
def store_message_in_database(_from, _to, msg):
mydb.open()
ISOTIMEFORMAT='%Y-%m-%d %X'
createdTime=str(time.strftime( ISOTIMEFORMAT, time.localtime() ))
result = mydb.insert_db('insert into message(_from,_to,msg,createdAt) values(?,?,?,?)',
[_from,_to,msg,createdTime])
mydb.close_db()
def generate_graph():
mydb.open()
graph= dict()
query = 'select * from edge'
result = mydb.query_db(query, one = False)
mydb.close_db()
inactive_pid_list=SendMessage.find_all_inactive_pid()
if not result==None:
for r in result:
f=r['_from']
t=r['_to']
if not f in inactive_pid_list and not t in inactive_pid_list:
# if the key is not exist in the graph
if f not in graph:
graph[f]=[t]
else:
temp_List=graph[f]
temp_List.append(t)
if t not in graph:
graph[t]=[f]
else:
temp_List=graph[t]
temp_List.append(f)
# print str(r['_from'])+' to '+ str(r['_to'])
return graph
def delete_pattern(pattern_id):
mydb.open()
mydb.delete_db('delete from edge where _from = ? or _to = ?',[pattern_id, pattern_id])
mydb.delete_db('delete from pattern where pid=?', [pattern_id])
mydb.delete_db('delete from node where pid=? and node_index = 1', [pattern_id])
mydb.close_db()
return True
def add_connection(nid1,nid2):
mydb.open()
p1 = mydb.query_db('SELECT * FROM node WHERE nid = ?', [nid1], one = True)
p2 = mydb.query_db('SELECT * FROM node WHERE nid = ?', [nid2], one = True)
mydb.insert_db('INSERT INTO edge(_from, _to) VALUES (?,?)', [p1['pid'], p2['pid']])
mydb.close_db()
pass
def one_pattern_edge(pid):
edges = []
mydb.open()
results = mydb.query_db('SELECT * FROM edge WHERE _from = ?', [pid])
for e in results:
edges.append(e['_to'])
mydb.close_db()
return edges
def change_pwd(username,pwd):
mydb.open()
result=_valid_login_format(username,pwd)
if not result==None:
return result
mydb.insert_db("""update account set pwd=? WHERE username= ?""", [pwd, username])
mydb.close_db()
return None
def delete_connection(nid1,nid2):
mydb.open()
p1 = mydb.query_db('SELECT * FROM node WHERE nid = ?', [nid1], one = True)
p2 = mydb.query_db('SELECT * FROM node WHERE nid = ?', [nid2], one = True)
mydb.delete_db('delete from edge where _from = ? and _to = ?',[p1['pid'], p2['pid']])
mydb.delete_db('delete from edge where _from = ? and _to = ?',[p2['pid'], p1['pid']])
mydb.close_db()
pass
def change_security_question(index, q, a, username):
if len(a) == 0:
return -1
mydb.open()
query = 'update account set sq_%d= ?,as_%d=? WHERE username = ?' % (index, index)
print query
mydb.insert_db(query, [q, a, username])
mydb.close_db()
return 1
def add_pattern(ps):
mydb.open()
pid = mydb.insert_db('INSERT INTO pattern (creator) VALUES (?)',['spikewang'])
print pid
nid = mydb.insert_db('INSERT INTO node (pid,node_index,active) VALUES (?,1,1)', [pid])
for p in ps:
mydb.insert_db('INSERT INTO edge(_from, _to) VALUES (?,?)',[pid,p])
mydb.close_db()
return (pid, nid)
def add_node(pid,index):
mydb.open()
results = mydb.query_db('SELECT * FROM node WHERE pid = ? and node_index = ?', [pid, index/2] )
if len(results)!=0:
nid = mydb.insert_db('INSERT INTO node (pid,node_index,active) VALUES (?,?,1)',[pid,index])
mydb.close_db()
return nid
else:
return -1
def get_node_byID(nid):
mydb.open()
# active =1 means it is active
result = mydb.query_db('select pid,node_index from node WHERE nid=?',[nid], one=True)
mydb.close_db()
if result:
# we only need pid, and node index for a node to find a path
node=Node(nid,result['pid'],result['node_index'])
return node
def get_all_users():
mydb.open()
result = mydb.query_db('select * from account WHERE type = 0',[],one=False)
mydb.close_db()
userList=[]
for user in result:
dict = {'name':user['username'],'fname':user['fname'],'lname':user['lname']}
userList.append(dict)
return userList
def get_security_question(username):
mydb.open()
result = mydb.query_db('select * from account WHERE username = ? ', [username], one=True)
mydb.close_db()
if result:
questions = question(result['sq_1'],result['as_1'],result['sq_2'],result['as_2'],result['sq_3'],result['as_3'])
return questions
else:
return None
def transform_to_nidList(pid, path):
nidList=[]
mydb.open()
for index in path:
query='select nid from node WHERE pid=? AND node_index= ?'
result = mydb.query_db(query,[str(pid),index], one = True)
if not result==None:
nidList.append(result['nid'])
mydb.close_db()
return nidList
def add_pattern(ps, did):
mydb.open()
d_pattern = mydb.query_db('SELECT * FROM pattern WHERE did = ? and isDomain = 1', [did], one = True)
pid = mydb.insert_db('INSERT INTO pattern (did, isDomain) VALUES (?,0)', [did])
nid = mydb.insert_db('INSERT INTO node (pid,node_index,active) VALUES (?,1,1)', [pid])
mydb.insert_db('INSERT INTO edge(_from, _to) VALUES (?,?)', [pid, d_pattern['pid']])
for p in ps:
mydb.insert_db('INSERT INTO edge(_from, _to) VALUES (?,?)',[pid,p])
mydb.close_db()
return (pid, nid)
def find_all_inactive_pid():
mydb.open()
# active =1 means it is active
result = mydb.query_db('select pid from node WHERE active=0 AND node_index=1', one=False)
mydb.close_db()
pidList=[]
if result:
for node in result:
pidList.append(node['pid'])
return pidList
def find_all_inactiveNode():
mydb.open()
# active =1 means it is active
result = mydb.query_db('select * from node WHERE active=0', one=False)
mydb.close_db()
nidList=[]
if result:
for nid in result:
nidList.append(nid['nid'])
return nidList
def return_p_edges():
edges = []
mydb.open()
results = mydb.query_db('SELECT * FROM edge ')
for e in results:
source = e['_from']
target = e['_to']
tmp_edge = edge(source, target)
edges.append(tmp_edge)
mydb.close_db()
return edges
def view_all_message():
message_list=[]
mydb.open()
result = mydb.query_db('select * from message', one=False)
if result:
for msg in result:
m=message(msg['from_id'],msg['to_id'],msg['msg'])
message_list.append(m)
mydb.close_db()
return message_list
def view_all_message():
message_list=[]
mydb.open()
result = mydb.query_db('select * from message', one=False)
if result:
for msg in result:
m = message(msg['_from'], msg['_to'], msg['msg'], None, msg['createdAt'])
message_list.append(m)
mydb.close_db()
return json.dumps(message_list, default=methodcaller("json"))
def delete_domain(pid, did):
mydb.open()
d_pattern = mydb.query_db('SELECT * FROM pattern WHERE did = ? and isDomain = 1', [did], one = True)
mydb.delete_db('delete from edge where _from = ? or _to = ?',[d_pattern['pid'], d_pattern['pid']])
mydb.delete_db('delete from domain where did=?', [did])
mydb.delete_db('delete from pattern where pid=?', [d_pattern['pid']])
mydb.delete_db('delete from node where pid=?', [d_pattern['pid']])
mydb.delete_db('delete from pattern where pid=?', [pid])
mydb.delete_db('delete from node where pid=?', [pid])
mydb.close_db()
return True
def return_json():
nodes = []
mydb.open()
nodes_results = mydb.query_db('SELECT * FROM node ')
# print "the nodes has %s node" % len(nodes_results)
for n in nodes_results:
tmp_node = Node(n['nid'], n['pid'], n['node_index'], n['active'], n['x'], n['y'])
nodes.append(tmp_node)
mydb.close_db()
# print json.dumps(nodes, default = methodcaller("json"))
# print json.dumps(get_nodes_edges(nodes)+return_p_edges(), default = methodcaller("json"))
return json.dumps(return_ps(nodes), default = methodcaller("json"))
def verifyQues(index, username, answer):
mydb.open()
query = 'select as_%d from account WHERE username = ? '%index
print query
result = mydb.query_db(query, [username], one = True)
mydb.close_db()
name = 'as_%d'%index
if result:
return result[name] == answer
else:
return False
def verify(username, password):
mydb.open()
result = mydb.query_db('select * from account WHERE username = ? and pwd = ?', [username, password], one=True)
mydb.close_db()
if result:
if not result['sq_1'] == None:
print result['sq_1']
questions = question(result['sq_1'],result['as_1'],result['sq_2'],result['as_2'],result['sq_3'],result['as_3'])
else:
questions=None
return User(result['username'], result['type'], result['status'], questions)
else:
return None
def find_path_nidList(start, end):
pidList=get_shortest_path(generate_graph(),start,end )
# del pidList[0]
# del pidList[len(pidList)-1]
nidList=[]
mydb.open()
for pid in pidList:
query='select nid from node WHERE pid=? AND node_index=1'
result = mydb.query_db(query,str(pid), one = True)
if not result==None:
nidList.append(result['nid'])
mydb.close_db()
return nidList
def add_domain(ds):
mydb.open()
did = mydb.insert_db('INSERT INTO domain (creator) VALUES (?)',['spikewang'])
print did
d_pid = mydb.insert_db('INSERT INTO pattern (did, isDomain) VALUES (?,1)', [did])
d_nid = mydb.insert_db('INSERT INTO node (pid, node_index, active) VALUES (?,1,1)', [d_pid])
pid = mydb.insert_db('INSERT INTO pattern (did, isDomain) VALUES (?,0)', [did])
nid = mydb.insert_db('INSERT INTO node (pid, node_index, active) VALUES (?,1,1)', [pid])
for d in ds:
d_pattern = mydb.query_db('SELECT * FROM pattern WHERE did = ? and isDomain = 1', [d], one = True)
mydb.insert_db('INSERT INTO edge(_from, _to) VALUES (?,?)', [d_pid, d_pattern['pid']])
mydb.insert_db('INSERT INTO edge(_from, _to) VALUES (?,?)', [d_pid, pid])
mydb.close_db()
return (did, pid, nid, d_nid)
def randomly_inactive_node(seed):
num=random.randint(0,100)
if(num<seed):
mydb.open()
# active =1 means it is active
result = mydb.query_db('select * from node WHERE active=1', one=False)
mydb.close_db()
nidList=[]
if result:
for nid in result:
nidList.append(nid['nid'])
inactive_nid=nidList[random.randint(0, len(nidList)-1)]
# set not whose nid is inactive_nid to inactive status, pass nid and inactive(0)
change_node_status(inactive_nid,0)
# return a random nid as inactive node
return find_all_inactiveNode()
def verifyQues(index, username, answer):
mydb.open()
query = 'select as_%d from account WHERE username = ? '%index
print query
result = mydb.query_db(query, [username], one = True)
mydb.close_db()
name = 'as_%d'%index
if result:
return result[name] == answer
else:
return False
#addAccount(['spike1390','2cs744',0,1])
#print verifyQues(3,'spikewang','asdfasdfs')
def createAccount(list):
mydb.open()
if len(list[4])==0:
return ["First name can't be null", 0]
if len(list[5])==0:
return ["Last name can't be null",1]
result = _valid_login_format(list[0],list[1])
# if error happend return error message
if not result == None :
return result
if not check_username(list[0]):
return ["Username has already existed",2]
mydb.insert_db("""insert into account(username,pwd,type,status,fname,lname)
values(?,?,?,?,?,?)""", [list[0], list[1], list[2], list[3], list[4], list[5]])
mydb.close_db()
return None
def compare_user_interests_and_courses_impl(usr1, usr2):
c: sqlite3.Connection = mydb.open_db()
ret = []
# get list of user ids
cur = c.cursor()
user_courses1 = fetch_user_courses(cur, usr1)
course_tags1 = fetch_course_tags(cur, user_courses1)
interest_tags1 = fetch_interest_tags(cur, usr1)
user_courses2 = fetch_user_courses(cur, usr2)
course_tags2 = fetch_course_tags(cur, user_courses2)
interest_tags2 = fetch_interest_tags(cur, usr2)
# How common are their interests?
common_tags = {}
# Ugly order squared :-(. Converting these to numeric interest keys would be faster.
# Better yet convert to numerics then use numpy or TensorFlow to correlate in GPU if needed.
for user_interest1 in interest_tags1:
for user_interest2 in interest_tags2:
if user_interest1 == user_interest2:
common_tags[user_interest1] = user_interest1
# So the count could be an absolute metric of commonality. Or as a ratio of total interests vs. common.
ret.append({
'user_handle1': usr1,
'user_handle2': usr2,
'user_score': len(common_tags),
'common_tags': [*common_tags]
})
mydb.close_db(c)
ret = {"data": ret}
return ret
common_tags = {}
# Ugly order squared :-(. Converting these to numeric interest keys would be faster.
# Better yet convert to numerics then use numpy or TensorFlow to correlate in GPU if needed.
for user_interest1 in interest_tags1:
for user_interest2 in interest_tags2:
if user_interest1 == user_interest2:
common_tags[user_interest1] = user_interest1
# So the count could be an absolute metric of commonality. Or as a ratio of total interests vs. common.
ret.append({
'user_handle1': usr1,
'user_handle2': usr2,
'user_score': len(common_tags),
'common_tags': [*common_tags]
})
mydb.close_db(c)
ret = {"data": ret}
return ret
if __name__ == "__main__":
# Get database set up if not already set up.
conn: sqlite3.Connection = mydb.init_mydb()
mydb.load_csvs_to_db(conn)
mydb.close_db(conn)
conn = None
app.run(debug=True, use_reloader=False)
