Andmebaasiga liidestamine

Selles ainekursuses tuleb teil demonstreerida, et oskate luua rakendust, mis kasutab relatsioonilist andmebaasi. Lisaks relatsioonilisele andmebaasile võib projektides kasutada ka teisi andmebaase. Punkte annavad kolm võtet:

1. Andmete salvestamine andmebaasi
   • Teie veebirakendusest peab saama lisada andmeid andmebaasi.
2. Andmete küsimine andmebaasist
   • Teie veebirakendusest peab saama ligi vähemalt osadele andmebaasi lisatud andmetele.
   • Teie lahenduses peab olema kasutatud mitme relatsioonilise andmebaasi tabeli omavahelist liitmist. Andmetabelite liitmine tuleb teha efektiivselt ehk siis liitmine tuleb teha andmebaasiserveris (st. kasutada tuleb SQL keele JOIN lauset).
3. Agregeeritud andmete esitamine
   • Agregeeritud ehk koondatud andmed on andmed, mis arvutatakse üle mitme andmetabeli rea (nt. kasutades SQL keele GROUP BY lauset ja funktsioone nagu COUNT või SUM).

Andmebaasi defineeriv skript (andmebaasi loomise/muutmise skript) tuleks talletada tervikluse tagamise ja hindamise eesmärgil koodihoidlas.

Andmebaasi poole pöördumine

Andmebaasisuhtlus algab alati ühenduse loomisega ja lõpeb ühenduse sulgemisega. Andmebaasiserverid reeglina hävitavad sulgemata ühendused mõningase ooteaja järel (nt. 30 minutit või rohkem). Ühenduste sulgemata jätmine võib põhjustada üheaegsete ühenduste piirini jõudmist mis takistab uute ühenduste loomist kuni mõni vana ühendus sulgetakse. Tavaliselt on andmebaasidel 3-4 üheaegse ühenduse piirang (võib olla seotud kasutajate arvuga). Seega on veebirakenduse ülesanne tagada, et ühendused saaksid õigeaegselt suletud. Kuna ühenduse loomine ja sulgemine võtavad aega, on soovitav kõik ühe veebipäringu käitamise aegsed andmebaasipäringud teha ühe ja sama ühenduse peal (st. mitte luua iga päringu jaoks uut ühendust). Mõned veebiarendusraamistikud toetavad ka ühenduse jagamist erinevate veebipäringute vahel, mis võib olla vajalik väga suure koormusega lehtede korral. Väiksemate koormuste korral tuleks eelistada eri kasutajate päringute lahus hoidmist. Olenevalt rakenduse laadist võib olla kohane veebirakenduse kasutaja vahendamine andmebaasiserverile.

PHP ja MySQL

PHP ja ka teiste hallatud mäluga keelte puhul tuleb olla hoolas haldamata ressursside osas. Näiteks PHP mysqli liidese puhul tuleb kindlasti vabastada kõigi päringute vastused - vastasel juhul võib tekkida probleeme mälukasutuse (mäluleke) ja anmdebaasiliidese kasutamisega (poolikud operatsioonid). Ehk siis, PHP mysqli liidese õige kasutamine näeb välja selline (laetakse kasutaja nimi ja talletatakse viimane "nägemise" aeg):

 $connection = mysqli_connect($server, $user, $pass, $db); // loome AB ühenduse
 $query = mysqli_prepare($connection, 'SELECT k.name AS name, k.id AS id FROM t_kasutaja AS k INNER JOIN t_grupp AS g WHERE k.grupp_id=g.id AND k.id=?'); // valmistame päringu ette
 mysqli_stmt_bind_param($query, $kid); // anname id-le väärtuse (väärtust saab hiljem muuta $kid väärtust muutes)
 mysqli_stmt_execute($query); // saadame päringu AB-le
 if(mysqli_stmt_store_result($query)) // talletame vastuse kiiremaks käitluseks
 {
  // siin saab kasutada mysqli_stmt_bind_result ja mysqli_stmt_fetch meetodeid tulemuste läbi käimimseks.
  $query2 = mysqli_prepare($connection, 'UPDATE t_kasutaja SET viimane_aeg=NOW() WHERE id=?'); // valmistame päringu ette
  mysqli_stmt_bind_param($query2, $kid); // anname id-le väärtuse (väärtust saab hiljem muuta $kid väärtust muutes)
  mysqli_stmt_execute($query2); // sellel päringul vastust pole
  mysqli_stmt_free_result($query); // vabastame päringu vastuse
  mysqli_stmt_close($query); // sulgeme lause
 }
 mysqli_close($connection); // sulgeme AB ühenduse

Objektorienteeritult näeb sama asi välja järgmine:

 $mysql = mysqli($server, $user, $pass, $db); // loome AB ühenduse
 $query = $mysql->prepare('SELECT k.name AS name, k.id AS id FROM t_kasutaja AS k INNER JOIN t_grupp AS g WHERE k.grupp_id=g.id AND k.id=?'); // valmistame päringu ette
 $query->bind_param($kid); // anname id-le väärtuse (väärtust saab hiljem muuta $kid väärtust muutes)
 $query->execute(); // saadame päringu AB-le
 if($query->store_result()) // talletame vastuse kiiremaks käitluseks
 {
  // siin saab kasutada bind_result ja fetch meetodeid tulemuste läbi käimimseks.
  $query2 = $mysql->prepare('UPDATE t_kasutaja SET viimane_aeg=NOW() WHERE id=?'); // valmistame päringu ette
  $query2->bind_param($kid); // anname id-le väärtuse (väärtust saab hiljem muuta $kid väärtust muutes)
  $query2->execute(); // sellel päringul vastust pole
  $query->freeResult(); // vabastame päringu vastuse
  $query->close(); // sulgeme lause
 }
 $mysql->close(); // sulgeme AB ühenduse

Ka ilma lauseta (statement) tuleks päringu (meetod query ja analoogid) vastused vabastada.

Nippe

1. Kapseldage andmebaasiga suhtlemine eraldi klassi. See võimaldab teil andmebaasiühenduse loomine panna konstruktorisse (meetod __construct) ja ühenduse sulgemise destruktorisse (meetod __destruct) ning lihtsalt kasutada sama anmdebaasiühendust kogu oma rakendusest.
2. Seadke oma arendusmasinas rakenduse MySQL kasutaja üheaegselt lubatud ühenduste arvuks 2-4, mis võimaldab kiiresti tuvastada sulgemata ühendustest tingitud probleeme. Ühenduste arvu piiramise näited leiate MySQL dokumentatsioonist.

Head praktikad

Hea praktika kohaselt ei tohiks rakendused manipuleerida otse tabeleid (erandiks on väga väikesed süsteemid). Hästi projekteeritud lahendus eraldab andmete haldamise ja äriloogika jättes andmete haldamise andmebaasiadministraatori mureks ja äriloogika programmeerijate mureks. See tähendab, et andmebaasiadministraator ja programmeerijad lepivad kokku liideses, mille vahendusel rakendus andmeid manipuleerib. See liides koosneb andmebaasi vaadete ja protseduuride loetelust. Selline tööjaotus annab väga palju eeliseid võrreldes otsepöördustega andmebaasitabelitele. Mõned olulisemad neist on:

• See jätab andmebaasiadministraatorile võimaluse optimeerida andmete füüsilist estitust ja kasutada andmebaasimootori poolt optimeeritud päringutehnikaid (vähendab edastatavaid andmemahtusid rakenduse ja andmebaasi vahel ning programmeerijad ei pea tegelema päringu ja admestruktuuride optimeerimisega madalal tasemel).
• Samuti võimaldab see andmebaasi ja rakenduse paralleelarendamist.
• See aitab tagada saavutada parima võimaliku andmekihi kiiruse.
• See võimaldab õiguste kontrolli andmekirjete tasemel - konfidentsiaalsuse lekke risk on väiksem kui rakendus saab alati ligi ainult sisse loginud kasutaja andmetele.
• See võimaldab lihtsamini uurida ja lahendada andmepäringutega seotud jõudlusprobleeme.

Objekt-relatsiooniliste kaardistuste (ORM - Object-Relational Mapping) kasutamine on kiire ja mugav lahendus prototüüpimiseks kuid lisab täiendavaid piiranguid sellele, mida saab optimeerida ja kuidas saab päringuid teha (ning lisab täiendava vahekihi andmebaasi ja rakenduse vahele). Samuti ei oska ORM-d arvestada andmebaasimootorite eripäradega ja võivad seetõttu tekitada probleeme suure pideva koormusega töökeskkondades. ORM-i efektiivne kasutamine on seetõttu keerulisem ning programmeerijate jaoks ajakulukam kui andmebaasiliidese defineerimine, kuid võib olla ainus lahendus projektitiimides, kus puuduvad andmebaasiarendajad ja/või -administraatorid. Sellegipoolest on soovituslik ORM-e kasutada vaid prototüüpimisel või väga väikese koormuse ja kasutajaskonnaga projektide korral (nt. enamik tudengiprojekte kuulub siia alla) kuid jõudlustundlike süsteemide puhul tuleks neid vältida või kasutada piiratud mahus.

Hindamine

Viiteid

• W3Schools SQL sissejuhatus - http://www.w3schools.com/SQl/default.asp
• SQL dialektide võrdlus - http://troels.arvin.dk/db/rdbms/
  • T-SQL reference - http://technet.microsoft.com/en-us/library/bb510741.aspx
  • MySQL reference - http://dev.mysql.com/doc/refman/5.7/en/sql-syntax.html
  • PostgreSQL reference - http://www.postgresql.org/docs/9.3/interactive/sql.html
  • Oracle SQL reference - http://docs.oracle.com/cd/E11882_01/server.112/e41084/toc.htm
  • IBM SQL reference - http://www.ibm.com/developerworks/data/library/techarticle/0206sqlref/0206sqlref.html
• Top 10 steps for optimising SQL database access
  • Part 1 - http://www.codeproject.com/Articles/34372/Top-10-steps-to-optimize-data-access-in-SQL-Server
  • Part II (best practices) - http://www.codeproject.com/Articles/35665/Top-10-steps-to-optimize-data-access-in-SQL-Server
  • Part III - http://www.codeproject.com/Articles/36091/Top-10-steps-to-optimize-data-access-in-SQL-Serv-3
  • Part IV - http://www.codeproject.com/Articles/37089/Top-10-steps-to-optimize-data-access-in-SQL-Serv-5
  • Part V - http://www.codeproject.com/Articles/43629/Top-10-steps-to-optimize-data-access-in-SQL-Serv-4
• Databasebest practices - http://www.c2.com/cgi/wiki?DatabaseBestPractices
• ORM vs SQL Roundup - http://www.youtube.com/watch?v=Wz1_GYc4GmU
• ORM "n+1" probleem (st. miks on oluline laadida ORM-ga andmeid laisalt) - http://stackoverflow.com/questions/97197/what-is-the-n1-selects-issue
• Azure Database Limitations and Practices - https://msdn.microsoft.com/en-us/library/azure/ee336245.aspx