-- Script to create neccesary tables and their prequisites for the FESA DB. In particular for the Directory server.

-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--
-- FESA_CLASS_TYPE
--
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE TABLE "FESA"."FESA_CLASS_TYPE" 
	("CLASS_TYPE" VARCHAR2(14 BYTE) NOT NULL ENABLE, 
	 CONSTRAINT "FESA_CLASS_TYPE_PK" PRIMARY KEY ("CLASS_TYPE")
  	 	USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  		STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  			PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  			BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  		TABLESPACE "FESA"  ENABLE) SEGMENT CREATION IMMEDIATE 
	PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
	NOCOMPRESS LOGGING
  	STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 		PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  		BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
	TABLESPACE "FESA" ;

-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--
-- FESA_DEVICECLASSES
--
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE TABLE "FESA"."FESA_DEVICECLASSES" 
	("CLASSNAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
	 "CLASS_ID" NUMBER, 
	 CONSTRAINT "FESA_DEVICECLASSES_PK" PRIMARY KEY ("CLASSNAME")
  	 	USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  		STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  			PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  			BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  	 TABLESPACE "FESA" ENABLE
   	) 
	SEGMENT CREATION IMMEDIATE 
	PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
	NOCOMPRESS LOGGING
	STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
	  	PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
	  	BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
	TABLESPACE "FESA" ;

CREATE OR REPLACE TRIGGER "FESA"."FESA_DEVICECLASSES_D_ABC" 
	after delete on fesa_deviceclasses
	begin
    		delete from abc_fesa_deviceclasses a
			where a.implementation = 'FESA'
				and not exists (select 'x' fesa_deviceclasses f a.classname = f.classname);
	end;

/
ALTER TRIGGER "FESA"."FESA_DEVICECLASSES_D_ABC" ENABLE;

CREATE OR REPLACE TRIGGER "FESA"."FESA_DEVICECLASSES_IU_ABC" 
	after insert or update on fesa_deviceclasses
	begin
      		MERGE INTO abc_fesa_deviceclasses v
			USING (select classname from fesa_deviceclasses) t
			ON (v.classname = t.classname)
			WHEN MATCHED THEN
	  			UPDATE SET v.implementation = 'FESA'
			WHEN NOT MATCHED THEN
	  			INSERT (v.classname, v.implementation, v.lastupdated) VALUES (t.classname, 'FESA', sysdate);
	end;

/
ALTER TRIGGER "FESA"."FESA_DEVICECLASSES_IU_ABC" ENABLE;

-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--
-- FESA_CLASSVERSIONS
--
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE TABLE "FESA"."FESA_CLASSVERSIONS" 
	("CLASSNAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
	 "VERSION" NUMBER(3,0) NOT NULL ENABLE, 
	 "CLASS_TYPE" VARCHAR2(14 BYTE) DEFAULT 'standard-class' NOT NULL ENABLE, 
	 "DESCRIPTION" VARCHAR2(4000 BYTE), 
	 "FRAMEWORK_VERSION" VARCHAR2(4 BYTE), 
	 CONSTRAINT "FESA_CLASSVERSIONS_PK" PRIMARY KEY ("CLASSNAME", "VERSION")
  		USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  		STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  			PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  			BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  	 TABLESPACE "FESA"  ENABLE, 
	 CONSTRAINT "FESA_CLASSVERSIONS_CHK1" CHECK ( TRIM(FRAMEWORK_VERSION)=FRAMEWORK_VERSION) ENABLE, 
	 CONSTRAINT "FESA_CLASSVERSIONS_FK2" FOREIGN KEY ("CLASS_TYPE") 
	 	REFERENCES "FESA"."FESA_CLASS_TYPE" ("CLASS_TYPE") ENABLE, 
	 CONSTRAINT "FESA_CLASSVERSIONS_FK1" FOREIGN KEY ("CLASSNAME")
	 	REFERENCES "FESA"."FESA_DEVICECLASSES" ("CLASSNAME") ON DELETE CASCADE ENABLE
   	) SEGMENT CREATION IMMEDIATE 
	PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
	NOCOMPRESS LOGGING
	STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
		PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
		BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
	TABLESPACE "FESA";

CREATE INDEX "FESA"."FESA_CLASSVERSIONS_IDX1" ON "FESA"."FESA_CLASSVERSIONS" ("CLASS_TYPE") 
	PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
	STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
		PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
		BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
	TABLESPACE "FESA";

-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--
-- ABC_FESA_DEVICECLASS
--
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE TABLE "FESA"."ABC_FESA_DEVICECLASSES" 
	("CLASSNAME" VARCHAR2(20 BYTE), 
	 "IMPLEMENTATION" VARCHAR2(10 BYTE) CONSTRAINT "DEVC_IMPLEMENTATION_NN" NOT NULL ENABLE, 
	 "ABGROUP" VARCHAR2(10 BYTE), 
	 "ALARMS" CHAR(1 BYTE), 
	 "PPMCOUNT" NUMBER(2,0), 
	 "CREADATE" DATE, 
	 "CREATOR" VARCHAR2(30 BYTE), 
	 "LASTUPDATED" DATE, 
	 "UPDATER" VARCHAR2(30 BYTE), 
	 "IMPLEMENTS" VARCHAR2(100 BYTE), 
	 "GM_SUPERCLASS" VARCHAR2(20 BYTE), 
	 "GM_CLASSCAT" CHAR(2 BYTE), 
	 "GM_CLASSNO" NUMBER(6,0), 
	 "GM_MULTIMACH" CHAR(1 BYTE), 
	 "CLASSDOMAIN" VARCHAR2(16 BYTE), 
	 "DESCRIPTION" VARCHAR2(80 BYTE), 
	 CONSTRAINT "DEVICECLASSES_PK" PRIMARY KEY ("CLASSNAME")
  	 	USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  		STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  			PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  			BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  	 TABLESPACE "FESA"  ENABLE, 
	 CONSTRAINT "DEVC_GM_CLASSNO_CHK" 
		CHECK ( IMPLEMENTATION = 'GM' AND GM_CLASSNO IS NOT NULL OR IMPLEMENTATION != 'GM' ) ENABLE
   	) SEGMENT CREATION IMMEDIATE 
  	PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  	NOCOMPRESS LOGGING
  	STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  		PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  		BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  	TABLESPACE "FESA";

-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--
-- ABC_TGM_NETWORKS
--
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE TABLE "FESA"."ABC_TGM_NETWORKS" 
	("TGM_NETWORK_ID" NUMBER(38,0), 
	 "TGM_NETWORK_NAME" VARCHAR2(32 BYTE) CONSTRAINT "TGMN_TGM_NETWORK_NAME_NN" NOT NULL ENABLE, 
	 "DESCRIPTION" VARCHAR2(60 BYTE), 
	CONSTRAINT "TGM_NETWORKS_PK" PRIMARY KEY ("TGM_NETWORK_ID")
  		USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  		STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  			PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  			BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  	TABLESPACE "FESA"  ENABLE
   	) SEGMENT CREATION IMMEDIATE 
  	PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 	NOCOMPRESS LOGGING
  	STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  		PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  		BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  	TABLESPACE "FESA";

-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--
-- ABC_FESA_DEVICECLASSES
--
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE TABLE "FESA"."FESA_DEVICECLASSES" 
	("CLASSNAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
	 "CLASS_ID" NUMBER, 
	 CONSTRAINT "FESA_DEVICECLASSES_PK" PRIMARY KEY ("CLASSNAME")
  	 	USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  		STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  			PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  			BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  	 TABLESPACE "FESA" ENABLE
   	) 
	SEGMENT CREATION IMMEDIATE 
	PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
	NOCOMPRESS LOGGING
	STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
	  	PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
	  	BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
	TABLESPACE "FESA" ;

CREATE OR REPLACE TRIGGER "FESA"."FESA_DEVICECLASSES_D_ABC" 
  after delete on fesa_deviceclasses
  begin
    delete from abc_fesa_deviceclasses a
		where a.implementation = 'FESA'
		and not exists (
			select 'x' from fesa_deviceclasses f
				where a.classname = f.classname
		);
  end;

/
ALTER TRIGGER "FESA"."FESA_DEVICECLASSES_D_ABC" ENABLE;

CREATE OR REPLACE TRIGGER "FESA"."FESA_DEVICECLASSES_IU_ABC" 
  after insert or update on fesa_deviceclasses
    begin
      MERGE INTO abc_fesa_deviceclasses v
	USING (select classname from fesa_deviceclasses) t
	ON (v.classname = t.classname)
	WHEN MATCHED THEN
	  UPDATE SET v.implementation = 'FESA'
	WHEN NOT MATCHED THEN
	  INSERT (v.classname, v.implementation, v.lastupdated) VALUES (t.classname, 'FESA', sysdate);
    end;

/
ALTER TRIGGER "FESA"."FESA_DEVICECLASSES_IU_ABC" ENABLE;

-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--
-- ABC_COMPUTERS
--
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE TABLE "FESA"."ABC_COMPUTERS" 
	("COMPNAME" VARCHAR2(20 BYTE), 
    	 "COMPTYPE" VARCHAR2(8 BYTE) CONSTRAINT "COMPUTERS_COMPTYPE_NN" NOT NULL ENABLE, 
    	 "COMPNO" NUMBER(4,0), 
    	 "DIRECTORY" VARCHAR2(8 BYTE), 
    	 "COMPDESCRIP" VARCHAR2(40 BYTE), 
    	 "PLSMACHINE" VARCHAR2(6 BYTE), 
    	 "DIGIOMBNO" NUMBER(5,0), 
    	 "RESETVAL" NUMBER(6,0), 
    	 "LASTUSER" VARCHAR2(40 BYTE), 
    	 "CONCENTRATOR" VARCHAR2(8 BYTE), 
    	 "CONSPORT" NUMBER(5,0), 
	 "SERVER" VARCHAR2(8 BYTE), 
	 "SERVPORT" NUMBER(5,0), 
	 "BUILDING" VARCHAR2(5 BYTE), 
	 "ROOMCODE" VARCHAR2(10 BYTE), 
	 "RACK" VARCHAR2(20 BYTE), 
	 "HOSTNAME" VARCHAR2(20 BYTE), 
	 "CONSOLE" VARCHAR2(8 BYTE), 
	 "PRIORITY" NUMBER(3,0) DEFAULT 0, 
	 "TGM_NETWORK_ID" NUMBER(38,0), 
	 "TEL" VARCHAR2(4 BYTE), 
	 "OPERATIONAL_FLAG" VARCHAR2(2 BYTE), 
	 CONSTRAINT "COMPUTERS_COMPNAME_CHK" CHECK (  compname = lower(compname)  ) ENABLE, 
	 CONSTRAINT "COMPUTERS_PK" PRIMARY KEY ("COMPNAME")
	 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
	 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
		 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  		 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  	 TABLESPACE "FESA"  ENABLE, 
	 CONSTRAINT "COMPUTERS_TGM_NETWORKS_FK" FOREIGN KEY ("TGM_NETWORK_ID")
	 	REFERENCES "FESA"."ABC_TGM_NETWORKS" ("TGM_NETWORK_ID") ENABLE
   	) SEGMENT CREATION IMMEDIATE 
  	PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 	NOCOMPRESS LOGGING
  	STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  		PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  		BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  	TABLESPACE "FESA" ;

-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--
-- ABC_DEVICES
--
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE TABLE "FESA"."ABC_DEVICES" 
       ("DEVICE_ID" NUMBER(10,0), 
	"DEVICENAME" VARCHAR2(30 BYTE) CONSTRAINT "DEVICES_DEVICENAME_NN" NOT NULL ENABLE, 
	"ALIAS" VARCHAR2(30 BYTE), 
	"ACCELERATOR" VARCHAR2(10 BYTE), 
	"SUBSYSTEM" VARCHAR2(24 BYTE), 
	"FECNAME" VARCHAR2(20 BYTE), 
	"BUSTYPE" VARCHAR2(10 BYTE), 
	"SERVERNAME" VARCHAR2(50 BYTE), 
	"CLASSNAME" VARCHAR2(20 BYTE), 
	"DEVICETYPE" VARCHAR2(20 BYTE), 
	"PPM" CHAR(1 BYTE), 
	"ALARMS" CHAR(1 BYTE), 
	"DESCRIPTION" VARCHAR2(80 BYTE), 
	"GM_MBNO" NUMBER(5,0), 
	"SL_FAMILY" VARCHAR2(10 BYTE), 
	"SL_MEMNUMBER" VARCHAR2(10 BYTE), 
	"SL_ACCESSTYPE" VARCHAR2(5 BYTE), 
	"SL_EQUIPMENTSORT" VARCHAR2(10 BYTE), 
	"SL_MEMHISTID" NUMBER(10,0), 
	"SL_LOCALEDU" VARCHAR2(1 BYTE), 
	"SL_FIELD5" VARCHAR2(9 BYTE), 
	"SL_FIELD6" VARCHAR2(9 BYTE), 
	"SL_FIELD7" VARCHAR2(9 BYTE), 
	"SL_FIELD8" VARCHAR2(9 BYTE), 
	"SL_FIELD9" VARCHAR2(9 BYTE), 
	"SL_FIELD10" VARCHAR2(9 BYTE), 
	"DERIVED1" VARCHAR2(80 BYTE), 
	"DERIVED2" VARCHAR2(80 BYTE), 
	"DERIVED3" VARCHAR2(80 BYTE), 
	"DERIVED4" VARCHAR2(80 BYTE), 
	"DERIVED5" VARCHAR2(80 BYTE), 
	"DERIVED6" VARCHAR2(80 BYTE), 
	"DERIVED7" VARCHAR2(80 BYTE), 
	"DERIVED8" VARCHAR2(80 BYTE), 
	"DERIVED9" VARCHAR2(80 BYTE), 
	"DERIVED10" VARCHAR2(80 BYTE), 
	"INSTALL_CLASSVERSION" NUMBER(4,0), 
	"INSTALL_GM_CLASSNO" NUMBER(5,0), 
	"TEMPORARY" CHAR(1 BYTE), 
	"SL_USERNAME" VARCHAR2(15 BYTE), 
	"SL_DATETIME" DATE, 
	"UPDATED" DATE, 
	"UPDATER" VARCHAR2(20 BYTE), 
	"TIMING_DOMAIN" VARCHAR2(10 BYTE), 
	"REFIMPL" VARCHAR2(4 BYTE) DEFAULT 'DIR' CONSTRAINT "DEVICES_REFIMPL_NN" NOT NULL ENABLE, 
	"CLASS_ID" NUMBER, 
	 CONSTRAINT "DEVICES_PK" PRIMARY KEY ("DEVICE_ID")
  		USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  		STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  			PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  			BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  		TABLESPACE "FESA"  ENABLE, 
	 CONSTRAINT "DEVICES_UK" UNIQUE ("DEVICENAME")
  		USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  		STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  			PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  			BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  		TABLESPACE "FESA"  ENABLE, 
	 CONSTRAINT "DEVICES_FECNAME_CHK" CHECK (  fecname = lower(fecname)  ) ENABLE, 
	 CONSTRAINT "DEVICES_REFIMPL_CHK" CHECK (   refimpl in ('LSA', 'DIR', 'NONE')   ) ENABLE, 
	 CONSTRAINT "DEVICES_COMPUTERS_FK" FOREIGN KEY ("FECNAME")
	 	REFERENCES "FESA"."ABC_COMPUTERS" ("COMPNAME") ON DELETE SET NULL DISABLE, 
	 CONSTRAINT "DEV_DEVC_CLASSNAME_FK" FOREIGN KEY ("CLASSNAME")
	 	REFERENCES "FESA"."ABC_FESA_DEVICECLASSES" ("CLASSNAME") ON DELETE CASCADE ENABLE
   	) SEGMENT CREATION IMMEDIATE 
	PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
	NOCOMPRESS LOGGING
	STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
		PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
		BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
	TABLESPACE "FESA" ;

CREATE INDEX "FESA"."DEVICES5" ON "FESA"."ABC_DEVICES" ("SL_FAMILY", "SL_MEMNUMBER") 
	PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
	STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
		PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
		BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
	TABLESPACE "FESA" ;

CREATE INDEX "FESA"."DEVICES_ALIAS_I" ON "FESA"."ABC_DEVICES" ("ALIAS") 
	PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
	STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
		PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
		BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
	TABLESPACE "FESA" ;

CREATE SEQUENCE "FESA"."ABC_DEVICE_ID_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 12218 CACHE 20 NOORDER  NOCYCLE;

-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--
-- CMW_DEVICES
--
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE OR REPLACE FORCE 
	VIEW "FESA"."CMW_DEVICES" ("DEVICE_ID", "DEVICENAME", "ALIAS", "ACCELERATOR", "SUBSYSTEM", "FECNAME", "BUSTYPE",
				   "SERVERNAME", "CLASSNAME", "DEVICETYPE", "PPM", "ALARMS", "DESCRIPTION", "GM_MBNO", "SL_FAMILY", 
				   "SL_MEMNUMBER", "SL_ACCESSTYPE", "SL_EQUIPMENTSORT", "SL_MEMHISTID", "SL_LOCALEDU", "SL_FIELD5", 
				   "SL_FIELD6", "SL_FIELD7", "SL_FIELD8", "SL_FIELD9", "SL_FIELD10", "DERIVED1", "DERIVED2",
				   "DERIVED3", "DERIVED4", "DERIVED5", "DERIVED6", "DERIVED7", "DERIVED8", "DERIVED9",
				   "DERIVED10", "INSTALL_CLASSVERSION", "INSTALL_GM_CLASSNO", "TEMPORARY", "SL_USERNAME",
				   "SL_DATETIME", "UPDATED", "UPDATER", "TIMING_DOMAIN", "REFIMPL", "PROXYNAME") 
	AS SELECT "DEVICE_ID", "DEVICENAME", "ALIAS", "ACCELERATOR", "SUBSYSTEM", "FECNAME", "BUSTYPE",
		  "SERVERNAME", "CLASSNAME", "DEVICETYPE", "PPM", "ALARMS", "DESCRIPTION", "GM_MBNO", "SL_FAMILY",
		  "SL_MEMNUMBER", "SL_ACCESSTYPE", "SL_EQUIPMENTSORT", "SL_MEMHISTID", "SL_LOCALEDU", "SL_FIELD5",
		  "SL_FIELD6", "SL_FIELD7", "SL_FIELD8", "SL_FIELD9", "SL_FIELD10", "DERIVED1","DERIVED2",
		  "DERIVED3", "DERIVED4", "DERIVED5", "DERIVED6", "DERIVED7", "DERIVED8", "DERIVED9",
		  "DERIVED10", "INSTALL_CLASSVERSION", "INSTALL_GM_CLASSNO", "TEMPORARY", "SL_USERNAME",
		  "SL_DATETIME", "UPDATED", "UPDATER", "TIMING_DOMAIN", "REFIMPL", null as "PROXYNAME" 
	FROM "FESA"."ABC_DEVICES" 
	order by "DEVICENAME";

-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--
-- SERVERS_DIRECTORY
--
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE SEQUENCE  "FESA"."SERVERS_DIRECTORY_ID_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 
	INCREMENT BY 1 START WITH 1801 CACHE 20 NOORDER  NOCYCLE;

CREATE TABLE "FESA"."SERVERS_DIRECTORY" 
	("SERVERNAME" VARCHAR2(50 BYTE) CONSTRAINT "SDIR_SERVERNAME_NN" NOT NULL ENABLE, 
	 "LOCATION" VARCHAR2(1024 BYTE), 
	 "ID" NUMBER, 
	 "CONNECTED" TIMESTAMP (6) DEFAULT systimestamp CONSTRAINT "SERVERS_DIRECTORY_CONNECTED_NN" NOT NULL ENABLE, 
	 "UPDATED_BY_CMW" TIMESTAMP (6), 
	 "IP_ADDRESS" VARCHAR2(40 BYTE), 
	 "PROTECT_IP" VARCHAR2(1 BYTE) DEFAULT 'N' CONSTRAINT "SERVERS_DIR_PROTECT_IP_NN" NOT NULL ENABLE, 
	 "PROTECT_LOCATION" VARCHAR2(1 BYTE) DEFAULT 'N' CONSTRAINT "SERVERS_DIR_PROTECT_LOC_NN" NOT NULL ENABLE, 
	 "HOSTNAME" VARCHAR2(30 BYTE), 
	 CONSTRAINT "SERVERS_DIRECTORY_UK" UNIQUE ("SERVERNAME")
  	  	USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  	  	STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  		  	PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  		  	BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
		TABLESPACE "FESA"  ENABLE, 
	 CONSTRAINT "SERVERS_DIR_PROTECT_IP_CHK" CHECK (protect_ip in ('Y','N')) ENABLE, 
	 CONSTRAINT "SERVERS_DIR_PROTECT_LOC_CHK" CHECK (protect_location in ('Y','N')) ENABLE, 
	 CONSTRAINT "SERVERS_DIRECTORY_PK" PRIMARY KEY ("ID") 
		USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  		STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  			PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
			BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  		TABLESPACE "FESA"  ENABLE
   	) SEGMENT CREATION IMMEDIATE 
	PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
	NOCOMPRESS LOGGING 
	STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  		PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  		BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
	TABLESPACE "FESA" ;

CREATE OR REPLACE TRIGGER "FESA"."SERVERS_DIRECTORY_ID_CHECK" before
	insert on servers_directory 
	for each row declare l_error_message varchar2 (4000) := 'Trigger servers_directory_id_check:';
	begin
		if (:new.id is null) then
			select servers_directory_id_seq.nextval
			into :new.id
			from dual;
		end if;
		exception
		when others then
			l_error_message := l_error_message||substr (sqlerrm, 1, 500) ;
			-- error.report_and_stop (null, l_error_message) ;
	end;

/  
ALTER TRIGGER "FESA"."SERVERS_DIRECTORY_ID_CHECK" ENABLE;

CREATE OR REPLACE TRIGGER "FESA"."SDIR_BEFORE_UPDATE_FER" before
	update of location on servers_directory for each row declare l_error_message varchar2 (1000) := 'Trigger servers_directory_before_update_fer:';
	begin
		:new.connected := systimestamp;
		exception
		when others then
			l_error_message := l_error_message||substr (sqlerrm, 1, 500) ;
			-- error.report_and_go (null, l_error_message) ;
	end;

/
ALTER TRIGGER "FESA"."SDIR_BEFORE_UPDATE_FER" ENABLE;
