Tietokannat ja PHP

Tietokannan luominen
Tietokantayhteys
Kyselyt
Lisääminen
Poistaminen
Päivittäminen
Monimutkaisemmat tulostukset
Oliot tietokantojen käsittelysssä
Transaktiot
- Automaattiset avainkentät
Lisätietoa

Demojen aiheena on tietokantojen käsittely PHP-kielellä.

Tietokannan luominen

Demoissa käytetään SQLite3-tietokantaa jota voidaan hallinnoida suoraan komentoriviltä käynnistyvällä työkalulla. Samaan tapaan toimii myös hienompien tietokannan hallintajärjestelmien hallinnointi (Postgresql, mysql).

Ota SSH-yhteys people.cc.jyu.fi:hin
Tee uusi www:ssä näkyvä kansio esim. demo7

Kopioi itsellesi resepti.sql-tiedosto wget-komennolla:

wget http://appro.mit.jyu.fi/sovellukset/demot/demo7/resepti.sql

Luo uusi SQLite3-tietokanta komennolla:
```
sqlite3 resepti
```
SQLite luo nyt uuden resepti-tiedoston ja tekee siitä tietokannan
Voit tutustua työkalun osaamiin komentoihin kirjoittamalla .help
Luo varsinaisen tietokannan taulut komennolla:
```
.read resepti.sql
```
SQLite lukee resepti.sql-tiedoston ja suorittaa sen sisältämät SQL-komennot. Älä huolestu DROP TABLE-lauseiden virheilmoituksista.
Tutki tietokantasi rakennetta seuraavilla komennoilla: .tables, .schema resepti ja .schema ruokalaji
Tutki mitä taulut sisältävät yksinkertaisilla SQL-kyselyillä:
```
SELECT * FROM Resepti;SELECT * FROM Ruokalaji;
```
.quit-komennolla pääset takaisin unix-shelliin

Tietokantayhteys

Sovelluksen alussa pitää luoda tietokantaan yhteys jota käytetään koko suorituksen ajan. Yhteyden avaaminen on aina raskas prosessi joten turhaan yhteyttä ei pidä aukoa ja sulkea. Suurissa sovelluksissa tietokantayhteys pidetään auki pitempään ja samaa yhteyttä käytetään uudelleen ja uudelleen.

Tarkista mikä on hakemistopolku edellä luomaasi tietokantaan pwd-komennolla.
Aloita uuden PHP-ohjelman kirjoittaminen esim. resepti.php

Avaa tietokantayhteys:

try {
 // tarkista että polku tietokantaan on oikea
 $dbh = new PDO('sqlite:/home3/368/tjlahton/public_html/php/demo7/resepti');
}
catch ( PDOException $e ) {
  die ( $e->getMessage() );
}

Tietokantayhteyden avaamisen onnistuminen pitää aina tarkistaa try-catch-lohkolla ja virheen tapahtuessa reagoida sopivalla tavalla.

Kokeile suorittaa php-ohjelmasi komentoriviltä niin näet tuleeko virheitä
Anna tietokantaan ja kansioon jossa tietokantatiedosto sijaitsee kaikille kirjoitusoikeus (chmod a+w resepti ja chmod a+w .) niin tietokantaan kohdistuvat muutosoperaatiot toimivat jatkossa myös WWW:n kautta
Jos tietokannan luomiseen liittyvä SQL tuottaa hankaluuksia niin voit perehtyä asiaan henkilökohtaisen tiedonhallinnan perusteet -kurssin luentosivulta

Kyselyt

Tietokantaan kohdistuvat kyselyt ovat jokaisen sovelluksen perusta. Jos SQL-kyselyiden kirjoittaminen tuottaa suuria hankaluuksia voit harjoitella niitä henkilökohtaisen tiedonhallinnan perusteet -kurssin demotehtävillä.

SQL-kysely valmistellaan (prepare) tietokannassa jolloin tietokantamoottori voi jo valmiiksi tallentaa kyselyn optimoituna ja sen suorittaminen jatkossa on nopeaa. Valmisteltu kysely suoritetaan (execute) ja käydään kyselyn tulos läpi

Valmistele yksinkertainen kysely:

$sql = $dbh->prepare('SELECT * FROM resepti');

Suorita kysely:
```
$ok = $sql->execute();
```

Tarkista onnistuiko kyselyn suorittaminen:

if ( !$ok ) print_r( $sql->errorInfo() );

Käy silmukassa läpi kyselyn palauttamat tietueet:


// fetch-metodi palauttaa aina uuden rivin taulukkona
while ($row = $sql->fetch(PDO_FETCH_ASSOC)) {
// käydään läpi taulukko (tietue) ja tulostetaan jokaisen kentän nimi ja arvo
    foreach ( $row as $key => $arvo ) {
        print $key . " = " . $arvo . "\n";
    }
}

Kokeile suorittaa ohjelmasi komentoriviltä

Normaalisti SQL-kyselyn palauttaman tuloksen kenttien nimet ovat tiedossa ja voidaan tietysti viitata suoraan haluttuihin kenttiin. Muuta edellinen silmukka seuraavanlaiseksi ja kokeile suorittaa:

while ($row = $sql->fetch(PDO_FETCH_ASSOC)) {
    print "Nimi = " . $row["Nimi"] . "\n";
    print "Kuvaus = " . $row["Kuvaus"] . "\n";
    print "Henkilomaara = " . $row["Henkilomaara"] . "\n";
    print "ReseptiID = " . $row["ReseptiID"] . "\n";
    print "RuokalajiID = " . $row["RuokalajiID"] . "\n";
}

Yleensä kyselyjä pitää rajoittaa sopivilla ehdoilla jotka muuttuvat ohjelman suorituksen mukana. Tällöin kyselystä tulee dynaaminen. Muuta edellistä kyselyä seuraavanlaiseksi:

// valmistellaan kysely ja sijoitetaan dynaamisen arvon tilalle :lkm-muuttuja
// haetaan reseptit jotka ovat halutulle henkilömäärälle
$sql = $dbh->prepare('SELECT Nimi FROM Resepti WHERE henkilomaara = :lkm');
// sijoitetaan PHP:n muuttujaan haluttu arvo, joka on saatu esim. käyttäjän syötteenä lomakkeelta
$lkm = 4;
// sidotaan muuttuja SQL-lauseessa olleeseen muuttujaan
$sql->bindParam( ":lkm", $lkm );

Korjaa myös tulostussilmukka sopivaksi ja kokeile komentoriviltä. Kokeile muuttaa $lkm-muuttujan arvoa.

Käytettäessä valmisteltuja kyselyjä ja sidottuja muuttujia ei tarvitse itse välittää siitä onko tietokannan kentän tyyppi numeerinen, merkkijono tai jotain muuta. Tietokanta pitää itse huolen siitä, että sijoitettavat arvot ovat tarpeen mukaan heittomerkkien sisällä tai ilman.

Lisääminen

Tietojen lisääminen tapahtuu valmisteltujen kyselyjen avulla aivan samaan tapaan kuin kyselyjenkin tekeminen:

Lisätään tietokantaan uusi resepti:


$sql = $dbh->prepare('INSERT INTO resepti
                        (Nimi, Kuvaus, Henkilomaara, RuokalajiID, reseptiID)
                        VALUES (:nimi, :kuvaus, :henkilomaara, :ruokalaji, :reseptiID )');

// tarkistetaan myös mahdolliset virheet.
// huom. virhe pitää tässä kohtaa pyytää tietokantayhteydeltä 
if ( !$sql ) print_r( $dbh->errorInfo() );

$nimi = "Makaronilaatikko";
$kuvaus = "Perusruoka";
$henkilomaara = 6;
$ruokalaji = 2;
$reseptiID = 10;

$sql->bindParam( ":nimi", $nimi );
$sql->bindParam( ":kuvaus", $kuvaus );
$sql->bindParam( ":henkilomaara", $henkilomaara );
$sql->bindParam( ":ruokalaji", $ruokalaji );
$sql->bindParam( ":reseptiID", $reseptiID );

$ok = $sql->execute();

if ( !$ok ) print_r( $sql->errorInfo() );

// tämän pitäisi tulostaa tieto moneenko tietueeseen operaatio on kohdistunut
// mutta ei toimi kuin vasta PHP5.1:ssä ja uudemman SQLiten kanssa
print $sql->rowCount();

Kokeile suorittaa saman reseptin lisääminen uudelleen. Saat virheilmoituksen, miksi?

Poistaminen

Poistetaan edellä lisätty resepti

$sql = $dbh->prepare('DELETE FROM resepti
	WHERE reseptiID = :id');

if ( !$sql ) print_r( $dbh->errorInfo() );

$reseptiID = 10;

$sql->bindParam( ":id", $reseptiID );

$ok = $sql->execute();

if ( !$ok ) print_r( $sql->errorInfo() );

Kokeile onnistuuko poistaminen

Yritä poistaa ruokalajeja. Onnistuuko se ilman ongelmia? Voitko vapaasti poistaa minkä tahansa ruokalajin?

Päivittäminen

Valmisteltujen kyselyjen edut tulevat esiin parhaiten kun samaa kyselyä joudutaan suorittamaan useaan kertaan. Päivitetään useaa reseptiä:

// kokeillaan muuttaa reseptien henkilomäärää
// käytetään toisenlaista prepare-muotoa ?-merkkien avulla
$sql = $dbh->prepare('UPDATE resepti
        SET henkilomaara = ?
        WHERE reseptiID = ?');

if ( !$sql ) print_r( $dbh->errorInfo() );

// sijoitetaan taulukkoon kaikki henkilomaarat ja
// reseptien tunnisteet jotka aiotaan muuttaa
$reseptit = array(1 => 1,
        2 => 2,
        3 => 4,
        4 => 3,
        5 => 2,
        6 => 3);

foreach ( $reseptit as $resepti => $maara) {
 // tehdään taulukko jossa on henkilomäärä ja reseptin tunniste
 print $resepti . " " . $maara . "\n";
 $arvot = array($maara, $resepti ) ;

 // bindParam-kutsua ei tarvita ollenkaan vaan sitominen
 // tapahtuu executen yhteydessä $arvot-taulukon arvoihin
 $ok = $sql->execute($arvot);
 if ( !$ok ) print_r( $sql->errorInfo() );

}

Monimutkaisemmat tulostukset

Kyselyjen kohdistuessa useampaan tauluun voi tulla kiusaus kirjoittaa silmukka, joka tekee useita hyvin samankaltaisia kyselyjä tietokantaan. Tietokanta menee kuitenkin helposti jumiin jos siihen kohdistuu kyselyjä hirvittävän nopealla tahdilla. Hyvänä perussääntönä voi pitää: minimoi tietokantaan kohdistuvien erillisten operaatioiden määrä. Yksittäisessä kyselyssä kannattaa kuitenkin tietokanta laittaa tekemään kaikki mahdolliset laskutoimitukset ja järjestämiset.

Listataan kaikki ruokalajit resepteineen hierarkisena listana:


// annetaan nimi-kentille uudet nimet koska muuten tulee
// ongelmia assosiatiivisen taulukon kanssa
$sql = $dbh->prepare('SELECT resepti.nimi AS resepti, ruokalaji.nimi AS ruokalaji
         FROM resepti, ruokalaji
         WHERE ruokalaji.ruokalajiID = resepti.ruokalajiID
         ORDER BY ruokalaji.nimi, resepti.nimi');

if ( !$sql ) print_r( $dbh->errorInfo() );

$ok = $sql->execute();

if ( !$ok ) print_r( $sql->errorInfo() );

print "<ul>\n";
$row = $sql->fetch(PDO_FETCH_ASSOC);

while ($row) {
    // otetaan muistiin minkä ruokalajin reseptejä ollaan listaamassa
    $laji = $row["ruokalaji"];
    print "<li>" . $laji . "\n\t<ul>\n";
    // listataan sisempää listaa (reseptejä) kunnes ruokalajin nimi muuttuu
    while ( $laji == $row["ruokalaji"] ) {
        $resepti =  $row["resepti"];
                print "\t\t<li>";
                print $resepti;
                print "</li>\n";
        $row = $sql->fetch(PDO_FETCH_ASSOC);
    }
    print "\t</ul>\n</li>\n";

}
print "</ul>";

Kokeile toimiiko listaus. Lisää ohjelmaasi xhtml-sivun edellyttämät perustiedot ja kokeile sivua myös selaimella

Entäs jos halutaankin listata kaikki ruokalajit? Nythän listattiin vain sellaiset joille on määritelty reseptejä. Ohjelmaa pitää muuttaa seuraavasti:


// kysely pitää muuttaa käyttämään ulkoliitosta
// niin saadaan mukaan myös sellaiset ruokalajit joille
// ei ole vielä reseptejä
$sql = $dbh->prepare('SELECT resepti.nimi AS resepti, ruokalaji.nimi AS ruokalaji
         FROM ruokalaji LEFT OUTER JOIN resepti
         ON ruokalaji.ruokalajiID = resepti.ruokalajiID
         ORDER BY ruokalaji.nimi, resepti.nimi');

if ( !$sql ) print_r( $dbh->errorInfo() );

$ok = $sql->execute();

if ( !$ok ) print_r( $sql->errorInfo() );

print "<ul>\n";
$row = $sql->fetch(PDO_FETCH_ASSOC);
while ($row) {
    // otetaan muistiin minkä ruokalajin reseptejä ollaan listaamassa
    $laji = $row["ruokalaji"];
    print "<li>" . $laji . "\n";
    // jos reseptiä ei ole niin ei myöskään aloiteta sisempää listaa
    if ( $row["resepti"] != "" ) {
        print "\t<ul>\n";
    }
    // listataan sisempää listaa (reseptejä) kunnes ruokalajin nimi muuttuu
    while ( $laji == $row["ruokalaji"] ) {
         $resepti =  $row["resepti"];
         // jos reseptiä ei ole niin ei myöskään tulosteta sisempää listaa
         if ( $resepti != "" ) {
                print "\t\t<li>";
                print $resepti;
                print "</li>\n";
         }
        $row = $sql->fetch(PDO_FETCH_ASSOC);
    }
    // jos reseptiä ei ole niin ei myöskään lopeteta sisempää listaa
    if ( $row["resepti"] != "" ) {
        print "\t</ul>\n";
    }
    print "</li>\n";

}
print "</ul>";

Kokeile miten uusittu tulostus toimii

Oliot tietokantojen käsittelysssä

Tietokantataulut voidaan piilottaa olioiden taakse jolloin niiden käsittely yksinkertaistuu. Jokaista taulua kohti voi kirjoittaa oman luokan, käyttää valmista koodigeneraattoria joka tulkitsee tietokannan rakenteen ja luo valmiin koodin tai käyttää yhtä dynaamista luokkaa, joka mukautuu tarpeen mukaan. Seuraavassa tutustutaan yksinkertaiseen dynaamiseen luokaan.

Ota ohjelmassasi käyttöön Table.php tiedosto:
```
require 'Table.php'
```

Luodaan oliot resepti- ja ruokalaji-tauluja varten:


// oliota luodessa pitää antaa käytetty tietokantayhteys,
// taulun nimi ja perusavaimen nimi
// luokka toimii vain sellaisilla tauluilla joissa
// perusavain muodostuu yhdestä kentästä
 $resepti = new Table($dbh, "resepti", "reseptiid");
 $ruokalaji = new Table($dbh, "ruokalaji", "ruokalajiid");

// haetaan perusavaimen mukaan jokin tietue
 $resepti->select("1");
 $ruokalaji->select("2");
// yksittäisen kentän arvo saadaan get-metodilla
 print $resepti->get("nimi") . "\n";
 print $ruokalaji->get("nimi") . "\n";

 // kaikkien kenttien nimet saadaan fields-metodilla, joka palauttaa
// taulukon
 print_r( $resepti->fields() );

// muutetaan tietoja
 $resepti->set("nimi", "Mangokana");
// muutokset päivittyvät tietokantaa vasta kutsuttaessa update-metodia
 $resepti->update();

// tehdään uusi ruokalaji joten muutetaan perusavaimelle uusi arvo
// ja annetaan uusi nimi
 $ruokalaji->set("ruokalajiID", 13);
 $ruokalaji->set("nimi", "Hieno ruokalajiryhmä");
// lisätään uusi tietokantaan
// palautusarvona tulee mahdollinen virhe. Jos kaikki menee ok niin
// palautuu 0
 $ok = $ruokalaji->insert();
 if ( $ok ) { print_r($ok); };
// jos oltaisiin käytetty update-metodia olisi yritetty päivittää olematonta
// koska ruokalajiID 13 ei vielä ole käytössä. Tällä luokalla ei pysty
// vaihtamaan perusavaimen arvoa

// poistetaan edellä lisätty ruokalaji
 $ruokalaji->delete();

Tietokannan taulujen sisällön muokkaaminen yksinkertaistuu huomattavasti olioiden avulla koska hankalat SQL-lauseet on piilotettu luokan sisälle. Kyselyt täytyy kuitenkin tehdä perinteiseen tapaan.
Kirjoita olioiden avulla seuraavat operaatiot:
- Lisää uusi mustamakkara-resepti kolmelle hengelle
- Päivitä mustamakkara-resepti kuudelle henkilölle
- Poista lisäämäsi mustamakkara-resepti
Kokeile toimivatko operaatiot selaimen kautta.

Transaktiot

Transaktioiden avulla pystytään hallitsemaan tietokantaoperaatiokokonaisuuksia. Esim. Tietokantaan pitää syöttää useita tietueita peräkkäin ja jos yksikin lisäys epäonnistuu niin kaikki lisäykset pitää pystyä peruuttamaan. Transaktio varmistaa, että keskeneräisen transaktion vaikutukset tietokantaan eivät heijastu muille tietokannan käyttäjille kuin vasta transaktion hyväksymisen jälkeen.

Harjoitellaan transaktioiden toimintaa lisäämällä samalla kerralla uusi ruokalaji ja siihen liittyviä reseptejä:

// käytetään olioita

 $resepti = new Table($dbh, "resepti", "reseptiid");
 $ruokalaji = new Table($dbh, "ruokalaji", "ruokalajiid");
 $err = 0; // lasketaan mahdolliset virheet

// aloitetaan transaktio

$dbh->beginTransaction();

// luodaan ihan uusi ruokalaji. Miksi
// ruokalaji pitää luoda ennen reseptiä?

 $ruokalaji->set("ruokalajiid", 20);
 $ruokalaji->set("nimi", "SQL-ateria");
 $ruokalaji->set("kuvaus", "Esimerkki ruokalajista");
// tallennetaan uusi ruokalaji tietokantaa
// mutta se ei näy vielä muille koska transaktio on kesken
 $ok = $ruokalaji->insert();
 if ( $ok ) { print_r($ok); $err++; }

 // luodaan uusi resepti, joka liittyy edellä luotuun ruokalajiin
 $resepti->set("ruokalajiid", 20); // miksi 20?
 $resepti->set("nimi", "SQL-puuro");
 $resepti->set("kuvaus", "Maukasta aamiaiseksi");
 $resepti->set("reseptiid", 50);
 $resepti->set("henkilomaara", 1);
 $ok = $resepti->insert();
 if ( $ok ) { print_r($ok); $err++; }

// luodaan vielä toinen resepti. Kaikkia kenttiä ei tarvi määrätä
// koska oletukset säilyvät edeltä. Jätetään virheellisesti
// reseptiID muuttamatta jotta saadaan aikaan virhe
 $resepti->set("nimi", "SQL-velli");
 $resepti->set("kuvaus", "Maukasta iltapalana");
 $ok = $resepti->insert();
 if ( $ok ) { print_r($ok); $err++; }

 // jos tulee virheitä peruutetaan koko transaktio
// jos ei tule virheitä niin hyväksytään
if ( $err ) {
 $dbh->rollback();
}
else {
 $dbh->commit();

}

Kokeile ohjelmaa. Lisää ennen ja jälkeen transaktiota listaus ruokalajeista niin näet varmasti peruuntuuko koko transaktio vai ei.
Korjaa SQL-vellille toimiva reseptiID ja suorita ohjelma uudelleen. Nyt pitäisi uuden ruokalajin ja reseptien ilmestyä tietokantaan.

Automaattiset avainkentät

Usein käytetään avaimina kenttiä joiden arvon keksiminen jätetään tietokannan harteille. Esim. reseptiID ja ruokalajiID ovat tällaisia kenttiä. Tähän saakka ne on keksitty itse mutta entäs jos tietokanta keksii ne:

Muutetaan edellistä transaktioesimerkkiä hieman:


// ei määritellä ollenkaan ruokalajiID:tä
 $ruokalaji->set("nimi", "SQL-ateria"); 
 $ruokalaji->set("kuvaus", "Esimerkki ruokalajista");
 $ok = $ruokalaji->insert();
 if ( $ok ) { print_r($ok); $err++; }

 // tietokanta palauttaa tiedon viimeksi lisätyn tietueen
 // avaimesta. Jos kentän tyyppi olisi muuta kuin
 // INTEGER palauttaisi SQLite sisäisen rivinumeron
 // jota vastaava varsinainen avain pitäisi erikseen hakea
 // SELECT-lauseella: SELECT avain FROM taulu WHERE ROWID = :last_insert_rowid;
 // Tämä on tietokantakohtaisesti muuttuva toiminto

 $ruokalajiid = $dbh->lastInsertId();
 print $ruokalajiid . "\n";

// reseptiID:tä ei siis tarvitse ollenkaan määrittää
// ruokalajiID:ksi annetaan sama minkä tietokanta edellisen lisäyksen
// yhteydessä keksi
 $resepti->set("ruokalajiid", $ruokalajiid);
 $resepti->set("nimi", "SQL-puuro");
 $resepti->set("kuvaus", "Maukasta aamiaiseksi");
 $resepti->set("henkilomaara", 1);
 $ok = $resepti->insert();
 if ( $ok ) { print_r($ok); $err++; }

 $resepti->set("nimi", "SQL-velli");
 $resepti->set("kuvaus", "Maukasta iltapalana");
 $ok = $resepti->insert();
 if ( $ok ) { print_r($ok); $err++; }

Kokeile ohjelmaa. Saat luultavasti virheen, miksi? Korjaa ruokalajin tietoja niin, että lisäys onnistuu.

Lisätietoa

Kannattaa tutustua Philip Greenspunin kirjaan: SQL for Web Nerds

Sessiot

Esteettömyys