/**
* Adds a "create table" query for the table $tableName with
* definition $tableDefinition to the internal list of queries.
*
* Adds additional CREATE queries for sequences and triggers
* to implement autoincrement fields that not supported in Oracle directly.
*
* @param string $tableName
* @param ezcDbSchemaTable $tableDefinition
*/
protected function generateCreateTableSql($tableName, ezcDbSchemaTable $tableDefinition)
{
$sql = '';
$sql .= "CREATE TABLE \"{$tableName}\" (\n";
$this->context['skip_primary'] = false;
// dump fields
$fieldsSQL = array();
$autoincrementSQL = array();
$fieldCounter = 1;
foreach ($tableDefinition->fields as $fieldName => $fieldDefinition) {
$fieldsSQL[] = "\t" . $this->generateFieldSql($fieldName, $fieldDefinition);
if ($fieldDefinition->autoIncrement && !$this->context['skip_primary']) {
$sequenceName = ezcDbSchemaOracleHelper::generateSuffixCompositeIdentName($tableName, $fieldName, "seq");
$triggerName = ezcDbSchemaOracleHelper::generateSuffixCompositeIdentName($tableName, $fieldName, "trg");
$constraintName = ezcDbSchemaOracleHelper::generateSuffixedIdentName(array($tableName), "pkey");
$autoincrementSQL[] = "CREATE SEQUENCE \"{$sequenceName}\" start with 1 increment by 1 nomaxvalue";
$autoincrementSQL[] = "CREATE OR REPLACE TRIGGER \"{$triggerName}\" " . "before insert on \"{$tableName}\" for each row " . "begin " . "select \"{$sequenceName}\".nextval into :new.\"{$fieldName}\" from dual; " . "end;";
$autoincrementSQL[] = "ALTER TABLE \"{$tableName}\" ADD CONSTRAINT \"{$constraintName}\" PRIMARY KEY ( \"{$fieldName}\" )";
$this->context['skip_primary'] = true;
}
$fieldCounter++;
}
$sql .= join(",\n", $fieldsSQL);
$sql .= "\n)";
$this->queries[] = $sql;
if (count($autoincrementSQL) > 0) {
$this->queries = array_merge($this->queries, $autoincrementSQL);
}
// dump indexes
foreach ($tableDefinition->indexes as $indexName => $indexDefinition) {
$fieldsSQL[] = $this->generateAddIndexSql($tableName, $indexName, $indexDefinition);
}
}
/**
* 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;
}
