1 | <?php |
||
2 | /* |
||
3 | * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS |
||
4 | * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT |
||
5 | * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR |
||
6 | * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT |
||
7 | * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, |
||
8 | * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT |
||
9 | * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, |
||
10 | * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY |
||
11 | * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT |
||
12 | * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE |
||
13 | * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. |
||
14 | * |
||
15 | * This software consists of voluntary contributions made by many individuals |
||
16 | * and is licensed under the MIT license. For more information, see |
||
17 | * <http://www.doctrine-project.org>. |
||
18 | */ |
||
19 | |||
20 | namespace Doctrine\DBAL\Platforms; |
||
21 | |||
22 | use Doctrine\DBAL\Schema\Sequence; |
||
23 | |||
24 | /** |
||
25 | * Platform to ensure compatibility of Doctrine with Microsoft SQL Server 2012 version. |
||
26 | * |
||
27 | * Differences to SQL Server 2008 and before are that sequences are introduced, |
||
28 | * and support for the new OFFSET... FETCH syntax for result pagination has been added. |
||
29 | * |
||
30 | * @author Steve Müller <[email protected]> |
||
31 | */ |
||
32 | class SQLServer2012Platform extends SQLServer2008Platform |
||
33 | { |
||
34 | /** |
||
35 | * {@inheritdoc} |
||
36 | */ |
||
37 | 1 | public function getAlterSequenceSQL(Sequence $sequence) |
|
38 | { |
||
39 | 1 | return 'ALTER SEQUENCE ' . $sequence->getQuotedName($this) . |
|
40 | 1 | ' INCREMENT BY ' . $sequence->getAllocationSize(); |
|
41 | } |
||
42 | |||
43 | /** |
||
44 | * {@inheritdoc} |
||
45 | */ |
||
46 | 1 | View Code Duplication | public function getCreateSequenceSQL(Sequence $sequence) |
47 | { |
||
48 | 1 | return 'CREATE SEQUENCE ' . $sequence->getQuotedName($this) . |
|
49 | 1 | ' START WITH ' . $sequence->getInitialValue() . |
|
50 | 1 | ' INCREMENT BY ' . $sequence->getAllocationSize() . |
|
51 | 1 | ' MINVALUE ' . $sequence->getInitialValue(); |
|
52 | } |
||
53 | |||
54 | /** |
||
55 | * {@inheritdoc} |
||
56 | */ |
||
57 | 1 | public function getDropSequenceSQL($sequence) |
|
58 | { |
||
59 | 1 | if ($sequence instanceof Sequence) { |
|
60 | $sequence = $sequence->getQuotedName($this); |
||
61 | } |
||
62 | |||
63 | 1 | return 'DROP SEQUENCE ' . $sequence; |
|
64 | } |
||
65 | |||
66 | /** |
||
67 | * {@inheritdoc} |
||
68 | */ |
||
69 | public function getListSequencesSQL($database) |
||
70 | { |
||
71 | return 'SELECT seq.name, |
||
72 | CAST( |
||
73 | seq.increment AS VARCHAR(MAX) |
||
74 | ) AS increment, -- CAST avoids driver error for sql_variant type |
||
75 | CAST( |
||
76 | seq.start_value AS VARCHAR(MAX) |
||
77 | ) AS start_value -- CAST avoids driver error for sql_variant type |
||
78 | FROM sys.sequences AS seq'; |
||
79 | } |
||
80 | |||
81 | /** |
||
82 | * {@inheritdoc} |
||
83 | */ |
||
84 | 1 | public function getSequenceNextValSQL($sequenceName) |
|
85 | { |
||
86 | 1 | return 'SELECT NEXT VALUE FOR ' . $sequenceName; |
|
87 | } |
||
88 | |||
89 | /** |
||
90 | * {@inheritdoc} |
||
91 | */ |
||
92 | 1 | public function supportsSequences() |
|
93 | { |
||
94 | 1 | return true; |
|
95 | } |
||
96 | |||
97 | /** |
||
98 | * {@inheritdoc} |
||
99 | * |
||
100 | * Returns Microsoft SQL Server 2012 specific keywords class |
||
101 | */ |
||
102 | 57 | protected function getReservedKeywordsClass() |
|
103 | { |
||
104 | 57 | return Keywords\SQLServer2012Keywords::class; |
|
105 | } |
||
106 | |||
107 | /** |
||
108 | * {@inheritdoc} |
||
109 | */ |
||
110 | 25 | protected function doModifyLimitQuery($query, $limit, $offset = null) |
|
111 | { |
||
112 | 25 | if ($limit === null && $offset === null) { |
|
113 | return $query; |
||
114 | } |
||
115 | |||
116 | // Queries using OFFSET... FETCH MUST have an ORDER BY clause |
||
117 | // Find the position of the last instance of ORDER BY and ensure it is not within a parenthetical statement |
||
118 | // but can be in a newline |
||
119 | 25 | $matches = []; |
|
120 | 25 | $matchesCount = preg_match_all("/[\\s]+order\\s+by\\s/im", $query, $matches, PREG_OFFSET_CAPTURE); |
|
121 | 25 | $orderByPos = false; |
|
122 | 25 | if ($matchesCount > 0) { |
|
123 | 17 | $orderByPos = $matches[0][($matchesCount - 1)][1]; |
|
124 | } |
||
125 | |||
126 | 25 | if ($orderByPos === false |
|
127 | 25 | || substr_count($query, "(", $orderByPos) - substr_count($query, ")", $orderByPos) |
|
0 ignored issues
–
show
Bug
introduced
by
Loading history...
|
|||
128 | ) { |
||
129 | 9 | if (preg_match('/^SELECT\s+DISTINCT/im', $query)) { |
|
130 | // SQL Server won't let us order by a non-selected column in a DISTINCT query, |
||
131 | // so we have to do this madness. This says, order by the first column in the |
||
132 | // result. SQL Server's docs say that a nonordered query's result order is non- |
||
133 | // deterministic anyway, so this won't do anything that a bunch of update and |
||
134 | // deletes to the table wouldn't do anyway. |
||
135 | 2 | $query .= " ORDER BY 1"; |
|
136 | } else { |
||
137 | // In another DBMS, we could do ORDER BY 0, but SQL Server gets angry if you |
||
138 | // use constant expressions in the order by list. |
||
139 | 7 | $query .= " ORDER BY (SELECT 0)"; |
|
140 | } |
||
141 | } |
||
142 | |||
143 | 25 | if ($offset === null) { |
|
144 | 19 | $offset = 0; |
|
145 | } |
||
146 | |||
147 | // This looks somewhat like MYSQL, but limit/offset are in inverse positions |
||
148 | // Supposedly SQL:2008 core standard. |
||
149 | // Per TSQL spec, FETCH NEXT n ROWS ONLY is not valid without OFFSET n ROWS. |
||
150 | 25 | $query .= " OFFSET " . (int) $offset . " ROWS"; |
|
151 | |||
152 | 25 | if ($limit !== null) { |
|
153 | 25 | $query .= " FETCH NEXT " . (int) $limit . " ROWS ONLY"; |
|
154 | } |
||
155 | |||
156 | 25 | return $query; |
|
157 | } |
||
158 | } |
||
159 |