2.1 - Implémentation | Base de données 2

2.1 - Implémentation

Fork open-source de MySQL qui avait été acheté par Oracle
Interopérabilité, pratiquement complète, avec l'écosystème MySQL
- Code SQL
- Librairies
- Installation et fichier exécutables
Liste des différences avec MySQL
Choisir MariaDB ou MySQL?

Installation sur Debian

apt update && apt install -y mariadb-server

systemctl status mariadb

mysql -u [username] -p -h [server_host] [database]
# Documentation et autre utilitaires, mysqldump, explain analyzer, etc.
# https://mariadb.com/kb/en/clients-utilities/

# Alternatives GUI
# SQLectron, Beekeeper Studio, MySQL Workbench, ...

📚 Create user

Un utilisateur est défini par son account name username@host

root@localhost disponible par défaut
'username'@'%' où % est un passe-partout

-- Securiser l'utilisateur, avec un mot de passe
CREATE USER 'alice'@'localhost' IDENTIFIED BY 'pwd';

-- ou avec le plugin unix_socket
CREATE USER 'alice'@'localhost' IDENTIFIED VIA unix_socket;

-- Plusieurs mecanismes
CREATE USER 'bob'@'localhost' IDENTIFIED VIA unix_socket OR mysql_native_password USING PASSWORD('pwd');

-- Assigner/Modifier le mot de passe
SET PASSWORD [FOR username@host] = PASSWORD('pwd');

-- Voir les utilisateurs existants, en tant que root ou equivalent
SELECT user, host, plugin, password FROM mysql.user;

📚 Grant, Revoke

-- Voir les permissions
SHOW GRANTS [FOR username@host];

-- Attribuer une permission
GRANT priv_type [(column_list)] [, priv_type [(column_list)], ...]
  ON [object_type] db_name.obj_name
  TO username@host [WITH GRANT OPTION];

-- Le priv_type représente l'élément autorisé
-- https://mariadb.com/kb/en/grant/#privilege-levels

-- Preciser le type d'objet correspondant au priv_type, si nécessaire
object_type:
    TABLE
  | FUNCTION
  | PROCEDURE

-- Le wildcard * peut être utilisé pour le db_name et obj_name

-- WITH GRANT OPTION permet à l'utilisateur d'attributer a d'autres utilisateurs les permissions qu'il possède

-- Pour retirer une permission, REVOKE ... FROM

📚 Accès distant

# /etc/mysql/mariadb.conf.d/50-server.cnf

bind-address = A.B.C.D
# 0.0.0.0 est un passe-partout pour toutes les interfaces
# localhost pour BLOQUER les connexion externes

📚 Create database

CREATE [OR REPLACE] DATABASE [IF NOT EXISTS] db_name

DROP DATABASE [IF EXISTS] db_name

SHOW DATABASES [LIKE 'pattern']

📚 Create table

CREATE [OR REPLACE] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)

CREATE [OR REPLACE] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [IGNORE | REPLACE] [AS] SELECT ...

CREATE [OR REPLACE] TABLE [IF NOT EXISTS] tbl_name LIKE old_table_name

create_definition:
  { col_name column_definition | constraint_definition  | index_definition }

column_definition:
  data_type
    [NOT NULL | NULL] [DEFAULT default_value | (expression)]
    [ON UPDATE [NOW | CURRENT_TIMESTAMP] [(precision)]]
    [AUTO_INCREMENT] [ZEROFILL] [UNIQUE | [PRIMARY KEY]]
    [INVISIBLE] [CHECK (expression)]
  | data_type AS (expression) [VIRTUAL | PERSISTENT | STORED] [UNIQUE]

constraint_definition:
   CONSTRAINT [constraint_name] CHECK (expression)

index_definition:
    {INDEX | KEY} [index_name](index_col_name,...) 
  | {FULLTEXT | SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) 
  | [CONSTRAINT [symbol]] PRIMARY KEY  (index_col_name,...) 
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] (index_col_name,...) 
  | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) 
    REFERENCES tbl_name (index_col_name,...)
      [ON DELETE { RESTRICT | CASCADE | SET NULL }]
      [ON UPDATE { RESTRICT | CASCADE | SET NULL }]

index_col_name:
    col_name [(length)] [ASC | DESC]

DESCRIBE tbl_name;

SHOW CREATE TABLE tbl_name;

📚 Colonne calculée, AS

Valeur d'une colonne calculée selon d'autres champs de la même table.
- VIRTUAL: N'occupe pas d'espace, calculé à chaque requête
- PERSISTENT: Stocké physiquement, calculé au INSERT et UPDATE

CREATE TABLE IF NOT EXISTS users (
  firstname VARCHAR(50) NOT NULL, 
  lastname VARCHAR(50) NOT NULL,
  fullname VARCHAR(101) AS (CONCAT(firstname, ' ', lastname))
);

📚 Check

Définit des contraintes d'intégrités personnalisées sur une colonne OU une table.
- Ne peut utiliser de SELECT ou routine personnalisée, ni non-déterministe

CREATE TABLE IF NOT EXISTS users (
  email VARCHAR(50) NOT NULL CHECK (email REGEXP '^(.+)@(.+)\.(.+)$'), 
);

📚 Full-text search

Type d'index spécifique qui permet d'exploiter l'engin d'évaluation d'expressions de recherche textuelle(tokens >= 4 caractères)
- NATURAL LANGUAGE MODE: Algorithme interne
- BOOLEAN MODE: Opérateurs(*, -, >, <, ...)

CREATE TABLE IF NOT EXISTS item (
  name VARCHAR(50) NOT NULL, 
  description VARCHAR(50) NOT NULL,

  FULLTEXT INDEX `fulltext_name` (name),
  FULLTEXT INDEX `fulltext_description` (description),
  FULLTEXT INDEX `fulltext_name_description` (name, description)
);

SELECT * FROM projects 
WHERE MATCH (name) AGAINST ('cats dogs'); 
-- name seulement, un index contenant exactement les memes colonnes que le MATCH doit exister

SELECT * FROM projects 
WHERE MATCH (name, description) AGAINST ('cats dogs');

SELECT *, (MATCH (name, description) AGAINST ('dogs cats')) as `rank` 
FROM projects 
WHERE MATCH (name, description) AGAINST ('dogs cats');

SELECT * FROM projects 
WHERE MATCH (name, description) AGAINST ('dogs -cats' IN BOOLEAN MODE);

Insert et Delete offre la clause RETURNING

INSERT [IGNORE] INTO tbl_name [(col,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE
    col=expr
    [, col=expr] ... ] 
[RETURNING select_expr  [, select_expr ...]]

-- INSERT ... SELECT abrégé, ON DUPLICATE et RETURNING aussi disponibles
INSERT INTO tbl_name [(col,...)]
SELECT ...

DELETE FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
[RETURNING select_expr [, select_expr ...]]

Update et Delete offre une syntaxe pour plusieurs tables

UPDATE table_references
SET col1={expr1|DEFAULT} [, col2={expr2|DEFAULT}] ...
[WHERE where_condition]

DELETE tbl_name [, tbl_name] ...
FROM table_references 
[WHERE where_condition]

📚 Structures de contrôle

IF
CASE
IFNULL

📚 Chaîne de charactères

CHAR_LENGTH
CONCAT
LOWER, UPPER
SUBSTRING

📚 Date et heure

CURDATE
CURTIME
DATEDIFF
TIMESTAMPDIFF
DAY, MONTH, YEAR
DATE_FORMAT

📚 Numérique

ABS
DIV, MOD
CEIL, FLOOR
ROUND

COUNT
SUM
AVG, MIN, MAX

Les Window Functions permettent d'appliquer certaines aggrégation pour chaque ligne et ses enregistrements reliés SANS utiliser le GROUP BY

Permet de récupérer des informations dans une ou plusieurs autres tables.

L'instruction WITH permet de réutiliser une sous-requête comme une table dans une requête

Permet de réunir des données de table différentes, habituellement réunies par des Foreign Keys.

INNER JOIN

LEFT / RIGHT JOIN

CROSS JOIN

Crée une représentation virtuelle des données, évaluée lors de la requêtes, qui peut être utilisée comme une table

CREATE VIEW `projects_for_users` AS
  SELECT  p.name, p.description, p.creation_date, 
          u.fullname, PROJECT_COLLABORATORS(p.id) as collaborators 
  FROM projects p
  JOIN users u ON p.owner = u.id
  ORDER BY p.owner;

SQL supporte les instructions traditionnelles d'un langage de programmation

Bloc
Variables
Conditions
Boucles

Permet de réagir aux événements sur les tables de base de données(BEFORE ou AFTER) INSERT, UPDATE, DELETE pour chaque ligne affectée

Offre les identifiants NEW et OLD
On peut SELECT, INSERT, UPDATE, DELETE, utiliser des routines et SIGNAL

CREATE TRIGGER users_hash_password_BEFORE_INSERT BEFORE INSERT ON users FOR EACH ROW
BEGIN
    IF (NEW.password != '') THEN    
        SET NEW.password = SHA2(NEW.password, 256);
    END IF;
END

Permet de créer une unité de code SQL réutilisable.

DELIMITER $$

CREATE FUNCTION PROJECT_COLLABORATORS(p_project_id INT UNSIGNED) RETURNS LONGTEXT 
NOT DETERMINISTIC READS SQL DATA
BEGIN
    DECLARE collaborators LONGTEXT;

    SELECT GROUP_CONCAT(u.fullname SEPARATOR ', ') INTO collaborators
    FROM users_projects up
    JOIN users u ON up.user_id = u.id
    WHERE up.project_id = p_project_id;

    RETURN collaborators;
END$$

DELIMITER ;

📚 Procedure

Retourne, ou pas, des données ou un ensemble de données
Ne peut pas être utilisé dans une requête SELECT, WHERE, etc.
- On doit utiliser la commande CALL pour l'appeler

📚 Function

Retourne obligatoirement une valeur, pas d'ensemble
Peut être utilisé dans une requête SELECT, WHERE, etc.
On peut créer des fonctions d'aggrégation personnalisées

Génère une erreur SQL qui interrompt l'opération en cours

Stored procedures, Triggers
45000 est le code d'erreur générique

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid email provided.';

Permet de planifier l'exécution d'une commande

CREATE [OR REPLACE]  EVENT  [IF NOT EXISTS] event_name    
ON SCHEDULE schedule [ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE]
DO sql_statement;

schedule:
  AT timestamp [+ INTERVAL interval] ...
| EVERY interval 
  [STARTS timestamp [+ INTERVAL interval] ...] 
  [ENDS timestamp [+ INTERVAL interval] ...]

interval:
  quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
            WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
            DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

MySQL Connections

Identification des serveurs

Enregistrement de favoris, +
TCP/IP over SSH
Clic-droit pour modifier

Manipulations d'un serveur

Panneau de navigation de gauche, onglet SCHEMAS

Bouton refresh pour actualiser les objets
Double-clic sur une BD pour la rendre active, use
Liste des BD et des objets
- Clic-droit offre plusieurs options
- Survol d'une table affiche des boutons d'action:
- Info(lecture seule),
- Propriétés(éditable),
- Aperçu des données(Result grid éditable, ne pas oublier de APPLY, import/export)
- Survol d'une procedure/function permets l'exécution, un pop-up demande les paramètres si nécessaire

Sur la BD active, la barre d'outils horizontale offre des actions rapides

Nouveau/Ouvrir script SQL
Inspecteur(affiche les caractéristiques de l'item sélectionné)
Création d'items: DB, Table, View, Procedure, Function

Éditeur SQL

Auto-complétion des objets de la DB (ne pas oublier de rendre active)
En utilisant la création/ouverture de fichiers SQL par la barre d'outils ouvre un nouvel onglet
Les raccourcis de la sous-barre horizontale d'outils de l'éditeur affectent l'onglet courant
Éclairs:
- Exécution complète, Exécution du code sélectionné: CTRL SHIFT ENTER
- Exécution de la commande sous le curseur: CTRL ENTER
Désactiver l'arrêt sur les erreurs

1.3 - Laboratoire 1 🔔

2.2 - Laboratoire 2 🔔

Base de données 2

2.1 - Implémentation

2.1 - Implémentation

MariaDB

Installation sur Debian

Utilisateurs

📚 Create user

📚 Grant, Revoke

📚 Accès distant

BD, tables et colonnes

📚 Create database

📚 Create table

📚 Colonne calculée, AS

📚 Check

📚 Full-text search

Insert, Update, Delete

Fonctions

📚 Structures de contrôle

📚 Chaîne de charactères

📚 Date et heure

📚 Numérique

Aggrégation

Sous-requêtes

Jointures

INNER JOIN

LEFT / RIGHT JOIN

CROSS JOIN

Vues

Instructions de programmation

Déclencheurs

Routines

Signal

Événements

MySQL Workbench

MySQL Connections

Manipulations d'un serveur

Éditeur SQL