/*
** Wonderware - InSQL 9.0
** Migration script for InSQL 7.1 to 9.0 upgrade
**------------------------------------------------------------------------
** DO NOT RUN THIS SCRIPT AGAINST AN ALREADY CONFIGURED INSQL 9.0 DATABASE
** THIS SCRIPT CAN RUN AGAINST A NEWLY BUILD RUNTIME DATABASE
**------------------------------------------------------------------------
**
** THIS SCRIPT DOES NOT MIGRATE THE HISTORY DATA (Event / Summary / Manual)
** Run the MigrateSQLHistData71to90.sql once this migration has been 
**    completed if you did not allow its execution during the installation.
**
*/

use Runtime
go
set NOCOUNT ON
go

/* Add IOServerTypes which might not be in 9.0 release list */
BEGIN TRAN
insert IOServerType (ApplicationName, Description, ExeName, Revision, Platform)
  select ApplicationName, Description, ExeName, Revision, Platform
     from Runtime71.dbo.IOServerType
          where ApplicationName not in 
             (select ApplicationName from Runtime.dbo.IOServerType)
COMMIT TRAN
go

/* IOServer <SysDrv is excluded> */
BEGIN TRAN
delete IOServer where IOServerKey > 2

SET IDENTITY_INSERT IOServer ON

insert IOServer (IOServerKey, StorageNodeKey, IODriverKey, ApplicationName, Path, ComputerName, AutoStart,
                 ExeType, InitializationStatus, ProtocolType, Description)
   select IOServerKey + 1, StorageNodeKey, 2, ApplicationName, Path, ComputerName, AutoStart,
          ExeType, InitializationStatus, ProtocolType, Description
      from Runtime71.dbo.IOServer
         where ApplicationName <> 'SysDrv'

SET IDENTITY_INSERT IOServer OFF
COMMIT TRAN
go

/* Topic */
BEGIN TRAN

SET IDENTITY_INSERT Topic ON

insert Topic (TopicKey, IOServerKey, StorageNodeKey, Name, TimeOut)
   select TopicKey, IOServerKey + 1 , StorageNodeKey, Name, TimeOut
      from Runtime71.dbo.Topic
         where IOServerKey > 1  -- Don't need SysDrv Topics 

SET IDENTITY_INSERT Topic OFF

COMMIT TRAN
go

/* Storage Location */
BEGIN TRAN
update StorageLocation set Path = o.Path,
                           MaxMBSize = o.MaxMBSize,
                           MinMBThreshold = o.MinMBThreshold,
                           SortOrder = o.SortOrder
   from Runtime.dbo.StorageLocation n, Runtime71.dbo.StorageLocation o
      where n.StorageType = o.StorageType 
COMMIT TRAN
go

/* Message */
BEGIN TRAN
insert Message (Message0, Message1)
   select Message0, Message1
      from Runtime71.dbo.Message
         where Message0 + ' ' + Message1 <> ALL
                  (SELECT Message0 + ' ' + Message1 FROM Runtime.dbo.Message)
COMMIT TRAN
go

/* EngineeringUnit */
BEGIN TRAN
insert EngineeringUnit (Unit, DefaultTagRate)
   select Unit, DefaultTagRate
      from Runtime71.dbo.EngineeringUnit
         where Unit <> ALL
                  (SELECT Unit FROM Runtime.dbo.EngineeringUnit)
COMMIT TRAN
go

/* Tag: Analog, Discrete and String */
BEGIN TRAN
insert into Tag (TagName, IOServerKey, StorageNodeKey, TopicKey, Description,
            AcquisitionType, StorageType, AcquisitionRate, TagType,
            TimeDeadband, DateCreated, CreatedBy, 
            CurrentEditor, SamplesInActiveImage, ItemName, StorageRate)
   select t71.TagName, tpTo.IOServerKey, t71.StorageNodeKey, tpTo.TopicKey, t71.Description,
          t71.AcquisitionType, t71.StorageType, t71.AcquisitionRate, t71.TagType,
          t71.TimeDeadband, t71.DateCreated, t71.CreatedBy, 
          t71.CurrentEditor, t71.SamplesInActiveImage, t71.ItemName,
          case 
            when t71.StorageRate = 0 then 0
            when t71.StorageRate between 1 and 1999 then 1000
            when t71.StorageRate between 2000 and 2999 then 2000
            when t71.StorageRate between 3000 and 4999 then 3000
            when t71.StorageRate between 5000 and 5999 then 5000
            when t71.StorageRate between 6000 and 9999 then 6000
            when t71.StorageRate between 10000 and 14999 then 10000
            when t71.StorageRate between 15000 and 29999 then 15000
            when t71.StorageRate between 30000 and 59999 then 30000
            when t71.StorageRate between 60000 and 119999 then 60000
            when t71.StorageRate between 120000 and 179999 then 120000
            when t71.StorageRate between 180000 and 299999 then 180000
            when t71.StorageRate between 300000 and 359999 then 300000
            when t71.StorageRate between 360000 and 599999 then 360000
            when t71.StorageRate between 600000 and 899999 then 600000
            when t71.StorageRate between 900000 and 1199999 then 900000
            when t71.StorageRate between 1200000 and 1799999 then 1200000
            when t71.StorageRate between 1800000 and 3599999 then 1800000
            else 3600000           
          end 
      from Runtime71.dbo.Tag t71, Runtime71.dbo.Topic tp71, Runtime71.dbo.IOServer IO71,
           Runtime.dbo.Topic tpTo, Runtime.dbo.IOServer IOTo
         where  t71.TopicKey = tp71.TopicKey
         and    t71.TagType < 4      -- Just Ana, Discrete and String tags
         and    t71.IOServerKey > 1  -- No System Tags
         and    tp71.IOServerKey = IO71.IOServerKey
         and    t71.IOServerKey = IO71.IOServerKey
         and    IO71.ApplicationName = IOTo.ApplicationName
         and    IO71.ComputerName    = IOTo.ComputerName
         and    tp71.Name            = tpTo.Name
         and    tpTo.IOServerKey     = IOTo.IOServerKey
COMMIT TRAN
go

/* Tag: Other types */
BEGIN TRAN
insert into Tag (TagName, IOServerKey, StorageNodeKey, TopicKey, Description,
            AcquisitionType, StorageType, AcquisitionRate, TagType,
            TimeDeadband, DateCreated, CreatedBy, 
            CurrentEditor, SamplesInActiveImage)
   select t71.TagName, NULL, 1, NULL, t71.Description,
          t71.AcquisitionType, t71.StorageType, t71.AcquisitionRate, t71.TagType,
          t71.TimeDeadband, t71.DateCreated, t71.CreatedBy, 
          t71.CurrentEditor, t71.SamplesInActiveImage
      from Runtime71.dbo.Tag t71
         where t71.TagType > 3
         and   t71.TagName <> ALL
                  (select TagName from Runtime.dbo.Tag where TagType > 3)
COMMIT TRAN
go

/* AnalogTag */
BEGIN TRAN
insert into AnalogTag (TagName, EUKey, MinEU, MaxEU, MinRaw, MaxRaw,
                       Scaling, RawType, ValueDeadband, InitialValue,
                       IntegerSize, SignedInteger)
   select a71.TagName, uTo.EUKey, MinEU, MaxEU, MinRaw, MaxRaw,
          Scaling, RawType,
          CASE
             WHEN ValueDeadband < 0 THEN 0
             WHEN ValueDeadband > 100 THEN 100
             ELSE ValueDeadband
          END, InitialValue,
          IntegerSize, SignedInteger
      from Runtime71.dbo.AnalogTag a71, Runtime71.dbo.Tag t71,
           Runtime71.dbo.EngineeringUnit u71, Runtime.dbo.EngineeringUnit uTo
         where a71.TagName = t71.TagName
         and   t71.IOServerKey > 1   -- exclude system tags
         and   a71.EUKey = u71.EUKey
         and   u71.Unit  = uTo.Unit
COMMIT TRAN
go

/* DiscreteTag */
BEGIN TRAN
insert into DiscreteTag (TagName, MessageKey, InitialValue)
   select d71.TagName, mTo.MessageKey, InitialValue
      from Runtime71.dbo.DiscreteTag d71, Runtime71.dbo.Tag t71,
           Runtime71.dbo.Message m71, Runtime.dbo.Message mTo
         where d71.TagName = t71.TagName
         and   t71.IOServerKey > 1  -- exclude system tags
         and   d71.MessageKey = m71.MessageKey
         and   m71.Message0 + ' ' + m71.Message1 = mTo.Message0 + ' ' + mTo.Message1
COMMIT TRAN
go

/* StringTag */
BEGIN TRAN
insert into StringTag (TagName, InitialValue, MaxLength)
   select s71.TagName, InitialValue, 
       case 
           when  MaxLength = 0 then 131
           when  MaxLength between 1 and 8 then 8
           when  MaxLength between 9 and 16 then 16
           when  MaxLength between 17 and 24 then 24
           when  MaxLength between 25 and 32 then 32
           when  MaxLength between 33 and 48 then 48
           when  MaxLength between 49 and 64 then 64
           when  MaxLength between 65 and 128 then 128
           when  MaxLength between 129 and 131 then 131  -- InTouch Legacy
           when  MaxLength between 132 and 256 then 256
           when  MaxLength between 257 and 512 then 512
          else 512
       end
      from Runtime71.dbo.StringTag s71, Runtime71.dbo.Tag t71
      where s71.TagName = t71.TagName
      and t71.IOServerKey > 1  -- exclude system tag
COMMIT TRAN
go


/* Context */
BEGIN TRAN
delete from Context  -- Reset everything

SET IDENTITY_INSERT Context ON

insert Context (ContextKey, Description)
  select ContextKey, Description from Runtime71.dbo.Context

SET IDENTITY_INSERT Context OFF
COMMIT TRAN
go

/* RateOfChange */
BEGIN TRAN
delete from RateOfChange -- Reset everything

insert into RateOfChange
   select TagName, ContextKey, Value, TimeBase, Priority, Checked
      from Runtime71.dbo.RateOfChange
COMMIT TRAN
go

/* Deviation */
BEGIN TRAN
delete from Deviation -- Reset everything

insert into Deviation
   select TagName, ContextKey, MinorDeviation, MinorChecked, MinorPriority,
          MajorDeviation, MajorChecked, MajorPriority, Target, Deadband
      from Runtime71.dbo.Deviation
COMMIT TRAN
go

/* LimitName */
BEGIN TRAN
delete from LimitName  -- Reset everything

SET IDENTITY_INSERT LimitName ON

insert LimitName (LimitNameKey, Name)
  select LimitNameKey, Name from Runtime71.dbo.LimitName

SET IDENTITY_INSERT LimitName OFF
COMMIT TRAN
go

/* Limit */
BEGIN TRAN
delete from Limit  -- Reset everything

insert into Limit
   select TagName, ContextKey, LimitType, Value, LimitNameKey,
          Priority, Checked, Description
      from Runtime71.dbo.Limit 

COMMIT TRAN
go

/* Event SubSystem */
/* DetectorType */
BEGIN TRAN
insert into DetectorType (Name, Description, EditorClassName, DetectorClassName,
                          ExecutionMode)
   select Name, Description, EditorClassName, DetectorClassName, ExecutionMode
      from Runtime71.dbo.DetectorType
      where DetectorTypeKey > 6  -- only detector created by user
COMMIT TRAN
go

/* ActionType */
BEGIN TRAN
insert into ActionType (Name, Description, EditorClassName, ActionClassName)
   select Name, Description, EditorClassName, ActionClassName
      from Runtime71.dbo.ActionType
      where ActionTypeKey > 6  -- only action created by user
COMMIT TRAN
go

/* EventTag */
BEGIN TRAN
insert into EventTag (TagName, DetectorTypeKey, ActionTypeKey, ScanRate,
                      TimeDeadband, Logged, Status, PostDetectorDelay,
                      UseThreadPool, DetectorString, ActionString,
                      Priority, Edge)
   select e71.TagName, DetectorTypeKey, ActionTypeKey, ScanRate, 
          e71.TimeDeadband, Logged, Status, PostDetectorDelay, UseThreadPool,
          RTRIM(convert(nvarchar(1500),DetectorString)), RTRIM(convert(nvarchar(1500),ActionString)),
          0, Edge
      from Runtime71.dbo.EventTag e71, Runtime71.dbo.Tag t71
         where e71.TagName = t71.TagName
         and e71.DetectorTypeKey < 7 -- no migration of user's detectors
         and e71.ActionTypeKey < 7   -- no migration of user's actions
         and e71.TagName <> ALL
             (select TagName from Runtime.dbo.EventTag)


SET IDENTITY_INSERT TimeDetectorDetail ON

insert into TimeDetectorDetail (TimeDetectorDetailKey, TagName, FrequencyID, Periodicity, StartDateTime, 
                                RunTimeDay, RunTimeHour, RunTimeMin)
   select TimeDetectorDetailKey, t71.TagName, FrequencyID, Periodicity, StartDateTime, 
          RunTimeDay, RunTimeHour, RunTimeMin
      from Runtime71.dbo.TimeDetectorDetail t71, Runtime71.dbo.EventTag e71
         where t71.TagName <> ALL
             (select TagName from Runtime.dbo.TimeDetectorDetail)
         and e71.TagName = t71.TagName
         and e71.DetectorTypeKey = 6

SET IDENTITY_INSERT TimeDetectorDetail OFF

COMMIT TRAN
go 

/* Update EventTag.DetectorString to new syntax */
BEGIN TRAN
/* The migration requires parsing of the old string so it */
/* has to be done on a 1 by 1 basis                       */
declare @thetag varchar(100)
declare @query  varchar(1500)
declare @edge   varchar(50)
declare @detect varchar(50)
declare @value  varchar(50)
declare @res    varchar(50)
declare @nquery varchar(1500)
declare @operator varchar(10)
declare @sign   int


declare thedetector cursor for
  select TagName, DetectorString from EventTag
     where DetectorTypeKey in (4, 5) -- Analog/Discrete SpecificValue 
     and TagName <> 'SysStatusEvent'

open thedetector
fetch next from thedetector into @thetag, @query

while @@FETCH_STATUS = 0
begin
   set @res  = '0'
   -- Get the edge
   if (select PATINDEX( '%LEADING%', UPPER(@query))) > 0
   begin
      set @edge = ', "Leading", '
   end
   else
   if (select PATINDEX( '%TRAILING%', UPPER(@query))) > 0
   begin
      set @edge = ', "Trailing", '
   end
   if (select PATINDEX( '%BOTH%', UPPER(@query))) > 0
   begin
      set @edge = ', "Both", '
   end
   else
   if (select PATINDEX( '%NONE%', UPPER(@query))) > 0
   begin 
      set @edge = ', "None", '
      set @res  = (select SUBSTRING(@query, PATINDEX( '% wwResolution %', @query) + 14, 
             PATINDEX('%SELECT %', @query) - PATINDEX( '% wwResolution %', @query) - 14))
   end

   -- find the detection tag
   set @detect = (select SUBSTRING(@query, PATINDEX( '% AND %', @query)+ 5 , 
       PATINDEX('% IS NOT%', @query) - (PATINDEX( '% AND %', @query)+ 5)))

   -- find the detection value, have to take the logic sign in consideration
   if (PATINDEX('% ' + @detect + ' = %', @query)) > 0
   begin
      set @operator = ', "=",'
      set @value = (select SUBSTRING(@query, PATINDEX( '% ' + @detect + ' = %', @query)+ len(@detect)+ 4 , 
          PATINDEX('% AND %', @query) - (PATINDEX( '% ' + @detect + ' = %', @query)+ len(@detect) + 4)))
   end
   else
   if (PATINDEX('% ' + @detect+ ' >= %', @query)) > 0
   begin
      set @operator = ', ">=",'
      set @value = (select SUBSTRING(@query, PATINDEX( '% ' + @detect + ' >= %', @query)+ len(@detect)+ 5  , 
          PATINDEX('% AND %', @query) - (PATINDEX( '% ' + @detect + ' >= %', @query)+ len(@detect)+ 5)))
   end
   else
   if (PATINDEX('% ' + @detect+ ' <= %', @query)) > 0
   begin
      set @operator = ', "<=",'
      set @value = (select SUBSTRING(@query, PATINDEX( '% ' + @detect + ' <= %', @query)+ len(@detect)+ 5  , 
          PATINDEX('% AND %', @query) - (PATINDEX( '% ' + @detect + ' <= %', @query)+ len(@detect)+ 5)))
   end
   else
   if (PATINDEX('% ' + @detect + ' > %', @query)) > 0
   begin
      set @operator = ', ">",'
      set @value = (select SUBSTRING(@query, PATINDEX( '% ' + @detect + ' > %', @query)+ len(@detect)+ 4 , 
          PATINDEX('% AND %', @query) - (PATINDEX( '% ' + @detect + ' > %', @query)+ len(@detect) + 4)))
   end
   else
   if (PATINDEX('% ' + @detect + ' < %', @query)) > 0
   begin
      set @operator = ', "<",'
      set @value = (select SUBSTRING(@query, PATINDEX( '% ' + @detect + ' < %', @query)+ len(@detect)+ 4 , 
          PATINDEX('% AND %', @query) - (PATINDEX( '% ' + @detect + ' < %', @query)+ len(@detect) + 4)))
   end
   else
   if (PATINDEX('% ' + @detect+ ' <> %', @query)) > 0
   begin
      set @operator = ', "<>" '
      set @value = (select SUBSTRING(@query, PATINDEX( '% ' + @detect + ' <> %', @query)+ len(@detect)+ 5  , 
          PATINDEX('% AND %', @query) - (PATINDEX( '% ' + @detect + ' <> %', @query)+ len(@detect)+ 5)))
   end
 
   -- Build the ole-db query
   set @nquery = 
     'exec aaEventDetection "' + @detect + '"' + @operator + ' ' + @value + @edge + @res + ', "@StartTime", "@EndTime"'


   update EventTag set DetectorString = RTRIM(@nquery)
      where TagName = @thetag  

   fetch next from thedetector into @thetag, @query
end

close thedetector
deallocate thedetector

COMMIT TRAN
go

/* Update EventTag.ActionString snapshots and summary to new syntax */
BEGIN TRAN
update EventTag set ActionString = 
'exec aaEventSnapshotInsert @EventLogKey, "@EventTime", "@EventTagName"'  
where ActionTypeKey = 3
and TagName <> 'SysStatusEvent'
go

update EventTag set ActionString = 
'exec aaSummaryActionInsert "@EventTagName", "@EventTime"'
where ActionTypeKey = 6
and TagName <> 'SysStatusEvent'
go

/* Set EventTag Status as per prior to the migration */
/* (Insert trigger would have modified those) */
update EventTag set Status = e71.Status
   from Runtime71.dbo.EventTag e71, Runtime.dbo.EventTag eTo
      where e71.TagName = eTo.TagName

/* Disable events with Generic SQL for Detector or Action */
update EventTag set Status = 98
   where DetectorTypeKey = 3
   or    ActionTypeKey   = 2

COMMIT TRAN
go


/* SnapshotTag */
delete from SnapshotTag
   where EventTagName <> 'SysStatusEvent'
go
BEGIN TRAN
SET IDENTITY_INSERT SnapshotTag ON

insert into SnapshotTag (SnapshotTagKey, TagName, EventTagName, TagType) 
   select SnapshotTagKey, tTo.TagName, EventTagName, tTo.TagType
      from Runtime71.dbo.SnapshotTag st71, Runtime.dbo.Tag tTo
        where tTo.TagName = st71.TagName         -- Make sure that the tag is still there
        and st71.TagName <> 'SysSpaceMain'       -- Avoid conflict with new System Tags
        and st71.EventTagName = 'SysStatusEvent'

insert into SnapshotTag (SnapshotTagKey, TagName, EventTagName, TagType) 
   select SnapshotTagKey, tTo.TagName, EventTagName, tTo.TagType
      from Runtime71.dbo.SnapshotTag st71, Runtime.dbo.Tag tTo
        where tTo.TagName = st71.TagName          -- Make sure that the tag is still there
        and st71.EventTagName <> 'SysStatusEvent'

SET IDENTITY_INSERT SnapshotTag OFF

COMMIT TRAN
go

/* InTouchNode */
BEGIN TRAN
insert into InTouchNode (NodeKey, MachineName, ApplicationName, Path, Description,
                         DuplicateChar, PrefixOrSuffix, AlwaysModifyName,
                         ImportPlantTags, ImportMemoryTags, FixedStorageRate,
                         ImportRoute)
   select NodeKey, MachineName, ApplicationName, Path, Description,
          DuplicateChar, PrefixOrSuffix, AlwaysModifyName,
          ImportPlantTags, ImportMemoryTags, FixedStorageRate,
          ImportRoute
      from Runtime71.dbo.InTouchNode
go
/* TopicImportInfo */
insert into TopicImportInfo (NodeKey, DdeSourceKey, SourceName, ApplicationName,
                             TopicName, RequestInitialData, AlwaysAdvise,
                             DefaultStorageRate, TimeDeadband, Import,
                             ProtocolType)
   select NodeKey, DdeSourceKey, SourceName, ApplicationName,
          TopicName, RequestInitialData, AlwaysAdvise,
          DefaultStorageRate, TimeDeadband, Import,ProtocolType
      from Runtime71.dbo.TopicImportInfo
go
/* InTouchSpecific */
insert into InTouchSpecific (NodeKey, TagName, OriginalName, TypeInfo,
                             InInSQL, Comment)
   select NodeKey, TagName, OriginalName, TypeInfo, InPrealism, Comment
      from Runtime71.dbo.InTouchSpecific
COMMIT TRAN
go

/* PublicNameSpace */
/* Migration Rule: there are new Keys in the 90 DB (16 & 17)
** when moving data accross, any NameKey / ParentKey > 15 will increased by 2 */
BEGIN TRAN
/* This next step is necessary to allow Children to be inserted at the same time as the Parent */
alter table PublicNameSpace disable trigger tI_PublicNameSpace
set IDENTITY_INSERT PublicNameSpace ON
go
insert into PublicNameSpace (NameKey, Type, ParentKey, Name, ConfigStor)
   select NameKey + 2, Type,
          CASE 
             when ParentKey > 15 then (ParentKey + 2)
             else ParentKey
          end,
          Name, ConfigStor
      from Runtime71.dbo.PublicNameSpace
         where NameKey > 15
go
set IDENTITY_INSERT PublicNameSpace OFF
alter table PublicNameSpace enable trigger tI_PublicNameSpace
COMMIT TRAN
go

/* Public Group Tag */
/* Same rule as above for NameKey */
/* For wwDomainTagKey, we need to validate with the TagName (no guarantee that it is the same number) */
/* Query is split in two to handle the AND clause properly (NameKey issue here above)*/
BEGIN TRAN
insert into PublicGroupTag (NameKey, wwDomainTagKey)
   select pgt71.NameKey,
          trTo.wwDomainTagKey
      from Runtime71.dbo.PublicGroupTag pgt71, Runtime71.dbo.TagRef tr71,
           Runtime.dbo.TagRef trTo
         where tr71.ServerKey = trTo.ServerKey
         and   tr71.TagName   = trTo.TagName
         and   pgt71.wwDomainTagKey = tr71.wwDomainTagKey
         and   pgt71.NameKey <= 15
         -- And we cannot get duplication...
         and  ( RTRIM(CAST(pgt71.NameKey as varchar(10))) + ' ' + RTRIM(CAST(trTo.wwDomainTagKey as varchar(10))) )
                 <> ALL
                 (select RTRIM(CAST(NameKey as varchar(10))) + ' ' + RTRIM(CAST(wwDomainTagKey as varchar(10)))
                     from PublicGroupTag) 
go
insert into PublicGroupTag (NameKey, wwDomainTagKey)
   select pgt71.NameKey + 2,
          trTo.wwDomainTagKey
      from Runtime71.dbo.PublicGroupTag pgt71, Runtime71.dbo.TagRef tr71,
           Runtime.dbo.TagRef trTo
         where tr71.ServerKey = trTo.ServerKey
         and   tr71.TagName   = trTo.TagName
         and   pgt71.wwDomainTagKey = tr71.wwDomainTagKey
         and   pgt71.NameKey > 15
         -- And we cannot get duplication...
         and  ( RTRIM(CAST((pgt71.NameKey + 2)  as varchar(10))) + ' ' + RTRIM(CAST(trTo.wwDomainTagKey as varchar(10))) )
                 <> ALL
                 (select RTRIM(CAST(NameKey as varchar(10))) + ' ' + RTRIM(CAST(wwDomainTagKey as varchar(10)))
                     from PublicGroupTag) 
COMMIT TRAN
go

/* TagGroup */
BEGIN TRAN
insert into TagGroup (GroupID, Description, CreatedDate, CreatedBy, Type)
   select GroupID, Description, CreatedDate, CreatedBy, Type
      from Runtime71.dbo.TagGroup
         where GroupID > 1  -- 1 is created by Runtime.sql for System Tags 
go
/* Group Tag List */
insert into GroupTagList (GroupID, wwDomainTagKey, Triggerval)
   select gtl71.GroupID, trTo.wwDomainTagKey, gtl71.Triggerval
      from Runtime71.dbo.GroupTagList gtl71, Runtime71.dbo.TagRef tr71,
           Runtime.dbo.TagRef trTo
         where tr71.ServerKey = trTo.ServerKey
         and   tr71.TagName   = trTo.TagName
         and   gtl71.wwDomainTagKey = tr71.wwDomainTagKey
         and   gtl71.GroupID > 1  -- see above
COMMIT TRAN
go

/* sysusers */
/* need to add all custom users from Runtime71 */

declare @name sysname
declare userlist cursor for
  select name from Runtime71.dbo.sysusers
      where name not in (select name from Runtime.dbo.sysusers)
	  and uid <> gid

open userlist
fetch next from userlist into @name
while @@FETCH_STATUS = 0
BEGIN
/* Verify that the login corresponding to the user exists on the server.   */
/* It probably will not if this is a migration to a new server.            */
    if exists(select name from master..syslogins where name = @name)
       exec sp_grantdbaccess @name, @name
	else
	   print 'InSQL 7.1 user not added: ' + @name
    fetch next from userlist into @name
END
close userlist
deallocate userlist

/* sysmembers */
declare rolemembers scroll cursor for
  select memberuid, groupuid from Runtime71.dbo.sysmembers

declare @mid71 int, @midTo int, @member sysname
declare @gid71 int, @gidTo int, @role sysname

open rolemembers
fetch next from rolemembers into @mid71, @gid71

while @@FETCH_STATUS = 0
begin
   /* get the Runtime 90 memberid */
   select  @member = ruTo.name, @midTo = ruTo.uid 
      from Runtime.dbo.sysusers ruTo,
           Runtime71.dbo.sysusers ru71
       where ruTo.name = ru71.name
       and   ru71.uid = @mid71 
   /* get the Runtime 90 groupid */
   select  @role = ruTo.name, @gidTo = ruTo.uid
      from Runtime.dbo.sysusers ruTo,
           Runtime71.dbo.sysusers ru71
       where ruTo.name = ru71.name
       and   ru71.uid = @gid71 
   /* insert in Runtime 90 sysmembers */
   if not exists
            (select memberuid, groupuid from Runtime.dbo.sysmembers
                     where memberuid = @midTo and groupuid = @gidTo)
            and @role is not NULL
            and @member is not NULL
   begin
      exec sp_addrolemember @role, @member
   end 
   
   fetch next from rolemembers into @mid71, @gid71
end

close rolemembers
deallocate rolemembers

/* user Detail */
exec aaUserDetailUpdate

/* Update User Detail Access Level */
update UserDetail set AccessLevel = u71.AccessLevel   
   from Runtime71.dbo.UserDetail u71, Runtime.dbo.UserDetail uTo
     where u71.UserName = uTo.UserName
go

/* PrivateNameSpace */
BEGIN TRAN
/* This next step is necessary to allow Children to be inserted at the same time as the Parent */
alter table PrivateNameSpace disable trigger tI_PrivateNameSpace
set IDENTITY_INSERT PrivateNameSpace ON

insert into PrivateNameSpace (UserKey, NameKey, Type, ParentKey, Name, ConfigStor)
   select udTo.UserKey, pns71.NameKey, pns71.Type, pns71.ParentKey, pns71.Name, pns71.ConfigStor
      from Runtime71.dbo.PrivateNameSpace pns71, Runtime71.dbo.UserDetail ud71, 
           Runtime.dbo.UserDetail udTo
         where ud71.UserName = udTo.UserName
         and   ud71.UserKey  = pns71.UserKey
         and   pns71.NameKey > 1  -- root key

set IDENTITY_INSERT PrivateNameSpace OFF
alter table PrivateNameSpace enable trigger tI_PrivateNameSpace
go
/* PrivateGroupTag */
insert into PrivateGroupTag (NameKey, UserKey, wwDomainTagKey)
   select pgt71.NameKey, udTo.UserKey, trTo.wwDomainTagKey
      from Runtime71.dbo.PrivateGroupTag pgt71, Runtime71.dbo.TagRef tr71,
           Runtime71.dbo.UserDetail ud71,
           Runtime.dbo.UserDetail udTo, Runtime.dbo.TagRef trTo
         where ud71.UserName = udTo.UserName
         and   ud71.UserKey = pgt71.UserKey
         and   pgt71.wwDomainTagKey = tr71.wwDomainTagKey
         and   tr71.ServerKey = trTo.ServerKey
         and   tr71.TagName = trTo.TagName
COMMIT TRAN
go

/* Annotation */
BEGIN TRAN
set IDENTITY_INSERT Annotation ON

insert into Annotation (AnnotationKey, UserKey, TagName, DateCreated,
                        Content, DateTime, Value)
   select a71.AnnotationKey, udTo.UserKey, a71.TagName, a71.DateCreated,
          RTRIM(convert(nvarchar(1000),a71.Content)), a71.DateTime, a71.Value
      from Runtime71.dbo.Annotation a71, Runtime71.dbo.UserDetail ud71,
           Runtime.dbo.UserDetail udTo, Runtime.dbo.Tag tTo
         where ud71.UserName = udTo.UserName
         and   ud71.UserKey = a71.UserKey
         and   a71.TagName = tTo.TagName  -- Be sure the TagName is still there

set IDENTITY_INSERT Annotation OFF
COMMIT TRAN
go

/* WideTableDictionary */
BEGIN TRAN
insert into WideTableDictionary (UserKey, TagName)
   select udTo.UserKey, wtd71.TagName
      from Runtime71.dbo.WideTableDictionary wtd71, Runtime71.dbo.UserDetail ud71,
           Runtime.dbo.UserDetail udTo, Runtime.dbo.Tag tTo
         where ud71.UserName = udTo.UserName
         and   ud71.UserKey = wtd71.UserKey
         and   wtd71.TagName = tTo.TagName  -- Be sure the TagName is still there 
COMMIT TRAN
go


/* Summary Sub-System */
truncate table SummaryOperation
go
BEGIN TRAN
set IDENTITY_INSERT SummaryOperation ON

insert into SummaryOperation (OperationKey, TagName, CalcType, Description, Duration,
                              Resolution, TimeStamp, Frequency, SourceType)
   select OperationKey, TagName, CalcType, Description, Duration, Resolution,
          TimeStamp, Frequency, SourceType
      from Runtime71.dbo.SummaryOperation

set IDENTITY_INSERT SummaryOperation OFF
go
set IDENTITY_INSERT SummaryTagList ON

insert into SummaryTagList (SumVarKey, TagName, OperationKey, LowerLimit,
                            UpperLimit, Description)
   select SumVarKey, tTo.TagName, OperationKey, LowerLimit, UpperLimit, stl71.Description
      from Runtime71.dbo.SummaryTagList stl71, Runtime.dbo.Tag tTo
          where tTo.TagName = stl71.TagName

set IDENTITY_INSERT SummaryTagList OFF

COMMIT TRAN
go

/* Modification Tracking */
/* Note : ModBinary and ModText are not migrated */
BEGIN TRAN
set IDENTITY_INSERT ModLogTable ON

insert into ModLogTable (ModTableKey, id, ModType, RowKey, UserKey, DateTime, UserName)
   select m71.ModKey, sysTo.id, N'U', N'-', uTo.UserKey, m71.DateTime, u71.UserName
      from Runtime71.dbo.ModLog m71, Runtime71.dbo.InSQLSysObjects sys71,
           Runtime.dbo.HistorianSysObjects sysTo, Runtime71.dbo.UserDetail u71,
           Runtime.dbo.UserDetail uTo
         where sysTo.Name = sys71.Name
         and   uTo.UserName = u71.UserName
         and   m71.UserKey = u71.UserKey
         and   m71.id = sys71.id

set IDENTITY_INSERT ModLogTable OFF
go

insert into ModLogColumn (ModTableKey, ColumnName, OldValue, NewValue)
   select m71.ModKey, m71.ColumnName, convert(sql_variant,OldValue), 
             convert(sql_variant, NewValue)
      from Runtime71.dbo.ModLog m71, Runtime71.dbo.ModInteger mod
         where m71.ModKey = mod.ModKey

insert into ModLogColumn (ModTableKey, ColumnName, OldValue, NewValue)
   select m71.ModKey, m71.ColumnName, convert(sql_variant,OldValue), 
             convert(sql_variant, NewValue)
      from Runtime71.dbo.ModLog m71, Runtime71.dbo.ModDecimal mod
         where m71.ModKey = mod.ModKey

insert into ModLogColumn (ModTableKey, ColumnName, OldValue, NewValue)
   select m71.ModKey, m71.ColumnName, convert(sql_variant,OldValue), 
             convert(sql_variant, NewValue)
      from Runtime71.dbo.ModLog m71, Runtime71.dbo.ModBit mod
         where m71.ModKey = mod.ModKey

insert into ModLogColumn (ModTableKey, ColumnName, OldValue, NewValue)
   select m71.ModKey, m71.ColumnName, convert(sql_variant,OldValue), 
             convert(sql_variant, NewValue)
      from Runtime71.dbo.ModLog m71, Runtime71.dbo.ModChar mod
         where m71.ModKey = mod.ModKey

insert into ModLogColumn (ModTableKey, ColumnName, OldValue, NewValue)
   select m71.ModKey, m71.ColumnName, convert(sql_variant,OldValue), 
             convert(sql_variant, NewValue)
      from Runtime71.dbo.ModLog m71, Runtime71.dbo.ModDateTime mod
         where m71.ModKey = mod.ModKey

insert into ModLogColumn (ModTableKey, ColumnName, OldValue, NewValue)
   select m71.ModKey, m71.ColumnName, convert(sql_variant,OldValue), 
             convert(sql_variant, NewValue)
      from Runtime71.dbo.ModLog m71, Runtime71.dbo.ModReal mod
         where m71.ModKey = mod.ModKey

COMMIT TRAN
go

/* SystemParameter */
/* update just the user configured rows */
BEGIN TRAN
update SystemParameter set Value =
   (select NumericValue from Runtime71.dbo.NamedSystemParameter
       where Name = 'EventStorageDuration')
   where Name = 'EventStorageDuration'


if (select NumericValue from Runtime71.dbo.NamedSystemParameter
       where Name = 'ModLogTrackingStatus')  =  1
begin
   update SystemParameter set Value = 2
       where Name = 'ModLogTrackingStatus'
end

update SystemParameter set Value =
   (select NumericValue from Runtime71.dbo.NamedSystemParameter
       where Name = 'SummaryStorageDuration')
   where Name = 'SummaryStorageDuration'
COMMIT TRAN
go

/* SQLTemplate table                 */
/* Add templates created by the user */
BEGIN TRAN

insert into SQLTemplate (Description, Type, Script)
   select Description, Type, Script 
       from Runtime71.dbo.SQLTemplate
           where TemplateKey > 10

COMMIT TRAN
go
           
/* Clean up the Status columns and Config table */
BEGIN TRAN
update Tag set Status = 0
update Topic set Status = 0
update IOServer set Status = 0
update IODriver set Status = 0
update StorageLocation set Status = 0
update SnapshotDetail set Status = 0
update SystemParameter set Status = 0
COMMIT TRAN
go

truncate table ConfigStatusPending
go

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


