====== Base de données ======

La base de données sera donc une base Mysql. Elle présente l'avantage d'être rapide et facile d'utilisation.

===== Introduction =====

La base de données sera hébergée sur un serveur linux debian, toutes les commandes suivantes seront pour debian, il faudra adapté si vous utilisez un autre système d'exploitation.

===== Architecture =====

Le plus important dans une base de données c'est son architecture. Il faut toujours bien réfléchir à l'utilisation que l'on souhaite en faire et essayer de l'organiser de la manière la plus logique possible.

Dans notre cas notre base de données sera constituée de trois tables différentes, l'une stockant les différents utilisateurs, l'une stockant la liste des projets réalisés au sein du lab et enfin une qui recense l'ensemble l'historique des accès au lab.

Il est également nécessaire de définir le type de données pour chacun des éléments des tables, il faut que le type soit le plus léger possible afin que la base de données reste rapide, mais que cela permette de stocker l'ensemble des données possibles. Ainsi le prénom de l'utilisateur sera stocké sur 15 caractères ce qui est en général suffisant. Au final la base de données ressemblera à ceci:
{{ :mysql_erd.png?nolink | Schéma de la base de données}}

===== Création de la base de données =====

À présent que l'architecture de la base de données est faite, il ne reste plus qu'à la créer, nous supposerons ici que Mysql est déjà installé sur votre serveur et que vous connaissez le mot de passe root.

Tout d'abord il faut lancer mysql grâce à la commande suivante

<code bash> mysql -u root -p </code>

Puis on va créer notre base de données grâce à la commande suivante :

<code sql> CREATE DATABASE RFID; </code>

À présent qu'elle est crée il faut la sélectionner. Pour se faire, on utilise la commande suivante :

<code sql> USE RFID; </code>

Il ne nous reste plus qu'à créer les différentes tables que nous avons présentées plutôt.

La table stockant l'historique d'accès
<code sql> CREATE TABLE `ACCESS_HISTORY` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `rfid_id` bigint(20) unsigned NOT NULL,
  `access_time` datetime DEFAULT NULL,
  `location` varchar(30) DEFAULT NULL,
  `id_project` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
);</code>

La base stockant la liste des projets
<code sql> CREATE TABLE `PROJECT` (
  `id_project` bigint(20) NOT NULL AUTO_INCREMENT,
  `project_name` varchar(30) DEFAULT '',
  `project_description` text,
  PRIMARY KEY (`id_project`)
);</code>

La base stockant la liste des utilisateurs
<code sql> CREATE TABLE `USERS` (
  `rfid_id` bigint(20) unsigned NOT NULL,
  `student_id` int(10) unsigned DEFAULT NULL,
  `phone_number` int(10) unsigned DEFAULT NULL,
  `last_name` varchar(15) DEFAULT 'Unset',
  `first_name` varchar(15) DEFAULT 'Unset',
  `email` varchar(40) DEFAULT 'Unset',
  `sex` char(1) DEFAULT 'N',
  `register_date` datetime DEFAULT NULL,
  `member_type` int(11) DEFAULT NULL,
  `authorization` bigint(20) DEFAULT NULL,
  `current_project_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`rfid_id`)
); </code>

===== Autoriser l’accès distant =====

Maintenant que la base de données est créée, il faut en autoriser l'accès à distance. En effet les bases Mysql ne permettent qu'une utilisation locale par defaut. 
Par mesure de sécurité nous allons également créer un utilisateur mysql dédié à notre base RFID.

On commence donc par créer notre utilisateur grâce à la commande suivante 

<code sql> CREATE USER 'RFID'@'%' IDENTIFIED BY 'somepassword';</code>

Ou RFID représente le nom d'utilisateur et somepassword le mot de passe qui lui est associé.

Puis on donne à cet utilisateur uniquement les privilèges dont il a besoin sur notre base de données nouvellement créée. Ainsi en cas de brèche on limite les dégâts.

<code sql>GRANT SELECT, INSERT, DELETE, UPDATE ON RFID TO 'RFID'@'%';</code>

Il ne nous reste plus qu'à autoriser les connexions extérieures. Pour ce faire, il faut modifier le ficher de configuration de mysql. On utilisera un éditeur de texte pour se faire.

<code bash>nano /etc/mysql/my.cnf</code>

et modifier la ligne suivante:

<code text>bind-address            = 127.0.0.1</code>

par 

<code text>bind-address            = 37.187.4.166</code>

Ou 37.187.4.166 représente l'adresse IP du serveur sur lequel mysql est installé.
Puis redémarrer le service mysql 
<code bash>/etc/init.d/mysql restart</code>

===== Utilisation de la base =====
Voici quelques commandes permettant d'interagir avec notre base de donnée:
====Ajout d'un utilisateur====
<code sql>INSERT INTO USERS (rfid_id,student_id,phone_number,last_name,first_name,email,sex,register_date,member_type,authorization,current_project_id) 
VALUES
(123456, 3200222, 0612345678,'John','Doe','john.doe@gmail.com','H',NOW(),1, 0,10); </code>
====Affichage d'un utilisateur à partir de son identifiant rfid====
<code sql>SELECT * FROM USERS WHERE rfid_id=123456;</code>
Ou 123456 est l'identifiant RFID recherché
====Affichage de l'ensemble des accès====
<code sql>SELECT * FROM ACCESS_HISTORY;</code>
====Affichage de l'ensemble des accès avec le nom, prénom de l'usager et titre du projet sur lequel il travail====
<code sql>SELECT USERS.last_name,USERS.first_name,ACCESS_HISTORY.location, ACCESS_HISTORY.access_time,PROJECT.project_name 
FROM ACCESS_HISTORY 
LEFT OUTER JOIN USERS ON ACCESS_HISTORY.rfid_id = USERS.rfid_id 
LEFT OUTER JOIN PROJECT ON ACCESS_HISTORY.id_project=PROJECT.id_project;</code>