Webbserverprogrammering01 [wesweb01]

Moment05 - Databasteori & SQL

Introduktion

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 vi 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 kommer hållas två genomgångar för att klara av detta. Uppgiften är också tvådelad men vi jobbar med samma databas så det blir mer en stor uppgift fast i två mindre delar.

Momentets mål

I varje moment så jobbar vi mot ett eller flera mål som skolverket har satt upp i varje kurs.

Centralt innehåll

  • Webbserverns och dynamiska webbplatsers funktionalitet.
  • Utvecklingsprocessen för ett webbtekniskt projekt. Målsättningar, planering, systemering, kodning, dokumentation och uppföljning.
  • Dokumentation av utvecklingsprocess och färdig produkt, inklusive kod och mjukvarugränssnitt.
  • En översikt över olika lösningar eller språk som finns för att skapa dynamiska webbplatser.
  • Grundfunktionen i ett programmeringsspråk för dynamiska webbplatser.
  • Datalagring, i relationsdatabas eller med annan teknik.
  • Teckenkodning. Begrepp, standarder och handhavande.
  • Kodning och dokumentation enligt vedertagen praxis för den teknik som används i sammanhanget.

Terminiologi

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 storlekt 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.

Databasmodellering

Hela denna delen gjordes första gången som en stor genomgång på tavlan 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 lades på denna sidan. 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. Kanske skrivs den om någon gång, kanske inte...

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

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.

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äms 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.

Datamodell

ER-diagramet 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.

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 nu 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 av typen mySql. Scriptet kollar vi på i senare moment.

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 at 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 och i datamodellen.

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 jag jobbade med moment06 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.

Första uppgiften

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 detta moment, lägg upp ditt ER-diagram och din databasmodell samt skriv en utvärdering på din sida.

Del 2

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.

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.

Verktyg

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 MAMP och som är förinstallerat på de flesta webbhotel. Verktyget heter phpMyAdmin och nås via MAMPs webbstartsida.

Jag har spelat in tre filmer där jag använder phpMyAdmin och visar på de viktigaste sakerna som du kommer använda databasen till.

Create

Insert och delete

select, update och joins

Importera databas

Vi har redan byggt upp en databas i verktyget Vertabelo 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.

För att importera denna databasen så gå in i phpMyAdmin, skapa en ny databas som du döper till något lämpligt som beskriver vad databasen skall innehålla information om, t.ex. bibliotek, som vi har jobbat med i det stora exemplet.
När databasen är skapad så går du till SQL-fliken och klistar in hela koden. Välj sedan att köra SQL-satsen, nu kommer alla tabeller och relationer att skapas.

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

-- 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.

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ärnyckar 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 måste kunnan skapa tabeller, attribut, nycklar och kopplingar för dina databaser till dina applikationer. Hur du gör det är inte lika viktigt.

Importera databasen

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

create

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

create database Library;

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".

use Library;

Kör scriptet

Nu är det bara att kopiera hela scriptet 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.

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.

drop

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

drop database Library;

insert

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

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 nyckelorder 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 ttributnamn 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.

INSERT INTO Person VALUES (NULL, '197511031234',
    'Johan Hällgren', 'Mölnlycke', '310');

delete

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

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.

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

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

Säkerhetskopiera

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

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 scriptet här:

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

Om du redan har importerat personer i tabellen så byt ut personId mot "NULL" så kan du importera personer utan att du får error att du försöker göra "Duplicate entry" på en Primary Key.

Sök med *

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

SELECT * FROM Person;

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

personId personalIdentityNumber name address loneCardNr
1 197511031234 Johan Hällgren Mölnlycke 310
2 198001011111 Anna Andersson Göteborg 311
3 199012241414 Tom Tén Mölndal 351
4 200110191234 Klas Sten Göteborg 354
5 199912319999 Yngve Sen Mölnlycke 355

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

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

Att skrova 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;

personId personalIdentityNumber name address loneCardNr
1 197511031234 Johan Hällgren Mölnlycke 310
5 199912319999 Yngve Sen Mölnlycke 355

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;

personId personalIdentityNumber name address loneCardNr
1 197511031234 Johan Hällgren Mölnlycke 310

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.

SELECT loneCardNr, name, address
FROM Person;

Den sökningen ger detta resultat;

loneCardNr name address
310 Johan Hällgren Mölnlycke
311 Anna Andersson Göteborg
351 Tom Tén Mölndal
354 Klas Sten Göteborg
355 Yngve Sen Mölnlycke

Dags att sortera svaret...

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

... med resultatet ....

loneCardNr name address
311 Anna Andersson Göteborg
310 Johan Hällgren Mölnlycke
354 Klas Sten Göteborg
351 Tom Tén Mölndal
355 Yngve Sen Mölnlycke

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.

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Databas: `Library`
--

--
-- Dumpning av Data i tabell `Author`
--
INSERT INTO `Author` (`authorId`, `author`) VALUES
(1, 'Lars Kepler'),
(2, 'Anders Jallai');

--
-- Dumpning av Data i tabell `Language`
--
INSERT INTO `Language` (`languageId`, `language`) VALUES
(1, 'Svenska'),
(2, 'Engelska'),
(3, 'Tyska'),
(4, 'Franska');

--
-- Dumpning av Data i tabell `Book`
--
INSERT INTO `Book` (`bookId`, `authorId`, `languageId`,
    `ISBN`, `title`) VALUES
(1, 1, 1, '9789170018831', 'Paganinikontraktet'),
(2, 1, 2, '9780007478262', 'The Nightmare'),
(3, 1, 3, '9783785724286', 'Paganinis Flucht'),
(4, 1, 1, '9789100136758', 'Stalker'),
(5, 1, 2, '9789100136741', 'Stalker'),
(6, 2, 1, '9789198131550', 'I rikets tjänst'),
(7, 2, 1, '9789198131505', 'Bilderbergmötet'),
(8, 2, 1, '9789198131536', 'Spionen på FRA 2.0'),
(9, 2, 1, '9789174610222', 'Landsförädaren');

--
-- Dumpning av Data i tabell `Copy`
--
INSERT INTO `Copy` (`copyId`, `bookId`, `serialNumber`,
    `active`) VALUES
(1, 1, 1, 1),
(2, 1, 2, 1),
(3, 1, 3, 1),
(4, 1, 4, 1),
(5, 1, 5, 1),
(6, 2, 1, 1),
(7, 2, 2, 1),
(8, 2, 3, 1),
(9, 2, 4, 1),
(10, 3, 1, 1),
(11, 3, 2, 1),
(12, 3, 3, 1),
(13, 4, 1, 1),
(14, 5, 1, 1),
(15, 5, 2, 1),
(16, 5, 3, 1),
(17, 5, 4, 0),
(18, 5, 5, 1),
(19, 6, 1, 1),
(20, 6, 2, 1),
(21, 6, 3, 1),
(22, 7, 1, 1),
(23, 7, 2, 1),
(24, 7, 3, 1),
(25, 7, 4, 1),
(26, 7, 5, 1),
(27, 8, 1, 1),
(28, 8, 2, 1),
(29, 9, 1, 1);


--
-- Dumpning av Data i tabell `Person`
--
INSERT INTO `Person` (`personId`, `personalIdentityNumber`,
    `name`, `address`, `loneCardNr`) VALUES
(1, '197511031234', 'Johan Hällgren', 'Mölnlycke', 310),
(2, '198001011111', 'Anna Andersson', 'Göteborg', 311),
(3, '199012241414', 'Tom Tén', 'Mölndal', 351),
(4, '200110191234', 'Klas Sten', 'Göteborg', 354),
(5, '199912319999', 'Yngve Sen', 'Mölnlycke', 355);

--
-- Dumpning av Data i tabell `PersonBook`
--
INSERT INTO `PersonBook` (`personBookId`, `personId`,
    `copyId`, `dateForLone`, `dateForReturn`, `dateReturned`)
    VALUES
(1, 1, 4, '2015-02-09 20:12:55', '2015-03-02', NULL),
(2, 1, 4, '2015-02-09 20:13:21', '2015-03-15', NULL),
(3, 1, 21, '2015-02-18 20:13:51', '2015-03-05',
    '2015-03-06'),
(4, 2, 15, '2015-03-15 20:15:25', '2015-02-02', NULL);

JOIN

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

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.

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.

author title
Anders Jallai Bilderbergmötet
Anders Jallai I rikets tjänst
Anders Jallai Landsförädaren
Anders Jallai Spionen på FRA 2.0
Lars Kepler Paganinikontraktet
Lars Kepler Paganinis Flucht
Lars Kepler Stalker
Lars Kepler Stalker
Lars Kepler The Nightmare

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 bibliotet. Först tar vi fram alla böcker och vilket språk de är skrivna på.

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;

language title
Svenska Paganinikontraktet
Svenska Stalker
Svenska I rikets tjänst
Svenska Bilderbergmötet
Svenska Spionen på FRA 2.0
Svenska Landsförädaren
Engelska The Nightmare
Engelska Stalker
Tyska Paganinis Flucht

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.

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äknande 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.

Language Quantity
Svenska 6
Engelska 2
Tyska 1

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 OUTHER JOIN. Vi testar och ser hur det skulle se ut i vårt exempel.

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å.

Language Quantity
Svenska 6
Engelska 2
Tyska 1
Franska 0

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. Tveka inte att fråga.

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 anvancerade 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.

Language Quantity
Svenska 6
Engelska 2
Tyska 1
Franska 0

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

SELECT * FROM LanguageQuantity WHERE Quantity > 0;

.... med resultatet....

Language Quantity
Svenska 6
Engelska 2
Tyska 1

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.

Andra uppgiften

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-satsre 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.

När du anser dig färdig med detta moment, gör en export av din databas, lägg upp databasdumpen, dina sql-satser och utvärdera momentet.

Redovisning av hela momentet

Nu skall du se till att du har redovisat bägge uppgifterna. Exakt hur du väljer att redovisa är upp till dig själv, antigne gör du allt i WordPress, eller så skriver du delar i WordPress och länkar resten eller så gör du på helt annat sätt. Jag vill dock kunna börja ifrån ett inlägg i WordPress och sedan länkas därifrån vid behov.

Skriv också lite text om själva uppgifterna och momentet i stort, hur har det varit att jobba med detta området? Tycker du att det är svårt eller börjar saker och ting falla på plats? Kommentera gärna också det material som ni får av mig, är det något jag behöver tänka på för att göra det bättre eller tydligare?

Det finns kodplugins i WP som är intressanta att använda, kika gärna på wp.kursolle.se för exempel.