text: ﻿/*
  Deployment script for ObservationsElwandle
  
  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 "ObservationsElwandle"
  :setvar DefaultFilePrefix "ObservationsElwandle"
  :setvar DefaultDataPath "D:\Program Files\Microsoft SQL Server\MSSQL15.SAEON2019\MSSQL\DATA\"
  :setvar DefaultLogPath "D:\Program Files\Microsoft SQL Server\MSSQL15.SAEON2019\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
  --IF EXISTS (SELECT 1
  --           FROM   [master].[dbo].[sysdatabases]
  --           WHERE  [name] = N'$(DatabaseName)')
  --    BEGIN
  --        ALTER DATABASE [$(DatabaseName)]
  --            SET TEMPORAL_HISTORY_RETENTION ON 
  --            WITH ROLLBACK IMMEDIATE;
  --    END
  
  
  GO
  PRINT N'Dropping Index [dbo].[Observation].[IX_Observation_ValueDecade]...';
  
  
  GO
  DROP INDEX [IX_Observation_ValueDecade]
      ON [dbo].[Observation];
  
  
  GO
  PRINT N'Dropping Index [dbo].[Observation].[IX_Observation_ValueYear]...';
  
  
  GO
  DROP INDEX [IX_Observation_ValueYear]
      ON [dbo].[Observation];
  
  
  GO
  PRINT N'Dropping Index [dbo].[UserDownloads].[IX_UserDownloads_DOI]...';
  
  
  GO
  DROP INDEX [IX_UserDownloads_DOI]
      ON [dbo].[UserDownloads];
  
  
  GO
  PRINT N'Dropping Foreign Key [dbo].[FK_UserDownloads_DigitalObjectIdentifiers]...';
  
  
  GO
  ALTER TABLE [dbo].[UserDownloads] DROP CONSTRAINT [FK_UserDownloads_DigitalObjectIdentifiers];
  
  
  GO
  PRINT N'Altering Table [dbo].[Observation]...';
  
  
  GO
  ALTER TABLE [dbo].[Observation] DROP COLUMN [ValueYear], COLUMN [ValueDecade];
  
  
  GO
  ALTER TABLE [dbo].[Observation]
      ADD [ValueYear]   AS (Year([ValueDate])),
          [ValueDecade] AS (Year([ValueDate]) / 10 * 10);
  
  
  GO
  PRINT N'Creating Index [dbo].[Observation].[IX_Observation_ValueDecade]...';
  
  
  GO
  CREATE NONCLUSTERED INDEX [IX_Observation_ValueDecade]
      ON [dbo].[Observation]([ValueDecade] ASC)
      ON [Observations];
  
  
  GO
  PRINT N'Creating Index [dbo].[Observation].[IX_Observation_ValueYear]...';
  
  
  GO
  CREATE NONCLUSTERED INDEX [IX_Observation_ValueYear]
      ON [dbo].[Observation]([ValueYear] ASC)
      ON [Observations];
  
  
  GO
  PRINT N'Altering Table [dbo].[UserDownloads]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  ALTER TABLE [dbo].[UserDownloads] ALTER COLUMN [Description] VARCHAR (5000) NULL;
  
  ALTER TABLE [dbo].[UserDownloads] ALTER COLUMN [DigitalObjectIdentifierID] INT NULL;
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating Index [dbo].[UserDownloads].[IX_UserDownloads_DOI]...';
  
  
  GO
  CREATE NONCLUSTERED INDEX [IX_UserDownloads_DOI]
      ON [dbo].[UserDownloads]([DigitalObjectIdentifierID] ASC);
  
  
  GO
  PRINT N'Creating Foreign Key [dbo].[FK_UserDownloads_DigitalObjectIdentifiers]...';
  
  
  GO
  ALTER TABLE [dbo].[UserDownloads] WITH NOCHECK
      ADD CONSTRAINT [FK_UserDownloads_DigitalObjectIdentifiers] FOREIGN KEY ([DigitalObjectIdentifierID]) REFERENCES [dbo].[DigitalObjectIdentifiers] ([ID]);
  
  
  GO
  PRINT N'Refreshing View [dbo].[vObservationExpansion]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  EXECUTE sp_refreshsqlmodule N'[dbo].[vObservationExpansion]';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Refreshing View [dbo].[vSensorThingsAPIObservations]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  EXECUTE sp_refreshsqlmodule N'[dbo].[vSensorThingsAPIObservations]';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Refreshing View [dbo].[vObservation]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  EXECUTE sp_refreshsqlmodule N'[dbo].[vObservation]';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Refreshing View [dbo].[vObservationJSON]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  EXECUTE sp_refreshsqlmodule N'[dbo].[vObservationJSON]';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Refreshing View [dbo].[vStationObservations]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  EXECUTE sp_refreshsqlmodule N'[dbo].[vStationObservations]';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Refreshing View [dbo].[vUserDownloads]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  EXECUTE sp_refreshsqlmodule N'[dbo].[vUserDownloads]';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating View [dbo].[vInventoryDatasets]...';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE VIEW [dbo].[vInventoryDatasets]
  AS 
  Select
    Row_Number() over (order by StationCode, PhenomenonCode, OfferingCode, UnitOfMeasureCode) ID, s.*
  from
  (
  Select
    OrganisationID, OrganisationCode, OrganisationName, OrganisationDescription, OrganisationUrl,
    ProgrammeID, ProgrammeCode, ProgrammeName, ProgrammeDescription, ProgrammeUrl,
    ProjectID, ProjectCode, ProjectName, ProjectDescription, ProjectUrl,
    SiteID, SiteCode, SiteName, SiteDescription, SiteUrl,
    StationID, StationCode, StationName, StationDescription, StationUrl,
    PhenomenonID, PhenomenonCode, PhenomenonName, PhenomenonDescription, PhenomenonUrl,
    PhenomenonOfferingID, OfferingID, OfferingCode, OfferingName, OfferingDescription,
    PhenomenonUOMID, UnitOfMeasureID, UnitOfMeasureCode, UnitOfMeasureUnit, UnitOfMeasureSymbol,
    Sum(Count) Count,
    Sum(VerifiedCount) VerifiedCount,
    Min(StartDate) StartDate,
    Max(EndDate) EndDate,
    Max(LatitudeNorth) LatitudeNorth,
    Min(LatitudeSouth) LatitudeSouth,
    Min(LongitudeWest) LongitudeWest,
    Max(LongitudeEast) LongitudeEast,
    Min(ElevationMinimum) ElevationMinimum,
    Max(ElevationMaximum) ElevationMaximum
  from
    vImportBatchSummary
  group by
    OrganisationID, OrganisationCode, OrganisationName, OrganisationDescription, OrganisationUrl,
    ProgrammeID, ProgrammeCode, ProgrammeName, ProgrammeDescription, ProgrammeUrl,
    ProjectID, ProjectCode, ProjectName, ProjectDescription, ProjectUrl,
    SiteID, SiteCode, SiteName, SiteDescription, SiteUrl,
    StationID, StationCode, StationName, StationDescription, StationUrl,
    PhenomenonID, PhenomenonCode, PhenomenonName, PhenomenonDescription, PhenomenonUrl,
    PhenomenonOfferingID, OfferingID, OfferingCode, OfferingName, OfferingDescription,
    PhenomenonUOMID, UnitOfMeasureID, UnitOfMeasureCode, UnitOfMeasureUnit, UnitOfMeasureSymbol
  ) s
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Checking existing data against newly created constraints';
  
  
  GO
  USE [$(DatabaseName)];
  
  
  GO
  ALTER TABLE [dbo].[UserDownloads] WITH CHECK CHECK CONSTRAINT [FK_UserDownloads_DigitalObjectIdentifiers];
  
  
  GO
  PRINT N'Update complete.';
  
  
  GO
  