Details
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.
If there is another approach to specify the table/column names are case sensitive in PGSQL please let me know.