[DBAL-337] Column types are truncated when mapping PostGIS types Created: 04/Sep/12  Updated: 05/Aug/13  Resolved: 05/Sep/12

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms, Schema Managers
Affects Version/s: 2.2.1
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Jonathan Derrough Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: postgis
Environment:

Linux Debian Stable, Symfony 2.0, Doctrine 2.2.1, PostgreSQL, PostGIS



 Description   

When performing a doctrine schema update with Symfony2 ('php app/console doctrine:schema:update --force --verbose') using a custom PostGIS type mapping, column type descriptions are truncated.

How to reproduce:

1) add a custom 'point' type to Doctrine's type mapping

in a controller's boot method
$em = $this->container->get('doctrine.orm.default_entity_manager');
$conn = $em->getConnection();
if (!Type::hasType('point')) {
  Type::addType('point', 'Demo\GeoBundle\Lib\Type\PointType');
  $conn->getDatabasePlatform()->registerDoctrineTypeMapping('point', 'point');
}
PointType.php
<?php

namespace Demo\GeoBundle\Lib\Type;

use Demo\GeoBundle\Lib\Point;

use Doctrine\DBAL\Types\Type; 
use Doctrine\DBAL\Platforms\AbstractPlatform;

class PointType extends Type {

  const POINT = 'point';

  /**
   *
   * @param array $fieldDeclaration
   * @param AbstractPlatform $platform
   * @return string 
   */
  public function getSqlDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
  {
    return 'geography(POINT,'.Point::$SRID.')';
  }

  /**
   *
   * @param type $value
   * @param AbstractPlatform $platform
   * @return Point 
   */
  public function convertToPHPValue($value, AbstractPlatform $platform)
  {
    return Point::fromGeoJson($value);
  }

  public function getName()
  {
    return self::POINT;
    //return 'geography(POINT,'.Point::$SRID.')';
  }

  public function convertToDatabaseValue($value, AbstractPlatform $platform)
  {
    return $value->toWKT();
  }

  public function canRequireSQLConversion()
  {
    return true;
  }

  public function convertToPHPValueSQL($sqlExpr, $platform)
  {
    return 'ST_AsGeoJSON('.$sqlExpr.') ';
  }

  public function convertToDatabaseValueSQL($sqlExpr, AbstractPlatform $platform)
  {
    return $sqlExpr;
  }

}

?>
Point.php
<?php

namespace Demo\GeoBundle\Lib;

use Demo\GeoBundle\Lib\GeoException;

class Point {
  private $lat;
  private $lon;
  public static $SRID = '4326';

  private function __construct($lon, $lat) {
    $this->lat = $lat;
    $this->lon = $lon;
  }
  
  public function setLongitude($lon) {
    $this->lon = $lon;
  }
  
  public function getLongitude() {
    return $this->lon;
  }

  public function setLatitude($lat) {
    $this->lat = $lat;
  }

  public function getLatitude() {
    return $this->lat;
  }

  public function toGeoJson(){
    $array = array("type" => "Point", "coordinates" => array ($this->lon, $this->lat));
    return \json_encode($array);
  }

  /**
   *
   * @return string 
   */
  public function toWKT() {
    return 'SRID='.self::$SRID.';POINT('.$this->lon.' '.$this->lat.')';
  }

  /**
   *
   * @param string $geojson
   * @return Point 
   */
  public static function fromGeoJson($geojson) 
  {
    $a = json_decode($geojson);
    //check if the geojson string is correct
    if ($a == null or !isset($a->type) or !isset($a->coordinates)){
      throw GeoException::badJsonString();
    }

    if ($a->type != "Point"){
      throw GeoException::badGeoType();
    } else {
      $lon = $a->coordinates[0];
      $lat = $a->coordinates[1];
      return Point::fromLonLat($lon, $lat);
    }

  }
  
  /**
   *
   * @return string
   */
  public function __toString() {
    return $this->lon . ' ' . $this->lat;
  }

  /**
   *
   * @return array
   */
  public function toArray() {
    return array($this->lon, $this->lat);
  }

  public static function fromLonLat($lon, $lat)
  {
    if (($lon > -180.0 && $lon < 180.0) && ($lat > -90.0 && $lat < 90.0))
    {
      return new Point($lon, $lat);
    } else {
      throw GeoException::badCoordinates($lon, $lat);
    }
  }
}

?>
GeoException.php
<?php

namespace Demo\GeoBundle\Lib;

class GeoException extends \RuntimeException
{
    public function __construct($message = null, \Exception $previous = null, $code = 0)
    {
        parent::__construct($message, $code, $previous);
    }

    public static function badJSonString()
    {
      return new GeoException("Bad JSon string");
    }

    public static function badGeoType()
    {
      return new GeoException("Bad geo type");
    }

    public static function badCoordinates($lon, $lat)
    {
      return new GeoException("Bad coordinates: " . $lon . " " . $lat);
    }
}

2) with Symfony2, create a simple entity using the Point type:

TestPoint.php
<?php

namespace Demo\TestBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="test_points")
 */
class TestPoint
{
  /**
   * @ORM\Id
   * @ORM\Column(type="integer")
   * @ORM\GeneratedValue(strategy="AUTO")
   */
  protected $id;

  /**
   * @ORM\Column(type="point")
   */
  protected $coordinates;

  /**
   * Get id
   *
   * @return integer 
   */
  public function getId()
  {
    return $this->id;
  }

  /**
   * Set coordinates
   *
   * @param point $coordinates
   * @return Partner
   */
  public function setCoordinates($coordinates)
  {
    $this->coordinates = $coordinates;
    return $this;
  }

  /**
   * Get coordinates
   *
   * @return point 
   */
  public function getCoordinates()
  {
    return $this->coordinates;
  }
  
  public function setLongitude($lon)
  {
    if ($this->coordinates == null)
    {
      $this->coordinates = Point::fromLonLat($lon, 0);
    }
    else
    {
      $this->coordinates->setLongitude($lon);
    }
  }
  
  public function getLongitude()
  {
    return $this->coordinates == null ? 0 : $this->coordinates->getLongitude();
  }
  
  public function setLatitude($lat)
  {
    if ($this->coordinates == null)
    {
      $this->coordinates = Point::fromLonLat(0, $lat);
    }
    else
    {
      $this->coordinates->setLatitude($lat);
    }
  }
  
  public function getLatitude()
  {
    return $this->coordinates == null ? 0 : $this->coordinates->getLatitude();
  }
}

3) use the update script to create the table:

php app/console doctrine:schema:update --force --verbose

4) run it again to update:

php app/console doctrine:schema:update --force --verbose

Errors should ensue:

Errors

[Doctrine\DBAL\DBALException]
Unknown database type geography requested, Doctrine\DBAL\Platforms\PostgreSqlPlatform may not support it.

Exception trace:
() at /.../Symfony/vendor/doctrine-dbal/lib/Doctrine/DBAL/Platforms/AbstractPlatform.php:261
Doctrine\DBAL\Platforms\AbstractPlatform->getDoctrineTypeMapping() at /.../Symfony/vendor/doctrine-dbal/lib/Doctrine/DBAL/Schema/PostgreSqlSchemaManager.php:285
Doctrine\DBAL\Schema\PostgreSqlSchemaManager->_getPortableTableColumnDefinition() at /.../Symfony/vendor/doctrine-dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:672
Doctrine\DBAL\Schema\AbstractSchemaManager->_getPortableTableColumnList() at /.../Symfony/vendor/doctrine-dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:159
Doctrine\DBAL\Schema\AbstractSchemaManager->listTableColumns() at /.../Symfony/vendor/doctrine-dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:254
Doctrine\DBAL\Schema\AbstractSchemaManager->listTableDetails() at /.../Symfony/vendor/doctrine-dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:242
Doctrine\DBAL\Schema\AbstractSchemaManager->listTables() at /.../Symfony/vendor/doctrine-dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:830
Doctrine\DBAL\Schema\AbstractSchemaManager->createSchema() at /.../Symfony/vendor/doctrine/lib/Doctrine/ORM/Tools/SchemaTool.php:689
Doctrine\ORM\Tools\SchemaTool->getUpdateSchemaSql() at /.../Symfony/vendor/doctrine/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/UpdateCommand.php:103
Doctrine\ORM\Tools\Console\Command\SchemaTool\UpdateCommand->executeSchemaCommand() at /.../Symfony/vendor/doctrine/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/AbstractCommand.php:59
Doctrine\ORM\Tools\Console\Command\SchemaTool\AbstractCommand->execute() at /.../Symfony/vendor/symfony/src/Symfony/Bundle/DoctrineBundle/Command/Proxy/UpdateSchemaDoctrineCommand.php:62
Symfony\Bundle\DoctrineBundle\Command\Proxy\UpdateSchemaDoctrineCommand->execute() at /.../Symfony/vendor/symfony/src/Symfony/Component/Console/Command/Command.php:226
Symfony\Component\Console\Command\Command->run() at /.../Symfony/vendor/symfony/src/Symfony/Component/Console/Application.php:194
Symfony\Component\Console\Application->doRun() at /.../Symfony/vendor/symfony/src/Symfony/Bundle/FrameworkBundle/Console/Application.php:76
Symfony\Bundle\FrameworkBundle\Console\Application->doRun() at /.../Symfony/vendor/symfony/src/Symfony/Component/Console/Application.php:118
Symfony\Component\Console\Application->run() at /.../Symfony/app/console:22

doctrine:schema:update [--complete] [--dump-sql] [--force] [--em[="..."]]

What seems to be happening is that the returned type for the column holding the points is truncated. "Full" type is 'geography(Point,4326)', returned type from Doctrine is 'geography' (missing the specifics).
Adding other custom mappings for PostGIS types, LineString for example, will result with the exact same error although the full type is 'geography(LineString,4326)'. The above test case would still be erroneous with the full type returned as the mapping specifies a 'dbType' known as 'point' or 'linestring' and not 'geography(Point,4326)' or 'geography(LineString,4326)', adjustments should be made there.



 Comments   
Comment by Jonathan Derrough [ 04/Sep/12 ]

Further investigation showed that the truncation makes sense in a general context as types (ex: varchar) are simplifications of complete types (ex: character varying (255)). But it still presents a problem for PostGIS users.

A quick workaround would be to change the mapping to:

in a controller's boot method
$em = $this->container->get('doctrine.orm.default_entity_manager');
$conn = $em->getConnection();
if (!Type::hasType('point')) {
  Type::addType('point', 'Demo\GeoBundle\Lib\Type\PointType');
  $conn->getDatabasePlatform()->registerDoctrineTypeMapping('geography', 'point');
}

It seems to work fine with several custom PostGIS types mapped but it wasn't fully tested.

Comment by Benjamin Eberlei [ 05/Sep/12 ]

The way you do it its actually a workaround, but Doctrine Bundle has a way to configure the registerDoctrineMappingType() calls via configuration.

Check with "php app/console config:dump-reference DoctrineBundle"

Comment by Jonathan Derrough [ 05/Sep/12 ]

Yes, I am aware of the mapping configuration with the config file. I tested it again:

config.yml
# Doctrine Configuration
doctrine:
    dbal:
        driver:   %database_driver%
        host:     %database_host%
        port:     %database_port%
        dbname:   %database_name%
        user:     %database_user%
        password: %database_password%
        charset:  UTF8
        types:
            point: Demo\GeoBundle\Lib\Type\PointType
            linestring: Demo\GeoBundle\Lib\Type\LineStringType
    orm:
        auto_generate_proxy_classes: %kernel.debug%
        auto_mapping: true

But the issue here remains, the same error is outputed:

[Doctrine\DBAL\DBALException]
Unknown database type geography requested, Doctrine\DBAL\Platforms\PostgreSqlPlatform may not suppo
rt it.

As I detailed previously, the fetched type of a geography column is truncated. So my workaround to avoid the errors during update was to register my custom types with 'geography' as db-type. It doesn't seem to be the right way since only one doctrine-type can be map a db-type (so here, 'geography' would be associated with either 'point' or 'linestring').

Comment by Benjamin Eberlei [ 05/Sep/12 ]

Thats only half the picture, see "mapping_types" here http://symfony.com/doc/current/reference/configuration/doctrine.html

Comment by Jonathan Derrough [ 07/Sep/12 ]

With the updated config.yml:

config.yml
# Doctrine Configuration
doctrine:
    dbal:
        driver:   %database_driver%
        host:     %database_host%
        port:     %database_port%
        dbname:   %database_name%
        user:     %database_user%
        password: %database_password%
        charset:  UTF8
        types:
            point: Demo\GeoBundle\Lib\Type\PointType
            linestring: Demo\GeoBundle\Lib\Type\LineStringType
        mapping_types:
            geography(point,4326): point
            geography(linestring,4326): linestring
    orm:
        auto_generate_proxy_classes: %kernel.debug%
        auto_mapping: true

errors are still showing.

Using:

config.yml
        mapping_types:
            geography: point
            geography: linestring

errors are gone but I guess it is the same as:

$conn->getDatabasePlatform()->registerDoctrineTypeMapping('geography', 'point');
$conn->getDatabasePlatform()->registerDoctrineTypeMapping('geography', 'linestring');

and I don't see how it would be right since the 'linestring' mapping would replace the 'point' mapping.

I'm affraid I don't get it.

Comment by Almog Baku [ 05/Aug/13 ]

this bug is happening to me too with mysql.. Actually its works as excepted within `schema:create`, but it throw an error with `schema:update` or `schema:drop`

I using doctrine with symfony

Generated at Sun Dec 21 16:44:36 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.