123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535 |
- <?php
- namespace yii\db\mssql;
- use yii\base\InvalidArgumentException;
- use yii\db\Constraint;
- use yii\db\Expression;
- class QueryBuilder extends \yii\db\QueryBuilder
- {
-
- public $typeMap = [
- Schema::TYPE_PK => 'int IDENTITY PRIMARY KEY',
- Schema::TYPE_UPK => 'int IDENTITY PRIMARY KEY',
- Schema::TYPE_BIGPK => 'bigint IDENTITY PRIMARY KEY',
- Schema::TYPE_UBIGPK => 'bigint IDENTITY PRIMARY KEY',
- Schema::TYPE_CHAR => 'nchar(1)',
- Schema::TYPE_STRING => 'nvarchar(255)',
- Schema::TYPE_TEXT => 'nvarchar(max)',
- Schema::TYPE_TINYINT => 'tinyint',
- Schema::TYPE_SMALLINT => 'smallint',
- Schema::TYPE_INTEGER => 'int',
- Schema::TYPE_BIGINT => 'bigint',
- Schema::TYPE_FLOAT => 'float',
- Schema::TYPE_DOUBLE => 'float',
- Schema::TYPE_DECIMAL => 'decimal(18,0)',
- Schema::TYPE_DATETIME => 'datetime',
- Schema::TYPE_TIMESTAMP => 'datetime',
- Schema::TYPE_TIME => 'time',
- Schema::TYPE_DATE => 'date',
- Schema::TYPE_BINARY => 'varbinary(max)',
- Schema::TYPE_BOOLEAN => 'bit',
- Schema::TYPE_MONEY => 'decimal(19,4)',
- ];
-
- protected function defaultExpressionBuilders()
- {
- return array_merge(parent::defaultExpressionBuilders(), [
- 'yii\db\conditions\InCondition' => 'yii\db\mssql\conditions\InConditionBuilder',
- 'yii\db\conditions\LikeCondition' => 'yii\db\mssql\conditions\LikeConditionBuilder',
- ]);
- }
-
- public function buildOrderByAndLimit($sql, $orderBy, $limit, $offset)
- {
- if (!$this->hasOffset($offset) && !$this->hasLimit($limit)) {
- $orderBy = $this->buildOrderBy($orderBy);
- return $orderBy === '' ? $sql : $sql . $this->separator . $orderBy;
- }
- if (version_compare($this->db->getSchema()->getServerVersion(), '11', '<')) {
- return $this->oldBuildOrderByAndLimit($sql, $orderBy, $limit, $offset);
- }
- return $this->newBuildOrderByAndLimit($sql, $orderBy, $limit, $offset);
- }
-
- protected function newBuildOrderByAndLimit($sql, $orderBy, $limit, $offset)
- {
- $orderBy = $this->buildOrderBy($orderBy);
- if ($orderBy === '') {
-
- $orderBy = 'ORDER BY (SELECT NULL)';
- }
- $sql .= $this->separator . $orderBy;
-
- $offset = $this->hasOffset($offset) ? $offset : '0';
- $sql .= $this->separator . "OFFSET $offset ROWS";
- if ($this->hasLimit($limit)) {
- $sql .= $this->separator . "FETCH NEXT $limit ROWS ONLY";
- }
- return $sql;
- }
-
- protected function oldBuildOrderByAndLimit($sql, $orderBy, $limit, $offset)
- {
- $orderBy = $this->buildOrderBy($orderBy);
- if ($orderBy === '') {
-
- $orderBy = 'ORDER BY (SELECT NULL)';
- }
- $sql = preg_replace('/^([\s(])*SELECT(\s+DISTINCT)?(?!\s*TOP\s*\()/i', "\\1SELECT\\2 rowNum = ROW_NUMBER() over ($orderBy),", $sql);
- if ($this->hasLimit($limit)) {
- $sql = "SELECT TOP $limit * FROM ($sql) sub";
- } else {
- $sql = "SELECT * FROM ($sql) sub";
- }
- if ($this->hasOffset($offset)) {
- $sql .= $this->separator . "WHERE rowNum > $offset";
- }
- return $sql;
- }
-
- public function renameTable($oldName, $newName)
- {
- return 'sp_rename ' . $this->db->quoteTableName($oldName) . ', ' . $this->db->quoteTableName($newName);
- }
-
- public function renameColumn($table, $oldName, $newName)
- {
- $table = $this->db->quoteTableName($table);
- $oldName = $this->db->quoteColumnName($oldName);
- $newName = $this->db->quoteColumnName($newName);
- return "sp_rename '{$table}.{$oldName}', {$newName}, 'COLUMN'";
- }
-
- public function alterColumn($table, $column, $type)
- {
- $type = $this->getColumnType($type);
- $sql = 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ALTER COLUMN '
- . $this->db->quoteColumnName($column) . ' '
- . $this->getColumnType($type);
- return $sql;
- }
-
- public function addDefaultValue($name, $table, $column, $value)
- {
- return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ADD CONSTRAINT '
- . $this->db->quoteColumnName($name) . ' DEFAULT ' . $this->db->quoteValue($value) . ' FOR '
- . $this->db->quoteColumnName($column);
- }
-
- public function dropDefaultValue($name, $table)
- {
- return 'ALTER TABLE ' . $this->db->quoteTableName($table)
- . ' DROP CONSTRAINT ' . $this->db->quoteColumnName($name);
- }
-
- 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 = $this->db->quoteColumnName(reset($table->primaryKey));
- $value = "(SELECT COALESCE(MAX({$key}),0) FROM {$tableName})+1";
- } else {
- $value = (int) $value;
- }
- return "DBCC CHECKIDENT ('{$tableName}', RESEED, {$value})";
- } elseif ($table === null) {
- throw new InvalidArgumentException("Table not found: $tableName");
- }
- throw new InvalidArgumentException("There is not sequence associated with table '$tableName'.");
- }
-
- public function checkIntegrity($check = true, $schema = '', $table = '')
- {
- $enable = $check ? 'CHECK' : 'NOCHECK';
- $schema = $schema ?: $this->db->getSchema()->defaultSchema;
- $tableNames = $this->db->getTableSchema($table) ? [$table] : $this->db->getSchema()->getTableNames($schema);
- $viewNames = $this->db->getSchema()->getViewNames($schema);
- $tableNames = array_diff($tableNames, $viewNames);
- $command = '';
- foreach ($tableNames as $tableName) {
- $tableName = $this->db->quoteTableName("{$schema}.{$tableName}");
- $command .= "ALTER TABLE $tableName $enable CONSTRAINT ALL; ";
- }
- return $command;
- }
-
- protected function buildAddCommentSql($comment, $table, $column = null)
- {
- $tableSchema = $this->db->schema->getTableSchema($table);
- if ($tableSchema === null) {
- throw new InvalidArgumentException("Table not found: $table");
- }
- $schemaName = $tableSchema->schemaName ? "N'" . $tableSchema->schemaName . "'": 'SCHEMA_NAME()';
- $tableName = "N" . $this->db->quoteValue($tableSchema->name);
- $columnName = $column ? "N" . $this->db->quoteValue($column) : null;
- $comment = "N" . $this->db->quoteValue($comment);
- $functionParams = "
- @name = N'MS_description',
- @value = $comment,
- @level0type = N'SCHEMA', @level0name = $schemaName,
- @level1type = N'TABLE', @level1name = $tableName"
- . ($column ? ", @level2type = N'COLUMN', @level2name = $columnName" : '') . ';';
- return "
- IF NOT EXISTS (
- SELECT 1
- FROM fn_listextendedproperty (
- N'MS_description',
- 'SCHEMA', $schemaName,
- 'TABLE', $tableName,
- " . ($column ? "'COLUMN', $columnName " : ' DEFAULT, DEFAULT ') . "
- )
- )
- EXEC sys.sp_addextendedproperty $functionParams
- ELSE
- EXEC sys.sp_updateextendedproperty $functionParams
- ";
- }
-
- public function addCommentOnColumn($table, $column, $comment)
- {
- return $this->buildAddCommentSql($comment, $table, $column);
- }
-
- public function addCommentOnTable($table, $comment)
- {
- return $this->buildAddCommentSql($comment, $table);
- }
-
- protected function buildRemoveCommentSql($table, $column = null)
- {
- $tableSchema = $this->db->schema->getTableSchema($table);
- if ($tableSchema === null) {
- throw new InvalidArgumentException("Table not found: $table");
- }
- $schemaName = $tableSchema->schemaName ? "N'" . $tableSchema->schemaName . "'": 'SCHEMA_NAME()';
- $tableName = "N" . $this->db->quoteValue($tableSchema->name);
- $columnName = $column ? "N" . $this->db->quoteValue($column) : null;
- return "
- IF EXISTS (
- SELECT 1
- FROM fn_listextendedproperty (
- N'MS_description',
- 'SCHEMA', $schemaName,
- 'TABLE', $tableName,
- " . ($column ? "'COLUMN', $columnName " : ' DEFAULT, DEFAULT ') . "
- )
- )
- EXEC sys.sp_dropextendedproperty
- @name = N'MS_description',
- @level0type = N'SCHEMA', @level0name = $schemaName,
- @level1type = N'TABLE', @level1name = $tableName"
- . ($column ? ", @level2type = N'COLUMN', @level2name = $columnName" : '') . ';';
- }
-
- public function dropCommentFromColumn($table, $column)
- {
- return $this->buildRemoveCommentSql($table, $column);
- }
-
- public function dropCommentFromTable($table)
- {
- return $this->buildRemoveCommentSql($table);
- }
-
- protected function getAllColumnNames($modelClass = null)
- {
- if (!$modelClass) {
- return null;
- }
-
- $schema = $modelClass::getTableSchema();
- return array_keys($schema->columns);
- }
-
- protected function isOldMssql()
- {
- return version_compare($this->db->getSchema()->getServerVersion(), '11', '<');
- }
-
- public function selectExists($rawSql)
- {
- return 'SELECT CASE WHEN EXISTS(' . $rawSql . ') THEN 1 ELSE 0 END';
- }
-
- private function normalizeTableRowData($table, $columns, &$params)
- {
- if (($tableSchema = $this->db->getSchema()->getTableSchema($table)) !== null) {
- $columnSchemas = $tableSchema->columns;
- foreach ($columns as $name => $value) {
-
- if (isset($columnSchemas[$name]) && $columnSchemas[$name]->type === Schema::TYPE_BINARY && $columnSchemas[$name]->dbType === 'varbinary' && (is_string($value) || $value === null)) {
- $phName = $this->bindParam($value, $params);
- $columns[$name] = new Expression("CONVERT(VARBINARY, $phName)", $params);
- }
- }
- }
- return $columns;
- }
-
- public function insert($table, $columns, &$params)
- {
- return parent::insert($table, $this->normalizeTableRowData($table, $columns, $params), $params);
- }
-
- public function upsert($table, $insertColumns, $updateColumns, &$params)
- {
-
- list($uniqueNames, $insertNames, $updateNames) = $this->prepareUpsertColumns($table, $insertColumns, $updateColumns, $constraints);
- if (empty($uniqueNames)) {
- return $this->insert($table, $insertColumns, $params);
- }
- $onCondition = ['or'];
- $quotedTableName = $this->db->quoteTableName($table);
- foreach ($constraints as $constraint) {
- $constraintCondition = ['and'];
- foreach ($constraint->columnNames as $name) {
- $quotedName = $this->db->quoteColumnName($name);
- $constraintCondition[] = "$quotedTableName.$quotedName=[EXCLUDED].$quotedName";
- }
- $onCondition[] = $constraintCondition;
- }
- $on = $this->buildCondition($onCondition, $params);
- list(, $placeholders, $values, $params) = $this->prepareInsertValues($table, $insertColumns, $params);
- $mergeSql = 'MERGE ' . $this->db->quoteTableName($table) . ' WITH (HOLDLOCK) '
- . 'USING (' . (!empty($placeholders) ? 'VALUES (' . implode(', ', $placeholders) . ')' : ltrim($values, ' ')) . ') AS [EXCLUDED] (' . implode(', ', $insertNames) . ') '
- . "ON ($on)";
- $insertValues = [];
- foreach ($insertNames as $name) {
- $quotedName = $this->db->quoteColumnName($name);
- if (strrpos($quotedName, '.') === false) {
- $quotedName = '[EXCLUDED].' . $quotedName;
- }
- $insertValues[] = $quotedName;
- }
- $insertSql = 'INSERT (' . implode(', ', $insertNames) . ')'
- . ' VALUES (' . implode(', ', $insertValues) . ')';
- if ($updateColumns === false) {
- return "$mergeSql WHEN NOT MATCHED THEN $insertSql;";
- }
- if ($updateColumns === true) {
- $updateColumns = [];
- foreach ($updateNames as $name) {
- $quotedName = $this->db->quoteColumnName($name);
- if (strrpos($quotedName, '.') === false) {
- $quotedName = '[EXCLUDED].' . $quotedName;
- }
- $updateColumns[$name] = new Expression($quotedName);
- }
- }
- list($updates, $params) = $this->prepareUpdateSets($table, $updateColumns, $params);
- $updateSql = 'UPDATE SET ' . implode(', ', $updates);
- return "$mergeSql WHEN MATCHED THEN $updateSql WHEN NOT MATCHED THEN $insertSql;";
- }
-
- public function update($table, $columns, $condition, &$params)
- {
- return parent::update($table, $this->normalizeTableRowData($table, $columns, $params), $condition, $params);
- }
-
- public function getColumnType($type)
- {
- $columnType = parent::getColumnType($type);
-
- $columnType = preg_replace("/\s*comment '.*'/i", '', $columnType);
- $columnType = preg_replace('/ first$/i', '', $columnType);
- return $columnType;
- }
-
- protected function extractAlias($table)
- {
- if (preg_match('/^\[.*\]$/', $table)) {
- return false;
- }
- return parent::extractAlias($table);
- }
- }
|