Schema.php 10.3 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
namespace yii\db\oci;

10
use yii\base\InvalidCallException;
11
use yii\db\Connection;
p0larbeer committed
12
use yii\db\TableSchema;
Qiang Xue committed
13
use yii\db\ColumnSchema;
p0larbeer committed
14

p0larbeer committed
15
/**
Qiang Xue committed
16 17 18 19 20 21
 * 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
22 23 24
 */
class Schema extends \yii\db\Schema
{
25 26 27 28 29 30 31 32 33 34 35 36 37
    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)';
38 39 40 41 42 43 44
    /**
     * @inheritdoc
     */
    public function init()
    {
        parent::init();
        if ($this->defaultSchema === null) {
Tóth Sándor committed
45
            $this->defaultSchema = strtoupper($this->db->username);
46 47
        }
    }
p0larbeer committed
48

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

Qiang Xue committed
57 58 59 60
    /**
     * @inheritdoc
     */
    public function quoteSimpleTableName($name)
miramir committed
61 62 63 64
    {
        return strpos($name, '"') !== false ? $name : '"' . $name . '"';
    }

65 66 67 68 69 70 71
    /**
     * @inheritdoc
     */
    public function createQueryBuilder()
    {
        return new QueryBuilder($this->db);
    }
p0larbeer committed
72

73 74 75 76 77 78 79
    /**
     * @inheritdoc
     */
    public function loadTableSchema($name)
    {
        $table = new TableSchema();
        $this->resolveTableNames($table, $name);
p0larbeer committed
80

81 82
        if ($this->findColumns($table)) {
            $this->findConstraints($table);
p0larbeer committed
83

84 85 86 87 88
            return $table;
        } else {
            return null;
        }
    }
p0larbeer committed
89

90 91 92 93
    /**
     * Resolves the table name and schema name (if any).
     *
     * @param TableSchema $table the table metadata object
94
     * @param string $name the table name
95 96 97 98 99 100 101 102 103 104 105
     */
    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
106

107 108
        $table->fullName = $table->schemaName !== $this->defaultSchema ? $table->schemaName . '.' . $table->name : $table->name;
    }
p0larbeer committed
109

110 111
    /**
     * Collects the table column metadata.
112 113
     * @param TableSchema $table the table schema
     * @return boolean whether the table exists
114 115 116 117 118 119 120
     */
    protected function findColumns($table)
    {
        $schemaName = $table->schemaName;
        $tableName = $table->name;

        $sql = <<<EOD
p0larbeer committed
121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141
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)
142
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
143 144
WHERE
    a.owner = '{$schemaName}'
145 146
    and (b.object_type = 'TABLE' or b.object_type = 'VIEW')
    and b.object_name = '{$tableName}'
p0larbeer committed
147 148
ORDER by a.column_id
EOD;
p0larbeer committed
149

150 151 152 153 154 155 156 157 158 159 160
        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;
161
                $table->sequenceName = $this->getTableSequenceName($table->name);
162 163 164 165 166
                $c->autoIncrement = true;
            }
        }
        return true;
    }
Qiang Xue committed
167

168 169 170 171 172 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
    /**
     * 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) {
Qiang Xue committed
199
            // get the last insert id from the master connection
200 201 202
            return $this->db->useMaster(function (Connection $db) use ($sequenceName) {
                return $db->createCommand("SELECT {$sequenceName}.CURRVAL FROM DUAL")->queryScalar();
            });
203 204 205 206 207
        } else {
            throw new InvalidCallException('DB Connection is not active.');
        }
    }

208 209 210 211 212 213 214
    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
215

216 217
        $this->extractColumnType($c, $column['DATA_TYPE']);
        $this->extractColumnSize($c, $column['DATA_TYPE']);
Qiang Xue committed
218

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

227 228
        return $c;
    }
p0larbeer committed
229

230 231 232 233
    protected function findConstraints($table)
    {
        $sql = <<<EOD
        SELECT D.constraint_type as CONSTRAINT_TYPE, C.COLUMN_NAME, C.position, D.r_constraint_name,
p0larbeer committed
234
                E.table_name as table_ref, f.column_name as column_ref,
235
                C.table_name
p0larbeer committed
236 237 238 239 240 241 242 243 244
        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;
245 246 247 248 249 250 251 252
        $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
253

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

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

279 280
        return $names;
    }
p0larbeer committed
281

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

291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307
        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
308

309 310 311
    /**
     * Extracts size, precision and scale information from column's DB type.
     * @param ColumnSchema $column
312
     * @param string $dbType the column's DB type
313 314 315 316 317 318 319 320 321 322 323
     */
    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
324
}