Sommaire
Est-ce de la sorcellerie ?
Tout d'abord, mettons les choses au clair : votre application aura bien un backend et un frontend, il n'y a pas de miracle. Mais pour les applications simples, le frontend est souvent juste un assemblage de composants standards, et le backend qu'une sorte de passe-plats entre le frontend et la base de données. Ce que permet SQLPage, et que nous allons étudier ici c'est :
- d'invoquer des composants prédéfinis d'interface graphique en donnant simplement leur nom et quelques paramètres,
- de faire le lien entre l'interface graphique et la base de données avec de simples fichiers SQL qui sont exécutés automatiquement lorsque l'utilisateur charge une page.
Comment ça marche ?
SQLPage est un simple serveur web : c'est un programme qui tourne en continu, attend des requêtes HTTP, et dès qu'il en reçoit une, fournit une réponse.
Si SQLPage reçoit une requête vers /site/contenu.sql?article=42, il va chercher un fichier nommé contenu.sql, dans un dossier nommé site. Il va ensuite lire le contenu du fichier, et l'interpréter comme une série de requêtes SQL, qui vont être préparées. Elles seront ensuite exécutées une par une. Si l'une de ces requêtes fait référence à une variable nommée $article, la valeur 42 venant de la requête de l'utilisateur lui sera associée.
Les requêtes sont envoyées à la base de données, et celle-ci commence à retourner des lignes de données, une par une.
Les lignes vont ensuite être analysées au fil de l'eau par SQLPage, qui va décider quel composant graphique renvoyer au navigateur web, et quelles données utiliser pour remplir le composant.
Construisons une application
Pour rendre tout ce discours plus concret, créons ensemble une petite application, entièrement en SQL, et en vingt minutes.
Pour vous donner un avant-goût, voilà ce à quoi nous allons arriver au final
| Page d'accueil |
Gestion d'utilisateurs |
Liste de dépenses |
Graphique de dettes |
 |
 |
 |
 |
Il n'y a pas toutes les fonctionnalités de l'application originelle, mais c'est seulement 83 lignes de code, grâce à tout ce que SQLPage gère automatiquement. Et le résultat est quand même plus joli que l'original.
Notre application : une application opensource pour faire ses comptes entre amis
Nous allons créer une application pour faire ses comptes entre amis. Elle aura les fonctionnalités suivantes :
- créer un nouveau compte de dépenses partagé
- ajouter des participants et visualiser la liste des participants existants
- pour chaque participant :
- ajouter une dépense
- voir les dépenses des autres
- voir combien il doit au reste du groupe ou combien lui est dû
Première étape : choisir un schéma pour notre base de données
Et oui, on ne va pas passer quatre jours à choisir un framework JavaScript, un framework CSS, un ORM, ou autres choses compliquées que l'on fait quand on commence une application web classique. Avec SQLPage, on rentre tout de suite dans le cœur du sujet, et ce qui sera important pour la suite: quelles données stockerons-nous, et sous quelle forme.
Ici, je propose le schéma suivant :
- une table
expense_group pour nos comptes de dépenses partagés, avec un identifiant numérique et un nom.
- une table
group_member pour les utilisateurs, avec un identifiant numérique, un nom, et l'identifiant du compte partagé auquel il appartient.
- une table
expense pour les dépenses, avec l'identifiant de l'utilisateur ayant fait la dépense, une description, et un montant. Pour cet exemple, nous ne prendrons pas en compte le cas où une dépense peut ne concerner qu'une partie du groupe; ce sera simple à ajouter dans un second temps.
Deuxième étape : création de la base de données et lancement de SQLPage
C'est parti ! Téléchargeons SQLPage sur le site officiel.
Créons un dossier pour notre application, et dans ce dossier créons la structure de fichiers suivante:
├── sqlpage
│ ├── migrations
│ │ └── 000_base.sql
│ └── sqlpage.json
└── sqlpage.bin
Nous créons donc les fichiers suivants:
-
sqlpage/migrations/000_base.sql dans lequel nous définirons la structure de notre base de données
-
sqlpage/sqlpage.json dans lequel nous mettrons pour l'instant simplement la ligne suivante: {"database_url": "sqlite://:memory:"}. Cela nous permet de travailler avec une base de données temporaire en mémoire. Nous le modifierons plus tard pour nous connecter à une base de données plus pérenne.
Intéressons-nous d'abord à sqlpage/migrations/000_base.sql. Pour créer la structure de base de données définie plus tôt, utilisons quelques instructions de création de table :
CREATE TABLE expense_group(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
);
CREATE TABLE group_member(
id INTEGER PRIMARY KEY AUTOINCREMENT,
group_id INTEGER REFERENCES expense_group(id),
name TEXT
);
CREATE TABLE expense(
id INTEGER PRIMARY KEY AUTOINCREMENT,
spent_by INTEGER REFERENCES group_member(id), -- identifiant du membre qui a fait la dépense
date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- date et heure de la dépense
name TEXT, -- intitulé
amount DECIMAL -- montant en euros
);
On peut maintenant lancer l'exécutable sqlpage.bin (ou sqlpage.exe sous Windows 😬) depuis le dossier de notre site.
Il doit se lancer, et afficher dans le terminal le message suivant : Applying migrations from 'sqlpage/migrations [...] Found 1 migrations. Cela signifie qu'il a créé avec succès notre base de données selon le schéma demandé.
En ouvrant la page http://localhost:8080 sur notre navigateur web, nous devrions voir le message suivant:
Troisième étape : création de notre première page web
Le moment tant attendu est arrivé : nous allons créer notre première page web et pouvoir l'ouvrir dans notre navigateur.
Pour cela, créons un fichier nommé index.sql à la racine du dossier de notre site web. À l'intérieur, nous allons écrire une série de requêtes SQL.
SQLPage marche de la manière suivante : on fait une première requête pour invoquer un composant graphique, comme une liste, un formulaire, du texte, ou un graphique. Ensuite, on fait une seconde requête pour définir comment peupler notre composant : les éléments de la liste, les champs du formulaire, les paragraphes de texte, ou les points de notre graphique.
Dans notre cas, notre premier composant sera un formulaire pour créer un nouveau groupe de dépenses à partager entre amis. Pour cela, nous allons invoquer le composant form. Dans index.sql, écrivons :
SELECT
'form' as component,
'Nouveau compte partagé' as title,
'Créer le compte de dépenses partagé !' as validate;
Cela crée un formulaire, vide, que l'on peut déjà voir dans notre navigateur ! Maintenant, ajoutons un champ dans le formulaire. Immédiatement à la suite de la requête précédente, ajoutons:
SELECT 'Nom du compte' AS label, 'shared_expense_name' AS name;
Rouvrons notre navigateur, et nous devrions maintenant voir cela :
Insertion de données dans la base de données
Pour l'instant, lorsque l'on clique sur le bouton Créer le compte de dépenses partagées, il ne se passe rien. Corrigeons cela !
Toujours dans index.sql, à la fin de notre fichier, ajoutons une nouvelle requête SQL :
INSERT INTO expense_group(name)
SELECT :shared_expense_name WHERE :shared_expense_name IS NOT NULL;
Ici, on utilise une requête de type INSERT INTO ... SELECT pour insérer une nouvelle ligne dans la table expense_group. On ajoute une clause WHERE pour qu'une ligne ne soit insérée que lorsque l'utilisateur a rempli une valeur dans le formulaire, et pas à chaque fois que la page se charge.
La variable SQL :shared_expense_name sera associée à la valeur que l'utilisateur aura rentré dans le champ de texte que nous avons appelé shared_expense_name à l'étape précédente.
Maintenant, chaque validation de formulaire crée une nouvelle ligne dans notre base de données. Il est temps de créer notre premier composant dynamique, dont le contenu va dépendre de ce qu'il y a dans notre base de données. Toujours à la suite, dans index.sql:
SELECT 'list' as component;
SELECT
name AS title,
'group.sql?id=' || id AS link
FROM expense_group;
Ici, nous utilisons un nouvel élément issu de la bibliothèque standard de SQLPage: le composant list. Après l'avoir sélectionné, nous le peuplons avec des données qui viennent de la table expense_group de notre base de données. Pour chaque élément de la liste, nous spécifions un lien vers lequel l'utilisateur sera emmené lorsqu'il cliquera dessus. Pour créer ce lien, nous concaténons le nom d'un nouveau fichier SQL que nous allons créer, avec une variable qui contient l'identifiant du groupe à afficher.
Amélioration de l'application, création de nouvelles pages
Nous avons maintenant vu tous les éléments nécessaires à la construction d'une application. Il ne nous reste plus qu'à les appliquer à la création des pages restantes de notre application opensource.
Dans group.sql, réutilisons les composants from et list que nous connaissons maintenant :
SELECT 'title' as component, name as contents
FROM expense_group WHERE id = $id;
INSERT INTO group_member(group_id, name)
SELECT $id, :new_member_name WHERE :new_member_name IS NOT NULL;
SELECT 'list' as component, 'Membres' as title;
SELECT name AS title FROM group_member WHERE group_id=$id;
SELECT 'form' as component, 'Ajouter un membre au groupe' as validate;
SELECT 'Nom du membre' AS 'label', 'new_member_name' AS name;
SELECT 'title' as component, 'Dépenses' as contents;
SELECT 'form' as component, 'Ajouter une dépense' as title, 'Ajouter' as validate;
SELECT 'Description' AS name;
SELECT 'Montant' AS name, 'number' AS type;
SELECT 'select' as type, 'Dépensé par' AS name,
json_group_array(json_object("label", name, "value", id)) as options
FROM group_member WHERE group_id = $id;
INSERT INTO expense(spent_by, name, amount)
SELECT :"Dépensé par", :Description, :Montant WHERE :Montant IS NOT NULL;
SELECT 'card' as component, 'Dépenses' as title;
SELECT
expense.name as title,
'Par ' || group_member.name || ', le ' || expense.date as description,
expense.amount || ' €' as footer,
CASE WHEN expense.amount > 100 THEN 'red' WHEN expense.amount > 50 THEN 'orange' ELSE 'blue' END AS color
FROM expense
INNER JOIN group_member on expense.spent_by = group_member.id
WHERE group_member.group_id = $id;
Nous avons ici créé une seule page, qui contient plusieurs listes et plusieurs formulaires, juste en écrivant nos requêtes SQL les unes après les autres dans notre fichier.
Le seul point particulier à noter, qui est différent de ce que nous avons vu avant, est l'utilisation de la fonction sql json_group_array pour remplir la valeur du champ de formulaire à choix multiple, qui prend un tableau json comme valeur.
Nous arrivons au résultat suivant :
Cerise sur le gâteau : calcul des dettes
Une fonctionnalité pratique de l'application originelle est le calcul du tableau de dette. L'application fait elle-même le calcul final de qui doit combien. C'est un peu moins trivial que les requêtes classiques de listage de données que l'on a vues jusqu'ici, mais on peut aussi implémenter cela entièrement en SQL.
On crée quelques vues qui nous seront utiles pour nos calculs.
Dans SQLPage, on ne crée en général pas de fonctions, et on n'importe pas des bibliothèques. Pour construire une fonctionnalité complexe, le plus simple est de construire des vues successives de nos données, dans lesquelles on les groupe et les filtre comme on le suite. Ici, on construit les trois vues simples suivantes, chacune avec sa fonction SQL :
-
members_with_expenses, qui va lier nos tables entre elles pour associer les noms des membres à leurs montants de dépenses.
-
average_debt_per_person qui va diviser le montant total dépensé par le groupe par le nombre de participants.
-
individual_debts qui va soustraire la dépense moyenne aux dépenses personnelles de chacun, pour savoir combien il doit ou combien on lui doit.
Ici c'est du SQL classique, il n'y a rien qui soit propre à SQLPage. Je vous laisse lire les 27 lignes de code sur github.
Conclusion
Nous avons vu comment construire une application web complète entièrement en SQL grâce à SQLPage. Nous pouvons maintenant la faire tourner sur un tout petit serveur chez nous, dans le cloud, ou même en mode sans-serveur. SQLPage est écrit dans le langage de programmation rust et consomme très peu de resources par rapport à une application web classique, l'application sera donc très peu chère à héberger.
Pour résumer ce que nous avons vu
Nous avons tout d'abord créé une structure de base de données grâce aux migrations.
Ensuite, nous avons affiché des composants graphiques grâce à la bibliothèque de composants intégrés de SQLPage.
Enfin, nous avons inséré des données dynamiquement dans notre base de données grâce au système de variables de SQLPage.
Pour aller plus loin
SQLPage est un logiciel libre et gratuit. Si vous rencontrez des problèmes lors de son utilisation, n'hésitez pas à rapporter un bug ou demander une fonctionnalité sur github, ou à discuter de son utilisation sur les pages de discussion.
Et si vous cherchez une idée pour vous entraîner… Pourquoi pas un TapTempo entièrement en SQL ?
