STIC:STIC I - Atelier technique 4

Cette page fait partie des cours STIC I et STIC II

STIC

Introduction

Cet Atelier propose une série d'informations et activités de consolidation à propos d'une technologie très utilisée dans les sites web dynamiques : la base de données.

Une activité liée à votre projet spécifique est également prévue : la création d'une fiche technique, dans laquelle vous illustrez vos choix par rapport aux technologies utilisées pour votre projet.

Connaissances/compétences envisagées

À l'issue de cet exercice vous devez avoir acquis les connaissances/compétences suivantes :

• Identifier dans les bases de données l'extension du principe fondamentale de array en programmation : associer à une information un ou plusieurs indexes pour la récupérer
• Comprendre les grandes lignes des fonctions d'une base de données, notamment l'acronyme CRUD (Create, Retrieve, Update, Delete)
• Connaître l'existence de plusieurs systèmes de bases de données, notamment les bases de données relationnelles qui utilisent un langage de type SQL (Structured Query Language)
• Pouvoir exécuter des interrogations simples à une base de données à travers phpMyAdmin
• Savoir esquisser une simple modélisation de données pour une table

Prérequis

Sur le plan technique, pour effectuer les activités, il vous faudra les accès au service MySQL du serveur tecfaetu.unige.ch qui sera fait à travers l'interface phpMyAdmin. Les données d'accès vous ont été communiquées dans l'atelier précédent.

Sur le plan théorique, il vous faudra remobiliser les connaissances à propos du fonctionnement d'une page web dynamique vue lors de l'Atelier technique 3 pendant la période précédente.

Qu'est-ce qu'une base de données ?

Cette section résume le contenu de la page base de données, à consulter pour ceux qui sont veulent en savoir plus. Les termes base de données et l'équivalent anglais database seront utilisés comme synonymes.

Fonction générale d'une base de données

Une base de données est l'extension du principe fondamental de array en programmation (voir Introduction à la programmation pour rappel). En effet, l'un des principes fondamentaux de l'informatique, que ce soit sur le plan théorique ou pratique, consiste à manier de l'information. Les bases de données sont une complexification et une optimisation de ce principe fondamental qui s'articulent sur plusieurs niveaux :

• Service ou système

  Au niveau plus élevé on retrouve un logiciel qui sert comme service ou système de gestion des bases de données, comme par exemple MySQL. Ce système s'occupe de rendre les bases de données accessibles et maniables de différentes manières, notamment à travers un langage de programmation côté serveur utilisé pour la création de sites web dynamiques comme PHP

• Base de données

  C'est la première forme d'organisation des données. Une base de données regroupe des tableaux (table, en anglais) qui partagent une certaine relation entre eux (e.g. toutes les tables nécessaires au fonctionnement de WordPress ou Mediawiki)

• Table (ou parfois tableau)

  Une table (depuis l'anglais table) est une organisation de données plus spécifique, organisée souvent - mais pas exclusivement - en format rectangulaire, c'est-à-dire par lignes et colonnes (i.e. similaire à une feuille Excel)

• Champs

  Un champ (field en anglais) détermine l'association clé-valeur entre la structure des données et l'information à l'intérieur d'une table

Types de bases de données

Il existe plusieurs types de bases de données. Dans le cadre de cette activité, nous nous focalisons sur les databases de type relationnelles qui restent les plus utilisées à présent. Mais d'autres systèmes sont en train de gagner du terrain car plus adaptés à certains types de données. La différence principale qui divise les systèmes de base de données concerne la structuration de l'information :

• Les bases de données de type relationnel utilisent une structure fixe pour les tableaux, c'est-à-dire que chaque entrée dans le tableau (i.e. chaque ligne) doit respecter le nombre de champs (i.e. toutes les colonnes) prévus par le tableau
• D'autres bases de données utilisent une structure flexible, par exemple basée sur un format de type JSON. C'est le même principe que pour l'exercice sur XML que vous venez de faire : les données peuvent se structurer de manière imbriquée plutôt que à plat dans une structure rectangulaire

En termes techniques on fait cette division avec les acronymes :

• Database SQL

  Bases de données à structure fixe qui utilisent le Structured Query Language, un langage d’interrogation de bases de données qu'on verra par la suite

• Database NoSQL

  Bases de données à structure flexible. NoSQL peut être traduit comme No SQL ou Not Only SQL, car certains systèmes sont hybrides.

Nous ne verrons pas de databases NoSQL dans cette activité, mais si vous êtes intéressé(e)s référez-vous à la page base de données.

Fonctions principales des bases de données

La plupart des systèmes de gestion de databases permettent des opérations sur les données telles que :

• CRUD : acronyme de Create, Retrieve, Update, et Delete, c'est-à-dire les opérations les plus communes effectuées sur des données ;
• Filtrer : appliquer des critères de sélection pour obtenir un sous-ensemble de données par rapport à la totalité de l'information stockée ;
• Trier (ou Sorting en anglais) : utiliser des critères pour placer les données dans un ordre précis. Le tri est une opération informatique qui nécessite une grande puissance computationnelle, et les bases de données sont souvent optimisées pour ce type d'opération ;
• Répliquer : créer des "clones" d'une base de données pour des raisons de sécurité (backup) ou pour balancer les ressources (e.g. utiliser deux databases "jumeaux" pour départager un nombre élevé d'interrogations).

Ces opérations se font à travers des interrogations (en anglais queries) qui déterminent :

1. Quel type d'opération effectuer (créer, modifier, effacer, ... des données) ;
2. Quelles données seront affectées par l'opération.

Toute opération sur les données comporte un temps d'exécution qui est souvent proportionnel à la complexité de l'interrogation et/ou au nombre de données affectées par l'opération.

Activité avec base de données

Vous allez effectuer des activités sur les bases de données à travers l'interface graphique phpMyAdmin qui vous permet de manier les données à travers du code et/ou des commandes à l'interface (e.g. formulaires et boutons).

Connectez-vous à l'interface phpMyAdmin :

• phpMyAdmin

Pour éviter le risque de faire des manipulations sur des tables déjà existantes dans vos bases de données personnelles, utilisez tous la base de données aegir_demo pour les activités 01 et 02.

Sélectionnez la base de données depuis la liste à gauche en cliquant sur son nom.

En général, pendant l'activité faites bien attention à :

• Avoir sélectionné la bonne base de données (aegir_demo)
• Avoir sélectionné la bonne table à l'intérieur de cette base de données
  • 01_books pour l'activité 01
  • 02_grades pour l'activité 02

Si vous ne vous trouvez pas dans la bonne base ET dans la bonne table, vos commandes ne vont pas marcher même si elles sont écrites de la bonne manière.

Activité 01

La première activité consiste à insérer des données dans une base de données déjà existante. Vous allez insérer les données de deux manières différentes :

1. D'abord à travers l'interface de phpMyAdmin
2. Ensuite à travers votre première query SQL

Insérer une nouvelle entrée avec l'interface graphique

Dans la base de données aegir_demo vous trouverez une table qui s'appelle 01_books. Cliquez sur le nom de la table. La table se compose de 4 champs :

• id : un identificateur unique pour chaque entrée qui est généré automatiquement par la base de données de manière séquentielle
• title : le titre du livre
• author : l'auteur du livre
• proposed_by : le nom de la personne qui a proposé le livre

L'interface de phpMyAdmin qui s'ouvre lorsque vous cliquez sur le nom de la table devrait déjà vous montrer quelques entrées disponibles. Cliquez sur le bouton Insérer en haut de la partie principale de l'interface. Un écran avec des champs de formulaire va apparaître. Normalement, vous avez deux fois les mêmes champs qui se répètent, mais n'utilisez que la première série.

Insérer une entrée dans une table avec l'interface phpMyAdmin

Remplissez les champs pour proposer un nouveau livre, sauf le champ id qui doit rester vide.

Cliquez sur le bouton Exécuter. Vous noterez soit des éventuels messages d'erreurs à l'écran, soit le livre va s'ajouter à la liste et vous obtenez à l'interface un nouvel écran qui reporte la query que vous venez d'exécuter, par exemple :

INSERT INTO `01_books` (`id`, `title`, `author`, `proposed_by`) VALUES (NULL, 'Le Nom de la rose', 'Umberto Eco', 'MAF');

Copiez cette ligne, on la modifiera pour la réutiliser dans l'étape suivante. Puis cliquez sur le bouton en haut Afficher pour voir la liste des entrées, la votre sera la dernière (selon le nombre d'entrées déjà présentes, il faudra peut-être changer de pagination).

Insérer une nouvelle entrée avec l'exécution du code SQL

Maintenant vous allez insérer un autre livre avec une instruction SQL. Cliquez sur le bouton SQL en haut de la partie de l'interface principale. Vous aurez à l'image un grand espace de txte (textarea) dans lequel vous avez déjà une query :

SELECT * FROM `01_books` WHERE 1

Effacez-la et remplacez-la avec le code de votre query précédente. Puis adaptez le titre et l'auteur dans le texte pour insérer une nouvelle entrée différente de la précédente, par exemple.

INSERT INTO `01_books` (`id`, `title`, `author`, `proposed_by`) VALUES (NULL, 'Les Misérables', 'Victor Hugo', 'MAF');

Cliquez sur le bouton Exécuter en bas à droite.

Lisez bien le code est essayez de le reproduire pour d'autres livres encore (2-3 c'est suffisant) en l'écrivant vous-même ou à l'aide des boutons de l'interface de la boîte pour les requêtes. À savoir :

• Les ` (backtics en anglais) autour du nom de la table et des champs ne sont pas nécessaires
• Les ' autour des éléments textuels que vous ajoutez sont obligatoires (vous pouvez utiliser les doubles guillemets aussi)
• La première valeur des éléments à insérer doit toujours être NULL, car le champ id est généré automatiquement par la base de données

À retenir

Les opérations que vous venez d'effectuer correspondent à la partie Create de l'acronyme CRUD, car vous venez de créer des nouvelles entrées dans une table d'une base de données.

La query SQL est assez self-explaining, car elle utilise des verbes et une syntaxe assez simple à lire dans ce cas. Pour des tables plus complexes, comme dans le cas de vos plateformes/portails, les query seront sûrement plus articulées, mais le principe est le même.

Vous pouvez assez facilement imaginer que dans votre dispositif il existe une page qui vous permet d'ajouter des informations, normalement structurée par champs (e.g. nouveau post dans Word_press ou nouvelle page dans Mediawiki). Quelque part dans la logique de votre application, il existe un bout de code qui va exécuter la query INSERT INTO ....

Activité 02

Dans cette activité vous allez travailler sur la base de données 02_grades qui contient 1'000 entrées. Il s'agit de la simulation de 1'000 notes de certains cours du MALTT. La table propose les champs suivants :

• id : identifiant unique de l'entrée
• student_id : identifiant d'un étudiant (un étudiant peut avoir plusieurs notes dans la table)
• course : acronyme du cours MALTT
• grade : la note comprise entre 2 et 6
• type : si la note concerne un travail individuel (Individual) ou de groupe (Group)
• delay : si le travail a été rendu en retard ou pas
  • 0 = pas en retard
  • 1 = en retard

Vous allez exécuter une série de query de type SELECT sur cette table, ce qui correspond à l'action Retrieve dans l'acronyme CRUD.

Vous allez utiliser en prévalence le tab SQL de la table 02_grades. Cliquez donc d'abord sur le nom de la table pour la sélectionner et ensuite sur le bouton SQL en haut de l'interface principale.

Vous allez exécuter plusieurs query de suite, donc il est conseillé de cocher la case Conserver la boîte de requêtes dans la zone bleu clair, à côté du bouton Exécuter. De cette manière, vous aurez toujours cette boîte affichée avec le résultat de la query. Vous pourrez donc modifier le texte de la query au fur et à mesure.

Conserver la boite des requêtes après chaque query

Pour exécuter une requête, vous pouvez :

• Cliquer sur le bouton Exécuter vers le bas de l'interface
• Utilisez le shortcut clavier CTRL + Enter

Sélectionner les colonnes

L'interface SQL de phpMyAdmin vous aide à créer des queries. Lorsque vous l'ouvrez, vous avez déjà une query qui s'affiche dans le textarea :

SELECT * FROM `02_grades` WHERE 1

Cette query se traduit par :

• Sélectionner tous ce qui est contenu dans la table 02_grades

Le * est en effet un raccourcis pour toutes les colonnes.

Vous avez déjà vu que phpMyAdmin utilise souvent les backticks, c'est-à-dire le caractère `, pour entourer les noms des tables et des colonnes. Ce caractère est en réalité nécessaire seulement si vos noms de tables ou colonnes contiennent des caractères particuliers, comme des espaces par exemple. Si ce n'est pas le cas, vous pouvez ne pas les utiliser. Donc on pourrait écrire la même query de la manière suivante :

SELECT * FROM 02_grades WHERE 1

La notation WHERE 1 est un raccourcis pour écrire n'importe quel critère qui est toujours vrai, afin de sélectionner tout le contenu de la table. Nous verrons par la suite qu'on peut définir des critères pour filtrer les résultats.

Au lieu de sélectionner toutes les colonnes à travers le shortcut *, on peut définir quelles colonnes on veut retenir dans une liste séparée par des virgules, par exemple :

SELECT id, student_id FROM 02_grades WHERE 1

On aura dans ce cas seulement les deux premières colonnes.

À vous :

1. Sélectionnez seulement les colonnes avec le nom du cours, la note
2. Ajoutez le type de travail à la requête précédente

Appliquer des critères de sélection

On peut appliquer des critères de sélection aux requêtes à travers la clause WHERE, par exemple :

SELECT * FROM 02_grades WHERE course = "STIC I"

Cette requête filtre les entrées afin de retenir seulement celles qui ont "STIC I" dans la colonne course. Deux choses à noter dans la syntaxe :

• Signe d'équivalence unique, donc = et non pas ==

  Contrairement à la plupart des langages de programmation, y compris JavaScript, qui utilisent == pour l'équivalence, en SQL il suffit d'un seule signe d'équivalence

• Utiliser les guillemets pour entourer des valeurs littérales

  Pour filtrer par rapport à des critères textuels, il faut entourer la valeur littérale par des guillemets simples ou doubles

On peut utiliser également

• le symbole != pour la négation de l'équivalence, donc l'exemple précédent s'écrirait ainsi :

  SELECT * FROM 02_grades WHERE course != "STIC I"
  

  Ce qui équivaut à dire : toutes les entrées dont la valeur dans la colonne cours n'est pas STIC I

• Les symboles >, >= ou respectivement < ou <=

De plus, on peut complexifier les requêtes avec les opérateurs logiques :

• AND, pour des entrées qui respectent les deux critères en même temps
• OR, pour des entrées qui respectent au moins l'un de deux critères

(On peut utiliser sur 3, 4, ... N critères à la fois, mais ici on va faire simple)

Par exemple :

SELECT * FROM 02_grades WHERE course = "STIC I" AND delay = 1

Cette requête récupère toutes les entrées dont le cours est STIC I et dont les travaux ont été rendus en retard.

À vous maintenant :

1. Récupérez les entrées du cours ERGO I
2. Récupérez les entrées dont la note est supérieure ou égale à la moyenne de 4
3. Récupérez les entrées du domaine VIP (i.e. des deux cours VIP I et VIP II)
4. Récupérez les entrées du cours ADID II qui correspondent à un travail en groupe
5. Récupérez toutes les notes inférieures à la moyenne de 4 de tous les cours, sauf Methodo

Définissez vous-mêmes au moins 2 autres critères de sélection et essayez de les mettre en place à travers les requêtes correspondantes.

Ordonner les résultats

Vous pouvez déterminer l'ordre d'affichage des résultats en spécifiant la clause ORDER BY qui accepte un ou plusieurs champs, et la direction :

• ASC pour l'ordre croissant (utilisé si ce n'est pas spécifié)
• DESC pour l'ordre décroissant

Par exemple :

SELECT * FROM 02_grades WHERE course = "STIC II" ORDER BY grade DESC

Vous pouvez spécifier des critères d'ordre supplémentaire à travers des virgules, par exemple :

SELECT * FROM 02_grades WHERE course = "STIC II" ORDER BY grade DESC, delay ASC

À vous :

1. Sélectionner les entrées des cours ERGO I et ERGO II ordonnées par la note en ordre croissant
2. Ordonner toutes les entrées par note décroissante et par le nom du cours croissant

Appliquer des fonctions à des entrées groupées (plus avancé)

On peut appliquer des fonctions directement dans les requêtes et grouper les entrées selon un certain critère, ce qui est utile pour travailler avec des résultats agrégés. Nous allons utiliser la fonction "Average" AVG() qui calcule la moyenne d'une colonne numérique, appliquée à la clause GROUP BY pour stratifier les résultats en fonction d'une certaine modalité de sélection. Voyons un example pour essayer de rendre ce concept plus clair :

SELECT course, AVG(grade) AS course_mean FROM 02_grades GROUP BY course ORDER BY course_mean DESC

Cette requête :

• sélectionne le nom du cours
• crée une colonne agrégée qui calcule la moyenne de la colonne grade en lui donnant le nom de course_mean
• groupe les notes en fonction du course avec la clause GROUP BY course
• ordonne les résultats du cours avec la moyenne la plus élevée en premier

On peut combiner également avec des critères de sélection, par exemple :

SELECT course, AVG(grade) AS course_mean FROM 02_grades GROUP BY course HAVING course_mean >= 4 ORDER BY course_mean DESC

La clause HAVING permet de filtrer en fonction des résultats agrégés.

À vous :

1. Calculez les moyennes de notes en fonction du type de travail
2. Trouver les étudiants dont la moyenne de tous les cours n'est pas suffisante pour passer l'année (i.e. est inférieur à 4)

   Hint : le nombre est 42

À retenir

SQL permet de filtrer les résultats et de les ordonner en fonction de plusieurs critères qui peuvent se combiner dans des queries même très complexes. Vous pouvez essayer d'imaginer ce type de queries dans vos dispositifs, par exemple lorsque Word_press affiche les derniers posts ou Mediawiki filtre les pages par catégorie.

Activité 03 (facultative)

Activité facultative à faire dans votre propre base de données, i.e. aegir_votre_login_court

Dans cette activité vous allez effectuer une modélisation des données pour une base de données relationnelle (à ne pas confondre avec la modélisation des données en Data Science, voir par exemple Introduction à la modélisation des données avec R).

Modéliser pour une base de données implique de :

• Créer une schéma avec toutes les associations clé-valeur nécessaires à vos entrées
• Définir les caractéristiques de chaque association en termes du type de données (e.g. chiffre, texte, ...) et éventuelles contraintes à respecter (e.g. longueur, valeur NULL acceptée ou pas, etc.)

La modélisation des données peut être plus ou moins complexe en fonction de deux aspects :

• Le nombre de champs à modéliser
• Si la modélisation prévoit des relations entre différentes tables, par exemple le schéma suivant affiche trois tables qui sont en lien avec ce qu'on appelle une foreign key :
  1. Une table avec la liste des étudiants (en bleu)
  2. Une table avec la liste des cours (en vert)
  3. Une table avec la liste des notes des étudiants qui puise les étudiants et les noms du cours des deux tables respectives

Modélisation des données sur 3 tables en relation avec des foreign keys

Les associations relationnelles dépassent le caractère introductif de cette activité, mais peuvent être un élément d'intérêt à approfondir pour ceux qui veulent se construire un background technique.

Créer une nouvelle table en spécifiant votre propre modèle

Essayez de modéliser une simple table avec 5-6 champs qui schématisent une structure de données de votre intérêt. Pour créer la table :

1. Cliquez sur Nouvelle table dans la partie gauche de l'interface sous votre propre base des données
2. Choisissez un nom pour la table dans le champ en haut de l'écran qui s'affiche (pas très visible en réalité)
3. Si besoin, ajoutez le nombre de champs nécessaires toujours à travers la partie haute de l'interface
4. Passez à la définition de chaque champ
   • Pour le premier, utiliser le nom id et sélectionnez SERIAL dans le menu déroulant Type. Il ne faut pas spécifier d'autres paramètres. Cette option vous permet d'avoir une identifiant unique pour chaque entrée de votre table qui est donnée directement par la base de données
   • Pour les autres, définissez le nom et le type. Les 4 types proposés en début de la liste (INT, VARCHAR, TEXT, DATE) sont normalement suffisant pour la plupart des utilisations. Il y a beaucoup d'options à choisir et les indiquer toutes serait inutile. Procédez par essais.

Une fois votre schéma prêt, cliquez d'abord sur Aperçu SQL en bas de l'interface. Cela vous indique que même la création des tables se fait à travers le même système de query SQL.

Enfin, cliquez sur Sauvegarder pour créer votre table. Vous pouvez répéter les activités 1 et 2 avec votre base pour insérer et récupérer des entrées.

Conclusion

Dans cette activité vous avez travaillé avec une base de données de type relationnel, en utilisant des queries du langage SQL pour manier des données. Plus spécifiquement, vous avez essayé les opérations Create et Retrieve de l'acronyme CRUD, souvent utilisé avec les opérations sur des données.

Pour Create vous avez utilisé la formule INSERT INTO ... qui vous a permis d'ajouter des entrées à une table dans une base de données.

Pour Retrieve vous avez utilisé la formule SELECT ... FROM ... qui peut être articulée de manière plus ou moins complexe afin de détailler de manière plus précise les entrées à retenir dans une query.

Les opérations Update et Delete n'ont pas été abordées dans l'activité, mais suivent plus ou moins les mêmes principes.

Liens avec le projet Webmaster

La plupart des portails/plateformes disponibles dans le web actuellement utilisent une base de données relationnelle comme système de stockage et récupération de données. Il faut donc s'imaginer que dans les fichiers .php qui composent votre dispositif il existe des queries qui font du CRUD dans les tables de la base de données que vous avez associée pendant l'installation.

Pour aller plus loin

Voir Base de données :

• Utilité des bases de données
• CRUD -> Create, Retrieve, Update, Delete
• SQL vs NoSQL

Liens

Texte

SQL and MySQL tutorial (Edutechwiki anglais)

Transparents

http://tecfa.unige.ch/guides/tie/html/mysql-intro/mysql-intro.html (français, qq. détails sont à améliorer, il faudrait notamment utiliser "varchar" au lieu de "char"...)

http://tecfa.unige.ch/guides/te/files/mysql-intro.pdf (Anglais)

PhpMyAdmin

http://tecfa.unige.ch/admin/phpMyAdmin/

Liens techniques

en:SQL (liens)

en:MySQL (liens)