-- Fehlertypen

CREATE TABLE t_fault_type (
	fault_type			VARCHAR2(50)	NOT NULL,
CONSTRAINT pk_fault_type PRIMARY KEY (fault_type)
);

-- Fehleranzahlen

CREATE TABLE t_fault_count (
	fault_date			DATE			NOT NULL,
	fault_type			VARCHAR2(50)	NOT NULL,
	fault_host			VARCHAR2(50)	NOT NULL,
	fault_process		VARCHAR2(100)	NOT NULL,
	fault_count			NUMBER(8)		NOT NULL,
CONSTRAINT uk_fault_count UNIQUE (fault_date, fault_type, fault_host, fault_process),
CONSTRAINT fk_fault_count FOREIGN KEY (fault_type) REFERENCES t_fault_type (fault_type) ON DELETE CASCADE
);

CREATE INDEX ix_fault_count ON t_fault_count (
	fault_date			DESC,
	fault_type			ASC,
	fault_host			ASC,
	fault_process		ASC
);

-- Fehlermeldungen

CREATE TABLE t_fault (
	fault_id			NUMBER(12)		NOT NULL,
	fault_date			DATE			NOT NULL,
	fault_type			VARCHAR2(50)	NOT NULL,
	fault_host			VARCHAR2(50)	NOT NULL,
	fault_process		VARCHAR2(100)	NULL,
	fault_payload		CLOB			NULL,
	fault_message		CLOB			NOT NULL,
	fault_status		NUMBER(1)		DEFAULT 0,
CONSTRAINT uk_fault UNIQUE (fault_id),
CONSTRAINT chk_fault CHECK (fault_status IN (0, 1)),
CONSTRAINT fk_fault FOREIGN KEY (fault_type) REFERENCES t_fault_type (fault_type) ON DELETE CASCADE
);

CREATE INDEX ix_fault ON t_fault (
	fault_date			DESC,
	fault_type			ASC,
	fault_host			ASC,
	fault_process		ASC
);

CREATE SEQUENCE s_fault
	INCREMENT BY 1
	START WITH 1
	MAXVALUE 999999999999
	MINVALUE 1
	CYCLE
	CACHE 100
;

CREATE OR REPLACE TRIGGER ti_fault BEFORE INSERT ON t_fault
	FOR EACH ROW
	BEGIN
		FOR rec IN (SELECT s_fault.nextval AS new_id FROM DUAL)
		LOOP
			:new.fault_id := rec.new_id;
		END LOOP;
	END
;
/
