analytics/configuration/schemas/delivery/sqlserver/NC5_UPDATE_MS.sql

--
--    Licensed Materials - Property of IBM
--    
--    BI and PM: JSM
--    
--    (c) Copyright IBM Corp. 2003, 2010.
--    
--    US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
--    


-------------------------------------------------------------------------------
--	NC5_UPDATE_MS.SQL			*	DO NOT EDIT	 *			Data Store Version: 5 		 
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
--$100-- backup create begin
-------------------------------------------------------------------------------

CREATE TABLE X_TASKSCHEDULE(
   PK_TASKSCHEDULE_ID            INTEGER NOT NULL,
   FK_TASK_ID                    INTEGER,
   NAME                          NVARCHAR(255),
   DESCRIPTION                   NVARCHAR(1000),
   LASTMOD_BY                    CHAR(40),
   MODCOUNT                      INTEGER DEFAULT (-1) NOT NULL,
   RUN_REQUIREMENTS              INTEGER,
   LAST_EXECUTION_AT             NUMERIC,
   NEXT_EXECUTION_REQUEST        NUMERIC,
   SCHEDULE_DELAY                NUMERIC,
   DELETE_AFTER_LAST_RUN         INTEGER
)
go

-------------------------------------------------------------------------------
--$110-- backup inserts begin
-------------------------------------------------------------------------------

INSERT INTO X_TASKSCHEDULE (PK_TASKSCHEDULE_ID,FK_TASK_ID ,NAME,DESCRIPTION,
        LASTMOD_BY,MODCOUNT, RUN_REQUIREMENTS, LAST_EXECUTION_AT, NEXT_EXECUTION_REQUEST,
        SCHEDULE_DELAY) 
    SELECT PK_TASKSCHEDULE_ID,FK_TASK_ID ,NAME,DESCRIPTION,
        LASTMOD_BY,MODCOUNT, RUN_REQUIREMENTS, LAST_EXECUTION_AT, NEXT_EXECUTION_REQUEST,
        SCHEDULE_DELAY FROM NC_TASKSCHEDULE
go        

-------------------------------------------------------------------------------
--$120-- foreign keys drop begin
-------------------------------------------------------------------------------

ALTER TABLE NC_TASKSCHEDULE
   DROP CONSTRAINT F2N_TASKSCHEDULE 
go

ALTER TABLE R_TASKSCHEDULE_SCHEDULE
   DROP CONSTRAINT F1R_TASKSCHEDULE_S
go

-------------------------------------------------------------------------------
--$130-- views drop begin
-------------------------------------------------------------------------------


-------------------------------------------------------------------------------
--$140-- tables drop begin
-------------------------------------------------------------------------------
DROP TABLE NC_TASKSCHEDULE
go

-------------------------------------------------------------------------------
--$200-- recreate tables begin
-------------------------------------------------------------------------------

CREATE TABLE NC_TASKSCHEDULE(
   PK_TASKSCHEDULE_ID            INTEGER NOT NULL,
   FK_TASK_ID                    INTEGER,
   NAME                          NVARCHAR(255),
   DESCRIPTION                   NVARCHAR(1000),
   LASTMOD_BY                    CHAR(40),
   MODCOUNT                      INTEGER DEFAULT (-1) NOT NULL,
   RUN_REQUIREMENTS              INTEGER,
   LAST_EXECUTION_AT             NUMERIC,
   NEXT_EXECUTION_REQUEST        NUMERIC,
   SCHEDULE_DELAY                NUMERIC,
   DELETE_AFTER_LAST_RUN         INTEGER
)
go


CREATE TABLE NC_SCHEDULE_QUEUE(
   	SCHEDULE_ID CHAR(32) NOT NULL,
	RUN_DATE SMALLINT
)
go

-------------------------------------------------------------------------------
--$201-- indices create begin
-------------------------------------------------------------------------------

CREATE INDEX I1N_TASKSCHEDULE ON NC_TASKSCHEDULE (
   FK_TASK_ID)
go

CREATE INDEX I1N_SCHEDULE_QUEUE ON NC_SCHEDULE_QUEUE (
   SCHEDULE_ID)
go

-------------------------------------------------------------------------------
--$202-- primary keys add begin
-------------------------------------------------------------------------------

ALTER TABLE NC_TASKSCHEDULE
   ADD CONSTRAINT PN_TASKSCHEDULE PRIMARY KEY (PK_TASKSCHEDULE_ID)
go

ALTER TABLE NC_SCHEDULE_QUEUE
   ADD CONSTRAINT PN_SCHEDULE_QUEUEI PRIMARY KEY (SCHEDULE_ID)
go

-------------------------------------------------------------------------------
--$203-- alter tables begin
-------------------------------------------------------------------------------


-------------------------------------------------------------------------------
--$210-- views create begin
-------------------------------------------------------------------------------



-------------------------------------------------------------------------------
--$220-- server data inserts begin
-------------------------------------------------------------------------------

INSERT INTO NC_DB_VERSION (DB_VERSION_ID,BUILD) VALUES (5,'build no.')
go

DELETE FROM NC_CTMAP WHERE OBJTYPE = 902
go
DELETE FROM NC_CTMAP WHERE OBJTYPE = 1000
go
DELETE FROM NC_CTMAP WHERE OBJTYPE = 1001
go
DELETE FROM NC_CTMAP WHERE OBJTYPE = 1002
go
DELETE FROM NC_CTMAP WHERE OBJTYPE = 1003
go
DELETE FROM NC_CTMAP WHERE OBJTYPE = 1004
go
DELETE FROM NC_CTMAP WHERE OBJTYPE = 1005
go
DELETE FROM NC_CTMAP WHERE OBJTYPE = 1020
go
DELETE FROM NC_CTMAP WHERE OBJTYPE = 1021
go
DELETE FROM NC_CTMAP WHERE OBJTYPE = 1022
go
DELETE FROM NC_CTMAP WHERE OBJTYPE = 1023
go
DELETE FROM NC_CTMAP WHERE OBJTYPE = 1024
go


INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES(
      902,
      'noticecast.scheduling.nc.TaskScheduleImpl',
      'NC_TASK_SCHEDULE',
      'noticecast.datatier.scheduling.JDBCDhTaskScheduleImpl',
      'noticecast.scheduling.nc.TaskScheduleRImpl'
   )
   go
   
    
   INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES(
      1000,
      'noticecast.scheduling.schedules.YearlySchedule',
      'NC_SCHEDULE',
      'noticecast.datatier.scheduling.JDBCDhScheduleImpl',
      NULL
   )
   go
   
   
   INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES(
      1001,
      'noticecast.scheduling.schedules.MonthlySchedule',
      'NC_SCHEDULE',
      'noticecast.datatier.scheduling.JDBCDhScheduleImpl',
      NULL
   )
   go
   
   
   INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES(
      1002,
      'noticecast.scheduling.schedules.WeeklySchedule',
      'NC_SCHEDULE',
      'noticecast.datatier.scheduling.JDBCDhScheduleImpl',
      NULL
   )
   go
   
   
   INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES(
      1003,
      'noticecast.scheduling.schedules.DailySchedule',
      'NC_SCHEDULE',
      'noticecast.datatier.scheduling.JDBCDhScheduleImpl',
      NULL
   )
   go
   
   
   INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES(
      1004,
      'noticecast.scheduling.schedules.FixedIntervalSchedule',
      'NC_SCHEDULE',
      'noticecast.datatier.scheduling.JDBCDhScheduleImpl',
      NULL
   )
   go
   
   
   INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES(
      1005,
      'noticecast.scheduling.schedules.ISchedule',
      'NC_SCHEDULE',
      'noticecast.datatier.scheduling.JDBCDhScheduleImpl',
      NULL
   )
   go
   
   
   INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES(
      1020,
     'noticecast.scheduling.schedules.ISchedule',
      'NC_SCHEDULE',
      'noticecast.datatier.scheduling.JDBCDhScheduleImpl',
      NULL
   )
   go
   
   
   INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES(
      1021,
      'noticecast.scheduling.schedules.ISchedule',
      'NC_SCHEDULE',
      'noticecast.datatier.scheduling.JDBCDhScheduleImpl',
      NULL
   )
   go
   
   
   INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES(
      1022,
      'noticecast.scheduling.schedules.ISchedule',
      'NC_SCHEDULE',
      'noticecast.datatier.scheduling.JDBCDhScheduleImpl',
      NULL
   )
   go
   
   
   INSERT INTO NC_CTMAP (OBJTYPE,CLASS,TABLENAME,HELPER,REMOTECLASS) VALUES(
      1023,
      'noticecast.scheduling.schedules.ISchedule',
      'NC_SCHEDULE',
      'noticecast.datatier.scheduling.JDBCDhScheduleImpl',
      NULL
   )
   go

-------------------------------------------------------------------------------
--$230-- restore inserts begin
-------------------------------------------------------------------------------

INSERT INTO NC_TASKSCHEDULE (PK_TASKSCHEDULE_ID,FK_TASK_ID ,NAME,DESCRIPTION,
        LASTMOD_BY,MODCOUNT, RUN_REQUIREMENTS, LAST_EXECUTION_AT, NEXT_EXECUTION_REQUEST,
        SCHEDULE_DELAY) 
    SELECT PK_TASKSCHEDULE_ID,FK_TASK_ID ,NAME,DESCRIPTION,
        LASTMOD_BY,MODCOUNT, RUN_REQUIREMENTS, LAST_EXECUTION_AT, NEXT_EXECUTION_REQUEST,
        SCHEDULE_DELAY FROM X_TASKSCHEDULE
go        

-------------------------------------------------------------------------------
--$240-- foreign keys add begin
-------------------------------------------------------------------------------

ALTER TABLE NC_TASKSCHEDULE
   ADD CONSTRAINT F2N_TASKSCHEDULE FOREIGN KEY (FK_TASK_ID)
      REFERENCES NC_TASK
go

ALTER TABLE R_TASKSCHEDULE_SCHEDULE
   ADD CONSTRAINT F1R_TASKSCHEDULE_S FOREIGN KEY (FK_TASKSCHEDULE_ID)
      REFERENCES NC_TASKSCHEDULE
go

-------------------------------------------------------------------------------
--$250-- backup drop begin
-------------------------------------------------------------------------------

DROP TABLE X_TASKSCHEDULE
go


-------------------------------------------------------------------------------
--$260-- end sql by function
-------------------------------------------------------------------------------
--	END NC5_UPDATE_MS.SQL			*	DO NOT EDIT	 * 		 
-------------------------------------------------------------------------------