Kirjautuminen

Haku

Tehtävät

Kilpailu

Ohjelmoi tekoäly!
Aikaa 4.2. saakka.

Koodivinkit: PHP, SQL: PDO - hyvä tapa käsitellä tietokantoja

Kirjoittaja: map_; viimeksi muokattu 18.08.2011.

Tagit: kirjastot

Hyödyllisyys: Tietokannan käsittely PHP:ssa kätevästi ja turvallisesti.

Esitiedot: PHP:n perusteet. Jonkin tietokannan (esim. MySQL) perusteet.
Viimeistä kappaletta varten myös olio-ohjelmoinnin perusteet.

Lyhyesti

PDO on PHP 5.1:n mukana tullut uusi ja kätevä tapa käsitellä tietokantoja. PDO:n kautta voi käyttää kaikkia PHP:n tukemia tietokantamoottoreita. Se on siis yhtenäistetty vaihtoehto vanhoille mysql, mysqli, postgresql, sqlite yms. rajapinnoille. Koska PDO on oliopohjainen, sitä on helppoa laajentaa omilla apumetodeilla.

Esimerkkitietokanta

Tässä esimerkissä oletetaan seuraavanlainen pieni tietokanta, jossa on käyttäjiä ja heidän luomiaan viestejä.

CREATE TABLE useraccount (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username TEXT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE post (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    userId INT NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    posttime TIMESTAMP NOT NULL DEFAULT current_timestamp,
    FOREIGN KEY (userId) REFERENCES useraccount (id)
) ENGINE=InnoDB;

-- Testidataa:
INSERT INTO useraccount (username) VALUES ('jussi'); -- saa id:n 1
INSERT INTO useraccount (username) VALUES ('jukka'); -- saa id:n 2

INSERT INTO post (userId, title, content, posttime)
VALUES (1, 'mielipiteitä', 'höpöhöpö, höpönlöpön', TIMESTAMP(NOW(), '01:00'));
INSERT INTO post (userId, title, content, posttime)
VALUES (1, 'lisää mielipiteitä', 'päläpälä, käläkälä', TIMESTAMP(NOW(), '02:00'));
INSERT INTO post (userId, title, content, posttime)
VALUES (2, 'php-opas', '<?php echo .....', TIMESTAMP(NOW(), '03:00'));

Nämä CREATE TABLE:t on kirjoitettu MySQL:ää varten. Koodiesimerkin loppuosa ei oleta MySQL:ää, vaan toimii myös esimerkiksi PostgreSQL:llä.

PDO:n alustus

PDO alustetaan luomalla uusi PDO-olio ja antamalla parametreiksi tietokannan asetukset.

<?php
// Tiedosto: init.php

// Luodaan PDO-olio, joka pitää sisällään tietokantayhteyden.
// Tietokantaa käytetään luodun PDO-olion kautta.
$db = new PDO('mysql:host=localhost;dbname=test',
              'kayttaja',
              'salasana');

// Oletusarvoisesti PDO ei sano mitään, jos tapahtuu virhe, mikä on typerää.
// Käsketään PDO:ta heittämään virhetilanteissa poikkeus,
// joka voidaan tarvittaessa kaapata try .. catch -lohkossa.
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Tietokanta on nyt valmis käytettäväksi.

Kyselyjen tekeminen

Tehdään pari SELECT-kyselyä PDO:n läpi.

PDO:ssa kyselyn tekemisessä on seuraavat vaiheet:
- Kysely valmistellaan antamalla SQL-koodi ja saamalla PDOStatement-olio.
- Kyselyyn sidotaan esimerkiksi käyttäjän antamat parametrit.
- Kysely suoritetaan.
- Tulokset luetaan.

Koodivinkin lopussa näytetään, miten PDO:ta voi laajentaa metodeilla, jotka tekevät kyselyistä vaivattomampia.

<?php
require('init.php');

/*
 * Oletetaan esimerkin vuoksi, että tälle skriptille
 * on lähetetty lomakkeesta kenttä 'uid', jonka tulisi olla
 * käyttäjätunnus:
 */
$_REQUEST['uid'] = '1';


// Kysellään, montako viestiä tämä käyttäjä on lähettänyt

// Aluksi luodaan valmisteltu lause ("prepared statement") siten, että parametrien
// kohdalle kirjoitetaan kysymysmerkki.
$stmt = $db->prepare('SELECT COUNT(*) AS n FROM post WHERE userId = ?');

// Seuraavaksi annetaan jokaiselle kysymysmerkille parametrin arvo
// kutsumalla valmistellun lausekkeen metodia
// bindValue(kysymysmerkin_numero, arvo);
//
// Tämä lisää tarvittavat lainausmerkit ja kenoviivat,
// mikä estää SQL-injektiot. Toisin sanoen, tämä ajaa saman asian kuin
// mysql-rajapinnan mysql_real_escape_string.
$stmt->bindValue(1, $_REQUEST['uid']);

// Suoritetaan kysely.
$stmt->execute();

// Luetaan tulos.
// Tässä tapauksessa tuloksia on tasan yksi, koska kyselimme COUNT(*):n.
$result = $stmt->fetchObject();

// Tuloksemme on tulosolion kentässä "n", koska kyselyssämme lukee AS n.
echo $result->n . "\n";

Toisessa esimerkissä kysellään useampi rivi ja näytetään vaihtoehto bindValue:lle.

<?php
require('init.php');

// Oletetaan taas, että skripti sai lomakkeesta parametrin 'uid'.
$_REQUEST['uid'] = '1';

// Kysellään kaikki pyydetyn käyttäjän lähettämät viestit lähetysjärjestyksessä
$stmt = $db->prepare('SELECT * FROM post ' .
                     'WHERE userId = ? ' .
                     'ORDER BY posttime DESC');
/*
 * Sen sijaan, että sanotaan erikseen
 * $stmt->bindValue(1, $_REQUEST['uid']) ja $stmt->execute(),
 * voidaan sanoa:
 */
$stmt->execute(array($_REQUEST['uid']));

while ($result = $stmt->fetchObject()) {
    echo $result->title . "\n";
    echo $result->content . "\n";
    echo "-----\n";
}

HUOM: Yleinen aloittelijan virhe on olla laiska ja kirjoittaa kyselyparametrit suoraan prepare:n sisään. Tämä luo lähes aina tietoturvahaavoittuvuuden nimeltä SQL-injektio, koska käyttäjä voi kirjoittaa parametriksi mitä tahansa SQL-koodia. Nyrkkisääntönä prepare:n parametrin tulisi olla aina vakiotekstiä.

INSERT, UPDATE yms. kyselyt toimivat PDO:n kautta samalla tavalla kuin SELECT-kyselyt sillä erotuksella, että tulosrivejä ei tietenkään tarvitse hakea.

Transaktiot

Lähes kaikissa epätriviaaleissa tietokantasovelluksissa on toimintoja, jotka edellyttävät peräkkäisia muutoksia useaan tauluun.

Otetaan esimerkiksi keskustelupalsta. Keskustelupalstan tietokannassa on oletettavasti yksi taulu ketjuille ja toinen viesteille. Palstan PHP-ohjelma on suunniteltu sellaisen oletuksen ("invariantin") varaan, että jokaisella ketjulla on aloitusviesti.

Uuden ketjun luomisessa on kaksi vaihetta:
- Luo rivi ketjutauluun ja ota sen ID.
- Luo rivi viestitauluun ja assosioi se ketjun kanssa.
Näiden kahden operaation täytyy olla atomisia, eli niiden täytyy onnistua tai epäonnistua yhdessä ja niiden vaikutukset eivät saa näkyä ulos päin ennen kuin kumpikin on suoritettu.

Jos vain ketjutauluun syntyisi rivi, ohjelman oletus, että jokaisella ketjulla on aloitusviesti ei olisikaan enää voimassa, ja ohjelma voisi kaatua.
Toisaalta näin kävisi myös, jos jotain toista pyyntöä suorittava PHP-prosessi voisi edes hetkellisesti nähdä tietokannan tilassa, jossa ketju on lisätty, mutta viestiä ei.

Itse asiassa jopa SELECT-kyselyjä voi olla tarpeen tehdä atomisesti, koska muuten jokin toinen prosessi voi muuttaa tietokannan sisältöä radikaalistikin kahden SELECT:n välillä.

Joukko atomisesti suoritettavia operaatioita on nimeltään transaktio.

PDO:ssa transaktio aloitetaan metodilla beginTransaction() ja suoritetaan metodilla commit().

<?php
try {
    $db->beginTransaction();
    // Tässä välissä tehdään kantaan muutoksia (inserttejä, updateja, deletejä),
    // joista kaikkien on onnistuttava tai epäonnistuttava yhdessä.
    $db->commit(); // tehdään muutokset pysyviksi ja muille näkyviksi
} catch (Exception $e) { // Jos tuli poikkeus...
    $db->rollBack();     // ... peruutetaan muutokset
    echo "Virhe: " . $e->getMessage() . "\n";
}

Käytännössä kaikissa tietokantamoottoreissa yksittäiset kyselyt ovat aina atomisia, vaikka ne olisivat monimutkaisiakin.

MySQL-käyttäjät HUOM! Oletusasetuksillaan MySQL luo MyISAM-tyyppisiä tauluja, jotka eivät tue transaktioita. Tämä on typerää, mutta aivan naurettavaksi asian tekee se, että MySQL ei edes varoita käyttäjää kun transaktiota yritetään soveltaa moiseen tauluun. MySQL-tauluja luodessa CREATE TABLE-lauseiden loppuun täytyy muistaa kirjoittaa ENGINE=InnoDB, jotta transaktiot toimisivat.

Ekstraa: PDO:n laajentaminen

Kuten nähtiin, PDO:n käyttäminen ei ole yhtään työläämpää kuin muidenkaan vastaavien rajapintojen käyttäminen. Silti PDO voisi olla kätevämpikin.

Tässä on esimerkkinä eräs tapa laajentaa PDO:ta. Teemme PDO:lle alaluokan MyPDO, joka lisää luokkaan muutaman kätevän metodin sekä asettaa poikkeusten heittämisen oletusarvoisesti päälle.

<?php
// Tiedosto: MyPDO.php

class MyPDO extends PDO {

    public function __construct(/*...*/) {
        // (älä huolestu, jos et ymmärrä seuraavaa kahta koodiriviä)
        // Haetaan tämän metodin kaikki parametrit taulukkoon
        $args = func_get_args();
        // Kutsutaan yläluokan konstruktoria kaikilla parametreilla
        call_user_func_array(array($this, 'parent::__construct'), $args);

        // Asetetaan poikkeusten heittäminen oletusarvoksi
        $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    /*
     * Tekee kyselyn ja palauttaa kaikki tulosoliot taulukossa.
     */
    public function queryAll($sql, array $params) {
        $stmt = $this->prepare($sql);
        $stmt->execute($params);
        $resArr = array();
        while ($res = $stmt->fetchObject()) {
            $resArr[] = $res;
        }
        return $resArr;

        // Harjoitus 1: toteuta tämä tehokkaammin $stmt->fetchAll():lla
        // Harjoitus 2: muuta funktiota siten, että $params-taulukon
        //              sijaan sille voi antaa argumentit suoraan
        //              esim. $db->queryAll('SELECT ...', $a, $b, $c);
        //              (eikä $db->queryAll('SELECT ...', array($a, $b, $c));)
    }

    /*
     * Tekee kyselyn ja palauttaa ensimmäisen tulosolion.
     * Palauttaa false, jos tulosta ei ole.
     */
    public function queryOneRow($sql, array $params) {
        $stmt = $this->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchObject();
    }

    /*
     * Tekee kyselyn ja palauttaa ensimmäisen tulosrivin ensimmäisen sarakkeen.
     * Palauttaa false, jos tulosta ei ole.
     */
    public function queryOneCol($sql, array $params) {
        $stmt = $this->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchColumn();
    }
}

Tässä esimerkki laajennetun luokan käytöstä samoilla esimerkkikyselyillä kuin aikaisemmin:

<?php
require('MyPDO.php');

$db = new MyPDO('mysql:host=localhost;dbname=test',
                'kayttaja',
                'salasana');

$_REQUEST['uid'] = '1';

$posts = $db->queryAll('SELECT * FROM post ' .
                       'WHERE userId = ? ' .
                       'ORDER BY posttime DESC',
                       array($_REQUEST['uid']));

foreach ($posts as $post) {
    echo $post->title . "\n";
    echo $post->content . "\n";
    echo "-----\n";
}

$numUserPosts = count($posts);
$numAllPosts = $db->queryOneCol('SELECT COUNT(*) FROM post', array());
echo "Käyttäjän viestit: $numUserPosts / $numAllPosts\n";

PDO:ta laajentavia tai muuten käyttäviä luokkia löytyy toki netistä lisää.

PDO:ssa on paljon enemmän tai vähemmän hyödyllisiä piirteitä, jotka löytyvät sen dokumentaatiosta.

Kommentit

ankzilla [03.06.2009 00:22:14]

Lainaa #

Löydettiinpä taas koodia josta meikäläinen ei ymmärrä hevon p-tä. Eiks toi phpMyAdmin oo ihan hyvä tommoseen, jos käyttää mysliä?

Chiman [03.06.2009 19:09:13]

Lainaa #

Hyvä koodivinkki.

ankzilla kirjoitti:

Eiks toi phpMyAdmin oo ihan hyvä tommoseen, jos käyttää mysliä?

Ei. Tässä vinkissä on kyse itse tehdyn sovelluksen sisäisistä tietokantaoperaatioista, phpMyAdmin liittyy enemmän tietokannan ylläpitoon. Voihan ylläpitäjä toki lukea ja kirjoittaa foorumiviestejä suoraan phpMyAdminilla, mutta hankalaa se olisi.

Zeeli [08.06.2009 20:58:41]

Lainaa #

Hyvä vinkki. PDO is the way to go!

alker [09.07.2009 20:44:31]

Lainaa #

Mitä eroa tällä on php:een mysql komennoilla. Niillähän saa saman tehtyä kuin tällä?

map_ [09.07.2009 21:30:54]

Lainaa #

alker kirjoitti:

Mitä eroa tällä on php:een mysql komennoilla. Niillähän saa saman tehtyä kuin tällä?

Alun mainospuheet jäivät tosiaan vähän lyhyiksi.

Toki vanhoilla rajapinnoilla voi tehdä samat asiat, mutta minun (ja monen muun mielestä) PDO on siistimpi ja (inhimillisestä näkökulmasta) turvallisempi:

PDO:n kanssa riittää, kun muistaa käyttää kysymysmerkkisyntaksia. Ei tarvitse muistaa filtteröidä jokaista muuttujaa mysql_real_escape_string:n tms härvelin läpi.

PDO:sta saa ulos poikkeuksia. Vanhan MySQL-rajapinnan kanssa piti joka välissä iffailla virhekoodeja.

Luokan laajentaminen on selkeämpää ja joustavampaa kuin funktioperheen laajentaminen. Bonuksena olemassaolevia metodeja voi myös laajentaa esim. kyselyiden loggaamisella tms, jos sille on tarvetta. Mahdollistaapa oliopohjaisuus vale-olion tekemisenkin yksikkötestejä varten.

Vanhassa rajapinnassa käytettiin yleensä yhtä globaalia tietokantayhteyttä. Globaalit ovat, kuten tiedetään, pahasta kaikissa paitsi ihan pienissä ohjelmissa (houkuttelevat rikkomaan abstraktiotasoja jne jne). PDO:ssa tietokantaa voi käyttää vain PDO-olion kautta, jolloin on helpompi varmistaa, etteivät tietokantaan koske sellaiset komponentit, joilla ei ohjelman arkkitehtuurisuunnitelman mukaan ole sinne asiaa.

Joillekin on iloa myös siitä, että PDO toimii MySQL:n lisäksi muidenkin tietokantojen kanssa.

MikaBug [11.08.2009 19:12:21]

Lainaa #

Hieno koodivinkki! Vaikuttaa kyllä varsin kätevältä tavalta käsitellä tietokantoja tämä PDO.

ApE!0 [12.08.2009 03:40:21]

Lainaa #

CREATE TABLE post (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

mutta

$stmt = $db->prepare('SELECT COUNT(*) AS n FROM posts WHERE userId = ?');

muuten ihan hyvältä oppaalta vaikuttaa

map_ [12.08.2009 11:52:43]

Lainaa #

Oho - korjattu.

walkout_ [07.09.2009 13:09:33]

Lainaa #

Niin eikö se ole niin että SQL-lauseet tässä valmistetaan niin, että sama ohjelma pitäisi toimia niin MySQL:ssä kuin Oraclessa? Tosin ei täysin 100% varmuudella.
Oraclessahan on SQL-kielessä eroja verrattuna MySQL:n ja muutekin se on erilainen ja siinä on toimintoja mitä MySQL ei tue ja päin vastoin.
Olen myös huommannut että jotkin SQL-lauseet eivät toimi vanhemmissa MySQL versioissa kun taas uusimmassa toimii.

PS: Olen tutkinut paljon Zend Frameworkiä ja ihmettelen joitakin olioita kun ne ei tee mitään eritavalla kuin alkuperäinen PHP-functio.. mutta sitä varten pitää olla sitten erikseen olio.

map_ [07.09.2009 18:35:45]

Lainaa #

PDO ei tosiaan auta SQL-murteiden eroihin. Siihen tarvitaan paljon laajempi ja monimutkaisempi abstraktiokerros, kuten
Doctrine. Onneksi useimmille sovelluksille riittää toimia yhden tietokannan kanssa.

Voi olla monta syytä tehdä yksinkertaisista funktioista luokan metodeja. Näistä ehkäpä tärkein on polymorfisuus: Funktio halutaan kääriä jonkin tietyn rajapinnan (interface) toteuttavaan luokkaan. Tällaisen luokan olioita voidaan sitten kutsua muualla tietämättä tarkkaa toteutusta (eli polymorfisesti). Tästä hyviä esimerkkejä ovat Zend_Validate- ja Zend_Filter-luokat, joita käyttää mm. Zend_Form.

Jos jokin Zendin ratkaisu ihmetyttää, kannattanee avata siitä ketju keskustelualueelle.

Triskal [28.09.2009 15:53:47]

Lainaa #

Olin jo ehtinyt itse tehdä omat mysliluokat: DBConnection ja MySQLDataSource, kunnes törmäsin tähän ja päätin kokeilla. Omat luokkani käyttävät tietenkin tätä "vanhaa" rajapintaa. Jos ja kun on olioita olioiden sisällä, niin tarvitaan uusia temppuja PDO:n saamiseksi kaikkien ulottuville. Yksi temppu on välittää viittaus PDO-oliosta muille olioille jotka sitä tarvitsevat, joko konstruktorissa tai erillisen metodin avulla, tai sitten toinen tapa on määritellä PDO-olio staattiseksi. Eli jos luokkarakenne on vaikka tällainen:

<?php

class DBClass extends PDO {
    function laa() {
        echo "Laa!";
    }
}

class ClassA {
    static $dbObj;
    var $classBInstance;
    function __construct() {
        self::$dbObject = new PDO(/*whaeva*/)
        $this->classBInstance = new ClassB();
    }
}


class ClassB {
    function __construct() {
    }
    function usePDO() {
        ClassA::$dbObj->laa();
    }
}

$aa = new ClassA();
$aa->classBInstance->usePDO(); // Laa!
ClassA::$dbObj->laa(); // Laa!

?>

Tiedä sitten onko tästä hyötyä kellekkään, mutta minulle tuli omissa kokeiluissa tämmöinen tänkapå vastaan.

Kirjoita kommentti

Muista lukea keskustelun ohjeet.
Tietoa sivustosta