public function testPostgresqlDialect() { $dialect = new \Phalcon\Db\Dialect\Postgresql(); $columns = $dialect->getColumnList(array('column1', 'column2', 'column3')); $this->assertEquals($columns, '"column1", "column2", "column3"'); $columns = $this->getColumns(); //Column definitions $this->assertEquals($dialect->getColumnDefinition($columns['column1']), 'CHARACTER VARYING(10)'); $this->assertEquals($dialect->getColumnDefinition($columns['column2']), 'INT'); $this->assertEquals($dialect->getColumnDefinition($columns['column3']), 'NUMERIC(10,2)'); $this->assertEquals($dialect->getColumnDefinition($columns['column4']), 'CHARACTER(100)'); $this->assertEquals($dialect->getColumnDefinition($columns['column5']), 'DATE'); $this->assertEquals($dialect->getColumnDefinition($columns['column6']), 'TIMESTAMP'); $this->assertEquals($dialect->getColumnDefinition($columns['column7']), 'TEXT'); $this->assertEquals($dialect->getColumnDefinition($columns['column8']), 'FLOAT'); $this->assertEquals($dialect->getColumnDefinition($columns['column9']), 'CHARACTER VARYING(10)'); $this->assertEquals($dialect->getColumnDefinition($columns['column10']), 'INT'); $this->assertEquals($dialect->getColumnDefinition($columns['column11']), 'BIGINT'); $this->assertEquals($dialect->getColumnDefinition($columns['column12']), "ENUM(\"A\", \"B\", \"C\")"); $this->assertEquals($dialect->getColumnDefinition($columns['column13']), "TIMESTAMP"); //Add Columns $this->assertEquals($dialect->addColumn('table', null, $columns['column1']), 'ALTER TABLE "table" ADD COLUMN "column1" CHARACTER VARYING(10)'); $this->assertEquals($dialect->addColumn('table', 'schema', $columns['column1']), 'ALTER TABLE "schema"."table" ADD COLUMN "column1" CHARACTER VARYING(10)'); $this->assertEquals($dialect->addColumn('table', null, $columns['column2']), 'ALTER TABLE "table" ADD COLUMN "column2" INT'); $this->assertEquals($dialect->addColumn('table', 'schema', $columns['column2']), 'ALTER TABLE "schema"."table" ADD COLUMN "column2" INT'); $this->assertEquals($dialect->addColumn('table', null, $columns['column3']), 'ALTER TABLE "table" ADD COLUMN "column3" NUMERIC(10,2) NOT NULL'); $this->assertEquals($dialect->addColumn('table', 'schema', $columns['column3']), 'ALTER TABLE "schema"."table" ADD COLUMN "column3" NUMERIC(10,2) NOT NULL'); $this->assertEquals($dialect->addColumn('table', null, $columns['column4']), 'ALTER TABLE "table" ADD COLUMN "column4" CHARACTER(100) NOT NULL'); $this->assertEquals($dialect->addColumn('table', 'schema', $columns['column4']), 'ALTER TABLE "schema"."table" ADD COLUMN "column4" CHARACTER(100) NOT NULL'); $this->assertEquals($dialect->addColumn('table', null, $columns['column5']), 'ALTER TABLE "table" ADD COLUMN "column5" DATE NOT NULL'); $this->assertEquals($dialect->addColumn('table', 'schema', $columns['column5']), 'ALTER TABLE "schema"."table" ADD COLUMN "column5" DATE NOT NULL'); $this->assertEquals($dialect->addColumn('table', null, $columns['column6']), 'ALTER TABLE "table" ADD COLUMN "column6" TIMESTAMP NOT NULL'); $this->assertEquals($dialect->addColumn('table', 'schema', $columns['column6']), 'ALTER TABLE "schema"."table" ADD COLUMN "column6" TIMESTAMP NOT NULL'); $this->assertEquals($dialect->addColumn('table', null, $columns['column7']), 'ALTER TABLE "table" ADD COLUMN "column7" TEXT NOT NULL'); $this->assertEquals($dialect->addColumn('table', 'schema', $columns['column7']), 'ALTER TABLE "schema"."table" ADD COLUMN "column7" TEXT NOT NULL'); $this->assertEquals($dialect->addColumn('table', null, $columns['column8']), 'ALTER TABLE "table" ADD COLUMN "column8" FLOAT NOT NULL'); $this->assertEquals($dialect->addColumn('table', 'schema', $columns['column8']), 'ALTER TABLE "schema"."table" ADD COLUMN "column8" FLOAT NOT NULL'); $this->assertEquals($dialect->addColumn('table', null, $columns['column9']), 'ALTER TABLE "table" ADD COLUMN "column9" CHARACTER VARYING(10) DEFAULT "column9"'); $this->assertEquals($dialect->addColumn('table', 'schema', $columns['column9']), 'ALTER TABLE "schema"."table" ADD COLUMN "column9" CHARACTER VARYING(10) DEFAULT "column9"'); $this->assertEquals($dialect->addColumn('table', null, $columns['column10']), 'ALTER TABLE "table" ADD COLUMN "column10" INT DEFAULT "10"'); $this->assertEquals($dialect->addColumn('table', 'schema', $columns['column10']), 'ALTER TABLE "schema"."table" ADD COLUMN "column10" INT DEFAULT "10"'); $this->assertEquals($dialect->addColumn('table', null, $columns['column11']), 'ALTER TABLE "table" ADD COLUMN "column11" BIGINT'); $this->assertEquals($dialect->addColumn('table', 'schema', $columns['column11']), 'ALTER TABLE "schema"."table" ADD COLUMN "column11" BIGINT'); $this->assertEquals($dialect->addColumn('table', null, $columns['column12']), 'ALTER TABLE "table" ADD COLUMN "column12" ENUM("A", "B", "C") DEFAULT "A" NOT NULL'); $this->assertEquals($dialect->addColumn('table', 'schema', $columns['column12']), 'ALTER TABLE "schema"."table" ADD COLUMN "column12" ENUM("A", "B", "C") DEFAULT "A" NOT NULL'); $this->assertEquals($dialect->addColumn('table', 'schema', $columns['column13']), 'ALTER TABLE "schema"."table" ADD COLUMN "column13" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL'); //Modify Columns $this->assertEquals($dialect->modifyColumn('table', null, $columns['column1'], $columns['column2']), 'ALTER TABLE "table" RENAME COLUMN "column2" TO "column1";ALTER TABLE "table" ALTER COLUMN "column1" TYPE CHARACTER VARYING(10);'); $this->assertEquals($dialect->modifyColumn('table', 'schema', $columns['column1'], $columns['column2']), 'ALTER TABLE "schema"."table" RENAME COLUMN "column2" TO "column1";ALTER TABLE "schema"."table" ALTER COLUMN "column1" TYPE CHARACTER VARYING(10);'); $this->assertEquals($dialect->modifyColumn('table', null, $columns['column2'], $columns['column1']), 'ALTER TABLE "table" RENAME COLUMN "column1" TO "column2";ALTER TABLE "table" ALTER COLUMN "column2" TYPE INT;'); $this->assertEquals($dialect->modifyColumn('table', 'schema', $columns['column2'], $columns['column1']), 'ALTER TABLE "schema"."table" RENAME COLUMN "column1" TO "column2";ALTER TABLE "schema"."table" ALTER COLUMN "column2" TYPE INT;'); $this->assertEquals($dialect->modifyColumn('table', null, $columns['column3'], $columns['column2']), 'ALTER TABLE "table" RENAME COLUMN "column2" TO "column3";ALTER TABLE "table" ALTER COLUMN "column3" TYPE NUMERIC(10,2);ALTER TABLE "table" ALTER COLUMN "column3" SET NOT NULL;'); $this->assertEquals($dialect->modifyColumn('table', 'schema', $columns['column3'], $columns['column2']), 'ALTER TABLE "schema"."table" RENAME COLUMN "column2" TO "column3";ALTER TABLE "schema"."table" ALTER COLUMN "column3" TYPE NUMERIC(10,2);ALTER TABLE "schema"."table" ALTER COLUMN "column3" SET NOT NULL;'); $this->assertEquals($dialect->modifyColumn('table', null, $columns['column4'], $columns['column2']), 'ALTER TABLE "table" RENAME COLUMN "column2" TO "column4";ALTER TABLE "table" ALTER COLUMN "column4" TYPE CHARACTER(100);ALTER TABLE "table" ALTER COLUMN "column4" SET NOT NULL;'); $this->assertEquals($dialect->modifyColumn('table', 'schema', $columns['column4'], $columns['column2']), 'ALTER TABLE "schema"."table" RENAME COLUMN "column2" TO "column4";ALTER TABLE "schema"."table" ALTER COLUMN "column4" TYPE CHARACTER(100);ALTER TABLE "schema"."table" ALTER COLUMN "column4" SET NOT NULL;'); $this->assertEquals($dialect->modifyColumn('table', null, $columns['column5'], $columns['column2']), 'ALTER TABLE "table" RENAME COLUMN "column2" TO "column5";ALTER TABLE "table" ALTER COLUMN "column5" TYPE DATE;ALTER TABLE "table" ALTER COLUMN "column5" SET NOT NULL;'); $this->assertEquals($dialect->modifyColumn('table', 'schema', $columns['column5'], $columns['column2']), 'ALTER TABLE "schema"."table" RENAME COLUMN "column2" TO "column5";ALTER TABLE "schema"."table" ALTER COLUMN "column5" TYPE DATE;ALTER TABLE "schema"."table" ALTER COLUMN "column5" SET NOT NULL;'); $this->assertEquals($dialect->modifyColumn('table', null, $columns['column6'], $columns['column2']), 'ALTER TABLE "table" RENAME COLUMN "column2" TO "column6";ALTER TABLE "table" ALTER COLUMN "column6" TYPE TIMESTAMP;ALTER TABLE "table" ALTER COLUMN "column6" SET NOT NULL;'); $this->assertEquals($dialect->modifyColumn('table', 'schema', $columns['column6'], $columns['column2']), 'ALTER TABLE "schema"."table" RENAME COLUMN "column2" TO "column6";ALTER TABLE "schema"."table" ALTER COLUMN "column6" TYPE TIMESTAMP;ALTER TABLE "schema"."table" ALTER COLUMN "column6" SET NOT NULL;'); $this->assertEquals($dialect->modifyColumn('table', null, $columns['column7'], $columns['column2']), 'ALTER TABLE "table" RENAME COLUMN "column2" TO "column7";ALTER TABLE "table" ALTER COLUMN "column7" TYPE TEXT;ALTER TABLE "table" ALTER COLUMN "column7" SET NOT NULL;'); $this->assertEquals($dialect->modifyColumn('table', 'schema', $columns['column7'], $columns['column2']), 'ALTER TABLE "schema"."table" RENAME COLUMN "column2" TO "column7";ALTER TABLE "schema"."table" ALTER COLUMN "column7" TYPE TEXT;ALTER TABLE "schema"."table" ALTER COLUMN "column7" SET NOT NULL;'); $this->assertEquals($dialect->modifyColumn('table', null, $columns['column8'], $columns['column2']), 'ALTER TABLE "table" RENAME COLUMN "column2" TO "column8";ALTER TABLE "table" ALTER COLUMN "column8" TYPE FLOAT;ALTER TABLE "table" ALTER COLUMN "column8" SET NOT NULL;'); $this->assertEquals($dialect->modifyColumn('table', 'schema', $columns['column8'], $columns['column2']), 'ALTER TABLE "schema"."table" RENAME COLUMN "column2" TO "column8";ALTER TABLE "schema"."table" ALTER COLUMN "column8" TYPE FLOAT;ALTER TABLE "schema"."table" ALTER COLUMN "column8" SET NOT NULL;'); $this->assertEquals($dialect->modifyColumn('table', null, $columns['column9'], $columns['column2']), 'ALTER TABLE "table" RENAME COLUMN "column2" TO "column9";ALTER TABLE "table" ALTER COLUMN "column9" TYPE CHARACTER VARYING(10);ALTER TABLE "table" ALTER COLUMN "column9" SET DEFAULT "column9"'); $this->assertEquals($dialect->modifyColumn('table', 'schema', $columns['column9'], $columns['column2']), 'ALTER TABLE "schema"."table" RENAME COLUMN "column2" TO "column9";ALTER TABLE "schema"."table" ALTER COLUMN "column9" TYPE CHARACTER VARYING(10);ALTER TABLE "schema"."table" ALTER COLUMN "column9" SET DEFAULT "column9"'); $this->assertEquals($dialect->modifyColumn('table', null, $columns['column10'], $columns['column2']), 'ALTER TABLE "table" RENAME COLUMN "column2" TO "column10";ALTER TABLE "table" ALTER COLUMN "column10" SET DEFAULT "10"'); $this->assertEquals($dialect->modifyColumn('table', 'schema', $columns['column10'], $columns['column2']), 'ALTER TABLE "schema"."table" RENAME COLUMN "column2" TO "column10";ALTER TABLE "schema"."table" ALTER COLUMN "column10" SET DEFAULT "10"'); $this->assertEquals($dialect->modifyColumn('table', null, $columns['column11'], $columns['column2']), 'ALTER TABLE "table" RENAME COLUMN "column2" TO "column11";'); $this->assertEquals($dialect->modifyColumn('table', 'schema', $columns['column11'], $columns['column2']), 'ALTER TABLE "schema"."table" RENAME COLUMN "column2" TO "column11";'); $this->assertEquals($dialect->modifyColumn('table', null, $columns['column12'], $columns['column2']), 'ALTER TABLE "table" RENAME COLUMN "column2" TO "column12";ALTER TABLE "table" ALTER COLUMN "column12" SET NOT NULL;ALTER TABLE "table" ALTER COLUMN "column12" SET DEFAULT "A"'); $this->assertEquals($dialect->modifyColumn('table', 'schema', $columns['column12'], $columns['column2']), 'ALTER TABLE "schema"."table" RENAME COLUMN "column2" TO "column12";ALTER TABLE "schema"."table" ALTER COLUMN "column12" SET NOT NULL;ALTER TABLE "schema"."table" ALTER COLUMN "column12" SET DEFAULT "A"'); $this->assertEquals($dialect->modifyColumn('table', 'schema', $columns['column13'], $columns['column2']), 'ALTER TABLE "schema"."table" RENAME COLUMN "column2" TO "column13";ALTER TABLE "schema"."table" ALTER COLUMN "column13" TYPE TIMESTAMP;ALTER TABLE "schema"."table" ALTER COLUMN "column13" SET NOT NULL;ALTER TABLE "schema"."table" ALTER COLUMN "column13" SET DEFAULT CURRENT_TIMESTAMP'); //Drop Columns $this->assertEquals($dialect->dropColumn('table', null, 'column1'), 'ALTER TABLE "table" DROP COLUMN "column1"'); $this->assertEquals($dialect->dropColumn('table', 'schema', 'column1'), 'ALTER TABLE "schema"."table" DROP COLUMN "column1"'); //Drop Tables $this->assertEquals($dialect->dropTable('table', null, true), 'DROP TABLE IF EXISTS "table"'); $this->assertEquals($dialect->dropTable('table', 'schema', true), 'DROP TABLE IF EXISTS "schema"."table"'); $this->assertEquals($dialect->dropTable('table', null, false), 'DROP TABLE "table"'); $this->assertEquals($dialect->dropTable('table', 'schema', false), 'DROP TABLE "schema"."table"'); $indexes = $this->getIndexes(); //Add Index $this->assertEquals($dialect->addIndex('table', null, $indexes['index1']), 'CREATE INDEX "index1" ON "table" ("column1")'); $this->assertEquals($dialect->addIndex('table', 'schema', $indexes['index1']), 'CREATE INDEX "index1" ON "schema"."table" ("column1")'); $this->assertEquals($dialect->addIndex('table', null, $indexes['index2']), 'CREATE INDEX "index2" ON "table" ("column1", "column2")'); $this->assertEquals($dialect->addIndex('table', 'schema', $indexes['index2']), 'CREATE INDEX "index2" ON "schema"."table" ("column1", "column2")'); $this->assertEquals($dialect->addIndex('table', null, $indexes['PRIMARY']), 'ALTER TABLE "table" ADD CONSTRAINT "PRIMARY" PRIMARY KEY ("column3")'); $this->assertEquals($dialect->addIndex('table', 'schema', $indexes['PRIMARY']), 'ALTER TABLE "schema"."table" ADD CONSTRAINT "PRIMARY" PRIMARY KEY ("column3")'); $this->assertEquals($dialect->addIndex('table', null, $indexes['index4']), 'CREATE UNIQUE INDEX "index4" ON "table" ("column4")'); $this->assertEquals($dialect->addIndex('table', 'schema', $indexes['index4']), 'CREATE UNIQUE INDEX "index4" ON "schema"."table" ("column4")'); //Drop Index $this->assertEquals($dialect->dropIndex('table', null, 'index1'), 'DROP INDEX "index1"'); $this->assertEquals($dialect->dropIndex('table', 'schema', 'index1'), 'DROP INDEX "index1"'); //Add Primary $this->assertEquals($dialect->addPrimaryKey('table', null, $indexes['PRIMARY']), 'ALTER TABLE "table" ADD CONSTRAINT "PRIMARY" PRIMARY KEY ("column3")'); $this->assertEquals($dialect->addPrimaryKey('table', 'schema', $indexes['PRIMARY']), 'ALTER TABLE "schema"."table" ADD CONSTRAINT "PRIMARY" PRIMARY KEY ("column3")'); //Drop Primary Key $this->assertEquals($dialect->dropPrimaryKey('table', null), 'ALTER TABLE "table" DROP CONSTRAINT "PRIMARY"'); $this->assertEquals($dialect->dropPrimaryKey('table', 'schema'), 'ALTER TABLE "schema"."table" DROP CONSTRAINT "PRIMARY"'); $references = $this->getReferences(); //Add Foreign Key $this->assertEquals($dialect->addForeignKey('table', null, $references['fk1']), 'ALTER TABLE "table" ADD CONSTRAINT "fk1" FOREIGN KEY ("column1") REFERENCES "ref_table" ("column2")'); $this->assertEquals($dialect->addForeignKey('table', 'schema', $references['fk1']), 'ALTER TABLE "schema"."table" ADD CONSTRAINT "fk1" FOREIGN KEY ("column1") REFERENCES "ref_table" ("column2")'); $this->assertEquals($dialect->addForeignKey('table', null, $references['fk2']), 'ALTER TABLE "table" ADD CONSTRAINT "fk2" FOREIGN KEY ("column3", "column4") REFERENCES "ref_table" ("column5", "column6")'); $this->assertEquals($dialect->addForeignKey('table', 'schema', $references['fk2']), 'ALTER TABLE "schema"."table" ADD CONSTRAINT "fk2" FOREIGN KEY ("column3", "column4") REFERENCES "ref_table" ("column5", "column6")'); $this->assertEquals($dialect->addForeignKey('table', null, $references['fk3']), 'ALTER TABLE "table" ADD CONSTRAINT "fk3" FOREIGN KEY ("column1") REFERENCES "ref_table" ("column2") ON DELETE CASCADE'); $this->assertEquals($dialect->addForeignKey('table', 'schema', $references['fk3']), 'ALTER TABLE "schema"."table" ADD CONSTRAINT "fk3" FOREIGN KEY ("column1") REFERENCES "ref_table" ("column2") ON DELETE CASCADE'); $this->assertEquals($dialect->addForeignKey('table', null, $references['fk4']), 'ALTER TABLE "table" ADD CONSTRAINT "fk4" FOREIGN KEY ("column1") REFERENCES "ref_table" ("column2") ON UPDATE SET NULL'); $this->assertEquals($dialect->addForeignKey('table', 'schema', $references['fk4']), 'ALTER TABLE "schema"."table" ADD CONSTRAINT "fk4" FOREIGN KEY ("column1") REFERENCES "ref_table" ("column2") ON UPDATE SET NULL'); $this->assertEquals($dialect->addForeignKey('table', null, $references['fk5']), 'ALTER TABLE "table" ADD CONSTRAINT "fk5" FOREIGN KEY ("column1") REFERENCES "ref_table" ("column2") ON DELETE CASCADE ON UPDATE NO ACTION'); $this->assertEquals($dialect->addForeignKey('table', 'schema', $references['fk5']), 'ALTER TABLE "schema"."table" ADD CONSTRAINT "fk5" FOREIGN KEY ("column1") REFERENCES "ref_table" ("column2") ON DELETE CASCADE ON UPDATE NO ACTION'); $this->assertEquals($dialect->dropForeignKey('table', null, 'fk1'), 'ALTER TABLE "table" DROP CONSTRAINT "fk1"'); $this->assertEquals($dialect->dropForeignKey('table', 'schema', 'fk1'), 'ALTER TABLE "schema"."table" DROP CONSTRAINT "fk1"'); //Create tables $definition = array('columns' => array($columns['column1'], $columns['column2'])); $expected = "CREATE TABLE \"table\" (\n"; $expected .= "\t\"column1\" CHARACTER VARYING(10),\n"; $expected .= "\t\"column2\" INT\n"; $expected .= ");"; $this->assertEquals($dialect->createTable('table', null, $definition), $expected); $definition = array('columns' => array($columns['column2'], $columns['column3'], $columns['column1']), 'indexes' => array($indexes['PRIMARY'])); $expected = "CREATE TABLE \"table\" (\n"; $expected .= "\t\"column2\" INT,\n"; $expected .= "\t\"column3\" NUMERIC(10,2) NOT NULL,\n"; $expected .= "\t\"column1\" CHARACTER VARYING(10),\n"; $expected .= "\tCONSTRAINT \"PRIMARY\" PRIMARY KEY (\"column3\")\n"; $expected .= ");"; $this->assertEquals($dialect->createTable('table', null, $definition), $expected); $definition['references'] = array($references['fk3']); $expected = "CREATE TABLE \"table\" (\n"; $expected .= "\t\"column2\" INT,\n"; $expected .= "\t\"column3\" NUMERIC(10,2) NOT NULL,\n"; $expected .= "\t\"column1\" CHARACTER VARYING(10),\n"; $expected .= "\tCONSTRAINT \"PRIMARY\" PRIMARY KEY (\"column3\"),\n"; $expected .= "\tCONSTRAINT \"fk3\" FOREIGN KEY (\"column1\") REFERENCES \"ref_table\" (\"column2\") ON DELETE CASCADE\n"; $expected .= ");"; $this->assertEquals($dialect->createTable('table', null, $definition), $expected); $definition = array('columns' => array($columns['column9'], $columns['column10'])); $expected = "CREATE TABLE \"table\" (\n"; $expected .= "\t\"column9\" CHARACTER VARYING(10) DEFAULT \"column9\",\n"; $expected .= "\t\"column10\" INT DEFAULT \"10\"\n"; $expected .= ");"; $this->assertEquals($dialect->createTable('table', null, $definition), $expected); $definition = array('columns' => array($columns['column11'], $columns['column12'], $columns['column13'])); $expected = "CREATE TABLE \"table\" (\n"; $expected .= "\t\"column11\" BIGINT,\n"; $expected .= "\t\"column12\" ENUM(\"A\", \"B\", \"C\") DEFAULT \"A\" NOT NULL,\n"; $expected .= "\t\"column13\" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL\n"; $expected .= ");"; $this->assertEquals($dialect->createTable('table', null, $definition), $expected); // issue 11359 $this->assertEquals($dialect->describeColumns('table', 'database.name.with.dots'), "SELECT DISTINCT c.column_name AS Field, c.data_type AS Type, c.character_maximum_length AS Size, c.numeric_precision AS NumericSize, c.numeric_scale AS NumericScale, c.is_nullable AS Null, CASE WHEN pkc.column_name NOTNULL THEN 'PRI' ELSE '' END AS Key, CASE WHEN c.data_type LIKE '%int%' AND c.column_default LIKE '%nextval%' THEN 'auto_increment' ELSE '' END AS Extra, c.ordinal_position AS Position, c.column_default FROM information_schema.columns c LEFT JOIN ( SELECT kcu.column_name, kcu.table_name, kcu.table_schema FROM information_schema.table_constraints tc INNER JOIN information_schema.key_column_usage kcu on (kcu.constraint_name = tc.constraint_name and kcu.table_name=tc.table_name and kcu.table_schema=tc.table_schema) WHERE tc.constraint_type='PRIMARY KEY') pkc ON (c.column_name=pkc.column_name AND c.table_schema = pkc.table_schema AND c.table_name=pkc.table_name) WHERE c.table_schema='database.name.with.dots' AND c.table_name='table' ORDER BY c.ordinal_position"); $this->assertEquals($dialect->describeColumns('table', 'database.name.with.dots'), "SELECT DISTINCT c.column_name AS Field, c.data_type AS Type, c.character_maximum_length AS Size, c.numeric_precision AS NumericSize, c.numeric_scale AS NumericScale, c.is_nullable AS Null, CASE WHEN pkc.column_name NOTNULL THEN 'PRI' ELSE '' END AS Key, CASE WHEN c.data_type LIKE '%int%' AND c.column_default LIKE '%nextval%' THEN 'auto_increment' ELSE '' END AS Extra, c.ordinal_position AS Position, c.column_default FROM information_schema.columns c LEFT JOIN ( SELECT kcu.column_name, kcu.table_name, kcu.table_schema FROM information_schema.table_constraints tc INNER JOIN information_schema.key_column_usage kcu on (kcu.constraint_name = tc.constraint_name and kcu.table_name=tc.table_name and kcu.table_schema=tc.table_schema) WHERE tc.constraint_type='PRIMARY KEY') pkc ON (c.column_name=pkc.column_name AND c.table_schema = pkc.table_schema AND c.table_name=pkc.table_name) WHERE c.table_schema='database.name.with.dots' AND c.table_name='table' ORDER BY c.ordinal_position"); $this->assertEquals($dialect->describeColumns('table', 'database.name.with.dots'), "SELECT DISTINCT c.column_name AS Field, c.data_type AS Type, c.character_maximum_length AS Size, c.numeric_precision AS NumericSize, c.numeric_scale AS NumericScale, c.is_nullable AS Null, CASE WHEN pkc.column_name NOTNULL THEN 'PRI' ELSE '' END AS Key, CASE WHEN c.data_type LIKE '%int%' AND c.column_default LIKE '%nextval%' THEN 'auto_increment' ELSE '' END AS Extra, c.ordinal_position AS Position, c.column_default FROM information_schema.columns c LEFT JOIN ( SELECT kcu.column_name, kcu.table_name, kcu.table_schema FROM information_schema.table_constraints tc INNER JOIN information_schema.key_column_usage kcu on (kcu.constraint_name = tc.constraint_name and kcu.table_name=tc.table_name and kcu.table_schema=tc.table_schema) WHERE tc.constraint_type='PRIMARY KEY') pkc ON (c.column_name=pkc.column_name AND c.table_schema = pkc.table_schema AND c.table_name=pkc.table_name) WHERE c.table_schema='database.name.with.dots' AND c.table_name='table' ORDER BY c.ordinal_position"); }