---------------------------------------------------------------------
-- BASE_VERSION 8

-- You could execute this script from MSSQL Query Analyzer
-- or use OSQL utility 
-- Don't forget that user should have appropriate rights
--
--#########################################################################################################

if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 1

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

DECLARE @componentName varchar(20)
SET @componentName = 'IntegrationService'

IF EXISTS( SELECT veri FROM components WHERE name=@componentName and veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

/*Источники данных. Т.е. объекты, с которыми ведется программный обмен.*/
CREATE TABLE [dbo].[DataSources](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[UID] [nvarchar](256) NOT NULL,
	[Title] [nvarchar](256) NOT NULL,
	[AdapterName] [nvarchar](256) NOT NULL,
	[ConnectionString] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_DataSources] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

/*множество интегрируемых станций*/
CREATE TABLE [dbo].[Stations](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[DataSourceId] [int] NOT NULL,
	[UID] [nvarchar](256) NOT NULL,
	[Title] [nvarchar](256) NOT NULL,
 CONSTRAINT [PK_Stations] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

/*тображение полей базы в поле МБД*/
CREATE TABLE [dbo].[FieldMapping](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[StationId] [int] NOT NULL,
	[ExternalFieldId] [nvarchar](256) NOT NULL,
	[DigispotFieldId] [nvarchar](256) NOT NULL,
	[SyncToMdb] [bit] NOT NULL,
	[SyncToExternal] [bit] NOT NULL,
 CONSTRAINT [PK_FieldMapping] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

/*множество всех синхронизируемых БД*/
CREATE TABLE [dbo].[SyncDataBases](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[StationId] [int] NOT NULL,
	[IsAutoSyncEnabled] [bit] NOT NULL,
	[AutoSyncTimeout] [int] NOT NULL,
	[LastSyncedStamp] [bigint] NULL,
 CONSTRAINT [PK_SyncDataBases] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

/*Данные о синхронизированных элементах*/
CREATE TABLE [dbo].[SyncElemItemsData](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[SyncedDbId] [int] NOT NULL,
	[ExternalElemId] [int] NOT NULL,
	[DigispotElemId] [int] NOT NULL,
	[ExternalElemTimestamp] [bigint] NOT NULL,
	[DigispotElemTimestamp] [bigint] NOT NULL,
	[ExternalElemMd5] [int] NOT NULL,
	[DigispotElemMd5] [int] NOT NULL,
 CONSTRAINT [PK_SyncElemItemsData] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

/*Соответствие синхронизируемых категорий*/
CREATE TABLE [dbo].[SyncToAttrVals](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[SyncedDbId] [int] NOT NULL,
	[ExternalPartition] [int] NULL,	/*Внешний тип категории (музыка/джинглы и т.п.)*/
	[attrval_id] [int] NOT NULL,
 CONSTRAINT [PK_SyncToAttrVals] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


/*все сверяемые расписания*/
CREATE TABLE [dbo].[ReconcileSchedules](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[StationId] [int] NOT NULL,
	[ScheduleId] [int] NOT NULL,
	[IsAutoReconcileEnabled] [bit] NOT NULL,
 CONSTRAINT [PK_ReconcileSchedules] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


------------------
INSERT INTO components (name, veri) VALUES(@componentName, @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO


--#########################################################################################################

if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 2

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

DECLARE @componentName varchar(20)
SET @componentName = 'IntegrationService'

IF EXISTS( SELECT veri FROM components WHERE name=@componentName and veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE [dbo].[FieldMapping]  WITH CHECK ADD  CONSTRAINT [FK_FieldMapping_Stations] FOREIGN KEY([StationId])
REFERENCES [dbo].[Stations] ([Id])
ON DELETE CASCADE

ALTER TABLE [dbo].[FieldMapping] CHECK CONSTRAINT [FK_FieldMapping_Stations]

ALTER TABLE [dbo].[ReconcileSchedules]  WITH CHECK ADD  CONSTRAINT [FK_ReconcileSchedules_SCHEDULES] FOREIGN KEY([ScheduleId])
REFERENCES [dbo].[SCHEDULES] ([id])

ALTER TABLE [dbo].[ReconcileSchedules] CHECK CONSTRAINT [FK_ReconcileSchedules_SCHEDULES]

ALTER TABLE [dbo].[ReconcileSchedules]  WITH CHECK ADD  CONSTRAINT [FK_ReconcileSchedules_Stations] FOREIGN KEY([StationId])
REFERENCES [dbo].[Stations] ([Id])
ON DELETE CASCADE

ALTER TABLE [dbo].[ReconcileSchedules] CHECK CONSTRAINT [FK_ReconcileSchedules_Stations]

ALTER TABLE [dbo].[Stations]  WITH CHECK ADD  CONSTRAINT [FK_Stations_DataSources] FOREIGN KEY([DataSourceId])
REFERENCES [dbo].[DataSources] ([Id])
ON DELETE CASCADE

ALTER TABLE [dbo].[Stations] CHECK CONSTRAINT [FK_Stations_DataSources]

ALTER TABLE [dbo].[SyncDataBases]  WITH CHECK ADD  CONSTRAINT [FK_SyncDataBases_Stations] FOREIGN KEY([StationId])
REFERENCES [dbo].[Stations] ([Id])
ON DELETE CASCADE

ALTER TABLE [dbo].[SyncDataBases] CHECK CONSTRAINT [FK_SyncDataBases_Stations]

ALTER TABLE [dbo].[SyncElemItemsData]  WITH CHECK ADD  CONSTRAINT [FK_SyncElemItemsData_PH] FOREIGN KEY([DigispotElemId])
REFERENCES [dbo].[PH] ([id])

ALTER TABLE [dbo].[SyncElemItemsData] CHECK CONSTRAINT [FK_SyncElemItemsData_PH]

ALTER TABLE [dbo].[SyncElemItemsData]  WITH CHECK ADD  CONSTRAINT [FK_SyncElemItemsData_SyncDataBases] FOREIGN KEY([SyncedDbId])
REFERENCES [dbo].[SyncDataBases] ([Id])
ON DELETE CASCADE

ALTER TABLE [dbo].[SyncElemItemsData] CHECK CONSTRAINT [FK_SyncElemItemsData_SyncDataBases]

ALTER TABLE [dbo].[SyncToAttrVals]  WITH CHECK ADD  CONSTRAINT [FK_SyncToAttrVals_ATTRIB_VALS] FOREIGN KEY([attrval_id])
REFERENCES [dbo].[ATTRIB_VALS] ([id])

ALTER TABLE [dbo].[SyncToAttrVals] CHECK CONSTRAINT [FK_SyncToAttrVals_ATTRIB_VALS]

ALTER TABLE [dbo].[SyncToAttrVals]  WITH CHECK ADD  CONSTRAINT [FK_SyncToAttrVals_SyncDataBases] FOREIGN KEY([SyncedDbId])
REFERENCES [dbo].[SyncDataBases] ([Id])
ON DELETE CASCADE
ALTER TABLE [dbo].[SyncToAttrVals] CHECK CONSTRAINT [FK_SyncToAttrVals_SyncDataBases]

------------------
INSERT INTO components (name, veri) VALUES(@componentName, @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################

if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 3

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

DECLARE @componentName varchar(20)
SET @componentName = 'IntegrationService'

IF EXISTS( SELECT veri FROM components WHERE name=@componentName and veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE DataSources ADD LogId int NULL
ALTER TABLE ReconcileSchedules ADD LogId int NULL
ALTER TABLE SyncDataBases ADD LogId int NULL

------------------
INSERT INTO components (name, veri) VALUES(@componentName, @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################

if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 4

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

DECLARE @componentName varchar(20)
SET @componentName = 'IntegrationService'

IF EXISTS( SELECT veri FROM components WHERE name=@componentName and veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE [dbo].[DataSources]  WITH CHECK ADD  CONSTRAINT [FK_DataSources_OperationsLog] FOREIGN KEY([LogId])
REFERENCES [dbo].[OperationsLog] ([Id])

ALTER TABLE [dbo].[DataSources] CHECK CONSTRAINT [FK_DataSources_OperationsLog]

ALTER TABLE [dbo].[ReconcileSchedules]  WITH CHECK ADD  CONSTRAINT [FK_ReconcileSchedules_OperationsLog] FOREIGN KEY([LogId])
REFERENCES [dbo].[OperationsLog] ([Id])

ALTER TABLE [dbo].[ReconcileSchedules] CHECK CONSTRAINT [FK_ReconcileSchedules_OperationsLog]

ALTER TABLE [dbo].[SyncDataBases]  WITH CHECK ADD  CONSTRAINT [FK_SyncDataBases_OperationsLog] FOREIGN KEY([LogId])
REFERENCES [dbo].[OperationsLog] ([Id])

ALTER TABLE [dbo].[SyncDataBases] CHECK CONSTRAINT [FK_SyncDataBases_OperationsLog]

------------------
INSERT INTO components (name, veri) VALUES(@componentName, @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################

if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 5

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

DECLARE @componentName varchar(20)
SET @componentName = 'IntegrationService'

IF EXISTS( SELECT veri FROM components WHERE name=@componentName and veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE SyncDataBases ADD LastExternalSyncedStamp bigint NULL

------------------
INSERT INTO components (name, veri) VALUES(@componentName, @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################

if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 6

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

DECLARE @componentName varchar(20)
SET @componentName = 'IntegrationService'

IF EXISTS( SELECT veri FROM components WHERE name=@componentName and veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE ReconcileSchedules ADD 
BlockId nvarchar(max) NULL,
InBlockId int NULL,
BlockTime datetime NULL

------------------
INSERT INTO components (name, veri) VALUES(@componentName, @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################

if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 7

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

DECLARE @componentName varchar(20)
SET @componentName = 'IntegrationService'

IF EXISTS( SELECT veri FROM components WHERE name=@componentName and veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

EXEC sp_rename 'DataSources', 'intgr_DataSources' 
EXEC sp_rename 'FieldMapping', 'intgr_FieldMapping'
EXEC sp_rename 'ReconcileSchedules', 'intgr_ReconcileSchedules'
EXEC sp_rename 'Stations', 'intgr_Stations'
EXEC sp_rename 'SyncDataBases', 'intgr_SyncDataBases'
EXEC sp_rename 'SyncElemItemsData', 'intgr_SyncElemItemsData'
EXEC sp_rename 'SyncToAttrVals', 'intgr_SyncToAttrVals'


------------------
INSERT INTO components (name, veri) VALUES(@componentName, @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################

if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 8

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

DECLARE @componentName varchar(20)
SET @componentName = 'IntegrationService'

IF EXISTS( SELECT veri FROM components WHERE name=@componentName and veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE [dbo].[intgr_SyncElemItemsData] DROP CONSTRAINT [FK_SyncElemItemsData_PH]

ALTER TABLE [dbo].[intgr_SyncElemItemsData]  WITH CHECK ADD  CONSTRAINT [FK_SyncElemItemsData_PH] FOREIGN KEY([DigispotElemId])
REFERENCES [dbo].[PH] ([id])
ON DELETE CASCADE

ALTER TABLE [dbo].[intgr_SyncElemItemsData] CHECK CONSTRAINT [FK_SyncElemItemsData_PH]

------------------
INSERT INTO components (name, veri) VALUES(@componentName, @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################

if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 9

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

DECLARE @componentName varchar(20)
SET @componentName = 'IntegrationService'

IF EXISTS( SELECT veri FROM components WHERE name=@componentName and veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE [dbo].[intgr_SyncToAttrVals]  ADD
ExternalCatID nvarchar(max) NULL

------------------
INSERT INTO components (name, veri) VALUES(@componentName, @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################

if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 10

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

DECLARE @componentName varchar(20)
SET @componentName = 'IntegrationService'

IF EXISTS( SELECT veri FROM components WHERE name=@componentName and veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

CREATE UNIQUE NONCLUSTERED INDEX [MID_EID_UNIQUE] ON [dbo].[intgr_SyncElemItemsData]
(
	[SyncedDbId] ASC,
	[ExternalElemId] ASC,
	[DigispotElemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

------------------
INSERT INTO components (name, veri) VALUES(@componentName, @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################

if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 11

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

DECLARE @componentName varchar(20)
SET @componentName = 'IntegrationService'

IF EXISTS( SELECT veri FROM components WHERE name=@componentName and veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

IF OBJECT_ID(N'OnDeleteData', N'TR') IS NOT NULL
    DROP TRIGGER OnDeleteData;

exec sp_executesql N'
CREATE TRIGGER [dbo].[OnDeleteData]
   ON  [dbo].[intgr_SyncElemItemsData]
   FOR DELETE
AS 
		SET NOCOUNT ON;
   
		UPDATE       PH
		SET          ID_Number = N''''
		WHERE		 id = (Select DigispotElemId FROM Deleted)
'

IF OBJECT_ID(N'OnDeleteAttrVal', N'TR') IS NOT NULL
    DROP TRIGGER OnDeleteAttrVal;

exec sp_executesql N'
CREATE TRIGGER [dbo].[OnDeleteAttrVal]
   ON  [dbo].[intgr_SyncToAttrVals]
   FOR DELETE
AS 
		SET NOCOUNT ON;
		UPDATE       PH
		SET          ID_Number = N''''
		WHERE        (id IN (SELECT PH_ID FROM dbo.ufnGetPhIdentities((Select attrval_id FROM Deleted)))) 
'

------------------
INSERT INTO components (name, veri) VALUES(@componentName, @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################

if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 12

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

DECLARE @componentName varchar(20)
SET @componentName = 'IntegrationService'

IF EXISTS( SELECT veri FROM components WHERE name=@componentName and veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

IF OBJECT_ID(N'OnDeleteData', N'TR') IS NOT NULL
    DROP TRIGGER OnDeleteData;

exec sp_executesql N'
CREATE TRIGGER [dbo].[OnDeleteData]
   ON  [dbo].[intgr_SyncElemItemsData]
   FOR DELETE
AS 
		SET NOCOUNT ON;
	BEGIN TRY
		UPDATE       PH
		SET          ID_Number = N''''
		WHERE		 id in (Select DigispotElemId FROM Deleted)
	END TRY  
	BEGIN CATCH  
	END CATCH 

'

IF OBJECT_ID(N'OnDeleteAttrVal', N'TR') IS NOT NULL
    DROP TRIGGER OnDeleteAttrVal;

exec sp_executesql N'
CREATE TRIGGER [dbo].[OnDeleteAttrVal]
   ON  [dbo].[intgr_SyncToAttrVals]
   FOR DELETE
AS 
	SET NOCOUNT ON;
	BEGIN TRY

		DECLARE @tmpInformation TABLE (	ID_TO_UPDATE int )
		INSERT INTO @tmpInformation SELECT attrval_id FROM Deleted

		DECLARE @MAXID INT
		SELECT @MAXID = COUNT(*) FROM @tmpInformation

		WHILE (@MAXID > 0)
		BEGIN
			--DO THE PROCESSING HERE 
			DELETE FROM  intgr_SyncElemItemsData
			WHERE (DigispotElemId IN (SELECT PH_ID FROM 
						dbo.ufnGetPhIdentities((SELECT TOP (1) ID_TO_UPDATE FROM @tmpInformation))))

			DELETE TOP (1) FROM @tmpInformation

			SET @MAXID = @MAXID - 1
		END

	END TRY  
	BEGIN CATCH  
	END CATCH
'


IF OBJECT_ID(N'OnUpdateAttrVal', N'TR') IS NOT NULL
    DROP TRIGGER OnUpdateAttrVal;

exec sp_executesql N'
CREATE TRIGGER OnUpdateAttrVal
   ON  intgr_SyncToAttrVals
   FOR  UPDATE
AS 
	SET NOCOUNT ON;
	BEGIN TRY

		DECLARE @tmpInformation TABLE (	ID_TO_UPDATE int )
		INSERT INTO @tmpInformation	SELECT del.attrval_id
		FROM Deleted del 
			INNER JOIN Inserted ins ON del.id = ins.id AND del.attrval_id <> ins.attrval_id 
			INNER JOIN ATTRIB_VALS At1 ON del.attrval_id = At1.id 
			INNER JOIN ATTRIB_VALS At2 ON ins.attrval_id = At2.id AND At1.Type <> At2.Type

		DECLARE @MAXID INT, @DEL_ID INT, @INS_ID INT
		SELECT @MAXID = COUNT(*) FROM @tmpInformation

		WHILE (@MAXID > 0)
			BEGIN
				--DO THE PROCESSING HERE 
				DELETE FROM  intgr_SyncElemItemsData
				WHERE        (DigispotElemId IN (SELECT PH_ID FROM 
						dbo.ufnGetPhIdentities((SELECT TOP(1) tmp.ID_TO_UPDATE FROM @tmpInformation tmp))))

				DELETE TOP (1) FROM @tmpInformation		
				SET @MAXID = @MAXID - 1

			END

	END TRY  
	BEGIN CATCH  
	END CATCH
'

ALTER TABLE [dbo].[intgr_SyncElemItemsData] DROP CONSTRAINT [FK_SyncElemItemsData_SyncDataBases]

ALTER TABLE [dbo].[intgr_SyncElemItemsData]  WITH CHECK ADD  CONSTRAINT [FK_SyncElemItemsData_SyncDataBases] FOREIGN KEY([SyncedDbId])
REFERENCES [dbo].[intgr_SyncDataBases] ([Id])
ON DELETE NO ACTION

ALTER TABLE [dbo].[intgr_SyncElemItemsData] CHECK CONSTRAINT [FK_SyncElemItemsData_SyncDataBases]

------------------

INSERT INTO components (name, veri) VALUES(@componentName, @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO


--#########################################################################################################

if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 13

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

DECLARE @componentName varchar(20)
SET @componentName = 'IntegrationService'

IF EXISTS( SELECT veri FROM components WHERE name=@componentName and veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

CREATE UNIQUE NONCLUSTERED INDEX [SyncToAttrVals_UNIQUE_AttrVals] ON [dbo].[intgr_SyncToAttrVals]
(
	[attrval_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)


CREATE UNIQUE NONCLUSTERED INDEX [SyncToAttrVals_UNIQUE_ExternalPartition] ON [dbo].[intgr_SyncToAttrVals]
(
	[SyncedDbId] ASC,
	[ExternalPartition] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)


------------------

INSERT INTO components (name, veri) VALUES(@componentName, @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################

if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 14

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

DECLARE @componentName varchar(20)
SET @componentName = 'IntegrationService'

IF EXISTS( SELECT veri FROM components WHERE name=@componentName and veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------

ALTER TABLE [dbo].[intgr_ReconcileSchedules]  
DROP COLUMN  [BlockId], [InBlockId], [BlockTime]

ALTER TABLE [dbo].[intgr_ReconcileSchedules]  
ADD LastOperationId nvarchar(max) NULL 
------------------
INSERT INTO components (name, veri) VALUES(@componentName, @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################


if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 15

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

DECLARE @componentName varchar(20)
SET @componentName = 'IntegrationService'

IF EXISTS( SELECT veri FROM components WHERE name=@componentName and veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------
ALTER TABLE [dbo].[intgr_SyncDataBases]  
ADD PostFix nvarchar(max) NULL 
------------------
INSERT INTO components (name, veri) VALUES(@componentName, @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################


if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver = 16

DECLARE @sver varchar(10)
SET @sver = CAST(@ver AS varchar(10))

DECLARE @componentName varchar(20)
SET @componentName = 'IntegrationService'

IF EXISTS( SELECT veri FROM components WHERE name=@componentName and veri>=@ver )
BEGIN
print 'DB Version is equal or higher than '+ @sver
return
END

print '----------------------------------------------------------'
print 'Updating MDB to Version ' + @sver

SET XACT_ABORT ON
BEGIN TRAN
------------------
--Disable Index
DROP INDEX [intgr_SyncElemItemsData].[MID_EID_UNIQUE]

ALTER TABLE [dbo].[intgr_SyncElemItemsData]  
ALTER COLUMN [ExternalElemId] nvarchar(400) NOT NULL

CREATE UNIQUE NONCLUSTERED INDEX [MID_EID_UNIQUE] ON [dbo].[intgr_SyncElemItemsData]
(
	[SyncedDbId] ASC,
	[ExternalElemId] ASC,
	[DigispotElemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

------------------
INSERT INTO components (name, veri) VALUES(@componentName, @ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################