vstf/vstf/controller/database/dbinterface.py

   1 #!/usr/bin/python
   2 # -*- coding: utf8 -*-
   3 # author: wly
   4 # date: 2015-07-29
   5 # see license for license details
   6 __version__ = ''' '''
   7 import os
   8 import logging
   9
  10 from sqlalchemy import create_engine
  11 from sqlalchemy.orm import sessionmaker
  12 from sqlalchemy import and_
  13 from vstf.controller.database.tables import *
  14
  15 LOG = logging.getLogger(__name__)
  16
  17 """
  18 @event.listens_for(Engine, "before_cursor_execute")
  19 def before_cursor_execute(conn, cursor, statement,
  20     parameters, context, executemany):
  21     conn.info.setdefault('query_start_time', []).append(time.time())
  22     logging.debug("Start Query: %s", statement)
  23 @event.listens_for(Engine, "after_cursor_execute")
  24 def after_cursor_execute(conn, cursor, statement,
  25     parameters, context, executemany):
  26     total = time.time() - conn.info['query_start_time'].pop(-1)
  27     logging.debug("Query Complete!")
  28     logging.debug("Total Time: %f", total)"""
  29
  30
  31 class DbManage(object):
  32     def __init__(self, db_name=const.DBPATH):
  33         db_exists = os.path.exists(db_name)
  34         try:
  35             self._engine = create_engine('sqlite:///%s' % db_name, echo=False)
  36             db_session = sessionmaker(bind=self._engine)
  37             self._session = db_session()
  38         except Exception as e:
  39             raise e
  40
  41         # if the db is new , cleate all tables and init static tables
  42         if not db_exists:
  43             self.create_tables()
  44             self.init_tables()
  45
  46     def __delete__(self):
  47         self._engine.close_all()
  48
  49     def create_tables(self):
  50         Base.metadata.create_all(self._engine)
  51         self._session.commit()
  52
  53     def drop_tables(self):
  54         Base.metadata.drop_all(self._engine)
  55         self._session.commit()
  56
  57     def init_tables(self):
  58         self.init_casetable()
  59         self.init_scenario_table()
  60         self._session.commit()
  61
  62     def init_scenario_table(self):
  63         items = []
  64         for values in const.SCENARIO_INFO_LIST:
  65             item = TblScenarioInfo(ScenarioName=values[0],
  66                                    FigurePath=values[1],
  67                                    Description=values[2])
  68             items.append(item)
  69         self._session.add_all(items)
  70
  71     # Single TblCaseInfo API
  72     def init_casetable(self):
  73         items = []
  74         for values in const.CASE_INFO_LIST:
  75             item = TblCaseInfo(CaseTag=values[0],
  76                                ScenarioName=values[1],
  77                                CaseName=values[2],
  78                                FigurePath=values[3],
  79                                Description=values[4],
  80                                Direction=values[5],
  81                                Directiontag=values[6],
  82                                Configure=values[7])
  83             items.append(item)
  84         self._session.add_all(items)
  85
  86     def query_caseinfo(self):
  87         query = self._session.query(TblCaseInfo.ScenarioName,
  88                                     TblCaseInfo.CaseTag,
  89                                     TblCaseInfo.CaseName,
  90                                     TblCaseInfo.Direction,
  91                                     TblCaseInfo.Configure)
  92         return query.all()
  93
  94     def query_case(self, casetag):
  95         query = self._session.query(TblCaseInfo.ScenarioName,
  96                                     TblCaseInfo.Directiontag)
  97         return query.first()
  98
  99     # Single TblTaskList API
 100     def get_last_taskid(self):
 101         query = self._session.query(TblTaskList.TaskID)
 102         if query:
 103             return query.all()[-1][0]
 104         else:
 105             return 0
 106
 107     def query_tasklist(self):
 108         query = self._session.query(TblTaskList)
 109         return query.all()
 110
 111     def query_taskdate(self, taskid):
 112         query = self._session.query(TblTaskList.Date).filter(and_(
 113             TblTaskList.TaskID == taskid))
 114         result = ""
 115         if query:
 116             result += query.first()[0]
 117         return result
 118
 119     def query_taskname(self, taskid):
 120         query = self._session.query(TblTaskList.TaskName).filter(and_(
 121             TblTaskList.TaskID == taskid))
 122         result = ""
 123         if query:
 124             result += query.first()[0]
 125         return result
 126
 127     def create_task(self, name, date, desc):
 128         try:
 129             item = TblTaskList(name, date, desc)
 130             self._session.add(item)
 131             self._session.commit()
 132         except Exception:
 133             return -1
 134
 135         return self.get_last_taskid()
 136
 137     # Single TblHostInfo API
 138     def add_host_2task(self, taskid, name, machine, cpu, men, nic, os):
 139         """All var except task must be string"""
 140         item = TblHostInfo(taskid, name, machine, cpu, men, nic, os)
 141
 142         self._session.add(item)
 143         self._session.commit()
 144
 145     def query_task_host_list(self, taskid):
 146         query = self._session.query(TblHostInfo.HostName,
 147                                     TblHostInfo.Server,
 148                                     TblHostInfo.CPU,
 149                                     TblHostInfo.MEM,
 150                                     TblHostInfo.NIC,
 151                                     TblHostInfo.OS).filter(
 152             TblHostInfo.TaskID == taskid)
 153         return query.all()
 154
 155     # Single TblTestList API
 156     def get_last_testid(self):
 157         query = self._session.query(TblTestList.TestID)
 158         if query:
 159             return query.all()[-1][0]
 160         else:
 161             return 0
 162
 163     def add_test_2task(self, task, case, protocol, provider, typ, tool):
 164         try:
 165             item = TblTestList(task, case, protocol, provider, typ, tool)
 166             self._session.add(item)
 167             self._session.commit()
 168         except Exception:
 169             return -1
 170
 171         return self.get_last_testid()
 172
 173     def get_test_type(self, testid):
 174         query = self._session.query(TblTestList.Type).filter(
 175             TblTestList.TestID == testid)
 176         return query.first()
 177
 178     def add_extent_2task(self, task, name, content, description):
 179         item = TblEXTInfo(task, name, content, description)
 180         self._session.add(item)
 181         self._session.commit()
 182
 183     def add_data_2test(self, testid, data):
 184         """
 185         :data example {'64':{
 186                             'AvgFrameSize':0
 187                             'OfferedLoad':0
 188                             'PercentLoss':0
 189                             'Bandwidth':0
 190                             'MinimumLatency':0
 191                             'MaximumLatency':0
 192                             'AverageLatency':0
 193                             'TxFrameCount':0
 194                             'RxFrameCount':0
 195                             'Duration':0
 196                             'CPU':0
 197                             'MppspGhz':0
 198                             }}
 199         """
 200         ptype = self.get_test_type(testid)
 201         instance_map = {
 202             'throughput': TblThroughput,
 203             'frameloss': TblFrameloss,
 204             'latency': TblLatency
 205         }
 206
 207         if ptype and ptype[0] not in instance_map:
 208             print "cant find this test(id=%d)" % (testid)
 209             return False
 210
 211         test_table_instance = instance_map[ptype[0]]
 212         for pktlen in data.iterkeys():
 213             args = data.get(pktlen)
 214             query = self._session.query(test_table_instance).filter(and_(
 215                 test_table_instance.TestID == testid,
 216                 test_table_instance.AvgFrameSize == pktlen))
 217             if query.all():
 218                 data_dict = {}
 219                 for key, value in data.items():
 220                     if key in test_table_instance.__dict__:
 221                         data_dict[test_table_instance.__dict__[key]] = value
 222                 query.update(data_dict)
 223             else:
 224                 print args
 225                 tester = test_table_instance(testid, pktlen, **args)
 226                 self._session.add(tester)
 227         self._session.commit()
 228
 229     def query_tasks(self):
 230         result = []
 231         ret = self._session.query(TblTaskList)
 232         if ret:
 233             for tmp in ret.all():
 234                 result.append([tmp.TaskID, tmp.TaskName, tmp.Date, tmp.EXTInfo])
 235         return result
 236
 237     def query_all_task_id(self):
 238         query = self._session.query(TblTaskList.TaskID)
 239         if query:
 240             return query.all()
 241         else:
 242             return []
 243
 244     def get_caseinfo(self):
 245         query = self._session.query(TblCaseInfo.ScenarioName,
 246                                     TblCaseInfo.CaseTag,
 247                                     TblCaseInfo.CaseName,
 248                                     TblCaseInfo.Direction,
 249                                     TblCaseInfo.Configure)
 250         return query.all()
 251
 252     def query_scenario(self, casetag):
 253         query = self._session.query(TblCaseInfo.ScenarioName).filter(TblCaseInfo.CaseTag == casetag)
 254         ret = ""
 255         if query and query.first():
 256             ret = query.first()[0]
 257         return ret
 258
 259     def query_casefigure(self, casetag, tools):
 260         query = self._session.query(TblCaseInfo.FigurePath).filter(and_(
 261             TblCaseInfo.CaseTag == casetag))
 262         result = ""
 263         if query:
 264             result += query.first()[0]
 265         print tools, casetag
 266         result += tools + '/' + casetag + '.jpg'
 267         return result
 268
 269     def query_casename(self, casetag):
 270         query = self._session.query(TblCaseInfo.CaseName).filter(and_(
 271             TblCaseInfo.CaseTag == casetag))
 272         result = ""
 273         if query:
 274             result += query.first()[0]
 275         return result
 276
 277     # Single TblScenarioInfo API
 278
 279     def query_caselist(self, taskid, scenario):
 280         query = self._session.query(TblTestList.CaseTag).filter(and_(
 281             TblTestList.CaseTag == TblCaseInfo.CaseTag,
 282             TblCaseInfo.ScenarioName == scenario,
 283             TblTestList.TaskID == taskid)).group_by(TblCaseInfo.CaseTag)
 284         return query.all()
 285
 286     def query_casetool(self, taskid, casetag, provider, ptype):
 287         query = self._session.query(TblTestList.Tools).filter(and_(
 288             TblTestList.TaskID == taskid,
 289             TblTestList.CaseTag == casetag,
 290             TblTestList.Provider == provider,
 291             TblTestList.Type == ptype))
 292         return query.all()
 293
 294     def query_casetools(self, taskid, casetag):
 295         query = self._session.query(TblTestList.Tools).filter(and_(
 296             TblTestList.CaseTag == casetag,
 297             TblTestList.TaskID == taskid)).group_by(TblTestList.Tools)
 298         return query.all()
 299
 300     def query_scenariolist(self, taskid):
 301         query = self._session.query(TblCaseInfo.ScenarioName).filter(and_(
 302             TblTestList.CaseTag == TblCaseInfo.CaseTag,
 303             TblTestList.TaskID == taskid)).group_by(TblCaseInfo.ScenarioName)
 304         return query.all()
 305
 306     def query_throughput_load(self, taskid, casetag, provider):
 307         ptype = 'throughput'
 308         query = self._session.query(TblThroughput.AvgFrameSize, TblThroughput.OfferedLoad).filter(and_(
 309             TblTestList.TaskID == taskid,
 310             TblTestList.CaseTag == casetag,
 311             TblTestList.Provider == provider, TblTestList.Type == ptype,
 312             TblTestList.TestID == TblThroughput.TestID))
 313         return query.all()
 314
 315     def query_throughput_bandwidth(self, taskid, casetag, provider):
 316         ptype = 'throughput'
 317         query = self._session.query(TblThroughput.AvgFrameSize, TblThroughput.Bandwidth).filter(and_(
 318             TblTestList.TaskID == taskid,
 319             TblTestList.CaseTag == casetag,
 320             TblTestList.Provider == provider, TblTestList.Type == ptype,
 321             TblTestList.TestID == TblThroughput.TestID))
 322         return query.all()
 323
 324     def query_throughput_table(self, taskid, casetag, provider):
 325         ptype = 'throughput'
 326         query = self._session.query(TblThroughput.AvgFrameSize,
 327                                     TblThroughput.Bandwidth,
 328                                     TblThroughput.OfferedLoad,
 329                                     TblThroughput.CPU,
 330                                     TblThroughput.MppspGhz,
 331                                     TblThroughput.MinimumLatency,
 332                                     TblThroughput.MaximumLatency,
 333                                     TblThroughput.AverageLatency,
 334                                     ).filter(and_(
 335             TblTestList.TaskID == taskid,
 336             TblTestList.CaseTag == casetag,
 337             TblTestList.Provider == provider, TblTestList.Type == ptype,
 338             TblTestList.TestID == TblThroughput.TestID))
 339         return query.all()
 340
 341     def query_throughput_simpletable(self, taskid, casetag, provider):
 342         ptype = 'throughput'
 343         query = self._session.query(TblThroughput.AvgFrameSize,
 344                                     TblThroughput.Bandwidth,
 345                                     TblThroughput.OfferedLoad,
 346                                     TblThroughput.CPU,
 347                                     TblThroughput.MppspGhz,
 348                                     TblThroughput.AverageLatency,
 349                                     ).filter(and_(
 350             TblTestList.TaskID == taskid,
 351             TblTestList.CaseTag == casetag,
 352             TblTestList.Provider == provider, TblTestList.Type == ptype,
 353             TblTestList.TestID == TblThroughput.TestID))
 354         return query.all()
 355
 356     def query_throughput_avg(self, taskid, casetag, provider):
 357         ptype = 'throughput'
 358         query = self._session.query(TblThroughput.AvgFrameSize, TblThroughput.AverageLatency).filter(and_(
 359             TblTestList.TaskID == taskid,
 360             TblTestList.CaseTag == casetag,
 361             TblTestList.Provider == provider, TblTestList.Type == ptype,
 362             TblTestList.TestID == TblThroughput.TestID))
 363         return query.all()
 364
 365     def query_frameloss_bandwidth(self, taskid, casetag, provider):
 366         ptype = 'frameloss'
 367         query = self._session.query(TblFrameloss.AvgFrameSize, TblFrameloss.Bandwidth).filter(and_(
 368             TblTestList.TaskID == taskid,
 369             TblTestList.CaseTag == casetag,
 370             TblTestList.Provider == provider, TblTestList.Type == ptype,
 371             TblTestList.TestID == TblFrameloss.TestID))
 372         return query.all()
 373
 374     def query_frameloss_load(self, taskid, casetag, provider):
 375         ptype = 'frameloss'
 376         query = self._session.query(TblFrameloss.AvgFrameSize, TblFrameloss.OfferedLoad).filter(and_(
 377             TblTestList.TaskID == taskid,
 378             TblTestList.CaseTag == casetag,
 379             TblTestList.Provider == provider, TblTestList.Type == ptype,
 380             TblTestList.TestID == TblFrameloss.TestID))
 381         return query.all()
 382
 383     def query_frameloss_table(self, taskid, casetag, provider):
 384         ptype = 'frameloss'
 385         query = self._session.query(TblFrameloss.AvgFrameSize,
 386                                     TblFrameloss.Bandwidth,
 387                                     TblFrameloss.OfferedLoad,
 388                                     TblFrameloss.CPU,
 389                                     TblFrameloss.MppspGhz,
 390                                     TblFrameloss.MinimumLatency,
 391                                     TblFrameloss.MaximumLatency,
 392                                     TblFrameloss.AverageLatency,
 393                                     ).filter(and_(
 394             TblTestList.TaskID == taskid,
 395             TblTestList.CaseTag == casetag,
 396             TblTestList.Provider == provider, TblTestList.Type == ptype,
 397             TblTestList.TestID == TblFrameloss.TestID))
 398         return query.all()
 399
 400     def query_frameloss_simpletable(self, taskid, casetag, provider):
 401         ptype = 'frameloss'
 402         query = self._session.query(TblFrameloss.AvgFrameSize,
 403                                     TblFrameloss.Bandwidth,
 404                                     TblFrameloss.OfferedLoad,
 405                                     TblFrameloss.CPU,
 406                                     TblFrameloss.MppspGhz,
 407                                     TblFrameloss.AverageLatency,
 408                                     ).filter(and_(
 409             TblTestList.TaskID == taskid,
 410             TblTestList.CaseTag == casetag,
 411             TblTestList.Provider == provider, TblTestList.Type == ptype,
 412             TblTestList.TestID == TblFrameloss.TestID))
 413         return query.all()
 414
 415     def query_frameloss_avg(self, taskid, casetag, provider):
 416         ptype = 'frameloss'
 417         query = self._session.query(TblFrameloss.AvgFrameSize, TblFrameloss.AverageLatency).filter(and_(
 418             TblTestList.TaskID == taskid,
 419             TblTestList.CaseTag == casetag,
 420             TblTestList.Provider == provider, TblTestList.Type == ptype,
 421             TblTestList.TestID == TblFrameloss.TestID))
 422         return query.all()
 423
 424     def query_latency_avg(self, taskid, casetag, provider):
 425         ptype = 'latency'
 426         query = self._session.query(TblLatency.AvgFrameSize, TblLatency.AverageLatency).filter(and_(
 427             TblTestList.TaskID == taskid,
 428             TblTestList.CaseTag == casetag,
 429             TblTestList.Provider == provider, TblTestList.Type == ptype,
 430             TblTestList.TestID == TblLatency.TestID))
 431         return query.all()
 432
 433     def query_summary_table(self, taskid, casetag, provider, ptype):
 434         if ptype in ['throughput', 'frameloss']:
 435             qfunc = getattr(self, "query_%s_table" % (ptype))
 436             return qfunc(taskid, casetag, provider)
 437         return []
 438
 439     def query_summary_simpletable(self, taskid, casetag, provider, ptype):
 440         if ptype in ['throughput', 'frameloss']:
 441             qfunc = getattr(self, "query_%s_simpletable" % (ptype))
 442             return qfunc(taskid, casetag, provider)
 443         return []
 444
 445     def query_bandwidth(self, taskid, casetag, provider, ptype):
 446         if ptype in ['throughput', 'frameloss']:
 447             qfunc = getattr(self, "query_%s_bandwidth" % (ptype))
 448             return qfunc(taskid, casetag, provider)
 449         return []
 450
 451     def query_load(self, taskid, casetag, provider, ptype):
 452         if ptype in ['throughput', 'frameloss']:
 453             qfunc = getattr(self, "query_%s_load" % (ptype))
 454             return qfunc(taskid, casetag, provider)
 455         return []
 456
 457     def query_avglatency(self, taskid, casetag, provider, ptype):
 458         if ptype in ['throughput', 'frameloss', 'latency']:
 459             qfunc = getattr(self, "query_%s_avg" % (ptype))
 460             return qfunc(taskid, casetag, provider)
 461         return []
 462
 463     def query_throughput_provider(self, taskid, casetag, provider):
 464         query = self._session.query(TblThroughput).filter(and_(TblTestList.CaseTag == casetag,
 465                                                                TblTestList.Provider == provider,
 466                                                                TblTestList.TaskID == taskid,
 467                                                                TblTestList.TestID == TblThroughput.TestID))
 468         return query.all()
 469
 470     def query_frameloss_provider(self, taskid, casetag, provider):
 471         query = self._session.query(TblFrameloss).filter(and_(TblTestList.CaseTag == casetag,
 472                                                               TblTestList.Provider == provider,
 473                                                               TblTestList.TaskID == taskid,
 474                                                               TblTestList.TestID == TblFrameloss.TestID))
 475         return query.all()
 476
 477     def query_latency_provider(self, taskid, casetag, provider):
 478         query = self._session.query(TblLatency).filter(and_(TblTestList.CaseTag == casetag,
 479                                                             TblTestList.Provider == provider,
 480                                                             TblTestList.TaskID == taskid,
 481                                                             TblTestList.TestID == TblLatency.TestID))
 482         return query.all()
 483
 484     def query_case_type_count(self, taskid, casetag, ptype):
 485         query = self._session.query(TblTestList).filter(and_(TblTestList.CaseTag == casetag,
 486                                                              TblTestList.Type == ptype, TblTestList.TaskID == taskid))
 487
 488         return query.count()
 489
 490     def query_case_provider_count(self, taskid, casetag, provider):
 491         query = self._session.query(TblTestList).filter(and_(TblTestList.CaseTag == casetag,
 492                                                              TblTestList.Provider == provider,
 493                                                              TblTestList.TaskID == taskid))
 494         return query.count()
 495
 496     def query_case_type_provider_count(self, taskid, casetag, provider, ptype):
 497         query = self._session.query(TblTestList).filter(and_(TblTestList.CaseTag == casetag,
 498                                                              TblTestList.Type == ptype,
 499                                                              TblTestList.Provider == provider,
 500                                                              TblTestList.TaskID == taskid))
 501
 502         return query.count()
 503
 504     def query_exten_info(self, taskid):
 505         query = self._session.query(TblEXTInfo.EXTName,
 506                                     TblEXTInfo.EXTContent,
 507                                     TblEXTInfo.Description).filter(TblEXTInfo.TaskID == taskid)
 508         return query.all()
 509
 510
 511 def unit_test():
 512     import time
 513     dbase = DbManage()
 514
 515     taskid = dbase.create_task("test", str(time.ctime()), "this is a unit test")
 516     dbase.add_host_2task(taskid, "hosta", "hw82576", "xxx", "x", "82599", "ubuntu")
 517     dbase.add_extent_2task(taskid, "CETH", "driver", "version 2.0")
 518     dbase.add_extent_2task(taskid, "EVS", "switch", "version 3.0")
 519
 520     testid = dbase.add_test_2task(taskid, "Tn-1", 'udp', "rdp", "throughput", "netperf")
 521     data = {
 522         '64': {
 523             'OfferedLoad': 2,
 524             'PercentLoss': 3,
 525             'Bandwidth': 4,
 526             'MinimumLatency': 5,
 527             'MaximumLatency': 6,
 528             'AverageLatency': 7,
 529             'TxFrameCount': 8,
 530             'RxFrameCount': 9,
 531             'Duration': 10,
 532             'CPU': 11,
 533             'MppspGhz': 12,
 534         }
 535     }
 536     dbase.add_data_2test(testid, data)
 537
 538     testid = dbase.add_test_2task(taskid, "Tn-1", 'udp', "rdp", "frameloss", "netperf")
 539     data = {
 540         '64': {
 541             'OfferedLoad': 2,
 542             'PercentLoss': 3,
 543             'Bandwidth': 4,
 544             'MinimumLatency': 5,
 545             'MaximumLatency': 6,
 546             'AverageLatency': 7,
 547             'TxFrameCount': 8,
 548             'RxFrameCount': 9,
 549             'Duration': 10,
 550             'CPU': 11,
 551             'MppspGhz': 12,
 552         }
 553     }
 554     dbase.add_data_2test(testid, data)
 555
 556     testid = dbase.add_test_2task(taskid, "Tn-1", 'udp', "rdp", "latency", "netperf")
 557     data = {
 558         64: {'MaximumLatency': 0.0, 'AverageLatency': 0.0, 'MinimumLatency': 0.0, 'OfferedLoad': 0.0},
 559         128: {'MaximumLatency': 0.0, 'AverageLatency': 0.0, 'MinimumLatency': 0.0, 'OfferedLoad': 0.0},
 560         512: {'MaximumLatency': 0.0, 'AverageLatency': 0.0, 'MinimumLatency': 0.0, 'OfferedLoad': 0.0},
 561         1024: {'MaximumLatency': 0.0, 'AverageLatency': 0.0, 'MinimumLatency': 0.0, 'OfferedLoad': 0.0}
 562     }
 563     dbase.add_data_2test(testid, data)
 564
 565
 566 if __name__ == '__main__':
 567     unit_test()