/**
* 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 = 'DESCRIBE ' . $this->adapter->quoteIdentifier($tableName, true);
$result = $this->fetchAll($sql, array(), ARRAY_A);
$desc = array();
$row_defaults = array('Length' => null, 'Scale' => null, 'Precision' => null, 'Unsigned' => null, 'Primary' => false, 'PrimaryPosition' => null, 'Identity' => false);
$i = 1;
$p = 1;
foreach ($result as $key => $row) {
$row = array_merge($row_defaults, $row);
if (preg_match('/unsigned/', $row['Type'])) {
$row['Unsigned'] = true;
}
if (preg_match('/^((?:var)?char)\\((\\d+)\\)/', $row['Type'], $matches)) {
$row['Type'] = $matches[1];
$row['Length'] = $matches[2];
}
if (preg_match('/^((?:var)?binary)\\((\\d+)\\)/', $row['Type'], $matches)) {
$row['Type'] = $matches[1];
$row['Length'] = $matches[2];
} elseif (preg_match('/^decimal\\((\\d+),(\\d+)\\)/', $row['Type'], $matches)) {
$row['Type'] = 'decimal';
$row['Precision'] = $matches[1];
$row['Scale'] = $matches[2];
} elseif (preg_match('/^float\\((\\d+),(\\d+)\\)/', $row['Type'], $matches)) {
$row['Type'] = 'float';
$row['Precision'] = $matches[1];
$row['Scale'] = $matches[2];
} elseif (preg_match('/^double\\((\\d+),(\\d+)\\)/', $row['Type'], $matches)) {
$row['Type'] = 'double';
$row['Precision'] = $matches[1];
$row['Scale'] = $matches[2];
} elseif (preg_match('/^((?:big|medium|small|tiny)?int)\\((\\d+)\\)/', $row['Type'], $matches)) {
$row['Type'] = $matches[1];
/**
* The optional argument of a MySQL int type is not precision
* or length; it is only a hint for display width.
*/
}
if (strtoupper($row['Key']) == 'PRI') {
$row['Primary'] = true;
$row['PrimaryPosition'] = $p;
if ($row['Extra'] == 'auto_increment') {
$row['Identity'] = true;
} else {
$row['Identity'] = false;
}
++$p;
}
$desc[$this->adapter->foldCase($row['Field'])] = array('SCHEMA_NAME' => null, 'TABLE_NAME' => $this->adapter->foldCase($tableName), 'COLUMN_NAME' => $this->adapter->foldCase($row['Field']), 'COLUMN_POSITION' => $i, 'DATA_TYPE' => $row['Type'], 'DEFAULT' => $row['Default'], 'NULLABLE' => (bool) ($row['Null'] == 'YES'), 'LENGTH' => $row['Length'], 'SCALE' => $row['Scale'], 'GENERIC_TYPE' => $this->mapNativeTypeToGenericType($row['Type'], $row['Length']), 'PRECISION' => $row['Precision'], 'UNSIGNED' => $row['Unsigned'], 'PRIMARY' => $row['Primary'], 'PRIMARY_POSITION' => $row['PrimaryPosition'], 'IDENTITY' => $row['Identity']);
++$i;
}
return $desc;
}
/**
* 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;
}
