diff options
Diffstat (limited to 'vstf/vstf/controller/database/dbinterface.py')
| -rwxr-xr-x | vstf/vstf/controller/database/dbinterface.py | 567 |
1 files changed, 567 insertions, 0 deletions
diff --git a/vstf/vstf/controller/database/dbinterface.py b/vstf/vstf/controller/database/dbinterface.py new file mode 100755 index 00000000..ae34c861 --- /dev/null +++ b/vstf/vstf/controller/database/dbinterface.py @@ -0,0 +1,567 @@ +#!/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() |