Tag finder: Tags... amplifier application bd blog book computing dailymotion debian dev electronic forum friends geek git github javascript jquery linux net nodejs ooc php pomm postgresql python screen shell silex speaker store symfony ubuntu valve vim whitepaper youtube

h2. Writing PHP application with Postgresql: Pomm

It has been a long time since I wrote an article on my blog. Well, lot of things have changed in my life last couple of month and I have been quite busy working on Pomm . For those who do not know Pomm, it is a fast, lightweight, efficient non ORM.

What is a non ORM ?

Well it is a software that is different of what ORMs aimed at :

• no database abstraction: Pomm works with Postgresql only.
• no external agnostic schema: the database is the schema.
• no relation guessing: do what you want and just what you need.

• no query builder: the query language is Postgres’SQL.

In doing this, we do free ourselves from ORM’s limitations in terms of type support, speed and query possibilities. In other words, Pomm is a layer above PDO to provide some features it lacks. Here are some:

• Pg <-> PHP type translators (Arrays, geometric, binary and some more types support like HStore, Ltree … )
• Identity Mapper
• Schema agnostic objects hydration
• Lazy results fetching
• Pagers
• Pg Schemas support

• Database introspection (tables and views).

Let’s go

I did really like Peter van Hardenberg’s presentation about Postgresql and the example is simple enough to think about creating a PHP application that would show servers’ states.

Bootstraping the database

We will use PHP 5.3 and postgresql 8.4 as there are the minimum requirements for Pomm. First, we have to set up the database. We will create a schema _server_status_ where the event table will be stored:

db=> CREATE SCHEMA server_stat;
CREATE SCHEMA
db=> SET search_path TO server_stat, public;
SET
db=> CREATE TABLE event AS
 SELECT
   floor(random() * 20) as app_id, 
   (ARRAY[
     'starting', 'running', 'offline'
   ])[floor(random() * 3) + 1] as state,
   generate_series(
     now() - '1 year'::interval, 
     now(), 
     '2 hour'::interval) as entered_at;
CREATE TABLE
db=> SELECT * FROM event LIMIT 5;
 app_id |  state   |          entered_at           
--------+----------+-------------------------------
     16 | offline  | 2011-01-27 12:46:30.943267+00
     11 | starting | 2011-01-27 14:46:30.943267+00
     17 | starting | 2011-01-27 16:46:30.943267+00
      1 | offline  | 2011-01-27 18:46:30.943267+00
      8 | starting | 2011-01-27 20:46:30.943267+00
(5 rows)

We now have an event table full of records to test our not yet born application.

Bootstraping the application

No need for a framework here, we will just rely on Composer’s autoloader to ease our development. Let’s start in an empty directory, we are going to set tell composer our dependencies and create some directories. Open an editor and create the composer.json file as is:

{
    "require": {
        "pomm/pomm": "master-dev"
    }
} 

Let’s invoke composer and finish the job:

$ composer.phar install
Installing from lock file
  - Package pomm/pomm (master-dev)
    Downloading
    Unpacking archive
    Cleaning up

Generating autoload files
$ mkdir {web,config,bin}

Let’s finish with writing the config/init.php file that sets up the database connection:

<?php #config/init.php

require __DIR__."/../vendor/.composer/autoload.php";

$service = new Pomm\Service(
    array(
        'default' => array(
            'dsn' => sprintf('pgsql://user/your_db_name', __DIR__)
        )
));

return $service;

Parsing this file from the command line should return you no error. If you do not know what is the DSN of your database, check pomm’s documentation about it. If really in doubt, the documentation provided with Pomm might be more up to date.

Generate the model files

File generation is not something triggered by an online call. It is something developers want from the command line. Let’s create the PHP generation script we store in the bin directory

<?php # bin/generate_model.php

$service = require(__DIR__."/../config/init.php");

$scan = new Pomm\Tools\ScanSchemaTool(array(
    'schema' => 'server_stat',
    'database' => $service->getDatabase(),
    'prefix_dir' => __DIR__."/../model",

    ));
$scan->execute();

$ php bin/generate_model.php
$ tree model
model
└── YourDbName
    └── ServerStat
        ├── Base
        │   └── EventMap.php
        ├── EventMap.php
        └── Event.php

3 directories, 3 files

If we open the EventMap.php file in the Base subdirectory, we should see something like this :

<?php # model/YourDbName/ServerStat/Base/EventMap.php

namespace YourDbName\ServerStat\Base;

use \Pomm\Object\BaseObjectMap;
use \Pomm\Exception\Exception;

abstract class EventMap extends BaseObjectMap
{
    public function initialize()
    {

        $this->object_class =  'YourDbName\ServerStat\Event';
        $this->object_name  =  'server_stat.event';

        $this->addField('app_id', 'Number');
        $this->addField('state', 'String');
        $this->addField('entered_at', 'Timestamp');

        $this->pk_fields = array('');
    }
}

The last thing we must do before going on doing real things with it is to register the model directory to the autoloader.

<?php #config/init.php

$loader = require __DIR__."/../vendor/.composer/autoload.php";
$loader->add('YourDbName', __DIR__."/../model");

...

Writing the application

Let’s start simple

The first application we will write will just show all records from the database.

<?php # web/index.php

$service = require(__DIR__."/../config/init.php");

$map = $service
    ->getDatabase()
    ->createConnection()
    ->getMapFor("YourDbName\ServerStat\Event");

?>

<html>
    <body>
    <table>
        <th>
            <td>app_id</td><td>state</td><td>At</td>
        </th>
<?php foreach ($map->findAll() as $event): ?>
        <tr>
            <td><?php echo $event['app_id'] ?></td>
            <td><?php echo $event['state'] ?></td>
            <td><?php echo $event['entered_at']->format('Y-m-d H:i:s') ?></td>
        </tr>
<?php endforeach ?>
    </table>
    </body>
</html>

A bit too simplistic

Of course, we are not interested in all the records but only the last ones and it would be nice if the results could be ordered by _entered_at_ from the most recent to the oldest. This is the job of findWhere

// SELECT app_id, state, entered_at FROM server_stat.event WHERE true ORDER BY entered_at DESC LIMIT 30
$events = $map->findWhere('true', array(), 'ORDER BY entered_at DESC LIMIT 30');

Of course the «WHERE true» is not that sexy but we will see later how to write custom nice queries. For now, a nice feature of our application would be to make the user able to click on an app_id to filter only the state changes of this particular server instance. Let’s modify a bit our script:

<?php # web/index.php

$service = require(__DIR__."/../config/init.php");

$map = $service
    ->getDatabase()
    ->createConnection()
    ->getMapFor("YourDbName\ServerStat\Event");

if (isset($_GET['app_id'])) {
    $condition = 'app_id = ?';
    $values = array($_GET['app_id']);
} else {
    $condition = 'true';
    $values = array();
}

$events = $map->findWhere($condition, $values, 'ORDER BY entered_at DESC LIMIT 30');

?>

<html>
    <body>
    <p>back to <a href="index.php">index</a></p>
    <table>
        <th>
            <td>app_id</td><td>state</td><td>At</td>
        </th>
<?php foreach ($events as $event): ?>
        <tr>
            <td><a href="?app_id=<?php echo $event['app_id'] ?>"><?php echo $event['app_id'] ?></a></td>
            <td><?php echo $event['state'] ?></td>
            <td><?php echo $event['entered_at']->format('Y-m-d H:i:s') ?></td>
        </tr>
<?php endforeach ?>
    </table>
    </body>
</html>

Pagers

One coolest thing here would be using a pager instead of truncating the results to an arbitrary 30 rows limit. Using Pomm’s Pager class is that easy:

<?php # web/index.php
$page = isset($_GET['page']) ? $_GET['page'] : 1;

$pager = $map->PaginatefindWhere($condition, $values, 'ORDER BY entered_at DESC', 30, $page);
$events = $pager->getCollection();

The Pager also contains a lot of informations that we are going to use to display kind of a ruler to navigate trough pages. Here is the whole file:

<?php # web/index.php

$service = require(__DIR__."/../config/init.php");

$map = $service
    ->getDatabase()
    ->createConnection()
    ->getMapFor("YourDbName\ServerStat\Event");

if (isset($_GET['app_id'])) {
    $condition = 'app_id = ?';
    $values = array($_GET['app_id']);
} else {
    $condition = 'true';
    $values = array();
}

$page = isset($_GET['page']) ? $_GET['page'] : 1;

$pager = $map->PaginatefindWhere($condition, $values, 'ORDER BY entered_at DESC', 30, $page);
$events = $pager->getCollection();

?>

<html>
    <body>
    <p>back to <a href="index.php">index</a></p>
    <table>
        <th>
            <td>app_id</td><td>state</td><td>At</td>
        </th>
<?php foreach ($events as $event): ?>
        <tr>
            <td><a href="?app_id=<?php echo $event['app_id'] ?>"><?php echo $event['app_id'] ?></a></td>
            <td><?php echo $event['state'] ?></td>
            <td><?php echo $event['entered_at']->format('Y-m-d H:i:s') ?></td>
        </tr>
<?php endforeach ?>
    </table>
<?php if ($pager->getLastPage() > 1): ?>
<div>
  <a href="?page=1">First</a>&nbsp;
  <?php if ($pager->isPreviousPage()): ?><a href="?page=<?php echo $pager->getPage() - 1 ?>">Previous</a><?php endif ?>
    Page <?php echo $pager->getPage() ?> / <?php echo $pager->getLastPage() ?> 
    <?php if ($pager->isNextPage()): ?><a href="?page=<?php echo $pager->getPage() + 1 ?>">Next</a>&nbsp;<?php endif ?>
<a href="?page=<?php echo $pager->getLastPage() ?>">Last</a>
</div>
<?php endif ?>
    </body>
</html>

And after ?

Of course the example displayed below is simple and only aimed at showing how simple it is to use Pomm’s basics. If you went to the end of Hardenberg’s presentation you might be tempted to create a new page and display server’s uptime with the impressive query he showed in his slides. That is as easy as creating a VIEW with this query and ask Pomm to generate the model again.

db=> CREATE VIEW server_uptime AS
WITH
  transitions AS (SELECT
    (event) as current,
      lead((event), 1) over (
          partition by app_id 
              order by entered_at) as next
                FROM event),
  durations AS (
    SELECT *, 
      (next).entered_at - 
        (current).entered_at as duration
    FROM transitions
  ),
  interesting_transitions AS (
    SELECT * FROM durations
      WHERE (current).state = 'running'
        AND (next).state = 'offline')
SELECT (current).app_id,
       justify_hours(sum(duration))
         as running_for
FROM interesting_transitions
GROUP BY (current).app_id
;
CREATE VIEW
db=> SELECT * FROM server_uptime;
 app_id |   running_for    
--------+------------------
      0 | 41 days 22:00:00
     10 | 45 days 14:00:00
     17 | 39 days 06:00:00
     12 | 37 days 18:00:00
     13 | 30 days
     15 | 27 days 06:00:00
      5 | 28 days
     16 | 29 days 12:00:00
      2 | 37 days 14:00:00
      7 | 47 days 02:00:00
     11 | 40 days 20:00:00
      9 | 24 days 20:00:00
      1 | 49 days 14:00:00
      3 | 27 days 06:00:00
      6 | 32 days 14:00:00
      8 | 53 days 14:00:00
     19 | 62 days 10:00:00
     14 | 57 days
      4 | 44 days 12:00:00
     18 | 38 days 18:00:00
(20 rows)

That’s it for today, next time we will go trough the converter system. Take care.