1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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
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
135
136
137
138
139
140
141
142
143
144
145
146
|
<?php
namespace Doctrine\DBAL\Platforms;
use Doctrine\DBAL\Schema\Sequence;
use function preg_match;
use function preg_match_all;
use function substr_count;
use const PREG_OFFSET_CAPTURE;
/**
* Platform to ensure compatibility of Doctrine with Microsoft SQL Server 2012 version.
*
* Differences to SQL Server 2008 and before are that sequences are introduced,
* and support for the new OFFSET... FETCH syntax for result pagination has been added.
*/
class SQLServer2012Platform extends SQLServer2008Platform
{
/**
* {@inheritdoc}
*/
public function getAlterSequenceSQL(Sequence $sequence)
{
return 'ALTER SEQUENCE ' . $sequence->getQuotedName($this) .
' INCREMENT BY ' . $sequence->getAllocationSize();
}
/**
* {@inheritdoc}
*/
public function getCreateSequenceSQL(Sequence $sequence)
{
return 'CREATE SEQUENCE ' . $sequence->getQuotedName($this) .
' START WITH ' . $sequence->getInitialValue() .
' INCREMENT BY ' . $sequence->getAllocationSize() .
' MINVALUE ' . $sequence->getInitialValue();
}
/**
* {@inheritdoc}
*/
public function getDropSequenceSQL($sequence)
{
if ($sequence instanceof Sequence) {
$sequence = $sequence->getQuotedName($this);
}
return 'DROP SEQUENCE ' . $sequence;
}
/**
* {@inheritdoc}
*/
public function getListSequencesSQL($database)
{
return 'SELECT seq.name,
CAST(
seq.increment AS VARCHAR(MAX)
) AS increment, -- CAST avoids driver error for sql_variant type
CAST(
seq.start_value AS VARCHAR(MAX)
) AS start_value -- CAST avoids driver error for sql_variant type
FROM sys.sequences AS seq';
}
/**
* {@inheritdoc}
*/
public function getSequenceNextValSQL($sequence)
{
return 'SELECT NEXT VALUE FOR ' . $sequence;
}
/**
* {@inheritdoc}
*/
public function supportsSequences()
{
return true;
}
/**
* {@inheritdoc}
*
* Returns Microsoft SQL Server 2012 specific keywords class
*/
protected function getReservedKeywordsClass()
{
return Keywords\SQLServer2012Keywords::class;
}
/**
* {@inheritdoc}
*/
protected function doModifyLimitQuery($query, $limit, $offset = null)
{
if ($limit === null && $offset <= 0) {
return $query;
}
// Queries using OFFSET... FETCH MUST have an ORDER BY clause
// Find the position of the last instance of ORDER BY and ensure it is not within a parenthetical statement
// but can be in a newline
$matches = [];
$matchesCount = preg_match_all('/[\\s]+order\\s+by\\s/im', $query, $matches, PREG_OFFSET_CAPTURE);
$orderByPos = false;
if ($matchesCount > 0) {
$orderByPos = $matches[0][$matchesCount - 1][1];
}
if (
$orderByPos === false
|| substr_count($query, '(', $orderByPos) - substr_count($query, ')', $orderByPos)
) {
if (preg_match('/^SELECT\s+DISTINCT/im', $query)) {
// SQL Server won't let us order by a non-selected column in a DISTINCT query,
// so we have to do this madness. This says, order by the first column in the
// result. SQL Server's docs say that a nonordered query's result order is non-
// deterministic anyway, so this won't do anything that a bunch of update and
// deletes to the table wouldn't do anyway.
$query .= ' ORDER BY 1';
} else {
// In another DBMS, we could do ORDER BY 0, but SQL Server gets angry if you
// use constant expressions in the order by list.
$query .= ' ORDER BY (SELECT 0)';
}
}
if ($offset === null) {
$offset = 0;
}
// This looks somewhat like MYSQL, but limit/offset are in inverse positions
// Supposedly SQL:2008 core standard.
// Per TSQL spec, FETCH NEXT n ROWS ONLY is not valid without OFFSET n ROWS.
$query .= ' OFFSET ' . (int) $offset . ' ROWS';
if ($limit !== null) {
$query .= ' FETCH NEXT ' . (int) $limit . ' ROWS ONLY';
}
return $query;
}
}
|
