def populateData(self, idxs): if len(idxs) == 0: return map( list, DB.fetchall(""" select s.rowid,s.name,t.count,r.count,r.wpm,ifelse(nullif(t.dis,t.count),'No','Yes') from source as s left join (select source,count(*) as count,count(disabled) as dis from text group by source) as t on (s.rowid = t.source) left join (select source,count(*) as count,avg(wpm) as wpm from result group by source) as r on (t.source = r.source) where s.disabled is null order by s.name""")) if len(idxs) > 1: return [] r = self.rows[idxs[0]] return map( list, DB.fetchall( """select t.rowid,substr(t.text,0,40)||"...",length(t.text),r.count,r.m,ifelse(t.disabled,'Yes','No') from (select rowid,* from text where source = ?) as t left join (select text_id,count(*) as count,agg_median(wpm) as m from result group by text_id) as r on (t.id = r.text_id) order by t.rowid""", (r[0], )))
def populateData(self, idxs): if len(idxs) == 0: return map( list, DB.fetchall( """ select s.rowid,s.name,t.count,r.count,r.wpm,ifelse(nullif(t.dis,t.count),'No','Yes') from source as s left join (select source,count(*) as count,count(disabled) as dis from text group by source) as t on (s.rowid = t.source) left join (select source,count(*) as count,avg(wpm) as wpm from result group by source) as r on (t.source = r.source) where s.disabled is null order by s.name""" ), ) if len(idxs) > 1: return [] r = self.rows[idxs[0]] return map( list, DB.fetchall( """select t.rowid,substr(t.text,0,40)||"...",length(t.text),r.count,r.m,ifelse(t.disabled,'Yes','No') from (select rowid,* from text where source = ?) as t left join (select text_id,count(*) as count,agg_median(wpm) as m from result group by text_id) as r on (t.id = r.text_id) order by t.rowid""", (r[0],), ), )
def addFromTyped(self): words = [ x[0] for x in DB.fetchall( 'select distinct data from statistic where type = 2 order by random()' ) ] self.filterWords(words)
def update(self): self.progress_.show() n_text = DB.fetchone("""select count(*) from text""", (0,))[0] self.progress_.inc(2) n_res = DB.fetchone("""select count(*) from result""", (0,))[0] self.progress_.inc(2) n_words = DB.fetchall( """select count(*),sum(count) from statistic group by type order by type""" ) self.progress_.inc(2) if len(n_words) != 3: n_words = [(0, 0), (0, 0), (0, 0)] n_first = DB.fetchone("""select w from result order by w asc limit 1""", (time.time(),))[0] self.progress_.hide() self.stats_.setText( locale.format_string( """Texts: %d Results: %d Analysis data: %d (%d keys, %d trigrams, %d words) %d characters and %d words typed total\n""" + ("First result was %.2f days ago.\n" % ((time.time() - n_first) / 86400.0)), tuple( [n_text, n_res, sum(map(lambda x: x[0], n_words))] + map(lambda x: x[0], n_words) + [n_words[0][1], n_words[2][1]] ), True, ) )
def cleanup(self): s_in_day = 24*60*60 now = time.time() pending = [] for idx, grp, lim in [ (1, 30, Settings.get("group_month")), (2, 7, Settings.get("group_week")), (3, 1, Settings.get("group_day")), ]: minimum = now - s_in_day * lim binsize = s_in_day * grp pending.extend(DB.fetchall(f""" select avg(w), data, type, agg_mean(time, count), sum(count), sum(mistakes), agg_median(viscosity) from statistic where w <= {minimum} group by data, type, cast(w/{binsize} as int)""")) self.progressbar.set_fraction(idx/5) DB.executemany("""insert into statistic (w, data, type, time, count, mistakes, viscosity) values (?,?,?,?,?,?,?)""", pending) self.progressbar.set_fraction(4/5) # FIXME vacuum not supported # DB.execute("vacuum") self.progressbar.set_fraction(5/5) DB.commit() self.progressbar.set_fraction(0)
def update(self): self.progress_.show() n_text = DB.fetchone('''select count(*) from text''', (0, ))[0] self.progress_.inc(2) n_res = DB.fetchone('''select count(*) from result''', (0, ))[0] self.progress_.inc(2) n_words = DB.fetchall('''select count(*), sum(count) from statistic group by type order by type''') self.progress_.inc(2) if len(n_words) != 3: n_words = [(0, 0), (0, 0), (0, 0)] n_first = DB.fetchone( '''select w from result order by w asc limit 1''', (time.time(), ))[0] self.progress_.hide() self.stats_.setText( locale.format_string( '''Texts: %d Results: %d Analysis data: %d (%d keys, %d trigrams, %d words) %d characters and %d words typed total\n''' + ("First result was %.2f days ago.\n" % ((time.time() - n_first) / 86400.0)), tuple([n_text, n_res, sum(map(lambda x: x[0], n_words))] + list(map(lambda x: x[0], n_words)) + [n_words[0][1], n_words[2][1]]), True))
def cleanup(self): day = 24 * 60 * 60 now = time.time() q = [] self.progress_.show() for grp, lim in [(30.0, Settings.get('group_month')), (7.0, Settings.get('group_week')), (1.0, Settings.get('group_day'))]: w = now - day * lim g = grp * day q.extend( DB.fetchall(''' select avg(w), data, type, agg_mean(time, count), sum(count), sum(mistakes), agg_median(viscosity) from statistic where w <= %f group by data, type, cast(w/%f as int)''' % (w, g))) self.progress_.inc() DB.execute('''delete from statistic where w <= ?''', (w, )) self.progress_.inc() DB.executemany( '''insert into statistic (w, data, type, time, count, mistakes, viscosity) VALUES (?, ?, ?, ?, ?, ?, ?)''', q) self.progress_.inc() DB.commit() DB.execute('vacuum') self.progress_.inc() self.progress_.hide()
def cleanup(self): day = 24*60*60 now = time.time() q = [] self.progress_.show() for grp, lim in [(30.0, Settings.get('group_month')), (7.0, Settings.get('group_week')), (1.0, Settings.get('group_day'))]: w = now - day*lim g = grp * day q.extend(DB.fetchall(''' select avg(w), data, type, agg_mean(time, count), sum(count), sum(mistakes), agg_median(viscosity) from statistic where w <= %f group by data, type, cast(w/%f as int)''' % (w, g))) self.progress_.inc() DB.execute('''delete from statistic where w <= ?''', (w, )) self.progress_.inc() DB.executemany('''insert into statistic (w, data, type, time, count, mistakes, viscosity) VALUES (?, ?, ?, ?, ?, ?, ?)''', q) self.progress_.inc() DB.execute('vacuum') self.progress_.inc() DB.commit() self.progress_.hide()
def addFromTyped(self): words = [ x[0] for x in DB.fetchall( "select distinct data from statistic where type = 2 order by random()" ) ] self.filterWords(words)
def updateData(self, *args): if self.editflag: return where = [] if self.cb_source.currentIndex() <= 0: pass elif self.cb_source.currentIndex() == 1: # last text where.append( "r.text_id = (select text_id from result order by w desc limit 1)" ) elif self.cb_source.currentIndex() == 2: # all texts where.append("s.discount is null") elif self.cb_source.currentIndex() == 3: # all lessons texts where.append("s.discount is not null") else: s = self.cb_source.itemData(self.cb_source.currentIndex()) where.append("r.source = %d" % s.toInt()[0]) if len(where) > 0: where = "where " + " and ".join(where) else: where = "" g = Settings.get("perf_group_by") if g == 0: # no grouping sql = """select text_id,w,s.name,wpm,100.0*accuracy,viscosity from result as r left join source as s on (r.source = s.rowid) %s %s order by w desc limit %d""" elif g: sql = """select agg_first(text_id),avg(r.w) as w,count(r.rowid) || ' result(s)',agg_median(r.wpm), 100.0*agg_median(r.accuracy),agg_median(r.viscosity) from result as r left join source as s on (r.source = s.rowid) %s %s order by w desc limit %d""" group = "" if g == 1: # by Settings.get('def_group_by') DB.resetCounter() gn = Settings.get("def_group_by") if gn <= 1: gn = 1 group = "group by cast(counter()/%d as int)" % gn elif g == 2: # by sitting mis = Settings.get("minutes_in_sitting") * 60.0 DB.resetTimeGroup() group = "group by time_group(%f, r.w)" % mis elif g == 3: # by day group = "group by cast((r.w+4*3600)/86400 as int)" n = Settings.get("perf_items") sql = sql % (where, group, n) self.model.setData(map(list, DB.fetchall(sql))) self.updateGraph()
def updateData(self, *args): if self.editflag: return where = [] if self.cb_source.currentIndex() <= 0: pass elif self.cb_source.currentIndex() == 1: # last text where.append( 'r.text_id = (select text_id from result order by w desc limit 1)' ) elif self.cb_source.currentIndex() == 2: # all texts where.append('s.discount is null') elif self.cb_source.currentIndex() == 3: # all lessons texts where.append('s.discount is not null') else: s = self.cb_source.itemData(self.cb_source.currentIndex()) where.append('r.source = %d' % s.toInt()[0]) if len(where) > 0: where = 'where ' + ' and '.join(where) else: where = "" g = Settings.get('perf_group_by') if g == 0: # no grouping sql = '''select text_id,w,s.name,wpm,100.0*accuracy,viscosity from result as r left join source as s on (r.source = s.rowid) %s %s order by w desc limit %d''' elif g: sql = '''select agg_first(text_id),avg(r.w) as w,count(r.rowid) || ' result(s)',agg_median(r.wpm), 100.0*agg_median(r.accuracy),agg_median(r.viscosity) from result as r left join source as s on (r.source = s.rowid) %s %s order by w desc limit %d''' group = '' if g == 1: # by Settings.get('def_group_by') DB.resetCounter() gn = Settings.get('def_group_by') if gn <= 1: gn = 1 group = "group by cast(counter()/%d as int)" % gn elif g == 2: # by sitting mis = Settings.get('minutes_in_sitting') * 60.0 DB.resetTimeGroup() group = "group by time_group(%f, r.w)" % mis elif g == 3: # by day group = "group by cast((r.w+4*3600)/86400 as int)" n = Settings.get("perf_items") sql = sql % (where, group, n) self.model.setData(map(list, DB.fetchall(sql))) self.updateGraph()
def refresh_sources(self): self.editflag = True self.cb_source.remove_all() self.cb_source.append("all", "<ALL>") self.cb_source.append("last text", "<LAST TEXT>") self.cb_source.append("all texts", "<ALL TEXTS>") self.cb_source.append("all lessons", "<ALL LESSONS>") for rid, label in DB.fetchall( "select rowid,abbreviate(name,30) from source order by name"): self.cb_source.append(str(rid), label) self.editflag = False
def doubleClicked(self, idx): p = idx.parent() if not p.isValid(): return q = self.model.data(idx, Qt.UserRole) v = DB.fetchall('select id,source,text from text where rowid = ?', (q[0], )) self.cur = v[0] if len(v) > 0 else self.defaultText self.emit(SIGNAL("setText"), self.cur) self.emit(SIGNAL("gotoText"))
def populate_data(self): self.clear() for source in DB.fetchall(""" select s.rowid,s.name,t.count,r.count,r.wpm,ifelse(nullif(t.dis,t.count),'No','Yes') from source as s left join (select source,count(*) as count,count(disabled) as dis from text group by source) as t on (s.rowid = t.source) left join (select source,count(*) as count,avg(wpm) as wpm from result group by source) as r on (t.source = r.source) where s.disabled is null order by s.name"""): s_iter = self.append(None, list(source)) for text in DB.fetchall( """ select t.rowid,substr(t.text,0,40)||"...",length(t.text),r.count,r.m,ifelse(t.disabled,'Yes','No') from (select rowid,* from text where source = ?) as t left join (select text_id,count(*) as count,agg_median(wpm) as m from result group by text_id) as r on (t.id = r.text_id) order by t.rowid""", (source[0], )): self.append(s_iter, list(text))
def refreshSources(self): self.editflag = True self.cb_source.clear() self.cb_source.addItem("<ALL>") self.cb_source.addItem("<LAST TEXT>") self.cb_source.addItem("<ALL TEXTS>") self.cb_source.addItem("<ALL LESSONS>") for id, v in DB.fetchall('select rowid,abbreviate(name,30) from source order by name'): self.cb_source.addItem(v, QVariant(id)) self.editflag = False
def doubleClicked(self, idx): p = idx.parent() if not p.isValid(): return q = self.model.data(idx, Qt.UserRole) v = DB.fetchall("select id,source,text from text where rowid = ?", (q[0],)) self.cur = v[0] if len(v) > 0 else self.defaultText self.emit(SIGNAL("setText"), self.cur) self.emit(SIGNAL("gotoText"))
def double_clicked(self, treeview, where, _column): model = treeview.get_model() if model.iter_depth(model.get_iter(where)) == 0: return row = Gtk.TreeModelRow(model, where) tgts = DB.fetchall("select id,source,text from text where rowid = ?", (row[0], )) cur = tgts[0] if tgts else self.default_text self.emit("set-text", *cur) self.emit("go-to-text")
def update_data(self): if self.editflag: return where = [] where_query = "" selected = self.cb_source.get_active_id() if selected == "last text": where.append( "r.text_id = (select text_id from result order by w desc limit 1)" ) elif selected == "all texts": where.append("s.discount is null") elif selected == "all lessons": where.append("s.discount is not null") elif selected and selected.isdigit(): rowid = int(selected) where.append(f"r.source = {rowid}") if where: where_query = "where " + " and ".join(where) sql_template = """select agg_first(text_id),avg(r.w) as w,count(r.rowid) || ' result(s)',agg_median(r.wpm), 100.0*agg_median(r.accuracy),agg_median(r.viscosity) from result as r left join source as s on (r.source = s.rowid) %s %s order by w desc limit %d""" groupby = Settings.get("perf_group_by") group = "" print(groupby) if groupby == 1: # by def_group_by DB.reset_counter() group = "group by cast(counter()/%d as int)" % max( Settings.get("def_group_by"), 1) elif groupby == 2: # by sitting mis = Settings.get("minutes_in_sitting") * 60.0 DB.reset_time_group() group = "group by time_group(%f, r.w)" % mis elif groupby == 3: # by day group = "group by cast((r.w+4*3600)/86400 as int)" elif not groupby: # no grouping sql_template = """select text_id,w,s.name,wpm,100.0*accuracy,viscosity from result as r left join source as s on (r.source = s.rowid) %s %s order by w desc limit %d""" items = Settings.get("perf_items") sql = sql_template % (where_query, group, items) self.model.set_stats([list(r) for r in DB.fetchall(sql)]) self.update_graph()
def __init__(self, *args): super(StringStats, self).__init__(*args) self.model = WordModel() tw = AmphTree(self.model) tw.setIndentation(0) tw.setUniformRowHeights(True) tw.setRootIsDecorated(False) self.stats = tw ob = SettingsCombo('ana_which', [ ('wpm asc', 'slowest'), ('wpm desc', 'fastest'), ('viscosity desc', 'least fluid'), ('viscosity asc', 'most fluid'), ('accuracy asc', 'least accurate'), ('misses desc', 'most mistyped'), ('total desc', 'most common'), ('damage desc', 'most damaging'), ]) wc = SettingsCombo('ana_what', ['keys', 'trigrams', 'words']) lim = SettingsEdit('ana_many') s = DB.fetchall("select rowid, name from source") source = SettingsCombo('ana_source', [(-1, 'all')] + s) self.w_count = SettingsEdit('ana_count') self.connect(Settings, SIGNAL("change_ana_which"), self.update) self.connect(Settings, SIGNAL("change_ana_what"), self.update) self.connect(Settings, SIGNAL("change_ana_many"), self.update) self.connect(Settings, SIGNAL("change_ana_count"), self.update) self.connect(Settings, SIGNAL("history"), self.update) self.setLayout( AmphBoxLayout( [[ "Display statistics about the", ob, wc, "from ", source, None, AmphButton("Update List", self.update) ], [ "Limit list to", lim, "items of count ", self.w_count, " and older than", SettingsEdit("history"), "hours", None, AmphButton( "Send List to Lesson Generator", lambda: self.emit(SIGNAL("lessonStrings"), [x[0] for x in self.model.words])) ], (self.stats, 1)]))
def update(self): which = Settings.get("ana_which") what = Settings.get("ana_what") limit = Settings.get("ana_many") least = Settings.get("ana_count") hist = time.time() - Settings.get("history") * 86400.0 sql = f"""select data,12.0/time as wpm, 100.0-100.0*misses/cast(total as real) as accuracy, viscosity,total,misses, total*time*time*(1.0+misses/total) as damage from (select data,agg_median(time) as time,agg_median(viscosity) as viscosity, sum(count) as total,sum(mistakes) as misses from statistic where w >= ? and type = ? group by data) where total >= ? order by {which} limit {limit}""" self.model.set_stats(DB.fetchall(sql, (hist, what, least)))
def __init__(self, *args): super(StringStats, self).__init__(*args) self.model = WordModel() tw = AmphTree(self.model) tw.setIndentation(0) tw.setUniformRowHeights(True) tw.setRootIsDecorated(False) self.stats = tw ob = SettingsCombo('ana_which', [ ('wpm asc', 'slowest'), ('wpm desc', 'fastest'), ('viscosity desc', 'least fluid'), ('viscosity asc', 'most fluid'), ('accuracy asc', 'least accurate'), ('misses desc', 'most mistyped'), ('total desc', 'most common'), ('damage desc', 'most damaging'), ]) wc = SettingsCombo('ana_what', ['keys', 'trigrams', 'words']) lim = SettingsEdit('ana_many') s = DB.fetchall("select rowid, name from source") source = SettingsCombo('ana_source', [ (-1, 'all') ] + s) self.w_count = SettingsEdit('ana_count') self.connect(Settings, SIGNAL("change_ana_which"), self.update) self.connect(Settings, SIGNAL("change_ana_what"), self.update) self.connect(Settings, SIGNAL("change_ana_many"), self.update) self.connect(Settings, SIGNAL("change_ana_count"), self.update) self.connect(Settings, SIGNAL("history"), self.update) self.setLayout(AmphBoxLayout([ ["Display statistics about the", ob, wc, "from ", source, None, AmphButton("Update List", self.update)], ["Limit list to", lim, "items of count ", self.w_count," and older than", SettingsEdit("history"), "hours", None, AmphButton("Send List to Lesson Generator", lambda: self.emit(SIGNAL("lessonStrings"), [x[0] for x in self.model.words]))], (self.stats, 1) ]))
def update(self, *arg): ord = Settings.get('ana_which') cat = Settings.get('ana_what') limit = Settings.get('ana_many') count = Settings.get('ana_count') hist = time.time() - Settings.get('history') * 86400.0 sql = """select data,12.0/time as wpm, 100.0-100.0*misses/cast(total as real) as accuracy, viscosity,total,misses, total*time*time*(1.0+misses/total) as damage from (select data,agg_median(time) as time,agg_median(viscosity) as viscosity, sum(count) as total,sum(mistakes) as misses from statistic where w >= ? and type = ? group by data) where total >= ? order by %s limit %d""" % (ord, limit) self.model.setData(DB.fetchall(sql, (hist, cat, count)))
def update(self): texts = DB.fetchone("select count(*) from text", (0,))[0] self.progressbar.set_fraction(1/4) results = DB.fetchone("select count(*) from result", (0,))[0] self.progressbar.set_fraction(2/4) keys, trigrams, words = DB.fetchall( "select count(*),sum(count) from statistic group by type order by type") self.progressbar.set_fraction(3/4) first = DB.fetchone("select w from result order by w asc limit 1", (time.time(), ))[0] self.progressbar.set_fraction(4/4) total = keys[0] + trigrams[0] + words[0] history = (time.time() - first) / 86400 self.stats.set_text(f""" Texts: {texts} Results: {results} Analysis data: {total} ({keys[0]} keys, {trigrams[0]} trigrams, {words[0]} words) {keys[1]} characters and {words[1]} words typed in total. First result was {round(history, 2)} days ago. """) self.progressbar.set_fraction(0)
def add_from_typed(self): query = 'select distinct data from statistic where type = 2 order by random()' words = [x[0] for x in DB.fetchall(query)] self.filter_words(words)