query_optimization/process.py at main · Advaith123/query_optimization · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
'''
things to do

add ui
add sql to store last 5 queries

'''


import sqlparse

#query = input()
#query = sqlparse.split(query)
#SELECT *, (SELECT count(DISTINCT c.id)   FROM  comments AS c   WHERE  c.PostId = p.id LIMIT 1)   FROM  posts AS p  WHERE p.AnswerCount > 3 AND p.title LIKE '%optimized'  AND p.CreationDate >= '2017-01-01 00:00:00'  ORDER BY p.CreationDate LIMIT 100
#print(query)
#SELECT p.id, count(distinct c.id) FROM posts AS p LEFT JOIN comments AS c ON c.PostId = p.id GROUP BY;
#SELECT * FROM TABLE WHERE PRODUCT IN (SELECT NAME FROM ANIMALS)

def star(query):
    if 'SELECT *' in query or 'select *' in query:
        return 'use column names instead of *'

def like(query):
    subs = query.split("'")
    #print(subs)
    for i in subs:
        if i[-1] == "%":
            return 'avoid using leading wildcard'

def dates(query):
    subs = query.split("'")
    #print(subs)
    count = 0
    for i in subs:
        try:
            if i[4]=='-' and i[7]=='-' :
            #print('date yey',i)
                count = 1
        except:
            pass
    if count>0:
        return 'Avoid Using DATE Functions In Conditions'

def group(query):
    parsed = sqlparse.parse(query)
    #print(parsed[0].tokens)
    g=0; j=0; jf=0
    for i in parsed[0].tokens:
        k = str(i)
        #print(k)
        if 'GROUP BY' in k:
            #print(k)
            g=1
        if 'JOIN' in k and g>0:
            #its ok
            j=1
        if 'JOIN' in k and g==0:
            #not ok
            jf=1
            #print("perform group by before join to improve speed")
    if jf>0 and g>0:
        return "perform GROUP BY before JOIN to improve speed"
    pass

def dist(query):
    parsed = sqlparse.parse(query)
    for i in parsed[0].tokens:
        #print(i)
        try:
            #print(i)
            if 'DISTINCT' in str(i):
                #print('p')
                return "Avoid DISTINCT if possible to increase speed"
        except:
            pass
def exist(query):
    parsed = sqlparse.parse(query)
    #print(parsed[0].tokens)
    for i in parsed[0].tokens:
        #print(i)
        if 'IN' in str(i):
            return "Use EXISTS instead of IN"

#print(exist(query))
#print(dist(query))
#group(query)
#dates(query)
#like(query)
#star(query)