/**
* Returns the column descriptions for a table.
*
* The return value is an associative array keyed by the column name,
* as returned by the RDBMS.
*
* The value of each array element is an associative array
* with the following keys:
*
* SCHEMA_NAME => string; name of database or schema
* TABLE_NAME => string;
* COLUMN_NAME => string; column name
* COLUMN_POSITION => number; ordinal position of column in table
* DATA_TYPE => string; SQL datatype name of column
* DEFAULT => string; default expression of column, null if none
* NULLABLE => boolean; true if column can have nulls
* LENGTH => number; length of CHAR/VARCHAR
* SCALE => number; scale of NUMERIC/DECIMAL
* PRECISION => number; precision of NUMERIC/DECIMAL
* UNSIGNED => boolean; unsigned property of an integer type
* PRIMARY => boolean; true if column is part of the primary key
* PRIMARY_POSITION => integer; position of column in primary key
* IDENTITY => integer; true if column is auto-generated with unique values
*
* @param string $tableName
* @return array
*/
public function describeTable($tableName)
{
$sql = "SELECT\n a.attnum,\n n.nspname,\n c.relname,\n a.attname AS colname,\n t.typname AS type,\n a.atttypmod,\n FORMAT_TYPE(a.atttypid, a.atttypmod) AS complete_type,\n d.adsrc AS default_value,\n a.attnotnull AS notnull,\n a.attlen AS length,\n co.contype,\n ARRAY_TO_STRING(co.conkey, ',') AS conkey\n FROM pg_attribute AS a\n JOIN pg_class AS c ON a.attrelid = c.oid\n JOIN pg_namespace AS n ON c.relnamespace = n.oid\n JOIN pg_type AS t ON a.atttypid = t.oid\n LEFT OUTER JOIN pg_constraint AS co ON (co.conrelid = c.oid\n AND a.attnum = ANY(co.conkey) AND co.contype = 'p')\n LEFT OUTER JOIN pg_attrdef AS d ON d.adrelid = c.oid AND d.adnum = a.attnum\n WHERE a.attnum > 0 AND c.relname = " . $this->adapter->quote($tableName) . ' ORDER BY a.attnum';
$result = $this->fetchAll($sql, array(), Adapter::ARRAY_N);
$attnum = 0;
$nspname = 1;
$relname = 2;
$colname = 3;
$type = 4;
$atttypemod = 5;
$complete_type = 6;
$default_value = 7;
$notnull = 8;
$length = 9;
$contype = 10;
$conkey = 11;
$desc = array();
foreach ($result as $key => $row) {
$defaultValue = $row[$default_value];
if ($row[$type] == 'varchar' || $row[$type] == 'bpchar') {
if (preg_match('/character(?: varying)?(?:\\((\\d+)\\))?/', $row[$complete_type], $matches)) {
if (isset($matches[1])) {
$row[$length] = $matches[1];
} else {
$row[$length] = null;
// unlimited
}
}
if (preg_match("/^'(.*?)'::(?:character varying|bpchar)\$/", $defaultValue, $matches)) {
$defaultValue = $matches[1];
}
}
if ($row[$type] === 'bool' && $row[$default_value]) {
$defaultValue = 'true' === $row[$default_value] ? 1 : 0;
}
if (false !== strpos($defaultValue, '(')) {
$defaultValue = null;
}
list($primary, $primaryPosition, $identity) = array(false, null, false);
if ($row[$contype] == 'p') {
$primary = true;
$primaryPosition = array_search($row[$attnum], explode(',', $row[$conkey])) + 1;
$identity = (bool) preg_match('/^nextval/', $row[$default_value]);
}
$desc[$this->adapter->foldCase($row[$colname])] = array('SCHEMA_NAME' => $this->adapter->foldCase($row[$nspname]), 'TABLE_NAME' => $this->adapter->foldCase($row[$relname]), 'COLUMN_NAME' => $this->adapter->foldCase($row[$colname]), 'COLUMN_POSITION' => $row[$attnum], 'DATA_TYPE' => $row[$type], 'GENERIC_TYPE' => $this->mapNativeTypeToGenericType($row[$type], $length), 'DEFAULT' => $defaultValue, 'NULLABLE' => (bool) ($row[$notnull] != 't'), 'LENGTH' => $row[$length], 'SCALE' => null, 'PRECISION' => null, 'UNSIGNED' => null, 'PRIMARY' => $primary, 'PRIMARY_POSITION' => $primaryPosition, 'IDENTITY' => $identity);
}
return $desc;
}
