1 - Gérer une base de données avec Apache, MariaDB et phpMyAdmin
Vous allez voir qu'une base de données est un objet complet et indépendant de votre langage de programmation pour stocker les informatiosn.
Dans cette activité, vous allez voir comment gérer votre base de données de façon totalement autonome par rapport à vos propres programmes. Une fois cette activité réalisée, vous pourrez alors comprendre comment réaliser un programme (en Python, en PHP...) pour interagir automatiquement avec une base de données.
Mais pour l'instant, nous allons voir comment gérer une base de données SQL à travers une interface web (PHPMyAdmin) ou en utilisant les requêtes (instructions) SQL.
1 - Présentation des base de données
Un programme informatique désirant stocker des informations peut le faire principalement de trois façons :
- en plaçant les informations dans une structure de données propre au langage (strings, listes, dictionnaires ...) mais les données sont stockées uniquement dans la mémoire vive et seul ce langage pourra y accéder.
- en plaçant les informations dans un fichier texte ou équivalent mais il faudra créer une codification et la fournir si vous voulez que votre fichier soit compréhensible par d'autres. Et il y aura toujours le problème de l'accès multiple : plusieurs programmes ne pourront pas modifier le fichier en même temps en toute sécurité.
- en plaçant les informations dans une base de données qui pourra alors communiquer avec plusieurs utilisateurs à la fois.
Voici donc quelques avantages des bases de données ou databases en anglais :
- Accès optimisé : la base a été codée par des spécialistes pour être optimisée en terme de stabilité et de rapidité.
- Grande capacité : la base est conçue pour gérer des quantités énormes d'informations. Stocker autant d'informations dans un seul fichier serait très très lourd.
- Standardisée : le langage d'accès et d'écriture dans la base de données est partagé par de nombreuses personnes. Nul besoin d'apprendre une codification tordue lorsqu'on rentre dans l'équipe gérant le projet. L'un des langages les plus utilisés pour les bases de données relationnelles est le SQL pour Structured Query Language.
Pour gérer la base de données, on utilise un ensemble de logiciels spécifiques qu'on nomme système de gestion de base de données (ou SGBD). En voici quelques exemples gratuits :
MySQL est certainement le plus connu. Il est distribué avec une licence libre si vous l'utilisez pour créer un programme libre de droit ou avec une licence payante si vous voulez l'utiliser pour construire un programme payant. En 2017, c'est Oracle qui édite MySQL, alors que la société vend également son propre SGBD, nommé ... Oracle Database..
MariaDB est un fork du précédent, réalisé par son créateur après le rachat de MySQL et les doutes que cela entrainait sur la pérenité de MySQL.. Pourquoi de tels noms ? My est le prénom de sa première fille et Maria celui de sa deuxième fille.
PostgreSQL est un autre projet libre. Comme les projets libres Apache et Linux, PostgreSQL est géré par une communauté mondiale de développeurs et d'entreprises.
SQLite est un autre projet libre. Il a la particularité de n'utiliser qu'un fichier pour gèrer la base de données. Il est facilement intégrable à tout projet car son créateur, D. Richard Hipp, l'a placé dans le domaine public ! Comme il ne comporte qu'un fichier, on ne retrouve pas la nécessité d'avoir un serveur actif se lequel se connecte le client. Il est donc très souvent employé sur les projets autonomes (sans serveur) et les prototypes de gros projets. Le navigateur Firefox l'utilise par exemple pour gérer ses propres données.
2 - Présentation de XAMP
Nous allons travailler avec un logiciel qu'il vous faudra installer chez vous pour réaliser cette activité : XAMP.
Pour le télécharger et l'installer, le mieux est encore d'aller directement sur le site de la distribution Apache : XAMP.
Une fois installé, vous pourrez le mettre en marche :
A quoi sert ce logiciel ? C'est noté sur l'écran de base, si on sait à quoi cela correspond :
- Il permet de créer un serveur HTTP Apache.. Cela vous permettra notamment de pouvoir utiliser le PHP ou perl.
- Il permet de placer une base de données de type MySQL sur ce serveur.
- Il permet d'utiliser le logiciel libre de transfert FTP FileZilla.
- Il permet d'utiliser Mercury pour gérer les mails sur votre serveur Apache.
- Il permet d'utiliser Tomcat qui permet de créer un serveur sur lequel faire tourner du Java.
Vous auriez pu installer chacun de ces outils sans passer par XAMP. L'intérêt de XAMP, c'est qu'il installe le tout et évite les configurations manuelles à gérer avant la première utilisation. On installe, et ça roule. Normalement.
01° Démarrer votre serveur Apache.
02° Démarrer MySQL.
Si vous êtes attentif, vous devriez remarquer que le port du serveur Apache est le port 80. Le port par défaut des serveurs Web.
03° Sur la ligne MySQL, cliquez sur ADMIN pour accéder à la page d'accueil de phpMyAdmin : cette interface permet de gérer votre base de données (insérer, trier et supprimer des données) via un site Internet local écrit en php.
Comment fonctionne tout ceci ?
Vous êtes actuellement l'un des clients. Vous êtes en train d'utiliser phpMyAdmin, un site qui tourne actuellement sur votre serveur Apache local. phpMyAdmin vous permettra de gérer la base de données. Vous pouvez donc administrer votre base de données.
Un autre client pourrait utiliser un autre site (tournant en php, en Python, en Java ...) et communiquer avec la même base de données. C'est ce qui se passe lorsque vous observez les objets en vente sur un site commercant par exemple : la liste des objets disponibles n'est pas générée par un code HTML en dur mais le code HTML est généré par le programme sur le serveur après communication avec la base de données.
3 - Gérer sa base de données avec phpMyAdmin
Regardons maintenant phpMyAdmin de plus près. Commencer par regarder le nom de l'onglet sur votre navigateur ou ce qui est noté à côté de phpMyAdmin:
Dans l'onglet :
En haut à gauche de la page elle-même :
On vous signale que vous êtes connecté à votre propre machine (localhost) dont l'adresse IP version 4 est par convention 127.0.0.1. En Ipv6, c’est devenu ::1.
A droite, vous pouvez constater que vous allez utiliser une base de données MariaDB et que les caractères sont encodés en UTF-8.
04° Cliquer sur le bouton Bases de données pour accéder à la vue vous montrant les bases déjà présentes. Donner un nom (par exemple maPremiereBdd, choisir un type d'Interclassement (par exemple uft8_general_ci et cliquer sur Créer.
Interclassement ? C'est quoi ça encore ?!
Derrière ce terme, un peu étrange, se cachent les algorithmes de tri ou du moins, la façon dont on veut afficher le tri :
Si vous choississez uft8_bin par exemple, vous aurez un tri selon le code réel des caractères : les lettres A (code 65) et a (code 97) ne seront donc pas traitées la même manière. Ainsi Ac serait devant ab.
En utilisant un interclassement comme uft8_general_ci, on obtiendra des listes de résultats triées dans l'ordre alphabétique : les lettres A et a seront traitées de la même façon lors du tri. Le tri sera donc insensible à la casse (minuscules ou majuscules). Cette fois, ab serait devant Ac.
On obtient alors la visualisation de notre base de données, à savoir un truc vide pour l'instant !
Il est temps de voir ce que contient une base de données : des tableaux, tables en anglais. On utilise d'ailleurs souvent le mot table, même en français, dans le contexte des bases de données.
Dans les base de données relationnelles, une table correspond à un tableau à deux dimensions où :
- Les colonnes correspondent à une catégorie précise d'éléments (on parle de champs) : des nombres, des textes, des booléens, des images ...
- Les lignes correspondent aux différents enregistrements stockées dans cette table : on parle d'entrées.
Exemple d'une table décrivant des jeux vidéos (des très vieux, pour éviter les problèmes liées à la publicité et pour faire un peu d'archéologie ludique) :
| id | Jeu | Description | Année de sortie | Editeur | Support | Genre | Capture d'écran |
|---|---|---|---|---|---|---|---|
| 1 | Flight Simulator | Simulation de piotage d'avion avec des pixels gros comme des camions ! | 1980 | subLOGIC | Apple 2 | Simulateur |  |
| 2 | DONKEY KONG | Un méchant singe géant a capturé la fiancé de JumpMan (qui portera plus tard le nom de Mario). Guide JumpMan pour qu'il délivre sa dulcinée. | 1981 | Nintendo | Arcade | Plate-formes |  |
| 3 | PITFALL! | Pitfall Harry, un explorateur, ramasse des objets éparpillés dans la jungle. | 1982 | Activision | Atari 2600 | Plate-formes |  |
| 4 | PITFALL! | Pitfall Harry, un explorateur, ramasse des objets éparpillés dans la jungle. | 1983 | Activision | Commodore 64 (C64) | Plate-formes |  |
| 5 | Boulder Dash | Rockford, mineur téméraire, ramasse des diamants et tente de ne pas se faire écraser par les énormes pierres instables. | 1983 | First Star Software | Commodore 64 (C64) | Plate-formes |  |
05° Créer une table comportant 8 colonnes puisque notre tableau de jeux comporte 8 champs : le nom, la description, l'année de sortie, l'éditeur, le support, le type et le screenshot.
Vous voyez que nous avons maintenant la possibilité de choisir ce que nous allons mettre dans les colonnes :
Décrivons peu à peu les différents cases à renseigner.
- Id : c'est le numéro de l'entrée. On ne le rajoute jamais spontanément en tant qu'humain. Mais cela permet de séparer et d'identifier des entrées qui possèdent des éléments identiques (même nom, mêmê prénom ...)
- Jeu : c'est le nom du champ la façon dont la base de données va le reconnaitre.
- Type : un peu comme pour les variables, nous allons indiquer ici ce que va contenir cette case : des caractères, des nombres, du texte libre, des images ... Nous n'allons pas décrire l'ensemble des types disponibles mais si vous cliquez dessus, vous allez voir qu'il y en a un certain nombre ... Nous allons en décrire quelques-uns plus bas. Le réglage fin de cette case est très important car c'est elle qui détermine le poids en octet de chaque champ.
- Taille/Valeurs : nous détaillerons plus loin, en même temps que Type.
- Valeur par défaut : Vous pouvez ne rien mettre par défaut, définir une valeur vous même, placer NULL(une valeur spécifique qui vous garantit que la personne n'a pas encore défini cette valeur ou la date de création.
Et il y en a encore beaucoup beaucoup... Il est temps de commmencer à créer notre table.
4 - Création de la Clé Primaire (Primary Key)
La première colonne est un peu particulière : elle contient l'identificateur de l'entrée. Pourquoi ne pas utiliser simplement le nom du jeu pour l'identifier de façon certaine ? Simplement car il est possible que deux jeux possèdent le même nom (une vielle version et une version récente) ou encore que deux jeux soient réellement le même jeu mais sur deux supports différents (voir Pitfall). Il est donc important que vos entrées utilisateurs soient clairement identifiés par des numéros qui ne poourront pas être identifiques.
06° Nommer le premier champ id et laisser la souris au dessus de INT pour voir les possibilités de numérotation que cela représente.
Deux problèmes ici. le premier est que l'identifiant peut aller de -2,147 milliards à +2,147 milliards en gros. Or, je ne veux que des identifiants positifs ou nuls. Autant ne pas utiliser un type d'encodage des nombres qui occupe de la mémoire pour gérer un signe négatif qui ne sera jamais utilisé.
07° Changer la valeur de Attribut en choississant Unsigned, ce qui signifie que vous ne voulez pas gérer le signe du nombre.
Nous allons donc maintenant pouvoir placer dans id des nombres allant de 0 à plus de 4,29 milliards. Par contre, chaque id occupe donc 4 octets alors qu'il est très peu probable que mon site de jeux atteigne ce nombre de référence. D'ailleurs, je ne pense même pas qu'il y ai eu autant de sorties de jeux commerciaux.
08° Changer le type d'Integer pour SMALLINT. Combien de numéros différents sachant que j'utilise alors 2 octets pour stocker l'id ? J'ai pour objectif d'atteindre 10 000 références. Pourquoi ce type d'Integer est-il correctement choisi ?
Deux choses sont encore possibles : signaler que id est l'identificateur de cette table et lui demander de créer automatiquement ce numéro dès qu'on rajoute une instance à cette table.
09° Sélectionner pour index l'option Primary et pour AI (AUTO_INCREMENT), cocher la case : le numéro va automatiquement être attribué par la base de donnée : on garantit ainsi que deux numéros identiques ne seront jamais distribués à deux instances en même temps.
Résumons : nous avons créé une clé d'identification de l'entrée (qui correspondra à l'enregistrement d'un nouveau jeu) qui sera automatiquement attribuée par la base de données.
Il s'agit d'une clé primaire numérique composée d'un entier (integer) positif (unsigned) encodé sur 2 octets par clé.
On dispose donc de 2 fois 8 bits, soit 16 bits pour encoder le nombre. Aucun bit n'est perdu ici pour stoker le signe.
Vite vu, avec juste les ordres de grandeurs, cela donne :
| Nom | nbr octets (bytes) | Ordre de grandeur |
|---|---|---|
| TINYINT | 1 | Centaines d'entrée |
| SMALLINT | 2 | Milliers d'entrée |
| MEDIUMINT | 3 | Millions d'entrée |
| INT | 4 | Milliards d'entrée |
| BIGINT | 8 | Milliards de milliards |
Voici le tableau récupitulatif des différents types d'integer :
| Nom | Signe ? | nbr octets (bytes) | Valeur min. | Valeur max. |
|---|---|---|---|---|
| TINYINT | Unsigned | 1 | 0 | 255 |
| Signed | 1 | - 128 | + 127 | |
| SMALLINT | Unsigned | 2 | 0 | 65 535 |
| Signed | 2 | - 32 768 | + 32 767 | |
| MEDIUMINT | Unsigned | 3 | 0 | 16 777 215 |
| Signed | 3 | - 8 388 608 | + 8 388 607 | |
| INT | Unsigned | 4 | 0 | 4 294 967 295 |
| Signed | 4 | - 2 147 483 648 | + 2 147 483 647 | |
| BIGINT | Unsigned | 8 | 0 | 18 446 744 073 709 551 615 |
| Signed | 8 | - 9 223 372 036 854 775 808 | + 9 223 372 036 854 775 807 |
10° Pourquoi ne pas mettre constamment tous les integers en BIGINT pour être tranquille ?
...CORRECTION...
Pour limiter la taille de la base de données.
Si vous avez 1 000 000 entrées, le moindre octet gagné pour chaque entrée permet d'obtenir une base plus petite et donc plus rapide d'accès.
5 - Création des autres colonnes
Pour la colonne suivante, c'est plus facile :
11° Créer un champ de nom jeu de type VARCHAR qui contiendra jusqu'à 60 caractères au maximum (cela devrait suffire pour être certain d'écrire le nom du jeu. Aucune valeur par défaut mais un commentaire du type Placer le nom du jeu ici.
Le type VARCHAR permet de stocker des suites de caractères, des strings. Ici, on précise la longueur maximale voulue : 60 caractères.
Vous pourrez alors valider ces deux premières lignes en cliquant sur le bouton en bas à droite.
Pour rajouter d'autres colonnes après création des premières, il faut rechercher votre table dans le menu vertical de gauche et cliquer sur Nouvelle Colonne.
12° Créer un champ de nom description de type TEXT. Valeur par défaut NULL, un commentaire du type Placer la description du jeu ici. Cocher la case NULL : cela permet d'autoriser la case à contenir NULL lors de l'enregistrement. NULL est donc une valeur à part qui signale clairement que la case n'a pas été traitée par l'utilisateur. C'est donc un peu différent d'une entrée 'vide'.
13° Créer un champ de nom sortie de type YEAR. Cocher la case NULL.
Il est conseillé de ne pas utiliser de nom de champ avec des caractères autres que ceux de l'ASCII. Ne placez ainsi que des lettres (majuscules, minuscules), des chiffres ou d'underscore. Mais pas d'accents ou de caractères étranges sortant de l'ASCII.
Pourquoi ?
Pour éviter les problèmes de différences d'encodage entre la base de données, le serveur et le logiciel qui va traiter les résultats de la base de données. Si votre texte s'affiche mal, c'est un problème mais si votre programme ne parvient pas à retrouver les colonnes, c'est plus problématique.
Si vous maitrisez de bout en bout les encodages lors des communications entre programmes, vous pouvez faire ce que vous voulez. C'est juste un conseil, pas une obligation. Mais le jour où votre programme cessera de répondre correctement (après une mise à jour, un changement d'encodage par défaut...), ce sera un réel problème.
14° Créer un champ de nom editeur de type VARCHAR de longueur 40. Cocher la case NULL.
15° Créer un champ de nom support de type SET : nous allons pouvoir fournir les réponses acceptées. Contrairement à ENUM, nous pouvons fournir des réponses multiples (ce qui est le cas pour le support d'un jeu : il sort souvent sur plusieurs machines à la fois). Rentrer ensuite les valeurs possibles suivantes : Arcade, Apple2, Atari 2600, C64, Amiga ...
La différence entre SET et ENUM est donc la suivante :
ENUM impose de choisir UNE valeur dans le menu déroulant parmi les choix proposés.
SET est l'équivalent de choix via des cases à cocher : on peut en cocher une, plusieurs ou aucune. Via l'interface, il faut appuyer sur CTRL pour valider des choix multiples.
16° Créer un champ de nom genre de type ENUM : nous allons pouvoir fournir les réponses acceptées. Rentrer ensuite les valeurs possibles suivantes : Plate-formes, RPG, Simulateur ...
Il reste le stockage des images. Deux possibilités ici :
- La première méthode est la moins lourde pour la base mais celle qui garantit le moins l'intégrité des données : on crée un champ de type VARCHAR dans lequel on place le chemin d'accés et le nom du fichier voulu.
- La deuxième méthode est plus interne : on stocke le fichier directement dans la base, sous sa forme binaire (la suite d'octets quoi). Le défaut est ici de faire gonfler fortement la taille de la base de données si les images sont multiples et de tailles non négligeables.
Voyons comment stocker directement l'image : nous allons devoir stocker le fichier image sous sa forme binaire en utilisant le type BLOB pour Binary Large Object. Comme pour INT, il en existe plusieurs types en fonction de la taille de l'objet.
17° Créer un champ de nom fichier_image, de type BLOB. Dans Transformation d'images, choisir Inline(image), cela permettra à PhpMyAdmin de vous montrer une vignette de l'image encodée. Choisir également Transformation de saisie sur Image Upload de façon à proposer de charger une image.
6 - Création des entrées
Une fois la table en place, il ne reste qu'à la remplir !
Pour cela, il faut cliquer sur votre table dans le menu vertical de gauche puis sélectionner Insérer dans le menu horizontal de la fenêtre principale.
Par défaut, phpMyAdmin vous propose d'insérer deux entrées/instances, mais vous pouvez en régler plus en bas de page.
Choix des ID : Lors de la rentrée des données, ne donnez pas de numéros pour les ID : la base de données va s'en charger elle-même. Cela évitera de donner un numéro à une entrée déjà existante.
18° Créer les entrées correspondantes au tableau ci-dessous ou avec des entrées de votre choix :
Remarque : Il faudra enregistrer les images sur votre poste au vu de la méthode fournie pour uploader les images.
| id | Jeu | Description | Année de sortie | Editeur | Support | Genre | Capture d'écran |
|---|---|---|---|---|---|---|---|
| 1 | Flight Simulator | Simulation de piotage d'avion avec des pixels gros comme des camions ! | 1980 | subLOGIC | Apple 2 | Simulateur | |
| 2 | DONKEY KONG | Un méchant singe géant a capturé la fiancé de JumpMan (qui portera plus tard le nom de Mario). Guide JumpMan pour qu'il délivre sa dulcinée. | 1981 | Nintendo | Arcade | Plate-formes | |
| 3 | PITFALL! | Pitfall Harry, un explorateur, ramasse des objets éparpillés dans la jungle. | 1982 | Activision | Atari 2600 | Plate-formes | |
| 4 | PITFALL! | Pitfall Harry, un explorateur, ramasse des objets éparpillés dans la jungle. | 1983 | Activision | Commodore 64 (C64) | Plate-formes | |
| 5 | Boulder Dash | Rockford, mineur téméraire, ramasse des diamants et tente de ne pas se faire écraser par les énormes pierres instables. | 1983 | First Star Software | Commodore 64 (C64) | Plate-formes | |
En allant ensuite sur Parcourir (dans le menu horizontal), vous pourrez voir le contenu de votre petite table.
Comme vous pouvez le constater sur l'image, la base de donnée commence ici à 7 car j'avais déjà créé et supprimé un certain nombre d'entrées dans cette table. Retenez ainsi que la plus grande valeur de l'ID ne correspond pas au nombre d'entrées réelles car certaines ont pu être supprimées.
7 - Faire des recherches dans la table
Pour faire des recherches dans votre table, il suffit de sélectionner la bonne table et d'utiliser l'onglet Rechercher du menu horizontal.
19° Faire une recherche pour trouver toutes les entrées compatibles avec le C64 puis toutes les entrées de type Plateformes.
8 - Exporter vos données
Maintenant que vos données sont créés et en mémoire, vous désirez également en faire une copie de sauvegarde. Comment faire ?
En allant dans le menu horizontal et en cherchant Exporter.
20° Sélectionnez votre table (pas la base de données) puis exportez la au format SQL sous le nom jeux_sauvegarde.
Stockez votre version bien au chaud dans votre espace de travail. Vous allez devoir l'importer la prochaine fois. Le fichier se trouve normalement dans le fichier Téléchargement de votre navigateur Internet.
Mon fichier possède une taille de 291 Ko.
Copie de base : il est également possible de sauvegarder la base dans son intégralité. Dans ce cas, il suffit de sélectionner la base dans le menu vertical puis de cliquer sur Exporter.
9 - Détruire ou vider une table
Pour supprimer une table ou ses éléments, il faut aller dans l'onglet Opérations Vous pourrez trouver les deux boutons de destruction en bas à gauche :
21° Supprimer votre table puis tenter d'importer votre copie.
Et voilà, c'est tout pour cette fois. Dans l'activité suivante, nous verrons surtout comme lier deux tables entre elles : nous pourrons ainsi fournir des indications précises sur le C64 ou l'Apple 2 (puissance, taille de l'écran ...) sans pour autant avoir à les stocker dans chaque entrée des jeux utilisant cet appareil.