--- /dev/null
+#!/usr/bin/python
+# -*- coding: utf8 -*-
+# author: wly
+# date: 2015-07-29
+# see license for license details
+__version__ = ''' '''
+import os
+import logging
+
+from sqlalchemy import create_engine
+from sqlalchemy.orm import sessionmaker
+from sqlalchemy import and_
+from vstf.controller.database.tables import *
+
+LOG = logging.getLogger(__name__)
+
+"""
+@event.listens_for(Engine, "before_cursor_execute")
+def before_cursor_execute(conn, cursor, statement,
+ parameters, context, executemany):
+ conn.info.setdefault('query_start_time', []).append(time.time())
+ logging.debug("Start Query: %s", statement)
+@event.listens_for(Engine, "after_cursor_execute")
+def after_cursor_execute(conn, cursor, statement,
+ parameters, context, executemany):
+ total = time.time() - conn.info['query_start_time'].pop(-1)
+ logging.debug("Query Complete!")
+ logging.debug("Total Time: %f", total)"""
+
+
+class DbManage(object):
+ def __init__(self, db_name=const.DBPATH):
+ db_exists = os.path.exists(db_name)
+ try:
+ self._engine = create_engine('sqlite:///%s' % db_name, echo=False)
+ db_session = sessionmaker(bind=self._engine)
+ self._session = db_session()
+ except Exception as e:
+ raise e
+
+ # if the db is new , cleate all tables and init static tables
+ if not db_exists:
+ self.create_tables()
+ self.init_tables()
+
+ def __delete__(self):
+ self._engine.close_all()
+
+ def create_tables(self):
+ Base.metadata.create_all(self._engine)
+ self._session.commit()
+
+ def drop_tables(self):
+ Base.metadata.drop_all(self._engine)
+ self._session.commit()
+
+ def init_tables(self):
+ self.init_casetable()
+ self.init_scenario_table()
+ self._session.commit()
+
+ def init_scenario_table(self):
+ items = []
+ for values in const.SCENARIO_INFO_LIST:
+ item = TblScenarioInfo(ScenarioName=values[0],
+ FigurePath=values[1],
+ Description=values[2])
+ items.append(item)
+ self._session.add_all(items)
+
+ # Single TblCaseInfo API
+ def init_casetable(self):
+ items = []
+ for values in const.CASE_INFO_LIST:
+ item = TblCaseInfo(CaseTag=values[0],
+ ScenarioName=values[1],
+ CaseName=values[2],
+ FigurePath=values[3],
+ Description=values[4],
+ Direction=values[5],
+ Directiontag=values[6],
+ Configure=values[7])
+ items.append(item)
+ self._session.add_all(items)
+
+ def query_caseinfo(self):
+ query = self._session.query(TblCaseInfo.ScenarioName,
+ TblCaseInfo.CaseTag,
+ TblCaseInfo.CaseName,
+ TblCaseInfo.Direction,
+ TblCaseInfo.Configure)
+ return query.all()
+
+ def query_case(self, casetag):
+ query = self._session.query(TblCaseInfo.ScenarioName,
+ TblCaseInfo.Directiontag)
+ return query.first()
+
+ # Single TblTaskList API
+ def get_last_taskid(self):
+ query = self._session.query(TblTaskList.TaskID)
+ if query:
+ return query.all()[-1][0]
+ else:
+ return 0
+
+ def query_tasklist(self):
+ query = self._session.query(TblTaskList)
+ return query.all()
+
+ def query_taskdate(self, taskid):
+ query = self._session.query(TblTaskList.Date).filter(and_(
+ TblTaskList.TaskID == taskid))
+ result = ""
+ if query:
+ result += query.first()[0]
+ return result
+
+ def query_taskname(self, taskid):
+ query = self._session.query(TblTaskList.TaskName).filter(and_(
+ TblTaskList.TaskID == taskid))
+ result = ""
+ if query:
+ result += query.first()[0]
+ return result
+
+ def create_task(self, name, date, desc):
+ try:
+ item = TblTaskList(name, date, desc)
+ self._session.add(item)
+ self._session.commit()
+ except Exception:
+ return -1
+
+ return self.get_last_taskid()
+
+ # Single TblHostInfo API
+ def add_host_2task(self, taskid, name, machine, cpu, men, nic, os):
+ """All var except task must be string"""
+ item = TblHostInfo(taskid, name, machine, cpu, men, nic, os)
+
+ self._session.add(item)
+ self._session.commit()
+
+ def query_task_host_list(self, taskid):
+ query = self._session.query(TblHostInfo.HostName,
+ TblHostInfo.Server,
+ TblHostInfo.CPU,
+ TblHostInfo.MEM,
+ TblHostInfo.NIC,
+ TblHostInfo.OS).filter(
+ TblHostInfo.TaskID == taskid)
+ return query.all()
+
+ # Single TblTestList API
+ def get_last_testid(self):
+ query = self._session.query(TblTestList.TestID)
+ if query:
+ return query.all()[-1][0]
+ else:
+ return 0
+
+ def add_test_2task(self, task, case, protocol, provider, typ, tool):
+ try:
+ item = TblTestList(task, case, protocol, provider, typ, tool)
+ self._session.add(item)
+ self._session.commit()
+ except Exception:
+ return -1
+
+ return self.get_last_testid()
+
+ def get_test_type(self, testid):
+ query = self._session.query(TblTestList.Type).filter(
+ TblTestList.TestID == testid)
+ return query.first()
+
+ def add_extent_2task(self, task, name, content, description):
+ item = TblEXTInfo(task, name, content, description)
+ self._session.add(item)
+ self._session.commit()
+
+ def add_data_2test(self, testid, data):
+ """
+ :data example {'64':{
+ 'AvgFrameSize':0
+ 'OfferedLoad':0
+ 'PercentLoss':0
+ 'Bandwidth':0
+ 'MinimumLatency':0
+ 'MaximumLatency':0
+ 'AverageLatency':0
+ 'TxFrameCount':0
+ 'RxFrameCount':0
+ 'Duration':0
+ 'CPU':0
+ 'MppspGhz':0
+ }}
+ """
+ ptype = self.get_test_type(testid)
+ instance_map = {
+ 'throughput': TblThroughput,
+ 'frameloss': TblFrameloss,
+ 'latency': TblLatency
+ }
+
+ if ptype and ptype[0] not in instance_map:
+ print "cant find this test(id=%d)" % (testid)
+ return False
+
+ test_table_instance = instance_map[ptype[0]]
+ for pktlen in data.iterkeys():
+ args = data.get(pktlen)
+ query = self._session.query(test_table_instance).filter(and_(
+ test_table_instance.TestID == testid,
+ test_table_instance.AvgFrameSize == pktlen))
+ if query.all():
+ data_dict = {}
+ for key, value in data.items():
+ if key in test_table_instance.__dict__:
+ data_dict[test_table_instance.__dict__[key]] = value
+ query.update(data_dict)
+ else:
+ print args
+ tester = test_table_instance(testid, pktlen, **args)
+ self._session.add(tester)
+ self._session.commit()
+
+ def query_tasks(self):
+ result = []
+ ret = self._session.query(TblTaskList)
+ if ret:
+ for tmp in ret.all():
+ result.append([tmp.TaskID, tmp.TaskName, tmp.Date, tmp.EXTInfo])
+ return result
+
+ def query_all_task_id(self):
+ query = self._session.query(TblTaskList.TaskID)
+ if query:
+ return query.all()
+ else:
+ return []
+
+ def get_caseinfo(self):
+ query = self._session.query(TblCaseInfo.ScenarioName,
+ TblCaseInfo.CaseTag,
+ TblCaseInfo.CaseName,
+ TblCaseInfo.Direction,
+ TblCaseInfo.Configure)
+ return query.all()
+
+ def query_scenario(self, casetag):
+ query = self._session.query(TblCaseInfo.ScenarioName).filter(TblCaseInfo.CaseTag == casetag)
+ ret = ""
+ if query and query.first():
+ ret = query.first()[0]
+ return ret
+
+ def query_casefigure(self, casetag, tools):
+ query = self._session.query(TblCaseInfo.FigurePath).filter(and_(
+ TblCaseInfo.CaseTag == casetag))
+ result = ""
+ if query:
+ result += query.first()[0]
+ print tools, casetag
+ result += tools + '/' + casetag + '.jpg'
+ return result
+
+ def query_casename(self, casetag):
+ query = self._session.query(TblCaseInfo.CaseName).filter(and_(
+ TblCaseInfo.CaseTag == casetag))
+ result = ""
+ if query:
+ result += query.first()[0]
+ return result
+
+ # Single TblScenarioInfo API
+
+ def query_caselist(self, taskid, scenario):
+ query = self._session.query(TblTestList.CaseTag).filter(and_(
+ TblTestList.CaseTag == TblCaseInfo.CaseTag,
+ TblCaseInfo.ScenarioName == scenario,
+ TblTestList.TaskID == taskid)).group_by(TblCaseInfo.CaseTag)
+ return query.all()
+
+ def query_casetool(self, taskid, casetag, provider, ptype):
+ query = self._session.query(TblTestList.Tools).filter(and_(
+ TblTestList.TaskID == taskid,
+ TblTestList.CaseTag == casetag,
+ TblTestList.Provider == provider,
+ TblTestList.Type == ptype))
+ return query.all()
+
+ def query_casetools(self, taskid, casetag):
+ query = self._session.query(TblTestList.Tools).filter(and_(
+ TblTestList.CaseTag == casetag,
+ TblTestList.TaskID == taskid)).group_by(TblTestList.Tools)
+ return query.all()
+
+ def query_scenariolist(self, taskid):
+ query = self._session.query(TblCaseInfo.ScenarioName).filter(and_(
+ TblTestList.CaseTag == TblCaseInfo.CaseTag,
+ TblTestList.TaskID == taskid)).group_by(TblCaseInfo.ScenarioName)
+ return query.all()
+
+ def query_throughput_load(self, taskid, casetag, provider):
+ ptype = 'throughput'
+ query = self._session.query(TblThroughput.AvgFrameSize, TblThroughput.OfferedLoad).filter(and_(
+ TblTestList.TaskID == taskid,
+ TblTestList.CaseTag == casetag,
+ TblTestList.Provider == provider, TblTestList.Type == ptype,
+ TblTestList.TestID == TblThroughput.TestID))
+ return query.all()
+
+ def query_throughput_bandwidth(self, taskid, casetag, provider):
+ ptype = 'throughput'
+ query = self._session.query(TblThroughput.AvgFrameSize, TblThroughput.Bandwidth).filter(and_(
+ TblTestList.TaskID == taskid,
+ TblTestList.CaseTag == casetag,
+ TblTestList.Provider == provider, TblTestList.Type == ptype,
+ TblTestList.TestID == TblThroughput.TestID))
+ return query.all()
+
+ def query_throughput_table(self, taskid, casetag, provider):
+ ptype = 'throughput'
+ query = self._session.query(TblThroughput.AvgFrameSize,
+ TblThroughput.Bandwidth,
+ TblThroughput.OfferedLoad,
+ TblThroughput.CPU,
+ TblThroughput.MppspGhz,
+ TblThroughput.MinimumLatency,
+ TblThroughput.MaximumLatency,
+ TblThroughput.AverageLatency,
+ ).filter(and_(
+ TblTestList.TaskID == taskid,
+ TblTestList.CaseTag == casetag,
+ TblTestList.Provider == provider, TblTestList.Type == ptype,
+ TblTestList.TestID == TblThroughput.TestID))
+ return query.all()
+
+ def query_throughput_simpletable(self, taskid, casetag, provider):
+ ptype = 'throughput'
+ query = self._session.query(TblThroughput.AvgFrameSize,
+ TblThroughput.Bandwidth,
+ TblThroughput.OfferedLoad,
+ TblThroughput.CPU,
+ TblThroughput.MppspGhz,
+ TblThroughput.AverageLatency,
+ ).filter(and_(
+ TblTestList.TaskID == taskid,
+ TblTestList.CaseTag == casetag,
+ TblTestList.Provider == provider, TblTestList.Type == ptype,
+ TblTestList.TestID == TblThroughput.TestID))
+ return query.all()
+
+ def query_throughput_avg(self, taskid, casetag, provider):
+ ptype = 'throughput'
+ query = self._session.query(TblThroughput.AvgFrameSize, TblThroughput.AverageLatency).filter(and_(
+ TblTestList.TaskID == taskid,
+ TblTestList.CaseTag == casetag,
+ TblTestList.Provider == provider, TblTestList.Type == ptype,
+ TblTestList.TestID == TblThroughput.TestID))
+ return query.all()
+
+ def query_frameloss_bandwidth(self, taskid, casetag, provider):
+ ptype = 'frameloss'
+ query = self._session.query(TblFrameloss.AvgFrameSize, TblFrameloss.Bandwidth).filter(and_(
+ TblTestList.TaskID == taskid,
+ TblTestList.CaseTag == casetag,
+ TblTestList.Provider == provider, TblTestList.Type == ptype,
+ TblTestList.TestID == TblFrameloss.TestID))
+ return query.all()
+
+ def query_frameloss_load(self, taskid, casetag, provider):
+ ptype = 'frameloss'
+ query = self._session.query(TblFrameloss.AvgFrameSize, TblFrameloss.OfferedLoad).filter(and_(
+ TblTestList.TaskID == taskid,
+ TblTestList.CaseTag == casetag,
+ TblTestList.Provider == provider, TblTestList.Type == ptype,
+ TblTestList.TestID == TblFrameloss.TestID))
+ return query.all()
+
+ def query_frameloss_table(self, taskid, casetag, provider):
+ ptype = 'frameloss'
+ query = self._session.query(TblFrameloss.AvgFrameSize,
+ TblFrameloss.Bandwidth,
+ TblFrameloss.OfferedLoad,
+ TblFrameloss.CPU,
+ TblFrameloss.MppspGhz,
+ TblFrameloss.MinimumLatency,
+ TblFrameloss.MaximumLatency,
+ TblFrameloss.AverageLatency,
+ ).filter(and_(
+ TblTestList.TaskID == taskid,
+ TblTestList.CaseTag == casetag,
+ TblTestList.Provider == provider, TblTestList.Type == ptype,
+ TblTestList.TestID == TblFrameloss.TestID))
+ return query.all()
+
+ def query_frameloss_simpletable(self, taskid, casetag, provider):
+ ptype = 'frameloss'
+ query = self._session.query(TblFrameloss.AvgFrameSize,
+ TblFrameloss.Bandwidth,
+ TblFrameloss.OfferedLoad,
+ TblFrameloss.CPU,
+ TblFrameloss.MppspGhz,
+ TblFrameloss.AverageLatency,
+ ).filter(and_(
+ TblTestList.TaskID == taskid,
+ TblTestList.CaseTag == casetag,
+ TblTestList.Provider == provider, TblTestList.Type == ptype,
+ TblTestList.TestID == TblFrameloss.TestID))
+ return query.all()
+
+ def query_frameloss_avg(self, taskid, casetag, provider):
+ ptype = 'frameloss'
+ query = self._session.query(TblFrameloss.AvgFrameSize, TblFrameloss.AverageLatency).filter(and_(
+ TblTestList.TaskID == taskid,
+ TblTestList.CaseTag == casetag,
+ TblTestList.Provider == provider, TblTestList.Type == ptype,
+ TblTestList.TestID == TblFrameloss.TestID))
+ return query.all()
+
+ def query_latency_avg(self, taskid, casetag, provider):
+ ptype = 'latency'
+ query = self._session.query(TblLatency.AvgFrameSize, TblLatency.AverageLatency).filter(and_(
+ TblTestList.TaskID == taskid,
+ TblTestList.CaseTag == casetag,
+ TblTestList.Provider == provider, TblTestList.Type == ptype,
+ TblTestList.TestID == TblLatency.TestID))
+ return query.all()
+
+ def query_summary_table(self, taskid, casetag, provider, ptype):
+ if ptype in ['throughput', 'frameloss']:
+ qfunc = getattr(self, "query_%s_table" % (ptype))
+ return qfunc(taskid, casetag, provider)
+ return []
+
+ def query_summary_simpletable(self, taskid, casetag, provider, ptype):
+ if ptype in ['throughput', 'frameloss']:
+ qfunc = getattr(self, "query_%s_simpletable" % (ptype))
+ return qfunc(taskid, casetag, provider)
+ return []
+
+ def query_bandwidth(self, taskid, casetag, provider, ptype):
+ if ptype in ['throughput', 'frameloss']:
+ qfunc = getattr(self, "query_%s_bandwidth" % (ptype))
+ return qfunc(taskid, casetag, provider)
+ return []
+
+ def query_load(self, taskid, casetag, provider, ptype):
+ if ptype in ['throughput', 'frameloss']:
+ qfunc = getattr(self, "query_%s_load" % (ptype))
+ return qfunc(taskid, casetag, provider)
+ return []
+
+ def query_avglatency(self, taskid, casetag, provider, ptype):
+ if ptype in ['throughput', 'frameloss', 'latency']:
+ qfunc = getattr(self, "query_%s_avg" % (ptype))
+ return qfunc(taskid, casetag, provider)
+ return []
+
+ def query_throughput_provider(self, taskid, casetag, provider):
+ query = self._session.query(TblThroughput).filter(and_(TblTestList.CaseTag == casetag,
+ TblTestList.Provider == provider,
+ TblTestList.TaskID == taskid,
+ TblTestList.TestID == TblThroughput.TestID))
+ return query.all()
+
+ def query_frameloss_provider(self, taskid, casetag, provider):
+ query = self._session.query(TblFrameloss).filter(and_(TblTestList.CaseTag == casetag,
+ TblTestList.Provider == provider,
+ TblTestList.TaskID == taskid,
+ TblTestList.TestID == TblFrameloss.TestID))
+ return query.all()
+
+ def query_latency_provider(self, taskid, casetag, provider):
+ query = self._session.query(TblLatency).filter(and_(TblTestList.CaseTag == casetag,
+ TblTestList.Provider == provider,
+ TblTestList.TaskID == taskid,
+ TblTestList.TestID == TblLatency.TestID))
+ return query.all()
+
+ def query_case_type_count(self, taskid, casetag, ptype):
+ query = self._session.query(TblTestList).filter(and_(TblTestList.CaseTag == casetag,
+ TblTestList.Type == ptype, TblTestList.TaskID == taskid))
+
+ return query.count()
+
+ def query_case_provider_count(self, taskid, casetag, provider):
+ query = self._session.query(TblTestList).filter(and_(TblTestList.CaseTag == casetag,
+ TblTestList.Provider == provider,
+ TblTestList.TaskID == taskid))
+ return query.count()
+
+ def query_case_type_provider_count(self, taskid, casetag, provider, ptype):
+ query = self._session.query(TblTestList).filter(and_(TblTestList.CaseTag == casetag,
+ TblTestList.Type == ptype,
+ TblTestList.Provider == provider,
+ TblTestList.TaskID == taskid))
+
+ return query.count()
+
+ def query_exten_info(self, taskid):
+ query = self._session.query(TblEXTInfo.EXTName,
+ TblEXTInfo.EXTContent,
+ TblEXTInfo.Description).filter(TblEXTInfo.TaskID == taskid)
+ return query.all()
+
+
+def unit_test():
+ import time
+ dbase = DbManage()
+
+ taskid = dbase.create_task("test", str(time.ctime()), "this is a unit test")
+ dbase.add_host_2task(taskid, "hosta", "hw82576", "xxx", "x", "82599", "ubuntu")
+ dbase.add_extent_2task(taskid, "CETH", "driver", "version 2.0")
+ dbase.add_extent_2task(taskid, "EVS", "switch", "version 3.0")
+
+ testid = dbase.add_test_2task(taskid, "Tn-1", 'udp', "rdp", "throughput", "netperf")
+ data = {
+ '64': {
+ 'OfferedLoad': 2,
+ 'PercentLoss': 3,
+ 'Bandwidth': 4,
+ 'MinimumLatency': 5,
+ 'MaximumLatency': 6,
+ 'AverageLatency': 7,
+ 'TxFrameCount': 8,
+ 'RxFrameCount': 9,
+ 'Duration': 10,
+ 'CPU': 11,
+ 'MppspGhz': 12,
+ }
+ }
+ dbase.add_data_2test(testid, data)
+
+ testid = dbase.add_test_2task(taskid, "Tn-1", 'udp', "rdp", "frameloss", "netperf")
+ data = {
+ '64': {
+ 'OfferedLoad': 2,
+ 'PercentLoss': 3,
+ 'Bandwidth': 4,
+ 'MinimumLatency': 5,
+ 'MaximumLatency': 6,
+ 'AverageLatency': 7,
+ 'TxFrameCount': 8,
+ 'RxFrameCount': 9,
+ 'Duration': 10,
+ 'CPU': 11,
+ 'MppspGhz': 12,
+ }
+ }
+ dbase.add_data_2test(testid, data)
+
+ testid = dbase.add_test_2task(taskid, "Tn-1", 'udp', "rdp", "latency", "netperf")
+ data = {
+ 64: {'MaximumLatency': 0.0, 'AverageLatency': 0.0, 'MinimumLatency': 0.0, 'OfferedLoad': 0.0},
+ 128: {'MaximumLatency': 0.0, 'AverageLatency': 0.0, 'MinimumLatency': 0.0, 'OfferedLoad': 0.0},
+ 512: {'MaximumLatency': 0.0, 'AverageLatency': 0.0, 'MinimumLatency': 0.0, 'OfferedLoad': 0.0},
+ 1024: {'MaximumLatency': 0.0, 'AverageLatency': 0.0, 'MinimumLatency': 0.0, 'OfferedLoad': 0.0}
+ }
+ dbase.add_data_2test(testid, data)
+
+
+if __name__ == '__main__':
+ unit_test()
Code Review / bottlenecks.git / blobdiff