domenica, gennaio 02, 2011

Basi di Dati: PHP ed XML

PHP ed XML

XML ci permette di definire una base di dati semi-strutturata (non sono costretto a scriverne lo schema)
E' possibile definire degli schema xml con dtd (più vecchio, meno usato ora) o xsd (xsd è basato su xml a sua volta)
In xml è obbligatoria nella prima riga l'intestazione xml (nell' es. le parti in grassetto sono obbligatorie <?xml version="1.0" encoding="UTF-8">) che è l'unico "tag" che non viene chiuso.
Esisono diversi modi di leggere un xml con php (in generale con ogni linguaggio di programmazione che supporta xml)

XPath

E' un linguaggio basato su xml che permette di indirizzare nodi ed attributi di xml.
Ha una sintassi simile a quella del filesystem. Il carattere / all'inizio di un xpath rappresenta la root e ogni nome seguito da / indica un element a sua volta. Es:
<Root><Element1><Element11 attr="ciao"></Element11><Element12 attr="Piero"></Element12></Element1></Root>
Posso indicare Element11 con /Element1/Element11
Ammette * come carattere jolly. | indica un or logico (es.: /Element1/Element11 | /Element1/Element12
. indica la posizione attuale nell'albero. .. il padre della posizione attuale
// indica qualsiasi percorso tra 2 nodi (es. Root//Element12 indica tutti gli element12 che si trovano a qualsiasi livello sotto Root)
Posso indicare dei predicati (come se fosse una where sql)
book[title="titolo"] (presenza di un sotto-elemento di nome title con valore "titolo")
book[@title="titolo"] (presenza di un attributo di nome title con valore "titolo")
book/chapter[name = "pluto" | name = "pippo"]
book/chapter[position() = 1]
book [count(chapter) < 10)
book[title contains(text(), "miotesto")]

W3C Document Object Model (DOM)

DOM è uno standard W3C e definisce un metodo comune a tutti i linguaggi di programmazione che lo implementano di leggere un xml.
Si parte dalla root e si naviga andando ai sottonodi. Il testo contenuto in un nodo è considerato un nodo a sua volta (???).
La differenza di visione tra SAX e DOM è sintetizzata nella slide seguente:

Inizializzo DOM: $dom = new DomDocument();

Lettura xml con DOM

Carico l'xml che voglio leggere (oppure salto se voglio creare un xml da zero): $dom->load('filename');
Individuazione del nodo radice: $root = $dom->documentElement;
Scorro i figli finché non incontro quello che mi interessa $root->childNodes; e a loro volta i loro figli.
In alternativa posso prendere direttamente un array di nodi con XPath:
$xp = new DomXPath($dom);
$nodes = $xp->query('espressioneXPath' [, $nodoDaCuiValutare]); Dove nodoDaCuiValutare è un nodo (di default dovrebbe essere la root) da cui valutare eventuali percorsi relativi contenuti nell'XPath passato.
I nodi possono essere di due tipi: XML_ELEMENT_NODE (nodo vero e proprio) o XML_TEXT_NODE (testo in un nodo). Si vede dalla proprietà: $node->nodeType;
Il nome del nodo è: $node->nodeName; Il valore di un nodo testuale è: $node->nodeValue;
Gli attributi del nodo (se presenti) sono nella proprietà: $node->nodeAttributeNode; Rende una array chiave-valore (nome=chiave)

Scrittura xml con DOM

Creare un nodo: $root = $dom->CreateElement('nome');
Impostarlo come radice: $dom->appendChild($root); o come figlio di un altro nodo: $node->appendChild($otherNode);
Creazione attributi: $node->setAttribute('nome', 'valore');
Inserimento di un nodo testuale: $node->appendChild($dom->createTextNode('testo contenuto'));
Salvataggio: $mioXml = $dom->saveXML();

Simple Api Xml (SAX)

Scorre un documento xml e solleva un eventi all'apertura di un tag e alla chiusura di un tag
Si crea il parser $xml = xml_parser_create('UTF-8');
Si inizializza la gestione degli eventi:
xml_set_element_handler($xml, 'funzioneDaRichiamareAllAperturaDiUnTag', 'funzioneDaRichiamareAllaChiusuraDiUnTag')
xml_set_character_data_handler($xml, 'funzioneDaRichiamareConIlContenutoDiUnTag')
L'implementazione delle funzioni (nomi cambiati per abbreviare):
function opentag($xml, $tag, $attr) {}
function closetag($xml, $tag) {}
function content($xml, $data) {}

Basi di Dati: PHP e Database

PHP e Database

Esistono librerie di accesso ad un DB, oppure librerie che cercano di standardizzare l'accesso a tutti i DB (es. Pear).

MySQL

mysql_connect

resource mysql_connect ([ string $server = ini_get("mysql.default_host") [, string $username = ini_get("mysql.default_user") [, string $password = ini_get("mysql.default_password") [, bool $new_link = false [, int $client_flags = 0 ]]]]] )
Tenta la connessione ad un db

mysql_pconnect

Tenta la connessione ad un db, é persistente, cioè fa connection pooling

mysql_select_db

bool mysql_select_db ( string $database_name [, resource $link_identifier ] )
permette di scegliere il db a cui connettersi tra quelli ospitati sul server.

mysql_query

resource mysql_query ( string $query [, resource $link_identifier ] )
Invia una query. $query deve essere una stringa sql valida. Rende un puntatore all'area di memoria che contiene i dati.

Posso anche richiamare mysql_num_rows(puntatoreresodaquery) per sapere il numero di righe ritornate da una select o mysql_affected_rows(puntatoreresodaquery)

mysql_fetch_array

array mysql_fetch_array ( resource $result [, int $result_type = MYSQL_BOTH ] )
Legge un record risposto dalla query e lo carica in un array. Il parametro result_type può valere MYSQL_ASSOC (nome del campo come indice dell'array), MYSQL_NUM (indice dell'array numerico) o MYSQL_BOTH (entrambi, default).
Dopo avere chiamato mysql_query richiamo mysql_fetch_array per caricare il primo record e il cursore della query passa avanti al secondo record (se c'è). Quando non viene trovato il record viene ritornato un array vuoto (o null). Tipicamente si fa:

while ($row = mysql_fetch_array(…

mysql_free_result

Libera I risultati letti da una query

mysql_close

Chiude la connessione al database

Postregs

pg_connect

resource pg_connect ( string $connection_string [, int $connect_type ] )
Connette ad un database, prevede una stringa di connessione.
La stringa di connessione si compone di tante coppie nomevalore=valore separate da spazi. Valori possibili: host, port, dbname, user, password.
Es.: pg_connect("host=myhost port=1000 dbname=mydb user=michele password=michpass")

pg_pconnect

Analogo a pg_connect, ma persistente con connection pooling.

pg_query

Anche in questo caso ha delle funzioni per contare le righe ritornate: pg_num_rows, pg_affected_rows

pg_fetch_array

array pg_fetch_array ( resource $result [, int $row [, int $result_type ]] )
Effettua il fetch in un array. E' possibile specificare il tipo di array ritornato con le costanti: PGSQL_ASSOC, PGSQL_NUM e PGSQL_BOTH

pg_free_result

pg_close

PHP e Pear

Pear sta per Php Extension and Application Repository
Insieme di pacchetti molto ampio (posta elettronica, xml, database)
Va installata l'applicazione di Pear. Dopodiché da linea di comando si possono decidere quali pacchetti di pear installare

Pear e Database

Devo mettere nei file php che lo usano: require_once "DB.php";

connect

Si passa una uri per connettersi ad un database. dbtype://username:password@protocol+hosts/database?options=value
Es: "pgsql://michele:michipass@localhost/bdd"
Connessione:
$db = DB::connect("pgsql://michele:michipass@localhost/bdd");

Subito dopo i metodi di pear sarebbe opportuno richiamare DB::isError($db) per verificare eventuali errori.

query()

query($query, $par=array())
Restituisce false se non ci sono risultati, altrimenti un oggetto con i risultati della richiesta.
$query è una stringa sql, ma al posto dei valori delle where è opportuno mettere il carattere ? così facendo si crea un parametro. Il valore dei parametri viene passato nel successivo argomento.

limitQuery()

limitQuery($query, $from, $count, $par=array())
Come query, ma ritorna solo un certo numero di record, precisamente $count record a partire da $from.

prepare()/execute()

$s=prepare($query)
$result=execute($s, $params=array())
Prepare analizza una query senza inviarla al server. Execute la invia al server.

simpleQuery()

Per le query che non rendono risultati (esempio una CREATE TABLE)

Modalità di fetch

Ho 3 modalità di fetch: DB_FETCHMODE_ORDERED (array con indice numerico), DB_FETCHMODE_ASSOC (array con chiave nome del campo), DB_FETCHMODE_OBJECT (oggetto, anche definito dal programmatore.
Posso impostare la modalità di fetch a livello di connessione (per tutte le query): $db->setFetchMode(DB_FETCHMODE_...); (con $db risultato della DB::connect)
oppure a livello di singola riga: $riga = $result->fetchRow(DB_FETCHMODE_...); oppure $result->fetchInto($riga, DB_FETCHMODE_...); (con $result pari al ritornato dal metodo query)

Per effettuare il fetch in un oggetto user-defined (definito dal programmatore) devo usare $db->setFetchMode(DB_FETCHMODE_OBJECT, "nomeClasse"). L'oggetto deve evere un singolo costruttore che riceve un array (con chiave = nome dei campi)

Basi di Dati: PHP, operatori e strutture di controllo

Operatori

Confronto

== uguaglianza
=== identità (non effettua cast: controlla anche i tipi)
!= <> disuguaglianza
!== non identità
< minore di
> maggiore di
<= minore uguale
>= maggiore uguale

Assegnazione

= assegnazione standard
=& (CREDO) assegnazione per riferimento

Aritmetici

+ - * / % (l'operatore % è il resto della divisione intera. Es. 38%7 = 3)

Concatenazione di stringhe

.

Ibridi assegnazione

+= -= *= /= %=
++ -- (sia nella variante $a++ che nella variante ++$a con logica simile al java. Francamente ne sconsiglio l'uso per non creare espressioni poco leggibili)
.=

Logici

AND (anche &&)
OR (anche ||)
NOT (anche !)
XOR

Strutture di controllo

if, elseif, else come in java
switch come in C (switch (variabile) { case
valore: codice [break;]; default: codice; }. Una volta che si è trovato un valore valido è eseguito anche il codice dei successivi case (e default)!!!
operatore condizionale ternario: $var
=
condizione
?
ValoreSeVero
:
ValoreSeFalso

while (condizione) {}
do {} while (condizione)
for identico a java
foreach (array
as
$k
=>
$val) {codice} permette di iterare le chiavi ed i valori di un array. Il codice viene eseguito per ogni coppia chiave/valore dell'array. Il codice può fare riferimento al valore della chiave con la variabile $k e al valore co $val. Mettendo solo foreach (array
as
$val) {codice} ciclo solo i valori
break esce dal ciclo
continue permette di saltare all'iterazione successiva

Definire una funzione:

Non sono costrette a ritornare un valore.
function
nomefunzione ($arg1, $arg2 …) { codice; [return($var)] }

Gli argomenti possono avere un valore di default:
function miafunzione ($arg1, $arg2 = 'Ciao') {return($arg2) }
Ovviamente dopo una variabile con default non possono essercene altre senza il default (assegnamento posizionale!)

Il codice che non si trova in nessuna function viene eseguito quando viene incontrato. Quello nelle function solo se vengono esplicitamente richiamate

Basi di Dati: PHP, Sintassi e Tipi

Sintassi PHP

Gli script PHP vanno messi in TAG così:
<?PHP
/*
Codice
*/ ?>
La sintassi è simile a C (commenti con // o /**/, ; a fine riga
Posso includere un altro file php con la funzione include('filename.php')

Gestione dei tipi

Le variabili iniziano con $ e possono iniziare con un numero: $2Variabile
I nomi delle variabili sono case sensitive

E' loose type: la variabile si dichiara assegnandole un valore, il tipo è deciso a runtime dall'interprete

Tipi di dato
Scalari: Boolean, Integer, Float, String
Complessi: Array, Object
Speciali: Resource, null

Posso usare come Boolean anche altri tipi. Vengono valutate a false anche: 0, 0.0, "", "0" e null (da far vomitare!)

Interi: se inizio un intero con 0 si intenderà su base ottale (quindi: 10 vale 10, ma 010 vale 8!). 0x10 è in base esadecimale = 16

Stringhe: si può quotare sia con apice singolo che doppio. Se uso il doppio apice ed indico il nome di una variabile questo verrà valutato come il contenuto di tale variabile. Es.:
$Variabile = "mamma"; $Var2="Ciao $Variabile"; "Var2 conterrà in realtà la stringa "Ciao mamma". Posso inserire caratteri di escape come "\n" (="a capo").
Se devo attaccare una variabile ad un altro pezzo di testo posso usare {}. Es.: $Var3="Oh ${Variabile}saura!"; Var3 varrà "Oh mammasaura!".
Sintassi Here Document: (non riporto, ma consiglio di rivederle tutte qui)
Il casting di un array in una stringa produce la stringa "array" (non il contenuto)! Stessa cosa per object e null.
Operatore di concatenazione di stringhe: . (punto)

Array: possono essere "standard" (=con chiave numerica) o con chiave stringa (o alcuni elementi con chiave numerica e altri con chiave stringa), gli elementi dell'array possono essere di qualsiasi tipo.
Creare un array, modo esplicito: funzione array: $Var=array(Chiave1=>Valore1, Chiave2=>Valore2) o senza chiavi: $Var=array(Valore1, Valore2) o misto: $Var=array(Chiave1=>Valore1, Valore2). Se imposto una chiave numerica e poi un elemento senza chiave esplicita l'indice adottato sarà pari all'indice più alto aumentato di uno, nel esempio "due" avrà chiave 17: $Var=array(16=>"uno", "due")
Se non ci sono chiavi numeriche la prima è 0 (base zero).
Funzione array_keys(array) ritorna le chiavi di un array. array_key_exists(chiave, array) ritorna true o false se la chiave esiste o meno.
Acesso ad elementi dell' array (sia lettura che scrittura) con parentesi quadre: $MyArr["uno"]=1; Se l'array non esiste così viene definito in modo implicito
Per togliere un elemento dall'array uso la funzione unset($array[chiave]). unset($array) cancella tutto l'array
count($array) Conta il numero di elementi in un array
Convertendo da un tipo scalare ad array si ottiene un array con quello scalare come unico elemento di indice 0.
Convertendo da un object ad array si ottiene un array con elementi pari al valore delle proprietà di tale oggetto e chiavi pari ai nomi delle proprietà stesse
Convertendo da null ad un array si ottiene un array vuoto.

Funzione gettype($Varname) ritorna il tipo
is_int($Varname) ritorna true se è di tipo intero, is_string($Varname) ritorna true se è stringa, is_null($Varname) ritorna true se il contenuto è null

Funzione echo: echo $Var scrive sulla pagina web il contenuto di $Var
Funzione print: stessa di echo (ma ritorna un valore e fa delle formattazioni)
Funzione print_r(array) stampa il valore di un array. var_dump(object) stampa il valore di un object. Sono funzioni di debug.

Posso forzare dei casting. Es.: $Var=1; $Var2=(string) $Var;

Strutture di controllo

if, elseif, else come in java
switch come in C (switch (variabile) { case
valore: codice [break;]; default: codice; }. Una volta che si è trovato un valore valido è eseguito anche il codice dei successivi case (e default)!!!
operatore condizionale ternario: $var
=
condizione
?
ValoreSeVero
:
ValoreSeFalso

while (condizione) {}
do {} while (condizione)
for identico a java
foreach (array
as
$k
=>
$val) {codice} permette di iterare le chiavi ed i valori di un array. Il codice viene eseguito per ogni coppia chiave/valore dell'array. Il codice può fare riferimento al valore della chiave con la variabile $k e al valore co $val. Mettendo solo foreach (array
as
$val) {codice} ciclo solo i valori
break esce dal ciclo
continue permette di saltare all'iterazione successiva

Definire una funzione:

Non sono costrette a ritornare un valore.
function
nomefunzione ($arg1, $arg2 …) { codice; [return($var)] }

Gli argomenti possono avere un valore di default:
function miafunzione ($arg1, $arg2 = 'Ciao') {return($arg2) }
Ovviamente dopo una variabile con default non possono essercene altre senza il default (assegnamento posizionale!)

Il codice che non si trova in nessuna function viene eseguito quando viene incontrato. Quello nelle function solo se vengono esplicitamente richiamate

Scope delle variabili

All'interno di una funzione non vengono viste le variabili definite fuori, ma solo quelle passate o definite dentro. Per referenziare una variabile definita all'esterno uso $GLOBALS['nomevariabile']
Posso anche definire due variabili dentro una funzione specificando che sono 2 variabili globali con l'istruzione global. Es:
$a = 1;
$b = 2;
function miafunc() {
    global $a, $b;
    return $a+$b;
}
E' possibile definire una variabile globale anche dentro una funzione preponendo la parola chiave global (es. global
$miavar;)

Passaggio di parametri per riferimento

Apponendo il carattere & davanti a $ si intendono passati per riferimento e non per valore (default). Es:
$a = 1;
$b = 1;
function miafunc(&$par1, &$par2) {
    $par1 += $par2;
}
miafunc $a, $b
In uscita da miafunc il valore di $a sarà effettivamente cambiato

Oggetti

Si crea una class con la parola chiave class (posso specificare la classe base con extends):
class mia_class extends altraclass {
    public $prop1
    function __construct() {}
    public function donothing() {}
}

Modificatori d'accesso: public, private, protected

Nome del costruttore: __construct() {}
Nome del distruttore: __destruct() {}

Supporto alle interfacce ed ereditarietà multipla

Costanti di classe (con const)

Proprietà e metodi statici

Metodi e classi astratte

Gestione delle eccezioni con try, throw, catch
Nel catch posso specificare i tipi di Exception (che sono classi che estendono Exception): try {} catch (miaexception $e) {}

Richiamare metodi di classe (ie conclusioni, non esposto nelle video lezioni!)

Per richiamare il metodo di un oggetto si usa ->
Es: miooggetto->miometodo()

Per richiamare metodi statici di una classe analogamente si usa :: es. miaclasse::miometodostatic

Basi di Dati: PHP, presentazione e configurazione

Una premessa: ho seguito tutte le videolezioni su PHP (non lo conoscevo prima). La mia personale considerazione è: "php è una merda". Probabilmente è facile da imparare, ma è veramente un linguaggio sporco, mi permetto di sconsigliarlo a tutti.

E' interpretato (non compilato), embedded nelle pagine html

Ci sono molte librerie

Aggiungere alla configurazione di Apache:
LoadModule php5_module "Percorso/php5apache2.dll"
AddType application/x-httpd-php .php
PHPIniDir
"Percorso"

Poi c'è il file di configurazione di PHP (php.ini), con direttive come:
include_path = "Percorso\includes" (cartella degli include)
extension_dir = "Percorso\extensions" (cartella dei moduli esterni di PHP)
Le estensioni hanno poi bisogno di essere "attivate" con:
extension=php_mysql.dll
extension=php_pgsql.dll
extension=php_xsl.dll

Posso inserire direttive di configurazione anche nel file di configurazione di apache (httpd.conf).
Posso modificare localmente una impostazione chiamando la funzione ini_set("NomeImpostazione", "Valore")

Alcune direttive:

max_execution_time = tempo massimo di elaborazione di una pagina php
upload_max_filesize = dimensione massima di un file uploadato
max_post_size = dimenzione massima dei dati inviati tramite POST (deve essere maggiore della precedente)
upload_tmp_dir = Directory dove vengono messi temporaneamente i files caricati
display_errors = (Off/On) Mostra eventuali errori dello script in una pagina web

venerdì, settembre 19, 2008

Basi di dati: Controllo dell'accesso: GRANT

Modello di Autorizzazione del System R (GRANT)

E' simile a quello implementato nella maggior parte dei BBMS

E' un sistema chiuso (di default nessun permesso concesso) e discrezionale (a discrezione dell'amministratore concedere i privilegi)

La gestione dei privilegi è delegata agli owner e a quanti essi delegano mediante GRANT. Quando un utente riceve dei privilegi WITH GRANT OPTION può anche delegarli a sua volta.

I privilegi delegabili sono SELECT, INSERT, UPDATE e DELETE

Esempi:

GRANT UPDATE ON
Tabella
TO
Utente (da diritto di UPDATE a Utente su Tabella)

GRANT UPDATE (Campo1, Campo2) ON
Tabella
TO
Utente (da diritto di UPDATE a Utente su campo1 e Campo2 di Tabella)

GRANT SELECT ON
Tabella
TO
Utente
WITH GRANT OPTION (da diritto di SELECT a Utente su Tabella. Utente può a sua volta delegare tale diritto)

GRANT ALL PRIVILEGES ON
Tabella
TO
Utente (da tutti i diritti a Utente su Tabella)


Le informazioni delle GRANT stanno nel CATALOG, precisamente nelle tabelle SYSAUTH e SYSCOLAUTH (suppongo in postgreSQL).

Esempio di SYSOUT:


La colonna UPDATE può avere valori: N, SOME, ALL. Se è SOME nella tabella SYSCOLAUTH devono essere specificati i campi per cui ha diritto di UPDATE.

Nella colonna go è specificato se l'utente ha la GRANT OPTION

Eliminare una GRANT: REVOKE

Solo chi ha concesso privilegi può rimuoverli

REVOKE
Privilege
ON
Tabella
FROM
Utente

Se un utente ha ricevuto GRANT da più utenti è necessario che tutti questi gli facciano una REVOKE affinché vengano effettivamente rimossi (BELLO SCHIFO!)

La revoca è ricorsiva (se faccio la REVOKE su un utente che a sua volta tramite GRANT OPTION ha concesso ad altri vengono revocati anche a quest'ultimi)

Aggiungo il timestamp (s) del momento in cui la GRNT viene concessa nella SYSAUTH: così posso fare la revoca ricorsiva


Posso ridurre ad un grafo:


Se un privilegio è dato in modalità non delegabile viene barrato l'arco di congiungimento

Quando revoco un privilegio che aveva GRANT OPTION per la ricorsione valuta il timestamp:

Se A da SELECT a B nell'istante 1

B da SELECT a C nell'istante 2

D da SELECT a B nell'istante 3

B da SELECT a E nell'istante 4

Se A revoca a B allora verrà revocato anche il permesso a C, ma non a E

Sistemi commerciali

Altri permessi concedibili/revocabili:

USAGE -> (per domini o insiemi di caratteri) Abilita l'uso del nuovo dominio (tipo di dati)

Anche INSERT è applicabile a singole colonne

REFERENCES -> Abilita delle colonne a essere inserite in vincoli di integrità referenziale

 
 

Gestioni degli utenti per gruppi

 
 

Comando di REVOKE aggiunge delle clausole opzionali:

  • Per rimuovere solo la GRANT OPTION
  • RESTRICT / CASCADE (default) -> non viene messa la revoca a cascata

Contenuti gestiti da Michele Bernardi. Non mi assumo alcuna responsabilità sulla correttezza di questi dati, usateli a vostro rischio e pericolo.

Basi di dati: Sicurezza di Basi di dati

Divisibile in 3 capitoli:

  1. Segretezza: proteggere da letture non consentite
  2. Integrità: proteggere da scritture non consentite
  3. Disponibilità: garantire il giusto accesso agli utenti legittimamente autorizzati (proteggersi da attacchi DOS: Denied Of Service)

Tecniche di protezione:

  1. Autenticazione: verificare di dare accesso solo a chi consentito
  2. Controllo dell'accesso: validare richieste di accesso (verificare cosa può fare l'utente autenticato)
    1. Crittografia: offuscare i dati per renderli non leggibili a terzi

     
     

    Innanzitutto devono essere definite delle politiche di sicurezza

    Le politiche di sicurezza vengono trasformate in Regole di autorizzazione comprensibili al database

    Il motore del database ha una parte adibita alla gestione delle autorizzazioni, il Reference Monitor che concede, nega o concede parzialmente l'accesso

     
     

    Due famiglie politiche di amministrazione della sicurezza (stabiliscono chi può concedere/revocare i permessi di sicurezza):

  • Centralizzato: un unico amministratore concede tutti i permessi di sicurezza
  • Distribuito: più di un amministratore. Di solito chi è abilitato a creare oggetti sul DB diventa proprietario degli oggetti che crea e ne è amministratore

 
 

 
 

Due famiglie di politiche di controllo d'accesso:

  • Politica del minimo privilegio. Le autorizzazioni di accesso ai dati vengono concesse solo per il minimo dei dati che l'utente userà. L'accesso è di default negato.
  • Politica della massima condivisione. L'accesso è di default consentito (deve essere negato esplicitamente)!

Controllo d'accesso dipendente dal nome: imposto le autorizzazione sui diversi oggetti del DB

Controllo accessi dipendente dal contenuto: creo viste e stored procedure e poi do le autorizzazioni su questi

Controllo accessi dipendente dal contesto (ad esempio dall'orario): mediante stored procedure

Controllo dipendenti dalla storia degli accessi (es: non di n accessi). Mediante stored procedures/triggers

Le politiche di controllo degli accessi si dividono in:

  • Discrezionali: DAC
    Richiedono che vengano specificati i diritti che ogni soggetto possiede sugli oggetti del sistema. Molto flessibili, ma di limitata portata. I DBMS normali sono DAC (ci sono anche i trusted DBMS che sono MAC)
  • Mandatorie: MAC
    Politiche basate su classificazione di dati e utenti (diversi livelli di sicurezza: Top Secret, Secret, Classified, Unclassified). I sistemi mandatori vietano che dati di livelli elevati possano spostarsi in livelli più bassi. No-Read-Up: Un utente con permessi per un livello può leggere quello e i sottostanti, non i superiori. No-Write-Down: non scrivere dati di livello alto in fonti raggiungibili dal basso

Attacco basato su cavallo di Troia

Un utente privo dei diritti su particolari dati riesce a modificare un programma di utilità generale. Il programma viene lanciato da un utente a privilegi alti e copia i dati in una posizione raggiungibile dall'hacker

Contenuti gestiti da Michele Bernardi. Non mi assumo alcuna responsabilità sulla correttezza di questi dati, usateli a vostro rischio e pericolo.

Basi di dati: Progettazione fisica di un database

Occorre ottimizzare molti parametri, alcuni specifici del DBMS

Ad esempio cache, suddivisione in file, ecc.

Creazione di INDICI

Gli indici sono un B-TREE che ordina alcuni campi di una tabella.

Possono essere UNIQUE (con vincolo di unicità) o no

Si creano con CREATE INDEX NomeIndice ON NomeTabella(ElencoCampiIndicizzati)

Si elimina con DROP INDEX NomeIndice

Vanno mantenuti (un indice non usato è un peso per il DB)

E' necessario inserire indici per le query che vengono fatte più spesso (per ottimizzarle)

L'accesso tramite indice è molto performante

Gli indici si dividono in vari tipi:

  • CLUSTERED (sono l'ordine fisico della tabella stessa) e UNCLUSTERED (solitamente residenti in una parte diversa della tabella)
  • DENSE (hanno un entry per ogni tupla della tabella), SPARSE (opposto)
  • SINGLE LEVEL (indice "flat"), MULTI LEVEL INDEX (indice organizzato ad albero)

Il DBMS definisce automaticamente un INDICE su ogni chiave primaria (di solito CLUSTERED)

E' abbastanza ragionevole mettere indici sulle chiavi esterne

Basi di dati: Schema logico di un database

Lo schema logico (o schema relazionale) non è indipendente ed astratto come lo schema concettuale.

Praticamente partendo dallo schema concettuale lo impoverisco per essere adattato alla realtà relazionale che è fatta solo di entità ed attributi e non ha legami di classi (superclassi, sottoclassi).

Analisi dei dati derivati

Ovvero i dati "derivabili da altri dati" (ad esempio numero di posti liberi in una sala da cinema (derivabile dal n° di prenotazioni)

Il progettista per decidere se tenere un dato derivato deve paragonare i "costi" fra averlo e non averlo. Ovvero la differenza di costo tra mantenere il dato derivato o calcolarlo quando serve.

I tipi di dati derivati sono ridondanti (appunto derivabili da un'altra fonte) possono essere di varia natura:

  • direttamente derivabili (es. durata sapendo ora inizio e ora fine)
  • derivabili tramite conteggio attraverso la relazione tra due entità (es: importo totale dell'acquisto se ho ho già gli oggetti acquistati ed il loro singolo prezzo)
  • derivabili tramite conteggio attraverso una relationship (es. n° di posti prenotati/liberi per le varie sale di un cinema)
  • ridondanti per ciclo di associazioni: potrebbe esserci addirittura una relationship ridonandante (ad esempio: avendo già gli studenti iscritti ad un corso e il docente che tiene il corso sarebbe ridondante avere una relationship diretta tra studente e professore del tipo "insegna a").

Gerarchie di generalizzazione

Ovvero la rappresentazione di una gerarchia

Possono essere ristrutturate in 3 modi:

  1. Mantenimento delle sole entità superclasse (generica)
    Esempio: Tengo impiegato al posto di avere impiegato, ingegnere, segretario, direttore. Gli attributi "extra" diventano opzionali in impiegato
  2. Mantenimento delle sole entità sottoclasse (specifica)
    Esempio: tengo le classi impiegato, ingegnere, segretario, direttore e gli attributi comuni (impiegato) li metto in ciascuna sottoclasse
  3. Mantenimento di entrambe le classi
    Ad esempio realizzerò la classe impiegato e le varie sottoclassi le realizzerò come entità "differenziali": ovvero munite dei soli attributi che estendono il concetto di impiegato, oltre, ovviamente, ad una relazione verso l'entità impiegato che viene effettuato mediante l'unica chiave primaria (unico gruppo di attributi ripetuto in ogni tabella. Ad es. codice fiscale). Nella entità impiegato ci sarà un campo booleano che indica l'appartenenza ad una sottoclasse (ad esempio attributi come ingegnere, segretario, direttore). Dovrò implementare un vincolo che non permetta di avere più di un attributo di sottoclasse attivo nel DB.

Normalmente la alternativa 3 è consigliabile.

Se ho (quasi) solo accessi che mi chiedono TUTTI gli attributi indipendentemente dalla sottoclasse di appartenenza conviene la 1 perché riduce gli accessi al DB (che altrimenti dovrebbero essere 1 per la superclasse e 1 per la sottoclasse).

Se invece ho (quasi) solo query che accedono a tutti gli attributi DIPENDENTEMENTE dalla sottoclasse di appartenenza (es.: non voglio mai vedere l'elenco di tutto il personale, ma solo separatamente quello di ingegneri, direttori, segretari ed impiegati) allora l'alternativa 2 è conveniente.

Scelta dell'identificatore primario

Se ci sono più identificatori che possono essere chiavi candidate devo scegliere.

  • Escludere quelli che possono avere valori nulli (ad es. se non sempre inserisco subito il codice fiscale lo devo evitare)
  • Evitare le chiavi composte
  • Scegliere una chiave che abbia un valore semantico se possibile (non un ID creato apposta)

Togliere attributi multi valore e attributi complessi

Trasformarli in entità associata (ad esempio il n° di telefono) che conserva la stessa cardinalità (ad es. 1-n)

Attributi composti possono essere "espansi" sull'entità stessa o possono essere messi in una nuova entità collegata (ad es. indirizzo come composito da via, CAP, città). Potrebbero anche essere tenuti e collassati in un solo attributi (ad es. indirizzo stringa unica).

Trasformazione delle relazioni

Molti a molti: si devono necessariamente trasformare in relazioni. Le relazioni così formate avranno la chiave primaria costituita dall'insieme degli attributi di PK delle due relazioni che associa più tutti i campi necessari a descrivere gli attributi di associazione.

Uno a uno: inserisco all'interno dell'una o dell'altra relazione la chiave esterna che riferisce all'altra entità. Dove definisco la chiave esterna metto anche gli attributi di relazione. Di solito bisogna pensare al tipo di query che verrà effettuato in modo da minimizzare il numero di join. Bisognerebbe cercare di evitare valori nulli (quindi se una relazioni ha cardinalità (0;1) (1;1) conviene mettere la chiave sterna a destra).

Uno a molti: inserisco all'interno della relazione che sta dalla parte con cardinalità massima 1 la chiave esterna (tra persona e indirizzi su indirizzi).

Ina caso di associazione tra più di 2 tabelle è necessario ricorrere al caso molti-a-molti illustrato qui sopra.

 
 

 
 

 
 

 
 

Contenuti gestiti da Michele Bernardi. Non mi assumo alcuna responsabilità sulla correttezza di questi dati, usateli a vostro rischio e pericolo.

Basi di dati: PL SQL e PLpgSQL

Esistono due sistemi per estendere le funzionalità dei RDBMS:

  1. Estensioni native al linguaggio SQl (caso di PL SQL (Oracle) e PLpgSQL (Postgre))
  2. Linguaggio "ospitato" (ad esempio un linguaggio come PYTON che viene interpretato dal motore del DB a cui viene aggiunta la possibilità di avere statement SQL

PLSQL

SELECT INTO

: per scrivere il valore di una select che ritorna un solo record in variabili utilizzabili:
SELECT
Campo1, Campo2
INTO
Variabile1, variabile2 FROM….

CURSORE

: Una struttura dati che ci permette di navigare tra i record ritornati dalla SELECT (navigare solo in avanti).
DECLARE CURSOR
NomeCursore
FOR SELECT ….
OPEN
NomeCursore -> Apre il cursore e lo posiziona sul primo record
FETCH
NomeCursore
INTO
Var1, Var2, ecc.-> Scrive il record attuale dentro a delle variabili e passa al record successivo
CLOSE
NomeCursore -> Chiude il cursore (e dealloca le risorse)
Una variabile mostra lo stato del cursore (SQLCODE)

Programmi PLpgSQL

Un programma PL SQL è diviso in blocchi

Un header (con dichiarazione variabili, ecc.)

Un corpo con i comandi PL SQL e SQL-DML (SELECT INSERT UPDATE DELETE (CRUD)). Ha una gestione degli errori

DECLARE
Header
BEGIN
Corpo del programma
EXCEPTION
Gestione dell'eccezione
END;

Gestione Eccezioni

La gestione dell'errore può essere puntuale per tipo di errore:
BEGIN
Istruzioni
EXCEPTION
WHEN

TipoException
THEN
IstruzioniDiGestioneErrore
[WHEN AltroTipoException
THEN
IstruzioniDiGestioneErrore]
END

Per avviare una funzione posso usare RUN
nomeFunzione oppure SELECT
nomeFunzione

Il PLSQL è organizzato a blocchi

Sono ammessi tutti i tipi di dati SQL + tipi estesi

Sono ammessi tutti i comandi DML (DDL no, ma posso farli come DML sul catalog)

Posso dichiarare una variabile "come un certo attributo di una certa tabella" (il tipo sarà: NomeTabella.NomeCampo%TYPE) oppure come un record di una certa tabella (il tipo sarà: NomeTabella%ROWTYPE)

Esempi:

DECLARE
x NUMBER;
y VARCHAR(20);

oppure:

DECLARE
x tabella.campo%TYPE;

DECLARE
x tabella%ROWTYPE; (tipo di dati "riga": come riga della tabella specificata)

Le variabili dichiarate sono nulle fino ad un'assegnazione. L'operatore di assegnazione è :=

Cicli di controllo

IF
condizione1
THEN
istruzioni
[ELSEIF
condizione2
THEN
istruzioni]
[ELSE
istruzioni]
END IF

LOOP
istruzioni
EXIT WHEN
condizione (uscita dal loop)
END LOOP

WHILE
condizione
LOOP
istruzioni
END LOOP


FOR
var
IN
inizio fine
LOOP
istruzioni
END LOOP

Esempio (con tabella T1):

DECLARE

a T1.e%TYPE;
b T1.f%TYPE;
CURSOR
T1Cursor
IS
SELECT
e, f
FROM
T1
WHERE
e < f
FOR UPDATE; /*Con FOR UPDATE il cursore é aggiornabile*/

BEGIN

OPEN
T1Cursor;

LOOP
FETCH

T1Cursor
INTO
a, b;
EXIT WHEN
T1Cursor%NOTFOUND;
DELETE FROM
T1Cursor
WHERE CURRENT OF
T1Cursor; /*permesso solo se ho FOR UPDATE nel CURSORE*/
INSERT INTO
T1
VALUES (b, a); /*Li ho ri-scritti invertiti*/
END LOOP;

CLOSE
T1Cursor;

END;
.
run;

Posso incapsulare in procedure o funzioni (le funzioni hanno un valore di ritorno)

Posso passare parametri (anche con IN o OUT (o INOUT default) per definire se sono parametri passati o solo che ritornano)

Posso non specificare nome per i parametri (in tal caso si chiameranno $1, $2, …, $n)

Posso associare le procedure a dei trigger

CREATE OR REPLACE PROCEDURE
NomeProcedure (
param1 tipoParam1,

paramN tipoParamN
) AS
BEGIN
istruzioni
END

Per richiamarla:

BEGIN
NomeProdecure(param1,…paramN);
END;

Le funzioni hanno valore di ritorno: CREATE FUNCTION
NomeFunzione
RETURN
Tipo
AS

Si può fare l'OVERLOAD di funzioni, non si può dare valori di default ai parametri

Operatore di concatenamento di stringhe é: ||

TRIGGER

Le funzioni invocate da un trigger hanno uno specifico tipo di ritorno (TRIGGER). Es:
CREATE OR REPLACE FUNCTION
NomeProcedure
RETURN TRIGGER AS
Questo mi permette di avere all'interno del corpo le due variabili di tipo riga NEW (in caso di "ON INSERT" o "ON UPDATE") e OLD (in caso di UPDATE o DELETE). Se la funzione è per INSERT o UPDATE alla fine dovrò fare "RETURN NEW" (oppure "RETURN OLD" oppure "RETURN AltraRiga" oppure "RETURN NULL" (=nessuna riga (in caso di insert non inserisce niente!))

Per attivare la funzione come un trigger basterà poi la sintassi già vista. Es di trigger per riga su INSERT:
CREATE TRIGGER
NomeTrigger
BEFORE INSERT ON NomeTabella
FOR EACH ROW EXECUTE PROCEDURE
NomeProcedure();

Al posto di BEFORE INSERT posso mettere UPDATE o DELETE (oppure INSERT OR UPDATE, ecc.)

Dopo AS dovrei specificare il linguaggio. Es.: CREATE PROCEDURE
NomeProcedure
AS
$$
BEGIN

END $$ LANGUAGE plpgsql

La lezione bdd_2005112401 perde audio dal 33° minuto. Si vede un esempio d'uso di refcursor che non approfondisco.

Contenuti gestiti da Michele Bernardi. Non mi assumo alcuna responsabilità sulla correttezza di questi dati, usateli a vostro rischio e pericolo.

Basi di dati: Postgre SQL

Tutorial su http://www.postresql.org


 

SQL 99 compliant (tra cui transazioni)

Supporto ad estensioni (funzioni, operatori, funzioni aggregate, index methods, tipi di dati estesi)

Gestione del partizionamento (fisico sui dischi), gestioni di tipi di dato complessi

Gestione delle sequenze: campi che prendono automaticamente un valore in base ad un'espressione (tipo campo contatore, ma più flessibile). Il tipo di dato è serial (di default somma 1, ma posso definire come voglio: CREATE SEQUENCE SequenceName INCREMENT BY increment, più altre opzioni come [MINVALUE MinValue] [MAXVALUE MaxValue] [START StartValue] [CYCLE] (ripropone a ciclo i valori)). In realtà usa un campo int il suo default value è il valore di una funzione

Gestione degli array (SCONSIGLIATO: viola i principi dei DB relazionali!)


 

Il server è un servizio (postmaster)

Il servizio viene avviato con un utente apposito (che non deve essere un amministratore di sistema)

Basato sul concetto di cluster: uno storage fisico dei database (solo uno alla volta).

Per creare un nuovo cluster:

  1. Creare una cartella (utente PostgreSQL deve avere permessi di lett/scr)
  2. Initdb –D cartella


 


 

Comandi

Comandi per il server iniziano con "\"

\? help sui comandi

\h help su SQL

\l Lista dei DB

\d Descrittore (\dt elenco tabelle, \df elenco delle funzioni)

\z lista di accesso a tabelle, viste e sequenze con privilegi di accesso


 

Comandi SQL si scrivono normali


 

Ereditarietà

Potrei ereditare una tabella da un'altra (lo fa solo Postgre, però ammazza la portabilità): CREATE TABLE (campi diversi) INHERITS TabellaBase;

Esempio Capitali eredità Città. Inserendo una capitale automaticamente sarà presente anche la città corrispondente automaticamente (ed eliminandola viene eliminata anche dalla classe base, sembra scontato, ma non lo è!)!


 


 


 

IN PRATICA

Connessione dal client:

psql TemplateName [-h host] [-U utente]


 

Appena lanciato lo script di inizializzazione crea 2 database: Template0 e Template1

Ogni DB in PostgreSQL viene creato a partire da un Template (che può essere personalizzato aggiungendo delle tabelle di uso comune ad esempio)

Crea postgresql.conf -> Configurazione generale del cluster. pg_hba.conf -> gestione della politica degli accessi (funziona in maniera simile alla configurazione di un firewall)


 

(fare partire il server)

Script di creazione degli utenti (createuser)

Script di creazione di un database (createdb)


 

Manipolare una sequenza:

Nextval(sequenceName)

Currval(sequenceName)

Setval(sequenceName, value[, bool]) Il valore bool indica se il valore inserito influenza nextval


 

Manipolare le connessioni

Editando il file pg_hba.conf

Ho le connessioni di tipo: local (locali), host (via rete), hostssl (via rete, ma solo con SLL), hostnossl

Sono definite a livello di database e utente (posso definirle in generale per un utente e/o DB)

Posso anche ulteriormente filtrare per un Ip e una mask


 

Dump/Restore di un DB

Pg_dump [opzioni] nomeDB > filename

Pg_dumpall consente il dump dell'intero cluster

Restore: Psql nomeDB < filename


 

Basi di dati: Progettazione di schemi E/R

Approcci:

top-down: Parto da un concetto generale e lo sviluppo verso uno più particolare (es.: base di dati per l'università -> entità prof. e studenti in relazione -> entità studenti, corsi e prof. In relazione, ecc.), evolvo le specializzazioni gerarchiche e infine metto gli attributi.

bottom-up: opposto di top-down. Parto dagli attribute e li raggruppo in entità, pio creo le relazioni tra le entità… Lo schema NON evolve, ma si completa a differenza di top-down.

inside-out: Caso specific di bottom-up. Si allarga a macchia d'olio: parto da un particolare ed espando da quel punto. Personalmente mi sembra la più logica.

mixed: Appliazione mista delle 3 precedenti…

Di solito si usano mixed o inside-out.

Caratteristiche:

Leggibilità: uno schema é completato solo quando può essere definito leggibile. Evitare o minimizzare le intersezioni di linee (ad esempio delle relazioni). Scegliere nomi significativi.

Minimalità: Lo schema non dovrebbe contenere informazioni ridondanti o calcolabili. Se per ragioni di prestazioni o altro si decide di lasciare un dato derivato va segnalato!

Completezza: Uno schema deve contenere tutti i requisiti dei dati desiderati

Correttezza: evitare errori sintattici (violare le regole dei modelli E/R), oppure semantici (sbagliare l'uso di elementi (es: esprimere un'associazione con un'entità))


Come agire: selezionare dal testo in linguaggio naturale le parole chiave (diventeranno entità o attributi). Se in una frase compare più di un antità valutare dei legami (associazioni o generalizzazioni).

Basi di dati: MySQL

Estensioni allo standard SQL 99:

  • REPLACE (DELETE + INSERT)
  • Tipi di dato: medium int, set, enum, text, varchar
  • Vincoli di attributo: AUTO_INCREMENT, BINARY, NULL, UNSIGNED, ZEROFILL
  • Supporto a espressioni regolari e altro nelle SELECT
  • || e && come alternativa a OR e AND

Mancanze:

  • INTERSECT
  • EXCEPT
  • E' necessario prima rimuove i permessi ad un tabella prima di cancellarla (REVOKE)
  • Vincoli di integrità SOLO con InnoDB

Le componenti di MySQL (per mia esperienza simili agli altri db):

Syntax parser: parser delle query
Query optimizer: ottimizzatore
Execution Component: Esecutore delle query
Storage manager: Gestisce l'IO
Recovery manager: gestisce la parte volatile (risultato di un'interrogazione i.e.)
Process manager: gestione Thread e connessioni di rete

Funzioni di librerie

Default porta 3306

DB di sistema:

mysql (informazioni del DB, tipo utenti e permessi)
information_schema: database del catalog (informazioni su database, tabelle, viste, ecc.)

Username administrator: root, nessuna password di default (da localhost)

Case INSENSITIVE. Comandi terminati da ;

Connessione al server: Mysql –u root –p

Comando SHOW (DATABASES / TABLES)

Comando DESCRIBE TABLE

Domando: USE databaseName

PRIVILEGI

memorizzati in mysql.users
Privilegi globali: *.*
A livello di database: dbname.*
A livello di tabella: dbname.tableName

Creando un privilegio creo un utente

Posso usare wildcard

SET PASSWORD nomeutente@nomehost = PASSWORD('NewPassword')

Creare un utente: GRANT
Privilege
ON
NomeDatabase.* TO
Utente@PuntoDiAccesso
IDENTIFIED BY
Password

MySQL differenzia i privilegi a seconda del computer di provenienza della richiesta: Utente@% indica l'utente da qualsiasi punto di accesso.

Contenuti gestiti da Michele Bernardi. Non mi assumo alcuna responsabilità sulla correttezza di questi dati, usateli a vostro rischio e pericolo.

Basi di dati: Raccolta e Analisi dei Requisiti

Raccolta dei requisiti:

usare quanto esiste: moduli, precedenti versioni

intervistare gli utenti principali

Raccolta documentazione

Identificazione di utenti e aree

Ordinamento dei requisiti:

Saranno informali (in linguaggio naturale), vanno organizzati

Costruire un glossario dei termini (risolvere omonimi (cambiare nome se due cose sono chiamate con lo stesso nome) e sinonimi (scegliere un solo modo per chiamare un entità/attributo))

Basi di dati: Modello Entity - Relationship

Tre principi fondamentali: Entity, Relationship, Attributo

Entity

Tipo entità: rappresenta una classe.
Ha un nome e si rappresenta con un rettangolo


 

Relationship (associazione)

Relazione tra due tipi di identità
Si rappresenta come un rombo con nome e due linee


Ha un grado che equivale al numero di entità coinvolte (binarie di solito)

Due entità possono essere connesse da svariate associazioni

Associazioni binarie possono essere "ricorsive" ("ad anello"): legano un'entità a se stessa, in tal caso specifico i ruoli (etichette sulle braccia dell'associazione):

Attenzione si tratta comunque di una relazione BINARIA (impiegato nell'esempio ha valenza doppia)

Attributo

E' una caratteristica di un entity... Ha dei valori

Si indica con in pallino collegato:


E' univoco per entità o relazione.


 

Altri 5 principi "secondari":

Cardinalità

Ovvero la coppia di numeri che legano un'entità ad relazione (cardinalità minima mc e massima MC):


Ovviamente da ogni lato della relazione è specificata una coppia di mc/MC (vedi più avanti)
Per indicare che non c'é limite metto n
Se le cardinalità massime sono 1 e 1 si chiama associazione "uno a uno". Se uno a più di uno allora sarà "uno a molti", se più di uno a più di uno: "molti a molti"

Cardinalità degli attributi:

Se la cardinalità é omessa si intende (1, 1)
Attenzione il modello relazionale non consente più di un valore (a meno di splittare in tabelle diverse)
Nel modello relazionale la cardinalità degli attributi può essere solo (0,1) o (1,1) perché deve osservare la prima forma normale

Identificatori

Sono l'equivalente del concetto di chiave primaria.
Permette di identificare l'entità E.
Negli schemi vengono rappresentati come un attributo con un pallino "pieno" (a differenza degli attributi normali che hanno un pallino "vuoto"

Gli identificatori possono essere semplici (composti da un solo attributo) o composti


Identificatori interni hanno solo attributi interni, esterni hanno anche degli attributi esterni (nel modello relazionale hanno delle foreign key nella propria chiave primaria). Da notare che se l'entità E1 ha l'entità E2 come parte del proprio identificatore devono essere unite da una relazione e dalla parte di E1 la cardinalità di tale relazione deve essere (1,1)

Se l'identificatore é esterno le entità sono dette deboli (espresse con pallino bianco e riferimento in identificatore:

Attributi composti

Attributi complessi costruiti da più "sottoattributi" vengono rappresentati all'interno di un piccolo ovale collegato all'entità (vedi schema dell'attributo qui sopra)

Gerarchia (=ereditarietà)

Rappresentano i legami tra tipi di entità diversi per indicare legami di sottoclasse/superclasse
Si esprimono con una freccia dalla sottoclasse alla superclasse:

Ovviamente esiste il concetto di ereditarietà anche in questi schemi ER, le proprietà ereditate sono: attributi identificatori associazioni

Le gerarchie possono essere trovate in due modi:
Generalizzazione: generalizzazione di un certo numero di sottoclassi in una superclasse comune (partendo dalle sottoclassi cane, gatto, ecc. generalizzo la superclasse animale)
Specializzazione: specializza la superclasse in sottoclasse (partendo dalla superclasse pane specializzo in "pane all'olio", "pane alle olive", ecc.)

Le gerarchie sono sottoposte a vincoli di totalità e disgiunzione.
Una gerarchia é parziale (P) se é possibile avere istanze del supertipo (che non non siano del sottotipo) = istanziabile, altrimenti é totale (T)
Disgiunzione: se un'istanza può essere può appartenere ad più di una sottoclasse é sovrapposta (O = overlapped) altrimenti é disgiunta (E)

Di default é totale esclusiva (TE)

Descrizione

Possono essere specificati dei vincoli o degli altri commenti
Per maggior chiarezza:
http://blacklight.gotdns.org/wiki/index.php/Introduzione_ai_database_e_modellazione_E/R

Contenuti gestiti da Michele Bernardi. Non mi assumo alcuna responsabilità sulla correttezza di questi dati, usateli a vostro rischio e pericolo.

Basi di dati: Progettazione concettuale di una base dati

3 livelli di progettazione:

concettuale (Entity / Relationship): indipendente dal DBMS che verrà scelta. Produce schema concettuale
logica: dipendente dal modello logico scelto (es. Rdbms per relazionale). Produce uno schema logico
fisica: dipendente dal motore di DB scelto (ad esempio se avessi scelto MySQL ci sono caratteristiche diverse da Oracle. Poduce la base di dati vera e propria

  1. Raccolta e analisi dei requisiti -> produce un documento in linguaggio naturale

Modelli concettuali:

Utilizzati nella fase di progettazione, di solito sono grafici.

Il più famoso é Entity-Relationship

Si parte astraendo dalla realtà le caratteristiche
interessate dei dati che sappiamo di dover raccogliere.
Es. se voglio descrivere "studente" sceglierò caratteristiche come anno e matricola e trascurerò altre caratteristiche che, seppure proprie di ogni studente non importano alla figura di studente stesso (es. colore degli occhi).

I tre metodi di astrazione più usati sono:

Classificazione

Definire un concetto come classe degli oggetti che voglio descrivere caratterizzati da proprietà comuni.
Trovo le proprietà comuni tra le informazioni che devo classificare.
Es.= Presi 3 elementi (es. VW Golf, Fiat punto e Audi A3) troviamo le caratteristiche comuni per definire la classe a cui appartengono (es. automobile)

Relazione: is member of

Aggregazione

Vedo le parti (già classi, non caratteristiche!) che compongono gli oggetti che voglio descrivere e aggregando ottengo la classe che le contiene tutte.
Es.: Prendo carrozzeria, motore sono 3 classi con le proprie proprietà e le aggrego nell'unica classe automobile.

Relazione: Part of

Generalizzazione

Ricerca delle proprietà comuni per definire una forma di ereditarietà tra un sottoinsieme di informazioni (base) e un sovrainsieme (derivate)
Es. Prese le classi uomo e donna generalizzo le proprietà comuni nella superclasse individuo e lascio nelle sottoclassi uomo e donna solo quelle specifiche.
Es2. Presa la classe dipendente specifico (che è il contrario di generalizzo!) nelle sottoclassi programmatore e sistemista

Relazione: is a

Basi di dati: SQL: Trigger

Il trigger é un elemento dello schema che corrisponde ad una reazione attiva ad una azione sul database

Le Dasi di dati che comprendono i trigger sono dette ATTIVE

Evento-Condizione-Azione

Gli eventi possono essere scatenati da statement di aggiornamento della base dati (UPDATE, INSERT, DELETE)

Le condizioni sono delle "riduzioni" agli eventi

Le azioni sono statement SQL oppure statement tipici della BD


Un trigger risponde ad eventi su una sola tabella

SINTASSI ORACLE

CREATE TRIGGER
TriggerName
BEFORE/AFTER
INSERT/UPDATE/DELETE
ON [CampoCambiato
OF] TabellaCambiata

[FOR EACH ROW]

[WHEN (Condizione)]

BEGIN

[NEW] [OLD]…

END


NEW è una parola chiave: corrisponde alla nuova tupla, OLD alla vecchia

Nell'esempio Viene modificato il valore di Campo1 all'occorrenza del TRIGGER: … BEGIN NEW.Campo1 = Valore
END

Granularità

FOR EACH ROW = Granularità di tupla

(default ovvero per statement) = Granularità di statement SQL (1 evento per ogni statement sql anche se aggiorna più record)

Velocità di valutazione del trigger

Immediata: BEFORE o AFTER

Il TRIGGER viene eseguito nella stessa transazione della modifica che l'ha scatenato


Differita (non trovo corrispondenze in Oracle): valutata dopo la chiusura della transazione che lo solleva

Detached (non trovo corrispondenze in Oracle): gestione in una transazione separata


Inoltre c'é la possibilità che il trigger non venga chiamato immediatamente, ma in un momento spearato rispetto alla valutazione della condizione che lo solleva

Un TRIGGER potrebbe avere una doppia attivazione: es. "AFTER INSERT, UPDATE)


Quando usare un TRIGGER:

  • Specificare vincoli di integrità complessi
  • Gestione di dati derivati
  • Mantenimento di consistenza delle viste materializzate


Qui c'é la guida ufficiale alla CREATE TRIGGER di ORACLE!

Qui Wikipedia Italia tratta il PL/SQL (linguaggio per scrivere le azioni dei trigger)

Basi di dati: SQL, Viste

Sono delle "tabelle virtuali" con un nome

Non é memorizzata: é una selezione di una tabella

Posso creare delle "viste materializzate": ovvero copio delle viste in una tabella, ma poi devo gestirne l'allineamento

CREATE VIEW

CREATE VIEW
NomeVista (Campo1, Campo2) AS SELECT ... [WITH [LOCAL|CASCADED] CHECK OPTION]

Può servire anche per limitare l'accesso a certi dati da parte di certi utenti (permessi solo sulla vista e non sulla tabella)

Una View può accedere ad un'altra View in cascata

Se non metto l'elenco degli attributi verrà usato il nome posizionale degli attributi della SELECT

AGGIORNABILITA':

In View con JOIN potrei avere Update problematici

In genere posso aggiornare solo viste definite su una sola tabella (e dovrebbe essere specificata anche la chiave primaria)

Alcune implementazioni di Database potrebbero essere più permissivi

La CHECK OPTION:

Se specificata posso aggiornare la vista solo se le righe aggiornate faranno ancora parte della vista

LOCAL: Controlla solo per la vista attuale

CASCADED (default): Controlla anche per le eventuali viste che compongono la vista corrente

Basi di dati: SQL: INSERT, UPDATE, DELETE

INSERT INTO

INSERT INTO
Tabella (Campo1, Campo2) VALUES (Valore1, Valore2)

Per specificare valori stringa devo metterli tra apici. Se voglio inserire un apice lo metto doppio: 'un po'' di fortuna'

Oppure posso usare il risultato di una SELECT:

INSERT INTO
Tabella (Campo1, Campo2) SELECT
Campo3, Campo4
FROM
Tabella2

Attenzione: l'inserimento é posizionale non per nome attributo

UPDATE

UPDATE
Tabella
SET
Campo = Valore [WHERE
Condizione]

Come argomento del SET posso anche mettere un'espressione. Ad esempio Campo = Campo + 1

Analogamente posso mettere una SELECT:

UPDATE
Tabella
SET
Campo = (SELECT .... WHERE
Condizione)

Posso anche settare al valore di default:

UPDATE
Tabella
SET
Campo = DEFAULT
WHERE
Condizione

DELETE

DELETE FROM Tabella WHERE Condizione

Attenzione cancella un record non un dato!

Basi di dati: SQL: Query annidate (e implementazione della divisione)

ANY

Usata per mettere in clausola WHERE il risultato di un'altra query o di un elenco.

Es.= SELECT
Campo1
FROM
Tabella1
WHERE
Tabella1.CampoX = ANY (SELECT ……..)

Es.= SELECT
Campo1
FROM
Tabella1
WHERE
Tabella1.CampoX = ANY (valore1, valore2)

IN

Stesso significato diversa sintassi

Es.= SELECT
Campo1
FROM
Tabella1
WHERE
Tabella1.Campo2
IN (SELECT ……..)


SUBQUERY CORRELATE

Mediante alias di tabelle/query posso passare parametri all'interno/esterno delle subquery

EXISTS

True se la subquery ritorna almeno 1 valore

NOT EXISTS

Opposto di EXISTS


Implementazione della DIVISIONE

Regola del controesempio: doppia negazione con NOT EXISTS


Es:

Gli impiegati che partecipano a tutti i progetti con budget > 50k

=

Tutti gli impiegati esclusi (quelli a cui manca almeno un progetto > 50k)

=

Tutti gli impiegati esclusi (gli impiegati a cui manca la partecipazione (ad un progetto da 50k))


Soluzione prof:

SELECT ID FROM Impiegato I WHERE NOT EXISTS

(

SELECT * FROM Progetto Y WHERE Budget > 50000 AND NOT EXISTS

(

SELECT * FROM Partecipazione P WHERE I.ID = P.ID AND Y.Prog# = P.Prog#

)

)

Basi di dati: SQL: operazioni insiemistiche

UNION

DEFAULT = Elimina i duplicati

ALL = Mantiene i duplicati

Es.:

SELECT Campo1 FROM Tabella1 UNION SELECT Campo2 FROM Tabella2

Se avessi messo "UNION ALL" avrebbe preso anche eventuali duplicati

INTERSECT

= Intersezione

Ritorna solo i record comuni alle 2 tabelle

EXCEPT

= Differenza

Except ritorna solo i record presenti nella prima tabella, ma non nella seconda

Basi di Dati: SQL, SELECT

L'istruzione per leggere dati da delle tabelle é. SELECT ed ha la seguente sintassi:

SELECT [DISTINCT] Campo1 [AS
AliasCampo1], Campo2 [AS
AliasCampo2] FROM
Tabella1 AliasTabella1, Tabella2
AliasTabella2 [WHERE Condizioni] [GROUP BY
Campo1, Campo2, … [HAVING
CondizioneAggregata]] [ORDER BY Campo1 [DESC], Campo2 [DESC, …]

DISTINCT Specifica che non vanno prese le ennuple duplicate nel risultato

Nella clausola FROM é possibile anche specificare delle clausole di JOIN:

[INNER] JOIN (corrisponde a Teta JOIN

Es: FROM Tabella1 INNER JOIN Tabella2 ON Tabella1.Campo1 = Tabella2.Campo2

NATURAL JOIN

Es: FROM Tabella1 NATURAL JOIN Tabella2

RIGHT, LEFT, [FULL] OUTER JOIN: Join esterni


OPERATORI AGGREGATI

COUNT: Numero di elementi

SELECT COUNT (*) FROM -> numero di record

SELECT COUNT (DISTINCT Campo1) FROM -> numero di valori diversi assunto da Campo1


SUM: Somma di valori (es. SELECT SUM(Campo1) FROM)

MAX: Valore più alto

MIN: Valore più basso

AVG: Valore medio


GROUP BY

Raggruppa i record secondo certi valori. I campi aggregati verranno calcolati per ciascuno di quei valori.
L'esempio seguente estrae lo stipendio più basso per ogni citta:

SELECT MIN(Stipendio) FROM
Dipendenti
GROUP BY
Citta

Se uso una GROUP BY nella select posso mettere solo gli attributi del GROUP BY e le aggregazioni. Es:
Sì: SELECT SELECT Citta, MIN(Stipendio) FROM
Dipendenti
GROUP BY
Citta

No: SELECT SELECT Nome, MIN(Stipendio) FROM
Dipendenti
GROUP BY
Citta


WHERE

Esprime una condizione della query


HAVING

Permette di esprimere condizioni sui valori aggregati. Praticamente é come una WHERE effettuata dopo il raggruppamento

Es.: La query seguente filtra i dirigenti, li raggruppa per dipartimento, prende la somma degli stipenti dei dirigenti per dipartimento e ritorna solo i record la cui somma degli stipenti é maggiore di 10000

SELECT Dipartimento, SUM(Stipendio) AS Totale FROM Dipendenti WHERE Grado = 'Dirigente' HAVING SUM(Stipendio) > 10000


ORDER BY

Ordina per uno o più campi il risultato.

Si può specificare le parole chiave ASC (default) per ordine crescente oppure DESC per descrescente.

Se si specificano più campi (separati da virgole) l'ordine sarà valutato da sinistra a destra (esattamente come ci si aspetterebbe!)

Basi di dati: SQL: DDL

Domini Elementari:

TIPI NUMERICI:

Esatti: integer, smallint, numeric, decimal (virgola fissa)
Approssimati (virgola mobile): real, double precision, float

STRINGA:

char (numero fisso di caratteri), varchar (numero variabile di caratteri). Si può associare la lunghezza massima

Bit: strighe composte di 0 o 1
Boolean: memorizza true o false

DATE:

Date: Data (passata in formato yyyyMMdd
Time: Ora, passata in formato hh:mm:ss
Timestamp: Date + Time

OGGETTI GRANDI:

Blob, Clob


 

TRE FAMIGLIE DI COMANDI: CREATE (creazione), DROP (cancellazione), ALTER (modifica)

CREATE

CREATE SCHEMA (implementato spesso come CREATE DATABASE)

CREATESCHEMA NomeSchema AUTHORIZATION Owner {ElementoSchema}

CREATE TABLE

CREATE TABLE
NomeTabella (
NomeAttributo
Dominio [DEFAULT
ValoreDefault] [Vincoli],
NomeAttributo
Dominio [DEFAULT
ValoreDefault] [Vincoli],
…)

Dominio può essere composto anche da tipo(dimensione). Es.: char(20).

Vincoli:
UNIQUE: Non possono esserci duplicati (ma potrebbero esserci nulli)
NOT NULL: Valori nulli non ammessi
PRIMARY KEY: Se metto questo vincolo su più campi faranno tutti parte dell'unica primary key

Per mettere un vincolo su un maggior numero di attributi posso metterli alla fine (es. primary con 2 campi):
CREATE TABLE
MyTable (campo1 tipo1, campo2 tipo2,
PRIMARY KEY (campo1, campo2))

VINCOLI DI INTEGRITÀ (INTRA-RELAZIONALI)

Posso applicare un vincolo "custom" mettendo dopo l'elenco dei campi (come per primary key qui sopra):
[CONSTRAINT
ConstraintName] CHECK condizione
Es: CONSTRAINT MioVincolo CHECK campo IN ('value1', 'value2')
Es2: CONSTRAINT MioVincolo2 CHECK campo2 > 10

I vincoli CONSTRAINT sono rigorosamente INTRArelazionali. Se si desidera specificare un vincolo CHECK interrelazionale è necessario creare un ASSERTION (vedi più avanti). Per quanto ne so io le ASSERTION comunque non sono supportate dalla maggior parte dei motori relazionali, al massimo creare un trigger.

E' possibile specificare altri vincoli mediante CONSTRAINT, come ad esempio il vincolo UNIQUE.
Nell'esempio seguente non sono Campo1 e Campo2 a dover essere UNIQUE, ma la loro combinazione:
[CONSTRAINT
MyConstraint] UNIQUE (Campo1, Campo2)

Perché specificare la parte opzionale "CONSTRAINT
ConstraintName"? Perché successivamente sarà possibile fare un ALTER o un DROP riferendosi a tale nome.

VINCOLI INTER-RELAZIONALI

FOREIGN KEY: Integrità referenziale

Definito tra due tabelle: tabella che riferisce (es.Indirizzo, con IDPersona) e riferita (es. Persona con campo ID)
La tabella riferita dovrebbe avere sui propri attributi il vincolo UNIQUE

[CONSTRAINT
ConstraintName] [FOREIGN KEY
MioCampo] REFERENCES altroCampo(altraTabella))
Se lo appongo dopo la dichiarazione di un campo basta REFERENCES (es: mioCampo mioTipo REFERENCES altroCampo(altraTabella))
Se la chiave è multi-valore sono costretto a metterlo alla fine della tabella: FOREIGN KEY (campi) REFERENCES tabella (altricampi)

Posso specificare delle azioni tipo:
REFERENCES altroCampo(altraTabella) [ON DELETE/UPDATE] CASCADE/SET NULL/SET DEFAULT/NO ACTION

ON DELETE: alla cancellazione del record nella tabella principale cancello anche i record riferiti
ON UPDATE: all'aggiornamento del campo primary key della tabella riferita
    CASCADE: Il cambio della chiave primaria cambia anche le chiavi esterne (o cancello)
    SET NULL: Se cancello o cambio imposta a NULL le foreign keys
    SET DEFAULT: Imposto le foreign key al valore di default
    NO ACTION: (default) Rifiuto il cambiamento/cancellazione

La parte ON DELETE/UPDATE è opzionale: se tralasciata si intendono entrambe.

CREATE ASSERTION

Del tutti analogo a mettere un CONSTRAINT all'interno di una CREATE TABLE, ma con la possibilità di specificare vincoli inter-relazionali:
CREATE ASSERTION
AssertionName
Es: CREATE ASSERTION
MyAssertion
CHECK
Tab1.Campo1 > SELECT MAX(Campo2) FROM Tab2

CREATE DOMAIN

Permette di creare un dominio partendo da uno esistente
CREATE DOMAIN
NewDomainName
AS
tipoBase [DEFAULT
valore1] [CHECK (condizione)]

Potrei creare un dominio intero compreso tra 10 e 100 con valore di default 20:
CREATE DOMAIN
MyDomain
AS int DEFAULT
20
CHECK (VALUE >= 10 AND VALUE <= 100)

ALTER

ALTER TABLE
MyTable
ALTER COLUMN MyColumn
CAMBIAMENTO

Esempi di possibili cambiamenti:
tipo di dati di una colonna ALTER TABLE
MyTable
ALTER COLUMN MyColumn NewType
default di una colonna ALTER TABLE
MyTable
ALTER COLUMN MyColumn
SET DEFAULT
NuovoDefault
eliminazione valore di default: ALTER TABLE
MyTable
ALTER COLUMN MyColumn
DROP DEFAULT
aggiunta/rimozione di una colonna: … ADD/DROP COLUMN ColumnName (se ADD specificare tipo, eventuali vincoli, ecc.)
aggiunta/rimozione di un vincolo (CON NOME!) ADD/DROP CONSTRANT
ConstraintName (se ADD definizone del vincolo)

DROP

DROP SCHEMA (DROP DATABASE) DatabaseName
DROP TABLE
TableName
DROP DOMAIN
DomainName
DROP VIEW
ViewName
DROP ASSERTION
AssertionName
DROP CONSTRAINT
AssertionName

Con opzioni:
RESTRICT (default): Se ci sono oggetti che ne dipendono (es una vista sulla tabella che voglio cancellare) NON li cancella
CASCADE: Cancella gli oggetti che ne dipendono

CATALOG

Le informazioni sugli oggetti di un database vengono raccolte all'interno di una BD relazionale detta CATALOG (riflessività)
Maggiori informazione con la definizione di trigger e viste