Tuesday, 23 April 2013

Version Controlling of Database Object

Introduction

I got a lot of publish related to source code version controlling. But not get any database object version controlling posts.
To understand what i mean to say please read this case study.

Case study
Take a scenario that we have 10 clients and we maintain the same database objects for all of our clients. We need some version controlling for our client database objects. Only the higher version script of DB objects can be compiled and create object in the database not the lower version.

What to do
Here we are using SQL server extended property to solve this problem. In our case we are taking Trigger as a database object.

Creating version control SP

-- [ Version Control Procedure ]
IF OBJECT_ID('UP_VERSIONCONTROL') IS NOT NULL
   BEGIN
     DROP PROCEDURE UP_VERSIONCONTROL;
   END
GO  
CREATE PROCEDURE UP_VERSIONCONTROL
  (
    @param_Type       VARCHAR(1)   = NULL, -- A-Addition D-Deletin U-Updation
    @param_TblObject  VARCHAR(MAX) = NULL,
    @param_TrgObject  VARCHAR(MAX) = NULL,
    @param_Version    VARCHAR(50)  = NULL
   )
AS
DECLARE @v_VersionDescr sql_variant;
BEGIN
   SET @v_VersionDescr = @param_Version;
  
   IF ISNULL(@param_Type,'')= 'A'
      BEGIN 
                 EXEC sys.sp_addextendedproperty
                           @name = N'MS_DescriptionExample',
                           @value = @v_VersionDescr,
                           @level0type = N'SCHEMA', @level0name = 'dbo',
                           @level1type = N'TABLE',  @level1name = @param_TblObject,
                           @level2type = N'TRIGGER',@level2name = @param_TrgObject;
         END               
   ELSE IF ISNULL(@param_Type,'')= 'D'  
           BEGIN
              EXEC sp_dropextendedproperty
                   @name = N'MS_DescriptionExample',
                   @level0type = N'SCHEMA', @level0name = 'dbo',
                   @level1type = N'TABLE',  @level1name = @param_TblObject,
                              @level2type = N'TRIGGER',@level2name = @param_TrgObject;
           END
        ELSE IF ISNULL(@param_Type,'')= 'U'
                BEGIN
                   EXEC sp_dropextendedproperty
                                         @name = N'MS_DescriptionExample',
                                         @level0type = N'SCHEMA', @level0name = 'dbo',
                                         @level1type = N'TABLE',  @level1name = @param_TblObject,
                                         @level2type = N'TRIGGER',@level2name = @param_TrgObject;
                                        
                              EXEC sys.sp_addextendedproperty
                                         @name = N'MS_DescriptionExample',
                                         @value = @v_VersionDescr,
                                         @level0type = N'SCHEMA', @level0name = 'dbo',
                                         @level1type = N'TABLE',  @level1name = @param_TblObject,
                                         @level2type = N'TRIGGER',@level2name = @param_TrgObject;            
                END 
             ELSE IF ISNULL(@param_Type,'')= 'R'
                     BEGIN
                         SELECT * FROM fn_listextendedproperty('MS_DescriptionExample',
                                                               'SCHEMA',
                                                               'dbo',
                                                               'TABLE',
                                                               @param_TblObject,
                                                               'TRIGGER',
                                                               @param_TrgObject);
                                                               
                     END
END  

Create the Base Table

IF OBJECT_ID('TBL_TESTING') IS NOT NULL
   BEGIN
     DROP TABLE TBL_TESTING;
   END
GO  
CREATE TABLE TBL_TESTING
       ( ID     INT           IDENTITY PRIMARY KEY NOT NULL,
         STNAME VARCHAR(50)   NOT NULL,
         MARKS  DECIMAL(20,0) NULL);
GO

INSERT INTO TBL_TESTING
            (STNAME)
VALUES ('JOYDEEP'),('RAJESH'),('RAHIM');           
GO

Creating Trigger Object

-- [ Creating Trigger Objects ]

DECLARE @VERSION_NO DECIMAL(20);
SET @VERSION_NO = 200;

IF OBJECT_ID('TRG_TESTING') IS NOT NULL
   BEGIN
     IF OBJECT_ID('TMP_VERSIONCTRL') IS NOT NULL
        BEGIN
           DROP TABLE TMP_VERSIONCTRL;
        END
     CREATE TABLE TMP_VERSIONCTRL
          (TYPEOFOBJECT    sql_variant,
           NAMEOFOBJECT    sql_variant,
           OBJECTNAME         sql_variant,
           VERSIONNO       sql_variant);
          
     INSERT INTO TMP_VERSIONCTRL
     EXEC UP_VERSIONCONTROL
                @param_Type       = 'R',
                @param_TblObject  = 'TBL_TESTING',
                @param_TrgObject  = 'TRG_TESTING'
               
    
     IF NOT EXISTS(SELECT * FROM TMP_VERSIONCTRL)
        BEGIN
           DROP TRIGGER TRG_TESTING;
        END
     ELSE
        BEGIN 
           IF (SELECT CONVERT(DECIMAL, VERSIONNO) FROM TMP_VERSIONCTRL) < @VERSION_NO
              BEGIN
                DROP TRIGGER TRG_TESTING
              END
           ELSE
              BEGIN
                PRINT 'Can NOT detete Trigger as Version control restricts for Lower version Install'
              END  
        END   
   END
GO 

CREATE TRIGGER TRG_TESTING
   ON  TBL_TESTING
   AFTER INSERT
AS
BEGIN
  IF EXISTS(SELECT * FROM inserted)
     BEGIN
                UPDATE TRG_TESTING SET MARKS = 50
                WHERE  ID=(SELECT ID FROM inserted);
     END
END
GO
-- Contain When the Trigger run first time
EXEC UP_VERSIONCONTROL
     @param_Type       = 'R',
     @param_TblObject  = 'TBL_TESTING',
     @param_TrgObject  = 'TRG_TESTING',
     @param_Version    = '100'
    
GO

To understand the above T-SQL please read it carefully. It's self explanatory.

Hope you like it.

Posted by: MR. JOYDEEP DAS

1 comment:

  1. Please make some comments related to it. If have you another view, please share it with us.

    ReplyDelete