Des pages avec SQL

Des exemples sur le site modèle : Page avec SQL du site modèle

I. Avant de commencer

La structure du site

Dans le fichier mkdocs.yml vers la ligne 125, dans plugins il doit y avoir - sqlite-console absolument après : - pyodide_macros

• Dans le fichier .gitlab-ci.yml environ à partir de la ligne 15, modifier si nécessaire pour avoir ceci :

  build:
  stage: build
  before_script:
      - python -m venv .venv
      - source .venv/bin/activate
      - pip install --upgrade pip
      - pip install -r requirements.txt
  

• Dans le fichier requirements.txt ajouter la dernière ligne

Fichier requirements.txt

pyodide-mkdocs-theme
mkdocs-awesome-pages-plugin
mkdocs-enumerate-headings-plugin
mkdocs-exclude-search
mkdocs-sqlite-console

• dans le fichier xtra/stylsheets/ajustements.css ajouter à la fin :

  /*
  Pour cacher le div vide avec les erreurs s’il y en a lorsqu'on exécute du code caché en SQL
  */
  div.sqlhidden > * {
      display: none;
      }
  

Syntaxe pour utiliser un IDE avec SQL

Remarque : ancienne syntaxe utilisées avec une version de PMT antérieure à Pyodide-MkDocs-Theme v.4.4.6

Cette ancienne syntaxe reste compatible avec les versions suivante de PMT, mais il est recommandé d'utiliser la nouvelle syntaxe.

Ancienne syntaxe

{!{ sqlide titre="essai" espace="page_sql"}!}

Comme précisé dans la documentation, on peut depuis Pyodide-MkDocs-Theme v.4.4.6 utiliser la syntaxe suivante :

{{ sqlide(titre="essai", espace="page_sql") }}

La syntaxe

• Noter les parenthèses autour des arguments,
• Noter des virgules entre les arguments,
• Noter que les guillemets autour des valeurs des arguments sont indispensables.

Le paramètre espace

Utiliser le même argument pour le paramètre espace dans différents IDE permet de rendre accessible dans tous les IDE ayant le même argument (par exemple espace="exercice_mediatheque") les bases de données créées, tables créées, modifiées etc.
⚠️ C'est indispensable pour des IDE correspondant à différentes questions portant sur la même base, ou pour un exercice dont les questions s'enchainent.

Attention : Intégrité des contraintes de clef étrangère

Par défaut SQLite ne vérifie pas l’intégrité des contraintes de clef étrangères. Il faut lui dire explicitement de le faire avec le code suivant :
PRAGMA foreign_keys=1;

Il est possible de rajouter ce code :

• dans un fichier caché donné par init= (exemple dans : créer un IDE avec du code caché)
• ou dans un fichier exécuté automatiquement, de façon cachée (exemple dans : créer un IDE avec une base de donnée cachée. Dans cet exemple il s'agit du fichier option.sql)

Chemins

Comme pour les exerccies avec des IDE Python, les chemins sont relatifs depuis le fichier .md en cours

Documentation officielle

Se reporter ici : documentation

Remarque

Ce qui est au début de cette documentation donnée en lien ne correspond pas aux sites créés avec PMT (comme ce tutoriel ou le site modèle associé). De même ne pas tenir compte de la fin de la documentation (Dans le cas de l'utilisation du plugin des macros seul, avec un ajout à faire dans le fichier main.py).

II. Détail des syntaxes avec des exemples

Créer un IDE SQL vide

code à copier

{{ sqlide(titre="Votre code SQL", espace="exercices_sql") }}

Votre code SQL
Exécuter

Créer un IDE SQL avec du code prérempli

code à copier

{{ sqlide(titre="tester SQL avec du code pré-saisi", sql="sql/code_1.sql", espace="exercices_sql") }}

tester SQL avec du code pré-saisi
-- les tirets permettent d'insérer des commentaires DROP TABLE IF EXISTS eleves; -- notez bien ce ; en fin de chaque bloc d'instructions CREATE TABLE eleves ( id INTEGER PRIMARY KEY , nom TEXT NOT NULL , prenom TEXT NOT NULL , date_naiss DATE FORMAT 'dd.mm.yyyy' NOT NULL , classe TEXT NOT NULL , nb_heures INTEGER ); -- notez bien de nouveau ce ; Exécuter

Le fichier code_1.sql

-- les tirets permettent d'insérer des commentaires
DROP TABLE IF EXISTS eleves; -- notez bien ce ; en fin de chaque bloc d'instructions
CREATE TABLE eleves (
    id INTEGER PRIMARY KEY ,  
    nom TEXT NOT NULL ,
    prenom TEXT NOT NULL ,
    date_naiss DATE FORMAT 'dd.mm.yyyy' NOT NULL , 
    classe TEXT NOT NULL ,
    nb_heures INTEGER

); -- notez bien de nouveau ce ;

Syntaxe simplifiée si les trois options titre, sql et espace sont présentes

On peut dans ce cas-là en gardant le même ordre ne pas écrire le nom des options :

code à copier

{{ sqlide("tester SQL avec du code pré-saisi", "sql/code_1.sql", "exercices_sql") }}

tester SQL avec du code pré-saisi
-- les tirets permettent d'insérer des commentaires DROP TABLE IF EXISTS eleves; -- notez bien ce ; en fin de chaque bloc d'instructions CREATE TABLE eleves ( id INTEGER PRIMARY KEY , nom TEXT NOT NULL , prenom TEXT NOT NULL , date_naiss DATE FORMAT 'dd.mm.yyyy' NOT NULL , classe TEXT NOT NULL , nb_heures INTEGER ); -- notez bien de nouveau ce ; Exécuter

Créer un IDE avec du code caché

Ci-dessous le code du fichier caché donné par init= :

Fichier init_1.sql

-- Pour la vérification de l'intégrité des contraintes de clé étrangère : 
PRAGMA foreign_keys=1;

-- Pour créer la table
DROP TABLE IF EXISTS employees;
CREATE TABLE employees
(
    id          integer,
    name        text,
    designation text,
    manager     integer,
    hired_on    date,
    salary      integer,
    commission  float,
    dept        integer
);

INSERT INTO employees VALUES (1,'JOHNSON','ADMIN',6,'1990-12-17',18000,NULL,4);
INSERT INTO employees VALUES (2,'HARDING','MANAGER',9,'1998-02-02',52000,300,3);
INSERT INTO employees VALUES (3,'TAFT','SALES I',2,'1996-01-02',25000,500,3);
INSERT INTO employees VALUES (4,'HOOVER','SALES I',2,'1990-04-02',27000,NULL,3);
INSERT INTO employees VALUES (5,'LINCOLN','TECH',6,'1994-06-23',22500,1400,4);
INSERT INTO employees VALUES (6,'GARFIELD','MANAGER',9,'1993-05-01',54000,NULL,4);

code à copier

{{ sqlide(titre="init + code", sql="sql/code_sql.sql", espace="exercices_sql", init="sql/init_1.sql") }}

init + code
SELECT * FROM employees; SELECT COUNT(*) FROM employees; Exécuter

Syntaxe simplifiée si les trois options titre, sql et espace sont présentes

On peut dans ce cas-là en gardant le même ordre ne pas écrire le nom des trois premières options :

code à copier

{{ sqlide("init + code", "sql/code_sql.sql", "exercices_sql", init="sql/init_1.sql") }}

init + code
SELECT * FROM employees; SELECT COUNT(*) FROM employees; Exécuter

Créer un IDE avec un fichier SQL caché exécuté automatiquement et une base de donnée cachée chargée automatiquement

Depuis la version 2.1.0 de mkdocs-sqlite-console base et init peuvent être utilisés ensemble. Si les arguments base et init sont tous les deux utilisés, la base est chargée avant que le code du fichier init ne soit exécuté.

fichier init_integrite.sql

PRAGMA foreign_keys=1;

code à copier pour un exemple d'exercice

???+ question "Exercice avec plusieurs questions"

    **Question 1 :** Combien de livres contiennent la chaîne 'Astérix' dans leur titre ?

    {{ sqlide(titre="", sql="sql/asterix_q1.sql", espace="mediatheque", base="bases/Livres.db", init="sql/init_integrite.sql") }}

    ??? success "Solution"

        {{ sqlide(titre="", sql="sql/asterix.sql", espace="mediatheque", base="bases/Livres.db", init="sql/init_integrite.sql") }}

    **Question 2 :** Peut-on supprimer n’importe quel enregistrement ?  
    Essayons de supprimer le livre "Hacker's Delight" sachant que son code ISBN est 978-0201914658.

    {{ sqlide(titre="", sql="sql/exemple.sql", espace="mediatheque", base="bases/Livres.db", init="sql/init_integrite.sql") }}

    ??? success "Solution"
        Le SGBD nous indique que supprimer ce livre, violerait la contrainte de clé étrangère.  
        En effet, le code isbn est une clé étrangère dans la table `auteur_de`.

Exercice avec plusieurs questions

Question 1 : Combien de livres contiennent la chaîne 'Astérix' dans leur titre ?

Sql
SELECT ... FROM ... WHERE ... Exécuter

Solution

Sql
SELECT COUNT(titre) FROM livre WHERE titre LIKE "%Astérix%"; Exécuter

Question 2 : Peut-on supprimer n’importe quel enregistrement ?
Essayons de supprimer le livre "Hacker's Delight" sachant que son code ISBN est 978-0201914658.

Sql
DELETE FROM livre WHERE isbn = '978-0201914658'; Exécuter

Solution

Le SGBD nous indique que supprimer ce livre, violerait la contrainte de clé étrangère.
En effet, le code isbn est une clé étrangère dans la table auteur_de.

Avec les versions antérieures

⚠️ Avant la version 2.1.0 de mkdocs-sqlite-console base et init ne pouvaient pas être utilisés en même temps.

Dans l'exemple suivant :

• le code caché SQL du fichier pointé par la paramètre sql (fichier option.sql) est auto-exécuté grâce au paramètre autoexec=True. Souvenons nous qu' avec SQLite utilisé sur ce site l'instruction PRAGMA foreign_keys=1; est nécessaire pour l'intégrité de contrainte de clé étrangère.
• la base de donnée livres est chargée de façon cachée grâce au paramètre hide=True

fichier option.sql

PRAGMA foreign_keys=1;

code à copier pour un exemple d'exercice

???+ question "Exercice avec plusieurs questions"


    {{ sqlide(titre="avec base Livres", sql="sql/option.sql", espace="mediatheque_2", base="bases/Livres.db", autoexec=True, hide=True) }}


    **Question 1 :** Combien de livres contiennent la chaîne 'Astérix' dans leur titre ?

    {{ sqlide(titre="", sql="sql/asterix_q1.sql", espace="mediatheque_2") }}

    ??? success "Solution"

        {{ sqlide(titre="", sql="sql/asterix.sql", espace="mediatheque_2") }}

    **Question 2 :** Peut-on supprimer n’importe quel enregistrement ?  
    Essayons de supprimer le livre "Hacker's Delight" sachant que son code ISBN est 978-0201914658.

    {{ sqlide(titre="", sql="sql/exemple.sql", espace="mediatheque_2") }}

    ??? success "Solution"
        Le SGBD nous indique que supprimer ce livre, violerait la contrainte de clé étrangère.  
        En effet, le code isbn est une clé étrangère dans la table `auteur_de`.

Exercice avec plusieurs questions

avec base Livres
PRAGMA foreign_keys=1; Exécuter

Question 1 : Combien de livres contiennent la chaîne 'Astérix' dans leur titre ?

Sql
SELECT ... FROM ... WHERE ... Exécuter

Solution

Sql
SELECT COUNT(titre) FROM livre WHERE titre LIKE "%Astérix%"; Exécuter

Question 2 : Peut-on supprimer n’importe quel enregistrement ?
Essayons de supprimer le livre "Hacker's Delight" sachant que son code ISBN est 978-0201914658.

Sql
DELETE FROM livre WHERE isbn = '978-0201914658'; Exécuter

Solution

Le SGBD nous indique que supprimer ce livre, violerait la contrainte de clé étrangère.
En effet, le code isbn est une clé étrangère dans la table auteur_de.

Exercices en plusieurs versions

On peut mettre des IDE avec SQL dans des volets.

Code à copier

Combien de livres contiennent la chaîne 'Astérix' dans leur titre ?
=== "Version vide"

    {{ sqlide(titre="", espace="mediatheque") }}

=== "Version à compléter"

    {{ sqlide(titre="", sql="sql/exercice_q1.sql", espace="mediatheque") }}

Combien de livres contiennent la chaîne 'Astérix' dans leur titre ?

Version videVersion à compléter

Sql
Exécuter

Sql
SELECT ... FROM ... WHERE ... Exécuter

Signature exacte de la macro sqlide

Voici la déclaration exacte de la macro, et les valeurs par défaut associées aux différents arguments :

🐍 Script Python

sqlide(
    self,
    titre='Sql',
    sql='',
    espace=None,
    *,
    base='/',
    init='',
    hide=False,
    autoexec=False,
)

• Les trois premiers arguments, titre, sql et espace sont des arguments positionnels avec des valeurs par défaut : il n'est pas obligatoire de mettre le nom de l'argument (mais dans ce cas, il faut respecter l'ordre de déclaration).

• Les arguments après *, sont des arguments nommés. Ils doivent impérativement être renseignés en précisant le nom de l'argument.

• Il est toujours possible de renseigner les noms des arguments positionnels si on le souhaite (dans ce cas, il n'est pas indispensable de respecter leur ordre dans la déclaration).

Crédit pour la réalisation du plugin mkdocs-sqlite-console et la documentation : Rafaël Lopez

Crédit pour l'adaptation à PMT : Frédéric Zinelli