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):
36 def __init__(self, db_name=const.DBPATH):
37 db_exists = os.path.exists(db_name)
39 self._engine = create_engine('sqlite:///%s' % db_name, echo=False)
40 db_session = sessionmaker(bind=self._engine)
41 self._session = db_session()
42 except Exception as e:
45 # if the db is new , create all tables and init static tables
51 self._engine.close_all()
53 def create_tables(self):
54 table.Base.metadata.create_all(self._engine)
55 self._session.commit()
57 def drop_tables(self):
58 table.Base.metadata.drop_all(self._engine)
59 self._session.commit()
61 def init_tables(self):
63 self.init_scenario_table()
64 self._session.commit()
66 def init_scenario_table(self):
68 for values in const.SCENARIO_INFO_LIST:
69 item = table.TblScenarioInfo(ScenarioName=values[0],
71 Description=values[2])
73 self._session.add_all(items)
75 # Single TblCaseInfo API
76 def init_casetable(self):
78 for values in const.CASE_INFO_LIST:
79 item = table.TblCaseInfo(CaseTag=values[0],
80 ScenarioName=values[1],
83 Description=values[4],
85 Directiontag=values[6],
88 self._session.add_all(items)
90 def query_caseinfo(self):
91 query = self._session.query(table.TblCaseInfo.ScenarioName,
92 table.TblCaseInfo.CaseTag,
93 table.TblCaseInfo.CaseName,
94 table.TblCaseInfo.Direction,
95 table.TblCaseInfo.Configure)
98 def query_case(self, casetag):
99 query = self._session.query(table.TblCaseInfo.ScenarioName,
100 table.TblCaseInfo.Directiontag)
103 # Single TblTaskList API
104 def get_last_taskid(self):
105 query = self._session.query(table.TblTaskList.TaskID)
107 return query.all()[-1][0]
111 def query_tasklist(self):
112 query = self._session.query(table.TblTaskList)
115 def query_taskdate(self, taskid):
116 query = self._session.query(table.TblTaskList.Date).filter(and_(
117 table.TblTaskList.TaskID == taskid))
120 result += query.first()[0]
123 def query_taskname(self, taskid):
124 query = self._session.query(table.TblTaskList.TaskName).filter(and_(
125 table.TblTaskList.TaskID == taskid))
128 result += query.first()[0]
131 def create_task(self, name, date, desc):
133 item = table.TblTaskList(name, date, desc)
134 self._session.add(item)
135 self._session.commit()
139 return self.get_last_taskid()
141 # Single TblHostInfo API
142 def add_host_2task(self, taskid, name, machine, cpu, men, nic, os):
143 """All var except task must be string"""
144 item = table.TblHostInfo(taskid, name, machine, cpu, men, nic, os)
146 self._session.add(item)
147 self._session.commit()
149 def query_task_host_list(self, taskid):
150 query = self._session.query(table.TblHostInfo.HostName,
151 table.TblHostInfo.Server,
152 table.TblHostInfo.CPU,
153 table.TblHostInfo.MEM,
154 table.TblHostInfo.NIC,
155 table.TblHostInfo.OS).filter(
156 table.TblHostInfo.TaskID == taskid)
159 # Single TblTestList API
160 def get_last_testid(self):
161 query = self._session.query(table.TblTestList.TestID)
164 return query.all()[-1][0]
168 def add_test_2task(self, task, case, protocol, typ, switch, provider, tool):
170 item = table.TblTestList(task, case, protocol, typ, switch, provider, tool)
171 self._session.add(item)
172 self._session.commit()
176 return self.get_last_testid()
178 def get_test_type(self, testid):
179 query = self._session.query(table.TblTestList.Type).filter(
180 table.TblTestList.TestID == testid)
183 def add_extent_2task(self, task, name, content, description):
184 item = table.TblEXTInfo(task, name, content, description)
185 self._session.add(item)
186 self._session.commit()
188 def add_data_2test(self, testid, data):
190 :data example {'64':{
205 ptype = self.get_test_type(testid)
207 'throughput': table.TblThroughput,
208 'frameloss': table.TblFrameloss,
209 'latency': table.TblLatency
212 if ptype and ptype[0] not in instance_map:
213 print "cant find this test(id=%d)" % (testid)
216 test_table_instance = instance_map[ptype[0]]
217 for pktlen in data.iterkeys():
218 args = data.get(pktlen)
219 query = self._session.query(test_table_instance).filter(and_(
220 test_table_instance.TestID == testid,
221 test_table_instance.AvgFrameSize == pktlen))
224 for key, value in data.items():
225 if key in test_table_instance.__dict__:
226 data_dict[test_table_instance.__dict__[key]] = value
227 query.update(data_dict)
230 tester = test_table_instance(testid, pktlen, **args)
231 self._session.add(tester)
232 self._session.commit()
234 def query_tasks(self):
236 ret = self._session.query(table.TblTaskList)
238 for tmp in ret.all():
239 result.append([tmp.TaskID, tmp.TaskName, tmp.Date, tmp.EXTInfo])
242 def query_all_task_id(self):
243 query = self._session.query(table.TblTaskList.TaskID)
249 def get_caseinfo(self):
250 query = self._session.query(table.TblCaseInfo.ScenarioName,
251 table.TblCaseInfo.CaseTag,
252 table.TblCaseInfo.CaseName,
253 table.TblCaseInfo.Direction,
254 table.TblCaseInfo.Configure)
257 def query_scenario(self, casetag):
258 query = self._session.query(table.TblCaseInfo.ScenarioName).filter(table.TblCaseInfo.CaseTag == casetag)
260 if query and query.first():
261 ret = query.first()[0]
264 def query_casefigure(self, casetag, tools):
265 query = self._session.query(table.TblCaseInfo.FigurePath).filter(and_(
266 table.TblCaseInfo.CaseTag == casetag))
269 result += query.first()[0]
271 result += tools + '/' + casetag + '.jpg'
274 def query_casename(self, casetag):
275 query = self._session.query(table.TblCaseInfo.CaseName).filter(and_(
276 table.TblCaseInfo.CaseTag == casetag))
279 result += query.first()[0]
282 # Single TblTestList API
284 def query_caselist(self, taskid, scenario):
285 query = self._session.query(table.TblTestList.CaseTag).filter(and_(
286 table.TblTestList.CaseTag == table.TblCaseInfo.CaseTag,
287 table.TblCaseInfo.ScenarioName == scenario,
288 table.TblTestList.TaskID == taskid)).group_by(table.TblCaseInfo.CaseTag)
291 def query_testlist(self, taskid, scenario):
296 query = self._session.query(table.TblTestList).filter(and_(
297 table.TblTestList.CaseTag == table.TblCaseInfo.CaseTag,
298 table.TblCaseInfo.ScenarioName == scenario,
299 table.TblTestList.TaskID == taskid))
302 def query_casetool(self, taskid, casetag, provider, ptype):
303 query = self._session.query(table.TblTestList.Tools).filter(and_(
304 table.TblTestList.TaskID == taskid,
305 table.TblTestList.CaseTag == casetag,
306 table.TblTestList.Provider == provider,
307 table.TblTestList.Type == ptype))
310 def query_casetools(self, taskid, casetag):
311 query = self._session.query(table.TblTestList.Tools).filter(and_(
312 table.TblTestList.CaseTag == casetag,
313 table.TblTestList.TaskID == taskid)).group_by(table.TblTestList.Tools)
316 def query_scenariolist(self, taskid):
317 query = self._session.query(table.TblCaseInfo.ScenarioName).filter(and_(
318 table.TblTestList.CaseTag == table.TblCaseInfo.CaseTag,
319 table.TblTestList.TaskID == taskid)).group_by(table.TblCaseInfo.ScenarioName)
322 def query_throughput_load(self, taskid, casetag, provider):
324 query = self._session.query(table.TblThroughput.AvgFrameSize, table.TblThroughput.OfferedLoad).filter(and_(
325 table.TblTestList.TaskID == taskid,
326 table.TblTestList.CaseTag == casetag,
327 table.TblTestList.Provider == provider, table.TblTestList.Type == ptype,
328 table.TblTestList.TestID == table.TblThroughput.TestID))
331 def query_throughput_bandwidth(self, taskid, casetag, provider):
333 query = self._session.query(table.TblThroughput.AvgFrameSize, table.TblThroughput.Bandwidth).filter(and_(
334 table.TblTestList.TaskID == taskid,
335 table.TblTestList.CaseTag == casetag,
336 table.TblTestList.Provider == provider, table.TblTestList.Type == ptype,
337 table.TblTestList.TestID == table.TblThroughput.TestID))
340 def query_throughput_table(self, taskid, casetag, provider):
342 query = self._session.query(table.TblThroughput.AvgFrameSize,
343 table.TblThroughput.Bandwidth,
344 table.TblThroughput.OfferedLoad,
345 table.TblThroughput.CPU,
346 table.TblThroughput.MppspGhz,
347 table.TblThroughput.MinimumLatency,
348 table.TblThroughput.MaximumLatency,
349 table.TblThroughput.AverageLatency,
351 table.TblTestList.TaskID == taskid,
352 table.TblTestList.CaseTag == casetag,
353 table.TblTestList.Provider == provider, table.TblTestList.Type == ptype,
354 table.TblTestList.TestID == table.TblThroughput.TestID))
357 def query_throughput_simpletable(self, taskid, casetag, provider):
359 query = self._session.query(table.TblThroughput.AvgFrameSize,
360 table.TblThroughput.Bandwidth,
361 table.TblThroughput.OfferedLoad,
362 table.TblThroughput.CPU,
363 table.TblThroughput.MppspGhz,
364 table.TblThroughput.AverageLatency,
366 table.TblTestList.TaskID == taskid,
367 table.TblTestList.CaseTag == casetag,
368 table.TblTestList.Provider == provider, table.TblTestList.Type == ptype,
369 table.TblTestList.TestID == table.TblThroughput.TestID))
372 def query_testdata(self, testid, ptype):
373 obj = getattr(table, "Tbl%s" % (ptype.title()))
374 query = self._session.query(obj).filter(obj.TestID == testid)
377 def query_throughput_avg(self, taskid, casetag, provider):
379 query = self._session.query(table.TblThroughput.AvgFrameSize, table.TblThroughput.AverageLatency).filter(and_(
380 table.TblTestList.TaskID == taskid,
381 table.TblTestList.CaseTag == casetag,
382 table.TblTestList.Provider == provider, table.TblTestList.Type == ptype,
383 table.TblTestList.TestID == table.TblThroughput.TestID))
386 def query_frameloss_bandwidth(self, taskid, casetag, provider):
388 query = self._session.query(table.TblFrameloss.AvgFrameSize, table.TblFrameloss.Bandwidth).filter(and_(
389 table.TblTestList.TaskID == taskid,
390 table.TblTestList.CaseTag == casetag,
391 table.TblTestList.Provider == provider, table.TblTestList.Type == ptype,
392 table.TblTestList.TestID == table.TblFrameloss.TestID))
395 def query_frameloss_load(self, taskid, casetag, provider):
397 query = self._session.query(table.TblFrameloss.AvgFrameSize, table.TblFrameloss.OfferedLoad).filter(and_(
398 table.TblTestList.TaskID == taskid,
399 table.TblTestList.CaseTag == casetag,
400 table.TblTestList.Provider == provider, table.TblTestList.Type == ptype,
401 table.TblTestList.TestID == table.TblFrameloss.TestID))
404 def query_frameloss_table(self, taskid, casetag, provider):
406 query = self._session.query(table.TblFrameloss.AvgFrameSize,
407 table.TblFrameloss.Bandwidth,
408 table.TblFrameloss.OfferedLoad,
409 table.TblFrameloss.CPU,
410 table.TblFrameloss.MppspGhz,
411 table.TblFrameloss.MinimumLatency,
412 table.TblFrameloss.MaximumLatency,
413 table.TblFrameloss.AverageLatency
415 table.TblTestList.TaskID == taskid,
416 table.TblTestList.CaseTag == casetag,
417 table.TblTestList.Provider == provider, table.TblTestList.Type == ptype,
418 table.TblTestList.TestID == table.TblFrameloss.TestID))
421 def query_frameloss_simpletable(self, taskid, casetag, provider):
423 query = self._session.query(table.TblFrameloss.AvgFrameSize,
424 table.TblFrameloss.Bandwidth,
425 table.TblFrameloss.OfferedLoad,
426 table.TblFrameloss.CPU,
427 table.TblFrameloss.MppspGhz,
428 table.TblFrameloss.AverageLatency
430 table.TblTestList.TaskID == taskid,
431 table.TblTestList.CaseTag == casetag,
432 table.TblTestList.Provider == provider, table.TblTestList.Type == ptype,
433 table.TblTestList.TestID == table.TblFrameloss.TestID))
436 def query_frameloss_avg(self, taskid, casetag, provider):
438 query = self._session.query(table.TblFrameloss.AvgFrameSize, table.TblFrameloss.AverageLatency).filter(and_(
439 table.TblTestList.TaskID == taskid,
440 table.TblTestList.CaseTag == casetag,
441 table.TblTestList.Provider == provider, table.TblTestList.Type == ptype,
442 table.TblTestList.TestID == table.TblFrameloss.TestID))
445 def query_latency_avg(self, taskid, casetag, provider):
447 query = self._session.query(table.TblLatency.AvgFrameSize, table.TblLatency.AverageLatency).filter(and_(
448 table.TblTestList.TaskID == taskid,
449 table.TblTestList.CaseTag == casetag,
450 table.TblTestList.Provider == provider, table.TblTestList.Type == ptype,
451 table.TblTestList.TestID == table.TblLatency.TestID))
454 def query_summary_table(self, taskid, casetag, provider, ptype):
455 if ptype in ['throughput', 'frameloss']:
456 qfunc = getattr(self, "query_%s_table" % (ptype))
457 return qfunc(taskid, casetag, provider)
460 def query_summary_simpletable(self, taskid, casetag, provider, ptype):
461 if ptype in ['throughput', 'frameloss']:
462 qfunc = getattr(self, "query_%s_simpletable" % (ptype))
463 return qfunc(taskid, casetag, provider)
466 def query_bandwidth(self, taskid, casetag, provider, ptype):
467 if ptype in ['throughput', 'frameloss']:
468 qfunc = getattr(self, "query_%s_bandwidth" % (ptype))
469 return qfunc(taskid, casetag, provider)
472 def query_load(self, taskid, casetag, provider, ptype):
473 if ptype in ['throughput', 'frameloss']:
474 qfunc = getattr(self, "query_%s_load" % (ptype))
475 return qfunc(taskid, casetag, provider)
478 def query_avglatency(self, taskid, casetag, provider, ptype):
479 if ptype in ['throughput', 'frameloss', 'latency']:
480 qfunc = getattr(self, "query_%s_avg" % (ptype))
481 return qfunc(taskid, casetag, provider)
484 def query_throughput_provider(self, taskid, casetag, provider):
485 query = self._session.query(table.TblThroughput).filter(and_(table.TblTestList.CaseTag == casetag,
486 table.TblTestList.Provider == provider,
487 table.TblTestList.TaskID == taskid,
488 table.TblTestList.TestID == table.TblThroughput.TestID))
491 def query_frameloss_provider(self, taskid, casetag, provider):
492 query = self._session.query(table.TblFrameloss).filter(and_(table.TblTestList.CaseTag == casetag,
493 table.TblTestList.Provider == provider,
494 table.TblTestList.TaskID == taskid,
495 table.TblTestList.TestID == table.TblFrameloss.TestID))
498 def query_latency_provider(self, taskid, casetag, provider):
499 query = self._session.query(table.TblLatency).filter(and_(table.TblTestList.CaseTag == casetag,
500 table.TblTestList.Provider == provider,
501 table.TblTestList.TaskID == taskid,
502 table.TblTestList.TestID == table.TblLatency.TestID))
505 def query_case_type_count(self, taskid, casetag, ptype):
506 query = self._session.query(table.TblTestList).filter(and_(table.TblTestList.CaseTag == casetag,
507 table.TblTestList.Type == ptype,
508 table.TblTestList.TaskID == taskid))
512 def query_case_provider_count(self, taskid, casetag, provider):
513 query = self._session.query(table.TblTestList).filter(and_(table.TblTestList.CaseTag == casetag,
514 table.TblTestList.Provider == provider,
515 table.TblTestList.TaskID == taskid))
518 def query_case_type_provider_count(self, taskid, casetag, provider, ptype):
519 query = self._session.query(table.TblTestList).filter(and_(table.TblTestList.CaseTag == casetag,
520 table.TblTestList.Type == ptype,
521 table.TblTestList.Provider == provider,
522 table.TblTestList.TaskID == taskid))
526 def query_exten_info(self, taskid):
527 query = self._session.query(table.TblEXTInfo.EXTName,
528 table.TblEXTInfo.EXTContent,
529 table.TblEXTInfo.Description).filter(table.TblEXTInfo.TaskID == taskid)
537 taskid = dbase.create_task("test", str(time.ctime()), "this is a unit test")
538 dbase.add_host_2task(taskid, "hosta", "hw82576", "xxx", "x", "82599", "ubuntu")
539 dbase.add_extent_2task(taskid, "CETH", "driver", "version 2.0")
540 dbase.add_extent_2task(taskid, "EVS", "switch", "version 3.0")
542 testid = dbase.add_test_2task(taskid, "Tn-1", 'udp', "throughput", "ovs", None, "netperf")
558 dbase.add_data_2test(testid, data)
560 testid = dbase.add_test_2task(taskid, "Tn-1", 'udp', "frameloss", "ovs", None, "netperf")
576 dbase.add_data_2test(testid, data)
578 testid = dbase.add_test_2task(taskid, "Tn-1", 'udp', "latency", "ovs", None, "netperf")
580 64: {'MaximumLatency': 0.0, 'AverageLatency': 0.0, 'MinimumLatency': 0.0, 'OfferedLoad': 0.0},
581 128: {'MaximumLatency': 0.0, 'AverageLatency': 0.0, 'MinimumLatency': 0.0, 'OfferedLoad': 0.0},
582 512: {'MaximumLatency': 0.0, 'AverageLatency': 0.0, 'MinimumLatency': 0.0, 'OfferedLoad': 0.0},
583 1024: {'MaximumLatency': 0.0, 'AverageLatency': 0.0, 'MinimumLatency': 0.0, 'OfferedLoad': 0.0}
585 dbase.add_data_2test(testid, data)
586 query = dbase.query_testlist(1, "Tn")
590 query = dbase.query_testdata(1, "throughput")
593 if __name__ == '__main__':