|
0
|
1 <?php
|
|
|
2
|
|
|
3 /**
|
|
|
4 +-----------------------------------------------------------------------+
|
|
|
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 *
|
|
|
24 * @package Framework
|
|
|
25 * @subpackage Database
|
|
|
26 */
|
|
|
27 class rcube_db_mssql extends rcube_db
|
|
|
28 {
|
|
|
29 public $db_provider = 'mssql';
|
|
|
30
|
|
|
31 /**
|
|
|
32 * Object constructor
|
|
|
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
|
|
|
37 */
|
|
|
38 public function __construct($db_dsnw, $db_dsnr = '', $pconn = false)
|
|
|
39 {
|
|
|
40 parent::__construct($db_dsnw, $db_dsnr, $pconn);
|
|
|
41
|
|
|
42 $this->options['identifier_start'] = '[';
|
|
|
43 $this->options['identifier_end'] = ']';
|
|
|
44 }
|
|
|
45
|
|
|
46 /**
|
|
|
47 * Driver-specific configuration of database connection
|
|
|
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)
|
|
|
55 $dbh->query("SET DATEFORMAT ymd");
|
|
|
56 }
|
|
|
57
|
|
|
58 /**
|
|
|
59 * Return SQL function for current time and date
|
|
|
60 *
|
|
|
61 * @param int $interval Optional interval (in seconds) to add/subtract
|
|
|
62 *
|
|
|
63 * @return string SQL function to use in query
|
|
|
64 */
|
|
|
65 public function now($interval = 0)
|
|
|
66 {
|
|
|
67 if ($interval) {
|
|
|
68 $interval = intval($interval);
|
|
|
69 return "dateadd(second, $interval, getdate())";
|
|
|
70 }
|
|
|
71
|
|
|
72 return "getdate()";
|
|
|
73 }
|
|
|
74
|
|
|
75 /**
|
|
|
76 * Return SQL statement to convert a field value into a unix timestamp
|
|
|
77 *
|
|
|
78 * @param string $field Field name
|
|
|
79 *
|
|
|
80 * @return string SQL statement to use in query
|
|
|
81 * @deprecated
|
|
|
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 *
|
|
|
107 * @param string $query SQL query
|
|
|
108 * @param int $limit Number of rows
|
|
|
109 * @param int $offset Offset
|
|
|
110 *
|
|
|
111 * @return string SQL query
|
|
|
112 */
|
|
|
113 protected function set_limit($query, $limit = 0, $offset = 0)
|
|
|
114 {
|
|
|
115 $limit = intval($limit);
|
|
|
116 $offset = intval($offset);
|
|
|
117 $end = $offset + $limit;
|
|
|
118
|
|
|
119 // query without OFFSET
|
|
|
120 if (!$offset) {
|
|
|
121 $query = preg_replace('/^SELECT\s/i', "SELECT TOP $limit ", $query);
|
|
|
122 return $query;
|
|
|
123 }
|
|
|
124
|
|
|
125 $orderby = stristr($query, 'ORDER BY');
|
|
|
126 $offset += 1;
|
|
|
127
|
|
|
128 if ($orderby !== false) {
|
|
|
129 $query = trim(substr($query, 0, -1 * strlen($orderby)));
|
|
|
130 }
|
|
|
131 else {
|
|
|
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";
|
|
|
135 }
|
|
|
136
|
|
|
137 $query = preg_replace('/^SELECT\s/i', '', $query);
|
|
|
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
|
|
|
141 return $query;
|
|
|
142 }
|
|
|
143
|
|
|
144 /**
|
|
|
145 * Returns PDO DSN string from DSN array
|
|
|
146 */
|
|
|
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 }
|
|
|
170
|
|
|
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 }
|
|
|
190 }
|
