VIDEOS_STRUCTURE.sql

-------------------------------------------------------------------------------------------------
-- VIDEOS_STRUCTURE
-------------------------------------------------------------------------------------------------
SET NOCOUNT ON;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE @IS_COMMIT BIT = 0;
-------------------------------------------------------------------------------------------------
USE VideoHosting;

BEGIN TRAN
PRINT N'➕ JOB IS STARTED';

IF EXISTS (SELECT * FROM [sys].[tables] WHERE [name] = N'VIDEOS') BEGIN
	PRINT N'❌ TABLE [VIDEOS] IS ALREADY EXISTS';
END ELSE BEGIN
	PRINT N'➕ TABLE [VIDEOS] IS NOT EXISTS AND WILL BE CREATED';
    CREATE TABLE [VIDEOS] (
		[UID] UNIQUEIDENTIFIER NOT NULL,
		[TITLE] NVARCHAR(100) NOT NULL,
        [DESCRIPTION] NVARCHAR(250) NULL,
        [PATH] NVARCHAR(125) NOT NULL,
        [CREATED_DT] DATETIME NOT NULL,
        [USER_UID] UNIQUEIDENTIFIER NOT NULL, 
        [ACCESS_UID] UNIQUEIDENTIFIER NOT NULL,
        [AGE_RESTRICTIONS_UID] UNIQUEIDENTIFIER NOT NULL,
	);
    PRINT N'➕ TABLE [VIDEOS] WAS CREATED';
END;

-- PRIMARY KEY
IF NOT EXISTS (SELECT 1 FROM [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] WHERE [CONSTRAINT_TYPE] = 'PRIMARY KEY' AND [TABLE_NAME] = N'VIDEOS') BEGIN
    ALTER TABLE [dbo].[VIDEOS] ADD CONSTRAINT [PK_VIDEOS_UID] PRIMARY KEY CLUSTERED ([UID] ASC)
        PRINT N'➕ PRIMARY KEY [PK_VIDEOS_UID] WAS CREATED';
END;

-- FOREIGN KEY
IF NOT EXISTS (SELECT 1 FROM [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] WHERE [CONSTRAINT_NAME] = 'FK_VIDEOS_USER_UID') BEGIN
	ALTER TABLE [VIDEOS] ADD CONSTRAINT [FK_VIDEOS_USER_UID] 
        FOREIGN KEY([USER_UID]) REFERENCES [USERS] ([UID]);
	PRINT N'➕ FOREIGN KEY [FK_VIDEOS_USER_UID] WAS CREATED';
END;
IF NOT EXISTS (SELECT 1 FROM [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] WHERE [CONSTRAINT_NAME] = 'FK_VIDEOS_USER_ACCESS_UID') BEGIN
	ALTER TABLE [VIDEOS] ADD CONSTRAINT [FK_VIDEOS_USER_ACCESS_UID] 
		FOREIGN KEY([ACCESS_UID]) REFERENCES [ACCESS_LEVELS] ([UID]);
	PRINT N'➕ FOREIGN KEY [FK_VIDEOS_USER_ACCESS_UID] WAS CREATED';
END;
IF NOT EXISTS (SELECT 1 FROM [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] WHERE [CONSTRAINT_NAME] = 'FK_VIDEOS_AGE_UID') BEGIN
	ALTER TABLE [VIDEOS] ADD CONSTRAINT [FK_VIDEOS_AGE_UID]
		FOREIGN KEY([AGE_RESTRICTIONS_UID]) REFERENCES [AGE_RESTRICTIONS] ([UID]);
	PRINT N'➕ FOREIGN KEY [FK_VIDEOS_AGE_UID] WAS CREATED';
END;

-- UNIQUE
IF NOT EXISTS (SELECT 1 FROM [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] WHERE [CONSTRAINT_NAME] = 'UQ_VIDEOS_TITLE_USER_UID' AND [TABLE_NAME] = N'VIDEOS') BEGIN
    ALTER TABLE [VIDEOS] ADD CONSTRAINT [UQ_VIDEOS_TITLE_USER_UID] UNIQUE([USER_UID], [TITLE])
    PRINT N'➕ UNIQUE KEY [UQ_VIDEOS_TITLE_USER_UID] WAS CREATED';
END;

-- DEFAULT
IF NOT EXISTS (SELECT 1 FROM [SYS].[DEFAULT_CONSTRAINTS] WHERE [NAME] = 'DF_VIDEOS_UID') BEGIN
	ALTER TABLE [VIDEOS] ADD CONSTRAINT [DF_VIDEOS_UID] DEFAULT (NEWID()) FOR [UID];
	PRINT N'➕ DEFAULT KEY [DF_VIDEOS_UID] WAS CREATED';
END;
IF NOT EXISTS (SELECT 1 FROM [SYS].[DEFAULT_CONSTRAINTS] WHERE [NAME] = 'DF_VIDEOS_CREATED_DT') BEGIN
    ALTER TABLE [VIDEOS] ADD CONSTRAINT [DF_VIDEOS_CREATED_DT] DEFAULT (GETDATE()) FOR [CREATED_DT];
    PRINT N'➕ DEFAULT KEY [DF_VIDEOS_CREATED_DT] WAS CREATED';
END;

IF (@IS_COMMIT = 1) BEGIN
	COMMIT TRAN
	PRINT N'➕ STRUCTURE IS COMMITTED';
END ELSE BEGIN
    ROLLBACK TRAN
	PRINT N'❌ STRUCTURE IS ROLL-BACKED';
END;