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