SQL keel Sqlite näitel

SQL on lühend sõnadest Structured Query Language (struktureeritud päringukeel) ja see on keel andmebaasidega suhtlemiseks. SQL keel sai alguse varastel 70-ndatel ning sellest ajast saati on standardiseeritud seda mitmete kokkulepetega ning loodud mitmeid SQL andmebaasisüsteeme, tuntumatest võib nimetada IBM DB2, Oracle, Microsoft SQL, Postgresql, Mysql, Sqlite jm. Siinses jutukeses uurime SQL keelt Sqlite näitel.

Sqlite (www.sqlite.org) on väikene ja kiire C-s kirjutatud Avaliku omandi (Public domain, st. täiesti tasuta) andmebaasisüsteem, mis ei vaja eraldi andmebaasiserverit. Andmebaasiks on omaette fail ja funktsioonid sellega toimetamiseks saadakse PHP moodulist. Neid PHP extensione on koguni mitu, protseduuriline ehk tavafunktsioonidena töötav sqlite2 ning OOP lähenemisega PDO moodul sqlite3 jaoks. Sqlite tuleb alates php5-st vaikimisi aktiveerituna, seda saab tasuta kasutada nt www.zone.ee freewebis.

Sqlitet saab kasutada ka teistes keeltes nagu c, python, perl, ruby jm. Sqlite kodulehel on saadaval käsureaprogramm, mille abil saab andmebaase luua, muuta ning päringuid sooritada. Kolmandate osapoolte poolt on tehtud ka graafilise liidesega andmebaasihaldureid nagu:

• http://sqliteadmin.orbmu2k.de/ - Sqlite Administrator
• http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index - Sqlite Spy
• http://sqlitebrowser.sourceforge.net/ - Sqlite Browser
• http://freshmeat.net/projects/sqlitemanager/ - Sqlite manager (veebipõhine php script)
• http://zone.ee/internetu/php/dbed/ - dbEd (veebipõhine php script)

Andmebaas kujutab endast kohta, mis sisaldab hoiule talletatud andmeid. Sinna saab neid vähese vaevaga lisada ja parandada ning erinevate tingimuste põhjal välja otsida. Andmebaasis hoitakse andmeid tabelitena, mis meenutavad veidi Exceli lehekülgi - on tulbad (columns) ja read (rows). Tulpadel on nimed, neid kasutatakse kõikvõimalikel juhtudel, nt otsimisel saab leida kasutajad tulba vanus järgi, kus vanus on nt suurem kui 16.

Sqlite andmebaasi tekitamiseks on kõige lihtsam kasutada ülaltoodud linkidelt mõnd programmi, nt Sqlite Administratoris: Database → New, seal valida andmebaasile nimi ja tüüp, kas versioon 2 või 3. Selles jutus vaatame Sqlite versioon 2-te.

Andmetabelit luues määratakse tabeli tulpadele kindlad tüübid (field types) ning muud lisapiirangud (constraints). Kuigi Sqlite 2-s käsitletakse kõiki tulpasid vaikimisi sõnedena ja otsest kohustust andmetüüpide määramiseks ei ole, on siiski parem, kui väljadele on määratud kindlad tüübid - nii on endalgi parem ülevaade, kuhu mislaadseid andmeid lisada.

Tulpadele määratakse andmetüübid tabeli loomisel. Nagu SQL keelele tavaks, luuakse need ka Sqlite puhul käsuga

CREATE TABLE tabelinimi (...väljanimed ja tüübid...)

Näiteks teeme väikese jututoa ja olgu tabeli nimeks sonumid:

<?php
 
$db = sqlite_open('dbkaust/jutukas.db', 0666, $viga);
 
if($viga)
{
	exit('tekkis viga: '.$viga);
}
else
{
	$sql_paring = 'CREATE TABLE sonumid (
		"id" INTEGER UNSIGNED AUTOINCREMENT PRIMARY KEY, 
		"kes" VARCHAR(20) DEFAULT "kylaline" NOT NULL, 
		"mida" TEXT NOT NULL, 
		"aeg" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, 
		"ip" VARCHAR(20) NOT NULL)';
 
	$tulem = sqlite_query($db, $sql_paring);
}
 
sqlite_close($db);
?>

Siin on loodud tabel PHP-d kasutades ning teistkordsel skripti täitmisel antakse veateade, et tabel on juba olemas. Tabelid võib luua ka hiirega klikkides kasutades ülalantud linkidel asuvate programmide abi.

Aga tulles uuesti SQL päringu juurde siis seal on näha, et väljanimed on jutumärkides ning neile järgnevad erinevad täpsustused. Kasutatud on järgnevaid andmetüüpe:

• INTEGER - täisarv. Esimesele, id väljale on lisatud piirangud UNSIGNED (ainult positiivsed arvud) ja AUTOINCREMENT (täitmata jätmisel suurendatakse välja sisu ühe võrra) ning PRIMARY KEY, see tähendab, et väli on kindlalt nõutud täitmiseks
• VARCHAR - varieeruva pikkusega lühike tekst, sulgudes lubatud pikkus
• TEXT - pikem tekst
• TIMESTAMP - kuupäev ja kellaaeg, kujul AAAA-KK-PP TT:MM:SS

täpsustus NOT NULL tähendab, et tühjaksjätt ei ole lubatud.

lisaks on veel olemas

• BOOLEAN - tõeväärtus, true/false, 1 või 0, tõene või väär
• FLOAT - komaga arv, näiteks toote hind
• TIME - kellaaeg, kujul TT:MM:SS
• DATE - kuupäev, kujul AAAA-KK-PP

Sqlite 3-s on veel kasutusel

• BLOB - pikem tekst, mis võib sisaldada binaarandmeid, nagu nt mingi (pildi)faili sisu

Andmete lisamiseks on kasutusel INSERT päring:

INSERT INTO sonumid (kes,mida,aeg,ip) VALUES ("nimi", "tere", "2008-12-06 18:26:12", "127.0.0.1")

Selline päring täidetakse PHP poolel sarnaselt tabeli loomisele, sqlite_query() funktsiooni abil:

<?php
 
$db = sqlite_open('dbkaust/jutukas.db', 0666, $viga);
 
if($viga)
{
	exit('tekkis viga: '.$viga);
}
else
{
	$sql_paring = 'INSERT INTO sonumid (kes, mida, aeg, ip) 
			VALUES ("nimi", "tere", "'.date('Y-m-d H:i:s').'", "'.$_SERVER['REMOTE_ADDR'].'")';
 
	$tulem = sqlite_query($db, $sql_paring);
	var_dump($tulem);
}
 
sqlite_close($db);
?>

See koodijupp lisab igal lehe laadimisel rea andmebaasi jutukas.db, mis asub kataloogis dbkaust.

Vajaliku teabe saab tabelist leida SELECT päringu abil. Päringu süntaks näeb välja selliselt:

SELECT vali1, vali2, valiN FROM tabelinimi WHERE tingimused

vali1 - n on väljade nimed, mida me kuvada soovime. Kui soovida väljastada kõik väljad siis tuleks nende asemel lisada tärn - *. Nt

SELECT * FROM sonumid WHERE aeg > "2008-06-12 20:27:43" ORDER BY aeg DESC LIMIT 30

Praegusel juhul leitakse sissekanded, kus aeg on üle kindla kellaaja, need järjestatakse ORDER BY tingimuse abil (clausel) kahanevalt ning leitakse maksimaalselt 30 sissekannet. Kui on rohkem siis neid lihtsalt ei otsita.

$db = sqlite_open('dbjutukas/jutukas.db', 0666, $vead);
 
if($vead)
 exit($vead);
 
$paring = 'SELECT * FROM sonumid WHERE aeg > "2008-06-12 20:27:43" ORDER BY aeg DESC LIMIT 30';
$sissekanded = sqlite_array_query($db, $paring, SQLITE_ASSOC);
foreach ($sissekanne as $i=>$rida) {
{
    // värvime read üle ühe hallikaks 
    $taust=($i%2) ? '' : ' style="background-color:#fafafa"';
    echo '<div'.$taust.'>' . $rida['kes'] . ' @ ' .$rida['aeg'] . ': ' . $rida['mida'].'</div>';
}
sqlite_close($db);

Andmete muutmiseks on UPDATE käsk:

UPDATE tabelinimi SET vali_1="väärtus 1", vali_2="väärtus2" WHERE tingimused

Tingimustes määratakse väli, mille alusel andmeid muudetakse. Selleks on tavaliselt id (primary key identifikaator) väli.

Andmete eemaldamiseks on DELETE käsk:

DELETE FROM tabelinimi WHERE tingimused

Tabeli kustutamine (ettevaatust - pane tähele et vajalikud andmed ei kao!) toimub järgmiselt:

DROP TABLE downloads

Kasutajasisestatud andmed (otsitavad väljendid, sisestatavad tekstid jne) tuleks teha ohutuks, ehk kontrollida et see ei sisaldaks liigseid jutumärke jms. Järgnevalt väike funktsioonike selliseks kontrollimikseks:

function esc($txt)
{
	return "'".sqlite_escape_string($txt)."'";
}

Kasutamiseks nt:

$sql="INSERT INTO kasutajad (nimi, parool) VALUES (".esc($_POST['nimi']).",".esc($_POST['parool']).")";

Andmete otsimise, muutmise ning kustutamise juures saab kasutada erinevaid tingimusi:

= - andmed on täpselt millegagi võrdsed
<> - andmed pole võrdsed
< - väiksem kui
> - suurem kui
<= - väiksem võrdne
>= - suurem võrdne
BETWEEN - andmed on vahemikus, nt BETWEEN 1 AND 100
LIKE - andmed sarnanevad , siis siin võib kasutada veel %-märki: nt LIKE "mida%" siis leitakse mida, midagi, midagistki jms

Tingimusi saab kombineerida AND ning OR sidesõnadega, nt:

UPDATE minutabel SET midagi=1 WHERE asi=true AND teineasi > 10

Takistamaks andmebaasi allalaadimist külastajate poolt tuleks see paigutada veebivälisesse kataloogi või siis lisada andmebaasikausta .htaccess fail sisuga:

# Deny sqlite file access. File extensions in parentheses
<FilesMatch "\.(sqlite|sqlite2|sqlite3|sq|sq2|sq3)$">
Deny from all
</FilesMatch>

Kui neid asju pole võimalik teha, siis võiks väljalülitada vigade kuvamise error_reporting(0) ning muuta failitee keerulisemalt äraarvatavaks.

• http://www.sqlite.org/lang.html - Sqlite juhendid
• http://freshmeat.net/articles/view/1428 - sqlite kasutamisest käsureal
• http://ee.php.net/manual/en/ref.sqlite.php - PHP käsiraamatu peatükk sqlite kohta
• http://ee.php.net/manual/en/book.sqlite3.php - PHP käsiraamatu peatükk sqlite3 kohta
• http://ee.php.net/manual/en/ref.pdo-sqlite.php PHP käsiraamatu peatükk sqlite PDO andmebaasikihi kohta
• http://www.litewebsite.com/?k=SQLite3 - eri versioonide php-kasutamisnäiteid