Création des tables et des views

Nous rejoindre sur Discord : lien discord

Les tables regroupent toutes les informations lors de l'utilisation du projet All in One, les views permettent de regrouper les informations de plusieurs tables en une seule.

Création des tables

Respectez l'ordre d'exécution des requêtes !

Pour la création des tables, vous pouvez exécuté chacune des requêtes à la suite ou bien utiliser le fichier suivant : http://images.xelyos.fr/all-in-one-project/allinone.sql (ne fonctionne pas pour le moment)

personnes : Informations sur chaque citoyen recensé sur le serveur

CREATE TABLE IF NOT EXISTS `personnes` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `nom` varchar(32) NOT NULL,
  `prenom` varchar(32) NOT NULL,
  `DOB` DATE NOT NULL,
  `phone` varchar(32),
  `nationality` varchar(32) NOT NULL,
  `sexe` ENUM('M','F') NOT NULL DEFAULT 'M',
  `grp_sanguin` varchar(32),
  `job` varchar(32),
  `ppa` ENUM('0','1') NOT NULL DEFAULT '0',
  `permis` ENUM('0','1') NOT NULL DEFAULT '0',
  `donneur` ENUM('0','1') NOT NULL DEFAULT '0',
  `date_permis` INTEGER NULL DEFAULT NULL,
  `photo` varchar(1024) NOT NULL,
  `present` ENUM('0','1') NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
);

lspd_delit : Délits

CREATE TABLE IF NOT EXISTS `lspd_delit` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `intitule` varchar(128) NOT NULL,
  `amende` INTEGER,
  `temps_prison` INTEGER,
  `type_delit` ENUM('0','1','2','3') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);

ems_liste_intervention : Types d'intervention par les secours

CREATE TABLE IF NOT EXISTS `ems_liste_intervention` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `intitule` varchar(512) NOT NULL,
  PRIMARY KEY (`id`)
);

lspd_categorie_arme : Catégories des armes

CREATE TABLE IF NOT EXISTS `lspd_categorie_arme` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `nom` varchar(128) NOT NULL,
  `prefix` varchar(4) NOT NULL,
  PRIMARY KEY (`id`)
);

lspd_liste_arme : Armes recensables

CREATE TABLE IF NOT EXISTS `lspd_liste_arme` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `nom` varchar(32) NOT NULL,
  `categorie` INTEGER NOT NULL,
  `photo` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (categorie) REFERENCES lspd_categorie_arme(id)
);

lspd_grade : Grade cop

CREATE TABLE IF NOT EXISTS `lspd_grade` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `nom` varchar(32)  NOT NULL,
  `position` INT(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`, `position`)
);

ems_grade : Grade secours

CREATE TABLE IF NOT EXISTS `ems_grade` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `nom` varchar(32)  NOT NULL,
  `position` INT(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`, `position`)
);

lspd_policier : Informations sur les cop

CREATE TABLE IF NOT EXISTS `lspd_policier` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `matricule` varchar(32) NOT NULL UNIQUE,
  `Passwd` varchar(128) NOT NULL,
  `Sel_de_table` varchar(32) NOT NULL,
  `personne` INTEGER NOT NULL UNIQUE,
  `rang` INTEGER NOT NULL DEFAULT '1',
  `note` varchar(64) DEFAULT NULL,
  `lspd_admin` ENUM('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`, `matricule`),
  FOREIGN KEY (personne) REFERENCES personnes(id),
  FOREIGN KEY (rang) REFERENCES lspd_grade(id)
);

ems_hopital : Informations sur les ems

CREATE TABLE IF NOT EXISTS `ems_hopital` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `matricule` varchar(32) NOT NULL UNIQUE,
  `Passwd` varchar(128) NOT NULL,
  `Sel_de_table` varchar(32) NOT NULL,
  `personne` INTEGER NOT NULL UNIQUE,
  `rang` INTEGER NOT NULL DEFAULT '1',
  `note` varchar(64) DEFAULT NULL,
  `admin_ems` ENUM('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`, `matricule`),
  FOREIGN KEY (personne) REFERENCES personnes(id),
  FOREIGN KEY (rang) REFERENCES ems_grade(id)
);

lspd_arme : Armes recensées

CREATE TABLE IF NOT EXISTS `lspd_arme` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `numero` varchar(4) NOT NULL UNIQUE,
  `type` INTEGER NOT NULL,
  `proprio` INTEGER NOT NULL,
  `enregistrer_par` INTEGER NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (proprio) REFERENCES personnes(id),
  FOREIGN KEY (type) REFERENCES lspd_liste_arme(id),
  FOREIGN KEY (enregistrer_par) REFERENCES lspd_policier(id)
);

lspd_historique_connexion : Historique de connexion des agents cop

CREATE TABLE IF NOT EXISTS `lspd_historique_connexion` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `matricule_utilise` varchar(32) NOT NULL,
  `mpd_utilise` varchar(128) NOT NULL,
  `adresse_ip` varchar(32) NOT NULL,
  `etat` ENUM('Réussite','Echec') NOT NULL DEFAULT 'Echec',
  `commentaire` varchar(128) NOT NULL,
  `date_connexion` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
);

ems_historique_connexion : Historique de connexion des agents secours

CREATE TABLE IF NOT EXISTS `ems_historique_connexion` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `matricule_utilise` varchar(32) NOT NULL,
  `mpd_utilise` varchar(128) NOT NULL,
  `adresse_ip` varchar(32) NOT NULL,
  `etat` ENUM('Réussite','Echec') NOT NULL DEFAULT 'Echec',
  `commentaire` varchar(128) NOT NULL,
  `date_connexion` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
);

ems_liste_medicament : Médicaments disponibles

CREATE TABLE IF NOT EXISTS `ems_liste_medicament` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `nom` varchar(128) NOT NULL,
  `description` varchar(1024)  NOT NULL,
  PRIMARY KEY (`id`)
);

modeles_vehicules : Modèles des véhicules recensables

CREATE TABLE IF NOT EXISTS `modeles_vehicules` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `nom` varchar(128) NOT NULL,
  `lien` varchar(1024),
  PRIMARY KEY (`id`)
);

vehicules : Véhicules recensés

CREATE TABLE IF NOT EXISTS `vehicules` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `modeles` INTEGER NOT NULL,
  `couleur` varchar(32) NOT NULL,
  `plaque` varchar(32) NOT NULL,
  `proprietaire` INTEGER NOT NULL,
  `enregistre_par` INTEGER NOT NULL,
  `en_circulation` ENUM('0','1') NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  FOREIGN KEY (modeles) REFERENCES modeles_vehicules(id),
  FOREIGN KEY (proprietaire) REFERENCES personnes(id),
  FOREIGN KEY (enregistre_par) REFERENCES lspd_policier(id)
);

lspd_habilitation : Habilitations attribuées à un cop

CREATE TABLE IF NOT EXISTS `lspd_habilitation` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `matricule` INTEGER NOT NULL NOT NULL UNIQUE,
  `hab_1` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_2` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_3` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_4` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_5` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_6` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_7` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_8` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_9` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_10` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_11` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_12` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_13` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_14` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_15` ENUM('0','1','2') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  FOREIGN KEY (matricule) REFERENCES lspd_policier(id)
);

ems_habilitation : Habilitations attribuées à un secours

CREATE TABLE IF NOT EXISTS `ems_habilitation` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `matricule` INTEGER NOT NULL NOT NULL UNIQUE,
  `hab_1` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_2` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_3` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_4` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_5` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_6` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_7` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_8` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_9` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_10` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_11` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_12` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_13` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_14` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_15` ENUM('0','1','2') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  FOREIGN KEY (matricule) REFERENCES ems_hopital(id)
);

lspd_autorisation : Autorisations attribuées à un cop (inutilisé pour le moment mais nécessaire)

CREATE TABLE IF NOT EXISTS `lspd_autorisation` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `matricule` INTEGER NOT NULL,
  `add_perm` ENUM('0','1','2','3','4','5') NOT NULL DEFAULT '0',
  `edit_perm` ENUM('0','1','2','3','4','5') NOT NULL DEFAULT '0',
  `delete_perm` ENUM('0','1','2','3','4','5') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  FOREIGN KEY (matricule) REFERENCES lspd_policier(id)
);

lspd_plainte : Plaintes déposées par les citoyens

CREATE TABLE IF NOT EXISTS `lspd_plainte` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `deposeur` INTEGER NOT NULL,
  `plainte_sur` INTEGER,
  `detail_plainte` LONGTEXT,
  `enregistrer_par` INTEGER NOT NULL,
  `enregistrer_le` DATE NOT NULL,
  `etat` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `fermer_par` INTEGER,
  `fermer_le` DATE,
  PRIMARY KEY (`id`),
  FOREIGN KEY (deposeur) REFERENCES personnes(id),
  FOREIGN KEY (enregistrer_par) REFERENCES lspd_policier(id),
  FOREIGN KEY (fermer_par) REFERENCES lspd_policier(id)
);

lspd_route : Délits routiers

CREATE TABLE IF NOT EXISTS `lspd_route` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `vehicule` INTEGER NOT NULL,
  `conducteur` INTEGER NOT NULL,
  `numero_delit` INTEGER NOT NULL,
  `enregistrer_par` INTEGER NOT NULL,
  `enregistrer_le` DATE NOT NULL,
  `acquite` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `acquite_par` INTEGER,
  `acquite_le` DATE,
  `remarque` LONGTEXT,
  PRIMARY KEY (`id`),
  FOREIGN KEY (vehicule) REFERENCES vehicules(id),
  FOREIGN KEY (conducteur) REFERENCES personnes(id),
  FOREIGN KEY (numero_delit) REFERENCES lspd_delit(id),
  FOREIGN KEY (enregistrer_par) REFERENCES lspd_policier(id),
  FOREIGN KEY (acquite_par) REFERENCES lspd_policier(id)
);

lspd_casier : Délits civils

CREATE TABLE IF NOT EXISTS `lspd_casier` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `personne` INTEGER NOT NULL,
  `numero_delit` INTEGER NOT NULL,
  `enregistrer_par` INTEGER NOT NULL,
  `enregistrer_le` DATE NOT NULL,
  `acquite` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `acquite_par` INTEGER,
  `acquite_le` DATE,
  `remarque` LONGTEXT,
  PRIMARY KEY (`id`),
  FOREIGN KEY (personne) REFERENCES personnes(id),
  FOREIGN KEY (numero_delit) REFERENCES lspd_delit(id),
  FOREIGN KEY (enregistrer_par) REFERENCES lspd_policier(id),
  FOREIGN KEY (acquite_par) REFERENCES lspd_policier(id)
);

ems_intervention : Interventions médicales sur civil

CREATE TABLE IF NOT EXISTS `ems_intervention` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `personne` INTEGER NOT NULL,
  `numero_intervention` INTEGER NOT NULL,
  `enregistrer_par` INTEGER NOT NULL,
  `enregistrer_le` DATE NOT NULL,
  `remarque` LONGTEXT,
  PRIMARY KEY (`id`),
  FOREIGN KEY (personne) REFERENCES personnes(id),
  FOREIGN KEY (numero_intervention) REFERENCES ems_liste_intervention(id),
  FOREIGN KEY (enregistrer_par) REFERENCES ems_hopital(id)
);

ems_arret_travail : Arrêts de travail pour un civil

CREATE TABLE IF NOT EXISTS `ems_arret_travail` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `personne` INTEGER NOT NULL,
  `motif` LONGTEXT,
  `enregistrer_par` INTEGER NOT NULL,
  `enregistrer_le` DATE NOT NULL,
  `fin_le` DATE NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (personne) REFERENCES personnes(id),
  FOREIGN KEY (enregistrer_par) REFERENCES ems_hopital(id)
);

ems_certificat_ppa : Certificats médicaux pour le port d'arme

CREATE TABLE IF NOT EXISTS `ems_certificat_ppa` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `personne` INTEGER NOT NULL,
  `etat_ppa` ENUM('0','1') NOT NULL DEFAULT '0',
  `rapport` LONGTEXT,
  `enregistrer_par` INTEGER NOT NULL,
  `enregistrer_le` DATE NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (personne) REFERENCES personnes(id),
  FOREIGN KEY (enregistrer_par) REFERENCES ems_hopital(id)
);

ems_certificat_travail : Certificats de travail

CREATE TABLE IF NOT EXISTS `ems_certificat_travail` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `personne` INTEGER NOT NULL,
  `etat_job` ENUM('0','1') NOT NULL DEFAULT '0',
  `job_vise` varchar(64) NOT NULL,
  `motif` LONGTEXT,
  `enregistrer_par` INTEGER NOT NULL,
  `enregistrer_le` DATE NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (personne) REFERENCES personnes(id),
  FOREIGN KEY (enregistrer_par) REFERENCES ems_hopital(id)
);

ems_ordonnance : Ordonnances attribuées à un civil

CREATE TABLE IF NOT EXISTS `ems_ordonnance` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `patient` INTEGER NOT NULL,
  `enregistrer_par` INTEGER NOT NULL,
  `enregistrer_le` DATE NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (patient) REFERENCES personnes(id),
  FOREIGN KEY (enregistrer_par) REFERENCES ems_hopital(id)
);

ems_details_ordonnance : Médicaments d'une ordonnance

CREATE TABLE IF NOT EXISTS `ems_details_ordonnance` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `n_ordonnance` INTEGER NOT NULL,
  `nom_medicament` INTEGER NOT NULL,
  `quantite` INTEGER NOT NULL,
  `periode` INTEGER NOT NULL,
  `frequence` ENUM('1','2','3','4','5','6','7') NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  FOREIGN KEY (n_ordonnance) REFERENCES ems_ordonnance(id),
  FOREIGN KEY (nom_medicament) REFERENCES ems_liste_medicament(id)
);

lspd_rapport : Rapport interne cop

CREATE TABLE IF NOT EXISTS `lspd_rapport` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `concerne` INTEGER NOT NULL,
  `editeur` INTEGER NOT NULL,
  `titre` varchar(128) NOT NULL,
  `contenu` LONGTEXT,
  `ecrit_le` DATE NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (concerne) REFERENCES lspd_policier(id),
  FOREIGN KEY (editeur) REFERENCES lspd_policier(id)
);

lspd_rapport_vu : Rapport cop vu par un agent responsable

CREATE TABLE IF NOT EXISTS `lspd_rapport_vu` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `policier_id` INTEGER NOT NULL,
  `rapport_id` INTEGER NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (policier_id) REFERENCES lspd_policier(id),
  FOREIGN KEY (rapport_id) REFERENCES lspd_rapport(id)
);

lspd_historique : Historique des actions cop

CREATE TABLE IF NOT EXISTS `lspd_historique` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `matricule` INTEGER NOT NULL,
  `contenu` varchar(1024) DEFAULT 'Erreur dans le processus',
  `date_even` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
);

ems_historique : Historique des actions secours

CREATE TABLE IF NOT EXISTS `ems_historique` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `matricule` INTEGER NOT NULL,
  `contenu` varchar(1024) DEFAULT 'Erreur dans le processus',
  `date_even` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
);

lspd_candidature : Candidature pour rejoindre les cop

CREATE TABLE IF NOT EXISTS `lspd_candidature` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `discord_id` varchar(32) NOT NULL,
  `nom` varchar(32) NOT NULL,
  `prenom` varchar(32) NOT NULL,
  `phone` varchar(32) NOT NULL,
  `age_IG` INTEGER NOT NULL,
  `age_IRL` INTEGER NOT NULL,
  `tmps_serveur` INTEGER NOT NULL,
  `tmps_life` INTEGER NOT NULL,
  `ancien_flic` ENUM('0','1') NOT NULL DEFAULT '0',
  `detail_flic` varchar(128),
  `dispo_ecole` ENUM('0','1') NOT NULL DEFAULT '0',
  `detail_ecole` varchar(64),
  `dispo_vacance` ENUM('0','1') NOT NULL DEFAULT '0',
  `detail_vacance` varchar(64),
  `dispo_travail` ENUM('0','1') NOT NULL DEFAULT '0',
  `detail_travail` varchar(64),
  `objectif_lspd` LONGTEXT,
  `motivation_lspd` varchar(1024),
  `reponse_intervention` INTEGER NOT NULL,
  `reponse_k9` varchar(64) NOT NULL,
  `concat` varchar(32) NOT NULL,
  `date_depot` DATE NOT NULL,
  `etat_act` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `adress_ip` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
);

ems_candidature : Candidature pour rejoindre les secours

CREATE TABLE IF NOT EXISTS `ems_candidature` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `discord_id` varchar(32) NOT NULL,
  `nom` varchar(32) NOT NULL,
  `prenom` varchar(32) NOT NULL,
  `phone` varchar(32) NOT NULL,
  `age_IG` INTEGER NOT NULL,
  `age_IRL` INTEGER NOT NULL,
  `tmps_serveur` INTEGER NOT NULL,
  `tmps_life` INTEGER NOT NULL,
  `ancien_flic` ENUM('0','1') NOT NULL DEFAULT '0',
  `detail_flic` varchar(32),
  `dispo_ecole` ENUM('0','1') NOT NULL DEFAULT '0',
  `detail_ecole` varchar(64),
  `dispo_vacance` ENUM('0','1') NOT NULL DEFAULT '0',
  `detail_vacance` varchar(64),
  `dispo_travail` ENUM('0','1') NOT NULL DEFAULT '0',
  `detail_travail` varchar(64),
  `objectif_lspd` varchar(512),
  `motivation_lspd` LONGTEXT,
  `reponse_intervention` INTEGER NOT NULL,
  `reponse_k9` varchar(64) NOT NULL,
  `concat` varchar(32) NOT NULL,
	`date_depot` DATE NOT NULL,
  `etat_act` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `adress_ip` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
);

chat : Chat inter-services

CREATE TABLE IF NOT EXISTS chat (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `author` varchar(128) NOT NULL,
  `message` varchar(512) NOT NULL,
  `side` ENUM('cop','ems') NOT NULL,
  `send_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Création des Views

info_lspd

CREATE OR REPLACE VIEW info_lspd AS
  SELECT
    personnes.id AS user_id, lspd_policier.id AS lspd_id, lspd_policier.matricule AS matricule, personnes.nom AS nom,
    personnes.prenom AS prenom, personnes.phone AS telephone, personnes.nationality AS nationality,
    personnes.photo AS photo, personnes.sexe AS sexe, lspd_grade.nom AS `grade`, lspd_grade.position AS `grade_id`,
    lspd_policier.note AS `note`, lspd_autorisation.add_perm AS ajouter, lspd_autorisation.edit_perm AS editer,
    lspd_autorisation.delete_perm AS supprimer, lspd_habilitation.hab_1, lspd_habilitation.hab_2,
    lspd_habilitation.hab_3,lspd_habilitation.hab_4, lspd_habilitation.hab_5, lspd_habilitation.hab_6, 
    lspd_habilitation.hab_7, lspd_habilitation.hab_8, lspd_habilitation.hab_9, lspd_habilitation.hab_10,
    lspd_habilitation.hab_11, lspd_habilitation.hab_12, lspd_habilitation.hab_13, lspd_habilitation.hab_14,
    lspd_habilitation.hab_15, lspd_policier.lspd_admin AS admin
  FROM
    lspd_policier, personnes, lspd_grade, lspd_autorisation, lspd_habilitation
  WHERE
    personnes.id = lspd_policier.personne AND lspd_policier.id = lspd_autorisation.matricule AND 
    lspd_policier.rang = lspd_grade.ID AND lspd_habilitation.matricule = lspd_policier.id
  ORDER BY
    lspd_grade.`position` DESC;

info_casier

CREATE OR REPLACE VIEW info_casier AS
  SELECT
  		lspd_casier.id AS id_delit, lspd_casier.personne AS id_personne, lspd_delit.intitule AS nom, 
        lspd_delit.type_delit AS type_d, lspd_delit.amende AS amende, lspd_delit.temps_prison AS prison,
        lspd_casier.enregistrer_par AS enregistrer_par, lspd_casier.enregistrer_le AS enregistrer_le, lspd_casier.acquite AS etat,
  		lspd_casier.acquite_par AS acquite_par, lspd_casier.acquite_le AS acquite_le, lspd_casier.remarque AS remarque
  	FROM
  		lspd_casier, lspd_delit
  	WHERE
  		lspd_casier.numero_delit = lspd_delit.id;

info_plainte

CREATE OR REPLACE VIEW info_plainte AS
  SELECT
    lspd_plainte.id AS p_id, personnes.nom AS nom_1, personnes.prenom AS prenom_1, personnes.photo AS lien,
    lspd_plainte.detail_plainte AS detail, lspd_plainte.etat AS etat
  FROM
    lspd_plainte, personnes
  WHERE
    lspd_plainte.plainte_sur = personnes.id;

info_route

CREATE OR REPLACE VIEW info_route AS
  SELECT
  		lspd_route.id AS delit_id, lspd_route.vehicule AS v_id, lspd_route.conducteur AS conducteur_id, 
        lspd_delit.intitule AS nom, lspd_delit.amende AS amende, lspd_delit.temps_prison AS prison, lspd_delit.id AS delit_ref, 
        lspd_route.enregistrer_par AS enregistreur_id, lspd_route.acquite AS etat, lspd_route.enregistrer_le AS date_enregistre, 
        lspd_route.acquite_par AS acquite_par, lspd_route.acquite_le AS acquite_le, lspd_route.remarque AS remarque
  	FROM
  		lspd_route, lspd_delit
  	WHERE
  		lspd_route.numero_delit = lspd_delit.id;

info_voiture

CREATE OR REPLACE VIEW info_voiture AS
  SELECT
  		vehicules.id AS v_id, modeles_vehicules.nom AS nom, modeles_vehicules.lien AS lien, vehicules.couleur AS couleur, 
        vehicules.plaque AS plaque, vehicules.proprietaire AS proprio,
  		vehicules.enregistre_par AS enregistreur, vehicules.en_circulation AS circulation
  	FROM
  		modeles_vehicules, vehicules
  	WHERE
  		vehicules.modeles = modeles_vehicules.id;

info_ems

CREATE OR REPLACE VIEW info_ems AS
  SELECT
    personnes.id AS user_id, ems_hopital.id AS ems_id, ems_hopital.matricule AS matricule, personnes.nom AS nom, personnes.prenom AS 
    prenom, personnes.phone AS telephone, personnes.nationality AS nationality, personnes.photo AS photo, personnes.sexe AS sexe, 
    personnes.grp_sanguin AS sang, personnes.donneur AS donneur, ems_grade.nom AS `grade`, ems_grade.`position` AS `grade_id`, 
    ems_hopital.note AS `note`, ems_habilitation.hab_1, ems_habilitation.hab_2, ems_habilitation.hab_3, ems_habilitation.hab_4, 
    ems_habilitation.hab_5, ems_habilitation.hab_6, ems_habilitation.hab_7, ems_habilitation.hab_8,
    ems_habilitation.hab_9, ems_habilitation.hab_10, ems_habilitation.hab_11, ems_habilitation.hab_12, ems_habilitation.hab_13, 
    ems_habilitation.hab_14, ems_habilitation.hab_15,
    ems_hopital.admin_ems AS admin
  FROM
    ems_hopital, personnes, ems_grade, ems_habilitation
  WHERE
    personnes.id = ems_hopital.personne  AND ems_hopital.rang = ems_grade.id AND ems_habilitation.matricule = ems_hopital.id
  ORDER BY
    ems_grade.`position` DESC;

info_intervention

CREATE OR REPLACE VIEW info_intervention AS
  SELECT
    ems_intervention.id AS inter_id, personnes.id AS id_civil, personnes.nom, personnes.prenom AS prenom, 
    ems_intervention.numero_intervention AS num_inter, ems_liste_intervention.intitule AS nom_inter,
    ems_intervention.enregistrer_par AS enregistre_par, ems_intervention.enregistrer_le AS enregistrer_le, ems_intervention.remarque AS rapport
	FROM
		ems_intervention, personnes, ems_liste_intervention
	where
		ems_intervention.personne = personnes.id AND ems_liste_intervention.id = ems_intervention.numero_intervention
	ORDER BY inter_id;

info_rapport

CREATE OR REPLACE VIEW info_rapport AS
	SELECT
		lspd_rapport.id, info_lspd.grade, info_lspd.nom, info_lspd.photo, lspd_rapport.titre, lspd_rapport.editeur, info_lspd.lspd_id
	FROM
		info_lspd, lspd_rapport
	WHERE
		lspd_rapport.concerne = info_lspd.lspd_id;

info_ordonnance

CREATE OR REPLACE VIEW info_ordonnance AS
	SELECT
		ems_ordonnance.id AS ordonnance_id, ems_details_ordonnance.nom_medicament AS id_med, ems_liste_medicament.nom AS nom_med,
        ems_liste_medicament.description AS description_med, ems_details_ordonnance.quantite AS quantite, 
        ems_details_ordonnance.periode AS periode, ems_details_ordonnance.frequence AS frequence
	FROM
		ems_ordonnance, ems_details_ordonnance, ems_liste_medicament
	WHERE
		ems_ordonnance.id = ems_details_ordonnance.n_ordonnance AND ems_details_ordonnance.nom_medicament = ems_liste_medicament.id;

info_arme

CREATE OR REPLACE VIEW info_arme AS
	SELECT
		lspd_liste_arme.nom AS nom, lspd_arme.numero, lspd_categorie_arme.nom AS categorie, lspd_categorie_arme.prefix AS prefix,
		lspd_arme.proprio AS proprio, lspd_arme.enregistrer_par AS matricule, lspd_liste_arme.photo AS photo
	FROM
		lspd_liste_arme, lspd_arme, lspd_categorie_arme
	WHERE
		lspd_arme.`type` = lspd_liste_arme.id AND lspd_liste_arme.categorie = lspd_categorie_arme.id;