Pomm 1.x in few steps

We are going to show a simple application as quick Pomm test sandbox.

requirements:
➥ PHP 5.4 CLI
➥ A working Postgresql database

Step 1: Install the sandbox

Fire up a terminal, and enter the following line to download and launch the quick installer:

wget -O - 'https://gist.github.com/chanmix51/9192161/download' 2>/dev/null |tar xzO > install.sh
    bash install.sh
It will prompt you for useful informations about your database connection and bootstrap a ready to run Silex application for you. When the script is over, open your browser and enter the URL http://localhost:1025, you should be granted with a «Hello world» page saying your database connection is working. If not, you may have wrong connection settings, check the file sources/config/config.php to change your connection parameters.

The sandbox is like the following (omitting the vendor directory and some more files):


    .
    ├── bin
    │   └── generate_model.php
    ├── sources
    │   ├── application.php
    │   ├── bootstrap.php
    │   ├── config
    │   │   ├── config.php
    │   │   └── environment.php
    │   ├── lib
    │   │   ├── Controller
    │   │   │   └── DumbController.php
    │   │   └── Model
    │   │       └── MyDatabase.php
    │   └── twig
    │       ├── error.html.twig
    │       └── index.html.twig
    └── web
        ├── favicon.ico
        └── index.php
    

Step 2: Generate the model

You can use the bin/generate_model.php script to generate the model files. You have to pass the schema where your tables are as argument. If you do not know about Postgresql schemas, enter public which is the default schema.

php bin/generate_model.php public

This command will generate model files under the sources/lib/Model directory using database name and schema name as namespaces. So it should end like the following:


    sources/lib/Model
        ├── MyDatabase.php
        └── Sandbox
            └── PublicSchema
                ├── Base
                │   ├── FactoryMap.php
                │   ├── MeasureMap.php
                │   ├── StationMap.php
                │   └── StatisticMap.php
                ├── FactoryMap.php
                ├── Factory.php
                ├── MeasureMap.php
                ├── Measure.php
                ├── StationMap.php
                ├── Station.php
                ├── StatisticMap.php
                └── Statistic.php
    

Step 3: Query the database

Once map classes set up, you can create your controllers method. From your controller, use the map class to fetch data from your model:

     1 // sources/lib/Controller/DumbController.php
     2     public function listItem()
     3     {
     4         $stations = $this->app['pomm.connection']
     5             ->getMapFor('\Sandbox\PublicSchema\Station')
     6             ->findWhere('name LIKE $*', [ '%some_place%' ]);
     7 
     8         return $this->app['twig']->render('list_stations.html.twig', [ 'stations' => $stations ]);
    9      }
    10 
    

In your template, iterating over the collection or using the entities getters is pretty straightforward:

    1 <ul>
    2 {% for station in stations %}
    3     <li>name: <a href="/station/{{ station.station_id }}">{{ station.name }}</a>
    4       operate since {{ station.created_at | date('Y-m-d') }}</li>
    5 {% endfor %}
    6 </ul>
    

Step 4: Tune projection and converter

Unlike ORMs, Pomm maps database sets into schemaless entities. Map classes define a projection and trigger data conversion between PHP and the database. If by example the database holds sensitive data like encryption keys, it is possible to remove them from the projection hence they will not be fetched from the database. It is also possible to add fields. If we prefer having the age of the station entity instead of giving its creation date, we can modify the projection so the database adds the age to other fields:

     1 // sources/lib/Model/Sandbox/PublicSchema/StationMap.php
     2 
     3     public function getSelectFields($alias = null)
     4     {
     5         $fields = parent::getSelectFields($alias);
     6         unset($fields['encryption_key']);
     7         $fields['age'] = sprintf("age(%s)", $this->aliasField('created_at', $alias));
     8 
     9         return $fields;
    10     }
    11 
    

Adding fields to the default projection is easy but without more information, Pomm will assume there are strings. In the case above, the Postgresql's function age returns an interval so it would be nice to tell the map file to manage the age as such:

     1 // sources/lib/Model/Sandbox/PublicSchema/StationMap.php
     2 
     3     public function initialize()
     4     {
     5         parent::initialize();
     6 
     7         $this->addVirtualField('age', 'interval');
     8     }
     9 
    
This way, the converter associated with Postgresql's interval type will be used to produce a PHP DateInterval instance.

Step 5: Tune the queries

Default queries coming with the generated map classes are pretty simples, they just apply the given projection from a given set in the database. Most of the time it is enough but some cases require more informations coming from other database tables. By example, in a blog post, it would be useful to provide links to the previous and / or next published articles. A custom query can handle that:

     1 // sources/lib/Model/Sandbox/Blog/PostMap.php
     2 
     3     public function findBySlugWithNeighbours($slug)
     4     {
     5         $sql = <<<SQL
     6 WITH
     7   neighbour AS (
     8     SELECT
     9       slug,
    10       lag(slug) OVER published_at_wdw AS next_slug,
    11       lead(slug) OVER  published_at_wdw AS prev_slug
    12     FROM
    13       :news_table
    14     WINDOW
    15       published_at_wdw AS (ORDER BY published_at DESC)
    16     )
    17 SELECT
    18     :news_fields,
    19     n.next_slug AS next_slug,
    20     n.prev_slug AS prev_slug
    21 FROM
    22     :news_table
    23       NATURAL JOIN neighbour n
    24 WHERE
    25     news.slug = $*
    26 SQL;
    27 
    28         $sql = strtr($sql, array(
    29             ':news_fields' => $this->formatFields('getSelectFields'),
    30             ':news_table' => $this->getTableName()
    31         ));
    32 
    33         return $this->query($sql, array($slug))->current();
    34     }
    
At first glance this looks complicated but it is not that complex. The method defines a custom SQL query using placeholders (:news_table and :news_fields). A first set named neighbour is defined, containing every slug with the previous and following slugs based on publishing date (window function). From that set, only the asked blog post is picked up with its time based neighbours' slug. Placeholders are expanded with the default projection using the formatFields() formatter. Since slug is the primary key for that table, only the first element (if it exists) it returned using the current() method.

The controller is a simple as:

     1 
     2 // sources/lib/Controller/DumbController.php
     3 
     4     public function showPost($slug)
     5     {
     6         $post = $this->app['pomm.connection']
     7             ->getMapFor('\Sandbox\Blog\Post')
     8             ->findBySlugWithNeighbours($slug);
     9 
    10         if (!$post)
    11         {
    12             return $this->app->abort(404, "No such post sorry");
    13         }
    14 
    15         return $this->app['twig']->render('show_post.html.twig', [ "post" => $post ]);
    16     }