innobitlab/php-fly-rrm

An entity free 'RRM' to extract structured data from relational DBMS

php-fly-rrm

, _(*1)

An entity free "RRM" (Resource Relational Mapping) to extract structured data from relational DBMS, _(*2)

Why a Resource Relational Mapping

Because sometimes you need to extract data from a relational DBMS in a structured way, and maybe you don't want to add an "heavy" ORM with all his dependencies... or you can't beacuse of a legacy context., _(*3)

Give me an example

Ready, set, go!, _(*4)

Suppose we have this type of database:, _(*5)

users, _(*6)

contracts, _(*7)

banks, _(*8)

employees, _(*9)

payments, _(*10)

and you have been asked to export this data in a similar structured json format:, _(*11)

{
   "employees":[
      {
         "name":"Mario",
         "surname":"Rossi",
         "contract":{
            "name":"half-time"
         },
         "creator":{
            "username":"admin"
         },
         "last-edit":null,
         "payments":[
            {
               "value":1200.23,
               "bank":{
                  "name":"super bank"
               }
            },
            {
               "value":1240.23,
               "bank":{
                  "name":"super bank"
               }
            }
         ]
      },
      {
         "name":"Giuseppe",
         "surname":"Verdi",
         "contract":{
            "name":"full-time"
         },
         "creator":{
            "username":"admin"
         },
         "last-edit":{
            "username":"james.white"
         },
         "payments":[
            {
               "value":1540,
               "bank":{
                  "name":"money first bank"
               }
            }
         ]
      }
   ]
}

and you have been asked to make this export configurable via a similar YAML file:, _(*12)

``` yaml resource: alias: 'employees' table: 'employees' primary-key: 'id', <sub>(*13)</sub>

fields:
    -
        name: 'name'
    -
        name: 'surname'

relationships:
    -
        type: 'many-to-one'
        join-column: 'id_contract'

        resource:
            alias: 'contract'
            table: 'contracts'
            primary-key: 'id'

            fields:
                -
                    name: 'name'

    -
        type: 'many-to-one'
        join-column: 'id_user_creation'

        resource:
            alias: 'creator'
            table: 'users'
            primary-key: 'id'

            fields:
                -
                    name: 'username'

    -
        type: 'many-to-one'
        join-column: 'id_user_last_edit'

        resource:
            alias: 'last-edit'
            table: 'users'
            primary-key: 'id'

            fields:
                -
                    name: 'username'

    -
        type: 'one-to-many'
        join-column: 'id_employee'

        resource:
            alias: 'payments'
            table: 'payments'
            primary-key: 'id'

            fields:
                -   
                    name: 'value'
                    type: 'number'

            relationships:
                -
                    type: 'many-to-one'
                    join-column: 'id_bank'
                    primary-key: 'id'

                    fields:
                        -
                            name: 'name'

## Installation ##

### Composer ###

You can install `innobitlab/php-fly-rrm` using [composer](http://getcomposer.org/) Dependency Manager.

If you need information about installing composer: [http://getcomposer.org/doc/00-intro.md#installation-nix](http://getcomposer.org/doc/00-intro.md#installation-nix)

Add this to your composer.json file:

    {
        "require": {
            "innobitlab/php-fly-rrm": "dev-master"
        }
    }

## Usage ##

### Without facade (more flexibility)

``` php
<?php

use FlyRRM\DataExtraction\DataExtractor;
use FlyRRM\Formatting\ArrayFormatter;
use FlyRRM\Formatting\Field\FieldFormatterConcreteFactory;
use FlyRRM\Hydration\ArrayHydrator;
use FlyRRM\Hydration\Field\FieldHydrationConcreteFactory;
use FlyRRM\Mapping\Parsing\Yaml\YamlMappingParser;
use FlyRRM\QueryBuilding\DBALQueryBuilder;
use FlyRRM\QueryExecution\DatabaseConfiguration;
use FlyRRM\QueryExecution\DBALQueryExecutor;

require_once '..\vendor\autoload.php';

// getting mapping file content
$yamlMapping = file_get_contents('emplyees_mapping.yaml');

// instanciate the parser
$parser = new YamlMappingParser();

// parsing mapping
$resource = $parser->parse($yamlMapping);

// instanciate a query builder
$dbalQueryBuilder = new DBALQueryBuilder();

// create a database configuration
$databaseConf = new DatabaseConfiguration();
$databaseConf->setDatabaseName('supercompany_db');
$databaseConf->setDriver('pdo_mysql');
$databaseConf->setHost('127.0.0.1');
$databaseConf->setPassword('SuperSecret!');
$databaseConf->setPort(3306);
$databaseConf->setUsername('myUser');

// instanciate the query executor with the related db config
$queryExecutor = new DBALQueryExecutor($databaseConf);

// instanciate a data extator with the query builder and the query executor 
$dataExtractor = new DataExtractor($dbalQueryBuilder, $queryExecutor);

// extracting data as plain array
$plainData = $dataExtractor->extractData($resource);

// instanciate the array hydrator with the appropriate field hydrator factory
$dataHydrator = new ArrayHydrator(new FieldHydrationConcreteFactory());

// hydrate into an array all the plain data
$hydratedData = $dataHydrator->hydrate($plainData, $resource);

// reformat the data (hydrator will hydrate to objects like \DateTime...)
$dataFormatter = new ArrayFormatter(new FieldFormatterConcreteFactory());
$formattedData = $dataFormatter->format($hydratedData, $resource);

// now you can encode in json
$json = json_encode($formattedData, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES);

With facade (more easy)

A proper facade to manage all the process end-to-end will be soon available, <sub>(*14)</sub>

Mapping

Currently the only implemented mapping parser is a YAML mapping parser. Other parsers can be implemented (ex: json, xml, php arrays)., <sub>(*15)</sub>

YAML

The root node must be a resource. A mapping must contain only one root resource., <sub>(*16)</sub>

Resource properties

• alias : mandatory, will be the "name" of the formatted resource
• table : mandatory, the name of the table where the resource data is stored
• primary-key : mandatory, the name of the primary-key field
• fields : mandatory, must be a sequence of fields
• relationships : optional, must be a sequence of relationship

A resource must have one and only one table., <sub>(*17)</sub>

At the moment only one-field primary keys are supported. Fly-rrm doesn't mind about the primary-key field type, be sure to use a type compatible with the sub-resources primary-key., <sub>(*18)</sub>

A resource must contain one or more fields. A resource could contain one or more relationship., <sub>(*19)</sub>

Field properties

• alias : optional, will be the "name" of the formatted resource field
• name : mandatory, the name of the field in the resource table
• type : optional, the type of the field
• format-string: optional, the field format string.

If no alias is specified will be used the name., <sub>(*20)</sub>

If no type is specified will be by default a string. Allowed types are: string, date, datetime, number., <sub>(*21)</sub>

Currently format-string is supported only on date and datetime types. You can use php's \Datetime::format pattern syntax., <sub>(*22)</sub>

Relationship properties

• type : mandatory, the type of relationship
• join-column : mandatory, the name of the join field on db
• resource : mandatory, mapping of the referenced resource

Supported relationship types are: one-to-many and many-to-one., <sub>(*23)</sub>

Many to many isn't useful because you always start watching from a resource to another., <sub>(*24)</sub>

You can indent as many resource with relationships as you need., <sub>(*25)</sub>

Connect to database

The only query builder and executor availables are the Docrine DBAL ones. Using DBAL you can perform extraction on many different DBMS., <sub>(*26)</sub>

For futher information about DBAL see: http://www.doctrine-project.org/projects/dbal.html, <sub>(*27)</sub>

Hydrating data

As shown in the example you can even use the hydrated array data. This will be a php associative array with alias and proper objects (ex: \DateTime for date and datetime types)., <sub>(*28)</sub>

Formatting data

The hydrated array should be formatted before export. This process will transform objects in strings according to the specified format-string. Numbers won't be formatted., <sub>(*29)</sub>

Exporting data

You can encode formatted data to your prefered format (ex. json, xml...), <sub>(*30)</sub>

Methodologies

This project has been developed with the Test Driven Development and Pomodoro technique methodologies. All the production code is covered by tests., <sub>(*31)</sub>

Until this readme the project has required me 55 Pomodoros of development., <sub>(*32)</sub>

Contributing

If you find any bug or you want to share improvements please send a pull-request. Test coverage on bugs or new features will be appreciated., <sub>(*33)</sub>

Credits

Idea and development: Gabriele Tondi info@gabrieletondi.it, <sub>(*34)</sub>

Thanks to Innobit s.r.l. for giving me the opportunity to share this code., <sub>(*35)</sub>