Viikon 4 etapit
  • Sovelluksessa on ainakin kolme tietokantataulua, joista jokainen on käytössä.
  • Sovelluksessa on ainakin yksi monimutkaisempi yhteenvetokysely, jonka tulokset näytetään käyttäjälle.
  • Ulkoasun viilaus. Sovelluksessa käytetään Bootstrap-kirjastoa (tai muuta vastaavaa) ulkoasun tyylittelyssä.
  • Toiminnallisuuden täydentäminen.

Tietokantasovellus-kurssin neljännen viikon materiaali sisältää esimerkin perinnän käytöstä toisteisen sisällön vähentämiseksi tietokohteiden yhteydessä. Tämän lisäksi materiaalissa tarkastellaan yhteenvetokyselyiden rakentamista sekä tulosten näyttämistä. Lopulta tutustutaan lyhyesti Bootstrap-kirjaston käyttöön materiaalin ulkoasun tyylittelyssä.

Huomaathan, että materiaali ei sisällä kaikkea harjoitustyöhön tarvittavaa. Oman harjoitustyön tulee luonnollisesti olla erillinen tästä materiaalista.

Tietokohteiden lisääminen

Edellisen viikon esimerkissä on kaksi tietokantataulua kuvaavaa luokkaa: Task ja User. Luokat ovat seuraavanlaiset. 

from application import db
  
class Task(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    date_created = db.Column(db.DateTime, default=db.func.current_timestamp())
    date_modified = db.Column(db.DateTime, default=db.func.current_timestamp(),
        onupdate=db.func.current_timestamp())

    name = db.Column(db.String(144), nullable=False)
    done = db.Column(db.Boolean, nullable=False)

    account_id = db.Column(db.Integer, db.ForeignKey('account.id'),
        nullable=False)

    def __init__(self, name):
        self.name = name
        self.done = False

from application import db
from application.models import BaseTable

class User(db.Model):

    __tablename__ = "account"
  
    id = db.Column(db.Integer, primary_key=True)
    date_created = db.Column(db.DateTime, default=db.func.current_timestamp())
    date_modified = db.Column(db.DateTime, default=db.func.current_timestamp(),
        onupdate=db.func.current_timestamp())

    name = db.Column(db.String(144), nullable=False)
    username = db.Column(db.String(144), nullable=False)
    password = db.Column(db.String(144), nullable=False)

    tasks = db.relationship("Task", backref='account', lazy=True)
  
    def __init__(self, name):
        self.name = name
  
    def get_id(self):
        return self.id
  
    def is_active(self):
        return True

    def is_anonymous(self):
        return False

    def is_authenticated(self):
        return True

Kuten huomaamme, luokissa on toisteisuutta. Sekä Task että User sisältävät numeerisen pääavaimen, tiedon luomishetkestä, ja tiedon viimeisestä päivityshetkestä. Olio-ohjelmoinnissa vastaava toisteisuus tyypillisesti yleistetään abstraktiin luokkaan. Sama onnistuu myös tässä.

Luodaan application-kansioon tiedosto models.py, ja luodaan tiedostoon "abstrakti" luokka Base. Luokka määritellään "abstraktiksi" SQLAlchemyä varten rivillä __abstract__ = True. Luokka Base tulee sisältämään pääavaimen, tiedon luomishetkestä sekä tiedon viimeisestä päivityshetkestä. 

from application import db

class Base(db.Model):

    __abstract__ = True
  
    id = db.Column(db.Integer, primary_key=True)
    date_created = db.Column(db.DateTime, default=db.func.current_timestamp())
    date_modified = db.Column(db.DateTime, default=db.func.current_timestamp(),
        onupdate=db.func.current_timestamp())

Kun luokat Task ja User perivät luokan Base, ei luokassa Base määriteltyjä muuttujia tarvitse määritellä uudestaan luokissa Task ja User. Luokan perintä onnistuu lisäämällä perittävän luokan nimi määriteltävän luokan perään sulkuihin. Alla luokat Task ja User uudelleen määriteltyinä siten, että ne perivät luokan Base. 

from application import db
from application.models import Base

class Task(Base):

    name = db.Column(db.String(144), nullable=False)
    done = db.Column(db.Boolean, nullable=False)

    account_id = db.Column(db.Integer, db.ForeignKey('account.id'), nullable=False)

    def __init__(self, name):
        self.name = name
        self.done = False

from application import db
from application.models import Base

class User(Base):

    __tablename__ = "account"

    name = db.Column(db.String(144), nullable=False)
    username = db.Column(db.String(144), nullable=False)
    password = db.Column(db.String(144), nullable=False)

    tasks = db.relationship("Task", backref='account', lazy=True)
  
    def __init__(self, name):
        self.name = name

    def get_id(self):
        return self.id
  
    def is_active(self):
        return True

    def is_anonymous(self):
        return False

    def is_authenticated(self):
        return True

Tietokantataulujen määrän kasvaessa vastaavia abstrakteja luokkia voi luoda useampia. Esimerkiksi nimen sisältävä abstrakti luokka saattaa olla hyödyllinen..

Yhteenvetokyselyt

Yhteenvetokyselyt (aggregate query, aggregate function) ovat kyselyitä, joilla haetaan yhteenvetotietoa tietokannasta. Käyttäjiä ja tehtäviä sisältävässä esimerkissämme yhteenvetokyselyitä voisivat olla muunmuassa "kuinka monta tekemätöntä tehtävää tietokannassa on?", "kuinka monta tehtävää kullakin käyttäjällä on?", ja "ketkä käyttäjät ovat tehneet kaikki heille määrätyt tehtävät?".

SQL-kielellä SQLitessä kyselyt olisivat muotoa: 

SELECT COUNT(Task.id) FROM Task WHERE Task.done = 0; 

SELECT Account.id, Account.name, COUNT(Task.id) FROM Account
    LEFT JOIN Task ON Task.account_id = Account.id
    GROUP BY Account.id

SELECT Account.id, Account.name FROM Account
    LEFT JOIN Task ON Task.account_id = Account.id
    WHERE (Task.done IS null OR Task.done = 1)
    GROUP BY Account.id
    HAVING COUNT(Task.id) = 0

Yhteenvetokyselyt määritellään models.py -tiedostoon luokkakohtaisina metodeina. Alla esimerkki, missä viimeinen yllä kuvatuista kyselyistä suoritetaan sekä kyselyn tulokset tulostetaan konsoliin. 

from application import db
from application.models import Base

from sqlalchemy.sql import text

class User(Base):

    __tablename__ = "account"

    name = db.Column(db.String(144), nullable=False)
    username = db.Column(db.String(144), nullable=False)
    password = db.Column(db.String(144), nullable=False)

    tasks = db.relationship("Task", backref='account', lazy=True)
  
    def __init__(self, name):
        self.name = name

    def get_id(self):
        return self.id
  
    def is_active(self):
        return True

    def is_anonymous(self):
        return False

    def is_authenticated(self):
        return True


    @staticmethod
    def find_users_with_no_tasks():
        stmt = text("SELECT Account.id, Account.name FROM Account"
                    " LEFT JOIN Task ON Task.account_id = Account.id"
                    " WHERE (Task.done IS null OR Task.done = 1)"
                    " GROUP BY Account.id"
                    " HAVING COUNT(Task.id) = 0")
        res = db.engine.execute(stmt)
  
        for row in res:
            print(row[0])
            print(row[1])

Kun luokassa User määriteltyä metodia find_users_with_no_tasks kutsutaan, tulostuu kyselyn tulosrivit konsoliin.

Metodia voi luonnollisesti muokata siten, että se palauttaa arvon. Alla tuloksista luodaan lista, joka sisältää jokaiseen tulosriviin liittyvän hajautustaulun. 

# ...
  
    @staticmethod
    def find_users_with_no_tasks():
        stmt = text("SELECT Account.id, Account.name FROM Account"
                     " LEFT JOIN Task ON Task.account_id = Account.id"
                     " WHERE (Task.done IS null OR Task.done = 1)"
                     " GROUP BY Account.id"
                     " HAVING COUNT(Task.id) = 0")
        res = db.engine.execute(stmt)

        response = []
        for row in res:
            response.append({"id":row[0], "name":row[1]})

        return response

Nyt metodia voidaan kutsua muualta sovelluksesta. Luodaan sovellukseen mahdollisuus tehtäviä tarvitsevien käyttäjien listaamiseen. Lisätään ensin application-kansion tiedostoon views.py kutsu metodiin ja lisätään metodikutsun palauttama lista osaksi pääsivun luomista. 

from flask import render_template
from application import app
from application.auth.models import User

@app.route('/')
def index():
    return render_template("index.html", needs_tasks=User.find_users_with_no_tasks())

Muokataan tämän jälkeen etusivua (eli templates-kansion tiedostoa index.html) siten, että se listaa needs_tasks-listan sisällön mikäli listalla on sisältöä. Alla hajautustaulun avaimiin viitataan HTML-tiedostosta suoraan niiden nimillä. 

{% extends "layout.html" %}

{% block body %}
<p>
  Hello world!
</p>

{% if needs_tasks is defined %}
<p>
  The following users need work:
</p>

<ul>
  {% for user in needs_tasks %}
  <li>
    {{user.id}} {{user.name}}
  </li>
  {% endfor %}
</ul>

{% endif %}

{% endblock %}

Nyt sovelluksen etusivu näyttää (esimerkiksi) seuraavalta. Alla olevassa esimerkissä tietokannassa on yksi käyttäjä, jolla ei ole tällä hetkellä tehtäviä.

Etusivulla näkyy käyttäjä, joka tarvitsee työtä.

 

SQL-injektiot, parametrit ja lisää aiheesta

Kyselyihin halutaan usein lisätä parametreja. Mikäli parametrit lisätään suoraan osaksi SQL-lausetta, luodaan mahdollisuus SQL-injektioon. SQLAlchemy tarjoaa mahdollisuuden parametrien lisäämiseen text-metodin palauttaman arvon sisältämän params-metodin avulla. Tällöin parametrit määritellään osaksi tekstimuodossa annettavaa kyselyä kaksoispisteiden avulla, esim arvo = :parametri, ja parametrien arvot annetaan metodilla params.

Muokataan yllä olevaa esimerkkiä siten, että voimme valita joko edellisen esimerkin tai toiminnallisuuden, jolla haemme käyttäjiä, joilla ei ole koskaan ollutkaan tehtäviä tai joilla on tekemättömiä tehtäviä. Tämä tapahtuu muuntamalla kyselyä siten, että Task.done-kentän arvo voidaan määritellä parametrina. 

# ...
    @staticmethod
    def find_users_with_no_tasks(done=0):
        stmt = text("SELECT Account.id, Account.name FROM Account"
                     " LEFT JOIN Task ON Task.account_id = Account.id"
                     " WHERE (Task.done IS null OR Task.done = :done)"
                     " GROUP BY Account.id"
                     " HAVING COUNT(Task.id) = 0").params(done=done)
        res = db.engine.execute(stmt)

        response = []
        for row in res:
            response.append({"id":row[0], "name":row[1]})

        return response

Nyt metodikutsulle voitaisiin antaa myös parametrina arvo 1, jolloin kyselyn toiminnallisuus muuttuu.

SQLAlchemy tarjoaa myös mahdollisuuden yhteenvetokyselyiden tekemiseen ohjelmallisesti. Harjoitustyössä yhteenvetokyselyt tulee kirjoittaa SQL-kielellä. Aiheesta lisää http://docs.sqlalchemy.org/en/latest/orm/tutorial.html.

Ulkoasun viilaus

Isohko osa verkkosovelluksista käyttää nykyään ulkoasun määrittelyyn Bootstrap-kirjastoa. Osoitteessa https://getbootstrap.com/docs/4.0/getting-started/introduction/ on ohjeistus alkuun pääsemiseksi ja osoitteessa https://getbootstrap.com/docs/4.0/examples/ on nippu esimerkkejä. Verkossa on myös liuta muita sivustoja kuten https://startbootstrap.com/, jotka tarjoavat Bootstrap-teemoja.

Muokataan tässä sovellustamme siten, että valikko ja kirjautuminen on yläpalkissa, ja tehtävien listaus on hieman tyylikkäämpi.

Muokkaus aloitetaan lisäämällä templates-kansion layout.html-tiedostoon tarvittavat riippuvuudet. 

  <!DOCTYPE html>
  <html>
    <head>
      <meta charset="utf-8">
      <title>TodoApplication</title>
      <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
    </head>
    
    <body>
      {% if current_user.is_authenticated %}
      <p>
	Kirjautunut nimellä {{ current_user.name }}. <a href="{{ url_for('auth_logout') }}">Kirjaudu ulos</a>
      </p>
      {% else %}
      <a href="{{ url_for('auth_login') }}">Kirjaudu</a>
      {% endif %}
      
      <ul>
	<li><a href="{{ url_for('tasks_index') }}">List tasks</a></li>
	<li><a href="{{ url_for('tasks_form') }}">Add a task</a></li>
      </ul>
      
      {% block body %}
      <p>
	Content.
      </p>
      {% endblock %}

      <script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script>
      <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js" integrity="sha384-ApNbgh9B+Y1QKtv3Rn7W3mgPxhU9K/ScQsAP7hUibX39j7fakFPskvXusvfa0b4Q" crossorigin="anonymous"></script>
      <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
    </body>
  </html>

Nyt Bootstrap on sovelluksen käytössä. Tämä näkyy heti etusivun ulkoasun muutoksena, sillä fontit näyttävät hieman erilaiselta.

Ulkoasu muuttunut. Sivun fontit näyttävät erilaiselta.

Lisätään seuraavaksi yläpalkki. Käytetään osoitteessa https://getbootstrap.com/docs/4.0/examples/navbar-static/ olevaa sivua esimerkkinä. Sivun lähdekoodista huomaamme, että yläpalkin luomiseen on käytetty seuraavaa koodia. 

<nav class="navbar navbar-expand-md navbar-dark bg-dark mb-4">
  <a class="navbar-brand" href="#">Top navbar</a>
  <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarCollapse" aria-controls="navbarCollapse" aria-expanded="false" aria-label="Toggle navigation">
    <span class="navbar-toggler-icon"></span>
  </button>
  <div class="collapse navbar-collapse" id="navbarCollapse">
    <ul class="navbar-nav mr-auto">
      <li class="nav-item active">
        <a class="nav-link" href="#">Home <span class="sr-only">(current)</span></a>
      </li>
      <li class="nav-item">
        <a class="nav-link" href="#">Link</a>
      </li>
      <li class="nav-item">
        <a class="nav-link disabled" href="#">Disabled</a>
      </li>
    </ul>
    <form class="form-inline mt-2 mt-md-0">
      <input class="form-control mr-sm-2" type="text" placeholder="Search" aria-label="Search">
      <button class="btn btn-outline-success my-2 my-sm-0" type="submit">Search</button>
    </form>
  </div>
</nav>

Muokataan yläpalkista käyttötarkoitukseemme sopiva. Käyttöliittymässä on linkit tehtävien listaukseen sekä tehtävien lisäämiseen. Näiden lisäksi käyttäjä voi kirjautua. 

  <!DOCTYPE html>
  <html>
    <head>
      <meta charset="utf-8">
      <title>TodoApplication</title>
      <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
    </head>
    
    <body>

      <nav class="navbar navbar-expand-md navbar-dark bg-dark mb-4">
	<a class="navbar-brand" href="#">TodoApplication</a>
	<button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarCollapse" aria-controls="navbarCollapse" aria-expanded="false" aria-label="Toggle navigation">
          <span class="navbar-toggler-icon"></span>
	</button>
	<div class="collapse navbar-collapse" id="navbarCollapse">
          <ul class="navbar-nav mr-auto">
            <li class="nav-item">
              <a class="nav-link" href="{{ url_for('tasks_index') }} ">List tasks</a>
            </li>
            <li class="nav-item">
              <a class="nav-link" href="{{ url_for('tasks_form') }} ">Add a task</a>
            </li>
          </ul>
          <div class="form-inline mt-2 mt-md-0">
	    {% if current_user.is_authenticated %}
	    <a href="{{ url_for('auth_logout') }}">Hello {{ current_user.name }} -- Logout</a>
	    {% else %}
	    <a href="{{ url_for('auth_login') }}">Login</a>
	    {% endif %}
          </div>
	</div>
      </nav>
      
      {% block body %}
      <p>
	Content.
      </p>
      {% endblock %}

      <script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script>
      <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js" integrity="sha384-ApNbgh9B+Y1QKtv3Rn7W3mgPxhU9K/ScQsAP7hUibX39j7fakFPskvXusvfa0b4Q" crossorigin="anonymous"></script>
      <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
    </body>
  </html>

Sovellus näyttää nyt seuraavanlaiselta.

Ulkoasussa on yläpalkki.

 

Suurimmassa osassa sovelluksista käyttäjälle näytettävä sisältö on ruudulla ainakin osittain keskitetty. Myös tähän löytyy valmis esimerkki -- hyödynnetään osoitteessa https://getbootstrap.com/docs/4.0/examples/starter-template/ olevan sivun tyyliä sivun keskiosan asettelussa. Sivun lähdekoodia tarkastelemalla huomaamme, että asettelu onnistuu seuraavalla tavalla. 

<main role="main" class="container">

  <div class="starter-template">
    <h1>Bootstrap starter template</h1>
    <p class="lead">Use this document as a way to quickly start any new project.<br> All you get is this text and a mostly barebones HTML document.</p>
  </div>

</main>

Tuodaan tyyli osaksi tiedostoa layout.html. 

  <!DOCTYPE html>
  <html>
    <head>
      <meta charset="utf-8">
      <title>TodoApplication</title>
      <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
    </head>
    
    <body>

      <nav class="navbar navbar-expand-md navbar-dark bg-dark mb-4">
	<a class="navbar-brand" href="#">TodoApplication</a>
	<button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarCollapse" aria-controls="navbarCollapse" aria-expanded="false" aria-label="Toggle navigation">
          <span class="navbar-toggler-icon"></span>
	</button>
	<div class="collapse navbar-collapse" id="navbarCollapse">
          <ul class="navbar-nav mr-auto">
            <li class="nav-item">
              <a class="nav-link" href="{{ url_for('tasks_index') }} ">List tasks</a>
            </li>
            <li class="nav-item">
              <a class="nav-link" href="{{ url_for('tasks_form') }} ">Add a task</a>
            </li>
          </ul>
          <div class="form-inline mt-2 mt-md-0">
	    {% if current_user.is_authenticated %}
	    <a href="{{ url_for('auth_logout') }}">Hello {{ current_user.name }} -- Logout</a>
	    {% else %}
	    <a href="{{ url_for('auth_login') }}">Login</a>
	    {% endif %}
          </div>
	</div>
      </nav>

      
      <main role="main" class="container">
	{% block body %}
	<p>
	  Content.
	</p>
	{% endblock %}
      </main>

      <script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script>
      <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js" integrity="sha384-ApNbgh9B+Y1QKtv3Rn7W3mgPxhU9K/ScQsAP7hUibX39j7fakFPskvXusvfa0b4Q" crossorigin="anonymous"></script>
      <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
    </body>
  </html>

Sovellus näyttää nyt seuraavanlaiselta.

Ulkoasussa on yläpalkki ja sisältö on keskitetty.

 

Sovelluksen sisäsivujen kuten lomakkeiden ulkoasun viilauksessa on hyvä käyttää myös Bootstrap-kirjastoa. Sivun https://getbootstrap.com/docs/4.0/getting-started/introduction/ hakutoiminnallisuuden kautta löytää suurimman osan yksinkertaisilla verkkosivuilla tarvituista komponenteista. Esimerkiksi lomakkeiden tyylittelyyn löytyy ohjeistusta osoitteesta https://getbootstrap.com/docs/4.0/components/forms/.

Sisällysluettelo