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 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 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 check_username(username):
mydb.open()
result = mydb.query_db('select * from account WHERE username = ?', [username], one=True)
if result==None:
return True
else:
return False
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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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')