/******************************************************
 *
 * Name:         step04-implement-sliding-window.sql
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     03-17-2018
 *     Purpose:  Create the schema for the [db4stocks] database.
 * 
 ******************************************************/


--
-- 1 - Create hash key table for sliding window
--

-- Delete existing table
DROP TABLE IF EXISTS [ACTIVE].[PARTITIONS]
GO

-- Create new table
CREATE TABLE [ACTIVE].[PARTITIONS]
(
   part_id int not null identity (1, 1),
   part_hash_key int not null
);
GO

-- Alter the table (primary key)
ALTER TABLE [ACTIVE].[PARTITIONS]
   ADD CONSTRAINT PK_PARTITIONS_ID PRIMARY KEY CLUSTERED (part_id);
GO

-- Alter the table (primary key)
ALTER TABLE [ACTIVE].[PARTITIONS]
   ADD CONSTRAINT UNQ_PARTITIONS_HASH UNIQUE (part_hash_key);
GO

-- Add data to the table
INSERT INTO [ACTIVE].[PARTITIONS] (part_hash_key)
VALUES
    (201101),
    (201102),
    (201103),
    (201104),
    (201201),
    (201202),
    (201203),
    (201204),
    (201301),
    (201302),
    (201303),
    (201304),
    (201401),
    (201402),
    (201403),
    (201404),
    (201501),
    (201502),
    (201503),
    (201504),
    (201601),
    (201602),
    (201603),
    (201604),
    (201701),
    (201702),
    (201703),
    (201704),
    (201801),
    (201802),
    (201803),
    (201804),
    (201901),
    (201902),
    (201903),
    (201904);

	
--
-- 2 - Make two helper functions (kudos to Kalen Delaney for base code)
-- 

-- Drop existing function
DROP FUNCTION IF EXISTS [ACTIVE].[UFN_GET_INDEX_NAME]
GO

-- Get the name of an index by ordinal position
EXEC('CREATE FUNCTION [ACTIVE].[UFN_GET_INDEX_NAME] (@object_id int, @index_id tinyint)
RETURNS sysname
AS
BEGIN
    RETURN
	(
    SELECT name 
    FROM sys.indexes
    WHERE object_id = @object_id and index_id = @index_id
    )
END;');
GO

-- Drop existing view
DROP VIEW IF EXISTS [ACTIVE].[UFN_GET_PARTITION_INFO]
GO

-- Return all partition information for the database
CREATE VIEW [ACTIVE].[UFN_GET_PARTITION_INFO] AS
    SELECT 
        OBJECT_NAME(i.[object_id]) AS ObjectName, 
        (SELECT [ACTIVE].[UFN_GET_INDEX_NAME] (i.[object_id], i.index_id)) AS IndexName, 
	    p.[partition_number] AS PartitionNo, 
	    fg.[name] AS FileGroupName, 
	    p.[rows] AS TotalRows, 
	    au.[total_pages] AS TotalPages,
	    CASE pf.[boundary_value_on_right] 
            WHEN 1 THEN 'less than' 
            ELSE 'less than or equal to' 
        END AS CompareType, 
        rv.[value] AS CompareValue

    FROM 
        sys.partitions p JOIN sys.indexes i
	        ON p.[object_id] = i.[object_id] AND 
			   p.[index_id] = i.[index_id]
        JOIN sys.partition_schemes ps 
            ON ps.[data_space_id] = i.[data_space_id]
        JOIN sys.partition_functions pf 
		    ON pf.[function_id] = ps.[function_id]
        LEFT JOIN sys.partition_range_values rv    
            ON pf.[function_id] = rv.[function_id] AND 
			   p.[partition_number] = rv.[boundary_id]
        JOIN sys.destination_data_spaces dds
		    ON dds.[partition_scheme_id] = ps.[data_space_id] AND 
			   dds.[destination_id] = p.[partition_number]
 	    JOIN sys.filegroups fg 
		    ON dds.[data_space_id] = fg.[data_space_id]
	    JOIN 
	      (
	         SELECT 
			     [container_id], 
				 sum([total_pages]) as total_pages 
		     FROM 
			     sys.allocation_units
		     GROUP BY 
			     [container_id]
		  ) AS au
		    ON au.[container_id] = p.[partition_id]
    WHERE 
	    i.[index_id] < 2;
GO

-- Show partition compare values
SELECT * 
FROM [ACTIVE].[UFN_GET_PARTITION_INFO] AS TVF
WHERE TVF.PartitionNo IN (1, 2, 3, 20, 21, 22)


--
-- 3 - Create a function to add a new partition
--

-- Delete existing procedure
DROP PROCEDURE IF EXISTS [ACTIVE].[USP_ADD_STOCKS_PARTITION]
GO

-- Create new procedure
CREATE PROCEDURE [ACTIVE].[USP_ADD_STOCKS_PARTITION]
AS

    -- No info messages
    SET NOCOUNT ON;

    -- Declare variables
    DECLARE @VAR_VAL1 INT;
    DECLARE @VAR_STR1 VARCHAR(128);
    DECLARE @VAR_SQL1 VARCHAR(MAX);

    -- Get the next max value
    SELECT 
        @VAR_VAL1 = [part_id],
        @VAR_STR1 = [part_hash_key]
    FROM 
        [ACTIVE].[PARTITIONS] AS P1
    WHERE
	    P1.part_id = 
	(		 
        SELECT 
            P2.[part_id] + 1
        FROM 
            [ACTIVE].[PARTITIONS] AS P2
        WHERE
	        P2.[part_hash_key] =
	    (
            SELECT CONVERT(INT, MAX(P3.CompareValue)) 
            FROM [ACTIVE].[UFN_GET_PARTITION_INFO] as P3
	    )
    );      

	-- Set as new place to store data
    SET @VAR_SQL1 = '';
    SET @VAR_SQL1 = @VAR_SQL1 + 'ALTER PARTITION SCHEME PS_HASH_BY_VALUE ';    
    SET @VAR_SQL1 = @VAR_SQL1 + 'NEXT USED [PRIMARY];';    
    PRINT @VAR_SQL1;
    EXEC(@VAR_SQL1);

    -- Add at new partition at max value
    SET @VAR_SQL1 = '';
    SET @VAR_SQL1 = @VAR_SQL1 + 'ALTER PARTITION FUNCTION PF_HASH_BY_VALUE () ';    
    SET @VAR_SQL1 = @VAR_SQL1 + 'SPLIT RANGE (' + LTRIM(RTRIM(@VAR_STR1)) + ');';   

    -- Show & Execute	 
    PRINT @VAR_SQL1;
    EXEC(@VAR_SQL1);

GO

-- Show new split range
SELECT * 
FROM [ACTIVE].[UFN_GET_PARTITION_INFO] AS TVF
WHERE TVF.PartitionNo > 20


--
-- 4 - Create TEMP schema
--

-- Delete existing schema.
DROP SCHEMA IF EXISTS [TEMP]
GO
 
-- Add new schema.
CREATE SCHEMA [TEMP] AUTHORIZATION [dbo]
GO



--
-- 5 - Create a function to add a new table
--

-- Delete existing procedure
DROP PROCEDURE IF EXISTS [ACTIVE].[USP_ADD_STOCKS_TEMP_TBL]
GO

-- Create new procedure
CREATE PROCEDURE [ACTIVE].[USP_ADD_STOCKS_TEMP_TBL]
AS

    -- No info messages
    SET NOCOUNT ON;

    -- Declare variables
    DECLARE @VAR_SQL2 VARCHAR(MAX);

    -- Create temp table on primary
    SET @VAR_SQL2 = '';

    SET @VAR_SQL2 = @VAR_SQL2 + 'CREATE TABLE [TEMP].[STOCKS] '
    SET @VAR_SQL2 = @VAR_SQL2 + '( '
    SET @VAR_SQL2 = @VAR_SQL2 + '  ST_ID INT IDENTITY(1, 1) NOT NULL, '
    SET @VAR_SQL2 = @VAR_SQL2 + '  ST_SYMBOL VARCHAR(32) NOT NULL, '
    SET @VAR_SQL2 = @VAR_SQL2 + '  ST_DATE DATE NOT NULL, '
    SET @VAR_SQL2 = @VAR_SQL2 + '  ST_OPEN REAL NULL, '
    SET @VAR_SQL2 = @VAR_SQL2 + '  ST_HIGH REAL NULL, '
    SET @VAR_SQL2 = @VAR_SQL2 + '  ST_LOW REAL NULL, '
    SET @VAR_SQL2 = @VAR_SQL2 + '  ST_CLOSE REAL NULL, '
    SET @VAR_SQL2 = @VAR_SQL2 + '  ST_ADJ_CLOSE REAL NULL, '
    SET @VAR_SQL2 = @VAR_SQL2 + '  ST_VOLUME BIGINT NULL, '
    SET @VAR_SQL2 = @VAR_SQL2 + '  ST_SKEY AS ( DATEPART("YY", ST_DATE) * 100 + DATEPART("qq", ST_DATE) ) PERSISTED, '
    SET @VAR_SQL2 = @VAR_SQL2 + '  CONSTRAINT [PK_STOCKS_TEMP_ID] PRIMARY KEY CLUSTERED (ST_SKEY, ST_ID ASC) '
    SET @VAR_SQL2 = @VAR_SQL2 + ') '
    SET @VAR_SQL2 = @VAR_SQL2 + 'ON [PRIMARY] WITH (DATA_COMPRESSION = PAGE); '
  
    -- Show & Execute
    PRINT @VAR_SQL2;
    EXEC(@VAR_SQL2);

GO


--
-- 6 - Create a function to drop a old partition
--

-- Delete existing procedure
DROP PROCEDURE IF EXISTS [ACTIVE].[USP_DEL_STOCKS_PARTITION]
GO

-- Create new procedure
CREATE PROCEDURE [ACTIVE].[USP_DEL_STOCKS_PARTITION]
AS
    -- No info messages
    SET NOCOUNT ON;

    -- Declare variables
    DECLARE @VAR_VAL3 INT;
    DECLARE @VAR_STR3 VARCHAR(128);  
    DECLARE @VAR_VAL4 INT;
    DECLARE @VAR_STR4 VARCHAR(128);    
    DECLARE @VAR_SQL5 VARCHAR(MAX);

    -- Get information for partition 1
    SELECT 
         @VAR_VAL3 = [part_id],
         @VAR_STR3 = [part_hash_key]
    FROM 
        [ACTIVE].[PARTITIONS] AS P1
    WHERE
	    P1.part_id = 
	(		 
        SELECT 
            P2.[part_id] + 0
        FROM 
            [ACTIVE].[PARTITIONS] AS P2
        WHERE
	        P2.[part_hash_key] =
	    (
            SELECT CONVERT(INT, MIN(P3.CompareValue)) 
            FROM [ACTIVE].[UFN_GET_PARTITION_INFO] as P3
	    )
    );  
		  
    -- Get information for partition 2
    SELECT 
         @VAR_VAL4 = [part_id],
         @VAR_STR4 = [part_hash_key]
    FROM 
        [ACTIVE].[PARTITIONS] AS P1
    WHERE
	    P1.part_id = 
	(		 
        SELECT 
            P2.[part_id] + 1
        FROM 
            [ACTIVE].[PARTITIONS] AS P2
        WHERE
	        P2.[part_hash_key] =
	    (
            SELECT CONVERT(INT, MIN(P3.CompareValue)) 
            FROM [ACTIVE].[UFN_GET_PARTITION_INFO] as P3
	    )
    );  

  -- Add new temporary table on partition 2
  SET @VAR_SQL5 = 'EXEC [ACTIVE].[USP_ADD_STOCKS_TEMP_TBL] ';
  PRINT @VAR_SQL5;
  EXEC(@VAR_SQL5);
          
  -- Move data from part 2 to temporary table
  SET @VAR_SQL5 = 'ALTER TABLE [ACTIVE].[STOCKS] SWITCH PARTITION 2 TO [TEMP].[STOCKS]';
  PRINT @VAR_SQL5;
  EXEC(@VAR_SQL5);

  -- Remove from function - part 1
  SET @VAR_SQL5 = 'ALTER PARTITION FUNCTION PF_HASH_BY_VALUE () MERGE RANGE (' + STR(@VAR_STR3) + ');';
  PRINT @VAR_SQL5;
  EXEC(@VAR_SQL5);

  -- Remove from function - part 2
  SET @VAR_SQL5 = 'ALTER PARTITION FUNCTION PF_HASH_BY_VALUE () MERGE RANGE (' + STR(@VAR_STR4) + ');';
  PRINT @VAR_SQL5;
  EXEC(@VAR_SQL5);

  -- drop the temporary table
  SET @VAR_SQL5 = 'DROP TABLE [TEMP].[STOCKS];'
  PRINT @VAR_SQL5;  
  EXEC(@VAR_SQL5);

  -- Set hospital before as new place to store data
  SET @VAR_SQL5 = 'ALTER PARTITION SCHEME PS_HASH_BY_VALUE NEXT USED [PRIMARY];';
  PRINT @VAR_SQL5;  
  EXEC(@VAR_SQL5);

  -- Split hospital on old value
  SET @VAR_SQL5 = 'ALTER PARTITION FUNCTION PF_HASH_BY_VALUE () SPLIT RANGE (' + @VAR_STR4 + ');';
  PRINT @VAR_SQL5;  
  EXEC(@VAR_SQL5);
    
GO


-- Delete oldest partition
[ACTIVE].[USP_DEL_STOCKS_PARTITION]


-- Show new split range
SELECT * 
FROM [ACTIVE].[UFN_GET_PARTITION_INFO] AS TVF
WHERE TVF.PartitionNo < 4
