DBAL-337: Column types are truncated when mapping PostGIS types #1523

Closed
doctrinebot opened this Issue Sep 4, 2012 · 7 comments

2 participants

@doctrinebot

Jira issue originally created by user jderrough:

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

$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');
}
<?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;
  }

}

?>
<?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);
    }
  }
}

?>
<?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:

<?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:
{panel:title=Errors| borderStyle=dashed| borderColor=#ccc| titleBGColor=#F7D6C1| bgColor=#FFFFCE}
[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[="..."]]
{panel}

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.

@doctrinebot

Comment created by jderrough:

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:

$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.

@doctrinebot

Comment created by @beberlei:

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"

@doctrinebot

Issue was closed with resolution "Invalid"

@doctrinebot

Comment created by jderrough:

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

# 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').

@doctrinebot

Comment created by @beberlei:

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

@doctrinebot

Comment created by jderrough:

With the updated 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:

        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.

@doctrinebot

Comment created by almogbaku:

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

@doctrinebot doctrinebot added the Bug label Dec 6, 2015
@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment