-- ------------------------------------------------------------------------------------
-- ION Data v5.0 Database Upgrade: Step 2
-- 
-- Ensures the SourceID is an integer. This is due to a decision taken during Corsica:
-- * databases from a new install featured an ID column with type INT
-- * databases from a upgraded version (such as 5.x) featured an ID column with type SMALLINT
-- 
-- If the SourceID is already an INT, no modification is performed.
-- ------------------------------------------------------------------------------------
	SELECT dbo.FormatStatusMessage('Step 2: Started.')
GO
	-- SCR#24226: databases should be using INT for the InputDeviceID column
	-- Place this statement outside the IF statement to ensure this is applied
	-- to 6.0 SP1 databases that have a Source ID datatype INT and an 
	-- InputDeviceID of type SMALLINT.
	ALTER TABLE dbo.Channel ALTER COLUMN InputDeviceID INT NULL
GO
	IF EXISTS 
		(
		SELECT 
			OBJECT_NAME(c.OBJECT_ID) AS [TableName]
			, c.name AS [ColumnName]
		FROM 
			sys.columns AS c
			JOIN sys.types AS t ON (c.user_type_id=t.user_type_id)
		WHERE 
			t.name = 'SMALLINT'
			AND OBJECT_NAME(c.OBJECT_ID) = 'Source'
			AND c.name = 'ID'
		)
	BEGIN
	
		SELECT dbo.FormatStatusMessage('Step 2: Updating datatype of ID column on Source table from SMALLINT to INT.')
		
		ALTER TABLE dbo.WaveformLog DROP CONSTRAINT FK_WaveformLog_Source
		ALTER TABLE dbo.SourceState DROP CONSTRAINT FK_SourceState_Source
		ALTER TABLE dbo.Recorder DROP CONSTRAINT FK_Recorder_Source
		ALTER TABLE dbo.EventLog DROP CONSTRAINT FK_EventLog_Source
		ALTER TABLE dbo.Device DROP CONSTRAINT FK_Device_Source
		ALTER TABLE dbo.DataLogStamp DROP CONSTRAINT FK_DataLogStamp_Source

		ALTER TABLE dbo.Source DROP CONSTRAINT PK_Source_ID
		ALTER TABLE dbo.Device DROP CONSTRAINT PK_Device_ID
		ALTER TABLE dbo.SourceState DROP CONSTRAINT PK_SourceState_SourceID
		ALTER TABLE dbo.Recorder DROP CONSTRAINT IX_Recorder_SourceID_Name

		ALTER TABLE dbo.Source ALTER COLUMN ID INT NOT NULL
		ALTER TABLE dbo.Device ALTER COLUMN ID INT NOT NULL
		ALTER TABLE dbo.Source WITH NOCHECK ADD CONSTRAINT PK_Source_ID PRIMARY KEY CLUSTERED (ID)
		ALTER TABLE dbo.Device WITH NOCHECK ADD CONSTRAINT PK_Device_ID PRIMARY KEY CLUSTERED (ID)

		ALTER TABLE dbo.WaveformLog ALTER COLUMN SourceID INT NOT NULL
		ALTER TABLE dbo.SourceState ALTER COLUMN SourceID INT NOT NULL
		ALTER TABLE dbo.Recorder ALTER COLUMN SourceID INT NOT NULL
		ALTER TABLE dbo.EventLog ALTER COLUMN SourceID INT NOT NULL

		SELECT dbo.FormatStatusMessage('Step 2: Refreshing views dependant on the Source table (previous ID column datatype was cached).')
		EXEC sp_refreshview 'dbo.vEventLog'
		EXEC sp_refreshview 'dbo.vwEVT_EventLog'

		SELECT dbo.FormatStatusMessage('Step 2: Re-adding database constraints dependant on the Source table.')
	
		-- WaveformLog
		ALTER TABLE 
			[dbo].[WaveformLog] 
		ADD CONSTRAINT 
			[FK_WaveformLog_Source] 
		FOREIGN KEY
				([SourceID]) REFERENCES [dbo].[Source] ([ID])
	
		-- SourceState
		ALTER TABLE 
			[dbo].[SourceState] 
		ADD  CONSTRAINT 
			[PK_SourceState_SourceID] PRIMARY KEY CLUSTERED 
		(
			[SourceID] ASC
		)

		ALTER TABLE 
			[dbo].[SourceState]
		ADD CONSTRAINT 
			[FK_SourceState_Source]
		FOREIGN KEY
			([SourceID]) REFERENCES [dbo].[Source] ([ID])		
	
		-- Recorder
		ALTER TABLE 
			[dbo].[Recorder] 
		ADD CONSTRAINT 
			[FK_Recorder_Source] 
		FOREIGN KEY
			([SourceID]) REFERENCES [dbo].[Source] ([ID])

		ALTER TABLE 
			[dbo].[Recorder]
		ADD  CONSTRAINT 
			[IX_Recorder_SourceID_Name] UNIQUE NONCLUSTERED 
		(
			[SourceID] ASC,
			[Name] ASC
		)
	
		-- EventLog
		ALTER TABLE 
			[dbo].[EventLog] 
		ADD CONSTRAINT 
			[FK_EventLog_Source] 
		FOREIGN KEY
			([SourceID]) REFERENCES [dbo].[Source] ([ID])
	
		-- Device
		ALTER TABLE 
			[dbo].[Device] 
		ADD CONSTRAINT 
			[FK_Device_Source] 
		FOREIGN KEY
			([ID]) REFERENCES [dbo].[Source] ([ID])

	END
	ELSE
	BEGIN
		SELECT dbo.FormatStatusMessage('Step 2: Source ID column is already of datatype INT. No modifications are necessary.')
	END
GO
PRINT 'Step 2: Completed.'