def get_document(word):
    trans = """SELECT DISTINCT m.trans FROM Sounds s
LEFT JOIN Metaphors m ON s.id=m.verb_id
WHERE s.id IN (SELECT id FROM Sounds WHERE verb='""" + word+ """');"""
    dir = """SELECT DISTINCT d.example, d.trans FROM Sounds s
LEFT JOIN DirectExamples d ON s.id=d.verb_id
WHERE s.id IN (SELECT id FROM Sounds WHERE verb='""" + word+ """');"""
    met = """SELECT DISTINCT m.ex, m.extr, concat(t.name) FROM Sounds s
LEFT JOIN Metaphors m ON s.id=m.verb_id
LEFT JOIN Tags t ON m.tag_id=t.id
WHERE s.id IN (SELECT id FROM Sounds WHERE verb='""" + word+ """') GROUP BY m.ex;"""
    d = {u'verb': word, u"trans": [], u'dir': [], u'met': []}
    db = SQLClient(db_name)
    for i in db.query(trans):
        if i is not None and i is not '': d[u'trans'].append(i)
    for i in db.query(dir):
        if i[0] is not None or i[1] is not None: d[u'dir'].append({u'ex':i[0], u'trans':i[1]})
    for i in db.query(met):
        if i[0] is not None or i[1] is not None: d[u'met'].append({u'ex':i[0], u'trans':i[1], u'tag': i[2]})
    # print d
    return json.dumps(d)
def simple_search():
    if len(request.args) <= 0:
        return render_template('animals.html')
    if 'q' in request.args:
        q = request.args["q"]
        a = request.args["a"]
        l = request.args["l"]
        t = request.args["t"]
        sql_query = make_sql_for_simple_search(a, l, t)
        db = SQLClient(db_name)
        arr = []
        for i in db.query(sql_query):
            arr.append({u'data':{u'title':i[2], u'released': i[1], u'language': i[0], u'verb': i[3], u'trans': i[4], u'tag': i[5], u'animal':i[1], u'animal2': i[2], u'ex': i[6]}})
        return json.dumps(arr)
    abort(404)
def graph_making():
    if len(request.args) <= 0:
        abort(404)
        return render_template('animals.html')
    if 'q' in request.args:
        q = request.args["q"]
        a = request.args["a"]
        l = request.args["l"]
        t = request.args["t"]
        # print q, a, l, t
        sql_query = make_sql_for_simple_search(a, l, t)
        db = SQLClient(db_name)
        graph = {"nodes": [], "links": []}
        nodes = []
        pairs = {}
        for i in db.query(sql_query):
            i = list(i)
            if i[1] not in pairs:
                pairs[i[1]] = {i[0]}
            else:
                pairs[i[1]].add(i[0])
            if i[5] not in pairs:
                pairs[i[5]] = {i[0]}
            else:
                pairs[i[5]].add(i[0])
            i[5] = ', '.join(sorted(i[5].split(', ')))
            if i[1] not in nodes:
                nodes.append(i[1])
                graph["nodes"].append({u"name":i[1], u'label': u"Animal", u"id":nodes.index(i[1]), "color":"red"})
            if i[3] not in nodes:
                set_color = lang_colors[i[0]]
                nodes.append(i[3])
                graph["nodes"].append({u"name":i[3] + ' (' + i[0] + ')', u'label': u"Verb", u"id":nodes.index(i[3]), "color": set_color})
            if i[5] not in nodes and i[5] != '':
                nodes.append(i[5])
                graph["nodes"].append({u"name":i[5], u'label': u"Tag", u"id":nodes.index(i[5]),"color":"black"})
            graph["links"].append({u"source":nodes.index(i[1]), u'target': nodes.index(i[3]), u"type": "MAKES_SOUND"})
            if i[5] != '':
                graph["links"].append({u"source":nodes.index(i[3]), u'target': nodes.index(i[5]), u"type": "TAGGED"})
        for n in graph['nodes']:
            if n["name"] in pairs:
                n["radius"] = len(pairs[n["name"]])
            else:
                n["radius"] = 0
        #     print n["name"], n["radius"]
        # for i in pairs:
        #     print i, ' '.join(pairs[i])
        return json.dumps(graph)