James Hoffman

Techniques de l'Informatique - Cégep de Shawinigan

Menu

Base de données 1

Nous allons exploiter une base de données dans un environnement de développement plus concret sous la forme d'une application web PHP.

En root

bash
copier
apt update && apt install -y php php-mysql zip unzip

Configuration de la base de données

  • Créer une nouvelle base de données
  • Donner tous les privilèges sur cette BD à un utilisateur local identifié par un mot de passe

En usager standard

bash
copier
wget -O tweeties.zip https://bitbucket.org/jameshoffman/mariaphp-tweeties/get/master.zip

unzip tweeties.zip

cd jameshoffman-mariaphp-tweeties-<TAB>

cp .SECRETS.php SECRETS.php
# Inscrire les information de connexion à votre BD dans le fichier SECRETS.php

# Exécuter le fichier SQL de départ DATABASE.sql

php -S localhost:8080 webroot/index.php

# Parcourir l'app :)

Analyse

Identifier les tables, relations et multiplicités de la base de données ci-dessous.

sql
copier
drop table if exists likes, tweeties, users;

--
-- USERS
--

create table users (
id int unsigned auto_increment primary key,
username varchar(50) not null unique,
pwd varchar(64) not null
);

insert into
users(username, pwd)
values
('alice', 'pwda'),
('bob', 'pwdb'),
('charlie', 'pwdc');


--
-- TWEETIES
--

create table tweeties(
id int unsigned auto_increment primary key,
text varchar(240) not null,
created_at datetime not null default now(),
updated_at datetime,
user_id int unsigned not null,

foreign key (user_id) references users(id)
on delete cascade
);

insert into tweeties (text, created_at, user_id)
values
('My first tweetie', date_add(now(), interval -36 hour), 1),
('Another tweetie', default, 1),
('Tweetie, here I am!!!1!11!!', date_add(now(), interval -49 hour), 2);

--
-- LIKES
--

create table likes (
user_id int unsigned not null,
tweety_id int unsigned not null,

primary key (user_id, tweety_id),

foreign key (user_id) references users(id)
on delete cascade,

foreign key (tweety_id) references tweeties(id)
on delete cascade
);

insert into
likes
values
(1, 1),
(2, 1),
(2, 2);
SOLUTION 👀


Modifier la base de données pour y intégrer les éléments ci-dessous.

DÉFI 🔥
Effectuer les changements sans détruire les données présentes dans la BD!

Check

users

tweeties

  • text ne doit pas être vide
  • updated_at > created_at

Déclencheur

users

  • pwd respectant les conditions suivantes lors de l'insertion (il faudrait habituellement faire le même traitement lors des modifications)
    • Au moins 1 chiffre '\\d+'
    • Au moins 1 lettre minuscule '[a-z]+'
    • Au moins 1 lettre majuscule '[A-Z]+'
    • Exploiter la comparaison binaire pour respecter la case
    • Minimum 8 charactères
    • Il est plus facile de valider plusieurs conditions que de créer une seule expression régulière complexe
    • pwd devra également être hashé dans le déclencheur si le format est valide
    • SHA2(value, 256)

tweeties

  • updated_at
    • Initialisée à NULL à la création
    • Assigné à NOW() lors de la mise à jour

Vue

  • Encapsuler l'affichage des tweeties pour faciliter la réutilisation
  • La requête récupère tous les tweeties, le nom d'utilisateur de l'auteur et calcule
    • le nombre de like,
    • la dernière édition, soit la création ou la mise à jour
  • trié du plus récent au plus ancien
+----+-----------------------------+---------+----------------+---------------------+-------------+
| id | text | user_id | username | last_edit | likes_count |
+----+-----------------------------+---------+----------------+---------------------+-------------+
| 1 | MY FIRST TWEETIE | 1 | alice@mail.com | 2023-11-17 13:29:53 | 2 |
| 2 | Another tweetie | 1 | alice@mail.com | 2023-11-17 13:29:48 | 1 |
| 3 | Tweetie, here I am!!!1!11!! | 2 | bob@mail.com | 2023-11-15 12:29:53 | 0 |
+----+-----------------------------+---------+----------------+---------------------+-------------+
SOLUTION 👀
sql
copier
drop table if exists likes, tweeties, users;


--
-- USERS
--

create table users (
id int unsigned auto_increment primary key,
username varchar(50) not null unique check(username regexp '^[^@]+@[^@]+\\.[^@]+$'), -- check
pwd varchar(64) not null
);

--
-- TRIGGER
--

-- Pour pouvoir faire des ; dans le trigger
delimiter &&

-- Comment éviter de répéter le même code dans 2 triggers?
-- https://mariadb.com/kb/en/stored-procedures/
--
--
--
-- create or replace procedure validate (in pwd varchar(100))
-- begin
-- if( pwd not regexp '\\d+' ) then
-- signal sqlstate '45000' set message_text = 'password must contain at least 1 number.';
-- end if;
--
-- if( binary pwd not regexp '[a-z]+' ) then
-- signal sqlstate '45000' set message_text = 'password must contain at least 1 lower case letter.';
-- end if;
--
-- if( binary pwd not regexp '[a-z]+' ) then
-- signal sqlstate '45000' set message_text = 'password must contain at least 1 upper case letter.';
-- end if;
--
-- if( char_length(pwd) < 8) then
-- signal sqlstate '45000' set message_text = 'password must contain at least 8 characters long.';
-- end if;
-- end&&
--
--
--
-- Puis dans le trigger
--
-- call validate(new.pwd);

create or replace trigger users_insert_hash_password before insert on users for each row
begin

if( new.pwd not regexp '\\d+' ) then
signal sqlstate '45000' set message_text = 'password must contain at least 1 number.';
end if;

if( binary new.pwd not regexp '[a-z]+' ) then
signal sqlstate '45000' set message_text = 'password must contain at least 1 lower case letter.';
end if;

if( binary new.pwd not regexp '[a-z]+' ) then
signal sqlstate '45000' set message_text = 'password must contain at least 1 upper case letter.';
end if;

if( char_length(new.pwd) < 8) then
signal sqlstate '45000' set message_text = 'password must contain at least 8 characters long.';
end if;

set new.pwd = sha2(new.pwd, 256);

end&&

create or replace trigger users_update_hash_password before update on users for each row
begin

if( new.pwd not regexp '\\d+' ) then
signal sqlstate '45000' set message_text = 'password must contain at least 1 number.';
end if;

if( binary new.pwd not regexp '[a-z]+' ) then
signal sqlstate '45000' set message_text = 'password must contain at least 1 lower case letter.';
end if;

if( binary new.pwd not regexp '[a-z]+' ) then
signal sqlstate '45000' set message_text = 'password must contain at least 1 upper case letter.';
end if;

if( char_length(new.pwd) < 8) then
signal sqlstate '45000' set message_text = 'password must contain at least 8 characters long.';
end if;

set new.pwd = sha2(new.pwd, 256);
end&&

delimiter ;

insert into
users(username, pwd)
values
('alice@mail.com', 'pwdpwdA1'),
('bob@mail.com', 'pwdpwdB2'),
('charlie@mail.com', 'pwdpwdC3');

--
-- TWEETIES
--

create table tweeties(
id int unsigned auto_increment primary key,
text varchar(240) not null check(trim(text) != ''), -- CHECK
created_at datetime not null default now(),
updated_at datetime,
user_id int unsigned not null,

-- CHECK
constraint created_less_than_updated check(ifnull(created_at < updated_at, true)),

foreign key (user_id) references users(id)
on delete cascade
);

delimiter &&

create or replace trigger tweeties_create_created_at before insert on tweeties for each row
begin
set new.created_at = now();
end&&

create or replace trigger tweeties_update_updated_at before update on tweeties for each row
begin
set new.updated_at = now();
end&&

delimiter ;

insert into tweeties (text, created_at, user_id)
values
('My first tweetie', date_add(now(), interval -36 hour), 1),
("Another tweetie", date_add(now(), interval -5 second), 1),
("Tweetie, here I am!!!1!11!!", date_add(now(), interval -49 hour), 2);

--
-- LIKES
--

create table likes (
user_id int unsigned not null,
tweety_id int unsigned not null,

primary key (user_id, tweety_id),

foreign key (user_id) references users(id)
on delete cascade,

foreign key (tweety_id) references tweeties(id)
on delete cascade
);

insert into
likes
values
(1, 1),
(2, 1),
(2, 2);

--
-- VIEW
--

-- Pourrait être réutilisé dans la liste globale des tweeties ET le profil d'un tweetor
create or replace view tweets as
select
tweeties.id,
tweeties.text,
tweeties.user_id,
username,
coalesce(updated_at, created_at) as `last_edit`,
count(likes.user_id) as `likes_count`
from tweeties
inner join users on user_id = users.id
left join likes on tweeties.id = likes.tweety_id
group by tweeties.id
order by last_edit desc;

--
-- TESTS
--

select * from users;

-- pas email
-- insert into users values (default, 'a', 'pwdpwd123');

-- pas password valide
-- insert into users values (default, 'a@a.a', 'pwd');

-- text vide
-- insert into tweeties(text, user_id) values (' ', 1);

-- password update trigger
-- password invalide
-- update users set pwd = 'non' where id = 1;

-- password hash apres update
update
users
set
pwd = 'pwdaAa111'
where id = 1;

select * from users where id = 1;

-- tweeties

select * from tweets;

-- created_at trigger
-- insert into tweeties(text, created_at, user_id) values ('mauvaise created at', date_add(now(), interval 36 year), 1);

-- updated_at trigger
update
tweeties
set
text = upper(text)
where id = 1;

-- invalid created_at
-- update tweeties set created_at = now() where id = 1;

select * from tweets;

select * from tweets where user_id = 1;