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