/******************************************************
 *
 * Name:         autos-version5.sql
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     05-08-2012
 *     Purpose:  An example of DDL triggers used
 *               to track schema changes.
 * 
 ******************************************************/


/*  
	Create the new database.
*/

-- Start with master database
USE MASTER;
GO

-- Delete existing databases.
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'AUTOS')
DROP DATABASE [AUTOS]
GO

-- Create a autos database
CREATE DATABASE AUTOS;
GO

-- Use the database
USE [AUTOS]
GO


/*  
	Create the new schemas.  USA (United States) & ADT (Audit)
*/

-- Delete existing schema.
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'USA')
DROP SCHEMA [USA];
GO

-- Create a USA schema
CREATE SCHEMA [USA] AUTHORIZATION dbo;
GO

-- Delete existing schema.
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'ADT')
DROP SCHEMA [ADT];
GO

-- Create a ADT schema
CREATE SCHEMA [ADT] AUTHORIZATION dbo;
GO


/*  
	Create schema level auditing - table.
*/

-- Remove table if it exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ADT].[LOG_DDL_CHANGES]') AND type in (N'U'))
DROP TABLE [ADT].[LOG_DDL_CHANGES]
GO

-- Create a DDL Log Changes table
CREATE TABLE [ADT].[LOG_DDL_CHANGES]
(
	[ChangeId] BIGINT IDENTITY(1,1) NOT NULL,
	[ChangeDate] [datetime] NOT NULL,
	[ChangeType] [varchar](20) NOT NULL,
	[ChangeBy] [nvarchar](256) NOT NULL,
	[AppName] [nvarchar](128) NOT NULL,
	[HostName] [nvarchar](128) NOT NULL,
	[SchemaName] [sysname] NULL,
	[ObjectName] [sysname] NULL,
	[Tsql] nvarchar(MAX) NULL,
 CONSTRAINT [pk_Ldc_ChangeId] PRIMARY KEY CLUSTERED ([ChangeId] ASC)
) 
GO

-- Add defaults for key information
ALTER TABLE [ADT].[LOG_DDL_CHANGES] ADD CONSTRAINT [df_Ldc_ChangeDate] DEFAULT (getdate()) FOR [ChangeDate];
ALTER TABLE [ADT].[LOG_DDL_CHANGES] ADD CONSTRAINT [df_Ldc_ChangeType] DEFAULT ('') FOR [ChangeType];
ALTER TABLE [ADT].[LOG_DDL_CHANGES] ADD CONSTRAINT [df_Ldc_ChangeBy] DEFAULT (coalesce(suser_sname(),'?')) FOR [ChangeBy];
ALTER TABLE [ADT].[LOG_DDL_CHANGES] ADD CONSTRAINT [df_Ldc_AppName] DEFAULT (coalesce(APP_NAME(),'?')) FOR [AppName];
ALTER TABLE [ADT].[LOG_DDL_CHANGES] ADD CONSTRAINT [df_Ldc_HostName] DEFAULT (coalesce(HOST_NAME(),'?')) FOR [HostName];
GO


/*  
	Create schema level auditing - trigger.
*/

-- Remove trigger if it exists
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[TRG_TRACK_DDL_CHANGES]'))
DROP TRIGGER [TRG_TRACK_DDL_CHANGES]
GO

-- Add the trigger to the db
CREATE TRIGGER [TRG_TRACK_DDL_CHANGES] ON DATABASE 
FOR 
  DDL_TRIGGER_EVENTS, DDL_FUNCTION_EVENTS, DDL_PROCEDURE_EVENTS, DDL_TABLE_VIEW_EVENTS, DDL_DATABASE_SECURITY_EVENTS 
AS 
BEGIN

    -- Declare local variables
    DECLARE @VAR_DATA XML;
    DECLARE @VAR_SCHEMA sysname;
    DECLARE @VAR_OBJECT sysname; 
    DECLARE @VAR_EVENT_TYPE sysname;

    -- Set local variables
    SET @VAR_DATA = EVENTDATA();
    SET @VAR_EVENT_TYPE = @VAR_DATA.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
    SET @VAR_SCHEMA = @VAR_DATA.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    SET @VAR_OBJECT = @VAR_DATA.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname') 

    -- Do not track replication or statistics
	IF (( @VAR_EVENT_TYPE <> 'CREATE_STATISTICS') and ( @VAR_OBJECT not like 'syncobj_0x%')) 
	BEGIN
		INSERT [ADT].[LOG_DDL_CHANGES] ([ObjectName], [ChangeType], [Tsql], [SchemaName]) VALUES 
		( 
		    CONVERT(sysname, @VAR_OBJECT), 
		    @VAR_EVENT_TYPE, 
		    @VAR_DATA.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'), 
		    CONVERT(sysname, @VAR_SCHEMA)
		 );
	END

END;


/*  
	Status Code Table.
*/

-- Remove table if it exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[USA].[BRANDS]') AND type in (N'U'))
DROP TABLE [USA].[BRANDS]
GO

-- Create a BRANDS table
CREATE TABLE [USA].[BRANDS]
(
MyId INT PRIMARY KEY CLUSTERED,
MyValue VARCHAR(20)
)
GO

-- Load the table with data
INSERT INTO USA.BRANDS (MyId, MyValue) VALUES
(1, 'Continental'),
(2, 'Edsel'),
(3, 'Lincoln'),
(4, 'Mercury'),
(5, 'Ram')
GO


/*  
	Status Code Table.
*/

-- Remove table if it exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[USA].[STATUS_CODE]') AND type in (N'U'))
DROP TABLE [USA].[STATUS_CODE]
GO

-- Create a STATUS CODE table
CREATE TABLE USA.STATUS_CODE
(
MyId INT PRIMARY KEY CLUSTERED,
MyValue VARCHAR(20)
)
GO

-- Load the table with data
INSERT INTO USA.STATUS_CODE (MyId, MyValue) VALUES
(1, 'Completed'),
(2, 'Scraped'),
(3, 'Rework Needed')
GO


/*  
	Make Status Table.
*/

-- Remove table if it exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[USA].[MAKE_STATUS]') AND type in (N'U'))
DROP TABLE [USA].[MAKE_STATUS]
GO

-- Create MAKE STATUS table (Latest Record)
CREATE TABLE [USA].[MAKE_STATUS]
(
    [VehicleId] [varchar](17) NOT NULL,
    [BrandId] Int NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [FinishDate] [datetime] NULL,
    [MessageText] [varchar](max) NULL,
    [StatusCode] [int] NULL,

    [Duration] AS 
        (datediff(second,[StartDate],[FinishDate])) PERSISTED,
    CONSTRAINT [pk_Make_Status] PRIMARY KEY CLUSTERED 
    ( 
        [VehicleId] ASC,  
        [StartDate] ASC
    )
)
GO



/*  
	Make History Table.
*/

-- Remove table if it exists
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[USA].[MAKE_HISTORY]') AND type in (N'U'))
DROP TABLE [USA].[MAKE_HISTORY]
GO

-- Create MAKE HISTORY table (All Records)
CREATE TABLE [USA].[MAKE_HISTORY]
(
    [VehicleId] [varchar](17) NOT NULL,
    [BrandId] Int NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [FinishDate] [datetime] NULL,
    [MessageText] [varchar](max) NULL,
    [StatusCode] [int] NULL,

    [Duration] AS 
        (datediff(second,[StartDate],[FinishDate])) PERSISTED,
    CONSTRAINT [pk_Make_History] PRIMARY KEY CLUSTERED 
    ( 
        [VehicleId] ASC,  
        [StartDate] ASC
    )
)
GO


/*  
	Trigger to move data.
*/

-- Remove trigger if it exists
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[USA].[TRG_MAKE_STATUS_TO_HISTORY]'))
DROP TRIGGER [USA].[TRG_MAKE_STATUS_TO_HISTORY]
GO

-- Move modified record to history table
CREATE TRIGGER [USA].[TRG_MAKE_STATUS_TO_HISTORY] ON [USA].[MAKE_STATUS]
FOR INSERT, UPDATE
AS
    INSERT INTO [USA].[MAKE_HISTORY]
	SELECT
            [VehicleId],
            [BrandId],
            [StartDate],
            [FinishDate],
            [MessageText],
            [StatusCode]
	FROM inserted
GO



/*  
	Clear tables.
*/

-- Clear the tables
TRUNCATE TABLE [USA].[MAKE_STATUS]
GO

TRUNCATE TABLE [USA].[MAKE_HISTORY]
GO



/*  
	Create upsert procedure (use MERGE if > 2008)
*/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[USA].[UPSERT]') AND type in (N'P', N'PC'))
DROP PROCEDURE [USA].[UPSERT]
GO

-- Create the procedure
CREATE PROCEDURE [USA].[UPSERT] (
    @VehicleId [varchar](17),
    @BrandId [Int],
    @StartDate [datetime],
    @FinishDate [datetime],
    @MessageText [varchar](max),
    @StatusCode [int])
AS
BEGIN

    -- Worry about concurrency
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

    BEGIN TRAN
    
        -- Try the update first
        UPDATE [USA].[MAKE_STATUS] 
        SET 
            [BrandId] = @BrandId,
            [StartDate] = @StartDate,
            [FinishDate] = @FinishDate,
            [MessageText] = @MessageText,
            [StatusCode] = @StatusCode
        WHERE [VehicleId] = @VehicleId
          
        -- Did not update vehicle
        IF @@ROWCOUNT = 0        
        BEGIN
        
            -- Add new record
            INSERT INTO [USA].[MAKE_STATUS] SELECT
                @VehicleId,
                @BrandId,
                @StartDate,
                @FinishDate,
                @MessageText,
                @StatusCode
                
            -- Detected error
            IF @@ERROR <> 0
                ROLLBACK
               
        END
    
    -- Save the transcation
    COMMIT
    
END
GO


/*  
	Fictitious build of Lincoln Town Car.
*/

-- http://www.ehow.com/how-does_4928886_automobile-assembly-process.html
-- Typical assembly process for Lincoln Town Car (UPSERT)

-- Step 1
EXEC USA.UPSERT '1G1FP22PXS2100001', 3, '2011-04-01 00:00:00', '2011-04-01 00:01:40', 'Start with Frame', 1;
GO

-- Step 2
EXEC USA.UPSERT '1G1FP22PXS2100001', 3, '2011-04-02 00:00:00', '2011-04-02 00:03:20', 'Install Parts on Frame', 1;
GO

-- Step 3
EXEC USA.UPSERT '1G1FP22PXS2100001', 3, '2011-04-03 00:00:00', '2011-04-03 00:05:00', 'Install Engine and Transmission', 1;
GO

-- Step 4
EXEC USA.UPSERT '1G1FP22PXS2100001', 3, '2011-04-04 00:00:00', '2011-04-04 00:06:40', 'Building the Shell', 1;
GO

-- Step 5
EXEC USA.UPSERT '1G1FP22PXS2100001', 3, '2011-04-05 00:00:00', '2011-04-05 00:08:20', 'Final Body Components', 1;
GO

-- Step 6
EXEC USA.UPSERT '1G1FP22PXS2100001', 3, '2011-04-06 00:00:00', '2011-04-06 00:10:00', 'Inspection and Washing', 1;
GO

-- Step 7
EXEC USA.UPSERT '1G1FP22PXS2100001', 3, '2011-04-07 00:00:00', '2011-04-07 00:11:40', 'Paint', 1;
GO

-- Step 8
EXEC USA.UPSERT '1G1FP22PXS2100001', 3, '2011-04-08 00:00:00', '2011-04-08 00:13:20', 'Add Inner Workings', 1;
GO

-- Step 9
EXEC USA.UPSERT '1G1FP22PXS2100001', 3, '2011-04-09 00:00:00', '2011-04-09 00:15:00', 'Join Shell and Frame', 1;
GO

-- Step 10
EXEC USA.UPSERT '1G1FP22PXS2100001', 3, '2011-04-10 00:00:00', '2011-04-10 00:16:40', 'Test the Car', 2;
GO

-- Step 11
EXEC USA.UPSERT '1G1FP22PXS2100001', 3, '2011-04-11 00:00:00', '2011-04-11 00:18:20', 'Test the Car', 1;
GO