Accès à une base de données MySQL
Le SGBD
MySQL est un SGBD (Système de Gestion de Base de Données).
Il s'agit d'un logiciel serveur. Le serveur MySQL peut se trouver sur n'importe quel poste accessible du réseau.
Il est nécessaire de disposer d'un logiciel client pour s'y connecter.
Une fois connecté, le langage de communication avec le serveur est le SQL.
Le plus souvent, nous utilisons le SQL pour extraire les données d'une base de données (BDD), préalablement créée ou pour y écrire des données.
Complément : Système embarqué
Lorsqu'on désire gérer des données sans vouloir utiliser trop de ressource (mémoire, cpu), il est possible d'utiliser sqlite au lieu de MySQL. Il s'agit d'un fichier contenant la base de données sans serveur. Le driver existe pour Qt.
Base de données
Une base de données contient une ou plusieurs tables.
Chaque table contient des colonnes portant un titre et des caractéristiques sur le contenu (type d'information attendu).
Un enregistrement correspond à une ligne dans une table.
Exemple : Table user
Id | Nom | prenom | login | mdp |
1 | ANTOINE | Philippe | antoine | 652v4f65v24w65 |
2 | SILANUS | Marc | silanus | dssdf654wf5dfs6 |
Cette table comporte 2 enregistrements.
Le champ Id
se nomme la clef primaire de la table. Chaque valeur est unique.
Le champ login
est unique. Il ne peut exister 2 valeurs identiques, on comprend pourquoi. Sinon me demander.
Le données du champs mdp
sont cryptées.
Administration d'une base de données
Une base de données MySQL s'administre par un client comme dit précédemment.
En ligne de commande, le client se nomme mysql
(très original) et nécessite une très bonne connaissance du langage SQL pour discuter avec le SGBD (créer les droits d'accès au SGBD, créer une base de données, définir ses droits d'accès, son contenu).
Heureusement, il existe un paquet nommé phpMyAdmin permettant l'administration graphique du SGBD.
phpMyAdmin est construit sous la forme d'un site Web en PHP. Il nécessite donc pour fonctionner un serveur Web.
Apache2 est le serveur Web qu'il nous faut pour le rendre accessible par n'importe quel navigateur Web.
Pour résumer, afin de disposer d'un SGBD administrable, il faut :
Le SGBD MySQL
Le serveur Web Apache2
Le site d'administration de MySQL phpMyAdmin.
Ces trois entités doivent être installées et opérationnelles sur un ordinateur. Cela peut être votre poste de travail ou tout autre poste disponible sur le réseau.
Remarque : phpMyAdmin
Heureusement, l'installation du paquet phpMyAdmin se fait dans le bon dossier du serveur Apache2 (/srv/www/htdocs
). Ainsi, après son installation, il est directement accessible par un navigateur en tapant :
http://AdresseIP/phpMyAdmin/
ou
http://localhost/phpMyAdmin/
si l'installation se trouve sur votre ordinateur (ou Raspberry).
Mise en place de la BDD
Résumé des activités à effectuer côté serveur
En vous aidant de ce qui suit :
|
Installation des paquets
Sur une Raspberry, tapez :
sudo apt-get install mysql mysql-client sqlite3
Sur un PC sous linux openSuSE, utilisez Yast2, l'utilitaire d'administration et cherchez le serveur mariadb, nouveau nom pour MySQL.
Mise en service des serveurs MySQL et Apache2
Vous pouvez taper :
sudo service apache2 start
sudo service mysql start
ou
sudo systemctl start mysql.service
sudo systemctl start apache2.service
selon les systèmes.
Complément : Commande systemctl
start
pour lancer un service.
stop
pour stopper un service.
restart
pour redémarrer un service.
enable
pour lancer le service au démarrage du système.
disable
pour supprimer le lancement au démarrage du service.
Création de la base de données
La BDD se nommera bddFormation
.
Réalisez les opérations suivante avec phpMyAdmin après vous être connecté en tant qu'administrateur :
Cliquez sur New
pour procéder à la création de la base de données.
Entrez les renseignements ci-dessus et validez.
Créez la table nommée user
contenant 5 colonnes et validez.
Créez les 5 champs tels que représentés sur l'image ci-dessus.
Validez.
Quelques remarques sur la création de la table user :
Le champs
id
est une clef primaire (petite clef jaune à côté du nom du champ). Le champ est en auto-incrémentation. Il n'y a donc pas lieu de le remplir. C'est le serveur MySQL qui se charge d'établir de manière unique sa valeur.Le champs
login
dispose de la propriété UNIQUE. Cela signifie qu'il ne peut y avoir deux fois la même valeur.Le type de données VARCHAR doit être suivi du nombre de caractère maximum que peut contenir le champ.
C'est le bon moment pour créer un compte d'accès au SGBD avec tous les droits sur la BDD bddFormation
.
Insérez des données, au moins 1 enregistrement tel que représenté ci-dessus.
Notez :
Le champs
id
n'est pas rempli eu égard à ce que nous avons expliqué ci-dessus.La valeur du champs
mdp
(mot de passe) est chiffrée en utilisant l'algorithmeSHA1
. Il sera indispensable d'utiliser cet algorithme dans toute requête ayant besoin de comparer le mot de passe.
Le champs id s'est vu affecté la valeur 1 par le serveur MySQL.
Le mot de passe est crypté avec SHA1.
Et si on passait à la programmation maintenant ?
Connection à la base de données
Il existe un objet Qt pour cela : QSqlDatabase.
Exemple : Exemple de connexion à la base de données
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL"); // voir complément ci-dessous pour cette syntaxe particulière
db.setHostName("192.168.2.25"); // on peut mettre aussi localhost si le SGBD est local
db.setDatabaseName("test");
db.setUserName("user");
db.setPassword("user");
bool ok = db.open();
La méthode addDatabase()
gère la connexion et la communication avec un serveur MySQL.
Les renseignements ci-dessus sont indispensables à fournir.
N'oubliez pas de tester la variable ok
pour vérifier que la connexion est ouverte.
Complément : addDatabase est une méthode statique
En C++ une méthode static
signifie qu'elle est globale.
Il n'est pas nécessaire d'instancier un objet pour l'utiliser, elle est indépendante de tout objet.
Dans notre exemple, nous utilisons directement la méthode static addDatabase
()
pour nous retourner une instance d'objet QSqlDatabase. D'où cette syntaxe un peu particulière.
Effectuer des requêtes SQL sur la base de données
Les besoins les plus fréquents sont de récupérer/Ajouter/effacer/modifier des données de la BDD.
Cela se traduit par des requêtes SQL qui ont la forme suivante :
Récupérer des données | SELECT * FROM user ; | Récupère toutes les lignes de toutes les colonnes de la table |
Modifier des données | UPDATE user SET nom='toto' WHERE nom='ANTOINE'; | Met à jour le nom d'un utilisateur. |
Effacer des données | DELETE FROM user WHERE nom='ANTOINE' ; | Efface l'utilisateur. |
Ajouter des données | INSERT INTO user VALUES ('','TOTO', 'titi', 'toto', SHA1('toto')) ; | Ajoute un utilisateur en chiffrant le mot de passe. |
Complément : Requêtes SQL
La clause WHERE
peut s'appliquer à la plupart des requêtes pour filtrer un contenu.
Effectuer une requête d'extraction de données
C'est l'objet QSqlQuery qui sert à effectuer des requêtes sur la BDD.
Voici un exemple :
model = new QStringListModel(this);
QStringList liste;
QSqlQuery q(db);
q.exec("SELECT * FROM user ORDER BY nom");
while (q.next()) {
liste << q.value("login").toString();
} // for
model->setStringList(liste);
ui->lvTable->setModel(model);
Ligne 3 : Déclaration d'un objet q
relié à notre BDD.
Ligne 4 : Utilisation de la méthode exec()
avec comme paramètre la fameuse requête SQL. Cette requête récupère tous les enregistrements de la table user
par ordre alphabétique des valeurs de la colonne nom
.
Ligne 5 : Pour accéder au premier enregistrement récupéré, il faut absolument invoquer la méthode next()
. Boucle tant qu'il existe un enregistrement suivant.
Ligne 6 : Sauvegarde dans liste
de tous les valeurs de la colonne login
de la table. liste
est un objet QStringList
qui permet le stockage sous forme de tableau de QString
. Notez la méthode value()
qui permet de récupérer la valeur de l'enregistrement pour une colonne dont le nom est passé en paramètre.
Les deux dernières lignes permettent d'afficher les valeurs dans l'IHM (QListView
).
Effectuer une requête d'ajout dans la table user
QSqlQuery q(db);
q.prepare("INSERT INTO user VALUES (NULL, :name, :pname, :login, SHA1(:mdp))");
q.bindValue(":name", ui->leNom->text());
q.bindValue(":pname", ui->lePnom->text());
q.bindValue(":login", ui->leLogin->text());
q.bindValue(":mdp", ui->leMdp->text());
q.exec();
Ligne 2 : La méthode prepare()
permet de former une requête SQL qui contient des paramètres. Les paramètres sont indiqués par le signe :
suivi du nom du paramètre (exemple dans cette requête : :name
). La fonction SQL SHA1()
permet de chiffrer mot de passe avant de le sauver dans la BDD.
Lignes 3 à 6 : La méthode bindValue()
permet le remplacement d'un paramètre par sa valeur réelle.
Effectuer une requête de modification d'enregistrement
QSqlQuery q(db);
q.prepare("UPDATE user SET nam=:name, prenom=:pname, mdp=SHA1(:mdp) WHERE login=:login LIMIT 1");
q.bindValue(":name", ui->leNom->text());
q.bindValue(":pname", ui->lePnom->text());
q.bindValue(":login", ui->leLogin->text());
q.bindValue(":mdp", ui->leMdp->text());
q.exec();
Ligne 2 : Ici, la requête contient des paramètres insérés comme vu précédemment. La clause finale LIMIT 1
permet de n'agir que sur un seul enregistrement, le premier trouvé. Puisqu'on modifie un mot de passe, il ne devrait de toute façon y en avoir qu'un. C'est donc un garde-fou.
Effectuer une requête d'effacement d'enregistrement
QSqlQuery q(db);
q.prepare("DELETE FROM user WHERE login=:login LIMIT 1");
q.bindValue(":login", ui->leLogin->text());
q.exec();
Ligne 2 : Rien de nouveau si ce n'est cet exemple de requête d'effacement.
Résumé
Vous avez appris dans ce module à paramétrer le SGBD MySQL, créer une base de données, insérer des données grâce au site d'administration phpMyAdmin.
Vous avez vu comment accéder à la BDD par la programmation Qt.
Exerçons nous maintenant par un exercice.
Conception d'une base de données
Méthode MERISE
Pour faire vite, cela consiste à :
Chercher les informations dans le cahier des charges à stocker dans la BDD.
Établir un Modèle Conceptuel de Données (MCD).
En déduire un Modèle Logique de Données (MLD).
Traduire le MLD en une BDD physique (langage SQL).