SQLServer2005 创建矢集快照数据库基本要素

Author:水如烟

矢集快照

--  =============================================
--  Author:LzmTW
--  Create date:20080101
--  Description:创建矢集快照数据库基本要素
--  =============================================
CREATE   PROCEDURE   [ Helper ] . [ p_CreateLDateDb ]  
     @DatabaseName   nvarchar ( 20 )
AS
BEGIN
     SET  NOCOUNT  ON ;

     DECLARE  
              @SQL   nvarchar ( MAX )
            , @IsExists   bit

     -- 库若存在，则返回
     SET   @SQL   =  N '
    USE [master];
    SELECT @IsExists = 
        CASE
            WHEN [name] IS NULL THEN 0
            ELSE 1
        END
    FROM sys.databases WHERE name = @DatabaseName '

     EXEC  sp_executesql  @SQL , N ' @IsExists bit OUT, @DatabaseName nvarchar(20) ' ,  @IsExists  OUT,  @DatabaseName

     IF   @IsExists   =   1   RETURN  

     -- 生成库
     SET   @DatabaseName   =   QUOTENAME ( @DatabaseName )

     SET   @SQL   =  N '
    CREATE DATABASE @DatabaseName '
    
     SET   @SQL   =   REPLACE ( @SQL , N ' @DatabaseName ' ,  @DatabaseName )

     EXEC  sp_executesql  @SQL

     -- 添加[Private]、[Helper]命名空间
     -- 添加[LDateDefault]默认值
     -- 添加[LDate]数据类型并设默认值为[LDateDefault]

     SET   @SQL =  N '
    USE @DatabaseName
    EXEC sp_executesql N '' CREATE SCHEMA [Private] AUTHORIZATION [dbo] ''
    EXEC sp_executesql N '' CREATE SCHEMA [Helper] AUTHORIZATION [dbo] ''
    EXEC sp_executesql N '' CREATE DEFAULT [dbo].[LDateDefault] AS N '''' Current ''''''
    EXEC sp_executesql N '' CREATE TYPE [dbo].[LDate] FROM [nvarchar](19) NOT NULL ''
    EXEC sp_bindefault @defname=N '' [dbo].[LDateDefault] '' , @objname=N '' [dbo].[LDate] '''

     SET   @SQL   =   REPLACE ( @SQL , N ' @DatabaseName ' ,  @DatabaseName )

     EXEC  sp_executesql  @SQL

     -- 创建标量值函数，返回查询矢集快照矢轴采样值的SQL语句
     SET   @SQL =  N '
    USE @DatabaseName

EXEC sp_executesql N '' CREATE FUNCTION [dbo].[GetCreateLdateFunctionStatement]
(
     @FunctionNamespace nvarchar(50)= N '''' dbo ''''
    ,@FunctionName nvarchar(50)
    ,@LDateTableNamespace nvarchar(50)= N '''' dbo ''''
    ,@LDateTable nvarchar(50)
    ,@LDateTableID nvarchar(50)
)
RETURNS nvarchar(MAX)
AS
BEGIN
    DECLARE @SQL nvarchar(MAX)
    SET @SQL =N ''''
CREATE FUNCTION [@FunctionNamespace].[@FunctionName] 
(    
     @QueryTime    [dbo].[LDate] = N '''''''' Current ''''''''
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT a.*
    FROM [@LDateTableNamespace].[@LDateTable] a
    RIGHT OUTER JOIN
    (
        SELECT     [@LDateTableID]
                ,MIN([Last])AS QueryTime
        FROM    [@LDateTableNamespace].[@LDateTable]
        WHERE   @QueryTime BETWEEN [First] AND [Last]
        GROUP BY [First], [Last], [@LDateTableID]
    ) b
    ON a.[@LDateTableID]= b.[@LDateTableID] AND a.Last = b.QueryTime
)
''''

    SET @SQL = REPLACE(@SQL, N '''' @FunctionNamespace '''' , @FunctionNamespace)
    SET @SQL = REPLACE(@SQL, N '''' @FunctionName '''' , @FunctionName)
    SET @SQL = REPLACE(@SQL, N '''' @LDateTableNamespace '''' , @LDateTableNamespace)
    SET @SQL = REPLACE(@SQL, N '''' @LDateTableID '''' , @LDateTableID)
    SET @SQL = REPLACE(@SQL, N '''' @LDateTable '''' , @LDateTable)

    RETURN @SQL
END '''

     SET   @SQL   =   REPLACE ( @SQL , N ' @DatabaseName ' ,  @DatabaseName )

     EXEC  sp_executesql  @SQL
    
     -- 存储过程,创建时间矢集快照
     SET   @SQL =  N '
    USE @DatabaseName

EXEC sp_executesql N '' CREATE PROCEDURE [dbo].[CreateLDateTable]
     @Database nvarchar(50)
    ,@Namespace nvarchar(50)= N '''' dbo ''''
    ,@Table nvarchar(50)
    ,@TableID nvarchar(50)                /*属性*/
    ,@TableIDType varchar(20)
    ,@Character    nvarchar(50) = NULL        /*特性*/
    ,@CharacterType varchar(20) = NULL
AS
BEGIN
    DECLARE @SQL nvarchar(MAX)
    SET @SQL =N ''''
USE @Database ''''

    IF @Character IS NULL OR LEN(@Character) = 0
        SET @SQL = @SQL + N ''''

    CREATE TABLE [@Namespace].[@Table](
        [ID] int IDENTITY(1,1),
        [@TableID] @TableIDType NOT NULL,
        [Last] [dbo].[LDate] NOT NULL,
        [First] [dbo].[LDate] NOT NULL,
     CONSTRAINT [PK_@Table] PRIMARY KEY CLUSTERED 
    (
        [@TableID] ASC,
        [Last] ASC
    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY] ''''
    ELSE
        SET @SQL = @SQL + N ''''

    CREATE TABLE [@Namespace].[@Table](
        [ID] int IDENTITY(1,1),
        [@TableID] @TableIDType NOT NULL,
        [Last] [dbo].[LDate] NOT NULL,
        [First] [dbo].[LDate] NOT NULL,
        [@Character] @CharacterType,
     CONSTRAINT [PK_@Table] PRIMARY KEY CLUSTERED 
    (
        [@TableID] ASC,
        [Last] ASC
    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY] ''''

    SET @SQL = @SQL + N ''''

EXEC sys.sp_bindefault @defname=N '''''''' [dbo].[LDateDefault] '''''''' , @objname=N '''''''' [@Namespace].[@Table].[Last] ''''''''  , @futureonly= '''''''' futureonly ''''''''

EXEC sys.sp_bindefault @defname=N '''''''' [dbo].[LDateDefault] '''''''' , @objname=N '''''''' [@Namespace].[@Table].[First] ''''''''  , @futureonly= '''''''' futureonly ''''''''

ALTER TABLE [@Namespace].[@Table]  WITH CHECK ADD  CONSTRAINT [CK_@Table_First] CHECK  ((isdate([First])=(1)))

ALTER TABLE [@Namespace].[@Table] CHECK CONSTRAINT [CK_@Table_First]

ALTER TABLE [@Namespace].[@Table]  WITH CHECK ADD  CONSTRAINT [CK_@Table_Last] CHECK  ((isdate([Last])=(1) OR [Last]= '''''''' Current ''''''''  AND [Last] >= [First] ))

ALTER TABLE [@Namespace].[@Table] CHECK CONSTRAINT [CK_@Table_Last]
''''
    SET @SQL = REPLACE(@SQL, N '''' @Database '''' , @Database)
    SET @SQL = REPLACE(@SQL, N '''' @Namespace '''' , @Namespace)
    SET @SQL = REPLACE(@SQL, N '''' @TableIDType '''' , @TableIDType)
    SET @SQL = REPLACE(@SQL, N '''' @TableID '''' , @TableID)
    SET @SQL = REPLACE(@SQL, N '''' @Table '''' , @Table)

    IF NOT( @Character IS NULL OR LEN(@Character) = 0)
    BEGIN
        SET @SQL = REPLACE(@SQL, N '''' @CharacterType '''' , @CharacterType)
        SET @SQL = REPLACE(@SQL, N '''' @Character '''' , @Character)
    END

    EXEC(@SQL)
END '''

     SET   @SQL   =   REPLACE ( @SQL , N ' @DatabaseName ' ,  @DatabaseName )

     EXEC  sp_executesql  @SQL

     -- 创建存储过程，对时间矢集快照进行数据行更新
     SET   @SQL =  N '
    USE @DatabaseName

EXEC sp_executesql N '' CREATE PROCEDURE [dbo].[CreateLDateTableRowUpdate]
     @Database nvarchar(50)
    ,@Namespace nvarchar(50)= N '''' dbo ''''
    ,@Table nvarchar(50)
    ,@TableID nvarchar(50)
    ,@TableIDType varchar(20)
    ,@Character nvarchar(20)
    ,@CharacterType varchar(20)

AS
BEGIN
    DECLARE 
        @SQL nvarchar(MAX)

    SET @SQL = N ''''
USE %Database%

EXEC sp_executesql N ''''''''
-- =====================================================================
-- Author:    LzmTW
-- Create date: 20080105
-- Description:    时间矢集快照单行数据的更新
-- @%TableID%:属性
-- @%Character%:属性特性值
-- @Date:采样时间
-- @IsBreak:是否撤销属性
-- @LDateUnit:时间矢量轴单位,年、月、日、时、分、秒(yy,mm,dd,hh,mi,ss)
-- =====================================================================
CREATE PROCEDURE [%Namespace%].[p_%Table%RowUpdate]
     @%TableID% %TableIDType%
    ,@%Character% %CharacterType% = NULL
    ,@Date Datetime
    ,@IsBreak bit = 0
    ,@LDateUnit char(2) =  '''''''''''''''' dd ''''''''''''''''
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
         @MaxFirst LDate
        ,@LDate LDate

        ,@Current%Character% %CharacterType%
        ,@CurrentFirst varchar(19)
        ,@CurrentID int

        ,@PreLDate LDate
        ,@Pre%Character% %CharacterType%
        ,@PreID int
        
    SET @LDate= [dbo].[DateToLDate](@Date, 0, @LDateUnit)
    SET @PreLDate = [dbo].[DateToLDate](@Date, -1, @LDateUnit)

    --当前要更新的数据其采样点时间不能小于数据集中最后的采样点时间
    SELECT @MaxFirst = MAX(First)
    FROM [%Namespace%].[%Table%]

    IF NOT @MaxFirst IS NULL AND @MaxFirst > @LDate
        RETURN -1     

    --撤销属性
    IF @IsBreak = 1
    BEGIN
        SET @CurrentID = NULL

        SELECT
            @CurrentID = [ID]
        FROM [%Namespace%].[f_%Table%](@LDate)
        WHERE %TableID% = @%TableID%

        IF @CurrentID IS NULL
            RETURN 1

        UPDATE [%Namespace%].[%Table%]
        SET Last = @PreLDate
        WHERE [ID] = @CurrentID

        RETURN 0
    END

    --如果是新属性，先检查是否回滚上次的撤销属性(即维持上一特性值)，若不是回滚，则插入新属性，包括其特性值
    IF NOT EXISTS(
        SELECT %TableID% 
        FROM [%Namespace%].[f_%Table%](@LDate) 
        WHERE %TableID% = @%TableID% )
    BEGIN
        SET @Pre%Character% = NULL
        SET @PreID = NULL

        SELECT
             @Pre%Character% = %Character%
            ,@PreID = [ID]
        FROM [%Namespace%].[f_%Table%](@PreLDate)
        WHERE %TableID% = @%TableID%

        IF (NOT @PreID IS NULL) AND (@Pre%Character% = @%Character% OR
        (@Pre%Character% IS NULL AND @%Character% IS NULL))
        BEGIN
            UPDATE [%Namespace%].[%Table%] /*回滚上次的撤销属性*/
            SET Last =  '''''''''''''''' Current ''''''''''''''''
            WHERE [ID] = @PreID

            RETURN 0
        END

        INSERT INTO [%Namespace%].[%Table%] /*插入新属性*/
        (%TableID%, Last, First, %Character%)
        VALUES(@%TableID%,  '''''''''''''''' Current '''''''''''''''' , @LDate, @%Character%)

        RETURN 0
    END
    
    --以下允许更新最新的特性值
    SET @CurrentID = NULL
    SET @Current%Character% = NULL
    SET @CurrentFirst = NULL

    SELECT
         @Current%Character% = %Character%
        ,@CurrentFirst = First
        ,@CurrentID = [ID]
    FROM [%Namespace%].[f_%Table%](@LDate)
    WHERE %TableID% = @%TableID%

    IF @Current%Character% = @%Character% OR 
        (@Current%Character% IS NULL AND @%Character% IS NULL)
        RETURN 0    /*如果特性值相等，不作处理*/

    SET @Pre%Character% = NULL
    SET @PreID = NULL

    SELECT
         @Pre%Character% = %Character%
        ,@PreID = [ID]
    FROM [%Namespace%].[f_%Table%](@PreLDate)
    WHERE %TableID% = @%TableID%    

    IF (NOT @PreID IS NULL) AND @LDate = @CurrentFirst AND (@Pre%Character% = @%Character% OR
    (@Pre%Character% IS NULL AND @%Character% IS NULL))
    BEGIN /*重新维持上一采样点的特性值*/
        DELETE FROM [%Namespace%].[%Table%]
        WHERE [ID] = @CurrentID

        UPDATE [%Namespace%].[%Table%] 
        SET Last =  '''''''''''''''' Current ''''''''''''''''
        WHERE [ID] = @PreID

        RETURN 0
    END

    IF @LDate = @CurrentFirst /*时间相同，更新最新的特性值*/
    BEGIN
        UPDATE [%Namespace%].[%Table%] 
        SET %Character% = @%Character%
        WHERE [ID] = @CurrentID

        RETURN 0
    END

    IF @LDate <> @CurrentFirst /*时间不同，插入新属性*/
    BEGIN
        UPDATE [%Namespace%].[%Table%] 
        SET Last = @PreLDate
        WHERE [ID] = @CurrentID

        INSERT INTO [%Namespace%].[%Table%] 
        (%TableID%, Last, First, %Character%)
        VALUES(@%TableID%,  '''''''''''''''' Current '''''''''''''''' , @LDate, @%Character%)        

        RETURN 0
    END
END ''''''''''''

    SET @SQL = REPLACE(@SQL, N '''' %CharacterType% '''' , @CharacterType)
    SET @SQL = REPLACE(@SQL, N '''' %Character% '''' , @Character)
    SET @SQL = REPLACE(@SQL, N '''' %TableIDType% '''' , @TableIDType)
    SET @SQL = REPLACE(@SQL, N '''' %TableID% '''' , @TableID)
    SET @SQL = REPLACE(@SQL, N '''' %Table% '''' , @Table)
    SET @SQL = REPLACE(@SQL, N '''' %Namespace% '''' , @Namespace)
    SET @SQL = REPLACE(@SQL, N '''' %Database% '''' , @Database)

    EXEC sp_executesql @SQL
END '''

     SET   @SQL   =   REPLACE ( @SQL , N ' @DatabaseName ' ,  @DatabaseName )

     EXEC  sp_executesql  @SQL

     -- 创建存储过程，可以在本地数据库创建时间矢集快照，及其查询函数，数据行更新存储过程
     SET   @SQL =  N '
    USE @DatabaseName

EXEC dbo.sp_executesql N '' CREATE PROCEDURE [dbo].[CurrentDbCreateLDateTable]
     @Namespace nvarchar(50)= N '''' dbo ''''
    ,@Table nvarchar(50)
    ,@TableID nvarchar(50)
    ,@TableIDType varchar(20)
    ,@Character nvarchar(50) = NULL
    ,@CharacterType varchar(20) = NULL
AS
BEGIN
    DECLARE 
         @Database nvarchar(50)
        ,@SQL nvarchar(MAX)

    SET @Database = N '''' @DatabaseName ''''

    EXEC [dbo].[CreateLDateTable] 
         @Database
        ,@Namespace
        ,@Table
        ,@TableID
        ,@TableIDType
        ,@Character
        ,@CharacterType

    SELECT @SQL = [dbo].[GetCreateLdateFunctionStatement] (
         @Namespace
        ,N '''' f_ ''''  + @Table
        ,@Namespace
        ,@Table
        ,@TableID)
    
    EXEC(@SQL)

    IF NOT @Character IS NULL
    EXEC    [dbo].[CreateLDateTableRowUpdate]
            @Database,
            @Namespace,
            @Table,
            @TableID,
            @TableIDType,
            @Character,
            @CharacterType 

END '''

     SET   @SQL   =   REPLACE ( @SQL , N ' @DatabaseName ' ,  @DatabaseName )

     EXEC  sp_executesql  @SQL

     -- 创建函数,将时间转换为时间矢轴上的点值
     SET   @SQL =  N '
    USE @DatabaseName

EXEC sp_executesql N '' -- =================================================================
-- Author:        LzmTW
-- Create date: 20080105
-- Description:    将时间转换为时间矢轴上的点值
-- @Date: 采样时间
-- @Add: 增加值
-- @Unit: 矢轴单位，年、月、日、时、分、秒(yy,mm,dd,hh,mi,ss)
-- =================================================================
CREATE FUNCTION [dbo].[DateToLDate]
(
     @Date datetime
    ,@Add int = 0
    ,@Unit char(2) =  '''' dd ''''
)
RETURNS varchar(19)
AS
BEGIN
    DECLARE 
         @Result varchar(19)
        ,@NowDate datetime

    SET @NowDate =
        CASE @Unit
            WHEN  '''' yy ''''  THEN DATEADD(yy, @Add, @Date)
            WHEN  '''' mm ''''  THEN DATEADD(mm, @Add, @Date)
            WHEN  '''' dd ''''  THEN DATEADD(dd, @Add, @Date)
            WHEN  '''' hh ''''  THEN DATEADD(hh, @Add, @Date)
            WHEN  '''' mi ''''  THEN DATEADD(mi, @Add, @Date)
            WHEN  '''' ss ''''  THEN DATEADD(ss, @Add, @Date)
            ELSE NULL
        END

    IF @Unit IN ( '''' yy '''' ,  '''' mm '''' ,  '''' dd '''' )
        SET @Result = CONVERT(varchar(8), @NowDate, 112)


    IF @Unit IN ( '''' hh '''' ,  '''' mi '''' ,  '''' ss '''' )
        SET @Result =REPLACE(CONVERT(varchar(19), @NowDate, 120),  '''' - '''' ,  '''''''' )

    IF @Unit IN ( '''' hh '''' ,  '''' mi '''' )
        SET @Result = STUFF(@Result, 16, 2,  '''' 00 '''' )

    IF @Unit IN ( '''' hh '''' )
        SET @Result = STUFF(@Result, 13, 2,  '''' 00 '''' )

    RETURN @Result
END '''

     SET   @SQL   =   REPLACE ( @SQL , N ' @DatabaseName ' ,  @DatabaseName )

     EXEC  sp_executesql  @SQL

     -- 创建函数,将时间矢轴上的点值转换为时间
     SET   @SQL =  N '
    USE @DatabaseName

EXEC sp_executesql N '' -- =================================================================
-- Author:        LzmTW
-- Create date: 20080105
-- Description:    将时间矢轴上的点值转换为时间
-- @LDate: 矢轴点值
-- @Add: 增加值
-- @Unit: 矢轴单位，年、月、日、时、分、秒(yy,mm,dd,hh,mi,ss)
-- =================================================================
CREATE FUNCTION [dbo].[LDateToDate]
(
     @LDate varchar(19)
    ,@Add int = 0
    ,@Unit char(2) =  '''' dd ''''
)
RETURNS datetime
AS
BEGIN
    DECLARE 
         @Result datetime

    SET @Result =
        CASE @Unit
            WHEN  '''' yy ''''  THEN DATEADD(yy, @Add, @LDate)
            WHEN  '''' mm ''''  THEN DATEADD(mm, @Add, @LDate)
            WHEN  '''' dd ''''  THEN DATEADD(dd, @Add, @LDate)
            WHEN  '''' hh ''''  THEN DATEADD(hh, @Add, @LDate)
            WHEN  '''' mi ''''  THEN DATEADD(mi, @Add, @LDate)
            WHEN  '''' ss ''''  THEN DATEADD(ss, @Add, @LDate)
            ELSE  '''' Current ''''  /*非有效值发出错误*/
        END

    RETURN @Result
END '''

     SET   @SQL   =   REPLACE ( @SQL , N ' @DatabaseName ' ,  @DatabaseName )

     EXEC  sp_executesql  @SQL
END