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
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
|
<?php
/**
* TMsssqlCommandBuilder class file.
*
* @author Wei Zhuo <weizhuo[at]gmail[dot]com>
* @link http://www.pradosoft.com/
* @copyright Copyright © 2005-2014 PradoSoft
* @license http://www.pradosoft.com/license/
* @package System.Data.Common
*/
Prado::using('System.Data.Common.TDbCommandBuilder');
/**
* TMssqlCommandBuilder provides specifics methods to create limit/offset query commands
* for MSSQL servers.
*
* @author Wei Zhuo <weizho[at]gmail[dot]com>
* @package System.Data.Common
* @since 3.1
*/
class TMssqlCommandBuilder extends TDbCommandBuilder
{
/**
* Overrides parent implementation. Uses "SELECT @@Identity".
* @return integer last insert id, null if none is found.
*/
public function getLastInsertID()
{
foreach($this->getTableInfo()->getColumns() as $column)
{
if($column->hasSequence())
{
$command = $this->getDbConnection()->createCommand('SELECT @@Identity');
return intval($command->queryScalar());
}
}
}
/**
* Overrides parent implementation. Alters the sql to apply $limit and $offset.
* The idea for limit with offset is done by modifying the sql on the fly
* with numerous assumptions on the structure of the sql string.
* The modification is done with reference to the notes from
* http://troels.arvin.dk/db/rdbms/#select-limit-offset
*
* <code>
* SELECT * FROM (
* SELECT TOP n * FROM (
* SELECT TOP z columns -- (z=n+skip)
* FROM tablename
* ORDER BY key ASC
* ) AS FOO ORDER BY key DESC -- ('FOO' may be anything)
* ) AS BAR ORDER BY key ASC -- ('BAR' may be anything)
* </code>
*
* <b>Regular expressions are used to alter the SQL query. The resulting SQL query
* may be malformed for complex queries.</b> The following restrictions apply
*
* <ul>
* <li>
* In particular, <b>commas</b> should <b>NOT</b>
* be used as part of the ordering expression or identifier. Commas must only be
* used for separating the ordering clauses.
* </li>
* <li>
* In the ORDER BY clause, the column name should NOT be be qualified
* with a table name or view name. Alias the column names or use column index.
* </li>
* <li>
* No clauses should follow the ORDER BY clause, e.g. no COMPUTE or FOR clauses.
* </li>
* </ul>
*
* @param string SQL query string.
* @param integer maximum number of rows, -1 to ignore limit.
* @param integer row offset, -1 to ignore offset.
* @return string SQL with limit and offset.
*/
public function applyLimitOffset($sql, $limit=-1, $offset=-1)
{
$limit = $limit!==null ? intval($limit) : -1;
$offset = $offset!==null ? intval($offset) : -1;
if ($limit > 0 && $offset <= 0) //just limit
$sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $limit", $sql);
else if($limit > 0 && $offset > 0)
$sql = $this->rewriteLimitOffsetSql($sql, $limit,$offset);
return $sql;
}
/**
* Rewrite sql to apply $limit > and $offset > 0 for MSSQL database.
* See http://troels.arvin.dk/db/rdbms/#select-limit-offset
* @param string sql query
* @param integer $limit > 0
* @param integer $offset > 0
* @return sql modified sql query applied with limit and offset.
*/
protected function rewriteLimitOffsetSql($sql, $limit, $offset)
{
$fetch = $limit+$offset;
$sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $fetch", $sql);
$ordering = $this->findOrdering($sql);
$orginalOrdering = $this->joinOrdering($ordering);
$reverseOrdering = $this->joinOrdering($this->reverseDirection($ordering));
$sql = "SELECT * FROM (SELECT TOP {$limit} * FROM ($sql) as [__inner top table__] {$reverseOrdering}) as [__outer top table__] {$orginalOrdering}";
return $sql;
}
/**
* Base on simplified syntax http://msdn2.microsoft.com/en-us/library/aa259187(SQL.80).aspx
*
* @param string $sql
* @return array ordering expression as key and ordering direction as value
*/
protected function findOrdering($sql)
{
if(!preg_match('/ORDER BY/i', $sql))
return array();
$matches=array();
$ordering=array();
preg_match_all('/(ORDER BY)[\s"\[](.*)(ASC|DESC)?(?:[\s"\[]|$|COMPUTE|FOR)/i', $sql, $matches);
if(count($matches)>1 && count($matches[2]) > 0)
{
$parts = explode(',', $matches[2][0]);
foreach($parts as $part)
{
$subs=array();
if(preg_match_all('/(.*)[\s"\]](ASC|DESC)$/i', trim($part), $subs))
{
if(count($subs) > 1 && count($subs[2]) > 0)
{
$ordering[$subs[1][0]] = $subs[2][0];
}
//else what?
}
else
$ordering[trim($part)] = 'ASC';
}
}
return $ordering;
}
/**
* @param array ordering obtained from findOrdering()
* @return string concat the orderings
*/
protected function joinOrdering($orders)
{
if(count($orders)>0)
{
$str=array();
foreach($orders as $column => $direction)
$str[] = $column.' '.$direction;
return 'ORDER BY '.implode(', ', $str);
}
}
/**
* @param array original ordering
* @return array ordering with reversed direction.
*/
protected function reverseDirection($orders)
{
foreach($orders as $column => $direction)
$orders[$column] = strtolower(trim($direction))==='desc' ? 'ASC' : 'DESC';
return $orders;
}
}
|
