/*
** Wonderware - InSQL 9.0
** Migration script for InSQL 7.1 to 9.0 upgrade  HISTORY DATA
**------------------------------------------------------------------------
** DO NOT RUN THIS SCRIPT AGAINST AN ALREADY MIGRATED INSQL 9.0 DATABASE
** THIS SCRIPT MIGRATES THE EVENT & SUMMARY HISTORY DATA 
**                                           AND THE MANUAL HISTORY DATA
** IF YOU DID NOT LET THIS MIGRATION RUN DURING THE INSTALLATION:
**       USE THIS SCRIPT ONLY ONCE THE STATIC DATA HAS BEEN MIGRATED
**       IMPORTANT: THIS SCRIPT MUST BE RUN BEFORE RESTARTING INSQL
** 
**------------------------------------------------------------------------
*/

/*Reindex tables*/
USE Runtime71

DBCC DBREINDEX ('Runtime71.dbo.EventHistory')
GO
DBCC DBREINDEX ('Runtime71.dbo.SummaryHistory')
GO
DBCC DBREINDEX ('Runtime71.dbo.SummaryData')
GO
DBCC DBREINDEX ('Runtime71.dbo.StringHistory')
GO
DBCC DBREINDEX ('Runtime71.dbo.AnalogSnapshot')
GO
DBCC DBREINDEX ('Runtime71.dbo.DiscreteSnapshot')
GO
DBCC DBREINDEX ('Runtime71.dbo.StringSnapshot')
GO
DBCC DBREINDEX ('Runtime71.dbo.ManualAnalogHistory')
GO
DBCC DBREINDEX ('Runtime71.dbo.ManualDiscreteHistory')
GO

USE Runtime
GO

/* StringHistory */
/* Migrates into the ManualStringHistory Table */
truncate table ManualStringHistory
go
/* Data Tables could have lots of rows, generating huge log files if */
/* processed all at once. We will process them by batch.             */
declare @howmanyrows int, @bottomrow int, @batchvalue int
declare @bottomdatetime datetime

set @batchvalue = 100000

set @bottomrow = @batchvalue
select @howmanyrows = count(DateTime) from Runtime71.dbo.StringHistory 
select @bottomdatetime = min(DateTime) from Runtime71.dbo.StringHistory

/* need to tick that datetime down to get first batch*/
set @bottomdatetime = dateadd(mi, -1, @bottomdatetime)

while @bottomrow <= (@howmanyrows + @batchvalue)
begin
   BEGIN TRAN
   insert into Runtime.dbo.ManualStringHistory 
      select TOP 100000 DateTime, s71.TagName, s71.Value, s71.Quality,
             s71.QualityDetail, tTo.wwTagKey
         from Runtime71.dbo.StringHistory s71, Runtime.dbo.Tag tTo
           where s71.TagName = tTo.TagName
           and s71.DateTime > @bottomdatetime
         order by DateTime       
   COMMIT TRAN

   BACKUP LOG Runtime with TRUNCATE_ONLY

   /* The TOP could have stopped the result set in the middle of batch with the */
   /* same time stamp so we remove the last row inserted to avoid missing       */
   /*  rows in the next batch unless we are done */
   if (select count(DateTime) from Runtime71.dbo.StringHistory)
          <> (select count(DateTime) from Runtime.dbo.ManualStringHistory)
   begin
      select @bottomdatetime = max(DateTime) from Runtime.dbo.ManualStringHistory
      delete Runtime.dbo.ManualStringHistory 
         where DateTime = @bottomdatetime 
      /* now get the final bottom datetime */
      select @bottomdatetime = max(DateTime) from Runtime.dbo.ManualStringHistory
   end
   else
   begin
      break
   end
   set @bottomrow = @bottomrow + @batchvalue  -- next batch
end
go

/******************************************************************************/
/******************************************************************************/

/* ManualAnalogHistory */
/* Data Tables could have lots of rows, generating huge log files if */
/* processed all at once. We will process them by batch.             */
declare @howmanyrows int, @bottomrow int, @batchvalue int
declare @bottomdatetime datetime

set @batchvalue = 100000

set @bottomrow = @batchvalue
select @howmanyrows = count(DateTime) from Runtime71.dbo.ManualAnalogHistory 
select @bottomdatetime = min(DateTime) from Runtime71.dbo.ManualAnalogHistory

/* need to tick that datetime down to get first batch*/
set @bottomdatetime = dateadd(mi, -1, @bottomdatetime)

while @bottomrow <= (@howmanyrows + @batchvalue)
begin
   BEGIN TRAN
   insert into Runtime.dbo.ManualAnalogHistory 
      select TOP 100000 DateTime, a71.TagName, a71.Value, a71.Quality,
             a71.QualityDetail, tTo.wwTagKey
         from Runtime71.dbo.ManualAnalogHistory a71, Runtime.dbo.Tag tTo
           where a71.TagName = tTo.TagName
           and a71.DateTime > @bottomdatetime
         order by DateTime       
   COMMIT TRAN

   BACKUP LOG Runtime with TRUNCATE_ONLY

   /* The TOP could have stopped the result set in the middle of batch with the */
   /* same time stamp so we remove the last row inserted to avoid missing       */
   /*  rows in the next batch unless we are done */
   if (select count(DateTime) from Runtime71.dbo.ManualAnalogHistory)
          <> (select count(DateTime) from Runtime.dbo.ManualAnalogHistory)
   begin
      select @bottomdatetime = max(DateTime) from Runtime.dbo.ManualAnalogHistory
      delete Runtime.dbo.ManualAnalogHistory 
         where DateTime = @bottomdatetime 
      /* now get the final bottom datetime */
      select @bottomdatetime = max(DateTime) from Runtime.dbo.ManualAnalogHistory
   end
   else
   begin
      break
   end
   set @bottomrow = @bottomrow + @batchvalue  -- next batch
end
go

/******************************************************************************/
/******************************************************************************/

/* ManualDiscreteHistory */
/* Data Tables could have lots of rows, generating huge log files if */
/* processed all at once. We will process them by batch.             */
declare @howmanyrows int, @bottomrow int, @batchvalue int
declare @bottomdatetime datetime

set @batchvalue = 100000

set @bottomrow = @batchvalue
select @howmanyrows = count(DateTime) from Runtime71.dbo.ManualDiscreteHistory 
select @bottomdatetime = min(DateTime) from Runtime71.dbo.ManualDiscreteHistory

/* need to tick that datetime down to get first batch*/
set @bottomdatetime = dateadd(mi, -1, @bottomdatetime)

while @bottomrow <= (@howmanyrows + @batchvalue)
begin
   BEGIN TRAN
   insert into Runtime.dbo.ManualDiscreteHistory
      select TOP 100000 DateTime, d71.TagName, d71.Value, d71.Quality,
             d71.QualityDetail, tTo.wwTagKey
         from Runtime71.dbo.ManualDiscreteHistory d71, Runtime.dbo.Tag tTo
           where d71.TagName = tTo.TagName
           and d71.DateTime > @bottomdatetime
         order by DateTime       
   COMMIT TRAN

   BACKUP LOG Runtime with TRUNCATE_ONLY

   /* The TOP could have stopped the result set in the middle of batch with the */
   /* same time stamp so we remove the last row inserted to avoid missing       */
   /*  rows in the next batch unless we are done */
   if (select count(DateTime) from Runtime71.dbo.ManualDiscreteHistory)
          <> (select count(DateTime) from Runtime.dbo.ManualDiscreteHistory)
   begin
      select @bottomdatetime = max(DateTime) from Runtime.dbo.ManualDiscreteHistory
      delete Runtime.dbo.ManualDiscreteHistory
         where DateTime = @bottomdatetime 
      /* now get the final bottom datetime */
      select @bottomdatetime = max(DateTime) from Runtime.dbo.ManualDiscreteHistory
   end
   else
   begin
      break
   end
   set @bottomrow = @bottomrow + @batchvalue  -- next batch
end
go


/******************************************************************************/
/******************************************************************************/

/* EventHistory */
truncate table EventHistory -- Reset everything
go

BEGIN TRAN
   SET IDENTITY_INSERT EventHistory ON

   insert into EventHistory (EventLogKey, TagName, DateTime, DetectDateTime, Edge)
      select EventLogKey, eh71.TagName, DateTime, DetectDateTime, eh71.Edge
         from Runtime71.dbo.EventHistory eh71, Runtime71.dbo.EventTag et71
            where eh71.TagName = et71.TagName

   SET IDENTITY_INSERT EventHistory OFF
   COMMIT TRAN
go

BACKUP LOG Runtime with TRUNCATE_ONLY
go

/* Snapshots */
truncate table AnalogSnapshot -- Reset everything
truncate table DiscreteSnapshot -- Reset everything
truncate table StringSnapshot -- Reset everything
go
/* Analog Snapshot */
/* Data Tables could have lots of rows, generating huge log files if */
/* processed all at once. We will process them by batch.             */
declare @howmanyrows int, @bottomrow int, @batchvalue int
declare @bottomkey int

set @batchvalue = 50000

set @bottomrow = @batchvalue
select @howmanyrows = count(EventLogKey) from Runtime71.dbo.AnalogSnapshot
select @bottomkey = 0

while @bottomrow <= (@howmanyrows + @batchvalue)
begin
   BEGIN TRAN
      insert into AnalogSnapshot (SnapshotTagKey, EventLogKey, Value, Quality, QualityDetail) 
         select TOP 50000 stTo.SnapshotTagKey, as71.EventLogKey, Value, Quality, QualityDetail
            from Runtime71.dbo.AnalogSnapshot as71, Runtime.dbo.SnapshotTag stTo,
                 Runtime71.dbo.EventTag et71, Runtime71.dbo.SnapshotTag st71
            where st71.TagName = stTo.TagName
            and   as71.SnapshotTagKey = st71.SnapshotTagKey
            and   st71.EventTagName = stTo.EventTagName
            and   st71.EventTagName = et71.TagName
            and   as71.EventLogKey > @bottomkey
         order by as71.EventLogKey
   COMMIT TRAN

   BACKUP LOG Runtime with TRUNCATE_ONLY

   /* The TOP could have stopped the result set in the middle of batch with the  */
   /* same time stamp so we remove the last row inserted to avoid missing        */
   /* rows in the next batch unless we are done. */
   if (select count(EventLogKey) from Runtime71.dbo.AnalogSnapshot as71, Runtime.dbo.SnapshotTag stTo,
                 Runtime71.dbo.EventTag et71, Runtime71.dbo.SnapshotTag st71
            where st71.TagName = stTo.TagName
            and   as71.SnapshotTagKey = st71.SnapshotTagKey
            and   st71.EventTagName = stTo.EventTagName
            and   st71.EventTagName = et71.TagName)
          <> (select count(EventLogKey) from Runtime.dbo.AnalogSnapshot)
   begin
      select @bottomkey = max(EventLogKey) from Runtime.dbo.AnalogSnapshot
      delete AnalogSnapshot 
         where EventLogKey = @bottomkey 
      /* now get the final bottom key */
      select @bottomkey = max(EventLogKey) from Runtime.dbo.AnalogSnapshot
   end
   else
   begin
      break
   end
   set @bottomrow = @bottomrow + @batchvalue  -- next batch
end
go   

/* DiscreteSnapshot */
/* Data Tables could have lots of rows, generating huge log files if */
/* processed all at once. We will process them by batch.             */
declare @howmanyrows int, @bottomrow int, @batchvalue int
declare @bottomkey int

set @batchvalue = 50000

set @bottomrow = @batchvalue
select @howmanyrows = count(EventLogKey) from Runtime71.dbo.DiscreteSnapshot
select @bottomkey = 0

while @bottomrow <= (@howmanyrows + @batchvalue)
begin
   BEGIN TRAN
      insert into DiscreteSnapshot (SnapshotTagKey, EventLogKey, Value, Quality, QualityDetail) 
         select TOP 50000 stTo.SnapshotTagKey, as71.EventLogKey, Value, Quality, QualityDetail
            from Runtime71.dbo.DiscreteSnapshot as71, Runtime.dbo.SnapshotTag stTo,
                 Runtime71.dbo.EventTag et71, Runtime71.dbo.SnapshotTag st71
            where st71.TagName = stTo.TagName
            and   as71.SnapshotTagKey = st71.SnapshotTagKey
            and   st71.EventTagName = stTo.EventTagName
            and   st71.EventTagName = et71.TagName
            and   as71.EventLogKey > @bottomkey
         order by as71.EventLogKey
   COMMIT TRAN

   BACKUP LOG Runtime with TRUNCATE_ONLY

   /* The TOP could have stopped the result set in the middle of batch with the  */
   /* same time stamp so we remove the last row inserted to avoid missing        */
   /* rows in the next batch unless we are done. */
   if (select count(EventLogKey) from Runtime71.dbo.DiscreteSnapshot as71, Runtime.dbo.SnapshotTag stTo,
                 Runtime71.dbo.EventTag et71, Runtime71.dbo.SnapshotTag st71
            where st71.TagName = stTo.TagName
            and   as71.SnapshotTagKey = st71.SnapshotTagKey
            and   st71.EventTagName = stTo.EventTagName
            and   st71.EventTagName = et71.TagName)
          <> (select count(EventLogKey) from Runtime.dbo.DiscreteSnapshot)
   begin
      select @bottomkey = max(EventLogKey) from Runtime.dbo.DiscreteSnapshot
      delete DiscreteSnapshot 
         where EventLogKey = @bottomkey 
      /* now get the final bottom key */
      select @bottomkey = max(EventLogKey) from Runtime.dbo.DiscreteSnapshot
   end
   else
   begin
      break
   end
   set @bottomrow = @bottomrow + @batchvalue  -- next batch
end
go  

/* StringSnapshot */
/* Data Tables could have lots of rows, generating huge log files if */
/* processed all at once. We will process them by batch.             */
declare @howmanyrows int, @bottomrow int, @batchvalue int
declare @bottomkey int

set @batchvalue = 50000

set @bottomrow = @batchvalue
select @howmanyrows = count(EventLogKey) from Runtime71.dbo.StringSnapshot
select @bottomkey = 0

while @bottomrow <= (@howmanyrows + @batchvalue)
begin
   BEGIN TRAN
      insert into StringSnapshot (SnapshotTagKey, EventLogKey, Value, Quality, QualityDetail) 
         select TOP 50000 stTo.SnapshotTagKey, as71.EventLogKey, Value, Quality, QualityDetail
            from Runtime71.dbo.StringSnapshot as71, Runtime.dbo.SnapshotTag stTo,
                 Runtime71.dbo.EventTag et71, Runtime71.dbo.SnapshotTag st71
            where st71.TagName = stTo.TagName
            and   as71.SnapshotTagKey = st71.SnapshotTagKey
            and   st71.EventTagName = stTo.EventTagName
            and   st71.EventTagName = et71.TagName
            and   as71.EventLogKey > @bottomkey
         order by as71.EventLogKey
   COMMIT TRAN

   BACKUP LOG Runtime with TRUNCATE_ONLY

   /* The TOP could have stopped the result set in the middle of batch with the  */
   /* same time stamp so we remove the last row inserted to avoid missing        */
   /* rows in the next batch unless we are done. */
   if (select count(EventLogKey) from Runtime71.dbo.StringSnapshot as71, Runtime.dbo.SnapshotTag stTo,
                 Runtime71.dbo.EventTag et71, Runtime71.dbo.SnapshotTag st71
            where st71.TagName = stTo.TagName
            and   as71.SnapshotTagKey = st71.SnapshotTagKey
            and   st71.EventTagName = stTo.EventTagName
            and   st71.EventTagName = et71.TagName)
          <> (select count(EventLogKey) from Runtime.dbo.StringSnapshot)
   begin
      select @bottomkey = max(EventLogKey) from Runtime.dbo.StringSnapshot
      delete StringSnapshot 
         where EventLogKey = @bottomkey 
      /* now get the final bottom key */
      select @bottomkey = max(EventLogKey) from Runtime.dbo.StringSnapshot
   end
   else
   begin
      break
   end
   set @bottomrow = @bottomrow + @batchvalue  -- next batch
end
go    

/******************************************************************************/
/******************************************************************************/

/* Summary Data */

BEGIN TRAN
   set IDENTITY_INSERT SummaryHistory ON

   insert into SummaryHistory (LogKey, OperationKey, SummaryDate, SumDateTimeStamp,
                               SumDateCalcType, SumDateDuration, SumDateResolution,
                               Status, OperationStart, OperationEnd)
      select LogKey, OperationKey, SummaryDate, SumDateTimeStamp, SumDateCalcType,
             SumDateDuration, SumDateResolution, Status, OperationStart, OperationEnd
         from Runtime71.dbo.SummaryHistory

   set IDENTITY_INSERT SummaryHistory OFF
COMMIT TRAN
go

BACKUP LOG Runtime with TRUNCATE_ONLY
go

truncate table SummaryData
go
/* Data Tables could have lots of rows, generating huge log files if */
/* processed all at once. We will process them by batch.             */
declare @howmanyrows int, @bottomrow int, @batchvalue int
declare @bottomkey int

set @batchvalue = 50000

set @bottomrow = @batchvalue
select @howmanyrows = count(LogKey) from Runtime71.dbo.SummaryData
select @bottomkey = 0

while @bottomrow <= (@howmanyrows + @batchvalue)
begin
   BEGIN TRAN
      insert into SummaryData
         select TOP 50000 LogKey, sd71.SumVarKey, Value, Quality, Modified
            from Runtime71.dbo.SummaryData sd71, Runtime.dbo.SummaryTagList stlTo
               where stlTo.SumVarKey = sd71.SumVarKey
               and sd71.LogKey > @bottomkey
         order by sd71.LogKey
   COMMIT TRAN

   BACKUP LOG Runtime with TRUNCATE_ONLY

   /* The TOP could have stopped the result set in the middle of batch with the  */
   /* same time stamp so we remove the last row inserted to avoid missing        */
   /* rows in the next batch unless we are done. */
   if (select count(LogKey) from Runtime71.dbo.SummaryData sd71, Runtime.dbo.SummaryTagList stlTo
               where stlTo.SumVarKey = sd71.SumVarKey)
          <> (select count(LogKey) from Runtime.dbo.SummaryData)
   begin
      select @bottomkey = max(LogKey) from Runtime.dbo.SummaryData
      delete SummaryData 
         where LogKey = @bottomkey 
      /* now get the final bottom key */
      select @bottomkey = max(LogKey) from Runtime.dbo.SummaryData
   end
   else
   begin
      break
   end
   set @bottomrow = @bottomrow + @batchvalue  -- next batch
end


/******************************************************************************/
/******************************************************************************/

/* Shrink the db */
use master
go
BACKUP LOG Runtime with TRUNCATE_ONLY
go
DBCC SHRINKDATABASE (Runtime, 20)
go
