Using CRATE with Symfony2

Posted on: 2015-01-30 | Categories: Databse PHP

Today I want to introduce to you great elastic data store Crate.io that I was working with lately. CRATE is a new way of building scalable applications: “Crate takes care of synchronization, sharding, scaling, and replication even for mammoth data sets.” Also I’m going to show how easily we can integrate it with PHP / Symfony2 framework – if we can call setting new DBAL driver as integration.

Crate

What is Crate.IO?

There is already really interesting overview: What is Crate? on the CRATE website – this is must-read introduction.

For us CRATE has everything we need:

highly-efficient
scalable
SQL support
lots of clients (PHP, Python, JAVA and more)

Lately we were looking for some new solution to store our Call Detail Records which grow very quickly. MySQL database engine was doing quite well so far but with the beginning of the year it was time for change.
Before migration on table with about 30 million records, MySQL quries were executed in about 5 to 15 seconds. Now with CRATE it takes less than 500 milliseconds.

Installation

On my Linux Mint 17 (Cinnamon) I’ve installed CRATE with following steps:

$ sudo apt-get install python-software-properties
$ sudo add-apt-repository ppa:crate/stable
$ sudo apt-get update
$ sudo apt-get install crate

$ sudo apt-get install python-software-properties

$ sudo add-apt-repository ppa:crate/stable

$ sudo apt-get update

$ sudo apt-get install crate

Complete guide how to install CRATE can be found here.

Crate Admin

CRATE comes with a nice web based admin panel where we can for example: check Cluster status, manage tables or run SQL queries. Admin interface can be accessed with any browser by providing the following url in your browser:

http://localhost:4200/admin

1	http://localhost:4200/admin

Notice: Using CRATE Tutorial tab in Admin UI we can import some tweets for testing – that’s pretty useful.

CRASH – CRAte SHell

CRASH is another useful CRATE feature that can be used for testing purpose and not only. Using CRASH we can for example: create new table, insert / update / delete records or run other queries. Please read the documentation for full list of supported commands.

To start CRASH type this in your shell:

$ /usr/share/crate/bin/crash

1	$ /usr/share/crate/bin/crash

and from now you can type SQL commands.

Notice: In my environment I’ve created crash alias to avoid using long command – path can be different in different systems:

$ alias crash="/usr/share/crate/bin/crash"

1	$ alias crash="/usr/share/crate/bin/crash"

The project

CRATE can be used in any PHP project thanks to Crate-PDO driver. In this tutorial we are going to use CRATE with Symfony2 and Doctrine2 ORM, that’s why we need Crate-DBAL which is already using Crate-PDO.

Setup Symfony2 project

Lets create sample project Symfony2 project (we can call it sfCrate):

$ composer create-project symfony/framework-standard-edition sfCrate
$ cd sfCrate

1 2	$ composer create-project symfony/framework-standard-edition sfCrate $ cd sfCrate

More information can be found on Installing and Configuring Symfony2 page.

Notice: ~~For now CRATE DBAL supports only Doctrine2 DBAL in version 2.3.5. This should be fixed be the end of next week but for now the following line is needed in project composer.json.~~ CRATE has now support for the latest Doctrine2 DBAL version.

Then we need to install crate-dbal with command:

$ php composer require crate/crate-dbal:~0.1

1	$ php composer require crate/crate-dbal:~0.1

To complete integration process we need to modify app/config/config.yml file:

# Doctrine Configuration
doctrine:
    dbal:
        driver_class: Crate\DBAL\Driver\PDOCrate\Driver
        host:         localhost
        port:         4200

# Doctrine Configuration

doctrine:

dbal:

driver_class: Crate\DBAL\Driver\PDOCrate\Driver

host: localhost

port: 4200

That’s all. From now we can use CRATE DBAL connection in Symfony2 with Doctrine2.

Sample usage:

<?php 
    
    // ...
    public function indexAction()
    {
        // Crate connection - default connection as we have only one connection set
        $conn = $this->getDoctrine()->getConnection();
        
        $stmt = $conn->query("SELECT * FROM tweets LIMIT 100");

        // or using QueryBuilder
        $qb = $conn->createQueryBuilder();
        
        $stmt = $qb
            ->select('*')
            ->from('tweets')
            // extra conditions
            // ->where( ... )
            ->execute();
        
        // ...   
    }

<?php

// ...

public function indexAction()

{

// Crate connection - default connection as we have only one connection set

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

$stmt = $conn->query("SELECT * FROM tweets LIMIT 100");

// or using QueryBuilder

$qb = $conn->createQueryBuilder();

$stmt = $qb

->select('*')

->from('tweets')

// extra conditions

// ->where( ... )

->execute();

// ...

}

CRATE DBAL has also support for SchemaManager excluding features that are not supported by CRATE like JOINs (under heavy development) or foreign keys.

<?php
    $conn = $this->getDoctrine()->getConnection();
    // get SchemaManager object
    $sm = $conn->getSchemaManager();
        
    $tables = $sm
        ->listTables();
        
    foreach ($tables as $table) {
        // fetch table columns
        $columns = $sm
            ->listTableColumns($table->getName());
    }

<?php

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

// get SchemaManager object

$sm = $conn->getSchemaManager();

$tables = $sm

->listTables();

foreach ($tables as $table) {

// fetch table columns

$columns = $sm

->listTableColumns($table->getName());

}

If we want to use CRATE as extra connection in our project (new or existing), configuration should looks like this:

# Doctrine Configuration
doctrine:
    dbal:
        default_connection: default
        connections:
            # some other connection
            default:
                driver:   "%database_driver%"
                host:     "%database_host%"
                port:     "%database_port%"
                dbname:   "%database_name%"
                user:     "%database_user%"
                password: "%database_password%"
                charset:  UTF8
            crate:
                driver_class: Crate\DBAL\Driver\PDOCrate\Driver
                host:         localhost
                port:         4200

# Doctrine Configuration

doctrine:

dbal:

default_connection: default

connections:

# some other connection

default:

driver: "%database_driver%"

host: "%database_host%"

port: "%database_port%"

dbname: "%database_name%"

user: "%database_user%"

password: "%database_password%"

charset: UTF8

crate:

driver_class: Crate\DBAL\Driver\PDOCrate\Driver

host: localhost

port: 4200

To get CRATE connection we need to change to following line.

<?php
    $conn = $this->getDoctrine()->getConnection('crate');

1 2	<?php $conn = $this->getDoctrine()->getConnection('crate');

I hope this will help you start working with CRATE and Symfony2. I can assure you that it’s worth giving a go.

« Previous post Next post »

Your email address will not be published. Required fields are marked *

No comments

Recent blog posts

VoIPstudio launches in Spain

VoIPstudio brings award-winning features, premium quality voice and intuitive mobile and desktop performance – at affordable prices to Spanish SoHo, SME and enterprise users Palma de Mallorca, Spain, October 1st, 2018 – VoIPstudio is excited to announce the launch of … Continue reading VoIPstudio launches in Spain →

Level 7 Systems launches its new flagship product, GoTrunk.

Expanding the VoIP offer with SIP Trunking Creating a long-term enterprise communication strategy involves a lot of decisions and choices, most of them binary in nature. Should you upgrade to VoIP or stick with your existing analog solution? Purchase new … Continue reading Level 7 Systems launches its new flagship product, GoTrunk. →

VoIPstudio CTI Connector Now Available in the Oracle Cloud Marketplace

Oracle Sales Cloud Customers Can Use VoIPstudio CTI Connector To Improve Efficiencies News Facts VoIPstudio, a specialist in high quality VoIP and Session Initial Protocol (SIP) communications and Gold level member of Oracle PartnerNetwork (OPN), today announced that its CTI … Continue reading VoIPstudio CTI Connector Now Available in the Oracle Cloud Marketplace →