1 ##############################################################################
2 # Copyright (c) 2015 Huawei Technologies Co.,Ltd and others.
4 # All rights reserved. This program and the accompanying materials
5 # are made available under the terms of the Apache License, Version 2.0
6 # which accompanies this distribution, and is available at
7 # http://www.apache.org/licenses/LICENSE-2.0
8 ##############################################################################
13 from sqlalchemy import create_engine
14 from sqlalchemy.orm import sessionmaker
15 from sqlalchemy import and_
16 import vstf.controller.database.tables as table
17 from vstf.controller.database import constants as const
19 LOG = logging.getLogger(__name__)
22 @event.listens_for(Engine, "before_cursor_execute")
23 def before_cursor_execute(conn, cursor, statement,
24 parameters, context, executemany):
25 conn.info.setdefault('query_start_time', []).append(time.time())
26 logging.debug("Start Query: %s", statement)
27 @event.listens_for(Engine, "after_cursor_execute")
28 def after_cursor_execute(conn, cursor, statement,
29 parameters, context, executemany):
30 total = time.time() - conn.info['query_start_time'].pop(-1)
31 logging.debug("Query Complete!")
32 logging.debug("Total Time: %f", total)"""
35 class DbManage(object):
37 def __init__(self, db_name=const.DBPATH):
38 db_exists = os.path.exists(db_name)
40 self._engine = create_engine('sqlite:///%s' % db_name, echo=False)
41 db_session = sessionmaker(bind=self._engine)
42 self._session = db_session()
43 except Exception as e:
46 # if the db is new , create all tables and init static tables
52 self._engine.close_all()
54 def create_tables(self):
55 table.Base.metadata.create_all(self._engine)
56 self._session.commit()
58 def drop_tables(self):
59 table.Base.metadata.drop_all(self._engine)
60 self._session.commit()
62 def init_tables(self):
64 self.init_scenario_table()
65 self._session.commit()
67 def init_scenario_table(self):
69 for values in const.SCENARIO_INFO_LIST:
70 item = table.TblScenarioInfo(ScenarioName=values[0],
72 Description=values[2])
74 self._session.add_all(items)
76 # Single TblCaseInfo API
77 def init_casetable(self):
79 for values in const.CASE_INFO_LIST:
80 item = table.TblCaseInfo(CaseTag=values[0],
81 ScenarioName=values[1],
84 Description=values[4],
86 Directiontag=values[6],
89 self._session.add_all(items)
91 def query_caseinfo(self):
92 query = self._session.query(table.TblCaseInfo.ScenarioName,
93 table.TblCaseInfo.CaseTag,
94 table.TblCaseInfo.CaseName,
95 table.TblCaseInfo.Direction,
96 table.TblCaseInfo.Configure)
99 def query_case(self, casetag):
100 query = self._session.query(table.TblCaseInfo.ScenarioName,
101 table.TblCaseInfo.Directiontag)
104 # Single TblTaskList API
105 def get_last_taskid(self):
106 query = self._session.query(table.TblTaskList.TaskID)
108 return query.all()[-1][0]
112 def query_tasklist(self):
113 query = self._session.query(table.TblTaskList)
116 def query_taskdate(self, taskid):
117 query = self._session.query(table.TblTaskList.Date).filter(and_(
118 table.TblTaskList.TaskID == taskid))
121 result += query.first()[0]
124 def query_taskname(self, taskid):
125 query = self._session.query(table.TblTaskList.TaskName).filter(and_(
126 table.TblTaskList.TaskID == taskid))
129 result += query.first()[0]
132 def create_task(self, name, date, desc):
134 item = table.TblTaskList(name, date, desc)
135 self._session.add(item)
136 self._session.commit()
140 return self.get_last_taskid()
142 # Single TblHostInfo API
143 def add_host_2task(self, taskid, name, machine, cpu, men, nic, os):
144 """All var except task must be string"""
145 item = table.TblHostInfo(taskid, name, machine, cpu, men, nic, os)
147 self._session.add(item)
148 self._session.commit()
150 def query_task_host_list(self, taskid):
151 query = self._session.query(table.TblHostInfo.HostName,
152 table.TblHostInfo.Server,
153 table.TblHostInfo.CPU,
154 table.TblHostInfo.MEM,
155 table.TblHostInfo.NIC,
156 table.TblHostInfo.OS).filter(
157 table.TblHostInfo.TaskID == taskid)
160 # Single TblTestList API
161 def get_last_testid(self):
162 query = self._session.query(table.TblTestList.TestID)
165 return query.all()[-1][0]
179 item = table.TblTestList(
180 task, case, protocol, typ, switch, provider, tool)
181 self._session.add(item)
182 self._session.commit()
186 return self.get_last_testid()
188 def get_test_type(self, testid):
189 query = self._session.query(table.TblTestList.Type).filter(
190 table.TblTestList.TestID == testid)
193 def add_extent_2task(self, task, name, content, description):
194 item = table.TblEXTInfo(task, name, content, description)
195 self._session.add(item)
196 self._session.commit()
198 def add_data_2test(self, testid, data):
200 :data example {'64':{
215 ptype = self.get_test_type(testid)
217 'throughput': table.TblThroughput,
218 'frameloss': table.TblFrameloss,
219 'latency': table.TblLatency
222 if ptype and ptype[0] not in instance_map:
223 print "cant find this test(id=%d)" % (testid)
226 test_table_instance = instance_map[ptype[0]]
227 for pktlen in data.iterkeys():
228 args = data.get(pktlen)
229 query = self._session.query(test_table_instance).filter(and_(
230 test_table_instance.TestID == testid,
231 test_table_instance.AvgFrameSize == pktlen))
234 for key, value in data.items():
235 if key in test_table_instance.__dict__:
236 data_dict[test_table_instance.__dict__[key]] = value
237 query.update(data_dict)
240 tester = test_table_instance(testid, pktlen, **args)
241 self._session.add(tester)
242 self._session.commit()
244 def query_tasks(self):
246 ret = self._session.query(table.TblTaskList)
248 for tmp in ret.all():
250 [tmp.TaskID, tmp.TaskName, tmp.Date, tmp.EXTInfo])
253 def query_all_task_id(self):
254 query = self._session.query(table.TblTaskList.TaskID)
260 def get_caseinfo(self):
261 query = self._session.query(table.TblCaseInfo.ScenarioName,
262 table.TblCaseInfo.CaseTag,
263 table.TblCaseInfo.CaseName,
264 table.TblCaseInfo.Direction,
265 table.TblCaseInfo.Configure)
268 def query_scenario(self, casetag):
269 query = self._session.query(
270 table.TblCaseInfo.ScenarioName).filter(
271 table.TblCaseInfo.CaseTag == casetag)
273 if query and query.first():
274 ret = query.first()[0]
277 def query_casefigure(self, casetag, tools):
278 query = self._session.query(table.TblCaseInfo.FigurePath).filter(and_(
279 table.TblCaseInfo.CaseTag == casetag))
282 result += query.first()[0]
284 result += tools + '/' + casetag + '.jpg'
287 def query_casename(self, casetag):
288 query = self._session.query(table.TblCaseInfo.CaseName).filter(and_(
289 table.TblCaseInfo.CaseTag == casetag))
292 result += query.first()[0]
295 # Single TblTestList API
297 def query_caselist(self, taskid, scenario):
298 query = self._session.query(
299 table.TblTestList.CaseTag).filter(
301 table.TblTestList.CaseTag == table.TblCaseInfo.CaseTag,
302 table.TblCaseInfo.ScenarioName == scenario,
303 table.TblTestList.TaskID == taskid)).group_by(
304 table.TblCaseInfo.CaseTag)
307 def query_testlist(self, taskid, scenario):
312 query = self._session.query(table.TblTestList).filter(and_(
313 table.TblTestList.CaseTag == table.TblCaseInfo.CaseTag,
314 table.TblCaseInfo.ScenarioName == scenario,
315 table.TblTestList.TaskID == taskid))
318 def query_casetool(self, taskid, casetag, provider, ptype):
319 query = self._session.query(table.TblTestList.Tools).filter(and_(
320 table.TblTestList.TaskID == taskid,
321 table.TblTestList.CaseTag == casetag,
322 table.TblTestList.Provider == provider,
323 table.TblTestList.Type == ptype))
326 def query_casetools(self, taskid, casetag):
327 query = self._session.query(
328 table.TblTestList.Tools).filter(
330 table.TblTestList.CaseTag == casetag,
331 table.TblTestList.TaskID == taskid)).group_by(
332 table.TblTestList.Tools)
335 def query_scenariolist(self, taskid):
336 query = self._session.query(
337 table.TblCaseInfo.ScenarioName).filter(
339 table.TblTestList.CaseTag == table.TblCaseInfo.CaseTag,
340 table.TblTestList.TaskID == taskid)).group_by(
341 table.TblCaseInfo.ScenarioName)
344 def query_throughput_load(self, taskid, casetag, provider):
346 query = self._session.query(
347 table.TblThroughput.AvgFrameSize,
348 table.TblThroughput.OfferedLoad).filter(
350 table.TblTestList.TaskID == taskid,
351 table.TblTestList.CaseTag == casetag,
352 table.TblTestList.Provider == provider,
353 table.TblTestList.Type == ptype,
354 table.TblTestList.TestID == table.TblThroughput.TestID))
357 def query_throughput_bandwidth(self, taskid, casetag, provider):
359 query = self._session.query(
360 table.TblThroughput.AvgFrameSize,
361 table.TblThroughput.Bandwidth).filter(
363 table.TblTestList.TaskID == taskid,
364 table.TblTestList.CaseTag == casetag,
365 table.TblTestList.Provider == provider,
366 table.TblTestList.Type == ptype,
367 table.TblTestList.TestID == table.TblThroughput.TestID))
370 def query_throughput_table(self, taskid, casetag, provider):
372 query = self._session.query(
373 table.TblThroughput.AvgFrameSize,
374 table.TblThroughput.Bandwidth,
375 table.TblThroughput.OfferedLoad,
376 table.TblThroughput.CPU,
377 table.TblThroughput.MppspGhz,
378 table.TblThroughput.MinimumLatency,
379 table.TblThroughput.MaximumLatency,
380 table.TblThroughput.AverageLatency,
383 table.TblTestList.TaskID == taskid,
384 table.TblTestList.CaseTag == casetag,
385 table.TblTestList.Provider == provider,
386 table.TblTestList.Type == ptype,
387 table.TblTestList.TestID == table.TblThroughput.TestID))
390 def query_throughput_simpletable(self, taskid, casetag, provider):
392 query = self._session.query(
393 table.TblThroughput.AvgFrameSize,
394 table.TblThroughput.Bandwidth,
395 table.TblThroughput.OfferedLoad,
396 table.TblThroughput.CPU,
397 table.TblThroughput.MppspGhz,
398 table.TblThroughput.AverageLatency,
401 table.TblTestList.TaskID == taskid,
402 table.TblTestList.CaseTag == casetag,
403 table.TblTestList.Provider == provider,
404 table.TblTestList.Type == ptype,
405 table.TblTestList.TestID == table.TblThroughput.TestID))
408 def query_testdata(self, testid, ptype):
409 obj = getattr(table, "Tbl%s" % (ptype.title()))
410 query = self._session.query(obj).filter(obj.TestID == testid)
413 def query_throughput_avg(self, taskid, casetag, provider):
415 query = self._session.query(
416 table.TblThroughput.AvgFrameSize,
417 table.TblThroughput.AverageLatency).filter(
419 table.TblTestList.TaskID == taskid,
420 table.TblTestList.CaseTag == casetag,
421 table.TblTestList.Provider == provider,
422 table.TblTestList.Type == ptype,
423 table.TblTestList.TestID == table.TblThroughput.TestID))
426 def query_frameloss_bandwidth(self, taskid, casetag, provider):
428 query = self._session.query(
429 table.TblFrameloss.AvgFrameSize,
430 table.TblFrameloss.Bandwidth).filter(
432 table.TblTestList.TaskID == taskid,
433 table.TblTestList.CaseTag == casetag,
434 table.TblTestList.Provider == provider,
435 table.TblTestList.Type == ptype,
436 table.TblTestList.TestID == table.TblFrameloss.TestID))
439 def query_frameloss_load(self, taskid, casetag, provider):
441 query = self._session.query(
442 table.TblFrameloss.AvgFrameSize,
443 table.TblFrameloss.OfferedLoad).filter(
445 table.TblTestList.TaskID == taskid,
446 table.TblTestList.CaseTag == casetag,
447 table.TblTestList.Provider == provider,
448 table.TblTestList.Type == ptype,
449 table.TblTestList.TestID == table.TblFrameloss.TestID))
452 def query_frameloss_table(self, taskid, casetag, provider):
454 query = self._session.query(
455 table.TblFrameloss.AvgFrameSize,
456 table.TblFrameloss.Bandwidth,
457 table.TblFrameloss.OfferedLoad,
458 table.TblFrameloss.CPU,
459 table.TblFrameloss.MppspGhz,
460 table.TblFrameloss.MinimumLatency,
461 table.TblFrameloss.MaximumLatency,
462 table.TblFrameloss.AverageLatency).filter(
464 table.TblTestList.TaskID == taskid,
465 table.TblTestList.CaseTag == casetag,
466 table.TblTestList.Provider == provider,
467 table.TblTestList.Type == ptype,
468 table.TblTestList.TestID == table.TblFrameloss.TestID))
471 def query_frameloss_simpletable(self, taskid, casetag, provider):
473 query = self._session.query(
474 table.TblFrameloss.AvgFrameSize,
475 table.TblFrameloss.Bandwidth,
476 table.TblFrameloss.OfferedLoad,
477 table.TblFrameloss.CPU,
478 table.TblFrameloss.MppspGhz,
479 table.TblFrameloss.AverageLatency).filter(
481 table.TblTestList.TaskID == taskid,
482 table.TblTestList.CaseTag == casetag,
483 table.TblTestList.Provider == provider,
484 table.TblTestList.Type == ptype,
485 table.TblTestList.TestID == table.TblFrameloss.TestID))
488 def query_frameloss_avg(self, taskid, casetag, provider):
490 query = self._session.query(
491 table.TblFrameloss.AvgFrameSize,
492 table.TblFrameloss.AverageLatency).filter(
494 table.TblTestList.TaskID == taskid,
495 table.TblTestList.CaseTag == casetag,
496 table.TblTestList.Provider == provider,
497 table.TblTestList.Type == ptype,
498 table.TblTestList.TestID == table.TblFrameloss.TestID))
501 def query_latency_avg(self, taskid, casetag, provider):
503 query = self._session.query(
504 table.TblLatency.AvgFrameSize,
505 table.TblLatency.AverageLatency).filter(
507 table.TblTestList.TaskID == taskid,
508 table.TblTestList.CaseTag == casetag,
509 table.TblTestList.Provider == provider,
510 table.TblTestList.Type == ptype,
511 table.TblTestList.TestID == table.TblLatency.TestID))
514 def query_summary_table(self, taskid, casetag, provider, ptype):
515 if ptype in ['throughput', 'frameloss']:
516 qfunc = getattr(self, "query_%s_table" % (ptype))
517 return qfunc(taskid, casetag, provider)
520 def query_summary_simpletable(self, taskid, casetag, provider, ptype):
521 if ptype in ['throughput', 'frameloss']:
522 qfunc = getattr(self, "query_%s_simpletable" % (ptype))
523 return qfunc(taskid, casetag, provider)
526 def query_bandwidth(self, taskid, casetag, provider, ptype):
527 if ptype in ['throughput', 'frameloss']:
528 qfunc = getattr(self, "query_%s_bandwidth" % (ptype))
529 return qfunc(taskid, casetag, provider)
532 def query_load(self, taskid, casetag, provider, ptype):
533 if ptype in ['throughput', 'frameloss']:
534 qfunc = getattr(self, "query_%s_load" % (ptype))
535 return qfunc(taskid, casetag, provider)
538 def query_avglatency(self, taskid, casetag, provider, ptype):
539 if ptype in ['throughput', 'frameloss', 'latency']:
540 qfunc = getattr(self, "query_%s_avg" % (ptype))
541 return qfunc(taskid, casetag, provider)
544 def query_throughput_provider(self, taskid, casetag, provider):
545 query = self._session.query(
546 table.TblThroughput).filter(
548 table.TblTestList.CaseTag == casetag,
549 table.TblTestList.Provider == provider,
550 table.TblTestList.TaskID == taskid,
551 table.TblTestList.TestID == table.TblThroughput.TestID))
554 def query_frameloss_provider(self, taskid, casetag, provider):
555 query = self._session.query(
556 table.TblFrameloss).filter(
558 table.TblTestList.CaseTag == casetag,
559 table.TblTestList.Provider == provider,
560 table.TblTestList.TaskID == taskid,
561 table.TblTestList.TestID == table.TblFrameloss.TestID))
564 def query_latency_provider(self, taskid, casetag, provider):
565 query = self._session.query(
566 table.TblLatency).filter(
568 table.TblTestList.CaseTag == casetag,
569 table.TblTestList.Provider == provider,
570 table.TblTestList.TaskID == taskid,
571 table.TblTestList.TestID == table.TblLatency.TestID))
574 def query_case_type_count(self, taskid, casetag, ptype):
575 query = self._session.query(
576 table.TblTestList).filter(
578 table.TblTestList.CaseTag == casetag,
579 table.TblTestList.Type == ptype,
580 table.TblTestList.TaskID == taskid))
584 def query_case_provider_count(self, taskid, casetag, provider):
585 query = self._session.query(
586 table.TblTestList).filter(
588 table.TblTestList.CaseTag == casetag,
589 table.TblTestList.Provider == provider,
590 table.TblTestList.TaskID == taskid))
593 def query_case_type_provider_count(self, taskid, casetag, provider, ptype):
594 query = self._session.query(
595 table.TblTestList).filter(
597 table.TblTestList.CaseTag == casetag,
598 table.TblTestList.Type == ptype,
599 table.TblTestList.Provider == provider,
600 table.TblTestList.TaskID == taskid))
604 def query_exten_info(self, taskid):
605 query = self._session.query(
606 table.TblEXTInfo.EXTName,
607 table.TblEXTInfo.EXTContent,
608 table.TblEXTInfo.Description).filter(
609 table.TblEXTInfo.TaskID == taskid)
617 taskid = dbase.create_task("test", str(
618 time.ctime()), "this is a unit test")
619 dbase.add_host_2task(
627 dbase.add_extent_2task(taskid, "CETH", "driver", "version 2.0")
628 dbase.add_extent_2task(taskid, "EVS", "switch", "version 3.0")
630 testid = dbase.add_test_2task(
653 dbase.add_data_2test(testid, data)
655 testid = dbase.add_test_2task(
678 dbase.add_data_2test(testid, data)
680 testid = dbase.add_test_2task(
690 'MaximumLatency': 0.0,
691 'AverageLatency': 0.0,
692 'MinimumLatency': 0.0,
695 'MaximumLatency': 0.0,
696 'AverageLatency': 0.0,
697 'MinimumLatency': 0.0,
700 'MaximumLatency': 0.0,
701 'AverageLatency': 0.0,
702 'MinimumLatency': 0.0,
705 'MaximumLatency': 0.0,
706 'AverageLatency': 0.0,
707 'MinimumLatency': 0.0,
709 dbase.add_data_2test(testid, data)
710 query = dbase.query_testlist(1, "Tn")
714 query = dbase.query_testdata(1, "throughput")
717 if __name__ == '__main__':