.. _ref-sql-advanced-queries:

Requêtes avancées (optionnel)
=============================

.. note:: 

   Cette partie du TP est plus avancée. Elle n'est pas obligatoire, mais elle vous permettra de mieux comprendre les avantages de SQL et d'approfondir votre maîtrise du langage. En fonction de votre avancement, il est peut-être préfrable de faire ces exercices après avoir discuté et commencé la phase 3 en groupe.

Maintenant que vous disposez de la base de données complète, vous allez pouvoir écrire des requêtes SQL plus complexes. Ajoutez ces fonctions dans ``mobility/models/airport.py``.

**Important** : l'objectif est de réaliser l'ensemble du traitement directement en SQL, en une seule requête, plutôt que de faire une requête simple puis traiter le résultat en Python. En effet, le moteur SQL est optimisé pour effectuer ces opérations efficacement, même sur de grandes quantités de données. Récupérer toutes les lignes en Python pour ensuite les trier ou les filtrer est beaucoup plus lent et consomme inutilement de la mémoire.

.. warning::

   La base de données contient des aéroports dont les coordonnées (``latitude_deg``, ``longitude_deg``) sont ``NULL``. Si vous ne les filtrez pas, SQLite placera ces lignes en tête du résultat lors d'un ``ORDER BY`` croissant (les ``NULL`` sont considérés comme plus petits que toute valeur), et la version Python lèvera une ``TypeError`` en tentant d'effectuer des opérations arithmétiques sur ``None``. Pensez à filtrer ces entrées avec ``IS NOT NULL`` en SQL, ou avec un ``if ... is None: continue`` en Python.

Pour vous en convaincre, vous pouvez utiliser le décorateur suivant pour mesurer le temps d'exécution de vos fonctions :

.. code-block:: python

   import time
   import functools

   def measure_time(func):
       @functools.wraps(func)
       def wrapper(*args, **kwargs):
           start = time.time()
           result = func(*args, **kwargs)
           end = time.time()
           print(f"\n{'='*50}\n⏱  {func.__name__} : {end - start:.4f} secondes\n{'='*50}\n", flush=True)
           return result
       return wrapper

Il suffit ensuite d'ajouter ``@measure_time`` au-dessus de la fonction que vous souhaitez mesurer :

.. code-block:: python

   @measure_time
   def get_airports_by_country():
       ...

Comparez le temps d'exécution entre une version qui fait le travail en SQL et une version qui récupère toutes les données puis les traite en Python.

.. container:: literal-block-wrapper docutils
   :name: idairportadvanced

   .. container:: code-block-caption

      ``mobility/models/airport.py``

   .. code-block:: python

      def get_airports_by_country():
        # TODO
        # Retourne la liste des aéroports avec le nom du pays correspondant
        # Les résultats doivent être triés par ordre alphabétique du nom du pays,
        # puis par latitude décroissante au sein de chaque pays

      def get_closest_airport(iata_code: str):
        # TODO
        # Retourne l'aéroport le plus proche de l'aéroport dont le code IATA est "iata_code"
        # Utilisez la distance euclidienne simplifiée :
        # d = (lat1 - lat2)² + (lon1 - lon2)²
        # Attention : certains aéroports n'ont pas de coordonnées dans la DB,
        # filtrez-les avec IS NOT NULL

      def get_busiest_airports():
        # TODO
        # Retourne les 10 aéroports avec le plus grand nombre de vols au départ,
        # avec le nom du pays correspondant.
        # Utilisez la table "flight" (colonne iata_departure) et un COUNT + GROUP BY.

   .. container:: code-block-caption

      ``mobility/models/airport.py``

   .. raw:: html

       <details>
       <summary><a>Solution (Essayez d'abord par vous-même)</a></summary>

   **Version SQL (recommandée)**

   .. code-block:: python

      def get_airports_by_country():
         db = get_db()
         return db.execute(
            'SELECT airport.iata_code, airport.name, airport.latitude_deg, '
            'airport.longitude_deg, country.name AS country_name '
            'FROM airport '
            'JOIN country ON airport.iso_country = country.iso_country '
            'ORDER BY country.name, airport.latitude_deg DESC'
         ).fetchall()

      def get_closest_airport(iata_code: str):
         db = get_db()
         return db.execute(
            'SELECT a2.iata_code, a2.name, a2.latitude_deg, a2.longitude_deg, a2.iso_country, '
            '(a1.latitude_deg - a2.latitude_deg) * (a1.latitude_deg - a2.latitude_deg) + '
            '(a1.longitude_deg - a2.longitude_deg) * (a1.longitude_deg - a2.longitude_deg) AS distance '
            'FROM airport a1, airport a2 '
            'WHERE a1.iata_code = ? AND a2.iata_code != a1.iata_code '
            'AND a2.latitude_deg IS NOT NULL AND a2.longitude_deg IS NOT NULL '
            'ORDER BY distance '
            'LIMIT 1',
            (iata_code,)
         ).fetchone()

      def get_busiest_airports():
         db = get_db()
         return db.execute(
            'SELECT airport.iata_code, airport.name, country.name AS country_name, '
            'COUNT(flight.flight_id) AS nb_flights '
            'FROM airport '
            'JOIN country ON airport.iso_country = country.iso_country '
            'JOIN flight ON airport.iata_code = flight.iata_departure '
            'GROUP BY airport.iata_code '
            'ORDER BY nb_flights DESC '
            'LIMIT 10'
         ).fetchall()

   **Version Python naïve (pour comparaison de performance — uniquement pour** ``get_closest_airport`` **et** ``get_busiest_airports`` **)**

   .. code-block:: python

      def get_closest_airport_python(iata_code: str):
         db = get_db()
         airports = db.execute('SELECT * FROM airport').fetchall()

         origin = None
         for airport in airports:
            if airport["iata_code"] == iata_code:
               origin = airport
               break

         if origin is None:
            return None

         closest = None
         min_distance = float("inf")
         for airport in airports:
            if airport["iata_code"] == iata_code:
               continue
            if airport["latitude_deg"] is None or airport["longitude_deg"] is None:
               continue
            distance = ((origin["latitude_deg"] - airport["latitude_deg"]) ** 2 +
                        (origin["longitude_deg"] - airport["longitude_deg"]) ** 2)
            if distance < min_distance:
               min_distance = distance
               closest = airport

         return closest

      def get_busiest_airports_python():
         db = get_db()
         flights = db.execute('SELECT * FROM flight').fetchall()
         airports = db.execute('SELECT * FROM airport').fetchall()
         countries = db.execute('SELECT * FROM country').fetchall()

         country_names = {c['iso_country']: c['name'] for c in countries}
         airport_info = {a['iata_code']: a for a in airports}

         counts = {}
         for f in flights:
            iata = f['iata_departure']
            counts[iata] = counts.get(iata, 0) + 1

         result = []
         for iata, nb in counts.items():
            if iata in airport_info:
               a = airport_info[iata]
               result.append({
                  'iata_code': iata,
                  'name': a['name'],
                  'country_name': country_names.get(a['iso_country'], '?'),
                  'nb_flights': nb
               })

         result.sort(key=lambda x: x['nb_flights'], reverse=True)
         return result[:10]

   .. raw:: html

        </details>

Ajoutez ensuite les routes correspondantes dans ``mobility/airport.py`` et les templates nécessaires pour afficher les résultats de ces requêtes.

.. container:: literal-block-wrapper docutils
   :name: idairportadvancedcontroller

   .. container:: code-block-caption

      ``mobility/airport.py``

   .. code-block:: python

      @bp.route('/airports_by_country')
      def airports_by_country():
         # TODO
         # Affiche la liste des aéroports triés par pays puis par latitude

      @bp.route('/closest_airport/<iata_code>')
      def closest_airport(iata_code):
         # TODO
         # Affiche l'aéroport le plus proche de l'aéroport dont le code IATA est "iata_code"

      @bp.route('/busiest_airports')
      def busiest_airports():
         # TODO
         # Affiche le top 10 des aéroports les plus fréquentés

.. note::

   N'oubliez pas de mettre à jour la ligne d'import en tête de ``mobility/airport.py`` pour y inclure les nouvelles fonctions :

   .. code-block:: python

      from mobility.models.airport import get_airport_list, search_airport_by_iata_code, get_airports_by_country, get_closest_airport

   .. container:: code-block-caption

      ``mobility/airport.py``

   .. raw:: html

       <details>
       <summary><a>Solution (Essayez d'abord par vous-même)</a></summary>

   .. code-block:: python

      from mobility.models.airport import get_airports_by_country, get_closest_airport

      @bp.route('/airports_by_country')
      def airports_by_country():
         airports = get_airports_by_country()
         return render_template("airports_by_country.html", airports=airports)

      @bp.route('/closest_airport/<iata_code>')
      def closest_airport(iata_code):
         airport = get_closest_airport(iata_code)
         return render_template("closest.html", iata_code=iata_code, airport=airport)

   .. raw:: html

        </details>

Créez ensuite les templates nécessaires pour afficher les résultats.

.. container:: literal-block-wrapper docutils
   :name: idairportsbycountrytemplate

   .. container:: code-block-caption

      ``mobility/templates/airports_by_country.html``

   .. raw:: html

       <details>
       <summary><a>Solution (Essayez d'abord par vous-même)</a></summary>

   .. code-block:: html+jinja

      {% extends "base.html" %}
      {% block content %}

      <h2>Airports by country</h2>
      <table>
          <tr>
              <th>IATA code</th>
              <th>Name</th>
              <th>Latitude</th>
              <th>Longitude</th>
              <th>Country</th>
          </tr>
          {% for airport in airports %}
          <tr>
              <td>{{ airport['iata_code'] }}</td>
              <td>{{ airport['name'] }}</td>
              <td>{{ airport['latitude_deg'] }}</td>
              <td>{{ airport['longitude_deg'] }}</td>
              <td>{{ airport['country_name'] }}</td>
          </tr>
          {% endfor %}
      </table>

      {% endblock %}

   .. raw:: html

        </details>

.. container:: literal-block-wrapper docutils
   :name: idclosesttemplate

   .. container:: code-block-caption

      ``mobility/templates/closest.html``

   .. raw:: html

       <details>
       <summary><a>Solution (Essayez d'abord par vous-même)</a></summary>

   .. code-block:: html+jinja

      {% extends "base.html" %}
      {% block content %}

      <h2>Closest airport to {{ iata_code }}</h2>
      {% if airport %}
      <table>
          <tr>
              <th>IATA code</th>
              <th>Name</th>
              <th>Latitude</th>
              <th>Longitude</th>
              <th>Country</th>
          </tr>
          <tr>
              <td>{{ airport['iata_code'] }}</td>
              <td>{{ airport['name'] }}</td>
              <td>{{ airport['latitude_deg'] }}</td>
              <td>{{ airport['longitude_deg'] }}</td>
              <td>{{ airport['iso_country'] }}</td>
          </tr>
      </table>
      {% else %}
      <p>No airport found.</p>
      {% endif %}

      {% endblock %}

   .. raw:: html

        </details>

Créez le template ``mobility/templates/busiest_airports.html`` pour afficher le classement :

.. container:: literal-block-wrapper docutils
   :name: idbusiesttemplate

   .. container:: code-block-caption

      ``mobility/templates/busiest_airports.html``

   .. raw:: html

       <details>
       <summary><a>Solution (Essayez d'abord par vous-même)</a></summary>

   .. code-block:: html+jinja

      {% extends "base.html" %}
      {% block content %}

      <h2>Top 10 des aéroports les plus fréquentés</h2>
      <table>
          <tr>
              <th>Rang</th>
              <th>IATA code</th>
              <th>Name</th>
              <th>Country</th>
              <th>Departures</th>
          </tr>
          {% for airport in airports %}
          <tr>
              <td>{{ loop.index }}</td>
              <td>{{ airport['iata_code'] }}</td>
              <td>{{ airport['name'] }}</td>
              <td>{{ airport['country_name'] }}</td>
              <td>{{ airport['nb_flights'] }}</td>
          </tr>
          {% endfor %}
      </table>

      {% endblock %}

   .. raw:: html

        </details>

Comparer les performances SQL et Python
---------------------------------------

Ajoutez les routes dédiées pour les versions Python naïve et pour l'aéroport le plus fréquenté dans ``mobility/airport.py``. Commencez par mettre à jour la ligne d'import pour inclure toutes les nouvelles fonctions :

.. code-block:: python

   from mobility.models.airport import (get_airport_list, search_airport_by_iata_code,
                                        get_airports_by_country, get_closest_airport,
                                        get_closest_airport_python,
                                        get_busiest_airports, get_busiest_airports_python)

Ajoutez ensuite les trois nouvelles routes :

.. code-block:: python

   @bp.route('/closest_airport_python/<iata_code>')
   def closest_airport_python_view(iata_code):
       airport = get_closest_airport_python(iata_code)
       return render_template("closest.html", iata_code=iata_code, airport=airport)

   @bp.route('/busiest_airports')
   def busiest_airports():
       airports = get_busiest_airports()
       return render_template("busiest_airports.html", airports=airports)

   @bp.route('/busiest_airports_python')
   def busiest_airports_python_view():
       airports = get_busiest_airports_python()
       return render_template("busiest_airports.html", airports=airports)

Décorez les fonctions avec ``@measure_time`` dans ``mobility/models/airport.py``, puis lancez le serveur :

.. code-block:: bash

   flask --app=mobility --debug run

Ouvrez votre navigateur et comparez les deux versions côte à côte :

**Aéroport le plus proche**

- Version SQL : `http://127.0.0.1:5000/closest_airport/CRL <http://127.0.0.1:5000/closest_airport/CRL>`__
- Version Python : `http://127.0.0.1:5000/closest_airport_python/CRL <http://127.0.0.1:5000/closest_airport_python/CRL>`__

**Top 10 des aéroports les plus fréquentés**

- Version SQL : `http://127.0.0.1:5000/busiest_airports <http://127.0.0.1:5000/busiest_airports>`__
- Version Python : `http://127.0.0.1:5000/busiest_airports_python <http://127.0.0.1:5000/busiest_airports_python>`__

Les deux URLs affichent le même résultat, mais le terminal affichera les temps d'exécution mesurés par ``@measure_time``. La différence est particulièrement visible sur ``busiest_airports``, qui traite 121 000 vols : SQL laisse le moteur agréger les données en C, tandis que Python charge toutes les lignes en mémoire et les parcourt une par une en Python pur.

.. note::

   Les chiffres exacts dépendent de votre machine. Sur un Intel Core i7-1255U (12ᵉ génération) :

   .. list-table::
      :header-rows: 1
      :widths: 40 30 30

      * - Fonction
        - Version SQL
        - Version Python
      * - ``get_closest_airport``
        - ~0.002 s
        - ~0.018 s
      * - ``get_busiest_airports``
        - ~0.065 s
        - ~0.24 s

   La vitesse du CPU influencera les deux temps. Mais c'est surtout la taille de la base de données qui creuse l'écart : SQLite traite les données en interne et ne retourne à Python que le résultat final, tandis que la version naïve transfère l'intégralité des tables en mémoire Python avant de les traiter. Pour de très grands volumes, cette approche peut simplement devenir infaisable.

.. only:: html

   Continuez en lisant :ref:`ref-sql-keep-developing`.