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
|
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# Copyright (C) 2017 Mate Soos
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; version 2
# of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
# 02110-1301, USA.
import helper
import optparse
import time
class QueryFixPerc (helper.QueryHelper):
def __init__(self, dbfname):
super(QueryFixPerc, self).__init__(dbfname)
def check_table_exists(self):
q = "SELECT name FROM sqlite_master WHERE type='table' AND name='{table_name}';".format(
table_name="used_later_percentiles_backup")
self.c.execute(q)
rows = self.c.fetchall()
if len(rows) == 1:
print("Already fixed")
return True
return False
def copy_db(self):
# Drop table
q_drop = """
DROP TABLE IF EXISTS `used_later_percentiles_backup`;
"""
self.c.execute(q_drop)
# Create and fill used_later_X tables
q_create = """
CREATE TABLE used_later_percentiles_backup AS
SELECT *
FROM used_later_percentiles;"""
self.c.execute(q_create)
def create_percentiles_table(self):
# Drop table
q_drop = """
DROP TABLE IF EXISTS `used_later_percentiles`;
"""
self.c.execute(q_drop)
# Create and fill used_later_X tables
q_create = """
create table `used_later_percentiles` (
`type_of_dat` string NOT NULL,
`percentile_descr` string NOT NULL,
`percentile` float DEFAULT NULL,
`val` float NOT NULL
);"""
self.c.execute(q_create)
idxs = """
create index `used_later_percentiles_idx3` on `used_later_percentiles` (`type_of_dat`, `percentile_descr`, `percentile`, `val`);
create index `used_later_percentiles_idx2` on `used_later_percentiles` (`type_of_dat`, `percentile_descr`, `val`);"""
for q in idxs.split("\n"):
self.c.execute(q)
def fix(self):
q2 = """
insert into used_later_percentiles (type_of_dat, percentile_descr, percentile, val)
{q}
"""
t = time.time()
q = "select * from used_later_percentiles_backup"
self.c.execute(q)
rows = self.c.fetchall()
fixed = 0
for i in range(len(rows)):
if options.verbose:
print("row: ", rows[i])
row = rows[i]
a = row[0][1:]
c = None
if row[1][:13] == "top_also_zero":
b = "top_also_zero"
c = row[1][13:]
elif row[1][:12] == "top_non_zero":
b = "top_non_zero"
c = row[1][12:]
elif row[1] == "avg":
b = "avg"
c = None
else:
print("c ? ", row[1])
assert False, "What?"
if c is not None:
c = float(c.rstrip("_perc").strip("_"))
else:
c = "NULL"
d = row[2]
if a == "":
# this was removed
continue
if options.verbose:
print("a: ", a)
print("b: ", b)
print("c: ", c)
print("d: ", d)
self.c.execute(q2.format(q="select '{a}', '{b}', {c}, {d};".format(
a=a, b=b, c=c, d=d)))
fixed+=1
print("Fixed:", fixed)
# fix 100
for name in ["short", "long", "forever"]:
self.c.execute(q2.format(name=name, q="select '{name}', 'top_non_zero', 100.0, 0.0;".format(name=name)))
self.c.execute(q2.format(name=name, q="select '{name}', 'top_also_zero', 100.0, 0.0;".format(name=name)))
print("Fixed top 100 too")
if __name__ == "__main__":
usage = "usage: %prog [options] file1.sqlite [file2.sqlite ...]"
parser = optparse.OptionParser(usage=usage)
# verbosity level
parser.add_option("--verbose", "-v", action="store_true", default=False,
dest="verbose", help="Print more output")
parser.add_option("--sql", action="store_true", default=False,
dest="dump_sql", help="Dump SQL queries")
(options, args) = parser.parse_args()
if len(args) < 1:
print("ERROR: You must give at least one file")
exit(-1)
for f in args:
print("Doing file:" , f)
with QueryFixPerc(f) as q:
if q.check_table_exists():
continue
q.copy_db()
q.create_percentiles_table()
q.fix()
|
