[366] - Andmebaasid II (IDU0230) (sügis 2017)

• Jätkata avatud lähtekoodiga veebipõhise metaandmetega juhitavate veebirakenduste kiirprogrammeerimise vahendi pgApex arendamist. Selle süsteemi esimese versiooni loomisega lõppenud magistritöös on arendusvaates kirjas suur hulk täiendusvajadusi. pgApexit tuleb ise katsetada, võrrelda Oracle APEXiga ja teha kindlaks, kas leidub veel täiendusvõimalusi. Järgmiseks ülesandeks on väljalaske planeerimine (release planning) – milline funktsionaalsus lõputöö tulemusena realiseerida. Tuleb valida meetod selle otsustamiseks ja seda rakendada. Kõik täiendused tuleb mudelite abil projekteerida ja ka tarkvaras realiseerida ning testida. Töö peab ka kirjeldama täienduste tegemise protsessi. Siin on pgApexi serverisse üles laetud arenduskeskkond ning siin on selle vahendi MIT litsentsiga kaitstud lähtekood. Töö tulemus peab olema samuti avatud lähtekoodiga, mida kaitseb MIT litsents. Projekt peab olema GitHubis. Töö kõrvaltegevusena tuleb teha statistikat andmebaasi skeemimuudatuste ja nendest tingitud rakenduse lähtekoodi muudatuste kohta.
• phpPgAdmin vahendi teisendamine PostgreSQL 10.0-ga koostöötavaks. PostgreSQL viimastes versioonides lisandunud andmebaasiobjektidele/võimalustele toetuse lisamine (hetktõmmised, identiteedi veerud, laiendused, välised tabelid, …). Kindlasti on veelgi võimalikke funktsionaalsuse täiendusi, aga ka kasutajaliidese parandamise võimalusi. Täienduste hulk peab kaitsmiskomisjonile muljet avaldama ja täpne hulk selgub töö käigus. Lisaks vahendi täiendamisele on vaja ka täiendused mudelite keeles ära kirjeldada. Tulemuseks olev tarkvara versioon peab olema samuti avatud lähtekoodiga ning GitHubis kättesaadav.
• Enterprise Architecti täiendamine. Kõik selles jaotises olevad teemad tähendavad muuhulgas vajadust töö tulemusena hinnata, kui lihtne või keeruline on Enterprise Architect CASE vahendi funktsionaalsust laiendada. Kõigi nendel teemadel töö tegijatele on oluline see bakalaureusetöö, kus projekteeritakse ja realiseeritakse Enterprise Architecti jaoks mudeliteisendus. Siit, aga näite nimekirja kõigist kolmandate osapoolte poolt loodud Enterprise Architecti laiendustest. Üks sellisel teemal tehtud töö peaks lisaväärtusena sisaldama Enterprise Architecti mallide parandusi, mis likvideerivad "Andmebaasid II" iseseisva töö ülesandes 4 väljatoodud puudujäägid.
  1. Luua Enterprise Achitect (EA) CASE vahendis skriptid või muu lahendus, mis ühtlustab andmebaasi füüsilise disaini mudelis kitsenduste ja indeksite nimesid võttes aluseks tabelite ja veergude nimed (nt primaarvõtme kitsenduse nimi pk_). Kasutajal peab olema võimalus nimede formaate ise määrata/muuta.
  2. Enterprise Achitect (EA) CASE vahendis saab muuhulgas genereerida andmebaasi füüsilise disaini mudelist SQL koodi. See on mudelist koodi genereerimise näide. EA võimaldab luua uusi koodigeneraatoreid. Lõputöö ülesandeks on projekteerida ja realiseerida koodigeneraator, mis loeb sisendinda andmebaasi füüsilise disaini mudelit ja põhiobjekti olekumasina mudelit ning produtseerib PostgreSQL jaoks trigerite koodi, mis kontrollib seisundimuudatuse vastavust olekumasina mudelile (vt andmebaaside mustripõhise juhendi ("Andmebaasid I" kodulehel) Oracle koodinäidet, kus on selliste trigerite näide).
  3. "Puhast koodi" (clean code) on lust lugeda ja lihtne edasi arendada. Kood on spetsifikatsioon arvutitele, mudel on spetsifikatsioon inimestele. Nii nagu puhas kood on soovitavad ka puhtad mudelid. Clean Code raamatus esitatud koodi puhastamise juhiste alusel on ühes bakalaureusetöös kirja pandud samasugused juhised süsteemianalüüsi mudelitele. Töö ülesandeks on hinnata kõigi nende juhiste alusel mudelite automatiseerimise võimalust ning realiseerida EA laiendus, mis võimalikult suure hulga juhiste alusel mudelite kontrollimist automatiseerib.

Üle kümne aasta uus väljaanne: Readings in Database Systems, 5th Edition. Selles esitavad andmebaasidega seotud teadus- ja arendustöö suurkujud viiteid mõnedele andmebaaside valdkonda palju ja püsivalt mõjutanud teadusartiklitele. Mis peamine, iga peatüki ees on toimetajate kommentaar, milles tutvustatakse artikleid ning kommenteeritakse andmebaasisüsteemide minevikku, olevikku ja tulevikku. Kellel on andmebaaside ja maailma selle valdkonna trendide vastu sügavam huvi, soovitan lugeda.

Kui käivitate päringu välise tabeli põhjal ja saate teate, et on liiga palju kliente, siis palun

• Käivitage lause: DROP EXTENSION IF EXISTS postgres_fdw CASCADE;
• Käivitage uuesti üleseande 5 väliste tabelite kood

• Projekti punktid (saate teada minupoolt saadetud hindamismudeli failist). Lävendi ületamiseks on vaja vähemalt 31 punkti.
• Eksami punktid. Lävendi ületamiseks on vaja vähemalt 20 punkti. Eksami punktide saamiseks liidetakse:
  1. vahetestide punktid (võimalik saada kuni 18 punkti),
  2. lõpptesti punktid (võimalik saada kuni 30 punkti).
• Harjutustundides kogutud aktiivsuspunktid.

Vahetestide punktid aitavad ületada eksami lävendit ja suurendavad ka lõplikku punktisummat. Aktiivsuspunktid ühtegi lävendit ületada ei aita, kuid suurendavad lõplikku punktisummat.

Töövihiku projektis registreeritakse eraldi isiku seisund (elus, surnud, ...) ja töötaja seisund (tööl, puhkusel, vallandatud, lahkunud poolte kokkuleppel, ...). Mõistlik oleks sisselogimisel kontrollida nii töötaja kui ka isiku seisundit, et näiteks sellest maailmast lahkunud isik ei saaks süsteemi siseneda (ilmselgelt kasutab siis keegi tema kontot kurjasti ära). Need seisundid on omavahel seotud (kui isik sureb, siis tuleb tema kui töötajaga tööleping lõpetada), kuid kunagi ei tea kas kõik muudatused on andmebaasi jõudnud – parem karta kui kahetseda.

Soovin kõigile kaunist pühade jätku!

Seda ülesannet saab pgApexi korral lahendada nii, et loote andmebaasis vaate, kus kasutate kokkuvõttefunktsiooni string_agg. Vaate alampäring paneb iga registreeritud X eksemplari kohta kokku täpselt ühe rea. Selles reas on andmed kõigist tabelitest, mis sisaldavad X eksemplari andmeid – näiteks Kaup, Kauba_kategooria_omamine, Kauba_omaduse_väärtus, Kauba_variant. string_agg funktsiooni kasutamise abil saate teha näiteks seda, et iga kauba kohta pannakse kokku nimekiri kategooriatest ja nende tüüpidest, millesse see kaup kuulub. Võite nimekirjas kategooriate eraldajaks panna ';< br >' (eemaldage tühikud), et veebilehe kaudu vaadates oleks iga kategooria andmed väljas eraldi real (aruandes tuleb selle veeru korral märkida Is text escaped=False). Vaate alampäringus tuleb kasutada välisühendamist tagamaks, et juhul kui näiteks X eksemplar ei kuulu ühtegi kategooriasse, oleks selle andmed ikkagi päringu tulemuses. Selle vaate peale saate teha pgApexis aruande. Ei näe küll väga ilus välja, kuid ajab asja ära. Selle eest, et ei saa klõpsata lingil, misjärel avanevad konkreetse X eksemplari andmed, miinust ei saa – eeldusel, et suures nimekirjas on kõik andmed olemas. Kui selline lahendus ei rahulda, siis võib rakenduse teha ka muus vahendis. string_agg kasutava vaate loomise lisandväärtus on, et see annaks korrektse realisatsiooni korral lisapunkte (vt hindamismudel, lisapunktide tööleht).

string_agg kasutamise näite leiate andmebaasi kapseldamise slaidikomplektist kataloogis Teooria testideks (vahetestid, eksam) valmistumine/Harjutustunni slaidid (enamasti iseseisvaks uurimiseks). Otsige sealt sõna string_agg.

Nägin andmebaase ülevaadates paaril juhul, et funktsioonides oli otsingutee pandud jutumärkidesse.

SET search_path = "public, pg_temp"; Väidetavalt tekkisid jutumärgid, kui funktsiooni PgAdmini akende kaudu lisada. SEE ON VIGA!

Õige moodus, nii nagu on ka näidatud teate alguses viidatud lehel, on otsingutee ümber jutumärke mitte panna. Samuti ei saa kasutada apostroofe. Kui neid kasutate, siis kood ei tööta nagu vaja.

Minupoolne näide on mängitud läbi http://maurus.ttu.ee/346 kataloogis Andmebaasid 2/Andmebaasisüsteemide juhendid ja koodinäited/Slaidid andmebaasiobjektide kohta failis Operaatorid ja funktsioonid andmebaasisüsteemis PostgreSQL. SQL keeles kirjutatud kasutaja-defineeritud funktsioonid. slaidi 69 märkmelehel.

Mina ei saa PgAdmin vigade eest vastutust võtta, vaid saan ainult neile osutada. Lahenduseks on kirjutada ise CREATE FUNCTION lause ja see käima tõmmata.

Vaadake ka seda.

Kui kontrollite seisundimuudatuste lubatavust trigeritega (trigerite loomist käsitleb ülesanne 11), siis selle asemel, et kirjutada rutiinis:

UPDATE Kaup
SET kauba_seisundi_liik_kood=2
WHERE kaup_kood=p_kaup_kood AND kauba_seisundi_liik_kood=1;
/*Seisundisse 2 saab viia vaid seisundis 1 olevaid kaupu.*/

kirjutage:

UPDATE Kaup
SET kauba_seisundi_liik_kood=2
WHERE kaup_kood=p_kaup_kood;

Niimoodi väldite koodi halba lõhna Dubleerimine.

Teistpidi – kui trigerite abil pole sellist kontrolli tehtud, siis oleks parem selline lisatingimus rutiini lisada.

Selgus, et probleemi allikaks oli ühes andmebaasis olev riknenud väline tabel ja/või selle loomiseks vajalik laiendus. Selle tabeli põhjal päringut tehes loodi ühe ühenduse asemel nii palju ühendusi, et aktiivsete ühenduste limiit sai täis. Käivitades nimetatud andmebaasis lause:

DROP EXTENSION IF EXISTS postgres_fdw CASCADE;

ning seejärel uuesti ülesandes 5 oleva väliste tabelite loomise koodi, sai väliseid tabeleid probleemideta kasutada. Kui puutute sama probleemiga kokku oma andmebaasis, siis peaksite toimima sarnaselt - kustutage laiendus ja sellest sõltuvad objektid enda andmebaasist ning käivitage ülesande 5 kood uuesti.

Palun vabandust, et põhjusele varem jälile ei jõudnud.

Testil osalemiseks on kohustuslik registreerida. Registreerimine toimub Mauruse kaudu: Üldist => Teadmiste kontroll (nagu kevadel SQL kontrolltööle). Kuna klassi peab jääma hingamisruumi, siis on ühele ajale registreerijate arvu ülapiir range. Testil osalemiseks peab olema kaasas isikut tõendav dokument (nagu kevadel SQL kontrolltööl ja eksamil; raamatukogu- krediit- jms kaardid ei sobi ja nendega testi teha ei saa; autojuhiluba sobib).

Test toimub elektrooniliselt, Mauruse testikeskkonnas. Kasutada võib arvutiklassi arvutit või oma arvutit (erinevalt eksamist, mis toimub ainult arvutiklassi arvutites). Küsimustele vastamiseks materjale kasutada ei või. Testis on kuus juhuslikult valitud küsimust, vastamiseks on aega 10 minutit. Küsimused on valikvastustega ning ainult üks vastuse variant on õige. Nädala jooksul saab testi teha maksimaalselt ühe korra. Kui Te ei soovi testi teha, siis peate tundi tulema 15 minutit peale algust.

Seda testi järgi teha ei saa, kuid kokkuleppel õppejõuga võib selle teha varem.

Mis puudutab juhtaja töökohta, siis juhataja saab lõpetada X eksemplare. Kui realiseerite lõpetamise funktsiooni, mis lihtsalt muudab tabelis X seisundit, siis selleks, et samaaegsed seisundimuudatused üksteist üle ei kirjutaks ning ei tekitaks vastuolu projekti seisundidiagrammiga on vaja ka realiseerida ülesandes 11 viidatud seisundimuudatuste trigeripõhine kontroll.

Eksam
23. jaanuar 2018 kell 14:00, 15:30 ICT-402
24. jaanuar 2018 kell 10:00, 11:15 ICT-402

Vastuvõtuaeg
23. jaanuar 2018 kell 12:30–13:30 ICT-627

Vabandan segaduse eest!

Iseseisva töö personaalset ettenäitamist (nagu on aines "Andmebaasid I") ei toimu. Ülesande 9 lk 1 on kirjas, millisel juhul on vaja tulla oma projekti andmebaasirakendust eksamisessiooni ajal toimuval vastuvõtuajal ette näitama. Vastuvõtuajad on kirjas eksamit tutvustava dokumendi lõpus (kataloog Aine korraldus). Mingil ajal peale töö esitamist saate minult e-meilile minu poolt täidetud hindamismudeli faili.

Meenutan, et hindamismudelis (kataloog Aine korraldus) on lehekülg Lisapunktid, kus on kirjas võimalused saada projekti eest rohkem punkte, tehes selleks projekti nõutust mahukamaks.

Olen tänaseks pannud kataloogi Iseseisva töö projekti tegemine/Töö harjutustunnis (samm-sammuline juhend) kõik ülesanded, mis kokku moodustavad projekti tegemise juhendi.

Kataloogi Teooria testideks (vahetestid, eksam) valmistumine/Harjutustunni slaidid (enamasti iseseisvaks uurimiseks) on pandud viimane slaidikomplekt, millest võiks olla abi nii projektis nõutud täitmisplaani kirjelduse koostamisel, kui ka viimaseks lisapunktide testiks ja eksamiks (teema 7 kohta) ettevalmistamisel.

Kiirtesti käivitamiseks minge andmebaaside disaini kontrollimise rakendusse (vanem kasutajaliides; uuem kasutajaliides). Valige huvipakkuv andmebaas. Testiks valige Quick test. Andke korraldus test käivitada. Vanemas kasutajaliideses vajutage selleks nupule "Execute and show only queries that return rows".

Stiilinäiteks on päring, mis loendab kokku andmebaasis kasutaja poolt loodud trigereid ja reegleid. Tulemuses on rida vaid siis, kui nende arv on alla kolme.

WITH activedb AS (SELECT trigger_schema, trigger_name, 'TRIGGER' AS type
FROM INFORMATION_SCHEMA.triggers
WHERE trigger_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT r.schemaname, r.rulename, 'RULE' AS type
FROM pg_catalog.pg_rules r, pg_catalog.pg_namespace n, pg_catalog.pg_user u
WHERE r.schemaname = n.nspname AND n.nspowner = u.usesysid AND
(n.nspname = 'public' OR u.usename <> 'postgres'))
SELECT 'Too few triggers and/or rules, must be at least 3' As comment, (SELECT Count(*) AS cnt FROM activedb) AS cnt
WHERE (SELECT Count(*) AS cnt FROM activedb)<3;

Mõned kommentaarid.

• Skeemis pg_catalog on PostgreSQL süsteemikataloogi baastabelid.
• Skeemis INFORMATION_SCHEMA on süsteemikataloogi baastabelite põhjal defineeritud standardse struktuuriga vaated.
• Trigerite ja reeglite otsimisel on vaja jätta välja süstemikataloogis loodud trigerid ja reeglid.
• WITH klausel võimaldab defineerida käesoleva lause piires kasutatavaid virtuaalsed tabelid, millele lauses saab peale defineerimist nime kasutades viidata. See võimaldab SQL koodi paremini struktureerida. WITH klauslis võib olla järjest mitu alampäringut. Igas sellises alampäringus saab viidata kõikidele selles lauses eespool defineeritud alampäringutele.
• PostgreSQLis võib kirjutada SELECT lause, kus puudub FROM klausel, aga on WHERE klausel. WHERE klausli eesmärgiks on antud juhul tagada, et päringu tulemuses on rida vaid siis, kui trigerite ja reeglite koguarv on alla kolme.
• SELECT klauslisse saab kirjutada konstante ja ka mittekorreleeruvaid skalaarseid alampäringuid. Nende väärtuseid korratakse kõikides päringu tulemuseks olevates ridades.
• Väikeste andmehulkade korral ei ole PostgreSQLis NOT IN konstruktsiooni kasutamine probleem, kuid suurte andmehulkade korral tuleks sellest töökiiruse huvides hoiduda.

Selleks, et saaksite harjutustunnis kaasa töötada ning hiljem saadud infot oma projekti juures rakendada, palun Teil oma arvutisse panna PostgreSQL ODBC draiver ja luua andmeühenduse spetsifikatsioon vastavalt siin toodud juhendile. Selleks, et kõik töötaks nagu vaja valige palun versioon psqlodbc_09_05_0400. Kui olete installeerinud mõne hilisema versiooni, siis võtke see palun maha ja pange peale nimetatud versioon.

Hilisemate versioonide häda on selles, et kui loote andmeühenduse spetsifikatsiooni PostgreSQL_yhendus, kus jätate enamiku väljadest tühjaks, siis tabelite MS Accessi linkimisel ei avata mitte akent, kus saab täita lüngad, vaid saate veateate.

Testil osalemiseks on kohustuslik registreerida. Registreerimine toimub Mauruse kaudu: Üldist => Teadmiste kontroll (nagu kevadel SQL kontrolltööle). Kuna klassi peab jääma hingamisruumi, siis on ühele ajale registreerijate arvu ülapiir range. Testil osalemiseks peab olema kaasas isikut tõendav dokument (nagu kevadel SQL kontrolltööl ja eksamil; raamatukogu- krediit- jms kaardid ei sobi ja nendega testi teha ei saa; autojuhiluba sobib).

Test toimub elektrooniliselt, Mauruse testikeskkonnas. Kasutada võib arvutiklassi arvutit või oma arvutit (erinevalt eksamist, mis toimub ainult arvutiklassi arvutites). Küsimustele vastamiseks materjale kasutada ei või. Testis on kuus juhuslikult valitud küsimust, vastamiseks on aega 10 minutit. Küsimused on valikvastustega ning ainult üks vastuse variant on õige. Tulemuse saate teada kohe, kuid vastuste õigsust saab vaadata üheksanda nädala harjutustundides. Nädala jooksul saab testi teha maksimaalselt ühe korra. Kui Te ei soovi testi teha, siis peate tundi tulema 15 minutit peale algust.

Seda testi järgi teha ei saa, kuid kokkuleppel õppejõuga võib selle teha varem.

Võimalike ebamugavuste vältimiseks palume kõigil kes seni ID-kaardiga arvutisse loginud omale https://pass.ttu.ee iseteeninduskeskkonnas TTÜ Uni-ID kasutajakonto ja parool aktiveerida.

Testil osalemiseks on kohustuslik registreerida. Registreerimine toimub Mauruse kaudu: Üldist => Teadmiste kontroll (nagu kevadel SQL kontrolltööle). Kuna klassi peab jääma hingamisruumi, siis on ühele ajale registreerijate arvu ülapiir range. Testil osalemiseks peab olema kaasas isikut tõendav dokument (nagu kevadel SQL kontrolltööl ja eksamil; raamatukogu- krediit- jms kaardid ei sobi ja nendega testi teha ei saa; autojuhiluba sobib).

Test toimub elektrooniliselt, Mauruse testikeskkonnas. Kasutada võib arvutiklassi arvutit või oma arvutit (erinevalt eksamist, mis toimub ainult arvutiklassi arvutites). Küsimustele vastamiseks materjale kasutada ei või. Testis on kuus juhuslikult valitud küsimust, vastamiseks on aega 10 minutit. Küsimused on valikvastustega ning ainult üks vastuse variant on õige. Tulemuse saate teada kohe, kuid vastuste õigsust saab vaadata üheksanda nädala harjutustundides. Nädala jooksul saab testi teha maksimaalselt ühe korra. Kui Te ei soovi testi teha, siis peate tundi tulema 15 minutit peale algust.

Seda testi järgi teha ei saa, kuid kokkuleppel õppejõuga võib selle teha varem.

Moraal - koode, mis on "kõrgemalt poolt" (riik, standardiseerimisega tegelev organisatsioon, ettevõtte peakontor jne) ette kirjutatud tuleb kasutada, kuid ise ei peaks selliseid koode leiutama hakata.

• PostgreSQL (10.0) dokumentatsioon
  • PostgreSQL SQL Reference

Tuleb välja, et phpPgAdmin (viimane versioon pärit aastast 2013) versiooni 10 ei toeta ja seega phpPgAdmini kasutada ei saa. Andmebaasiga töötamiseks tuleb kasutada kas psqli või PgAdmin 4. Kui serveris on PostgreSQL 10, siis PgAdmin 3 abil sisenemisel saab veateateid ja hoiatusi, kuid lõpuks võimaldab tarkvara siiski serveris olevate andmebaasidega töötada.

Kui midagi ei tööta nagu vaja, siis andke palun teada.

Kui käite harjutustunnis, kus on palju rahvast ning Teile tundub, et ei saa piisavalt õppejõu tähelepanu, siis võite tulla mõnda harjutustundi kus on vähe rahvast:

• teisipäeviti kell 16:00 ICT-404,
• kolmapäeviti kell 16:00 ICT-122,
• reedeti kell 14:00 ICT-404.

Kui mõni loeng peaks minema üle aja, siis vabandan selle eest. Võite alati tunni lõppedes lahkuda ja ei pea jääma ootama minu jutu lõppu.

Huvitava ressursina on Internetis ajaveeb, milles tuuakse välja mitmete erinevate eluvaldkondade (liising, kogumine, spordikool, tenniseturniirid, e-oksjon jne) andmebaaside andmemudelid. Tegemist on SQL-andmebaaside loogilise disaini mudelitega (seal kirjeldatakse võtmed ja välisvõtmed, kuid ei arvestata konkreetse andmebaasisüsteemi võimaluste ning piirangutega). Esitatud mudelites on nii diagrammid kui tabelite kirjeldused. Nendest mudelitest saab kerge vaevaga tuletada kontseptuaalsed andmemudelid.

Igal mudelil on tabelid jaotatud erinevate ainevaldkondade vahel (erineva taustavärviga kastid). Need kastid vastavad üldjoontes registritele. Sageli märkade kastide nimedes AND märki, mis tähendab, et ühte valdkonda on kokku võetud mitmele erinevale põhimõistele vastavad tabelid. See on otstarbe lahususe põhimõtte eiramine. Parem oleks, kui igale põhimõistele vastaks eraldi register.

Miks on ikkagi oluline jõuda analüüsi tulemusel hea kontseptuaalse andmemudelini? Jaakkola et al. (2016) teadusartikli kohaselt kasvavad nõuete kogumise (analüüsi) faasis tehtud vigade parandamiseks kuluv pingutus iga järgmise süsteemiarenduse faasiga kolm korda. Disaini faasis on neid 3, realiseerimise faasis 9 ja testimise faasis 27 korda kulukam parandada kui nõuete kogumise faasis. Seega on oluline, et analüüsi tulemus oleks tasemel.

Olen vastavalt täiendanud ka eeldusaine lehel olevat kaupade registri näidet.

Lisainfot leiab Martin Kleppmanni ajaveebist.