How to add data migration support to Silex

Add Doctrine data migrations to Silex application

There are quite a few blog articles about using doctrine migrations in different project types and this article was closest to what we need in Silex project.

Install doctrine migration module

We add a few required components to composer.json and execute update command.

     "doctrine/dbal": "~2.2",
     "symfony/console": "^2.7",
     "doctrine/migrations": "^1.1"

We need to have symfony/console module already installed and prepared for extending with new commands, as I wrote in this article.

Add migration commands to console.php program.  You can find them in vendor folder under:  “/lib/Doctrine/DBAL/Migrations/Tools/Console/Command” path.

<?php
// application.php

require __DIR__.'/vendor/autoload.php';

use Symfony\Component\Console\Application;

$application = new Application();

//Migrations commands 
$application->add(new \Doctrine\DBAL\Migrations\Tools\Console\Command\DiffCommand());
$application->add(new \Doctrine\DBAL\Migrations\Tools\Console\Command\ExecuteCommand());
$application->add(new \Doctrine\DBAL\Migrations\Tools\Console\Command\GenerateCommand());
$application->add(new \Doctrine\DBAL\Migrations\Tools\Console\Command\MigrateCommand());
$application->add(new \Doctrine\DBAL\Migrations\Tools\Console\Command\StatusCommand());
$application->add(new \Doctrine\DBAL\Migrations\Tools\Console\Command\VersionCommand());

$application->run();

Add migrations configuration files:

Migrations configuration define base settings for migration generation and execution environment.

name: Doctrine Migrations
migrations_namespace: Bisaga\Migrations
table_name: doctrine_migration_versions
migrations_directory: ./app/Migrations

Database configuration is needed that migrations can really do the job.

<?php
return array(
        'host'      => 'localhost',
        'port'      => '3306',
        'driver'    => 'pdo_mysql',
        'charset'   => 'utf8mb4',
        'dbname'    => 'bisagasamples',
        'user'      => 'dbuser',
        'password'  => 'dbpassword',
        'defaultTableOptions' => [ 'charset'=> 'utf8mb4', 'collate' => 'utf8mb4_slovenian_ci'],
);

Default table options

How to set default create options for tables and columns such as default collation sequence and character set ?

‘defaultTableOptions’ => [
‘charset’=> ‘utf8mb4’,
‘collate’ => ‘utf8mb4_slovenian_ci’
],

To set custom character set and collation just add “defaultTableOptions” array with your specific  default settings in connection configuration array (file: migrations-db.php).

Console command interface

You can test it immediately with console command:

>php console.php list migration

2015-11-18 21_47_07-Command Prompt

With data migrations you can easily update your project database with a programmatic interface with version support.  Your database schema can grow without risk of loosing control over it.

Sample script

Empty class is generated with “migrations:generate” command. After initial generation you code migration changes manually.

<?php

namespace Bisaga\Migrations;

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

/**
 * Auto-generated Migration: Please modify to your needs!
 */
class Version20151117184031 extends AbstractMigration
{
    /**
     * @param Schema $schema
     */
    public function up(Schema $schema)
    {
        
        $worklogtable = $schema->createTable('worklogtable');
        
        $worklogtable->addColumn('id', 'integer', ['unsigned' => true, 'autoincrement'=>true]);
        $worklogtable->addColumn('workdate', 'date', ['notnull'=>true]);
        $worklogtable->addColumn('location', 'string', ['length' => 60]);
        $worklogtable->addColumn('milage', 'decimal', ['precision' => 8, 'scale'=>2]);
        $worklogtable->addColumn('starttime', 'time');
        $worklogtable->addColumn('endtime','time');
        $worklogtable->addColumn('totaltime','time');
        $worklogtable->addColumn('status', 'string', ['length' => 30]);
        $worklogtable->setPrimaryKey(['id']);        
        
        
        // 
        $worklogline = $schema->createTable('worklogline');
        
        $worklogline->addColumn('id', 'integer', ['unsigned' => true, 'autoincrement'=>true]);
        $worklogline->addColumn('fromtime', 'time');
        $worklogline->addColumn('totime', 'time');
        $worklogline->addColumn('logtime', 'time');
        $worklogline->addColumn('description', 'string', ['length' => 250]);
        $worklogline->addColumn('taskcode', 'string', ['length' => 150]);
        $worklogline->addColumn('worklogid', 'integer', ['unsigned'=>true]);
        $worklogline->setPrimaryKey(['id']);                
        $worklogline->addForeignKeyConstraint($worklogtable, array('worklogid'), 
                        array('id'), array('onUpdate'=>'CASCADE', 'onDelete'=>'CASCADE'));
        
    }

    /**
     * @param Schema $schema
     */
    public function down(Schema $schema)
    {
        // this down() migration is auto-generated, please modify it to your needs
        $schema->dropTable('worklogline');
        $schema->dropTable('worklogtable');
    }
}

After generating first migration file the file is saved in configured migrations folder (./app/Migrations/).

2015-11-19 00_53_25-silex03 - NetBeans IDE 8.0.2

Run migrations

Your migration scripts will not running automatically, you need to do it by yourself.  To execute open migrations you need to run command:  “migrations:migrate” .

>console migrations:migrate

 

Adding symfony console support to silex application

Adding console support to Silex application

Silex is a micro-framework and as such doesn’t force you to use so many components included in full stack symfony framework. But, if we want to use some of them we need to do some extra work.

Install console component

Installing with composer is simple, just add additional requirements for “symfony/console”  to composer.json file. The full content of my composer.json file as currently is :

{
    "name": "igor.babic/bisaga",
    "description": "Sample silex application",
    "license": "MIT",
    "type": "project",
    "require": {
        "php": ">=5.5.9",
        "silex/silex": "~1.3",
        "doctrine/dbal": "~2.2",
        "twig/twig": "^1.23",
        "symfony/twig-bridge": "^2.7",
        "symfony/console": "^2.7",
        "doctrine/migrations": "^1.1"     
    },
    "autoload": {
        "psr-4" : {
            "Bisaga\\" : "src/"
        }
    }
}

After changing composer.json file just don’t forget to execute update command.

H:\Ampps\www\silex03>composer update

This way all required components for console support are installed. We can always check “vendor” folder after update, there will be everything required by composer definitions.

Creating main console program

To be able to use “console” command, we need console.php program first. We have very easy job here, because everything is already written, we just need to get it from symfony site .

We create console.php file with following content:

<?php
require __DIR__.'/vendor/autoload.php';

use Bisaga\Infrastructure\Console\GreetCommand;
use Symfony\Component\Console\Application;

$application = new Application();
$application->add(new GreetCommand());
$application->run();

As you can see, we already initialized one sample command here, “GreetCommand”, we need to create the command too.

Creating sample command in php

My silex application is as simple as it can be. The best in using micro-framework is that there is no forced structure from framework. We can create our own folder structure, so my is something like that:

2015-11-15 20_22_50-Programmer's Notepad - [console]

My  folder structure is not yet complete, it will evolve as application grows.

We place GreetCommand.php file from symfony web guide to our Infrastructure \ Console folder and change namespace to reflect our folder structure.

<?php

namespace Bisaga\Infrastructure\Console;

use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;

class GreetCommand extends Command
{
    protected function configure()
    {
        $this
            ->setName('demo:greet')
            ->setDescription('Greet someone')
            ->addArgument(
                'name',
                InputArgument::OPTIONAL,
                'Who do you want to greet?'
            )
            ->addOption(
               'yell',
               null,
               InputOption::VALUE_NONE,
               'If set, the task will yell in uppercase letters'
            )
        ;
    }

    protected function execute(InputInterface $input, OutputInterface $output)
    {
        $name = $input->getArgument('name');
        if ($name) {
            $text = 'Hello '.$name;
        } else {
            $text = 'Hello';
        }

        if ($input->getOption('yell')) {
            $text = strtoupper($text);
        }

        $output->writeln($text);
    }
}

Now we can test new command line program simply by calling console.php program with demo:greet parameter:

H:\Ampps\www\silex03>php console.php demo:greet

We put console.php file directly to the root folder of our application, so we call console command from that folder.

If you wish to simplify call to the console.php program, you can write simple  batch program and put it somewhere in the system path:

@ECHO OFF
ECHO Command: php console.php %*
call php %cd\%console.php %*

Now, you can call any console command as :

H:\Ampps\www\silex03>console demo:greet
Command: php console.php demo:greet
Hello

For checking which commands are already initialized in the application, we execute “list” command:

H:\Ampps\www\silex03>console list

2015-11-15 21_00_19-Command Prompt

That’s it !

We are ready to add any commands to our silex application !

 

 

 

Include Bootstrap in Symfony 2 application

Bootstrap

The best tool to design nice, responsive, mobile first web applications is bootstrap framework. It is  also integrated as form design theme in symfony.

Before you can use it in your symfony application, you will need to install it.

The easiest way is with a web front-end package manager – bower. You should install node first and then install bower with npm package manager.  Then including resources in your twig templates is as easy as use asset component.

Isn’t this little to much to start with ?

Shortcut

If you don’t know what node/npm/bower is or if you don’t want to know (for now) 🙂 , you can just use bootstrap directly from publicly accessible  CDN links. Do not forget to include jquery library first, it is required by bootstrap.

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1">        
        <title>{% block title %}Bisaga application{% endblock %}</title>
        {% block stylesheets %}{% endblock %}
        <link rel="icon" type="image/x-icon" href="{{ asset('favicon.ico') }}" />
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap-theme.min.css">
        <!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
        <!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
        <!--[if lt IE 9]>
          <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
          <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
        <![endif]-->        
    </head>
    <body>
        {% block body %}{% endblock %}
        {% block javascripts %}{% endblock %}
        <!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
        <script src="https://code.jquery.com/jquery-2.1.4.min.js"></script>
        <!-- Include all compiled plugins (below), or include individual files as needed -->
        <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
    </body>
</html>

Just don’t forget, if you take shortcuts, you get cut short.

Using bower

Why we needs front-end package management ?  With front-end package manager we simplify installing and updating project dependencies for client side libraries. Bower also check inter-dependencies between those libraries and won’t install the package incompatible with one that’s already installed.

If you use bower for front-end package management  and don’t want to use default download directory (“bower_components”), just create local config file “.bowerrc”:

{
  "directory": "libs"
}

Then you can install bootstrap with a command :

H:\Ampps\www\webapp05\www\> bower install --save bootstrap

You will get two sub folders, “bootstrap” and “jquery”, inside “libs” folder.

Asset component

With asset component we generate URL addresses of web assets such as CSS, stylesheets, graphics and Javascript files. This means that you can change location of assets at one place  in configuration file.

We create new  “assets” section in “framework”  section of config.yml file and add three “packages” inside :

framework:
    assets:
        packages:
            jquery:
                base_path: /libs/jquery/dist/
            bootstrapjs:
                base_path: /libs/bootstrap/dist/js/
            bootstrapcss:
                base_path: /libs/bootstrap/dist/css/

With that in place we can define URL location in our template file with asset function, for example:

 <link rel="stylesheet" href="{{ asset('bootstrap.min.css', 'bootstrapcss') }}">

Second parameter in asset function is package location from config.yml file.

And the final twig file with all required includes for bootstrap is :

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1">        
        <title>{% block title %}Bisaga application{% endblock %}</title>
        {% block stylesheets %}{% endblock %}
        <link rel="icon" type="image/x-icon" href="{{ asset('favicon.ico') }}" />
        <link rel="stylesheet" href="{{ asset('bootstrap.min.css', 'bootstrapcss') }}">
        <!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
        <!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
        <!--[if lt IE 9]>
          <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
          <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
        <![endif]-->        
    </head>
    <body>
        {% block body %}{% endblock %}
        {% block javascripts %}{% endblock %}
        <!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
        <script src="{{ asset('jquery.min.js', 'jquery') }}"></script>
        <!-- Include all compiled plugins (below), or include individual files as needed -->
        <script src="{{ asset('bootstrap.min.js', 'bootstrapjs')}}"></script>
    </body>
</html>

 

 

Doctrine DBAL – working with database

In this article I am going to cover using MySql database in Symfony 2 application.

I will use the simplest possible database access technology PHP offers – PDO,  but in slightly extended form of Doctrine DBAL. So if you want to use Doctrine ORM , this article is not right place to start.

Before we begin

We need some sample data in the database,  so we create a database “dbsamples” with “country” table and import data from this csv file. Because I will not use full Doctrine ORM ,  generating db schema is not an option, I will create database manually.

create table country (
    id int not null auto_increment,
    name char varying(250) not null,
    formal_name	char varying(250),
    legal_type char varying(100),
    legal_sub_type char varying(100),
    sovereignty char varying(100),
    capitol_city char varying(100),
    currency_code char varying(30), 
    currency_name char varying(100),
    telephone_code char varying(100),
    iso2code char varying(2),
    iso3code char varying(3),
    iso3num char varying(3),
    domain_code char varying(20),
    primary key (id),
    key ix_name (name),
    key ix_formal_name (formal_name)
)

You can use phpMyAdmin for that,  it is usually installed by default. If you need true utf8 database, you need to set character set to utf8mb4.

Connecting to the database

Because we use Symfony standard framework, we already have  connection parameters in a configuration file (“parameters.yml”).

# This file is auto-generated during the composer install
parameters:
    database_driver: pdo_mysql
    database_host: 127.0.0.1
    database_port: 3306
    database_name: dbsamples
    database_user: dbuser
    database_password: mypassword
    # [20] PDO::ATTR_EMULATE_PREPARES = false, 
    # [17] PDO::ATTR_STRINGIFY_FETCHES = false
    database_options: {20: 0, 17: 0}

I added one new parameter here, an array of custom driver options (“database_options”). By default all received data to php are stringified, it means you get all data in strings. To receive native data types from the database (int as integer for example), you need to set those options and use prepared statements. But this options will only work if you have luck and your hosting installed and enabled  “mysqlnd” native driver, check your hosting for the driver type.

In the config.yml file, this parameters are mapped to dbal connection  parameters:

# Doctrine Configuration
doctrine:
    dbal:
        driver:   "%database_driver%"
        host:     "%database_host%"
        port:     "%database_port%"
        dbname:   "%database_name%"
        user:     "%database_user%"
        password: "%database_password%"
        charset:  UTF8
        options: "%database_options%"

When we are in the symfony controller file (as in \AppBundle\Controller\DefaultController.php for example), we get connection directly from the container:

$cn = $this->getDoctrine()->getConnection();

or more specific for DBAL connection :

$cn = $this->container->get('doctrine.dbal.default_connection');

but because aquired connections are not pooled (php connections do not support connection pool yet), is actually the same if you create new connection by yourself :

$config = new \Doctrine\DBAL\Configuration();
$connectionParams = array('url'=>'mysql://dbuser:mypassword@localhost/dbsamples' );
$cn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams, $config);

Selecting data

Selecting data in DBAL is easy and intuitive, just use connections executeQuery method. If you use parameters in SQL statement, this will create prepared statement for you and bind parameters automatically. You should always use prepared statements because they are more secure and more efficient as plain text statements.

//create prepared statement and execute it 
$stmt = $cn->executeQuery("SELECT * FROM country WHERE id = :id", array("id"=>157));

But there is even better way, using QueryBuilder object and assembling SQL statement with PHP code.

$stmt = $cn->createQueryBuilder()
        ->select("*")
        ->from("country")
        ->where("id = :id")
        ->setParameters(array("id"=>157))
        ->execute();

Fetch data to types

To get result from a statement, we need to execute one of the “fetch” methods.

$country = $stmt->fetch();

By default we receive each result row as an array, or as array of arrays for multiple records. We can even fetch directly to an instance of an class:

//fetch result to class instance 
$stmt->setFetchMode(\PDO::FETCH_CLASS | \PDO::FETCH_PROPS_LATE, "\Bisaga\Database\Model\Country");
        
/*@var $country \Bisaga\Database\Model\Country  */
$country = $stmt->fetch();

Don’t forget we have all the power of PDO library at our disposal.

With little help from variable type hinting , we get full code completion on received object instance in the IDE:

2015-09-27 11_05_12-Untitled - paint.net 4.0.6Well, that’s of course possible only in case we use same exact names in database and in php class :

<?php

namespace Bisaga\Database\Model;

/**
 * Country table 
 */
class Country {
    /**
     * Row identifier 
     * @var integer auto numbered unique identifier
     */
    public $id;

    /**
     * Country name 
     * @var string country name  
     */
    public $name;
    
    /**
     * Formal country name 
     * @var string 
     */
    public $formal_name;

    /**
     * Country legal type 
     * @var string  
     */
    public $legal_type;
    
    /**
     * Country legal subtype 
     * @var string 
     */
    public $legal_sub_type;

    /**
     * Country sovereignty 
     * @var string  
     */
    public $sovereignty;

    /**
     * Country capitol 
     * @var string 
     */
    public $capitol_city;
    
    /**
     * Currency code 
     * @var string  
     */
    public $currency_code;
    
    /**
     * Country currency name  
     * @var string 
     */
    public $currency_name;

    /**
     * Country telephone code 
     * @var string
     */
    public $telephone_code;
    
    /**
     * Country 2 characters iso code 
     * @var string 
     */
    public $iso2code;

    /**
     * Country 3 characters iso code 
     * @var string 
     */
    public $iso3code;

    /**
     * Country 3 characters numeric iso code 
     * @var string 
     */
    public $iso3num;
    
    /**
     * Country domain name suffix
     * @var string 
     */
    public $domain_code;
    
}

Custom columns mapping

Well, you really need to have a very good reason to select different names for columns in database, but sometimes you are not original owner of database. In that case you could introduce mapping function like this:

 /**
 * Function create instance of target type and fill target properties 
 * 
 * @param object $sourceObject Object instance or array as source 
 * @param string $targetClass Target class name fith full namespace 
 * @param array $mapping  array with mapping names, 
 *                        key represent name of the property in source object, 
 *                        value represent column name in target object 
 * @return any function return object instance of target type 
 */
private function castToClass($sourceObject, $targetClass, $mapping = null)
{
	$target = new $targetClass();
	if (is_array($sourceObject) || is_object($sourceObject)) {
		foreach ($sourceObject as $key => $value) {
			
			if (null === $mapping ) {
				$target->$key = $value;
			}
			elseif ( array_key_exists($key, $mapping) )
			{
				$mapped_name = $mapping[$key];
				$target->$mapped_name = $value; 
			}
		}
	}
	return $target;
}

Prepare sample for mapping

We create new table “currency” in the database  :

create table currency (
    numeric_code int not null,
    currency_code char varying(20) not null,
    primary key (numeric_code),
    key ix_code (currency_code)
)

and write new class “Currency” in php :

<?php

namespace Bisaga\Database\Model;
/*
 * Currency table 
 */
class Currency {
    /**
     * Currency code (840, 978 etc.)
     * @var integer currency numeric value 
     */
    public $id;
    /**
     * Currency code (USD, EUR etc.)
     * @var string currency code 
     */
    public $code;
}

We do not want push to far in this direction, we could start developing a new ORM or something and that is not our intention. We want to retain full power of SQL language in the application, without need to write to much PHP or SQL code manually.

Usage example:
 $stmt = $cn->createQueryBuilder()
                ->select("numeric_code, currency_code")
                ->from("currency")
                ->where("id = :id")
                ->setParameters(array("id"=>852))
                ->execute();
        
 $object = $stmt->fetch();
 /*@var $currency \Bisaga\Database\Model\Currency  */
 $currency = $this->castToClass($object, 
               "\Bisaga\Database\Model\Currency", 
               array("numeric_code" => "id", 
                     "currency_code"=>"code"));

Mapping array contain database columns first (as keys) and class properties second (as values).

Inserting data

Inserting data is straightforward :

$cn->insert("country", (array)$country);
$country->id = $cn->lastInsertId();

Because insert command expect array as second parameter, we simply convert object instance ($country)  to associative array.  After record is inserted, we inspect last inserted value in auto numbered column and return value to the program.

Update data

Update is very similar to insert, except we need to add third parameter to find existing record :

 $cn->update("country", (array)$country, array("id"=>$country->id));

Delete data

And deleting is event simpler:

$cn->delete("country", array("id"=>$country->id));

I didn’t mentioned that all commands are available as query builder methods.

Working with transactions

There is full transaction support directly in php code. We must explicitly start transaction and commit after last database command. In case of errors we need to call rollback on the used connection.

$cn->beginTransaction();
try {
	$cn->insert("country", (array)$country);
	$country->id = $cn->lastInsertId();
	$cn->commit();
} catch (Exception $exc) {
	$cn->rollBack();
	throw $exc;
}

If you think this is little verbose and want to shorten the code, you actually can, just use closure with “transactional” function :

/**
 * @param \Bisaga\Database\Model\Country $country
 */
public function insertWithClosure(\Bisaga\Model\Country $country)
{            
	// use closure form with beginTransaction/commit/rollBack support 
	$this->getConnection()->transactional(function(\Doctrine\DBAL\Connection $cn) use ($country) {
		/* @var $cn Connection */
		$cn->insert("country", (array)$country);
		$country->id = $cn->lastInsertId();
	});
}

 

Symfony – demo application

Install demo application

Symfony provide demo application as a learning resource.  To install it just enter “demo” as parameter to “symfony” command.

H:\Ampps\www>symfony demo

Start demo application

To start newly installed app, navigate to:

http://localhost/symfony_demo/web/app_dev.php

First page of application :

2015-09-16 21_53_17-Symfony Demo applicationIf demo application doesn’t work as expected and you get error like this :

An exception has been thrown during the rendering of a template (“The Symfony\Component\Intl\DateFormatter\IntlDateFormatter::__construct() method’s argument $locale value NULL behavior is not implemented.  Only the locale “en” is supported. Please install the “intl” extension for full localization capabilities.”) in blog/post_show.html.twig at line 35.

Just repair one line of php code in “IntlDateFormatter.php” file, as described in details in this fix .

File is placed inside demo app source tree , in my case this is:

H:\Ampps\www\symfony_demo\vendor\symfony\symfony\src\Symfony\Component\Intl\DateFormatter\IntlDateFormatter.php

Open file and change first “if” statement in “__constructor” method to reflect second line :

-        if ('en' != $locale) {
+        if ('en' !== $locale && null !== $locale) {

Save file and navigate to application again, this time should work as expected.