Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1695

SQLs for PostgreSQL case sensite tables/fields are wrongly generated

    Details

    • Type: Bug Bug
    • Status: Reopened
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 2.1.6, 2.4.2
    • Fix Version/s: 2.1.7, 2.2.2
    • Component/s: None
    • Security Level: All
    • Labels:
      None
    • Environment:
      PostgreSQL 9.x, Symfony 2

      Description

      The SQLs for case sensitive schemas in postgreSQL are wronly generated.

      Example:
      Schema:

      CREATE TABLE "News" (
        "IdNews" serial NOT NULL,
        "IdUser" bigint NOT NULL,
        "IdLanguage" integer NOT NULL,
        "IdCondition" integer,
        "IdHealthProvider" integer,
        "IdSpeciality" integer,
        "IdMedicineType" integer,
        "IdTreatment" integer,
        "Title" character varying,
        "SmallText" character varying,
        "LongText" character varying,
        "PublishDate" timestamp with time zone,
        "IdxNews" tsvector,
        "Highlight" boolean NOT NULL DEFAULT false,
        "Order" integer NOT NULL DEFAULT 0,
        "Deleted" boolean NOT NULL DEFAULT false,
        "Active" boolean NOT NULL DEFAULT false,
        "UpdateToHighlighted" boolean DEFAULT false,
        CONSTRAINT "News_pkey" PRIMARY KEY ("IdNews" ));
      

      Object (I added quotes trying to generate the SQLs quoted.:

      <?php
      
      namespace GlobalTreatments\ApplicationBundle\Entity;
      
      use Doctrine\ORM\Mapping as ORM;
      
      /**
       * @ORM\Table(name="""News""")
       * @ORM\Entity
       */
      class News
      {
          /**
           * @var integer $idNews
           *
           * @ORM\Column(name="""IdNews""", type="integer", nullable=false)
           * @ORM\Id
           * @ORM\GeneratedValue(strategy="SEQUENCE")
           * @ORM\SequenceGenerator(sequenceName="""News_IdNews_seq""", allocationSize="1", initialValue="1")
           */
          private $idNews;
      
          /**
           * @var bigint $iduser
           *
           * @ORM\Column(name="""IdUser""", type="bigint", nullable=false)
           */
          private $idUser;
      
          /**
           * @var integer $idLanguage
           *
           * @ORM\Column(name="""IdLanguage""", type="integer", nullable=false)
           */
          private $idLanguage;
      
          /**
           * @var integer $idCondition
           *
           * @ORM\Column(name="""IdCondition""", type="integer", nullable=true)
           */
          private $idCondition;
      
          /**
           * @var integer $idHealthProvider
           *
           * @ORM\Column(name="""IdHealthProvider""", type="integer", nullable=true)
           */
          private $idHealthProvider;
      
          /**
           * @var integer $idSpeciality
           *
           * @ORM\Column(name="""IdSpeciality""", type="integer", nullable=true)
           */
          private $idSpeciality;
      
          /**
           * @var integer $idMedicineType
           *
           * @ORM\Column(name="""IdMedicineType""", type="integer", nullable=true)
           */
          private $idMedicineType;
      
          /**
           * @var integer $idTreatment
           *
           * @ORM\Column(name="""IdTreatment""", type="integer", nullable=true)
           */
          private $idTreatment;
      
          /**
           * @var string $title
           *
           * @ORM\Column(name="""Title""", type="string", nullable=true)
           */
          private $title;
      
          /**
           * @var string $smallText
           *
           * @ORM\Column(name="""SmallText""", type="string", nullable=true)
           */
          private $smallText;
      
          /**
           * @var string $longText
           *
           * @ORM\Column(name="""LongText""", type="string", nullable=true)
           */
          private $longText;
      
          /**
           * @var datetimetz $publishDate
           *
           * @ORM\Column(name="""PublishDate""", type="datetimetz", nullable=true)
           */
          private $publishDate;
      
          /**
           * @var tsvector $idxNews
           *
           * @ORM\Column(name="""IdxNews""", type="tsvector", nullable=true)
           */
          private $idxNews;
      
          /**
           * @var boolean $highlight
           *
           * @ORM\Column(name="""Highlight""", type="boolean", nullable=false)
           */
          private $highlight;
      
          /**
           * @var integer $order
           *
           * @ORM\Column(name="""Order""", type="integer", nullable=false)
           */
          private $order;
      
          /**
           * @var boolean $deleted
           *
           * @ORM\Column(name="""Deleted""", type="boolean", nullable=false)
           */
          private $deleted;
      
          /**
           * @var boolean $active
           *
           * @ORM\Column(name="""Active""", type="boolean", nullable=false)
           */
          private $active;
      
          /**
           * @var boolean $updateToHighlighted
           *
           * @ORM\Column(name="""UpdateToHighlighted""", type="boolean", nullable=true)
           */
          private $updateToHighlighted;
      
          /**
           * @var condition
           *
           * @ORM\ManyToOne(targetEntity="Condition")
           * @ORM\JoinColumns({
           *   @ORM\JoinColumn(name="""IdCondition""", referencedColumnName="""IdCondition""")
           * })
           */
          private $condition;
      
          /**
           * @var healthProvider
           *
           * @ORM\ManyToOne(targetEntity="HealthProvider")
           * @ORM\JoinColumns({
           *   @ORM\JoinColumn(name="""IdHealthProvider""", referencedColumnName="""IdHealthProvider""")
           * })
           */
          private $healthProvider;
      
          /**
           * @var language
           *
           * @ORM\ManyToOne(targetEntity="Language")
           * @ORM\JoinColumns({
           *   @ORM\JoinColumn(name="""IdLanguage""", referencedColumnName="""IdLanguage""")
           * })
           */
          private $language;
      
          /**
           * @var medicineType
           *
           * @ORM\ManyToOne(targetEntity="MedicineType")
           * @ORM\JoinColumns({
           *   @ORM\JoinColumn(name="""IdMedicineType""", referencedColumnName="""IdMedicineType""")
           * })
           */
          private $medicineType;
      
          /**
           * @var speciality
           *
           * @ORM\ManyToOne(targetEntity="Speciality")
           * @ORM\JoinColumns({
           *   @ORM\JoinColumn(name="""IdSpeciality""", referencedColumnName="""IdSpeciality""")
           * })
           */
          private $speciality;
      
          /**
           * @var treatment
           *
           * @ORM\ManyToOne(targetEntity="Treatment")
           * @ORM\JoinColumns({
           *   @ORM\JoinColumn(name="""IdTreatment""", referencedColumnName="""IdTreatment""")
           * })
           */
          private $treatment;
      
          /**
           * @var user
           *
           * @ORM\ManyToOne(targetEntity="User")
           * @ORM\JoinColumns({
           *   @ORM\JoinColumn(name="""IdUser""", referencedColumnName="""IdUser""")
           * })
           */
          private $user;
      
          ....
      
      }
      

      DQL:

      'SELECT n.smallText, n.publishDate ' .
      'FROM News n ' .
      	'INNER JOIN n.language la ' .
      'WHERE la.languageCode = :languageCode ' .
      'ORDER BY n.publishDate DESC'
      

      Generated SQL:

      SELECT "0_."SmallText" AS "SmallText"0, "0_."PublishDate" AS "PublishDate"1 FROM "News" "0_ INNER JOIN "Language" "1_ ON "0_."IdLanguage" = "1_."IdLanguage" WHERE "1_."LanguageCode" = ? ORDER BY "0_."PublishDate" DESC LIMIT 6
      

      Notice there are unmattched " in the SQL.

      1. doctrine-2.1.6.patch
        4 kB
        Ignacio Larranaga
      2. SqlWalker.patch
        2 kB
        Ignacio Larranaga

        Activity

        Hide
        Benjamin Eberlei added a comment -

        Fixed and merged into 2.1.x and 2.2 branches

        Show
        Benjamin Eberlei added a comment - Fixed and merged into 2.1.x and 2.2 branches
        Hide
        Julian Aicardo added a comment -

        This bug still unresolved for version 2.3.6-DEV included in Symfony 2.3.18.

        The patches does not work with this version.

        Generated query:

        SELECT "0_.id AS id0, "0_."verFirmware" AS verfirmware1, "0_."idEstacion" AS idestacion2, "0_."fechaHora" AS fechahora3, "0_.global AS global4, "0_.directa AS directa5, "0_.difusa AS difusa6, "0_."tempSensDirecta" AS tempsensdirecta7, "0_.vbat1 AS vbat18, "0_.vbat2 AS vbat29, "0_.flags AS flags10, "0_."Estacion" AS estacion11 FROM "RegistroRS" "0_ WHERE "0_."idEstacion" = '1' AND "0_."fechaHora" >= '2014-02-01 03:00:00' AND "0_."fechaHora" <= '2014-08-01 03:00:00'

        Notice there are unmattched " in the SQL.

        Show
        Julian Aicardo added a comment - This bug still unresolved for version 2.3.6-DEV included in Symfony 2.3.18. The patches does not work with this version. Generated query: SELECT "0_.id AS id0, "0_."verFirmware" AS verfirmware1, "0_."idEstacion" AS idestacion2, "0_."fechaHora" AS fechahora3, "0_.global AS global4, "0_.directa AS directa5, "0_.difusa AS difusa6, "0_."tempSensDirecta" AS tempsensdirecta7, "0_.vbat1 AS vbat18, "0_.vbat2 AS vbat29, "0_.flags AS flags10, "0_."Estacion" AS estacion11 FROM "RegistroRS" "0_ WHERE "0_."idEstacion" = '1' AND "0_."fechaHora" >= '2014-02-01 03:00:00' AND "0_."fechaHora" <= '2014-08-01 03:00:00' Notice there are unmattched " in the SQL.
        Hide
        Marco Pivetta added a comment -

        Julian Aicardo why are you using 2.3.6-DEV? Shouldn't you use a stable version? Is the bug reproducible also with later versions?

        Show
        Marco Pivetta added a comment - Julian Aicardo why are you using 2.3.6-DEV ? Shouldn't you use a stable version? Is the bug reproducible also with later versions?
        Hide
        Julian Aicardo added a comment -

        After changing to version 2.4.2 the bug still there. The same SQL query was generated.

        Show
        Julian Aicardo added a comment - After changing to version 2.4.2 the bug still there. The same SQL query was generated.
        Hide
        Julian Aicardo added a comment -

        The error occurs when try to get an alias based on case sensitive table name, because the alias is generated with the first character of table's name which is ".
        Ex:
        Table: car Alias: c0_
        Table: "Car" Alias: "0_

        Is in file SqlWalker.php line 295 in Doctrine 2.4.4:

        SqlWalker.php
        public function getSQLTableAlias($tableName, $dqlAlias = '')
        {
            $tableName .= ($dqlAlias) ? '@[' . $dqlAlias . ']' : '';
                
            if ( ! isset($this->tableAliasMap[$tableName])) {
                $this->tableAliasMap[$tableName] = strtolower(substr($tableName, 0, 1)) . $this->tableAliasCounter++ . '_';
            }
        
            return $this->tableAliasMap[$tableName];
        }
        
        Show
        Julian Aicardo added a comment - The error occurs when try to get an alias based on case sensitive table name, because the alias is generated with the first character of table's name which is ". Ex: Table: car Alias: c0_ Table: "Car" Alias: "0_ Is in file SqlWalker.php line 295 in Doctrine 2.4.4: SqlWalker.php public function getSQLTableAlias($tableName, $dqlAlias = '') { $tableName .= ($dqlAlias) ? '@[' . $dqlAlias . ']' : ''; if ( ! isset($ this ->tableAliasMap[$tableName])) { $ this ->tableAliasMap[$tableName] = strtolower(substr($tableName, 0, 1)) . $ this ->tableAliasCounter++ . '_'; } return $ this ->tableAliasMap[$tableName]; }

          People

          • Assignee:
            Marco Pivetta
            Reporter:
            Ignacio Larranaga
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated: