<< TP 1
TP 3 >>

2. Création et accès à la base de données





2.1. Création

2.1.1. Compte d'accès

Nous utilisons MySQL pour la base de données.

Sous Ubuntu, l'accès à la base données se fait en tant qu'administrateur avec la commande sudo sur le compte root. Nous créons ici une base de données nommée mabd ainsi qu'un utilisateur qui possède tous les droits sur cette base de données :

richer@zentopia:\$~/dev/symfony/$ sudo mysql -u root -p
> create database mabd;
> create user 'utilisateur'@'localhost' identified by 'motdepasse';
> grant all privileges on mabd.* to 'utilisateur'@'localhost';
> flush privileges;
> exit;

On crée donc un compte de libellé utilisateur et de mot de passe motdepasse doté de tous les privilèges afin de pouvoir insérer, supprimer, modifier des données ou des tables.

2.1.2. Base de données

Base de données vehicule

  1. -- MySQL dump 10.13  Distrib 8.0.39, for Linux (x86_64)
  2. --
  3. -- Host: localhost    Database: mabd
  4. -- ------------------------------------------------------
  5. -- Server version   8.0.39-0ubuntu0.24.04.1
  6.  
  7. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  8. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  9. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  10. /*!50503 SET NAMES utf8mb4 */;
  11. /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
  12. /*!40103 SET TIME_ZONE='+00:00' */;
  13. /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
  14. /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
  15. /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
  16. /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
  17.  
  18. --
  19. -- Table structure for table `conducteur`
  20. --
  21.  
  22. DROP TABLE IF EXISTS `conducteur`;
  23. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  24. /*!50503 SET character_set_client = utf8mb4 */;
  25. CREATE TABLE `conducteur` (
  27.   `co_nom` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  28.   PRIMARY KEY (`co_id`)
  29. ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  30. /*!40101 SET character_set_client = @saved_cs_client */;
  31.  
  32. --
  33. -- Table structure for table `equipement`
  34. --
  35.  
  36. DROP TABLE IF EXISTS `equipement`;
  37. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  38. /*!50503 SET character_set_client = utf8mb4 */;
  39. CREATE TABLE `equipement` (
  41.   `eq_libelle` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  42.   `eq_prix` double NOT NULL,
  43.   PRIMARY KEY (`eq_id`)
  44. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  45. /*!40101 SET character_set_client = @saved_cs_client */;
  46.  
  47. --
  48. -- Table structure for table `equipement_vehicule`
  49. --
  50.  
  51. DROP TABLE IF EXISTS `equipement_vehicule`;
  52. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  53. /*!50503 SET character_set_client = utf8mb4 */;
  54. CREATE TABLE `equipement_vehicule` (
  55.   `eqve_id` int NOT NULL AUTO_INCREMENT,
  56.   `eqve_equipement_id` int NOT NULL,
  57.   `eqve_vehicule_id` int NOT NULL,
  58.   `eqve_quantite` int NOT NULL,
  59.   PRIMARY KEY (`eqve_id`),
  60.   KEY `IDX_36FEF13984F274EA` (`eqve_equipement_id`),
  61.   KEY `IDX_36FEF1397E57CABE` (`eqve_vehicule_id`),
  62.   CONSTRAINT `FK_36FEF1397E57CABE` FOREIGN KEY (`eqve_vehicule_id`) REFERENCES `vehicule` (`ve_id`),
  63.   CONSTRAINT `FK_36FEF13984F274EA` FOREIGN KEY (`eqve_equipement_id`) REFERENCES `equipement` (`eq_id`)
  64. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  65. /*!40101 SET character_set_client = @saved_cs_client */;
  66.  
  67.  
  68. --
  69. -- Table structure for table `vehicule`
  70. --
  71.  
  72. DROP TABLE IF EXISTS `vehicule`;
  73. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  74. /*!50503 SET character_set_client = utf8mb4 */;
  75. CREATE TABLE `vehicule` (
  77.   `ve_co_id` int NOT NULL,
  78.   `ve_marque` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  79.   `ve_modele` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  80.   `ve_date` datetime DEFAULT NULL,
  81.   PRIMARY KEY (`ve_id`),
  82.   KEY `IDX_292FFF1DF9C68F4` (`ve_co_id`),
  83.   CONSTRAINT `FK_292FFF1DF9C68F4` FOREIGN KEY (`ve_co_id`) REFERENCES `conducteur` (`co_id`)
  84. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  85. /*!40101 SET character_set_client = @saved_cs_client */;
  86. /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
  87.  
  88. /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
  89. /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
  90. /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
  91. /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  92. /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  93. /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  94. /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
  95.  
  96. -- Dump completed on 2024-09-23  9:36:00
  97.  

2.2. Accès depuis Symfony

En ce qui concerne Symfony il faut modifier le fichier .env dans le répertoire principal du projet (~/dev/symfony/dvm). Modifiez :

# DATABASE_URL="sqlite:///%kernel.project_dir%/var/data.db"
# DATABASE_URL="mysql://app:!ChangeMe!@127.0.0.1:3306/app?serverVersion=8.0.32&charset=utf8mb4"
# DATABASE_URL="mysql://app:!ChangeMe!@127.0.0.1:3306/app?serverVersion=10.11.2-MariaDB&charset=utf8mb4"
DATABASE_URL="postgresql://app:!ChangeMe!@127.0.0.1:5432/app?serverVersion=16&charset=utf8"

en:

# DATABASE_URL="sqlite:///%kernel.project_dir%/var/data.db"
DATABASE_URL="mysql://utilisateur:motdepasse@127.0.0.1:3306/mabd?serverVersion=8.0.32&charset=utf8mb4"
# DATABASE_URL="mysql://app:!ChangeMe!@127.0.0.1:3306/app?serverVersion=10.11.2-MariaDB&charset=utf8mb4"
# DATABASE_URL="postgresql://app:!ChangeMe!@127.0.0.1:5432/app?serverVersion=16&charset=utf8"

Veillez à mettre en commentaire la ligne qui donne accès à Postgresql.

2.3. En cas de problème

Parfois l'installation plante ou alors il y a un conflit avec maria-db, il faut donc réinstaller mysql :

~/dev/symfony/$  sudo apt remove --purge mysql-server mysql-client mysql-common mysql-server-8.0
puis les commandes suivantes :
sudo apt autoremove
sudo apt autoclean
sudo rm -rf /etc/mysql /var/lib/mysql
sudo apt update
sudo apt install mysql-server

Enfin, sécuriser l'installation :

richer@zentopia:\$~/dev/symfony/$ sudo mysql_secure_installation
Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: No

Skipping password set for root as authentication with auth_socket is used by default.
If you would like to use password authentication instead, this can be done with the "ALTER_USER" command.
See https://dev.mysql.com/doc/refman/8.0/en/alter-user.html#alter-user-password-management for more information.

By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!





<< TP 1
TP 3 >>