Viktigt
Översättning är en gemenskapsinsats du kan gå med i. Den här sidan är för närvarande översatt till 100.00%.
15.2. Lektion: Implementering av datamodellen
Nu när vi har gått igenom all teori ska vi skapa en ny databas. Denna databas kommer att användas för våra övningar för de lektioner som kommer att följa efteråt.
Målet för den här lektionen: Att installera den programvara som krävs och använda den för att implementera vår exempeldatabas.
15.2.1. Installera PostgreSQL
Observera
Du kan hitta PostgreSQL-paket och installationsinstruktioner för ditt operativsystem på https://www.postgresql.org/download/. Observera att dokumentationen förutsätter att användarna kör QGIS under Ubuntu.
Under Ubuntu:
sudo apt install postgresql-9.1
Du bör få ett meddelande som detta:
[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]?
Tryck på Y och Enter och vänta tills nedladdningen och installationen är klar.
15.2.2. Hjälp
PostgreSQL har mycket bra online dokumentation.
15.2.3. Skapa en databasanvändare
Under Ubuntu:
När installationen är klar kör du det här kommandot för att bli postgres-användare och sedan skapa en ny databasanvändare:
sudo su - postgres
Skriv in ditt vanliga inloggningslösenord när du uppmanas att göra det (du måste ha sudo-rättigheter).
Skapa nu databasanvändaren i postgres-användarens bash-prompt. Se till att användarnamnet matchar ditt unix-inloggningsnamn: det kommer att göra ditt liv mycket enklare, eftersom postgres automatiskt autentiserar dig när du är inloggad som den användaren:
createuser -d -E -i -l -P -r -s qgis
Ange ett lösenord när du uppmanas att göra det. Du bör använda ett annat lösenord än ditt inloggningslösenord.
Vad innebär dessa alternativ?
-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
Nu ska du lämna postgres-användarens bash-skalmiljö genom att skriva:
exit
15.2.4. Verifiera det nya kontot
psql -l
Bör returnera något liknande detta:
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)
Skriv Q för att avsluta.
15.2.5. Skapa en databas
Kommandot createdb används för att skapa en ny databas. Det bör köras från skalprompten bash:
createdb address -O qgis
Du kan kontrollera att den nya databasen finns med hjälp av det här kommandot:
psql -l
Vilket borde ge något i stil med detta:
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)
Skriv Q för att avsluta.
15.2.6. Starta en shell-session för en databas
Du kan enkelt ansluta till din databas på följande sätt:
psql address
Om du vill lämna psql-databasens skal skriver du:
\q
Om du vill ha hjälp med att använda skalet skriver du:
\?
Om du vill ha hjälp med att använda sql-kommandon skriver du:
\help
För att få hjälp med ett specifikt kommando skriver du (till exempel):
\help create table
Se även Psql fuskblad.
15.2.7. Skapa tabeller i SQL
Låt oss börja skapa några tabeller! Vi kommer att använda vårt ER-diagram som en guide. Anslut först till adressdatabasen:
psql address
Skapa sedan en streets-tabell:
create table streets (id serial not null primary key, name varchar(50));
serial och varchar är datatyper. serial säger till PostgreSQL att starta en heltalssekvens (auto-number) för att fylla i id automatiskt för varje ny post. varchar(50) säger till PostgreSQL att skapa ett teckenfält med 50 tecken i längd.
Du kommer att märka att kommandot avslutas med ett ; - alla SQL-kommandon ska avslutas på detta sätt. När du trycker på Enter kommer psql att rapportera ungefär så här:
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
Det betyder att din tabell skapades framgångsrikt, med en primärnyckel streets_pkey som använder streets.id.
Obs: Om du trycker på retur utan att ange en ;, kommer du att få en prompt som denna: address-#. Detta beror på att PG förväntar sig att du ska skriva in mer. Ange ; för att köra ditt kommando.
Så här kan du visa ditt tabellschema:
\d streets
Vilket borde visa något liknande detta:
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)
Så här kan du visa innehållet i din tabell:
select * from streets;
Vilket borde visa något liknande detta:
id | name
---+------
(0 rows)
Som du kan se är vår tabell för närvarande tom.
Prova själv: ★★☆
Använd metoden som visas ovan för att skapa en tabell som heter personer:
Lägg till fält som telefonnummer, hemadress, namn etc. (alla dessa namn är inte giltiga, ändra dem så att de blir giltiga). Se till att du ger tabellen en ID-kolumn med samma datatyp som ovan.
Svar
Den SQL som krävs för att skapa rätt persontabell är:
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 );
Schemat för tabellen (ange d människor) ser ut så här:
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)
I illustrationssyfte har vi avsiktligt utelämnat fkey-begränsningen.
15.2.8. Skapa nycklar i SQL
Problemet med vår lösning ovan är att databasen inte vet att människor och gator har en logisk relation. För att uttrycka detta förhållande måste vi definiera en främmande nyckel som pekar på den primära nyckeln i tabellen streets.
Det finns två sätt att göra detta på:
Lägg till nyckeln efter att tabellen har skapats
Definiera nyckeln vid tidpunkten för skapandet av tabellen
Vår tabell har redan skapats, så låt oss göra det på det första sättet:
alter table people
add constraint people_streets_fk foreign key (street_id) references streets(id);
Det säger till tabellen people att dess fält street_id måste matcha en giltig gata id från tabellen streets.
Det vanligaste sättet att skapa en constraint är att göra det när du skapar tabellen:
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
Efter att ha lagt till begränsningen ser vårt tabellschema ut så här nu:
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. Skapa index i SQL
Vi vill ha blixtsnabba sökningar på personers namn. För att åstadkomma detta kan vi skapa ett index på namnkolumnen i vår tabell över personer:
create index people_name_idx on people(name);
\d people
Vilket resulterar i:
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. Borttagning av tabeller i SQL
Om du vill göra dig av med en tabell kan du använda kommandot drop:
drop table streets;
I vårt aktuella exempel skulle ovanstående kommando inte fungera. Varför skulle det inte det?
Svar
Anledningen till att DROP-kommandot inte skulle fungera i det här fallet är att tabellen people har en Foreign Key-begränsning till tabellen streets. Det innebär att om tabellen streets släpps (eller tas bort) kommer tabellen people att innehålla referenser till obefintliga data från streets.
Det är möjligt att ”tvinga” streets-tabellen att raderas med kommandot CASCADE, men detta skulle också radera people och alla andra tabeller som har en relation till streets-tabellen. Använd med försiktighet!
Om du använde samma drop table-kommando på people-tabellen skulle det lyckas:
drop table people;
Observera
Om du faktiskt skrev in kommandot och tappade bort tabellen people, är det nu en bra tid att bygga upp den igen, eftersom du kommer att behöva den i nästa övning.
15.2.11. Ett ord om pgAdmin III
Vi visar dig SQL-kommandona från psql-prompten eftersom det är ett mycket användbart sätt att lära sig om databaser. Det finns dock snabbare och enklare sätt att göra mycket av det vi visar dig. Installera pgAdmin III så kan du skapa, släppa, ändra etc tabeller med hjälp av ”peka och klicka”-operationer i ett grafiskt gränssnitt.
Under Ubuntu kan du installera det så här:
sudo apt install pgadmin3
pgAdmin III kommer att behandlas mer ingående i en annan modul.
15.2.12. Sammanfattningsvis
Du har nu sett hur du skapar en helt ny databas från grunden.
15.2.13. Vad händer härnäst?
Därefter får du lära dig hur du använder DBMS för att lägga till nya data.