Schema.php 6.91 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
	/**
	 * @inheritdoc
	 */
	public function init()
	{
		parent::init();
		if ($this->defaultSchema === null) {
			$this->defaultSchema = $this->db->username;
		}
	}
p0larbeer committed
33

Qiang Xue committed
34 35 36 37 38 39 40
	/**
	 * @inheritdoc
	 */
	public function quoteSimpleTableName($name)
	{
		return '"' . $name . '"';
	}
p0larbeer committed
41

Qiang Xue committed
42 43 44 45 46 47 48
	/**
	 * @inheritdoc
	 */
	public function quoteSimpleColumnName($name)
	{
		return '"' . $name . '"';
	}
p0larbeer committed
49

Qiang Xue committed
50 51 52 53 54 55 56
	/**
	 * @inheritdoc
	 */
	public function createQueryBuilder()
	{
		return new QueryBuilder($this->db);
	}
p0larbeer committed
57

Qiang Xue committed
58 59 60 61 62 63 64
	/**
	 * @inheritdoc
	 */
	public function loadTableSchema($name)
	{
		$table = new TableSchema();
		$this->resolveTableNames($table, $name);
p0larbeer committed
65

Qiang Xue committed
66 67 68 69 70 71 72
		if ($this->findColumns($table)) {
			$this->findConstraints($table);
			return $table;
		} else {
			return null;
		}
	}
p0larbeer committed
73

Qiang Xue committed
74 75 76 77 78 79 80 81 82 83 84 85 86
	/**
	 * Resolves the table name and schema name (if any).
	 *
	 * @param TableSchema $table the table metadata object
	 * @param string $name the table name
	 */
	protected function resolveTableNames($table, $name)
	{
		$parts = explode('.', str_replace('"', '', $name));
		if (isset($parts[1])) {
			$table->schemaName = $parts[0];
			$table->name = $parts[1];
		} else {
87 88
			$table->schemaName = $this->defaultSchema;
			$table->name = $name;
Qiang Xue committed
89
		}
p0larbeer committed
90

91
		$table->fullName = $table->schemaName !== $this->defaultSchema ? $table->schemaName . '.' . $table->name : $table->name;
Qiang Xue committed
92
	}
p0larbeer committed
93

Qiang Xue committed
94 95 96 97 98 99 100 101 102
	/**
	 * Collects the table column metadata.
	 * @param TableSchema $table the table schema
	 * @return boolean whether the table exists
	 */
	protected function findColumns($table)
	{
		$schemaName = $table->schemaName;
		$tableName = $table->name;
p0larbeer committed
103

Qiang Xue committed
104
		$sql = <<<EOD
p0larbeer committed
105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132
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)
LEFT JOIN user_col_comments com ON (A.table_name = com.table_name AND A.column_name = com.column_name)
WHERE
    a.owner = '{$schemaName}'
	and (b.object_type = 'TABLE' or b.object_type = 'VIEW')
	and b.object_name = '{$tableName}'
ORDER by a.column_id
EOD;
p0larbeer committed
133

Qiang Xue committed
134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170
		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;
				$table->sequenceName = '';
				$c->autoIncrement = true;
			}
		}
		return true;
	}

	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'];

		$this->extractColumnType($c, $column['DATA_TYPE']);
		$this->extractColumnSize($c, $column['DATA_TYPE']);

		if (stripos($column['DATA_DEFAULT'], 'timestamp') !== false) {
			$c->defaultValue = null;
		} else {
			$c->defaultValue = $c->typecast($column['DATA_DEFAULT']);
		}

		return $c;
	}
p0larbeer committed
171

Qiang Xue committed
172 173 174
	protected function findConstraints($table)
	{
		$sql = <<<EOD
p0larbeer committed
175 176 177 178 179 180 181 182 183 184 185 186
		SELECT D.constraint_type as CONSTRAINT_TYPE, C.COLUMN_NAME, C.position, D.r_constraint_name,
                E.table_name as table_ref, f.column_name as column_ref,
            	C.table_name
        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;
Qiang Xue committed
187 188 189 190 191 192 193 194
		$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
195

Qiang Xue committed
196 197 198 199 200 201 202
	/**
	 * @inheritdoc
	 */
	protected function findTableNames($schema = '')
	{
		if ($schema === '') {
			$sql = <<<EOD
p0larbeer committed
203 204
SELECT table_name, '{$schema}' as table_schema FROM user_tables
EOD;
Qiang Xue committed
205 206 207
			$command = $this->db->createCommand($sql);
		} else {
			$sql = <<<EOD
p0larbeer committed
208 209 210
SELECT object_name as table_name, owner as table_schema FROM all_objects
WHERE object_type = 'TABLE' AND owner=:schema
EOD;
Qiang Xue committed
211 212 213
			$command = $this->db->createCommand($sql);
			$command->bindParam(':schema', $schema);
		}
p0larbeer committed
214

Qiang Xue committed
215
		$rows = $command->queryAll();
Paul Kofmann committed
216
		$names = [];
Qiang Xue committed
217 218 219 220 221
		foreach ($rows as $row) {
			$names[] = $row['TABLE_NAME'];
		}
		return $names;
	}
p0larbeer committed
222

Qiang Xue committed
223 224 225 226 227 228 229 230
	/**
	 * Extracts the data types for the given column
	 * @param ColumnSchema $column
	 * @param string $dbType DB type
	 */
	protected function extractColumnType($column, $dbType)
	{
		$column->dbType = $dbType;
p0larbeer committed
231

Qiang Xue committed
232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248
		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]) and (((int)$values[1]) > 0)) {
					$column->type = 'double';
				} else {
					$column->type = 'integer';
				}
			} else {
				$column->type = 'double';
			}
		} else {
			$column->type = 'string';
		}
	}
p0larbeer committed
249

Qiang Xue committed
250 251 252 253 254 255 256 257 258 259 260 261 262 263 264
	/**
	 * Extracts size, precision and scale information from column's DB type.
	 * @param ColumnSchema $column
	 * @param string $dbType the column's DB type
	 */
	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
265
}