QueryBuilder.php 4.16 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 11 12
namespace yii\db\oci;

use yii\base\InvalidParamException;

/**
p0larbeer committed
13
 * QueryBuilder is the query builder for Oracle databases.
Qiang Xue committed
14
 *
15 16
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @since 2.0
p0larbeer committed
17
 */
p0larbeer committed
18 19
class QueryBuilder extends \yii\db\QueryBuilder
{
p0larbeer committed
20

Qiang Xue committed
21 22 23 24 25 26
	private $sql;

	public function build($query)
	{
		$params = $query->params;
		$clauses = [
27 28
			$this->buildSelect($query->select, $params, $query->distinct, $query->selectOption),
			$this->buildFrom($query->from, $params),
Qiang Xue committed
29 30 31 32 33 34 35 36
			$this->buildJoin($query->join, $params),
			$this->buildWhere($query->where, $params),
			$this->buildGroupBy($query->groupBy),
			$this->buildHaving($query->having, $params),
			$this->buildOrderBy($query->orderBy),
		];
		$this->sql = implode($this->separator, array_filter($clauses));

37
		$this->sql = $this->buildLimit($query->limit, $query->offset);
Qiang Xue committed
38 39 40 41 42 43

		$unions = $this->buildUnion($query->union, $params);
		if ($unions !== '') {
			$this->sql .= $this->separator . $unions;
		}

Qiang Xue committed
44 45 46 47 48
		return [$this->sql, $params];
	}

	public function buildLimit($limit, $offset)
	{
Paul Kofmann committed
49
		$filters = [];
50 51
		if ($this->hasOffset($offset) > 0) {
			$filters[] = 'rowNumId > ' . $offset;
Qiang Xue committed
52 53
		}

54 55
		if ($this->hasLimit($limit)) {
			$filters[] = 'rownum <= ' . $limit;
Qiang Xue committed
56 57
		}

58
		if (!empty($filters)) {
Qiang Xue committed
59
			$filter = implode(' and ', $filters);
60
			return <<<EOD
p0larbeer committed
61 62 63 64
WITH USER_SQL AS ({$this->sql}),
	PAGINATION AS (SELECT USER_SQL.*, rownum as rowNumId FROM USER_SQL)
SELECT *
FROM PAGINATION
65
WHERE $filter
p0larbeer committed
66
EOD;
67 68 69
		} else {
			return $this->sql;
		}
Qiang Xue committed
70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 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 133 134
	}


	/**
	 * Builds a SQL statement for renaming a DB table.
	 *
	 * @param string $table the table to be renamed. The name will be properly quoted by the method.
	 * @param string $newName the new table name. The name will be properly quoted by the method.
	 * @return string the SQL statement for renaming a DB table.
	 */
	public function renameTable($table, $newName)
	{
		return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' RENAME TO ' . $this->db->quoteTableName($newName);
	}

	/**
	 * Builds a SQL statement for changing the definition of a column.
	 *
	 * @param string $table the table whose column is to be changed. The table name will be properly quoted by the method.
	 * @param string $column the name of the column to be changed. The name will be properly quoted by the method.
	 * @param string $type the new column type. The {@link getColumnType} method will be invoked to convert abstract column type (if any)
	 * into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL.
	 * For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'.
	 * @return string the SQL statement for changing the definition of a column.
	 */
	public function alterColumn($table, $column, $type)
	{
		$type = $this->getColumnType($type);
		return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' MODIFY ' . $this->db->quoteColumnName($column) . ' ' . $this->getColumnType($type);
	}

	/**
	 * Builds a SQL statement for dropping an index.
	 *
	 * @param string $name the name of the index to be dropped. The name will be properly quoted by the method.
	 * @param string $table the table whose index is to be dropped. The name will be properly quoted by the method.
	 * @return string the SQL statement for dropping an index.
	 */
	public function dropIndex($name, $table)
	{
		return 'DROP INDEX ' . $this->db->quoteTableName($name);
	}

	/**
	 * @inheritdoc
	 */
	public function resetSequence($table, $value = null)
	{
		$tableSchema = $this->db->getTableSchema($table);
		if ($tableSchema === null) {
			throw new InvalidParamException("Unknown table: $table");
		}
		if ($tableSchema->sequenceName === null) {
			return '';
		}

		if ($value !== null) {
			$value = (int)$value;
		} else {
			$value = (int)$this->db->createCommand("SELECT MAX(\"{$tableSchema->primaryKey}\") FROM \"{$tableSchema->name}\"")->queryScalar();
			$value++;
		}
		return "DROP SEQUENCE \"{$tableSchema->name}_SEQ\";"
			. "CREATE SEQUENCE \"{$tableSchema->name}_SEQ\" START WITH {$value} INCREMENT BY 1 NOMAXVALUE NOCACHE";
	}
p0larbeer committed
135
}