Schema.php 10.2 KB
Newer Older
p0larbeer committed
1
<?php
Qiang Xue committed
2 3 4 5 6 7
/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */

p0larbeer committed
8 9 10
namespace yii\db\oci;

use yii\db\TableSchema;
Qiang Xue committed
11
use yii\db\ColumnSchema;
p0larbeer committed
12

p0larbeer committed
13
/**
Qiang Xue committed
14 15 16 17 18 19
 * Schema is the class for retrieving metadata from an Oracle database
 *
 * @todo mapping from physical types to abstract types
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @since 2.0
p0larbeer committed
20 21 22
 */
class Schema extends \yii\db\Schema
{
23 24 25 26 27 28 29 30 31 32 33 34 35
    const TYPE_PK = 'NUMBER(10) NOT NULL PRIMARY KEY';
    const TYPE_STRING = 'VARCHAR2(255)';
    const TYPE_TEXT = 'CLOB';
    const TYPE_INTEGER = 'NUMBER(10)';
    const TYPE_FLOAT = 'NUMBER';
    const TYPE_DECIMAL = 'NUMBER';
    const TYPE_DATETIME = 'TIMESTAMP';
    const TYPE_TIMESTAMP = 'TIMESTAMP';
    const TYPE_TIME = 'TIMESTAMP';
    const TYPE_DATE = 'DATE';
    const TYPE_BINARY = 'BLOB';
    const TYPE_BOOLEAN = 'NUMBER(1)';
    const TYPE_MONEY = 'NUMBER(19,4)';
36 37 38 39 40 41 42
    /**
     * @inheritdoc
     */
    public function init()
    {
        parent::init();
        if ($this->defaultSchema === null) {
Tóth Sándor committed
43
            $this->defaultSchema = strtoupper($this->db->username);
44 45
        }
    }
p0larbeer committed
46

47 48 49 50 51 52 53
    /**
     * @inheritdoc
     */
    public function releaseSavepoint($name)
    {
        // does nothing as Oracle does not support this
    }
54

55 56 57 58 59 60 61
    /**
     * @inheritdoc
     */
    public function quoteSimpleTableName($name)
    {
        return '"' . $name . '"';
    }
p0larbeer committed
62

63 64 65 66 67 68 69
    /**
     * @inheritdoc
     */
    public function quoteSimpleColumnName($name)
    {
        return '"' . $name . '"';
    }
p0larbeer committed
70

71 72 73 74 75 76 77
    /**
     * @inheritdoc
     */
    public function createQueryBuilder()
    {
        return new QueryBuilder($this->db);
    }
p0larbeer committed
78

79 80 81 82 83 84 85
    /**
     * @inheritdoc
     */
    public function loadTableSchema($name)
    {
        $table = new TableSchema();
        $this->resolveTableNames($table, $name);
p0larbeer committed
86

87 88
        if ($this->findColumns($table)) {
            $this->findConstraints($table);
p0larbeer committed
89

90 91 92 93 94
            return $table;
        } else {
            return null;
        }
    }
p0larbeer committed
95

96 97 98 99
    /**
     * Resolves the table name and schema name (if any).
     *
     * @param TableSchema $table the table metadata object
100
     * @param string $name the table name
101 102 103 104 105 106 107 108 109 110 111
     */
    protected function resolveTableNames($table, $name)
    {
        $parts = explode('.', str_replace('"', '', $name));
        if (isset($parts[1])) {
            $table->schemaName = $parts[0];
            $table->name = $parts[1];
        } else {
            $table->schemaName = $this->defaultSchema;
            $table->name = $name;
        }
p0larbeer committed
112

113 114
        $table->fullName = $table->schemaName !== $this->defaultSchema ? $table->schemaName . '.' . $table->name : $table->name;
    }
p0larbeer committed
115

116 117
    /**
     * Collects the table column metadata.
118 119
     * @param TableSchema $table the table schema
     * @return boolean whether the table exists
120 121 122 123 124 125 126
     */
    protected function findColumns($table)
    {
        $schemaName = $table->schemaName;
        $tableName = $table->name;

        $sql = <<<EOD
p0larbeer committed
127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147
SELECT a.column_name, a.data_type ||
    case
        when data_precision is not null
            then '(' || a.data_precision ||
                    case when a.data_scale > 0 then ',' || a.data_scale else '' end
                || ')'
        when data_type = 'DATE' then ''
        when data_type = 'NUMBER' then ''
        else '(' || to_char(a.data_length) || ')'
    end as data_type,
    a.nullable, a.data_default,
    (   SELECT D.constraint_type
        FROM ALL_CONS_COLUMNS C
        inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name
        WHERE C.OWNER = B.OWNER
           and C.table_name = B.object_name
           and C.column_name = A.column_name
           and D.constraint_type = 'P') as Key,
    com.comments as column_comment
FROM ALL_TAB_COLUMNS A
inner join ALL_OBJECTS B ON b.owner = a.owner and ltrim(B.OBJECT_NAME) = ltrim(A.TABLE_NAME)
148
LEFT JOIN all_col_comments com ON (A.owner = com.owner AND A.table_name = com.table_name AND A.column_name = com.column_name)
p0larbeer committed
149 150
WHERE
    a.owner = '{$schemaName}'
151 152
    and (b.object_type = 'TABLE' or b.object_type = 'VIEW')
    and b.object_name = '{$tableName}'
p0larbeer committed
153 154
ORDER by a.column_id
EOD;
p0larbeer committed
155

156 157 158 159 160 161 162 163 164 165 166
        try {
            $columns = $this->db->createCommand($sql)->queryAll();
        } catch (\Exception $e) {
            return false;
        }

        foreach ($columns as $column) {
            $c = $this->createColumn($column);
            $table->columns[$c->name] = $c;
            if ($c->isPrimaryKey) {
                $table->primaryKey[] = $c->name;
167
                $table->sequenceName = $this->getTableSequenceName($table->name);
168 169 170 171 172
                $c->autoIncrement = true;
            }
        }
        return true;
    }
Qiang Xue committed
173

174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210
    /**
     * Sequence name of table
     *
     * @param $tablename
     * @internal param \yii\db\TableSchema $table ->name the table schema
     * @return string whether the sequence exists
     */

    protected function getTableSequenceName($tablename){

        $seq_name_sql="select ud.referenced_name as sequence_name
                        from   user_dependencies ud
                               join user_triggers ut on (ut.trigger_name = ud.name)
                        where ut.table_name='{$tablename}'
                              and ud.type='TRIGGER'
                              and ud.referenced_type='SEQUENCE'";
        return $this->db->createCommand($seq_name_sql)->queryScalar();
    }

    /*
     * @Overrides method in class 'Schema'
     * @see http://www.php.net/manual/en/function.PDO-lastInsertId.php -> Oracle does not support this
     *
     * Returns the ID of the last inserted row or sequence value.
     * @param string $sequenceName name of the sequence object (required by some DBMS)
     * @return string the row ID of the last row inserted, or the last value retrieved from the sequence object
     * @throws InvalidCallException if the DB connection is not active
     */
    public function getLastInsertID($sequenceName = '')
    {
        if ($this->db->isActive) {
            return $this->db->createCommand("SELECT {$sequenceName}.CURRVAL FROM DUAL")->queryScalar();
        } else {
            throw new InvalidCallException('DB Connection is not active.');
        }
    }

211 212 213 214 215 216 217
    protected function createColumn($column)
    {
        $c = new ColumnSchema();
        $c->name = $column['COLUMN_NAME'];
        $c->allowNull = $column['NULLABLE'] === 'Y';
        $c->isPrimaryKey = strpos($column['KEY'], 'P') !== false;
        $c->comment = $column['COLUMN_COMMENT'] === null ? '' : $column['COLUMN_COMMENT'];
Qiang Xue committed
218

219 220
        $this->extractColumnType($c, $column['DATA_TYPE']);
        $this->extractColumnSize($c, $column['DATA_TYPE']);
Qiang Xue committed
221

Tóth Sándor committed
222
        if (!$c->isPrimaryKey) {
223 224 225
            if (stripos($column['DATA_DEFAULT'], 'timestamp') !== false) {
                $c->defaultValue = null;
            } else {
226
                $c->defaultValue = $c->phpTypecast($column['DATA_DEFAULT']);
227
            }
228
        }
Qiang Xue committed
229

230 231
        return $c;
    }
p0larbeer committed
232

233 234 235 236
    protected function findConstraints($table)
    {
        $sql = <<<EOD
        SELECT D.constraint_type as CONSTRAINT_TYPE, C.COLUMN_NAME, C.position, D.r_constraint_name,
p0larbeer committed
237
                E.table_name as table_ref, f.column_name as column_ref,
238
                C.table_name
p0larbeer committed
239 240 241 242 243 244 245 246 247
        FROM ALL_CONS_COLUMNS C
        inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name
        left join ALL_constraints E on E.OWNER = D.r_OWNER and E.constraint_name = D.r_constraint_name
        left join ALL_cons_columns F on F.OWNER = E.OWNER and F.constraint_name = E.constraint_name and F.position = c.position
        WHERE C.OWNER = '{$table->schemaName}'
           and C.table_name = '{$table->name}'
           and D.constraint_type <> 'P'
        order by d.constraint_name, c.position
EOD;
248 249 250 251 252 253 254 255
        $command = $this->db->createCommand($sql);
        foreach ($command->queryAll() as $row) {
            if ($row['CONSTRAINT_TYPE'] === 'R') {
                $name = $row["COLUMN_NAME"];
                $table->foreignKeys[$name] = [$row["TABLE_REF"], $row["COLUMN_REF"]];
            }
        }
    }
p0larbeer committed
256

257 258 259 260 261 262 263
    /**
     * @inheritdoc
     */
    protected function findTableNames($schema = '')
    {
        if ($schema === '') {
            $sql = <<<EOD
p0larbeer committed
264 265
SELECT table_name, '{$schema}' as table_schema FROM user_tables
EOD;
266 267 268
            $command = $this->db->createCommand($sql);
        } else {
            $sql = <<<EOD
p0larbeer committed
269 270 271
SELECT object_name as table_name, owner as table_schema FROM all_objects
WHERE object_type = 'TABLE' AND owner=:schema
EOD;
272 273 274 275 276 277 278 279 280
            $command = $this->db->createCommand($sql);
            $command->bindParam(':schema', $schema);
        }

        $rows = $command->queryAll();
        $names = [];
        foreach ($rows as $row) {
            $names[] = $row['TABLE_NAME'];
        }
p0larbeer committed
281

282 283
        return $names;
    }
p0larbeer committed
284

285 286 287
    /**
     * Extracts the data types for the given column
     * @param ColumnSchema $column
288
     * @param string $dbType DB type
289 290 291 292
     */
    protected function extractColumnType($column, $dbType)
    {
        $column->dbType = $dbType;
p0larbeer committed
293

294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310
        if (strpos($dbType, 'FLOAT') !== false) {
            $column->type = 'double';
        } elseif (strpos($dbType, 'NUMBER') !== false || strpos($dbType, 'INTEGER') !== false) {
            if (strpos($dbType, '(') && preg_match('/\((.*)\)/', $dbType, $matches)) {
                $values = explode(',', $matches[1]);
                if (isset($values[1]) && (((int) $values[1]) > 0)) {
                    $column->type = 'double';
                } else {
                    $column->type = 'integer';
                }
            } else {
                $column->type = 'double';
            }
        } else {
            $column->type = 'string';
        }
    }
p0larbeer committed
311

312 313 314
    /**
     * Extracts size, precision and scale information from column's DB type.
     * @param ColumnSchema $column
315
     * @param string $dbType the column's DB type
316 317 318 319 320 321 322 323 324 325 326
     */
    protected function extractColumnSize($column, $dbType)
    {
        if (strpos($dbType, '(') && preg_match('/\((.*)\)/', $dbType, $matches)) {
            $values = explode(',', $matches[1]);
            $column->size = $column->precision = (int) $values[0];
            if (isset($values[1])) {
                $column->scale = (int) $values[1];
            }
        }
    }
p0larbeer committed
327
}