Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-337

Column types are truncated when mapping PostGIS types

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Invalid
    • Affects Version/s: 2.2.1
    • Fix Version/s: None
    • Component/s: Platforms, Schema Managers
    • Security Level: All
    • Labels:
    • 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.

        Activity

        Jonathan Derrough created issue -
        Hide
        Jonathan Derrough added a comment -

        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.

        Show
        Jonathan Derrough added a comment - 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.
        Hide
        Benjamin Eberlei added a comment -

        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"

        Show
        Benjamin Eberlei added a comment - 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"
        Benjamin Eberlei made changes -
        Field Original Value New Value
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Invalid [ 6 ]
        Hide
        Jonathan Derrough added a comment - - edited

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

        Show
        Jonathan Derrough added a comment - - edited 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').
        Hide
        Benjamin Eberlei added a comment -

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

        Show
        Benjamin Eberlei added a comment - Thats only half the picture, see "mapping_types" here http://symfony.com/doc/current/reference/configuration/doctrine.html
        Hide
        Jonathan Derrough added a comment -

        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.

        Show
        Jonathan Derrough added a comment - 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.
        Hide
        Almog Baku added a comment -

        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

        Show
        Almog Baku added a comment - 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

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DBAL-337, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Jonathan Derrough
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: