def _pivotexecfunc(database,title,ycoltype,xcoltype,tblname,distinct=False,master=False,whereclause=None,result="count(*)"):
headers = {}
with database:
for hdrtype in [ycoltype,xcoltype]:
if distinct==True:
_,headers[hdrtype],_ = tbl_query(database,"select distinct({0}) from {1}".format(hdrtype,tblname))
else:
_,headers[hdrtype],_ = tbl_query(database,"select name from {0}".format(hdrtype))
headers['dow'] = ['MO','TU','WE','TH','FR']
headers[hdrtype] = [_hdrtype[0] for _hdrtype in headers[hdrtype]]
resulttable = []
resulttable.append([title] + headers[ycoltype])
with database:
for xaxishdr in headers[xcoltype]:
row=[]
row.append(xaxishdr)
for yaxishdr in headers[ycoltype]:
exec_str = "select {1} from {0} ".format(tblname,result)
#exec_str = "select subject,teacher from {0} ".format(tblname)
exec_str += "where {1} = {0} ".format("\""+str(yaxishdr)+"\"",ycoltype)
exec_str += " and {1} = {0} ".format("\""+str(xaxishdr)+"\"",xcoltype)
if master == True:
exec_str += " and status = \"master\" "
if whereclause <> None:
for pred,op,predval in whereclause:
exec_str += " and {0} {1} {2} ".format(pred,op,"\""+str(predval)+"\"")
_,results,exec_str = tbl_query(database,exec_str)
try:
if len(results[0]) > 1:
row.append(",".join(results[0]))
else:
row.append(results[0][0])
pass
except:
row.append(0)
resulttable.append(row)
row=[title]
with database:
for yaxishdr in headers[ycoltype]:
exec_str = "select count(*) from {0} ".format(tblname)
exec_str += "where {1} = {0} ".format("\""+str(yaxishdr)+"\"",ycoltype)
_,results,_ = tbl_query(database,exec_str)
try:
row.append(results[0][0])
except:
row.append(0)
resulttable.append(row)
return resulttable
def _pivotexecfunc(database,title,ycoltype,xcoltype,tblname,distinct=False,master=False,whereclause=None,result="count(*)"):
headers = {}
with database:
for hdrtype in [ycoltype,xcoltype]:
if distinct==True:
_,headers[hdrtype],_ = tbl_query(database,"select distinct({0}) from {1}".format(hdrtype,tblname))
else:
_,headers[hdrtype],_ = tbl_query(database,"select name from {0}".format(hdrtype))
headers['dow'] = ['MO','TU','WE','TH','FR']
headers[hdrtype] = [_hdrtype[0] for _hdrtype in headers[hdrtype]]
resulttable = []
resulttable.append([title] + headers[ycoltype])
with database:
for xaxishdr in headers[xcoltype]:
row=[]
row.append(xaxishdr)
for yaxishdr in headers[ycoltype]:
exec_str = "select {1} from {0} ".format(tblname,result)
#exec_str = "select subject,teacher from {0} ".format(tblname)
exec_str += "where {1} = {0} ".format("\""+str(yaxishdr)+"\"",ycoltype)
exec_str += " and {1} = {0} ".format("\""+str(xaxishdr)+"\"",xcoltype)
if master == True:
exec_str += " and status = \"master\" "
if whereclause <> None:
for pred,op,predval in whereclause:
exec_str += " and {0} {1} {2} ".format(pred,op,"\""+str(predval)+"\"")
_,results,exec_str = tbl_query(database,exec_str)
try:
if len(results[0]) > 1:
row.append(",".join(results[0]))
else:
row.append(results[0][0])
pass
except:
row.append(0)
resulttable.append(row)
row=[title]
with database:
for yaxishdr in headers[ycoltype]:
exec_str = "select count(*) from {0} ".format(tblname)
exec_str += "where {1} = {0} ".format("\""+str(yaxishdr)+"\"",ycoltype)
_,results,_ = tbl_query(database,exec_str)
try:
row.append(results[0][0])
except:
row.append(0)
resulttable.append(row)
return resulttable
def mask(oldlist,newlist,map,namemap=None):
if namemap==None:
namemap={}
for i in range(len(oldlist)):
namemap[oldlist[i]] = newlist[int(random()*len(newlist))]
with database:
for tablename,colnames in map.iteritems():
for colname in colnames:
for oldname,newname in namemap.iteritems():
exec_str = " select __id,{0} ".format(colname)
exec_str += " from {0} ".format(tablename)
exec_str += " where {0} like \"%{1}%\"".format(colname,oldname)
_,results,_ = tbl_query(database,exec_str)
for row in results:
__id = row[0]
_old = row[1]
_new = _old.replace(oldname,newname)
exec_str = " update {0} ".format(tablename)
exec_str += " set {0} = \"{1}\"".format(colname,_new)
exec_str += " where __id = \"{0}\"".format(__id)
tbl_query(database,exec_str)
print exec_str
def session_code_gen(dbname,dryrun=False):
database = Database(dbname)
exec_str = "select s.name,lt.code || '.' || s.code"
exec_str += " from subject as s,lessontype as lt"
exec_str += " where s.lessontype = lt.name"
with database:
colnames,rows = tbl_query(database,exec_str)
subject_lookup = dict((row[0],row[1]) for row in rows)
exec_str = "select teacher,subject,__id,code,type,day from session"
with database:
colnames,rows = tbl_query(database,exec_str)
enums = setenums('All','-1',database)
for row in rows:
teacher = row[0]
subject = row[1]
__id = row[2]
oldcode = row[3]
lessontype = row[4]
dow = row[5]
if dow == None:
dow = "??"
if teacher == None:
teacher_code = "??"
lessontype_code = "??"
if lessontype == "WP":
lessontype_code = "WP"
if subject == None:
subject_code = lessontype_code + ".??"
elif subject_lookup.has_key(subject) == False:
subject_code = lessontype_code + ".??"
else:
subject_code =subject_lookup[subject]
teacher_code = enums['adult']['name2code'][teacher]
#session_code = ".".join([teacher_code,subject_code,])
dow_code = enums['dow']['name2code'][dow]
session_code = ".".join([teacher_code,subject_code,dow_code])
if oldcode <> session_code:
with database:
exec_str,_ = tbl_rows_update(database,'session',
['code',"\""+session_code+"\"",'__id',"\""+__id+"\""],
dryrun=dryrun)
print exec_str,oldcode
log.log(thisfuncname(),4,msg="session code updated",execstr=exec_str,oldcode=oldcode)
def _versions_subjects(database,period,dow,student):
exec_str = "select distinct(subject) from lesson"
exec_str += " where period = \"{0}\" ".format(period)
exec_str += " and student = \"{0}\" ".format(student)
exec_str += " and dow = \"{0}\" ".format(dow)
return(tbl_query(database,exec_str))
def getdbenum(enums, database, fldname, tblname, **kwargs):
'''
every table has a code column which is a 2 digit unique mnemonic
given a name lookup a code
given a code lookup a name
given a code/name get an enumeration
get names, codes
'''
#database = Database(dbname)
exec_str = "select {1},code,enum from {0}".format(tblname, fldname)
where_str = ""
for key, value in kwargs.iteritems():
if where_str == "":
where_str = " where {0} = {1}".format(key, value)
else:
where_str = where_str + " and {0} = {1}".format(key, value)
exec_str = exec_str + where_str
import sqlite3
with database:
try:
coldefn, values, _ = tbl_query(database, exec_str)
except DBException, e:
log.log(thisfuncname(),
0,
exception=e,
msg=e.message,
tblname=tblname,
fldname=fldname)
def _versions(database,period,dow,student):
exec_str = "select \"lesson\",dow,period,subject,teacher,source,recordtype from lesson"
exec_str += " where period = \"{0}\" ".format(period)
exec_str += " and student = \"{0}\" ".format(student)
exec_str += " and dow = \"{0}\" ".format(dow)
return(tbl_query(database,exec_str))
def _versions_subjects(database,period,dow,student):
exec_str = "select distinct(subject) from lesson"
exec_str += " where period = \"{0}\" ".format(period)
exec_str += " and student = \"{0}\" ".format(student)
exec_str += " and dow = \"{0}\" ".format(dow)
return(tbl_query(database,exec_str))
def getdbenum(enums,database,fldname,tblname,**kwargs):
'''
every table has a code column which is a 2 digit unique mnemonic
given a name lookup a code
given a code lookup a name
given a code/name get an enumeration
get names, codes
'''
#database = Database(dbname)
exec_str = "select {1},code,enum from {0}".format(tblname,fldname)
where_str = ""
for key,value in kwargs.iteritems():
if where_str == "":
where_str = " where {0} = {1}".format(key,value)
else:
where_str = where_str + " and {0} = {1}".format(key,value)
exec_str = exec_str + where_str
import sqlite3
with database:
try:
coldefn,values,_ = tbl_query(database,exec_str)
except DBException, e:
log.log(thisfuncname(),0,exception=e,msg=e.message,tblname=tblname,fldname=fldname)
def test_(self):
class MyTcxDerived(TcxAnalyzerAddDerivedData):
def create_values(self, **kwargs):
''' to be overwritten; this is just a stub that creates copy of original data'''
bucket_ts = []
for i in range(0, len(self.sourcedata)):
_data = self.sourcedata[i] * kwargs['multiplier']
bucket_ts.append((self.sourceid[i], _data))
return bucket_ts
tcx_add_col = MyTcxDerived(self.databasename)
tcx_add_col.add('bar',
'id',
'watts',
'filename',
"\"fileA\"",
'bar',
'foobar',
'integer',
rows=[[3, '\"foo\"'], [4, '\"bar\"']])
with self.database:
_, rows, _ = tbl_query(self.database, "select id,foobar from bar")
expected_result = [[1, None], [2, None], [3, 'foo'], [4, 'bar']]
self.assertListEqual(rows, expected_result)
def _versions(database,period,dow,student):
exec_str = "select \"lesson\",dow,period,subject,teacher,source,recordtype from lesson"
exec_str += " where period = \"{0}\" ".format(period)
exec_str += " and student = \"{0}\" ".format(student)
exec_str += " and dow = \"{0}\" ".format(dow)
return(tbl_query(database,exec_str))
def _dbid2userdefid(database,asdict=False):
exec_str = "select __id,userobjid from lesson"
_,values,_ = tbl_query(database,exec_str)
if asdict==True:
return(_asdict(values))
return(values)
def _dbid2userdefid(database,asdict=False):
exec_str = "select __id,userobjid from lesson"
_,values,_ = tbl_query(database,exec_str)
if asdict==True:
return(_asdict(values))
return(values)
def _findsessions(database,period,dow,teacher):
exec_str = "select __id,teacher,code,subject,enum "
exec_str += " from session"
exec_str += " where period = {0}".format("\"" + str(period) + "\"")
exec_str += " and dow = {0}".format("\"" + dow + "\"")
exec_str += " and teacher = {0}".format("\"" + teacher + "\"")
return(tbl_query(database,exec_str))
def _findsessions(database,period,dow,teacher):
exec_str = "select __id,teacher,code,subject,enum "
exec_str += " from session"
exec_str += " where period = {0}".format("\"" + str(period) + "\"")
exec_str += " and dow = {0}".format("\"" + dow + "\"")
exec_str += " and teacher = {0}".format("\"" + teacher + "\"")
return(tbl_query(database,exec_str))
def _lastsaveversion_get(self):
try:
with self.database:
colndefn,rows = tbl_query(self.database,"select max(saveversion) from lesson")
return(rows[0][0])
except Exception:
return(-1)
def test(self):
self.tcxparser.persist(self.test_file_name2)
database = Database('foo',True)
with database:
_,rows,_ = tbl_query(database,'select count(*) from foo where filename=\"'+os.path.splitext(self.test_file_name2)[0]+'\"')
self.assertEqual(200,rows[0][0])
def _lastsaveversion_get(self):
try:
with self.database:
colndefn, rows = tbl_query(
self.database, "select max(saveversion) from lesson")
return (rows[0][0])
except Exception:
return (-1)
def _sessionversions(database,period,dow,subject):
exec_str = "select \"session\",dow,period,subject,teacher,source,recordtype from session"
exec_str += " where period = \"{0}\" ".format(period)
exec_str += " and dow = \"{0}\" ".format(dow)
exec_str += " and subject = \"{0}\" ".format(subject)
exec_str += " and substatus = \"nochildrenatinit\" "
exec_str += " and status = \"master\" "
print exec_str
return(tbl_query(database,exec_str))
def _execfunc(database,value,prep,dow):
exec_str = "select s.code "
exec_str += "from session as s,adult as a "
#exec_str += "where a.prep = {0} and ".format(prep)
exec_str += "where s.prep = {0} and ".format(prep)
exec_str += "a.name = s.teacher and "
exec_str += "s.period = {0} and ".format(value)
#exec_str += "s.day = \"{0}\"".format(dow)
exec_str += "s.dow = \"{0}\"".format(dow)
return(tbl_query(database,exec_str))
def _sessionversions(database,period,dow,subject):
exec_str = "select \"session\",dow,period,subject,teacher,source,recordtype from session"
exec_str += " where period = \"{0}\" ".format(period)
exec_str += " and dow = \"{0}\" ".format(dow)
exec_str += " and subject = \"{0}\" ".format(subject)
exec_str += " and substatus = \"nochildrenatinit\" "
exec_str += " and status = \"master\" "
print exec_str
return(tbl_query(database,exec_str))
def _execfunc(database,value,prep,dow):
exec_str = "select s.code "
exec_str += "from session as s,adult as a "
#exec_str += "where a.prep = {0} and ".format(prep)
exec_str += "where s.prep = {0} and ".format(prep)
exec_str += "a.name = s.teacher and "
exec_str += "s.period = {0} and ".format(value)
#exec_str += "s.day = \"{0}\"".format(dow)
exec_str += "s.dow = \"{0}\"".format(dow)
return(tbl_query(database,exec_str))
def test_all(self):
types = ['watts', 'hr']
files = ['fileA', 'fileB']
tcx_analyzer = TcxAnalyzer(self.databasename)
for _type in types:
for _file in files:
tcx_analyzer.get_source_data(self.tablename, 'id', _type,
'filename', "\"" + _file + "\"")
row = tcx_analyzer.get_ts_values()
tcx_analyzer.persist_ts_values(_file, _type, row)
with self.database:
_, watts_rows, _ = tbl_query(self.database,
"select * from watts_values")
_, hr_rows, _ = tbl_query(self.database, "select * from hr_values")
self.assertListEqual(
watts_rows, [[u'fileA', 240, 262, 247], [u'fileB', 306, 311, 308]])
self.assertListEqual(
hr_rows, [[u'fileA', 130, 133, 131], [u'fileB', 150, 157, 152]])
def test_persist_new_column(self):
filename = "fileA"
tcx_analyzer = TcxAnalyzer(self.databasename)
tcx_analyzer.get_source_data(self.tablename, 'id', 'watts', 'filename',
"\"" + filename + "\"")
rows = tcx_analyzer.get_buckets('watts')
tcx_analyzer.add_column_to_table(self.tablename, 'watt_bucket',
'string', rows)
with self.database:
_, row, _ = tbl_query(
self.database, "select watt_bucket from " + self.tablename +
" where filename=\"" + filename + "\"")
self.assertListEqual(row, [[u'221>260'], [u'221>260'], [u'221>260'],
[u'221>260'], [u'261>300'], [u'261>300']])
def test_from_db(self):
tcxparser = TcxParser(TEST_FILES,databasename="foo",tablename="foo2")
limits = tcxparser.get_limits_from_db('bar')
expected_results = {u'test_9trackpoints': {'start': 2, 'end': 6}, u'test_all_trackpoints': {'start': 900, 'end': 1500}}
self.assertEqual(limits,expected_results)
tcxparser.process_files(summation_type="avg",limits=limits,bucket_size=2)
tcxparser.persist()
database = Database('foo',True)
with database:
_,rows,_ = tbl_query(database,'select watts from foo2 where filename=\"test_9trackpoints\"')
self.assertListEqual(rows,self.wattsavg_points)
def get_source_data(self, tablename, pkcolname, valuecolname, predcolname,
predvalue):
''' query's the database and sets the following object attributes
sourceid : list of row indexex and sourcedata : list of values
also stores the predicates used to build the query '''
querystr = "select " + pkcolname + "," + valuecolname + " from " + tablename + " where " + predcolname + "=" + predvalue
try:
with self.database:
_, _rows, _ = tbl_query(self.database, querystr)
if len(_rows[0]) == 0:
raise Exception("zero rows retreived for query [" +
querystr + "]")
except Exception, e:
log.log(PRIORITY.FAILURE,
msg="failed to retrieve source data for [" + predcolname +
"]=[" + predvalue + "][" + e.message + "]")
return False
def _distinct(database,value,table):
exec_str = "select distinct({1}) from {0}".format(table,value)
#if orderby <> False:
# exec_str += " order by prep"
return tbl_query(database,exec_str)
def _maxsessionenum(database):
exec_str = "select max(enum) from session"
try:
return tbl_query(database,exec_str)
except OperationalError:
return [None,[[0]],None]
def _formatsexecfunc(database,colortype):
exec_str = "select f.name,c.hex "
exec_str += "from formats as f, colors as c "
exec_str += "where c.name = f.{0}".format(colortype)
return(tbl_query(database,exec_str))
def _columnheaderexecfunc(database,pred=None,predvalue=None):
exec_str = "select name from student"
if pred <> None:
exec_str = exec_str + " where {0} = {1}".format(pred,predvalue)
return(tbl_query(database,exec_str))
def _rowheaderexecfunc(database,pred=None,predvalue=None):
exec_str = "select name from period"
return(tbl_query(database,exec_str))
def _maxsessionenum(database):
exec_str = "select max(enum) from session"
try:
return tbl_query(database,exec_str)
except OperationalError:
return [None,[[0]],None]
def _rowcount(database,table):
exec_str = "select count(*) from {0} ".format(table)
return(tbl_query(database,exec_str))
def _rowcount(database,table):
exec_str = "select count(*) from {0} ".format(table)
return(tbl_query(database,exec_str))
def session_code_gen(dbname, dryrun=False):
database = Database(dbname)
exec_str = "select s.name,lt.code || '.' || s.code"
exec_str += " from subject as s,lessontype as lt"
exec_str += " where s.lessontype = lt.name"
with database:
colnames, rows = tbl_query(database, exec_str)
subject_lookup = dict((row[0], row[1]) for row in rows)
exec_str = "select teacher,subject,__id,code,type,day from session"
with database:
colnames, rows = tbl_query(database, exec_str)
enums = setenums('All', '-1', database)
for row in rows:
teacher = row[0]
subject = row[1]
__id = row[2]
oldcode = row[3]
lessontype = row[4]
dow = row[5]
if dow == None:
dow = "??"
if teacher == None:
teacher_code = "??"
lessontype_code = "??"
if lessontype == "WP":
lessontype_code = "WP"
if subject == None:
subject_code = lessontype_code + ".??"
elif subject_lookup.has_key(subject) == False:
subject_code = lessontype_code + ".??"
else:
subject_code = subject_lookup[subject]
teacher_code = enums['adult']['name2code'][teacher]
#session_code = ".".join([teacher_code,subject_code,])
dow_code = enums['dow']['name2code'][dow]
session_code = ".".join([teacher_code, subject_code, dow_code])
if oldcode <> session_code:
with database:
exec_str, _ = tbl_rows_update(
database,
'session', [
'code', "\"" + session_code + "\"", '__id',
"\"" + __id + "\""
],
dryrun=dryrun)
print exec_str, oldcode
log.log(thisfuncname(),
4,
msg="session code updated",
execstr=exec_str,
oldcode=oldcode)
def _dowexecfunc(database,value,prep,*args):
exec_str = "select code from dow "
return(tbl_query(database,exec_str))
def _refexecfunc(database,objtype):
exec_str = "select name from " + objtype
return(tbl_query(database,exec_str))
def _colorexecfunc(database):
exec_str = "select name,hex from colors "
return(tbl_query(database,exec_str))
def _columnheaderexecfunc(database,pred=None,predvalue=None):
exec_str = "select name from student"
if pred <> None:
exec_str = exec_str + " where {0} = {1}".format(pred,predvalue)
return(tbl_query(database,exec_str))
def _rowheaderexecfunc(database):
exec_str = "select name from period"
return(tbl_query(database,exec_str))
def _distinct(database,value,table):
exec_str = "select distinct({1}) from {0}".format(table,value)
#if orderby <> False:
# exec_str += " order by prep"
return tbl_query(database,exec_str)
def _dowexecfunc(database,value,prep,*args):
exec_str = "select code from dow "
return(tbl_query(database,exec_str))
def _sessionenum(database,code,period,prep):
exec_str = "select enum from session where code = {0} ".format(code)
exec_str += " and period = {0} ".format(period)
exec_str += " and prep = {0} ".format(prep)
return(tbl_query(database,exec_str))
def _refexecfunc(database,objtype):
exec_str = "select name from " + objtype
return(tbl_query(database,exec_str))
def _colorexecfunc(database):
exec_str = "select name,hex from colors "
return(tbl_query(database,exec_str))
if __name__ == "__main__":
types = ['watts', 'hr']
dbname = "trainerroad"
database = Database(dbname)
tcx_analyzer = TcxAnalyzer(dbname)
#data_filenames = ['20180126_10638564']
#metadata_filenames = ['20180126_10638564']
#UNCOMMENT THIS IF WANT TO REFRESH ALL _VALUES DATA
# get all distinct filenames
with database:
_, data_filenames, _ = tbl_query(database,
"select distinct filename from data")
data_filenames = [_filename[0] for _filename in data_filenames]
# get all filenames that have a meta record
with database:
_, metadata_filenames, _ = tbl_query(
database, "select distinct filename from metadata")
metadata_filenames = [_filename[0] for _filename in metadata_filenames]
# create hr_values and watts_values tables and data
tcx_analyzer.run(metadata_filenames, types)
# adds duration in seconds columns to the metadata tables
tcx_add_col = MyTcxDerived_Seconds(dbname)
for filename in metadata_filenames:
tcx_add_col.add('metadata', 'filename', 'start', 'filename',
def _formatsexecfunc(database,colortype):
exec_str = "select f.name,c.hex "
exec_str += "from formats as f, colors as c "
exec_str += "where c.name = f.{0}".format(colortype)
return(tbl_query(database,exec_str))
def _sessionenum(database,code,period,prep):
exec_str = "select enum from session where code = {0} ".format(code)
exec_str += " and period = {0} ".format(period)
exec_str += " and prep = {0} ".format(prep)
return(tbl_query(database,exec_str))
