Schema.php 7.07 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
 * Schema is the class for retrieving metadata from an Oracle database
 *
 * @todo mapping from physical types to abstract types
 *
Qiang Xue committed
18 19
 * @property string $defaultSchema Default schema.
 *
Qiang Xue committed
20 21
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @since 2.0
p0larbeer committed
22 23 24
 */
class Schema extends \yii\db\Schema
{
Qiang Xue committed
25
	private $_defaultSchema;
p0larbeer committed
26

Qiang Xue committed
27 28 29 30 31 32 33
	/**
	 * @inheritdoc
	 */
	public function quoteSimpleTableName($name)
	{
		return '"' . $name . '"';
	}
p0larbeer committed
34

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

Qiang Xue committed
43 44 45 46 47 48 49
	/**
	 * @inheritdoc
	 */
	public function createQueryBuilder()
	{
		return new QueryBuilder($this->db);
	}
p0larbeer committed
50

Qiang Xue committed
51 52 53 54 55 56 57
	/**
	 * @inheritdoc
	 */
	public function loadTableSchema($name)
	{
		$table = new TableSchema();
		$this->resolveTableNames($table, $name);
p0larbeer committed
58

Qiang Xue committed
59 60 61 62 63 64 65
		if ($this->findColumns($table)) {
			$this->findConstraints($table);
			return $table;
		} else {
			return null;
		}
	}
p0larbeer committed
66

Qiang Xue committed
67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83
	/**
	 * 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 {
			$table->schemaName = $this->getDefaultSchema();
			$table->name = $parts[0];
		}
	}
p0larbeer committed
84

Qiang Xue committed
85 86 87 88 89 90 91 92 93 94
	/**
	 * @return string default schema.
	 */
	public function getDefaultSchema()
	{
		if ($this->_defaultSchema === null) {
			$this->setDefaultSchema(strtoupper($this->db->username));
		}
		return $this->_defaultSchema;
	}
p0larbeer committed
95

Qiang Xue committed
96 97 98 99 100 101 102
	/**
	 * @param string $schema default schema.
	 */
	public function setDefaultSchema($schema)
	{
		$this->_defaultSchema = $schema;
	}
p0larbeer committed
103

Qiang Xue committed
104 105 106 107 108 109 110 111 112
	/**
	 * 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
113

Qiang Xue committed
114
		$sql = <<<EOD
p0larbeer committed
115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142
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
143

Qiang Xue committed
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 171 172 173 174 175 176 177 178 179 180
		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
181

Qiang Xue committed
182 183 184
	protected function findConstraints($table)
	{
		$sql = <<<EOD
p0larbeer committed
185 186 187 188 189 190 191 192 193 194 195 196
		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
197 198 199 200 201 202 203 204
		$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
205

Qiang Xue committed
206 207 208 209 210 211 212
	/**
	 * @inheritdoc
	 */
	protected function findTableNames($schema = '')
	{
		if ($schema === '') {
			$sql = <<<EOD
p0larbeer committed
213 214
SELECT table_name, '{$schema}' as table_schema FROM user_tables
EOD;
Qiang Xue committed
215 216 217
			$command = $this->db->createCommand($sql);
		} else {
			$sql = <<<EOD
p0larbeer committed
218 219 220
SELECT object_name as table_name, owner as table_schema FROM all_objects
WHERE object_type = 'TABLE' AND owner=:schema
EOD;
Qiang Xue committed
221 222 223
			$command = $this->db->createCommand($sql);
			$command->bindParam(':schema', $schema);
		}
p0larbeer committed
224

Qiang Xue committed
225
		$rows = $command->queryAll();
Paul Kofmann committed
226
		$names = [];
Qiang Xue committed
227 228 229 230 231
		foreach ($rows as $row) {
			$names[] = $row['TABLE_NAME'];
		}
		return $names;
	}
p0larbeer committed
232

Qiang Xue committed
233 234 235 236 237 238 239 240
	/**
	 * 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
241

Qiang Xue committed
242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258
		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
259

Qiang Xue committed
260 261 262 263 264 265 266 267 268 269 270 271 272 273 274
	/**
	 * 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
275
}