Tag finder:

Pomm: a concerto for PHP and Postgresql

 Posted by greg on June 10, 2012 8:27 PM |  Comments are deactivated for this post
Tags : geek  computing  postgresql  dev  php  pomm 

The weather website.

You can find the git archive of this tutorial on github.

After we did a discover of Pomm’s basic, let’s see how Postgresql’s types can make our life easier. We are now working on a « weather » application that probes weather condition for main French cities. In case you’d be interested in other’s country city … check this .

Exactly like the ‘previous article’:http://www.coolkeums.org/en/article/a-gentle-introduction-to-pomm.html , we are going to use Composer s autoloader with a minimal structured PHP application.

The database

The first important point is encoding. This tutorial uses french city names and some of them owns those weird and so french «é» or «ç» like Chambéry or Alençon. I strongly advise you use UTF-8 for encoding in your files and database or this tutorial might turn into a hidden bytes nightmare.

I have created a Gist with the structure and all the data pre-loaded. This SQL script will create the schema and load the data. If you want to ovoid encoding problem, I would recommend to just pipe the gist in the database:

wget -O - 'url' | psql 

Let’s just have a look at what we have:

your_db=> CREATE SCHEMA weather;
CREATE SCHEMA
your_db=> SET search_path TO weather, public;
SET
your_db=> CREATE TABLE city (
    name character varying NOT NULL,
    coords point NOT NULL
);
CREATE TABLE
your_db=> CREATE TABLE weather_probe (
    city_name character varying,
    created_at timestamp without time zone,
    temperature integer NOT NULL,
    wind_direction character(2) NOT NULL,
    wind_speed integer NOT NULL,
    humidity smallint NOT NULL,
    condition character varying NOT NULL,
    CONSTRAINT weather_probe_wind_speed_check CHECK ((wind_speed >= 0))
);
CREATE TABLE

The schema is very simple: we have 2 tables. city contains names and coordinates for all cities using the postgresql Point type and weather_probe contains measurements for temperature, wind etc.

Project structure

We do not need a PHP framework yet to display our page. So we are going to re-use the same structure as we used in the previous project :

  • a init.php file in the config directory.
  • a generate_model.php file in the bin directory.
  • a index.php file in the web directory.
<?php #config/init.php

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

$service = new Pomm\Service(
    array(
        'default' => array(
            'dsn' => 'pgsql://user:password@host:port/your_db'
        )
));

Just notice in the example below, the database name has been obfuscated using your_db to let you use your own database name. So is the namespace used for the model files as it depends on the database name. (see how to configure DSNs in the manual)

<?php #bin/generate_model.php

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

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

    ));
$scan->execute();
return $service;

If you parse the init.php from the command line, you should get no errors but calling the generate_model script should throw an exception:

$ php bin/generate_model.php

PHP Fatal error:  Uncaught exception 'PommExceptionException' with message 'Could not find a converter for type 'point' declared for database type 'PommConnectionDatabase' with dsn 'pgsql://greg/greg'.' in /var/lib/lxc/perso/rootfs/var/www/dev/tuto2/vendor/pomm/pomm/Pomm/Connection/Database.php:189
Stack trace:
[...] lot of useless lines here [...]

What’s going on ? It sounds the introspection class did not manage to find a converter for a type it met in the database: “point”. Yes, point is a standard type in Postgresql but it is not registered by default in Pomm. So this means we have to register this type on the database in the init.php file:

<?php #config/init.php

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

$service = new Pomm\Service(
    array(
        'default' => array(
            'dsn' => 'pgsql://user:password@host:port/your_db'
        )
));

$service->getDatabase('default')
    ->registerConverter('Point', new Pomm\Converter\PgPoint(), array('point'));

return $service;

What we do when registering the converter is basically saying: «I define a meta type “Point” using the converter “PgPoint” to deal with Postgresql’s type “point“». So every time Pomm will meet a data from type Point, it will use the given converter to get or set data in Postgresql. (see registering converters in the manual) We can now generate the model without pain.

$ php bin/generate_model.php
$ tree model
model
└── YourDb
    └── Weather
        ├── Base
        │   ├── CityMap.php
        │   └── WeatherProbeMap.php
        ├── CityMap.php
        ├── City.php
        ├── WeatherProbeMap.php
        └── WeatherProbe.php

3 directories, 6 files

When a lot of converters are needed, it is easier to create a children of the Database class that registers all them at initialization. Use the Class option in the Service constructor to specify the database class to instanciate.

Dealing with Google’s gadget weather web service

The command line script

We will acquire the data using google’s weather API so this will be triggered by a Cron job. The script has to retrieve cities from the database and ask Google the weather for each of them, then store the response in the database.

<?php #bin/get_weather_report.php

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

$connection = $service
    ->getDatabase()
    ->createConnection();

$city_map = $connection
    ->getMapFor("YourDb\Weather\City");

$weather_probe_map = $connection
    ->getMapFor("YourDb\Weather\WeatherProbe");

$cities = $city_map->findAll();

foreach($cities as $city) {
    try {
        $weather_probe_map->createFromGoogle($city);
        printf("'%s' ok.
", $city['name']);
        sleep(1);
    } catch(RunTimeException $e) {
        printf("Error wile processing city '%s'.
Ignoring.
", $city['name']);
    }
}

That simple script goes trough all cities in the database and put in charge the createFromGoogle method to get the weather data from Google’s API. Let’s code that method.

<?php #model/YourDatabase/Weather/WeatherProbeMap.php

namespace YourDb\Weather;

use YourDb\Weather\Base\WeatherProbeMap as BaseWeatherProbeMap;
use YourDb\Weather\WeatherProbe;
use Pomm\Exception\Exception;
use Pomm\Query\Where;

class WeatherProbeMap extends BaseWeatherProbeMap
{
    const GOOGLE_URL = "http://www.google.com/ig/api?weather=%s&hl=fr";

    public function createFromGoogle(City $city)
    {
        $data = utf8_encode(
          file_get_contents(
            sprintf(self::GOOGLE_URL, urlencode($city->get('name')))));
        $xml = simplexml_load_string($data);

        if (! $xml instanceof SimpleXMLElement ) {
            throw new \RuntimeException(
              sprintf("Error while getting XML for city '%s' with URL '%s'.", 
                $city->getName(), 
                self::GOOGLE_URL));
        }

        if (isset($xml->weather->problem_cause['data'])) {
            throw new \RuntimeException(
              sprintf("No data available for city '%s'.", 
                $city->getName()));
        }

        $weather_probe = $this->createObject();
        $weather_probe->setCityName($city->get('name'));
        $weather_probe->hydrateFromXml($xml);
        $this->saveOne($weather_probe);
    }
}

After getting the response and ensuring it is XML with expected data, we rely on an entity method to hydrate itself from the xml. Some of you might have noticed I used two types of getters, the first form get(‘name’) and getName(). Both returns the city name but internally, getName uses get(‘name’) we will see later the impact on inheritance it has. Note that I used the fr language to ask Google to use the metric system, so wind speed is in kilometers per hour and not miles. If you want miles, you can use the default setting.

<?php #model/YourDatabase/Weather/WeatherProbe.php

namespace YourDb\Weather;

use Pomm\Object\BaseObject;
use Pomm\Exception\Exception;

class WeatherProbe extends BaseObject
{
    public function hydrateFromXml(SimpleXMLElement $xml)
    {
        $this->set('temperature', 
          (string) $xml
            ->weather
            ->current_conditions
            ->temp_c['data']);

        $wind_condition = 
          (string) $xml
            ->weather
            ->current_conditions
            ->wind_condition['data'];

        preg_match('#Vent : ([SNEO]{1,2}) à ([0-9]+) km/h#', $wind_condition, $matchs);
        $this->set('wind_direction', $matchs[1]);
        $this->set('wind_speed', $matchs[2]);

        preg_match('#Humidité : ([0-9]{1,3}) %#', 
          (string) $xml
            ->weather
            ->current_conditions
            ->humidity['data'],
          $matchs);

        $this->set('humidity', $matchs[1]);
        $this->set('condition', 
          (string) $xml
            ->weather
            ->current_conditions
            ->icon['data']);
    }
}

Well, this code is not really error prone but it should do the job for a first attempt. Now launching the get_weather_report.php from the command line should work. Some cities may not be found, they will be ignored. Launch the script every two hours for a day, you should end up with a database full of data to play with ;) .

The application.

First contact.

The application is as simple as a showing a selectbox to pick a city. Once a city is selected, we display the city last weather info and links to the nearest cities.

<?php #web/index.php

header("Content-Type: text/html; charset=UTF-8");

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

$connection = $service
    ->getDatabase()
    ->createConnection();

$city_map = $connection
    ->getMapFor('YourDb\Weather\City');

$cities = $city_map->findAll();

?>
<html>
  <body>
    <div id="city_form">
      <form>
        <select name="city">
<?php foreach ($cities as $city): ?>
            <option value="<?php echo $city->get('name') ?>"><?php echo $city['name'] ?></option>
<?php endforeach ?>
        </select>
        <input type="submit" />
      </form>
    </div>
  </body>
</html>

This code only manages the selectbox part. Here, the cities are displayed like they are stored in the database, in lowercase. Maybe it would be nice to manage in the model a better way to display them. By overloading the getName method we do provide a way to do so without it interferes with the import script which uses get(‘name’) as we saw earlier.

<?php #model/YourDatabase/Weather/City.php

namespace YourDb\Weather;

use Pomm\ObjectBase\Object;
use Pomm\Exception\Exception;

class City extends BaseObject
{
    public function getName()
    {
        return ucwords($this->get('name'));
    }
}

It works, despite we used the ArrayAccess interface in our template ! Because internally, Pomm invokes getName when using the array or the direct attribute access form more convenient in templates. (see accessors in the manual).

Custom queries and joins.

Let’s finish our application. If a city is passed in the URL, we must retrieve its data with the associated last probed weather information and the, le’s say, 5 nearest cities.

<?php #web/index.php

// ...

if (isset($_GET['city'])) {
    $selected_city  = $city_map
      ->findByPk(array('name' => $_GET['city']));
    $nearest_cities = $city_map
      ->getNearestCitiesFrom($selected_city);
}

There are no built in queries for getting the 5 nearest city from a given one so we have to extend our model to create the according method. As we record the latitude and longitude of each city we can ask Postgresql to calculate the angular distance between each city and sort them by ascending order from a given city.

<?php #model/YourDatabase/Weather/CityMap.php

// ...

    public function getNearestCitiesFrom($city, $limit = 5)
    {
        $sql = <<<_
SELECT 
  %s, 
  c1.coords <-> c2.coords as distance 
FROM 
  %s c1, 
  %s c2
WHERE 
    c1.name = ? 
  AND 
    c1.name != c2.name 
ORDER BY 
  distance ASC 
LIMIT ?
_;
        $city_fields = join(', ', $this->getSelectFields('c2'));
        $city_table  = $this->getTableName(),

        $query = sprintf($sql, 
            $city_fields,
            $city_table,
            $city_table
            );

        return $this->query($query, array($city->get('name'), $limit));
    }

The query exposed above finds the cities ordered by ascending distance from a given city. The distance is given by the ‘<->‘ operator which gives the distance between two Point types. In our case, this is angular distance as the points own latitude and longitude but it is enough to get the nearest cities.

Let’s spend some time describing this example. The skeleton of a query is given where dynamic parts are set trough methods. These methods are very handy because they keep your query aware of changes in the schema. The fields are dynamically set using the getSelectFields method. This method is used in every SELECT query made by Pomm. This means overloading this method can add fields to all objects retrieved by this map class, we will see later how powerful this can be (see fields methods in the manual).

The query method is then called with the parameters. Unlike the printf parameters, the «?» parameters are substituted by the database engine. By doing so, Posgresql escapes them, you should always use this kind of parameter when variables are arguments you can’t trust. Here, “$limit” is passed as argument but you can’t tell it comes from a form or if it is set by a programmer. The query method returns a Collection which is an iterator on the results. (see custom queries chapter in the manual).

Relations and virtual fields

If we stand with the last query we just made, we are adapting the PHP code to a badly formatted set of data. What do we need ? We need a city with its weather information and the 5 nearest cities, isn’t it possible to ask Postgresql such a set of data ? Yes of course ! and even better: we can ask in one shot the 6 cities and their related last probed weather information. But the relationship between city and weather_probe is a 1 – n relation, how can I grab only the last set of weather data ? Of course, we won’t use Limit which is intended for formatting purposes only. We are going to take advantage of Pg’s window function rank().

SELECT 
  city_name, 
  created_at, 
  rank() OVER last_weather_location_window AS rank 
FROM 
  weather_probe 
ORDER BY
  city_name ASC
WINDOW 
  last_weather_location_window AS (PARTITION BY city_name ORDER BY created_at DESC)
;
 city_name |         created_at         | rank 
-----------+----------------------------+------
 nantes    | 2012-02-01 12:24:29.201203 |    1
 nantes    | 2012-01-31 14:07:31.422052 |    2
 nantes    | 2012-01-31 08:51:19.852184 |    3
 nantes    | 2012-01-30 15:15:25.383842 |    4
 paris     | 2012-02-01 12:26:24.960906 |    1
 paris     | 2012-01-30 15:15:25.383842 |    2

Let’s now join our queries. We can just join the city table with the weather_probe records having rank = 1. Let’s mix our two last queries:

WITH
  ranked_weather_probe AS (
    SELECT
      *,
      rank() OVER last_weather_location_window AS rank 
    FROM 
      weather_probe 
    WINDOW 
      last_weather_location_window AS 
        (PARTITION BY city_name ORDER BY created_at DESC)
  )
  SELECT 
    c2.*, 
    wp AS weather_probe,
    c1.coords <-> c2.coords AS distance
  FROM 
    city c1,
    city c2
      JOIN ranked_weather_probe wp ON 
          c2.name = wp.city_name 
        AND 
          wp.rank = 1
  WHERE
      c1.name = 'nantes'
  ORDER BY
    distance ASC
  LIMIT 5
;
      name       |     coords      |                                           weather_probe
-----------------+-----------------+----------------------------------------------------------------------
nantes           | (47.233,-1.583) | (nantes,"2012-02-25 14:17:54.749596",13,"E ",6,82,/ig/im...
-----------------+-----------------+----------------------------------------------------------------------
la-roche-sur-yon | (46.633,-1.5)   | (la-roche-sur-yon,"2012-02-25 14:17:55.981242",13,"E ",6,82,/ig/im...
-----------------+-----------------+----------------------------------------------------------------------
saint nazaire    | (47.283,-2.2)   | ("saint nazaire","2012-02-25 14:17:51.114168",14,NE,10,77,/ig/im...
-----------------+-----------------+----------------------------------------------------------------------
rennes           | (48.1,-1.667)   | (rennes,"2012-02-25 14:17:52.320063",11,NO,13,94,/ig/im...
-----------------+-----------------+----------------------------------------------------------------------
angers           | (47.483,-0.533) | (angers,"2012-02-25 14:18:03.381107",13,"E ",6,82,/ig/im...
-----------------+-----------------+----------------------------------------------------------------------
(5 rows)

Oh wait ! What have we done here ? Like the previous query, we sorted the weather_probe by rank and asked postgresql to return the object form of the joined weather_probe! This is a very powerful feature because it makes possible to retrieve easily associated sets of data. Note we now just have the 4th nearest cities as our select city is one of the fifith so it the next queries we will ask limit + 1.

Ok, Postgresql has done its job, let’s see how we can use Pomm to deal with the results. If I only hydrate City instances, of course Pomm would have no problem setting the extra attributes in them but we would shunt any existing getter overloads that would exist in WeatherProbe class. So the idea here is to ask Pomm to hydrate weather_probe results in a WeatherProbe instance and to tie it with the City instance. The idea here is to create a virtual field an tie it with a converter to WeatherProbe entity (see virtual field in the manual). Let’s update the getNearestCitiesFrom method:

<?php #model/YourDatabase/Weather/CityMap.php

// ...

    public function getNearestCitiesFrom($city_name, $limit)
    {

        $sql = <<<_
WITH
  ranked_weather_probe AS (
    SELECT 
      %s,
      rank() OVER created_at_window AS ranking
    FROM 
      %s
    WINDOW 
      created_at_window AS 
        (PARTITION BY city_name ORDER BY created_at DESC)
)
SELECT 
  %s,
  wp AS weather_probe,
  c1.coords <-> c2.coords AS distance
FROM 
  %s c1,
  %s c2
    JOIN ranked_weather_probe wp ON 
        c2.name = wp.city_name 
      AND 
        wp.ranking = 1
WHERE 
    c1.name = ?
ORDER BY 
  distance ASC
LIMIT ?
_;

        $weather_probe_map = $this->connection
          ->getMapFor('YourDb\Weather\WeatherProbe');
        $weather_probe_table  = $weather_probe_map->getTableName();
        $weather_probe_fields = join(', ', $weather_probe_map->getSelectFields());

        $city_fields = join(', ', $this->getSelectFields('c2'));
        $city_table  = $this->getTableName();

        $sql = sprintf($sql, 
            $weather_probe_fields,
            $weather_probe_table,
            $city_fields,
            $city_table,
            $city_table
        );

        $collection = $this->query($sql, array($city_name, $limit + 1));

        $this->addVirtualField('weather_probe', 'WeatherProbe');

        return $collection;
    }

The addVirtualField method links the result’s weather_probe column with a converter named WeatherProbe. For now, this converter is not registered in out database. We have to register it in the init.php file:

<?php #web/config/init.php
[...]

$service->getDatabase('default')
    ->registerConverter('Point', new Pomm\Converter\PgPoint(), array('point'))
    ->registerConverter('WeatherProbe', 
      new Pomm\Converter\PgEntity($service->getDatabase('default'), 'YourDb\Weather\WeatherProbe'),
      array('weather.weather_probe'));

Note the registerConverter method returns its Database instance so you can chain them.

<?php #web/index.php

// [...]

if (isset($_GET['city'])) {
    $selected_cities = $city_map
      ->getNearestCitiesFrom($_GET['city'], 5);
    $selected_city = $selected_cities->current();
}

?>
[ ... ]
<?php if (isset($selected_cities)): ?>
<div>
<div style="float:left;width:542px;">
<p><strong><?php echo $selected_city['name'] ?></strong></p>
  <p><img src="http://www.google.com<?php echo $selected_city['weather_probe']['condition'] ?>" /></p><p>T(celsius) <?php echo $selected_city['weather_probe']['temperature'] ?> Wind (<?php echo $selected_city['weather_probe']['wind_speed'] ?> km/h <?php echo $selected_city['weather_probe']['wind_direction'] ?>)</p>
  <img src="<?php printf("http://maps.googleapis.com/maps/api/staticmap?center=%s,%s&zoom=8&size=512x512&maptype=roadmap&sensor=false", $selected_city['coords']->x, $selected_city['coords']->y) ?>" />
</div>
<div>
<h3>Nearest cities around:</h3>
<ul>
<?php foreach($selected_cities as $city): ?>
<?php if ($selected_cities->isFirst()) continue ?>
<li><a href="?city=<?php echo $city->get('name') ?>"><?php echo $city['name'] ?></a> (<?php echo $city['weather_probe']['temperature'] ?> celsius)</li>
<?php endforeach ?>
</ul>
</div>
</div>
<?php endif ?>

Have a look at the way data are extracted from the City instance. «$city[‘weather_probe’]» returns the according WeatherProbe instance which also implements the ArrayAccess interface. Look also at the way coordinates are used to center the google map image. As we did register the Point converter, coords is turned into a PommTypePoint instance in PHP. This is the power of the converter system.

Collections are iterators on results. As we do fetch all results in one shot, we first extract the most interesting result by calling the current method to display its attributes on the main part of the page. The foreach statement does reset our iterator so we have to tell him to ignore the first result.

Sorting the cities

The selectbox displays all the cites but they are in a random order. Let’s order them by name

<?php #web/index.php

[...]

$cities = $city_map->findWhere('true', array(), 'ORDER BY name ASC');

The query is not optimal but it works for now, we will see later.

Logging the thing

When debugging, it is useful to have informations about the query sent to the server, the time they took and the number of results they had. The Logger class is meant for that.

<?php #web/index.php

[...]

$logger = new Pomm\Tools\Logger();
$connection
  ->registerFilter(new Pomm\FilterChain\LoggerFilter($logger));
register_shutdown_function(function() use ($logger) {
    $fh = fopen(__DIR__.'/../sql.logs', 'a+');
    fwrite($fh, print_r($logger->getLogs(), true));
    fclose($fh);
});

Just create en empty file sql.logs in the root of the project with the according rights and all the statements will be recorded in there.

Getting more from Postgresql

The XML cake

For now we were happy just with downloading Google’s XML, picking the data we needed before throwing the whole thing away. Moreover, the writing process involves a PHP computation that may present problems when scaling on a consequent number of sites. A solution for that problem would be to store the whole XML in the database and see what we can ask Postgres about that.

Let’s create our import table:

your_db=> CREATE TABLE probe_xml (city_name VARCHAR REFERENCES city (name), created_at TIMESTAMP DEFAULT now(), xml_response xml);
CREATE TABLE
-- IF PG >= 9.1
your_db=> ALTER TABLE probe_xml ADD CONSTRAINT valid_xml CHECK (xpath_exists('/xml_api_reply/weather/forecast_information/forecast_date', xml_response));
-- IF PG < 9.1
your_db=> ALTER TABLE probe_xml ADD CONSTRAINT valid_xml CHECK (array_dims(xpath('/xml_api_reply/weather/forecast_information/forecast_date', xml_response)) IS NOT NULL);

Adding the constraint is very interesting because it frees us from having to check the xml with PHP. If the XML does not comply with what we expect, an exception will be thrown and the record will be rejected. Do not forget to regenerate the model and we’ll see that greatly simplifies our import process:

<?php #config/get_weather_report.php

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

$connection = $service
    ->getDatabase()
    ->createConnection();

$city_map = $connection
    ->getMapFor("YourDb\Weather\City");

$probe_xml_map = $connection
    ->getMapFor("YourDb\Weather\ProbeXml");

$cities = $city_map->findAll();

foreach($cities as $city) {
    try {
        $probe_xml = $probe_xml_map
          ->createObject(array(
            'city_name' => $city->get('name'),
            'xml_response' => utf8_encode(file_get_contents(sprintf("http://www.google.com/ig/api?weather=%s&hl=fr", urlencode($city->get('name')))))
        ));
        $probe_xml_map->saveOne($probe_xml);
        sleep(1);
    } catch(PommExceptionException $e) {
        printf("Error wile processing city '%s'.
 Driver said '%s'.
Ignoring.
", $city['name'], $e->getMessage());
    }
}

the script from the command line should work and you should see results to appear in the probe_xml table. But we miss some transformations to get the data into a human readable format. So they need to be processed a bit more to get as clean as we used to have in the previous chapter. Well, since Postgres proposes XML functions, this is something that can be done on the fly with a VIEW. Let’s drop our table _weather_probe_ and create a view that does the job instead:

your_db=> DROP TABLE weather_probe;
DROP TABLE
your_db=> CREATE OR REPLACE VIEW weather_probe AS SELECT 
  city_name, 
  created_at,
  CAST(array_to_string(xpath('/xml_api_reply/weather/current_conditions/temp_c/@data', xml_response), '') AS integer) AS temperature,
  trim(substring(array_to_string(xpath('/xml_api_reply/weather/current_conditions/wind_condition/@data', xml_response), '') FROM '% #"[NSOE]+#" %' FOR '#')) AS wind_direction,
  CAST(substring(array_to_string(xpath('/xml_api_reply/weather/current_conditions/wind_condition/@data', xml_response), '') FROM '% #"[0-9]+#"%' FOR '#') AS integer) AS wind_speed,
  CAST(substring(array_to_string(xpath('/xml_api_reply/weather/current_conditions/humidity/@data', xml_response), '') FROM '% #"[0-9]+#"%' FOR '#') AS integer) AS humidity,
  trim(array_to_string(xpath('/xml_api_reply/weather/current_conditions/icon/@data', xml_response), '')) AS condition
FROM 
  probe_xml 
  ;
CREATE VIEW
your_db=> SELECT * FROM weather_probe;
    city_name     |         created_at         | temperature | wind_direction | wind_speed | humidity | ...
------------------+----------------------------+-------------+----------------+------------+----------+----
 nantes           | 2012-02-01 21:39:39.843716 |          -2 | NE             |         16 |       74 | ...
 saint nazaire    | 2012-02-01 21:50:34.205256 |          -2 | N              |         19 |       69 | ...
 saint brieuc     | 2012-02-01 21:50:35.607929 |           2 | NE             |         39 |       56 | ...
 ...

The SELECT for the VIEW may look a bit messy but this comes mainly because we have to parse XML content with regular expression as this XML was aimed at being used by javascript gadgets and its content to be printed out as is. Now, the application is working as it was before without the need of changing the code any further.

The last measurement design pattern

Well, intensive reads from the VIEW may put our database into troubles. Knowing we mostly need the last record of weather_probe for each city, why not create a caching mechanism ? The idea is to store somewhere the last record for each weather data probed. As soon as you know that for Postgresql, creating a table (or a view) is also declaring a new custom type, you understand we can add to the city table a column called last_weather_data of the _weather_probe_ type.

greg=> ALTER TABLE city ADD COLUMN weather_probe weather_probe;
ALTER TABLE
my_db=> UPDATE 
  city 
SET 
  weather_probe = wp
FROM (
  SELECT
    city_name,
    created_at,
    rank() OVER 
      (PARTITION BY city_name ORDER BY created_at DESC) AS ranking
  FROM
    weather_probe 
) wpr 
  INNER JOIN weather_probe wp 
    USING (city_name, created_at)
WHERE
    city.name = wp.city_name
  AND
    wpr.ranking = 1
;
UPDATE 108

As we already declared the converter for the WeatherProbe entity type, the model should generate without a hitch. There are still some changes we need to do because the effort we refused to do for every read shall now be done for every write. Every time a line is added to the probe_xml table we have to manually update our cache column in the city table, ensuring the data consistency with a transaction.

<?php #config/get_weather_report.php

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

$connection = $service
    ->getDatabase()
    ->createConnection();

$city_map = $connection
    ->getMapFor("YourDb\Weather\City");

$probe_xml_map = $connection
    ->getMapFor("YourDb\Weather\ProbeXml");

$weather_probe_map = $connection
    ->getMapFor("YourDb\Weather\WeatherProbe");

$cities = $city_map->findAll();

foreach($cities as $city) {
    try {
        $connection->begin();
        $probe_xml = $probe_xml_map->createObject(array(
            'city_name' => $city->get('name'),
            'xml_response' => utf8_encode(
              file_get_contents(
                sprintf("http://www.google.com/ig/api?weather=%s&hl=fr", urlencode($city->get('name')))))
        ));
        $probe_xml_map->saveOne($probe_xml);
        $city->setWeatherProbe(
          $weather_probe_map
            ->findWhere('city_name = ?', array($city->get('name')),
              'ORDER BY created_at DESC LIMIT 1')->current());
        $city_map->updateOne($city, array('weather_probe'));
        $connection->commit();
        sleep(1);
    } catch(PommExceptionException $e) {
        $connection->rollback();
        printf("Error wile processing city '%s'.
 Driver said '%s'.
Ignoring.
", $city['name'], $e->getMessage());
    }
}

The most interesting part here is the transaction of course but, look at the line 27 how easy it is to fetch the record from the view and assign it to the city table. The updateOne method located just under only saves the fields given as second parameter, so basically the _weather_probe_ field is just added. Of course, this could be done in the form of a triggered stored procedure so every software using the database would do the right thing but that’s another story. Let’s see how it is simple now to fetch the selected city and its five nearest neighbours:

<?php #model/YourDatabase/Weather/CityMap.php

// ...
    public function getNearestCitiesFrom($city_name, $nb)
    {
        $sql = <<<EOSQL
SELECT 
  %s,
  c1.coords <-> c2.coords AS distance
FROM 
  %s c1,
  %s c2
WHERE 
    c2.name = ?
ORDER BY 
  distance ASC
LIMIT ?
EOSQL;

        $sql = sprintf($sql, 
            join(', ', $this->getSelectFields('c1')),
            $this->getTableName(),
            $this->getTableName()
        );

        return $this->query($sql, array($city_name, $nb + 1));
    }

With that now simple query, we do retrieve our selected cities and its five closer neighbours and all their weather data plus … their angular distance with the city we chose. If we had a nice getDistance getter we can convert it into kilometers and display it:

<?php #model/YourDatabase/Weather/City.php

// [...]

    public function getDistance()
    {
        return $this->has('distance') ? (int) (((float) $this->get('distance') * pi() * 6371 ) / 180)  : null;
    }

Last thing, now we store both city data with their last weather information, the findWhere() call costs us a lot because it fetches more useless data than the other way around. Well, it is just the matter of creating a new finder that picks just wanted data:

<?php #model/YourDatabase/Weather/CityMap.php

[...]
    public function findAllName()
    {
        $sql = sprintf("SELECT name FROM %s ORDER BY name ASC", $this->getTableName());

        return $this->query($sql);
    }

That’s it. I hope you enjoyed getting trough Pomm’s features and I wish you had as pleasure doing this tutorial as I had creating it. Do not hesitate to send me questions, bug fixes, vacuum valves or more at “hubert dot greg at gmail dot com”.

Recent Links 

 sulzer young regulator

Bookmarked by greg on February 21, 2013 1:55 PM..
Tags: geek electronic

Follow link

 Méthode musique

Bookmarked by greg on February 2, 2013 3:00 PM..
Tags: geek

Follow link

 Pg modeler

Bookmarked by greg on September 29, 2012 6:28 AM..
Tags: geek computing postgresql

Follow link

 Parsing binary strings with PHP

Bookmarked by greg on September 24, 2012 3:29 PM..
Tags: geek computing dev php

Follow link

 Sprite map packager

Bookmarked by greg on September 24, 2012 2:24 PM..
Tags: geek computing dev

Follow link

 Rasp. Pi supercompteur

Bookmarked by greg on September 13, 2012 11:54 AM..
Tags: geek computing

Follow link

 Crew - code review for Git projects.

Bookmarked by greg on May 6, 2012 7:57 AM..
Tags: geek computing git

Follow link

 responsive grid system

Bookmarked by greg on May 5, 2012 12:38 PM..
Tags: geek computing dev

Follow link

 Vim "improved

Bookmarked by greg on March 8, 2012 9:26 AM..
Tags: geek computing vim

Follow link

 Highly scalable NoSql blog

Bookmarked by greg on March 3, 2012 9:12 AM..
Tags: geek computing dev

Follow link