Creating our Wallpaper Entity

In this video we are going to start working with the database. We will be using Doctrine to interact with MySQL, though you are free to switch out MySQL for Postgres or SQLite or any of the other supported database platforms.

One thing to note is that if not using MySQL, you may not be able to complete the migrations portion of this tutorial.

I'm going to assume you have at least a basic understanding of working with Doctrine at this point. If you do not, please consider taking this course - and in particular, this video - before proceeding.

To give ourselves an opportunity to learn a few extra things along the way, we are going to build a Symfony console command to help setup / populate our Wallpaper entity.

Now, the truth is, in the real world you would more likely wish to use Doctrine Fixtures for this. Other methods exist to achieve this same task, so just be aware that this is a solved problem and you need not reinvent the wheel here. Though we are doing so purely to explore console commands a little further.

To be completely clear, Fixtures are essentially a set of commands that help create and save a known set of data into your database. This might be some users with specific names and emails, or in our case, Wallpapers with expected file names and dimensions. This is helpful for both testing purposes, as well as giving us a "known good" starting point.

Our console command will allow us to play with the progress bar helper, and the table output - both of which are really smart in my opinion. We're going to use a glob to dynamically determine all the existing image files in our web/images directory, and then use the file name to help create an entity for each.

We could start with just a single entity - our Wallpaper entity. However, we have already been using a second concept - that of a Category - in our array-based implementation. Therefore we will also create a Category entity, and cover how we might relate the two entities together.

At this point our console command will become less useful. All we can determine - without a bit of file name convention - is the name to give to the file. We can't (easily) determine the correct category. Rather than hack around this issue, we will instead switch out to use Doctrine Fixtures. This is more work, but gives much finer control over the process.

Before we can start creating our entities we must configure the database connection parameters. We do this inside parameters.yml:

# /app/config/parameters.yml

parameters:
    database_host: 192.168.0.21
    database_port: null
    database_name: wallpaper_site
    database_user: dbuser
    database_password: dbpassword

    # other params here

Be sure to enter the appropriate entries for your database.

Once these are in place, we can get Doctrine to create our new database for us if it does not already exist:

php bin/console doctrine:database:create
Created database `wallpaper_site` for connection named default

This has had the added side benefit of ensuring our provided parameters are correct - otherwise we wouldn't have been able to create the new database :)

At this point we are good to go.

I'm going to use the provided entity generator command to not only speed up the creation of the two entity classes, but also get a free, pre-configured entity Repository class thrown in:

php bin/console doctrine:generate:entity

  Welcome to the Doctrine2 entity generator

This command helps you generate Doctrine2 entities.

First, you need to give the entity name you want to generate.
You must use the shortcut notation like AcmeBlogBundle:Post.

The Entity shortcut name: AppBundle:Wallpaper

Determine the format to use for the mapping information.

Configuration format (yml, xml, php, or annotation) [annotation]:

Instead of starting with a blank entity, you can add some fields now.
Note that the primary key will be added automatically (named id).

Available types: array, simple_array, json_array, object,
boolean, integer, smallint, bigint, string, text, datetime, datetimetz,
date, time, decimal, float, binary, blob, guid.

New field name (press <return> to stop adding fields): filename
Field type [string]:
Field length [255]:
Is nullable [false]:
Unique [false]:

New field name (press <return> to stop adding fields): slug
Field type [string]:
Field length [255]:
Is nullable [false]:
Unique [false]: true

New field name (press <return> to stop adding fields): width
Field type [string]: integer
Is nullable [false]:
Unique [false]:

New field name (press <return> to stop adding fields): height
Field type [string]: integer
Is nullable [false]:
Unique [false]:

New field name (press <return> to stop adding fields):

  Entity generation

  created ./src/AppBundle/Entity/
  created ./src/AppBundle/Entity/Wallpaper.php
> Generating entity class src/AppBundle/Entity/Wallpaper.php: OK!
> Generating repository class src/AppBundle/Repository/WallpaperRepository.php: OK!

  Everything is OK! Now get to work :).

Quick Tip: If you make a mistake then ctrl+C will cancel the process and you can start again. However, you will lose all your input up until this point.

The output on your screen will look nicer than this.

By using the 'shortcut name' of AppBundle:Wallpaper our entity will get put into the /src/AppBundle/Entity/ directory.

We don't need to tell the generator command to generate an id field. This we will get for free. The generator generated id property will be an auto incrementing integer. If you wish to use something different (a UUID or similar) then you would need to tweak this. That's out of the scope of this video but here's an example all the same:

    /**
     * @ORM\Column(type="guid")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="UUID")
     *
     * @var string
     */
    protected $id;

I've added in a bunch of properties that are best guesses at this stage:

• filename
• slug
• width
• height

You are free to add in others.

This approach will serve as a starting point, but we will definitely need to tweak this as we go.

The outcome of this command is that we have two new files in our project - the entity class itself, and a repository class into which we can create and store custom queries.

<?php

// /src/AppBundle/Entity/Wallpaper.php

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * Wallpaper
 *
 * @ORM\Table(name="wallpaper")
 * @ORM\Entity(repositoryClass="AppBundle\Repository\WallpaperRepository")
 */
class Wallpaper
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="filename", type="string", length=255)
     */
    private $filename;

    /**
     * @var string
     *
     * @ORM\Column(name="slug", type="string", length=255, unique=true)
     */
    private $slug;

    /**
     * @var int
     *
     * @ORM\Column(name="width", type="integer")
     */
    private $width;

    /**
     * @var int
     *
     * @ORM\Column(name="height", type="integer")
     */
    private $height;

    /**
     * Get id
     *
     * @return int
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set filename
     *
     * @param string $filename
     *
     * @return Wallpaper
     */
    public function setFilename($filename)
    {
        $this->filename = $filename;

        return $this;
    }

    /**
     * Get filename
     *
     * @return string
     */
    public function getFilename()
    {
        return $this->filename;
    }

    /**
     * Set slug
     *
     * @param string $slug
     *
     * @return Wallpaper
     */
    public function setSlug($slug)
    {
        $this->slug = $slug;

        return $this;
    }

    /**
     * Get slug
     *
     * @return string
     */
    public function getSlug()
    {
        return $this->slug;
    }

    /**
     * Set width
     *
     * @param integer $width
     *
     * @return Wallpaper
     */
    public function setWidth($width)
    {
        $this->width = $width;

        return $this;
    }

    /**
     * Get width
     *
     * @return int
     */
    public function getWidth()
    {
        return $this->width;
    }

    /**
     * Set height
     *
     * @param integer $height
     *
     * @return Wallpaper
     */
    public function setHeight($height)
    {
        $this->height = $height;

        return $this;
    }

    /**
     * Get height
     *
     * @return integer
     */
    public function getHeight()
    {
        return $this->height;
    }
}

And the repository class:

<?php

// /src/AppBundle/Repository/WallpaperRepository.php

namespace AppBundle\Repository;

/**
 * WallpaperRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class WallpaperRepository extends \Doctrine\ORM\EntityRepository
{
}

Notice on the Wallpaper class we have an Entity annotation that says our repositoryClass is the WallpaperRepository:

/**
 * Wallpaper
 *
 * @ORM\Table(name="wallpaper")
 * @ORM\Entity(repositoryClass="AppBundle\Repository\WallpaperRepository")
 */
class Wallpaper

All good stuff, and nothing that we have had to do ourselves which is always nice.

One thing that may be a little unintuitive is that our database does not yet reflect the creation of this entity. In other words, if you were to look inside your database at this point, you would have an empty database - not a database containing a single table - the wallpaper table as you might expect.

As is so often the case in software development, there are (at least) two ways of doing this. We can do it the easy way, or the more involved but better way.

It's very tempting to whack in the doctrine:schema:update --force command at this point and have our database table created for us. It's worth looking at this command anyway, in order to understand what it might do for us:

php bin/console doctrine:schema:update

ATTENTION: This operation should not be executed in a production environment.
           Use the incremental update to detect changes during development and use
           the SQL DDL provided to manually update your database in production.

The Schema-Tool would execute "1" queries to update the database.

Please run the operation by passing one - or both - of the following options:
    doctrine:schema:update --force to execute the command
    doctrine:schema:update --dump-sql to dump the SQL statements to the screen

We have one new entity. It has no relations at this point. Doctrine is telling us that to create the underlying SQL representation of this entity would take 1 query.

We can run the command again with --dump-sql to see exactly what that query would be:

php bin/console doctrine:schema:update --dump-sql

CREATE TABLE wallpaper (
  id INT AUTO_INCREMENT NOT NULL,
  filename VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL,
  width INT NOT NULL,
  height INT NOT NULL,
  UNIQUE INDEX UNIQ_D592642C989D9B62 (slug),
  PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8
  COLLATE utf8_unicode_ci
  ENGINE = InnoDB;

The formatting above is my own - when you run this command it will output on one line.

This contains all the necessary info to make a MySQL version of our entity. Notice things like how our slug was set to unique in our entity, and as such will have a UNIQUE INDEX applied. Likewise, a PRIMARY KEY will be created for the id field.

Now, if we forced this through - as is very tempting to do - we would run this SQL statement against our database and everything would very likely be good. We're creating a new table into a fresh database. There's very unlikely unexpected any side effects, and if there are, we could drop the database and start again.

This becomes a little less optimal as our site grows. Without hundreds, thousands, or millions of rows in our database tables, I for one want to be 100% crystal clear over what I'm doing - or about to do - to my database.

Migrations give me that clarity. I think of migrations as a form of version control for my database. I would never code today without some form of version control (git btw if unsure), and given that a form of version control exists for my database it would be silly not to use it.

One thing to point out however is that Migrations are not a perfect solution. They do add complexity, are an extra dependency to your project, and are yet another thing to learn (though honestly, not that big). As a piece of useless trivia, Migrations were the very first tutorial series I ever did for CodeReviewVideos :)

We're going to finish up here by adding in Doctrine Migrations Bundle, generating our first migration, and finally we will apply it.

I'm going to quickly recap the instructions from the official docs:

composer require doctrine/doctrine-migrations-bundle "^1.0"

Make sure you add the entry to AppKernel.php:

<?php

// /app/AppKernel.php

use Symfony\Component\HttpKernel\Kernel;
use Symfony\Component\Config\Loader\LoaderInterface;

class AppKernel extends Kernel
{
    public function registerBundles()
    {
        $bundles = [
            new Symfony\Bundle\FrameworkBundle\FrameworkBundle(),
            new Symfony\Bundle\SecurityBundle\SecurityBundle(),
            new Symfony\Bundle\TwigBundle\TwigBundle(),
            new Symfony\Bundle\MonologBundle\MonologBundle(),
            new Symfony\Bundle\SwiftmailerBundle\SwiftmailerBundle(),
            new Doctrine\Bundle\DoctrineBundle\DoctrineBundle(),
            new Sensio\Bundle\FrameworkExtraBundle\SensioFrameworkExtraBundle(),

            # new entry here
            new Doctrine\Bundle\MigrationsBundle\DoctrineMigrationsBundle(),
            new Knp\Bundle\PaginatorBundle\KnpPaginatorBundle(),

            new AppBundle\AppBundle(),
        ];

        if (in_array($this->getEnvironment(), ['dev', 'test'], true)) {
            $bundles[] = new Symfony\Bundle\DebugBundle\DebugBundle();
            $bundles[] = new Symfony\Bundle\WebProfilerBundle\WebProfilerBundle();
            $bundles[] = new Sensio\Bundle\DistributionBundle\SensioDistributionBundle();
            $bundles[] = new Sensio\Bundle\GeneratorBundle\SensioGeneratorBundle();
        }

        return $bundles;
    }

    // * snip*

And the appropriate configuration added to config.yml:

# /app/config/config.yml

doctrine_migrations:
    dir_name: "%kernel.root_dir%/DoctrineMigrations"
    namespace: Application\Migrations
    table_name: migration_versions
    name: Application Migrations

At this point we can generate our first migration.

We're going to use the diff command to do the hard work for us:

php bin/console doctrine:migrations:diff

Generated new migration class to "/path/to/your/project/wallpaper/app/DoctrineMigrations/Version20170527101133.php" from schema differences.

<?php

namespace Application\Migrations;

use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;

/**
 * Auto-generated Migration: Please modify to your needs!
 */
class Version20170527101133 extends AbstractMigration
{
    /**
     * @param Schema $schema
     */
    public function up(Schema $schema)
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');

        $this->addSql('CREATE TABLE wallpaper (id INT AUTO_INCREMENT NOT NULL, filename VARCHAR(255) NOT NULL, slug VARCHAR(255) NOT NULL, width INT NOT NULL, height INT NOT NULL, UNIQUE INDEX UNIQ_D592642C989D9B62 (slug), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
    }

    /**
     * @param Schema $schema
     */
    public function down(Schema $schema)
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');

        $this->addSql('DROP TABLE wallpaper');
    }
}

Note here that the SQL statement added inside $this->addSql for the up method is identical to that we saw when using doctrine:schema:update --dump-sql.

The nice thing is if this goes wrong, we can roll back in a much more orderly fashion. However, rollbacks are never quite as straightforward as this so I don't want to mislead you.

Applying this migration is super easy:

php bin/console doctrine:migrations:migrate

                    Application Migrations

WARNING! You are about to execute a database migration that could result in schema changes and data lost. Are you sure you wish to continue? (y/n)y
Migrating up to 20170527101133 from 0

  ++ migrating 20170527101133

     -> CREATE TABLE wallpaper (id INT AUTO_INCREMENT NOT NULL, filename VARCHAR(255) NOT NULL, slug VARCHAR(255) NOT NULL, width INT NOT NULL, height INT NOT NULL, UNIQUE INDEX UNIQ_D592642C989D9B62 (slug), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB

  ++ migrated (0.09s)

  ------------------------

  ++ finished in 0.09s
  ++ 1 migrations executed
  ++ 1 sql queries

Now if we check our database, lo-and-behold we have a wallpaper table that matches the properties specified on our Wallpaper entity.

As mentioned, a video tutorial for Doctrine Migrations Bundle is already available on this site.

That's the setup done. In the next video we are going to start work on our console command, and see how far we can push our luck :)