forked from cryptodotis/code-peer-review
/
databaseQueries.py
executable file
·147 lines (118 loc) · 5.82 KB
/
databaseQueries.py
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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
#!/usr/bin/python
import MySQLdb, time
from common import *
from config import Config
from database import DB
from keywordsfilter import *
from repo import Repo
from commit import Commit
from gitcommit import GitCommit
from svncommit import SVNCommit
class DBQ:
@staticmethod
def find(query, components):
conn = DB.getConn()
c = conn.cursor()
c.execute(query, components)
commitrows = c.fetchall()
commitfiles = []
if commitrows:
allcommitids = ",".join([str(int(commit[0])) for commit in commitrows])
#This is poor practice, but we assured ourselves the value is composed only of ints first
DB.execute(c, "SELECT * from " + DB.commitfile._table + " WHERE commitid IN (" + allcommitids + ")")
commitfiles = c.fetchall()
DB.execute(c, "SELECT * from " + DB.commitkeyword._table + " WHERE commitid IN (" + allcommitids + ")")
commitkeywords = c.fetchall()
DB.execute(c, "SELECT commitid, case when length(data) < 307200 then data else 'TOOLARGE' end as data from " + DB.commitdiffs._table + " WHERE commitid IN (" + allcommitids + ")")
commitdata = c.fetchall()
commits = []
for i in commitrows:
r = Repo()
r.loadFromValues(i[DB.commit._numColumns + DB.repo.id], i[DB.commit._numColumns + DB.repo.name], i[DB.commit._numColumns + DB.repo.repotypeid], i[DB.commit._numColumns + DB.repo.url],
i[DB.commit._numColumns + DB.repo.viewlink], i[DB.commit._numColumns + DB.repo.tagname], i[DB.commit._numColumns + DB.repo.tagmaturity])
files = [file[DB.commitfile.file] for file in commitfiles
if file[DB.commitfile.commitid] == i[DB.commit.id]]
keywords = [keyword[DB.commitkeyword.keyword] for keyword in commitkeywords
if keyword[DB.commitkeyword.commitid] == i[DB.commit.id]]
data = [cdata[DB.commitdiffs.data] for cdata in commitdata
if cdata[DB.commitdiffs.commitid] == i[DB.commit.id]][0]
if i[DB.commit._numColumns + DB.repo.repotypeid] == Repo.Type.GIT:
c = GitCommit()
elif i[DB.commit._numColumns + DB.repo.repotypeid] == Repo.Type.SVN:
c = SVNCommit()
else:
c = Commit()
c.loadFromDatabase(r, i, files, keywords, data)
commits.append(c)
return commits
@staticmethod
def findByKeywords(keywords, moreRecentThan=0):
getcommitsSQL = "SELECT c.*, r.* " + \
"FROM " + DB.commit._table + " c " + \
"INNER JOIN " + DB.repo._table + " r " + \
" ON r.id = c.repoid "
whereClause = " 1=1 "
components = []
if keywords:
keywordsTree = KeywordsParser(keywords)
whereClause, components = keywordsTree.getEvaluationString('sql')
getcommitsSQL += "WHERE "
if moreRecentThan > 0: getcommitsSQL += " c.date > " + str(int(moreRecentThan)) + " AND "
getcommitsSQL += whereClause + " "
getcommitsSQL += "ORDER BY c.date DESC "
return DBQ.find(getcommitsSQL, components)
@staticmethod
def findByIDs(project, uniqueid, moreRecentThan=0):
getcommitsSQL = "SELECT c.*, r.* " + \
"FROM " + DB.commit._table + " c " + \
"INNER JOIN " + DB.repo._table + " r " + \
" ON r.id = c.repoid "
whereClause = " 1=1 "
components = []
if project and uniqueid:
whereClause += "AND r.tagname = %s AND c.uniqueid = %s "
components = [project, uniqueid]
getcommitsSQL += "WHERE "
if moreRecentThan > 0: getcommitsSQL += " c.date > " + str(int(moreRecentThan)) + " AND "
getcommitsSQL += whereClause + " "
getcommitsSQL += "ORDER BY c.date DESC "
return DBQ.find(getcommitsSQL, components)
@staticmethod
def findByKeywordsAndFulltext(keywords, moreRecentThan=0):
getcommitsSQL = "SELECT c.*, r.* " + \
"FROM " + DB.commit._table + " c " + \
"INNER JOIN " + DB.repo._table + " r " + \
" ON r.id = c.repoid "
whereClause = " 1=1 "
components = []
if keywords:
keywordsTree = KeywordsParser(keywords)
whereClause, components = keywordsTree.getEvaluationString('sql')
getcommitsSQL += "WHERE "
if moreRecentThan > 0: getcommitsSQL += " c.date > " + str(int(moreRecentThan)) + " AND "
getcommitsSQL += whereClause + " "
getcommitsSQL += "ORDER BY c.date DESC "
prelim_commits = DBQ.find(getcommitsSQL, components)
#This test is done well enough by the database for now. Maybe eventually we'll need to turn it back on
#if keywords and keywordsTree.anyFulltext():
# final_commits = []
#
# evalstr, evalcomponents = keywordsTree.getEvaluationString('eval')
# evalstr = evalstr % tuple(evalcomponents)
#
# for c in prelim_commits:
# testResult = eval(evalstr)
# if testResult:
# final_commits.append(c)
#
# return final_commits
#else:
return prelim_commits
@staticmethod
def logTerms(ip, keywords):
insertSQL = "INSERT INTO " + DB.searchqueries._table + "(timestamp, ip, terms) " + \
"VALUES(%s, INET_ATON(%s), %s) "
conn = DB.getConn()
c = conn.cursor()
DB.execute(c, insertSQL, (int(time.time()), ip, keywords))
conn.commit()