CMSIS/DSP/Testing/extractDb.py

import argparse
import sqlite3
import re
import pandas as pd
import numpy as np
from TestScripts.doc.Structure import *
from TestScripts.doc.Format import *


runidCMD = "runid = ?"

# Command to get last runid 
lastID="""SELECT runid FROM RUN ORDER BY runid DESC LIMIT 1
"""

# Command to get last runid and date
lastIDAndDate="""SELECT date FROM RUN WHERE runid=?
"""

# Command to get last runid 
runIDDetails="""SELECT distinct core FROM %s 
INNER JOIN CORE USING(coreid)
WHERE %s
"""

def joinit(iterable, delimiter):
    # Intersperse a delimiter between element of a list
    it = iter(iterable)
    yield next(it)
    for x in it:
        yield delimiter
        yield x


def getLastRunID():
  r=c.execute(lastID)
  return(int(r.fetchone()[0]))

def getrunIDDate(forID):
  r=c.execute(lastIDAndDate,(forID,))
  return(r.fetchone()[0])



runid = 1

parser = argparse.ArgumentParser(description='Generate summary benchmarks')

parser.add_argument('-b', nargs='?',type = str, default="bench.db", help="Database")
parser.add_argument('-o', nargs='?',type = str, default="full.md", help="Full summary")
parser.add_argument('-r', action='store_true', help="Regression database")
parser.add_argument('-t', nargs='?',type = str, default="md", help="type md or html")
parser.add_argument('-byc', action='store_true', help="Result oganized by Compiler")
parser.add_argument('-g', action='store_true', help="Include graphs in regression report")

parser.add_argument('-details', action='store_true', help="Details about runids")
parser.add_argument('-lastid', action='store_true', help="Get last ID")

# For runid or runid range
parser.add_argument('others', nargs=argparse.REMAINDER,help="Run ID")

args = parser.parse_args()

c = sqlite3.connect(args.b)

if args.others:
   if len(args.others) == 1:
      if re.search(r'[,]',args.others[0]):
         runidval=tuple([int(x) for x in args.others[0].split(",")])
         runidCMD=["runid == ?" for x in runidval]
         runidCMD = "".join(joinit(runidCMD," OR "))
         runidCMD = "(" + runidCMD + ")"
      else:
         runid=int(args.others[0])
         runidval = (runid,)
   else:
      runidCMD = "runid >= ? AND runid <= ?"
      runid=int(args.others[1])
      runidLOW=int(args.others[0])
      runidval = (runidLOW,runid)
else:
   runid=getLastRunID()
   print("Last run ID = %d\n" % runid)
   runidval=(runid,)


# We extract data only from data tables
# Those tables below are used for descriptions
REMOVETABLES=['TESTNAME','TESTDATE','RUN','CORE', 'PLATFORM', 'COMPILERKIND', 'COMPILER', 'TYPE', 'CATEGORY', 'CONFIG']

# This is assuming the database is generated by the regression script
# So platform is the same for all benchmarks.
# Category and type is coming from the test name in the yaml
# So no need to add this information here
# Name is removed here because it is added at the beginning
REMOVECOLUMNS=['runid','name','type','platform','category','coredef','OPTIMIZED','HARDFP','FASTMATH','NEON','HELIUM','UNROLL','ROUNDING','DATE','compilerkindid','date','categoryid', 'ID', 'platformid', 'coreid', 'compilerid', 'typeid']

REMOVECOLUMNSFORHISTORY=['Regression','MAXREGCOEF','name','type','platform','category','coredef','OPTIMIZED','HARDFP','FASTMATH','NEON','HELIUM','UNROLL','ROUNDING','DATE','compilerkindid','date','categoryid', 'ID', 'platformid', 'coreid', 'compilerid', 'typeid']

# Get existing benchmark tables
def getBenchTables():
    r=c.execute("SELECT name FROM sqlite_master WHERE type='table'")
    benchtables=[]
    for table in r:
        if not table[0] in REMOVETABLES:
          benchtables.append(table[0])
    return(benchtables)

# get existing types in a table
def getExistingTypes(benchTable):
    r=c.execute("select distinct typeid from %s order by typeid desc" % benchTable).fetchall()
    result=[x[0] for x in r]
    return(result)

def getrunIDDetails():
  tables=getBenchTables()
  r=[]
  for table in tables:
      r += [x[0] for x in c.execute(runIDDetails % (table,runidCMD),runidval).fetchall()]
  r=list(set(r)) 
  print(r)

if args.lastid:
   quit()
   
if args.details:
   getrunIDDetails()
   quit()


# Get compilers from specific type and table
allCompilers="""select distinct compilerid from %s WHERE typeid=?"""

# Get compilers from specific type and table
allCores="""select distinct coreid from %s WHERE typeid=?"""


compilerDesc="""select compiler,version from COMPILER 
  INNER JOIN COMPILERKIND USING(compilerkindid) WHERE compilerid=?"""

coreDesc="""select core from CORE WHERE coreid=?"""

# Get existing compiler in a table for a specific type
# (In case report is structured by types)
def getExistingCompiler(benchTable,typeid):
    r=c.execute(allCompilers % benchTable,(typeid,)).fetchall()
    return([x[0] for x in r])

def getExistingCores(benchTable,typeid):
    r=c.execute(allCores % benchTable,(typeid,)).fetchall()
    return([x[0] for x in r])



def getCoreDesc(compilerid):
    r=c.execute(coreDesc,(compilerid,)).fetchone()
    return(r)

def getCompilerDesc(compilerid):
    r=c.execute(compilerDesc,(compilerid,)).fetchone()
    return(r)

# Get type name from type id
def getTypeName(typeid):
    r=c.execute("select type from TYPE where typeid=?",(typeid,)).fetchone()
    return(r[0])
 
# Diff of 2 lists 
def diff(first, second):
        second = set(second)
        return [item for item in first if item not in second]


# Command to get data for specific core 
# and type
historyCmd="""select %s from %s
  INNER JOIN CATEGORY USING(categoryid)
  INNER JOIN PLATFORM USING(platformid)
  INNER JOIN CORE USING(coreid)
  INNER JOIN COMPILER USING(compilerid)
  INNER JOIN COMPILERKIND USING(compilerkindid)
  INNER JOIN TYPE USING(typeid)
  INNER JOIN TESTNAME USING(testnameid)
  WHERE compilerid=? AND coreid=? AND typeid = ? AND ID = ? AND runid > (? - 10)
  """

compilersForHistory="""select distinct compilerid,compiler,version  from %s
  INNER JOIN COMPILER USING(compilerid)
  INNER JOIN COMPILERKIND USING(compilerkindid)
  WHERE coreid=? AND typeid = ? AND ID = ? AND runid > (? - 10)
  """

# Command to get data for specific core 
# and type
benchCmdForCore="""select %s from %s
  INNER JOIN CATEGORY USING(categoryid)
  INNER JOIN PLATFORM USING(platformid)
  INNER JOIN CORE USING(coreid)
  INNER JOIN COMPILER USING(compilerid)
  INNER JOIN COMPILERKIND USING(compilerkindid)
  INNER JOIN TYPE USING(typeid)
  INNER JOIN TESTNAME USING(testnameid)
  WHERE coreid=? AND typeid = ? AND %s
  """

coresForHistory="""select distinct coreid,core from %s
  INNER JOIN CORE USING(coreid)
  WHERE compilerid=? AND typeid = ? AND ID = ? AND runid > (? - 10)
  """

# Command to get data for specific compiler 
# and type
benchCmdForCompiler="""select %s from %s
  INNER JOIN CATEGORY USING(categoryid)
  INNER JOIN PLATFORM USING(platformid)
  INNER JOIN CORE USING(coreid)
  INNER JOIN COMPILER USING(compilerid)
  INNER JOIN COMPILERKIND USING(compilerkindid)
  INNER JOIN TYPE USING(typeid)
  INNER JOIN TESTNAME USING(testnameid)
  WHERE compilerid=? AND typeid = ? AND %s
  """

# Command to get test names for specific compiler 
# and type
benchNamesForCore="""select distinct ID,name from %s
  INNER JOIN COMPILER USING(compilerid)
  INNER JOIN COMPILERKIND USING(compilerkindid)
  INNER JOIN TYPE USING(typeid)
  INNER JOIN TESTNAME USING(testnameid)
  WHERE coreid=? AND typeid = ? AND %s
  """
# Command to get test names for specific compiler 
# and type
benchNamesForCompiler="""select distinct ID,name from %s
  INNER JOIN COMPILER USING(compilerid)
  INNER JOIN COMPILERKIND USING(compilerkindid)
  INNER JOIN TYPE USING(typeid)
  INNER JOIN TESTNAME USING(testnameid)
  WHERE compilerid=? AND typeid = ? AND %s
  """

# Command to get columns for specific table
benchCmdColumns="""select * from %s
  INNER JOIN CATEGORY USING(categoryid)
  INNER JOIN PLATFORM USING(platformid)
  INNER JOIN CORE USING(coreid)
  INNER JOIN COMPILER USING(compilerid)
  INNER JOIN COMPILERKIND USING(compilerkindid)
  INNER JOIN TESTNAME USING(testnameid)
  INNER JOIN TYPE USING(typeid)
  """

def joinit(iterable, delimiter):
    it = iter(iterable)
    yield next(it)
    for x in it:
        yield delimiter
        yield x

# Is not a column name finishing by id 
# (often primary key for thetable)
def isNotIDColumn(col):
    if re.match(r'^.*id$',col):
        return(False)
    else:
        return(True)

# Get test names
# for specific typeid and core (for the data)
def getTestNamesForCore(benchTable,core,typeid):
    vals=(core,typeid) + runidval
    result=c.execute(benchNamesForCore % (benchTable,runidCMD),vals).fetchall()
    names=[(x[0],x[1]) for x in list(result)]
    return(names)

# Get test names
# for specific typeid and compiler (for the data)
def getTestNamesForCompiler(benchTable,comp,typeid):
    vals=(comp,typeid) + runidval
    result=c.execute(benchNamesForCompiler % (benchTable,runidCMD),vals).fetchall()
    names=[(x[0],x[1]) for x in list(result)]
    return(names)

# Command to get data for specific core 
# and type
nbElemsInBenchAndTypeAndCoreCmd="""select count(*) from %s
  WHERE coreid=? AND typeid = ? AND %s
  """

# Command to get data for specific compiler 
# and type
nbElemsInBenchAndTypeAndCompilerCmd="""select count(*) from %s
  WHERE compilerid=? AND typeid = ? AND %s
  """

nbElemsInBenchAndTypeCmd="""select count(*) from %s
  WHERE typeid = ? AND %s
  """

nbElemsInBenchCmd="""select count(*) from %s
  WHERE %s
  """

categoryName="""select distinct category from %s
  INNER JOIN CATEGORY USING(categoryid)
  WHERE %s
  """

def getCategoryName(benchTable,runid):
  result=c.execute(categoryName % (benchTable,runidCMD),runidval).fetchone()
  return(result[0])

# Get nb elems in a table
def getNbElemsInBenchAndTypeAndCoreCmd(benchTable,coreid,typeid):
    vals=(coreid,typeid) + runidval
    result=c.execute(nbElemsInBenchAndTypeAndCoreCmd % (benchTable,runidCMD),vals).fetchone()
    return(result[0])

# Get nb elems in a table
def getNbElemsInBenchAndTypeAndCompilerCmd(benchTable,comp,typeid):
    vals=(comp,typeid) + runidval
    result=c.execute(nbElemsInBenchAndTypeAndCompilerCmd % (benchTable,runidCMD),vals).fetchone()
    return(result[0])

def getNbElemsInBenchAndTypeCmd(benchTable,typeid):
    vals=(typeid,) + runidval
    result=c.execute(nbElemsInBenchAndTypeCmd % (benchTable,runidCMD),vals).fetchone()
    return(result[0])

def getNbElemsInBenchCmd(benchTable):
    result=c.execute(nbElemsInBenchCmd % (benchTable,runidCMD),runidval).fetchone()
    return(result[0])

# Get names of columns and data for a table
# for specific typeid and coreid (for the data)
def getColNamesAndHistory(benchTable,compiler,core,typeid,testid):
    cursor=c.cursor()
    result=cursor.execute(benchCmdColumns % (benchTable))
    cols= [member[0] for member in cursor.description]
    keepCols = ['name','runid'] + [c for c in diff(cols , REMOVECOLUMNSFORHISTORY) if isNotIDColumn(c)]
    keepColsStr = "".join(joinit(keepCols,","))
    vals=(compiler,core,typeid,testid,runid)
    result=cursor.execute(historyCmd % (keepColsStr,benchTable),vals)
    vals =np.array([list(x) for x in list(result)])
    return(keepCols,vals)

# Get names of columns and data for a table
# for specific typeid and coreid (for the data)
def getColNamesAndDataForCore(benchTable,core,typeid):
    cursor=c.cursor()
    result=cursor.execute(benchCmdColumns % (benchTable))
    cols= [member[0] for member in cursor.description]
    keepCols = ['name'] + [c for c in diff(cols , REMOVECOLUMNS) if isNotIDColumn(c)]
    keepColsStr = "".join(joinit(keepCols,","))
    vals=(core,typeid) + runidval
    result=cursor.execute(benchCmdForCore % (keepColsStr,benchTable,runidCMD),vals)
    vals =np.array([list(x) for x in list(result)])
    return(keepCols,vals)


# Get names of columns and data for a table
# for specific typeid and compiler (for the data)
def getColNamesAndDataForCompiler(benchTable,comp,typeid):
    cursor=c.cursor()
    result=cursor.execute(benchCmdColumns % (benchTable))
    cols= [member[0] for member in cursor.description]
    keepCols = ['name'] + [c for c in diff(cols , REMOVECOLUMNS) if isNotIDColumn(c)]
    keepColsStr = "".join(joinit(keepCols,","))
    vals=(comp,typeid) + runidval
    result=cursor.execute(benchCmdForCompiler % (keepColsStr,benchTable,runidCMD),vals)
    vals =np.array([list(x) for x in list(result)])
    return(keepCols,vals)



PARAMS=["NB","NumTaps", "NBA", "NBB", "Factor", "NumStages","VECDIM","NBR","NBC","NBI","IFFT", "BITREV"]

def regressionTableFor(byname,name,section,ref,toSort,indexCols,field):
    data=ref.pivot_table(index=indexCols, columns=byname, 
    values=[field], aggfunc='first')
       
    data=data.sort_values(toSort)
       
    cores = [c[1] for c in list(data.columns)]
    columns = diff(indexCols,['name'])

    dataTable=Table(columns,cores)
    section.addContent(dataTable)

    dataForFunc=data.loc[name]
    if type(dataForFunc) is pd.DataFrame:
       bars={'cols':columns,'cores':cores,'data':[]}
       for row in dataForFunc.itertuples():
           row=list(row)
           if type(row[0]) is int:
              row=[row[0]] + row[1:]
           else: 
              row=list(row[0]) + row[1:]
           if field=="MAXREGCOEF":
              newrow = row
              newrow[len(columns):] = [("%.3f" % x) for x in row[len(columns):]]
              row=newrow
           dataTable.addRow(row)
           bars['data'].append(row)
       return(bars)
    else:
       if field=="MAXREGCOEF":
              dataForFunc=[("%.3f" % x) for x in dataForFunc]
       dataTable.addRow(dataForFunc)
       return(list(zip(cores,dataForFunc)))

def formatColumnName(c):
  return("".join(joinit(c,":")))

def getCoresForHistory(benchTable,compilerid,typeid,testid,runid):
    vals=(compilerid,typeid,testid,runid)
    result=c.execute(coresForHistory % benchTable,vals).fetchall()
    ids=[(x[0],x[1]) for x in list(result)]
    return(ids)

def getCompilerForHistory(benchTable,coreid,typeid,testid,runid):
    vals=(coreid,typeid,testid,runid)
    result=c.execute(compilersForHistory % benchTable,vals).fetchall()
    ids=[(x[0],x[1],x[2]) for x in list(result)]
    return(ids)

def getHistory(desc,testid,indexCols):
    benchName,sectionID,typeid,runid = desc

    #print(benchName)
    #print(sectionID)
    #print(typeid)
    #print(testid)
    columns = diff(indexCols,['name'])
    #print(columns)
    if args.byc:
       coreid=sectionID
       compilerids=getCompilerForHistory(benchName,coreid,typeid,testid,runid)
       series={}
       for compilerid,compilername,version in compilerids:
          result=getColNamesAndHistory(benchName,compilerid,coreid,typeid,testid)
          #print("%s:%s" % (compilername,version))
          maxpos = result[0].index('MAX')
          lrunid = result[0].index('runid')
          r=[[int(x[lrunid]),int(x[maxpos])] for x in result[1:][0]]
          series[corename]=r
       hist=History(series,runid)
       return(hist)
    else:
       compilerid=sectionID
       coreids = getCoresForHistory(benchName,compilerid,typeid,testid,runid)
       series={}
       for coreid,corename in coreids:
          result=getColNamesAndHistory(benchName,compilerid,coreid,typeid,testid)
          #print(corename)
          maxpos = result[0].index('MAX')
          corepos = result[0].index('core')
          lrunid = result[0].index('runid')
          r=[[int(x[lrunid]),int(x[maxpos])] for x in result[1:][0]]
          series[corename]=r
       hist=History(series,runid)
       return(hist)

def formatTableBy(desc,byname,section,typeSection,testNames,cols,vals):
    if vals.size != 0:
       ref=pd.DataFrame(vals,columns=cols)
       toSort=["name"]
       
       for param in PARAMS:
          if param in ref.columns:
             ref[param]=pd.to_numeric(ref[param])
             toSort.append(param)
       if args.r:
         #  Regression table
         ref['MAX']=pd.to_numeric(ref['MAX'])
         ref['MAXREGCOEF']=pd.to_numeric(ref['MAXREGCOEF'])
       
         indexCols=diff(cols,byname + ['Regression','MAXREGCOEF','MAX'] + section)
         valList = ['Regression']
       else:
         ref['CYCLES']=pd.to_numeric(ref['CYCLES'])
       
         indexCols=diff(cols,byname + ['CYCLES'] + section)
         valList = ['CYCLES']
      
       

       for testid,name in testNames:
           if args.r:
              testSection = Section(name)
              typeSection.addSection(testSection)

              maxCyclesSection = Section("Max cycles")
              testSection.addSection(maxCyclesSection)
              theCycles=regressionTableFor(byname,name,maxCyclesSection,ref,toSort,indexCols,'MAX')
              if args.g:
                 if type(theCycles) is dict:
                   nbParams=len(theCycles['cols'])
                   for bar in theCycles['data']:
                       params=bar[0:nbParams]
                       values=bar[nbParams:]
                       title=[("%s=%s" % x) for x in list(zip(theCycles['cols'],params))]
                       title="".join(joinit(title," "))
                       sec=Section(title)
                       maxCyclesSection.addSection(sec)
                       values=list(zip(theCycles['cores'],values))
                       barChart=BarChart(values)
                       sec.addContent(barChart)
                 else:
                    #print(theCycles)
                    sec=Section("Graph")
                    maxCyclesSection.addSection(sec)
                    barChart=BarChart(theCycles)
                    sec.addContent(barChart)

                 #history=getHistory(desc,testid,indexCols)
                 #testSection.addContent(history)

              regressionSection = Section("Regression")
              testSection.addSection(regressionSection)
              regressionTableFor(byname,name,regressionSection,ref,toSort,indexCols,'Regression')
              
              
              maxRegCoefSection = Section("Max Reg Coef")
              testSection.addSection(maxRegCoefSection)
              regressionTableFor(byname,name,maxRegCoefSection,ref,toSort,indexCols,'MAXREGCOEF')

           else:
              data=ref.pivot_table(index=indexCols, columns=byname, 
              values=valList, aggfunc='first')
       
              data=data.sort_values(toSort)
       
              #print(list(data.columns))
              columnsID = [formatColumnName(c[1:]) for c in list(data.columns)]
              columns = diff(indexCols,['name'])

              testSection = Section(name)
              typeSection.addSection(testSection)

              dataTable=Table(columns,columnsID)
              testSection.addContent(dataTable)

              dataForFunc=data.loc[name]
              if type(dataForFunc) is pd.DataFrame:
                 for row in dataForFunc.itertuples():
                     row=list(row)
                     if type(row[0]) is int:
                        row=[row[0]] + row[1:]
                     else: 
                        row=list(row[0]) + row[1:]
                     dataTable.addRow(row)
              else:
                 dataTable.addRow(dataForFunc)

# Add a report for each table
def addReportFor(document,runid,benchName):
    nbElems = getNbElemsInBenchCmd(benchName)
    if nbElems > 0:
       categoryName = getCategoryName(benchName,runid)
       benchSection = Section(categoryName)
       document.addSection(benchSection)
       print("Process %s\n" % benchName)
       allTypes = getExistingTypes(benchName)
       # Add report for each type
       for aTypeID in allTypes:
           nbElems = getNbElemsInBenchAndTypeCmd(benchName,aTypeID)
           if nbElems > 0:
              typeName = getTypeName(aTypeID)
              typeSection = Section(typeName)
              benchSection.addSection(typeSection)
              if args.byc:
                ## Add report for each core
                allCores = getExistingCores(benchName,aTypeID)
                for core in allCores:
                    #print(core)
                    nbElems = getNbElemsInBenchAndTypeAndCoreCmd(benchName,core,aTypeID)
                    # Print test results for table, type, compiler
                    if nbElems > 0:
                       coreName=getCoreDesc(core)
                       coreSection = Section("%s" % coreName)
                       typeSection.addSection(coreSection)
                       cols,vals=getColNamesAndDataForCore(benchName,core,aTypeID)
                       desc=(benchName,core,aTypeID,runid)
                       names=getTestNamesForCore(benchName,core,aTypeID)
                       formatTableBy(desc,['compiler','version'],['core'],coreSection,names,cols,vals)
              else:
                ## Add report for each compiler
                allCompilers = getExistingCompiler(benchName,aTypeID)
                for compiler in allCompilers:
                    #print(compiler)
                    nbElems = getNbElemsInBenchAndTypeAndCompilerCmd(benchName,compiler,aTypeID)
                    # Print test results for table, type, compiler
                    if nbElems > 0:
                       compilerName,version=getCompilerDesc(compiler)
                       compilerSection = Section("%s (%s)" % (compilerName,version))
                       typeSection.addSection(compilerSection)
                       cols,vals=getColNamesAndDataForCompiler(benchName,compiler,aTypeID)
                       desc=(benchName,compiler,aTypeID,runid)
                       names=getTestNamesForCompiler(benchName,compiler,aTypeID)
                       formatTableBy(desc,['core'],['version','compiler'],compilerSection,names,cols,vals)
                       



toc=[Hierarchy("BasicMathsBenchmarks"),    
Hierarchy("ComplexMathsBenchmarks"),
Hierarchy("FastMath"),
Hierarchy("Filters",
  [Hierarchy("FIR"),
   Hierarchy("BIQUAD"),
   Hierarchy("DECIM"), 
   Hierarchy("MISC")]),

Hierarchy("Support Functions",
  [Hierarchy("Support"),
   Hierarchy("SupportBar")]),
        
Hierarchy("Matrix Operations"    ,  
  [Hierarchy("Binary"),
   Hierarchy("Unary")]),
Hierarchy("Transform"),

]

processed=[]

def createDoc(document,sections,benchtables):
    global processed
    for s in sections:
        if s.name in benchtables:
           addReportFor(document,runid,s.name)
           processed.append(s.name)
        else:
           section=Section(s.name)
           document.addSection(section)
           createDoc(section,s.sections,benchtables)

try:
      benchtables=getBenchTables()
      theDate = getrunIDDate(runid)
      document = Document(runid,theDate)
      createDoc(document,toc,benchtables)
      misc=Section("Miscellaneous")
      document.addSection(misc)
      remaining=diff(benchtables,processed)
      for bench in remaining:
          addReportFor(misc,runid,bench)

      #for bench in benchtables:
      #    addReportFor(document,bench)
      with open(args.o,"w") as output:
          if args.t=="md":
             document.accept(Markdown(output))
          if args.t=="html":
             document.accept(HTML(output,args.r))

finally:
     c.close()