commit | author | age
|
0d94fd
|
1 |
<?php |
AM |
2 |
|
a95874
|
3 |
/** |
0d94fd
|
4 |
+-----------------------------------------------------------------------+ |
AM |
5 |
| This file is part of the Roundcube Webmail client | |
|
6 |
| Copyright (C) 2005-2012, The Roundcube Dev Team | |
|
7 |
| | |
|
8 |
| Licensed under the GNU General Public License version 3 or | |
|
9 |
| any later version with exceptions for skins & plugins. | |
|
10 |
| See the README file for a full license statement. | |
|
11 |
| | |
|
12 |
| PURPOSE: | |
|
13 |
| Database wrapper class that implements PHP PDO functions | |
|
14 |
| for MS SQL Server database | |
|
15 |
+-----------------------------------------------------------------------+ |
|
16 |
| Author: Aleksander Machniak <alec@alec.pl> | |
|
17 |
+-----------------------------------------------------------------------+ |
|
18 |
*/ |
|
19 |
|
|
20 |
/** |
|
21 |
* Database independent query interface |
|
22 |
* This is a wrapper for the PHP PDO |
|
23 |
* |
9ab346
|
24 |
* @package Framework |
AM |
25 |
* @subpackage Database |
0d94fd
|
26 |
*/ |
AM |
27 |
class rcube_db_mssql extends rcube_db |
|
28 |
{ |
88107d
|
29 |
public $db_provider = 'mssql'; |
TB |
30 |
|
8c2375
|
31 |
/** |
d18640
|
32 |
* Object constructor |
AM |
33 |
* |
|
34 |
* @param string $db_dsnw DSN for read/write operations |
|
35 |
* @param string $db_dsnr Optional DSN for read only operations |
|
36 |
* @param bool $pconn Enables persistent connections |
8c2375
|
37 |
*/ |
d18640
|
38 |
public function __construct($db_dsnw, $db_dsnr = '', $pconn = false) |
0d94fd
|
39 |
{ |
d18640
|
40 |
parent::__construct($db_dsnw, $db_dsnr, $pconn); |
AM |
41 |
|
0d94fd
|
42 |
$this->options['identifier_start'] = '['; |
AM |
43 |
$this->options['identifier_end'] = ']'; |
|
44 |
} |
|
45 |
|
|
46 |
/** |
66407a
|
47 |
* Driver-specific configuration of database connection |
AM |
48 |
* |
|
49 |
* @param array $dsn DSN for DB connections |
|
50 |
* @param PDO $dbh Connection handler |
|
51 |
*/ |
|
52 |
protected function conn_configure($dsn, $dbh) |
|
53 |
{ |
|
54 |
// Set date format in case of non-default language (#1488918) |
120db6
|
55 |
$dbh->query("SET DATEFORMAT ymd"); |
66407a
|
56 |
} |
AM |
57 |
|
|
58 |
/** |
0d94fd
|
59 |
* Return SQL function for current time and date |
AM |
60 |
* |
aa44ce
|
61 |
* @param int $interval Optional interval (in seconds) to add/subtract |
AM |
62 |
* |
0d94fd
|
63 |
* @return string SQL function to use in query |
AM |
64 |
*/ |
aa44ce
|
65 |
public function now($interval = 0) |
0d94fd
|
66 |
{ |
aa44ce
|
67 |
if ($interval) { |
AM |
68 |
$interval = intval($interval); |
|
69 |
return "dateadd(second, $interval, getdate())"; |
|
70 |
} |
|
71 |
|
0d94fd
|
72 |
return "getdate()"; |
AM |
73 |
} |
|
74 |
|
|
75 |
/** |
|
76 |
* Return SQL statement to convert a field value into a unix timestamp |
|
77 |
* |
3d231c
|
78 |
* @param string $field Field name |
0d94fd
|
79 |
* |
3d231c
|
80 |
* @return string SQL statement to use in query |
0d94fd
|
81 |
* @deprecated |
AM |
82 |
*/ |
|
83 |
public function unixtimestamp($field) |
|
84 |
{ |
|
85 |
return "DATEDIFF(second, '19700101', $field) + DATEDIFF(second, GETDATE(), GETUTCDATE())"; |
|
86 |
} |
|
87 |
|
|
88 |
/** |
|
89 |
* Abstract SQL statement for value concatenation |
|
90 |
* |
|
91 |
* @return string SQL statement to be used in query |
|
92 |
*/ |
|
93 |
public function concat(/* col1, col2, ... */) |
|
94 |
{ |
|
95 |
$args = func_get_args(); |
|
96 |
|
|
97 |
if (is_array($args[0])) { |
|
98 |
$args = $args[0]; |
|
99 |
} |
|
100 |
|
|
101 |
return '(' . join('+', $args) . ')'; |
|
102 |
} |
|
103 |
|
|
104 |
/** |
|
105 |
* Adds TOP (LIMIT,OFFSET) clause to the query |
|
106 |
* |
3d231c
|
107 |
* @param string $query SQL query |
AM |
108 |
* @param int $limit Number of rows |
|
109 |
* @param int $offset Offset |
8c2375
|
110 |
* |
AM |
111 |
* @return string SQL query |
0d94fd
|
112 |
*/ |
AM |
113 |
protected function set_limit($query, $limit = 0, $offset = 0) |
|
114 |
{ |
8c2375
|
115 |
$limit = intval($limit); |
AM |
116 |
$offset = intval($offset); |
ec6a77
|
117 |
$end = $offset + $limit; |
8c2375
|
118 |
|
ec6a77
|
119 |
// query without OFFSET |
879b23
|
120 |
if (!$offset) { |
ec6a77
|
121 |
$query = preg_replace('/^SELECT\s/i', "SELECT TOP $limit ", $query); |
879b23
|
122 |
return $query; |
AM |
123 |
} |
|
124 |
|
ec6a77
|
125 |
$orderby = stristr($query, 'ORDER BY'); |
AM |
126 |
$offset += 1; |
|
127 |
|
8c2375
|
128 |
if ($orderby !== false) { |
ec6a77
|
129 |
$query = trim(substr($query, 0, -1 * strlen($orderby))); |
8c2375
|
130 |
} |
ec6a77
|
131 |
else { |
AM |
132 |
// it shouldn't happen, paging without sorting has not much sense |
|
133 |
// @FIXME: I don't know how to build paging query without ORDER BY |
|
134 |
$orderby = "ORDER BY 1"; |
8c2375
|
135 |
} |
0d94fd
|
136 |
|
ec6a77
|
137 |
$query = preg_replace('/^SELECT\s/i', '', $query); |
AM |
138 |
$query = "WITH paging AS (SELECT ROW_NUMBER() OVER ($orderby) AS [RowNumber], $query)" |
|
139 |
. " SELECT * FROM paging WHERE [RowNumber] BETWEEN $offset AND $end ORDER BY [RowNumber]"; |
|
140 |
|
0d94fd
|
141 |
return $query; |
AM |
142 |
} |
|
143 |
|
|
144 |
/** |
8c2375
|
145 |
* Returns PDO DSN string from DSN array |
0d94fd
|
146 |
*/ |
AM |
147 |
protected function dsn_string($dsn) |
|
148 |
{ |
|
149 |
$params = array(); |
|
150 |
$result = $dsn['phptype'] . ':'; |
|
151 |
|
|
152 |
if ($dsn['hostspec']) { |
|
153 |
$host = $dsn['hostspec']; |
|
154 |
if ($dsn['port']) { |
|
155 |
$host .= ',' . $dsn['port']; |
|
156 |
} |
|
157 |
$params[] = 'host=' . $host; |
|
158 |
} |
|
159 |
|
|
160 |
if ($dsn['database']) { |
|
161 |
$params[] = 'dbname=' . $dsn['database']; |
|
162 |
} |
|
163 |
|
|
164 |
if (!empty($params)) { |
|
165 |
$result .= implode(';', $params); |
|
166 |
} |
|
167 |
|
|
168 |
return $result; |
|
169 |
} |
90f7aa
|
170 |
|
AM |
171 |
/** |
|
172 |
* Parse SQL file and fix table names according to table prefix |
|
173 |
*/ |
|
174 |
protected function fix_table_names($sql) |
|
175 |
{ |
|
176 |
if (!$this->options['table_prefix']) { |
|
177 |
return $sql; |
|
178 |
} |
|
179 |
|
|
180 |
// replace sequence names, and other postgres-specific commands |
|
181 |
$sql = preg_replace_callback( |
|
182 |
'/((TABLE|(?<!ON )UPDATE|INSERT INTO|FROM(?! deleted)| ON(?! (DELETE|UPDATE|\[PRIMARY\]))' |
|
183 |
. '|REFERENCES|CONSTRAINT|TRIGGER|INDEX)\s+(\[dbo\]\.)?[\[\]]*)([^\[\]\( \r\n]+)/', |
|
184 |
array($this, 'fix_table_names_callback'), |
|
185 |
$sql |
|
186 |
); |
|
187 |
|
|
188 |
return $sql; |
|
189 |
} |
0d94fd
|
190 |
} |