Tietokannat ja PHP

• Tietokannan luominen
• Tietokantayhteys
• Kyselyt
• Lisääminen
• Poistaminen
• Päivittäminen
• Monimutkaisemmat tulostukset
• Lisätehtävää
  • 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 onnistuu myös hienompien tietokannan hallintajärjestelmien hallinnointi (Postgresql, Mysql). SQLite-tietokannan kanssa mahdollisesti vastaantulevista ongelmista on kerätty oma dokumenttinsa, kts. http://appro.mit.jyu.fi/doc/sqlite/.

1. Avaa editori ja luo W-asemalle sovellukset-hakemistoon alihakemisto demo6.
2. Ota SSH-yhteys jalava.cc.jyu.fi:hin.
3. Siirry html-linkin kautta users.jyu.fi:hin. Luo tänne alihakemisto hidden. Luo sen alle hakemisto kanta ja siirry sen sisälle.
4. Kopioi itsellesi resepti_demo6.sql-tiedosto wget-komennolla:

   wget http://appro.mit.jyu.fi/sovellukset/demot/demo6/resepti_demo6.sql

5. Luo uusi kanta-hakemistoon SQLite3-tietokanta komennolla:

   sqlite3 resepti

   SQLite luo nyt uuden resepti-tiedoston ja tekee siitä tietokannan.

6. Voit tutustua työkalun osaamiin komentoihin kirjoittamalla .help.
7. Luo varsinaisen tietokannan taulut komennolla:

   .read resepti_demo6.sql

   SQLite lukee resepti_demo6.sql-tiedoston ja suorittaa sen sisältämät SQL-komennot. Älä huolestu DROP TABLE-lauseiden virheilmoituksista.

8. Tutki tietokantasi rakennetta seuraavilla komennoilla: .tables, .schema resepti ja .schema ruokalaji.
9. Tutki mitä taulut sisältävät yksinkertaisilla SQL-kyselyillä:

   SELECT * FROM Resepti;

   SELECT * FROM Ruokalaji;

10. .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 kanta-hakemistoon komennolle echo $HOME.
• Aloita uuden PHP-ohjelman index.php kirjoittaminen editorilla demo6-hakemistoon. Avaa tietokantayhteys:

  try {
   // tarkista että polku tietokantaan on oikea (kirjoita jalavassa "more ~käyttäjätunnuksesi")
   $dbh = new PDO('sqlite:/homeX/YZ/tunnus/html/hidden/kanta/resepti');
  }
  catch ( PDOException $e ) {
    print ( $e->getMessage() );
  }

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

• Valitettavasti emme voi testata ohjelmaa komentoriviltä tai debuggerin avulla, koska niiden kanssa ei voida käyttää PDO-laajennusta. Kokeile ohjelmaa siis suoraan selaimessa.
• 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. hidden-hakemistoon ei pääse WWW:n kautta, mutta PHP-tiedosto voi käsitellä sitä.

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

1. 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.
2. Valmistele yksinkertainen kysely:

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

3. Suorita (execute) kysely ja tarkista paluumuuttujasta onnistuiko kyselyn suorittaminen. Jos virheitä on tullut, niin tulosta ne (errorInfo-metodi).
4. Käy silmukassa läpi kyselyn palauttamat tietueet. Tulosta kustakin tietueesta kentän nimi ja sen arvo.
   • Tarvitset $sql-olion fetch-metodia.
   • PDO_FETCH_ASSOC -tyyli on helpoin, se palauttaa rivin taulukkona, jossa avaimina ovat kenttien nimet.
   • Rivejä voi kysellä while-silmukassa ja taulukon voi käydä läpi foreach-silmukalla.
   • SQLiten tiedostopohjaisuuden takia tietojen lisäys, poistaminen ja päivittäminen ei onnistu silmukan sisällä. Paremmissa tietokannanhallintajärjestelmissä tämän pitäisi onnistua. Operaatiot kuitenkin onnistuvat, jos kyselyn kaikki tulosrivit otetaan kerralla muistiin fetchAll-metodilla.
5. Kokeile suorittaa ohjelmasi selaimessa.
6. 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.
   // Muuttujan luominen on välttämätöntä sidonnassa!
   $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.

7. Normaalisti SQL-kyselyn palauttaman tuloksen kenttien nimet ovat tiedossa, joten haluttu arvo saadaan suoraan rivin sisältämästä taulukosta $rivi["kentännimi"]. Kokeile tulostaa kaikkien kenttien sijaan kustakin tietueesta esimerkiksi Nimi-kentän arvo.

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. Tietokanta pitää myös huolen, että kenttiin ei tule vääräntyyppistä tietoa. Kyselyjen valmistelu parantaa tietoturvaa SQL Injection-tyyppisiä hyökkäyksiä vastaan.

Lisääminen

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

1. Tee edellisen kohdan koodeista kopio kommentteihin.
2. Lisätään tietokantaan uusi resepti. Muuta valmisteltava kysely muotoon:

   INSERT INTO resepti (Nimi, Kuvaus, Henkilomaara, RuokalajiID, ReseptiID)
   VALUES (:nimi, :kuvaus, :henkilomaara, :ruokalaji, :reseptiid)

3. Kehittele jokin reseptin nimi ja lyhyt kuvaus (esim. makaronilaatikko) ja luo muuttujat kaikille parametreille. resepti_demo6.sql:stä voit katsoa millaisilla ID-arvoilla ruokalajeja on olemassa. Anna ReseptiID:ksi joku sellainen numero mitä ei vielä ole tietokannassa (esim. 10).
4. Sido muuttujat kyselyyn ja suorita se.
5. Kokeile suorittaa saman reseptin lisääminen uudelleen. Saat virheilmoituksen, miksi? Muuta kyselyä siten, että samat tiedot menisivät useampaan kertaan samaan tauluun.

Poistaminen

1. Tee edellisen kohdan koodeista kopio kommentteihin.
2. Poistetaan edellä lisätty resepti. Muuta kysely muotoon

   DELETE FROM resepti WHERE reseptiID = :id

3. Luo id-muuttuja ja sille arvo, joka löytyy tietokannasta (kts. resepti_demo6.sql). Sido muuttuja kyselyyn. Kokeile onnistuuko poistaminen.
4. Yritä poistaa ruokalajeja. Onnistuuko se ilman ongelmia? Voitko vapaasti poistaa minkä tahansa ruokalajin?
5. Kokeile poistaa kerralla useampia tietueita. Tämä onnistuu kyselyllä:

   DELETE FROM taulu WHERE tunniste IN ( 1, 2, 3 );.

6. Pohdi miten tekisit poistamisen hakusanan perusteella (ei tarvitse tässä toteuttaa).

Päivittäminen

1. Kopioi edellinen osuus kommenteihin.
2. Muuta kysely muotoon

   UPDATE resepti SET henkilomaara = ? WHERE reseptiID = ?

3. Muuta lisäämäsi tietueen henkilömäärä toiseksi luvuksi. Nyt bindParam-metodin ensimmäinen parametrin pitää olla luku (1 tai 2), joka kertoo monennenko kysymysmerkin kohdalle muuttuja sijoitetaan.
4. Aja kysely napauttamalla selaimen refresh-painiketta.
5. Tarkista, että muutos tapahtui tietokannan komentorivikäyttöliittymästä antamalla SQL-kysely:

   SELECT * FROM resepti WHERE reseptiid = 10;

6. Sitominen voidaan tehdä myös execute-metodin yhteydessä. Näin voidaan tehdä useita päivityksiä kerralla. Luodaan taulukko, jossa on tarvittavat sidontaan tarvittavat parametrit:

   $parametrit = array(
     array(4, 'Tiramisu'),
     array(5, 'Hieno herkku'),
     array(4, 'Banaanicurry'),
     array(10, 'Rommikakku')
   );
   

7. Muuta kyselyn rajoitteeksi reseptin nimi.
8. Tee foreach-silmukka, jossa käydään $parametrit-taulukko läpi. Vie kukin sidottava SQL-parametripari execute-metodin parametriksi. Silmukassa kannattaa tarkistaa onnistuiko kukin kysely.
9. Testaa tapahtuivatko muutokset.

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.

1. Listataan kaikki ruokalajit otsikkoina ja otsikon alle ruokalajiin liittyvät reseptit välilyönneillä eroteltuna. Haetaan ensin kaikki ruokalajit ja niihin liittyvät reseptit.

   SELECT ruokalaji.nimi AS ruokalaji, resepti.nimi AS resepti 
   FROM resepti, ruokalaji
   WHERE ruokalaji.ruokalajiID = resepti.ruokalajiID
   ORDER BY ruokalaji.nimi, resepti.nimi

2. Aja kysely ja käy while-fetch-silmukalla ja tulosta aluksi kultakin riviltä löytyvä ruokalaji h2-elementin sisälle.
3. Nyt ruokalaji toistuu turhaan moneen kertaan. Tutki onko ruokalaji sama kuin edellisellä silmukan kierroksella ja tee tulostus vain ensimmäisellä kerralla.
   • Huom.! Jos meitä kiinnoistaisi ylipäätään tietää mille ruokalajeille on olemassa reseptejä, niin tähän sopisi kysely SELECT DISTINCT ruokalaji.nimi as ruokalaji FROM ruokalaji, resepti WHERE ruokalaji.ruokalajiID = resepti.ruokalajiID
4. Nyt halutaan tulostaa vielä reseptit ruokalajin alle. Tulosta reseptin nimi siinä tapauksessa, jos edellisellä kierroksella oli sama ruokalaji.
5. Testaa. Huomaa, että myös sillä rivillä, jossa ruokalaji muuttuu on yksi resepti. Tulosta siis myös se.
6. Tarkista, että h2 ja p-elementit tulostuvat järkevästi niin että koodi pystyy validina.
   • Tämä voi vaatia hieman erityisjärjestelyjä silmukan ensimmäisen tai viimeisen alkion tapauksessa.
   • Tulostus voidaan tehdä myös sisäkkäisillä while-silmukoilla, jolloin erityisjärjestelyjä ei tarvita. Ulommassa tulostetaan ruokalajeja, sisemmässä reseptejä niin kauan kuin ruokalaji pysyy samana.
7. Entäs jos halutaankin listata kaikki ruokalajit? Nythän listattiin vain sellaiset joille on määritelty reseptejä. Kyselyä pitää muuttaa seuraavasti:

   SELECT ruokalaji.nimi AS ruokalaji, resepti.nimi AS resepti
   FROM ruokalaji LEFT OUTER JOIN resepti
   ON ruokalaji.ruokalajiID = resepti.ruokalajiID
   ORDER BY ruokalaji.nimi, resepti.nimi

8. Lisäksi on tarkistettava onko rivillä ruokalaji ja resepti vai pelkkä ruokalaji ja tehtävä tulostus sen mukaisesti
9. Kokeile miten uusittu tulostus toimii.

Lisätehtävää

Seuraavia taitoja tarvitaan vaativammissa tietokantatehtävissä.

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.

1. Harjoitellaan transaktioiden toimintaa lisäämällä samalla kerralla uusi ruokalaji ja siihen liittyviä reseptejä. Valmistele kaksi kyselyä, joista toinen lisää ruokalajin ja toinen reseptin.
2. Tee lisäys kuten yllä eli luo muuttuja ja sido ne kyselyihin. Aseta reseptin ruokalajiID:ksi sama kuin mikä keksimälläsi ruokalajilla on. Laita reseptin reseptiID:ksi jokin sellainen numero mikä on jo olemassa tietokannassa.
3. Kokeile suorittaa kysely. Muista tehdä tarkistukset kyselyiden onnistumiselle. Nyt pitäisi tulla virheilmoitus reseptin lisäyksen epäonnistumisesta. Tarkista tietokannan komentorivikäyttöliittymästä onnistuiko ruokalajin lisäys.
4. Tällainen toiminta ei ole kuitenkaan usein toivottavaa, koska halusimme kerralla lisätä reseptin ja siihen liittyvän ruokalajin. Usein operaatioketju on paljon pidempi (vrt. esim. pankin tilisiirto). Pitäisi pystyä siis peruuttamaan koko operaatio. Tämä onnistuu transaktioilla. Aloita transaktio ennen SQL-lauseiden suorittamista metodilla beginTransaction. Lisää myös laskuri virheiden määrälle, joka on aluksi nolla.
5. Jos SQL-lauseiden suorituksessa tulee virhe, niin lisää virhelaskurin määrä.
6. Kaikkien SQL-lauseiden lopussa tee tarkistus onko tullut virheitä. Jos virheitä ei ole tullut, niin suorita metodi commit(), muutoin metodi rollback().
7. Poista viime testissä lisätty ruokalaji komentoriviltä SQL-lauseella

   DELETE FROM Ruokalaji WHERE ruokalajiid = 123 // keksimäsi ruokalaji-id numeroksi

8. Kokeile nyt ohjelmaa. Tuliko ruokalaji takaisin vai peruuntuiko koko operaatio?

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?

1. Muutetaan edellistä lisäystä hieman. Poista kyselyistä reseptin INSERT-kyselystä reseptiID ja ruokalajin lisäyksestä ruokalajiID.
2. Mistä nyt tiedämme mikä on reseptin ruokalajin id-numero? Tämän saa selville tietokannanhallintajärjestelmältä kysymällä.

   $ruokalajiid = $dbh->lastInsertId();

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