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

use [#DB_N#]

Declare @MyActionMsg1 nvarchar (max)

Declare @MyTableName nvarchar (max)
set @MyTableName='TaskScheduler_tbl'

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

--------------------------------------------------------------------------------
TableCreate:
  set @MyActionMsg1='-- Table: ['+ @MyTableName + '] dose not exist and will be created new'
  Print (@MyActionMsg1)
  CREATE TABLE [dbo].[TaskScheduler_tbl] (
          [Id]                         [uniqueidentifier] CONSTRAINT [ID_NewId] DEFAULT newid() NOT NULL,
          [Order]                      [nvarchar](10) ,       -- Reihenfolge der abarbeitung
          [Active]                     [bit] NULL ,           -- ist dieser Task aktive
          [ReDo]                       [bit] DEFAULT 0 NULL , -- diesen Task wiederholen auch wenn schon einmal ausgeführt (neu 30.10.2008 J.Fes)
          [Name]                       [nvarchar](32) ,       -- Name ist eindeutig  ein OnDemant Job ist dann gegeben wenn Name OD-000001 ..
          [Description]                [nvarchar](max) ,      -- Beschreibung des Job's

          [TimeSchedule]               [nvarchar](max) ,      -- Schedule Time

          [cmd]                        [nvarchar](max) ,      -- Command to Execute Script Interne funktion
          [cmdPara]                    [nvarchar](max) ,      -- Command Parameter

          [TaskExcuteOnComputers]      [nvarchar] (max) ,     -- 15.12.2009  legt fest von welchem(n)/Computer(n) dieser Task alternativ(wenn es nicht der JobServer ist) ausgefuehrt werden darf

          [ProcessState]               [nvarchar](2) ,        -- aktueller status N=Non D=Done P=Process is running
          [ProcessStepControl]         [nvarchar](32) ,       -- aktueller Step p UGMaster P-UGNonMaster = Processing NonMaster
          [ProcessLastMsg]             [nvarchar](max) ,      -- letzte Meldung zum Process
          [ProcessLastRunStart]        [datetime] ,           -- letze start Zeit des Task Processes
          [ProcessLastRunEnd]          [datetime] ,           -- Zeit an der der Task Prozess beim letztenmal beendet wurde

          [ProcessType]                [nvarchar](10) ,       -- JPJob = JobPlaner-Task TaskJob= geplanter Task Job (neu 10.09.2008)
          [ProcessOwner]               [nvarchar](32) ,       -- Owner des Task (neu 10.09.2008 durch JobPlaner)
          [ProcessObjectsProcStateCol] [nvarchar](32) ,       -- Name des Columes der Objects_tbl deren Status umgesetzt werden soll (neu 10.09.2008 durch JobPlaner)
          [ProcessTaskParameterPacket] [xml] ,                -- speichert die erweiterten Eigenschaften eines Task's (z.B.Parameter Environ) im XML Format. Siehe hierzu JobServer.Contracts -> ...TaskParameterPacket  ...(neu 26.09.2008 durch JobPlaner)
          [Note]                       [nvarchar](max)        -- Notice zum TASK
  ) ON [PRIMARY]


Goto CheckCreateIndex
--------------------------------------------------------------------------------
TableUpdate:

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

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

  set @ColumeName='ProcessType'
  if (Select Coalesce(Col_length('TaskScheduler_tbl',@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [OD = OnDemant (neu 10.09.2008)]')
      set @ExecuteStr='ALTER TABLE [dbo].[TaskScheduler_tbl] ADD [' + @ColumeName +'] [nvarchar] (10)'
      execute (@ExecuteStr)
    end

  -- rename 21.10.2008 16:52 J.Fes
  set @ColumeNameOld='ProcessODOwner'
  set @ColumeNameNew='ProcessOwner'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:' + @ColumeNameOld + ' to:' + @ColumeNameNew)
     set @ExecuteStr='sp_rename ' + '''' + '[dbo].[' + @MyTableName + '].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
      print (@ExecuteStr)
      execute (@ExecuteStr)
    end

  -- wenn ProcessOwner nicht angelegt dann jetzt anlegen Achtung Reihenfolge!!
  set @ColumeName='ProcessOwner'
  if (Select Coalesce(Col_length('TaskScheduler_tbl',@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [Owner des Task (neu 10.09.2008)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (32)'
      execute (@ExecuteStr)
    end
    
  -- rename 21.10.2008 16:52 J.Fes
  set @ColumeNameOld='ProcessODObjectsProcStateCol'
  set @ColumeNameNew='ProcessObjectsProcStateCol'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:' + @ColumeNameOld + ' to:' + @ColumeNameNew)
     set @ExecuteStr='sp_rename ' + '''' + '[dbo].[' + @MyTableName + '].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
      print (@ExecuteStr)
      execute (@ExecuteStr)
    end
  -- wenn ProcessObjectsProcStateCol nicht angelegt dann jetzt anlegen Achtung Reihenfolge!!
  set @ColumeName='ProcessObjectsProcStateCol'
  if (Select Coalesce(Col_length('TaskScheduler_tbl',@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [Name des Columes der Objects_tbl deren Status umgesetzt werden soll (neu 10.09.2008)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (32)'
      execute (@ExecuteStr)
    end

  -- CodeExample: Anlegen eines Column
  set @ColumeName='Note'
  if (Select Coalesce(Col_length('TaskScheduler_tbl',@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName)
      set @ExecuteStr='ALTER TABLE [dbo].[TaskScheduler_tbl] ADD [' + @ColumeName +'] [nvarchar] (max)'
   execute (@ExecuteStr)
    end

  -- CodeExample: Editieren der Eigenschaften Datentype eines Column
  --   set @ColumeName='Note'
  --   if not (Select Coalesce(Col_length('TaskScheduler_tbl',@ColumeName),0)) = 0
  --     begin
  --       print ('Edit Column:' + @ColumeName)
  --       set @ExecuteStr='ALTER TABLE [dbo].[TaskScheduler_tbl] ALTER COLUMN [' + @ColumeName +'] [nvarchar](max)'
  --       execute (@ExecuteStr)
  --     end

  -- CodeExample: löschen eines Columes
  if not (Select Coalesce(Col_length('TaskScheduler_tbl','ProcStateCol'),0)) = 0
    begin
      print ('Delete Column: ProcStateCol')
      ALTER TABLE [dbo].[TaskScheduler_tbl] DROP COLUMN [ProcStateCol]
    end

  -- neu 31.01.2008 20:54
  set @ColumeName='cmd'
  if (Select Coalesce(Col_length('TaskScheduler_tbl',@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName)
      set @ExecuteStr='ALTER TABLE [dbo].[TaskScheduler_tbl] ADD [' + @ColumeName +'] [nvarchar] (max)'
      execute (@ExecuteStr)
    end

  -- neu 31.01.2008 20:54
  set @ColumeName='cmdPara'
  if (Select Coalesce(Col_length('TaskScheduler_tbl',@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName)
      set @ExecuteStr='ALTER TABLE [dbo].[TaskScheduler_tbl] ADD [' + @ColumeName +'] [nvarchar] (max)'
      execute (@ExecuteStr)
    end

  -- 01.02.2008 13:11:50 - umbennenen
  set @ColumeNameOld='Aktive'
  set @ColumeNameNew='Active'
  if not (Select Coalesce(Col_length('TaskScheduler_tbl',@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:' + @ColumeNameOld + ' to:' + @ColumeNameNew)
      -- EXEC sp_rename '[dbo].[TaskScheduler_tbl].[Active]','Aktive','COLUMN'
      set @ExecuteStr='sp_rename ' + '''' + '[dbo].[TaskScheduler_tbl].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
      print (@ExecuteStr)
      execute (@ExecuteStr)
    end

  -- löschen 17.04.2008 18:35
  set @ColumeName='cmdType'
  if not (Select Coalesce(Col_length('TaskScheduler_tbl',@ColumeName),0)) = 0
    begin
      print ('Delete Column:' + @ColumeName)
      set @ExecuteStr = 'ALTER TABLE [dbo].[TaskScheduler_tbl] DROP COLUMN [' + @ColumeName + ']'
      print (@ExecuteStr)
      execute (@ExecuteStr)
    end

  set @ColumeName='ProcessTaskParameterPacket'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName)
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [xml]'
      execute (@ExecuteStr)
    end

  -- CodeExample: löschen einer Colume
  set @ColumeName='ProcessODNumber'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Delete Column:' + @ColumeName)
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] DROP COLUMN [' + @ColumeName +']'
      execute (@ExecuteStr)
    end

  -- entnommen da dieser Status in dem ProcessGruppen def steht J.FES 12.10.2008
  set @ColumeName='ProcessODObjectsSetToStatus'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Delete Column:' + @ColumeName)
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] DROP COLUMN [' + @ColumeName +']'
      execute (@ExecuteStr)
    end

  -- diesen Task wiederholen auch wenn schon einmal ausgeführt (neu 30.10.2008 J.Fes)
  set @ColumeName='ReDo'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName)
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] bit DEFAULT 0 NULL'
      execute (@ExecuteStr)
    end

  -- wenn ProcessObjectsProcStateCol nicht angelegt dann jetzt anlegen Achtung Reihenfolge!!
  set @ColumeName='ProcessStepControl'
  if (Select Coalesce(Col_length('TaskScheduler_tbl',@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [field to control process steps example UGMaster befor NonUGMaster (new 14.04.2009)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (32)'
      execute (@ExecuteStr)
    end

  -- neu 15.12.2009 21:59
  set @ColumeName='TaskExcuteOnComputers'
  if (Select Coalesce(Col_length('TaskScheduler_tbl',@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [new 15.12.2009  legt fest von welchem(n)/Computer(n) dieser Task alternativ(wenn es nicht der JobServer ist) ausgefuehrt werden darf]')
      set @ExecuteStr='ALTER TABLE [dbo].[TaskScheduler_tbl] ADD [' + @ColumeName +'] [nvarchar] (max)'
      execute (@ExecuteStr)
    end


  goto CheckCreateIndex
--------------------------------------------------------------------------------

CheckCreateIndex:

  If NOT Exists (select Name from sysindexes where Name = 'TaskScheduler_tbl_pk')
  begin
     print (' --> create TaskScheduler_tbl_pk')
     ALTER TABLE [dbo].[TaskScheduler_tbl]
       ADD CONSTRAINT [TaskScheduler_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 = 'TaskScheduler_tbl_Name_uq')
  begin
     print (' --> create TaskScheduler_tbl_Name_uq')
     ALTER TABLE [dbo].[TaskScheduler_tbl]
        ADD CONSTRAINT [TaskScheduler_tbl_Name_uq]
        UNIQUE NONCLUSTERED ([Name])
        WITH (
          FILLFACTOR = 80,
          PAD_INDEX = OFF,
          IGNORE_DUP_KEY = OFF,
          STATISTICS_NORECOMPUTE = OFF,
          ALLOW_ROW_LOCKS = ON,
          ALLOW_PAGE_LOCKS = ON)
  end

  If NOT Exists (select Name from sysindexes where Name = 'TaskScheduler_tbl_IDX1')
  begin
   print (' --> create index: TaskScheduler_tbl_IDX1 -> Order')
   CREATE NONCLUSTERED INDEX [TaskScheduler_tbl_IDX1] ON [dbo].[TaskScheduler_tbl]
      ([Order])
    WITH (
      PAD_INDEX = OFF,
      DROP_EXISTING = OFF,
      STATISTICS_NORECOMPUTE = OFF,
      SORT_IN_TEMPDB = OFF,
      ONLINE = OFF,
      ALLOW_ROW_LOCKS = ON,
      ALLOW_PAGE_LOCKS = ON)
     ON [PRIMARY]
  end

  If NOT Exists (select Name from sysindexes where Name = 'TaskScheduler_tbl_IDX2')
  begin
   print (' --> create index: TaskScheduler_tbl_IDX2 -> ProcessOwner')
   CREATE NONCLUSTERED INDEX [TaskScheduler_tbl_IDX2] ON [dbo].[TaskScheduler_tbl]
     ([ProcessOwner])
   WITH (
     PAD_INDEX = OFF,
     DROP_EXISTING = OFF,
     STATISTICS_NORECOMPUTE = OFF,
     SORT_IN_TEMPDB = OFF,
     ONLINE = OFF,
     ALLOW_ROW_LOCKS = ON,
     ALLOW_PAGE_LOCKS = ON)
   ON [PRIMARY]
  end


  Goto EndeExit

--------------------------------------------------------------------------------
EndeExit:
 Print '------------------------------------------------------------------------'
 Print (@MyActionMsg1)
 Print ('   |-> Done on Database: #DB_N#')
 Print '------------------------------------------------------------------------'

GO
