Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Table         : [JobComputers_tbl]'
Print ' Creation Date : 22.03.2006'
Print ' Autor         : J.Feuerstein'
Print ' '
Print '-------------------------------------------------------------------------'

use [#DB_N#]

Declare @MyActionMsg1 nvarchar (max)
Declare @MyTableName nvarchar (max)
    set @MyTableName='JobComputers_tbl'

--------------------------------------------------------------------------------
-- verzweigen auf TableCreate: oder TableUpdate:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[JobComputers_tbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  BEGIN
   goto TableUpdate
  END
ELSE
  BEGIN
   goto TableCreate
  END
  


--------------------------------------------------------------------------------
TableCreate:
  set @MyActionMsg1=
      '-- Table: [JobComputers_tbl] dose not exist and will be created new'
  Print (@MyActionMsg1)

  -- Info: bei Struktur anpassungen GitterVar_Cls.ReplaceTextString beachten
  CREATE TABLE [dbo].[JobComputers_tbl] (
          [Id] [uniqueidentifier] NOT NULL ,
          [ComputerName]         [nvarchar]  (64) ,
          [ComputerFQDN]         [nvarchar]  (256) , -- Full Qualified ComputerDomainName (new 09.07.2010)
          [ComputerAktiv]        [bit] NULL ,
          [TcSiteId_Default_id_IsRefTo_Sites_tbl_id] [nvarchar] (64) ,
          [TcUser]               [nvarchar] (32) ,
          [TcPassword]           [nvarchar] (64) ,   -- #LUp:22.03.2017/J.Fes set from 32 to 64
          [TcPasswordFile]       [nvarchar] (265) ,  -- #New:18.01.2018/J.Fes stores name or location for password file
          [TimeSchedule]         [nvarchar] (max) ,
          [JobStateInfoCur]      [nvarchar] (max) ,  -- stellt den aktuellen/letzte JobStatus Info des JobCLinets dar neu 16.01.2008
          [JobControlActivInfo]  [nvarchar] (max) ,  -- #New:31.10.2018/S.Gueth, stores last JobControlActivStatus Rule information
          [LastConnectionDateTimeLocal]   [datetime] NULL ,
          [LastConnectionDateTimeGMT]     [datetime] NULL ,
          -- [LastConnectionDateTimeGMT_DIF]  AS  Right('0000' + CONVERT(varchar,FLOOR(DateDiff(ss, LastConnectionDateTimeGMT,GetDate())/60)),4) + ' [min]:' + Right('0'+CONVERT(varchar,DateDiff(ss, LastConnectionDateTimeGMT,GetDate())-FLOOR(DateDiff(ss, LastConnectionDateTimeGMT,GetDate())/60)*60),2) + ' [sec]' ,
          [OS]                   [nvarchar] (max) ,
--          [OsUser]               [nvarchar] (32) ,
--          [OsPassword]           [nvarchar] (32) ,
--          [OSDestrib_DP]         [nvarchar] (max) , -- Destrib Path
          [Memory]               [nvarchar] (32) ,
          [CPUMhz]               [nvarchar] (32) ,
          [IP]                   [nvarchar] (32)  NULL ,
          [ComputerDescription]  [nvarchar] (128) ,
          [ComputerAssignment]   [nvarchar] (32)  , -- Server Client (Zuteilung {f}; Zuweisung {f}; Verwendung {f}  assignment  )
          [SelObjectsBy_Volume]  [nvarchar] (max) , -- JobComputer bezogener Volumefilter
          [SelObjectsBy_Group]   [nvarchar] (max) , -- Defines Otional filter for JC to select JobObjects by Volume
          [JobRulesName_Filter]  [nvarchar] (max) , -- Defines Otional filter of JobRules that are used by this JobComputer (JC) #New: 27.02.2014/J.Fes
          [NxVersion]            [nvarchar] (128) ,
          [TcVersion]            [nvarchar] (128) ,
          [Place]                [nvarchar] (64) ,
          [Notice]               [nvarchar] (max) ,
          [Group]                [nvarchar] (32) ,    -- Group
          [Order]                [nvarchar] (32) ,    -- Reihenfolge
          [Custom01]             [nvarchar] (64) ,
          [Custom02]             [nvarchar] (64) ,
          [Custom03]             [nvarchar] (64) ,
          [Custom04]             [nvarchar] (64) ,
          [System_CPUUtilization] [nvarchar] (128),
          [System_MemoryUsage]    [nvarchar] (128),
          [JCRuntimeDuration]     [nvarchar] (128)

  ) ON [PRIMARY]

  goto CheckCreateIndex


--------------------------------------------------------------------------------
TableUpdate:

  set @MyActionMsg1=
       '-- Table: [JobComputers_tbl] exist and will be updated'
  Print (@MyActionMsg1)

  Declare @ColumeName nvarchar (256)
  Declare @ColumeNameOld nvarchar (256)
  Declare @ColumeNameNew nvarchar (256)
  Declare @ExecuteSQLstr nvarchar (max)


  set @ColumeName='ComputerFQDN'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [(new 09.07.2010)]')
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (256) NULL'
      execute (@ExecuteSQLstr)
    end

  set @ColumeName='JobRulesName_Filter'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column: [' + @ColumeName + '] Defines Otional filter of JobRules.Name that are used by this JobComputer (JC) [(new 27.02.2014/J.Fes)]')
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (max) NULL'
      execute (@ExecuteSQLstr)
    end

  set @ColumeName='SelObjectsBy_Group'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column: [' + @ColumeName + '] Defines Otional filter for JC to select JobObjects [(new 16.04.2014/J.Fes)]')
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (max) NULL'
      execute (@ExecuteSQLstr)
    end

  -- 09.07.2010 21:25 - umbennenen von User..Custom
  set @ColumeNameOld='User01'
  set @ColumeNameNew='Custom01'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:' + @ColumeNameOld + ' to:' + @ColumeNameNew)
      -- EXEC sp_rename '[dbo].[TaskScheduler_tbl].[Active]','Aktive','COLUMN'
      set @ExecuteSQLstr='sp_rename ' + '''' + '[dbo].[' + @MyTableName + '].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
      print (@ExecuteSQLstr)
      execute (@ExecuteSQLstr)
    end
  set @ColumeNameOld='User02'
  set @ColumeNameNew='Custom02'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:' + @ColumeNameOld + ' to:' + @ColumeNameNew)
      -- EXEC sp_rename '[dbo].[TaskScheduler_tbl].[Active]','Aktive','COLUMN'
      set @ExecuteSQLstr='sp_rename ' + '''' + '[dbo].[' + @MyTableName + '].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
      print (@ExecuteSQLstr)
      execute (@ExecuteSQLstr)
    end
  set @ColumeNameOld='User03'
  set @ColumeNameNew='Custom03'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:' + @ColumeNameOld + ' to:' + @ColumeNameNew)
      -- EXEC sp_rename '[dbo].[TaskScheduler_tbl].[Active]','Aktive','COLUMN'
      set @ExecuteSQLstr='sp_rename ' + '''' + '[dbo].[' + @MyTableName + '].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
      print (@ExecuteSQLstr)
      execute (@ExecuteSQLstr)
    end
  set @ColumeNameOld='User04'
  set @ColumeNameNew='Custom04'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:' + @ColumeNameOld + ' to:' + @ColumeNameNew)
      -- EXEC sp_rename '[dbo].[TaskScheduler_tbl].[Active]','Aktive','COLUMN'
      set @ExecuteSQLstr='sp_rename ' + '''' + '[dbo].[' + @MyTableName + '].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
      print (@ExecuteSQLstr)
      execute (@ExecuteSQLstr)
    end

  -- New 06.10.2015 S.Gueth
  set @ColumeName='System_CPUUtilization'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column: [' + @ColumeName + '] Defines CPU load for JobClient [(new 06.10.2015/S.Gueth)]')
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (128) NULL'
      execute (@ExecuteSQLstr)
    end

  -- New 06.10.2015 S.Gueth
  set @ColumeName='System_MemoryUsage'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column: [' + @ColumeName + '] Defines Memory load for JobClient [(new 06.10.2015/S.Gueth)]')
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (128) NULL'
      execute (@ExecuteSQLstr)
    end

  -- New 08.10.2015 S.Gueth
  set @ColumeName='JCRuntimeDuration'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column: [' + @ColumeName + '] Defines Runtime duration of JobClient [(new 08.10.2015/S.Gueth)]')
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (128) NULL'
      execute (@ExecuteSQLstr)
    end

  -- #Doc: 13.04.2017/J.Fes renamed
  set @ColumeNameOld='TceUser'
  set @ColumeNameNew='TcUser'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:' + @ColumeNameOld + ' to:' + @ColumeNameNew)
      set @ExecuteSQLstr='sp_rename ' + '''' + '[dbo].[' + @MyTableName + '].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
      print (@ExecuteSQLstr)
      execute (@ExecuteSQLstr)
    end

  -- #Doc: 13.04.2017/J.Fes renamed
  set @ColumeNameOld='TcePassword'
  set @ColumeNameNew='TcPassword'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:' + @ColumeNameOld + ' to:' + @ColumeNameNew)
      set @ExecuteSQLstr='sp_rename ' + '''' + '[dbo].[' + @MyTableName + '].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
      print (@ExecuteSQLstr)
      execute (@ExecuteSQLstr)
    end

  -- #Doc: 13.04.2017/J.Fes renamed
  set @ColumeNameOld='TceSiteId_Default_id_IsRefTo_Sites_tbl_id'
  set @ColumeNameNew='TcSiteId_Default_id_IsRefTo_Sites_tbl_id'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:' + @ColumeNameOld + ' to:' + @ColumeNameNew)
      set @ExecuteSQLstr='sp_rename ' + '''' + '[dbo].[' + @MyTableName + '].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
      print (@ExecuteSQLstr)
      execute (@ExecuteSQLstr)
    end

  -- #Doc: 13.04.2017/J.Fes renamed
  set @ColumeNameOld='TceVersion'
  set @ColumeNameNew='TcVersion'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:' + @ColumeNameOld + ' to:' + @ColumeNameNew)
      set @ExecuteSQLstr='sp_rename ' + '''' + '[dbo].[' + @MyTableName + '].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
      print (@ExecuteSQLstr)
      execute (@ExecuteSQLstr)
    end


  --#New 13.04.2017 J.Fes
  set @ColumeName='TcPassword'
  if (Select Col_length(@MyTableName,@ColumeName)) <> 128*2
    BEGIN
     print ('UpSize Column:' + @ColumeName + '  [to 128 char (Update: 13.04.2017)]')
     set @ExecuteSQLstr=  'ALTER TABLE [dbo].[' + @MyTableName + ']' + ' ALTER COLUMN [' + @ColumeName + '] nvarchar(128)'
     execute sp_executesql @ExecuteSQLstr
    END


  --#New 22.03.2017 J.Fes
  set @ColumeName='TimeSchedule'
  if (Select Col_length(@MyTableName,@ColumeName)) <> -1
    BEGIN
     set @ExecuteSQLstr=Col_length(@MyTableName,@ColumeName) / 2
     print ('UpSize Column:' + @ColumeName + '  [from ' + @ExecuteSQLstr + ' 32 to max char (Update: 22.03.2017)]')
     set @ExecuteSQLstr=  'ALTER TABLE [dbo].[' + @MyTableName + ']' + ' ALTER COLUMN [' + @ColumeName + '] nvarchar(max)'
     execute sp_executesql @ExecuteSQLstr
    END

  -- #Doc: 13.04.2017 removed columes OsUser,OsPassword,OSDestrib_DP J.Fes
  set @ColumeName='OsUser'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Delete Column:' + @ColumeName)
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] DROP COLUMN [' + @ColumeName +']'
      execute (@ExecuteSQLstr)
    end
  set @ColumeName='OsPassword'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Delete Column:' + @ColumeName)
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] DROP COLUMN [' + @ColumeName +']'
      execute (@ExecuteSQLstr)
    end
  set @ColumeName='OSDestrib_DP'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Delete Column:' + @ColumeName)
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] DROP COLUMN [' + @ColumeName +']'
      execute (@ExecuteSQLstr)
    end

  -- New 18.01.2018 J.Fes
  set @ColumeName='TcPasswordFile'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column: [' + @ColumeName + '] stores name or location for password file [(new 18.01.2018/J.Fes)]')
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (256) NULL'
      execute (@ExecuteSQLstr)
    end

  -- New 31.10.2018 S.Gueth
  set @ColumeName='JobControlActivInfo'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column: [' + @ColumeName + '] stores last JobControlActivStatus Rule information [(new 31.10.2018/S.Gueth)]')
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (max)'
      execute (@ExecuteSQLstr)
    end

goto CheckCreateIndex
--------------------------------------------------------------------------------
  CheckCreateIndex:

  If NOT Exists (select Name from sysindexes where Name = 'JobComputers_tbl_pk')
  begin
     print (' --> create index: JobComputers_tbl_pk')
     ALTER TABLE [dbo].[JobComputers_tbl]
       ADD CONSTRAINT [JobComputers_tbl_pk]
       PRIMARY KEY NONCLUSTERED ([Id])
       WITH (
         FILLFACTOR = 80,
         PAD_INDEX = OFF,
         STATISTICS_NORECOMPUTE = OFF,
         ALLOW_ROW_LOCKS = ON,
         ALLOW_PAGE_LOCKS = ON)
  end

  If NOT Exists (select Name from sysindexes where Name = 'JobComputers_tbl_uq')
  begin
     Print(' --> create index: JobComputers_tbl_uq')
     ALTER TABLE [dbo].[JobComputers_tbl]
        ADD CONSTRAINT [JobComputers_tbl_uq]
        UNIQUE NONCLUSTERED ([ComputerName])
        WITH (
          FILLFACTOR = 80,
          PAD_INDEX = OFF,
          IGNORE_DUP_KEY = OFF,
          STATISTICS_NORECOMPUTE = OFF,
          ALLOW_ROW_LOCKS = ON,
          ALLOW_PAGE_LOCKS = ON)
  end
  
--------------------------------------------------------------------------------
EndeExit:
 Print '------------------------------------------------------------------------'
 Print 'Done on Database: #DB_N#'
 Print '------------------------------------------------------------------------'

GO
