5. Moment05 - Databasteori & SQL

Databaser är ett strukturerat sätt att lagra datamängder, vanligtvis stora sådana. I denna kursen så gäller att använda datalagring som stöd till våra webbapplikationer och då har jag valt att använda databaser, det skulle gå att lagra data i andra filformat som .txt, .xml eller .json men det är lika bra att ta tjuren vid hornen och lära sig det bästa alternativet för större datasamlingar, allt för att inte bli begränsad senare.

Först så kommer vi jobba med databasmodellering vilket innebär att vi ritar upp hur databasen skall se ut för att kunna lagra data på ett tydligt sätt.

När vi sedan kan modellera en databas är det dags att börja jobba med databasen. I detta moment skall vi lära oss att skapa databasen, populera den och sedan ställa frågor och manipulera det data som lagras i databasen.

Detta är ett teoritungt moment så det vi kommer dela upp den i två delar. Uppgiften är också tvådelad men vi jobbar med samma databas så det blir mer en stor uppgift fast i två mindre delar.

5.1 Terminologi

För att lösa detta moment behöver vi lära oss terminiologin för databaser.

I en relationsdatabas så måste det finnas tabeller som på något sätt har en relation mellan varandra.

I varje tabell finns det rader, varje rad kallas post, och kolumner som kallas fält. Varje fält måste definieras där man talar om vilken datatyp och eventuell storlek som skall användas för detta fält.

Index skapas för att snabba på sökningar i en databas. För enklare databaser är det inte så viktigt att skapa egna index.

Ett fält, eller en kombination av fält, i varje tabell används som primärnyckel, primary key. Primärnycklar används för att unikt identifiera en post och är ansvarig för att det inte finns två poster med samma nyckel. En primärnyckel är en typ av index.

Tabellerna kopplas ihop genom att det som är primärnyckel i en tabell finns med som kopplande värde i en annan tabell, detta kallas då för främmande nyckel, foreign key.

När en databas skall skapas så bygger definitionerna av tabeller, fält och index upp ett schema. Varje schema lagras i en egen databas.

Vi använder frågespråket SQL - Structured Query Language för att en klient skall kunna kommunicera med databasen.

Vyer är ett sätt att skapa en SQL-fråga som ligger i databasen. Istället för att skicka en lång fråga från tex php-sidan så kan vi göra ett anrop till en skapad vy som ger samma svar.

5.2 Databasmodellering

Bug

Hela denna delen gjordes första gången som en stor genomgång på tavlan ihop med elever där vi gick igenom hela processens olika delar och steg för steg tog oss framåt. Efter varje lektion skrevs exemplet ner och det är dessa anteckningar som har byggt upp momentet. Alla fel som vi upptäckte under utvecklingens gång fick vara med, och så har vi kommenterat och rättat istället för att rätta och göra allt perfekt. Detta sätt har visat sig lyckat så felen och bristerna har fått bestå.

För att kunna översätta verkligheten till ett databasschema så använder vi oss av databasmodellering. Databasmodelleringen sker i flera steg och vi börjar med att skapa ett ER-diagram.

5.2.1 ER-diagram

Vi visar hur ett ER-diagram fungerar genom ett exempel. Vi tänker oss att skapa ett väldigt enkelt bibliotekssystem där en person skall kunna låna en bok. I den första versionen har vi satt begränsningen till att det bara får finnas ett exemplar av en enda bok.

Alla ER-diagram har jag ritat i draw.io som du troligtvis har använt tidigare i någon av mina kurser. Se till att koppla draw.io till din Google Drive så att dina modeller sparas.

Vi börjar med att identifiera det "saker" som skall finnas i vårt system, i detta fall så kan vi se att det bör finnas böcker och låntagare, alltså någon som kan låna en bok. Vi väljer att skriva dessa entitetstyper i singular och ramar in dem i en rektangel. Vi väljer också att använda "Person" istället för "Låntagare" då vi tror detta är ett bättre namn.

Vi ritar nu ut sambanden mellan våra entiteter. Sambandet binds ihop med hjälp av en utdragen romb eller en diamantform som det ofta benämns som. I detta fallet så är vårt samband mellan Bok och Person möjligheten att låna en bok. Vi kallar sambandet "Låna".

Det finns olika sambandstyper, dessa blir intressanta sedan när vi skall skapa tabeller av vårt ER-diagram. De tre sambanden vi har att välja på är följande;

  • 1:1 - Ett till ett
    Här kan vi visa på att en en person kör en bil, det går inte att fler kör samma bil vid ett givet tillfälle. En bil körs också av en person.
  • 1:N - Ett till många
    En bil kan bara ägas av en person, men en person kan äga många bilar.
  • N:M - Många till många
    Här blir exemplet att en person kan äga många hus och ett hus kan ägas av många personer.

I vårt exempel så kan vi se att en person kan låna många böcker men en specifik bok kan bara lånas av en person, vid ett specifikt tillfälle. Det innebär att vi behöver ange sambandstypen 1:N. Vi sätter 1 vid personen och N vid boken eftersom "1 person kan låna flera böcker".

Dags att kika på vilka attribut (ett attribut är en egenskap som är kopplad till en entitet eller ett samband) som skall kopplas till våra entiteter. Vi ringar in attributen och drar streck till den entitet, eller det samband, som den tillhör.

Vi måste välja något attribut i varje entitet som skall kunna identifiera en post på något unikt sätt. När vi har valt detta attribut så stryker vi under det. Ibland är det enkelt att välja rätt attribut, ibland är det svårare. Personnummer, isbn-nummer, lånekortsnummer och liknande attribut är tacksamma att använda. Ibland kan man behöva hjälp av flera attribut för att skapa en unik kombination. För sambandstypen "Låna" hade det behövts tre attribut (lånare, boken och datum) för att få det unikt, då kan det vara enklare att skapa ett id som endast har i uppgift att vara en unik identifierare.

Det vi har gjort nu är minimum av vad man måste kunna för att modellera en databas. Nu skall vi kika vidare på hur man gör om det till en datamodell.

5.2.2 Datamodell

ER-diagrammet används för att få en bättre förståelse för verkligheten, nu skall vi se hur vi kan få in detta i en datamodell vilket sedan blir grunden för våra tabeller i databasen.

Alla datamodeller är skapade i Vertabelo som är en grym tjänst på nätet för att bygga upp datamodeller och även skapa script för att skapa databaser. Detta kommer vi använda oss av under resten av denna kurs. För att skapa ett eget konto på Vertabelo så kolla på kunskapsdokument: Vertabelo.

Det första vi skall göra är att göra om varje entitet till en tabell och låta attributen till entiteterna bilda kolumner. Vi behöver också fundera på om våra samband behöver bli egna tabeller eller om det är möjligt att lägga detta som attribut i redan befintliga tabeller. I vårt exempel kommer vi behöva skapa en tabell även för lånesambandet eftersom det skall lagra data om när en bok lånades ut, när den skall vara tillbaka och när den faktiskt är återlämnad.

Som du ser i vår datamodell så anger vi attributet, datatyp samt vilken typ av nyckel det är. Det går också att ange om ett visst attribut får innehålla "null", i vårt exempel har jag missat det, men attributet återlämnad (date) borde absolut vara null. Detta rättar vi senare i exemplet.

Detta borde räcka och i Vertabelo så kan man här välja att exportera ett sql-script som kan skapa denna databasen i en databashanterare. Skriptet kollar vi på i senare moment.

5.2.2.1 Fördjupning

Eftersom vi körde övningen på tavlan så hade vi olika funderingar och diskussioner på olika lösningar.

  1. Det första var att bryta ut författare och språk eftersom detta är data som annars behöver dubbellagras. När ett visst författarnamn skall skrivas många gånger så är risken stor att det någon gång stavas fel vilket innebär att det blir svårt att söka efter namn som är felstavade.
  2. Ibland kan det vara moraliskt tveksamt att låta känsligt data vara nycklar, i detta fall gäller det främst personnummer. Ett sätt att komma runt detta är att skapa id som en identifierare för varje tabell. Detta id har ett "nonsensvärde" vilket innebär att det inte spelar någon roll om värdet är 7, 19 eller 299, allt värdet gör är att unikt identifiera en post och koppla ihop värden mellan tabeller. Eftersom vi vill ha en enhetlig struktur på varje tabell så skapar vi sådana här id som primärnyckel för alla tabeller.

Vi kikar på hur vårt ER-diagram skulle se ut med en sådan lösning.

Och så här ser datamodellen ut. Här ser du också att jag har döpt om id för varje tabell till tabellensNamnId för att det skall vara konsekvent och tydligt vilket id som är vilket. Kolla på tabellen Bok som exempel, de tre första fälten är alla id:n, den första är primärnyckel för tabellen Bok och de följande två är främmande nycklar från tabellerna Språk och Författare. För att förenkla kopplingen senare så väljer jag här att låta språkId heta samma sak i bägge tabellerna.

Ytterligare en sak som vi pratade om var att istället för att låna en viss bok så lånar man ett exemplar av en bok som då givetvis kan finnas i flera exemplar på biblioteket. För att lösa detta behöver vi skapa en ny entitet, vi gör ändringen både i ER-diagrammet.

När vi gör samma ändring i datamodellen och kontrollerar denna så kommer vi märka att vi får ett antal varningar.

I Vertabelo kommer vi nu få ett gäng varningar, alla dessa varningar bygger på det svenska språket. Eftersom jag gick igenom alla lager där det är viktigt att använda rätt teckenkodning så förstår ni hur lätt det blir fel, därför ändrar vi hela vår datamodell till engelska.

Rättning

I samband med att vi jobbade med senare moment så hittade jag lite fel i vår slutliga modell, vi lånar just nu böcker, men i den senaste modellen så skall vi låna ett exemplar av en bok, vilket innebär att PersonBook borde kopplas till Copy istället för Book. Nu gör jag om kopplingen, men jag döper inte om tabellen i detta fall. För att det skall vara maximalt konsekvent borde tabellen istället heta PersonCopy men det låter inte så bra så det struntar jag i. PersonBook beskriver bättre vad den gör, men allt som ändras på vägen får konsekvenser och om vi har flera sådana här missar så blir till slut modellerna helt inkonsekventa och det blir svårt att få ihop projektet. Hittar vi inga fler misstag så kan vi leva med det, skulle fler konstigheter dyka upp får vi kanske se över helheten en gång till och backa lite och göra om.

Så här skall de uppdaterade modellerna se ut, först ER-diagrammet.

Och sedan datamodellen.


Egentligen skulle jag också vilja ändra namn på tabellerna, jag vill att tabellnamnen skall börja med en gemen, alltså liten bokstav, men det får vara denna gången.

5.2.3 Uppgift

Uppgift inloggningsapplikation

I projekt01 så byggde du en väldigt enkel inloggningsapplikation, du skall nu skissa på en databas som du kan använda för detta ändamålet. Uppgiften blir att skapa databasen som kan ta hand om användare och lösenord. Exakt vad som skall finnas i din databas/tabell vet bara du men följande saker kan vara bra att fundera på om du vill ha eller inte vill ha.

  • Användarnamn (måste finnas)
  • Lösenord (måste finnas, skall senare krypteras)
  • Mailadress (finns ofta för kommunikation)
  • Flagga för att byta lösenord (varför?)
  • Unik identifierar (hur görs det?)
  • Låst konto (låsa/låsa upp konto?)
  • Räkna felaktiga inloggningsförsök (bra att ha?)
  • Datum för senaste inloggningen (bra att ha?)

När du anser dig färdig med denna delen, lägg upp ditt ER-diagram och din databasmodell samt skriv en utvärdering i ett inlägg på din WordPress. Beskriv här också vilka attribut du har valt att lägga i tabellen. Motivera om du anser att det behövs.

5.3 Praktik & SQL

Det var den rent teoretiska delen av databasbygget. Här kan det vara smart att stanna till en stund extra för att kolla att vi har fått med oss alla delar. Om vi gör en bra databasmodell första gången så behöver vi kanske inte komma tillbaka hit allt för många gånger och förändra.

Nu är det dags att börja med det praktiska arbetet med själva databasen.

5.3.1 SQL

SQL, Structured Query Language, är ett standardiserat programspråk för att hämta och modifiera data i en relationsdatabas. Vi kommer lära oss SQL genom att fortsätta jobba med vårt exempel. Du måste behärska SQL i slutet av denna kursen, men det är inget måste att du lär dig allt utantill, även om det kommer spara dig en hel del tid. Jag skall visa på några sätt du kan ta fram den SQL du behöver för att bygga projekten utan att skriva SQL själv.
Men som allt annat, kan du skriva det på egen hand så sparar du tid och i webbranschen kan vi enkelt använda den gamla klyschan Tid är pengar.

5.3.2 Importera databas

Vi har redan byggt upp en databas i verktyget Vertabelo (xml-filen till biblioteket finns här) och där finns det en trevlig funktion som gör att vi kan exportera hela databasen som sql-kommandon vilket gör att vi på ett enkelt sätt kan importera denna till vår databashanterare.

Vi kikar lite på koden, det kan vara kul att förstå vad som händer.

SQL-skript för databaen Library

-- Created by Vertabelo (http://vertabelo.com)
-- Last modification date: 2018-12-02 15:49:14.818

-- tables
-- Table: Author
CREATE TABLE Author (
    authorId int NOT NULL,
    author varchar(50) NOT NULL,
    CONSTRAINT Author_pk PRIMARY KEY (authorId)
);

-- Table: Book
CREATE TABLE Book (
    bookId int NOT NULL,
    authorId int NOT NULL,
    languageId int NOT NULL,
    ISBN varchar(13) NOT NULL,
    title varchar(100) NOT NULL,
    CONSTRAINT Book_pk PRIMARY KEY (bookId)
);

-- Table: Copy
CREATE TABLE Copy (
    copyId int NOT NULL,
    bookId int NOT NULL,
    serialNumber int NOT NULL,
    active bool NOT NULL,
    CONSTRAINT Copy_pk PRIMARY KEY (copyId)
);

-- Table: Language
CREATE TABLE Language (
    languageId int NOT NULL,
    language varchar(30) NOT NULL,
    CONSTRAINT Language_pk PRIMARY KEY (languageId)
);

-- Table: Person
CREATE TABLE Person (
    personId int NOT NULL AUTO_INCREMENT,
    personalIdentityNumber varchar(12) NOT NULL,
    name varchar(50) NOT NULL,
    address varchar(50) NOT NULL,
    loneCardNr int NOT NULL,
    CONSTRAINT Person_pk PRIMARY KEY (personId)
);

-- Table: PersonBook
CREATE TABLE PersonBook (
    personBookId int NOT NULL AUTO_INCREMENT,
    personId int NOT NULL,
    copyId int NOT NULL,
    dateForLone timestamp NOT NULL,
    dateForReturn date NOT NULL,
    dateReturned date NULL,
    CONSTRAINT PersonBook_pk PRIMARY KEY (personBookId)
);

-- foreign keys
-- Reference: Book_Author (table: Book)
ALTER TABLE Book ADD CONSTRAINT Book_Author FOREIGN KEY Book_Author (authorId)
    REFERENCES Author (authorId);

-- Reference: Book_Language (table: Book)
ALTER TABLE Book ADD CONSTRAINT Book_Language FOREIGN KEY Book_Language (languageId)
    REFERENCES Language (languageId);

-- Reference: Copy_Book (table: Copy)
ALTER TABLE Copy ADD CONSTRAINT Copy_Book FOREIGN KEY Copy_Book (bookId)
    REFERENCES Book (bookId);

-- Reference: PersonBook_Copy (table: PersonBook)
ALTER TABLE PersonBook ADD CONSTRAINT PersonBook_Copy FOREIGN KEY PersonBook_Copy (copyId)
    REFERENCES Copy (copyId);

-- Reference: PersonBook_Person (table: PersonBook)
ALTER TABLE PersonBook ADD CONSTRAINT PersonBook_Person FOREIGN KEY PersonBook_Person (personId)
    REFERENCES Person (personId);

-- End of file.

Då går vi kort igenom den kod som har genererats från Vertabelo.

-- En kodrad som börjar med -- hanteras som en kommentar och kommer inte att köras.
Varje SQL-sats som exekveras avslutas med semikolon (;), det innebär att du kan skriva kod på en rad eller på flera rader, allt som finns innan ett semikolon kommer att köras.

På raderna 9-13 skapas tabellen Author, nyckelkommandot här är CREATE TABLE, sedan listas de attribut som finns i tabellen, sedan anges vilken datatyp attributet har samt om det är tillåtet att lagra NULL i attributet. Vi har valt att ge våra primärnycklar ett automatiskt ID, detta sköts av "AUTO_INCREMENT", vilket innebär att det är en räknare som hela tiden räknar upp det värdet automatiskt. När alla attribut är skapade anges vilket attribut som skall vara primärnyckel.
Övriga tabeller skapas på samma sätt.

Från rad 62 skapas kopplingarna mellan tabellerna, här anges vilka tabeller som skall kopplas ihop och vilka attribut det är som kopplar ihop tabellerna.

All den kod som nu är genererad som ett SQL-skript kan vi naturligtvis skapa själva eller med hjälp av tex phpMyAdmin. I denna kurs behöver du inte lära dig att skriva all denna kod men du behöver kunna skapa tabeller, attribut, nycklar och kopplingar för dina databaser till dina applikationer. Hur du gör det är inte lika viktigt.

5.3.2.1 Importera databasen till mySQL

För att importera databasen behövs förutom skriptet ovan även två kommandon köras.

5.3.2.2 create

Create används för att skapa en databas, eller en tabell.

SQL-skript för att skapa databas

create database Library;

5.3.2.3 use

När du har loggat in i en databashanterare så har du åtkomst till alla databaser kopplade till din användare. Du måste tala om exakt vilken databas du vill jobba med. För detta ändamål använder du "use".

SQL-skript för att välja databas

use Library;

5.3.2.4 kör skriptet

Nu är det bara att kopiera hela skriptet och köra det från SQL-läget i phpMyAdmin, förhoppningsvis får vi nu inga error och då har vi en databasstruktur importerad med sex tabeller som just nu är helt utan poster.

5.4 SQL - Modifiera data

Nu har vi en fungerande databas, hos mig heter den Library. Nu skall vi kika på ett antal sql-kommandon som är bra att kunna. Naturligtvis finns det många fler kommandon än de som anges nedan, men då hänvisar jag till andra referenser. Här fokuserar jag på saker som du behöver kunna för att komplettera det som görs automatiskt i phpMyAdmin.

5.4.1 Drop

Drop används för att ta bort en tabell eller en databas.

SQL-skript för att ta bort databas

drop database Library;

5.4.2 Insert

Insert använder vi för att populera, alltså lägga in data i en tabell.

SQL-skript för att lägga till en post

INSERT INTO 'Library'.'Person' ('personId',
   'personalIdentityNumber', 'name', 'address',
   'loneCardNr')
VALUES
   (NULL, '197511031234', 'Johan Hällgren', 'Mölnlycke', '310');

Det som händer här är att vi med nyckelordet INSERT talar om att vi skall lagra något i tabellen Person, som ligger i databasen Library. Vi visar också vilja attribut som skall få värden, här är det viktigt att attributnamn och värden har samma ordning i den första parentesen och i parentesen efter "VALUES", sker inte detta kommer data röras ihop och i värsta fall så får vi ett error.
Kolla på värdet för personId som är satt till NULL, det innebär att vi inte skriver in något värde, istället kommer databasen ge detta attribut ett automatiskt värde, i detta fall ett inkrementerat värde, alltså ett nummer högre än värdet på posten innan.

Det går att skriva denna kod lite kortare, det kan ibland vara skönt när vi sedan skall skicka koden från php. Här gäller det dock att vara noga att vi har skrivit attributen i samma ordning som de är registrerade i tabellen. Felmarginalen här är noll, så var säker på vad du gör.

5.4.3 Delete

Delete är ett kommando som gör precis vad vi tror, det tar bort en, eller flera poster, i en tabell.

SQL-skript för att ta bort en post

DELETE FROM Person WHERE personId = 1;

I koden ovan så tar vi bort posten som har personId = 1, vi kan välja att ta bort flera poster genom att t.ex. ta bort alla personer som bor i Mölnlycke.

SQL-skript för att ta bort poster baserat på villkor

DELETE FROM Person WHERE address = 'Mölnlycke';

Skriver du utan villkor (WHERE) så tar du bort alla personer i databasen.

Var försiktig med delete, så länge du inte använder transaktionshantering så går det inte att återskapa några borttagna poster.

5.4.4 Säkerhetskopiera

I phpMyAdmin finns möjligheten att ta ut hela databasen som ett sql-script vilket innebär att alla tabeller, nycklar, relationer, andra strukturer samt tabellernas innehåll kan exporteras och enkelt läggas in i en annan databashanterare eller lagras som en fil.

5.5 SQL - söka efter data

Jag har skapat 5 poster till tabellen Person i min databas. Vill du importera dem till din databas så är skriptet här:

SQL-skript för att lägga till flera poster till en tabell

INSERT INTO Person VALUES
(NULL, '197511031234', 'Johan Hällgren', 'Mölnlycke', 310),
(NULL, '198001011111', 'Anna Andersson', 'Göteborg', 311),
(NULL, '199012241414', 'Tom Tén', 'Mölndal', 351),
(NULL, '200110191234', 'Klas Sten', 'Göteborg', 354),
(NULL, '199912319999', 'Yngve Sen', 'Mölnlycke', 355);

5.5.1 Söka med *

Nu skall vi lära oss att söka i vår tabell Person.

SQL-skript för söka efter poster

SELECT * FROM Person;

Utskrift

* (stjärna) betyder här att vi hämtar alla attribut från tabellen Person.

Om vi bara vill ha personer som kommer från "Mölnlycke" i vårt resultat skriver vi så här;

SQL_skript för att hämta poster baserat på villkor

SELECT *
FROM Person
WHERE address = "Mölnlycke";

Att skriva på tre rader eller en enda rad spelar ingen roll, allt som skrivs innan semikolon kommer köras som en händelse.

SELECT * FROM Person WHERE address = "Mölnlycke";

Resultatet blir då så här;

Utskrift

SQL-skript för att söka poster med flera villkor

Vi kan slå ihop flera argument för att specificera vår lista. Då är det AND som skall användas;

SELECT *
FROM Person
WHERE address = "Mölnlycke"
AND loneCardNr < 350;

Resultatet blir då så här;

Utskrift

Vi kan naturligtvis också använda OR som operator;

SELECT *
FROM Person
WHERE address = "Mölnlycke"
OR loneCardNr < 350;

Resultatet blir då så här;

Utskrift

5.5.2 Välj vilka attribut som skall hämtas

Det är ju möjligt att hämta alla attribut och sedan bestämma mha php vad som skall visas eller användas, men att hämta mer än vad man behöver är ju onödigt. Bättre då att lära sig hur man hämtar bara det man vill ha.

SQL-skript för att välja attribut som skall visas

SELECT loneCardNr, name, address
FROM Person;

Den sökningen ger detta resultat;

Utskrift

Dags att sortera svaret...

SELECT loneCardNr, name, address
FROM Person
ORDER BY name ASC;

... med resultatet ....

Utskrift

ASC står för ascending och är 1-9 eller A-Ö, motsatsen är DESC som står för descending.

5.6 SQL - söka från flera tabeller

Att söka från en tabell har bara en viss mängd av svårighet, det blir klurigare när vi skall slå ihop flera tabeller för att ställa frågor. För att du själv skall kunna testa de exempel jag går igenom här så kan det vara bra att ha populerat databasen.

SQL-skript för att populera med exempeldata

-- Created by Vertabelo (http://vertabelo.com)
-- Script type: create
-- Scope: [tables, references, sequences, views, procedures]
-- Generated at Sun Mar 15 09:41:23 UTC 2015


-- tables
-- Table Author
CREATE TABLE Author (
    authorId int    NOT NULL AUTO_INCREMENT,
    author varchar(50)    NOT NULL ,
    CONSTRAINT Author_pk PRIMARY KEY (authorId)
);

-- Table Book
CREATE TABLE Book (
    bookId int    NOT NULL AUTO_INCREMENT,
    authorId int    NOT NULL ,
    languageId int    NOT NULL ,
    ISBN varchar(13)    NOT NULL ,
    title varchar(100)    NOT NULL ,
    CONSTRAINT Book_pk PRIMARY KEY (bookId)
);

-- Table Copy
CREATE TABLE Copy (
    copyId int    NOT NULL AUTO_INCREMENT,
    bookId int    NOT NULL ,
    serialNumber int    NOT NULL ,
    active bool    NOT NULL ,
    CONSTRAINT Copy_pk PRIMARY KEY (copyId)
);

-- Table Language
CREATE TABLE Language (
    languageId int    NOT NULL AUTO_INCREMENT,
    language varchar(30)    NOT NULL ,
    CONSTRAINT Language_pk PRIMARY KEY (languageId)
);

-- Table Person
CREATE TABLE Person (
    personId int    NOT NULL AUTO_INCREMENT,
    personalIdentityNumber varchar(12)    NOT NULL ,
    name varchar(50)    NOT NULL ,
    address varchar(50)    NOT NULL ,
    loneCardNr int    NOT NULL ,
    CONSTRAINT Person_pk PRIMARY KEY (personId)
);

-- Table PersonBook
CREATE TABLE PersonBook (
    personBookId int    NOT NULL  AUTO_INCREMENT,
    personId int    NOT NULL ,
    copyId int    NOT NULL ,
    dateForLone timestamp    NOT NULL ,
    dateForReturn date    NOT NULL ,
    dateReturned date    NULL ,
    CONSTRAINT PersonBook_pk PRIMARY KEY (personBookId)
);

-- foreign keys

-- Reference:  Book_Author (table: Book)
ALTER TABLE Book ADD CONSTRAINT Book_Author FOREIGN KEY
    Book_Author (authorId) REFERENCES Author (authorId);

-- Reference:  Book_Language (table: Book)
ALTER TABLE Book ADD CONSTRAINT Book_Language FOREIGN KEY
    Book_Language (languageId) REFERENCES Language (languageId);

-- Reference:  Copy_Book (table: Copy)
ALTER TABLE Copy ADD CONSTRAINT Copy_Book FOREIGN KEY
    Copy_Book (bookId) REFERENCES Book (bookId);

-- Reference:  PersonBook_Copy (table: PersonBook)
ALTER TABLE PersonBook ADD CONSTRAINT PersonBook_Copy
    FOREIGN KEY PersonBook_Copy (copyId)
    REFERENCES Copy (copyId);

-- Reference:  PersonBook_Person (table: PersonBook)
ALTER TABLE PersonBook ADD CONSTRAINT PersonBook_Person
    FOREIGN KEY PersonBook_Person (personId)
    REFERENCES Person (personId);

-- End of file.

Om du inte har lagt till personer till tabellen Person tidigare så får du avmarkera kodraderna 78-83.

5.6.1 Join

Join är nyckelordet för att slå ihop tabeller, via nycklar, och på det sättet få sammansatta resultat.

5.6.1.1 INNER JOIN

Inner join används när vi bara vill ha resultat som kopplas. Ett exempel är en lista på alla böcker samt författare.

SQL-skript för att koppla tabeller med INNER JOIN

SELECT a.author, b.title
FROM Book b
INNER JOIN author a
ON b.authorId = a.authorId
ORDER BY a.author ASC, b.title ASC;

Lägg märke till att jag har skapat alias för tabellerna för att kunna skriva en kortare kod. På rad 2 och 3 så talar jag om att jag vill skapa alias b för tabellen Book och alias a för tabellen Author.
Frågan ger följande resultat.

Utskrift

5.6.1.2 OUTER JOIN

Med outer join så hämtas och data som inte fullt ut är kopplat mellan tabeller. Jag visar på exempel i tre steg.

Steg 1. Jag vill veta hur många böcker det finns av varje språk i vårt biblioteket. Först tar vi fram alla böcker och vilket språk de är skrivna på.

SQL-skript för att koppla två tabeller

SELECT l.language, b.title
FROM Language l
INNER JOIN Book b
ON b.languageId = l.languageId;

Inga konstigheter med frågan, resultatet blir följande;

Utskrift

Steg 2. Nu skall vi gruppera antalet böcker på språk och sedan räkna hur många det finns av varje språk.

SQL-skript för att koppla två tabeller

SELECT l.language Language, COUNT(b.title) Quantity
FROM Language l
INNER JOIN Book b
ON b.languageId = l.languageId
GROUP BY l.language
ORDER BY Quantity DESC;

Här behövs det kanske en förklaring på några saker, på rad 1 döper jag om bägge kolumnerna i svaret till "Language" respektive "Quantity", detta gör jag mest för att det skall vara snyggare och enklare att jobba med när det kommer till php. Hade jag inte döpt om den beräknade kolumnen hade jag behövt ropa på ett index som heter "COUNT(b.title)" i php och det vill jag helst inte.
På rad 5 talar jag om vad som skall grupperas, och i detta fall vill jag gruppera på Language. Om jag inte grupperar så kan jag heller inte räkna på någon kolumn.
På rad 6 talar jag om att jag vill sortera listan efter antal böcker på skrivet språk från högst till lägst.
Resultatet blir så här.

Utskrift

Jättefin lista, men det skrivs inte ut att det finns 0 böcker på franska, i detta fallet har jag ju faktiskt skapat möjlighet i vårt bibliotek att lagra franska böcker. Ett annat exempel då detta skulle vara intressant är när jag vill skriva ut en lista på alla våra registrerade låntagare och hur många böcker de har lånat senaste halvåret. Vill jag bara ha ut de som lånat böcker så funkar INNER JOIN, men vill jag ha en lista på alla måste jag istället använda OUTER JOIN. Vi testar och ser hur det skulle se ut i vårt exempel.

SQL-skript för att koppla två tabeller

SELECT l.language Language, COUNT(b.title) Quantity
FROM Language l
LEFT OUTER JOIN Book b
ON b.languageId = l.languageId
GROUP BY l.language
ORDER BY Quantity DESC;

Den enda skillnaden ser vi på rad 3, här står det LEFT OUTER JOIN istället för INNER JOIN och det betyder att vi även skall ta med de okopplade värdena från tabellen Language, som i detta fall anses som den vänstra tabellen, den kommer ju först. Hade vi velat skriva ut okopplade värden från den högra tabellen, i detta fall Book, så hade resultatet blivit exakt som när vi körde INNER JOIN.
Resultatet blir då.

Utskrift

Jag förstår att detta blir lite rörigt. Det är mycket nytt, vi skall både skrapa på ytan samtidigt som ni behöver kunna vissa saker. Mest lär ni er genom att hamna i en situation där du måste skriva en viss SQL-sats och sedan prova dig fram eller leta efter tips i något referensverk, t.ex. W3schools.com SQL Tutorial. Vi kommer i en tutorial jobba med att bygga upp en databas och sedan skapa de SQL-satser som behövs, så det kommer mer tillfällen att träna på detta. Tveka inte att fråga.

5.7 SQL - Vyer

En vy fungerar som en virtuell tabell. Om vi ofta ställer en SQL-fråga som använder data från flera olika tabeller så blir denna SQL-fråga ofta ganska så stor, då kan vi välja att skapa en vy istället, vilket gör att vyn innehåller SQL-frågan och vi kan skicka en enklare SQL-sats till databasen. Vi visar med ett exempel.

Vi tar exemplet ovan där vi listade antalet böcker på varje språk, den SQL-satsen var på 6 rader, det går naturligtvis att skriva väldigt mycket mer avancerade sql-satser, t.ex. lista alla utlånade böcker som ännu inte är tillbakalämnade och ge alla information om de enskilda böckerna inklusive språk och författarens namn, men vi nöjer oss med ett enklare exempel.

Vi börjar med att skapa vyn, det innebär att vi lagrar sql-kommandot i databasen.

CREATE VIEW LanguageQuantity AS
SELECT l.language Language, COUNT(b.title) Quantity
FROM Language l
LEFT OUTER JOIN Book b
ON b.languageId = l.languageId
GROUP BY l.language
ORDER BY Quantity DESC;

Samma SQL-sats som tidigare förutom första raden där vi talar om att sql-satsen skall skapa en vy. Denna vyn kommer nu ligga i databasen och när vi vill ställa en fråga till vyn så skriver vi bara följande kod.

SELECT * FROM LanguageQuantity;

Det som då händer är att resultatet kommer se likadant ut som tidigare.

Utskrift

Det går naturligtvis att ge vyn vissa argument också....

SELECT * FROM LanguageQuantity WHERE Quantity > 0;

.... med resultatet....

Utskrift

Det gick ju smidigt. Innebär detta att vi alltid skall skapa vyer för varje SQL-sats som skall användas i ett projekt? Nej, använd det där det behövs. Vem som bestämmer när det behövs? Det gör du själv. Fundera på vilket som är smidigast och lös problemet på det sättet.

5.8 Uppgift

Uppgift inloggningsapplikation

Du skall fortsätta jobba med inloggningsapplikationen. Du skall nu implementera din databasmodell i mySQL samt populera databasen med användare. Skriv också den/de sql-satser som du kan behöva för att kunna jämföra om användarnamn och lösenord från formuläret matchar det som finns i databasen.

De frågor som du absolut behöver kunna skapa är;

  • Skapa en användare. (INSERT)
  • Sök efter en användare med användarnamn och lösenord. (SELECT)
  • Övriga frågor som du kan tänka dig behöva utifrån din egen lösning.

Frågor som kan vara bra att skapa är;

  • Uppdatera information om en användare. (UPDATE)
  • Ta bort en användare. (DELETE)

När du anser dig färdig med denna del av momentet, gör en export av din databas, lägg upp databasdumpen, dina sql-satser och utvärdera momentet. Det går bra att skriva detta i samma inlägg som du använde förra gången.

Mitt tips är att använda någon kodplugin i WordPress för att få till snyggare koder. Det finns ett antal olika att välja på men håll koll på att de är hyggligt aktiva (vissa har inte uppdaterats på några år), lämpliga sökord är syntax, code, highlight.

5.9 phpMyAdmin

Det finns flera olika sätt att jobba mot en databas, denna kurs bygger på MySQL och då finns det ett ypperligt verktyg som följer med i XAMPP och som är förinstallerat på de flesta webbhotel. Verktyget heter phpMyAdmin och nås via XAMPPs webbstartsida, eller via http://localhost/phpmyadmin/ (http://localhost:8888/phpmyadmin/ om du kör MAMP).

3 filmer om hur du använder phpMyAdmin [klicka för att visa]

5.10 Databas kopplas till PHP

Dags att kika lite på hur det fungerar när vi skall koppla databasen till de PHP-applikationer som vi skall bygga.

5.10.1 PDO (PHP Data Objects)

Det finns olika sätt att via PHP koppla sig mot en MySQL-databas. Om du använder äldre webbapplikationer så används ofta gränsnitten mysql eller mysqli. mysql var den första varianten och den är vidareutvecklad till mysqli, i står för improved. Fördelen med mysql(i) är att de är förhållandevis enkla att jobba med, nackdelen är att de har sämre säkerhet och fungerar bara på databasen mysql. Vill du använda en annan databas så måste du använda andra funktioner som kan se annorlunda ut och funderar lite annorlunda.

PHP PDO, PHP Data Object, är ett gränssnitt som är objektorienterat, det har högre inbyggd säkerhet och det jobbar på samma sätt mot olika databaser vilket innebär att du som utvecklare inte behöver bry dig om databasen är mySQL, SQLServer, Oracle eller något annat.

Det sägs också att vid någon kommande större uppdatering av PHP, så kommer de äldre gränssnitten att fasas ut. Redan nu har utvecklare jobbat att göra för att fixa ordning på system som använder sig av vissa mysql-funktioner som inte längre har stöd, utan behöver bytas ut mot motsvarande mysqli-funktioner.

5.10.2 Koppla upp mot databas

Vi börjar med att sätta upp ett antal variabler, så att det är lätt att andra data för en uppkoppling. Sedan försöker vi skapa en databasuppkoppling, fungerar detta så har vi skapat ett objekt. Skulle det gå fel så tar vi hand om detta och meddelar användaren att något har gått fel.

Kodexempel: Koppla upp mot databasen

<?php
# Variabler
$host = "localhost";      // Den server där databasen ligger
$user = "root";           // Ditt användarnamn
$pwd  = "root";           // Ditt lösenord
$db   = "databasensnamn"; // Databasen vi vill jobba mot

# dsn - data source name
$dsn = "mysql:host=".$host.";dbname=".$db;

# Inställningar som körs när objektet skapas
$options  = array(
  PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'",
  PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_EMULATE_PREPARES, false);

# Skapa objektet eller kasta ett fel
try {
  $pdo = new PDO($dsn, $user, $pwd, $options);
}
catch(Exception $e) {
    die('Could not connect to the database:<br/>'.$e);
}
?>

På rad 13-15 så görs några inställningar som kommer gälla för databasuppkopplingen;
Rad 13. Här bestäms teckenkoden för objektets uppkoppling.
Rad 14. Här bestäms vilken nivå av felkoder som skall visas när dessa uppstår.
Rad 15. Här bestäms om SQL-satserna måste prepareras innan de körs.

5.10.3 Ställ fråga mot databas

Nu när vi har ett databasobjekt är det detta objekt vi skall jobba med för att skapa och köra SQL-frågor.

Kodexempel: Koppla upp mot databasen

<?php
require_once('databasuppkopplingen.php');
$sql = "SELECT * FROM user";
$stm = $pdo->prepare($sql);
$stm->execute();
$res = $stm->fetchAll(PDO::FETCH_ASSOC);
?>

Variabeln $sth är ett objekt av klassen PDO Statement och är objektet av den sql-fråga som vi skapar på rad 3 och som vi förbereder på rad 4. Just att köra prepare gör att vi kan dra nytta av inbyggd säkerhet för att undvika sabotage av vår databas.

På rad 5 så exekveras frågan och på rad 6 hämtar vi ut vårt resultset från databasen och lagrar detta i variabeln $res. Vi använder argumentet PDO::FETCH_ASSOC för att välja hur våra index skall namnges i arrayen. I detta fallet så får indexet samma namn som attributet i sql-frågan, jag tycker att det blir lättare att arbeta med det om man vet att username lagras i index username och inte med ett sifferindex som kan vara 0, 1 eller något annat. I just detta exempel skulle det fått värdet 1 eftersom det är den andra kolumnen i vårt resultset.

Kanske läge att förklara vad som händer på rad 2. Jag har valt att lägga databasuppkopplingen i en egen fil så kan jag läsa in den på de sidor där den behövs. Funktionen require_once() används för att säkerställa att denna filen bara läses in en gång, skulle man inkludera samma fil från flera olika delar av programmet, vilket man riskerar att göra i större filer, så ser denna funktionen till att den inte läses in igen om den redan finns inläst. Require_once och include_once gör egentligen samma sak, förutom när det blir något fel med inläsningen och ett error skall genereras, då skickar include/include_once en varning medan require/require_once stoppar scriptet och skickar ett "fatal error". I detta fallet så vill vi ju inte fortsätta scriptet om filen med databasinformation inte finns.

Om vi vill skriva ut innehållet i variabeln $res så gör vi det med print_r() och får följande utskrift.

Utskrift av innehållet i $res

Array
(
    [0] => Array
        (
            [userId] => 1
            [username] => admin
            [password] => admin
        )
    [1] => Array
        (
            [userId] => 2
            [username] => user
            [password] => user
        )
    [2] => Array
        (
            [userId] => 3
            [username] => johan
            [password] => johan
        )
)

Här ser du att vi får en tvådimensionell array som vi enklast loopar igenom med foreach(). För att skriva ut innehållet i $res så att det blir läsligt så kan du skriva på detta sätt.

Kodexempel: Formaterad utskrift av $res

<?php
# Create the table from resultset
$table ="<table><tr><th>userId</th><th>username</th><th>password</th></tr>";
foreach ($res as $row) {
  $table .= "<tr>";
  $table .= "<td>{$row['userId']}</td>";
  $table .= "<td>{$row['username']}</td>";
  $table .= "<td>{$row['password']}</td>";
  $table .= "</tr>\n";
}
$table .= "</table>";
echo $table;
?>

Detta kodexempel kommer ge följande utskrift.

Formaterad utskrift av $res

userIdusernamepassword
1adminadmin
2useruser
3johanjohan

5.10.4 Ställ fråga mot databas med parameter

Ofta kopplar vi ju frågorna mot databasen utifrån vilken information vi vill fråga efter, att alltid fråga efter alla användare i vårt system är ju inte det enda vi vill göra. Om vi vill fråga efter en specifik användare så skriver vi följande kod.

Kodexempel: Databasfråga med parameter

<?php
$username = trim($_POST['username']);

$sql = "SELECT * FROM user WHERE username = :username;";
$stm = $pdo->prepare($sql);
$stm->execute(array('username' => $username));
$res = $stm->fetch(PDO::FETCH_ASSOC);

echo "<pre>";
print_r($res);
echo "</pre>";

echo "<p>Du har angivit användarnamn: " .$res['username']."</p>";
?>

Skillnaden i detta exempel mot det som du har sett tidigare är;

Parameter i SQL-satsen markeras med :, mitt tips är att inte krångla till det utan att låta alla variabler/parameterar som gör samma sak heta samma. När det gäller användarnamn så heter attributet username i tabellen user, i formuläret heter variabeln 'username' ($_POST['username']), parametern heter :username och den variabeln som jag skapar för att hämta data från formuläret heter $username. Det blir enklare att få ihop allt om vi enkelt använder rätt namn.

Alla parametrar kopplas när vi kör execute genom att skapa en array med de parametrar som skall användas. Här kan du ha många parametrar om det är en specifik fråga.

Vet jag att frågan bara skall ge ett svar så kan jag använda mig av fetch() istället för fetchAll() vilket innebär att jag istället för en tvådimensionell array som svar få en enkel array. På det sättet behöver jag inte loopa igenom utan kan använda arrayen direkt.

Detta kodexempel kommer ge följande utskrift.

Utskrift av innehållet i $res

Array
  (
      [userId] => 1
      [username] => admin
      [password] => admin
  )
  

Du har angivit användarnamn: admin

5.10.5 Insert, update och delete

När vi skickar andra SQL-satser till databasen för att skapa, uppdatera eller ta bort poster så skiljer det sig inte så mycket från det vi tidigare har gjort. Den största skillnaden är att vi inte får något resultset tillbaka som svar.

Kodexempel: Lägg till en ny användare

<?php
$sql = "INSERT INTO user (userId, username, password) VALUES (NULL, 'new', 'new');";
$stm = $pdo->prepare($sql);
$stm->execute();
$lastId = $pdo->lastInsertId();

echo "<p>Du har lagt till en användare med id: ".$lastId."</p>";
?>

Det som är mest intressant här är att vi frågar vårt pdo-objekt vilket id den nya användaren har fått. Detta fungerar endast om vi redan har talat om att det skall vara en automatisk räknare så att databasen själv tar hand om detta.

Detta kodexempel kommer ge följande utskrift.

Utskrift vid insert av ny användare

Du har lagt till en användare med id: 4

5.11 Uppgift

Uppgift inloggningsapplikation kopplad till databas

Du skall nu bygga vidare på uppgiften i Moment04 och koppla din inloggningsapplikation till den databas som du har designat och implementerat tidigare i detta moment.

Uppgiften är att skapa en fullt fungerande applikation där det går att logga in som användare. Väl inloggad skall du kunna skapa nya användare, ta bort befintliga användare och uppdatera användarnamn och lösenord på befintliga användare.

Hjälp tutorial [klicka för att visa]

Inlämningsinstruktioner

Innan inlämning bör du se till att följande saker är gjorda.

  • Testa applikationen grundligt så att den fungerar som det är tänkt.
  • Kika över koden så att den ser bra ut, ta bort det som skall tas bort och snygga till.
  • Lägg upp applikationen i en egen katalog på din domän, se till att CSource finns tillgängligt och att den är länkad på lämpligt sätt.
  • Länka till applikationen från din samlingssida.
  • Skapa ett inlägg på din WordPress där du utvärderar arbetet, gör en reflektion kring svårigheter och hur du har tänkt när du löst dessa. Länka här också till applikationen samt till CSource.
  • Har du tidigare skrivit inlägg i din WordPress med ER-modell och databasmodell så länka till dessa inlägg. Har du inte gjort det tidigare är det hög tid nu.

Uppgift inloggningsapplikationen, fördjupande uppgift

Om vi fortsätter utveckla applikationen vidare så finns det följande saker som du kan tänka på;

  • Gör det möjligt att spärra en användare, alltså inte ta bort hen från tabellen men flagga på något sätt så att användaren kan eller inte kan logga in.
  • Utöka så att användarna kan ha olika typer av rättigheter, kanske en vanlig user och en admin.
  • Denna inloggningsapplikation kommer vi senare bygga vidare på/bygga ut så tänk på att göra den så generell som möjligt. Den skall senare användas för ett bankkonto, ett enkelt CMS och/eller andra liknande applikationer. Ett problem som elever inte tänker på är att om samma applikation är grund för flera andra applikationer så finns det risk att användaren loggar in i en applikation och får full access även till en annan applikation.