Doctrine DBAL and Migrations Example

composer.json

{
    "require": {
        "doctrine/dbal": "2.3.0",
        "doctrine/migrations": "@dev",
        "symfony/console": "2.1.2",
        "symfony/yaml": "2.1.2"
    }
}

console.php

#!/usr/bin/env php
<?php
/**
 * Migrations CLI Application:
 *
 * Usage:
 * $ php console.php migrations:status
 * $ php console.php migrations:migrate
 */
require_once __DIR__ . '/../vendor/autoload.php';

use Doctrine\DBAL\DriverManager;
use Symfony\Component\Console\Application;
use Symfony\Component\Console\Helper\HelperSet;

$db = DriverManager::getConnection(array(
    'dbname' => 'test',
    'user' => 'root',
    'password' => '',
    'host' => 'localhost',
    'driver' => 'pdo_mysql',
    'charset' => 'utf8',
    'driverOptions' => array(
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
    )
));

$helperSet = new HelperSet(array(
    'db' => new \Doctrine\DBAL\Tools\Console\Helper\ConnectionHelper($db),
    'dialog' => new \Symfony\Component\Console\Helper\DialogHelper,
));

$console = new Application;
$console->setHelperSet($helperSet);
$console->addCommands(array(
    new \Doctrine\DBAL\Migrations\Tools\Console\Command\DiffCommand,
    new \Doctrine\DBAL\Migrations\Tools\Console\Command\ExecuteCommand,
    new \Doctrine\DBAL\Migrations\Tools\Console\Command\GenerateCommand,
    new \Doctrine\DBAL\Migrations\Tools\Console\Command\MigrateCommand,
    new \Doctrine\DBAL\Migrations\Tools\Console\Command\StatusCommand,
    new \Doctrine\DBAL\Migrations\Tools\Console\Command\VersionCommand,
));

$console->run();

dbal.php

<?php
/**
 * Doctrine DBAL Examples:
 */
require_once __DIR__ . '/../vendor/autoload.php';

use Doctrine\DBAL\Configuration;
use Doctrine\DBAL\DriverManager;
use Doctrine\DBAL\Schema\Schema;
use Doctrine\DBAL\Platforms\MySqlPlatform;

$connection = DriverManager::getConnection(array(
    'dbname' => 'test',
    'user' => 'root',
    'password' => '',
    'host' => 'localhost',
    'driver' => 'pdo_mysql',
    'charset' => 'utf8',
    'driverOptions' => array(
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
    )
), $config = new Configuration);

/**
 * Objects.
 */
$databasePlatform = $connection->getDatabasePlatform();
$schemaManager = $connection->getSchemaManager();
$queryBuilder = $connection->createQueryBuilder();

/**
 * Table Details
 */
$details = $schemaManager->listTableDetails('test');

/**
 * Insert Record
 */
$results = $connection->insert('test', array(
    'title' => 'Hello, World!', 
    'content' => 'This is some awesome text of awesomeness.'
));

/**
 * Fetch Records
 */
$records = $connection->fetchAll('SELECT * FROM test');
foreach ($records as $record) {
    print_r($record);
}

/**
 * Query Builder
 * See: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Query/QueryBuilder.php
 */
$results = $queryBuilder->select('*')
                         ->from('test', 't')
                         ->orderBy('t.title', 'ASC')
                         ->execute()
                         ->fetchAll();

/**
 * Schema Builder
 */
$schema = new Schema;
$table = $schema->createTable('more_test');
$table->addColumn('id', 'integer', array('unsigned' => true));
$table->addColumn('title', 'string', array('length' => 128));
$table->addColumn('content', 'text');
$table->setPrimaryKey(array('id'));

$queries = $schema->toSql($databasePlatform);
$drops = $schema->toDropSql($databasePlatform);

migrations.yml

name: Example Migrations
migrations_namespace: ExampleMigrations
table_name: migrations
migrations_directory: /path/to/migrations/directory

Version20121011141021.php

<?php
/*
 * /path/to/migrations/directory/Version20121011141021.php
 */
namespace ExampleMigrations;

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

class Version20121011141021 extends AbstractMigration
{
    public function up(Schema $schema)
    {
        $table = $schema->createTable('more_more_test');
        $table->addColumn('id', 'integer', array('unsigned' => true));
        $table->addColumn('title', 'string', array('length' => 128));
        $table->addColumn('content', 'text');
        $table->setPrimaryKey(array('id'));
    }

    public function down(Schema $schema)
    {
        $schema->dropTable('more_more_test');
    }
}