/**
* 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));
}