/**
* Fetch fields definition for the table $tableName
*
* This method loops over all the fields in the table $tableName and
* returns an array with the field specification. The key in the returned
* array is the name of the field.
*
* @param string $tableName
* @return array(string=>ezcDbSchemaField)
*/
protected function fetchTableFields($tableName)
{
$fields = array();
// fetching fields info from PostgreSQL
$resultArray = $this->db->query("SELECT a.attnum, a.attname AS field, t.typname AS type,\n format_type(a.atttypid, a.atttypmod) AS fulltype,\n ( SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d \n WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef\n ) AS default,\n a.attlen AS length, a.atttypmod AS lengthvar, a.attnotnull AS notnull\n FROM pg_class c, pg_attribute a, pg_type t \n WHERE c.relname = '{$tableName}' AND \n a.attnum > 0 AND \n a.attrelid = c.oid AND\n a.atttypid = t.oid \n ORDER BY a.attnum");
$resultArray->setFetchMode(PDO::FETCH_ASSOC);
foreach ($resultArray as $row) {
$fieldLength = false;
$fieldType = self::convertToGenericType($row['fulltype'], $fieldLength, $fieldPrecision);
if (!$fieldLength) {
$fieldLength = false;
}
$fieldNotNull = $row['notnull'];
$fieldDefault = null;
$fieldAutoIncrement = false;
if ($row['default'] != '') {
// detecting autoincrement field by string like "nextval('public.TableName_FieldName_seq'::text)"
// in "default"
if (strstr($row['default'], $row['field'] . '_seq') != false) {
$fieldAutoIncrement = true;
} else {
// try to cut off single quotes and "::Typename" that postgreSQL
// adds to default value string for some types.
// we should do it to get clean value for default clause.
if (preg_match("@'(.*)('::[a-z ]*)\$@", $row['default'], $matches) == 1) {
$fieldDefault = $matches[1];
} else {
$fieldDefault = $row['default'];
if ($fieldType == 'boolean') {
$fieldDefault == 'true' ? $fieldDefault = 'true' : ($fieldDefault = 'false');
}
}
}
}
// FIXME: unsigned needs to be implemented
$fieldUnsigned = false;
$fields[$row['field']] = ezcDbSchema::createNewField($fieldType, $fieldLength, $fieldNotNull, $fieldDefault, $fieldAutoIncrement, $fieldUnsigned);
}
return $fields;
}
/**
* Fetch fields definition for the table $tableName
*
* This method loops over all the fields in the table $tableName and
* returns an array with the field specification. The key in the returned
* array is the name of the field.
*
* @param string $tableName
* @return array(string=>ezcDbSchemaField)
*/
protected function fetchTableFields($tableName)
{
$fields = array();
$resultArray = $this->db->query("PRAGMA TABLE_INFO( '{$tableName}' )");
$resultArray->setFetchMode(PDO::FETCH_NUM);
foreach ($resultArray as $row) {
$fieldLength = false;
$fieldPrecision = null;
$fieldType = self::convertToGenericType($row[2], $fieldLength, $fieldPrecision);
$fieldNotNull = false;
if ($row[3] == '99' || $row[3] == '1') {
$fieldNotNull = true;
}
$fieldDefault = null;
if ($row[4] != '') {
$fieldDefault = $row[4];
if ($fieldType == 'text') {
// strip enclosing single quotes if needed
if ($fieldDefault[0] == "'" && substr($fieldDefault, -1) == "'") {
$fieldDefault = substr($fieldDefault, 1, -1);
}
}
if ($fieldType == 'integer') {
$fieldDefault = (int) $fieldDefault;
}
if ($fieldType == 'boolean') {
$fieldDefault = $fieldDefault ? 'true' : 'false';
}
}
$fieldAutoIncrement = false;
if ($row[5] == '1') {
$fieldAutoIncrement = true;
$fieldDefault = null;
}
// FIXME: unsigned needs to be implemented
$fieldUnsigned = false;
$fields[$row[1]] = ezcDbSchema::createNewField($fieldType, $fieldLength, $fieldNotNull, $fieldDefault, $fieldAutoIncrement, $fieldUnsigned);
}
return $fields;
}
/**
* Extracts information about a table field from the XML element $field
*
* @param SimpleXMLElement $field
*
* @return ezcDbSchemaField or an inherited class
*/
private function parseField(SimpleXMLElement $field)
{
return ezcDbSchema::createNewField((string) $field->type, isset($field->length) ? (string) $field->length : false, isset($field->notnull) ? (string) $field->notnull == 'true' || (string) $field->notnull == '1' ? true : false : false, isset($field->default) ? (string) $field->default : null, isset($field->autoincrement) ? (string) $field->autoincrement == 'true' || (string) $field->autoincrement == '1' ? true : false : false, isset($field->unsigned) ? (string) $field->unsigned == 'true' || (string) $field->unsigned == '1' ? true : false : false);
}
/**
* Fetch fields definition for the table $tableName
*
* This method loops over all the fields in the table $tableName and
* returns an array with the field specification. The key in the returned
* array is the name of the field.
*
* @param string $tableName
* @return array(string=>ezcDbSchemaField)
*/
protected function fetchTableFields($tableName)
{
$fields = array();
$resultArray = $this->db->query("DESCRIBE `{$tableName}`");
$resultArray->setFetchMode(PDO::FETCH_ASSOC);
foreach ($resultArray as $row) {
$fieldLength = false;
// bool and boolean is synonyms for TINYINT(1) in MySQL
if ($row['type'] == 'tinyint(1)') {
$fieldType = 'boolean';
} else {
$fieldType = self::convertToGenericType($row['type'], $fieldLength, $fieldPrecision);
if (!$fieldLength) {
$fieldLength = false;
}
}
$fieldNotNull = false;
if (strlen($row['null']) == 0 || $row['null'][0] != 'Y' || $fieldType == 'timestamp') {
$fieldNotNull = true;
}
$fieldDefault = null;
if (strlen($row['default']) != 0) {
if ($fieldType == 'boolean') {
$fieldDefault = $row['default'] == '0' ? 'false' : 'true';
} else {
if ($fieldType != 'timestamp') {
$fieldDefault = $row['default'];
}
}
}
if ($fieldType == 'integer' && $row['default'] !== null) {
$fieldDefault = (int) $fieldDefault;
}
$fieldAutoIncrement = false;
if (strstr($row['extra'], 'auto_increment') !== false) {
$fieldAutoIncrement = true;
}
// FIXME: unsigned needs to be implemented
$fieldUnsigned = false;
$fields[$row['field']] = ezcDbSchema::createNewField($fieldType, $fieldLength, $fieldNotNull, $fieldDefault, $fieldAutoIncrement, $fieldUnsigned);
}
return $fields;
}
/**
* Fetch fields definition for the table $tableName
*
* This method loops over all the fields in the table $tableName and
* returns an array with the field specification. The key in the returned
* array is the name of the field.
*
* @param string $tableName
* @return array(string=>ezcDbSchemaField)
*/
protected function fetchTableFields($tableName)
{
$fields = array();
// will detect autoincrement field by presence of sequence tableName_fieldPos_seq
$sequencesQuery = $this->db->query("SELECT * FROM user_sequences");
$sequencesQuery->setFetchMode(PDO::FETCH_ASSOC);
$sequences = array();
foreach ($sequencesQuery as $seq) {
$sequences[] = $seq['sequence_name'];
}
// fetching fields info from Oracle
$resultArray = $this->db->query("SELECT a.column_name AS field, " . " a.column_id AS field_pos, " . " DECODE (a.nullable, 'N', 1, 'Y', 0) AS notnull, " . " a.data_type AS type, " . " a.data_length AS length, " . " a.data_precision AS precision, " . " a.data_scale AS scale, " . " a.data_default AS default_val " . "FROM user_tab_columns a " . "WHERE a.table_name = '{$tableName}' " . "ORDER BY a.column_id");
$resultArray->setFetchMode(PDO::FETCH_ASSOC);
foreach ($resultArray as $row) {
$fieldLength = $row['length'];
$fieldPrecision = null;
$fieldType = self::convertToGenericType($row['type'], $fieldLength, $fieldPrecision);
if (in_array($fieldType, array('clob', 'blob', 'date', 'float', 'timestamp'))) {
$fieldLength = false;
} else {
if ($fieldType == 'integer') {
if ($row['precision'] != '') {
$fieldType = 'decimal';
$fieldLength = $row['precision'];
} else {
if ($fieldLength == 22) {
$fieldLength = false;
}
}
}
}
$fieldNotNull = $row['notnull'];
$fieldDefault = null;
if ($row['default_val'] != '') {
$row['default_val'] = rtrim($row['default_val']);
if ($fieldType == 'boolean') {
$row['default_val'] == '1' ? $fieldDefault = 'true' : ($fieldDefault = 'false');
} else {
if ($fieldType == 'text') {
$fieldDefault = substr($row['default_val'], 1, -1);
// avoid quotes for text
} else {
$fieldDefault = $row['default_val'];
// have a number value
}
}
}
$fieldAutoIncrement = false;
// new sequence naming included
if (in_array(ezcDbSchemaOracleHelper::generateSuffixCompositeIdentName($tableName, $row['field_pos'], 'seq'), $sequences) || in_array(ezcDbSchemaOracleHelper::generateSuffixCompositeIdentName($tableName, $row['field'], 'seq'), $sequences)) {
$fieldAutoIncrement = true;
}
// FIXME: unsigned needs to be implemented
$fieldUnsigned = false;
$fields[$row['field']] = ezcDbSchema::createNewField($fieldType, $fieldLength, $fieldNotNull, $fieldDefault, $fieldAutoIncrement, $fieldUnsigned);
}
return $fields;
}
