name: inter-slide class: left, middle, inverse {{ content }} --- name: layout-general layout: true class: left, middle <style> .remark-slide-number { position: inherit; } .remark-slide-number .progress-bar-container { position: absolute; bottom: 0; height: 4px; display: block; left: 0; right: 0; } .remark-slide-number .progress-bar { height: 100%; background-color: red; } </style>
--- class: middle, left, inverse # Bases de Données : SQL (V), Fonctions (SQL) et Vues ### 2022-10-21 #### [Licence MIASHS et Mathématiques]() #### [Bases de Données](http://stephane-v-boucheron.fr/courses/bdd/) #### [Équipe BD](http://stephane-v-boucheron.fr) --- template: inter-slide ###
- [Requêtes paramétrées : pourquoi ?](#req-param-pourquoi) - [Fonctions SQL (version PostGreSQL): définition](#fonctions-sql-version-postgresql-def) - [Fonctions SQL: usages](#fonctions-sql-usages) - [Vues : pourquoi ?](#vues--pourquoi-) - [Vues : Definition](#vues--definition) - [Vues : usages](#vues--usages) - [Résumé](#résumé) - [Perspectives](#perspectives) --- template: inter-slide name: req-param-pourquoi ## Requêtes paramétrées : pourquoi ? ??? > SQL is much like chess—a few hours to learn, a lifetime to master --- ### Pourquoi ? Lorsqu'une collection de requêtes de même forme est utilisée sur une base de données, il est pertinent de préparer ces requêtes Les objectifs de cette préparation sont divers - éviter de coder de façon répétitive - permettre une planification en amont des requêtes - éviter de recalculer des jointures coûteuses - ... --- ### Comment ? Les SGBD relationnels offrent une gamme d'outils pour combler ces besoins - Les `PREPARED STATEMENT` - Les **fonctions** - Les **vues** --- template: inter-slide name: fonctions-sql-version-postgresql-def ## Fonctions SQL (version PostGreSQL): définition ??? --- > Dans SQL et dans PostgreSQL en particulier , il est possible de définir des fonctions ou procédures persistantes (`STORED PROCEDURE`) > Ces fonctions sont des *objets permanents* d'un schéma (comme les tables, vues, utilisateurs, etc) > Elle permettent de regrouper un traitement complexe en un seul programme que l'on peut appeler à tout moment si on en a le droit --- ### Syntaxe générale des fonctions ```psql CREATE OR REPLACE FUNCTION func_name(arg1 arg1_datatype DEFAULT arg1_default) RETURNS some type | set of some type | TABLE (..) AS $$ BODY of function $$ LANGUAGE language_of_function ``` .fl.w-50.pa2[ `langage_of_function` peut prendre différentes valeurs : - `SQL` (*Trusted*) - `PL/pgSQL` (*Trusted*) - `C` - `PL/Python` (*Untrusted*) - ... ] .fl.w-50.pa2[ ```sql SELECT lanname FROM pg_language; ``` ``` lanname | --------+ internal| c | sql | plpgsql | ``` ] ??? --- ### Signature de la fonction ```sql CREATE OR REPLACE FUNCTION <func_name>(<arg1> <arg1_datatype> [DEFAULT <arg1_default>]) RETURNS <some type> | SETOF <some type> | TABLE (..) ``` La signature d'une fonction est formée par - le nom de la fonction - la liste des arguments (nom et type attendu) - le type du résultat retourné par la fonction --- ### Signature de la fonction (suite) - `<arg1_datatype>`: - type prédéfini ou non, voir `CREATE TYPE ...` - type de colonne d'une table : `country.countrycode%TYPE` - type des tuples d'une table : `country%ROWTYPE`... - `<arg1_default>`: litéral ou expression - Type du résultat: - type prédéfini ou non - `SETOF ...` (table) - Table --- exclude: true > Arguments can have default values, which allow the caller of the function to omit them. > Optional arguments must be positioned after nonoptional arguments in the function definition. --- ### Types définis par l'usager ```sql CREATE TYPE type_utilisateur AS (<identifiant> <type défini>) [, <identifiant> <type défini>]* ); ``` Usage pour une création de table ```sql CREATE TABLE <nom de table> OF type_utilisateur (CONSTRAINT <nom de contrainte> PRIMARY KEY (<nom de colonne>)); ``` Pratique pour créer des tables de même schéma -- ###
quand on crée une table, PostgreSQL crée automatiquement un type associé --- ### Exemple - Définition d'un type ```sql CREATE TYPE basic_user AS (user_name varchar(50), pwd varchar(10)); ``` - Usage du type défini par usager ```sql CREATE TABLE super_users OF basic_user (CONSTRAINT pk_su PRIMARY KEY (user_name)); ``` ??? --- ### Annotations Les définitions de fonctions comportent parfois des qualifications supplémentaires - `VOLATILITY`: `IMMUTABLE`, `STABLE`, `VOLATILE` (default) - `SECURITY` : `SECURITY DEFINER`, `SECURITY INVOKER` --- ### Les fonctions écrites en langage SQL SQL est d'abord un langage permettant d'émettre des requêtes, il peut aussi être utilisé pour écrire des fonctions Dans PostgreSQL, l'utilisation d'un morceau de SQL existant est facile : - prenez vos instructions SQL existantes (éventuellement plusieurs) - ajoutez un en-tête et une conclusion fonctionnels et ... vous avez terminé
--- ### Les fonctions écrites en langage SQL (suite) Mais cette facilité a un prix
Vous ne pouvez pas : - utiliser des structures de contrôle (boucles, alternatives) ou des définitions de variables locales - exécuter des instructions SQL *dynamiques* que vous assemblez à la volée en utilisant les arguments passés dans la fonction Mais, tout de même - le planificateur de requêtes peut examiner une fonction SQL et en optimiser l'exécution ??? > SQL functions execute an arbitrary list of SQL statements, returning the result of the last query in the list. > In the simple (non-set) case, the first row of the last query's result will be returned. > Bear in mind that "the first row" of a multirow result is not well-defined unless you use ORDER BY. > If the last query happens to return no rows at all, the null value will be returned. --- ### Corps de la fonction
La syntaxe du corps de la fonction dépend du `language_of_function` Dans le cas où `language_of_function` est `SQL` ```sql $$ BODY of function $$ ``` ??? --- ### Références aux argument On peut faire références aux arguments en les nommant dans le corps de la fonction Si les arguments ne sont pas nommés, vous faites référence aux arguments par leur position dans la signature : `$1`, `$2`, `$3`, etc
Si vous nommez les arguments, vous pouvez utiliser la notation ```sql big_elephant(name => 'Wooly', ear_size => 1.2) ``` lors des appels à la fonction --- ### Exemple ```sql CREATE OR REPLACE FUNCTION write_to_log(param_user_name varchar, param_description text) RETURNS integer AS $$ INSERT INTO logs (user_name, description) VALUES($1, $2) RETURNING log_id; $$ LANGUAGE 'sql' VOLATILE; ``` -- Invocation ```sql SELECT write_to_log('alex', 'Logged in at 11:59 AM.') AS new_id; ``` --- Les fonctions SQL peuvent retourner des ensembles Trois manières de faire: - ANSI SQL standard: `RETURNS TABLE` - spécifier des paramètres `OUT` et retourner `SETOF RECORD` - types de données composés `SETOF ...` --- ### Exemple de fonction retournant un type simple Dans le schéma `world`, on veut écrire une fonction qui prend en argument une région et renvoie la population maximale parmi les capitales de la région ```sql CREATE OR REPLACE FUNCTION username.taille_max_capitale_region(p_region text) RETURNS INTEGER LANGUAGE SQL AS $$ SELECT MAX(population_city) AS max_pop FROM world.country c JOIN world.city cc ON (c.capital=cc.id) WHERE c.region=p_region AND cc.population_city IS NOT NULL; $$ ; ``` --- ## Fonctions qui retournent un type composé Dans le schéma `world`, on veut écrire une fonction qui prend en argument une région et renvoie le nom et la population de la capitale la plus peuplée de cette région On peut utiliser le qualifiant `OUT` pour désigner des paramètres de sortie. ```sql CREATE OR REPLACE FUNCTION username.capitale(p_region text, OUT o_name_capital TEXT, OUT o_population_capital INTEGER) RETURNS RECORD LANGUAGE SQL AS $$ WITH r AS( SELECT cc.*, RANK() OVER (PARTITION BY c.region ORDER BY cc.population_city DESC) AS rnk FROM world.country c JOIN world.city cc ON (c.capital=cc.id) WHERE c.region=p_region) SELECT r.name_city, r.population_city FROM r WHERE r.rnk = 1 ; $$ ; ``` --- ### Fonctions qui retournent un type composé défini par les lignes d'une table Dans le schéma `world`, on veut écrire une fonction qui prend en argument une région et renvoie la description de la capitale la plus peuplée de cette région On se contente de nommer la table pour indiquer que le résultat doit avoir même structure qu'une ligne de la table ```sql CREATE OR REPLACE FUNCTION username.capitale(p_region text) RETURNS world.city LANGUAGE SQL AS $$ WITH r AS( SELECT cc.*, RANK() OVER (PARTITION BY c.region ORDER BY cc.population_city DESC) AS rnk FROM world.country c JOIN world.city cc ON (c.capital=cc.id) WHERE c.region=p_region) SELECT r.id, r.name_city, r.countrycode, r.district, r.population_city FROM r WHERE r.rnk = 1 ; $$ ; ``` --- ### Fonctions qui retournent une table Dans le schéma `world`, on veut écrire une fonction qui prend en argument une région et renvoie la table des capitales de la région --- ### Table de schéma explicite On se contente d'abord de renvoyer le nom de la capitale. On explicite le schéma de la table résultat ```sql CREATE OR REPLACE FUNCTION username.capitales_region(p_region text) RETURNS TABLE (name_capital text) LANGUAGE SQL AS $$ SELECT cc.name_city FROM world.country c JOIN world.city cc ON (c.capital=cc.id) WHERE c.region=p_region ; $$ ; ``` --- ### Table de même schéma qu'une autre table [https://www.postgresql.org/docs/15/xfunc-sql.html](https://www.postgresql.org/docs/15/xfunc-sql.html) On veut maintenant récupérer une table de même schéma que `city`. La solution est très simple. On profite de ce qu'à chaque table correspond un type de même nom et on utilise le mot-clé `SETOF`. ```sql CREATE OR REPLACE FUNCTION username.capitales_region_large(p_region text) RETURNS SETOF world.city LANGUAGE SQL AS $$ SELECT cc.* FROM world.country c JOIN world.city cc ON (c.capital=cc.id) WHERE c.region=p_region ; $$ ; ``` --- ### Autres langages - PL/pgSQL - C - PL/Python - PL/R - ... ??? `CREATE PRODCEDURAL LANGUAGE` --- template: inter-slide name: fonctions-sql-usages ## Fonctions SQL: usages --- <img src="./img/bd_2023-sakila.png" width="989" /> --- ### Fonction reprise depuis `sakila` ```sql CREATE OR REPLACE FUNCTION user.inventory_held_by_customer( p_inventory_id integer, OUT customer_id int2) AS $$ SELECT customer_id FROM sakila.rental WHERE return_date IS NULL AND inventory_id = p_inventory_id ; $$ LANGUAGE 'sql' ; ``` --- ### Fonctions de `sakila` ```sql CREATE OR REPLACE FUNCTION uname.film_in_stock( p_film_id integer, p_store_id integer, OUT p_film_count integer) RETURNS SETOF integer AS $$ SELECT inventory_id FROM sakila.inventory WHERE film_id = $1 AND store_id = $2 AND sakila.inventory_in_stock(inventory_id); $$ LANGUAGE sql ; ``` --- ### Invocation ```sql SELECT * FROM sakila.film_in_stock(12, 1) ; ``` |p_film_count| |-----------:| | 60| | 61| | 62| ??? --- template: inter-slide ## Vues : pourquoi ? ??? > Almost all relational database products offer views as a level of abstraction from tables. In a view, you can query multiple tables and present additional derived columns based on complex calculations. > Views are generally read-only, but PostgreSQL allows you to update the underlying data by updating the view, provided that the view draws from a single table. > To update data from views that join multiple tables, you need to create a trigger against the view. > Version 9.3 introduced materialized views, which cache data to speed up commonly used queries at the sacrifice of having the most up-to-date data. --- template: inter-slide ## Vues : motivation Si vous vous retrouvez à écrire tous les jours la même requête, créer une vue Une vue n'est rien d'autre qu'une requête stockée de façon persistante --- ### Vues sur une seule table ```psql CREATE OR REPLACE VIEW co_asia AS SELECT * FROM world.country WHERE continent = 'Asia' ; ``` --- ### Interroger une vue Une vue s'interroge comme une table : ```psql SELECT region, sum(population_country) AS pop FROM co_asia GROUP BY region ORDER BY pop DESC ; ``` -- .plot-callout[ ``` region |pop | -------------------------+----------+ Eastern Asia |1507328000| Southern and Central Asia|1490776000| Southeast Asia | 518541000| Middle East | 188380700| ``` ] --- ### Vues et manipulation de données Si - la vue (`VIEW`) provient d'une table unique - la vue contient la *clé primaire* de la table d'origine Alors on peut modifier la table au travers de la vue. On peut - insérer - supprimer - mettre à jour #### Exemple - La vue `co_asia` provient d'une unique table : `world.country` - La vue `co_asia` contient la *clé primaire* `countrycode` de la table `world.country` ??? Updatable views --- ###
Cette instruction : ```psql UPDATE co_asia SET continent='Antarctica'; ``` ... est légale -- Mais, après exécution : - la vue `co_asia` est vide
- les pays d'Asie de `country` sont délocalisés en Antarctique
--- ### Pour éviter ca : `WITH CHECK OPTION ` ```psql CREATE OR REPLACE VIEW boucheron.co_asia AS SELECT * FROM world.country WHERE continent = 'Asia' WITH CHECK OPTION ; ``` Ajouter cette modification lors de la création de la vue et PostgreSQL s'opposera à une tentative d'insertion hors de la vue ou à une tentative de mise à jour qui placerait des tuples hors de la vue --- exclude: true .f6[ ```psql CREATE OR REPLACE VIEW sakila.actor_info AS SELECT a.actor_id, a.first_name, a.last_name, sakila.group_concat(DISTINCT (c.name::text || ': '::text) || (( SELECT sakila.group_concat(f.title::text) AS group_concat FROM sakila.film f JOIN sakila.film_category fc_1 ON f.film_id = fc_1.film_id JOIN sakila.film_actor fa_1 ON f.film_id = fa_1.film_id WHERE fc_1.category_id = c.category_id AND fa_1.actor_id = a.actor_id GROUP BY fa_1.actor_id)) ) AS film_info FROM sakila.actor a LEFT JOIN sakila.film_actor fa ON a.actor_id = fa.actor_id LEFT JOIN sakila.film_category fc ON fa.film_id = fc.film_id LEFT JOIN sakila.category c ON fc.category_id = c.category_id GROUP BY a.actor_id, a.first_name, a.last_name; ``` ] --- ### Vues multi-tables : exemple de vue sur `world` .f6[ ```psql CREATE OR REPLACE VIEW country_info AS SELECT cn.countrycode, cn.name_country, cn.region, string_agg(DISTINCT ci.name_city::text , ', '::text) AS cities FROM world.country cn LEFT JOIN world.city ci ON (cn.countrycode = ci.countrycode) GROUP BY cn.countrycode, cn.name_country, cn.region; ``` ] ??? [Stackoverflow](https://stackoverflow.com/questions/2560946/postgresql-group-concat-equivalent) --- ### Insertion/Mise à jour/Suppression sur les vues multi-tables Problème : quelles tables doivent être modifiées ? ```psql DELETE FROM boucheron.country_info WHERE countrycode = 'FRA' ; ``` - supprimer les lignes de `country` ? - supprimer les lignes de `city` ? ??? [Database abstraction](http://www.postgresonline.com/journal/archives/11-Database-Abstraction-with-updatable-Views.html) --- exclude: true template: inter-slide ## Vues : usages --- exclude: true ### Vues et confidentialité ??? --- ### Vues matérialisées Les vue matérialisées *cachent* les données récupérées Cela se produit - lors de la création de la vue et - lorsque on exécute `REFRESH MATERIALIZED VIEW ...` ??? > When you mark a view as materialized, it will requery the data only when you issue the `REFRESH` command. > - The upside is that you're not wasting resources running complex queries repeatedly; > - the downside is that you might not have the most up-todate data when you use the view. --- exclude: true template: inter-slide ## Résumé --- exclude:true template: inter-slide ## Perspectives --- exclude:true ### References - [Postgresql Bloggers](https://planet.postgresql.org) --- class: middle, center, inverse background-image: url('./img/pexels-cottonbro-3171837.jpg') background-size: cover # The End