高访问量的评论系统数据库存储过程架构

SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[CommentsTables](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [Key] [nvarchar](50) NOT NULL,
 [TableName] [nvarchar](80) NOT NULL,
 [StartID] [int] NOT NULL,
 [EndID] [int] NOT NULL,
 CONSTRAINT [PK_SysTables] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
 
 
 
 
 
--根据SourceID和key获得表名
create function [dbo].[funGetTableName]
(
@SourceID int,
@Key nvarchar(50)
)
RETURNS nvarchar(80)
as
begin
 declare @tableName nvarchar(80);
 declare @tableArea int;
 declare @mod int;
 
 declare @Size int;
 set @Size = 1000;
 
 set @mod = @SourceID % @Size;
 if @mod > 0 
 set @tableArea = Cast((@SourceID-1) / @Size as int) + 1; 
 else 
 set @tableArea = Cast((@SourceID-1) / @Size as int);
 
 
 set @tableName = 'comments_' + @Key + Cast(@tableArea as nvarchar(10));
 
 return @tableName
end
GO
 
 
 
 
 
 
 
--评论写入调用存储过程
 
CREATE proc [dbo].[procAddComment]
(
@ParentID int,
@SourceID int,
@NickName nvarchar(20),
@Content nvarchar(300),
@IP nvarchar(30),
@City nvarchar(30),
@BeFiltered bit,
@Disable bit,
@Key nvarchar(50),
@InsertedID int Output
)
as
begin
 declare @tableName nvarchar(80);
 declare @tableArea int;
 declare @mod int;
 
 declare @Size int;
 set @Size = 1000;
 
 set @mod = @SourceID % @Size;
 if @mod > 0 
 set @tableArea = Cast((@SourceID-1) / @Size as int) + 1; 
 else 
 set @tableArea = Cast((@SourceID-1) / @Size as int);
 
 
 
 
 set @tableName = 'comments_' + @Key + Cast(@tableArea as nvarchar(10));
 
 if not Exists(select 'x' from [CommentsTables] where [Key]=@Key and [TableName]=@tableName) 
 begin
 
 declare @StartID int;
 declare @EndID int;
 
 set @EndID = @tableArea * @Size;
 set @StartID = @EndID - (@Size-1);
 
 
 --创建表
 declare @CreateSQL nvarchar(MAX);
 set @CreateSQL = 
 'Create table [dbo].['+@tableName+'](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [ParentID] [int] NOT NULL,
 [SourceID] [int] NOT NULL,
 [NickName] [nvarchar](20) NOT NULL,
 [Content] [nvarchar](300) NOT NULL,
 [Datetime] [datetime] NOT NULL,
 [IP] [nvarchar](30) NOT NULL,
 [City] [nvarchar](30) NOT NULL,
 [BeFiltered] [bit] NOT NULL,
 [Disable] [bit] NOT NULL,
 [Lou] [int] NOT NULL,
 [Ding] [int] NOT NULL,
 [Cai] [int] NOT NULL,
 CONSTRAINT [PK_'+@tableName+'] PRIMARY KEY CLUSTERED 
 (
 [ID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]'
 
 
 EXEC(@CreateSQL);
 
 
 --创建索引 ID DESC
 EXEC(' 
 CREATE UNIQUE NONCLUSTERED INDEX [IX_'+@tableName+'_ID_DESC] ON [dbo].['+@tableName+'] 
 (
 [ID] DESC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]');
 
 --创建索引 Ding DESC
 EXEC('
 CREATE NONCLUSTERED INDEX [IX_'+@tableName+'_Ding_DESC] ON [dbo].['+@tableName+'] 
 (
 [Ding] DESC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]');
 
 --创建索引 SourceID DESC
 EXEC('
 CREATE NONCLUSTERED INDEX [IX_'+@tableName+'_SourceID_DESC] ON [dbo].['+@tableName+'] 
 (
 [SourceID] DESC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]');
 
 --创建索引 Lou DESC
 EXEC('
 CREATE NONCLUSTERED INDEX [IX_'+@tableName+'_Lou_DESC] ON [dbo].['+@tableName+'] 
 (
 [Lou] DESC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]')
 
 
 --创建默认值
 EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_ParentID] DEFAULT ((0)) FOR [ParentID]');
 
 EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_Datetime] DEFAULT (getdate()) FOR [Datetime]');
 
 EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_BeFiltered] DEFAULT ((0)) FOR [BeFiltered]'); 
 
 EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_Disable] DEFAULT ((0)) FOR [Disable]');
 
 EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_Lou] DEFAULT ((1)) FOR [Lou]');
 
 EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_Ding] DEFAULT ((0)) FOR [Ding]');
 
 EXEC('ALTER TABLE [dbo].['+@tableName+'] ADD CONSTRAINT [DF_'+@tableName+'_Cai] DEFAULT ((0)) FOR [Cai]');
 
 Insert Into [CommentsTables]([Key],[TableName],[StartID],[EndID]) values(@Key,@tableName,@StartID,@EndID);
 end
 
 
 declare @TemLou int;
 declare @SQL nvarchar(MAX);
 set @SQL = N'select @TemLou = Count(ID) from dbo.['+@tableName+N'] where SourceID=@SourceID';
 
 exec sp_executesql @SQL,
 N'@SourceID int,@TemLou int output',
 @SourceID,
 @TemLou output;
 
 if @TemLou = 0
 set @TemLou = 1;
 else
 set @TemLou = @TemLou + 1;
 
 
 
 declare @Lou int;
 set @Lou = @TemLou;
 
 declare @InsertSQL nvarchar(MAX);
 set @InsertSQL = N'Insert Into dbo.['+@tableName+N'](ParentID,SourceID,NickName,Content,IP,City,BeFiltered,[Disable],[Lou])
 values (@ParentID,@SourceID,@NickName,@Content,@IP,@City,@BeFiltered,@Disable,@Lou);select @InsertedID = SCOPE_IDENTITY();';
 
 exec sp_executesql @InsertSQL,
 N'@ParentID int,@SourceID int,@NickName nvarchar(20),@Content nvarchar(300),@IP nvarchar(30),@City nvarchar(30),@BeFiltered bit,@Disable bit,@Lou int,@InsertedID int output',
 @ParentID,
 @SourceID,
 @NickName,
 @Content,
 @IP,
 @City,
 @BeFiltered,
 @Disable,
 @Lou,
 @InsertedID output;
end
 
 
 
 
 
 
 
 
GO
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
--获得最新评论存储过程
 
CREATE proc [dbo].[procGetNewComments]
(
@SourceID int,
@Key nvarchar(50),
@PageIndex int,
@PageSize int,
@Fields nvarchar(100),
@PageCount int output
)
as
begin
 declare @tableName nvarchar(80);
 set @tableName = dbo.funGetTableName(@SourceID,@Key);
 declare @Rc int; 
 
 declare @SQL nvarchar(MAX);
 set @SQL = N'select @Rc = COUNT(ID) from dbo.['+@tableName+N'] where SourceID = @SourceID'; 
 
 exec sp_executesql @SQL,
 N'@SourceID int,@Rc int output',
 @SourceID,
 @Rc output;
 
 if @Rc % @PageSize > 0 
 set @PageCount = Cast(@Rc / @PageSize as int) + 1;
 else
 set @PageCount = Cast(@Rc / @PageSize as int);
 
 
 
 if @PageIndex = 1 
 begin
 set @SQL = N'select top '+Cast(@PageSize as nvarchar(30))+' '+@Fields + N' from dbo.['+@tableName+N'] where SourceID=@SourceID order by Lou desc'; 
 exec sp_executesql @SQL,
 N'@SourceID int',
 @SourceID;
 end 
 else
 begin
 declare @StartLou int;
 declare @EndLou int;
 
 --1 20 1 - 20,21- 40,41-60
 set @EndLou = @Rc - (@PageIndex-1) * @PageSize;
 
 if @EndLou > @Rc 
 set @EndLou = @Rc; 
 
 set @StartLou = @EndLou - @PageSize + 1;
 
 if @StartLou < 1 
 set @StartLou = 1;
 
 
 set @SQL = N'select '+@Fields + N' from dbo.['+@tableName+N'] where Lou>=@StartLou and Lou<=@EndLou and SourceID = @SourceID order by Lou desc';
 
 
 exec sp_executesql @SQL,
 N'@SourceID int,@StartLou int,@EndLou int',
 @SourceID,@StartLou,@EndLou;
 end
 
end
 
 
 
 
GO
 
 
 
 
 
 
 
 
 
 
 
--踩
 
Create proc [dbo].[procCai]
(
@ID int,
@SourceID int,
@key nvarchar(50),
@Times int output
)
as
begin
 declare @tableName nvarchar(80);
 set @tableName = dbo.funGetTableName(@SourceID,@key);
 
 declare @SQL nvarchar(MAX);
 set @SQL = N'update dbo.['+@tableName+N'] set Cai=Cai+1 where ID=@ID;select @Times=Cai from dbo.['+@tableName+N'] where ID=@ID';
 
 
 exec sp_executesql @SQL,
 N'@ID int,@Times int output',
 @ID,
 @Times output;
end
 
 
GO
 
 
--顶
create proc [dbo].[procDing]
(
@ID int,
@SourceID int,
@key nvarchar(50),
@Times int output
)
as
begin
 declare @tableName nvarchar(80);
 set @tableName = dbo.funGetTableName(@SourceID,@key);
 
 declare @SQL nvarchar(MAX);
 set @SQL = N'update dbo.['+@tableName+N'] set Ding=Ding+1 where ID=@ID;select @Times=Ding from dbo.['+@tableName+N'] where ID=@ID';
 
 
 exec sp_executesql @SQL,
 N'@ID int,@Times int output',
 @ID,
 @Times output;
end
 
GO