4.1. Relaatioskeemaa täydentäviä piirteitä: Näkymät (views)

1. 4.1. Relaatioskeemaa täydentäviä piirteitä: Näkymät (views) • Näkymä on johdettu relaatio, jota ei fyysisesti ole välttämättä olemassa. • Kutsutaan myös virtuaaliseksi relaatioksi. • Näkymä voidaan johtaa talletetuista perusrelaatioista ja/tai muista näkymistä. • Johtaminen tapahtuu SQL-kyselyn avulla:CREATE VIEW <nimi> AS SELECT … ; • Näkymää voi kyselyissä käyttää kuten perusrelaatioita. Käyttökelpoisen näkymän määrittely lyhentää sitä hyödyntäviä kyselyitä. • Tarpeeton näkymä voidaan poistaa: DROP VIEW <nimi>; 4.1-SQL-toimintoja Teuhola 2012

2. Esimerkki näkymän määrittelystä • Relaatiot: • Tuote (Tno, Tnimi, Valmistaja, Hinta) • Asiakas (Ano, Animi, Paikka) • Tilaus (Ano, Tno, Kpl, Pvm) • Näkymä ‘Tilaajat’ = Jotain tilanneet asiakkaat.CREATE VIEW TilaajatAS SELECT Ano, Animi, PaikkaFROM Asiakas AS aWHERE EXISTS ( SELECT *FROM Tilaus AS tWHERE t.Ano = a.Ano ); 4.1-SQL-toimintoja Teuhola 2012

3. Näkymän käyttö Vrt. Edellisen sivun näkymä: • Hae turkulaiset asiakkaat, jotka ovat tilanneet jotain:SELECT *FROM TilaajatWHERE Paikka = ‘Turku’; • Poista asiakkaat, jotka eivät ole tilanneet mitään:DELETE FROM AsiakkaatWHERE Ano NOT IN (SELECT AnoFROM Tilaajat); 4.1-SQL-toimintoja Teuhola 2012

4. Näkymän päivitys • Näkymärelaation päivitys pitäisi ‘projisoida’perusrelaatioiden päivitykseksi. • Onnistuu yksinkertaisissa tapauksissa (näkymä määritelty esim. projektion & valinnan avulla, ja pääavain mukana tuloksessa). • PostgreSQL: Näkymän päivitystä ei voi automaattisesti palauttaa perusrelaatioiden päivitykseksi, mutta voidaan kirjoittaa sääntöjä (rule), mitä eri tilanteissa tehdään. 4.1-SQL-toimintoja Teuhola 2012

5. Esimerkki näkymän päivityssäännöstä (PostgreSQL) • Jos ‘tilaajat’-näkymään yritetään lisätä jotain, tehdäänkin vastaava lisäys ‘asiakas’-relaatioon: CREATE RULE tilaajan_lisays AS ON INSERT TO tilaajat DO INSTEAD INSERT INTO asiakas VALUES (new.ano, new.animi, new.paikka); • Säännön poisto: DROP RULE tilaajan_lisays ON tilaajat; 4.1-SQL-toimintoja Teuhola 2012

6. Näkymän päivitys (jatk.) • Päivitys mahdollinen vain, jos vaikutus perus-relaatioihin on yksikäsitteinen ja pääteltävissä. • Hankala tapaus:Näkymä usean relaation liitos • Päivitys mahdoton jos näkymässä käytetty koostefunktioita (COUNT, SUM, ym.). • Näkymärelaation päivitys ei ole suositeltavaa muuten kuin poikkeustapauksissa. 4.1-SQL-toimintoja Teuhola 2012

7. Näkymän päivitys (jatk.) • Esimerkkitulkinnallisestiepämääräisestäpäivityksestä:CREATE VIEW TYÖTUNNITAS SELECT FNAME, LNAME, PNAME, HOURSFROM EMPLOYEE, PROJECT, WORKS_ONWHERE SSN = ESSN AND PNO = PNUMBER; Näkymään TYÖTUNNIT listataankunkintyöntekijäntekemättyötunniteriprojekteihin. • Yritetään nyt päivittää tätä näkymää, joka selvästikin sisältää taulujen välisiä liitoksia, seuraavalla operaatiolla: 4.1-SQL-toimintoja Teuhola 2012

8. Näkymän päivitys (jatk.) UPDATE TYÖTUNNITSET PNAME = ‘ProductY’WHERE LNAME = ‘Smith’ AND FNAME = ‘John’ AND PNAME = ‘ProductX’; Ilmeisestikin (?) tällä päivityksellä haluttaisiin siirtää John Smithin työtunnit projektista nimeltä ’ProductX’ projektiin nimeltä ’ProductY’. Tällöin näkymän päivityksen pitäisi johtaa ratkaisuun: 4.1-SQL-toimintoja Teuhola 2012

9. Näkymän päivitys (jatk.) UPDATEWORKS_ONSET PNO = (SELECT PNUMBERFROM PROJECTWHERE PNAME = ’ProductY’)WHERE ESSN IN (SELECT SSNFROM EMPLOYEEWHERE LNAME = ’Smith’ AND FNAME = ’John’)AND PNO = (SELECT PNUMBERFROM PROJECTWHERE PNAME = ’ProductX’); 4.1-SQL-toimintoja Teuhola 2012

10. Näkymän päivitys (jatk.) • MUTTA:Myös seuraava varsinaisten relaatioiden päivitysoperaatio toteuttaisi näkymäpäivityksessä annetut ehdot: UPDATE PROJECTSET PNAME = ‘ProductY’WHERE PNAME = ‘ProductX’; • Jälkimmäinentoteutustuntuuintuitiivisestityperältä, sillä se nimeäisi ‘ProjectX’:n uudelleensamallenimelle, kuintoinenjoolemassaolevaprojekti on. 4.1-SQL-toimintoja Teuhola 2012

11. Tiedon salaus näkymien avulla • Sisällytetään näkymään vain ne tiedot, jotka saavat näkyä muille, esim. salataan asiakkaiden paikkakunta: CREATE VIEW AsiakasNäkymäAS SELECT Ano, Animi FROM Asiakas; • Annetaan erikseen oikeuksia näkymän käyttöön:GRANT SELECT ON AsiakasNäkymä TO Esa; • Oikeuden saaja voi olla yksittäinen käyttäjä, käyttäjä-ryhmä tai PUBLIC. Oikeus voidaan myöntää eri operaatioihin: GRANT SELECT / INSERT / DELETE / ... 4.1-SQL-toimintoja Teuhola 2012

12. Relaatioskeemaa täydentäviä piirteitä: Rajoitteet eli väittämät (assertions) • Yleisimmät eheyssäännöt (CONSTRAINT) määritellään relaation luonnin yhteydessä(mm. PRIMARY KEY, FOREIGN KEY, UNIQUE,NOT NULL). • Väittämät ovat loogisia lausekkeita, joiden tulee olla aina tosia, paitsi tilapäisesti transaktion suorituksen aikana. • Määrittely: CREATE ASSERTION <nimi> CHECK ( <looginen lauseke> ); 4.1-SQL-toimintoja Teuhola 2012

13. Väittämät: esimerkki • Turkulaisten asiakkaiden tilausten minimikoko on 5 kpl per tuote.CREATE ASSERTION TilausRajaCHECK ( NOT EXISTS ( SELECT *FROM Asiakas AS a, Tilaus AS tWHERE a.Paikka=‘Turku’AND a.Ano=t.AnoAND t.Kpl < 5 ) ) • Huom! Yksinkertaiset CHECK-määreet (jotka koskevat yksittäistä attribuuttia tai riviä) voidaan sijoittaaCREATE TABLE –lauseisiin. 4.1-SQL-toimintoja Teuhola 2012

14. Relaatioskeemaa täydentäviä piirteitä: Herätteet (triggers) • Heräte (‘liipaisin’) on nimettyihin päivitys-operaatioihin liittyvä, mahdollisesti ehdollinen lisätoimenpide. • Tarvitaan esimerkiksi redundantin tiedon ylläpitämiseksi ajan tasalla, tai kompensoivien toimenpiteiden suorittamista, jos jokin oikeellisuussääntö on vaarassa rikkoontua. • Määrittely CREATE TRIGGER –lauseella. 4.1-SQL-toimintoja Teuhola 2012

15. Esimerkki herätteestä • Oletetaan, että kullekin asiakkaalle ylläpidetään tilausten summaa (Asiakas-relaatiossa). [Oraclen syntaksi …]CREATE TRIGGER TilSummaLisaysAFTER INSERT ON TilausFOR EACH ROWUPDATE Asiakas AS aSET TilSumma = TilSumma + ( SELECT t.Hinta * NEW.KplFROM Tuote AS tWHERENEW.Tno=t.Tno)WHERE a.Ano = NEW.Ano; 4.1-SQL-toimintoja Teuhola 2012

16. Herätteen toteutus PostgreSQL:n RULE-määrettä käyttäen. CREATE RULE TilSummaLisays ASON INSERT TO tilaus DO ALSOUPDATE asiakas aSET tilsumma = (SELECT SUM(ti.kpl * tu.hinta)FROM tilaus AS ti, tuote AS tuWHERE ti.ano = NEW.anoAND ti.tno = tu.tno)WHERE a.ano = NEW.ano; 4.1-SQL-toimintoja Teuhola 2012

17. Tilauksen poistoon liittyvä heräte(PostgreSQL) CREATE RULE TilSummaVahennys ASON DELETE TO tilaus DO ALSOUPDATE asiakas aSET tilsumma = (SELECT SUM(ti.kpl * tu.hinta)FROM tilaus AS ti, tuote AS tuWHERE ti.ano = OLD.anoAND ti.tno = tu.tnoAND NOT ti.tno = OLD.tno)WHERE a.ano = OLD.ano; 4.1-SQL-toimintoja Teuhola 2012

18. Relaatioskeemaa täydentäviä piirteitä: Talletetut proseduurit ja funktiot • Tietokannan yhteyteen palvelimelle voidaan tallettaa valmiiksi käännettyjä proseduureja ja funktioita, joita voidaan kutsua SQL:stä tai sovelluksista käsin. Etuja: • Useiden sovellusten tarvitsemat käsittelyrutiinit vain kertaalleen palvelimessa. • Talletetut proseduurit/funktiot suoritetaan palvelimessa ja voivat näin ollen pienentää työaseman ja palvelimen välistä liikennettä. • Voidaan mallintaa monimutkaisempia johdettuja asioita kuin näkymillä. • Voidaan tarkistaa monimutkaisia oikeellisuusehtoja. 4.1-SQL-toimintoja Teuhola 2012

19. Esimerkki johdetusta funktiosta(PostgreSQL) • Funktio, joka laskee asiakkaan tietyn tilauksen arvon.Parametreina asiakas- ja tuotenumero (integer-tyyppisiä) CREATE FUNCTION arvo(integer, integer)RETURNS integer AS'SELECT tu.hinta * ti.kpl FROM Tilaus AS ti, Tuote AS tu WHERE ti.ano = $1 AND ti.tno = $2 AND ti.tno = tu.tno;' LANGUAGE SQL; 4.1-SQL-toimintoja Teuhola 2012

20. Esimerkki johdetun funktion kutsusta(PostgreSQL) • Laskettava asiakkaan 111 kaikkien tilausten arvot. SELECT tno, kpl, arvo(ano, tno) FROM tilaus WHERE ano = 111; Tilaus Tuote 4.1-SQL-toimintoja Teuhola 2012

21. Talletetut proseduurit ja funktiot (jatk.) • Talletetut proseduurit/funktiot voidaan kirjoittaa jollain yleiskäyttöisellä ohjelmointikielellä tai SQL:llä. • Uudemmat SQL-standardit sisältävät laajennuksen(PSM = Persistent Stored Modules), joka tekee SQL:stä ‘täydellisen’ ohjelmointikielen, sisältäen mm. lauseet • IF … THEN … ELSEIF … THEN … END IF • WHILE … DO … END WHILE • REPEAT … UNTIL … END REPEAT • FOR … AS … CURSOR FOR … DO … END FOR 4.1-SQL-toimintoja Teuhola 2012

22. Käytännön tilanne SQL-ohjelmointi-kielissä • Valmistajakohtaisia laajennettuja kieliä: • Oracle: PL/SQL • Microsoft/Sybase: T-SQL • PostgreSQL: PL/pgSQL • PSM-standardin mukaisia (osittaisia) toteutuksia: • DB2: SQL PL • MySQL: SQL/PSM • PosgreSQL: PL/pgPSM (pieniä eroja PL/pgSQL:ään) 4.1-SQL-toimintoja Teuhola 2012

23. Esimerkki PL/pgSQL-ohjelmointikielestä CREATE LANGUAGE plpgsql; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - CREATE OR REPLACE FUNCTION terve(a integer) RETURNS varchar AS $$ DECLARE nimi varchar; BEGIN nimi := (SELECT animi FROM asiakas WHERE ano = a); RETURN 'Terve ' || nimi || ‘!’; END; $$ LANGUAGE plpgsql; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Kutsu: SELECT terve(111); Tulos: Terve Aalto! 4.1-SQL-toimintoja Teuhola 2012

24. Johtopäätös SQL-ohjelmoinnista • Talletetut proseduurit, funktiot, säännöt, rajoitteet ja herätteet ovat hyödyllisiä, kun määritellään tietokantaan liittyviä lisätoimintoja palvelimella. • Varsinainen sovellusohjelmointi tehdään usein mieluummin yleisillä ohjelmointikielillä(Java, C++, ym.), joista käsin voidaan kutsua SQL-operaatioita, mukaan lukien edellä mainitut lisätoiminnot. 4.1-SQL-toimintoja Teuhola 2012