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