text: ﻿/*
  Deployment script for TimesheetApp
  
  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 "TimesheetApp"
  :setvar DefaultFilePrefix "TimesheetApp"
  :setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\"
  :setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\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
  /*
  The column [dbo].[PROJECTS].[CUSTOMER_ID] is being dropped, data loss could occur.
  
  The column [dbo].[PROJECTS].[CLIENT_ID] on table [dbo].[PROJECTS] must be added, but the column has no default value and does not allow NULL values. If the table contains data, the ALTER script will not work. To avoid this issue you must either: add a default value to the column, mark it as allowing NULL values, or enable the generation of smart-defaults as a deployment option.
  */
  
  IF EXISTS (select top 1 1 from [dbo].[PROJECTS])
      RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT
  
  GO
  PRINT N'Dropping Default Constraint unnamed constraint on [dbo].[PROJECTS]...';
  
  
  GO
  ALTER TABLE [dbo].[PROJECTS] DROP CONSTRAINT [DF__PROJECTS__PROJEC__32E0915F];
  
  
  GO
  PRINT N'Dropping Default Constraint unnamed constraint on [dbo].[PROJECTS]...';
  
  
  GO
  ALTER TABLE [dbo].[PROJECTS] DROP CONSTRAINT [DF__PROJECTS__AUTO_A__33D4B598];
  
  
  GO
  PRINT N'Dropping Default Constraint unnamed constraint on [dbo].[PROJECTS]...';
  
  
  GO
  ALTER TABLE [dbo].[PROJECTS] DROP CONSTRAINT [DF__PROJECTS__CREATE__34C8D9D1];
  
  
  GO
  PRINT N'Dropping Foreign Key unnamed constraint on [dbo].[PROJECT_ASSIGNMENTS]...';
  
  
  GO
  ALTER TABLE [dbo].[PROJECT_ASSIGNMENTS] DROP CONSTRAINT [FK__PROJECT_A__PROJE__398D8EEE];
  
  
  GO
  PRINT N'Dropping Foreign Key unnamed constraint on [dbo].[PROJECTS]...';
  
  
  GO
  ALTER TABLE [dbo].[PROJECTS] DROP CONSTRAINT [FK__PROJECTS__USER_I__3A81B327];
  
  
  GO
  PRINT N'Dropping Foreign Key unnamed constraint on [dbo].[PROJECTS]...';
  
  
  GO
  ALTER TABLE [dbo].[PROJECTS] DROP CONSTRAINT [FK__PROJECTS__CUSTOM__3B75D760];
  
  
  GO
  PRINT N'Dropping Foreign Key unnamed constraint on [dbo].[TIMESHEET_ENTRIES]...';
  
  
  GO
  ALTER TABLE [dbo].[TIMESHEET_ENTRIES] DROP CONSTRAINT [FK__TIMESHEET__PROJE__3D5E1FD2];
  
  
  GO
  PRINT N'Starting rebuilding table [dbo].[PROJECTS]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  BEGIN TRANSACTION;
  
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  
  SET XACT_ABORT ON;
  
  CREATE TABLE [dbo].[tmp_ms_xx_PROJECTS] (
      [PROJECT_ID]              UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL,
      [PROJECT_NAME]            VARCHAR (50)     NOT NULL,
      [USER_ID]                 INT              NOT NULL,
      [CLIENT_ID]               UNIQUEIDENTIFIER NOT NULL,
      [AUTO_ASSIGN_TO_ALL_FLAG] BIT              DEFAULT 1 NOT NULL,
      [CREATE_DATETIME]         DATETIME         DEFAULT GETDATE() NOT NULL,
      [UPDATE_DATETIME]         DATETIME         NULL,
      PRIMARY KEY CLUSTERED ([PROJECT_ID] ASC)
  );
  
  IF EXISTS (SELECT TOP 1 1 
             FROM   [dbo].[PROJECTS])
      BEGIN
          INSERT INTO [dbo].[tmp_ms_xx_PROJECTS] ([PROJECT_ID], [PROJECT_NAME], [USER_ID], [AUTO_ASSIGN_TO_ALL_FLAG], [CREATE_DATETIME], [UPDATE_DATETIME])
          SELECT   [PROJECT_ID],
                   [PROJECT_NAME],
                   [USER_ID],
                   [AUTO_ASSIGN_TO_ALL_FLAG],
                   [CREATE_DATETIME],
                   [UPDATE_DATETIME]
          FROM     [dbo].[PROJECTS]
          ORDER BY [PROJECT_ID] ASC;
      END
  
  DROP TABLE [dbo].[PROJECTS];
  
  EXECUTE sp_rename N'[dbo].[tmp_ms_xx_PROJECTS]', N'PROJECTS';
  
  COMMIT TRANSACTION;
  
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating Table [dbo].[CLIENTS]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE TABLE [dbo].[CLIENTS] (
      [CLIENT_ID]       UNIQUEIDENTIFIER NOT NULL,
      [USER_ID]         INT              NOT NULL,
      [CUSTOMER_NAME]   VARCHAR (30)     NOT NULL,
      [CREATE_DATETIME] DATETIME         NOT NULL,
      [UPDATE_DATETIME] DATETIME         NULL,
      PRIMARY KEY CLUSTERED ([CLIENT_ID] ASC)
  );
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating Default Constraint unnamed constraint on [dbo].[CLIENTS]...';
  
  
  GO
  ALTER TABLE [dbo].[CLIENTS]
      ADD DEFAULT NEWID() FOR [CLIENT_ID];
  
  
  GO
  PRINT N'Creating Default Constraint unnamed constraint on [dbo].[CLIENTS]...';
  
  
  GO
  ALTER TABLE [dbo].[CLIENTS]
      ADD DEFAULT GETDATE() FOR [CREATE_DATETIME];
  
  
  GO
  PRINT N'Creating Foreign Key unnamed constraint on [dbo].[PROJECT_ASSIGNMENTS]...';
  
  
  GO
  ALTER TABLE [dbo].[PROJECT_ASSIGNMENTS] WITH NOCHECK
      ADD FOREIGN KEY ([PROJECT_ID]) REFERENCES [dbo].[PROJECTS] ([PROJECT_ID]);
  
  
  GO
  PRINT N'Creating Foreign Key unnamed constraint on [dbo].[PROJECTS]...';
  
  
  GO
  ALTER TABLE [dbo].[PROJECTS] WITH NOCHECK
      ADD FOREIGN KEY ([USER_ID]) REFERENCES [dbo].[USERS] ([USER_ID]);
  
  
  GO
  PRINT N'Creating Foreign Key unnamed constraint on [dbo].[PROJECTS]...';
  
  
  GO
  ALTER TABLE [dbo].[PROJECTS] WITH NOCHECK
      ADD FOREIGN KEY ([CLIENT_ID]) REFERENCES [dbo].[CLIENTS] ([CLIENT_ID]);
  
  
  GO
  PRINT N'Creating Foreign Key unnamed constraint on [dbo].[TIMESHEET_ENTRIES]...';
  
  
  GO
  ALTER TABLE [dbo].[TIMESHEET_ENTRIES] WITH NOCHECK
      ADD FOREIGN KEY ([PROJECT_ID]) REFERENCES [dbo].[PROJECTS] ([PROJECT_ID]);
  
  
  GO
  PRINT N'Creating Foreign Key unnamed constraint on [dbo].[CLIENTS]...';
  
  
  GO
  ALTER TABLE [dbo].[CLIENTS] WITH NOCHECK
      ADD FOREIGN KEY ([USER_ID]) REFERENCES [dbo].[USERS] ([USER_ID]);
  
  
  GO
  PRINT N'Checking existing data against newly created constraints';
  
  
  GO
  USE [$(DatabaseName)];
  
  
  GO
  CREATE TABLE [#__checkStatus] (
      id           INT            IDENTITY (1, 1) PRIMARY KEY CLUSTERED,
      [Schema]     NVARCHAR (256),
      [Table]      NVARCHAR (256),
      [Constraint] NVARCHAR (256)
  );
  
  SET NOCOUNT ON;
  
  DECLARE tableconstraintnames CURSOR LOCAL FORWARD_ONLY
      FOR SELECT SCHEMA_NAME([schema_id]),
                 OBJECT_NAME([parent_object_id]),
                 [name],
                 0
          FROM   [sys].[objects]
          WHERE  [parent_object_id] IN (OBJECT_ID(N'dbo.PROJECT_ASSIGNMENTS'), OBJECT_ID(N'dbo.PROJECTS'), OBJECT_ID(N'dbo.TIMESHEET_ENTRIES'), OBJECT_ID(N'dbo.CLIENTS'))
                 AND [type] IN (N'F', N'C')
                     AND [object_id] IN (SELECT [object_id]
                                         FROM   [sys].[check_constraints]
                                         WHERE  [is_not_trusted] <> 0
                                                AND [is_disabled] = 0
                                         UNION
                                         SELECT [object_id]
                                         FROM   [sys].[foreign_keys]
                                         WHERE  [is_not_trusted] <> 0
                                                AND [is_disabled] = 0);
  
  DECLARE @schemaname AS NVARCHAR (256);
  
  DECLARE @tablename AS NVARCHAR (256);
  
  DECLARE @checkname AS NVARCHAR (256);
  
  DECLARE @is_not_trusted AS INT;
  
  DECLARE @statement AS NVARCHAR (1024);
  
  BEGIN TRY
      OPEN tableconstraintnames;
      FETCH tableconstraintnames INTO @schemaname, @tablename, @checkname, @is_not_trusted;
      WHILE @@fetch_status = 0
          BEGIN
              PRINT N'Checking constraint: ' + @checkname + N' [' + @schemaname + N'].[' + @tablename + N']';
              SET @statement = N'ALTER TABLE [' + @schemaname + N'].[' + @tablename + N'] WITH ' + CASE @is_not_trusted WHEN 0 THEN N'CHECK' ELSE N'NOCHECK' END + N' CHECK CONSTRAINT [' + @checkname + N']';
              BEGIN TRY
                  EXECUTE [sp_executesql] @statement;
              END TRY
              BEGIN CATCH
                  INSERT  [#__checkStatus] ([Schema], [Table], [Constraint])
                  VALUES                  (@schemaname, @tablename, @checkname);
              END CATCH
              FETCH tableconstraintnames INTO @schemaname, @tablename, @checkname, @is_not_trusted;
          END
  END TRY
  BEGIN CATCH
      PRINT ERROR_MESSAGE();
  END CATCH
  
  IF CURSOR_STATUS(N'LOCAL', N'tableconstraintnames') >= 0
      CLOSE tableconstraintnames;
  
  IF CURSOR_STATUS(N'LOCAL', N'tableconstraintnames') = -1
      DEALLOCATE tableconstraintnames;
  
  SELECT N'Constraint verification failed:' + [Schema] + N'.' + [Table] + N',' + [Constraint]
  FROM   [#__checkStatus];
  
  IF @@ROWCOUNT > 0
      BEGIN
          DROP TABLE [#__checkStatus];
          RAISERROR (N'An error occurred while verifying constraints', 16, 127);
      END
  
  SET NOCOUNT OFF;
  
  DROP TABLE [#__checkStatus];
  
  
  GO
  PRINT N'Update complete.';
  
  
  GO
  