Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-65

No way to store binary data in PostgreSQL with Doctrine

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Invalid
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Environment:
      PostgreSQL 8.4

      Description

      The type system introduced by Doctrine makes impossible to store binary data in PostgreSQL databases that use Unicode. The `text` type is mapped to `TEXT`, but any trial to place some binary data there ends up with a database error, like this:

      SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding "UTF8": 0x9c

      This is a critical limitation, because Doctrine cannot be used now in projects that for any reasons have to use PostgreSQL, and their databases must store binary data. Even if it cannot be fixed right now, it should be clearly pointed out in the documentation in "Known vendor issues".

      A possible solution for this problem is creating an equivalent of 'text' field, called 'binary' or something like that. It must be a simple type that is mapped to the simplest, but large type available in the database engine without any form of data structure validation. For PostgreSQL, this could be 'blob', but other database engines can use different types.

        Activity

        Hide
        Jon Wadsworth added a comment - - edited

        This is an old post but just in case somebody else finds it. There is no need to do any of the above to store binary data in Postgres. I had the same situation and was easily solved by compressing file, base64 encoding it, and finally serializing it.

        public static function prepareFileforDatabase($file)

        { $compressor = new \Zend_Filter_Compress_Gz(); $file = $compressor->compress($file); $file = base64_encode($file); return serialize($file); }

        We use Zend and you may be able to get away with not compressing if you wanted to avoid the extra overhead on your server. To undo it is exactly the opposite.

        public static function prepareFileforPHP($file)

        { $compressor = new \Zend_Filter_Compress_Gz(); $file = unserialize($file); $file = base64_decode($file); return $compressor->decompress($file); }

        Sorry for the code coming out in all one line, but you get the idea.

        Show
        Jon Wadsworth added a comment - - edited This is an old post but just in case somebody else finds it. There is no need to do any of the above to store binary data in Postgres. I had the same situation and was easily solved by compressing file, base64 encoding it, and finally serializing it. public static function prepareFileforDatabase($file) { $compressor = new \Zend_Filter_Compress_Gz(); $file = $compressor->compress($file); $file = base64_encode($file); return serialize($file); } We use Zend and you may be able to get away with not compressing if you wanted to avoid the extra overhead on your server. To undo it is exactly the opposite. public static function prepareFileforPHP($file) { $compressor = new \Zend_Filter_Compress_Gz(); $file = unserialize($file); $file = base64_decode($file); return $compressor->decompress($file); } Sorry for the code coming out in all one line, but you get the idea.
        Hide
        Tomasz Jędrzejewski added a comment -

        I know I can create a custom type, but I'd like to have a portable binary type by default in Doctrine DBAL, not reinventing the wheel every time I want to have one. I consider binary data as one of the primitive types that every database engine supports.

        Show
        Tomasz Jędrzejewski added a comment - I know I can create a custom type, but I'd like to have a portable binary type by default in Doctrine DBAL, not reinventing the wheel every time I want to have one. I consider binary data as one of the primitive types that every database engine supports.
        Hide
        Benjamin Eberlei added a comment -

        This is not an issue, there are two options to "solve" your problem in userland:

        1. Create your own DBAL type - http://www.doctrine-project.org/projects/orm/2.0/docs/reference/basic-mapping/en#custom-mapping-types
        2. Use columnDefinition Attribute of @column - http://www.doctrine-project.org/projects/orm/2.0/docs/reference/annotations-reference/en#ann_column

        Show
        Benjamin Eberlei added a comment - This is not an issue, there are two options to "solve" your problem in userland: 1. Create your own DBAL type - http://www.doctrine-project.org/projects/orm/2.0/docs/reference/basic-mapping/en#custom-mapping-types 2. Use columnDefinition Attribute of @column - http://www.doctrine-project.org/projects/orm/2.0/docs/reference/annotations-reference/en#ann_column
        Hide
        Tomasz Jędrzejewski added a comment - - edited

        Just a small note why I consider this bug as quite serious: for many programmers and their projects the lack of both support for such content type and any information about the limitation can be very dangerous. It can be impossible to remove ORM, if such an issue is encountered in the implementation process, and trials to workaround it are time-consuming.

        If I'm about to decide whether to use a particular ORM or not, I must have full information about ORM and database-specific limitations.

        One more update: shame on me, obviously there is no "blob" type in PostgreSQL; in this database engine binary data could be represented by 'BYTEA'.

        Show
        Tomasz Jędrzejewski added a comment - - edited Just a small note why I consider this bug as quite serious: for many programmers and their projects the lack of both support for such content type and any information about the limitation can be very dangerous. It can be impossible to remove ORM, if such an issue is encountered in the implementation process, and trials to workaround it are time-consuming. If I'm about to decide whether to use a particular ORM or not, I must have full information about ORM and database-specific limitations. One more update: shame on me, obviously there is no "blob" type in PostgreSQL; in this database engine binary data could be represented by 'BYTEA'.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Tomasz Jędrzejewski
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: