48 lines
1.7 KiB
SQL
48 lines
1.7 KiB
SQL
CREATE TABLE severity (
|
|
id INTEGER PRIMARY KEY,
|
|
name NOT NULL,
|
|
weight INTEGER NOT NULL,
|
|
color NOT NULL
|
|
);
|
|
|
|
INSERT INTO severity VALUES (1, 'severe', 100, 'orange');
|
|
INSERT INTO severity VALUES (2, 'blocking', 200, 'red');
|
|
INSERT INTO severity VALUES (3, 'maintenance', 1000, 'blue');
|
|
|
|
CREATE TABLE application (
|
|
id INTEGER PRIMARY KEY,
|
|
name TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE incident (
|
|
id INTEGER PRIMARY KEY,
|
|
title NOT NULL,
|
|
description NOT NULL,
|
|
estimated_duration NOT NULL,
|
|
who_is_affected NOT NULL,
|
|
date_time DATETIME NOT NULL,
|
|
severity INTEGER NOT NULL REFERENCES severity(id),
|
|
status CHECK(status IN ('DETECTION', 'ANALYSIS', 'RESOLUTION', 'CLOSED')) NOT NULL
|
|
);
|
|
|
|
CREATE TABLE incident_info(
|
|
id INTEGER PRIMARY KEY,
|
|
incident_id INTEGER NOT NULL REFERENCES incident(id),
|
|
description NOT NULL,
|
|
status CHECK(status IN ('DETECTION', 'ANALYSIS', 'RESOLUTION', 'CLOSED')) NOT NULL
|
|
);
|
|
|
|
CREATE TABLE incident_application (
|
|
incident_id INTEGER NOT NULL REFERENCES incident(id),
|
|
application_id INTEGER NOT NULL REFERENCES application(id),
|
|
PRIMARY KEY (incident_id,application_id)
|
|
) WITHOUT ROWID;
|
|
|
|
CREATE VIEW application_status AS
|
|
SELECT a.name, s.name as severity, i.title AS incident_title, MAX(s.WEIGHT), s.color AS severity_color FROM application a
|
|
LEFT JOIN incident_application ia ON a.id = ia.application_id
|
|
LEFT JOIN incident i ON ia.incident_id = i.id
|
|
AND i.status <> 'CLOSED'
|
|
LEFT JOIN severity s ON i.severity = s.id
|
|
GROUP BY a.name
|
|
; |