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'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'Creating Index [dbo].[Observation].[IX_Observation_ImportBatchID]...';
  
  
  GO
  CREATE NONCLUSTERED INDEX [IX_Observation_ImportBatchID]
      ON [dbo].[Observation]([ImportBatchID] ASC)
      INCLUDE([DataValue], [PhenomenonOfferingID], [PhenomenonUOMID], [SensorID], [StatusID], [StatusReasonID], [Elevation], [Latitude], [Longitude], [ValueDate], [ValueDay])
      ON [Observations];
  
  
  GO
  PRINT N'Creating Index [dbo].[Observation].[IX_Observation_SensorID]...';
  
  
  GO
  CREATE NONCLUSTERED INDEX [IX_Observation_SensorID]
      ON [dbo].[Observation]([SensorID] ASC)
      ON [Observations];
  
  
  GO
  PRINT N'Creating Index [dbo].[Observation].[IX_Observation_StatusID]...';
  
  
  GO
  CREATE NONCLUSTERED INDEX [IX_Observation_StatusID]
      ON [dbo].[Observation]([StatusID] ASC)
      ON [Observations];
  
  
  GO
  PRINT N'Altering View [dbo].[vObservationExpansion]...';
  
  
  GO
  SET ANSI_NULLS ON;
  
  SET QUOTED_IDENTIFIER OFF;
  
  
  GO
  ALTER VIEW [dbo].[vObservationExpansion]
  AS
  Select distinct
    Observation.ID, Observation.ImportBatchID, ImportBatch.Code ImportBatchCode, ImportBatch.ImportDate ImportBatchDate, 
    Observation.ValueDate, Observation.ValueDay, Observation.ValueYear, Observation.ValueDecade, 
    Observation.RawValue, Observation.DataValue, Observation.TextValue, 
    Observation.Comment, Observation.CorrelationID,
    Site.ID SiteID, Site.Code SiteCode, Site.Name SiteName, Site.Description SiteDescription, Site.Url SiteUrl,
    Station.ID StationID, Station.Code StationCode, Station.Name StationName, Station.Description StationDescription, Station.Url StationUrl,
    Instrument.ID InstrumentID, Instrument.Code InstrumentCode, Instrument.Name InstrumentName, Instrument.Description InstrumentDescription, Instrument.Url InstrumentUrl,
    Observation.SensorID, Sensor.Code SensorCode, Sensor.Name SensorName, Sensor.Description SensorDescription, Sensor.Url SensorUrl,
    Coalesce(Observation.Latitude, Sensor.Latitude, Instrument_Sensor.Latitude, Instrument.Latitude, Station_Instrument.Latitude, Station.Latitude) Latitude,
    Coalesce(Observation.Longitude, Sensor.Longitude, Instrument_Sensor.Longitude, Instrument.Longitude, Station_Instrument.Longitude, Station.Longitude) Longitude,
    Coalesce(Observation.Elevation, Sensor.Elevation, Instrument_Sensor.Elevation, Instrument.Elevation, Station_Instrument.Elevation, Station.Elevation) Elevation,
    Observation.PhenomenonOfferingID, Phenomenon.ID PhenomenonID, Phenomenon.Code PhenomenonCode, Phenomenon.Name PhenomenonName, Phenomenon.Description PhenomenonDescription, Phenomenon.Url PhenomenonUrl,
    Offering.ID OfferingID, Offering.Code OfferingCode, Offering.Name OfferingName, Offering.Description OfferingDescription,
    Observation.PhenomenonUOMID, UnitOfMeasure.ID UnitOfMeasureID, UnitOfMeasure.Code UnitOfMeasureCode, UnitOfMeasure.Unit UnitOfMeasureUnit, UnitOfMeasure.UnitSymbol UnitOfMeasureSymbol,
    Observation.StatusID, Status.Code StatusCode, Status.Name StatusName, Status.Description StatusDescription,
    Observation.StatusReasonID, StatusReason.Code StatusReasonCode, StatusReason.Name StatusReasonName, StatusReason.Description StatusReasonDescription,
    Observation.UserId, Observation.AddedDate, Observation.AddedAt, Observation.UpdatedAt
  from
    Observation
    inner join ImportBatch
      on (Observation.ImportBatchID = ImportBatch.ID)
    inner join Sensor
      on (Observation.SensorID = Sensor.ID)
    inner join Instrument_Sensor
      on (Sensor.ID = Instrument_Sensor.SensorID) 
    inner join Instrument
      on (Instrument_Sensor.InstrumentID = Instrument.ID) 
    inner join Station_Instrument
      on (Station_Instrument.InstrumentID = Instrument.ID) 
    inner join Station
      on (Station_Instrument.StationID = Station.ID) 
    inner join Site
      on (Station.SiteID = Site.ID) 
    inner join PhenomenonOffering
      on (Observation.PhenomenonOfferingID = PhenomenonOffering.ID)
    inner join Phenomenon
      on (PhenomenonOffering.PhenomenonID = Phenomenon.ID)
    inner join Offering
      on (PhenomenonOffering.OfferingID = Offering.ID)
    inner join PhenomenonUOM
      on (Observation.PhenomenonUOMID = PhenomenonUOM.ID)
    inner join UnitOfMeasure
      on (PhenomenonUOM.UnitOfMeasureID = UnitOfMeasure.ID)
    left join Status
      on (Observation.StatusID = Status.ID)
    left join StatusReason
      on (Observation.StatusReasonID = StatusReason.ID)
  Where
    ((Instrument_Sensor.StartDate is null) or (Observation.ValueDate >= Instrument_Sensor.StartDate)) and
    ((Instrument_Sensor.EndDate is null) or (Observation.ValueDate <= Instrument_Sensor.EndDate)) and
    ((Instrument.StartDate is null) or (Observation.ValueDay >= Instrument.StartDate)) and
    ((Instrument.EndDate is null) or (Observation.ValueDay <= Instrument.EndDate)) and
    ((Station_Instrument.StartDate is null) or (Observation.ValueDay >= Station_Instrument.StartDate)) and
    ((Station_Instrument.EndDate is null) or (Observation.ValueDay <= Station_Instrument.EndDate)) and
    ((Station.StartDate is null) or (Observation.ValueDay >= Station.StartDate))  and
    ((Station.EndDate is null) or (Observation.ValueDay <= Station.EndDate)) and
    ((Site.StartDate is null) or (Observation.ValueDay >= Site.StartDate)) and
    ((Site.EndDate is null) or (Observation.ValueDay <= Site.EndDate))
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Refreshing View [dbo].[vSensorThingsAPIObservations]...';
  
  
  GO
  SET ANSI_NULLS ON;
  
  SET 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 ON;
  
  SET 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 ON;
  
  SET 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 ON;
  
  SET QUOTED_IDENTIFIER OFF;
  
  
  GO
  EXECUTE sp_refreshsqlmodule N'[dbo].[vStationObservations]';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Creating View [dbo].[vInventorySnapshots]...';
  
  
  GO
  SET ANSI_NULLS ON;
  
  SET QUOTED_IDENTIFIER OFF;
  
  
  GO
  CREATE VIEW [dbo].[vInventorySnapshots]
  as
  with VerifiedDatasets as
  (
  Select 
  	* 
  from 
  	vInventoryDatasets
  where
  	(VerifiedCount > 0)  and 
  	(LatitudeNorth is not null) and (LatitudeSouth is not null) and 
  	(LongitudeEast is not null) and (LongitudeWest is not null)
  ),
  VerifiedImportBatchSummaries as
  (
  Select
  	*
  from
  	vImportBatchSummary
  where
  	(VerifiedCount > 0)  and 
  	(LatitudeNorth is not null) and (LatitudeSouth is not null) and 
  	(LongitudeEast is not null) and (LongitudeWest is not null)
  )
  Select 
  	(Select Count(distinct OrganisationCode) from VerifiedDatasets) Organisations,
  	(Select Count(distinct ProgrammeCode) from VerifiedDatasets) Programmes,
  	(Select Count(distinct ProjectCode) from VerifiedDatasets) Projects,
  	(Select Count(distinct SiteCode) from VerifiedDatasets) Sites,
  	(Select Count(distinct StationCode) from VerifiedDatasets) Stations,
  	(Select Count(distinct InstrumentCode) from VerifiedImportBatchSummaries) Instruments,
  	(Select Count(distinct SensorCode) from VerifiedImportBatchSummaries) Sensors,
  	(Select Count(distinct PhenomenonCode) from VerifiedDatasets) Phenomena,
  	(Select Count(distinct OfferingCode) from VerifiedDatasets) Offerings,
  	(Select Count(distinct UnitOfMeasureCode) from VerifiedDatasets) UnitsOfMeasure,
  	(Select Count(*) from vVariables) Variables,
  	(Select Count(*) from VerifiedDatasets) Datasets,
  	(Select Sum(VerifiedCount) from VerifiedImportBatchSummaries) Observations,
  	(Select Count(*) from UserDownloads) Downloads
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Altering Procedure [dbo].[spCreateInventorySnapshot]...';
  
  
  GO
  SET ANSI_NULLS ON;
  
  SET QUOTED_IDENTIFIER OFF;
  
  
  GO
  ALTER PROCEDURE [dbo].[spCreateInventorySnapshot]
  AS
  BEGIN
  	Insert into InventorySnapshots
  	  (Organisations, Programmes, Projects, Sites, Stations, Instruments, Sensors, Phenomena, Offerings, UnitsOfMeasure, Variables, Datasets, Observations, Downloads)
  	Select 
  	  Organisations, Programmes, Projects, Sites, Stations, Instruments, Sensors, Phenomena, Offerings, UnitsOfMeasure, Variables, Datasets, Observations, Downloads
  	from 
  	  vInventorySnapshots
  	Select top(1) * from InventorySnapshots order by [When] Desc
  END
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Refreshing Procedure [dbo].[spCreateImportBatchSummaries]...';
  
  
  GO
  SET ANSI_NULLS ON;
  
  SET QUOTED_IDENTIFIER OFF;
  
  
  GO
  EXECUTE sp_refreshsqlmodule N'[dbo].[spCreateImportBatchSummaries]';
  
  
  GO
  SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
  
  
  GO
  PRINT N'Update complete.';
  
  
  GO
  