text: ﻿/*
  Deployment script for SAM5DB
  
  This code was generated by a tool.
  Changes to this file may cause incorrect behavior and will be lost if
  the code is regenerated.
  */
  
  GO
  SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
  
  SET NUMERIC_ROUNDABORT OFF;
  
  
  GO
  :setvar DatabaseName "SAM5DB"
  :setvar DefaultFilePrefix "SAM5DB"
  :setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL11.SAM\MSSQL\DATA\"
  :setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL11.SAM\MSSQL\DATA\"
  
  GO
  :on error exit
  GO
  /*
  Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
  To re-enable the script after enabling SQLCMD mode, execute the following:
  SET NOEXEC OFF; 
  */
  :setvar __IsSqlCmdEnabled "True"
  GO
  IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
      BEGIN
          PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
          SET NOEXEC ON;
      END
  
  
  GO
  USE [$(DatabaseName)];
  
  
  GO
  /*
   Pre-Deployment Script Template							
  --------------------------------------------------------------------------------------
   This file contains SQL statements that will be executed before the build script.	
   Use SQLCMD syntax to include a file in the pre-deployment script.			
   Example:      :r .\myfile.sql								
   Use SQLCMD syntax to reference a variable in the pre-deployment script.		
   Example:      :setvar TableName MyTable							
                 SELECT * FROM [$(TableName)]					
  --------------------------------------------------------------------------------------
  */
  print '$$$-predeployment-start-do-not-change-this-line-$$$'
  
  
  
  print '$$$-predeployment-end-do-not-change-this-line-$$$'
  GO
  
  GO
  PRINT N'Dropping [core].[fk_hierarchy_access_right]...';
  
  
  GO
  ALTER TABLE [core].[hierarchy] DROP CONSTRAINT [fk_hierarchy_access_right];
  
  
  GO
  PRINT N'Dropping [core].[fk_hierarchy_document_hierarchy]...';
  
  
  GO
  ALTER TABLE [core].[hierarchy_document] DROP CONSTRAINT [fk_hierarchy_document_hierarchy];
  
  
  GO
  /*
  The column [core].[hierarchy].[access_right_id] is being dropped, data loss could occur.
  */
  GO
  PRINT N'Starting rebuilding table [core].[hierarchy]...';
  
  
  GO
  BEGIN TRANSACTION;
  
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  
  SET XACT_ABORT ON;
  
  CREATE TABLE [core].[tmp_ms_xx_hierarchy] (
      [id]                INT            IDENTITY (1, 1) NOT NULL,
      [state]             SMALLINT       NOT NULL,
      [path]              VARCHAR (512)  NOT NULL,
      [name]              NVARCHAR (256) NOT NULL,
      [access_right_code] VARCHAR (128)  NULL,
      [modified_by]       INT            NOT NULL,
      [modified_date]     DATETIME2 (7)  NOT NULL,
      CONSTRAINT [tmp_ms_xx_constraint_pk_hierarchy1] PRIMARY KEY CLUSTERED ([id] ASC),
      CONSTRAINT [tmp_ms_xx_constraint_ak_hierarchy_path1] UNIQUE NONCLUSTERED ([path] ASC)
  );
  
  IF EXISTS (SELECT TOP 1 1 
             FROM   [core].[hierarchy])
      BEGIN
          SET IDENTITY_INSERT [core].[tmp_ms_xx_hierarchy] ON;
          INSERT INTO [core].[tmp_ms_xx_hierarchy] ([id], [state], [path], [name], [modified_by], [modified_date])
          SELECT   [id],
                   [state],
                   [path],
                   [name],
                   [modified_by],
                   [modified_date]
          FROM     [core].[hierarchy]
          ORDER BY [id] ASC;
          SET IDENTITY_INSERT [core].[tmp_ms_xx_hierarchy] OFF;
      END
  
  DROP TABLE [core].[hierarchy];
  
  EXECUTE sp_rename N'[core].[tmp_ms_xx_hierarchy]', N'hierarchy';
  
  EXECUTE sp_rename N'[core].[tmp_ms_xx_constraint_pk_hierarchy1]', N'pk_hierarchy', N'OBJECT';
  
  EXECUTE sp_rename N'[core].[tmp_ms_xx_constraint_ak_hierarchy_path1]', N'ak_hierarchy_path', N'OBJECT';
  
  COMMIT TRANSACTION;
  
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  
  
  GO
  PRINT N'Creating [core].[hierarchy_request]...';
  
  
  GO
  CREATE TABLE [core].[hierarchy_request] (
      [id]                  INT            IDENTITY (1, 1) NOT NULL,
      [hierarchy_id]        INT            NULL,
      [request_id]          INT            NOT NULL,
      [change_type_id]      CHAR (1)       NOT NULL,
      [state]               SMALLINT       NOT NULL,
      [name]                NVARCHAR (256) NOT NULL,
      [access_right_code]   VARCHAR (128)  NULL,
      [parent_hierarchy_id] INT            NULL,
      [modified_by]         INT            NOT NULL,
      [modified_date]       DATETIME2 (7)  NOT NULL,
      CONSTRAINT [pk_hierarchy_request] PRIMARY KEY CLUSTERED ([id] ASC)
  );
  
  
  GO
  PRINT N'Creating [core].[hierarchy_request_document]...';
  
  
  GO
  CREATE TABLE [core].[hierarchy_request_document] (
      [hierarchy_request_id] INT           NOT NULL,
      [document_id]          INT           NOT NULL,
      [document_type_id]     INT           NOT NULL,
      [modified_by]          INT           NOT NULL,
      [modified_date]        DATETIME2 (7) NOT NULL,
      CONSTRAINT [pk_hierarchy_request_document] PRIMARY KEY CLUSTERED ([hierarchy_request_id] ASC, [document_id] ASC, [document_type_id] ASC)
  );
  
  
  GO
  PRINT N'Creating [core].[fk_hierarchy_access_right]...';
  
  
  GO
  ALTER TABLE [core].[hierarchy] WITH NOCHECK
      ADD CONSTRAINT [fk_hierarchy_access_right] FOREIGN KEY ([access_right_code]) REFERENCES [sec].[access_right] ([code]);
  
  
  GO
  PRINT N'Creating [core].[fk_hierarchy_document_hierarchy]...';
  
  
  GO
  ALTER TABLE [core].[hierarchy_document] WITH NOCHECK
      ADD CONSTRAINT [fk_hierarchy_document_hierarchy] FOREIGN KEY ([hierarchy_id]) REFERENCES [core].[hierarchy] ([id]) ON DELETE CASCADE;
  
  
  GO
  PRINT N'Creating [core].[fk_hierarchy_request_hierarchy]...';
  
  
  GO
  ALTER TABLE [core].[hierarchy_request] WITH NOCHECK
      ADD CONSTRAINT [fk_hierarchy_request_hierarchy] FOREIGN KEY ([hierarchy_id]) REFERENCES [core].[hierarchy] ([id]);
  
  
  GO
  PRINT N'Creating [core].[fk_hierarchy_request_parent_hierarchy]...';
  
  
  GO
  ALTER TABLE [core].[hierarchy_request] WITH NOCHECK
      ADD CONSTRAINT [fk_hierarchy_request_parent_hierarchy] FOREIGN KEY ([parent_hierarchy_id]) REFERENCES [core].[hierarchy] ([id]);
  
  
  GO
  PRINT N'Creating [core].[fk_hierarchy_request_request]...';
  
  
  GO
  ALTER TABLE [core].[hierarchy_request] WITH NOCHECK
      ADD CONSTRAINT [fk_hierarchy_request_request] FOREIGN KEY ([request_id]) REFERENCES [req].[request] ([id]);
  
  
  GO
  PRINT N'Creating [core].[fk_hierarchy_request_access_right]...';
  
  
  GO
  ALTER TABLE [core].[hierarchy_request] WITH NOCHECK
      ADD CONSTRAINT [fk_hierarchy_request_access_right] FOREIGN KEY ([access_right_code]) REFERENCES [sec].[access_right] ([code]);
  
  
  GO
  PRINT N'Creating [core].[fk_hierarchy_request_document_hierarchy_request]...';
  
  
  GO
  ALTER TABLE [core].[hierarchy_request_document] WITH NOCHECK
      ADD CONSTRAINT [fk_hierarchy_request_document_hierarchy_request] FOREIGN KEY ([hierarchy_request_id]) REFERENCES [core].[hierarchy_request] ([id]) ON DELETE CASCADE;
  
  
  GO
  PRINT N'Creating [core].[fk_hierarchy_request_document_document]...';
  
  
  GO
  ALTER TABLE [core].[hierarchy_request_document] WITH NOCHECK
      ADD CONSTRAINT [fk_hierarchy_request_document_document] FOREIGN KEY ([document_type_id], [document_id]) REFERENCES [core].[document] ([document_type_id], [id]);
  
  
  GO
  PRINT N'Creating [core].[fk_hierarchy_request_document_document_type]...';
  
  
  GO
  ALTER TABLE [core].[hierarchy_request_document] WITH NOCHECK
      ADD CONSTRAINT [fk_hierarchy_request_document_document_type] FOREIGN KEY ([document_type_id]) REFERENCES [metadata].[document_type] ([id]);
  
  
  GO
  /*
  Post-Deployment Script Template							
  --------------------------------------------------------------------------------------
   This file contains SQL statements that will be appended to the build script.		
   Use SQLCMD syntax to include a file in the post-deployment script.			
   Example:      :r .\myfile.sql								
   Use SQLCMD syntax to reference a variable in the post-deployment script.		
   Example:      :setvar TableName MyTable							
                 SELECT * FROM [$(TableName)]					
  --------------------------------------------------------------------------------------
  */
  print '$$$-postdeployment-start-do-not-change-this-line-$$$'
  
  
  print '$$$-postdeployment-end-do-not-change-this-line-$$$'
  GO
  
  GO
  PRINT N'Checking existing data against newly created constraints';
  
  
  GO
  USE [$(DatabaseName)];
  
  
  GO
  ALTER TABLE [core].[hierarchy] WITH CHECK CHECK CONSTRAINT [fk_hierarchy_access_right];
  
  ALTER TABLE [core].[hierarchy_document] WITH CHECK CHECK CONSTRAINT [fk_hierarchy_document_hierarchy];
  
  ALTER TABLE [core].[hierarchy_request] WITH CHECK CHECK CONSTRAINT [fk_hierarchy_request_hierarchy];
  
  ALTER TABLE [core].[hierarchy_request] WITH CHECK CHECK CONSTRAINT [fk_hierarchy_request_parent_hierarchy];
  
  ALTER TABLE [core].[hierarchy_request] WITH CHECK CHECK CONSTRAINT [fk_hierarchy_request_request];
  
  ALTER TABLE [core].[hierarchy_request] WITH CHECK CHECK CONSTRAINT [fk_hierarchy_request_access_right];
  
  ALTER TABLE [core].[hierarchy_request_document] WITH CHECK CHECK CONSTRAINT [fk_hierarchy_request_document_hierarchy_request];
  
  ALTER TABLE [core].[hierarchy_request_document] WITH CHECK CHECK CONSTRAINT [fk_hierarchy_request_document_document];
  
  ALTER TABLE [core].[hierarchy_request_document] WITH CHECK CHECK CONSTRAINT [fk_hierarchy_request_document_document_type];
  
  
  GO
  PRINT N'Update complete.';
  
  
  GO
  