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