Osnove SQL in njegova uporaba v perlu

• pokazati kako se uporablja perl knjiznice DBI za dostop do SQL baz podatkov.
• nauciti slusatelje osnove jezika SQL
• pokazati kdaj je smoterno uporabljati SQL (in baze podatkov na sploh) in kdaj ne
• pokazati nekaj primerjav med razlicnimi SQL strezniki (MySQL, Oracle, PostgreSQL) - omejitve, prednosti itd...

0) Splosno

Obstaja pisana paleta SQL streznikov, nekateri so komercialni, drugi so zastonj, tretji pa imajo mesano licenco.
SQL streznik na katerem bomo demonstrirali uporabo bo PostgreSQL 6.5.3 in prihaja z GPL licencno, torej je dostopen za vse uporabnike zastonj. Potrebno je vedeti, da pri programiranju manjsih projektov ni bistvenih razlik med razlicnimi strezniki. Kjer so razlike v osnovnih funkcijah SQL jezika bomo na to opozorili posebej.

Pri vecjih projektih velja dodobra preuciti kaksne so nase potrebe in vcasih ustrezno s tem poseci tudi po drazjih - komercialnih streznikih. Razlike med njimi bomo posebaj razdelali na koncu predavanja.

1) Uporaba SQL znotraj perla prek DBI/DBD

V nas program jo vkljucimo s stavkom:

use DBI;

Ko zelimo narediti povezavo s streznikom, izvrsimo preprosti ukaz:

$dbh = DBI->connect("DBI:Pg:dbname=database_name", "username", "password");

V prejsnjem primeru je privzeti streznik kar lokalen racunalnik. Vir kjer se nahaja streznik (prvi parameter metode connect) pa lahko bolj natancno dolocimo kot:

dbi:DriverName:database=database_name;host=hostname;port=port

Vedno je dobro tudi preveriti ali je ukaz uspel:

&HTMLerror("Cannot connect PostgreSQL SDSS database") unless $dbh;

Preverjanje napak bomo v prihodnjih primerih opustili, seveda pa je le to nujno za vsak resen projekt.

Povezavo na koncu dela prekinemo z :

$dbh->disconnect;

POZOR: obnasanje disconnecta je med razlicnimi bazami razlicno. Nekatere baze kot so Oracle in PostgreSQL avtomatsko izvedejo tudi ukaz commit, ki zapise vse spremembe v trenutni transkciji, medtem ko druge, naprimer informix tega ne naredijo in je nujno poklicati commit, preden povezavo prekinemo.

Vecina baz, ki jih bomo srecevali bo podpirala tako imenovane transakcije, ki nam omogocajo, da najprej koncamo z manipuliranjem baze, in sele ko se prepricamo, da se bodo vse spremembe izvedle uspesno, dejansko povzrocimo zapis vseh akcij. Transakcija se ob ukazu commit izvede 'atomsko', to pomeni, brez sprememb baze med delovanjem. V primeru, ko ne zelimo da se transakcija uposteva uporabimo ukaz rollback.

Privzeta nastavitev DBI knjiznice je tako imenovani autocommit, kar pomeni, da ne deluje v transakcijskem nacinu. Ce hocemo preklopiti v transakcijski nacin, nastavimo to z klicom:

$dbh->{AutoCommit}=0;

Nato v primeru, da ne zelimo, da se spremembe uposetavjo zazenemo: $dbh->rollback(); Sicer pa poklicemo: $sdss::dbh->commit();

No sedaj, ko znamo vzpostaviti povezavo z SQL bazo, lahko tudi izvrsimo prve SQL ukaze. S stavkom prepare pripravimo ukaz, ga nato izvrsimo z execute in izpisemo vse rezultate s pomocjo ukaza fetchrow:

$query=$dbh->prepare("SELECT uid,username FROM users");
$query->execute();
while (($uid,$username)=$query->fetchrow) {
 print ("Uid: $uid - Name: $username\n");
}

Kot vidimo z ukazom prepare najprej napovemo SQL ukaz, ki ga zelimo izvesti. (SQL jezik si bomo ogledali v nadaljevanju) Dejansko pa se ukaz izvede sele ob klicu metode execute.
Pri vsem skupaj si malo vec pozornosti zasluzi ukaz fetchrow, ki v obliki arraya vrne eno vrstico zahtevane poizvedbe. Ce smo izcrpali ze vse vrstice poizvedbe vrne vrednost nic.

Edini pomemben ukaz, ki nam je ostal, je do. Ta ukaz je v v bistvu le spoj prepare in execute Obicajno se uporablja pri SQL ukazih, ki ne vsebujejo selecta.

$dbh->do("INSERT INTO users VALUES ('janez','');

Ta stavek se bo izvrsil takoj, nam za njim ni treba klicati execute.

2) Programski jezik SQL

• baza (database) - skupek vseh tabel, funkcij in nasploh vseh persistentnih podatkov, ki se nahajajo na strezniski strani
• tabela (table) - zakljucena celota vecih podatkov z enako strukturo - enakimi vrsticami
• vrstica (row) - samostojen vpis v tabeli, ki sestoji iz polij
• polje (field) - najmanjsa enota informacije znotraj ene vrstice; s polji je moc matematicno in drugace manipulirati znotraj jezika SQL, zato morajo imeti polja dolocen tip (stevilski, textovni...)

CREATE

CREATE TABLE users (
  uid int,
  username character varying(30),
  fullname character varying(30),
  password character varying(20),
  email character varying(50),
  subscribed timestamp,
  PRIMARY KEY (uid),
  UNIQUE (username)
)

Celoten ukaz bi lahko zapisali v eno vrstico, a ga zaradi citljivosti raje ne. Kot vidimo na koncu stavka ni nobenega posebnega indikatorja, ki bi kazal na to da je zakljucen. Razlog lezi v tem, da se ukazi strezniku posiljajo loceno (torej v paketih en po en), zato v jeziku SQL ni potrebe po dodatnem locevanju. Ce uporabljamo moznost uporabe SQLa iz ukazne vrstice, moramo navadno na koncu zapisati se podpicje, da interpretor ve kaj smo hoteli od njega.
Jezik SQL ni obcutljiv na razliko med velikimi in malimi crkami, a navadno se zaradi berljivosti vsi ukazi pisejo z velikimi.

No kot vidimo smo uporabili ukaz CREATE TABLE s katerim naredimo tabelo. Polja, ki jih bo imel vsak vnos v tabeli bodo uid, username, fullname, password, e-mail in cas vpisa.

Uporabili smo ze nekaj osnovnih tipov, pa povejmo se kaj natancno pomenijo:

• int - 4 bytno celo stevilo
• character varying (n) - string, maximalne dolzine n (nekateri strezniki imajo omejitev n pri 256 (mysql, postgresql), drugi pa malce visje, a vseeno ne nad nekaj kilo byti (krajse ime za ta tip, ki se navadno uporablja je varchar)
• timestamp - natancen datum in cas skupaj z informacijo o casovni coni v kateri je zapisan

INSERT

INSERT INTO users VALUES (1,'kjanez','Janez Kovac','slabogeslo','janez.kovac@siof.net',now()) INSERT INTO users VALUES (2,'nanica','Anica Novak','rozice','metka.novak@amor.net',now())

Torej, kot vidite smo preprosto navedli vsebino vseh polij, ki jih ima vsaka vrstica. Velja omeniti, da se nizi v SQL omejijo s pomocjo enojnih zgornjih narekovajev. Zaradi varnosti, nam perlov modul DBI ponuja funkcijo quote, ki nam vrne string v obliki, v kateri ni vec "escape sekvenc" (torej, vsebina stringa ne more vplivati na izvedbo SQL ukaza). Obstaja se druga - bolj profesionalna - resitev , ki se imenuje binding operatorjev, ki pa je tu ne bomo opisovali.

$stavek_q=$dbh->quote("My name's Mike");

SELECT

Najbolj enostavna oblika stavka SELECT je sledeca:

SELECT * FROM users

Stavek nam bo vrnil vsa polja vsake vrstice, ki je tabeli users. Zaporedje polij bo enako kot je bilo podano ukazu create. Kaksno bo zaporedje vrstic, ki bodo bile vrnjene ni definirano, zato se na to ne smemo zanasati.
Ce bi radi iz vsake vrstice dobili le polja uid in username bi bil ukaz SELECT naslednji:

SELECT uid,username FROM users

SELECT uid,username FROM users ORDER BY fullname ASC

Tu ASC pomeni ascending, ce pa zelimo obrnjen vrstni red uporabimo DESC. Sedaj smo lahko prepricani, da bodo vrnjeni podatki vedno prihajali v enakem vrstnem redu. V zgornjem primeru bo vedno vrnjena najprej vrstica (2,'nanica') in nato (1,'kjanez').

Ce bi radi izpisali le eno (prvo) vrstico, lahko omejimo stevilo vrnjenih odgovorov z dodatkom LIMIT.

SELECT uid,username FROM users ORDER BY fullname ASC LIMIT 1

Sedaj pa si pogljemo se iskanja po tabeli. Na primer, ce zelimo ugotoviti, kaksen je fullname uporabnika 'kjanez', bomo to storili z naslednjim ukazom:

SELECT fullname FROM users WHERE username='kjanez'

Z dodatkom WHERE dolocamo omejitv poizvedbe. Za njim lahko sledi poljuben izraz, ki ga zna SQL evaluirati.
Ker je kompletna mnozica izrazov s katerimi lahko operiramo zelo velika to ni primeren kraj za podrobnejse opise, priporocam pa ogled PostgreSQL user's guide v katerem so izjemno natancno opisani vsi mozni ukazi in izrazi, ki jih lahko uporabljamo v SQL. Mimogrede bi se omenil, da bi se lahko veliko free software projektov zgledovalo po PostgreSQL kar se tice dokumentacije, saj je le ta res vzorna (99% stvari, ki jih vem o SQL sem se naucil tam).

Kompleksnejse poizvedbe s SELECT

SELECT zivali.ime FROM zivali,users WHERE users.uid=14 AND zival.lastnik=users.username

Stetje s SELECT

SELECT count(*) FROM users WHERE uid<50

Zgornji stavek bo izpisal stevilo vrstic, v katerih je vrednost polja uid manjsa od 50.
V primeru, da zelimo sesteti skupno vrednost vseh polij, ki ustrezajo pogojem lahko uporabimo ukaz sum(polje):

SELECT sum(uid) FROM users

Ukaz bo vrnil skupen sestevek vseh uid polij v tabeli users. (Ta podatek seveda nima nobene pomenske vrednosti, je le primer uporabe)

DELETE

DELETE FROM users WHERE uid>40 AND uid<50

Ta stavek bo naprimer izbrisal iz tabele users vse vrstice, ki imajo uid vecji od 40 in hkrati manjsi od 50.

UPDATE

UPDATE users SET uid=uid+1000 WHERE uid<100

Zgornji ukaz bo vsa polja uid, ki so manjsa od 100 nastavil na prejsnjo vrednost + 1000.

3) Triki

Listanje po tabeli

SELECT uid,username FROM users ORDER BY fullname ASC LIMIT maxlist OFFSET odmik

Tak stavek nam bo vrnil le tiste vrstice, ki bi sicer bile po vrsti med odmik in odmik+maxlist, ce bi prebrali celotno zaporedje vrstic. Seveda tu nujna uporaba ORDER BY, saj bi sicer lahko podatki ob vsaki poizvedbi prihajali v popolnoma nakljucnem vrstnem redu.

Sporocanje napak

sub HTMLerror {
 print "Content-type: text/html; charset=iso-8859-2\n\n";
 print "<HTML><BODY>";
 print "<BR> Error: @_<BR>";
 print "<A HREF=\"JavaScript:history.back()\">Go back wherever you come from</A>";
 print "</BODY></HTML>";
 dbdisconnect;
 exit;
}

4) Kdaj SQL?

Za primer programa pri katerem je SQL odvec lahko vzamemo kar prijavljanje na Linux Fest 6. Najprej sem sicer imel namen aplikacijo napisati kot SQL odjemalec, vendar je postalo jasno, da za nalogo ni potrebno, razen vpisovanja v textovno datoteko in prikaz celotne datoteke, nic drugega. Manipuliranje s textovnimi datotekami je v tem primeru v perlu mnogo lazje, hitrejse in ucinkovitejse.

Ko pa se lotimo vecjih stvaritev - naprimer web oglasne deske ali cesa podobnega, kjer je potrebno imeti vec razlicnih strukturiranih podatkov, vcasih kaj spremeniti ali celo izbrisati, pa se hitro pokaze, da je SQL verjetno najhitrejsa in tudi najcenejsa resitev.
Najbolj pomembno je, da pazimo in ne izumljamo tople vode znova in znova. Nekaj ur ucenja SQLja lahko prihrani nekaj tednov pisanja lastnih rutin za tisto, kar so nekateri ze zdavnaj naredili in to dobro!

5) Kateri streznik uporabiti?

PostgreSQL je bil na zacetku razvoja osnovan predvsem kot raziskovalni projekt zato ponuja nekatere vcasih malce cudaske moznosti. Raziskovalni znacaj se mu se vedno pozna, saj nekatere lasnosti se vedno niso skladne s standardom SQL92, da o SQL3 ne govorimo.

Oracle je svojo podporo za Linux udejanil sele lansko leto, ko je dejansko izdal delujoce programje. Glede na visjo ceno, ki jo je potrebno placati za ta komercialni produkt je morda vredno premisliti ali se bo investicija obrestovala. Je pa potrebno tudi vedeti, da edino pri oraclu lahko dobimo podporo tudi v Sloveniji, medtem, ko se je pri ostalih dveh bazah potrebno zanesti predvsem na elektronske medije.

Natancne primerjave predvsem o konformnosti s standardom SQL na podlagi realnih testov in informacije o drugih omejitvah lahko dobimo na http://www.mysql.com/crash-me-choose.htmy

Viri:
www.mysql.com
www.postgresql.org
www.oracle.com

10) Nadalnje ctivo: