Contraintes.sql

-- CREATION DES CONTRAINTES

	-- Pour la table Vulnerabilite :

-- Plage de Score CVSS (0.0 à 10.0)
ALTER TABLE Vulnerabilite DROP CONSTRAINT chk_cvss_score;
ALTER TABLE Vulnerabilite
ADD CONSTRAINT chk_cvss_score CHECK (Score_CVSS BETWEEN 0 AND 10);

-- Cohérence des dates :
ALTER TABLE Vulnerabilite DROP CONSTRAINT chk_vuln_date;
ALTER TABLE Vulnerabilite
ADD CONSTRAINT chk_vuln_date CHECK (Date_PUB < Date_MAJ);

-- Domaine des valeurs de Gravite :
ALTER TABLE Vulnerabilite DROP CONSTRAINT chk_gravite_domaine;
ALTER TABLE Vulnerabilite
ADD CONSTRAINT chk_gravite_domaine CHECK (Gravite IN ('FAIBLE', 'MOYENNE', 'ELEVEE', 'CRITIQUE'));

-- Format de CVE (CVE-AAAA-NNNN) :
ALTER TABLE Vulnerabilite DROP CONSTRAINT chk_cve_format;
ALTER TABLE Vulnerabilite
ADD CONSTRAINT chk_cve_format CHECK (REGEXP_LIKE(CVE_Id, '^CVE-\d{4}-\d{4,10}$'));

-- Plage de Score CVSS (0.0 à 10.0):
-- Triggers
CREATE OR REPLACE TRIGGER trg_update_gravite
BEFORE INSERT OR UPDATE OF Score_CVSS ON Vulnerabilite
FOR EACH ROW
BEGIN
    IF :NEW.Score_CVSS IS NOT NULL THEN
        IF :NEW.Score_CVSS >= 9.0 THEN
            :NEW.Gravite := 'CRITIQUE';
        ELSIF :NEW.Score_CVSS >= 7.0 THEN
            :NEW.Gravite := 'ELEVEE';
        ELSIF :NEW.Score_CVSS >= 4.0 THEN
            :NEW.Gravite := 'MOYENNE';
        ELSE
            :NEW.Gravite := 'FAIBLE';
        END IF;
    END IF;
END;
/


	-- Pour la table Auteur :
	
-- Unicité du nom
ALTER TABLE Auteur DROP CONSTRAINT uk_auteur_nom;
ALTER TABLE Auteur
ADD CONSTRAINT uk_auteur_nom UNIQUE (Nom);

-- Unicité de l'email :
ALTER TABLE Auteur DROP CONSTRAINT uk_auteur_email;
ALTER TABLE Auteur
ADD CONSTRAINT uk_auteur_email UNIQUE (Email);

-- Format de l'email :
ALTER TABLE Auteur DROP CONSTRAINT chk_auteur_email_format;
ALTER TABLE Auteur
ADD CONSTRAINT chk_auteur_email_format CHECK (REGEXP_LIKE(Email, '^\S+@\S+\.\S+$'));

-- suppression auteur:
-- Triggers
CREATE OR REPLACE TRIGGER trg_delete_auteur
BEFORE DELETE ON Auteur
FOR EACH ROW
BEGIN
	IF :OLD.Nb_exploits > 0 THEN
		RAISE_APPLICATION_ERROR(-20001, 'supression impossible, pressence de publication');
	END IF;
END;
/


	-- Pour la table Exploit :

-- Ensemble des Tags :
ALTER TABLE Exploit DROP CONSTRAINT chk_exploit_tag;
ALTER TABLE Exploit
ADD CONSTRAINT chk_exploit_tag CHECK (Tag IN ('remote', 'local', 'dos', 'webapp', 'privilege escalation',
'rce', 'sqli', 'xss', 'clientside', 'kernel', 'network', 
'clientside', 'overflow', 'hardware', 'bypass', 'bruteforce', 
'fuzzing', 'iot', 'browser', 'mobile', 'data leakage'));

-- Coerence des date
ALTER TABLE Exploit DROP CONSTRAINT chk_exploit_date;
ALTER TABLE Exploit
ADD CONSTRAINT chk_exploit_date CHECK (Date_pub < Date_MAJ);

-- Coerence des date avec celle des vulnerabilite:
-- Triggers
CREATE OR REPLACE TRIGGER chk_exploit_date_vul
BEFORE INSERT OR UPDATE OF Date_pub ON Exploit
FOR EACH ROW
DECLARE
	v_date DATE;
BEGIN
	SELECT date_pub INTO v_date FROM Vulnerabilite WHERE CVE_Id = :NEW.CVE_Id;
	IF :NEW.date_pub < v_date THEN 
		RAISE_APPLICATION_ERROR(-20002, 'date de publication incompatible');
	END IF;
END;
/

-- Contrainte de format pour PoC (format URL) :
-- Triggers
CREATE OR REPLACE TRIGGER trg_update_poc
BEFORE INSERT OR UPDATE OF PoC ON Exploit
FOR EACH ROW
BEGIN
    :NEW.date_MAJ := CURRENT_DATE;
END;
/


	-- Pour la table Fournisseur :
	
-- Format de l'URL du site web (doit commencer par http:// ou https://) :
ALTER TABLE Fournisseur DROP CONSTRAINT chk_url_fournisseur_format;
ALTER TABLE Fournisseur
ADD CONSTRAINT chk_url_fournisseur_format CHECK (REGEXP_LIKE(Siteweb, 'http[s]?:\/\/[^\s]*'));

-- Nombre d'employés doit être un entier positif :
ALTER TABLE Fournisseur DROP CONSTRAINT chk_nb_employe_fournisseur;
ALTER TABLE Fournisseur
ADD CONSTRAINT chk_nb_employe_fournisseur CHECK (Nb_employer > 0);


	-- Pour la table Version_P

-- Format de version
--ALTER TABLE Version_P DROP CONSTRAINT chk_version_format;
--ALTER TABLE Version_P
--ADD CONSTRAINT chk_version_format CHECK (REGEXP_LIKE(Version, '^[A-Za-z0-9\.\-]+$'));

-- Coerence des date avec celle des Produit:
-- Triggers
CREATE OR REPLACE TRIGGER chk_V_produit_date
BEFORE INSERT OR UPDATE OF Date_pub ON Version_P
FOR EACH ROW
DECLARE
	v_date DATE;
BEGIN
	SELECT date_pub INTO v_date FROM Produit WHERE Id_produit = :NEW.Id_produit;
	IF :NEW.date_pub < v_date THEN 
		RAISE_APPLICATION_ERROR(-20002, 'date de publication incompatible');
	END IF;
END;
/