Activité : Base de données DMI

Base de données DMI

Prérequis : Base de données

Présentation

Le système « DMI » (Diffusion de Messages Interactifs) permet un affichage dynamique d’informations sur écran.

Ce système sera composé de plusieurs points d’affichage.

Un point d’affichage (PA) est composé :

  • d’un écran (télévision, vidéo) ou d’un bandeau lumineux (en option),
  • d’une carte RASPBERRY PI (2 B+ ou 3)
  • d’une interface réseau Ethernet filaire ou Wifi.

Tous les PA sont reliés à un serveur. La configuration du système « DMI » est réalisée via un client web.

Le serveur héberge :

  • un serveur de base de données MySQL ;
  • un serveur web et une application (HTML/CSS/PHP).

La base de données dmi

La base de données dmi est un ensemble de données permettant l’exploitation du réseau de PA. Ces données concernent :

  • les comptes d’accès : table diffuseurs
  • la liste des écrans (PA) : table ecrans
  • les types d’écran : table medias
  • les widgets associés à un écran (PA) : table widgets
  • la liste des widgets utilisables : table plugins

Remarque : les tables diffusion et messages ne sont pas utilisées dans cette version.

Un point d’affichage (PA) permet de décrire un écran à base de widgets (un élément graphique ). Les plugins forunissent des widgets utilisables sur un écran de PA :

  • label : permet d’afficher un simple texte
  • image : permet d’afficher une image (png, jpg, …)
  • animation : permet d’afficher un GIF animé
  • panel : permet d’afficher le contenu pointé par une adresse URL
  • video : permet d’afficher un flux vidéo (mp4, mpg, etc …)
  • webcam : permet d’afficher le contenu d’une webcam USB
  • bouton : permet d’activer un panel (pour les interfaces tactiles)
  • date : permet d’afficher la date sous forme de texte (format par défaut : dd/MM/yyyy)
  • horodatage : permet d’afficher l’heure et/ou la date sous forme de texte (format par défaut : HH:mm:ss dd/MM/yyyy)
  • horloge digitale : permet d’afficher l’heure sous forme digitale (format par défaut : HH:mm:ss)
  • horloge analogique : permet d’afficher l’heure sous forme analogique (aiguilles)
  • horloge analogique qwt : permet d’afficher l’heure sous une autre forme analogique (aiguilles)
  • chronometre : permet d’afficher un chronomètre
  • compte à rebours : permet d’afficher un compte à rebours

Ces données sont mémorisées dans la base de données MySQL du serveur et mises à disposition des différents PA.

Remarque : c’est un schéma relationnel partiel dont certains tables n’ont pas été représentées par souci de visibilité.

Base de données : dmi.zip

Le programme d’installation setup-dmi-www.sh installe automatiquement l’application web et la base de données sur le serveur.

Objectifs

Être capable d’exécuter des requêtes SQL sur une base de données MySQL.

Séquence 1 : installation de MySQL

Vérifier si les paquetages mysql (mysql-server, etc …) sont installés sur votre machine :

$ dpkg -l | grep -i mysql

Sinon pour installer le SGBDR MySQL, il faut faire :

$ sudo apt install mysql-server

Vérifier si le serveur mysql est démarré sur votre machine :

$ sudo service mysql status

$ sudo systemctl status mysql.service

Pour démarrer le serveur MySQL :

$ sudo service mysql start

$ sudo systemctl start mysql.service

Pour redémarrer le serveur MySQL :

$ sudo service mysql restart

$ sudo systemctl restart mysql.service

Pour arrêter le serveur MySQL :

$ sudo service mysql stop

$ sudo systemctl stop mysql.service

Démarrer la console mysql :

$ mysql -uroot -ppassword -hlocalhost
mysql>

Lister les bases de données :

mysql> show databases;

Sélectionner une base de données :

mysql> use dmi;

Lister les tables d’une base de données :

mysql> show tables;

Sélectionner des données d’une table :

mysql> select * from diffuseurs;

Lien : https://doc.ubuntu-fr.org/mysql

Séquence 1 : configuration de MySQL

Si votre application s’exécute sur la même machine que votre serveur de base de données, vous pourrez indiquer localhost comme nom de machine. Sinon, il vous faudra préciser le nom réseau ou l’adresse IP du serveur à joindre et configurer un accès distant pour cette application.

Éditer le fichier le fichier /etc/mysql/my.cnf ou /etc/mysql/mysql.conf.d/mysqld.cnf : dans la section [mysqld], indiquer pour le paramétre bind-address l’adresse IP de votre interface d’écoute ou la valeur 0.0.0.0 pour toutes les interfaces réseau de votre serveur.

Exemple :

Ajouter un utilisateur (ici rpi) pour lequel vous autorisez l’accès distant (% = pour tous les hôtes) à cette base de données :

mysql> use mysql;

mysql> select Host, User from user;

mysql> mysql> CREATE USER 'rpi'@'%' IDENTIFIED BY 'password';

mysql> GRANT ALL PRIVILEGES ON dmi.* TO 'rpi'@'%';

mysql> FLUSH PRIVILEGES;

Et pour supprimer l’utilisateur rpi :

mysql> DROP USER 'rpi'@'%'

Il faudra ensuite redémarrer le service MySQL :

$ sudo systemctl restart mysql.service

Remarque : en cas de problème, vérifier si un pare-feu (firewall) est actif et si les connexions entrantes sur le port 3306 (port d’écoute par défaut du serveur MySQL) ne sont pas bloquées (vous pouvez utiliser l’outil nmap).

// pare-feu actif ?
$ systemctl status ufw.service

// tables de filtrage ?
$ sudo iptables -L

// scan du port d'écoute MySQL ?
$ nmap -A -p3306 -T4 localhost

Séquence 2 : installation de la base de données dmi

Pour installer la base de données dmi, il faut :

  • créer la base de données dmi (CREATE DATABASE dmi)
  • créer les tables (CREATE TABLE diffuseurs ... etc …)
  • insérer des données dans les tables (INSERT INTO diffuseurs ... etc …)

L’ensemble des commandes peut être sauvegardé dans un fichier .sql:

DROP DATABASE IF EXISTS `dmi`;
CREATE DATABASE `dmi` DEFAULT CHARACTER SET utf8;
USE `dmi`;

--
-- Structure de la table `diffuseurs`
--

CREATE TABLE IF NOT EXISTS `diffuseurs` (
  `idDiffuseur` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `nom` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `droit` int(11) unsigned DEFAULT NULL,
  `etat` int(11) DEFAULT NULL,  
  PRIMARY KEY (`idDiffuseur`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Contenu de la table `diffuseur`
--

INSERT INTO `diffuseurs` (`idDiffuseur`, `nom`, `password`, `droit`, `etat`) VALUES
(1, 'admin', '5f4dcc3b5aa765d61d8327deb882cf99', 1, 1),
(2, 'dmi', '5f4dcc3b5aa765d61d8327deb882cf99', 2, 1);

--
-- Structure de la table `medias`
--

CREATE TABLE IF NOT EXISTS `medias` (
  `idMedia` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(255) DEFAULT NULL,
  --   `type` enum('video','bandeau',''),
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`idMedia`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Contenu de la table `medias`
--

INSERT INTO `medias` (`idMedia`, `type`, `description`) VALUES
(1, 'ecran', 'Vidéo'),
(2, 'ecran', 'TV');


--
-- Structure de la table `ecrans`
--

CREATE TABLE IF NOT EXISTS `ecrans` (
  `idEcran` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `nom` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `adresse` varchar(17) DEFAULT NULL,
  `ip` varchar(15) DEFAULT NULL,
  `background` varchar(16) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `etat` int(11) DEFAULT NULL,
  `presence` int(11) DEFAULT NULL,
  `refresh` int(11) DEFAULT NULL,
  `largeur` int(11) DEFAULT NULL,
  `hauteur` int(11) DEFAULT NULL,
  `idMedia` int(11) unsigned NOT NULL,  
  PRIMARY KEY (`idEcran`),
  KEY `fk_medias_idMedia1` (`idMedia`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Contraintes pour la table `ecrans`
--
ALTER TABLE `ecrans` ADD CONSTRAINT `fk_medias_idMedia1` FOREIGN KEY (`idMedia`) REFERENCES `medias` (`idMedia`) ON DELETE CASCADE;

--
-- Structure de la table `messages`
--

CREATE TABLE IF NOT EXISTS `messages` (
  `idMessage` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `heure` time NOT NULL,  
  `contenu` text DEFAULT NULL,
  `idDiffuseur` int(11) unsigned NOT NULL,
  PRIMARY KEY (`idMessage`),
  CONSTRAINT fk_ecrans_idDiffuseur
  FOREIGN KEY (idDiffuseur)
  REFERENCES diffuseurs(idDiffuseur)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Structure de la table `diffusion`
--

CREATE TABLE IF NOT EXISTS `diffusion` (
  `idDiffusion` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `dateDebut` date NOT NULL,
  `heureDebut` time NOT NULL,  
  `dateFin` date DEFAULT NULL,
  `heureFin` time DEFAULT NULL,  
  `duree` int(11) unsigned NOT NULL,
  `periode` int(11) unsigned NOT NULL,
  `etat` int(11) NOT NULL,
  `type` int(11) unsigned NOT NULL,
  `idMessage` int(11) unsigned NOT NULL,
  `idEcran` int(11) unsigned NOT NULL,
  PRIMARY KEY (`idDiffusion`),
  CONSTRAINT fk_messages_idMessage
  FOREIGN KEY (idMessage)
  REFERENCES messages(idMessage),
  CONSTRAINT fk_ecrans_idEcran1
  FOREIGN KEY (idEcran)
  REFERENCES ecrans(idEcran)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Structure de la table `plugins`
--

CREATE TABLE IF NOT EXISTS `plugins` (
  `idPlugin` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `nom` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`idPlugin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Contenu de la table `plugins`
--

INSERT INTO `plugins` (`idPlugin`, `nom`, `description`) VALUES
(1, 'horloge analogique', ''),
(2, 'horloge digitale', ''),
(3, 'date', ''),
(4, 'image', ''),
(5, 'panel', ''),
(6, 'label', ''),
(7, 'video', ''),
(8, 'horodatage', ''),
(9, 'bouton', ''),
(10, 'horloge analogique qwt', ''),
(11, 'animation', ''),
(12, 'webcam', ''),
(13, 'chronometre', ''),
(14, 'compte à rebours', '');

--
-- Structure de la table `widgets`
--

CREATE TABLE IF NOT EXISTS `widgets` (
  `uuid` varchar(255) NOT NULL,
  `ligne` int(11) unsigned DEFAULT NULL,
  `colonne` int(11) unsigned DEFAULT NULL,
  `largeur` int(11) unsigned DEFAULT NULL,
  `hauteur` int(11) unsigned DEFAULT NULL,
  `horizontal` int(11) unsigned DEFAULT NULL,
  `vertical` int(11) unsigned DEFAULT NULL,
  `etat` int(11) DEFAULT NULL,
  `periode` int(11) unsigned DEFAULT NULL,
  `idPlugin` int(11) unsigned NOT NULL,
  `idEcran` int(11) unsigned NOT NULL,
  PRIMARY KEY (`uuid`),
  KEY `fk_plugins_idPlugin` (`idPlugin`),
  KEY `fk_ecrans_idEcran2` (`idEcran`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- `horizontal` :
-- Qt::AlignLeft    0x0001  Aligns with the left edge.
-- Qt::AlignRight   0x0002  Aligns with the right edge.
-- Qt::AlignHCenter 0x0004  Centers horizontally in the available space.
-- Qt::AlignJustify 0x0008  Justifies the text in the available space.
-- `vertical` :
-- Qt::AlignTop     0x0020  32 Aligns with the top.
-- Qt::AlignBottom  0x0040  64 Aligns with the bottom.
-- Qt::AlignVCenter 0x0080  128 Centers vertically in the available space.

--
-- Contraintes pour la table `widgets`
--
ALTER TABLE `widgets` ADD CONSTRAINT `fk_plugins_idPlugin` FOREIGN KEY (`idPlugin`) REFERENCES `plugins` (`idPlugin`);
ALTER TABLE `widgets` ADD CONSTRAINT `fk_ecrans_idEcran2` FOREIGN KEY (`idEcran`) REFERENCES `ecrans` (`idEcran`);

--
-- Structure de la table `panels`
--

CREATE TABLE IF NOT EXISTS `panels` (
  `idPanel` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `url` varchar(255) DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `zoom` double DEFAULT NULL,
  `scrollbar` TINYINT(1) DEFAULT NULL,   
  `uuid` varchar(255) NOT NULL,
  PRIMARY KEY (`idPanel`),
  KEY `fk_widgets_uuid1` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Contraintes pour la table `panels`
--
ALTER TABLE `panels` ADD CONSTRAINT `fk_widgets_uuid1` FOREIGN KEY (`uuid`) REFERENCES `widgets` (`uuid`) ON DELETE CASCADE;

--
-- Structure de la table `images`
--

CREATE TABLE IF NOT EXISTS `images` (
  `idImage` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `nom` varchar(50) DEFAULT NULL,
  `taille` varchar(25) DEFAULT NULL,
  `type` varchar(25) DEFAULT NULL,
  `uuid` varchar(255) NOT NULL,
  `image` longblob NOT NULL,
  PRIMARY KEY (`idImage`),
  KEY `fk_widgets_uuid2` (`uuid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

--
-- Contraintes pour la table `images`
--
ALTER TABLE `images` ADD CONSTRAINT `fk_widgets_uuid2` FOREIGN KEY (`uuid`) REFERENCES `widgets` (`uuid`) ON DELETE CASCADE;

--
-- Structure de la table `dates`
--

CREATE TABLE IF NOT EXISTS `dates` (
  `idDate` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `police` varchar(255) DEFAULT NULL,
  `taille` int(11) unsigned DEFAULT NULL,
  `couleur` varchar(16) DEFAULT NULL,
  `format` varchar(16) DEFAULT NULL,
  `type` int(11) unsigned DEFAULT NULL,
  `style` varchar(255) DEFAULT NULL,
  `uuid` varchar(255) NOT NULL,
  PRIMARY KEY (`idDate`),
  KEY `fk_widgets_uuid3` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Contraintes pour la table `dates`
--
ALTER TABLE `dates` ADD CONSTRAINT `fk_widgets_uuid3` FOREIGN KEY (`uuid`) REFERENCES `widgets` (`uuid`) ON DELETE CASCADE;

--
-- Structure de la table `digitalclocks`
--

CREATE TABLE IF NOT EXISTS `digitalclocks` (
  `idDigitalClock` int(11) unsigned NOT NULL AUTO_INCREMENT,  
  `format` varchar(16) DEFAULT NULL,
  `background` varchar(16) DEFAULT NULL,
  `couleur` varchar(16) DEFAULT NULL,
  `style` varchar(255) DEFAULT NULL,
  `segment` int(11) DEFAULT NULL,
  `shape` int(11) DEFAULT NULL,
  `shadow` int(11) DEFAULT NULL,
  `uuid` varchar(255) NOT NULL,
  PRIMARY KEY (`idDigitalClock`),
  KEY `fk_widgets_uuid4` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Contraintes pour la table `digitalclocks`
--
ALTER TABLE `digitalclocks` ADD CONSTRAINT `fk_widgets_uuid4` FOREIGN KEY (`uuid`) REFERENCES `widgets` (`uuid`) ON DELETE CASCADE;

--
-- Structure de la table `analogclocks`
--

CREATE TABLE IF NOT EXISTS `analogclocks` (
  `idAnalogClock` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `style` varchar(255) DEFAULT NULL,
  `hourColor` varchar(16) DEFAULT NULL,
  `minuteColor` varchar(16) DEFAULT NULL,
  `secondeColor` varchar(16) DEFAULT NULL,
  `uuid` varchar(255) NOT NULL,
  PRIMARY KEY (`idAnalogClock`),
  KEY `fk_widgets_uuid5` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Contraintes pour la table `analogclocks`
--
ALTER TABLE `analogclocks` ADD CONSTRAINT `fk_widgets_uuid5` FOREIGN KEY (`uuid`) REFERENCES `widgets` (`uuid`) ON DELETE CASCADE;

--
-- Structure de la table `analogclocksqwt`
--

CREATE TABLE IF NOT EXISTS `analogclocksqwt` (
  `idAnalogClockQwt` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `hourColor` varchar(16) DEFAULT NULL,
  `minuteColor` varchar(16) DEFAULT NULL,
  `secondeColor` varchar(16) DEFAULT NULL,
  `background` varchar(16) DEFAULT NULL,
  `uuid` varchar(255) NOT NULL,
  PRIMARY KEY (`idAnalogClockQwt`),
  KEY `fk_widgets_uuid9` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Contraintes pour la table `analogclocksqwt`
--
ALTER TABLE `analogclocksqwt` ADD CONSTRAINT `fk_widgets_uuid9` FOREIGN KEY (`uuid`) REFERENCES `widgets` (`uuid`) ON DELETE CASCADE;

--
-- Structure de la table `labels`
--

CREATE TABLE IF NOT EXISTS `labels` (
  `idLabel` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `police` varchar(255) DEFAULT NULL,
  `taille` int(11) unsigned DEFAULT NULL,
  `couleur` varchar(16) DEFAULT NULL,
  `format` int(11) unsigned DEFAULT NULL,
  `style` varchar(255) DEFAULT NULL,
  `texte` text DEFAULT NULL,
  `uuid` varchar(255) NOT NULL,
  PRIMARY KEY (`idLabel`),
  KEY `fk_widgets_uuid6` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Contraintes pour la table `labels`
--
ALTER TABLE `labels` ADD CONSTRAINT `fk_widgets_uuid6` FOREIGN KEY (`uuid`) REFERENCES `widgets` (`uuid`) ON DELETE CASCADE;

--
-- Structure de la table `horodatages`
--

CREATE TABLE IF NOT EXISTS `horodatages` (
  `idHorodatage` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `police` varchar(255) DEFAULT NULL,
  `taille` int(11) unsigned DEFAULT NULL,
  `couleur` varchar(16) DEFAULT NULL,
  `format` varchar(64) DEFAULT NULL,
  `type` int(11) unsigned DEFAULT NULL,
  `style` varchar(255) DEFAULT NULL,
  `uuid` varchar(255) NOT NULL,
  PRIMARY KEY (`idHorodatage`),
  KEY `fk_widgets_uuid7` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Contraintes pour la table `horodatages`
--
ALTER TABLE `horodatages` ADD CONSTRAINT `fk_widgets_uuid7` FOREIGN KEY (`uuid`) REFERENCES `widgets` (`uuid`) ON DELETE CASCADE;

--
-- Structure de la table `boutons`
--

CREATE TABLE IF NOT EXISTS `boutons` (
  `idBouton` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `police` varchar(255) DEFAULT NULL,
  `taille` int(11) unsigned DEFAULT NULL,
  `couleur` varchar(16) DEFAULT NULL,
  `format` int(11) unsigned DEFAULT NULL,
  `style` varchar(255) DEFAULT NULL,
  `texte` text DEFAULT NULL,
  `uuid` varchar(255) NOT NULL,
  `url` varchar(255) NOT NULL,
  `idPanel` int(11) unsigned NOT NULL,
  PRIMARY KEY (`idBouton`),
  KEY `fk_widgets_uuid8` (`uuid`),
  KEY `fk_panels_idPanel2` (`idPanel`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Contraintes pour la table `boutons`
--
ALTER TABLE `boutons` ADD CONSTRAINT `fk_widgets_uuid8` FOREIGN KEY (`uuid`) REFERENCES `widgets` (`uuid`) ON DELETE CASCADE;
ALTER TABLE `boutons` ADD CONSTRAINT `fk_panels_idPanel2` FOREIGN KEY (`idPanel`) REFERENCES `panels` (`idPanel`);

--
-- Structure de la table `movies`
--

CREATE TABLE IF NOT EXISTS `movies` (
  `idMovie` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `nom` varchar(50) DEFAULT NULL,
  `taille` varchar(25) DEFAULT NULL,
  `type` varchar(25) DEFAULT NULL,
  `uuid` varchar(255) NOT NULL,
  `animation` longblob NOT NULL,
  PRIMARY KEY (`idMovie`),
  KEY `fk_widgets_uuid10` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Contraintes pour la table `movies`
--
ALTER TABLE `movies` ADD CONSTRAINT `fk_widgets_uuid10` FOREIGN KEY (`uuid`) REFERENCES `widgets` (`uuid`);

--
-- Structure de la table `videos`
--

CREATE TABLE IF NOT EXISTS `videos` (
  `idVideo` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `nom` varchar(50) DEFAULT NULL,
  `taille` varchar(25) DEFAULT NULL,
  `type` varchar(25) DEFAULT NULL,
  `uuid` varchar(255) NOT NULL,
  `video` longblob NOT NULL,
  PRIMARY KEY (`idVideo`),
  KEY `fk_widgets_uuid12` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Contraintes pour la table `videos`
--
ALTER TABLE `videos` ADD CONSTRAINT `fk_widgets_uuid12` FOREIGN KEY (`uuid`) REFERENCES `widgets` (`uuid`);

--
-- Structure de la table `webcams`
--

CREATE TABLE IF NOT EXISTS `webcams` (
  `idWebcam` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) NOT NULL,
  PRIMARY KEY (`idWebcam`),
  KEY `fk_widgets_uuid11` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Contraintes pour la table `movies`
--
ALTER TABLE `webcams` ADD CONSTRAINT `fk_widgets_uuid11` FOREIGN KEY (`uuid`) REFERENCES `widgets` (`uuid`);

--
-- Structure de la table `chronometres`
--

CREATE TABLE IF NOT EXISTS `chronometres` (
  `idChrono` int(11) unsigned NOT NULL AUTO_INCREMENT,  
  `valeur` int(11) DEFAULT NULL,
  `format` varchar(16) DEFAULT NULL,
  `background` varchar(16) DEFAULT NULL,
  `couleur` varchar(16) DEFAULT NULL,
  `style` varchar(255) DEFAULT NULL,
  `segment` int(11) DEFAULT NULL,
  `shape` int(11) DEFAULT NULL,
  `shadow` int(11) DEFAULT NULL,
  `uuid` varchar(255) NOT NULL,
  PRIMARY KEY (`idChrono`),
  KEY `fk_widgets_uuid13` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Contraintes pour la table `chronometres`
--
ALTER TABLE `chronometres` ADD CONSTRAINT `fk_widgets_uuid13` FOREIGN KEY (`uuid`) REFERENCES `widgets` (`uuid`) ON DELETE CASCADE;

--
-- Structure de la table `rebours`
--

CREATE TABLE IF NOT EXISTS `rebours` (
  `idRebours` int(11) unsigned NOT NULL AUTO_INCREMENT,  
  `valeur` int(11) DEFAULT NULL,
  `format` varchar(16) DEFAULT NULL,
  `background` varchar(16) DEFAULT NULL,
  `couleur` varchar(16) DEFAULT NULL,
  `style` varchar(255) DEFAULT NULL,
  `segment` int(11) DEFAULT NULL,
  `shape` int(11) DEFAULT NULL,
  `shadow` int(11) DEFAULT NULL,
  `uuid` varchar(255) NOT NULL,
  PRIMARY KEY (`idRebours`),
  KEY `fk_widgets_uuid14` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Contraintes pour la table `rebours`
--
ALTER TABLE `rebours` ADD CONSTRAINT `fk_widgets_uuid14` FOREIGN KEY (`uuid`) REFERENCES `widgets` (`uuid`) ON DELETE CASCADE;

Pour installer la base de données dmi, il suffit de faire :

$ mysql -uroot -ppassword -hlocalhost < dmi.sql

Base de données : dmi.zip

Séquence 3 : exécution de requêtes SQL

Démarrer la console mysql :

$ sudo mysql -uroot -ppassword -hlocalhost dmi
mysql>

Liste les noms des comptes :

select nom from diffuseurs;
+-------+
| nom   |
+-------+
| admin |
| tv    |
+-------+

Nombre d’écrans :

SELECT COUNT( DISTINCT idEcran ) AS nbEcrans FROM ecrans;
+----------+
| nbEcrans |
+----------+
|        2 |
+----------+

Nombre d’écrans détectés présents :

SELECT COUNT( DISTINCT idEcran ) AS nbEcrans FROM ecrans WHERE presence=1;
+----------+
| nbEcrans |
+----------+
|        1 |
+----------+

Liste les noms des widgets (ainsi que leur UID et état) pour un écran :

SELECT plugins.nom, widgets.uuid, widgets.etat FROM widgets INNER JOIN plugins ON plugins.idPlugin=widgets.idPlugin WHERE widgets.idEcran=1 ORDER BY plugins.nom ASC;
+--------------------+---------------+------+
| nom                | uuid          | etat |
+--------------------+---------------+------+
| bouton             | 68956d453800b |    0 |
| date               | 58956dbb1d69b |    1 |
| horloge analogique | 58956d4538042 |    1 |
| horloge digitale   | 58956dbb1d628 |    1 |
| horodatage         | 58956dbb1b79b |    0 |
| image              | 58956de6756b8 |    1 |
| label              | 58956d453800b |    1 |
| panel              | 58956de675645 |    1 |
+--------------------+---------------+------+

Questions de révisions

  1. Quel est le nom du fichier de configuration du serveur MySQL ? Où se situe-t-il ?
  2. Quel est le port d’écoute par défaut du serveur MySQL ?
  3. Quelle option interdit l’accès à distance au serveur MySQL ?
  4. Quelle option permet l’accès à distance au serveur MySQL ? Donner un exemple pour un intranet.
  5. Dans quelle base de données et quelle table sont stockées les comptes utilisateur de MySQL ?
  6. Donner les commandes MySQL qui vérifient que les tables de la base de données dmi ont bien été créées ?

  7. Donner les requêtes et résulats pour les cas suivants :

    1. liste des plugins existants pour cette version
    2. nombre de plugins existants pour cette version
    3. Liste le nom, la largeur et la hauteur de chaque widget actif pour un écran

Retour au sommaire

© 2016 Thierry Vaira