filesphere/database.sql at main · kaelianbaudelet/filesphere · 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
-- Initialisation de la base de données
SET FOREIGN_KEY_CHECKS = 0;

-- Création de la table User
CREATE TABLE IF NOT EXISTS User (
    id CHAR(36) PRIMARY KEY NOT NULL DEFAULT (UUID()),
    role ENUM('admin', 'teacher', 'student') NOT NULL,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT 0,
    reset_token varchar(255) DEFAULT NULL,
    reset_token_expires_at DATETIME DEFAULT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Création de la table File
CREATE TABLE IF NOT EXISTS File (
    id CHAR(36) PRIMARY KEY NOT NULL DEFAULT (UUID()),
    token VARCHAR(255) UNIQUE,
    name VARCHAR(255) NOT NULL,
    extension VARCHAR(10),
    size INT NOT NULL,
    user_id CHAR(36) NOT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES User(id) ON DELETE CASCADE

);
-- Création de la table Class
CREATE TABLE IF NOT EXISTS Class (
    id CHAR(36) PRIMARY KEY NOT NULL DEFAULT (UUID()),
        teacher_id CHAR(36) NOT NULL,
        file_id CHAR(36) DEFAULT NULL,
        color VARCHAR(7) DEFAULT '#ffffff',
        name VARCHAR(50) NOT NULL,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (teacher_id) REFERENCES User(id) ON DELETE CASCADE,
        FOREIGN KEY (file_id) REFERENCES File(id) ON DELETE SET NULL
);

-- Création de la table ClassStudent (relation entre élèves et classes)
CREATE TABLE IF NOT EXISTS ClassStudent (
    user_id CHAR(36) NOT NULL,
    class_id CHAR(36) NOT NULL,
    PRIMARY KEY (user_id, class_id),
    FOREIGN KEY (user_id) REFERENCES User(id) ON DELETE CASCADE,
    FOREIGN KEY (class_id) REFERENCES Class(id) ON DELETE CASCADE
);

-- Création de la table Section
CREATE TABLE IF NOT EXISTS Section (
    id CHAR(36) PRIMARY KEY NOT NULL DEFAULT (UUID()),
    name VARCHAR(255) NOT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Création de la table assignment
CREATE TABLE IF NOT EXISTS Assignment (
    id CHAR(36) PRIMARY KEY NOT NULL DEFAULT (UUID()),
    name VARCHAR(255) NOT NULL,
    description TEXT DEFAULT NULL,
    start_period DATETIME NOT NULL,
    end_period DATETIME NOT NULL,
    max_files INT DEFAULT 1,
    allow_late_submission BOOLEAN DEFAULT 0,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- Création de la table ClassSection (relation entre classes et sections)
CREATE TABLE IF NOT EXISTS ClassSection (
    section_id CHAR(36) NOT NULL,
    class_id CHAR(36) NOT NULL,
    PRIMARY KEY (section_id, class_id),
    FOREIGN KEY (section_id) REFERENCES Section(id) ON DELETE CASCADE,
    FOREIGN KEY (class_id) REFERENCES Class(id) ON DELETE CASCADE
);

-- Création de la table Sectionassignment (relation entre sections et devoirs)
CREATE TABLE IF NOT EXISTS SectionAssignment (
    section_id CHAR(36) NOT NULL,
    assignment_id CHAR(36) NOT NULL,
    PRIMARY KEY (section_id, assignment_id),
    FOREIGN KEY (section_id) REFERENCES Section(id) ON DELETE CASCADE,
    FOREIGN KEY (assignment_id) REFERENCES Assignment(id) ON DELETE CASCADE
);

-- Création de la table assignmentFile (relation entre devoirs et fichiers)
CREATE TABLE IF NOT EXISTS AssignmentFile (
    assignment_id CHAR(36) NOT NULL,
    file_id CHAR(36) NOT NULL,
    PRIMARY KEY (assignment_id, file_id),
    FOREIGN KEY (assignment_id) REFERENCES Assignment(id) ON DELETE CASCADE,
    FOREIGN KEY (file_id) REFERENCES File(id) ON DELETE CASCADE
);

-- Création de la table assignmentInstructionFile (relation entre devoirs et fichiers)
CREATE TABLE IF NOT EXISTS AssignmentInstructionFile (
    assignment_id CHAR(36) NOT NULL,
    file_id CHAR(36) NOT NULL,
    PRIMARY KEY (assignment_id, file_id),
    FOREIGN KEY (assignment_id) REFERENCES Assignment(id) ON DELETE CASCADE,
    FOREIGN KEY (file_id) REFERENCES File(id) ON DELETE CASCADE
);