bbs.sql

又一个课程设计 简易 JSP 论坛 功能较简单的那种, 界面上模仿了 Discuz JSP 本来就学的不行, 只是尽量实现了 MVC

--DROP TABLE [comment];
--DROP TABLE [topic];
--DROP TABLE [manager];
--DROP TABLE [usr];
--DROP TABLE [forum];
--GO

-- 建立用户表
CREATE TABLE [usr] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[usn] [varchar] (50) COLLATE Chinese_PRC_CI_AS PRIMARY KEY NOT NULL,
	[pwd] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[email] [varchar] (50) COLLATE Chinese_PRC_CI_AS DEFAULT ('') NULL,
	[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS DEFAULT ('') NULL,
	[sign] [varchar] (255) COLLATE Chinese_PRC_CI_AS DEFAULT ('') NULL,
	[reg] [datetime] DEFAULT (GetDate()) NOT NULL,
	[head] [varchar] (128) COLLATE Chinese_PRC_CI_AS DEFAULT ('userheads/default.jpg') NULL,
	[role] [int] DEFAULT (1) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];

-- 建立版块表
CREATE TABLE [forum] (
	[id] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL,
	[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[descripted] [nvarchar] (512) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[topic] [int] DEFAULT(0) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];

-- 建立管理员表
CREATE TABLE [manager] (
	[id] [int] IDENTITY (1, 1) NOT NULL,
	[usr] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[forum] [int] NOT NULL,
    PRIMARY KEY ([usr], [forum]),
    FOREIGN KEY ([usr]) REFERENCES [usr]([usn]) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY ([forum]) REFERENCES [forum]([id]) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];

-- 建立话题表
CREATE TABLE [topic] (
	[id] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL,
	[forum] [int] NOT NULL,
	[title] [varchar] (128) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[content] [text] COLLATE Chinese_PRC_CI_AS NOT NULL,
	[post] [datetime] DEFAULT (GetDate()) NOT NULL,
	[update] [datetime] DEFAULT (GetDate()) NULL,
	[author] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[intop] [bit] DEFAULT (0) NOT NULL,
	[view] [int] DEFAULT (0) NOT NULL,
	[reply] [int] DEFAULT (0) NOT NULL,
	[replyT] [datetime] DEFAULT (GetDate()) NULL,
    FOREIGN KEY ([forum]) REFERENCES [forum]([id]) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY ([author]) REFERENCES [usr]([usn]) ON DELETE CASCADE ON UPDATE CASCADE   
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];

-- 建立评论表
CREATE TABLE [comment] (
	[id] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL,
	[topic] [int] NOT NULL,
	[content] [text] COLLATE Chinese_PRC_CI_AS NOT NULL,
	[author] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[post] [datetime] DEFAULT (GetDate()) NOT NULL,
    FOREIGN KEY ([topic]) REFERENCES [topic]([id]) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];

GO

-- 建立触发器 INSTEAD OF 
CREATE TRIGGER [m_delete_comment] ON [usr] INSTEAD OF DELETE
AS
    DECLARE @usn [varchar](50)
    SELECT @usn=[usn] FROM DELETED
    DELETE FROM [comment] WHERE [author]=@usn
    DELETE FROM [usr] WHERE [usn]=@usn
GO

-- 'E10ADC3949BA59ABBE56E057F20F883E' = MD5('123456')
INSERT INTO [usr]([usn],[pwd],[email],[name],[sign],[role]) VALUES('alvin','E10ADC3949BA59ABBE56E057F20F883E','[e-mail]','林志斌','不停揣测你心里,可有我姓名..',3);