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
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
|
#!/usr/bin/env python
# This script copies the contents of the 'bm' database into the new and
# faster schema of the 'bm-fast' database.
import sys
import psycopg2
# === Global functions ===============================================
# Connects to a database and register the cursor in the global cursor list.
def connectDatabase(db):
try:
# ### hardcode for now:
conn = psycopg2.connect(
host="bmc.test.qt.nokia.com",
port="5432",
database=db,
user="bmuser")
except:
print "failed to connect to the database:", sys.exc_info()
sys.exit(1)
cursors[db] = conn.cursor()
# Executes a query against a database. Returns the result set if any.
def execQuery(db, query, fetch_results = True):
global cursors
if not 'cursors' in globals():
cursors = {}
if not db in cursors:
connectDatabase(db)
assert db in cursors
try:
cursors[db].execute(query)
if (fetch_results):
return cursors[db].fetchall()
except psycopg2.Error:
print "query failed: >" + query + "<"
print "reason:", str(sys.exc_info())
sys.exit(1)
# Commits everything that has been written to a database.
def commit(db):
cursor = cursors[db]
conn = cursor.connection
conn.commit()
cursor.close()
conn.close()
# ### 2 B DOCUMENTED!
def findOrInsertId(table, value, *args):
db = "bm-fast"
#print "value: >" + value + "<, ",
query_result = execQuery(
db, "SELECT id FROM " + table + " WHERE value = '" + str(value) + "';")
if len(query_result) == 1:
# Found, so return ID:
#print "returning existing ID: >" + str(query_result[0][0]) + "<"
return query_result[0][0]
# Not found, so insert:
query = "INSERT INTO " + table + " (value"
for i in range(0, len(args), 2):
query += ", " + str(args[i])
query += ") VALUES ('" + str(value) + "'";
for i in range(0, len(args), 2):
query += ", " + str(args[i + 1])
query += ");";
execQuery(db, query, False)
# ... and retrieve ID:
query_result = execQuery(
db, "SELECT id FROM " + table + " WHERE value = '" + str(value) + "';")
assert len(query_result) == 1
#print "returning new ID: >" + str(query_result[0][0]) + "<"
return query_result[0][0]
# ### 2 B DOCUMENTED!
def insertUpload(id_, startTime, clientIPAddr):
db = "bm-fast"
query = (
"INSERT INTO upload (id, startTime, clientIPAddr) VALUES (" +
str(id_) + ", '" + str(startTime) + "', '" + str(clientIPAddr) + "');")
execQuery(db, query, False)
# ### 2 B DOCUMENTED!
def insertResult(
hostId, platformId, branchId, sha1Id, benchmarkId, value, valid,
metricId, uploadId):
db = "bm-fast"
query = (
"INSERT INTO result (hostId, platformId, branchId, sha1Id, " +
"benchmarkId, value, valid, metricId, uploadId) VALUES ("
+ str(hostId)
+ ", " + str(platformId)
+ ", " + str(branchId)
+ ", " + str(sha1Id)
+ ", " + str(benchmarkId)
+ ", " + str(value)
+ ", " + str(valid)
+ ", " + str(metricId)
+ ", " + str(uploadId)
+ ");"
)
execQuery(db, query, False)
# === Main program ==========================================================
"""
Column layout of 'result' table in 'bm' database:
0: id | bigint
1: host - text
2: platform - text
3: branch - text
4: sha1 - text
5: timestamp | integer
6: testcase - text
7: testfunction - text
8: datatag - text
9: value - real
10: valid - boolean
11: metric - text
12: lowerisbetter - boolean
13: uploadid - bigint
"""
# *** Phase 1: Populate upload table ***
print "populating upload table ... ",
sys.stdout.flush()
# Copy explicitly from bm to bm-fast (no auto-incrementing of id attr):
bm_upload_rows = execQuery("bm", "SELECT * FROM upload;")
for u in bm_upload_rows:
insertUpload(u[0], u[1], u[2])
# Update auto-increment sequence for id attr:
maxId = execQuery("bm-fast", "SELECT max(id) FROM upload;")[0][0]
execQuery("bm-fast", "SELECT setval('upload_id_seq', " + str(maxId) + ");")
# *** Phase 2: Populate result table and text tables ***
print "done\npopulating result table ... "
sys.stdout.flush()
bm_result_rows = execQuery("bm", "SELECT * FROM result;")
i = 0
n = len(bm_result_rows)
progress_step = n / 1000
for r in bm_result_rows:
hostId = findOrInsertId("host", r[1])
platformId = findOrInsertId("platform", r[2])
branchId = findOrInsertId("branch", r[3])
sha1Id = findOrInsertId("sha1", r[4])
metricId = findOrInsertId("metric", r[11], "lowerIsBetter", r[12])
benchmark = r[6] + ":" + r[7] + "(" + str(r[8]) + ")"
benchmarkId = findOrInsertId("benchmark", benchmark)
insertResult(
hostId, platformId, branchId, sha1Id, benchmarkId, r[9], r[10],
metricId, r[13])
# Show progress:
i = i + 1
if (progress_step == 0) or ((i - 1) % progress_step):
print "\r{0:5.1f}%".format(100 * (i / float(n))),
sys.stdout.flush()
commit("bm-fast")
|
