--------------------------------------------------------------------- -- BASE_VERSION 2440 -- 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 ModifyDate0) 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 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 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 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 --######################################################################################################### -- ### 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