setup.sql

Bug管理系统

if exists (select * from dbo.sysobjects where id = object_id(N'[bug_attachments]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [bug_attachments]

if exists (select * from dbo.sysobjects where id = object_id(N'[bug_comments]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [bug_comments]

if exists (select * from dbo.sysobjects where id = object_id(N'[bug_subscriptions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [bug_subscriptions]

if exists (select * from dbo.sysobjects where id = object_id(N'[bugs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [bugs]

if exists (select * from dbo.sysobjects where id = object_id(N'[categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [categories]

if exists (select * from dbo.sysobjects where id = object_id(N'[priorities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [priorities]

if exists (select * from dbo.sysobjects where id = object_id(N'[project_user_xref]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [project_user_xref]

if exists (select * from dbo.sysobjects where id = object_id(N'[projects]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [projects]

if exists (select * from dbo.sysobjects where id = object_id(N'[queries]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [queries]

if exists (select * from dbo.sysobjects where id = object_id(N'[reports]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [reports]

if exists (select * from dbo.sysobjects where id = object_id(N'[sessions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [sessions]

if exists (select * from dbo.sysobjects where id = object_id(N'[statuses]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [statuses]

if exists (select * from dbo.sysobjects where id = object_id(N'[user_defined_attribute]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [user_defined_attribute]

if exists (select * from dbo.sysobjects where id = object_id(N'[users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [users]

/* USER */

create table users
(
us_id int identity primary key not null,
us_username nvarchar(40) not null,
us_password nvarchar(64) not null,
us_firstname nvarchar(60) null,
us_lastname nvarchar(60) null,
us_email nvarchar(120) null,
us_admin int not null default(0),
us_default_query int null,
us_enable_notifications int not null default(1),
us_auto_subscribe int not null default(0),
us_auto_subscribe_own_bugs int null default(0),
us_auto_subscribe_reported_bugs int null default(0),
us_only_status_change_notifications int null default(0),
us_send_notifications_to_self int null default(0),
us_only_new_bug_notifications int null default(0),
us_active int not null default(1),
us_bugs_per_page int null,
us_forced_project int null
)

insert into users (
us_username, us_firstname, us_lastname, us_password, us_admin, us_default_query)
values ('admin', 'System', 'Administrator', 'admin', 1, 1)

insert into users (
us_username, us_firstname, us_lastname, us_password, us_admin, us_default_query)
values ('fred', 'Fred', 'Flintstone', 'admin', 0, 2)

insert into users (
us_username, us_firstname, us_lastname, us_password, us_admin, us_default_query)
values ('wilma', 'Wilma', 'Flintstone', 'admin', 0, 3)

insert into users (
us_username, us_firstname, us_lastname, us_password, us_admin, us_default_query)
values ('email', 'For POP3', 'See Web.config', 'x', 0, 1)


/* SESSIONS */

create table sessions
(
	se_id char(37) not null,
	se_date datetime not null default(getdate()),
	se_user int not null
)

/* CATEGORIES */

create table categories
(
ct_id int identity primary key not null,
ct_name nvarchar(80) not null,
ct_sort_seq int not null default(0)
)

insert into categories (ct_name) values('something')
insert into categories (ct_name) values('whatever')

/* PROJECTS */

create table projects
(
pj_id int identity primary key not null,
pj_name nvarchar(80) not null,
pj_active int not null default(1),
pj_default_user int null,
pj_auto_assign_default_user int null,
pj_auto_subscribe_default_user int null,
pj_enable_pop3 int null,
pj_pop3_username varchar(50) null,
pj_pop3_password nvarchar(20) null,
pj_pop3_email_from nvarchar(120) null,
pj_enable_custom_dropdown1 int not null default(0),
pj_enable_custom_dropdown2 int not null default(0),
pj_enable_custom_dropdown3 int not null default(0),
pj_custom_dropdown_label1 nvarchar(80) null,
pj_custom_dropdown_label2 nvarchar(80) null,
pj_custom_dropdown_label3 nvarchar(80) null,
pj_custom_dropdown_values1 nvarchar(1200) null,
pj_custom_dropdown_values2 nvarchar(1200) null,
pj_custom_dropdown_values3 nvarchar(1200) null
)

insert into projects (pj_name) values('project 1')
insert into projects (pj_name) values('project 2')


/* BUGS */

create table bugs 
(
bg_id int identity primary key not null,
bg_short_desc nvarchar(200) not null,
bg_reported_user int not null,
bg_reported_date datetime not null,
bg_status int not null,
bg_priority int not null,
bg_category int not null,
bg_project int not null,
bg_assigned_to_user int null,
bg_last_updated_user int null,
bg_last_updated_date datetime null,
bg_user_defined_attribute int null,
bg_project_custom_dropdown_value1 nvarchar(120) null,
bg_project_custom_dropdown_value2 nvarchar(120) null,
bg_project_custom_dropdown_value3 nvarchar(120) null
)


/* COMMENTS */

create table bug_comments
(
bc_id int identity primary key not null,
bc_bug int not null,
bc_user int not null,
bc_date datetime not null,
bc_comment ntext not null,
bc_email_from nvarchar(800) null,
bc_email_to nvarchar(255) null,
bc_type varchar(8) not null
)

create index bc_index_1 on bug_comments (bc_bug)


/* ATTACHMENTS */

create table bug_attachments
(
ba_id int identity primary key not null,
ba_bug int not null,
ba_file nvarchar(1000) not null,
ba_desc nvarchar(200) null,
ba_size int not null,
ba_uploaded_date datetime not null,
ba_uploaded_user int not null,
ba_content_type nvarchar(200) null,
ba_comment int null
)

create index ba_index_1 on bug_attachments (ba_bug)


/* BUG SUBSCRIPTIONS */

create table bug_subscriptions
(
bs_id int identity primary key not null,
bs_bug int not null,
bs_user int not null,
)

create index bs_index_1 on bug_subscriptions (bs_user, bs_bug)
create index bs_index_2 on bug_subscriptions (bs_bug, bs_user)


/* PROJECT USER XREF */

create table project_user_xref
(
pu_id int identity primary key not null,
pu_project int not null,
pu_user int not null,
pu_auto_subscribe int not null default(0),
/* 0=none, 1=view only, 2=edit */
pu_permission_level int default(2)
)

create index pu_index_1 on project_user_xref (pu_project, pu_user)
create index pu_index_2 on project_user_xref (pu_user, pu_project)


/* USER DEFINED ATTRIBUTE */

create table user_defined_attribute
(
udf_id int identity primary key not null,
udf_name nvarchar(60) not null,
udf_sort_seq int not null default(0),
)
insert into user_defined_attribute (udf_name) values ('whatever')
insert into user_defined_attribute (udf_name) values ('anything')


/* STATUSES */

create table statuses
(
st_id int identity primary key not null,
st_name nvarchar(60) not null,
st_sort_seq int not null default(0),
st_style nvarchar(30) null
)

insert into statuses (st_name, st_sort_seq) values ('new', 1)
insert into statuses (st_name, st_sort_seq) values ('in progress', 2)
insert into statuses (st_name, st_sort_seq) values ('checked in', 3)
insert into statuses (st_name, st_sort_seq) values ('re-opened', 4)
insert into statuses (st_name, st_sort_seq) values ('closed', 5)

/* PRIORITIES */

create table priorities
(
pr_id int identity primary key not null,
pr_name nvarchar(60) not null,
pr_sort_seq int not null default(0),
pr_background_color nvarchar(14) not null,
pr_style nvarchar(30) null
)

insert into priorities (pr_name, pr_sort_seq, pr_background_color) values ('high', 1, '#ff9999')
insert into priorities (pr_name, pr_sort_seq, pr_background_color) values ('med', 2, '#ffdddd')
insert into priorities (pr_name, pr_sort_seq, pr_background_color) values ('low', 3, '#ffffff')


/* REPORTS */
create table reports
(
	rp_id int identity primary key not null,
	rp_desc nvarchar(200) not null,
	rp_sql ntext not null,
	rp_chart_type varchar(8) not null
)

/* Some examples to get you started */

insert into reports (rp_desc, rp_sql, rp_chart_type)
values('Bugs by Status',
'select st_name [status], count(1) [count] from bugs inner join statuses on bg_status = st_id group by st_name order by st_name',
'pie')

insert into reports (rp_desc, rp_sql, rp_chart_type)
values('Bugs by Priority',
'select pr_name [priority], count(1) [count] from bugs inner join priorities on bg_priority = pr_id group by pr_name order by pr_name',
'pie')

insert into reports (rp_desc, rp_sql, rp_chart_type)
values('Bugs by Category',
'select ct_name [category], count(1) [count] from bugs inner join categories on bg_category = ct_id group by ct_name order by ct_name',
'pie')

insert into reports (rp_desc, rp_sql, rp_chart_type)
values('Bugs by Month',
'select month(bg_reported_date) [month], count(1) [count] from bugs group by year(bg_reported_date), month(bg_reported_date) order by year(bg_reported_date), month(bg_reported_date)',
'bar')

insert into reports (rp_desc, rp_sql, rp_chart_type)
values('Bugs by Day of Year',
'select datepart(dy, bg_reported_date) [day of year], count(1) [count] from bugs group by datepart(dy, bg_reported_date), datepart(dy,bg_reported_date) order by 1',
'line')

insert into reports (rp_desc, rp_sql, rp_chart_type)
values('Bugs by User',
'select bg_reported_user, count(1) [r] into #t from bugs group by bg_reported_user; select bg_assigned_to_user, count(1) [a] into #t2 from bugs group by bg_assigned_to_user; select us_username, r [reported], a [assigned] from users left outer join #t on bg_reported_user = us_id left outer join #t2 on bg_assigned_to_user = us_id order by 1', 
'table')



/* QUERIES */

create table queries
(
	qu_id int identity primary key not null,
	qu_desc nvarchar(200) not null,
	qu_sql ntext not null,
	qu_default int null,
	qu_user int null
)

/*

The web pages that display the bugs expect the first two columns of the
queries to be the color or style of the row and the bug id.

Here are examples to get you started.

*/

insert into queries (qu_desc, qu_sql, qu_default) values (
'show all bugs',
'select isnull(pr_background_color,''#ffffff''), bg_id [id], '
+ ' bg_short_desc [desc], pj_name [project], ct_name [category], rpt.us_username [reported by], bg_reported_date [reported on], pr_name [priority], asg.us_username [assigned to], st_name [status], lu.us_username [last updated by], bg_last_updated_date [last updated on]'
+ ' from bugs '
+ ' left outer join users rpt on rpt.us_id = bg_reported_user'
+ ' left outer join users asg on asg.us_id = bg_assigned_to_user'
+ ' left outer join users lu on lu.us_id = bg_last_updated_user'
+ ' left outer join projects on pj_id = bg_project'
+ ' left outer join categories on ct_id = bg_category'
+ ' left outer join priorities on pr_id = bg_priority'
+ ' left outer join statuses on st_id = bg_status'
+ ' order by bg_id desc',
1)

insert into queries (qu_desc, qu_sql, qu_default) values (
'show all not-closed bugs - for developers',
'select isnull(pr_background_color,''#ffffff''), bg_id [id], '
+ ' bg_short_desc [desc], pj_name [project], ct_name [category], rpt.us_username [reported by], bg_reported_date [reported on], pr_name [priority], asg.us_username [assigned to], st_name [status], lu.us_username [last updated by], bg_last_updated_date [last updated on]'
+ ' from bugs '
+ ' left outer join users rpt on rpt.us_id = bg_reported_user'
+ ' left outer join users asg on asg.us_id = bg_assigned_to_user'
+ ' left outer join users lu on lu.us_id = bg_last_updated_user'
+ ' left outer join projects on pj_id = bg_project'
+ ' left outer join categories on ct_id = bg_category'
+ ' left outer join priorities on pr_id = bg_priority'
+ ' left outer join statuses on st_id = bg_status'
+ ' where bg_status <> 5 order by bg_id desc',
0)

insert into queries (qu_desc, qu_sql, qu_default) values (
'show my open bugs - for developer with tunnel vision',
'select isnull(pr_background_color,''#ffffff''), bg_id [id], '
+ ' bg_short_desc [desc], pj_name [project], ct_name [category], rpt.us_username [reported by], bg_reported_date [reported on], pr_name [priority], asg.us_username [assigned to], st_name [status], lu.us_username [last updated by], bg_last_updated_date [last updated on]'
+ ' from bugs '
+ ' left outer join users rpt on rpt.us_id = bg_reported_user'
+ ' left outer join users asg on asg.us_id = bg_assigned_to_user'
+ ' left outer join users lu on lu.us_id = bg_last_updated_user'
+ ' left outer join projects on pj_id = bg_project'
+ ' left outer join categories on ct_id = bg_category'
+ ' left outer join priorities on pr_id = bg_priority'
+ ' left outer join statuses on st_id = bg_status'
+ ' where bg_status <> 5 and bg_assigned_to_user = $ME order by bg_id desc',
0)

insert into queries (qu_desc, qu_sql, qu_default) values (
'show checked in bugs - for QA Analyst',
'select isnull(pr_background_color,''#ffffff''), bg_id [id], '
+ ' bg_short_desc [desc], pj_name [project], ct_name [category], rpt.us_username [reported by], bg_reported_date [reported on], pr_name [priority], asg.us_username [assigned to], st_name [status], lu.us_username [last updated by], bg_last_updated_date [last updated on]'
+ ' from bugs '
+ ' left outer join users rpt on rpt.us_id = bg_reported_user'
+ ' left outer join users asg on asg.us_id = bg_assigned_to_user'
+ ' left outer join users lu on lu.us_id = bg_last_updated_user'
+ ' left outer join projects on pj_id = bg_project'
+ ' left outer join categories on ct_id = bg_category'
+ ' left outer join priorities on pr_id = bg_priority'
+ ' left outer join statuses on st_id = bg_status'
+ ' where bg_status = 3 order by bg_id desc',
0)


insert into queries (qu_desc, qu_sql, qu_default) values (
'demo use of css classes',
'select isnull(pr_style + st_style,''datad''), bg_id [id], bg_short_desc [desc]'
+ ' from bugs '
+ ' left outer join priorities on pr_id = bg_priority '
+ ' left outer join statuses on st_id = bg_status '
+ ' order by bg_id desc',
0)

insert into queries (qu_desc, qu_sql, qu_default) values (
'demo showing last comment',
'select ''#ffffff'', bg_id [id], bg_short_desc, ' 
+ ' substring(bc_comment,1,40) [last comment], bc_date [last comment date]'
+ ' from bugs'
+ ' left outer join bug_comments on bg_id = bc_bug'
+ ' and bc_type = ''comment''' 
+ ' and bc_date in (select max(bc_date) from bug_comments where bc_bug = bg_id)'
+ ' order by bg_id desc',
0)