Schema.php 7.02 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
{
Qiang Xue committed
23
	private $_defaultSchema;
p0larbeer committed
24

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

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

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

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

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

Qiang Xue committed
65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81
	/**
	 * 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
82

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

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

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

Qiang Xue committed
112
		$sql = <<<EOD
p0larbeer committed
113 114 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
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
141

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

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

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

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

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

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

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