SQL statements are created from Query objects using the QueryBuilder::build-method.
QueryBuilder is also used by Command to build SQL statements such as INSERT, UPDATE, DELETE, CREATE TABLE.
For more details and usage information on QueryBuilder, see the guide article on query builders.
/**
* @inheritdoc
* Firebird has its own SELECT syntax
* SELECT [FIRST (<int-expr>)] [SKIP (<int-expr>)] <columns> FROM ...
* @author srusakov@gmail.com
*/
public function build($query, $params = [])
{
list($sql, $params) = parent::build($query, $params);
if ($this->hasLimit($query->limit) and $this->hasOffset($query->offset)) {
$sql = preg_replace('/limit\\s\\d+/i', '', $sql, 1);
$sql = preg_replace('/offset\\s\\d+/i', '', $sql, 1);
$sql = preg_replace('/^SELECT /i', "SELECT FIRST {$query->limit} SKIP {$query->offset} ", $sql, 1);
} elseif ($this->hasLimit($query->limit)) {
$sql = preg_replace('/limit\\s\\d+/i', '', $sql, 1);
$sql = preg_replace('/offset\\s\\d+/i', '', $sql, 1);
$sql = preg_replace('/^SELECT /i', "SELECT FIRST {$query->limit} ", $sql, 1);
} elseif ($this->hasOffset($query->offset)) {
$sql = preg_replace('/limit\\s\\d+/i', '', $sql, 1);
$sql = preg_replace('/offset\\s\\d+/i', '', $sql, 1);
$sql = preg_replace('/^SELECT /i', "SELECT SKIP {$query->offset} ", $sql, 1);
}
return [$sql, $params];
}
/**
* Generates a batch INSERT SQL statement.
* For example,
*
* ~~~
* $connection->createCommand()->batchInsert('user', ['name', 'age'], [
* ['Tom', 30],
* ['Jane', 20],
* ['Linda', 25],
* ])->execute();
* ~~~
*
* 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)
{
// SQLite supports batch insert natively since 3.7.11
// http://www.sqlite.org/releaselog/3_7_11.html
$this->db->open();
// ensure pdo is not null
if (version_compare($this->db->pdo->getAttribute(\PDO::ATTR_SERVER_VERSION), '3.7.11', '>=')) {
return parent::batchInsert($table, $columns, $rows);
}
$schema = $this->db->getSchema();
if (($tableSchema = $schema->getTableSchema($table)) !== null) {
$columnSchemas = $tableSchema->columns;
} else {
$columnSchemas = [];
}
$values = [];
foreach ($rows as $row) {
$vs = [];
foreach ($row as $i => $value) {
if (!is_array($value) && isset($columnSchemas[$columns[$i]])) {
$value = $columnSchemas[$columns[$i]]->dbTypecast($value);
}
if (is_string($value)) {
$value = $schema->quoteValue($value);
} elseif ($value === false) {
$value = 0;
} elseif ($value === null) {
$value = 'NULL';
}
$vs[] = $value;
}
$values[] = implode(', ', $vs);
}
foreach ($columns as $i => $name) {
$columns[$i] = $schema->quoteColumnName($name);
}
return 'INSERT INTO ' . $schema->quoteTableName($table) . ' (' . implode(', ', $columns) . ') SELECT ' . implode(' UNION SELECT ', $values);
}
/**
* @inheritdoc
* @throws NotSupportedException if `$columns` is an array
*/
protected function buildSubqueryInCondition($operator, $columns, $values, &$params)
{
if (is_array($columns)) {
throw new NotSupportedException(__METHOD__ . ' is not supported by MSSQL.');
}
return parent::buildSubqueryInCondition($operator, $columns, $values, $params);
}
/**
* This command will update auth tables with data from auth configs
*/
public function actionSyncDeploy()
{
$queryBuilder = new QueryBuilder(Yii::$app->db);
Yii::$app->db->createCommand("SET FOREIGN_KEY_CHECKS=0;")->execute();
if (file_exists($this->authItemConfig)) {
Yii::$app->db->createCommand()->delete('AuthItem')->execute();
$authItem = (require $this->authItemConfig);
$insertAuthItemQuery = $queryBuilder->batchInsert('AuthItem', ['name', 'type', 'description', 'rule_name', 'data'], $authItem);
Yii::$app->db->createCommand($insertAuthItemQuery)->execute();
}
if (file_exists($this->authItemChildConfig)) {
Yii::$app->db->createCommand()->delete('AuthItemChild')->execute();
$authItemChild = (require $this->authItemChildConfig);
$insertAuthItemChildQuery = $queryBuilder->batchInsert('AuthItemChild', ['parent', 'child'], $authItemChild);
Yii::$app->db->createCommand($insertAuthItemChildQuery)->execute();
}
if (file_exists($this->authRuleConfig)) {
Yii::$app->db->createCommand()->delete('AuthRule')->execute();
$authRule = (require $this->authRuleConfig);
$insertAuthRuleQuery = $queryBuilder->batchInsert('AuthRule', ['name', 'data'], $authRule);
Yii::$app->db->createCommand($insertAuthRuleQuery)->execute();
}
Yii::$app->db->createCommand("SET FOREIGN_KEY_CHECKS=1;")->execute();
Yii::$app->db->createCommand("DELETE aa FROM `AuthAssignment` aa LEFT JOIN AuthItem ai ON(aa.item_name = ai.name) WHERE ai.name IS NULL;")->execute();
Yii::$app->cache->flush();
}
/**
* This command will update auth tables with data from auth configs
*/
public function actionSyncDeploy()
{
$queryBuilder = new QueryBuilder(Yii::$app->db);
$authItemTable = Yii::$app->authManager->itemTable;
if ('pgsql' == Yii::$app->db->driverName) {
Yii::$app->db->createCommand("ALTER TABLE \"{$authItemTable}\" DISABLE TRIGGER ALL")->execute();
} else {
Yii::$app->db->createCommand("SET FOREIGN_KEY_CHECKS=0;")->execute();
}
if (file_exists($this->authItemConfig)) {
Yii::$app->db->createCommand()->delete($authItemTable)->execute();
$authItem = (require $this->authItemConfig);
if (!empty($authItem)) {
$insertAuthItemQuery = $queryBuilder->batchInsert($authItemTable, ['name', 'type', 'description', 'rule_name', 'data'], $authItem);
Yii::$app->db->createCommand($insertAuthItemQuery)->execute();
}
}
if (file_exists($this->authItemChildConfig)) {
Yii::$app->db->createCommand()->delete(Yii::$app->authManager->itemChildTable)->execute();
$authItemChild = (require $this->authItemChildConfig);
if (!empty($authItemChild)) {
$insertAuthItemChildQuery = $queryBuilder->batchInsert(Yii::$app->authManager->itemChildTable, ['parent', 'child'], $authItemChild);
Yii::$app->db->createCommand($insertAuthItemChildQuery)->execute();
}
}
if (file_exists($this->authRuleConfig)) {
Yii::$app->db->createCommand()->delete(Yii::$app->authManager->ruleTable)->execute();
$authRule = (require $this->authRuleConfig);
if (!empty($authRule)) {
$insertAuthRuleQuery = $queryBuilder->batchInsert(Yii::$app->authManager->ruleTable, ['name', 'data'], $authRule);
Yii::$app->db->createCommand($insertAuthRuleQuery)->execute();
}
}
if ('pgsql' == Yii::$app->db->driverName) {
Yii::$app->db->createCommand("ALTER TABLE \"{$authItemTable}\" ENABLE TRIGGER ALL")->execute();
} else {
Yii::$app->db->createCommand("SET FOREIGN_KEY_CHECKS=1;")->execute();
}
$this->processAssignments();
Yii::$app->cache->flush();
}
/**
* @inheritdoc
*/
public function dropTable($table)
{
$sql = parent::dropTable($table);
$tableSchema = $this->db->getTableSchema($table);
if ($tableSchema === null || $tableSchema->sequenceName === null) {
return $sql;
}
$sqlBlock = <<<SQL
EXECUTE block AS
BEGIN
EXECUTE STATEMENT {$this->db->quoteValue($sql)};
EXECUTE STATEMENT {$this->db->quoteValue("DROP SEQUENCE {$tableSchema->sequenceName}")};
END;
SQL;
return $sqlBlock;
}
/**
* Recalculate sorting
*/
public function recalculateSort()
{
$owner = $this->owner;
$db = $this->owner->getDb();
$builder = new QueryBuilder($db);
$orderFields = ['sort' => 'asc'];
foreach ($owner->primaryKey() as $field) {
if ($field != 'sort') {
$orderFields[$field] = 'asc';
}
}
// recalculate sort
$query = $builder->update($owner->tableName(), [$this->sortAttribute => new Expression('(@sortingCount:=(@sortingCount+1))')], $this->getCondition(), $params) . ' ' . $builder->buildOrderBy($orderFields);
$db->createCommand('set @sortingCount=-1;' . $query, $params)->execute();
// update in current record
if (!$owner->getIsNewRecord()) {
$owner->{$this->sortAttribute} = $owner->findOne($owner->getPrimaryKey())->{$this->sortAttribute};
}
}
/**
* @inheritdoc
*/
public function buildOrderBy($columns)
{
if (empty($columns)) {
// hack so LIMIT will work if no ORDER BY is specified
return 'ORDER BY (SELECT NULL)';
} else {
return parent::buildOrderBy($columns);
}
}
/**
* @inheritdoc
*/
public function update($table, $columns, $condition, &$params)
{
return parent::update($table, $this->normalizeTableRowData($table, $columns), $condition, $params);
}