Importante
La traduzione è uno sforzo comunitario you can join. Questa pagina è attualmente tradotta al 98.77%.
15.2. Lezione: Implementare il Modello Dati
Ora dopo aver trattato tutta la teoria, crea un nuovo database. Questo database verrà utilizzato per gli esercizi delle lezioni che seguiranno.
Obiettivo di questa lezione: Installare il software richiesto e utilizzarlo per realizzare il database di esempio.
15.2.1. Installare PostgreSQL
Nota
Puoi trovare i pacchetti PostgreSQL e le istruzioni di installazione per il tuo sistema operativo su https://www.postgresql.org/download/. Da notare che la documentazione presuppone che gli utenti eseguano QGIS sotto Ubuntu.
Sotto Ubuntu:
sudo apt install postgresql-9.1
Dovresti avere un messaggio come questo:
[sudo] password for qgis:
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
postgresql-client-9.1 postgresql-client-common postgresql-common
Suggested packages:
oidentd ident-server postgresql-doc-9.1
The following NEW packages will be installed:
postgresql-9.1 postgresql-client-9.1 postgresql-client-common postgresql-common
0 upgraded, 4 newly installed, 0 to remove and 5 not upgraded.
Need to get 5,012kB of archives.
After this operation, 19.0MB of additional disk space will be used.
Do you want to continue [Y/n]?
Premi S e Enter e aspetta la fine dell’installazione.
15.2.2. Guida
PostgreSQL ha un’ottima documentazione online.
15.2.3. Crea un utente del database
Sotto Ubuntu:
Al termine dell’installazione, esegui questo comando per diventare utente postgres e quindi creare un nuovo utente del database:
sudo su - postgres
Digita la normale password di accesso quando richiesto (è necessario disporre dei diritti sudo).
Ora, al prompt di sistema dell’utente postgres, crea l’utente del database. Assicurati che il nome utente corrisponda al tuo nome utente unix: renderà la tua vita molto più semplice, poiché postgres ti autenticherà automaticamente quando avrai effettuato l’accesso come tale utente:
createuser -d -E -i -l -P -r -s qgis
Immetti una password quando richiesto. È necessario utilizzare una password diversa per la password di accesso.
Cosa significano queste opzioni?
-d, --createdb role can create new databases
-E, --encrypted encrypt stored password
-i, --inherit role inherits privileges of roles it is a member of (default)
-l, --login role can login (default)
-P, --pwprompt assign a password to new role
-r, --createrole role can create new roles
-s, --superuser role will be superuser
Ora dovresti lasciare l’ambiente shell bash dell’utente postgre digitando:
exit
15.2.4. Verifica il nuovo account
psql -l
Dovrebbe restituire qualcosa del genere:
Name | Owner | Encoding | Collation | Ctype |
----------+----------+----------+------------+------------+
postgres | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 |
template0 | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 |
template1 | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 |
(3 rows)
Premi Q per uscire.
15.2.5. Creare un database
Puoi usare il comando createdb per creare un nuovo database. Dovrebbe essere eseguito dal prompt della shell bash:
createdb address -O qgis
Puoi verificare l’esistenza del nuovo database utilizzando questo comando:
psql -l
Che dovrebbe restituire qualcosa di simile:
Name | Owner | Encoding | Collation | Ctype | Access privileges
----------+----------+----------+------------+------------+-----------------------
address | qgis | UTF8 | en_ZA.utf8 | en_ZA.utf8 |
postgres | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 |
template0 | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 | =c/postgres: postgres=CTc/postgres
template1 | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 | =c/postgres: postgres=CTc/postgres
(4 rows)
Premi Q per uscire.
15.2.6. Avvio di una sessione shell del database
Puoi connetterti facilmente al tuo database in questo modo:
psql address
Per uscire dalla shell del database psql, digita:
\q
Per informazioni sull’utilizzo della shell, digita:
\?
Per informazioni sull’utilizzo dei comandi SQL, digita:
\help
Per ottenere aiuto su un comando specifico, digita (ad esempio):
\help create table
Vedi anche il Psql cheat sheet.
15.2.7. Creare tabelle in SQL
Inizia a fare alcune tabelle! Usa il diagramma ER come guida. Innanzitutto, connettiti al database address:
psql address
Quindi crea una tabella denominata streets:
create table streets (id serial not null primary key, name varchar(50));
serial e varchar sono i tipi di dati. serial indica a PostgreSQL di avviare una sequenza di numeri interi (numero automatico) per popolare automaticamente il campo id per ogni nuovo record. varchar(50) indica a PostgreSQL di creare un campo di tipo carattere della lunghezza di 50 caratteri.
Noterai che il comando termina con un ;: tutti i comandi SQL devono terminare in questo modo. Quando premi Enter, psql visualizzerà un messaggio simile a questo:
NOTICE: CREATE TABLE will create implicit sequence "streets_id_seq"
for serial column "streets.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"streets_pkey" for table "streets"
CREATE TABLE
Ciò significa che la tabella è stata creata correttamente, con una chiave primaria denominata streets_pkey che utilizza streets.id.
Nota: se premi Invio senza inserire un ;, ti apparirà un prompt del tipo: address-#. Questo accade perché PostgreSQL si aspetta che tu inserisca altro. Inserisci ; per eseguire il comando.
Per vedere lo schema della tabella:
\d streets
Dovrebbe mostrarti qualcosa di simile:
Table "public.streets"
Column | Type | Modifiers
--------+-----------------------+--------------------------------------
id | integer | not null default
| | nextval('streets_id_seq'::regclass)
name | character varying(50) |
Indexes:
"streets_pkey" PRIMARY KEY, btree (id)
Per vedere il contenuto della tabella:
select * from streets;
Dovrebbe mostrarti qualcosa di simile:
id | name
---+------
(0 rows)
Come puoi vedere, la tabella è attualmente vuota
Prova Tu: ★★☆
Usa l’approccio mostrato sopra per creare una tabella chiamata people:
Aggiungi campi come numero di telefono, indirizzo di casa, nome, ecc. (Questi non sono tutti nomi validi: cambiali per renderli validi). Assicurati di dare alla tabella una colonna ID con lo stesso tipo di dati di cui sopra.
Soluzione
L’SQL necessario per creare la tabella people corretta è:
create table people (id serial not null primary key,
name varchar(50),
house_no int not null,
street_id int not null,
phone_no varchar null );
Lo schema per la tabella (enter \\d people) si presenta così:
Table "public.people"
Column | Type | Modifiers
-----------+-----------------------+-------------------------------------
id | integer | not null default
| | nextval('people_id_seq'::regclass)
name | character varying(50) |
house_no | integer | not null
street_id | integer | not null
phone_no | character varying |
Indexes:
"people_pkey" PRIMARY KEY, btree (id)
A scopo illustrativo, abbiamo volutamente omesso il vincolo fkey.
15.2.8. Crea chiavi in SQL
Il problema ora è che il database non sa che people e streets hanno una relazione logica. Per esprimere questa relazione, dobbiamo definire una chiave esterna che punta alla chiave primaria della tabella streets.
Puoi farlo in due modi:
Add the key after the table has been created
Definisci la chiave quando crei la tabella
La tabella è già stata creata, quindi fallo nel primo modo:
alter table people
add constraint people_streets_fk foreign key (street_id) references streets(id);
Questo indica che i campi street_id della tabella people devono corrispondere agli id della tabella streets.
Il modo più usuale per creare una relazione è farlo quando crei la tabella:
create table people (id serial not null primary key,
name varchar(50),
house_no int not null,
street_id int references streets(id) not null,
phone_no varchar null);
\d people
Dopo aggiunto il vincolo lo schema della tabella appare ora così:
Table "public.people"
Column | Type | Modifiers
-----------+-----------------------+---------------------------------
id | integer | not null default
| | nextval('people_id_seq'::regclass)
name | character varying(50) |
house_no | integer | not null
street_id | integer | not null
phone_no | character varying |
Indexes:
"people_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"people_streets_fk" FOREIGN KEY (id) REFERENCES streets(id)
15.2.9. Crea indici in SQL
Vuoi ricerche veloci sui nomi delle persone. Per fare ciò, puoi creare un indice sulla colonna del nome della nostra tabella people:
create index people_name_idx on people(name);
\d people
Che risulta:
Table "public.people"
Column | Type | Modifiers
-----------+-----------------------+-----------------------------------
id | integer | not null default nextval
| | ('people_id_seq'::regclass)
name | character varying(50) |
house_no | integer | not null
street_id | integer | not null
phone_no | character varying |
Indexes:
"people_pkey" PRIMARY KEY, btree (id)
"people_name_idx" btree (name) <-- new index added!
Foreign-key constraints:
"people_streets_fk" FOREIGN KEY (id) REFERENCES streets(id)
15.2.10. Eliminare tabelle in SQL
Se vuoi sbarazzarti di una tabella puoi usare il comando DROP :
drop table streets;
Nel nostro esempio attuale, il comando di cui sopra non funzionerebbe. Perché no?
Soluzione
Il motivo per cui il comando DROP non funzionerebbe in questo caso è che la tabella people presenta un vincolo di chiave esterna rispetto alla tabella streets. Ciò significa che l’eliminazione (o la cancellazione) della tabella streets lascerebbe la tabella``people`` con riferimenti a dati relativi a strade inesistenti.
È possibile “forzare” l’eliminazione della tabella streets utilizzando il comando CASCADE , ma ciò comporterebbe anche l’eliminazione della tabella people e di qualsiasi altra tabella che abbia una relazione con la tabella streets. Da usare con cautela!
Se hai usato lo stesso comando drop table sulla tabella people, sarebbe riuscito:
drop table people;
Nota
Se hai digitato tale comando ed eliminato la tabella people, ora sarebbe il momento giusto per ricostruirlo, poiché ne avrai bisogno nei prossimi esercizi.
15.2.11. Una parola su pgAdmin III
Ti mostriamo i comandi SQL dal prompt di psql perché è un modo molto utile per conoscere i database. Tuttavia, ci sono modi più rapidi e semplici per fare molto di quello che ti stiamo mostrando. Installa pgAdmin III e puoi creare, eliminare, modificare le tabelle etc usando le operazioni in una GUI.
Sotto Ubuntu, puoi installarlo così:
sudo apt install pgadmin3
pgAdmin III sarà trattato in maggior dettaglio in un altro modulo.
15.2.12. In Conclusione
Ora hai visto come creare un nuovo database, iniziando completamente da zero.
15.2.13. Cosa Segue?
Adesso imparerai come usare il DBMS per aggiungere nuovi dati.