24. API REST : MIRAJ sur HTTP et HTTPS

  Application (web, mobile, script)
         │   HTTP ou HTTPS : GET https://hôte:7009/clients/42
         │   Authorization: Bearer mja_…   (ou Basic compte:mot de passe)
         ▼
  ┌─────────────────────────────── miraj-server ───────────────────────────────┐
  │  port 7007 : protocole réseau de MIRAJ                                     │
  │  port 7009 : point d'accès REST (--rest ON)                                │
  │     famille ouverte sur le serveur ? ─► client authentifié ?               │
  │     famille permise au jeton ? ─► droits SQL du compte (table, colonne,    │
  │     EXECUTE sur l'endpoint) ─► instruction SQL ─► verrous, déclencheurs,   │
  │     clés étrangères, journal, transactions, réplication                    │
  └────────────────────────────────────────────────────────────────────────────┘

<miraj>
    <rest>ON</rest>
    <rest_bind>0.0.0.0</rest_bind>
    <tls_cert>C:\miraj\cert.pem</tls_cert>
    <tls_key>C:\miraj\key.pem</tls_key>
    <rest_cors_origins>https://app.exemple.com</rest_cors_origins>
</miraj>

miraj-server.exe --root D:\miraj\data --log --rest ON
miraj-server.exe --root D:\miraj\data --log --rest ON --rest-tables ON --rest-sql ON --rest-port 8443 --rest-bind 0.0.0.0 --tls-cert cert.pem --tls-key key.pem

REST GET /clients/42 -> 200, jeton mobile, compte app@%, 0 ms, depuis 127.0.0.1
REST GET /noms -> 200, compte lecteur@%, 0 ms, depuis 127.0.0.1
[509] REST ! 1143 (42000): SELECT command denied to user 'lecteur'@'localhost' for column 'ville' in table 'clients' -- ENDPOINT gestion.villes
REST GET /villes -> 403, compte lecteur@%, 0 ms, depuis 127.0.0.1

CREATE API TOKEN nom [FOR compte]
    [ACCESS { ALL | famille [, famille …] }]     -- défaut : ACCESS ENDPOINTS
    [DATABASES (base, …)]
    [EXPIRE { NEVER | INTERVAL n DAY }];
--  famille := ENDPOINTS [([base.]nom, …)] | TABLES | SQL

ALTER API TOKEN nom ACCESS …;        -- familles remplacées, secret inchangé, dès la requête suivante
DROP API TOKEN [IF EXISTS] nom;
SHOW API TOKENS;

-- Application mobile : seulement les API prévues pour elle
CREATE API TOKEN mobile FOR 'app'@'%' ACCESS ENDPOINTS;
-- Partenaire : deux endpoints précis, 90 jours
CREATE API TOKEN partenaire FOR 'app'@'%' ACCESS ENDPOINTS (gestion.fiche_client, gestion.liste_clients) EXPIRE INTERVAL 90 DAY;
-- Outil interne : tables et SQL libre sur une base
CREATE API TOKEN outil FOR 'dev'@'%' ACCESS TABLES, SQL DATABASES (gestion);

+--------+------------------------------+-----------+-----------+---------+
| name   | token                        | account   | access    | expires |
+--------+------------------------------+-----------+-----------+---------+
| mobile | mja_3e3ZwjRCqjj47aCnxpFmUWPy | 'app'@'%' | ENDPOINTS | NULL    |
+--------+------------------------------+-----------+-----------+---------+

SHOW API TOKENS;

+------------+------+------+-----------------------------------------------------------------+-----------+---------------------+---------------------+
| Name       | User | Host | Access                                                          | Databases | Created             | Expires             |
+------------+------+------+-----------------------------------------------------------------+-----------+---------------------+---------------------+
| mobile     | app  | %    | ENDPOINTS                                                       | NULL      | 2026-10-19 02:28:09 | NULL                |
| outil      | dev  | %    | TABLES, SQL                                                     | gestion   | 2026-10-19 02:29:20 | NULL                |
| partenaire | app  | %    | ENDPOINTS (`gestion`.`fiche_client`, `gestion`.`liste_clients`) | NULL      | 2026-10-19 02:29:23 | 2027-01-17 02:29:23 |
+------------+------+------+-----------------------------------------------------------------+-----------+---------------------+---------------------+

{"error":{"code":9047,"sqlstate":"HY000","message":"REST access to SQL is not allowed for API token 'mobile'"}}
{"error":{"code":9047,"sqlstate":"HY000","message":"REST access to tables is not allowed for API token 'mobile'"}}
{"error":{"code":9047,"sqlstate":"HY000","message":"REST access to endpoints is not allowed for API token 'outil'"}}
{"error":{"code":9047,"sqlstate":"HY000","message":"REST access to endpoint gestion.nouveau_client is not allowed for API token 'partenaire'"}}
{"error":{"code":9047,"sqlstate":"HY000","message":"REST access to SQL is not allowed for password authentication"}}

CREATE [OR REPLACE] [DEFINER = compte] ENDPOINT [IF NOT EXISTS] [base.]nom
    { GET | POST | PUT | PATCH | DELETE } '/chemin/{param}/…'
    [PARAMS (nom type [DEFAULT valeur], …)]
    [RETURNS { ROWS | ONE | NONE | AFFECTED }]
    [COMMENT 'texte'] [SQL SECURITY { DEFINER | INVOKER }]
    AS instruction | BEGIN … END;

DROP ENDPOINT [IF EXISTS] [base.]nom;
SHOW ENDPOINTS [FROM base] [LIKE 'motif'];
SHOW CREATE ENDPOINT [base.]nom;

USE gestion;

-- Fiche d'un client : une ligne, 404 s'il n'existe pas
CREATE ENDPOINT fiche_client GET '/clients/{id}' PARAMS (id INT) RETURNS ONE
AS SELECT id, nom, ville FROM clients WHERE id = :id;

-- Liste filtrée, paramètres facultatifs
CREATE ENDPOINT liste_clients GET '/clients' PARAMS (ville VARCHAR(40) DEFAULT NULL, limite INT DEFAULT 50)
AS SELECT id, nom, ville FROM clients WHERE :ville IS NULL OR ville = :ville ORDER BY nom LIMIT :limite;

-- Création : 201 et lignes insérées
CREATE ENDPOINT nouveau_client POST '/clients' PARAMS (nom VARCHAR(80), ville VARCHAR(40)) RETURNS AFFECTED
AS INSERT INTO clients (nom, ville) VALUES (:nom, :ville);

-- Traitement en plusieurs instructions (avec miraj-cli, entre DELIMITER // et DELIMITER ;)
CREATE ENDPOINT solder_facture POST '/factures/{id}/solder' PARAMS (id INT) RETURNS ONE
AS BEGIN
    DECLARE reste DECIMAL(12,2);
    SELECT montant - paye INTO reste FROM factures WHERE id = :id FOR UPDATE;
    UPDATE factures SET paye = montant WHERE id = :id;
    INSERT INTO reglements (facture, montant) VALUES (:id, reste);
    SELECT :id AS facture, reste AS regle;
END;

-- L'application n'a que le droit d'appeler ces endpoints
CREATE USER 'app'@'%' IDENTIFIED BY '…';
GRANT EXECUTE ON ENDPOINT gestion.fiche_client TO 'app'@'%';
GRANT EXECUTE ON ENDPOINT gestion.liste_clients TO 'app'@'%';
GRANT EXECUTE ON ENDPOINT gestion.nouveau_client TO 'app'@'%';
GRANT EXECUTE ON ENDPOINT gestion.solder_facture TO 'app'@'%';
CREATE API TOKEN mobile FOR 'app'@'%';

curl http://127.0.0.1:7009/clients/42 -H "Authorization: Bearer mja_3e3ZwjRCqjj47aCnxpFmUWPy"
→ 200 {"id":42,"nom":"Ali","ville":"Oran"}

curl "http://127.0.0.1:7009/clients?ville=Oran&limite=10" -H "Authorization: Bearer mja_…"
→ 200 [{"id":42,"nom":"Ali","ville":"Oran"},{"id":12,"nom":"Karim","ville":"Oran"}]

curl -X POST http://127.0.0.1:7009/clients -H "Authorization: Bearer mja_…" \
     -H "Content-Type: application/json" -d '{"nom":"Lina","ville":"Alger"}'
→ 201 {"affected_rows":1,"last_insert_id":43}

curl -X POST http://127.0.0.1:7009/factures/7/solder -H "Authorization: Bearer mja_…"
→ 200 {"facture":7,"regle":120.50}

curl http://127.0.0.1:7009/clients/999 -H "Authorization: Bearer mja_…"
→ 404 {"error":{"code":null,"sqlstate":null,"message":"Not Found: no row"}}

→ 403 {"error":{"code":1370,"sqlstate":"42000","message":"execute command denied to user 'app'@'localhost' for routine 'gestion.solder_facture'"}}

ERREUR 9045 (HY000) : Invalid endpoint path 'clients': the path must start with '/'
ERREUR 9045 (HY000) : Invalid endpoint path '/api/x': paths under /api and /mcp are reserved
ERREUR 9045 (HY000) : Invalid endpoint path '/a b': invalid character in segment 'a b'
ERREUR 9045 (HY000) : Invalid endpoint path '/x/{id}/{id}': parameter 'id' appears twice

ERREUR 9044 (HY000) : Endpoint path GET /clients/{code} is already used by endpoint gestion.fiche_client

CREATE ENDPOINT echo_types PUT '/echo' PARAMS (b VARBINARY(8), n DECIMAL(10,2), d DATE, ok BOOLEAN, g GEOMETRY)
RETURNS ONE AS SELECT HEX(:b) AS hex_b, :n AS n, :d AS d, :ok AS ok, ST_AsText(:g) AS g, :g AS geo;

curl -X PUT http://127.0.0.1:7009/echo -u root:… -H "Content-Type: application/json" \
     -d '{"b":{"hex":"00ff"},"n":12.5,"d":"2026-09-25","ok":true,"g":{"wkt":"POINT(1 2)"}}'
→ 200 {"hex_b":"00FF","n":12.50,"d":"2026-09-25","ok":1,"g":"POINT(1 2)","geo":{"wkt": "POINT(1 2)", "srid": 0}}

CREATE ENDPOINT villes GET '/villes' SQL SECURITY INVOKER AS SELECT DISTINCT ville FROM clients;

→ 403 {"error":{"code":1143,"sqlstate":"42000","message":"SELECT command denied to user 'lecteur'@'localhost' for column 'ville' in table 'clients'"}}

SHOW GRANTS FOR 'app'@'%';

+-------------------------------------------------------------------+
| Grants for app@%                                                  |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app`@`%`                                   |
| GRANT EXECUTE ON ENDPOINT `gestion`.`fiche_client` TO `app`@`%`   |
| GRANT EXECUTE ON ENDPOINT `gestion`.`liste_clients` TO `app`@`%`  |
| GRANT EXECUTE ON ENDPOINT `gestion`.`nouveau_client` TO `app`@`%` |
| GRANT EXECUTE ON ENDPOINT `gestion`.`solder_facture` TO `app`@`%` |
+-------------------------------------------------------------------+

SELECT * FROM information_schema.ENDPOINT_PRIVILEGES;

+-----------+------------------+-----------------+----------------+----------------+--------------+
| GRANTEE   | ENDPOINT_CATALOG | ENDPOINT_SCHEMA | ENDPOINT_NAME  | PRIVILEGE_TYPE | IS_GRANTABLE |
+-----------+------------------+-----------------+----------------+----------------+--------------+
| 'app'@'%' | def              | gestion         | fiche_client   | EXECUTE        | NO           |
| 'app'@'%' | def              | gestion         | liste_clients  | EXECUTE        | NO           |
| 'app'@'%' | def              | gestion         | nouveau_client | EXECUTE        | NO           |
| 'app'@'%' | def              | gestion         | solder_facture | EXECUTE        | NO           |
+-----------+------------------+-----------------+----------------+----------------+--------------+

SHOW ENDPOINTS FROM gestion;

+---------+----------------+--------+-----------------------+----------+---------------------------------------------------------+----------------+---------------+---------------------+---------------------+---------+
| Db      | Name           | Method | Path                  | Returns  | Parameters                                              | Definer        | Security_type | Modified            | Created             | Comment |
+---------+----------------+--------+-----------------------+----------+---------------------------------------------------------+----------------+---------------+---------------------+---------------------+---------+
| gestion | fiche_client   | GET    | /clients/{id}         | ONE      | id int(11)                                              | root@localhost | DEFINER       | 2026-10-19 03:28:03 | 2026-10-19 03:28:03 |         |
| gestion | liste_clients  | GET    | /clients              | ROWS     | ville varchar(40) (optional), limite int(11) (optional) | root@localhost | DEFINER       | 2026-10-19 03:28:03 | 2026-10-19 03:28:03 |         |
| gestion | nouveau_client | POST   | /clients              | AFFECTED | nom varchar(80), ville varchar(40)                      | root@localhost | DEFINER       | 2026-10-19 03:28:03 | 2026-10-19 03:28:03 |         |
| gestion | solder_facture | POST   | /factures/{id}/solder | ONE      | id int(11)                                              | root@localhost | DEFINER       | 2026-10-19 03:28:09 | 2026-10-19 03:28:09 |         |
+---------+----------------+--------+-----------------------+----------+---------------------------------------------------------+----------------+---------------+---------------------+---------------------+---------+

CREATE DEFINER=`root`@`localhost` ENDPOINT `liste_clients` GET '/clients' PARAMS (ville VARCHAR(40) DEFAULT NULL, limite INT DEFAULT 50) RETURNS ROWS
AS SELECT id, nom, ville FROM clients WHERE :ville IS NULL OR ville = :ville ORDER BY nom LIMIT :limite

GET /api/gestion/clients?ville=Oran&nom.like=A%25&order=nom&limit=20
→ 200 [{"id":42,"nom":"Ali","ville":"Oran"}]

GET /api/gestion/clients?select=id,nom&order=id.desc&limit=2&offset=1
→ 200 [{"id":42,"nom":"Ali"},{"id":12,"nom":"Karim"}]

GET /api/gestion/clients?id.in=7,12&ville.null=false
→ 200 [{"id":7,"nom":"Samia","ville":"Alger"},{"id":12,"nom":"Karim","ville":"Oran"}]

POST /api/gestion/clients   {"nom":"Nadia","ville":"Blida"}
→ 201 {"affected_rows":1,"last_insert_id":44}

POST /api/gestion/clients   [{"nom":"Omar","ville":"Oran"},{"nom":"Rym","ville":null}]
→ 201 {"affected_rows":2,"last_insert_id":45}

PATCH /api/gestion/clients/44   {"ville":"Tlemcen"}
→ 200 {"affected_rows":1,"last_insert_id":0}

DELETE /api/gestion/clients/46
→ 204

GET /api/gestion/clients?limit=2
→ 200 [{"id":42,"nom":"Ali"},{"id":7,"nom":"Samia"}]

GET /api/gestion/clients?ville=Oran
→ 403 {"error":{"code":1143,"sqlstate":"42000","message":"SELECT command denied to user 'lecteur'@'localhost' for column 'ville' in table 'clients'"}}

{ "sql": "SELECT id, nom FROM gestion.clients WHERE ville = :ville", "params": { "ville": "Oran" }, "max_rows": 100 }

{"results":[{"columns":[{"name":"id","type":"int(11)"},{"name":"nom","type":"varchar(80)"}],"rows":[[42,"Ali"],[12,"Karim"]],"row_count":2,"truncated":false,"affected_rows":null,"last_insert_id":null}],"warnings":[]}

{"sql": "SELECT id, nom FROM gestion.clients WHERE id > ? ORDER BY id; UPDATE gestion.clients SET ville = ville WHERE id = 7", "params": [10]}

{"results":[{"columns":[{"name":"id","type":"int(11)"},{"name":"nom","type":"varchar(80)"}],"rows":[[12,"Karim"],[42,"Ali"],[43,"Lina"]],"row_count":3,"truncated":false,"affected_rows":null,"last_insert_id":null},{"columns":[],"rows":[],"row_count":0,"truncated":false,"affected_rows":1,"last_insert_id":0}],"warnings":[]}

{"error":{"code":1142,"sqlstate":"42000","message":"SELECT command denied to user 'dev'@'localhost' for table `ventes`.`article`"},"results":[{"columns":[{"name":"un","type":"bigint"}],"rows":[[1]],"row_count":1,"truncated":false,"affected_rows":null,"last_insert_id":null}],"warnings":[]}