QueryBuilder.php 3.91 KB
Newer Older
Carsten Brandt committed
1 2 3 4 5 6 7 8 9 10 11 12
<?php
/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */

namespace yii\db\cubrid;

use yii\base\InvalidParamException;

/**
13
 * QueryBuilder is the query builder for CUBRID databases (version 9.1.x and higher).
Carsten Brandt committed
14 15 16 17 18 19 20 21 22
 *
 * @author Carsten Brandt <mail@cebe.cc>
 * @since 2.0
 */
class QueryBuilder extends \yii\db\QueryBuilder
{
	/**
	 * @var array mapping from abstract column types (keys) to physical column types (values).
	 */
Alexander Makarov committed
23
	public $typeMap = [
24
		Schema::TYPE_PK => 'int NOT NULL AUTO_INCREMENT PRIMARY KEY',
25
		Schema::TYPE_BIGPK => 'bigint NOT NULL AUTO_INCREMENT PRIMARY KEY',
Carsten Brandt committed
26 27 28 29 30 31 32 33 34 35 36 37
		Schema::TYPE_STRING => 'varchar(255)',
		Schema::TYPE_TEXT => 'varchar',
		Schema::TYPE_SMALLINT => 'smallint',
		Schema::TYPE_INTEGER => 'int',
		Schema::TYPE_BIGINT => 'bigint',
		Schema::TYPE_FLOAT => 'float(7)',
		Schema::TYPE_DECIMAL => 'decimal(10,0)',
		Schema::TYPE_DATETIME => 'datetime',
		Schema::TYPE_TIMESTAMP => 'timestamp',
		Schema::TYPE_TIME => 'time',
		Schema::TYPE_DATE => 'date',
		Schema::TYPE_BINARY => 'blob',
38
		Schema::TYPE_BOOLEAN => 'smallint',
Carsten Brandt committed
39
		Schema::TYPE_MONEY => 'decimal(19,4)',
Alexander Makarov committed
40
	];
Carsten Brandt committed
41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75

	/**
	 * Creates a SQL statement for resetting the sequence value of a table's primary key.
	 * The sequence will be reset such that the primary key of the next new row inserted
	 * will have the specified value or 1.
	 * @param string $tableName the name of the table whose primary key sequence will be reset
	 * @param mixed $value the value for the primary key of the next new row inserted. If this is not set,
	 * the next new row's primary key will have a value 1.
	 * @return string the SQL statement for resetting sequence
	 * @throws InvalidParamException if the table does not exist or there is no sequence associated with the table.
	 */
	public function resetSequence($tableName, $value = null)
	{
		$table = $this->db->getTableSchema($tableName);
		if ($table !== null && $table->sequenceName !== null) {
			$tableName = $this->db->quoteTableName($tableName);
			if ($value === null) {
				$key = reset($table->primaryKey);
				$value = (int)$this->db->createCommand("SELECT MAX(`$key`) FROM " . $this->db->schema->quoteTableName($tableName))->queryScalar() + 1;
			} else {
				$value = (int)$value;
			}
			return "ALTER TABLE " . $this->db->schema->quoteTableName($tableName) . " AUTO_INCREMENT=$value;";
		} elseif ($table === null) {
			throw new InvalidParamException("Table not found: $tableName");
		} else {
			throw new InvalidParamException("There is not sequence associated with table '$tableName'.");
		}
	}

	/**
	 * Generates a batch INSERT SQL statement.
	 * For example,
	 *
	 * ~~~
Alexander Makarov committed
76 77 78 79 80
	 * $connection->createCommand()->batchInsert('tbl_user', ['name', 'age'], [
	 *     ['Tom', 30],
	 *     ['Jane', 20],
	 *     ['Linda', 25],
	 * ])->execute();
Carsten Brandt committed
81 82 83 84 85 86 87 88 89 90 91 92 93 94
	 * ~~~
	 *
	 * Note that the values in each row must match the corresponding column names.
	 *
	 * @param string $table the table that new rows will be inserted into.
	 * @param array $columns the column names
	 * @param array $rows the rows to be batch inserted into the table
	 * @return string the batch INSERT SQL statement
	 */
	public function batchInsert($table, $columns, $rows)
	{
		if (($tableSchema = $this->db->getTableSchema($table)) !== null) {
			$columnSchemas = $tableSchema->columns;
		} else {
Alexander Makarov committed
95
			$columnSchemas = [];
Carsten Brandt committed
96 97 98 99 100 101
		}

		foreach ($columns as $i => $name) {
			$columns[$i] = $this->db->quoteColumnName($name);
		}

Alexander Makarov committed
102
		$values = [];
Carsten Brandt committed
103
		foreach ($rows as $row) {
Alexander Makarov committed
104
			$vs = [];
Carsten Brandt committed
105 106 107 108 109 110 111 112 113 114 115 116 117
			foreach ($row as $i => $value) {
				if (!is_array($value) && isset($columnSchemas[$columns[$i]])) {
					$value = $columnSchemas[$columns[$i]]->typecast($value);
				}
				$vs[] = is_string($value) ? $this->db->quoteValue($value) : $value;
			}
			$values[] = '(' . implode(', ', $vs) . ')';
		}

		return 'INSERT INTO ' . $this->db->quoteTableName($table)
			. ' (' . implode(', ', $columns) . ') VALUES ' . implode(', ', $values);
	}
}