De database in Zend Framework

22-11-2010

Dit is het zesde deel van een serie over de bouw van een nieuwe website op basis van Zend Framework. Kijk in de zijbalk voor de linkjes naar de eerste delen.

De kern van de meeste applicaties wordt gevormd door de aanwezige data. Die kunnen op veel verschillende manieren worden opgeslagen, maar bij meer complexe toepassingen zal er vrijwel altijd sprake zijn van een database. ZF zou natuurlijk onvolledig zijn als er niet een uitgebreide serie componenten beschikbaar was om toegang te bieden tot de database, zijn tabellen en gegevens.

Twee klassen zijn met name van belang: Zend_Db_Table_Abstract, en Zend_Db_Table_Row_Abstract. De eerste biedt een aantal methoden om toegang te krijgen tot de rijen gegevens in een tabel; de tweede tot de eigenschappen (de kolommen) van een afzonderlijke rij.

Opbouw van de database

Voordat we ons gaan richten op de interactie tussen de elesio en de database, moeten we eerst de juiste tabellen inrichten. Dat doen we op basis van de beschrijving in hoofdstuk 2, Uitwerking van het concept. In Mysql Workbench, het freeware GUI programma van Mysql, heb ik dit schema gemaakt:

[caption id="attachment_689" align="alignleft" width="300" caption="Klik op de afbeelding om haar in groter formaat te zien."][/caption]

Centraal in het schema staan twee tabellen, products en articles.

Products is de moeder van twee afgeleide tabellen, books en gadgets. Boeken en apparaten worden beschouwd als de twee concrete producttypes die besproken kunnen worden op onze website. Die delen een aantal eigenschappen die in de moedertabel worden opgeslagen: het ean (een uniek productnummer, vergelijkbaar met het vroegere ISBN; maar dan algemener), de prijs, de invoerdatum, een herzieningsdatum, en een gemiddelde waardering van de gebruiker. Books en gadgets voeren daar nog hun specifieke eigenschappen aan toe, zoals titel, auteur, producent enzovoorts. Via de index products_id wordt de relatie gelegd met products tabel. De relatie tussen books en gadgets enerzijds en products anderzijds is ��n op ��n.

In articles wordt alle content opgeslagen. Over elk product kunnen meerdere artikelen worden geschreven. Die relatie wordt belichaamd door de vreemde sleutel products_id in articles. Bovendien kan een artikel alleen worden geschreven door een geregistreerde gebruiker. Deze bevindt zich in de users tabel, en komt terug in de article tabel door de vreemde sleutel users_id.

Er zijn voor de users vier verschillende rollen gedefinieerd: unconfirmed, registered, writer, admin. Wie zich registreert, is in eerste instantie een nieuwe, unconfirmed gebruiker. Zodra hij op de link in een bevestigingsmail klikt, wordt hij registered. Daarna is het aan de admin om een registered user te promoveren tot writer of zelfs tot admin. We hebben nog niet de exacte verschillen tussen deze rollen gedefinieerd, maar we willen onze applicatie toekomstbestendig maken; daarom is het handig om onze database daarop nu al in te richten.�Wachtwoorden in onze applicatie worden versleuteld opgeslagen (sha1).

De tabel images bevat onze afbeeldingen. Dat kunnen illustraties bij artikelen zijn, maar ook profielfotos of afbeeldingen van nieuwe gadgets. Er worden twee kolommen in deze tabel gedefinieerd die duidelijkheid geven over de aard van de afbeelding: type is een aanduiding voor het formaat (thumb, large, original), reference heeft betrekking op de context van de afbeelding: gebruiker, product of artikel.

In de tabel ratings worden de individuele beoordelingen van de gebruikers over verschillende producten opgeslagen. Van elke beoordeling wordt een userId opgeslagen, alsmede productsId waarop de rating van toepassing is. Overigens wordt, omwille van performance overwegingen, de gemiddelde rating in de tabel products zelf opgeslagen. Onze applicatie werkt sneller als niet bij elke request dat gemiddelde moet worden uitgerekend, maar dit meteen uit de tabel kan worden uitgelezen. Dat levert een beetje extra overhead op bij het geven van een rating, maar dat is de moeite meer dan waard.

In de tabel reacties worden reacties op artikelen opgeslagen. Vreemde sleutels zijn de users_id en de article_id.

Dan zijn ten slotte de twee tabellen advertisements en partners nog van belang. In de tabel partners worden de zakelijke partners van elesio opgeslagen. Dat kunnen eenmalige relaties zijn (een enkele adverteerder), maar ook bedrijven met wie elesio een affiliate relatie aangaat. Wat affiliate is, hebben we nog niet gedefinieerd, maar wel weten we dat de partnerinformatie cruciaal is voor de plaatsing en administratie van advertenties. Het is voorstelbaar dat partnerinformatie uiteindelijk aan onze office-systemen wordt kenbaar gemaakt via een webservice of een xml-formaat.

Hier vindt je het volledige sql-script:

[codesyntax lang="sql" lines="normal" title="Mysql create script"]

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
DROP SCHEMA IF EXISTS elesio ;
CREATE SCHEMA IF NOT EXISTS elesio DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE elesio ;

-- Table elesio.users

CREATE  TABLE IF NOT EXISTS elesio.users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  userName VARCHAR(255) NOT NULL ,
  email VARCHAR(255) NOT NULL ,
  password VARCHAR(45) NOT NULL ,
  firstName VARCHAR(45) NULL ,
  lastName VARCHAR(85) NULL ,
  lastNamePrefix VARCHAR(45) NULL ,
  role ENUM('unconfirmed','registered','writer','admin') NOT NULL DEFAULT 'unconfirmed' COMMENT 'Someone who registers the first time gets unconfirmed. Only after replying to an email, will he be registered. Its up to the admin to promote him to writer' ,
  membershipDate TIMESTAMP NOT NULL ,
  lastLoginDate DATETIME NOT NULL ,
  lastLoginIp VARCHAR(40) NOT NULL ,
  PRIMARY KEY (id) )
ENGINE = InnoDB
COMMENT = 'general user table';

-- Table elesio.products

CREATE  TABLE IF NOT EXISTS elesio.products (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  ean VARCHAR(45) NOT NULL ,
  price DOUBLE NULL ,
  creationDate TIMESTAMP NOT NULL ,
  revisionDate DATETIME NULL ,
  avgUserRating DOUBLE NOT NULL DEFAULT -1 ,
  PRIMARY KEY (id) ,
  INDEX ean (ean ASC) )
ENGINE = InnoDB
COMMENT = 'References to books and gadgets tables';

-- Table elesio.books

CREATE  TABLE IF NOT EXISTS elesio.books (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  title VARCHAR(255) NULL ,
  subtitle VARCHAR(255) NULL ,
  publisher VARCHAR(255) NULL ,
  remarks TEXT NULL ,
  products_id INT UNSIGNED NOT NULL ,
  PRIMARY KEY (id, products_id) ,
  INDEX fk_books_products1 (products_id ASC) ,
  CONSTRAINT fk_books_products1
    FOREIGN KEY (products_id )
    REFERENCES elesio.products (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'References the products table by productId';

-- Table elesio.gadgets

CREATE  TABLE IF NOT EXISTS elesio.gadgets (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  name VARCHAR(255) NOT NULL ,
  subname VARCHAR(255) NULL ,
  producer VARCHAR(255) NULL ,
  description TEXT NULL ,
  products_id INT UNSIGNED NOT NULL ,
  PRIMARY KEY (id, products_id) ,
  INDEX fk_gadgets_products (products_id ASC) ,
  CONSTRAINT fk_gadgets_products
    FOREIGN KEY (products_id )
    REFERENCES elesio.products (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'References Products';

-- Table elesio.articles

CREATE  TABLE IF NOT EXISTS elesio.articles (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  reactionsAllowed TINYINT(1)  NOT NULL DEFAULT 1 ,
  header VARCHAR(255) NULL ,
  lead TEXT NULL ,
  body TEXT NULL ,
  creationDate TIMESTAMP NOT NULL ,
  revisionDate DATETIME NULL ,
  publicationDate DATETIME NULL ,
  products_id INT UNSIGNED NOT NULL ,
  users_id INT UNSIGNED NOT NULL ,
  PRIMARY KEY (id, products_id, users_id) ,
  INDEX fk_articles_products1 (products_id ASC) ,
  INDEX fk_articles_users1 (users_id ASC) ,
  CONSTRAINT fk_articles_products1
    FOREIGN KEY (products_id )
    REFERENCES elesio.products (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_articles_users1
    FOREIGN KEY (users_id )
    REFERENCES elesio.users (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- Table elesio.images

CREATE  TABLE IF NOT EXISTS elesio.images (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  type ENUM('thumb','large','original') NOT NULL ,
  width TINYINT(4) NULL ,
  height TINYINT(4) NULL ,
  reference ENUM('product', 'user', 'article') NOT NULL ,
  referenceId INT UNSIGNED NOT NULL ,
  PRIMARY KEY (id) ,
  INDEX fk_images_articles1 (referenceId ASC) ,
  CONSTRAINT fk_images_articles1
    FOREIGN KEY (referenceId )
    REFERENCES elesio.articles (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_images_products1
    FOREIGN KEY (referenceId )
    REFERENCES elesio.products (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_images_users1
    FOREIGN KEY (referenceId )
    REFERENCES elesio.users (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'Images belonging to either users, articles or products';

-- Table elesio.reactions

CREATE  TABLE IF NOT EXISTS elesio.reactions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  userId INT NULL ,
  userName VARCHAR(255) NOT NULL ,
  userIp VARCHAR(40) NOT NULL ,
  creationDate TIMESTAMP NOT NULL ,
  header VARCHAR(255) NULL ,
  body TEXT NULL ,
  articles_id INT UNSIGNED NOT NULL ,
  PRIMARY KEY (id, articles_id) ,
  INDEX fk_reactions_articles1 (articles_id ASC) ,
  CONSTRAINT fk_reactions_articles1
    FOREIGN KEY (articles_id )
    REFERENCES elesio.articles (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'This table contains only reactions to articles (reviews)';

-- Table elesio.ratings

CREATE  TABLE IF NOT EXISTS elesio.ratings (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  rating TINYINT(1) UNSIGNED NOT NULL ,
  userId INT UNSIGNED NULL ,
  userIp VARCHAR(40) NOT NULL ,
  products_id INT UNSIGNED NOT NULL ,
  PRIMARY KEY (id, products_id) ,
  INDEX fk_ratings_products1 (products_id ASC) ,
  CONSTRAINT fk_ratings_products1
    FOREIGN KEY (products_id )
    REFERENCES elesio.products (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- Table elesio.partners

CREATE  TABLE IF NOT EXISTS elesio.partners (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  name VARCHAR(255) NOT NULL ,
  contactName VARCHAR(255) NOT NULL ,
  contactEmail VARCHAR(255) NOT NULL ,
  contactPhone VARCHAR(45) NULL ,
  isAffiliate TINYINT(1)  NOT NULL DEFAULT 0 ,
  PRIMARY KEY (id) )
ENGINE = InnoDB;

-- Table elesio.advertisements

CREATE  TABLE IF NOT EXISTS elesio.advertisements (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  linkUrl VARCHAR(1000) NOT NULL ,
  imageUrl VARCHAR(1000) NOT NULL ,
  numViews INT NOT NULL DEFAULT 0 ,
  numClicks INT NOT NULL DEFAULT 0 ,
  validFrom DATETIME NULL ,
  validTo DATETIME NULL ,
  preferedPage ENUM('all', 'fp','bookreviews','gadgetreviews','newsarticles','allreviews') NOT NULL DEFAULT 'all' COMMENT 'fp=frontpage' ,
  partners_id INT UNSIGNED NOT NULL ,
  PRIMARY KEY (id, partners_id) ,
  INDEX fk_advertisements_partners1 (partners_id ASC) ,
  CONSTRAINT fk_advertisements_partners1
    FOREIGN KEY (partners_id )
    REFERENCES elesio.partners (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

[/codesyntax]

Let wel: we hebben nu de basis van onze database opgezet, maar dit betekent niet dat deze in graniet is gebeiteld. Integendeel: in mijn ervaring worden er nog met enige regelmaat tijdens de ontwikkeling van de code wijzingen aangebracht in tabellen: kolommen toegevoegd of verplaatst, of zelfs compleet nieuwe tabellen die gemaakt moeten worden. Zolang we met die omstandigheid maar rekening houden tijdens het schrijven van onze code, is dat niet zo erg.

ZF vertellen over onze database

Voordat we werkelijk aan de gang kunnen met onze database, is het aan te raden om een aparte user aan te maken die toegang heeft tot de mysql-gegevens. Het is niet handig om deze aan te boren als de root-user; dat brengt allerlei beveiligingsproblemen met zich mee.

We maken een nieuwe gebruiker aan in de database; die noemen we elesio_test_user met het wachtwoord elesio_test_pass. Deze krijgt uitsluitend rechten om te lezen, schrijven, wijzigen en verwijderen (CRUD create, read, update and delete) op de elesio-database.

Nu gaan we Zend_Tool inzetten om onze applicatie te vertellen over onze databasetoegang. Dat doen we door dit statement in te voeren op een commandoregel:

[codesyntax lang="text" title="ZF configureren voor gebruik van Database"]

php binzf.php configure db-adapter "username=test_elesio_user&password=test_elesio_pass&dbname=elesio&adapter=pdo_mysql" development

[/codesyntax]

Als alles goed gaat, krijg je de mededeling terug dat het application/configs/application.ini bestand is aangepast met de benodigde informatie. Deze is toegevoegd aan de sectie development, het laatste argument in de commandoregel. De lange string tussen de aanhalingstekens geeft de details over de verbinding: de adapter die wordt gebruikt (pdo_mysql in dit geval), gebruikersnaam, wachtwoord en database.

Als je de site nu ververst, is er in principe niets veranderd. De database wordt nog niet aangeroepen; Zend Framework maakt gebruik van het zogeheten lazy loading, wat betekent dat er pas een connectie tot stand komt als deze daadwerkelijk nodig is. Het is op dit moment nog een beetje lastig om te checken of het allemaal werkt. Daar gaan we snel verandering in aanbrengen.

Tabellen uitlezen

Twee verschillende Zend componenten zijn van cruciaal belang in het uitlezen en bewerken van onze tabellen. Zend_Db_Table_Abstract biedt een aantal methoden om tabellen als geheel uit te lezen. Zend_Db_Table_Row_Abstract biedt methoden voor het werken met afzonderlijke rijen (records) uit de tabel. Met Zend_Db_Table_Abstract kun je dus bijvoorbeeld een serie rijen achter elkaar uit de database halen; en elke rij kun je toewijzen aan een afzonderlijke instantie van Zend_Db_Table_Row_Abstract.

Overigens gebruik je deze klassen nooit rechtstreeks; je zult altijd je eigen klassen maken die een extensie vormen van de Zend componenten.

Laten we het eens uitproberen met de users tabel als eerste. Dat is een betrekkelijk simpele tabel. We starten opnieuw onze commandoprompt op, en geven het commando

[codesyntax lang='text' title='User klasse aanmaken']

php binzf.php create db-table Users users

[/codesyntax]

Met dit commando vertellen we onze applicatie dat er een nieuwe klasse gedefinieerd moet worden, Users genaamd, die een reflectie vormt van de tabel users in onze database. Als we nu naar onze mappenstructuur bladeren, zien we dat er een nieuwe folder DbTable is geplaatst in application/models. Binnen deze nieuwe folder is een bestand Users.php gezet, dat onze nieuwe klasse bevat.

Open deze klasse, en je zult zien dat het een extensie is van Zend_Db_Table_Abstract. De klasse is zo goed als leeg, op een variabele na: de tabelnaam wordt gezet in de protected property $_name. Zend_Db_Table_Abstract gaat ervan uit dat deze tabel een primary key heeft die id heet. Heeft de primary key een andere naam, dan kun je dat aangeven in een nieuwe protected property $_primary. Is deze primary key een samengestelde sleutel, die uit meerdere kolommen bestaat, dan mag $_primary ook een array zijn.

Zou de tabel users nu al gevuld zijn, dan zou je met deze simpele twee regels code al de hele tabel kunnen leeghalen:

[codesyntax lang="php" title="Voorbeeldcode"]

<?php
$tblUsers = new Model_DbTable_Users();
$users = $tblUsers->fetchAll();

[/codesyntax]

Deze methode levert een Zend_Db_Table_Rowset op, die je feitelijk als een array kunt behandelen; elke record van de rowset is een associatieve array van de kolomnamen van de tabel met de respectievelijke waarden.

Meestal is het handiger om de rowset geen array van arrays te laten zijn, maar een array van echte objecten, die je zelf kunt defini�ren. Dat moeten we handmatig doen; dat lukt nog niet in Zend_Tool. We voeren daarvoor een nieuwe protected property in onze Model_DbTable_Users in. Deze property heet $_rowClass, en krijgt de waarde Model_DbRow_User. Gegeven onze naamgevingsconventie, impliceert deze naam dat we een nieuwe folder moeten maken in onze map application/models. De nieuwe folder heet DbRow; zo is meteen duidelijk dat DbTable echte tabel-objecten bevat, en DbRow echte rij-objecten. Binnen DbRow maken we een nieuw bestand aan, User.php, dat de definitie van een klasse Model_Db_Row_User bevat. Deze klasse moet een extensie vormen van Zend_Db_Table_Row_Abstract. De klasse kan voor dit moment verder leeg blijven.

De aanroep van de methode fetchAll() op een instantie van Model_DbTable_Users zou nu een array bevatten van Model_DbRow_User objecten. Dat is handig, want in de klassedefinitie zou je allerlei methoden kunnen onderbrengen die betrekking hebben op een User-object; de controle op de geldigheid van wachtwoorden, emailadressen, bijvoorbeeld; maar ook een standaardactie op het moment dat een nieuwe user wordt aangemaakt.

We zullen onze theorie even uittesten: maak een paar nieuwe records aan in de users tabel met willekeurige gegevens. Plaats vervolgens deze code in de methode indexAction in application/controllers/IndexController.php:

[codesyntax lang="php" title="indexAction in application/controllers/IndexController.php"]


<?php
$tblUsers = new Model_DbTable_Users();
$this->view->users = $tblUsers->fetchAll();

[/codesyntax]

In de eerste regel wordt een nieuwe instantie aangemaakt van onze Users klasse; zoals al eerder is uitgelegd, is deze klasse een extensie van Zend_Db_Table_Abstract, wat betekent dat hierin methoden voorkomen die betrekking hebben op de klasse als geheel. Een van die methoden is fetchAll, hier aangeroepen zonder argumenten; deze methode haalt meer dan een record op. Zoals we hem hier toepassen, wordt het resultaat in een nieuwe property van ons view object gestopt; deze property noemen we users.

Open nu je viewscript (application/views/scripts/index/index.phtml) en voeg deze code toe aan je al bestaande code:

[codesyntax lang="php" title="application/views/scripts/index/index.phtml"]

<?php
foreach ($this->users as $user) {
Zend_Debug::dump($user);
}
?>

[/codesyntax]

Een simpele foreach-loop haalt uit de view-property users ($this->users) elke afzonderlijke $user op; de methode Zend_Debug levert ons een goed leesbare dump op van de variabele die als argument wordt meegegeven, in dit geval $user. Als het goed is moet je nu een output als deze te zien krijgen in je browser (je hebt natuurlijk andere gegevens ingevoerd):

Zoals je ziet is elke $user een object van de klasse Model_DbRow_User, en zijn de properties van deze klasse bereikbaar met de $user->property notatie.

Tot besluit

OK, we weten nu hoe we contact maken met de database, en welke simpele methode we kunnen aanroepen om gegevens uit de tabel te halen. Natuurlijk is dat maar het tipje van de ijsberg; Zend_Db_Table_Abstract en Zend_Db_Table_Row_Abstract hebben veel meer methoden om gegevens uit te lezen, te manipuleren en anderszins te behandelen.

In het volgende hoofdstuk gaan we de gebruiker toestaan om zijn gegevens te wijzigen. We maken kennis met Zend_Form om formulieren te bouwen, en input van gebruikers te filteren op ongewenste content en te valideren alvorens wijzigingen in de database aan te brengen.