欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2023-12-24 14:13:45
...
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 =@StartLou and Lou

上一篇:

下一篇: