每日缺陷简报
上一篇 / 下一篇 2007-11-11 20:52:19 / 个人分类:自动化测试
有些项目组要求测试人员每天都要汇报测试情况,如何适当地自动化这个测试报告的过程呢?51Testing软件测试网 p gI EM-d
1Z|U v1u0
我们先对缺陷跟踪库的存储结构进行分析,然后通过SQL语句的组合查询统计出需要的缺陷报告。51Testing软件测试网*z5kA2z?:TT|0qd
2FqPX o[1T0R0
如果缺陷跟踪库的表结构比较简单,则实现的难度会比较低,以TestDirector的表结构为例,与存储缺陷相关的信息的表主要有两个:BUG表和History表。51Testing软件测试网9A'oT%fBVo
0LtykG0
BUG表的结构以SQL导出如下所示:51Testing软件测试网3hr9C/y,y m Xnc
if exists (select * from dbo.sysobjects where id = object_id(N'[td].[BUG]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)51Testing软件测试网PW4M"PW [ T
drop table [td].[BUG]
}*pC[D"Kh0GO51Testing软件测试网 @ t-j:Dxbc-d2K"H
CREATE TABLE [td].[BUG] (
Ev!h!aX d*m2FO0[BG_CYCLE_ID] [int] NULL ,
'\.n&ac @@0[BG_BUG_ID] [int] NOT NULL ,
g p8NR-Xj0[BG_STATUS] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网 o*gYqc{6e4D%Y
[BG_RESPONSIBLE] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
CP xl~,S]p0[BG_PROJECT] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网2Sh9oYp'd
[BG_SUBJECT] [int] NULL ,51Testing软件测试网oS7?8F~
[BG_SUMMARY] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
2~~ o7Q2B-Ep0og0[BG_DEscrīptION] [text] COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网]7} D^X
[BG_DEV_COMMENTS] [text] COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网,f`$c$E:B!~
[BG_REPRODUCIBLE] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
aa6B OL.y]Z0[BG_SEVERITY] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,
EBKEh+N/| @(m0[BG_PRIORITY] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,
@~6N/Jm g0[BG_DETECTED_BY] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
Dl%n!pF1Y b0[BG_TEST_REFERENCE] [int] NULL ,
%L4l8T!o_"s C0[BG_CYCLE_REFERENCE] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网CkW'I}u#C
[BG_RUN_REFERENCE] [int] NULL ,
)W7C? `_0[BG_STEP_REFERENCE] [int] NULL ,51Testing软件测试网$C q,U n(g8oRo
[BG_DETECTION_DATE] [datetime] NULL ,51Testing软件测试网qA$i9l;E*NL
[BG_DETECTION_VERSION] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,
\|'Y v2v#a E0[BG_PLANNED_CLOSING_VER] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,
|&m)~Bx#[u,p,g+b0[BG_ESTIMATED_FIX_TIME] [smallint] NULL ,
c'W8j}3T'umb0[BG_ACTUAL_FIX_TIME] [smallint] NULL ,51Testing软件测试网UQ6}8j S+v
[BG_CLOSING_DATE] [datetime] NULL ,
,xNq0\'T,t{b'FMy0[BG_CLOSING_VERSION] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网z8YhzZ4C2M^H ECkH
[BG_TO_MAIL] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
m1nr{S1L l-X`0[BG_ATTACHMENT] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
)xF$[0JNzG)Wn0[BG_USER_01] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网%zI1w1w+Q,cV%X?
[BG_USER_02] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网;P(X'a,YL$J
[BG_USER_03] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网+N6qZW5DO!f4i
[BG_USER_04] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
]iZC1J5Un'R0[BG_USER_05] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网&~Y3j+E?"n[?8c b
[BG_USER_06] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
6z_UF%y[0[BG_USER_07] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网6`.bi0l:`&|%|
[BG_USER_08] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网l bOk;wY6R
[BG_USER_09] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网$?#E%FEu;I~A k
[BG_USER_10] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
]N1`'rws1K0[BG_USER_11] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网/H/HS$z W,r{'M
[BG_USER_12] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
S8@$A4W^m.ES0[BG_USER_13] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网)[p^R;c+CK
[BG_USER_14] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
-dd.Y D SpA+px0[BG_USER_15] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
"q9I-RTD%M0[BG_USER_16] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网)Znk:cN;l!B1Q.D
[BG_USER_17] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
0kL'b?lh9g\`0[BG_USER_18] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网A,W|i kG2[:r
[BG_USER_19] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网K7oXF;vE/B`
[BG_USER_20] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网T\VN"W2T.Pl
[BG_USER_21] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
)R%a)E)O4IQ3ehz0[BG_USER_22] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网:Z9n#U+WAS:E#[A1o
[BG_USER_23] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
;\:e1tB:W` [_0[BG_USER_24] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
])Mf)^I0^0|lc0[BG_USER_HR_01] [int] NULL ,
,J-NbR kf.DyB0[BG_USER_HR_02] [int] NULL ,51Testing软件测试网A$HW|nH
[BG_USER_HR_03] [int] NULL ,51Testing软件测试网c X9^*ct B3e2t%a'`
[BG_USER_HR_04] [int] NULL ,51Testing软件测试网 `[cP,RHov
[BG_USER_HR_05] [int] NULL ,
(T1j.{;I`%g0[BG_USER_HR_06] [int] NULL ,
mo5J.qW)\5m0[BG_BUG_VER_STAMP] [int] NULL ,
/n0pX nje?0[BG_HAS_CHANGE] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网baE.Doh
[BG_VTS] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL51Testing软件测试网z-S0J7j7rs*W&\f }&[
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]51Testing软件测试网Tn8wg/`(W/K
GO51Testing软件测试网'~rJ%d ps-T
0o*M6vIDB$TfG+M0
通过分析可知,每个字段都可在我们录入缺陷的界面找到对应的字段,其中BG_BUG_ID(缺陷ID)、BG_STATUS(缺陷状态,如:Open、Fixed、Closed、Reopen、Rejected、Delay等)、BG_RESPONSIBLE(缺陷的负责人)、BG_PROJECT(缺陷出现的系统)、BG_SUBJECT(缺陷出现的模块)、BG_DETECTION_DATE(缺陷发现的日期)等几个重要的字段信息,可以提供给我们作为查询统计时使用。51Testing软件测试网 R wes.j$r:Qw
51Testing软件测试网;@3a#Kr+k"f?
History表的结构用SQL导出如下所示:
,X5I?J)MYd-[0if exists (select * from dbo.sysobjects where id = object_id(N'[td].[HISTORY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
D6@o*TAuLg0drop table [td].[HISTORY]51Testing软件测试网w2Z&nu}[^
GO51Testing软件测试网4{c;U6cxzC rm(\
CREATE TABLE [td].[HISTORY] (51Testing软件测试网R@|#l tR;^]o%RO
[HS_TABLE_NAME] [varchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,51Testing软件测试网:Gi Zy.ff'jK^z
[HS_KEY] [varchar] (70) COLLATE Chinese_PRC_CI_AS NOT NULL ,51Testing软件测试网"BmQ4^]|nb
[HS_COLUMN_NAME] [varchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,51Testing软件测试网lL%N4m!u!?dl2F
[HS_CHANGE_DATE] [datetime] NOT NULL ,
WwF|}:}[0[HS_CHANGE_TIME] [varchar] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,
&A/wZT8{V0[HS_CHANGER] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网 SK6TinG:x%F*h
[HS_NEW_VALUE] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL51Testing软件测试网:?.CT x4DR7Dn9i
) ON [PRIMARY]
3E3}'RS]4M~0GO51Testing软件测试网3j'^.WM3B:|2FC}
'`P @^3P y6T*J%W0
通过分析可知,可以通过指定HS_TABLE_NAME字段为缺陷表来查找所有Bug的历史信息,HS_CHANGE_DATE和HS_CHANGE_TIME记录Bug的各种状态更改时间。因此可以指定统计某段时间范围内的Bug的历史信息。
q}5wsW%B0Lf)o~0s P0
例如下面的SQL脚本统计某天的bug情况:51Testing软件测试网:BV9f0z0}
/*当天bug的情况*/51Testing软件测试网e ?s _,qR
SELECT COUNT(*) AS COUNT, HS_New_VALUE FROM td.HISTORY WHERE (HS_TABLE_NAME = 'BUG') AND (HS_COLUMN_NAME = 'BG_STATUS') AND (HS_CHANGE_DATE = '2007-4-12') GROUP BY (HS_NEW_VALUE)