/** * Method to test showTableColumns(). * * @return void * * @covers Windwalker\Query\Postgresql\PostgresqlQueryBuilder::showTableColumns */ public function testShowTableColumns() { $expected = "SHOW COLUMNS FROM {$this->qn}foo{$this->qn}"; $actual = PostgresqlQueryBuilder::showTableColumns('foo'); $this->assertEquals(\SqlFormatter::compress($expected), \SqlFormatter::compress($actual)); $expected = "SHOW FULL COLUMNS FROM {$this->qn}foo{$this->qn} WHERE a = b"; $actual = PostgresqlQueryBuilder::showTableColumns('foo', true, 'a = b'); $this->assertEquals(\SqlFormatter::compress($expected), \SqlFormatter::compress($actual)); }
/** * getColumnDetails * * @param bool $full * * @return mixed */ public function getColumnDetails($full = true) { $query = PostgresqlQueryBuilder::showTableColumns($this->db->replacePrefix($this->table), $full); $fields = $this->db->setQuery($query)->loadAll(); $result = array(); foreach ($fields as $field) { // Do some dirty translation to MySQL output. $result[$field->column_name] = (object) array('column_name' => $field->column_name, 'type' => $field->column_type, 'null' => $field->Null, 'Default' => $field->Default, 'Field' => $field->column_name, 'Type' => $field->column_type, 'Null' => $field->Null, 'Extra' => null, 'Privileges' => null, 'Comment' => $field->Comment); } $keys = $this->getIndexes(); foreach ($result as $field) { if (preg_match("/^NULL::*/", $field->Default)) { $field->Default = null; } if (strpos($field->Type, 'character varying') !== false) { $field->Type = str_replace('character varying', 'varchar', $field->Type); } if (strpos($field->Default, 'nextval') !== false) { $field->Extra = 'auto_increment'; } // Find key $index = null; foreach ($keys as $key) { if ($key->column_name == $field->column_name) { $index = $key; break; } } if ($index) { if ($index->is_primary) { $field->Key = 'PRI'; } elseif ($index->is_unique) { $field->Key = 'UNI'; } else { $field->Key = 'MUL'; } } } return $result; }
/** * Method to test showTableColumns(). * * @return void * * @covers Windwalker\Query\Postgresql\PostgresqlQueryBuilder::showTableColumns */ public function testShowTableColumns() { $expected = <<<SQL SELECT attr.attname AS "column_name", \tpg_catalog.format_type(attr.atttypid, attr.atttypmod) AS "column_type", \tCASE WHEN attr.attnotnull IS TRUE THEN 'NO' ELSE 'YES' END AS "Null", \tattrdef.adsrc AS "Default", \tpg_catalog.col_description(attr.attrelid, attr.attnum) AS "Comment" FROM pg_catalog.pg_attribute AS attr \tLEFT JOIN pg_catalog.pg_class AS class ON class.oid = attr.attrelid \tLEFT JOIN pg_catalog.pg_type AS typ ON typ.oid = attr.atttypid \tLEFT JOIN pg_catalog.pg_attrdef AS attrdef ON attr.attrelid = attrdef.adrelid AND attr.attnum = attrdef.adnum WHERE attr.attrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname='foo' \tAND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE \tnspname = 'public')) AND attr.attnum > 0 AND NOT attr.attisdropped ORDER BY attr.attnum SQL; $actual = PostgresqlQueryBuilder::showTableColumns('foo'); $this->assertEquals(\SqlFormatter::format($expected, false), \SqlFormatter::format($actual, false)); }