---------------------------------------------------------------------
-- BASE_VERSION 2801

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

print '----------------------------------------------------------'
print 'Check Server Version '

declare @ver int
declare @ver_str varchar(128)
set @ver_str = convert(varchar(128),SERVERPROPERTY('ProductVersion'))
set @ver = cast( left(@ver_str, charindex('.', @ver_str)-1) as int )

print 'SQL server version ' + @ver_str

if (@ver < 9) begin 
	RAISERROR('SQL server earlier than 2005 (version 9) not supported.',16,1)
	return
end

print '----------------------------------------------------------'
print 'Check database '

if(DB_NAME() = 'master')begin
	RAISERROR('You can not execute this script on the system database ''master''',16,1)
	return
end

IF NOT EXISTS( SELECT sysobjects.name FROM sysobjects WHERE name='TRACT' AND type='U' )
BEGIN 
	RAISERROR(' ''TRACT'' table was not found in the database. You should ececute mdb_create.sql script first.',16,1)
	return
END

print '----------------------------------------------------------'
print 'Check current MDB Version '

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=1780 )
	BEGIN
		print 'Current MDB Version could be updated...'
		print '----------------------------------------------------------'
		return
	END
ELSE
	BEGIN
		print 'Can not update Current MDB Version'
		print '----------------------------------------------------------'
		RAISERROR('Can not update Current MDB Version',16,1)
		return
	END

GO

--#########################################################################################################
-- ### Adding the 'Archive', 'NextPlayTime' and 'UserModifyTime' fields into the 'PH' table...

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

DECLARE @ver int
SET @ver  = 1781

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------
-- ### CONTENT
IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PH' AND syscolumns.name='Archive')
BEGIN 
	ALTER TABLE PH
	ADD Archive 		int default 0 not null, 
	    NextPlayTime 	datetime null, 
	    UserModifyTime 	datetime default CURRENT_TIMESTAMP not null 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
-- ### Creating 'STORAGE' table...

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

DECLARE @ver int
SET @ver  = 1782

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------
-- ### CONTENT
IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='STORAGE' AND TYPE='U')
BEGIN
	CREATE TABLE STORAGE(
	id			int identity not null,
	Type		int NOT NULL,
	CatId		int NULL,
	Archive	int NOT NULL,
	Path		varchar(260),
	primary key (id),
	foreign key (CatId) references ATTRIB_VALS (id) on delete cascade) 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
------------------
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
-- ### Creating 'ACC_RIGHTS' table...

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

DECLARE @ver int
SET @ver  = 1783

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------
-- ### CONTENT
IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='ACC_RIGHTS' AND TYPE='U')
BEGIN
	CREATE TABLE ACC_RIGHTS(
	id		int identity not null,
	Type		int NOT NULL,
	CatId		int NULL,
	CatType		int NOT NULL,
	Archive		int NOT NULL,
	UserName	varchar(260),
	Rights		int NOT NULL,
	primary key (id),
	foreign key (CatId) references ATTRIB_VALS (id) on delete cascade) 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
------------------
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
-- ### EMPTY

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

DECLARE @ver int
SET @ver  = 1790

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------
-- ### NO CONTENT
------------------
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO


--#########################################################################################################
-- ### Adding the 'DayRestrictions' field into the 'PH' table...

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

DECLARE @ver int
SET @ver  = 1800

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------
-- ### CONTENT
IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PH' AND syscolumns.name='DayRestrictions')
BEGIN 
	ALTER TABLE PH
	ADD DayRestrictions binary(168) null 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
-- ### Creating the 'MAG_DB_PARAMS' table...

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

DECLARE @ver int
SET @ver  = 1810

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------
-- ### CONTENT
IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='MAG_DB_PARAMS' AND TYPE='U')
BEGIN
	CREATE TABLE MAG_DB_PARAMS(
	id				int identity not null,
	Type			int not null,
	Name			varchar(3000) not null,
	Value			varchar(3000) not null,
	primary key (id))
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
------------------
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
-- ### Adding the 'ReadyOnAir' field into the 'PH' table...

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

DECLARE @ver int
SET @ver  = 1820

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------
-- ### CONTENT
IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PH' AND syscolumns.name='ReadyOnAir')
BEGIN 
	ALTER TABLE PH 
	ADD ReadyOnAir int default 0 not null
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO



--#########################################################################################################
-- ### Creating the 'DB_IMPORT_FILTERS' table...

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

DECLARE @ver int
SET @ver  = 1830

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------
-- ### CONTENT
IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='DB_IMPORT_FILTERS' AND TYPE='U')
BEGIN
	CREATE TABLE DB_IMPORT_FILTERS(
	id			int identity not null,
	Type			int not null,
	Name			varchar(3000) not null,
	Value			varchar(3000) not null,
	primary key (id))
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
-- ### Adding the 'Headline' field into the 'PH' table...

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

DECLARE @ver int
SET @ver  = 1840

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------
-- ### CONTENT
IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PH' AND syscolumns.name='Headline')
BEGIN 
	ALTER TABLE PH
	ADD Headline varchar(256) null 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
-- ### EMPTY

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

DECLARE @ver int
SET @ver  = 1850

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------
-- ### NO CONTENT
------------------
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
-- ### Creating the 'AUTO_MOVE' table...
-- ### Creating the 'OnDeleteCatForAutoMove' trigger...
-- ### Adding the 'CreationTime' field into the 'PH_TO_ATTRVALS' table...

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

DECLARE @ver int
SET @ver  = 1860

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------
-- ### CONTENT
IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='AUTO_MOVE' AND TYPE='U')
BEGIN
CREATE TABLE AUTO_MOVE(
	id			int identity not null,
	SrcCatID		int not null,
	SrcArchive		int not null,
	DstCatID		int not null,
	DstArchive		int not null,
	DstDelete		int not null,
	KeepTime		bigint not null,
	primary key (id)) 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'OnDeleteCatForAutoMove' AND type = 'TR')
	DROP TRIGGER OnDeleteCatForAutoMove
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

exec sp_executesql N'CREATE TRIGGER OnDeleteCatForAutoMove ON ATTRIB_VALS AFTER UPDATE AS DELETE FROM AUTO_MOVE 
WHERE SrcCatID IN (SELECT INS.ID FROM INSERTED INS WHERE Deleted=1) OR
		   DstCatID IN (SELECT INS.ID FROM INSERTED INS WHERE Deleted=1)'
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PH_TO_ATTRVALS' AND syscolumns.name='CreationTime')
BEGIN 
	ALTER TABLE PH_TO_ATTRVALS 
	ADD CreationTime datetime default CURRENT_TIMESTAMP not null 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
-- ### Adding the 'ForNewsBrowser' field into the 'ATTRIB_VALS' table...

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

DECLARE @ver int
SET @ver  = 1870

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------
-- ### CONTENT
IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='ATTRIB_VALS' AND syscolumns.name='ForNewsBrowser')
BEGIN 
	ALTER TABLE ATTRIB_VALS
	ADD ForNewsBrowser int null 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
-- ### Create table PARAMETERS and OnUpdateParameters trigger

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

DECLARE @ver int
SET @ver  = 1880

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------
-- ### CONTENT
IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='PARAMETERS' AND TYPE='U')
BEGIN
CREATE TABLE PARAMETERS(
id			int 		identity not null,
Type			int 		not null,
Name			varchar(3000) 	not null,
Value			varchar(3000) 	not null,
ModifyDate		datetime 	default CURRENT_TIMESTAMP not null,
primary key (id))
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'OnUpdateParameters' AND type = 'TR')
	DROP TRIGGER OnUpdateParameters
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

exec sp_executesql N'CREATE TRIGGER OnUpdateParameters ON PARAMETERS AFTER UPDATE AS 
UPDATE PARAMETERS SET ModifyDate=CURRENT_TIMESTAMP WHERE ID IN (SELECT INS.ID FROM INSERTED INS)'
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) return END

------------------
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
-- ### 	Adding 'CasseteStartFPS', 'CasseteLengthFPS', 'BackupTapeName', 'BackupTimeCode' and 'KeyFrame' field into the 'PH' table...

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

DECLARE @ver int
SET @ver  = 1890

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------
-- ### CONTENT
IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PH' AND syscolumns.name='CasseteStartFPS')
BEGIN 
ALTER TABLE PH 
ADD 	CasseteStartFPS 	int null, 
	CasseteLengthFPS 	int null, 
	BackupTapeName 		varchar(32), 
	BackupTimeCode 		int null, 
	KeyFrame 		int null, 
	KeyIcon 		image 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
------------------
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
-- ### empty

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

DECLARE @ver int
SET @ver  = 1900

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------
-- NO CONTENT
------------------
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
-- ### Creating the 'MODIFY_DATES' table...

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

DECLARE @ver int
SET @ver  = 1910

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------
-- ### CONTENT
IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='MODIFY_DATES' AND TYPE='U')
BEGIN
CREATE TABLE MODIFY_DATES(
ph_id			int null,
attrib_id		int null,
attrval_id		int null,
ModifyDate		datetime default CURRENT_TIMESTAMP not null, 
foreign key (ph_id) references PH(id) on delete cascade, 
foreign key (attrval_id) references ATTRIB_VALS(id) on delete cascade) 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
------------------
INSERT INTO TRACT (Version) VALUES(@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  = 1960

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='modify_date' AND sysobjects.name='MODIFY_DATES'
)
	DROP INDEX MODIFY_DATES.modify_date
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='modify_date' AND sysobjects.name='PH'
)
	DROP INDEX PH.modify_date
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='modify_date' AND sysobjects.name='ATTRIB_VALS'
)
	DROP INDEX ATTRIB_VALS.modify_date
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='modify_date' AND sysobjects.name='ATTRIBS'
)
	DROP INDEX ATTRIBS.modify_date
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

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

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ModifyPHAfterAttrVal' AND type = 'TR')
	DROP TRIGGER ModifyPHAfterAttrVal
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'OnDeleteAttrVal' AND type = 'TR')
	DROP TRIGGER OnDeleteAttrVal
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'InsertPH' AND type = 'TR')
	DROP TRIGGER InsertPH
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ModifyPH' AND type = 'TR')
	DROP TRIGGER ModifyPH
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ModifyPH_Arc' AND type = 'TR')
	DROP TRIGGER ModifyPH_Arc
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ModifyAttrVal' AND type = 'TR')
	DROP TRIGGER ModifyAttrVal
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'InsertAttrVal' AND type = 'TR')
	DROP TRIGGER InsertAttrVal
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ModifyAttribs' AND type = 'TR')
	DROP TRIGGER ModifyAttribs
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'InsertAttribs' AND type = 'TR')
	DROP TRIGGER InsertAttribs
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'OnModifyCatJnglBtns' AND type = 'TR')
	DROP TRIGGER OnModifyCatJnglBtns
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'OnInsertPhToAttrVals' AND type = 'TR')
	DROP TRIGGER OnInsertPhToAttrVals
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'OnDeletePhToAttrVals' AND type = 'TR')
	DROP TRIGGER OnDeletePhToAttrVals
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

--

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='TRACT' AND syscolumns.name='critical')
BEGIN							
ALTER TABLE TRACT 
ADD critical int default 0 not null 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

DELETE FROM MODIFY_DATES where ModifyDate<dateadd(hour, -1, CURRENT_TIMESTAMP)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='MODIFY_DATES' AND syscolumns.name='cat_jngl_btns_id')
BEGIN							
ALTER TABLE MODIFY_DATES 
ADD cat_jngl_btns_id int null 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='MODIFY_DATES' AND syscolumns.name='id')
BEGIN		
delete from MODIFY_DATES					
ALTER TABLE MODIFY_DATES
ADD id int identity not null,
primary key (id)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

--
exec sp_executesql N'CREATE TRIGGER InsertPH ON PH AFTER INSERT AS 
BEGIN 
	INSERT INTO MODIFY_DATES (ph_id) SELECT id FROM INSERTED 
END'
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) return END
 
exec sp_executesql N'CREATE TRIGGER ModifyPH ON PH AFTER UPDATE AS 
if (substring(columns_updated(),3,1)&power(2,3)=0 AND 
substring(columns_updated(),3,1)&power(2,2)=0) 
BEGIN 
	UPDATE PH SET ModifyDate=CURRENT_TIMESTAMP WHERE ID IN (SELECT ID FROM INSERTED) 
	INSERT INTO MODIFY_DATES (ph_id) SELECT id FROM INSERTED 
END'
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) return END

exec sp_executesql N'CREATE TRIGGER ModifyPH_Arc ON PH AFTER UPDATE AS 
if (substring(columns_updated(),7, 1) & power(2,6)<>0) 
BEGIN 
	INSERT INTO MODIFY_DATES (attrval_id) 
	SELECT PH_TO_ATTRVALS.attrval_id
	FROM INSERTED WITH(NOLOCK) LEFT OUTER JOIN PH_TO_ATTRVALS 
	ON INSERTED.ID=PH_TO_ATTRVALS.ph_id 
END'
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

exec sp_executesql N'CREATE TRIGGER ModifyAttrVal ON ATTRIB_VALS AFTER INSERT, UPDATE AS 
BEGIN 
	UPDATE ATTRIB_VALS SET ModifyDate=CURRENT_TIMESTAMP 
	WHERE ID IN (SELECT ID FROM INSERTED) 
	INSERT INTO MODIFY_DATES (attrval_id) 
	SELECT id from INSERTED 
END'
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

exec sp_executesql N'CREATE TRIGGER ModifyAttribs ON ATTRIBS AFTER INSERT, UPDATE AS 
BEGIN 
	UPDATE ATTRIBS SET ModifyDate=CURRENT_TIMESTAMP 
	WHERE ID IN (SELECT ID FROM INSERTED) 
	INSERT MODIFY_DATES (attrib_id) 
	SELECT id from INSERTED 
END'
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

exec sp_executesql N'CREATE TRIGGER OnModifyCatJnglBtns ON CAT_JNGL_BTNS AFTER INSERT, UPDATE AS 
BEGIN
	UPDATE CAT_JNGL_BTNS SET ModifyDate=CURRENT_TIMESTAMP 
	WHERE ID IN (SELECT ID FROM INSERTED)
	INSERT MODIFY_DATES (cat_jngl_btns_id) 
	SELECT id from INSERTED 
END'
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

DECLARE @v1 int
SET @v1=1910
WHILE (@v1<@ver)
BEGIN
IF NOT EXISTS( SELECT Version FROM TRACT WHERE Version=@v1 )
	INSERT INTO TRACT (Version) VALUES(@v1)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
SET @v1 = @v1 + 10
END

INSERT INTO TRACT (Version) VALUES(@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  = 1990

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
SELECT sysobjects.id FROM sysobjects
WHERE sysobjects.name='TB_ATTRVAL' AND TYPE='U')
BEGIN
CREATE TABLE TB_ATTRVAL(
id		int identity not null,
TbName		varchar(64) NOT NULL,
BtnName		varchar(64) NOT NULL,
attrval_id	int not null,
primary key (id),
foreign key (attrval_id) references ATTRIB_VALS (id) on delete cascade)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='ATTRIBS' AND syscolumns.name='ForNewsBrowser')
BEGIN							
ALTER TABLE ATTRIBS
ADD ForNewsBrowser int null
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PH' AND syscolumns.name='TmStamp')
BEGIN							
ALTER TABLE PH
ADD TmStamp TIMESTAMP 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='ATTRIBS' AND syscolumns.name='ToolBar')
BEGIN							
ALTER TABLE ATTRIBS
ADD ToolBar int null
END

if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

INSERT INTO TRACT (Version) VALUES(@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  = 2000

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PH' AND syscolumns.name='BackupLength')
BEGIN							
ALTER TABLE PH
ADD BackupLength int null
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PH' AND syscolumns.name='ProtyagFirst')
BEGIN							
ALTER TABLE PH
ADD ProtyagFirst int null
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PH' AND syscolumns.name='ProtyagLast')
BEGIN							
ALTER TABLE PH
ADD ProtyagLast int null
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='VIDEO_ARC_TAPES' AND TYPE='U')
BEGIN
CREATE TABLE VIDEO_ARC_TAPES(
	id		int identity not null,
	Name		varchar(32) not null,
	StartTc		int not null,
	FirstBfSize	int not null,
	GcpSize		int not null,
	SecondBfSize	int not null,
	BfSize		int not null,
	BlkAlignment	int not null,
	BfMinSize	int not null,
	LastBfSize	int not null,
	primary key (id),
	unique (Name))
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

INSERT INTO TRACT (Version) VALUES(@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  = 2010

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PH' AND syscolumns.name='LeftRightCh')
BEGIN							
ALTER TABLE PH
ADD LeftRightCh int null
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

INSERT INTO TRACT (Version) VALUES(@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  = 2020

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='TB_ATTRVAL' AND syscolumns.name='ATTRIB_ID')
BEGIN							
ALTER TABLE TB_ATTRVAL
ADD ATTRIB_ID int null
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'DeleteAttribs' AND type = 'TR')
	DROP TRIGGER DeleteAttribs
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

exec sp_executesql N'CREATE TRIGGER DeleteAttribs ON ATTRIBS
AFTER DELETE AS
BEGIN 
	DELETE FROM TB_ATTRVAL 
	WHERE attrib_id  IN (SELECT ID FROM DELETED) 
END'
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END


INSERT INTO TRACT (Version) VALUES(@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  = 2030

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 EXISTS(
SELECT sysobjects.id FROM sysobjects
WHERE sysobjects.name='PLAY_HISTORY' AND TYPE='U')
BEGIN
DROP TABLE PLAY_HISTORY
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
SELECT sysobjects.id FROM sysobjects
WHERE sysobjects.name='PH_PLAY_HISTORY' AND TYPE='U')
BEGIN
CREATE TABLE PH_PLAY_HISTORY(
id		int identity not null,
ph_id		int NOT NULL,
PlayTime	datetime default CURRENT_TIMESTAMP not null,
primary key (id),
foreign key (ph_id) references PH (id) on delete cascade)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='PlayTime' AND sysobjects.name='PH_PLAY_HISTORY'
)CREATE INDEX PlayTime ON PH_PLAY_HISTORY(PlayTime)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
SELECT sysobjects.id FROM sysobjects
WHERE sysobjects.name='PH_PLAY_PLAN' AND TYPE='U')
BEGIN
CREATE TABLE PH_PLAY_PLAN(
id		int identity not null,
ph_id		int NOT NULL,
PlayTime	datetime default CURRENT_TIMESTAMP not null,
primary key (id),
foreign key (ph_id) references PH (id) on delete cascade)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='PlayTime' AND sysobjects.name='PH_PLAY_PLAN'
)CREATE INDEX PlayTime ON PH_PLAY_PLAN(PlayTime)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS(
SELECT sysobjects.id FROM sysobjects
WHERE sysobjects.name='TB_ATTRVAL' AND TYPE='U')
BEGIN
	ALTER TABLE TB_ATTRVAL
	ALTER COLUMN ATTRVAL_ID int null
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

INSERT INTO TRACT (Version) VALUES(@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  = 2040

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 EXISTS (SELECT name FROM sysobjects WHERE name = 'OnInsertPhToAttrVals' AND type = 'TR')
	DROP TRIGGER OnInsertPhToAttrVals
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'OnDeletePhToAttrVals' AND type = 'TR')
	DROP TRIGGER OnDeletePhToAttrVals
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'OnPhToAttrVals' AND type = 'TR')
	DROP TRIGGER OnPhToAttrVals
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

exec sp_executesql N'CREATE TRIGGER OnPhToAttrVals ON PH_TO_ATTRVALS AFTER INSERT,UPDATE,DELETE AS 
BEGIN 
	INSERT MODIFY_DATES (ph_id)      SELECT ph_id      FROM INSERTED 
	INSERT MODIFY_DATES (ph_id)      SELECT ph_id      FROM DELETED
	INSERT MODIFY_DATES (attrval_id) SELECT attrval_id FROM INSERTED 
	INSERT MODIFY_DATES (attrval_id) SELECT attrval_id FROM DELETED
END' 
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END



INSERT INTO TRACT (Version) VALUES(@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  = 2050

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='attrib_vals' AND syscolumns.name='SearchDepth')
BEGIN							
ALTER TABLE attrib_vals 
ADD SearchDepth int null 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

INSERT INTO TRACT (Version) VALUES(@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  = 2060

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mag_work' AND syscolumns.name='attrval_id')
BEGIN							

exec sp_executesql N'
  DELETE FROM mag_work
  ALTER TABLE mag_work ALTER COLUMN sch_order int null
  ALTER TABLE mag_work ADD attrval_id int null
  ALTER TABLE mag_work ALTER COLUMN attrval_id int not null'

END	
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

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

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

DECLARE @ver int
SET @ver  = 2070

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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

INSERT INTO TRACT (Version) VALUES(@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  = 2080

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
SELECT sysobjects.id FROM sysobjects
WHERE sysobjects.name='P_BLOCKS' AND TYPE='U')
BEGIN
CREATE TABLE P_BLOCKS(
	id int IDENTITY NOT NULL ,
	DdbOwner varchar (260) NOT NULL ,
	SchName varchar (260) NOT NULL ,
	BlkName varchar (260) NOT NULL ,
	BlkDate datetime NOT NULL ,
	TmStamp TIMESTAMP  NOT NULL ,
	BlkMT BIGINT  NOT NULL,
	primary key (id))
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

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

--#########################################################################################################
-- add a NICK columm to ATTRIBS
if(@@ERROR<>0)
BEGIN
RAISERROR('Script terminated...',16,1)
return
END

DECLARE @ver int
SET @ver  = 2090

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='ATTRIBS' AND syscolumns.name='NICK')
BEGIN	
ALTER TABLE ATTRIBS
ADD NICK varchar(64) null 
END

if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

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

--#########################################################################################################
-- set ATTRIBS.NICK for predefined attribs and 
-- Add Attrib with NICK=VOCAL for music and jingles add
-- add attrib values for VOCAL 

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

DECLARE @ver int
SET @ver  = 2100

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='ATTRIBS' AND syscolumns.name='NICK')
BEGIN	
ALTER TABLE ATTRIBS
ADD NICK varchar(64) null 
END

if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
--------------------

IF EXISTS ( SELECT ID FROM ATTRIBS WHERE ID = 1 )
BEGIN
UPDATE ATTRIBS SET NICK='CATEGORY' WHERE ID = 1
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

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

IF EXISTS ( SELECT ID FROM ATTRIBS WHERE ID = 2 )
BEGIN
UPDATE ATTRIBS SET NICK='ARTIST' WHERE ID = 2
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

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

IF EXISTS ( SELECT ID FROM ATTRIBS WHERE ID = 3 )
BEGIN
UPDATE ATTRIBS SET NICK='AUTHOR' WHERE ID = 3
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

--------------------
-- VOCAL FOR JINGLES
IF NOT EXISTS ( SELECT ID FROM ATTRIBS WHERE NICK='VOCAL' AND TYPE=2 )
BEGIN
INSERT INTO ATTRIBS (Name,Type,Nick) VALUES ('Vocal',2,'VOCAL')
DECLARE @attrib_id_jngl int
SELECT @attrib_id_jngl = ID FROM ATTRIBS WHERE NICK='VOCAL' AND TYPE=2
INSERT INTO ATTRIB_VALS (Name, Type, attr_id )  VALUES ('Male',    2, @attrib_id_jngl )
INSERT INTO ATTRIB_VALS (Name, Type, attr_id )  VALUES ('Female',  2, @attrib_id_jngl )
INSERT INTO ATTRIB_VALS (Name, Type, attr_id )  VALUES ('Duet',    2, @attrib_id_jngl )
INSERT INTO ATTRIB_VALS (Name, Type, attr_id )  VALUES ('Chorus',  2, @attrib_id_jngl )
INSERT INTO ATTRIB_VALS (Name, Type, attr_id )  VALUES ('Instrumental',  2, @attrib_id_jngl )
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

--------------------
-- VOCAL FOR MUSIC
IF NOT EXISTS ( SELECT ID FROM ATTRIBS WHERE NICK='VOCAL' AND TYPE=3 )
BEGIN
INSERT INTO ATTRIBS (Name,Type,Nick) VALUES ('Vocal',3,'VOCAL')
DECLARE @attrib_id_music int
SELECT @attrib_id_music = ID FROM ATTRIBS WHERE NICK='VOCAL' AND TYPE=3
INSERT INTO ATTRIB_VALS (Name, Type, attr_id )  VALUES ('Male',    3, @attrib_id_music )
INSERT INTO ATTRIB_VALS (Name, Type, attr_id )  VALUES ('Female',  3, @attrib_id_music )
INSERT INTO ATTRIB_VALS (Name, Type, attr_id )  VALUES ('Duet',    3, @attrib_id_music )
INSERT INTO ATTRIB_VALS (Name, Type, attr_id )  VALUES ('Chorus',  3, @attrib_id_music )
INSERT INTO ATTRIB_VALS (Name, Type, attr_id )  VALUES ('Instrumental',  3, @attrib_id_music )
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
--------------------

INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

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

--#########################################################################################################
-- ADD MAG_SETTINGS table

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

DECLARE @ver int
SET @ver  = 2110

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='MAG_SETTINGS' AND TYPE='U')
BEGIN
CREATE TABLE [MAG_SETTINGS] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [char] (256) NOT NULL ,
	[Text] [text] NOT NULL ,
	CONSTRAINT [IX_MAG_SETTINGS] UNIQUE  NONCLUSTERED 
	(
		[Name]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
------------------
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
-- ADD fields to PH table: LcCode(licence), Publisher, Rds

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

DECLARE @ver int
SET @ver  = 2120

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PH' AND syscolumns.name='LcCode')
BEGIN	
ALTER TABLE PH
ADD LcCode varchar(64) null 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
------------------
if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PH' AND syscolumns.name='Publisher')
BEGIN	
ALTER TABLE PH
ADD Publisher varchar(64) null 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
------------------
if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PH' AND syscolumns.name='Rds')
BEGIN	
ALTER TABLE PH
ADD Rds varchar(128) null 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2130

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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

INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

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

--#########################################################################################################
-- Create table SCH_FILES

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

DECLARE @ver int
SET @ver  = 2140

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
SELECT sysobjects.id FROM sysobjects
WHERE sysobjects.name='SCH_FILES' AND TYPE='U')
BEGIN
CREATE TABLE SCH_FILES(
id		int identity not null,
BlkName		varchar(260) NOT NULL,
FileName	varchar(260) NOT NULL,
SchDate		datetime not null,
primary key (id)
)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
-- ADD fields to PH table: Cmd2, TextSizeMs, PhonoType

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

DECLARE @ver int
SET @ver  = 2150

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PH' AND syscolumns.name='Cmd2')
BEGIN	
ALTER TABLE PH
ADD Cmd2 int null
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
------------------
if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PH' AND syscolumns.name='TextSizeMs')
BEGIN	
ALTER TABLE PH
ADD TextSizeMs int null
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
------------------
if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PH' AND syscolumns.name='PhonoType')
BEGIN	
ALTER TABLE PH
ADD PhonoType int null
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

--#########################################################################################################
-- ADD fields to PH table: OwnerName

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

DECLARE @ver int
SET @ver  = 2170

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PH' AND syscolumns.name='OwnerName')
BEGIN	
ALTER TABLE PH
ADD OwnerName varchar(64) null
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
------------------

ALTER TABLE ph DISABLE TRIGGER ModifyPH
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

update ph set TextSizeMs=Duration where TextSizeMs IS NULL AND (filename LIKE '%.txt' or filename LIKE '%.sbj' or filename LIKE '%.rtf')
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

ALTER TABLE ph ENABLE TRIGGER ModifyPH
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print 'Version ' + @sver + ' updated successufully'
print '----------------------------------------------------------'
GO

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

DECLARE @ver int
SET @ver  = 2190

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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

INSERT INTO TRACT (Version) VALUES(@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  = 2200

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='USERS' AND TYPE='U')
BEGIN
	CREATE TABLE USERS(
	id		int identity not null,
	Login		varchar (64) NOT NULL,
	[password]	varchar (64) NULL,
	FullName	varchar (128) NOT NULL,
	[Description]	varchar (128) NULL,
	Disabled	int NULL,
	primary key (id),
	CONSTRAINT [IX_users] UNIQUE  NONCLUSTERED (login) 
)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='GROUPS' AND TYPE='U')
BEGIN
	CREATE TABLE GROUPS(
	id		int identity not null,
	str_id		varchar (64) NULL,
	[name]		varchar (128) NOT NULL,
	primary key (id))
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='RIGHTS' AND TYPE='U')
BEGIN
	CREATE TABLE RIGHTS(
	id		int identity not null,
	group_id	int NOT NULL,
	right_id	int NOT NULL,
	primary key (id),
	foreign key (group_id) references groups(id) on delete cascade) 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='USER_GROUP' AND TYPE='U')
BEGIN
	CREATE TABLE USER_GROUP(
	id		int identity not null,
	group_id	int NOT NULL,
	[user_id]	int NOT NULL,
	primary key (id),
	foreign key (group_id) references groups(id) on delete cascade, 
	foreign key (user_id) references users(id) on delete cascade) 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2210

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='SCHEDULES' AND TYPE='U')
BEGIN
	CREATE TABLE SCHEDULES(
	id		int identity not null,
	[name]		varchar (128) NOT NULL,
	path		varchar (256) NOT NULL,
	skel_path	varchar (256) NULL,
	manual_load	int NULL,
	disabled	int NULL,
	primary key (id),
	CONSTRAINT [IX_sch_bname] UNIQUE  NONCLUSTERED (name) 
)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='GRIDS' AND TYPE='U')
BEGIN
	CREATE TABLE GRIDS(
	id		int identity not null,
	bdate		datetime NULL,
	[name]		varchar (128) NOT NULL,
	schedule_id	int NOT NULL,
	primary key (id),
	foreign key (schedule_id) references SCHEDULES(id) on delete cascade
)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='GRID_DAYS' AND TYPE='U')
BEGIN
	CREATE TABLE GRID_DAYS(
	id		int identity not null,
	grid_id		int NOT NULL,
	week_day	int NULL,
	month_num	int NULL,
	month_day	int NULL,
	primary key (id),
	foreign key (grid_id) references GRIDS(id) on delete cascade
)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='GRID_CLOCKS' AND TYPE='U')
BEGIN
	CREATE TABLE GRID_CLOCKS(
	id		int identity not null,
	[name]		varchar (128) NOT NULL,
	sch_id		int NOT NULL,
	primary key (id),
	foreign key (sch_id) references SCHEDULES(id) on update cascade
)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='GRID_CLOCK_POS' AND TYPE='U')
BEGIN
	CREATE TABLE GRID_CLOCK_POS(
	id		int identity not null,
	day_id		int NOT NULL,
	clock_id	int NOT NULL,
	hour_num	int NOT NULL,
	primary key (id),
	foreign key (day_id) references GRID_DAYS(id) on delete cascade,
	foreign key (clock_id) references GRID_CLOCKS(id) on delete cascade
)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='PLAY_BLOCKS' AND TYPE='U')
BEGIN
	CREATE TABLE PLAY_BLOCKS(
	id		int identity not null,
	b_time		int NOT NULL,
	dur		int NOT NULL,
	type		int NOT NULL,
	[name]		varchar(128) NULL,
	blk_data	text NULL,
	primary key (id)
)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='GRID_BLOCKS' AND TYPE='U')
BEGIN
	CREATE TABLE GRID_BLOCKS(
	id		int identity not null,
	clock_id	int NOT NULL,
	play_block_id	int NOT NULL,
	primary key (id),
	foreign key (clock_id) references GRID_CLOCKS(id) on delete cascade,
	foreign key (play_block_id) references PLAY_BLOCKS(id) on delete cascade
)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2220

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_base_costs' AND TYPE='U')
BEGIN
	create table mp_base_costs (
	   id                   int                  identity,
	   tariff_scales_id     int                  not null,
	   date                 datetime             null,
	   day                  smallint             null,
	   cost                 money                not null,
	   hour                 smallint             null,
	   show_type            smallint             not null,
	   constraint PK_MP_BASE_COSTS primary key nonclustered (id)
	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='x18_FK' AND sysobjects.name='mp_base_costs'
)
BEGIN
	create index [x18_FK] on mp_base_costs (tariff_scales_id ASC)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_covenantees' AND TYPE='U')
BEGIN
	create table mp_covenantees (
	   id                   int                  identity,
	   users_id             int                  not null,
	   name                 varchar(100)         not null,
	   contacts             varchar(500)         null,
	   constraint PK_MP_COVENANTEES primary key nonclustered (id)
	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='x1_FK' AND sysobjects.name='mp_covenantees'
)
BEGIN
	create index [x1_FK] on mp_covenantees (users_id ASC)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_discount_types' AND TYPE='U')
BEGIN
	create table mp_discount_types (
	   id                   int                  identity,
	   name                 varchar(100)         not null,
	   apply_method         smallint             not null,
	   constraint PK_MP_DISCUONT_TYPES primary key nonclustered (id)
	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_discount_values' AND TYPE='U')
BEGIN
	create table mp_discount_values (
	   id                   int                  identity,
	   discount_types_id     int                  not null,
	   tariff_scales_id     int                  not null,
	   default_value        smallint             not null,
	   min_value            smallint             not null,
	   max_value            smallint             not null,
	   apply_method         smallint             not null,
	   constraint PK_MP_DISCOUNT_VALUES primary key nonclustered (id)
	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='x9_FK' AND sysobjects.name='mp_discount_values'
)
BEGIN
	create index [x9_FK] on mp_discount_values (discount_types_id ASC)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='x10_FK' AND sysobjects.name='mp_discount_values'
)
BEGIN
	create index [x10_FK] on mp_discount_values (tariff_scales_id ASC)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_mediaplans' AND TYPE='U')
BEGIN
	create table mp_mediaplans (
	   id                   int                  identity,
	   users_id             int                  not null,
	   tariff_scales_id     int                  not null,
	   covenantees_id       int                  not null,
	   schedules_id         int                  not null,
	   name                 varchar(100)         not null,
	   begin_date           datetime             not null,
	   end_date             datetime             not null,
	   modify_date          datetime             not null,
	   cost               	money             	 null,
	   releasedCost        	money             	 null,
	   status               smallint             not null,
	   constraint PK_MP_MEDIAPLANS primary key nonclustered (id)
	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='x3_FK' AND sysobjects.name='mp_mediaplans'
)
BEGIN
	create index [x3_FK] on mp_mediaplans (covenantees_id ASC)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='x4_FK' AND sysobjects.name='mp_mediaplans'
)
BEGIN
	create index [x4_FK] on mp_mediaplans (schedules_id ASC)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='x8_FK' AND sysobjects.name='mp_mediaplans'
)
BEGIN
	create index [x8_FK] on mp_mediaplans (tariff_scales_id ASC)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='last_user_FK' AND sysobjects.name='mp_mediaplans'
)
BEGIN
	create index [last_user_FK] on mp_mediaplans (users_id ASC)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_operations' AND TYPE='U')
BEGIN
	create table mp_operations (
	   id                   int                  identity,
	   users_id             int                  not null,
	   datetime             datetime             not null,
	   object               varchar(100)         not null,
	   type                 smallint             not null,
	   core                 varchar(500)         not null,
	   constraint PK_MP_OPERATIONS primary key nonclustered (id)
	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='x16_FK' AND sysobjects.name='mp_operations'
)
BEGIN
	create index [x16_FK] on mp_operations (users_id ASC)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_organizations' AND TYPE='U')
BEGIN
	create table mp_organizations (
	   id                   int                  identity,
	   covenantees_id       int                  not null,
	   name                 varchar(100)         not null,
	   essential_elements   varchar(500)         null,
	   constraint PK_MP_ORGANIZATIONS primary key nonclustered (id)
	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='x2_FK' AND sysobjects.name='mp_organizations'
)
BEGIN
	create index [x2_FK] on mp_organizations (	covenantees_id ASC	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_phonograms' AND TYPE='U')
BEGIN
	create table mp_phonograms (
	   id                   int                  identity,
	   ph_id                int                  null,
	   mediaplan_id         int                  null,
 	   covenantee_id        int                  null,
	   plan_number          smallint             not null,
	   type                 smallint             not null,
	   text                 varchar(1024)        null,
	   duration             int                  null,
	   plan_number_pos      smallint             not null,
	   constraint PK_MP_PHONOGRAMS primary key nonclustered (id)
	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='x13_FK' AND sysobjects.name='mp_phonograms'
)
BEGIN
	create index [x13_FK] on mp_phonograms (mediaplan_id ASC)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='x15_FK' AND sysobjects.name='mp_phonograms'
)
BEGIN
	create index [x15_FK] on mp_phonograms (ph_id ASC)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_positions' AND TYPE='U')
BEGIN
	create table mp_positions (
	   id                   int                  identity,
	   mediaplans_id        int                  not null,
	   users_id             int                  not null,
	   date                 datetime             not null,
	   block_number         smallint             not null,
	   plan_number          smallint             not null,
	   first                bit                  not null,
	   released             bit                  not null,
	   release_date         datetime             null,
	   version        		int             	 not null,
	   type 				int             	 not null,
	   position		 		int             	 not null,
	   placementMethod 		int             	 not null,
	   constraint PK_MP_POSITIONS primary key nonclustered (id)
	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='owner_FK' AND sysobjects.name='mp_positions'
)
BEGIN
	create index [owner_FK] on mp_positions (users_id ASC)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='x5_FK' AND sysobjects.name='mp_positions'
)
BEGIN
	create index [x5_FK] on mp_positions (mediaplans_id ASC)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_reports' AND TYPE='U')
BEGIN
	create table mp_reports (
	   id                   int                  identity,
	   name                 varchar(100)         not null,
	   application          varchar(250)         not null,
	   xml                  text                 not null,
	   xslt                 text                 not null,
	   constraint PK_MP_REPORTS primary key nonclustered (id)
	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_settings' AND TYPE='U')
BEGIN
	create table mp_settings (
	   id                   int                  identity,
	   name                 varchar(100)         not null,
	   value                binary(1024)         not null,
	   enabled              bit                  not null,
	   constraint PK_MP_SETTINGS primary key nonclustered (id)
	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_tariff_scales' AND TYPE='U')
BEGIN
	create table mp_tariff_scales (
	   id                   int                  identity,
	   schedules_id         int                  not null,
	   begin_date           datetime             not null,
	   type                 smallint             not null,
	   max_persent          smallint             not null,
	   exchange             smallint             not null,
	   constraint PK_MP_TARIFF_SCALES primary key nonclustered (id)
	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='x7_FK' AND sysobjects.name='mp_tariff_scales'
)
BEGIN
	create index [x7_FK] on mp_tariff_scales (schedules_id ASC)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_used_discounts' AND TYPE='U')
BEGIN
	create table mp_used_discounts (
	   id                   int                  identity,
	   mediaplans_id        int                  not null,
	   discount_values_id    int                  not null,
	   manager_value        smallint             not null,
	   constraint PK_MP_USED_DISCOUNTS primary key nonclustered (id)
	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='x11_FK' AND sysobjects.name='mp_used_discounts'
)
BEGIN
	create index [x11_FK] on mp_used_discounts (mediaplans_id ASC)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='x11_FK' AND sysobjects.name='mp_used_discounts'
)
BEGIN
	create index [x11_FK] on mp_used_discounts (discount_values_id ASC)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_accounts' AND TYPE='U')
BEGIN
	CREATE TABLE mp_accounts(
		[id] [int] IDENTITY(1,1) NOT NULL,
		[covenantee_id] [int] NOT NULL,
		[organization_id] [int] NULL,
		[date] [datetime] NULL,
		[mediaplan_id] [int] NULL,
		[price] [money] NULL,
		[amount] [int] NULL,
		[number] [varchar](100) NOT NULL,
		CONSTRAINT [PK_accounts] primary key nonclustered (id) 
	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_payments' AND TYPE='U')
BEGIN
	CREATE TABLE mp_payments(
		[id] [int] IDENTITY(1,1) NOT NULL,
		[account_id] [int] NOT NULL,
		[payment_date] [datetime] NOT NULL,
		[price] [money] NOT NULL,
		[order_number] [varchar](100) NOT NULL,
		CONSTRAINT [PK_payments] primary key nonclustered (id)  
	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

alter table mp_base_costs
   add constraint FK_MP_BASE__18_MP_TARIF foreign key (tariff_scales_id)
      references mp_tariff_scales (id)
         on delete cascade
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

alter table mp_covenantees
   add constraint FK_MP_COVEN_1_USERS foreign key (users_id)
      references users (id)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

alter table mp_discount_values
   add constraint FK_MP_DISCO_10_MP_TARIF foreign key (tariff_scales_id)
      references mp_tariff_scales (id)
         on delete cascade
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

alter table mp_discount_values
   add constraint FK_MP_DISCO_9_MP_DISCO foreign key (discount_types_id)
      references mp_discount_types (id)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

alter table mp_mediaplans
   add constraint FK_MP_MEDIA_3_MP_COVEN foreign key (covenantees_id)
      references mp_covenantees (id)
         on delete cascade
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

alter table mp_mediaplans
   add constraint FK_MP_MEDIA_4_SCHEDULE foreign key (schedules_id)
      references schedules (id)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

alter table mp_mediaplans
   add constraint FK_MP_MEDIA_8_MP_TARIF foreign key (tariff_scales_id)
      references mp_tariff_scales (id)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

alter table mp_mediaplans
   add constraint FK_MP_MEDIA_LAST_USERS foreign key (users_id)
      references users (id)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

alter table mp_operations
   add constraint FK_MP_OPERA_16_USERS foreign key (users_id)
      references users (id)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

alter table mp_organizations
   add constraint FK_MP_ORGAN_2_MP_COVEN foreign key (covenantees_id)
      references mp_covenantees (id)
         on delete cascade
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

alter table mp_phonograms
   add constraint FK_MP_PHONO_13_MP_MEDIA foreign key (mediaplan_id)
      references mp_mediaplans (id)
         on delete cascade
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

alter table mp_phonograms
   add constraint FK_MP_PHONO_15_PH foreign key (ph_id)
      references ph (id)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

alter table mp_positions
   add constraint FK_MP_POSIT_5_MP_MEDIA foreign key (mediaplans_id)
      references mp_mediaplans (id)
         on delete cascade
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

alter table mp_positions
   add constraint FK_MP_POSIT_OWNER_USERS foreign key (users_id)
      references users (id)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

alter table mp_tariff_scales
   add constraint FK_MP_TARIF_7_SCHEDULE foreign key (schedules_id)
      references schedules (id)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

alter table mp_used_discounts
   add constraint FK_MP_USED__11_MP_MEDIA foreign key (mediaplans_id)
      references mp_mediaplans (id)
         on delete cascade
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

alter table mp_used_discounts
   add constraint FK_MP_USED__12_MP_DISCO foreign key (discount_values_id)
      references mp_discount_values (id)
         on delete cascade
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2230

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2240

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 PH_PLAY_HISTORY
	ADD [sch_id]    [int] NULL,
	[mp_pos_id] [int] NULL
	
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END	
------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2250

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 mp_accounts
 add constraint FK_MP_MEDIA_MP_ACCOUNT foreign key (mediaplan_id)
    references mp_mediaplans (id) ON DELETE CASCADE 
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END


alter table mp_settings ALTER COLUMN name varchar(1000) 
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END


------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2260

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 mp_covenantees ADD insertionTime datetime DEFAULT GetDate() NOT NULL, modificationTime datetime NOT NULL DEFAULT GetDate() 
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

-- update bugfix. Added {default ''}.
alter table mp_discount_values add name varchar(100) NOT NULL default ''
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

-- update bugfix. Commented.
-- alter table mp_discount_values ALTER COLUMN name varchar(100) NOT NULL
-- if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

alter table mp_discount_values DROP CONSTRAINT FK_MP_DISCO_9_MP_DISCO
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

DROP INDEX mp_discount_values.x9_FK
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

alter table mp_discount_values DROP COLUMN discount_types_id 
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

insert into mp_discount_types(name, apply_method) VALUES('Ñêèäêà çà ñâîáîäíîå ðàçìåùåíèå', 1)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

insert into mp_discount_types(name, apply_method) VALUES('Íàöåíêà çà ïåðâîå ìåñòî', 1)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

insert into mp_discount_types(name, apply_method) VALUES('Ñêèäêà çà îáúåì', 1)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

insert into mp_discount_types(name, apply_method) VALUES('Ñåçîííàÿ ñêèäêà', 1)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END



------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2270

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 not exists (select top 1 id from groups where str_id='trafman')
	update groups set str_id='trafman' where str_id='TRAF_MAN'
else
	delete from groups where str_id='TRAF_MAN'

if not exists (select top 1 id from groups where str_id='saleman')
	update groups set str_id='saleman' where str_id='SALE_MAN'
else
	delete from groups where str_id='SALE_MAN'

if not exists (select top 1 id from groups where str_id='chifman')
	update groups set str_id='chifman' where str_id='CHIF_MAN'
else
	delete from groups where str_id='CHIF_MAN'

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2280

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
SELECT sysobjects.id FROM sysobjects
WHERE sysobjects.name='mp_organization_attributes' AND TYPE='U')
BEGIN
CREATE TABLE [dbo].[mp_organization_attributes](
[id] [int] IDENTITY(1,1) NOT NULL,
[organization_id] [int] NOT NULL,
[name] [varchar](100) NOT NULL,
[value] [nchar](500) NULL,
CONSTRAINT [PK_mp_organization_attributes] PRIMARY KEY CLUSTERED ([id])
)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

alter table mp_organizations drop column essential_elements
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2290

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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.Tmp_mp_organization_attributes
  (
  id int NOT NULL IDENTITY (1, 1),
  organization_id int NOT NULL,
  name varchar(100) NOT NULL,
  value nvarchar(500) NULL
  )  ON [PRIMARY]
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

SET IDENTITY_INSERT dbo.Tmp_mp_organization_attributes ON
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS(SELECT * FROM dbo.mp_organization_attributes)
   EXEC('INSERT INTO dbo.Tmp_mp_organization_attributes (id, organization_id, name, value)
    SELECT id, organization_id, name, CONVERT(nvarchar(500), value) FROM dbo.mp_organization_attributes')
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END    

SET IDENTITY_INSERT dbo.Tmp_mp_organization_attributes OFF
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

DROP TABLE dbo.mp_organization_attributes
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

EXECUTE sp_rename N'dbo.Tmp_mp_organization_attributes', N'mp_organization_attributes', 'OBJECT' 
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

ALTER TABLE dbo.mp_organization_attributes ADD CONSTRAINT
  PK_mp_organization_attributes PRIMARY KEY CLUSTERED 
  (
  id
  ) ON [PRIMARY]
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2300

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PH' AND syscolumns.name='DdbTmStamp')
BEGIN							
ALTER TABLE PH
ADD DdbTmStamp bigint NULL 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PH_TO_ATTRVALS' AND syscolumns.name='TmStamp')
BEGIN							
ALTER TABLE PH_TO_ATTRVALS
ADD TmStamp TIMESTAMP 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='ATTRIB_VALS' AND syscolumns.name='TmStamp')
BEGIN							
ALTER TABLE ATTRIB_VALS
ADD TmStamp TIMESTAMP 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='ATTRIBS' AND syscolumns.name='TmStamp')
BEGIN							
ALTER TABLE ATTRIBS
ADD TmStamp TIMESTAMP 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='DDB_FILES' AND TYPE='U')
BEGIN
	CREATE TABLE DDB_FILES(
	id			int identity not null,
	DdbOwner	varchar(128) not null,
	SrcFileName	varchar(512) not null,
	DstFileName	varchar(512) not null,
	Type		int NOT NULL,
	AirTime		bigint NOT NULL,
	ModifyFt	bigint NOT NULL,
	CheckTime	bigint NULL,
	primary key (id))
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

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


--#########################################################################################################
-- ### Adding the 'nc_root' text field into the 'PH' table...

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

DECLARE @ver int
SET @ver  = 2310

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------
-- ### CONTENT
IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PH' AND syscolumns.name='nc_root')
BEGIN 
	ALTER TABLE PH
	ADD nc_root text null 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2320

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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

INSERT INTO TRACT (Version) VALUES(@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  = 2330

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='SJM_LAYOUTS' AND TYPE='U')
BEGIN
	CREATE TABLE SJM_LAYOUTS(
	id		int identity not null,
	Name		varchar(128),
	Ready		int null,
	NcRoot 		text null,
	primary key (id))
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='SJM_GROUPS' AND TYPE='U')
BEGIN
	CREATE TABLE SJM_GROUPS(
	id		int identity not null,
	Name		varchar(128),
	primary key (id))
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='SJM_GROUP_TO_LAYOUT' AND TYPE='U')
BEGIN
	CREATE TABLE SJM_GROUP_TO_LAYOUT(
	id		int identity not null,
	group_id	int not NULL,
	layout_id	int not NULL,
	primary key (id),
	foreign key (group_id)  references SJM_GROUPS (id) on delete cascade,
	foreign key (layout_id) references SJM_LAYOUTS (id) on delete cascade)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='SJM_PAGES' AND TYPE='U')
BEGIN
	CREATE TABLE SJM_PAGES(
	id		int identity not null,
	Name		varchar(128),
	layout_id	int NOT NULL,
	Width		int NOT NULL,
	Height		int NOT NULL,
	Ready		int null,
	BackColor	int null,
	BtnBackColor	int null,
	BtnTextColor	int null,
	NcRoot 		text null,
	primary key (id),
	foreign key (layout_id) references SJM_LAYOUTS (id) on delete cascade) 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='SJM_BUTTONS' AND TYPE='U')
BEGIN
	CREATE TABLE SJM_BUTTONS(
	id		int identity not null,
	Name		varchar(256),
	page_id		int NOT NULL,
	x		int NOT NULL,
	y		int NOT NULL,
	ex		int NOT NULL,
	ey		int NOT NULL,
	Mode		int NULL,
	BackColor	int NULL,
	TextColor	int NULL,
	NcRoot 		text null,
	primary key (id),
	foreign key (page_id) references SJM_PAGES (id) on delete cascade)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='SJM_BTN_TO_PH' AND TYPE='U')
BEGIN
	CREATE TABLE SJM_BTN_TO_PH(
	id		int identity not null,
	ph_id		int not NULL,
	btn_id		int not NULL,
	Mode		int not NULL,
	primary key (id),
	foreign key (ph_id)  references PH (id) on delete cascade,
	foreign key (btn_id) references SJM_BUTTONS (id) on delete cascade)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='SJM_BTN_TO_BTN' AND TYPE='U')
BEGIN
	CREATE TABLE SJM_BTN_TO_BTN(
	id		int identity not null,
	btn_id_1	int NULL,
	btn_id_2	int NULL,
	primary key (id),
	foreign key (btn_id_1) references SJM_BUTTONS (id),
	foreign key (btn_id_2) references SJM_BUTTONS (id))
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2340

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='SJM_BTN_TO_BTN' AND syscolumns.name='Mode')
BEGIN 
	ALTER TABLE SJM_BTN_TO_BTN
	ADD Mode int null 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2350

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='GROUPS' AND syscolumns.name='ad_groups')
BEGIN 
	ALTER TABLE GROUPS
	ADD ad_groups varchar(800) null 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2360

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='SJM_PAGES' AND syscolumns.name='TmStamp')
BEGIN							
ALTER TABLE SJM_PAGES
ADD TmStamp TIMESTAMP 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2365

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PLAY_BLOCKS' AND syscolumns.name='runtime_ms')
BEGIN							
ALTER TABLE PLAY_BLOCKS
ADD runtime_ms int NULL 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2370

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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.mp_positions ADD 
	released_old bit NULL
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2380

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------

UPDATE dbo.mp_positions SET released_old = 1 WHERE released = 1;
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

ALTER TABLE dbo.mp_positions 
    DROP COLUMN released
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END    

ALTER TABLE dbo.mp_positions ADD
	released bit NULL
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2390

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------

UPDATE dbo.mp_positions SET released = 1 WHERE released_old = 1;
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

ALTER TABLE dbo.mp_positions ADD
is_bonus bit NOT NULL CONSTRAINT DF_mp_positions_is_bonus DEFAULT 0
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2400

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 EXISTS (SELECT name FROM sysobjects WHERE name = 'ModifyPH_Arc' AND type = 'TR')
	DROP TRIGGER ModifyPH_Arc
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'OnPhToAttrVals' AND type = 'TR')
	DROP TRIGGER OnPhToAttrVals
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

exec sp_executesql N'CREATE TRIGGER ModifyPH_Arc ON PH AFTER UPDATE AS 
if (substring(columns_updated(),7, 1) & power(2,6)<>0) 
BEGIN 
	INSERT INTO MODIFY_DATES (attrval_id) 
	SELECT PH_TO_ATTRVALS.attrval_id
	FROM INSERTED WITH(NOLOCK) 
	LEFT OUTER JOIN PH_TO_ATTRVALS 
	ON INSERTED.ID=PH_TO_ATTRVALS.ph_id 
	where PH_TO_ATTRVALS.attrval_id is not NULL
END'
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

exec sp_executesql N'CREATE TRIGGER OnPhToAttrVals ON PH_TO_ATTRVALS AFTER INSERT,UPDATE,DELETE AS 
BEGIN 
	INSERT MODIFY_DATES (attrval_id) SELECT attrval_id FROM INSERTED group by attrval_id 
	INSERT MODIFY_DATES (attrval_id) SELECT attrval_id FROM DELETED	 group by attrval_id
	INSERT MODIFY_DATES (ph_id)      SELECT ph_id      FROM INSERTED group by ph_id
	INSERT MODIFY_DATES (ph_id)      SELECT ph_id      FROM DELETED	 group by ph_id
END' 
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2410

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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

INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

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

--#########################################################################################################
-- ### Creating 'ADVANCE_COMM' table...

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

DECLARE @ver int
SET @ver  = 2420

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------
-- ### CONTENT
IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='ADVANCE_COMM' AND TYPE='U')
BEGIN
	CREATE TABLE ADVANCE_COMM(
	id		int identity not null,
	System		int NOT NULL,
	Name		varchar(128)  NOT  NULL,
	Request		varchar(3000) NOT NULL,
	Description	varchar(3000) NULL,
	primary key (id) )
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2430

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 EXISTS(SELECT sysobjects.id FROM sysobjects WHERE sysobjects.name='mp_discount_types' AND TYPE='U')
DROP TABLE mp_discount_types
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2440

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 mp_discount_types
(
	[id] [int] NOT NULL,
	[name] [varchar](100) NOT NULL,
	[apply_method] [smallint] NOT NULL,
 CONSTRAINT PK_MP_DISCUONT_TYPES PRIMARY KEY nonclustered ([id] ASC)
) ON [PRIMARY];
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

INSERT INTO mp_discount_types (id, name, apply_method) VALUES (1, 'Ñêèäêà çà ñâîáîäíîå ðàçìåùåíèå', 0)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

INSERT INTO mp_discount_types (id, name, apply_method) VALUES (2, 'Íàöåíêà çà ïåðâîå ìåñòî', 0)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

INSERT INTO mp_discount_types (id, name, apply_method) VALUES (3, 'Ñêèäêà çà îáúåì', 1)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

INSERT INTO mp_discount_types (id, name, apply_method) VALUES (4, 'Ñåçîííàÿ ñêèäêà', 1)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

INSERT INTO mp_discount_types (id, name, apply_method) VALUES (6, 'Íàöåíêà çà âòîðîå ìåñòî', 0)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

INSERT INTO mp_discount_types (id, name, apply_method) VALUES (7, 'Íàöåíêà çà òðåòüå ìåñòî', 0)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

INSERT INTO mp_discount_types (id, name, apply_method) VALUES (8, 'Íàöåíêà çà ïîñëåäíåå ìåñòî', 0)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

ALTER TABLE mp_discount_values ADD type_id INT;
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

ALTER TABLE mp_discount_values ADD FOREIGN KEY(type_id) REFERENCES mp_discount_types(id);
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2450

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------
-- VOCAL FOR ADVERTISMENT
IF NOT EXISTS ( SELECT ID FROM ATTRIBS WHERE NICK='VOCAL' AND TYPE=1 )
BEGIN
INSERT INTO ATTRIBS (Name,Type,Nick) VALUES ('Vocal',1,'VOCAL')
DECLARE @attrib_id_music int
SELECT @attrib_id_music = ID FROM ATTRIBS WHERE NICK='VOCAL' AND TYPE=1
INSERT INTO ATTRIB_VALS (Name, Type, attr_id )  VALUES ('Male',    1, @attrib_id_music )
INSERT INTO ATTRIB_VALS (Name, Type, attr_id )  VALUES ('Female',  1, @attrib_id_music )
INSERT INTO ATTRIB_VALS (Name, Type, attr_id )  VALUES ('Duet',    1, @attrib_id_music )
INSERT INTO ATTRIB_VALS (Name, Type, attr_id )  VALUES ('Chorus',  1, @attrib_id_music )
INSERT INTO ATTRIB_VALS (Name, Type, attr_id )  VALUES ('Instrumental',  1, @attrib_id_music )
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
--------------------

INSERT INTO TRACT (Version) VALUES(@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  = 2460

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='SJM_LAYOUTS' AND syscolumns.name='TmStamp')
BEGIN							
ALTER TABLE SJM_LAYOUTS
ADD TmStamp timestamp NOT NULL 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2470

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_mediaplans' AND syscolumns.name='payment_way')
BEGIN
ALTER TABLE mp_mediaplans ADD payment_way SMALLINT NULL;
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

ALTER TABLE mp_settings ALTER COLUMN value BINARY(2048);
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2480

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='TEST_NEW_2' AND TYPE='P')
	DROP PROCEDURE TEST_NEW_2
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

exec sp_executesql N'CREATE PROCEDURE TEST_NEW_2 @p_id int AS

declare @max_id int
SELECT @max_id = MAX(ID) FROM Modify_Dates 

if(@p_id >= 0 and @max_id > @p_id + 500)
	set @max_id = @p_id + 500

SELECT 5 as RECID, @max_id 

IF (@p_id >= 0 AND @max_id > @p_id)
BEGIN
SELECT 
	4 as RECID,
	CatID,
	ParentID,
	Pos,
	Deleted,
	ModifyDate 
FROM CAT_JNGL_BTNS
WHERE CAT_JNGL_BTNS.id IN (
	SELECT cat_jngl_btns_id FROM Modify_Dates  
	where cat_jngl_btns_id is not null and id > @p_id and id <= @max_id)
SELECT 	
	1 as RECID,
	ID, 
	Name, 
	Type, 
	ModifyDate, 
	DDB_Owner,
	DDB_ID,
	0 AS NoneCount
FROM ATTRIBS
WHERE ATTRIBS.id IN (
	SELECT attrib_id FROM Modify_Dates  
	where attrib_id is not null and id > @p_id and id <= @max_id)
SELECT
	2 as RECID,
	ATTRIB_VALS.ID,
	ATTRIB_VALS.Name,
	ATTRIB_VALS.Attr_ID,
	ATTRIB_VALS.Type,
	(SELECT COUNT(PH_TO_ATTRVALS.ID) FROM PH_TO_ATTRVALS 
	 LEFT OUTER JOIN PH ON PH.ID=PH_TO_ATTRVALS.Ph_ID 
	 WHERE PH_TO_ATTRVALS.AttrVal_ID=ATTRIB_VALS.ID AND 
			PH.Archive=0 AND PH.Deleted=0) AS NonArcCnt,
	ATTRIB_VALS.ModifyDate, 
	ATTRIB_VALS.FILE_PATH, 
	ATTRIB_VALS.Deleted, 
	ATTRIB_VALS.DDB_Allowed,
	ATTRIB_VALS.DDB_Owner, 
	ATTRIB_VALS.DDB_ID, 
	(SELECT COUNT(PH_TO_ATTRVALS.ID) FROM PH_TO_ATTRVALS 	
	 LEFT OUTER JOIN PH ON PH.ID=PH_TO_ATTRVALS.Ph_ID 
	 WHERE  PH_TO_ATTRVALS.AttrVal_ID=ATTRIB_VALS.ID AND 
			 PH.Archive=1 AND PH.Deleted=0) AS ArcCnt,
	ATTRIB_VALS.Color,
	ATTRIB_VALS.ForNewsBrowser,
	ATTRIB_VALS.SearchDepth,
	(SELECT cast(AVG(cast(PH.SmplSize - PH.ph_start - PH.ph_stop - PH.StartThis - PH.StartNext AS bigint) * 1000 / ph.Freq) as int) 
	 FROM PH_TO_ATTRVALS 
	 LEFT OUTER JOIN PH ON PH_TO_ATTRVALS.Ph_ID=PH.ID 
	 WHERE PH_TO_ATTRVALS.AttrVal_ID=ATTRIB_VALS.ID AND PH.Deleted=0 AND PH.Archive=0  AND PH.SmplSize<>0 AND PH.Freq<>0) AS AvgPhDur
 
	FROM ATTRIB_VALS
	WHERE ATTRIB_VALS.id IN (
		SELECT attrval_id FROM Modify_Dates  
		where attrval_id is not null and id > @p_id and id <= @max_id)
SELECT
	3 as RECID, 
	ph.ID,
	ph.Name, 			
	ph.Type, 			
	ph.Duration, 		
	ph.FileName, 		
	ph.FadeIN, 			
	ph.FadeOUT, 		
	ph.Intro, 			
	ph.Outro, 			
	ph.StartNext, 		
	ph.Ph_Start, 		
	ph.Ph_Stop, 		
	ph.AddDate, 		
	ph.Cmd, 			
	ph.Freq, 			
	ph.SmplSize, 		
	ph.RadioID, 		
	ph.RemoteCmd, 		
	ph.LastPlayTime, 	
	ph.Deleted, 		
	ph.ModifyDate, 		
	ATTRIBS.ID AS ATTRIBS_ID, 				
	ATTRIB_VALS.ID AS ATTRIB_VALS_ID, 		
	ATTRIB_VALS.Name AS ATTRIB_VALS_Name, 	
	ph.EndCode, 			
	ph.FirstReportLine, 	
	ph.LastReportLine, 		
	ph.MaxLvl, 				
	ph.MaxLvlOk, 			
	ph.Gain, 				
	ph.CDId, 				
	ph.CDTrackId, 			
	ph.Album, 				
	ph.ID_Number, 			
	ph.SongYear, 			
	ph.ModifyFT, 			
	ph.RmtList, 			
	ph.Version, 			
	ph.DDB_Owner, 			
	ph.DDB_ID, 				
	ph.CRC32, 				
	ph.LastPlayTime, 		
	ph.Intro2, 				
	ph.Intro3, 				
	ph.Comment, 			
	PH_TO_ATTRVALS.JM_pos AS PH_TO_ATTRVALS_JM_pos, 
	ph.FadeInType, 			
	ph.FadeOutType, 		
	ph.ActivateDate, 		
	ph.Mood, 				
	ph.Energy, 				
	ph.Tempo, 				
	ph.Tempo_End, 			
	ph.Texture, 			
	ph.Texture_End, 		
	ph.StartThis, 			
	ATTRIBS.Name AS ATTRIBS_Name, 		
	ph.NextPlayTime, 		
	ph.UserModifyTime, 		
	ph.Archive, 			
	ph.DayRestrictions, 	
	PH_TO_ATTRVALS.sch_order AS PH_TO_ATTRVALS_sch_order, 
	ph.ReadyOnAir, 			
	ph.Headline, 			
	ph.CasseteStartFPS, 	
	ph.CasseteLengthFPS, 	
	ph.BackupTapeName, 		
	ph.BackupTimeCode, 		
	ph.KeyFrame, 			
	NULL AS KeyIcon, 				
	ph.LcCode,
	ph.Publisher,
	ph.Rds,
	ph.Cmd2,
	ph.TextSizeMs,
	ph.PhonoType,
	ph.OwnerName,
	CAST(PH.TmStamp AS bigint),
	CAST(PH_TO_ATTRVALS.TmStamp AS bigint) AS PH_TO_ATTRVALS_TmStamp,
	CAST(ATTRIB_VALS.TmStamp AS bigint) AS ATTRIB_VALS_TmStamp,
	CAST(ATTRIBS.TmStamp AS bigint) AS ATTRIBS_TmStamp,
	ph.nc_root

FROM PH 
	LEFT OUTER JOIN PH_TO_ATTRVALS on PH_TO_ATTRVALS.ph_id=ph.ID
	LEFT OUTER JOIN ATTRIB_VALS on ATTRIB_VALS.ID=PH_TO_ATTRVALS.AttrVal_ID
	LEFT OUTER JOIN ATTRIBS on ATTRIBS.ID=ATTRIB_VALS.attr_ID
WHERE ph.id IN (
	SELECT ph_id FROM Modify_Dates 
	where ph_id is not null and id > @p_id and id <= @max_id)
ORDER BY ph.id 
END'
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='TEST_NEW_V2' AND TYPE='P')
	DROP PROCEDURE TEST_NEW_V2
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

exec sp_executesql N'CREATE PROCEDURE TEST_NEW_V2 @p_id int AS

declare @max_id int
SELECT @max_id = MAX(ID) FROM Modify_Dates 

if(@p_id >= 0 and @max_id > @p_id + 500)
	set @max_id = @p_id + 500

SELECT 5 as RECID, @max_id 

IF (@p_id >= 0 AND @max_id > @p_id)
BEGIN
SELECT 
	4 as RECID,
	CatID,
	ParentID,
	Pos,
	Deleted,
	ModifyDate 
FROM CAT_JNGL_BTNS
WHERE CAT_JNGL_BTNS.id IN (
	SELECT cat_jngl_btns_id FROM Modify_Dates  
	where cat_jngl_btns_id is not null and id > @p_id and id <= @max_id)
SELECT 	
	1 as RECID,
	ID, 
	Name, 
	Type, 
	ModifyDate, 
	DDB_Owner,
	DDB_ID,
	0 AS NoneCount
FROM ATTRIBS
WHERE ATTRIBS.id IN (
	SELECT attrib_id FROM Modify_Dates  
	where attrib_id is not null and id > @p_id and id <= @max_id)
SELECT
	2 as RECID,
	ATTRIB_VALS.ID,
	ATTRIB_VALS.Name,
	ATTRIB_VALS.Attr_ID,
	ATTRIB_VALS.Type,
	(SELECT COUNT(PH_TO_ATTRVALS.ID) FROM PH_TO_ATTRVALS 
	 LEFT OUTER JOIN PH ON PH.ID=PH_TO_ATTRVALS.Ph_ID 
	 WHERE PH_TO_ATTRVALS.AttrVal_ID=ATTRIB_VALS.ID AND 
			PH.Archive=0 AND PH.Deleted=0) AS NonArcCnt,
	ATTRIB_VALS.ModifyDate, 
	ATTRIB_VALS.FILE_PATH, 
	ATTRIB_VALS.Deleted, 
	ATTRIB_VALS.DDB_Allowed,
	ATTRIB_VALS.DDB_Owner, 
	ATTRIB_VALS.DDB_ID, 
	(SELECT COUNT(PH_TO_ATTRVALS.ID) FROM PH_TO_ATTRVALS 	
	 LEFT OUTER JOIN PH ON PH.ID=PH_TO_ATTRVALS.Ph_ID 
	 WHERE  PH_TO_ATTRVALS.AttrVal_ID=ATTRIB_VALS.ID AND 
			 PH.Archive=1 AND PH.Deleted=0) AS ArcCnt,
	ATTRIB_VALS.Color,
	ATTRIB_VALS.ForNewsBrowser,
	ATTRIB_VALS.SearchDepth,
	(SELECT cast(AVG(cast(PH.SmplSize - PH.ph_start - PH.ph_stop - PH.StartThis - PH.StartNext AS bigint) * 1000 / ph.Freq) as int) 
	 FROM PH_TO_ATTRVALS 
	 LEFT OUTER JOIN PH ON PH_TO_ATTRVALS.Ph_ID=PH.ID 
	 WHERE PH_TO_ATTRVALS.AttrVal_ID=ATTRIB_VALS.ID AND PH.Deleted=0 AND PH.Archive=0  AND PH.SmplSize<>0 AND PH.Freq<>0) AS AvgPhDur
 
	FROM ATTRIB_VALS
	WHERE ATTRIB_VALS.id IN (
		SELECT attrval_id FROM Modify_Dates  
		where attrval_id is not null and id > @p_id and id <= @max_id)
SELECT
	3 as RECID, 
	ph.ID,
	ph.Name, 			
	ph.Type, 			
	ph.Duration, 		
	ph.FileName, 		
	ph.FadeIN, 			
	ph.FadeOUT, 		
	ph.Intro, 			
	ph.Outro, 			
	ph.StartNext, 		
	ph.Ph_Start, 		
	ph.Ph_Stop, 		
	ph.AddDate, 		
	ph.Cmd, 			
	ph.Freq, 			
	ph.SmplSize, 		
	ph.RadioID, 		
	ph.RemoteCmd, 		
	ph.LastPlayTime, 	
	ph.Deleted, 		
	ph.ModifyDate, 		
	ATTRIBS.ID AS ATTRIBS_ID, 				
	ATTRIB_VALS.ID AS ATTRIB_VALS_ID, 		
	ATTRIB_VALS.Name AS ATTRIB_VALS_Name, 	
	ph.EndCode, 			
	ph.FirstReportLine, 	
	ph.LastReportLine, 		
	ph.MaxLvl, 				
	ph.MaxLvlOk, 			
	ph.Gain, 				
	ph.CDId, 				
	ph.CDTrackId, 			
	ph.Album, 				
	ph.ID_Number, 			
	ph.SongYear, 			
	ph.ModifyFT, 			
	ph.RmtList, 			
	ph.Version, 			
	ph.DDB_Owner, 			
	ph.DDB_ID, 				
	ph.CRC32, 				
	ph.LastPlayTime, 		
	ph.Intro2, 				
	ph.Intro3, 				
	ph.Comment, 			
	PH_TO_ATTRVALS.JM_pos AS PH_TO_ATTRVALS_JM_pos, 
	ph.FadeInType, 			
	ph.FadeOutType, 		
	ph.ActivateDate, 		
	ph.Mood, 				
	ph.Energy, 				
	ph.Tempo, 				
	ph.Tempo_End, 			
	ph.Texture, 			
	ph.Texture_End, 		
	ph.StartThis, 			
	ATTRIBS.Name AS ATTRIBS_Name, 		
	ph.NextPlayTime, 		
	ph.UserModifyTime, 		
	ph.Archive, 			
	ph.DayRestrictions, 	
	PH_TO_ATTRVALS.sch_order AS PH_TO_ATTRVALS_sch_order, 
	ph.ReadyOnAir, 			
	ph.Headline, 			
	ph.CasseteStartFPS, 	
	ph.CasseteLengthFPS, 	
	ph.BackupTapeName, 		
	ph.BackupTimeCode, 		
	ph.KeyFrame, 			
	ph.KeyIcon,
	ph.BackupLength,
	ph.ProtyagFirst,
	ph.ProtyagLast,
	ph.LeftRightCh,
	ph.LcCode,
	ph.Publisher,
	ph.Rds,
	ph.Cmd2,
	ph.TextSizeMs,
	ph.PhonoType,
	ph.OwnerName,
	CAST(PH.TmStamp AS bigint),
	CAST(PH_TO_ATTRVALS.TmStamp AS bigint) AS PH_TO_ATTRVALS_TmStamp,
	CAST(ATTRIB_VALS.TmStamp AS bigint) AS ATTRIB_VALS_TmStamp,
	CAST(ATTRIBS.TmStamp AS bigint) AS ATTRIBS_TmStamp,
	ph.nc_root

FROM PH 
	LEFT OUTER JOIN PH_TO_ATTRVALS on PH_TO_ATTRVALS.ph_id=ph.ID
	LEFT OUTER JOIN ATTRIB_VALS on ATTRIB_VALS.ID=PH_TO_ATTRVALS.AttrVal_ID
	LEFT OUTER JOIN ATTRIBS on ATTRIBS.ID=ATTRIB_VALS.attr_ID
WHERE ph.id IN (
	SELECT ph_id FROM Modify_Dates 
	where ph_id is not null and id > @p_id and id <= @max_id)
ORDER BY ph.id 
END'
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END


INSERT INTO TRACT (Version) VALUES(@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  = 2490

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_closed_days' AND TYPE='U')
BEGIN
	CREATE TABLE mp_closed_days(
	id				int identity NOT NULL,
	shedule_id		int NOT NULL,
	closed_day		datetime NOT NULL,
	constraint PK_MP_CLOSED_DAYS primary key nonclustered (id) 
)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='x12_FK' AND sysobjects.name='mp_closed_days'
)
BEGIN
	create index [x12_FK] on mp_closed_days (shedule_id ASC)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

alter table mp_closed_days
   add constraint FK_MP_CLOSED_DAYS_12_SCHEDULE foreign key (shedule_id)
      references schedules (id)
		on delete cascade
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2500

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='locked_objects' AND TYPE='U')
BEGIN
	CREATE TABLE locked_objects(
	id				int identity NOT NULL,
	obj_name		varchar(128) NOT NULL,
	last_update		datetime NOT NULL,
	owner_name		varchar(255) NULL,
	constraint PK_LOCKED_OBJECTS primary key nonclustered (id) 
)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2510

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='PH_PLAY_PLAN' AND syscolumns.name='sch_id')
BEGIN 
	ALTER TABLE PH_PLAY_PLAN
	ADD sch_id int null 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='MAG_CAT_SCH' AND TYPE='U')
BEGIN
	CREATE TABLE MAG_CAT_SCH(
	id					int identity not null,
	cat_id				int NOT NULL,
	sch_id				int NOT NULL,
	plot_number			int NOT NULL,
	nc_root 			text null,
	search_depth		int NULL,
	search_depth_units	int NOT NULL default 0,	-- 0=items, 1=percent
	stamp				timestamp not null,
	
	primary key (id),
	foreign key (cat_id) references ATTRIB_VALS (id) on delete cascade,
	foreign key (sch_id) references SCHEDULES (id) on delete cascade) 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='MAG2_WORK' AND TYPE='U')
BEGIN
	CREATE TABLE MAG2_WORK(
	WorkDate	datetime not null,
	sch_id		int not null,
	ph_id		int not null,
	sch_order 	int null,
	attrval_id	int not null
	) 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='MAG_SCH_PROP' AND TYPE='U')
BEGIN
	CREATE TABLE MAG_SCH_PROP(
	id			int identity not null,
	sch_id		int not null,
	nc_root 	text null,
	stamp		timestamp not null,
	primary key (id),
	foreign key (sch_id) references SCHEDULES (id) on delete cascade) 
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2520

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 locked_objects add constraint UK_OBJ_NAME UNIQUE nonclustered (obj_name)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2530

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_step_tariffing_values' AND TYPE='U')
BEGIN
	CREATE TABLE mp_step_tariffing_values(
	id					int identity NOT NULL,
	tariff_scales_id	int  NOT NULL,
	min_spot_duration   smallint,
	max_spot_duration	smallint,
	ratio				decimal(18, 3)
	constraint PK_MP_STEP_TARIFFING_VALUES primary key nonclustered (id) 
)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

ALTER TABLE mp_step_tariffing_values
   add constraint FK_MP_STEP_TARIFFING_VALUES_14_MP_TARIFF_SCALES  foreign key (tariff_scales_id)
      references mp_tariff_scales (id)
		on delete cascade
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2540

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_covenantees' AND syscolumns.name='agency')
BEGIN
ALTER TABLE mp_covenantees ADD agency varchar(255);
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2550

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_reports' AND syscolumns.name='name')
BEGIN
ALTER TABLE mp_reports DROP COLUMN name
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

if EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_reports' AND syscolumns.name='application')
BEGIN
ALTER TABLE mp_reports DROP COLUMN application
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_reports' AND syscolumns.name='type')
BEGIN
ALTER TABLE mp_reports ADD type int;
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_reports' AND syscolumns.name='app_type')
BEGIN
ALTER TABLE mp_reports ADD app_type int;
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_reports' AND syscolumns.name='is_used_app')
BEGIN
ALTER TABLE mp_reports ADD is_used_app bit default 0 not null;
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2560

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_mediaplans' AND syscolumns.name='reserv_term')
BEGIN
ALTER TABLE mp_mediaplans ADD reserv_term int NULL;
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_mediaplans' AND syscolumns.name='reserv_from')
BEGIN
ALTER TABLE mp_mediaplans ADD reserv_from datetime NULL;
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2570

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='rights' AND syscolumns.name='resource_id')
BEGIN
ALTER TABLE rights ADD resource_id varchar(1024) NULL;
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='rights' AND syscolumns.name='permission')
BEGIN
ALTER TABLE rights ADD permission int default 1 NOT NULL;
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

if NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='rights' AND syscolumns.name='resource_type')
BEGIN
ALTER TABLE rights ADD resource_type varchar(1024) NULL;
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END



------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2580

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 ph alter column Rds VARCHAR(512) NULL
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2590

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_mediaplans' AND syscolumns.name='prev_status')
BEGIN
ALTER TABLE mp_mediaplans ADD prev_status smallint NULL;
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

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

--#########################################################################################################
-- CREATE NETWORKS

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

DECLARE @ver int
SET @ver  = 2600

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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

------------------
declare @key_name nvarchar(256)
set @key_name=null
SELECT @key_name=name FROM sys.foreign_keys where name like 'FK__GRIDS__schedule___%'

IF @key_name is not null 
BEGIN
	ALTER TABLE GRIDS ALTER COLUMN schedule_id int null	
	set @key_name = 'ALTER TABLE GRIDS DROP CONSTRAINT [' + @key_name + ']' 
	exec sp_executesql @key_name
END

set @key_name=null
SELECT @key_name=name FROM sys.foreign_keys where name like 'FK__GRID_CLOC__sch_i__%'

IF @key_name is not null 
BEGIN
	ALTER TABLE GRID_CLOCKS ALTER COLUMN sch_id int null	
	set @key_name = 'ALTER TABLE GRID_CLOCKS DROP CONSTRAINT [' + @key_name + ']' 
	exec sp_executesql @key_name
END

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='NETWORKS' AND TYPE='U')
BEGIN
	CREATE TABLE NETWORKS(
	id			int identity not null,
	name		varchar(64) NOT NULL,
	primary key (id), unique (name))
END

IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='SCHEDULES' AND syscolumns.name='network_id')
BEGIN 
	ALTER TABLE SCHEDULES
	ADD network_id int null
END

IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='SCHEDULES' AND syscolumns.name='rotate_music')
BEGIN 
	ALTER TABLE SCHEDULES
	ADD rotate_music int null
END

IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='GRIDS' AND syscolumns.name='network_id')
BEGIN 
	ALTER TABLE GRIDS
	ADD network_id int null
END

IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='GRID_CLOCKS' AND syscolumns.name='network_id')
BEGIN 
	ALTER TABLE GRID_CLOCKS
	ADD network_id int null
END

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

INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

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

--#########################################################################################################
--UPDATE NETWORKs

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

DECLARE @ver int
SET @ver  = 2610

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS (SELECT * FROM NETWORKS)
BEGIN
	insert into NETWORKS select name from schedules where schedules.id in (select schedule_id from grids)
	update SCHEDULES set network_id = (select id from NETWORKS where NETWORKS.name=SCHEDULES.name)
	update SCHEDULES set rotate_music=1 where network_id is not null
	update GRIDS set network_id = (select network_id from SCHEDULES where schedules.id=GRIDS.schedule_id)
	update GRID_CLOCKS set network_id = (select network_id from SCHEDULES where schedules.id=GRID_CLOCKS.sch_id)
END

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID('FK_SCHEDULES_NETWORK_ID_NETWORKS_ID') AND parent_object_id = OBJECT_ID('SCHEDULES'))
	ALTER TABLE SCHEDULES  WITH CHECK ADD CONSTRAINT FK_SCHEDULES_NETWORK_ID_NETWORKS_ID FOREIGN KEY ([network_id])
	REFERENCES NETWORKS([id])
	ON DELETE SET NULL

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID('FK_GRIDS_NETWORK_ID_NETWORKS_ID') AND parent_object_id = OBJECT_ID('GRIDS'))
	ALTER TABLE GRIDS WITH CHECK ADD CONSTRAINT FK_GRIDS_NETWORK_ID_NETWORKS_ID FOREIGN KEY ([network_id])
	REFERENCES NETWORKS([id])
	ON DELETE CASCADE

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID('FK_GRID_CLOCKS_NETWORK_ID_NETWORKS_ID') AND parent_object_id = OBJECT_ID('GRID_CLOCKS'))
	ALTER TABLE GRID_CLOCKS WITH CHECK ADD CONSTRAINT FK_GRID_CLOCKS_NETWORK_ID_NETWORKS_ID FOREIGN KEY ([network_id])
	REFERENCES NETWORKS([id])
	ON DELETE SET NULL

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID('FK_GRID_CLOCKS_SCH_ID_SCHEDULES_ID') AND parent_object_id = OBJECT_ID('GRID_CLOCKS'))
	ALTER TABLE GRID_CLOCKS WITH CHECK ADD CONSTRAINT FK_GRID_CLOCKS_SCH_ID_SCHEDULES_ID FOREIGN KEY ([sch_id])
	REFERENCES SCHEDULES([id])
	ON DELETE SET NULL

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID('FK_GRIDS_SCHEDULE_ID_SCHEDULES_ID') AND parent_object_id = OBJECT_ID('GRIDS'))
	ALTER TABLE GRIDS WITH CHECK ADD CONSTRAINT FK_GRIDS_SCHEDULE_ID_SCHEDULES_ID FOREIGN KEY ([schedule_id])
	REFERENCES SCHEDULES([id])
	ON DELETE SET NULL

------------------
INSERT INTO TRACT (Version) VALUES(@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 = 2620

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------

-- change mp_mediaplans table

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID('[FK_MP_MEDIA_4_SCHEDULE]') AND parent_object_id = OBJECT_ID('mp_mediaplans'))
BEGIN
	ALTER TABLE mp_mediaplans DROP CONSTRAINT [FK_MP_MEDIA_4_SCHEDULE]
END

-- change mp_closed_days table

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID('[FK_MP_CLOSED_DAYS_12_SCHEDULE]') AND parent_object_id = OBJECT_ID('mp_closed_days'))
BEGIN
	ALTER TABLE mp_closed_days DROP CONSTRAINT [FK_MP_CLOSED_DAYS_12_SCHEDULE]
END

-- change mp_tariff_scales table

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID('[FK_MP_TARIF_7_SCHEDULE]') AND parent_object_id = OBJECT_ID('mp_tariff_scales'))
BEGIN
	ALTER TABLE mp_tariff_scales DROP CONSTRAINT [FK_MP_TARIF_7_SCHEDULE]
END

------------------
INSERT INTO TRACT (Version) VALUES(@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 = 2630

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------

-- change mp_mediaplans table

IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_mediaplans' AND syscolumns.name='type')
BEGIN 
	ALTER TABLE mp_mediaplans ADD type int default 0 not null
END

IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_mediaplans' AND syscolumns.name='network_id')
BEGIN 
	ALTER TABLE mp_mediaplans ADD network_id int
END

IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_mediaplans' AND syscolumns.name='preset_data')
BEGIN 
	ALTER TABLE mp_mediaplans ADD preset_data binary(2048) null
END

-- change mp_closed_days table

IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_closed_days' AND syscolumns.name='network_id')
BEGIN 
	ALTER TABLE mp_closed_days ADD network_id int
END

-- update mp_phonograms table

IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_phonograms' AND syscolumns.name='parent_id')
BEGIN 
	ALTER TABLE mp_phonograms ADD parent_id int null
END

-- create mp_replacement table

IF NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_replacement' AND TYPE='U')
BEGIN
	CREATE TABLE mp_replacement(
	id			int identity not null,
	schedule_id	int not null,
	spot_id	int not null,
	new_spot_id	int not null,
	used	bit not null,
	primary key (id)
	)
END

-- change mp_tariff_scales table

IF NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_tariff_scales' AND syscolumns.name='network_id')
BEGIN 
	ALTER TABLE mp_tariff_scales ADD network_id int
END

------------------
INSERT INTO TRACT (Version) VALUES(@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 = 2640

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_mediaplans' AND syscolumns.name='network_id')
BEGIN 
	update mp_mediaplans set network_id = (select network_id from SCHEDULES where schedules.id = mp_mediaplans.schedules_id)
END

IF EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_closed_days' AND syscolumns.name='shedule_id')
BEGIN 
	update mp_closed_days set network_id = (select network_id from SCHEDULES where schedules.id = mp_closed_days.shedule_id)
END

IF EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_tariff_scales' AND syscolumns.name='schedules_id')
BEGIN 
	update mp_tariff_scales set network_id = (select network_id from SCHEDULES where schedules.id = mp_tariff_scales.schedules_id)
END

IF EXISTS (
	select sysindexes.id from sysindexes 
	left outer join sysindexkeys on sysindexkeys.id = sysindexes.id   AND sysindexkeys.indid = sysindexes.indid
	left outer join syscolumns   on syscolumns.id   = sysindexkeys.id AND syscolumns.colid   = sysindexkeys.colid
	left outer join sysobjects   on sysobjects.id   = syscolumns.id
	where sysindexes.name='x12_FK' AND sysobjects.name='mp_closed_days'
)
BEGIN
	drop index mp_closed_days.x12_FK
END

/*
IF EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_closed_days' AND syscolumns.name='shedule_id')
BEGIN 
	ALTER TABLE mp_closed_days DROP COLUMN shedule_id
END

IF EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_tariff_scales' AND syscolumns.name='shedules_id')
BEGIN 
	ALTER TABLE mp_tariff_scales DROP COLUMN shedules_id
END
*/

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2650

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------

-- change mp_mediaplans table

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID('FK_MP_MEDIA_4_NETWORK') AND parent_object_id = OBJECT_ID('mp_mediaplans'))
	ALTER TABLE mp_mediaplans  WITH CHECK ADD CONSTRAINT FK_MP_MEDIA_4_NETWORK FOREIGN KEY ([network_id])
	REFERENCES NETWORKS([id])
	ON DELETE SET NULL

-- change mp_closed_days table

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID('[FK_MP_CLOSED_DAYS_12_NETWORK]') AND parent_object_id = OBJECT_ID('mp_closed_days'))
	ALTER TABLE mp_closed_days  WITH CHECK ADD CONSTRAINT [FK_MP_CLOSED_DAYS_12_NETWORK] FOREIGN KEY ([network_id])
	REFERENCES NETWORKS([id])
	ON DELETE SET NULL

-- change mp_tariff_scales table

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID('[FK_MP_TARIF_7_NETWORK]') AND parent_object_id = OBJECT_ID('mp_tariff_scales'))
	ALTER TABLE mp_tariff_scales  WITH CHECK ADD CONSTRAINT [FK_MP_TARIF_7_NETWORK] FOREIGN KEY ([network_id])
	REFERENCES NETWORKS([id])
	ON DELETE SET NULL

-- change mp_replacement table

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID('[FK_MP_REPLACEMENT_SCHEDULES]') AND parent_object_id = OBJECT_ID('mp_replacement'))
	ALTER TABLE mp_replacement  WITH CHECK ADD CONSTRAINT [FK_MP_REPLACEMENT_SCHEDULES] FOREIGN KEY ([schedule_id])
	REFERENCES SCHEDULES([id])
	ON DELETE CASCADE

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID('[FK_MP_REPLACEMENT_MP_PHONOGRAMS_1]') AND parent_object_id = OBJECT_ID('mp_replacement'))
	ALTER TABLE mp_replacement  WITH CHECK ADD CONSTRAINT [FK_MP_REPLACEMENT_MP_PHONOGRAMS_1] FOREIGN KEY (spot_id)
	REFERENCES mp_phonograms([id])
	ON DELETE CASCADE

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID('[FK_MP_REPLACEMENT_MP_PHONOGRAMS_2]') AND parent_object_id = OBJECT_ID('mp_replacement'))
	ALTER TABLE mp_replacement  WITH CHECK ADD CONSTRAINT [FK_MP_REPLACEMENT_MP_PHONOGRAMS_2] FOREIGN KEY (new_spot_id)
	REFERENCES mp_phonograms([id])

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2660

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 mp_settings ALTER COLUMN value BINARY(4000);
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2670

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 mp_mediaplans ALTER COLUMN preset_data BINARY(4000);
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2680

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_base_costs' AND syscolumns.name='tariff_type')
BEGIN 
	ALTER TABLE mp_base_costs ADD tariff_type smallint
	if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
END

------------------
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

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


DECLARE @ver int
SET @ver  = 2690

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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
------------------

update mp_base_costs set tariff_type = (select type from mp_tariff_scales where mp_tariff_scales.id = mp_base_costs.tariff_scales_id)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2700

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_tariff_scales' AND syscolumns.name='exchange' AND syscolumns.xtype=52)
BEGIN 
	alter table mp_tariff_scales alter column exchange int NOT NULL
	if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2710

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_reports' AND syscolumns.name='name')
BEGIN 
	alter table mp_reports add name varchar(256) NULL
	if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
END

------------------
INSERT INTO TRACT (Version) VALUES(@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  = 2720

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_payment_types' AND TYPE='U')
BEGIN
	CREATE TABLE mp_payment_types(
	id		smallint identity not null,
	Name	varchar(256),
	primary key (id))
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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 = 2730

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT sysobjects.id FROM sysobjects
	WHERE sysobjects.name='mp_position_replacement' AND TYPE='U')
BEGIN
	create table mp_position_replacement (
	   id                   int                  identity,
	   position_id			int                  not null,
	   schedules_id         int                  not null,
	   release_date         datetime             null,
	   placementMethod		int					 not null,
	   released             bit                  null,
	   is_bonus             bit                  not null,
	   constraint PK_MP_POSITION_REPLACEMENT primary key nonclustered (id)
	)
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

alter table mp_position_replacement
   add constraint FK_MP_POSIT_REPL_MP_POSIT foreign key (position_id)
      references mp_positions (id)
         on delete cascade
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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 = 2740

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 EXISTS(	SELECT sysobjects.id FROM sysobjects
			WHERE sysobjects.name='mp_position_replacement' AND TYPE='U')
BEGIN
		INSERT INTO mp_position_replacement (position_id, schedules_id, release_date, placementMethod, released, is_bonus)
		SELECT mpos.id AS position_id, mplan.schedules_id, mpos.release_date, mpos.placementMethod, mpos.released, mpos.is_bonus
		FROM mp_positions AS mpos INNER JOIN mp_mediaplans AS mplan ON mpos.mediaplans_id = mplan.id
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END


------------------
INSERT INTO TRACT (Version) VALUES(@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 = 2750

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 EXISTS(	SELECT sysobjects.id FROM sysobjects
			WHERE sysobjects.name='mp_tariff_scales' AND TYPE='U')
BEGIN
	delete from mp_tariff_scales where network_id=NULL
	if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
END
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

------------------
INSERT INTO TRACT (Version) VALUES(@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 = 2751

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_mediaplans' AND syscolumns.name='description')
BEGIN 
	ALTER TABLE mp_mediaplans ADD description VARCHAR(512) null
END

------------------
INSERT INTO TRACT (Version) VALUES(@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 = 2752

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='mp_mediaplans' AND syscolumns.name='settings')
BEGIN 
	ALTER TABLE mp_mediaplans ADD settings image null
END

------------------
INSERT INTO TRACT (Version) VALUES(@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 = 2753

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS(
	SELECT syscolumns.name FROM syscolumns 
	LEFT OUTER JOIN sysobjects ON syscolumns.id=sysobjects.id 
	WHERE sysobjects.name='SCHEDULES' AND syscolumns.name='user_name')
BEGIN 
	ALTER TABLE SCHEDULES
	ADD user_name varchar(256) null
END

------------------
INSERT INTO TRACT (Version) VALUES(@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 = 2800

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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].[components](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](128) NOT NULL,
	[vers] [varchar](128) NULL,
	[veri] [int] NULL
) 

------------------
INSERT INTO TRACT (Version) VALUES(@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 = 2801

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

IF EXISTS( SELECT Version FROM TRACT WHERE Version>=@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 NOT EXISTS (SELECT * FROM sys.key_constraints WHERE object_id = OBJECT_ID('[P_BLOCKS_UNIQUE_BlkDate_BlkName]') AND parent_object_id = OBJECT_ID('[P_BLOCKS]'))
BEGIN
	delete from p_blocks where id not in (select min(id) from p_blocks group by SchName, BlkDate, BlkName)
	ALTER TABLE p_blocks ADD CONSTRAINT [P_BLOCKS_UNIQUE_BlkDate_BlkName] UNIQUE (SchName, BlkDate,BlkName)
END
------------------
INSERT INTO TRACT (Version) VALUES(@ver)
if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END

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




-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- begin step: AddDate column for components with default to current datetime
if(@@ERROR<>0) BEGIN RAISERROR('Script terminated...',16,1) return END
DECLARE @comp_ver int
DECLARE @comp_name varchar(128)
SET @comp_ver = 1 
SET @comp_name = 'Components.AddDate column'

IF EXISTS( SELECT * FROM components WHERE name = @comp_name and veri >= @comp_ver) 
    BEGIN print 'Component: '+ @comp_name + ' already updated to version '  +   CAST(@comp_ver as varchar(128))+ ' or more' return END
    
SET XACT_ABORT ON
BEGIN TRAN    
-- ++++++  begin payload  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	ALTER TABLE components ADD AddDate DateTime NULL default getdate()
-- ------  end payload  --------------------------------------------------------------------------------------
INSERT INTO components (name, veri) VALUES(@comp_name,@comp_ver) if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print '**** Component:   ' + @comp_name + '   v: ' + CAST(@comp_ver as varchar(128)) + '    OK'
GO
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<






-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- begin step: LastPlayTime became nullable and no default value
if(@@ERROR<>0) BEGIN RAISERROR('Script terminated...',16,1) return END
DECLARE @comp_ver int
DECLARE @comp_name varchar(128)
SET @comp_ver = 1 
SET @comp_name = 'Nullable PH.LastPlayTime'

IF EXISTS( SELECT * FROM components WHERE name = @comp_name and veri >= @comp_ver) 
    BEGIN print 'Component: '+ @comp_name + ' already updated to version '  +   CAST(@comp_ver as varchar(128))+ ' or more' return END
    
SET XACT_ABORT ON
BEGIN TRAN    
-- ++++++  begin payload  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	ALTER TABLE ph ALTER COLUMN LastPlayTime DateTime NULL

	IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__PH__LastPlayTime__09DE7BCC]') AND type = 'D')
	BEGIN
		ALTER TABLE [dbo].[PH] DROP CONSTRAINT [DF__PH__LastPlayTime__09DE7BCC]
	END
-- ------  end payload  --------------------------------------------------------------------------------------
INSERT INTO components (name, veri) VALUES(@comp_name,@comp_ver) if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print '**** Component:   ' + @comp_name + '   v: ' + CAST(@comp_ver as varchar(128)) + '    OK'
GO
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<




-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- begin step: PH.ModifyPH trigger should ignore LastPlayTime field
-- In version 1 of this step this behaviour was removed. Now we bring it back #25712
if(@@ERROR<>0) BEGIN RAISERROR('Script terminated...',16,1) return END
DECLARE @comp_ver int
DECLARE @comp_name varchar(128)
SET @comp_ver = 2 
SET @comp_name = 'PH.Triggers.ModifyPH'

IF EXISTS( SELECT * FROM components WHERE name = @comp_name and veri >= @comp_ver) 
    BEGIN print 'Component: '+ @comp_name + ' already updated to version '  +   CAST(@comp_ver as varchar(128))+ ' or more' return END
    
SET XACT_ABORT ON
BEGIN TRAN
-- ++++++  begin payload  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    exec sp_executesql N'
	ALTER TRIGGER ModifyPH ON PH AFTER UPDATE AS 
	if (substring(columns_updated(),3,1)&power(2,3)=0 AND
		substring(columns_updated(),3,1)&power(2,2)=0) 
	BEGIN
		UPDATE PH SET ModifyDate=CURRENT_TIMESTAMP WHERE ID IN (SELECT ID FROM INSERTED)
		INSERT INTO MODIFY_DATES (ph_id) SELECT id FROM INSERTED
	END'
-- ------  end payload  --------------------------------------------------------------------------------------
INSERT INTO components (name, veri) VALUES(@comp_name,@comp_ver) if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print '**** Component:   ' + @comp_name + '   v: ' + CAST(@comp_ver as varchar(128)) + '    OK'
GO
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<



-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- MdbClean automatic

--=============================================================================
-- RunBackgroundTask procedure 
--=============================================================================
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- begin step: PH.ModifyPH trigger does not ignore LastPlayTime field
if(@@ERROR<>0) BEGIN RAISERROR('Script terminated...',16,1) return END
DECLARE @comp_ver int
DECLARE @comp_name varchar(128)
SET @comp_ver = 1 
SET @comp_name = 'SP.RunBackgroundTask'

IF EXISTS( SELECT * FROM components WHERE name = @comp_name and veri >= @comp_ver) 
    BEGIN print 'Component: '+ @comp_name + ' already updated to version '  +   CAST(@comp_ver as varchar(128))+ ' or more' return END
    
SET XACT_ABORT ON
BEGIN TRAN
-- ++++++  begin payload  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    
	IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'RunBackgroundTask')
		exec('CREATE PROCEDURE [dbo].[RunBackgroundTask] AS BEGIN SET NOCOUNT ON; END')
	
	exec sp_executesql N'
	ALTER PROCEDURE [dbo].[RunBackgroundTask]
	@proc_name nvarchar(64)
	AS
	BEGIN
		SET NOCOUNT ON
		declare @run_interval int
		declare @result integer
		declare @DateToRun datetime

		set @result = 0 --Äëÿ 2005 server

		BEGIN
			SELECT @run_interval = CAST(Value as int) FROM PARAMETERS WHERE Name = @proc_name + ''.Interval''
			SELECT @DateToRun = DATEADD(HOUR,@run_interval,MAX(LastExec)) FROM BackgroundTaskHistory WHERE ProcName = @proc_name
			IF (@run_interval IS NOT NULL AND (@DateToRun IS NULL OR GETUTCDATE() >= @DateToRun))
			BEGIN
				PRINT ''RunBackgroundTask: '' + DB_NAME() + ''.'' + @proc_name
				BEGIN TRY
					IF(@DateToRun IS NULL) 
						set @DateToRun = GETDATE()

					INSERT BackgroundTaskHistory (ProcName, LastExec) VALUES (@proc_name, @DateToRun)
				 
					exec @proc_name
				END TRY
				BEGIN CATCH
					SET @result = ERROR_NUMBER()
					print ERROR_MESSAGE()
				END CATCH

				UPDATE BackgroundTaskHistory SET Result = @result WHERE LastExec = @DateToRun AND ProcName = @proc_name
			END
		END
	END
	'
-- ------  end payload  --------------------------------------------------------------------------------------
INSERT INTO components (name, veri) VALUES(@comp_name,@comp_ver) if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print '**** Component:   ' + @comp_name + '   v: ' + CAST(@comp_ver as varchar(128)) + '    OK'
GO
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<

--=============================================================================
-- MdbBackgroundTask procedure 
--=============================================================================
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- begin step: PH.ModifyPH trigger does not ignore LastPlayTime field
if(@@ERROR<>0) BEGIN RAISERROR('Script terminated...',16,1) return END
DECLARE @comp_ver int
DECLARE @comp_name varchar(128)
SET @comp_ver = 1 
SET @comp_name = 'SP.MdbBackgroundTask'

IF EXISTS( SELECT * FROM components WHERE name = @comp_name and veri >= @comp_ver) 
    BEGIN print 'Component: '+ @comp_name + ' already updated to version '  +   CAST(@comp_ver as varchar(128))+ ' or more' return END
    
SET XACT_ABORT ON
BEGIN TRAN
-- ++++++  begin payload  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	IF NOT EXISTS(SELECT sysobjects.id FROM sysobjects WHERE sysobjects.name='BackgroundTaskHistory' AND TYPE='U')
		BEGIN
			CREATE TABLE BackgroundTaskHistory  (
				ProcName nvarchar(64) NOT NULL,
				LastExec datetime NOT NULL,
				Result integer
			)
		END
		
	IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'MdbBackgroundTask')
		exec('CREATE PROCEDURE [dbo].[MdbBackgroundTask] AS BEGIN SET NOCOUNT ON; END')
		
	IF NOT EXISTS (SELECT * FROM BackgroundTaskHistory WHERE ProcName = 'MdbClean')
		INSERT INTO BackgroundTaskHistory (ProcName, LastExec) VALUES ('MdbClean', DATEADD(DAY , -DATEPART(WEEKDAY,GETDATE()), DATEADD(DAY, DATEDIFF(DAY, '', GETDATE()),'03:00:00')))
	
	exec sp_executesql N'
	ALTER PROCEDURE [dbo].[MdbBackgroundTask] AS
	BEGIN
		SET NOCOUNT ON

		PRINT DB_NAME() + ''.MdbBackgroundTask started''
	
		DECLARE @sp_name varchar(max)
		DECLARE MY_CURSOR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
		FOR 
			SELECT DISTINCT PARSENAME (Name,2) FROM PARAMETERS WHERE (Name Like ''%.Interval'')

			OPEN MY_CURSOR
			FETCH NEXT FROM MY_CURSOR INTO @sp_name
				WHILE @@FETCH_STATUS = 0
					BEGIN 
						PRINT ''Start RunBackgroundTask '' + @sp_name
						EXEC RunBackgroundTask @sp_name
						PRINT ''Finish RunBackgroundTask '' + @sp_name
						FETCH NEXT FROM MY_CURSOR INTO @sp_name
					END
			CLOSE MY_CURSOR

		DEALLOCATE MY_CURSOR

		PRINT DB_NAME() + ''.MdbBackgroundTask complete''

	END
	'
-- ------  end payload  --------------------------------------------------------------------------------------
INSERT INTO components (name, veri) VALUES(@comp_name,@comp_ver) if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print '**** Component:   ' + @comp_name + '   v: ' + CAST(@comp_ver as varchar(128)) + '    OK'
GO

-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<

--=============================================================================
-- MdbClean procedure 
--=============================================================================
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- begin step: PH.ModifyPH trigger does not ignore LastPlayTime field
if(@@ERROR<>0) BEGIN RAISERROR('Script terminated...',16,1) return END
DECLARE @comp_ver int
DECLARE @comp_name varchar(128)
SET @comp_ver = 1 
SET @comp_name = 'SP.MdbClean'

IF EXISTS( SELECT * FROM components WHERE name = @comp_name and veri >= @comp_ver) 
    BEGIN print 'Component: '+ @comp_name + ' already updated to version '  +   CAST(@comp_ver as varchar(128))+ ' or more' return END
    
SET XACT_ABORT ON
BEGIN TRAN
-- ++++++  begin payload  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'MdbClean')
		exec('CREATE PROCEDURE [dbo].[MdbClean] AS BEGIN SET NOCOUNT ON; END')
	
	exec sp_executesql N'
	ALTER PROCEDURE [dbo].[MdbClean] AS
	BEGIN
		begin transaction

		DECLARE @id_to_clean table(id int NOT NULL);                                        

		insert into @id_to_clean
		select ph.id
		from attrib_vals
				left outer join ph_to_attrvals     on attrib_vals.id = ph_to_attrvals.attrval_id
				left outer join ph         on ph.id = ph_to_attrvals.ph_id
			where 
				attrib_vals.deleted=0 and attrib_vals.type = 5
				and attrib_vals.attr_id=1 and attrib_vals.ForNewsBrowser=1
				and ph.deleted=0 and ph.AddDate<(dateadd( day, -14, CURRENT_TIMESTAMP))

		ALTER TABLE [PH_TO_ATTRVALS] DISABLE TRIGGER OnPhToAttrVals
		ALTER TABLE [ph] DISABLE TRIGGER ModifyPH

		/* Clear all attribute links for deleted items (#3730)*/
		DELETE FROM [PH_TO_ATTRVALS] WHERE Ph_ID IN (select * from @id_to_clean)
		update ph set deleted=1, modifydate=CURRENT_TIMESTAMP where id in(select * from @id_to_clean)

		ALTER TABLE [PH_TO_ATTRVALS] ENABLE TRIGGER OnPhToAttrVals
		ALTER TABLE [ph] ENABLE TRIGGER ModifyPH

		commit transaction

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

		/*Delete lost attrib links*/
		print ''Delete lost attrib links...''

		begin transaction

		ALTER TABLE ph_to_attrvals DISABLE TRIGGER OnPhToAttrVals

		delete from ph_to_attrvals where id in 
		 (select  distinct ph_to_attrvals.id from ph_to_attrvals, attrib_vals 
		  where 
		   attrib_vals.id = ph_to_attrvals.attrval_id and 
		   attrib_vals.deleted = 1)

		delete from ph_to_attrvals where id in 
		 (select  distinct ph_to_attrvals.id from ph_to_attrvals, ph 
		  where 
		   ph.id = ph_to_attrvals.ph_id and 
		   ph.deleted = 1)

		ALTER TABLE ph_to_attrvals ENABLE TRIGGER OnPhToAttrVals

		commit transaction

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

		/*Clear recycle bin*/
		print ''DELETE FROM PH...''

		begin transaction

		ALTER TABLE ph_to_attrvals DISABLE TRIGGER OnPhToAttrVals

		DELETE FROM PH where deleted=1 AND modifydate<(dateadd( day, -7, CURRENT_TIMESTAMP)) AND 0 = (select count(*) from mp_phonograms where ph_id=PH.id)

		ALTER TABLE ph_to_attrvals ENABLE TRIGGER OnPhToAttrVals

		commit transaction

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

		/*Clear recycle bin*/
		print ''DELETE FROM ATTRIB_VALS...''
		DELETE FROM ATTRIB_VALS where deleted=1 AND modifydate<(dateadd( day, -1, CURRENT_TIMESTAMP))

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

		/*Clear recycle bin*/
		print ''DELETE FROM CAT_JNGL_BTNS...''
		DELETE FROM CAT_JNGL_BTNS where deleted=1 AND modifydate<(dateadd( day, -1, CURRENT_TIMESTAMP))
		if(@@ERROR<>0) BEGIN RAISERROR(''Script terminated...'',16,1) return END

		/*Clear Old Scheule elems*/
		print ''DELETE FROM PLIST_ELEMS...''
		DELETE FROM PLIST_ELEMS where not exists(select id from PLIST_BLOCKS where id = PLIST_ELEMS.BlockID)
		if(@@ERROR<>0) BEGIN RAISERROR(''Script terminated...'',16,1) return END

		/*Clear Old Scheule elems*/
		print ''DELETE FROM PLIST_ELEMS...''
		DELETE FROM PLIST_ELEMS 
		where BlockID in(select ID FROM PLIST_BLOCKS where [date] < (dateadd( day, -3, CURRENT_TIMESTAMP))) 
		if(@@ERROR<>0) BEGIN RAISERROR(''Script terminated...'',16,1) return END

		/*Clear Old Scheule Blocks*/
		print ''DELETE FROM PLIST_BLOCKS...''
		DELETE FROM PLIST_BLOCKS where [date] < (dateadd( day, -3, CURRENT_TIMESTAMP))
		if(@@ERROR<>0) BEGIN RAISERROR(''Script terminated...'',16,1) return END

		/*Clear Old Changes*/
		print ''DELETE FROM MODIFY_DATES...''
		DELETE FROM MODIFY_DATES where modifydate< (dateadd( day, -1, CURRENT_TIMESTAMP))
		if(@@ERROR<>0) BEGIN RAISERROR(''Script terminated...'',16,1) return END

		/*Clear sch_files*/
		print ''DELETE FROM SCH_FILES...''
		DELETE FROM SCH_FILES where SchDate < (dateadd( day, -3, CURRENT_TIMESTAMP))
		if(@@ERROR<>0) BEGIN RAISERROR(''Script terminated...'',16,1) return END

		/*Delete old DDB blocks info*/
		print ''Delete old DDB blocks info...''
		DELETE FROM P_BLOCKS where BlkDate < (dateadd( day, -3, CURRENT_TIMESTAMP)) 
		if(@@ERROR<>0) BEGIN RAISERROR(''Script terminated...'',16,1) return END

		/*Delete playback history older than 100 days*/
		print ''Delete playback history older than 100 days...''
		DELETE FROM PH_PLAY_HISTORY where PlayTime<(dateadd( day, -100, CURRENT_TIMESTAMP)) 
		if(@@ERROR<>0) BEGIN RAISERROR(''Script terminated...'',16,1) return END

		/*Delete plan history older than 100 days*/
		print ''Delete plan history older than 100 days...''
		DELETE FROM PH_PLAY_PLAN where PlayTime<(dateadd( day, -100, CURRENT_TIMESTAMP)) 
		if(@@ERROR<>0) BEGIN RAISERROR(''Script terminated...'',16,1) return END

		/* Delete old MAG2 shedule orders*/
		print ''Delete old mag schedule orders''
		DELETE  FROM [dbo].[MAG2_WORK] where WorkDate< dateadd( day, -14, GETDATE())
		if(@@ERROR<>0) BEGIN RAISERROR(''Script terminated...'',16,1) return END

		/*Delete PH_HISTORY rows older than 30 days*/
		IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[PH_HISTORY]'') AND type in (N''U''))
		BEGIN
			PRINT N''Delete PH_HISTORY rows older than 30 days''
			DECLARE @MinDate datetime
			SELECT @MinDate=dateadd(d,-30, GETDATE())
	
			DELETE	
			FROM [dbo].[PH_HISTORY] 
			WHERE ModifyDate < @MinDate	
		END

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

		/*Truncating transaction log file for current database*/
		print ''Truncating transaction log file for current database...''

		declare @ver int
		declare @ver_str varchar(128)
		set @ver_str = convert(varchar(128),SERVERPROPERTY(''ProductVersion''))
		set @ver = cast( left(@ver_str, charindex(''.'', @ver_str)-1) as int )

		declare @db_name varchar(128), 
			@log_name varchar(128),
			@dbcc_query varchar(256)

		SELECT @db_name = DB_NAME()
		SELECT @log_name=RTRIM(name) from sysfiles where (status&0x40)=0x40
	
		if (@ver < 10)
		BEGIN
			set @dbcc_query=''BACKUP LOG [''+@db_name+''] WITH TRUNCATE_ONLY''
			EXEC (@dbcc_query)
		END

		set @dbcc_query=''DBCC SHRINKFILE ([''+@log_name+''],0)''
		EXEC (@dbcc_query)
	END
	'
	IF (NOT EXISTS(SELECT * FROM PARAMETERS WHERE (Name = 'MdbClean.Interval')) and (SELECT COUNT(id) FROM MODIFY_DATES) < 13000000)
		INSERT INTO PARAMETERS (Type, Name, Value) VALUES (0, 'MdbClean.Interval', 168)
	ELSE 
		print '**** MdbClean automatic has been installed but will not executed because the amount  MODIFY_DATES elements exceeds 13 000 000'
		
-- ------  end payload  --------------------------------------------------------------------------------------
INSERT INTO components (name, veri) VALUES(@comp_name,@comp_ver) if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print '**** Component:   ' + @comp_name + '   v: ' + CAST(@comp_ver as varchar(128)) + '    OK'
GO

--=============================================================================
-- Components table
--=============================================================================


IF NOT EXISTS (SELECT * FROM master.sys.objects WHERE type = 'U' AND name = 'components')
BEGIN
CREATE TABLE [master].[dbo].[components](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](128) NOT NULL,
	[vers] [nvarchar](128) NULL,
	[veri] [int] NULL,
	[AddDate] [DateTime] NULL default getdate()
) 
END
GO


--=============================================================================
-- BootstrapBackgroundTask, BootstrapBackgroundTask procedures
--=============================================================================

-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- begin step: BootstrapBackgroundTask
if(@@ERROR<>0) BEGIN RAISERROR('Script terminated...',16,1) return END
DECLARE @comp_ver int
DECLARE @comp_name varchar(128)
SET @comp_ver = 1
SET @comp_name = 'SP.BootstrapBackgroundTask'

IF EXISTS( SELECT * FROM master.dbo.components WHERE name = @comp_name and veri >= @comp_ver) 
    BEGIN print 'Component: '+ @comp_name + ' already updated to version '  +   CAST(@comp_ver as varchar(128))+ ' or more' return END
    
SET XACT_ABORT ON
BEGIN TRAN    
-- ++++++  begin payload  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	IF NOT EXISTS (SELECT * FROM master.sys.objects WHERE type = 'P' AND name = 'DoBackgroundTask')
	   exec master.sys.sp_executesql N'CREATE PROCEDURE [dbo].[DoBackgroundTask] AS BEGIN SET NOCOUNT ON; END'

	exec master.sys.sp_executesql N'
	ALTER PROCEDURE [dbo].DoBackgroundTask AS
	BEGIN
		SET ANSI_DEFAULTS ON
		SET IMPLICIT_TRANSACTIONS OFF
		SET CONCAT_NULL_YIELDS_NULL ON
		PRINT ''USERPROC DoBackgroundTask: START LOOP''
		WHILE 1=1
		BEGIN
			BEGIN TRY
				DECLARE @ProcName NVARCHAR(50)
				SET @ProcName = N''[dbo].[MdbBackgroundTask]''

				DECLARE @SQL NVARCHAR(MAX)
				SELECT @SQL = STUFF(
					(SELECT CHAR(13) + N''BEGIN TRY EXEC ['' + name + ''].'' + @ProcName + '' END TRY BEGIN CATCH print ERROR_MESSAGE() END CATCH''
					FROM sys.databases 
					WHERE OBJECT_ID(name + ''.'' + @ProcName) IS NOT NULL
					FOR XML PATH(''''), TYPE
					).value(''.'', ''NVARCHAR(MAX)''), 1, 1, '''')

				PRINT @SQL
				EXEC sys.sp_executesql @SQL
			END TRY
			BEGIN CATCH 
				print ERROR_MESSAGE() 
			END CATCH
		WAITFOR DELAY ''01:00:00''
		END
		PRINT ''USERPROC DoBackgroundTask: END LOOP''
	END'

	IF NOT EXISTS (SELECT * FROM master.sys.objects WHERE type = 'P' AND name = 'BootstrapBackgroundTask')
	   exec master.sys.sp_executesql N'CREATE PROCEDURE [dbo].[BootstrapBackgroundTask] AS BEGIN SET NOCOUNT ON; END'

	exec master.sys.sp_executesql N'
	ALTER PROCEDURE [dbo].BootstrapBackgroundTask AS
	BEGIN
		PRINT ''USERPROC BootstrapBackgroundTask: DELAY''
		WAITFOR DELAY ''00:05:00''
		PRINT ''USERPROC BootstrapBackgroundTask: START BKG TASK''
		EXEC [dbo].DoBackgroundTask
	END'

-- ------  end payload  --------------------------------------------------------------------------------------
INSERT INTO master.dbo.components (name, veri) VALUES(@comp_name,@comp_ver) if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print '**** Component:   ' + @comp_name + '   v: ' + CAST(@comp_ver as varchar(128)) + '    OK'
GO
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<

EXEC master.sys.sp_procoption '[dbo].BootstrapBackgroundTask', 'startup', 'on'
GO
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<


-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- begin step: Insert ROLLBACK TRANSACTION on error to SP.RunBackgroundTask
if(@@ERROR<>0) BEGIN RAISERROR('Script terminated...',16,1) return END
DECLARE @comp_ver int
DECLARE @comp_name varchar(128)
SET @comp_ver = 2
SET @comp_name = 'SP.RunBackgroundTask'

IF EXISTS( SELECT * FROM components WHERE name = @comp_name and veri <> 1) 
    BEGIN print 'Component: '+ @comp_name + ' should not be be updated with version ' + CAST(@comp_ver as varchar(128)) + ', patch is applicable to version 1 only' return END
    
SET XACT_ABORT ON
BEGIN TRAN
-- ++++++  begin payload  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
declare @sql_code nvarchar(max)

SELECT @sql_code=m.definition
  FROM sys.sql_modules m 
       INNER JOIN 
       sys.objects o 
         ON m.object_id = o.object_id
         
  WHERE o.type_desc = 'SQL_STORED_PROCEDURE' and o.name='RunBackgroundTask'

  declare @sql_pos int 

  select @sql_pos=PATINDEX ('%BEGIN CATCH%',@sql_code);
  IF @sql_pos=0 OR @sql_pos is NULL
  BEGIN
	ROLLBACK
	RAISERROR('No pattern found in RunBackgroundTask code',16,1)
	RETURN
  END
  
  select @sql_pos=PATINDEX ('%ROLLBACK TRANSACTION%',@sql_code);
  IF @sql_pos=0
  BEGIN
	select @sql_code=REPLACE(@sql_code,'BEGIN CATCH',
	'BEGIN CATCH
					ROLLBACK TRANSACTION ')
	DROP PROCEDURE RunBackgroundTask
	exec sp_executesql @sql_code
  END

-- ------  end payload  --------------------------------------------------------------------------------------
INSERT INTO components (name, veri) VALUES(@comp_name,@comp_ver) if(@@ERROR<>0) BEGIN ROLLBACK RAISERROR('',16,1) RETURN END
COMMIT
print '**** Component:   ' + @comp_name + '   v: ' + CAST(@comp_ver as varchar(128)) + '    OK'
GO
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<