---------------------------------------------------------------------
-- BASE_VERSION 2200 

-- 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 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

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

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

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 
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)
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)
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 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)
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

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)
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 
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)
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)
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 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)
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
	
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)
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  = 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

