SQL (Structured Query Language) è un linguaggio di programmazione ideato per gestire e interrogare basi di dati. Questo libro svela gli errori più comuni e i trabocchetti in cui si imbattono gli sviluppatori di software alle prese con i database, spesso considerati a torto come una delle principali fonti di problemi delle applicazioni.
Grazie ai suggerimenti pratici e alle tecniche presentate in queste pagine, il lettore avrà in mano gli strumenti per ottenere dai database risultati più efficaci e per trasformare in soluzioni ottimali anche le strutture più complesse.
Strutturato in maniera da rispondere alle esigenze sia dei programmatori più esperti sia di quelli alle prime armi, il libro insegna a progettare e costruire un database, a scrivere query sempre più efficaci e a integrare al meglio il linguaggio SQL nel processo di programmazione delle applicazioni. Infine, non mancano indicazioni sulla progettazione di un codice sicuro e resistente agli attacchi esterni.
Argomenti in breve:
L'autore
Bill Karwin ha lavorato per oltre vent'anni come ingegnere del software, sviluppando applicazioni, librerie e server per progetti come Zend Framework, InterBase e Enhydra Java application server. Nel corso della sua carriera ha aiutato moltissimi programmatori a raggiungere il massimo del successo e della produttività: grazie a questa attività ha raggiunto una competenza unica nell'affrontare gli errori più comuni nell'utilizzo di SQL e nel consigliare la maniera più efficace per risolvere i problemi.
Indice generale
Nota dell’editore .....................................................................................xiii
Capitolo 1 Introduzione.......................................................................1
1.1 A chi si rivolge questo libro ..................................................................... 2
1.2 Contenuto del libro ................................................................................ 3
Struttura del libro .................................................................................... 3
Anatomia di un antipattern ..................................................................... 4
1.3 Che cosa non c’è in questo libro ............................................................. 4
1.4 Convenzioni adottate .............................................................................. 5
Tipografi a ............................................................................................... 5
Terminologia .......................................................................................... 5
Diagrammi entità-relazioni ...................................................................... 5
1.5 Database di esempio ................................................................................ 7
1.6 Ringraziamenti ....................................................................................... 9
Parte I Antipattern nella progettazione logica di database 11
Capitolo 2 Jaywalking ........................................................................13
2.1 Obiettivo: memorizzare attributi a più valori ......................................... 14
2.2 Antipattern: formato di elenchi separati da virgole ................................. 14
Ricerca dei prodotti di un account specifi co .......................................... 15
Ricerca degli account per un dato prodotto ........................................... 15
Query aggregate ................................................................................... 15
Aggiornamento di account per un prodotto specifi co ............................ 16
Validazione di ID di prodotto ................................................................ 17
Scelta di un carattere separatore ............................................................. 17
Limitazioni alla lunghezza degli elenchi ................................................. 17
2.3 Come riconoscere l’antipattern ............................................................. 17
2.4 Impieghi legittimi dell’antipattern ......................................................... 18
2.5 Soluzione: creare una tabella di riferimento incrociato ........................... 18
Ricerca di prodotti per account e viceversa ........................................... 19
Creare query aggregate ......................................................................... 19
Aggiornamento dei contatti per uno specifi co prodotto ......................... 20
Validazione di ID di prodotto ................................................................ 20
Scelta di un carattere separatore ............................................................. 21
Limitazioni alla lunghezza dell’elenco .................................................... 21
Altri vantaggi della tabella di riferimento incrociato............................... 21
Capitolo 3 Alberi naif .........................................................................23
3.1 Obiettivo: memorizzare e recuperare gerarchie ...................................... 24
3.2 Antipattern: dipende sempre dal padre ................................................... 24
Interrogazione di un albero con lista di adiacenza .................................. 26
Operazioni su un albero con lista di adiacenza ....................................... 27
3.3 Come riconoscere l’antipattern ............................................................. 28
3.4 Impieghi legittimi dell’antipattern ......................................................... 28
3.5 Soluzione: utilizzare modelli ad albero alternativi ................................... 30
Enumerazione di percorsi ...................................................................... 30
Insiemi annidati .................................................................................... 32
Tabella di chiusura ................................................................................ 35
Quale modello scegliere? ....................................................................... 39
Capitolo 4 ID richiesto .......................................................................41
4.1 Obiettivo: convenzioni per la chiave primaria ........................................ 42
4.2 Antipattern: una taglia universale ........................................................... 43
Creare una chiave ridondante ................................................................ 44
Consentire righe duplicate .................................................................... 44
Nascondere il signifi cato della chiave ..................................................... 45
USING ................................................................................................. 46
Le chiavi composte sono diffi cili ........................................................... 46
4.3 Come riconoscere l’antipattern ............................................................. 47
4.4 Impieghi legittimi dell’antipattern ......................................................... 48
4.5 Soluzione: lavorare su misura ................................................................. 48
Chiamare le cose con il proprio nome ................................................... 48
Superare le convenzioni ........................................................................ 49
Chiavi naturali e chiavi composte .......................................................... 49
Capitolo 5 Inserimento senza chiavi .................................................51
5.1 Obiettivo: semplifi care l’architettura del database ................................... 52
5.2 Antipattern: omettere i vincoli .............................................................. 52
Ipotizzare un codice senza errori ........................................................... 52
Controllare gli errori ............................................................................. 53
“Non è colpa mia!” ............................................................................... 54
Aggiornamenti da Comma 22 ............................................................... 54
5.3 Come riconoscere l’antipattern ............................................................. 55
5.4 Impieghi legittimi dell’antipattern ......................................................... 55
5.5 Soluzione: dichiarare i vincoli ................................................................ 56
Supporto per le modifi che multitabella .................................................. 56
Sovraccarico? Non esattamente ............................................................. 57
Capitolo 6 Entità-Attributo-Valore ...................................................59
6.1 Obiettivo: supportare gli attributi variabili ............................................. 60
6.2 Antipattern: usare una tabella di attributi generica.................................. 60
Query di un attributo ........................................................................... 62
Supportare l’integrità dei dati ................................................................ 62
Ricostruire una riga .............................................................................. 65
6.3 Come riconoscere l’antipattern ............................................................. 65
Indice generale vii
6.4 Impieghi legittimi dell’antipattern ......................................................... 66
6.5 Soluzione: modellare i sottotipi.............................................................. 67
Ereditarietà a tabella singola .................................................................. 67
Ereditarietà a tabella concreta ................................................................ 68
Ereditarietà a tabella di classe ................................................................. 69
Dati semistrutturati ............................................................................... 70
Post-elaborazione .................................................................................. 71
Capitolo 7 Associazioni polimorfe ....................................................73
7.1 Obiettivo: referenziare più padri ............................................................ 74
7.2 Antipattern: usare chiavi esterne a doppio scopo .................................... 74
Defi nire un’associazione polimorfa ........................................................ 75
Query su un’associazione polimorfa ...................................................... 75
Esempio non orientato agli oggetti ........................................................ 77
7.3 Come riconoscere l’antipattern ............................................................. 78
7.4 Impieghi legittimi dell’antipattern ......................................................... 78
7.5 Soluzione: semplifi care la relazione ........................................................ 79
Invertire il riferimento .......................................................................... 79
Creare una supertabella comune ............................................................ 82
Capitolo 8 Attributi multicolonna ....................................................85
8.1 Obiettivo: memorizzare attributi a più valori ......................................... 85
8.2 Antipattern: creare colonne multiple ...................................................... 86
Ricerca di valori ................................................................................... 87
Aggiunta e rimozione di valori.............................................................. 87
Garantire l’unicità ................................................................................. 88
Gestione di insiemi di valori crescenti ................................................... 89
8.3 Come riconoscere l’antipattern ............................................................. 89
8.4 Impieghi legittimi dell’antipattern ......................................................... 90
8.5 Soluzione: creare una tabella dipendente ................................................ 91
Capitolo 9 Metadati e tribble ............................................................93
9.1 Obiettivo: supportare la scalabilità .......................................................... 94
9.2 Antipattern: clonare tabelle o colonne ................................................... 94
Suddividere le tabelle ............................................................................ 95
Gestire l’integrità dei dati ...................................................................... 95
Sincronizzare i dati ................................................................................ 96
Garantire l’unicità ................................................................................. 97
Query su diverse tabelle ........................................................................ 97
Sincronizzare i metadati ........................................................................ 97
Gestire l’integrità referenziale ................................................................ 98
Identifi care le colonne con i metadati che si propagano ......................... 98
9.3 Come riconoscere l’antipattern ............................................................. 99
9.4 Impieghi legittimi dell’antipattern ......................................................... 99
9.5 Soluzione: partizione e normalizzazione .............................................. 100
Partizionamento orizzontale ................................................................ 100
Partizionamento verticale .................................................................... 101
Un rimedio per le colonne di metadati che si propagano ..................... 102
Parte II Antipattern nella progettazione fi sica di database 103
Capitolo 10 Errori di arrotondamento ............................................. 105
10.1 Obiettivo: utilizzare numeri frazionari invece degli interi ..................... 106
10.2 Antipattern: utilizzare il tipo di dati FLOAT ........................................ 106
Arrotondare per necessità .................................................................... 106
Utilizzare FLOAT in SQL ................................................................... 107
10.3 Come riconoscere l’antipattern ........................................................... 109
10.4 Impieghi legittimi dell’antipattern ....................................................... 110
10.5 Soluzione: utilizzare il tipo di dati NUMERIC ................................... 110
Capitolo 11 31 gusti .......................................................................... 113
11.1 Obiettivo: fi ssare l’insieme dei valori inseribili in una colonna .............. 114
11.2 Antipattern: specifi care i valori nella defi nizione della colonna ............. 114
Qual era il dato centrale? ..................................................................... 115
Aggiungere un nuovo gusto ................................................................ 116
I vecchi gusti non passano mai di moda ............................................... 117
La portabilità è diffi cile ....................................................................... 117
11.3 Come riconoscere l’antipattern ........................................................... 117
11.4 Impieghi legittimi dell’antipattern ....................................................... 118
11.5 Soluzione: specifi care i valori nei dati .................................................. 118
Query sull’insieme di valori inseribili .................................................. 119
La portabilità è facile ........................................................................... 120
Capitolo 12 File fantasma ................................................................. 121
12.1 Obiettivo: memorizzare immagini o altri media ................................... 122
12.2 Antipattern: supporre di dover utilizzare fi le ........................................ 122
I fi le non obbediscono a DELETE ...................................................... 123
I fi le non obbediscono all’isolamento della transazione ........................ 123
I fi le non obbediscono al rollback ........................................................ 124
I fi le non obbediscono agli strumenti per il backup del database .......... 124
I fi le non obbediscono ai privilegi di accesso SQL ............................... 125
I fi le non sono tipi di dati SQL ........................................................... 125
12.3 Come riconoscere l’antipattern ........................................................... 125
12.4 Impieghi legittimi dell’antipattern ....................................................... 126
12.5 Soluzione: utilizzare tipi di dati BLOB quando servono ....................... 127
Capitolo 13 Indici dappertutto ........................................................ 129
13.1 Obiettivo: ottimizzare le prestazioni ..................................................... 130
13.2 Antipattern: usare gli indici senza un piano .......................................... 130
Assenza di indici ................................................................................. 130
Troppi indici ....................................................................................... 131
Quando nessun indice può aiutare ....................................................... 132
13.3 Come riconoscere l’antipattern ........................................................... 133
13.4 Impieghi legittimi dell’antipattern ....................................................... 134
13.5 Soluzione: MENTOR ........................................................................ 135
Misurare ............................................................................................. 135
Spiegare .............................................................................................. 136
Dare il nome ....................................................................................... 137
Test ..................................................................................................... 138
Ottimizzare ......................................................................................... 138
Ricostruire ......................................................................................... 139
Parte III Antipattern relativi alle query 141
Capitolo 14 Paura dell’ignoto........................................................... 143
14.1 Obiettivo: distinguere i valori mancanti ............................................... 144
14.2 Antipattern: utilizzare null come valore normale, o viceversa ................ 144
Utilizzo di null nelle espressioni .......................................................... 145
Ricerche in colonne che ammettono valori null .................................. 145
Utilizzo di null nei parametri di una query .......................................... 146
Evitare il problema .............................................................................. 146
14.3 Come riconoscere l’antipattern ........................................................... 147
14.4 Impieghi legittimi dell’antipattern ....................................................... 148
14.5 Soluzione: utilizzare null come valore unico ........................................ 149
Null in espressioni scalari .................................................................... 149
Null in espressioni booleane ................................................................ 150
Ricerca di null .................................................................................... 150
Dichiarare colonne NOT NULL ........................................................ 151
Default dinamici ................................................................................. 151
Capitolo 15 Gruppi ambigui ............................................................. 153
15.1 Obiettivo: ottenere la riga con il valore massimo per gruppo ................ 154
15.2 Antipattern: fare riferimento a colonne non raggruppate ..................... 155
La regola del valore singolo ................................................................. 155
Query che svolgono il loro compito .................................................... 156
15.3 Come riconoscere l’antipattern ........................................................... 156
15.4 Impieghi legittimi dell’antipattern ....................................................... 158
15.5 Soluzione: utilizzare le colonne senza ambiguità .................................. 158
Eseguire query soltanto su colonne funzionalmente dipendenti ............ 159
Utilizzare una sottoquery correlata ...................................................... 159
Utilizzare una tabella derivata .............................................................. 159
Utilizzare un join ................................................................................ 160
Utilizzare una funzione di aggregazione per le colonne extra ............... 161
Concatenare tutti i valori per gruppo .................................................. 161
Capitolo 16 Selezione casuale .......................................................... 163
16.1 Obiettivo: estrarre una riga campione .................................................. 164
16.2 Antipattern: ordinare i dati a caso ........................................................ 164
16.3 Come riconoscere l’antipattern ........................................................... 165
16.4 Impieghi legittimi dell’antipattern ....................................................... 166
16.5 Soluzione: in nessun ordine particolare... .............................................. 166
Scegliere un valore della chiave a caso compreso tra 1 e MAX ............. 166
Scegliere il valore della chiave immediatamente superiore .................... 166
Ottenere un elenco di tutti i valori della chiave e sceglierne uno a caso .. 167
Scegliere una riga a caso utilizzando un offset ...................................... 168
Soluzioni proprietarie ......................................................................... 168
Capitolo 17 Il motore di ricerca dei poveri ...................................... 171
17.1 Obiettivo: ricerca full-text ................................................................... 172
17.2 Antipattern: predicati di pattern matching ........................................... 172
17.3 Come riconoscere l’antipattern ........................................................... 173
17.4 Impieghi legittimi dell’antipattern ....................................................... 174
17.5 Soluzione: utilizzare lo strumento giusto .............................................. 174
Estensioni dei produttori ..................................................................... 174
Motori di ricerca esterni ..................................................................... 179
Far da sé.............................................................................................. 181
Capitolo 18 Spaghetti query ............................................................ 185
18.1 Obiettivo: diminuire le query SQL ...................................................... 186
18.2 Antipattern: risolvere un problema complesso in un unico passaggio .... 186
Prodotti imprevisti .............................................................................. 186
E come se non bastasse... ..................................................................... 188
18.3 Come riconoscere l’antipattern ........................................................... 188
18.4 Impieghi legittimi dell’antipattern ....................................................... 189
18.5 Soluzione: divide et impera ................................................................. 189
Un passo alla volta ............................................................................... 190
Alla ricerca di UNION ....................................................................... 190
Risolvere il problema del capo ............................................................ 191
Scrivere codice automaticamente con SQL .......................................... 192
Capitolo 19 Colonne implicite .......................................................... 195
19.1 Obiettivo: ridurre la digitazione di codice ........................................... 196
19.2 Antipattern: una scorciatoia in cui ci si perde ....................................... 197
Violare il refactoring ........................................................................... 197
Costi nascosti ...................................................................................... 198
Avete avuto ciò che avete chiesto ........................................................ 198
19.3 Come riconoscere l’antipattern ........................................................... 198
19.4 Impieghi legittimi dell’antipattern ....................................................... 199
19.5 Soluzione: indicare esplicitamente i nomi delle colonne ....................... 199
A prova di errore ................................................................................. 200
Evitare ciò che non serve .................................................................... 200
Dovrete comunque rinunciare ai caratteri jolly .................................... 201
Parte IV Antipattern nello sviluppo di applicazioni 203
Capitolo 20 Password leggibili ......................................................... 205
20.1 Obiettivo: recuperare o reimpostare le password ................................... 206
20.2 Antipattern: memorizzare password in chiaro ....................................... 206
Memorizzare le password .................................................................... 206
Autenticare le password ....................................................................... 207
Invio di password all’interno di messaggi email .................................... 208
20.3 Come riconoscere l’antipattern ........................................................... 209
20.4 Impieghi legittimi dell’antipattern ....................................................... 209
20.5 Soluzione: memorizzare un hash della password ................................... 209
Le funzioni di hash.............................................................................. 209
Usare un hash in SQL ......................................................................... 210
Un po’ di sale per l’hash ...................................................................... 211
Nascondere la password a SQL ............................................................ 212
Reimpostare la password invece di recuperarla ..................................... 213
Indice generale xi
Capitolo 21 SQL injection ................................................................. 217
21.1 Obiettivo: scrivere query SQL dinamiche ............................................ 218
21.2 Antipattern: eseguire come codice input non verifi cato ....................... 218
Gli incidenti possono accadere ............................................................ 219
Le principali minacce alla sicurezza sul Web ......................................... 220
La ricerca di una cura .......................................................................... 220
21.3 Come riconoscere l’antipattern ........................................................... 225
21.4 Impieghi legittimi dell’antipattern ....................................................... 225
21.5 Soluzione: non fi darsi di nessuno ......................................................... 225
Filtrare l’input ..................................................................................... 226
Parametrizzare i valori dinamici ........................................................... 226
Racchiudere tra apici i valori dinamici ................................................ 227
Isolare l’input dell’utente dal codice .................................................... 228
Chiedere un controllo del codice a un amico ...................................... 230
Capitolo 22 Pseudochiavi al posto sbagliato .................................. 231
22.1 Obiettivo: mettere a posto i dati .......................................................... 232
22.2 Antipattern: riempire tutto .................................................................. 232
Assegnare numeri fuori sequenza ......................................................... 232
Rinumerare righe esistenti .................................................................. 233
Discrepanze nei dati ............................................................................ 234
22.3 Come riconoscere l’antipattern ........................................................... 234
22.4 Impieghi legittimi dell’antipattern ....................................................... 235
22.5 Soluzione: passarci sopra ...................................................................... 235
Numerare le righe............................................................................... 235
Utilizzare GUID ................................................................................. 236
Il problema più importante ................................................................. 237
Capitolo 23 Il diavolo si nasconde ................................................... 239
23.1 Obiettivo: scrivere meno codice .......................................................... 240
23.2 Antipattern: lavorare senza sfruttare gli strumenti giusti ........................ 240
Diagnosi senza strumenti di diagnostica ............................................... 240
Leggere tra le righe ............................................................................. 241
23.3 Come riconoscere l’antipattern ........................................................... 242
23.4 Impieghi legittimi dell’antipattern ....................................................... 243
23.5 Soluzione: recuperare gli errori in modo elegante ................................ 243
Mantenere il ritmo .............................................................................. 243
Risalire il percorso effettuato ............................................................... 244
Capitolo 24 Immunità diplomatica .................................................. 245
24.1 Obiettivo: impiegare le best practice .................................................... 246
24.2 Antipattern: fare di SQL un cittadino di seconda classe ........................ 246
24.3 Come riconoscere l’antipattern ........................................................... 247
24.4 Impieghi legittimi dell’antipattern ....................................................... 248
24.5 Soluzione: sviluppare una cultura universale della qualità...................... 248
Prova A: documentazione .................................................................... 248
Mantenere le tracce: controllo del codice sorgente ............................... 250
L’onere della prova: i test ..................................................................... 252
Carico di lavoro: lavorare in più diramazioni ........................................ 254
Capitolo 25 Fagioli magici ................................................................ 255
25.1 Obiettivo: semplifi care i modelli in MVC ............................................ 256
25.2 Antipattern: il modello è un active record ............................................ 257
Active Record accoppia i modelli allo schema ..................................... 258
Active Record espone funzioni CRUD ............................................... 258
Active Record incoraggia un domain model anemico ......................... 259
Eseguire unit test per i fagioli magici è diffi cile .................................... 261
25.3 Come riconoscere l’antipattern ........................................................... 262
25.4 Impieghi legittimi dell’antipattern ....................................................... 263
25.5 Soluzione: il modello ha un Active Record .......................................... 263
GRASP .............................................................................................. 263
Far entrare in azione il domain model ................................................. 264
Test di oggetti ..................................................................................... 267
Torniamo con i piedi per terra ............................................................ 267
Parte V Appendici 269
Appendice A Regole di normalizzazione ........................................... 271
A.1 Che cosa signifi ca relazionale? ............................................................. 272
Le righe non sono ordinate dall’alto in basso ....................................... 272
Le colonne non sono ordinate da sinistra a destra ................................. 272
Le righe duplicate non sono ammesse ................................................. 272
Ogni colonna ha un solo tipo di dati, e un solo valore per riga ............ 273
Le righe non hanno componenti nascosti ............................................ 273
A.2 Alcuni miti sulla normalizzazione ........................................................ 273
A.3 Che cos’è la normalizzazione? ............................................................. 274
Prima forma normale .......................................................................... 275
Seconda forma normale ...................................................................... 275
Terza forma normale ........................................................................... 277
Forma normale di Boyce-Codd .......................................................... 279
Quarta forma normale ........................................................................ 280
Quinta forma normale ........................................................................ 281
Ulteriori forme normali ...................................................................... 283
A.4 Buon senso ......................................................................................... 283
Appendice B Bibliografi a .................................................................... 285
Indice analitico...................................................................................... 287
