/******************************************************
 *
 * Name:         step03-create-azure-sqldb-schema.sql
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     03-17-2018
 *     Purpose:  Create the schema for the [db4stocks] database.
 * 
 ******************************************************/

--
-- Create the database
--

-- Delete existing database
/*

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'db4stocks')
DROP DATABASE [db4stocks]
GO

*/

-- Create new database
/*

CREATE DATABASE [db4stocks]
(
MAXSIZE = 1GB,
EDITION = 'STANDARD',
SERVICE_OBJECTIVE = 'S1'
)
GO   

*/


--
-- Create the partition function
--

-- Which database to use.
USE db4stocks
GO


-- Drop existing partition function
IF  EXISTS (SELECT name FROM sys.partition_functions WHERE name = N'PF_HASH_BY_VALUE')
DROP PARTITION FUNCTION PF_HASH_BY_VALUE
GO

-- Create new partition function
CREATE PARTITION FUNCTION PF_HASH_BY_VALUE (INT) AS RANGE LEFT 
FOR VALUES 
(
  201204, 
  201301,   201302,   201303,   201304, 
  201401,   201402,   201403,   201404, 
  201501,   201502,   201503,   201504, 
  201601,   201602,   201603,   201604, 
  201701,   201702,   201703,   201704
)
GO

-- Show the function
SELECT * FROM sys.partition_functions
GO



--
-- Create the partition scheme
--

-- Drop existing partition scheme
IF  EXISTS (SELECT name FROM sys.partition_schemes WHERE name = N'PS_HASH_BY_VALUE')
DROP PARTITION SCHEME PS_HASH_BY_VALUE
GO

-- Create the partition scheme
CREATE PARTITION SCHEME PS_HASH_BY_VALUE 
AS PARTITION PF_HASH_BY_VALUE
ALL TO ([PRIMARY]);
GO

-- Show the scheme
SELECT * FROM sys.partition_schemes
GO


--
-- Values stored per partition
--

-- Test partition function
SELECT 
  MY_VALUE,
  $PARTITION.PF_HASH_BY_VALUE(MY_VALUE) AS HASH_IDX
FROM 
(
 VALUES 
   (201203),    (201204),   
   (201301),    (201302),    (201303),    (201304),   
   (201401),    (201402),    (201403),    (201404), 
   (201501),    (201502),    (201503),    (201504), 
   (201601),    (201602),    (201603),    (201604), 
   (201701),    (201702),    (201703),    (201704), 
   (201801),    (201802)
) AS TEST (MY_VALUE);
GO


--
-- Create ACTIVE schema
--

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


--
-- Create STAGE schema
--

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


--
-- Create ACTIVE table
--
 
-- Delete existing table
DROP TABLE IF EXISTS [ACTIVE].[STOCKS]
GO
 
-- Create new table
CREATE TABLE [ACTIVE].[STOCKS]
(
  ST_ID INT IDENTITY(1, 1) NOT NULL,
  ST_SYMBOL VARCHAR(32) NOT NULL,
  ST_DATE DATE NOT NULL,
  ST_OPEN REAL NULL,
  ST_HIGH REAL NULL,
  ST_LOW REAL NULL,
  ST_CLOSE REAL NULL,
  ST_ADJ_CLOSE REAL NULL,
  ST_VOLUME BIGINT NULL,
  ST_SKEY AS ( DATEPART("YY", ST_DATE) * 100 + DATEPART("qq", ST_DATE) ) PERSISTED,
  CONSTRAINT [PK_STOCKS_ID] PRIMARY KEY CLUSTERED (ST_SKEY, ST_ID ASC)
)
ON PS_HASH_BY_VALUE ([ST_SKEY]) WITH (DATA_COMPRESSION = PAGE);
GO


--
-- Create STAGE table
--
 
-- Delete existing table
DROP TABLE IF EXISTS [STAGE].[STOCKS]
GO
 
-- Create new table
CREATE TABLE [STAGE].[STOCKS]
(
  ST_SYMBOL VARCHAR(32) NOT NULL,
  ST_DATE VARCHAR(32) NOT NULL,
  ST_OPEN VARCHAR(32) NULL,
  ST_HIGH VARCHAR(32) NULL,
  ST_LOW VARCHAR(32) NULL,
  ST_CLOSE VARCHAR(32) NULL,
  ST_ADJ_CLOSE VARCHAR(32) NULL,
  ST_VOLUME VARCHAR(32) NULL
)

-- Truncate the table
TRUNCATE TABLE [STAGE].[STOCKS]

-- Show the data
SELECT * FROM [STAGE].[STOCKS]
GO


--
-- Create audit table
--

-- Delete existing table
DROP TABLE IF EXISTS [STAGE].[AUDIT]
GO

-- Create new table
CREATE TABLE [STAGE].[AUDIT]
(
	AU_CHANGE_ID INT IDENTITY (1,1) NOT NULL,
	AU_CHANGE_DATE [datetime] NOT NULL,
	AU_CMD_TEXT [varchar](1024) NOT NULL,
	AU_CHANGE_BY [nvarchar](256) NOT NULL,
	AU_APP_NAME [nvarchar](128) NOT NULL,
	AU_HOST_NAME [nvarchar](128) NOT NULL,

    CONSTRAINT [PK_CHANGE_ID] PRIMARY KEY CLUSTERED (AU_CHANGE_ID ASC)
) 
GO

-- Add defaults for key information
ALTER TABLE [STAGE].[AUDIT]
ADD CONSTRAINT [DF_CHANGE_DATE] DEFAULT (GETDATE()) FOR AU_CHANGE_DATE;

ALTER TABLE [STAGE].[AUDIT]
ADD CONSTRAINT [DF_CHANGE_TEXT] DEFAULT ('') FOR AU_CMD_TEXT;

ALTER TABLE [STAGE].[AUDIT]
ADD CONSTRAINT [DF_CHANGE_BY] DEFAULT (COALESCE(SUSER_SNAME(),'?')) FOR AU_CHANGE_BY;

ALTER TABLE [STAGE].[AUDIT]
ADD CONSTRAINT [DF_APP_NAME] DEFAULT (COALESCE(APP_NAME(),'?')) FOR AU_APP_NAME;

ALTER TABLE [STAGE].[AUDIT]
ADD CONSTRAINT [DF_HOST_NAME] DEFAULT (COALESCE(CAST(CONNECTIONPROPERTY('client_net_address') AS nvarchar(128)),'?')) FOR AU_HOST_NAME;
GO
 

-- Test the table
INSERT INTO [STAGE].[AUDIT] (AU_CMD_TEXT) VALUES ('First test case.');
GO


-- Show the data
SELECT * FROM [STAGE].[AUDIT];
GO


--
-- Show database objects
--

SELECT *
FROM sys.objects
WHERE is_ms_shipped = 0;


--
-- Using compression to save space
--

-- Remove temporary table
DROP TABLE IF EXISTS #TBL_SIZE
GO

-- Create temporary table
CREATE TABLE #TBL_SIZE
(
    id int identity(1,1),
	name varchar(64),
    rows varchar(64),
	reserved varchar(64),
	data varchar(64),
	index_size varchar(64),
	unused varchar(64)
)
GO

-- Truncate the table
TRUNCATE TABLE #TBL_SIZE
GO

-- Try no compression 
ALTER TABLE [STAGE].[STOCKS]
REBUILD WITH (DATA_COMPRESSION = NONE);
GO

-- No compression
INSERT INTO #TBL_SIZE
exec sp_spaceused 'stage.stocks';
GO


-- Try Row Compression 
ALTER TABLE [STAGE].[STOCKS]
REBUILD WITH (DATA_COMPRESSION = ROW);
GO

-- Row compression
INSERT INTO #TBL_SIZE
exec sp_spaceused 'stage.stocks';
GO


-- Try Page Compression 
ALTER TABLE [STAGE].[STOCKS]
REBUILD WITH (DATA_COMPRESSION = PAGE);

-- Page compression
INSERT INTO #TBL_SIZE
exec sp_spaceused 'stage.stocks';
GO


-- Show the various data compression
SELECT 
    * 
	, -(cast(replace(reserved, ' KB', '') AS REAL) - 13064.00) / 13064.00 * 100.00 as PCT_SAVED 
FROM 
    #TBL_SIZE
GO


-- Get record count
SELECT COUNT(*) FROM STAGE.STOCKS

--
-- Two steps for data import
--

-- Move data from stage to active
INSERT INTO ACTIVE.STOCKS
SELECT  
    CAST(S.ST_SYMBOL AS VARCHAR(32)) AS ST_SYMBOL,
    CAST(S.ST_DATE AS DATE) AS ST_DATE,
    CAST(S.ST_OPEN AS REAL) AS ST_OPEN,
    CAST(S.ST_HIGH AS REAL) AS ST_HIGH,
    CAST(S.ST_LOW AS REAL) AS ST_LOW,
    CAST(S.ST_CLOSE AS REAL) AS ST_CLOSE,
    CAST(S.ST_ADJ_CLOSE AS REAL) AS ST_ADJ_CLOSE,
    CAST(S.ST_VOLUME AS BIGINT) AS ST_VOLUME
FROM 
    STAGE.STOCKS AS S
WHERE 
    (S.ST_VOLUME <> 'volume') AND
	(S.ST_VOLUME <> '0')
GO


-- Clear the staging table
TRUNCATE TABLE STAGE.STOCKS


--
-- Validate data placememt
--

-- Use dmv to get partitions
SELECT 
  Partition_Number, Row_Count
FROM sys.dm_db_partition_stats
WHERE object_id = object_id('ACTIVE.STOCKS'); 


-- Using the $PARTITION function
SELECT 
    $PARTITION.PF_HASH_BY_VALUE([ST_SKEY]) as Partition_Number, 
    COUNT(*) as Row_Count
FROM 
    db4stocks.active.stocks
GROUP BY 
    $PARTITION.PF_HASH_BY_VALUE([ST_SKEY]);


-- Final Table Size
exec sp_spaceused 'stage.stocks';
