SQLite

• Virheilmoitukset
  • Library routine called out of sequence
  • SQL logic error or missing database
  • Unable to open database file
  • Call to a member function on a non-object
  • Database table is locked
  • Safe_mode/open_basedir prohibits opening
  • Class 'PDO' not found
• Tiedon lisäämisen mahdolliset ongelmat
• PDO:n metodi fetch(PDO_FETCH_ASSOC) ei toimi
• rowCount() ei toimi
• lastInsertId() ei palauta viiteavainta
• Viite-eheyttä ei tueta
• Ulkoliitokset
• Autonumber/Autoincrement/serial
• Perusavaimet
• SQlite sallii merkkijonoja numeerisissa kentissä

SQLite on yksinkertainen tekstitiedostopohjainen tietokanta, jota voidaan käyttää esim. PHP-ohjelmissa. SQLiteen liittyy muutamia erikoispiirteitä, joita käydään läpi tässä dokumentissa.

Virheilmoitukset

Erityisesti PHP 5.0:n ja PDO-rajapinnan kautta käytettäessä SQLite saattaa joskus antaa erikoisia virheilmoituksia:

Library routine called out of sequence

Yleensä näitä virheitä tulee yhden todellisen virheilmoituksen jälkeen, jos tietokantaoperaatioita tehdään nopeaan tahtiin. Luultavasti SQLite jostain kuvittelee, että tietokantayhteyttä yritetään käyttää eri prosessista kuin missä se on alunperin avattu ja hermostuu tästä. Ongelma on pyritty korjaamaan uudemmissa SQLiten versioissa (> 3.3.1).

Ongelman saa yleensä kierrettyä preparoimalla uudelleen käsittelyn alla olevan kyselyn. Monesti tämä poistaa preparoinnilla saadut hyödyt, mutta sallii kuitenkin sovelluksen toimimisen.

SQL logic error or missing database

Tämä virhe tulee yleensä kun tietokannan sisältöä yritetään muuttaa WWW-liittymän kautta eikä tietokantatiedostoon/kansioon ole annettu kirjoitusoikeutta.

Esimerkiksi users.jyu.fi:ssä on annettava hakemistolle kaikille w-oikeus ja tietokantatiedostoon kaikille w-oikeus, koska PHP-skriptin suorittaja on apache-tunnus.

Unable to open database file

Kts. yllä.

Call to a member function on a non-object

Tämä virhe saattaa tulla, kun virheellisen kyselyn preparointi ei onnistu ja PDO-kyselyoliota ei synny. Sama virhe voi tulla, jos tietokantayhteys ei muodostu. Muista aina varmistaa onnistuuko preparointi:

$sql = $db->prepare("SELECT * FROM example");
// huomaa, että virheilmoitus pitää tässä pyytää tietokantayhteys-oliolta
if(!$sql) { print_r($db->errorInfo()); }

Database table is locked

Varmista, että sinulla ei ole avattuna tietokantaan lukuyhteyttä silloin, kun yrität samaan aikaan päivittää tietokantaa. Luettaessa tietokantaa SQLite ei salli kantaan muutoksia ennen kuin lukeminen lopetetaan.

Virhe tulee, kun lukee riveittäin läpi tietokantataulua ja samalla muokkaa sen sisältöä. Virhe tulee myös, kun tekee SELECT-kyselyn, mutta ei käy vastausjoukkoa läpi. Ongelman voi kiertää hakemalla kaikki kyselyn vastaukset kaksiulotteiseen taulukkoon fetchAll()-metodilla. Esim.

$sql_select = $dbh->prepare("SELECT id, nimi FROM foobar");
$sql_delete = $dbh->prepare("DELETE FROM foobar WHERE id = :id");

if(!$sql_select->execute())
  print_r($sql_select->errorInfo());

$resultset = $sql_select->fetchAll();
foreach($resultset as $row) {
  if(...ehto...) {
    $id = $row['id'];
    $sql_delete->bindParam(":id",$id);
    $sql_delete->execute();
  } else {
    echo $row['nimi'];  
  }
}

Safe_mode/open_basedir prohibits opening

Viite tietokantatiedostoon users.jyu.fi:ssä pitäisi olla muotoa:

sqlite:/homeX/YZ/tunnus/html/polku/tietokantatiedosto

Hakemistopolun saa komennolla:

echo $HOME

Class 'PDO' not found

Testaaminen komentoriviltä ei toimi, koska jalavassa hakemistopolku on erilainen kuin varsinaisella users.jyu.fi-palvelimella. PDO-laajennusta ei ole myöskään Eclipse-PHP:n kanssa ja hakemistopolkukin olisi väärä, koska PHP-tulkki on tällöin lokaalilla koneella. Testaus täytyy siis tehdä suoraan selaimessa.

Tiedon lisäämisen mahdolliset ongelmat

• Viite tietokantatiedostoon on väärin.
• Hakemistolla ja tietokantatiedostolla pitää olla kirjoitusoikeus kaikille käyttäjille.
• bindParam-metodille on tuotava toisena parametrina oikea muuttuja, pelkkä merkkijono tai arvo ei kelpaa.
• Onhan SQL-lause oikeaa muotoa eikä SQL:n suorituksessa tule virheitä?

PDO:n metodi fetch(PDO_FETCH_ASSOC) ei toimi

PHP:n uudemmissa versioissa vakiomuuttujat on muutettu PDO-nimiavaruuden alle. PHP 5.0.4 -versiossa on käytettävä ylläolevaa merkintää, mutta PHP 5.1:ssä muoto on PDO::FETCH_ASSOC.

rowCount() ei toimi

Tämä on jokin yhteensopivuusongelma vanhan PHP 5.0x ja SQLite3:n kanssa. PHP5.1:ssä $sql->rowCount(); toimii.

lastInsertId() ei palauta viiteavainta

Tietokanta palauttaa tiedon viimeksi lisätyn tietueen avaimesta $dbh->lastInsertId()-metodilla. Homma toimii OK, jos kentän tyyppi on INTEGER. Muutoin SQLite palauttaa perusavaimen sijaan sisäisen rivinumeron, jota vastaava varsinainen avain pitää hakea erikseen SELECT-lauseella:

$sql = $dbh->prepare("SELECT avain FROM taulu WHERE ROWID = :last_insert_rowid");
$last_insert_rowid = $dbh->lastInsertId();
$sql->bindParam(":last_insert_id", $last_insert_rowid);
$sql->execute();
$resultset = $sql->fetchAll();
$perusavain = $resultset[0]["avain"];

Viite-eheyttä ei tueta

Uusin versio SQlitestä osaa jo viite-eheydet mutta ne täytyy erikseen aktivoida jokaisessa tietokantayhteydessä komennolla:

 PRAGMA foreign_keys = ON;

Does SQLite support foreign keys?

SQLite ymmärtää viite-eheysmääritykset CREATE TABLE -lauseissa, mutta ei toteuta niitä. Haluttaessa viite-eheys toimintaan täytyy käyttää triggereitä seuraavaan tapaan:

CREATE TABLE Ruokalaji (
Nimi VARCHAR(100) NOT NULL,
Kuvaus VARCHAR(250) DEFAULT '-',
RuokalajiID INTEGER PRIMARY KEY AUTOINCREMENT,
CONSTRAINT Ruokalaji_Nimi_UQ   
        UNIQUE (Nimi)
);

CREATE TABLE Resepti (
Nimi VARCHAR(100) NOT NULL,
Kuvaus VARCHAR(250) DEFAULT '-',
Henkilomaara INTEGER DEFAULT 2,
ReseptiID INTEGER PRIMARY KEY AUTOINCREMENT,
RuokalajiID INTEGER NOT NULL,
CONSTRAINT Resepti_RuokalajiID 
	FOREIGN KEY (RuokalajiID)
	REFERENCES Ruokalaji (RuokalajiID)
);

-- Koska SQLite ei toteuta viite-eheyksiä toteutetaan ne itse
-- triggereiden avulla
CREATE TRIGGER fki_resepti_ruokalaji_id
BEFORE INSERT ON resepti
FOR EACH ROW BEGIN 
  SELECT CASE
     WHEN ((SELECT ruokalajiid FROM ruokalaji WHERE ruokalajiid = NEW.ruokalajiId) IS NULL)
     THEN RAISE(ABORT, 'insert on table "resepti" violates foreign key constraint "fk_resepti_ruokalaji_id"')
  END;
END;

CREATE TRIGGER fku_resepti_ruokalaji_id
BEFORE UPDATE ON resepti
FOR EACH ROW BEGIN 
   SELECT CASE
     WHEN ((SELECT ruokalajiid FROM ruokalaji WHERE ruokalajiid = NEW.ruokalajiId) IS NULL)
     THEN RAISE(ABORT, 'update on table "resepti" violates foreign key constraint "fk_resepti_ruokalaji_id"')
  END;
END;

-- estetään poistot jos viite-eheys rikkoontuu
CREATE TRIGGER fkd_resepti_ruokalaji_id
BEFORE DELETE ON ruokalaji
FOR EACH ROW BEGIN 
  SELECT CASE
     WHEN ((SELECT ruokalajiid FROM resepti WHERE ruokalajiid = OLD.ruokalajiId) IS NOT NULL)
     THEN RAISE(ABORT, 'delete on table "ruokalaji" violates foreign key constraint "fk_resepti_ruokalaji_id"')
  END;
END;

-- toinen vaihtoehto deletelle. vyörytetään poistot
CREATE TRIGGER fkd_resepti_ruokalaji_id
BEFORE DELETE ON ruokalaji
FOR EACH ROW BEGIN 
  DELETE from resepti WHERE ruokalajiID = OLD.ruokalajiid;
  END;
END;

Lisätietoja artikkelista: Enforce Foreign Key Integrity in SQLite with Triggers

Ulkoliitokset

SQLite tukee LEFT OUTER JOIN, mutta ei RIGHT tai FULL OUTER JOIN.

Autonumber/Autoincrement/serial

Automaattisesti numeroituja kenttiä voi luoda perusavaimen luonnin yhteydessä, mutta ei silloin, jos perusavain määritellään CONSTRAINT-lauseessa.

CREATE TABLE foo (
ID INTEGER PRIMARY KEY AUTOINCREMENT
);

Perusavaimet

Käytettäessä PHP:n PDO-rajapintaa voidaan tietokannalta kysyä viimeksi lisätyn tietueen perusavainta. Käytettäessä perusavaimena automaattisesti numeroitua kenttää tai tavallista INTEGER-tyyppistä kenttää palauttaa SQLite lisäysten yhteydessä aivan oikean perusavaimen arvon. Jos perusavain on jotain muuta tyyppiä, niin SQLite palauttaa oman sisäisen ROWID-kentän arvon (myös OID tai _ROWID_). Tässä tilanteessa voi joutua erikseen SELECT-lauseella etsimään ROWID-arvoa vastaavan perusavaimen arvon.

SQlite sallii merkkijonoja numeerisissa kentissä

SQLite sallii aivan vapaasti lisätä merkkijonoja numeerisiin kenttiin. SQLite yrittää kyllä muuttaa lisättävän tiedon numeroksi, mutta jos se ei onnistu, niin lisää kuitenkin.