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 ##############################################################################
14 from sqlalchemy import create_engine
15 from sqlalchemy.orm import sessionmaker
16 from sqlalchemy import and_
17 import vstf.controller.database.tables as table
18 from vstf.controller.database import constants as const
20 LOG = logging.getLogger(__name__)
23 @event.listens_for(Engine, "before_cursor_execute")
24 def before_cursor_execute(conn, cursor, statement,
25 parameters, context, executemany):
26 conn.info.setdefault('query_start_time', []).append(time.time())
27 logging.debug("Start Query: %s", statement)
28 @event.listens_for(Engine, "after_cursor_execute")
29 def after_cursor_execute(conn, cursor, statement,
30 parameters, context, executemany):
31 total = time.time() - conn.info['query_start_time'].pop(-1)
32 logging.debug("Query Complete!")
33 logging.debug("Total Time: %f", total)"""
36 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]
169 def add_test_2task(self, task, case, protocol, typ, switch, provider, tool):
171 item = table.TblTestList(task, case, protocol, typ, switch, provider, tool)
172 self._session.add(item)
173 self._session.commit()
177 return self.get_last_testid()
179 def get_test_type(self, testid):
180 query = self._session.query(table.TblTestList.Type).filter(
181 table.TblTestList.TestID == testid)
184 def add_extent_2task(self, task, name, content, description):
185 item = table.TblEXTInfo(task, name, content, description)
186 self._session.add(item)
187 self._session.commit()
189 def add_data_2test(self, testid, data):
191 :data example {'64':{
206 ptype = self.get_test_type(testid)
208 'throughput': table.TblThroughput,
209 'frameloss': table.TblFrameloss,
210 'latency': table.TblLatency
213 if ptype and ptype[0] not in instance_map:
214 print "cant find this test(id=%d)" % (testid)
217 test_table_instance = instance_map[ptype[0]]
218 for pktlen in data.iterkeys():
219 args = data.get(pktlen)
220 query = self._session.query(test_table_instance).filter(and_(
221 test_table_instance.TestID == testid,
222 test_table_instance.AvgFrameSize == pktlen))
225 for key, value in data.items():
226 if key in test_table_instance.__dict__:
227 data_dict[test_table_instance.__dict__[key]] = value
228 query.update(data_dict)
231 tester = test_table_instance(testid, pktlen, **args)
232 self._session.add(tester)
233 self._session.commit()
235 def query_tasks(self):
237 ret = self._session.query(table.TblTaskList)
239 for tmp in ret.all():
240 result.append([tmp.TaskID, tmp.TaskName, tmp.Date, tmp.EXTInfo])
243 def query_all_task_id(self):
244 query = self._session.query(table.TblTaskList.TaskID)
250 def get_caseinfo(self):
251 query = self._session.query(table.TblCaseInfo.ScenarioName,
252 table.TblCaseInfo.CaseTag,
253 table.TblCaseInfo.CaseName,
254 table.TblCaseInfo.Direction,
255 table.TblCaseInfo.Configure)
258 def query_scenario(self, casetag):
259 query = self._session.query(table.TblCaseInfo.ScenarioName).filter(table.TblCaseInfo.CaseTag == casetag)
261 if query and query.first():
262 ret = query.first()[0]
265 def query_casefigure(self, casetag, tools):
266 query = self._session.query(table.TblCaseInfo.FigurePath).filter(and_(
267 table.TblCaseInfo.CaseTag == casetag))
270 result += query.first()[0]
272 result += tools + '/' + casetag + '.jpg'
275 def query_casename(self, casetag):
276 query = self._session.query(table.TblCaseInfo.CaseName).filter(and_(
277 table.TblCaseInfo.CaseTag == casetag))
280 result += query.first()[0]
283 # Single TblTestList API
285 def query_caselist(self, taskid, scenario):
286 query = self._session.query(table.TblTestList.CaseTag).filter(and_(
287 table.TblTestList.CaseTag == table.TblCaseInfo.CaseTag,
288 table.TblCaseInfo.ScenarioName == scenario,
289 table.TblTestList.TaskID == taskid)).group_by(table.TblCaseInfo.CaseTag)
292 def query_testlist(self, taskid, scenario):
297 query = self._session.query(table.TblTestList).filter(and_(
298 table.TblTestList.CaseTag == table.TblCaseInfo.CaseTag,
299 table.TblCaseInfo.ScenarioName == scenario,
300 table.TblTestList.TaskID == taskid))
303 def query_casetool(self, taskid, casetag, provider, ptype):
304 query = self._session.query(table.TblTestList.Tools).filter(and_(
305 table.TblTestList.TaskID == taskid,
306 table.TblTestList.CaseTag == casetag,
307 table.TblTestList.Provider == provider,
308 table.TblTestList.Type == ptype))
311 def query_casetools(self, taskid, casetag):
312 query = self._session.query(table.TblTestList.Tools).filter(and_(
313 table.TblTestList.CaseTag == casetag,
314 table.TblTestList.TaskID == taskid)).group_by(table.TblTestList.Tools)
317 def query_scenariolist(self, taskid):
318 query = self._session.query(table.TblCaseInfo.ScenarioName).filter(and_(
319 table.TblTestList.CaseTag == table.TblCaseInfo.CaseTag,
320 table.TblTestList.TaskID == taskid)).group_by(table.TblCaseInfo.ScenarioName)
323 def query_throughput_load(self, taskid, casetag, provider):
325 query = self._session.query(table.TblThroughput.AvgFrameSize, table.TblThroughput.OfferedLoad).filter(and_(
326 table.TblTestList.TaskID == taskid,
327 table.TblTestList.CaseTag == casetag,
328 table.TblTestList.Provider == provider, table.TblTestList.Type == ptype,
329 table.TblTestList.TestID == table.TblThroughput.TestID))
332 def query_throughput_bandwidth(self, taskid, casetag, provider):
334 query = self._session.query(table.TblThroughput.AvgFrameSize, table.TblThroughput.Bandwidth).filter(and_(
335 table.TblTestList.TaskID == taskid,
336 table.TblTestList.CaseTag == casetag,
337 table.TblTestList.Provider == provider, table.TblTestList.Type == ptype,
338 table.TblTestList.TestID == table.TblThroughput.TestID))
341 def query_throughput_table(self, taskid, casetag, provider):
343 query = self._session.query(table.TblThroughput.AvgFrameSize,
344 table.TblThroughput.Bandwidth,
345 table.TblThroughput.OfferedLoad,
346 table.TblThroughput.CPU,
347 table.TblThroughput.MppspGhz,
348 table.TblThroughput.MinimumLatency,
349 table.TblThroughput.MaximumLatency,
350 table.TblThroughput.AverageLatency,
352 table.TblTestList.TaskID == taskid,
353 table.TblTestList.CaseTag == casetag,
354 table.TblTestList.Provider == provider, table.TblTestList.Type == ptype,
355 table.TblTestList.TestID == table.TblThroughput.TestID))
358 def query_throughput_simpletable(self, taskid, casetag, provider):
360 query = self._session.query(table.TblThroughput.AvgFrameSize,
361 table.TblThroughput.Bandwidth,
362 table.TblThroughput.OfferedLoad,
363 table.TblThroughput.CPU,
364 table.TblThroughput.MppspGhz,
365 table.TblThroughput.AverageLatency,
367 table.TblTestList.TaskID == taskid,
368 table.TblTestList.CaseTag == casetag,
369 table.TblTestList.Provider == provider, table.TblTestList.Type == ptype,
370 table.TblTestList.TestID == table.TblThroughput.TestID))
373 def query_testdata(self, testid, ptype):
374 obj = getattr(table, "Tbl%s" % (ptype.title()))
375 query = self._session.query(obj).filter(obj.TestID == testid)
378 def query_throughput_avg(self, taskid, casetag, provider):
380 query = self._session.query(table.TblThroughput.AvgFrameSize, table.TblThroughput.AverageLatency).filter(and_(
381 table.TblTestList.TaskID == taskid,
382 table.TblTestList.CaseTag == casetag,
383 table.TblTestList.Provider == provider, table.TblTestList.Type == ptype,
384 table.TblTestList.TestID == table.TblThroughput.TestID))
387 def query_frameloss_bandwidth(self, taskid, casetag, provider):
389 query = self._session.query(table.TblFrameloss.AvgFrameSize, table.TblFrameloss.Bandwidth).filter(and_(
390 table.TblTestList.TaskID == taskid,
391 table.TblTestList.CaseTag == casetag,
392 table.TblTestList.Provider == provider, table.TblTestList.Type == ptype,
393 table.TblTestList.TestID == table.TblFrameloss.TestID))
396 def query_frameloss_load(self, taskid, casetag, provider):
398 query = self._session.query(table.TblFrameloss.AvgFrameSize, table.TblFrameloss.OfferedLoad).filter(and_(
399 table.TblTestList.TaskID == taskid,
400 table.TblTestList.CaseTag == casetag,
401 table.TblTestList.Provider == provider, table.TblTestList.Type == ptype,
402 table.TblTestList.TestID == table.TblFrameloss.TestID))
405 def query_frameloss_table(self, taskid, casetag, provider):
407 query = self._session.query(table.TblFrameloss.AvgFrameSize,
408 table.TblFrameloss.Bandwidth,
409 table.TblFrameloss.OfferedLoad,
410 table.TblFrameloss.CPU,
411 table.TblFrameloss.MppspGhz,
412 table.TblFrameloss.MinimumLatency,
413 table.TblFrameloss.MaximumLatency,
414 table.TblFrameloss.AverageLatency
416 table.TblTestList.TaskID == taskid,
417 table.TblTestList.CaseTag == casetag,
418 table.TblTestList.Provider == provider, table.TblTestList.Type == ptype,
419 table.TblTestList.TestID == table.TblFrameloss.TestID))
422 def query_frameloss_simpletable(self, taskid, casetag, provider):
424 query = self._session.query(table.TblFrameloss.AvgFrameSize,
425 table.TblFrameloss.Bandwidth,
426 table.TblFrameloss.OfferedLoad,
427 table.TblFrameloss.CPU,
428 table.TblFrameloss.MppspGhz,
429 table.TblFrameloss.AverageLatency
431 table.TblTestList.TaskID == taskid,
432 table.TblTestList.CaseTag == casetag,
433 table.TblTestList.Provider == provider, table.TblTestList.Type == ptype,
434 table.TblTestList.TestID == table.TblFrameloss.TestID))
437 def query_frameloss_avg(self, taskid, casetag, provider):
439 query = self._session.query(table.TblFrameloss.AvgFrameSize, table.TblFrameloss.AverageLatency).filter(and_(
440 table.TblTestList.TaskID == taskid,
441 table.TblTestList.CaseTag == casetag,
442 table.TblTestList.Provider == provider, table.TblTestList.Type == ptype,
443 table.TblTestList.TestID == table.TblFrameloss.TestID))
446 def query_latency_avg(self, taskid, casetag, provider):
448 query = self._session.query(table.TblLatency.AvgFrameSize, table.TblLatency.AverageLatency).filter(and_(
449 table.TblTestList.TaskID == taskid,
450 table.TblTestList.CaseTag == casetag,
451 table.TblTestList.Provider == provider, table.TblTestList.Type == ptype,
452 table.TblTestList.TestID == table.TblLatency.TestID))
455 def query_summary_table(self, taskid, casetag, provider, ptype):
456 if ptype in ['throughput', 'frameloss']:
457 qfunc = getattr(self, "query_%s_table" % (ptype))
458 return qfunc(taskid, casetag, provider)
461 def query_summary_simpletable(self, taskid, casetag, provider, ptype):
462 if ptype in ['throughput', 'frameloss']:
463 qfunc = getattr(self, "query_%s_simpletable" % (ptype))
464 return qfunc(taskid, casetag, provider)
467 def query_bandwidth(self, taskid, casetag, provider, ptype):
468 if ptype in ['throughput', 'frameloss']:
469 qfunc = getattr(self, "query_%s_bandwidth" % (ptype))
470 return qfunc(taskid, casetag, provider)
473 def query_load(self, taskid, casetag, provider, ptype):
474 if ptype in ['throughput', 'frameloss']:
475 qfunc = getattr(self, "query_%s_load" % (ptype))
476 return qfunc(taskid, casetag, provider)
479 def query_avglatency(self, taskid, casetag, provider, ptype):
480 if ptype in ['throughput', 'frameloss', 'latency']:
481 qfunc = getattr(self, "query_%s_avg" % (ptype))
482 return qfunc(taskid, casetag, provider)
485 def query_throughput_provider(self, taskid, casetag, provider):
486 query = self._session.query(table.TblThroughput).filter(and_(table.TblTestList.CaseTag == casetag,
487 table.TblTestList.Provider == provider,
488 table.TblTestList.TaskID == taskid,
489 table.TblTestList.TestID == table.TblThroughput.TestID))
492 def query_frameloss_provider(self, taskid, casetag, provider):
493 query = self._session.query(table.TblFrameloss).filter(and_(table.TblTestList.CaseTag == casetag,
494 table.TblTestList.Provider == provider,
495 table.TblTestList.TaskID == taskid,
496 table.TblTestList.TestID == table.TblFrameloss.TestID))
499 def query_latency_provider(self, taskid, casetag, provider):
500 query = self._session.query(table.TblLatency).filter(and_(table.TblTestList.CaseTag == casetag,
501 table.TblTestList.Provider == provider,
502 table.TblTestList.TaskID == taskid,
503 table.TblTestList.TestID == table.TblLatency.TestID))
506 def query_case_type_count(self, taskid, casetag, ptype):
507 query = self._session.query(table.TblTestList).filter(and_(table.TblTestList.CaseTag == casetag,
508 table.TblTestList.Type == ptype,
509 table.TblTestList.TaskID == taskid))
513 def query_case_provider_count(self, taskid, casetag, provider):
514 query = self._session.query(table.TblTestList).filter(and_(table.TblTestList.CaseTag == casetag,
515 table.TblTestList.Provider == provider,
516 table.TblTestList.TaskID == taskid))
519 def query_case_type_provider_count(self, taskid, casetag, provider, ptype):
520 query = self._session.query(table.TblTestList).filter(and_(table.TblTestList.CaseTag == casetag,
521 table.TblTestList.Type == ptype,
522 table.TblTestList.Provider == provider,
523 table.TblTestList.TaskID == taskid))
527 def query_exten_info(self, taskid):
528 query = self._session.query(table.TblEXTInfo.EXTName,
529 table.TblEXTInfo.EXTContent,
530 table.TblEXTInfo.Description).filter(table.TblEXTInfo.TaskID == taskid)
538 taskid = dbase.create_task("test", str(time.ctime()), "this is a unit test")
539 dbase.add_host_2task(taskid, "hosta", "hw82576", "xxx", "x", "82599", "ubuntu")
540 dbase.add_extent_2task(taskid, "CETH", "driver", "version 2.0")
541 dbase.add_extent_2task(taskid, "EVS", "switch", "version 3.0")
543 testid = dbase.add_test_2task(taskid, "Tn-1", 'udp', "throughput", "ovs", None, "netperf")
559 dbase.add_data_2test(testid, data)
561 testid = dbase.add_test_2task(taskid, "Tn-1", 'udp', "frameloss", "ovs", None, "netperf")
577 dbase.add_data_2test(testid, data)
579 testid = dbase.add_test_2task(taskid, "Tn-1", 'udp', "latency", "ovs", None, "netperf")
581 64: {'MaximumLatency': 0.0, 'AverageLatency': 0.0, 'MinimumLatency': 0.0, 'OfferedLoad': 0.0},
582 128: {'MaximumLatency': 0.0, 'AverageLatency': 0.0, 'MinimumLatency': 0.0, 'OfferedLoad': 0.0},
583 512: {'MaximumLatency': 0.0, 'AverageLatency': 0.0, 'MinimumLatency': 0.0, 'OfferedLoad': 0.0},
584 1024: {'MaximumLatency': 0.0, 'AverageLatency': 0.0, 'MinimumLatency': 0.0, 'OfferedLoad': 0.0}
586 dbase.add_data_2test(testid, data)
587 query = dbase.query_testlist(1, "Tn")
591 query = dbase.query_testdata(1, "throughput")
594 if __name__ == '__main__':