SR10/createDB.sql at main · pierreadorni/SR10 · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
DROP DATABASE IF EXISTS SR10;
CREATE DATABASE SR10;

USE SR10;

CREATE TABLE Organisation
(
    siren      VARCHAR(255) NOT NULL,
    nom        VARCHAR(255) NOT NULL,
    rue        VARCHAR(255) NOT NULL,
    ville      VARCHAR(255) NOT NULL,
    region     VARCHAR(255) NOT NULL,
    codePostal INT          NOT NULL,
    pays       VARCHAR(255) NOT NULL,
    validated  BOOLEAN      NOT NULL DEFAULT FALSE,
    PRIMARY KEY (siren)
);

CREATE TABLE Utilisateur
(
    id              INT                                              NOT NULL AUTO_INCREMENT,
    typeUtilisateur ENUM ('Administrateur', 'Recruteur', 'Candidat') NOT NULL,
    email           VARCHAR(255)                                     NOT NULL,
    nom             VARCHAR(255)                                     NOT NULL,
    prenom          VARCHAR(255)                                     NOT NULL,
    dateCreation    DATE                                             NOT NULL,
    statutCompte    ENUM ('actif', 'inactif', 'bannis')              NOT NULL,
    mdpHash         VARCHAR(255)                                     NOT NULL,
    organisation    VARCHAR(255),
    PRIMARY KEY (id),
    FOREIGN KEY (organisation) REFERENCES Organisation (siren) ON DELETE CASCADE,
    #on delete cascade
    CHECK ((typeUtilisateur IN ('Administrateur', 'Candidat') AND organisation IS NULL) OR
           (typeUtilisateur = 'Recruteur' AND organisation IS NOT NULL))

);

CREATE TABLE FichePoste
(
    id              INT          NOT NULL AUTO_INCREMENT,
    organisation    VARCHAR(255),
    dateUpload      DATE         NOT NULL DEFAULT (CURRENT_DATE),
    intitule        VARCHAR(255) NOT NULL,
    responsable     VARCHAR(255) NOT NULL,
    typeMetier      VARCHAR(255) NOT NULL,
    rythme          VARCHAR(255) NOT NULL,
    fourchetteBasse INT          NOT NULL,
    fourchetteHaute INT          NOT NULL,
    description     TEXT         NOT NULL,
    localisation    VARCHAR(255) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (organisation) REFERENCES Organisation (siren) ON DELETE CASCADE
);

CREATE TABLE Offre
(
    numeroOffre INT  NOT NULL AUTO_INCREMENT,
    dateUpload  DATE NOT NULL DEFAULT (CURRENT_DATE),
    fichePoste  INT  NOT NULL,
    PRIMARY KEY (numeroOffre),
    FOREIGN KEY (fichePoste) REFERENCES FichePoste (id) ON DELETE CASCADE
);

CREATE TABLE DossierCandidature
(
    id              INT                                                                 NOT NULL AUTO_INCREMENT,
    dateCandidature DATE                                                                NOT NULL DEFAULT (CURRENT_DATE),
    statut          ENUM ('brouillon', 'refusé', 'en attente de traitement', 'accepté') NOT NULL,
    utilisateur     INT,
    offre           INT,
    PRIMARY KEY (id),
    FOREIGN KEY (utilisateur) REFERENCES Utilisateur (id) ON DELETE CASCADE,
    FOREIGN KEY (offre) REFERENCES Offre (numeroOffre) ON DELETE CASCADE,
    CHECK (statut IN ('brouillon', 'refusé', 'en attente de traitement', 'accepté'))
);

CREATE TABLE Document
(
    id                 INT          NOT NULL AUTO_INCREMENT,
    dateUpload         DATE         NOT NULL,
    nom                VARCHAR(255) NOT NULL,
    type               VARCHAR(255) NOT NULL,
    dossierCandidature INT          NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (dossierCandidature) REFERENCES DossierCandidature (id) ON DELETE CASCADE
);

CREATE TABLE demandeRecruteur
(
    id           INT                                      NOT NULL AUTO_INCREMENT,
    dateDemande  DATE                                     NOT NULL DEFAULT (CURRENT_DATE),
    statut       ENUM ('en attente', 'refuse', 'accepte') NOT NULL DEFAULT 'en attente',
    organisation VARCHAR(255)                             NOT NULL,
    utilisateur  INT                                      NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (organisation) REFERENCES Organisation (siren) ON DELETE CASCADE,
    FOREIGN KEY (utilisateur) REFERENCES Utilisateur (id) ON DELETE CASCADE,
    CHECK (statut IN ('en attente', 'refuse', 'accepte'))
);

CREATE TABLE FichierCandidature
(
    id                 INT          NOT NULL AUTO_INCREMENT,
    dateUpload         DATE         NOT NULL,
    path               VARCHAR(255) NOT NULL,
    originalName       VARCHAR(255) NOT NULL,
    dossierCandidature INT          NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (dossierCandidature) REFERENCES DossierCandidature (id) ON DELETE CASCADE
);