Sommaire

Base de données Gestion de stock
- La base de données MySQL

Base de données Gestion de stock

Le système « Gestion de stock » permet de contrôler et gérer l’utilisation de produits stockés dans des armoires et d’assurer la traçabilité de l’attribution du matériel et des consommables stockés.

Il nécessite une base de données MySQL.

La base de données MySQL

La base de données e-stock est un ensemble de données permettant l’exploitation d’un réseau d’armoires de stockage. Ces données sont mémorisées dans la base de données MySQL du serveur.

SQL : e-stock-bdd.zip

Le programme d’installation setup-estock.sh installe automatiquement la base de données e-stock.

Il faudra ensuite paramétrer les fichiers configuration.ini :

[BDD]
Hostname=192.168.52.12
Password=password
Port=3306
Username=estock
Database=e-stock

Les différents fichiers configuration.ini sont stockés dans le même répertoire que les applications.

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 e-stock;

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

mysql> show tables;

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

mysql> select * from Article;

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

Séquence 2 : 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 l’exemple) 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> CREATE USER 'rpi'@'%' IDENTIFIED BY 'password';

mysql> GRANT ALL PRIVILEGES ON `e-stock`.* 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 3 : installation de la base de données e-stock

Pour installer la base de données e-stock, il faut :

créer la base de données e-stock (CREATE DATABASE e-stock)
créer les tables (CREATE TABLE Article ... etc …)
insérer des données dans les tables (INSERT INTO Article ... etc …)

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

--
-- Base de données: `e-stock`
--

DROP DATABASE IF EXISTS `e-stock`;

CREATE DATABASE IF NOT EXISTS `e-stock`;

USE `e-stock`;

-- --------------------------------------------------------

-- Création du compte d'accès à la base de données e-stock

-- CREATE USER 'estock'@'%' IDENTIFIED BY 'password';
-- GRANT ALL PRIVILEGES ON `e-stock`.* TO 'estock'@'%';
-- FLUSH PRIVILEGES;

-- --------------------------------------------------------

--
-- Structure de la table `Profil`
--

CREATE TABLE IF NOT EXISTS `Profil` (
  `idProfil` int(11) NOT NULL AUTO_INCREMENT,
  `Nom` varchar(64) NOT NULL,
  PRIMARY KEY (`idProfil`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

INSERT INTO `Profil` (`Nom`) VALUES
('Administrateur'),
('Gestionnaire'),
('Utilisateur');

-- --------------------------------------------------------

--
-- Structure de la table `Groupe`
--

CREATE TABLE IF NOT EXISTS `Groupe` (
  `idGroupe` int(11) NOT NULL AUTO_INCREMENT,
  `Nom` varchar(64) NOT NULL,
  PRIMARY KEY (`idGroupe`),
  CONSTRAINT Unique_Groupe UNIQUE (`Nom`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

INSERT INTO `Groupe` (`Nom`) VALUES
('PROFESSEUR'),
('1-BTS-SN'),
('T-BTS-SN');

-- --------------------------------------------------------

--
-- Structure de la table `Utilisateur`
--

CREATE TABLE IF NOT EXISTS `Utilisateur` (
  `idUtilisateur` int(11) NOT NULL AUTO_INCREMENT,
  `idProfil` int(11) NOT NULL,
  `idGroupe` int(11) NOT NULL,
  `Nom` varchar(64) NOT NULL,
  `Prenom` varchar(64) NOT NULL,
  `DateValidite` date NOT NULL,
  `Identifiant` varchar(255) DEFAULT NULL,
  `MotDePasse` varchar(255) DEFAULT NULL,
  `Badge` varchar(16) NOT NULL,
  `Email` varchar(64) NOT NULL,  
  PRIMARY KEY (`idUtilisateur`),
--  CONSTRAINT Unique_Utilisateur UNIQUE (`Badge`),
  CONSTRAINT Utilisateur_fk_1 FOREIGN KEY (`idProfil`) REFERENCES Profil(`idProfil`) ON DELETE CASCADE,
  CONSTRAINT Utilisateur_fk_2 FOREIGN KEY (`idGroupe`) REFERENCES Groupe(`idGroupe`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Structure de la table `Armoire`
--

CREATE TABLE IF NOT EXISTS `Armoire` (
  `idArmoire` int(11) NOT NULL,
  `Nom` varchar(255) NOT NULL,
  `Description` varchar(255) DEFAULT NULL,
  `nbCasiers` int(11) NOT NULL DEFAULT 8,
  PRIMARY KEY (`idArmoire`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Structure de la table `Type`
--

CREATE TABLE IF NOT EXISTS `Type` (
  `idType` int(11) NOT NULL AUTO_INCREMENT,
  `Nom` varchar(64) NOT NULL,
  PRIMARY KEY (`idType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

INSERT INTO `Type` (`Nom`) VALUES
('Equipement'),
('Consommable');

-- --------------------------------------------------------

--
-- Structure de la table `Unite`
--

CREATE TABLE IF NOT EXISTS `Unite` (
  `idUnite` int(11) NOT NULL AUTO_INCREMENT,
  `Nom` varchar(64) NOT NULL,
  PRIMARY KEY (`idUnite`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

INSERT INTO `Unite` (`Nom`) VALUES
('Metre'),
('Piece'),
('Pourcentage'),
('Poids g'),
('Poids kg');

-- --------------------------------------------------------

--
-- Structure de la table `Comptage`
--

CREATE TABLE IF NOT EXISTS `Comptage` (
  `idComptage` int(11) NOT NULL AUTO_INCREMENT,
  `Nom` varchar(64) NOT NULL,
  PRIMARY KEY (`idComptage`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

INSERT INTO `Comptage` (`Nom`) VALUES
('Aucun'),
('Automatique'),
('CodeBarre');

-- --------------------------------------------------------

--
-- Structure de la table `Action`
--

CREATE TABLE IF NOT EXISTS `Action` (
  `idAction` int(11) NOT NULL AUTO_INCREMENT,
  `Nom` varchar(64) NOT NULL,
  PRIMARY KEY (`idAction`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

INSERT INTO `Action` (`Nom`) VALUES
('Entree'),
('Sortie');

-- --------------------------------------------------------

--
-- Structure de la table `Article`
--

CREATE TABLE IF NOT EXISTS `Article` (
  `idArticle` int(11) NOT NULL AUTO_INCREMENT,
  `idType` int(11) NOT NULL,  
  --   `Type` enum('Equipement','Consommable'),
  `Nom` varchar(255) NOT NULL,
  `Code` varchar(255) NOT NULL,
  `Designation` varchar(255) NOT NULL,
  `Poids` int(11) NOT NULL,  
  PRIMARY KEY (`idArticle`),
  CONSTRAINT Article_fk_1 FOREIGN KEY (`idType`) REFERENCES Type(`idType`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------

--
-- Structure de la table `Stock`
--

CREATE TABLE IF NOT EXISTS `Stock` (
  `idStock` int(11) NOT NULL AUTO_INCREMENT,
  `idArmoire` int(11) NOT NULL,
  `idArticle` int(11) NOT NULL,
  `idComptage` int(11) NOT NULL,
  `idUnite` int(11) NOT NULL,
  `Quantite` int(11) DEFAULT 0,
  `Disponible` int(11) DEFAULT 0,
  `Tare` int(11) NOT NULL,  
  `numeroCasier` int(11) NOT NULL,
  PRIMARY KEY (`idStock`),
  -- CONSTRAINT Unique_NumeroCasier UNIQUE (`numeroCasier`),
  CONSTRAINT Stock_fk_1 FOREIGN KEY (`idArmoire`) REFERENCES Armoire(`idArmoire`) ON DELETE CASCADE,
  CONSTRAINT Stock_fk_2 FOREIGN KEY (`idArticle`) REFERENCES Article(`idArticle`) ON DELETE CASCADE,
  CONSTRAINT Stock_fk_3 FOREIGN KEY (`idComptage`) REFERENCES Comptage(`idComptage`) ON DELETE CASCADE,
  CONSTRAINT Stock_fk_4 FOREIGN KEY (`idUnite`) REFERENCES Unite(`idUnite`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Structure de la table `Mouvement`
--

CREATE TABLE IF NOT EXISTS `Mouvement` (
  `idMouvement` int(11) NOT NULL AUTO_INCREMENT, 
  `idUtilisateur` int(11) NOT NULL,
  `idStock` int(11) NOT NULL,
  `idAction` int(11) NOT NULL,
  --   `Action` enum('Entree','Sortie'),
  `Quantite` int(11) NOT NULL,
  `Horodatage` datetime NOT NULL,
  PRIMARY KEY (`idMouvement`),
  CONSTRAINT Mouvement_fk_1 FOREIGN KEY (`idUtilisateur`) REFERENCES Utilisateur(`idUtilisateur`) ON DELETE CASCADE,
  CONSTRAINT Mouvement_fk_2 FOREIGN KEY (`idStock`) REFERENCES Stock(`idStock`) ON DELETE CASCADE,
  CONSTRAINT Mouvement_fk_3 FOREIGN KEY (`idAction`) REFERENCES Action(`idAction`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Pour les tests
--

INSERT INTO `Utilisateur` (`idProfil`,`idGroupe`,`Nom`,`Prenom`,`DateValidite`,`Identifiant`,`MotDePasse`,`Badge`,`Email`) VALUES('1','1','Vaira','Thierry','2021-07-01','admin','5f4dcc3b5aa765d61d8327deb882cf99','','tvaira@free.fr');
INSERT INTO `Utilisateur` (`idProfil`,`idGroupe`,`Nom`,`Prenom`,`DateValidite`,`Identifiant`,`MotDePasse`,`Badge`,`Email`) VALUES('2','1','Vaira','Thierry','2021-07-01','tvaira','5f4dcc3b5aa765d61d8327deb882cf99','046BF5EAA86480','tvaira@free.fr');
INSERT INTO `Utilisateur` (`idProfil`,`idGroupe`,`Nom`,`Prenom`,`DateValidite`,`Identifiant`,`MotDePasse`,`Badge`,`Email`) VALUES('2','1','Beaumont','Jerome','2021-07-01','jbeaumont','','','beaumont@lasalle84.org');
INSERT INTO `Utilisateur` (`idProfil`,`idGroupe`,`Nom`,`Prenom`,`DateValidite`,`Identifiant`,`MotDePasse`,`Badge`,`Email`) VALUES('3','3','LEGGER','Pierre-Antoine','2021-07-01','legger.pa','','5022A783','');
INSERT INTO `Utilisateur` (`idProfil`,`idGroupe`,`Nom`,`Prenom`,`DateValidite`,`Identifiant`,`MotDePasse`,`Badge`,`Email`) VALUES('3','3','TRANCHAT','Joffrey','2021-07-01','tranchat.j','','02BE0261','');
INSERT INTO `Utilisateur` (`idProfil`,`idGroupe`,`Nom`,`Prenom`,`DateValidite`,`Identifiant`,`MotDePasse`,`Badge`,`Email`) VALUES('3','3','BOUNOIR','Fabien','2021-07-01','bounoir.f','','30DDA983','');
INSERT INTO `Utilisateur` (`idProfil`,`idGroupe`,`Nom`,`Prenom`,`DateValidite`,`Identifiant`,`MotDePasse`,`Badge`,`Email`) VALUES('3','3','DEVINE','Vincent','2020-07-01','devine.v','','62A3F560','');

INSERT INTO `Armoire` (`idArmoire`,`Nom`,`Description`,`nbCasiers`) VALUES('1','A1','Composants','2');
INSERT INTO `Armoire` (`idArmoire`,`Nom`,`Description`,`nbCasiers`) VALUES('2','A2','Mesures','2');
INSERT INTO `Armoire` (`idArmoire`,`Nom`,`Description`,`nbCasiers`) VALUES('3','A3','Equipement','2');

INSERT INTO `Article` (`idType`, `Nom`, `Code`, `Designation`, `Poids`) VALUES('2','Vis six pans creux M2 8mm','','M2*8', 50);
INSERT INTO `Article` (`idType`, `Nom`, `Code`, `Designation`, `Poids`) VALUES('2','Vis tête cylindrique M2 8mm','','M2*8', 50);
INSERT INTO `Article` (`idType`, `Nom`, `Code`, `Designation`, `Poids`) VALUES('2','Vis six pans creux M2 12mm','','M2*12', 50);
INSERT INTO `Article` (`idType`, `Nom`, `Code`, `Designation`, `Poids`) VALUES('2','Vis tête cylindrique M2 12mm','','M2*12', 50);
INSERT INTO `Article` (`idType`, `Nom`, `Code`, `Designation`, `Poids`) VALUES('1','Fluke i30s','2584935','Amperemetre AC/DC', 100);
INSERT INTO `Article` (`idType`, `Nom`, `Code`, `Designation`, `Poids`) VALUES('1','Fluke 179','1234','Multimetre', 150);

INSERT INTO `Stock` (`idArmoire`,`idArticle`,`idComptage`,`idUnite`,`Quantite`,`Disponible`,`Tare`,`numeroCasier`) VALUES('1','5','3','2','8','6',1500,'1');
INSERT INTO `Stock` (`idArmoire`,`idArticle`,`idComptage`,`idUnite`,`Quantite`,`Disponible`,`Tare`,`numeroCasier`) VALUES('2','6','2','2','2','2',1520,'2');
INSERT INTO `Stock` (`idArmoire`,`idArticle`,`idComptage`,`idUnite`,`Quantite`,`Disponible`,`Tare`,`numeroCasier`) VALUES('1','6','3','2','8','2',1500,'2');

INSERT INTO `Mouvement` (`idMouvement`, `idUtilisateur`, `idStock`, `idAction`, `Quantite`, `Horodatage`) VALUES
(1, 2, 1, 2, 1, '2020-09-01 08:00:00'),
(2, 2, 1, 2, 2, '2020-09-01 08:30:00');
-- --------------------------------------------------------

Pour installer la base de données e-stock, il suffit de faire :

$ mysql -uroot -ppassword -hlocalhost < estock-v0.1.sql

SQL : e-stock-bdd.zip

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

Démarrer la console mysql :

$ sudo mysql -uroot -ppassword -hlocalhost e-stock
mysql>

Liste les noms des salles :

select nom from Article;
+-------------------------------+
| nom                           |
+-------------------------------+
| Vis six pans creux M2 8mm     |
| Vis tête cylindrique M2 8mm   |
| Vis six pans creux M2 12mm    |
| Vis tête cylindrique M2 12mm  |
| Fluke i30s                    |
| Fluke 179                     |
+-------------------------------+
6 rows in set (0.00 sec)

Questions de révisions MySQL

Quel est le nom du fichier de configuration du serveur MySQL ? Où se situe-t-il ?
Quel est le port d’écoute par défaut du serveur MySQL ?
Quelle option interdit l’accès à distance au serveur MySQL ?
Quelle option permet l’accès à distance au serveur MySQL ? Donner un exemple pour un intranet.
Dans quelle base de données et quelle table sont stockées les comptes utilisateur de MySQL ?
Donner les commandes MySQL qui vérifient que les tables de la base de données e-stock ont bien été créées ?
Donner les requêtes et résulats pour les cas suivants :
1. liste des articles pour cette version
2. nombre de articles pour cette version

Retour au sommaire