QueryBuilder.php 5.68 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\base\InvalidParamException;
11
use yii\db\Connection;
12
use yii\db\Exception;
p0larbeer committed
13 14

/**
p0larbeer committed
15
 * QueryBuilder is the query builder for Oracle databases.
Qiang Xue committed
16
 *
17 18
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @since 2.0
p0larbeer committed
19
 */
p0larbeer committed
20 21
class QueryBuilder extends \yii\db\QueryBuilder
{
22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
    /**
     * @var array mapping from abstract column types (keys) to physical column types (values).
     */
    public $typeMap = [
        Schema::TYPE_PK => 'NUMBER(10) NOT NULL PRIMARY KEY',
        Schema::TYPE_BIGPK => 'NUMBER(20) NOT NULL PRIMARY KEY',
        Schema::TYPE_STRING => 'VARCHAR2(255)',
        Schema::TYPE_TEXT => 'CLOB',
        Schema::TYPE_SMALLINT => 'NUMBER(5)',
        Schema::TYPE_INTEGER => 'NUMBER(10)',
        Schema::TYPE_BIGINT => 'NUMBER(20)',
        Schema::TYPE_FLOAT => 'NUMBER',
        Schema::TYPE_DECIMAL => 'NUMBER',
        Schema::TYPE_DATETIME => 'TIMESTAMP',
        Schema::TYPE_TIMESTAMP => 'TIMESTAMP',
        Schema::TYPE_TIME => 'TIMESTAMP',
        Schema::TYPE_DATE => 'DATE',
        Schema::TYPE_BINARY => 'BLOB',
        Schema::TYPE_BOOLEAN => 'NUMBER(1)',
        Schema::TYPE_MONEY => 'NUMBER(19,4)',
    ];

44

Qiang Xue committed
45 46 47
    /**
     * @inheritdoc
     */
48
    public function buildOrderByAndLimit($sql, $orderBy, $limit, $offset)
49
    {
50 51 52
        $orderBy = $this->buildOrderBy($orderBy);
        if ($orderBy !== '') {
            $sql .= $this->separator . $orderBy;
53 54 55
        }

        $filters = [];
56
        if ($this->hasOffset($offset)) {
57 58 59 60 61
            $filters[] = 'rowNumId > ' . $offset;
        }
        if ($this->hasLimit($limit)) {
            $filters[] = 'rownum <= ' . $limit;
        }
62 63 64
        if (empty($filters)) {
            return $sql;
        }
65

66 67 68
        $filter = implode(' AND ', $filters);
        return <<<EOD
WITH USER_SQL AS ($sql),
69
    PAGINATION AS (SELECT USER_SQL.*, rownum as rowNumId FROM USER_SQL)
p0larbeer committed
70 71
SELECT *
FROM PAGINATION
72
WHERE $filter
p0larbeer committed
73
EOD;
74 75 76 77 78
    }

    /**
     * Builds a SQL statement for renaming a DB table.
     *
79 80
     * @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.
81 82 83 84 85 86 87 88 89 90
     * @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.
     *
91 92
     * @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.
93
     * @param string $type the new column type. The [[getColumnType]] method will be invoked to convert abstract column type (if any)
94 95
     * 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'.
96 97 98 99 100 101 102 103 104 105 106 107
     * @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.
     *
108 109
     * @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.
110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132
     * @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 {
Qiang Xue committed
133
            // use master connection to get the biggest PK value
134 135 136
            $value = $this->db->useMaster(function (Connection $db) use ($tableSchema) {
                return $db->createCommand("SELECT MAX(\"{$tableSchema->primaryKey}\") FROM \"{$tableSchema->name}\"")->queryScalar();
            }) + 1;
137 138 139 140 141
        }

        return "DROP SEQUENCE \"{$tableSchema->name}_SEQ\";"
            . "CREATE SEQUENCE \"{$tableSchema->name}_SEQ\" START WITH {$value} INCREMENT BY 1 NOMAXVALUE NOCACHE";
    }
142 143 144 145 146 147 148 149 150 151 152 153 154 155

    /**
     * @inheritdoc
     */
    public function addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete = null, $update = null)
    {
        $sql = 'ALTER TABLE ' . $this->db->quoteTableName($table)
            . ' ADD CONSTRAINT ' . $this->db->quoteColumnName($name)
            . ' FOREIGN KEY (' . $this->buildColumns($columns) . ')'
            . ' REFERENCES ' . $this->db->quoteTableName($refTable)
            . ' (' . $this->buildColumns($refColumns) . ')';
        if ($delete !== null) {
            $sql .= ' ON DELETE ' . $delete;
        }
156 157
        if ($update !== null) {
            throw new Exception('Oracle does not support ON UPDATE clause.');
158 159
        }

160
        return $sql;
161
    }
p0larbeer committed
162
}