Tietokannat - Viikkotehtävä 4

• Taso 1
• Taso 3
• Taso 5

Tällä viikolla harjoitellaan tietokantojen käsittelyä.

Kaikille tasoille yhteiset vaatimukset:

• Käytetään Python-kieltä. Ohjelmakoodi on kommentoitava.
• Sivut on toteutettava Flask-sovelluksena, joka toimii users.jyu.fi-palvelimella. Muille palvelimille sijoitetut sovellukset eivät kelpaa.
• Kaikkien sivujen on oltava validia HTML5:sta
• Javascriptiä ei saa käyttää
• Sovellus ei saa kaatua
• Tietojen tallentamiseen on käytettävä users.jyu.fi:ssä toimivaa SQLite3-tietokantaa.
• Sivujen tulee pysyä valideina, vaikka käyttäjä syöttäisi HTML-koodia joihinkin syötekenttiin. Sivuston sisältämä HTML on tuotettava Jinja-templatejen avulla. Python-koodin seassa ei saa esiintyä HTML-koodia. Jinja-koodi on pidettävä mahdollisimman yksinkertaisena.
• Kommentoikaa koodin oleellisimpia osia!
• Käytetään valmista tietokantaa, jonka luontilauseet löytyvät tietokanta.sql-tiedostosta. Tietokanta on vastaava kuin viikkotehtävässä 1 mutta rakenne on muutettu relaatiotietokantaan sopivaksi.

  Tietokannan rakennetta ei saa muuttaa. Älä siirrä sql-lauseita leikepöydän kautta vaan käytä suoraan annettua tekstitiedostoa, joka on UTF-8-muodossa.

  

  Joukkueen jäsenlistaus on tallennettu JSON-muodossa joukkueet-tauluun

• Lomakkeessa, jolla aiheutetaan muutoksia (lisäys, muutos, poisto) tietokantaan, ei saa käyttää GET-metodia
• SQL-lauseiden muodostaminen merkkijono-operaatioilla on ehdottomasti kiellettyä ja pudottaa vastauksen pisteet nollaan
• Sivustolla on käytettävä UTF-8-merkistöä kaikkialla.
• Salasanoja ei saa tallentaa selkokielisinä vaan on käytettävä SHA-512-algoritmia.
• Tietokannan taulujen ID-kenttien sisältöä ei pidä näyttää käyttäjälle eikä kysyä käyttäjältä. Tietokannassa on käytetty SERIAL/AUTOINCREMENT-tyyppistä kenttää, jolloin tietokanta keksii ID-kenttien arvot tarvittaessa itse.
• SQL-kyselyissä ei saa käyttää DISTINCT-ominaisuutta
• Viite-eheyksien täytyy olla tietokantaa käsiteltäessä käytössä. Muista tietokantayhteyttä avattaessa antaa komento:

  PRAGMA foreign_keys=ON

• Älä käytä globaaleja muuttujia
• Sovellus on toteutettava käyttäen sqlite3-kirjastoa. SQLAlchemyn käyttäminen ei ole sallittua.

Taso 1

Toteuta tulospalvelusivusto, jolla on seuraavat ominaisuudet:

• Käytä valmista tietokannan määrittelyä ja sisältöä. Luo vastaava Sqlite-tietokanta, jota käytät sovelluksessasi. Tallenna kaikki muutokset samaan tietokantaan. Huom! Tietokannan sisältö ei ole aivan sama kuin aiemmissa tehtävissä. Tässä versiossa on useita samannimisiä joukkueita.
• Toteuta samanlainen sovellus (samat ominaisuudet) kuin viikkotehtävässä 3 tasolla 1, mutta käytä tietolähteenä luomaasi Sqlite-tietokantaa.
• Sovellukseen pitää kirjautua valitsemalla lomakkeelta jokin tietorakenteessa olevista kilpailuista ja syöttämällä lomakkeelle joukkueen nimi ja salasana. Salasana on kaikilla sama eli tiea2080. Salasana ei saa esiintyä selkokielisenä ohjelmakoodissa eikä tiedostoissa! Joukkueen nimessä ei huomioida isoja ja pieniä kirjaimia eikä mahdollisiä välilyöntejä nimen alussa ja lopussa. Ts. " Kukka kaali " on sama asia kuin "kukka kaali".
• Toteuta sivuston autentikointi siten, että käytät tietokannasta löytyviä salasanoja. Jokaisella joukkueella on sama salasana (tiea2080), jota vastaavaa tarkiste löytyy tietokannan joukkueet-taulusta, ja on luotu käyttäen tietokantaan tallennettua joukkueen id-kenttää seuraavalla tavalla:

  import hashlib
  m = hashlib.sha512()
  m.update( str(row['id']) )
  m.update( u'tiea2080')
  print m.hexdigest()

  Salasana on generoitu hexdigest()-funktiolla

• Jos nimi tai salasana oli väärä tai joukkuetta ei löydy valitusta kilpailusta, tulostetaan virheilmoitus: "Kirjautuminen epäonnistui" ja palautetaan käyttäjä takaisin kirjautumissivulle.
• Jos yrittää tulla jollekin sovelluksen sivulle kirjautumatta niin edelleenohjataan automaattisesti kirjautumissivulle.
• Oikeiden kirjautumistietojen syöttämisen jälkeen pääsee automaattisesti (edelleenohjaus, redirect) joukkuelistaussivulle
• Sovelluksen jokaisella alasivulla on sivun ylälaidassa navigointipalkki jossa on seuraavanlaiset tiedot:
  • Kilpailun nimi
  • Kirjautuneen joukkueen nimi, josta on linkki sivulle, jossa joukkue voi muokata tietojaan
  • Linkki joukkuelistaussivulle

  Kirjautumissivulla ei ole navigointilinkkejä.

• Toteuta joukkuelistaussivu jossa on listaus kaikista valitun kilpailun joukkueista. Listauksessa näkyvät myös sarjan nimi sekä joukkueiden jäsenet. Joukkuelistaus on ensisijaisesti aakkosjärjestyksessa sarjan nimen mukaan ja toissijaisesti joukkueen nimen mukaan. Joukkueen jäsenten nimet listataan myös aakkosjärjestyksessä. Järjestäminen on case-insensitive eli ei välitä onko käytetty isoja tai pieniä kirjaimia. Järjestäminen on tehtävä SQL-kyselyssä.. Kts. upper(). Listauksen on oltava seuraavan mallin mukainen:
  • Sarjan nimi
    • Rennot 2
      • Heikki Häkkinen
      • Pia Virtanen
      • Sari Maaninka
    • Retkellä v 13
      • Henna Venäläinen
      • Katja Vitikka
    • ...

  <ul>
      <li>Sarjan nimi
          <ul>
              <li>Rennot 2
                  <ul>
                  <li>Heikki Häkkinen</li>
                  <li>Pia Virtanen</li>
                  <li>Sari Maaninka</li>
                  </ul>
              </li>
              <li>Retkellä v 13
                  <ul>
                  <li>Henna Venäläinen</li>
                  <li>Katja Vitikka</li>
                  </ul>
              </li>
              <li>...</li>
          </ul>
      </li>
  </ul>
  

  Listauksessa tarvittavat tiedot on haettava tietokannasta yhdellä sql-kyselyllä. Jos yrität esim. hakea erikseen jokaisen joukkueen jäsenen tietoja niin pieleen menee.

• Luo sivu, jossa järjestelmään kirjautunut joukkue voi muuttaa oman joukkueensa tietoja. Lomakkeella on oltava valmiina joukkueen aiemmat tiedot. Sivulla on lomake, joka sisältää seuraavat asiat:
  • Sarja, joka on valittava tietorakenteessa tarjolla olevista sarjoista. Sarjan valinta on toteutettava radiobuttonien avulla.
  • Joukkueen nimi, jota ei saa jättää tyhjäksi
  • Samassa kilpailussa ei saa olla kahta samannimistä joukkuetta
  • Joukkueen jäsenet, joita voi olla 2-5 kappaletta. Lomakkeella on valmiina viisi kenttää joihin voi syöttää jäsenten nimiä. Jäsenen nimi ei saa olla tyhjä eli tyhjiä kenttiä ei huomioida.
  Lomakkeella on osattava järkevästi käyttää HTML5-lomake-elementtejä kuten label. Pelkästään lomake-elementtien rajoitteisiin ei voi luottaa vaan kaikki tarkistukset on tehtävä ja virheilmoitukset tuotettava myös CGI-ohjelmassa python-kielellä. Javascriptiä ei saa käyttää.

  Lomake on toteutettava Flask-WTF- ja WTForms-kirjastojen avulla

• Jos lomakkeen tiedoissa on puutteita niin virheistä pitää ilmoittaa kyseisen kentän vieressä esitettävällä virheilmoituksella. Esim. joukkueen nimeä ei ole annettu tai jäseniä on syötetty vähemmän kuin kaksi kappaletta. Jos tiedoissa on virheitä tai puutteita niin muutoksia ei tallenneta.
• Aiemmin tietorakenteessa olleiden arvojen täytyy olla lomakkeella valmiina odottamassa muutoksia. Jos syötetyissä tiedoissa on virheitä niin lomake muistaa myös virheelliset syötteet.

Taso 3

Toteuta taso 1 -mukainen sovellus seuraavilla muutoksilla ja lisäyksillä:

• Tee sovellukseen sivu (logout), jolla voi kirjautua ulos sovelluksesta painikkeella. Uloskirjautumisen jälkeen siirrytään takaisin kirjautumissivulle eikä muille sivuille enää pääse. Lisää navigointipalkkiin linkki uloskirjautumissivulle.
• Lisää sovellukseen uusi sivu (admin), jolla pyydetään kirjautumaan sisään ylläpitäjänä. Sivulla on yksi salasanakenttä. Salasanana toimii admin. Salasana ei saa esiintyä koodissa eikä tiedostoissa selkokielisenä.
• Kirjauduttaessa sovellukseen admin-sivulta muuttuu sovelluksen ulkoasu. Huom. tee aivan uudet sivut ylläpitäjän käyttöön. Älä yritä muokata edellisellä tasolla olevia sivuja. admin-sivulta kirjauduttaessa ei saa päästä edellistasolla toteutetuille sivuille
• Navigointipalkissa lukee joukkueen nimen sijaan sana admin, josta on linkki sovelluksen etusivulle.

  Sovelluksen etusivulla on linkkilistaus tietorakenteesta löytyvistä kilpailuista.

  • Jäärogaining
  • Fillarirogaining
  • Kintturogaining
• Kilpailun nimeä klikattaessa päästään sivulle, jossa listataan kilpailun sarjat keston mukaan järjestettyinä. Listaus on oltava seuraavan mallin mukainen:
  • 2 h
  • 4 h
  • 8 h

  <ul>
  <li><a href="...">4 h</a></li>
  <li><a href="...">2 h</a></li>
  <li><a href="...">8 h</a></li>
  </ul>

• Valittaessa sarjalistaussivulta sarja päästään sivulle, jossa listataan kaikki kyseisen sarjan joukkueet.
  • Samalla sivulla on lomake, jolla voidaan lisätä uusia joukkueita valittuna olevaan sarjaan. Joukkueelta kysytään samat tiedot, kuin joukkueen tietojen muutoslomakkeella (kts. taso 1). Myös samat tarkistukset ovat voimassa. Lisäksi lomakkeella voi antaa joukkueelle halutun salasanan. Lomake on toteutettava WTFormsin avulla.
  • Onnistuneen joukkueen lisäämisen jälkeen saadaan listaus sarjan joukkueista. Myös juuri lisätty joukkue täytyy näkyä listauksessa.
• Tee joukkuelistauksessa olevista joukkueiden nimistä linkkejä. Linkki vie joukkueen omalle sivulle, joka on samanlainen kuin tasolla 1, eli pääsee muokkaamaan joukkueen tietoja. Myös salasanan voi vaihtaa.
• Lisää joukkueen muokkauslomakkeeseen myös checkbox, jonka valitsemalla kyseinen joukkue poistetaan tietorakenteesta. Joukkuetta ei kuitenkaan saa poistaa, jos joukkueella on jo rastileimauksia. Anna selkeä virheilmoitus jos joukkueen poistaminen ei onnistu.
• Kirjauduttaessa järjestelmään joukkueena voi joukkue vaihtaa omalla sivullaan tietojensa lisäksi myös salasanansa.
• Kirjautumissivun on näytettävä seuraavanlaiselta (värit ja layout). Lomakkeella on lisäksi valittava kilpailu alasvetovalikosta.
  
• Sivun navigointipalkin on näytettävä seuraavanlaiselta (värit ja layout).
  
• Joukkueen tietojen muokkaussivun on näytettävä seuraavanlaiselta (värit ja layout):
  

Taso 5

Toteuta taso 3 -mukainen sovellus seuraavilla muutoksilla ja lisäyksillä:

• Lisää sovelluksen admin-osioon uusi sivu, jossa listataan kilpailun kaikki rastit kaikkine tietoineen taulukkomuodossa. Kunkin rastin yhteydessä kerrotaan myös lukumäärä tämän rastin leimauskerroista. Huomioi myös rastit joita ei ole leimattu kertaakaan! Lisää rastisivulle vievä linkki navigointipalkkiin sekä sarjalistaussivulle.
• Kaikkien rastien listaus on tehtävä SQL-kyselyllä, jossa käytetään ulkoliitosta (OUTER JOIN)
• Lisää joukkuekohtaiselle sivulle taulukkomuotoinen listaus kyseisen joukkueen rastileimauksista aikajärjestyksessä:

  Aika	Rasti
  2017-03-18 10:30:30	34
  2017-03-18 10:47:50	5C
  ...	...

  <table>
  <tr><th>Aika</th><th>Rasti</th></tr>
  <tr><td><a href="...">2017-03-18 10:30:30</a></td><td>34</td></tr>
  <tr><td><a href="...">2017-03-18 10:47:50</a></td><td>5C</td></tr>
  <tr><td>...</td><td>...</td></tr>
  </table>

• Admin-tilassa jokaisen rastileimauksen kellonajasta on linkki sivulle, jossa pääsee muokkaamaan kyseisen rastileimauksen tietoja.
  • Sivulla on oltava lomake, jossa voi muokata leimausaikaa ja valita leimatun rastin alasvetovalikosta.
  • Lomakkeella on myös "poista leimaus"-kohta, jonka voi ruksata, jos haluaa poistaa kyseisen leimauksen.
  • Leimaukseen liittyvää joukkuetta ei voi vaihtaa.