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
|
<?php
/**
+-----------------------------------------------------------------------+
| This file is part of the Roundcube Webmail client |
| Copyright (C) 2005-2012, The Roundcube Dev Team |
| |
| Licensed under the GNU General Public License version 3 or |
| any later version with exceptions for skins & plugins. |
| See the README file for a full license statement. |
| |
| PURPOSE: |
| Database wrapper class that implements PHP PDO functions |
| for MS SQL Server database |
+-----------------------------------------------------------------------+
| Author: Aleksander Machniak <alec@alec.pl> |
+-----------------------------------------------------------------------+
*/
/**
* Database independent query interface
* This is a wrapper for the PHP PDO
*
* @package Framework
* @subpackage Database
*/
class rcube_db_mssql extends rcube_db
{
public $db_provider = 'mssql';
/**
* Object constructor
*
* @param string $db_dsnw DSN for read/write operations
* @param string $db_dsnr Optional DSN for read only operations
* @param bool $pconn Enables persistent connections
*/
public function __construct($db_dsnw, $db_dsnr = '', $pconn = false)
{
parent::__construct($db_dsnw, $db_dsnr, $pconn);
$this->options['identifier_start'] = '[';
$this->options['identifier_end'] = ']';
}
/**
* Driver-specific configuration of database connection
*
* @param array $dsn DSN for DB connections
* @param PDO $dbh Connection handler
*/
protected function conn_configure($dsn, $dbh)
{
// Set date format in case of non-default language (#1488918)
$dbh->query("SET DATEFORMAT ymd");
}
/**
* Return SQL function for current time and date
*
* @param int $interval Optional interval (in seconds) to add/subtract
*
* @return string SQL function to use in query
*/
public function now($interval = 0)
{
if ($interval) {
$interval = intval($interval);
return "dateadd(second, $interval, getdate())";
}
return "getdate()";
}
/**
* Return SQL statement to convert a field value into a unix timestamp
*
* @param string $field Field name
*
* @return string SQL statement to use in query
* @deprecated
*/
public function unixtimestamp($field)
{
return "DATEDIFF(second, '19700101', $field) + DATEDIFF(second, GETDATE(), GETUTCDATE())";
}
/**
* Abstract SQL statement for value concatenation
*
* @return string SQL statement to be used in query
*/
public function concat(/* col1, col2, ... */)
{
$args = func_get_args();
if (is_array($args[0])) {
$args = $args[0];
}
return '(' . join('+', $args) . ')';
}
/**
* Adds TOP (LIMIT,OFFSET) clause to the query
*
* @param string $query SQL query
* @param int $limit Number of rows
* @param int $offset Offset
*
* @return string SQL query
*/
protected function set_limit($query, $limit = 0, $offset = 0)
{
$limit = intval($limit);
$offset = intval($offset);
$end = $offset + $limit;
// query without OFFSET
if (!$offset) {
$query = preg_replace('/^SELECT\s/i', "SELECT TOP $limit ", $query);
return $query;
}
$orderby = stristr($query, 'ORDER BY');
$offset += 1;
if ($orderby !== false) {
$query = trim(substr($query, 0, -1 * strlen($orderby)));
}
else {
// it shouldn't happen, paging without sorting has not much sense
// @FIXME: I don't know how to build paging query without ORDER BY
$orderby = "ORDER BY 1";
}
$query = preg_replace('/^SELECT\s/i', '', $query);
$query = "WITH paging AS (SELECT ROW_NUMBER() OVER ($orderby) AS [RowNumber], $query)"
. " SELECT * FROM paging WHERE [RowNumber] BETWEEN $offset AND $end ORDER BY [RowNumber]";
return $query;
}
/**
* Returns PDO DSN string from DSN array
*/
protected function dsn_string($dsn)
{
$params = array();
$result = $dsn['phptype'] . ':';
if ($dsn['hostspec']) {
$host = $dsn['hostspec'];
if ($dsn['port']) {
$host .= ',' . $dsn['port'];
}
$params[] = 'host=' . $host;
}
if ($dsn['database']) {
$params[] = 'dbname=' . $dsn['database'];
}
if (!empty($params)) {
$result .= implode(';', $params);
}
return $result;
}
}
|
