Algèbre Relationnelle et SQL - 4 - page 1/15 - Bertrand LIAUDET

EPSI Paris

70 rue Marius Aufan

92300 LEVALLOIS PERRET

http://www.epsi.fr/Campus/Campus-de-Paris

BASES DE DONNEES RELATIONNELLE

Algèbre relationnelle et SQL

4 : Compléments

B. LIAUDET

Algèbre Relationnelle et SQL - 4 - page 2/15 - Bertrand LIAUDET

SOMMAIRE

SOMMAIRE 2

COMPLEMENTS DE SQL 3

1. Gestion des utilisateurs et de leurs droits 3

2. Gestion des transactions 5

3. Le dictionnaire des données : la BD Information schema 7

4. PL-SQL : procédure et fonction 10

5. Déclencheur (trigger) 11

6. Intégration dans une application 13

Edition : août 2011

Algèbre Relationnelle et SQL - 4 - page 3/15 - Bertrand LIAUDET

COMPLEMENTS DE SQL

1. Gestion des utilisateurs et de leurs droits

Principes

Présentation

La BD est utilisée par de nombreux utilisateurs.

Le SGBD permet de gérer des utilisateurs (CMS) et particulièrement de définir les droits de

consultation, création, modification, suppression pour les tables, les attributs et les tuples de la

BD.

Notions de sécurité

La gestion des utilisateurs relève de la politique de sécurité.

En limitant les accès des utilisateurs, on limite les possibilités de piratage en tout genre.

CREATE USER

La commande CREATE USER permet de créer un utilisateur.

Exemple

CREATE USER toto IDENTIFIED BY monMotDePasse ;

On peut aussi utiliser le GRANT pour créer un utilisateur.

DROP USER

La commande DROP USER permet de créer un utilisateur.

Exemple

DROP USER toto CASCADE ;

GRANT

La commande GRANT sert au propriétaire d’une table à donner certains droits sur cette table à

d’autres utilisateurs.

Exemples

Donner le droit à l’utilisateur 1 de consulter la table emp : GRANT SELECT ON proprio.emp TO utilisateur1;

Consultation et insertion :

Algèbre Relationnelle et SQL - 4 - page 4/15 - Bertrand LIAUDET

GRANT SELECT, INSERT ON proprio.emp TO utilisateur1;

Tous les droits pour deux utilisateurs : GRANT ALL ON proprio.emp TO utilisateur1, utilisateur2;

Consultation pour tous les utilisateurs : GRANT SELECT ON proprio.oeuvres TO PUBLIC;

Limiter les droits à certains attributs GRANT SELECT (ename, job, deptno) ON proprio.emp TO PUBLIC;

Utilisation des rôles prédéfinis

GRANT CONNECT, RESOURCE TO Bertrand ;

CONNECT est un rôle prédéfini qui permet la création de tables et donc leur modification,

suppression, consultation.

RESOURCE est un rôle prédéfini qui permet de faire du PL-SQL : création de triggers et de

procédures stockées.

DBA donne tous les privilèges.

REVOKE

Inversement, la commande REVOKE permet de supprimer des droits.

Exemples

Suppression du droit d’insertion de l’utilisateur 1 : REVOKE INSERT ON emp FROM utilisateur1;

Suppression de tous les droits : REVOKE ALL PRIVILEGES ON emp FROM utilisateur1;

Algèbre Relationnelle et SQL - 4 - page 5/15 - Bertrand LIAUDET

2. Gestion des transactions

Présentation

Une transaction est un ensemble de requêtes élémentaires du DML (insert, update, delete) qui

doivent être exécutées ensemble pour maintenir la cohérence de la base.

Une transaction doit être ACID :

Atomique : tout ou rien

Cohérente : maintenir la cohérence de la BD

Isolée : être indépendantes des autres transactions

Durable : ses effets doivent être persistant

La durabilité est un principe de base des SGBD. La cohérence est une conséquence des

contraintes d’intégrité, de l’atomicité et de l’isolation. On va donc surtout s’intéresser aux

problème de l’atomicité et de l’isolation.

Atomicité : le problème des pannes

COMMIT et ROLLBACK

Pour être atomique, une transaction doit valider toutes ses requêtes élémentaires ou

aucunes.

SAVEPOINT : permet de marquer le début de la transaction.

COMMIT : permet de valider la transaction : toutes les requêtes élémentaires sont

désormais validées durablement.

ROLLBACK : tant que le COMMIT n’est pas exécuté, un ROLLBACK permet de revenir

au précédent SAVEPOINT. C’est ce que fera automatiquement le système en cas de panne

au milieu de la transaction.

SAVEPOINT automatique

Toute connexion au serveur génère un SAVEPOINT automatique.

Tout COMMIT génère un SAVEPOINT automatique.

COMMIT manuel

L’instruction COMMIT peut être passée manuellement à tout moment.

COMMIT automatique

Si la variable AUTOCOMMIT est à vrai, chaque instruction du DML est suivie

automatiquement par un COMMIT.

Toute instruction du DDL génère automatiquement un COMIIT.

Toute déconnexion « propre » d’un utilisateur (disconnect ou exit) génère automatiquement un

COMMIT.

Algèbre Relationnelle et SQL - 4 - page 6/15 - Bertrand LIAUDET

Isolation : le problème de la concurrence

Présentation

Pour éviter que deux utilisateurs ne modifient en même temps une donnée, les instructions

de modifications de données (insert, update et delete) verrouillent les lignes sur lesquelles

elles travaillent.

En contexte transactionnel cette gestion se complique du fait que la transaction traite

plusieurs requêtes élémentaires.

Le « niveau d’isolation » va définir la visibilité des modifications en cours dans une

transaction.

Si une transaction n’est pas isolée, un autre utilisateur voit les états intermédiaires avant le

commit.

Si une transaction est isolée, un autre utilisateur ne voit pas les états intermédiaires mais ne

voit que l’état qui précède la transaction et le résultat du commit de la transaction.

Le problème de l’isolation peut être en partie contourné comme celui de l’atomicité.

Algèbre Relationnelle et SQL - 4 - page 7/15 - Bertrand LIAUDET

3. Le dictionnaire des données : la BD Information schema

Présentation

Le dictionnaire des données contient des méta-données : des données sur les données.

Le dictionnaire des données contient toutes les informations sur tous les objets gérés par le

serveur : les tables, les attributs, les contraintes, les index, les utilisateurs, les droits, etc.

Normalement, c’est une vue construite à partir des données existants effectivement. Elle

n’est pas modifiable.

Usages

Le dictionnaire des données permet de :

Consulter l’état de la base

Ecrire des programmes qui auront des listes d’utilisateurs, de tables ou d’attributs, par

exemple, en paramètre.

Dictionnaire MySQL

Présentation

http://dev.mysql.com/doc/refman/5.0/fr/information-schema.html

MySQL contient deux bases de données par défaut pour gérer les données du SGBD :

la BD MySQL : première version du dictionnaire des données, principalement pour gérer

les droits. Les tuples de cette BD sont modifiables directement.

la BD information_shéma : dictionnaire des données plus standard apparu en version 5.

Exemples d’usage

Liste des tables du serveur

select table_schema , table_name

from information_schema.tables ;

Ré-écriture de DESC avec le dictionnaire des attributs

drop procedure if exists desc2;

delimiter //

Create procedure desc2(v_nomTable varchar(64), v_nomSchema

varchar(64))

BEGIN

SELECT

c.column_name as Nom,

c.column_type as Type,

c.is_nullable as ‘NULL’,

CASE WHEN k.column_name=c.column_name THEN ‘Oui’

ELSE ‘Non’

END as ‘Key’,

c.column_default as Extra

FROM information_schema.columns c

Algèbre Relationnelle et SQL - 4 - page 8/15 - Bertrand LIAUDET

LEFT OUTER JOIN information_schema.key_column_usage k

ON (k.column_name=c.column_name

AND k.table_name= c.table_name

AND k.table_schema= c.table_schema

AND k.constraint_name= ‘PRIMARY’)

WHERE c.table_name=v_nomTable

AND c.table_schema=v_nomSchema;

END;

//

delimiter ;

Algèbre Relationnelle et SQL - 4 - page 9/15 - Bertrand LIAUDET

Dictionnaire ORACLE

Les vues du dictionnaire des données

Le dictionnaire des données contient les vues préfixées par « DBA_ », « ALL_ » ou

« USER_ ».

Ces vues contiennent des informations sur les objets de la base.

Les vues « DBA_ » contiennent des informations sur tous les objets de tous les schémas.

Ces vues sont en général accessibles par les utilisateurs ayant des droits d’administration.

Les vues « ALL_ » contiennent des informations sur les objets accessibles par le groupe

PUBLIC et par l’utilisateur courrant. Les vues « ALL » sont les vues accessibles à tous.

Les vues « USER_ » contiennent les informations sur les objets accessibles par l’utilisateur

courant.

Les 3 catalogues

Le catalogue est la vue contenant tous les objets accessibles.

Il y a 3 catalogues : « DBA_Catalog », « ALL_Catalog » et « USER_Catalog »

« CAT » est un synonyme de la vue « USER_catalog ».

Exemples

Lister toutes les tables et d’autres objets

SQL> select * from cat;

Lister les tables

SQL> select table_name from user_tables ;

Principales vues du dictionnaire des données

all_catalog -- tables, sequence, synonyme, vue,

environ 4000

all_objects -- 19 types d’objets, environ 5000

all_views -- les vues, environ 1000

user_indexes -- les index

etc.

La vue des vues : all_views

La vue qui contient toutes les vues du dictionnaire s’appelle : « all_views » Desc all_views

select count(*) from all_views ;

usage de all_views

La vue ALL_VIEWS va nous permettre de trouver les vues qui nous intéressent. -- pour chercher les vues qui concernent les sequences :

Select view_name from all_views where view_name like « %SEQ% » ;

Algèbre Relationnelle et SQL - 4 - page 10/15 - Bertrand LIAUDET

4. PL-SQL : procédure et fonction

Le PL-SQL

Dans la plupart des SGBD-R, il existe un langage de programmation impératif classique qui

permet d’écrire des successions d’opérations relationnelles en utilisant les possibilités

habituelles de la programmation impérative type Pascal, C ou C++ (variable, affectation, tests,

boucle, exception, procédure et fonction…).

PL-SQL est le nom générique de ce langage pour tous les SGBD. La syntaxe du PL-SQL est en

partie standardisée.

Sous Oracle, ce langage s’appelle : le PL-SQL, (Procedural Language extensions to SQL).

Sous SQL Server, ce langage s’appelle : Transact-SQL. SQL-Server permet aussi d’écrire

des procédures en « CLR », c’est-à-dire avec un langage .NET pris en charge par le serveur

(C#.NET, Visual Basic.NET)

Fonctions PL-SQL

Les fonctions PL-SQL pourront être utilisées dans les requêtes SQL ou dans les procédures et

les fonctions PL-SQL.

Procédures PL-SQL

Les procédures PL-SQL pourront être utilisées dans les procédures et les fonctions PL-SQL.

Elles peuvent aussi être utilisées directement par des programmes utilisant la BD. Ainsi, des

tâches complexes peuvent être réalisées avec un seul accès au serveur.

Les procédures ont deux usages principaux :

Gérer l’environnement : c’est un usage administratif (administration de la base ou

administration d’une application)

Fournir une interface de programmation pour les applications qui soient efficaces et facile à

maintenir.

Algèbre Relationnelle et SQL - 4 - page 11/15 - Bertrand LIAUDET

5. Déclencheur (trigger)

Présentation

Définition

Un trigger est un programme déclenchée automatiquement quand il se passe quelque chose

dans le SGBD.

Classification

On peut définir trois grandes catégories de trigger :

Les triggers DDL

Les triggers DML

Les triggers de remplacement

Les triggers DDL

Les triggers DDL peuvent être déclenchés :

sur une action du DDL : CREATE, DROP ou ALTER

quand un utilisateur se connecte ou se déconnecte de la BD

Ces triggers ne sont pas standards. On les trouve particulièrement dans SQL Server.

Les triggers DML

Un trigger est un programme associée à une table et qui est déclenchée automatiquement avant

(BEFORE) ou après (AFTER) une action du DML : INSERT, UPDATE ou DELETE d’un

tuple de la table.

Les triggers de remplacement

Un trigger DML peut aussi permettre de remplacer l’instruction déclenchante par une autre

action.

Utilité des triggers DML : trigger BEFORE et trigger AFTER

Trigger BEFORE

Un trigger BEFORE est un trigger qui se déclenche avant l’action du DML (INSERT,

UPDATE, DELETE). Il permet de :

Interdire l’action du DML en cas d’incohérence des données saisies : on peut gérer les

valeurs limites avec un trigger. La vérification se fait avant d’avoir enregistré les nouvelles

données. Le trigger peut alors soit interdire la modification, soit la modifier, soit la laisser

s’exécuter mais envoyer un message d’avertissement.

Reformater les données saisies : on peut passer du texte en majuscules.

Algèbre Relationnelle et SQL - 4 - page 12/15 - Bertrand LIAUDET

Gérer les attributs calculés du tuple en cours de modification. Cette possibilité

correspond à un trigger AFTER mais ce dernier ne peux pas s’appliquer au tuple en cours

de modification.

Trigger AFTER

Un trigger AFTER est un trigger qui se déclenche après une action du DML : INSERT,

UPDATE ou DELETE. Il permet de :

1. Mettre à jour la BD du fait de la modification qu’on vient d’y apporter. La mise à jour

se fait après avoir enregistré les nouvelles données. Cette mise à jour concerne des attributs

calculés.

A noter que : un trigger AFTER ne peut pas modifier de données du tuple en cours de

modification. Les attributs calculés du tuple en cours de modification sont gérés par un

trigger BEFORE.

Algèbre Relationnelle et SQL - 4 - page 13/15 - Bertrand LIAUDET

6. Intégration dans une application

Présentation

Le problème

Les applications développées en C++, java, PHP, etc. peuvent communiquer avec le serveur de

la BDD pour consulter ou modifier les données.

Standard de communication

Deux grands standards de communication :

ODBC : Open DataBase Connectivity, API en langage C.

JDBC : Java DataBase Connectivity, API en langage Java.

Ces standards sont des interfaces de programmation d'assez bas niveau permettant de

transmettre des requêtes SQL à un SGBD et de récupérer les résultats.

Outils plus ou moins intégrés aux SGBD

ORACLE propose le PRO*C ou PRO*C++. Ce sont des outils permettant d'inclure des

commandes SQL dans un source C. Il s'agit en fait d'un précompilateur : le source Pro*C

est traduit en source C avec des appels aux librairies ORACLE.

Ce type d’outils est un peu dépassé du fait des standards ODBC et JDBC et du

développement du Web et du graphisme.

On peut intégrer une base de données SQL Server dans n’importe quel environnement de

développement de Visual Studio (Basic, C++, C#, Web Developer, ASP .Net Ajax)

Certains SGBD offrent des environnements de développement graphique qui permette un

développement rapide. Par exemple : ORACLE-APEX (Application Express), ou

l’environnement de développement 4D.

Etapes de fonctionnement

1. Le client établit une connexion au serveur et à la base de son choix.

2. Le client demande l’exécution d’une requête

3. Le serveur exécute la requête

4. Le client récupère le résultat

Exemple Java

Six méthodes importantes en Java

Connection DriverManager . getConnection (String url, String user, String password)

Etablir la connexion au serveur

Statement Connection . createStatement ( )

Algèbre Relationnelle et SQL - 4 - page 14/15 - Bertrand LIAUDET

Créer un état associé à la base

ResultSet Connection . executeQuery (String query)

Exécuter la requête « query ». La méthode renvoie un ResultSet qu’on va ensuite exploiter.

xxx ResultSet . getxxx (int no) , xxx ResultSet . getxxx (String name)

Renvoie la valeur du champ n° « no » ou de nom « name » d’un ResultSet.

bool ResultSet . next ( )

Passe au tuple suivant s’il en reste encore un, sinon renvoie faux.

* . close ( )

Fermetures diverses dont celle de la connexion à la BD et au serveur.

Programmme type

import java.io.*;

import java.sql.*;

public class jdbctest

{

public static void main(String args[]) throws

ClassNotFoundException, FileNotFoundException, IOException,

SQLException

{

String url = args[0];

String usr = args[1];

String pwd = args[2];

// Charger le pilote PostgreSQL

Class.forName("org.postgresql.Driver");

try

{

// Se connecter a la base

System.out.println("Connexion a la base " + url);

Connection db = DriverManager.getConnection(url, usr,

pwd);

System.out.println("Connecte.");

Statement st = db.createStatement();

ResultSet rs = st.executeQuery("select * from emp");

if (rs != null)

{

// On souhaite consulter les 3 premieres lignes au

plus

st.setMaxRows(3);

while (rs.next())

{

int id = rs.getInt("id"); // Recuperer la valeur

d'un attribut selon son nom

String nom = rs.getString(1); // ou selon son

numero

String comment = rs.getString("comment");

System.out.println("id="+id+" nom="+nom+"

comment="+comment);

}

rs.close();

Algèbre Relationnelle et SQL - 4 - page 15/15 - Bertrand LIAUDET

}

// Fermer la connexion

System.out.println("Fermeture de la connexion");

st.close();

db.close();

}

catch (Exception ex)

{

System.out.println("Exception levée - et non traitée");

}

}

}