PLAYLISTS_STRUCTURE.sql

-------------------------------------------------------------------------------------------------
-- PLAYLISTS_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'PLAYLISTS') BEGIN
	PRINT N'❌ TABLE [PLAYLISTS] IS ALREADY EXISTS';
END ELSE BEGIN
	PRINT N'➕ TABLE [PLAYLISTS] IS NOT EXISTS AND WILL BE CREATED';
    CREATE TABLE [PLAYLISTS] (
		[UID] UNIQUEIDENTIFIER NOT NULL,
		[TITLE] NVARCHAR(50) NOT NULL,
        [DESCRIPTION] NVARCHAR(250) NULL,
        [CREATED_DT] DATETIME NOT NULL,
        [USER_UID] UNIQUEIDENTIFIER NOT NULL, 
        [ACCESS_UID] UNIQUEIDENTIFIER NOT NULL,
	);
    PRINT N'➕ TABLE [PLAYLISTS] WAS CREATED';
END;

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

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

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

-- DEFAULT
IF NOT EXISTS (SELECT 1 FROM [SYS].[DEFAULT_CONSTRAINTS] WHERE [NAME] = 'DF_PLAYLISTS_UID') BEGIN
	ALTER TABLE [PLAYLISTS] ADD CONSTRAINT [DF_PLAYLISTS_UID] DEFAULT (NEWID()) FOR [UID];
	PRINT N'➕ DEFAULT KEY [DF_PLAYLISTS_UID] WAS CREATED';
END;
IF NOT EXISTS (SELECT 1 FROM [SYS].[DEFAULT_CONSTRAINTS] WHERE [NAME] = 'DF_PLAYLISTS_CREATED_DT') BEGIN
    ALTER TABLE [PLAYLISTS] ADD CONSTRAINT [DF_PLAYLISTS_CREATED_DT] DEFAULT (GETDATE()) FOR [CREATED_DT];
    PRINT N'➕ DEFAULT KEY [DF_PLAYLISTS_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;