有些项目组要求测试人员每天都要汇报测试情况,如何适当地自动化这个测试报告的过程呢?
d%a4s$c7c1M:_#{0 51Testing软件测试网0Z7z{cM?'p-t
我们先对缺陷跟踪库的存储结构进行分析,然后通过SQL语句的组合查询统计出需要的缺陷报告。
1GtujbYHE;t%I0 51Testing软件测试网3?+gt G*R0{#`Y
如果缺陷跟踪库的表结构比较简单,则实现的难度会比较低,以TestDirector的表结构为例,与存储缺陷相关的信息的表主要有两个:BUG表和History表。
a%_2{9? `#|?S0
Z5l!dG;^0x*?I F0BUG表的结构以SQL导出如下所示:51Testing软件测试网
fH oCI%J+o l
if exists (select * from dbo.sysobjects where id = object_id(N'[td].[BUG]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)51Testing软件测试网qa&B)t]
drop table [td].[BUG]
:KYH^ h0M{0GO51Testing软件测试网dBJ*| UM;z L
w0xe'UBT&_,W!q0CREATE TABLE [td].[BUG] (
s%e+r6@2foO0 [BG_CYCLE_ID] [int] NULL ,51Testing软件测试网JI PAnt1^,G
[BG_BUG_ID] [int] NOT NULL ,51Testing软件测试网]?b1Hs%r4\0n1\o
[BG_STATUS] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,
,e/Wn'dqvb:Y.Q0 [BG_RESPONSIBLE] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网3R:d"v4h'G `
[BG_PROJECT] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,
rS4iD wU[(i0 [BG_SUBJECT] [int] NULL ,51Testing软件测试网$nwI/K(curD;B
[BG_SUMMARY] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
v9]9}`p:?%Uqs8r0 [BG_DEscrīptION] [text] COLLATE Chinese_PRC_CI_AS NULL ,
K+w a1VK.}0 [BG_DEV_COMMENTS] [text] COLLATE Chinese_PRC_CI_AS NULL ,
!B7~(V4Bxf0L0 [BG_REPRODUCIBLE] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网!}`E~5B
[BG_SEVERITY] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,
)S1RYfA8N4a}5jO0 [BG_PRIORITY] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网4f+q:S&oT'b:qI
[BG_DETECTED_BY] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网;VH%kf},f
[BG_TEST_REFERENCE] [int] NULL ,
3AZaL \2X._0 [BG_CYCLE_REFERENCE] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
&jG]'u4l;fr;_(eA0 [BG_RUN_REFERENCE] [int] NULL ,51Testing软件测试网vHT.pUi-]
[BG_STEP_REFERENCE] [int] NULL ,
;q0W8j+L8E&jF4i!Y0 [BG_DETECTION_DATE] [datetime] NULL ,
Y"GXeTZ0 [BG_DETECTION_VERSION] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网w
C)g:D4x(]F
[BG_PLANNED_CLOSING_VER] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,
r1`z:]9Vs8?0 [BG_ESTIMATED_FIX_TIME] [smallint] NULL ,51Testing软件测试网;y!r_ F%u$w/cG%E
[BG_ACTUAL_FIX_TIME] [smallint] NULL ,
Aposn'F0 [BG_CLOSING_DATE] [datetime] NULL ,51Testing软件测试网
e/i2td!T
[BG_CLOSING_VERSION] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网^5e/{1I}CG K
[BG_TO_MAIL] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网'd d4o%MU2W9R
[BG_ATTACHMENT] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网(T&E4E1L
a1[}
[BG_USER_01] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网/XWx@"?(A6Q
[BG_USER_02] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网 ngE2G\mi%R
[BG_USER_03] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
{/kK?S0e8N
b%_ r$T0 [BG_USER_04] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
3v ek$cO FJ0 [BG_USER_05] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网F%?SX;nH@Lbp
[BG_USER_06] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
6O"k2K-Oz5S0 [BG_USER_07] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网0d*k9Gv8drd(f
[BG_USER_08] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
_@eR0z,` co0 [BG_USER_09] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网/UNktYOR!D
T ?
[BG_USER_10] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
&R%V cF{0 [BG_USER_11] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网g,Y!~k/F!\pt
[BG_USER_12] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网GX6zL1_X
[BG_USER_13] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
7z,C/b/qe"`0 [BG_USER_14] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
r(V+Ts`0 [BG_USER_15] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网1x+qq$}m;j"FT
TA
[BG_USER_16] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网'P3i\@ T@8J*e4|
[BG_USER_17] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网G's8sF%`q
[BG_USER_18] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
,kk{:Y0n0 [BG_USER_19] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网G b#V7}/}`Z8I
[BG_USER_20] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
&nK%ZSR:T2|3w0 [BG_USER_21] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
}Ke
L@;U0 [BG_USER_22] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
d)[J$M3Cbx0 [BG_USER_23] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网7W,_H|4n9bd
[BG_USER_24] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网j{rT a6}.b)u
[BG_USER_HR_01] [int] NULL ,51Testing软件测试网6iV\:iV8X2@-Y/I
[BG_USER_HR_02] [int] NULL ,
$n$A"}[2t&@+H6h~od"p0 [BG_USER_HR_03] [int] NULL ,
,s$p)SCFn2M2v0 [BG_USER_HR_04] [int] NULL ,
/S%_n@4NdNm0 [BG_USER_HR_05] [int] NULL ,51Testing软件测试网nv3iu9Cx
[BG_USER_HR_06] [int] NULL ,
2u$B\)jfO0 [BG_BUG_VER_STAMP] [int] NULL ,
kB ]&d7`6Jr!]X)d yS0 [BG_HAS_CHANGE] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
6v5{6e!M7Y2?,^0 [BG_VTS] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL
kq#pa*XK0) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
*vV&myI!A;O@o7K0GO51Testing软件测试网?3_"L\*l)u[5o
2@*D*E^Chu0通过分析可知,每个字段都可在我们录入缺陷的界面找到对应的字段,其中BG_BUG_ID(缺陷ID)、BG_STATUS(缺陷状态,如:Open、Fixed、Closed、Reopen、Rejected、Delay等)、BG_RESPONSIBLE(缺陷的负责人)、BG_PROJECT(缺陷出现的系统)、BG_SUBJECT(缺陷出现的模块)、BG_DETECTION_DATE(缺陷发现的日期)等几个重要的字段信息,可以提供给我们作为查询统计时使用。51Testing软件测试网|!eozFK
UT
g
O-z
51Testing软件测试网
H*A4S8Vv5L"Ex6QS"M
History表的结构用SQL导出如下所示:
/v,h6MkLl0if exists (select * from dbo.sysobjects where id = object_id(N'[td].[HISTORY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)51Testing软件测试网`TXz[K8en?
drop table [td].[HISTORY]
v6?'r/i _%ca6gH0GO51Testing软件测试网Kf5f8hN!NP6|1n
7a1E~
d$g_p#|)R0CREATE TABLE [td].[HISTORY] (51Testing软件测试网1{TJO!fpP5WL
[HS_TABLE_NAME] [varchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,51Testing软件测试网#L-B/Q4G4U
[HS_KEY] [varchar] (70) COLLATE Chinese_PRC_CI_AS NOT NULL ,51Testing软件测试网)k$G3S Ra+TUEa
[HS_COLUMN_NAME] [varchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,51Testing软件测试网 Hm5`j^(s-~
[HS_CHANGE_DATE] [datetime] NOT NULL ,51Testing软件测试网8Z3gD[s{oEAF
[HS_CHANGE_TIME] [varchar] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,51Testing软件测试网a8ws`!?.F5vf
[HS_CHANGER] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,51Testing软件测试网a3e9m
R g}VDg%f_r
[HS_NEW_VALUE] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL
P]H;Em}0) ON [PRIMARY]
~Dz"`(n0GO51Testing软件测试网iFN,f w/J3]A
#ZSWy$r@6N6x+`8L0通过分析可知,可以通过指定HS_TABLE_NAME字段为缺陷表来查找所有Bug的历史信息,HS_CHANGE_DATE和HS_CHANGE_TIME记录Bug的各种状态更改时间。因此可以指定统计某段时间范围内的Bug的历史信息。
%G&A~-~w2Ja0 51Testing软件测试网u(hO5y*|,zpL
例如下面的SQL脚本统计某天的bug情况:
8}:g5RM s'gF#u*d+s0/*当天bug的情况*/51Testing软件测试网
FiY4h3aHK
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)51Testing软件测试网%Vw|5L`g
D"MwB9_
Z0而下面的SQL脚本查询每天的BUG发现的个数,然后按顺序排列,可进一步取出发现BUG最多的几天的日期。
q1F-@^KZ:y;~F0/*到目前为止,发现BUG最多的几天*/51Testing软件测试网}4M]t;K o
SELECT COUNT(*) AS NumberOfBug, BG_DETECTION_DATE AS Date FROM td.BUG GROUP BY BG_DETECTION_DATE Order by NumberOfBug DESC
/u3A-C!^5p0
wr'M:c#}4f7@B+P%d0下面的SQL脚本统计当前各种状态的BUG的数量:
k4o/Jdu1hi R&s0/*当前所有状态的bug情况*/
D{:Y'p]jYA0Select count(*) as BUGCount,BG_STATUS as BUGStatus from td.BUG group by BG_STATUS51Testing软件测试网+|;W#H.@!rB.w9O
51Testing软件测试网+t6e
b{9M4f+K
每天定时执行类似上面的几个脚本,然后把返回的统计结果发送给项目组中的相关人员,则实现了每日缺陷的自动报告机制。51Testing软件测试网tBxj+d3Y+t
Ig5T5V"Z
51Testing软件测试网\p~/E(E
虽然有些缺陷跟踪管理工具支持定时邮件发送缺陷报告的功能,但是每日缺陷简报还是有其存在的必要性。
2Y"Z:q0v|Vj9|i0
B#H$b?#Jv0因为每日缺陷简报可定制自己需要的缺陷报告和统计数据。每天早上开发人员打开电脑就可以知道今天需要处理的BUG的情况,项目经理也可以每天都能及时了解到产品的缺陷情况。测试人员则可以了解最新的BUG修改情况,缺陷的发现率情况等。51Testing软件测试网zA5T2R"zh TZ
51Testing软件测试网\%_lu'ND)|Kth
5R#CH8lGC$F
j0