---------------------------------------------------------------------------
-- WARNING: Read carefully before running this script
--
-- This script should be used before upgrading MOM 2000 SP1 databases to
-- MOM 2005 ONLY. It should not be run in any version of MOM different than MOM 2000 SP1.
--
-- Make sure to back up your database before running this script.
-- 
----------------------------------------------------------------------------



use OnePoint
go


--------------------------------------
-- Dropping FK constraints
--------------------------------------


-- We need to drop the FKs first to be able to drop some of the indexes

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_EventParam_Event')
    ALTER TABLE [dbo].[EventParam]
    DROP CONSTRAINT [FK_EventParam_Event]
GO


IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_EventConsolidated_Event')
    ALTER TABLE [dbo].[EventConsolidated]
    DROP CONSTRAINT [FK_EventConsolidated_Event]
GO

---------------------------------
-- Dropping Event indexes
---------------------------------

if exists (select * from dbo.sysindexes where name = N'I_BrowseEvents' and id = object_id(N'[dbo].[Event]'))
drop index [dbo].[Event].[I_BrowseEvents]
go

if exists (select * from dbo.sysindexes where name = N'I_Event_EventSource' and id = object_id(N'[dbo].[Event]'))
drop index [dbo].[Event].[I_Event_EventSource]
go

if exists (select * from dbo.sysindexes where name = N'I_Event_idGeneratedBy' and id = object_id(N'[dbo].[Event]'))
drop index [dbo].[Event].[I_Event_idGeneratedBy]
go

if exists (select * from dbo.sysindexes where name = N'I_Event_User' and id = object_id(N'[dbo].[Event]'))
drop index [dbo].[Event].[I_Event_User]
go

if exists (select * from dbo.sysindexes where name = N'PK_EVENT' and id = object_id(N'[dbo].[Event]'))
drop index [dbo].[Event].[PK_EVENT]
go

if exists (select * from dbo.sysindexes where name = N'CI_EventGrooming' and id = object_id(N'[dbo].[Event]'))
drop index [dbo].[Event].[CI_EventGrooming]
go

-----------------------------------------
--  Dropping SampledNumericData indexes
-----------------------------------------

if exists (select * from dbo.sysindexes where name = N'I_Source' and id = object_id(N'[dbo].[SampledNumericData]'))
drop index [dbo].[SampledNumericData].[I_Source]
go

if exists (select * from dbo.sysindexes where name = N'CI_TimeSampled' and id = object_id(N'[dbo].[SampledNumericData]'))
drop index [dbo].[SampledNumericData].[CI_TimeSampled]
go

-----------------------------------------------
-- Re-creating Event Indexes
-----------------------------------------------


-- Make sure an index with this name does not already exist
IF NOT EXISTS (SELECT I.[id] FROM sysindexes AS I
               WHERE I.[name] = 'I_BrowseEvents_01' AND [id] = object_id(N'[dbo].[Event]'))
-- and make sure a clustered index does not already exist on this table
AND NOT EXISTS (SELECT I.[id] FROM sysindexes AS I WHERE I.indid = 1 AND  I.[id] = object_id(N'[dbo].[Event]'))
BEGIN 
    CREATE CLUSTERED INDEX [I_BrowseEvents_01] ON [dbo].[Event]([TimeGenerated], [idEvent])
END
GO


-- Make sure an index with this name does not already exist
IF NOT EXISTS (SELECT I.[id] FROM sysindexes AS I
WHERE I.[name] = 'PK_EVENT_01' AND [id] = object_id(N'[dbo].[Event]'))
BEGIN 
    CREATE UNIQUE INDEX [PK_EVENT_01] ON [dbo].[Event]([idEvent]) WITH IGNORE_DUP_KEY
END
GO


-- Make sure an index with this name does not already exist
IF NOT EXISTS (SELECT I.[id] FROM sysindexes AS I
WHERE I.[name] = 'I_Event_01_User' AND [id] = object_id(N'[dbo].[Event]'))
BEGIN 
    CREATE INDEX [I_Event_01_User] ON [dbo].[Event]([idUser], [idEvent])
END
GO


-- Make sure an index with this name does not already exist
IF NOT EXISTS (SELECT I.[id] FROM sysindexes AS I
WHERE I.[name] = 'I_Event_01_idLoggedOn' AND [id] = object_id(N'[dbo].[Event]'))
BEGIN 
    CREATE INDEX [I_Event_01_idLoggedOn] ON [dbo].[Event]([idLoggedOn])
END
GO

-----------------------------------------------
-- Re-creating SampledNumericData indexes
-----------------------------------------------

-- Make sure an index with this name does not already exist
IF NOT EXISTS (SELECT I.[id] FROM sysindexes AS I
               WHERE I.[name] = 'CI_TimeSampled_01' AND [id] = object_id(N'[dbo].[SampledNumericData]'))
-- and make sure a clustered index does not already exist on this table
AND NOT EXISTS (SELECT I.[id] FROM sysindexes AS I WHERE I.indid = 1 AND  I.[id] = object_id(N'[dbo].[SampledNumericData]'))
BEGIN 
    CREATE CLUSTERED INDEX [CI_TimeSampled_01] ON [dbo].[SampledNumericData](TimeSampled, idSampledNumericData)
END
GO



IF NOT EXISTS (SELECT I.[id] FROM sysindexes AS I
               WHERE I.[name] = 'I_Source_01' AND [id] = object_id(N'[dbo].[SampledNumericData]'))
BEGIN 
    CREATE INDEX [I_Source_01] ON [dbo].[SampledNumericData](idSampledNumericDataSource)
END
GO


IF NOT EXISTS (SELECT I.[id] FROM sysindexes AS I
WHERE I.[name] = 'PK_SampledNumbericData_01' AND [id] = object_id(N'[dbo].[SampledNumericData]'))
BEGIN 
    CREATE INDEX [PK_SampledNumbericData_01] ON [dbo].[SampledNumericData](idSampledNumericData)
END
GO


-- Update space usage
DBCC UPDATEUSAGE('OnePoint')
go

