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);
}
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");
}
