Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Table         : [Objects_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='Objects_tbl'
DECLARE @ExecuteSQLstr nvarchar(max)

declare @indexNrCur  int
declare @indexNrEnd  int
declare @indexNrStr       varchar(32)
declare @IndexName        varchar(max)
declare @ProcStateColName varchar(max)
set @indexNrEnd = 10


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

--------------------------------------------------------------------------------
TableCreate:
  set @MyActionMsg1=
      '-- Table: [Objects_tbl] dose not exist and will be created new'

  Print (@MyActionMsg1)

  -- Wichtig: bei struktur anpassungen immer Procedure: Object_Import mit anpassen !!
  CREATE TABLE [dbo].[Objects_tbl] (
         [Id]                            uniqueidentifier NOT NULL ,  -- = Interne Datensatz ID
         [ObjectPuid]                    nvarchar(15) COLLATE Latin1_General_CS_AS NULL , -- = PUID des Objektes neu 2.1.2009 J.Fes Info durch angabe von Latin1_General_CS_AS wird erreicht das das der Feldinhalt case sensitiv betrachtet wird
         [ObjectName]                    nvarchar(64)  , -- = Name des Objects = ID Refact:(08.02.2012) diesen Spalten Namen auf ObjectId umbenennen.
         [ObjectName2]                   nvarchar(32)  , -- = Name 2 = Part - Name neu 1.12.08 J.Fes    diese Spalten Namen auf ObjectName umstellen.
         [ObjectDescription]             nvarchar(255) , -- = Description des Object's neu 1.12.08 J.Fes

         [ObjectFromSiteID]              nvarchar(32)  , -- = die Site auf dem die DataSet gezogen wurde
         [ObjectClass]                   nvarchar(10)  , -- = I-(Item) DS-(Dataset) IR-(ItemRev)
         [ObjectType]                    nvarchar(32)  , -- = UGMASTER UGPART UGALTREP
         [ObjectCliName]                 nvarchar(255) , -- = ObjectCLIName ("C"omannd"L"ine"I"nterface - Name) (umbenannt von ObjectNamedId am 24.04.08 J.Fes)
         [ObjectOwningSiteID]            nvarchar(32)  , -- = Site dem das Object gehört
         [ObjectOwner]                   nvarchar(32)  , -- = Owner des Datasets
         [ObjectGroup]                   nvarchar(32)  , -- = Object gehoert der Gruppe
         [ObjectCreateDate]              datetime      , -- = erzeugungs Datum
         [ObjectLastModUser]             nvarchar(32)  , -- = Last Modi User
         [ObjectLastModDate]             datetime      , -- = Last Modi Date

         [ObjectUgMaster_Type]           nvarchar(32)  , -- = SP = Single Part AP = Assemplypart
         [ObjectUgMaster_CompCount]      int           , -- = anzahl der verbauten Komponenten wenn es eine Baugruppe ist
         [ObjectUgMaster_APLevels]       int           , -- = anzahl der verbauung Ebenen wenn es eine Baugruppe ist
         [ObjectUgMaster_FsHitCount]     int           , -- = anzahl der Hitcounts des Datasets - Parts
--          [ObjectUgMaster_FsHitCount_YN]
--             AS (case
--                 when [ObjectUgMaster_FsHitCount]>(0)
--                                then (1) else (0) end)  , -- = berechnetes Feld um die JobPakete nach den FSHitCount sortiert abarbeiten zu können 03.11.2011
         [ObjectFs_DPNE]                 nvarchar(255) , -- = Full Path to File Object
         [ObjectFs_Vol]                  nvarchar(64)  , -- = Volume
         [ObjectFs_NE]                   nvarchar(64)  , -- = FileName
         [ObjectUgMaster_PartFamMemName] nvarchar(64)  , -- = Name des PFM Templates

         [ObjectReleaseState]            nvarchar(32)  , -- = Release State  (neu 1.12.08)
         [ObjectReleaseDate]             datetime      , -- = Release Datum (neu 1.12.08)

         [ObjectSelectByMarker]          nvarchar(32)  , -- = fuer selection via Marker
         [ObjectNote]                    nvarchar(255) , -- = Notice Feld
         
         [ImportObjectErr]               nvarchar(64)  , -- = info über den Importvorgang des Objektes (neu 31.12.2008)

         [ProcStateCol01]                nvarchar(34)  , -- = Processstatus zu Proc1 Felgroesse angep. von 10 auf 32+2 = 34
         [ProcStateCol02]                nvarchar(34)  , -- = Processstatus zu Proc2
         [ProcStateCol03]                nvarchar(34)  , -- = ..
         [ProcStateCol04]                nvarchar(34)  , -- = ..
         [ProcStateCol05]                nvarchar(34)  , -- = ..
         [ProcStateCol06]                nvarchar(34)  , -- = ..
         [ProcStateCol07]                nvarchar(34)  , -- = ..
         [ProcStateCol08]                nvarchar(34)  , -- = ..
         [ProcStateCol09]                nvarchar(34)  , -- = ..
         [ProcStateCol10]                nvarchar(34)  , -- = Processstatus zu Proc10

         [ProcStateColRf]                nvarchar(34)   -- = Processstatus zu Refile

--          entnommen 20.12.2011 05:07 J.Fes
--          [ObjectAttr01]                  nvarchar(64)  , -- = ObjectAttr 01
--          [ObjectAttr02]                  nvarchar(64)  , -- = ObjectAttr 02
--          [ObjectAttr03]                  nvarchar(64)  , -- = ObjectAttr 03
--          [ObjectAttr04]                  nvarchar(64)  , -- = ObjectAttr 04
--          [ObjectAttr05]                  nvarchar(64)  , -- = ObjectAttr 05
--          [ObjectAttr06]                  nvarchar(64)  , -- = ObjectAttr 06
--          [ObjectAttr07]                  nvarchar(64)  , -- = ObjectAttr 07
--          [ObjectAttr08]                  nvarchar(64)  , -- = ObjectAttr 08
--          [ObjectAttr09]                  nvarchar(64)  , -- = ObjectAttr 09
--          [ObjectAttr10]                  nvarchar(64)   -- = ObjectAttr 10

         -- CONSTRAINT [Objects_tbl_ID_uq] UNIQUE ([ObjectCliName], [ObjectFromSiteID], [ObjectClass]),
         -- CONSTRAINT [Objects_tbl_pk] PRIMARY KEY NONCLUSTERED ([Id])
  )
  ON [PRIMARY]

  goto CheckCreateIndex

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

  set @MyActionMsg1=
       '-- Table: [' + @MyTableName + '] 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='ObjectPuid'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName +  ' .. PUID of Object (new 2.1.2009 J.Fes)')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(15) COLLATE Latin1_General_CS_AS NULL'
      execute (@ExecuteStr)
    end


  set @ColumeName='ImportObjectErr'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName +  ' .. stores info about importObject Results (new 31.12.2008)')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(64)'
      execute (@ExecuteStr)
    end


  set @ColumeName='ObjectName2'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName +  ' .. for Part-Name (new 16.12.08 J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(32)'
      execute (@ExecuteStr)
    end

  -- Ref auf die JobControl ProcessData solange = null ist das Objekt noch nicht in Process (new 26.09.08)
  set @ColumeName='ObjectDescription'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName +  ' .. Object Description (new 01.12.08 J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(255)'
      execute (@ExecuteStr)
    end

  set @ColumeName='ObjectReleaseState'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName +  ' .. Object ReleaseState (new 01.12.08 J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(64)'
      execute (@ExecuteStr)
    end

  set @ColumeName='ObjectReleaseDate'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName +  ' .. Object ObjectReleaseDate (new 01.12.08 J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] datetime'
      execute (@ExecuteStr)
    end

  -- ergaenzt 16.05.2008 08:38
  set @ColumeName='ProcStateColRf'
  if (Select Coalesce(Col_length('Objects_tbl',@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + '  [refile Processstatus (new 16.05.08)]')
      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +'] [nvarchar] (11) NULL'
      execute (@ExecuteStr)
    end

  -- rename 24.04.2008 12:00 J.Fes von ObjectNamedId --> ObjectCliName
  set @ColumeNameOld='ObjectNamedId'
  set @ColumeNameNew='ObjectCliName'
  if not (Select Coalesce(Col_length('Objects_tbl',@ColumeNameOld),0)) = 0
    begin
      print ('rename Column:' + @ColumeNameOld + ' to:' + @ColumeNameNew)
     set @ExecuteStr='sp_rename ' + '''' + '[dbo].[Objects_tbl].[' + @ColumeNameOld  + ']' + '''' + ',' + '''' + @ColumeNameNew + '''' + ',' + '''' + 'COLUMN' + ''''
      print (@ExecuteStr)
      execute (@ExecuteStr)
    end

  -- Colume ObjectCliName lengs von 128 -> 255 vergroesseern (24.04.2008 J.Fes)
  if (Select Col_length('Objects_tbl','ObjectCliName')) <> 255*2
  begin
     print ('Column:ObjectCliName upsize to 255 char')
     ALTER TABLE [dbo].[Objects_tbl] DROP CONSTRAINT [Objects_tbl_ID_uq]
     DROP INDEX [Objects_tbl_IDX2] ON [dbo].[Objects_tbl]
     ALTER TABLE [dbo].[Objects_tbl]
     ALTER COLUMN [ObjectCliName] nvarchar(255) COLLATE Latin1_General_CI_AS
  end

-- OFFEN 03.11.2011
--   set @ColumeName='ObjectUgMaster_FsHitCount_YN'
--   if (Select Coalesce(Col_length('Objects_tbl',@ColumeName),0)) = 0
--   begin
--      print ('Create Column:' + @ColumeName + ' [computed ObjectUgMaster_FsHitCount_YN (new 03.11.2011)]')
--      set @ExecuteStr='ALTER TABLE [dbo].[Objects_tbl] ADD [' + @ColumeName +']  AS (case when [ObjectUgMaster_FsHitCount]>(0) then (1) else (0) end)'
--      execute (@ExecuteStr)
--      print ('.')
--   end


  -- 23.11.2011/J.Fes Anpassung der ProcStateColName auf Size = 32+2=34 Zeichen siehe JobControlRules_tbl.JobName
  set @indexNrCur = 1
  WHILE @indexNrCur <= @indexNrEnd +1
  BEGIN
    set @indexNrStr=right('00' + convert(nvarchar,@indexNrCur),2)
    set @ProcStateColName='ProcStateCol' + @indexNrStr
    set @IndexName = 'Objects_tbl_IDX_PsCol' + @indexNrStr
    if @indexNrCur = @indexNrEnd +1
    begin
      -- Sonderregel: Process [ProcStateColRf]
      set @ProcStateColName='ProcStateColRf'
      set @IndexName = 'Objects_tbl_IDX_PsColRf'
    end
    if (Select Col_length('Objects_tbl',@ProcStateColName)) <> 34*2
     BEGIN
      If Exists (select Name from sysindexes where Name = @IndexName)
      BEGIN
        -- Step entfernen Index
        set @ExecuteSQLstr='DROP INDEX [' + @IndexName + ']  ON [dbo].[' + @MyTableName + ']'
        print (@ExecuteSQLstr)
        execute sp_executesql @ExecuteSQLstr
      END
      -- Anpassen auf Sice = 33
      set @ExecuteSQLstr=  'ALTER TABLE [dbo].[' + @MyTableName + ']' + ' ALTER COLUMN [' + @ProcStateColName + '] nvarchar(34) COLLATE Latin1_General_CI_AS'
      print (@ExecuteSQLstr)
      execute sp_executesql @ExecuteSQLstr
     END
     set @indexNrCur = @indexNrCur +1
  END


  -- 20.12.2011/J.Fes entferene ObjectAttr01..10 --
  set @indexNrCur = 1
  WHILE @indexNrCur <= 10
  BEGIN
   set @indexNrStr=right('00' + convert(nvarchar,@indexNrCur),2)
   set @ColumeName='ObjectAttr' + @indexNrStr
   if not (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
     BEGIN
       print ('Delete Column:' + @ColumeName +  ' .. cleanup Data (change 20.12.2011)')
       set @ExecuteStr = 'ALTER TABLE [dbo].[' + @MyTableName + '] DROP COLUMN [' + @ColumeName + ']'
       execute (@ExecuteStr)
     END
   set @indexNrCur = @indexNrCur +1
  END

--   entnommen 10.01.2012 11:49
--   set @ColumeName='ObjectIrType'
--   if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
--     begin
--       print ('Create Column:' + @ColumeName + ' [contains ItemRevision Type ItemRevision Cad-ItemRevision new 20.12.2011]')
--       set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (32) NULL'
--       execute (@ExecuteStr)
--     end

  set @ColumeName='ObjectIrType'
  if not (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      set @IndexName = 'Objects_tbl_IDX43'
      set @ExecuteSQLstr='DROP INDEX [' + @IndexName + ']  ON [dbo].[' + @MyTableName + ']'
      print (@ExecuteSQLstr)
      execute sp_executesql @ExecuteSQLstr

      print (' --> Delete Column:' + @MyTableName + ':' + @ColumeName + '  [ItemRevision Type from Dataset (new 10.01.2012 J.Fes)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] DROP Column [' + @ColumeName +']'
      execute (@ExecuteStr)
    end



goto CheckCreateIndex

--------------------------------------------------------------------------------
CheckCreateIndex:

  EXECUTE JF_PrimKeyCheckCreateDef 'Objects_tbl_pk', @MyTableName, 'Id'
  EXECUTE JF_IndexCheckCreateDef   'Objects_tbl_IDX1', @MyTableName, 'ObjectName'

  --- ReAdd 26.07.2010 18:41 J.Fes Wichtig !!!
  EXECUTE JF_IndexCheckCreateDef   'Objects_tbl_IDX2', @MyTableName, '[ObjectCliName], [ObjectFromSiteID], [ObjectClass]'
  EXECUTE JF_IndexCheckCreateDef   'Objects_tbl_IDX3', @MyTableName, 'ObjectFromSiteID'
  EXECUTE JF_IndexCheckCreateDef   'Objects_tbl_IDX4', @MyTableName, 'ObjectClass'

  set @indexNrCur = 1
  WHILE @indexNrCur <= @indexNrEnd + 1
  BEGIN

    set @indexNrStr=right('00' + convert(nvarchar,@indexNrCur),2)
    set @ProcStateColName='ProcStateCol' + @indexNrStr
    set @IndexName = 'Objects_tbl_IDX_PsCol' + @indexNrStr
    if @indexNrCur = @indexNrEnd +1
    begin
      -- Sonderregel: Process [ProcStateColRf] angepasst 23.11.2011 J.Fes
      set @ProcStateColName='ProcStateColRf'
      set @IndexName = 'Objects_tbl_IDX_PsColRf'
    end
    -- Create Index: (neue Methode 28.01.2011]
    EXECUTE JF_IndexCheckCreateDef @IndexName, @MyTableName, @ProcStateColName
    set @indexNrCur = @indexNrCur +1
  END

  -- Create Index: (neue Methode 28.01.2011]
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX17', @MyTableName, 'ObjectOwner'
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX18', @MyTableName, 'ObjectGroup'
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX19', @MyTableName, 'ObjectLastModUser'
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX20', @MyTableName, 'ObjectPuid'
  -- rem New 13.07.2010 19:10 siehe auch Objects_tbl_Object_GetDS
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX20', @MyTableName, '[ObjectFromSiteID] ASC, [ObjectPuid] ASC'
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX22', @MyTableName, 'ObjectDescription'
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX23', @MyTableName, 'ObjectName2'
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX25', @MyTableName, 'ObjectFs_Vol'
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX26_ObjectSelectByMarker', @MyTableName, 'ObjectSelectByMarker'

  -- rem New 26.08.2011 17:41 geting faster HitCounts
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX30', @MyTableName, 'ObjectFs_NE'
  -- rem New 26.08.2011 17:42 geting ObjectReleaseState
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX31', @MyTableName, 'ObjectReleaseState'

  -- rem New 18.10.2011 21:03 Optimice Get JobPaket Rf AP
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX40', @MyTableName, '[ObjectUgMaster_APLevels] ASC, [ObjectUgMaster_CompCount] ASC, [ObjectCreateDate] DESC'
  -- New 21.10.2011 21:13  Optimice Get JobPaket SP
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX41', @MyTableName, '[ObjectCreateDate] DESC'

  -- New 13.12.2011 21:35  Optimice Get JobPaket SP
  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX42', @MyTableName, '[ObjectUgMaster_FsHitCount]'
--   EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX43', @MyTableName, '[ObjectIrType]'



  -- rem New 18.10.2011 21:03 Optimice Get JobPaket
--  EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX41', @MyTableName, '[ObjectType] ASC, [ObjectFromSiteID] ASC,	[ObjectOwningSiteID] ASC,	[ObjectClass] ASC,	[ObjectUgMaster_Type] ASC,	[ProcStateColRf] ASC,	[ObjectCreateDate] DESC'

/*
  CREATE NONCLUSTERED INDEX [_dta_index_Objects_tbl_11_357576312__K8_K6_K10_K7_K17_K39_K13D] ON [dbo].[Objects_tbl]
(
	[ObjectType] ASC, [ObjectFromSiteID] ASC,	[ObjectOwningSiteID] ASC,	[ObjectClass] ASC,	[ObjectUgMaster_Type] ASC,	[ProcStateColRf] ASC,	[ObjectCreateDate] DESC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

*/


/* 18.10.2011 19:08
  CREATE CLUSTERED INDEX [Objects_tbl_IDX41] ON [dbo].[Objects_tbl]
(
	[ObjectUgMaster_APLevels] ASC, [ObjectUgMaster_CompCount] ASC, [ObjectCreateDate] DESC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

*/


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

GO
