--Order 20015011.110

---APPSERVERCLUSTER table---

ALTER TABLE APPSERVERCLUSTER ADD (CLUSTERTYPE NUMBER DEFAULT 0 NOT NULL);
ALTER TABLE APPSERVERCLUSTER ADD (TIMETOSHUTDOWN TIMESTAMP NULL);
 
COMMENT ON COLUMN APPSERVERCLUSTER.CLUSTERTYPE
  IS 'Possible values:
0 - Functional cluster
1 - Physical cluster';

ALTER TABLE APPSERVERCLUSTER
  ADD CONSTRAINT CLUSTERTYPE_VALID1_1
  CHECK (CLUSTERTYPE IN (0, 1));


---APPSERVERCLUSTERCONTENT table---

ALTER TABLE APPSERVERCLUSTERCONTENT ADD (CLUSTERTYPE NUMBER DEFAULT 0 NOT NULL);

ALTER TABLE APPSERVERCLUSTERCONTENT DROP CONSTRAINT XPKAPPSERVERCLUSTERCONTENT;

ALTER TABLE APPSERVERCLUSTERCONTENT
ADD CONSTRAINT  XPKAPPSERVERCLUSTERCONTENT PRIMARY KEY (APPSERVERCLUSTER_ID, APPLICATIONSERVER_ID, CLUSTERTYPE);
 
COMMENT ON COLUMN APPSERVERCLUSTERCONTENT.ACTUALSTATUS
  IS '0 = Unknown
1 = Connectable
2 = Not connectable
3 = Master
4 = Master/ needs refresh
5 = Connectable/ needs refresh
6 = Not connectable/ planned to be updated';

COMMENT ON COLUMN APPSERVERCLUSTERCONTENT.CLUSTERTYPE
  IS 'Copyied from the parent AppServerCluster.';

ALTER TABLE APPSERVERCLUSTERCONTENT
  DROP CONSTRAINT ACTUALSTATUS_VALID;
ALTER TABLE APPSERVERCLUSTERCONTENT
  ADD CONSTRAINT ACTUALSTATUS_VALID
  CHECK (ACTUALSTATUS IN (0, 1, 2, 3, 4, 5, 6));

---insert appserverclustercontent

insert into appservercluster apps
select seq_appservercluster.nextval,
       'PHYSICAL1',
       'Physical Cluster1',
       null,
       getminsystemuser(),
       sysdate,
       1,
       null
from dual
where (select count(1) from appservercluster apps where apps.clustertype = 1) < 2;
commit;

insert into appserverclustercontent
select appsc.appservercluster_id,
       apps.applicationserver_id,
       0,
       getminsystemuser(),
       sysdate,
       appsc.clustertype
from appservercluster appsc, applicationserver apps
where appsc.code = 'PHYSICAL1';
commit;

insert into appservercluster
select seq_appservercluster.nextval,
       'PHYSICAL2',
       'Physical Cluster2',
       null,
       getminsystemuser(),
       sysdate,
       1,
       null
from dual
where (select count(1) from appservercluster apps where apps.clustertype = 1) < 2;
commit;